MySQL查询与约束

1. 数据库介绍
    数据库管理系统--->MySQL应用软件-->仓库-->表-->记录（数据）
    实体和表关系
        一个实体对应一张表
        一个对象的 实例 对应一条记录
2. 数据库安装和使用
    登录：mysql -u用户名 -p密码
    退出：quit/exit
3. SQL语句
    结构化查询语言，操作数据库【CRUD】
    DDL：操作数据库和表
    DML：操作数据的增删改
    DQL：操作数据库查询
    -------------------------------------------
    DCL：操作用户和权限
    TCL：操作事务安全
    create database web01;
    use web01;
    create table student(
        id int,
        name varchar(32),
        birthday date
    desc student;
    insert into student values(1,'jack','1999-1-1');
    update student set birthday='2000-1-1' where id=1;
    delete from student where id=1;
    select distinct id 学号,name 姓名,birthday 生日 from student;
        null值与其他值参与运算都为null，ifnull()高级函数处理

0. 演示sqlyog使用
1. DQL单表高级查询【重点】
    条件、排序、聚合函数、分组、分页
2. 数据库约束【理解】
3. 表关系【重点】
    一对多（多对一）、多对多、一对一
4. 了解数据库备份与还原

-- DQL语句 单表查询
-- 创建表
CREATE TABLE student (
  id int,
  name varchar(20),
  age int,
  sex varchar(5),
  address varchar(100),
  math int,
  english int
-- 插入记录
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES 
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);

1. 语法：
        select ... from 表名 where 条件;
2. 关系（比较）运算符
        > < >= <= != =
3. 逻辑运算符
    &&  == and（条件同时成立）
    ||  == or （条件满足一个）
    ！  == not （条件取反）
4. in关键字（集合）
        select ... from 表名 where 列名 in(值1,值2,值3....);
5. between关键字（范围查询）
        select ... from 表名 where 列名 between 较小的值 and 较大的值;
        包头(>=)包围(<=);
6. (is null) (is not null)关键字 对空值的处理
7. like关键字（模糊查询）
        select ... from 表名 where 列名 like '通配符字符串';
        _ 单个任意字符
        % 多个任意字符
# 关系运算符
-- 查询math分数大于80分的学生
SELECT * FROM student WHERE math > 80;
-- 查询english分数小于或等于80分的学生
SELECT * FROM student WHERE english <= 80;
-- 查询age等于20岁的学生
SELECT * FROM student WHERE age = 20;
-- 查询age不等于20岁的学生
SELECT * FROM student WHERE age != 20;
# 逻辑运算符
-- 查询age大于35 且 性别为男的学生(两个条件同时满足)
SELECT * FROM student WHERE age > 35 AND sex = '男';
-- 查询age大于35 或 性别为男的学生(两个条件其中一个满足)
SELECT * FROM student WHERE age >35 OR sex='男';
-- 查询id是1或3或5的学生
SELECT * FROM student WHERE id = 1 OR id = 3 OR id = 5;
-- in关键字
-- 再次查询id是1或3或5的学生
SELECT * FROM student WHERE id IN(1,3,5);
-- 查询id 不是 1或3或5的学生
SELECT * FROM student WHERE id NOT IN(1,3,5);
-- 查询english成绩大于等于77，且小于等于87的学生
SELECT * FROM student WHERE english >= 77 AND english <=87;
SELECT * FROM student WHERE english BETWEEN 77 AND 87;
-- 查询英语成绩为null的学生
SELECT * FROM student WHERE english = NULL; -- 错误 六亲不认
SELECT * FROM student WHERE english IS NULL;
SELECT * FROM student WHERE english IS NOT NULL;-- 查询非空
# like模糊匹配
-- 查询姓马的学生
SELECT * FROM student WHERE `name` LIKE '马%';
-- 查询姓名中包含'德'字的学生
SELECT * FROM student WHERE `name` LIKE '%德%';
-- 查询姓马，且姓名有三个字的学生
SELECT * FROM student WHERE `name` LIKE '马__';

1. 语法：
        select ... from 表名 order by 指定排序列 [asc|desc],指定排序列 [asc|desc];
            asc 升序，默认值
            desc 降序
2. 注意：
        多字段排序，后面的字段是在前面字段排序的基础之上排序

1. 语法
    count(列名)：统计个数 count(*) 统计行
    max(列名)：最大值
    min(列名)：最小值
    avg(列名)：平均值
    sum(列名)：求和
# 聚合函数
-- 查询学生总数（null值处理）
SELECT COUNT(id) FROM student;
SELECT COUNT(english) FROM student;
SELECT COUNT(*) FROM student; -- 根据数据库行记录进行统计，包含null
-- 查询年龄大于40的总数
SELECT COUNT(*) FROM student WHERE age > 40;
-- 查询数学成绩总分
SELECT SUM(math) FROM student;
-- 查询数学成绩平均分
SELECT AVG(math) FROM student;
-- 查询数学成绩最高分
SELECT MAX(math) FROM student;
-- 查询数学成绩最低分
SELECT MIN(math) FROM student;

1. 语法：
        select 分组字段 from 表名 group by 分组字段 having 分组后条件筛选;
2. 区别
    where在分组前进行条件筛选，不能使用聚合函数
    having在分组后进行条件筛选，可以使用聚合函数
-- 男生有多少人，女神有多少人...
SELECT COUNT(*) FROM student WHERE sex = '男';
SELECT COUNT(*) FROM student WHERE sex = '女';
-- 按性别分组
SELECT sex FROM student GROUP BY sex;
-- 查询男女各多少人
SELECT sex,COUNT(sex) FROM student GROUP BY sex;
-- 作业 根据地区分组，统计人数
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
SELECT * FROM student WHERE age > 25;
SELECT sex FROM student WHERE age > 25 GROUP BY sex;
SELECT sex,COUNT(sex) FROM student WHERE age > 25 GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
SELECT sex,COUNT(sex) FROM student WHERE age > 25 AND COUNT(sex)>2 GROUP BY sex; -- 错误
SELECT sex, COUNT(SEX) FROM student WHERE age > 25 GROUP BY sex HAVING COUNT(SEX)>2; -- 正确

select ... from 表名 where 条件 group by 分组 having 条件 order by 排序 limit 分页

# 分页
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES 
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
1. 语法：
        select ... from 表名 limit 开始索引，每页显示个数。
2. 注意：
        索引是从0开始，默认值也为0
3. 索引公式
        索引= （当前页-1）× 每页个数
# 分页
-- 查询学生表中数据，从第三条（2）开始显示，显示6条
SELECT * FROM student LIMIT 2,6;
-- 查询学生表中数据，显示前6条
SELECT * FROM student LIMIT 0,6;
SELECT * FROM student LIMIT 6;
-- 模拟百度分页，一页显示5条
-- 第一页
SELECT * FROM student LIMIT 0,5;
-- 第二页
SELECT * FROM student LIMIT 5,5;
-- 第三页
SELECT * FROM student LIMIT 10,5;
-- 公式（索引）
-- 索引= （当前页-1）× 每页个数

1. 主键约束：primary key 主键数据 唯一 且 非空，通常使用id作为主键
2. 唯一约束：unique
3. 非空约束：not null
4. 默认值约束：default
5. 外键约束：foreign key

1. 添加主键
    1）创建表
        create table 表名(
            id int primary key,
    2）已有表
        alter table 表名 add primary key(id);
        一张表只能有一个主键，但是可以设置联合主键（包含多个字段）
2. 自增器
    1）创建表
        create table 表名(
            id int primary key auto_increment,
    2）默认起始值为1
    3）修改自增器起始值
        alter table 表名 auto_increment=10000;
3. 删除主键
    需要先删除自增器
        alter table 表名 modify id int;
        alter table 表名 drop primary key;
-- 主键约束
-- 修改表
ALTER TABLE student ADD PRIMARY KEY(id);
-- 创建表
CREATE TABLE stu1(
 id INT PRIMARY KEY,
 `name` VARCHAR(32)
INSERT INTO stu1 VALUES(1,'jack');
-- Duplicate entry '1' for key 'PRIMARY' id重复
INSERT INTO stu1 VALUES(1,'tom');
-- Column 'id' cannot be null id不能为空
INSERT INTO stu1 VALUES(NULL,'tom');
-- 再给name字段设置为主键
-- Multiple primary key defined
ALTER TABLE stu1 ADD PRIMARY KEY(NAME);
-- 联合（组合）主键
CREATE TABLE stu2(
  id INT,
  `name` VARCHAR(32),
  PRIMARY KEY(id,`name`)
INSERT INTO stu2 VALUES(1,'jack');
INSERT INTO stu2 VALUES(1,'tom');
-- Duplicate entry '1-tom' for key 'PRIMARY'
INSERT INTO stu2 VALUES(1,'tom');
-- 自增器
CREATE TABLE stu3(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(32)
INSERT INTO stu3 VALUES(1,'jack');
INSERT INTO stu3 VALUES(NULL,'jack');
-- 修改起始值
ALTER TABLE stu3 AUTO_INCREMENT=10000;
INSERT INTO stu3 VALUES(NULL,'jack');
-- delete 橡皮擦
DELETE FROM stu3;
INSERT INTO stu3 VALUES(NULL,'jack');
-- truncate 摧毁表
TRUNCATE TABLE stu3;
INSERT INTO stu3 VALUES(NULL,'jack');
-- 删除自增器
ALTER TABLE stu3 MODIFY id INT;
-- 删除主键
ALTER TABLE stu3 DROP PRIMARY KEY;

1. 添加唯一约束
    1）创建表
        create table 表名(
            列名 数据类型 unique,
    2）特点：
        可以出现null值
-- 唯一约束
CREATE TABLE stu4(
 id INT,
 `name` VARCHAR(32) UNIQUE
INSERT INTO stu4 VALUES(1,'jack');
-- Duplicate entry 'jack' for key 'name' name重复
INSERT INTO stu4 VALUES(2,'jack');
INSERT INTO stu4 VALUES(2,NULL);
INSERT INTO stu4 VALUES(3,NULL);

1. 添加非空约束
    1）创建表
        create table 表名(
            列名 数据类型 not null,
-- 唯一组合非空
CREATE TABLE stu5(
  id INT,
  `name` VARCHAR(32) UNIQUE NOT NULL
INSERT INTO stu5 VALUES(1,'jack');
-- Column 'name' cannot be null name不能为空
INSERT INTO stu5 VALUES(2,NULL);

1. 添加默认值约束
    1）创建表
        create table 表名(
            列名 数据类型 default 指定默认值,
-- 默认值约束
CREATE TABLE stu6(
    id INT,
    `name` VARCHAR(32),
    sex VARCHAR(1) DEFAULT '男'
INSERT INTO stu6(id,NAME) VALUES(1,'华华');
INSERT INTO stu6(id,NAME,sex) VALUES(2,'丽丽','女');
INSERT INTO stu6 VALUES(3,'小李帅',NULL);

1. 一对多
    应用场景：
        班级和学生、公司和部门、部门和员工...
        一个班级下方有多名同学，多名同学属于某一个班级
2. 多对多
    应用场景：
        学生和课程、老师和学生
        一名同学可以选修多门课程，一门课程可以被多名同学选修
3. 一对一
    应用场景：
        合法公民和身份号、公司和注册地
        一个公民只能有一个身份证号，一个身份证号只属于一个公民

* 举例：班级和学生

-- 表关系
CREATE DATABASE web02_1;
USE web02_1;
-- 一对多
-- 班级表（主表）
CREATE TABLE class(
 id INT PRIMARY KEY AUTO_INCREMENT,
 `name` VARCHAR(32)
INSERT INTO class VALUES(1,'javaEE109期');
INSERT INTO class VALUES(2,'javaEE110期');
-- 学生表（从表）
CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT,
 `name` VARCHAR(32),
 class_id INT -- 外键字段
INSERT INTO student VALUES(1,'小华',1);
INSERT INTO student VALUES(2,'大伟',1);
INSERT INTO student VALUES(3,'苍苍',2);
INSERT INTO student VALUES(4,'宝柱',2);
-- 查询 109期
SELECT * FROM student WHERE class_id =1;

* 举例：学生和课程

-- 多对多
-- 课程表
CREATE TABLE course(
 id INT PRIMARY KEY AUTO_INCREMENT,
 `name` VARCHAR(32)
INSERT INTO course VALUES(1,'美容美发');
INSERT INTO course VALUES(2,'挖掘机');
INSERT INTO course VALUES(3,'java');
INSERT INTO course VALUES(4,'厨师');
-- 中间表（从表）
CREATE TABLE sc(
    s_id INT,
    c_id INT,
    PRIMARY KEY(s_id,c_id),
    FOREIGN KEY(s_id) REFERENCES student(id),
    FOREIGN KEY(c_id) REFERENCES course(id)
-- 小华
INSERT INTO sc VALUES(1,1);
INSERT INTO sc VALUES(1,3);
-- 大伟
INSERT INTO sc VALUES(2,2);
INSERT INTO sc VALUES(2,3);
INSERT INTO sc VALUES(2,5); -- 错误

* 举例：公司和注册地

-- 一对一
-- 公司表
CREATE TABLE company (
 id INT PRIMARY KEY AUTO_INCREMENT,
 `name` VARCHAR(32)
INSERT INTO company VALUES(1,'拼夕夕');
INSERT INTO company VALUES(2,'快播');
-- 注册地
CREATE TABLE address(
 id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(32),
  FOREIGN KEY(id) REFERENCES company(id) -- 主键是外键
INSERT INTO address VALUES(1,'上海');
INSERT INTO address VALUES(2,'深圳');

1. 在从表的外键字段添加约束
    1）创建表
        create table 表名(
            外键字段 数据类型,
            [constraint] [约束名] foreign key(外键字段) references 主表(id)
    2）已有表
        alter table 表名 add [constraint] [约束名] foreign key(外键字段) references 主表(id);
2. 特征
    1）主表不能删除从表已引用的数据
    2）从表不能添加主表未拥有的数据
    3）先添加主表数据再添加从表数据
    4）先删除从表数据再删除主表数据
3. 删除外键约束
        alter table 表名 drop foreign key 约束名；

1. 分类（主表）和线路（从表），一对多
        category(id,name);
        route(id,name,c_id);
2. 用户（主表）和线路（主表）,多对多
        user(id,name);
        route(id,name,c_id); -- 在上个案例中已创建，直接使用