一、表的约束条件
1、约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
2、主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引（索引是一种树状结构）组织表，一张表中必须有且只有一个主键
ps: 无 primary key字段 ，无not null+unique
就会自动生成一个隐藏字段，
所以建立表的时候要有id字段，不为空且唯一的结构建立主键
作用：加速查询，表结构，表数据文件
# not null default create table t1(x int not null); insert into t1 values(); create table t2(x int not null default 111); insert into t2 values(); # unique # 单列唯一 create table t3(name varchar(10) unique); insert into t3 values("egon"); insert into t3 values("tom"); mysql> insert into t3 values("egon"); ERROR 1062 (23000): Duplicate entry ‘egon‘ for key ‘name‘ # 联合唯一 create table server( id int, name varchar(10), ip varchar(15), port int, unique(ip,port), unique(name) ); insert into server values (1,"web1","10.10.0.11",8080); insert into server values (2,"web2","10.10.0.11",8081); mysql> insert into server values(4,"web4","10.10.0.11",8081); ERROR 1062 (23000): Duplicate entry ‘10.10.0.11-8081‘ for key ‘ip‘ mysql> # not null 和unique的化学反应=>会被识别成表的主键 create table t4(id int,name varchar(10) not null unique); create table t5(id int,name varchar(10) unique); # 主键primary key # 特点 # 1、主键的约束效果是not null+unique # 2、innodb表有且只有一个主键，但是该主键可以是联合主键 create table t6( id int primary key auto_increment, name varchar(5) ); insert into t6(name) values ("egon"), ("tom"), ("to1"), ("to2"); # 联合主键（了解） create table t7( id int, name varchar(5), primary key(id,name) );
二、表之间的三种关系
多对一
多对多
一对一
# 引入 # 先创建被关联表 create table dep( id int primary key auto_increment, name varchar(6), comment varchar(30) ); # 再创建关联表 create table emp( id int primary key auto_increment, name varchar(10), gender varchar(5), dep_id int, foreign key(dep_id) references dep(id) on delete cascade on update cascade ); # 先往被关联表插入数据 insert into dep(id,name) values (1,‘技术部‘), (2,‘人力资源部‘), (3,‘销售部‘); # 先往关联表插入数据 insert into emp(name,gender,dep_id) values (‘egon‘,"male",1), (‘alex1‘,"male",2), (‘alex2‘,"male",2), (‘alex3‘,"male",2), (‘李坦克‘,"male",3), (‘刘飞机‘,"male",3), (‘张火箭‘,"male",3), (‘林子弹‘,"male",3), (‘加特林‘,"male",3) ; # 多对一 # 多对多 create table author( id int primary key auto_increment, name varchar(10) ); create table book( id int primary key auto_increment, name varchar(16) ); create table author2book( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on delete cascade on update cascade, foreign key(book_id) references book(id) on delete cascade on update cascade ); # 一对一 create table customer( id int primary key auto_increment, name varchar(16), phone char(11) ); create table student( id int primary key auto_increment, class varchar(10), course varchar(16), c_id int unique, foreign key(c_id) references customer(id) on delete cascade on update cascade );
三、表记录相关操作
增加记录
修改记录
删除记录
查询记录（单表查询，多表联合）
单表查询语法(一定要注意执行顺序！)
select distinct 字段1,字段2,字段3,... from 表名
where 过滤条件
group by 分组的条件
having 筛选条件
order by 排序字段
limit n;
# 插入 mysql> create table user(name varchar(16),password varchar(10)); Query OK, 0 rows affected (0.29 sec) mysql> mysql> insert into user select user,password from mysql.user; # 删除 delete from 表 where 条件; # 更新 update 表 set 字段=值 where 条件; # 单表查询语法 select distinct 字段1,字段2,字段3,... from 表名 where 过滤条件 group by 分组的条件 having 筛选条件 order by 排序字段 limit n; # 简单查询 select name,sex from emp; select name as 名字,sex 性别 from emp; select * from emp; # 避免重复（针对的是记录） select distinct post from emp; # 进行四则运算 select name as 名字,salary*12 as 年薪 from emp; # concat()拼接记录的内容 select name ,concat(salary*12,"$") from emp; select name ,concat("annual_salary",‘:‘,salary*12) as 年薪 from emp; select name ,concat("annual_salary",‘:‘,salary*12,‘:‘,‘$‘) as 年薪 from emp; select name ,concat_ws(":","annual_salary",salary*12,‘$‘) as 年薪 from emp; select ( case when name="egon" then name when name="alex" then concat(name,"_dsb") else concat(name,"_sb") end ) as 名字 from emp; ===========================================where select * from emp where id >= 3 and id <= 5; select * from emp where id between 3 and 5; select * from emp where id not between 3 and 5; select * from emp where id=3 or id=5 or id=7; select * from emp where id in (3,5,7); select * from emp where id not in (3,5,7); select * from emp where id=3 or id=5 or id=7; select * from emp where name like ‘jin%‘; select * from emp where name like ‘jin___‘; select * from emp where name regexp ‘n$‘; mysql> select * from emp where post_comment is not null; Empty set (0.00 sec) mysql> update emp set post_comment=‘‘ where id=3; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from emp where post_comment is not null; +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+ | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | | 8300.00 | 401 | 1 | +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+ 1 row in set (0.00 sec) mysql> select * from emp where name="丫丫"; select * from emp where name regexp "丫$"; select * from emp where name like "丫_"; select * from emp where name regexp "^程"; select hex(name) from t4 where hex(name) regexp ‘e[4-9][0-9a-f]{4}‘; ===========================================group by 分完组之后只能看到分组的字段以及聚合的结果 max() min() avg() sum() count() select depart_id,count(id),avg(salary),max(age),min(salary),sum(salary) from emp group by depart_id; # 每个部门都有多少个人 select depart_id,count(id) from emp group by depart_id; # 每个职位男生的平均薪资 select post,avg(salary) from emp where sex="male" group by post; select post, group_concat(name) from emp group by post; select post, group_concat(name) from emp where sex="male" group by post; ===========================================having # having与where本质区别就是在于having是在分组之后发生过滤，可以使用聚合函数 mysql> select max(salary) from emp where max(salary) > 100000; ERROR 1111 (HY000): Invalid use of group function mysql> select max(salary) from emp having max(salary) > 100000; +-------------+ | max(salary) | +-------------+ | 1000000.31 | +-------------+ 1 row in set (0.00 sec) mysql> # 找出来男生平均薪资大于3000的职位 select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 8000; ===========================================order by排序 select * from emp order by salary; select * from emp order by salary desc; select * from emp order by age,id desc; ps:asc升序（默认为升序，一般就不要写了）
desc降序 select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000; mysql> select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16000.043333 | | teacher | 175650.051667 | | 老男孩驻沙河办事处外交大使 | 7300.330000 | +-----------------------------------------+---------------+ 3 rows in set (0.00 sec) mysql> select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000 order by avg(salary); +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | 老男孩驻沙河办事处外交大使 | 7300.330000 | | operation | 16000.043333 | | teacher | 175650.051667 | +-----------------------------------------+---------------+ 3 rows in set (0.00 sec) mysql> select post,avg(salary) as v from emp where sex="male" group by post having avg(salary) > 3000 order by v; +-----------------------------------------+---------------+ | post | v | +-----------------------------------------+---------------+ | 老男孩驻沙河办事处外交大使 | 7300.330000 | | operation | 16000.043333 | | teacher | 175650.051667 | +-----------------------------------------+---------------+ 3 rows in set (0.00 sec) mysql> 分页显示：（比如淘宝里商品页面显示，可以分好多页） select * from emp limit 0,5; select * from emp limit 5,5; select * from emp limit 10,5; select * from emp limit 15,5; select * from emp limit 20,5;