建表建库:create database if not exists dbtest (库名) default charset utf8;
create table if not exists a(
id int primary key auto_increment,
name varchar(20) not null
bir date
);
not null 非空
primary key 主键,唯一 // auto_increment 自增 // int 数字型 // varchar 字符型 // date 日期型
增删改查
增: insert into a(name) values('张三'),('李四');
删: delete from a where 删除表信息,where后面不加条件,删除表里所有信息
改:update a set name='李四' where id=2 ; 将id=2的name改为李四
查:select * from a; 查询所有信息
select name ,bir from a 查询表里的name,bir的信息
select name from a where name='张三' 查询name为张三的bir
select * from a where name='张三' 查询表里name为张三的所有信息
表关联查询
内连接 inner join ... on
select * from table_a a inner join table_b b on a.a_id=b.b_id;
select * from table_a a,table_b b where a.a_id=b.b_id;
左连接 left join ...on
select * from table_a a left join table_b b on a.a_id=b.b_id;
右连接 right join ... on
select * from table_a a right join table_b b on a.a_id=b.b_id;
联合查询 union 如果后面没有all,则自动去重
select a_id,a_name from table_a union all select b_id ,b_bir from table_b
全连接
select * from table_a left join table_b on a.id = b.id union select * from table_a right join table_b on a.id = b.id ;
子查询