MySQL_高级查询&约束&多表！

1、DQL高级查询

排序查询：

语法：

select  字段名 from 表名 [where条件] order by 字段名 [ASC(升序：默认)|DESC(降序)]

聚合函数：

常用的SQL函数：
1、算数函数 2、字符串函数 3、日期函数 4、转换函数 5、聚合函数/聚集函数
函数SQL语法：
SELECT 聚合函数（列名） from 表名；

聚合函数计算中，排除NULL的方法：
选择不含非空的列进行计算
IFNULL函数
其他函数：

分组查询：

对一列数据进行分组，相同的内容分为一组，通常与聚合函数一起使用，完成统计工作

SELECT 字段 1,字段 2... FROM 表名 [where条件]
        GROUP BY 分组字段 [HAVING 条件] [order by];

分组之后查询的字段：分组字段，聚合函数
where和having的区别：
where：应用在分组前，操作的是原始表
having：应用在聚合函数后，操作的是结果集

分页查询：

LIMIT语法格式：
    LIMIT offset ， length；
  offset：起始行数
  length：返回的行数
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  -- 每页查询显示3条数据
    SELECT * FROM stu LIMIT 0,3; -- 第1页
    SELECT * FROM stu LIMIT 3,3; -- 第2页
    SELECT * FROM stu LIMIT 6,3; -- 第3页
SELECT * 字段列表 [as 别名] FROM 表名 [WHERE 子句] 
[GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT 子句];

2、约束

约束的分类：

主键约束：

限定某一列的值的非空且唯一，主键就是表中记录的唯一标识

-- 创建表时添加主键
create table stu(
        id int    primary key,    --给id添加主键约束
      name varchar(20)
);
1、非空且唯一
2、一张表只能有一个主键约束，但是我们可以设置联合主键（多个字段）
3、主键就是表中记录唯一的标识
-- 创建完表后添加主键
alter TABLE 表名 add primary key(id);
-- 创建完表后删除主键    
alter TABLE stu    modify id int ;     #先移除自增器
alter TABLE stu drop primary key; #删除主键约束
#因为只有主键才有意义设置自增器（保证唯一性）
#自增器：一张表中主键是int类型，每次添加数据时，主键自动加1 ：auto_increment加到

唯一约束：

限定某一列的值不能重复，可以出现多个NULL

create table 表名(
    列名 数据类型 unique,
    ...
    ...
);

非空约束：

限定某一列的值不能为NULL

create table 表名(
    列名 数据类型 not null,-- 非空约束
    列名 数据类型 unique not null-- （唯一+非空）
);
主键约束一张表只能有一个，唯一+非空 可以设置多个
所以：唯一+非空  不等于    主键

外键约束：

限定两张表有关系，保证数据的正确性，有效性和完整性
在实际开发中，一般不建立实际的外键关系，建立逻辑外键关系

create table 表名(
      列名 数据类型,
      [constraint] [约束名] foreign key(外键列) references 主表(主键)
);

默认值：

限定某一列的默认值，在没有指定的情况下，所有列的默认值为null

create table 表名(
    列名 数据类型 default 默认值,
    ...
    ...
);

3、表关系

一对一：

一个公民只有一个身份证号，一个身份证号对应一个公民

一对多：

一个班级有多个学生，一个部门有多个员工

多对多

一个教师教多个学生，一个学生被多个教师教
怎么说呢，这几个概念没啥记得，该懂就懂了

小练习：

 -- DQL高级查询
 -- 查看当前库的所有名称
SHOW DATABASES;
 -- 创建库
CREATE DATABASE gongsi;
-- 使用库
USE gongsi;
-- 创建表
CREATE TABLE yuangong (
    id INT PRIMARY KEY AUTO_INCREMENT,     #id，主键，自增
    `name` VARCHAR(20),    #姓名，
    age INT ,        #年龄
    sex VARCHAR(5),        #性别
    address VARCHAR(100),    #家庭住址
    math INT ,        #数学分数
    english INT         #英语分数
)
-- 插入数据
INSERT INTO yuangong(`name`,age,sex,address,math,english) 
    VALUES('马云',55,'男','杭州',34,76),
          ('马化腾',45,'男','深圳',98,89),
          ('马景涛',55,'男','香港',56,77),
        ('柳岩',20,'女','湖南',76,65),
        ('柳青',20,'男','湖南',86,NULL),
        ('刘德华',57,'男','香港',99,99),
        ('马德',22,'女','香港',99,99),
        ('德玛西亚',18,'男','南京',56,65),
        ('唐僧',25,'男','长安',87,78),
        ('孙悟空',18,'男','花果山',100,66),
        ('猪八戒',22,'男','高老庄',58,78),
        ('沙僧',50,'男','流沙河',77,88),
        ('白骨精',22,'女','白虎岭',66,66),
        ('蜘蛛精',23,'女','盘丝洞',88,88);
-- 查询        
SELECT * FROM yuangong;    
-- 排序查询
-- select 字段名 from 表名 【where条件】order by 字段名 【asc|desc】
-- 查询所有的字段，按照年龄降序
SELECT * FROM yuangong ORDER BY age DESC;        
-- 查询所有的字段，在年龄的降序基础上，按照数学成绩降序
SELECT * FROM yuangong ORDER BY age DESC,math DESC;
-- 查询所有的字段，按照成绩的和降序
SELECT id, `name`, math + IFNULL(english,0) AS sums FROM yuangong ORDER BY sums DESC;
-- 聚合函数查询
-- 查询表中的总记录数
SELECT COUNT(*) FROM yuangong; 
-- 查询表中数学成绩合格的记录数
SELECT COUNT(*) FROM yuangong WHERE math>=60;
-- 查询数学成绩的总分
SELECT SUM(math) FROM yuangong;
-- 查询数学成绩的平均分
SELECT AVG(math) FROM yuangong;
-- 查询数学成绩的最高分
SELECT MAX(math) FROM yuangong;
-- 查询数学成绩的最低分
SELECT MIN(math) FROM yuangong;
-- 分组查询
-- select 字段1,字段2...from 表名 [where条件] group by 分组字段 [having 条件][order by][esc |desc]
-- where操作原始表，having操作结果集
-- 查询男女共多少人
SELECT sex , COUNT(*) FROM yuangong GROUP BY sex;
-- 查询年龄大于18的，按性别分组，统计个数
SELECT sex ,COUNT(*) FROM yuangong WHERE age>18 GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,
-- 并只显示性别人数大于2的数据
SELECT sex , COUNT(*) AS renshu FROM yuangong WHERE age>25 GROUP BY sex HAVING renshu>2;
-- 分页查询
-- select * 字段列表[as 别名]from 表名[where 子句]
-- [group by 句子][having 子句][order by 子句][limit 子句];
-- limit 起始行数[(当前页-1)*每页显示的个数]，每页显示的个数;
SELECT * FROM yuangong LIMIT 0,3;
SELECT * FROM yuangong LIMIT 3,3;
SELECT * FROM yuangong LIMIT 6,3;
-- 查询名字，年龄，家庭住址，英语成绩，且家庭住址在香港，年龄大于18，英语成绩大于65；
SELECT `name` AS '名字' , age AS '年龄' ,address AS '家庭住址' ,english AS '英语成绩' FROM yuangong WHERE address = "香港" HAVING age>18 AND english>65;
-- 约束
-- 创建表，添加约束
CREATE TABLE wai(
    id INT PRIMARY KEY ,     #主键约束
    `name` VARCHAR(20) UNIQUE  NOT NULL #唯一约束 非空约束
)    
CREATE TABLE stu(
    id INT PRIMARY KEY ,     #主键约束
    `name` VARCHAR(20)  UNIQUE  NOT NULL,    #唯一约束 非空约束
    age INT UNIQUE,     #非空约束
    sex VARCHAR(5) DEFAULT '男',  #默认值
    #列名 数据类型,     foreign key(外键列) references 主表(主键)
    wai_id INT FOREIGN  KEY (wai_id) REFERENCES wai (id);
)