show databases;

create database lianxi DEFAULT character set utf8

use lianxi;

create table user_info(
id int not null auto_increment primary key,
name char(20),
age int,
gender char(1),
deparment_id int,
constraint 约束名称 foregin key(deparment_id) references dep_info(id)
)engine = innodb default charset=utf8;

create table dep_info(
id int not null auto_increment primary key,
title char(32),
)engine=innode default charset=utf8;

create table boy(
id int not null auto_increment primary key,
name char(32)
)engine = innodb default charset=utf8;

create table girl(
id int not null auto_increment primary key,
name char(32)
)engine = innodb default charset=utf8;

create table b2g(
id int not null auto_increment primary key,
b_id int,
g_id int,
constraint 约束名称1 foregin key(b_id) references boy(id),
constraint 约束名称2 foregin key(g_id) references girl(id)
)engine = innodb default charset = utf8;

drop database db_name;

insert into table_name(field) values(value),(value2) ;--> 两个(value)表示插如多行数据
insert into table_name(cname) select field from table_name; --> 把select查到的结果,当作数据来赋值给value

delete from table_name; --> 自增列会继续之前的ID
truncate table table_name； --> 物理删除，速度快，重新计算ID

delete from table_name where filed = values and/or ...  --> 只删除符合条件的数据
delete from table_name where filed in (1,2,3,4)；  
delete from table_name where id between 5 and 10； 

update table_name set field = ‘value‘； --> 更新所有数据的field字段的值，加 where 只修改匹配到的行
update table_name set id = 8 , name = ‘daxin‘ where age = 18;

select * from table_name where id > 2；
select field as ‘别名‘ from table_name --> 加别名
select * from table_name where id in (1,2)；
select * from table_name where cid in (select tid from teacher)；

select * from table_name order by field asc/desc(正序/倒序)
select * from table_name order by field asc limit 1 取第一个值
select * from table_name limit 1,2(起始位置,找几个)
select * from table_name where field like ‘%key%‘ --> 查找field字段包含key的数据
% 表示任意个任意字符， _表示任意一个字符

select * from table_name group by field --> 分组显示,会去重,需要使用聚合函数来统计重复的次数
select field,count(id) from table_name group by field --> 对id字段进行聚合(其他的还有min(),max(),sum(),avg()等)

SELECT class.caption,count(sid) from class
LEFT join student on student.class_id = class.cid group by class.caption;

SELECT class.caption,count(sid) as number from class
LEFT join student on student.class_id = class.cid
group by class.caption
HAVING number >= 2;

select course.cname,count(sid) from score
left join course on score.corse_id = course.cid
where number < 60
group by course.cname;