mysql2tb_MySQL2

一、主键、外键补充

create table tb1用户表(

id int not null auto_increment primary key,

name char(10),

department_id int,

p_id int,

constraint fk_1 foreign key (department_id,p_id) references tb2(tid,xid)

)engine=innodb default charset=utf8;

补充：主键

一个表只能有一个主键

主键可以由多列组成

补充：外键 ?

CREATE TABLE t5 (

nid int(11) NOT NULL AUTO_INCREMENT,

pid int(11) not NULL,

num int(11),

primary key(nid,pid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t6(

id int auto_increment primary key,

name char(10),

id1 int,

id2 int,

CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid)

)engine=innodb default charset=utf8;

二、数据行

insert into tb1(name,age) values('alex',18);

insert into tb1(name,age) values('alex',18),('egon',19),('yuan',20);　　　　可以同时插入多条数据

update tb1 set name='root' where id > 10

select * from tb;

select id,name from tb;

三、对于自增补充：

desc t10;　　　　查看该表的创建方式：列名，是否可以为空，数据类型，是否为主键，是否为自增

show create table t10;

show create table t10 \G;

alter table t10 AUTO_INCREMENT=20;　　　　修改下次开始自增的值

MySQL: 自增步长

基于会话级别：

show session variables like 'auto_inc%';查看全局变量

set session auto_increment_increment=2; 设置会话步长

# set session auto_increment_offset=10;

基于全局级别：

show global variables like 'auto_inc%'; 查看全局变量

set global auto_increment_increment=2; 设置会话步长

# set global auto_increment_offset=10;

SqlServer：自增步长：

基于表级别：

CREATE TABLE `t5` (

`nid` int(11) NOT NULL AUTO_INCREMENT,

`pid` int(11) NOT NULL,

`num` int(11) DEFAULT NULL,

PRIMARY KEY (`nid`,`pid`)

) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8

CREATE TABLE `t6` (

`nid` int(11) NOT NULL AUTO_INCREMENT,

`pid` int(11) NOT NULL,

`num` int(11) DEFAULT NULL,

PRIMARY KEY (`nid`,`pid`)

) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8

四、 今日内容：

0. 唯一索引：约束该列中数据唯一

create table t1(

id int ....,

num int,

xx int,

unique 唯一索引名称 (列名,列名),　　　　联合唯一

constraint ....

)

#

1 1 1

2 1 2

PS:

唯一索引：

约束不能重复(可以为空)

PS: 主键不能重复(不能为空)

加速查找

1. 外键的变种

a. 用户表和部门表

用户：

1 alex 1

2 root 1

3 egon 2

4 laoyao 3

部门:

1 服务

2 保安

3 公关

===》 一对多

b. 用户表和博客表

用户表：

1 alex

2 root

3 egon

4 laoyao

博客表：

FK() + 唯一

1 /yuanchenqi/ 4

2 /alex3714/ 1

3 /asdfasdf/ 3

4 /ffffffff/ 2

===> 一对一

create table userinfo1(

id int auto_increment primary key,

name char(10),

gender char(10),

email varchar(64)

)engine=innodb default charset=utf8;

create table admin(

id int not null auto_increment primary key,

username varchar(64) not null,

password VARCHAR(64) not null,

user_id int not null,

unique uq_u1 (user_id),

CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)

)engine=innodb default charset=utf8;

c. 用户表(百合网) 相亲记录表

示例1：

用户表

相亲表

示例2：

用户表

主机表

用户主机关系表

===》多对多

create table userinfo2(

id int auto_increment primary key,

name char(10),

gender char(10),

email varchar(64)

)engine=innodb default charset=utf8;

create table host(

id int auto_increment primary key,

hostname char(64)

)engine=innodb default charset=utf8;

create table user2host(

id int auto_increment primary key,

userid int not null,

hostid int not null,

unique uq_user_host (userid,hostid),

CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),

CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)

)engine=innodb default charset=utf8;

2. SQL语句数据行操作补充

create table tb12(

id int auto_increment primary key,

name varchar(32),

age int

)engine=innodb default charset=utf8;

增

insert into tb11(name,age) values('alex',12);

insert into tb11(name,age) values('alex',12),('root',18);

insert into tb12(name,age) select name,age from tb11;　　　　将tb11中的数据插入到tb12

删

delete from tb12;

delete from tb12 where id !=2

delete from tb12 where id =2

delete from tb12 where id > 2

delete from tb12 where id >=2

delete from tb12 where id >=2 or name='alex'

改

update tb12 set name='alex' where id>12 and name='xx'

update tb12 set name='alex',age=19 where id>12 and name='xx'

查(重点)

select * from tb12;

select id,name from tb12;

select id,name from tb12 where id > 10 or name ='xxx';

select id,name as cname from tb12 where id > 10 or name ='xxx';

select name,age,11 from tb12;

其他：

select * from tb12 where id != 1

select * from tb12 where id in (1,5,12);

select * from tb12 where id not in (1,5,12);

select * from tb12 where id in (select id from tb11)

select * from tb12 where id between 5 and 12;

通配符：

select * from tb12 where name like "a%"

select * from tb12 where name like "a_"

分页：

select * from tb12 limit 10;　　　　　　　　查看该表前10行数据

select * from tb12 limit 0,10;　　　　　　从1开始取10行数据

select * from tb12 limit 10,10;　　　　　 从11开始取10行数据

select * from tb12 limit 20,10;　　　　　 从21开始取10行数据

select * from tb12 limit 10 offset 20;　　　　　　从第21行开始读取，读取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 order by id desc; 　　　　　　　　大到小

select * from tb12 order by id asc; 　　　　　　　　  小到大

select * from tb12 order by age desc,id desc;

取后10条数据

select * from tb12 order by id desc limit 10;

分组：

select count(id),max(id),part_id from userinfo5 group by part_id;

聚合函数：

count

max

min

sum

avg

**** 如果对于聚合函数结果进行二次筛选时？必须使用having ****

select count(id),part_id from userinfo5 group by part_id having count(id) > 1;

select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;

连表操作：

select * from userinfo5,department5 where userinfo5.part_id = department5.id

select * from userinfo5 left join department5 on userinfo5.part_id = department5.id

select * from department5 left join userinfo5 on userinfo5.part_id = department5.id

# userinfo5左边全部显示

# select * from userinfo5 left join department5 on userinfo5.part_id = department5.id

# department5右边全部显示

# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id

将出现null时一行隐藏

select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id

将多个(3个)表连接起来：

select * from

department5

left join userinfo5 on userinfo5.part_id = department5.id

left join userinfo6 on userinfo5.part_id = department5.id

将多个(5个)表连接起来：

select

score.sid,

student.sid

from

score

left join student on score.student_id = student.sid

left join course on score.course_id = course.cid

left join class on student.class_id = class.cid

left join teacher on course.teacher_id=teacher.tid

select count(id) from userinfo5;

作业练习：

http://www.cnblogs.com/wupeiqi/articles/5729934.html

10-15个完成