Myql基础补充
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;
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 35
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个关系做了联合唯一索引，规范表的信息。
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;
mysql 给表添加一个联合唯一索引: alter table user add nuique index(user_id,user_name);
