MySQL的初步理解2

约束

约束：对数据表中数据进行限制的一种设置。

约束是只对某一列进行约束。

约束的统一写法

create table 表名(
	字段1 数据类型(长度) 约束,
    字段2 数据类型(长度) 约束,
    ...
);

主键约束(primary key):

最重要的约束，一般一个表都至少要有一个主键。

主键约束的设置

create table emp(
	id int primary key,
    name varchar(20),
    sal double(8,2)
);

主键的作用

-- 1.不能为空
insert into emp(id, name) values('张三');
	-- 主键为空报错
	-- Field 'id' doesn't have a default value;
	
-- 2.不能重复
insert into emp(id, name) values(1,'张三');
insert into emp(id, name) values(1,'张三');
-- 主键重复报错
-- Duplicate entry '1' for key 'PRIMARY'

联合主键

即多个列组合为一个主键。

	create table emp2(
		id int,
		name varchar(20),
		hiredate date,
		sal double(10,2),
        primary key(id,name)   -- 设计id和name为联合主键
  	);
-- 可以
insert into emp2 (id,name) values(1,'张三丰')
-- 只是id重复,可以
insert into emp2 (id,name) values(1,'张四丰')
-- 只是name重复,可以
insert into emp2 (id,name) values(2,'张三丰')
-- id和name都重复,不行
insert into emp2 (id,name) values(1,'张三丰')

自增约束(auto_increment)：

自增约束一般配合主键约束一起使用，即主键自增。

语法：在主键约束后加auto_increment。

create table emp3(
	id int primary key auto_increment,
    name varchar(20),
    sal double(8,2)
);

自增约束：

1、当没有值时，直接自增，从1开始。

2、依次递增1。

3、虽然设置了递增，但也可以自己在随意设置主键值，但是后续自增会按照自己设置的主键值继续递增。

4、执行delete删除数据时，不会影响递增顺序。

非空约束(not null)：

限制该列的值不能为空。

create table emp4(
		id int primary key auto_increment, -- 主键自增
		name varchar(20) not null, -- 不为空
		hiredate date,
		sal double(10,2)
  	);
  	-- 限制name列插入值时,不能为空

默认值(default)：

当给该列设置默认值时，没有给该列赋值，那么就会使用默认值。

create table emp5(
		id int primary key auto_increment, -- 主键自增
		name varchar(20) not null, -- 不为空
		hiredate date,
		sal double(10,2) default 3000.0  -- 默认值
  	);
  	-- 没有值时就会使用默认值填充,有值是不用默认值

唯一约束(unique)：

限制该列的值不能重复，但是可以为空

	create table emp6(
		id int primary key auto_increment, -- 主键自增
		name varchar(20) unique, -- 唯一
		hiredate date,
		sal double(10,2) default 3000.0  -- 默认值
  	);
  
-- 该值不能重复 	
insert into emp6 (name) values('李白');

-- 但是可以给null,且允许多个null
insert into emp6 (name) values(null);
insert into emp6 (name) values(null);

外键约束:

外键约束又叫引用约束，外键约束是使用在表与表之间的约束。

数据准备

-- 学生表
CREATE TABLE `stu3` (
  `sid` int(11) primary key,
  `sname` varchar(20) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL -- 关联老师id
);
-- 老师表
CREATE TABLE `teacher` (
  `tid` int(11) primary key,
  `tname` varchar(20) DEFAULT NULL
) ;

添加外键

注意：

外键必须是主键！！！

1、建表示添加外键

-- 学生表
CREATE TABLE `stu3` (
  `sid` int(11) DEFAULT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL, -- 关联老师id
   constraint fk_stu_tea foreign key(tid) references teacher(tid)
);

2、使用alter命令修改表，添加外键

alter table stu3 add constraint fk_stu_tea foreign key(tid) references teacher(tid);

3、使用图形化界面设置外键(Navicat Premium 15)

外键的作用

添加了外键后，教师表teacher是被引用的表，学生表stu是引用表，即teacher表是父表，stu表是子表。

加上外键后，在用法上也有了限制。

1、子表不能乱插入和修改数据，即不能插入或更新被引用表中没有的数据。

2、父表上的限制

当父表数据被子表引用时，父表就不能删除或更新。

当我们使用图形化界面设置主键时会发现对于主键我们可以设置一些属性

删除时/更新时:
RESTRICT：如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；
NO ACTION：同 RESTRICT，也是首先先检查外键；
CASCADE：父表delete、update的时候，子表会delete、update掉关联记录；
SET NULL：父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null；

DQL

查询就是从数据库查询出结果，查询数据不会对原数据有任何影响。

查询的完整语句

select ...     -- 要查询的列名
from  ...	-- 查询的表
where ...	-- 限制条件
group by ...	-- 对结果分组
having ...   -- 分组后的限制条件
order by ...  -- 对结果排序(asc升序 | desc降序)
limit ...    --结果限定(分页)

数据准备

CREATE TABLE `stu` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(25) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(6) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `groupLeaderId` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=1011 DEFAULT CHARSET=utf8;

基本查询：

语法格式

select 字段1,字段2,... 
from 表名;

案例

-- 查询所有列
select sid, sname, age,sex,score,cid,groupLeaderId 
from stu;

-- 查询结果是一张虚标，并不是真实存在的

-- 查询所有列，一般还可以写为
select *
from stu;

-- 按目的查询
select sid sname,age
from stu;

条件查询：

条件查询：将查询结果过滤一部分，只保留满足要求的数据。

语法格式

select 字段1,字段2,...
from 表名
where 条件;

限制条件可以使多种的，一般有：

1、 =、!=、<>(不等)、<、>、=> 、<=

2、between…and… ; 在某个区间

3、in(set);

4、is null;（结果为空） is not null; （结果不为空）

5、and; （和）

6、or;（或）

7、not;（非）

案例：

-- 查询学号为1001的学生信息
select * 
from stu 
where sid = 1001;

-- 查询学生成绩大于60的学生id 姓名,成绩
select sid,sname,score 
from stu 
where score > 60;

-- 查询学生性别为女，并且年龄小于50的记录
select * 
from stu 
where sex = '女' and age < 50;

-- 查询学生学号为1001，或者姓名为李四的记录
select * 
from stu 
where sid = 1001 or sname = '李四';

-- 查询学号为1001，1002，1004的记录
select * 
from stu 
where sid in (1001,1002,1003);

select * 
from stu 
where sid = 1001 or sid = 1002 or sid = 1003;

-- 查询学号不是1001,1002,1003的记录
select * 
from stu 
where sid not in (1001,1002,1003);

select * 
from stu 
where sid != 1001 and sid != 1002 and sid != 1003;

-- 查询学生年龄在20到40之间的学生记录
-- between..and是闭区间,即包含两端数据
select * 
from stu 
where age 
between 20 and 40;

select * 
from stu 
where age >= 20 and age <=40;

-- 查询性别非男的学生记录
select * 
from stu 
where sex != '男';

select * 
from stu
where sex <> '男';

-- 查询学生性别为空的数据
-- null不能用等号判断
select * 
from stu 
where sex = null; -- 不对!!

select * 
from stu 
where sex is null; 

select * 
from stu 
where sex is not  null; --注意是is not!!不是not is!!!

模糊查询：

模糊查询也是条件查询。使用关键字like

语法格式

where 字段 like '%关键字%';
或
where 字段 like '_关键字-'

模糊查询：

%：匹配任意个数的任意字符

_：匹匹配一个任意字符

案例：

-- 查询姓名以“张”开头的学生记录
select *
from stu
where sname like '张%'

-- 查询姓名中包含“三”的学生记录
select *
from stu
where sname like '%三%'

排序查询：

排序查询：对查询的结果按照某种顺序排序。

关键字asc(升序) 和desc(降序)

语法格式

order by 字段 asc|desc

案例：

-- 查询所有学生记录，按年龄升序排序(asc)
select *
from stu
order by age asc

-- 查询所有学生记录，按年龄降序排序(desc)
select *
from stu
order by age desc

-- 查询所有学生记录，按年龄升序排序,如果年龄相同时，按编号降序排序
select *
from stu
order by age asc, sid DESC   --先执行第一个排序，如果不满足第一个排序，再进行第二个排序

-- 查询成绩大于60的学生id,姓名,成绩,并根据成绩降序
select sid,sname,score
from stu
where score > 60
order by score desc

聚合函数：

对查询的结果进行计算的一种函数，聚合函数是将多行数据计算返回一个结果的函数。即，有聚合函数的sql语句，查询返回的结果只有一条记录。

常用的聚合函数：

• COUNT(字段)：统计指定列不为NULL的记录行数；

• MAX(字段)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

• MIN(字段)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

• SUM(字段)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

• AVG(字段)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

案例：

-- 查询stu表中记录数：
select COUNT(*)
from stu

-- 查询stu表中有成绩的人数：
select COUNT(score)
from stu

-- 查询stu表中成绩大于60的人数：
select COUNT(*)
from stu
where score > 60

-- 查询所有学生成绩和：
select SUM(score)
from stu

-- 统计所有学生平均成绩
select AVG(score)
from stu

-- 查询最高成绩和最低成绩：
select MAX(score) as '最大值', MIN(score) '最小值'
from stu

对于一些名比较长的字段，我们可以采用取别名的方法来化简sql语句。

如：

select MAX(score) as '最大值', MIN(score) '最小值'
from stu

我们也可以吧as省略不写。

去重：

对返回的结果去重。

语法格式

select distinct 字段
from 表名;

案例:

select DISTINCT age
from stu 
-- 常见的去重操作
select COUNT(DISTINCT age) '不同年龄的个数'
from stu

分组查询：

语法格式

group by 字段

根据该字段分组，将字段相同的分为一组。

特点:
1) 凡是分组查询,分为几组,结果只有几行
2) 聚合函数是对每组内的数据进行聚合
3) 出现在groupby后的字段,才可以出现在select后
凡是和聚合函数出现在一起的字段,必须出现在group by后

分组之后的过滤使用having

where和having的区别

1、where是分组前过滤，having是分组后过滤。

2、where后的限制条件时不能使用聚合函数的，而having后的限 制条件可以使用聚合函数。

案例：

-- 查询男生多少人,女生多少人
SELECT COUNT(sid)
FROM stu
GROUP BY sex

-- 查询每个班级的班级编号和每个班级的成绩和：
SELECT cid '班级编号',SUM(score) '班级成绩和' 
FROM stu
GROUP BY cid

-- 查询每个班级的班级编号以及每个班级的人数：
SELECT cid '班级编号',COUNT(sid) '每班人数'
FROM stu
GROUP BY cid

-- 查询成绩总和大于200的班级编号以及成绩和：
-- 在分组查询中的限制条件，需要使用having,并且要放在group by的后边
SELECT cid,SUM(score)
FROM stu
GROUP BY cid
HAVING SUM(score) > 200

分页：

使用关键字limit

语法格式

limit x,y;
-- x：与数组下标类似，表示从某个位置开始，
-- y：表示偏移量，即从x位置开始取y个数据。
-- 一般来说，偏移量是不发生变化的，变化的只有x的位置。

案例：

-- 获取第6-10条的所有学生信息
select *
from stu
limit 5,5;   -- 由于位置是从0开始的，所以要减1.

关于查询关键词的顺序问题

我们对于查询语句关键词的书写顺序为：

select
from
where
group by
having
order by
limit

查询语句的执行顺序为：

from -- 需要从哪个数据表检索数据
where -- 需要从哪个数据表检索数据
group by -- 如何将上面过滤出的数据分组
having -- 对上面已经分组的数据进行过滤的条件
select -- 查看结果集中的哪个列，或列的计算结果
order by -- 按照什么样的顺序来查看返回的数据

的后边
SELECT cid,SUM(score)
FROM stu
GROUP BY cid
HAVING SUM(score) > 200

### 分页：

使用关键字`limit`

**语法格式**

```sql
limit x,y;
-- x：与数组下标类似，表示从某个位置开始，
-- y：表示偏移量，即从x位置开始取y个数据。
-- 一般来说，偏移量是不发生变化的，变化的只有x的位置。

案例：

-- 获取第6-10条的所有学生信息
select *
from stu
limit 5,5;   -- 由于位置是从0开始的，所以要减1.

关于查询关键词的顺序问题

我们对于查询语句关键词的书写顺序为：

select
from
where
group by
having
order by
limit

查询语句的执行顺序为：

from -- 需要从哪个数据表检索数据
where -- 需要从哪个数据表检索数据
group by -- 如何将上面过滤出的数据分组
having -- 对上面已经分组的数据进行过滤的条件
select -- 查看结果集中的哪个列，或列的计算结果
order by -- 按照什么样的顺序来查看返回的数据