createtable t1(
id int ....,
num int,
xx int,
unique 唯一索引名称 (列名,列名),
constraint ....
)
#
111212
PS:
唯一:
约束不能重复(可以为空)
PS: 主键不能重复(不能为空)
加速查找
3)一对一
createtable userinfo1(
id int auto_increment primarykey,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;createtable admin(
id intnotnull auto_increment primarykey,
username varchar(64) notnull,
password VARCHAR(64) notnull,
user_id intnotnull,
unique uq_u1 (user_id),
CONSTRAINT fk_admin_u1 FOREIGNkey (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8;
4)多对多
reate table userinfo2(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;
createtable host(
id int auto_increment primarykey,
hostname char(64)
)engine=innodb default charset=utf8;createtable user2host(
id int auto_increment primarykey,
userid intnotnull,
hostid intnotnull,
unique uq_user_host (userid,hostid),
CONSTRAINT fk_u2h_user FOREIGNkey (userid) REFERENCES userinfo2(id),
CONSTRAINT fk_u2h_host FOREIGNkey (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;
3, SQL语句数据行操作补充
createtable tb12(
id int auto_increment primarykey,
name varchar(32),
age int
)engine=innodb default charset=utf8;
增
insertinto tb11(name,age) values('alex',12);insertinto tb11(name,age) values('alex',12),('root',18);insertinto tb12(name,age) select name,age from tb11;
删
deletefrom tb12;deletefrom tb12 where id !=2deletefrom tb12 where id =2deletefrom tb12 where id > 2deletefrom tb12 where id >=2deletefrom tb12 where id >=2or name='alex'
改
update tb12 set name='alex'where id>12and name='xx'update tb12 set name='alex',age=19where id>12and name='xx'
查
select * from tb12;select id,name from tb12;select id,name from tb12 where id > 10or name ='xxx';select id,name as cname from tb12 where id > 10or name ='xxx';select name,age,11from tb12;
其他:
select * from tb12 where id != 1select * from tb12 where id in (1,5,12);select * from tb12 where id notin (1,5,12);select * from tb12 where id in (select id from tb11)
select * from tb12 where id between 5and12;
通配符:
select * from tb12 where name like"a%"select * from tb12 where name like"a_"
分页:
select * from tb12 limit 10;select * from tb12 limit 0,10;select * from tb12 limit 10,10;select * from tb12 limit 20,10;select * from tb12 limit 10 offset 20;
从第20行开始读取,读取10行;
结合Python分页:
# page = input('请输入要查看的页码')
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1
# select * from tb12 limit 10,10;2
排序:
select * from tb12 orderby id desc; 大到小
select * from tb12 orderby id asc; 小到大
select * from tb12 orderby age desc,id desc;
取后10条数据
select * from tb12 orderby id desc limit 10;
分组:
selectcount(id),max(id),part_id from userinfo5 groupby part_id;
count
max
min
sum
avg
**** 如果对于聚合函数结果进行二次筛选时?必须使用having ****
selectcount(id),part_id from userinfo5 groupby part_id havingcount(id) > 1;selectcount(id),part_id from userinfo5 where id > 0groupby part_id havingcount(id) > 1;
连表操作:
select * from userinfo5,department5
select * from userinfo5,department5 where userinfo5.part_id = department5.id
select * from userinfo5 leftjoin department5 on userinfo5.part_id = department5.id
select * from department5 leftjoin userinfo5 on userinfo5.part_id = department5.id
# userinfo5左边全部显示
# select * from userinfo5 rightjoin department5 on userinfo5.part_id = department5.id
# department5右边全部显示
select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
将出现null时一行隐藏
select * from
department5
leftjoin userinfo5 on userinfo5.part_id = department5.id
leftjoin userinfo6 on userinfo5.part_id = department5.id
select
score.sid,
student.sid
from
score
leftjoin student on score.student_id = student.sid
leftjoin course on score.course_id = course.cid
leftjoin class on student.class_id = class.cid
leftjoin teacher on course.teacher_id=teacher.tid
selectcount(id) from userinfo5;