Mysql约束条件

not null 和 default

# 1 create table excel1(x int not null); insert into excel1 values(): ----------------------------------- >>> Field 'x' doesn't have a default value # 2 create table excel2(x int not null default 111); # 不为空且默认值为111 insert into excel2 values(); # 传入空值 desc excel2; ------------------------------------------ +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | # null 不为空 default 默认111 +-------+---------+------+-----+---------+-------+ | x | int(11) | NO | | 111 | | +-------+---------+------+-----+---------+-------+

unique

# 单列唯一 create table excel3(name varchar(10) unique, birthday date); insert into excel3 values("egon", 20000820); insert into excel3 values("egon", 20000830); ------------------------------------------------- Duplicate entry 'egon' for key 'name' # 联合唯一 create table excel4(id int, name varchar(10), ip varchar(15),port int, unique(ip,port)); # ip和port都唯一 insert into excel4 values(1, "gg", "127.0.0.1", 8080); insert into excel4 values(2,"hh", "127.0.0.1",8090); insert into excel4 values(3, "jj", "127.0.0.1", 8090); -------------------------------------------------- Duplicate entry '127.0.0.1-8090' for key 'ip' # not null 和 unique的化学反应=》会被识别为表的主键 create table excel5(id int,name varchar(10) not null unique); create table excel6(id int,name varchar(10) unique); desc excel5; desc excel6; ------------------------------------------------------------ +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | # excel5 +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | NO | PRI | NULL | | # name 设置为了主键 pri +-------+-------------+------+-----+---------+-------+ +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | # excel6 +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | YES | UNI | NULL | | # name 唯一 +-------+-------------+------+-----+---------+-------+

关于主键

无特殊需求下 Innodb 建议使用与业务无关的自增 ID 作为主键

主键(Primary Key)是唯一标识表中每行的列或一组列。当定义表的主键时，必须遵循以下规则：

主键必须包含唯一值。如果主键由多个列组成，则这些列中的值的组合必须是唯一的。主键列不能包含NULL值。 这意味着必须使用NOT NULL属性声明主键列。如果没有指定NOT NULL，MySQL将强制为主键列为NOT NULL。一张表只有一个主键，可以是联合主键（多个联合在一起）

主键列通常具有自动生成键的唯一序列的AUTO_INCREMENT属性。下一行的主键值大于前一个行的主键值。

# 主键 create table excel7(id int primary key auto_increment, name varchar(5)); # 以id为 自增长 并且为主键 # 联合主键（了解） create table excle8(id int, name varchar(5) primary key(id name));

表与表之间的关系

一对一

这种关系即多个表具有相同的主键，实际中用的并不多，因为完全可以将这种关系的合并为同一张表。

一对多

学生表

idnamegendercourse1米老鼠male铁人三项2唐老鸭male铁人三项3高飞male铁人三项4丽莎femal健美操

我们发现在迪士尼角色所上的课程中，有几个角色上的课程是一样的，那我们试着将他们分开（创建一个新的表），实现在物理层面的解耦合。

课程表

idcourse_namecourse_teacher1铁人三项黑皇后2铁人三项一个小矮人3健美操白雪公主

我用不同id来分开 每个课程和授课的老师，这样学生表中我们就只需要 引用序号 就知道这个学生学的课程和对应的老师了

新的学生表

idnamegendercourse_id1米老鼠male12唐老鸭male23高飞male14丽莎femal3

这样我们就实现了 逻辑层面的建立联系，物理层面相互独立 解耦合

在新的学生表中，由于我们需要引用课程表（被关联表）的内容，所以被关联表是要先于关联表创建的

# 先创建被关联表 create table course(id int primary key auto_increment,name varchar(5),teacher varchar(5)); insert into course(name, teacher) values("铁人三项","黑皇后"),("铁人三项","一个小矮人"),("健美操","白雪公主"); select * from course; --------------------------------------------- +----+----------+------------+ | id | name | teacher | +----+----------+------------+ | 1 | 铁人三项 | 黑皇后 | | 2 | 铁人三项 | 一个小矮人 | | 3 | 健美操 | 白雪公主 | +----+----------+------------+ # 再创建关联表 create table student(id int primary key auto_increment,name varchar(5), gender varchar(5), course_id int, foreign key(course_id) references course(id) on delete cascade on update cascade); insert into student(name, gender, course_id) values("米老鼠", "male", 1),("唐老鸭", "male", 2),("高飞", "male", 1),("丽莎", "female", 3); select * from student; ---------------------------------------------- +----+--------+--------+-----------+ | id | name | gender | course_id | +----+--------+--------+-----------+ | 1 | 米老鼠 | male | 1 | | 2 | 唐老鸭 | male | 2 | | 3 | 高飞 | male | 1 | | 4 | 丽莎 | female | 3 | +----+--------+--------+-----------+

多对多

其实就是建一个中间表，用于存放两个表的对应关系

student

idname1egon2alex3jack4rose

course

idname1python2linux3go4php

这里有两张表，他们之间的关系比较复杂，一门课程可以有多个学生，然后一个学生也可以选择多门课程，所以他们之间是相互引用的，但是这个就与我们被关联表先创建的原则相违背，所以我们可以用第三张表给他们建立关系。

connect

idstudent_idcourse_id111212313421523634

学生1 选了三门课 学生2 选了2门，学生3选了一门，课程一有2名学生，课程三有2名，其余各有一名

# student create table student(id int primary key auto_increment, name varchar(10)); # course create table course(id int primary key auto_increment, name varchar(10)); # connect create table connect(id primary key auto_increment,student_id int, course_id int, forign key(student_id) references student(id) on delete cascade on update cascade, forign key(course_id) references course(id) on delete cascade on update cascade);

表记录相关操作

单表查询语法

select 字段1,字段2,字段3… from 表名 where 限制条件 group by 分组的条件 having 筛选条件 order by 排序条件 limit n；

优先级

fromwheregroup byhavingorder bylimit n

他们之间的关系是层层递进的，只有完成上一级的操作，才能进行下一级的操作

1.找到表:from 2.拿着where指定的约束条件，去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 4.将分组的结果进行having过滤 5.执行select 6.去重 7.将结果按条件排序：order by 8.限制结果的显示条数

where 约束

比较运算符：> < >= <= <> !=between 80 and 100 值在10到20之间in(80,90,100) 值是10或20或30like ‘egon%’ pattern可以是%或_， %表示任意多字符 _表示一个字符逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

group by 分组查询

什么是分组？为什么要分组？

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

#3、为何要分组呢？ 取每个部门的最高工资 取每个部门的员工数 取男人数和女人数

小窍门：‘每’这个字后面的字段，就是我们分组的依据

#4、大前提： 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数 分完组之后只能看到分组的字段以及聚合的结果

单独使用GROUP BY关键字分组 SELECT post FROM employee GROUP BY post; 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数 GROUP BY关键字和GROUP_CONCAT()函数一起使用 SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名 SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post; GROUP BY与聚合函数一起使用 select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人 12345678910

强调: 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义 多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

聚合函数:

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 示例： SELECT COUNT(*) FROM employee; SELECT COUNT(*) FROM employee WHERE depart_id=1; SELECT MAX(salary) FROM employee; SELECT MIN(salary) FROM employee; SELECT AVG(salary) FROM employee; SELECT SUM(salary) FROM employee; SELECT SUM(salary) FROM employee WHERE depart_id=3; 12345678910

验证:

分完组之后只能看到分组的字段以及聚合的结果 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; 1234567891011121314151617181920

having过滤

having与where的区别

#！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数 1234

验证

# 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) # 找出来男生平均薪资大于3000的职位 select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 8000; 1234567891011121314151617

order by 排序

select * from emp order by salary; # 默认升序排序 select * from emp order by salary desc; # 假如desc关键词变降序 select * from emp order by age,id desc; # 联合排序 当age 排序结果不明显时候，可以再附加一个 id降序 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) 1234567891011121314151617181920212223242526272829303132333435

limit限制查询的记录数

分页显示不推荐使用limit,因为效率比较低（比如说limit 200，5，那他是先从1找到200然后再给我们返回五条，在数据量特别大的情况下更加的慢）

小练习:分页显示,每页5条

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; 第一个参数是起始位置 第二个参数是展示条数