MySQL
Concept¶
MySQL is an open-source relational database management system (RDBMS) that is widely used for storing and managing structured data. It is one of the most popular relational databases in the world and is known for its performance, reliability, and ease of use. MySQL is often used in web applications, content management systems (CMS), e-commerce platforms, and various other software applications that require data storage and retrieval.
Create¶
create database mydatabase
create table table1 (
id int not null primary key
name varchar(255) not null,
mail varchar (255)
)
CREATE TABLE `table1` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`MAIL` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
-- table with reference
create table table2(
id int not null auto_increment,
name varchar(50) not null,
created_by int not null,
marca varchar(50),
primary key(id),
foreign key(created_by) references table1(id),
);
ALTER TABLE mytable ADD myNewColumn int;
INSERT INTO table1 (name, mail) VALUES ('name1','name@mail.ru');
insert into CLIENTE values (1,"fran","78945632","masculino","viudo");
Show content¶
show databases
SHOW CREATE TABLE table1 -- show the form of the table
SELECT * FROM table1; -- show all content of table
-- show content of table
SELECT * FROM table1 WHERE id=1;
SELECT * FROM table1 WHERE mail='name@mail.com' AND name = 'name1';
SELECT * FROM table1 limit 1;
SELECT * FROM table1 WHERE id>5;
SELECT id, name FROM table1;
Modify¶
rename table table1 to table11 -- rename table
ALTER TABLE table1 MODIFY COLUMN id int auto_increment; -- modify column
drop table table1 -- delete table1
UPDATE table1 SET name = 'name3' where id = 2; -- update content
DELETE from table1 WHERE id = 3; -- delete
Advanced Filters¶
SELECT * FROM table1 WHERE email like '%gmail%'
SELECT * FROM table1 order by id asc;
SELECT * FROM table1 order by id desc;
SELECT max(id) as mayor from table1;
Select min(id) as menor from table1;
-- show left join and right join
select * from table;
select u.id, u.email, p.name from table1 u left join table2 p on u.id=p.create_by;
select u.id, u.email, p.name from table1 u right join table2 p on u.id=p.create_by;
select u.id, u.email, p.name from table1 u inner join table2 p on u.id=p.create_by;
select u.id, u.name, p.id, p.name from table1 u cross join table2 p;