MySQL数据库（四）

01. 条件查询-比较运算符

/*
    条件查询
    条件查询可以查询出满足条件要求的数据。
    条件查询要结合运算符去使用。
    运算符用作条件筛选。
    比较运算符
        >：大于
        >=：大于等于
        <： 小于
        <=： 小于等于
        =： 等于
        !=：不等于
        <>： 不等于
    条件查询的格式
        select * from 表名 where 条件;
*/
# 查询math分数大于80分的学生的所有信息
SELECT * FROM student WHERE math > 80;
​
# 查询math分数大于80分的学生的姓名以及年龄
SELECT `name`, age 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;
SELECT * FROM student WHERE age <> 20;

02. 条件查询-逻辑运算符

/*
    逻辑运算符
    and： 与， 相当于java中的&&。 
    or：　或， 相当于java中的||
    not： 非， 相当于java中的!
    in(...)：包含。　只要包含其中之一就算满足条件。　比如 id in(1,3,5) 表示只要id包含1,3,5其中之一即可。
*/
# 查询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);

03. 条件查询-范围查询

/*
    范围查询 between ... and ...
    比如： between 3 and 10， 表示数据在3到10之间。并且包含3也包含10.
*/
# 查询english成绩大于等于75，且小于等于90的学生
SELECT * FROM student WHERE english >= 75 AND english <= 90;
SELECT * FROM student WHERE english BETWEEN 75 AND 90; -- 在75和90之间，包含75和90

04. 条件查询-模糊匹配

/*
    模糊查询 like
    模糊查询要结合通配符去使用。
        %：表示任意个任意的字符。
        _：表示一个任意的字符。
    格式：
        select * from 表名 where 字段 like '通配符字符串';
    % 小技巧
        '马%'： 匹配以马开头的
        '%马'： 匹配以马结尾的
        '%马%'： 匹配包含马的。
*/
# 查询姓马的学生
# 查询姓名以马开头的学生。
# 马开头的字符串的特点 -> 第一个字符固定是马， 剩下可以有任意个任意字符。
SELECT * FROM student WHERE `name` LIKE '马%';
​
# 查询姓名中包含'德'字的学生
# 包含德。  前面可以有任意个任意的字符。 中间是德。 后面也可以有任意个任意的字符。
SELECT * FROM student WHERE `name` LIKE '%德%';
​
# 查询姓马，且姓名有三个字的学生
# 姓名的第一个字必须是马， 第二个字和第三个字可以是任意的字符。
SELECT * FROM student WHERE `name` LIKE '马__';

05. 排序查询

/*
    排序查询
    可以对查询出的结果根据指定的字段进行排序。
    排序查询使用order by关键字。
    格式：
        select * from 表名 order by 要排序的字段 [asc | desc];
    asc： 表示升序排序。
    desc： 表示降序排序。
    asc和desc可以省略，如果省略，默认是升序排序。

    组合排序： 可以根据两个字段进行排序。  先根据字段1进行排序，如果字段1有相同的数据，那么
    再根据字段2进行排序。
    格式：
        select * from 表名 order by 要排序的字段 [asc | desc], 要排序的字段 [asc | desc];
*/
# 查询所有学生的数据， 并按照年龄升序排序。
SELECT * FROM student ORDER BY age ASC;
# ASC也可以省略，如果省略默认是升序
SELECT * FROM student ORDER BY age;
# 查询所有数据， 并按照年龄降序排序。
SELECT * FROM student ORDER BY age DESC;
# 查询所有的学生数据，并按照年龄降序排序，如果年龄相同再按照数学成绩升序排序。
SELECT * FROM student ORDER BY age DESC, math ASC;

06. 聚合函数

/*
    聚合函数。
    我们之前查询数据都是横向查询。聚合函数是用来纵向查找。

    count(列)：统计该列有多少条数据。　不统计null值。
    max(列)：  统计该列数据的最大值
    min(列)：  统计该列数据的最小值
    sum(列)： 统计该列数据的总和
    avg(列)：  统计该列数据的平均值。

    聚合函数要用在要查询的字段位置
    select 聚合函数 from 表名;
*/
# 查询id这一列有多少条数据
SELECT COUNT(id) FROM student;
​
# 查询english这一列有多少条数据
SELECT COUNT(english) FROM student;
​
# 查询student表有多少条数据。
SELECT COUNT(*) FROM student;
​
# 查询student表中math数学成绩的最高分
SELECT MAX(math) FROM student;
​
# 查询student表中math数学成绩的最低分
SELECT MIN(math) FROM student;
​
# 查询student表中所有人的数学总成绩。
SELECT SUM(math) FROM student;
​
# 查询student表中数学成绩的平均分
SELECT AVG(math) FROM student;

07. 分组查询

/*
    查询每个性别的数学总成绩。
    如果要查询每个性别的数学总成绩，可以对数据根据性别进行分组，分组之后再查询总成绩。
    如果要进行分组，可以使用group by关键字。
    格式：
        group by 字段名;
    分组查询一定要结合聚合函数去使用。
    
    having 和 where的区别。
    他们都用作条件筛选， 区别如下：
        1. where用在分组之前进行过滤， having用在分组之后进行过滤
        2. where后面不能使用聚合函数， having后面可以使用聚合函数。
*/
# 查询每个性别的数学总成绩。
# 根据性别进行分组。
# 查询数学总成绩。
# 如果进行了分组，那么使用聚合函数查询出来的就是每个组的数据。
SELECT sex, SUM(math) FROM student GROUP BY sex;
​
# 查询男女各多少人（查询每个性别各有多少人）
SELECT sex, COUNT(*) FROM student GROUP BY sex;
​
# 查询年龄大于25岁的人,按性别分组,统计每组的人数 （统计每个性别中有多少大于25岁的人）
# 需要做的操作: 
# 查询条件为 年龄大于25
# 按性别进行分组。
# 统计人数（最终要查询的东西）
SELECT sex,COUNT(*) FROM student WHERE age > 25 GROUP BY sex;
​
# 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
SELECT sex,COUNT(*) FROM student WHERE age > 25 GROUP BY sex HAVING COUNT(*) > 2;

08. 分页查询

/*
        分页查询
        使用limit关键字
        格式：
            limit m, n
            m： 表示从第几条数据开始显示 （从0开始数）
            n： 表示一次显示几条数据。
*/ 
# 准备数据
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);
​
# 分页显示，一页显示五条数据
# 第一页
SELECT * FROM student LIMIT 0,5;  -- 从第0条开始显示，显示5条
​
# 第二页
SELECT * FROM student LIMIT 5,5;
​
# 第三页
SELECT * FROM student LIMIT 10,5;

12. 约束-主键约束

/*
    约束：可以对某一列的数据进行限制。
    主键约束 primary key
    主键约束可以保证该列的数据唯一且不能为空。
    主键只是做一个标记，除此之外没有特殊作用。

    一个表中最多只能有一个主键， 并且从表的设计角度来说。
    每个表都应该有一个主键。
    主键不建议是业务数据。 
*/
# 主键的添加方式一 （常用）
# 在创建表的时候直接添加主键约束。
# 字段名 数据类型(长度) [约束]
CREATE TABLE person (
    id INT PRIMARY KEY, -- primary key 表示对id加上了主键约束
    first_name VARCHAR(20), 
    last_name VARCHAR(20),
    address VARCHAR(20)
);
# 添加数据
INSERT INTO person (id, first_name, last_name, address) VALUES (1, '三丰', '张', '武当');
INSERT INTO person (id, first_name, last_name, address) VALUES (2, '师太', '灭绝', '峨眉');
# id已经添加了主键约束，里面的数据不能重复
INSERT INTO person (id, first_name, last_name, address) VALUES (2, '婆婆', '金华', '光明顶');
# id是主键，也不能是null
INSERT INTO person (id, first_name, last_name, address) VALUES (NULL, '婆婆', '金华', '光明顶');
​
# 主键的添加方式二
# 创建表之后使用alter语句进行添加
# 创建表，没有主键
CREATE TABLE person (
    id INT,
    first_name VARCHAR(20), 
    last_name VARCHAR(20),
    address VARCHAR(20)
);
​
# 使用alter语句添加主键
# alter table 表名 add primary key (列名);
ALTER TABLE person ADD PRIMARY KEY (id);
​
# 主键的删除
# alter table 表名 drop primary key;
ALTER TABLE person DROP PRIMARY KEY;
​
# 联合主键
# 多个列共同组成一个主键
# 添加联合主键的格式
# alter table 表名 add primary key (列名,列名);
ALTER TABLE person ADD PRIMARY KEY (first_name, last_name);

13. 约束-自动增长列

/*
    自动增长列 auto_increment
    使用auto_increment 修饰的列里面的数据系统可以自动维护。 
    如果我们向表中添加数据， 添加数据时忽略自动增长列进行添加， 那么系统会自动维护该列数据，把该列
    数据自动加1.
    自动增长列一定要对整数列使用。
    一般都会对主键设置自动增长，让系统自动去维护主键的值。
*/
# 删除之前的表
DROP TABLE person;
# 给列添加自动增长
# 在创建表的时候直接进行添加。
# 给主键加上自动增长。
CREATE TABLE person (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 主键并且自动增长。
    `name` VARCHAR(20), 
    address VARCHAR(20)
);
​
# 添加数据，并且忽略主键进行添加。
INSERT INTO person (`name`, address) VALUES ('张三丰', '武当山');
INSERT INTO person (`name`, address) VALUES ('张无忌', '光明顶');
INSERT INTO person (`name`, address) VALUES ('大幂幂', '北京');
​
# 添加数据，指定主键列
INSERT INTO person (id, `name`, address) VALUES (10, '刘备', '蜀国');
​
# 不指定主键，那么会自动维护
INSERT INTO person (`name`, address) VALUES ('达芬奇', '意大利');
​
# delete删除所有数据
# 是逐条删除所有数据，不会重置自增长序列
DELETE FROM person;
​
# truncate删除所有数据
# 直接摧毁表，然后新建一张一模一样的表， 会重置自增长序列
TRUNCATE TABLE person;

14. 唯一约束

/*
    唯一约束： unique
    可以保证某一列的值不能重复。
*/
# 删除原来的perosn表
DROP TABLE person;
​
# 在创建表的时候直接对某一列添加唯一约束。
# 字段名 数据类型(长度) [约束]
CREATE TABLE person (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自动增长
    `name` VARCHAR(20) UNIQUE, --  执行， 使用unique进行唯一约束
    address VARCHAR(20)
);
​
# 添加数据
INSERT INTO person (`name`, address) VALUES ('刘备', '中国');
INSERT INTO person (`name`, address) VALUES ('关二爷', '也是中国');
# name已经使用unique进行了唯一约束，不能添加重复数据。
INSERT INTO person (`name`, address) VALUES ('关二爷', '还是中国');
​
INSERT INTO person (`name`, address) VALUES ('张三爷', '还是中国');
​
# unique不能限制多个null值。 
INSERT INTO person (`name`, address) VALUES (NULL, '还是中国');
INSERT INTO person (`name`, address) VALUES (NULL, '还是中国');
​
# 删除唯一约束
# alter table 表名 drop index 列名;
ALTER TABLE person DROP INDEX `name`;

15. 约束-非空约束

/*
    非空约束 not null
    可以保证某一列的数据不能为null
*/
# 添加约束
# 在创建表的之后直接给某个列添加一个非空约束
CREATE TABLE person (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    `name` VARCHAR(20) NOT NULL, -- 对name使用非空约束，保证该列的值不能为null
    address VARCHAR(20)
);
​
# 添加数据
INSERT INTO person (`name`, address) VALUES ('刘备', '蜀国');
# name已经使用了非空约束，不能为null值。
INSERT INTO person (`name`, address) VALUES (NULL, '蜀国');
INSERT INTO person (`name`, address) VALUES ('刘备', '蜀国');

16. 约束-默认值

/*
    默认值约束 default
    default可以给某列数据提供一个默认值， 如果在添加的时候不向该列添加数据，该列数据会是默认值。
    格式：
        create table 表名(
            字段名 数据类型(长度) default 默认值
        );
*/
# 创建表， 给name字段添加默认值
CREATE TABLE person(
    id INT PRIMARY KEY AUTO_INCREMENT, 
    `name` VARCHAR(20) DEFAULT '佚名', -- 表示该列的默认值是佚名
    address VARCHAR(20)
);
# 添加数据
INSERT INTO person (address) VALUES ('我是谁我在哪');
INSERT INTO person (`name`,address) VALUES ('王叔叔', '我是王叔叔，我在隔壁');
​
# 查询
SELECT * FROM person;

18. 一对多（多对一）关系的介绍

参考下图:

19. 一对多关系的实现

/*
    一对多的关系（部门和员工）
    一个部门可以有多个员工，一个员工只能属于一个部门，这个就是一对多
    一对多建表原则是建立两张表   
        a. 部门表
        b. 员工表
    在员工表中提供一个列叫做部门id，用来和部门表产生关系。
*/
# 创建部门表
CREATE TABLE department (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    `name` VARCHAR(20), -- 部门名称
    description VARCHAR(20) -- 部门描述
);
​
# 创建员工表
CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20), -- 员工姓名
    age INT, -- 员工年龄
    dept_id INT -- 所属部门id
);
# 添加外键约束。
# 让员工表中dept_id和部门表中的id产生一个强制性关联
# 外键约束要添加在从表上
# 格式
# alter table 从表名 add [constraint 约束名] foreign key (外键列名) references 主表(主键);
ALTER TABLE employee ADD CONSTRAINT e_d_fk FOREIGN KEY (dept_id) REFERENCES department(id);
​
# 删除外键
# alter table 表名 drop foreign key 外键名称;
ALTER TABLE employee DROP FOREIGN KEY e_d_fk;
​
# 向部门表添加数据
INSERT INTO department (`name`, description) VALUES ('武当','都是道士');
INSERT INTO department (`name`, description) VALUES ('峨眉','尼姑很多');
INSERT INTO department (`name`, description) VALUES ('少林','都是秃子');
​
# 向员工表添加数据
INSERT INTO employee (`name`, age, dept_id) VALUES ('张三丰', 100, 1);
INSERT INTO employee (`name`, age, dept_id) VALUES ('张翠山', 90, 1);
INSERT INTO employee (`name`, age, dept_id) VALUES ('灭绝师太', 98, 2);
# 如果向从表中添加数据，如果指定的外键列的数据在主表中不存在，那么添加失败
INSERT INTO employee (`name`, age, dept_id) VALUES ('张无忌', 95, 10);
# 外键列可以添加null值。
INSERT INTO employee (`name`, age, dept_id) VALUES ('张无忌', 95, NULL);
# 如果删除主表中的数据时， 如果该数据被从表使用着，那么无法删除
DELETE FROM department WHERE id = 1;

20. 多对多关系的介绍

参考下图:

21. 多对多关系

/*
    多对多的关系（学生和课程）
    多对多建表原则是建立三张表。
        a.学生表
        b.学科表
        c.中间表
    使用中间表和另外两张表产生关联关系。
*/
# 创建学生表
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20),
    age INT
);
# 创建学科表
CREATE TABLE `subject` (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    `name` VARCHAR(20), -- 学科名称
    description VARCHAR(200) -- 学科描述 
);
# 建立中间表，用来和学生表和学科表产生关系
CREATE TABLE stu_sub(
    stu_id INT, -- 学生id
    sub_id INT  -- 学科id
)
# 外键约束要加在中间表上
# alter table 中间表 add [constraint 约束名称] foreign key (外键列名) references 主表(主键列名);
# 使用中间表中的stu_id和student表中的主键id产生关联关系
ALTER TABLE stu_sub ADD FOREIGN KEY (stu_id) REFERENCES student(id);
# 使用中间表中的sub_id和subject表中的主键id产生关联关系
ALTER TABLE stu_sub ADD FOREIGN KEY (sub_id) REFERENCES `subject`(id);
​
# 添加数据
# 向学生表添加数据
INSERT INTO student (`name`, age) VALUES ('张无忌',12);
INSERT INTO student (`name`, age) VALUES ('张三丰',13);
INSERT INTO student (`name`, age) VALUES ('东方不败',14);
​
# 向学科表中添加数据
INSERT INTO `subject` (`name`, description) VALUES ('九阳神功', '绝世神功');
INSERT INTO `subject` (`name`, description) VALUES ('葵花宝典', '神奇的武功');
INSERT INTO `subject` (`name`, description) VALUES ('太极拳', '以柔克刚');
​
# 向中间表添加关联关系
INSERT INTO stu_sub (stu_id, sub_id) VALUES (1, 1);
INSERT INTO stu_sub (stu_id, sub_id) VALUES (1, 3);
INSERT INTO stu_sub (stu_id, sub_id) VALUES (2, 3);
​
# 如果向中间表中添加数据， 数据在主表中不存在，添加失败。
INSERT INTO stu_sub (stu_id, sub_id) VALUES (12, 3);
​
# 删除主表中的数据时，如果该数据被使用着，无法删除。
DELETE FROM student WHERE id = 1;

22. 一对一关系的介绍

参考下图: