1.主键：
一个表只能有一个主键，主键可以由多列组成。
表中可以有对应的多个外键
1 CREATE TABLE t5 (
2 nid int(11) NOT NULL AUTO_INCREMENT,
3 pid int(11) not NULL,
4 num int(11),
5 primary key(nid,pid)
6 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
7
8
9
10 create table t6(
11 id int auto_increment primary key,
12 name char(10),
13 id1 int,
14 id2 int,
15 CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid)
16 )engine=innodb default charset=utf8;
1 先创建tb2部门表
2
3 create table tb2(
4 id int not null auto_increment primary key,
5 department varchar(30)
6 )engine=inbodb default charset=utf8;
7
8 create table tb1用户表(
9 id int not null auto_increment primary key,
10 name char(10),
11 department_id int,
12 p_id int,
13 constraint fk_1 foreign key (department_id,p_id) references tb2(tid,xid)
14 )engine=innodb default charset=utf8;View Code
2.对于自增的补充：
1 查看表字段属性： desc t10;
2
3 查看表创建信息 show create table t10;
4
5 竖行查看： show create table t10 \G;
6
7 设置自增字段的初始值：alter table t10 AUTO_INCREMENT=20;
8
9 MySQL: 自增步长
10 基于会话级别：
11 show session variables like 'auto_inc%'; 查看会话全局变量
12 set session auto_increment_increment=2; 设置会话步长
13 # set session auto_increment_offset=10;
14 基于全局级别：
15 show global variables like 'auto_inc%'; 查看全局变量
16 set global auto_increment_increment=2; 设置会话步长
17 # set global auto_increment_offset=10;
18
19
20 SqlServer：自增步长：
21 基础表级别：
22 CREATE TABLE `t5` (
23 `nid` int(11) NOT NULL AUTO_INCREMENT,
24 `pid` int(11) NOT NULL,
25 `num` int(11) DEFAULT NULL,
26 PRIMARY KEY (`nid`,`pid`)
27 ) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8
28
29 CREATE TABLE `t6` (
30 `nid` int(11) NOT NULL AUTO_INCREMENT,
31 `pid` int(11) NOT NULL,
32 `num` int(11) DEFAULT NULL,
33 PRIMARY KEY (`nid`,`pid`)
34 ) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8
35View Code
3.类似主键外键的唯一索引：
唯一：
约束不能重复（可以为空）
PS: 主键不能重复（不能为空）
加速查找
1 create table t1(
2 id int ....,
3 num int,
4 xx int,
5 unique 唯一索引名称 (列名,列名),
6 constraint ....
7 )
4.外键的变种：
1 ===》多对多
2
3 create table userinfo2(
4 id int auto_increment primary key,
5 name char(10),
6 gender char(10),
7 email varchar(64)
8 )engine=innodb default charset=utf8;
9
10 create table host(
11 id int auto_increment primary key,
12 hostname char(64)
13 )engine=innodb default charset=utf8;
14
15
16 主机归属表： create table user2host(
17 id int auto_increment primary key,
18 userid int not null,
19 hostid int not null,
20 unique uq_user_host (userid,hostid),
21 CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
22 CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
23 )engine=innodb default charset=utf8;
24
25
26 一个用户可以有多个主机使用权，但为避免重复，所以对用户与主机的2个关系做了联合唯一索引，规范表的信息。View Code
5.SQL语句数据操作补充。
1 create table tb12(
2 id int auto_increment primary key,
3 name varchar(32),
4 age int
5 )engine=innodb default charset=utf8;
6
7 增
8 insert into tb11(name,age) values('alex',12);
9
10 多个插入: insert into tb11(name,age) values('alex',12),('root',18);
11
12 从tb11表得到数据插入到tb12: insert into tb12(name,age) select name,age from tb11;
13 删
14 delete from tb12;
15 delete from tb12 where id !=2
16 delete from tb12 where id =2
17 delete from tb12 where id > 2
18 delete from tb12 where id >=2
19 delete from tb12 where id >=2 or name='alex'
20
21 改
22 update tb12 set name='alex' where id>12 and name='xx'
23 update tb12 set name='alex',age=19 where id>12 and name='xx'
24 查
25
26 select * from tb12;
27
28 select id,name from tb12;
29
30 select id,name from tb12 where id > 10 or name ='xxx';
31
32 select id,name as cname from tb12 where id > 10 or name ='xxx';
33
34 select name,age,11 from tb12;
35
36 其他：
37 select * from tb12 where id != 1
38 select * from tb12 where id in (1,5,12);
39 select * from tb12 where id not in (1,5,12);
40 select * from tb12 where id in (select id from tb11)
41 查看5-12id的数据，包括5,12: select * from tb12 where id between 5 and 12;
42
43
44 通配符：
45
46 %表示多个： select * from tb12 where name like "a%"
47 _表示一个： select * from tb12 where name like "a_"
48
49
50 分页：
51
52 初始值开始前10个： select * from tb12 limit 10;
53
54 select * from tb12 limit 0,10;
55 从10开始往后的10个： select * from tb12 limit 10,10;
56 select * from tb12 limit 20,10;
57
58 取20之前的10个数据： select * from tb12 limit 10 offset 20;
59
60
61 # page = input('请输入要查看的页码')
62 # page = int(page)
63 # (page-1) * 10
64 # select * from tb12 limit 0,10; 1
65 # select * from tb12 limit 10,10;2
66
67
68 排序：
69 select * from tb12 order by id desc; 大到小
70 select * from tb12 order by id asc; 小到大
71
72
73
74 age从大到小排，如果数据的age相等，按他们的id从大到小排： select * from tb12 order by age desc,id desc;
75
76 取后10条数据
77 select * from tb12 order by id desc limit 10;
78
79 分组：
80
81 select count(id),max(id),part_id from userinfo5 group by part_id;
82
83 计数 count
84 max
85 min
86 sum
87 平均值 avg
88
89 **** 如果对于聚合函数结果进行二次筛选时？必须使用having ****
90 取part_id列超过2个计数的part_id值： select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
91
92 select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
93
94
95 连表操作：
96
97 select * from userinfo5,department5
98
99 select * from userinfo5,department5 where userinfo5.part_id = department5.id
100
101
102 select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
103 select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
104 # userinfo5左边全部显示
105
106
107 # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
108 # department5右边全部显示
109
110
111
112 select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
113 将出现null时一行隐藏
114
115
116
117
118
119
120 select * from
121 department5
122 left join userinfo5 on userinfo5.part_id = department5.id
123 left join userinfo6 on userinfo5.part_id = department5.id
124
125
126 select
127 score.sid,
128 student.sid
129 from
130 score
131
132 left join student on score.student_id = student.sid
133
134 left join course on score.course_id = course.cid
135
136 left join class on student.class_id = class.cid
137
138 left join teacher on course.teacher_id=teacher.tid
139
140
141
142
143 select count(id) from userinfo5;View Code
mysql 给表添加一个联合唯一索引: alter table user add nuique index(user_id,user_name);
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
