1. 解决之道

解决之道-文件、数据库

MySQL 数据库的安装和配置(安装演示)

使用命令行窗口连接 MYSQL 数据库[示意图]

操作示意图

2. Navicat 安装和使用

介绍 : 图形化 MySQL 管理软件

3. 数据库三层结构-破除 MySQL 神秘

4. 数据在数据库中的存储方式

5. SQL 语句分类

6. 创建数据库

# 演示数据库的操作
#创建一个名称为 hsp_db01 的数据库。[图形化和指令 演示]
#使用指令创建数据库
CREATE DATABASE hsp_db01;
#删除数据库指令
DROP DATABASE hsp_db01
#创建一个使用 utf8 字符集的 hsp_db02 数据库
CREATE DATABASE hsp_db02 CHARACTER SET utf8
#创建一个使用 utf8 字符集，并带校对规则的 hsp_db03 数据库
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
#校对规则 utf8_bin 区分大小 默认 utf8_general_ci 不区分大小写
#下面是一条查询的 sql , select 查询 * 表示所有字段 FROM 从哪个表
#WHERE 从哪个字段 NAME = 'tom' 查询名字是 tom
SELECT *
    FROM t1
    WHERE NAME = 'tom'

7. 查看、删除数据库

#演示删除和查询数据库
#查看当前数据库服务器中的所有数据库
SHOW DATABASES
#查看前面创建的 hsp_db01 数据库的定义信息
SHOW CREATE DATABASE `hsp_db01` 
#老师说明 在创建数据库,表的时候，为了规避关键字，可以使用反引号解决
#删除前面创建的 hsp_db01 数据库
DROP DATABASE hsp_db01

8. 备份恢复数据库

#练习 : database03.sql 备份 hsp_db02 和 hsp_db03 库中的数据，并恢复
#备份, 要在 Dos 下执行 mysqldump 指令其实在 mysql 安装目录\bin
#这个备份的文件，就是对应的 sql 语句
mysqldump -u root -p -B hsp_db02 hsp_db03 > d:\\bak.sql
DROP DATABASE ecshop;
#恢复数据库(注意：进入 Mysql 命令行再执行)
source d:\\bak.sql
#第二个恢复方法， 直接将 bak.sql 的内容放到查询编辑器中，执行

备份恢复数据库的表

9. 安装 Ecshop 数据库

#这是一个 ecshop 的数据库，包括 ecshop 所有的表，请导入到 mysql 数据库中[备份]
#进入到 mysql 命令行: source ecshop 备份文件路径
#再将 ecshop 整个数据库备份到你的 d:\\ecshop.sql 到 dos 下 :
mysqldump -u root -p -B ecshop > d:\\ecshop.sql
#将 mysql 的 ecshop 数据库删除, 并通过备份的 d:\\ecshop.sql 恢复
#进入 mysql 命令行
source d:\\ecshop.sql

10. 创建表 (按课程大纲顺序)

#指令创建表
#注意：hsp_db02 创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user
表 (快速入门案例 create_tab01.sql )
    #id         整形 [图形化，指令]
    #name         字符串
    #password     字符串
    #birthday     日期
CREATE TABLE `user` (
    id INT, `name` VARCHAR(255), `password` VARCHAR(255), `birthday` DATE
)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

11. Mysql 常用数据类型(列类型)

数值型(整数)的基本使用

#演示整型的是一个
#老韩使用 tinyint 来演示范围 有符号 -128 ~ 127 如果没有符号 0-255
#说明： 表的字符集，校验规则, 存储引擎，老师使用默认
#1. 如果没有指定 unsinged , 则 TINYINT 就是有符号
#2. 如果指定 unsinged , 则 TINYINT 就是无符号 0-255
CREATE TABLE t3 (
    id TINYINT);
CREATE TABLE t4 (
    id TINYINT UNSIGNED);
INSERT INTO t3 VALUES(127); #这是非常简单的添加语句
SELECT * FROM t3
INSERT INTO t4 VALUES(255);
SELECT * FROM t4;

型如何定义一个无符号的整数

数值型(bit)的使用

#演示 bit 类型使用
#说明
#1. bit(m) m 在 1-64
#2. 添加数据 范围 按照你给的位数来确定，比如 m = 8 表示一个字节 0~255
#3. 显示按照 bit
#4. 查询时，仍然可以按照数来查询
CREATE TABLE t05 (num BIT(8));
INSERT INTO t05 VALUES(255);
SELECT * FROM t05;
SELECT * FROM t05 WHERE num = 1;

数值型(小数)的基本使用

#演示 decimal 类型、float、double 使用
#创建表
CREATE TABLE t06 (
    num1 FLOAT, num2 DOUBLE, num3 DECIMAL(30,20)
);
#添加数据
INSERT INTO t06 VALUES(88.12345678912345, 88.12345678912345,88.12345678912345);
SELECT * FROM t06;
#decimal 可以存放很大的数
CREATE TABLE t07 (
    num DECIMAL(65)
);
INSERT INTO t07 VALUES(8999999933338388388383838838383009338388383838383838383
                       SELECT * FROM t07;
CREATE TABLE t08(
    num BIGINT UNSIGNED
)
INSERT INTO t08 VALUES(8999999933338388388383838838383009338388383838383838383);
SELECT * FROM t08;

字符串的基本使用

#演示字符串类型使用 char varchar
#注释的快捷键 shift+ctrl+c , 注销注释 shift+ctrl+r
-- CHAR(size)
-- 固定长度字符串 最大 255 字符
-- VARCHAR(size) 0~65535 字节
-- 可变长度字符串 最大 65532 字节 【utf8 编码最大 21844 字符 1-3 个字节用于记录大小】
-- 如果表的编码是 utf8 varchar(size) size = (65535-3) / 3 = 21844
-- 如果表的编码是 gbk varchar(size) size = (65535-3) / 2 = 32766
CREATE TABLE t09 (
    `name` CHAR(255)
);
CREATE TABLE t10 (
    `name` VARCHAR(32766)) CHARSET gbk;
DROP TABLE t10;

字符串使用细节

#演示字符串类型的使用细节
#char(4) 和 varchar(4) 这个 4 表示的是字符，而不是字节, 不区分字符是汉字还是字母
CREATE TABLE t11(
    `name` CHAR(4));
INSERT INTO t11 VALUES('韩顺平好');
SELECT * FROM t11;
CREATE TABLE t12(
    `name` VARCHAR(4));
INSERT INTO t12 VALUES('韩顺平好');
INSERT INTO t12 VALUES('ab 北京');
SELECT * FROM t12;
#如果 varchar 不够用，可以考试使用 mediumtext 或者 longtext, #如果想简单点，可以使用直接使用 text
CREATE TABLE t13( content TEXT, content2 MEDIUMTEXT , content3 LONGTEXT);
INSERT INTO t13 VALUES('韩顺平教育', '韩顺平教育 100', '韩顺平教育 1000~~');
SELECT * FROM t13;

日期类型的基本使用

#演示时间相关的类型
#创建一张表, date , datetime , timestamp
CREATE TABLE t14 (
    birthday DATE , -- 生日
    job_time DATETIME, -- 记录年月日 时分秒
    login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 登录时间, 如果希望 login_time 列自动更新, 需要配置
); 
SELECT * FROM t14;
INSERT INTO t14(birthday, job_time)
    VALUES('2022-11-11','2022-11-11 10:10:10'); -- 如果我们更新 t14 表的某条记录，login_time 列会自动的以当前时间进行更新

12. 创建表练习

#创建表的课堂练习
-- 字段属性
-- Id 整形
-- name 字符型
-- sex 字符型
-- brithday 日期型（date）
-- entry_date 日期型 (date)
-- job 字符型
-- Salary 小数型
-- resume 文本型
-- 自己一定要练习一把
CREATE TABLE `emp` (
    id INT,
    `name` VARCHAR(32),
    sex CHAR(1),
    brithday DATE,
    entry_date DATETIME,
    job VARCHAR(32),
    salary DOUBLE,
    `resume` TEXT
) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB; -- 添加一条
INSERT INTO `emp` 
    VALUES(100, '小妖怪', '男', '2000-11-11',
           '2010-11-10 11:11:11', '巡山的', 3000, '大王叫我来巡山');
SELECT * FROM `emp`;

13. 修改表-基本介绍

14. 修改表-课堂练习

#修改表的操作练习
-- 员工表 emp 的上增加一个 image 列，varchar 类型(要求在 resume 后面)。
ALTER TABLE emp
    ADD image VARCHAR(32) NOT NULL DEFAULT '' 
    AFTER RESUME
DESC employee -- 显示表结构，可以查看表的所有列
-- 修改 job 列，使其长度为 60。
ALTER TABLE emp
    MODIFY job VARCHAR(60) NOT NULL DEFAULT '' 
-- 删除 sex 列。
ALTER TABLE emp
    DROP sex
-- 表名改为 employee。
RENAME TABLE emp TO employee
-- 修改表的字符集为 utf8
ALTER TABLE employee CHARACTER SET utf8
-- 列名 name 修改为 user_name
ALTER TABLE employee
    CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT '' 
DESC employee

15. 数据库 C[create]R[read]U[update]D[delete]语句

16. Insert 语句

使用 INSERT 语句向表中插入数据。

#练习 insert 语句
-- 创建一张商品表 goods (id int , goods_name varchar(10), price double ); -- 添加 2 条记录
CREATE TABLE `goods` (
    id INT ,
    goods_name VARCHAR(10), -- 长度 10
    price DOUBLE NOT NULL DEFAULT 100 ); 
-- 添加数据
INSERT INTO `goods` (id, goods_name, price)
    VALUES(10, '华为手机', 2000);
INSERT INTO `goods` (id, goods_name, price)
    VALUES(20, '苹果手机', 3000);
SELECT * FROM goods;
CREATE TABLE `goods2` (
    id INT ,
    goods_name VARCHAR(10), -- 长度 10
    price DOUBLE NOT NULL DEFAULT 100 );

学员练习：使用 insert 语句向表 employee 中插入 2 个员工的信息。

细节说明

#说明 insert 语句的细节
-- 1.插入的数据应与字段的数据类型相同。
    -- 比如 把 'abc' 添加到 int 类型会错误
    INSERT INTO `goods` (id, goods_name, price)
        VALUES('韩顺平', '小米手机', 2000);
-- 2. 数据的长度应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
    INSERT INTO `goods` (id, goods_name, price)
        VALUES(40, 'vovo 手机 vovo 手机 vovo 手机 vovo 手机 vovo 手机', 3000); 
-- 3. 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。
    INSERT INTO `goods` (id, goods_name, price) -- 不对
        VALUES('vovo 手机',40, 2000); 
-- 4. 字符和日期型数据应包含在单引号中。
    INSERT INTO `goods` (id, goods_name, price)
        VALUES(40, vovo 手机, 3000); -- 错误的 vovo 手机 应该 'vovo 手机' 
-- 5. 列可以插入空值[前提是该字段允许为空]，insert into table value(null)
    INSERT INTO `goods` (id, goods_name, price)
        VALUES(40, 'vovo 手机', NULL); 
-- 6. insert into tab_name (列名..) values (),(),() 形式添加多条记录
    INSERT INTO `goods` (id, goods_name, price)
        VALUES(50, '三星手机', 2300),(60, '海尔手机', 1800); 
-- 7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称
    INSERT INTO `goods` VALUES(70, 'IBM 手机', 5000); 
-- 8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错
    -- 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给 null 
    -- 如果我们希望指定某个列的默认值，可以在创建表时指定
    INSERT INTO `goods` (id, goods_name)
        VALUES(80, '格力手机');
    SELECT * FROM goods;
    INSERT INTO `goods2` (id, goods_name)
        VALUES(10, '顺平手机');
    SELECT * FROM goods2;

17. update 语句

使用 update 语句修改表中数据

基本使用

-- 演示 update 语句
-- 要求: 在上面创建的 employee 表中修改表中的纪录
-- 1. 将所有员工薪水修改为 5000 元。[如果没有带 where 条件，会修改所有的记录，因此要小心]
    UPDATE employee SET salary = 5000
-- 2. 将姓名为 小妖怪 的员工薪水修改为 3000 元。
    UPDATE employee
        SET salary = 3000
        WHERE user_name = '小妖怪' 
-- 3. 将 老妖怪 的薪水在原有基础上增加 1000 元
    INSERT INTO employee
        VALUES(200, '老妖怪', '1990-11-11', '2000-11-11 10:10:10', '捶背的', 5000, '给大王捶背', 'd:\\a.jpg');
        UPDATE employee
            SET salary = salary + 1000
            WHERE user_name = '老妖怪' 
-- 可以修改多个列的值
    UPDATE employee
        SET salary = salary + 1000 , job = '出主意的' 
        WHERE user_name = '老妖怪' 
    SELECT * FROM employee;

使用细节:

18. delete 语句

使用 delete 语句删除表中数据

-- delete 语句演示
-- 删除表中名称为’老妖怪’的记录。
DELETE FROM employee
    WHERE user_name = '老妖怪'; 
-- 删除表中所有记录, 老师提醒，一定要小心
DELETE FROM employee; 
-- Delete 语句不能删除某一列的值（可使用 update 设为 null 或者 ''）
UPDATE employee SET job = '' 
    WHERE user_name = '老妖怪';
SELECT * FROM employee
-- 要删除这个表
DROP TABLE employee;

使用细节

19. select 语句

基本语法

注意事项 (创建测试表学生表 )

课堂练习

-- select 语句【重点 难点】
CREATE TABLE student(
    id INT NOT NULL DEFAULT 1, 
    NAME VARCHAR(20) NOT NULL DEFAULT '', 
    chinese FLOAT NOT NULL DEFAULT 0.0, 
    english FLOAT NOT NULL DEFAULT 0.0, 
    math FLOAT NOT NULL DEFAULT 0.0
);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);
-- 查询表中所有学生的信息。
SELECT * FROM student; 
-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student; 
-- 过滤表中重复数据 distinct 。
SELECT DISTINCT english FROM student; 
-- 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT `name`, english FROM student;

使用表达式对查询的列进行运算

在 select 语句中可使用 as 语句

练习

-- select 语句的使用
-- 统计每个学生的总分
SELECT `name`, (chinese+english+math) FROM student; 
-- 在所有学生总分加 10 分的情况
SELECT `name`, (chinese + english + math + 10) FROM student; 
-- 使用别名表示学生分数。
SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score
    FROM student;

在 where 子句中经常使用的运算符

使用 where 子句，进行过滤查询

-- select 语句
-- 查询姓名为赵云的学生成绩
SELECT * FROM student
    WHERE `name` = '赵云' 
-- 查询英语成绩大于 90 分的同学
SELECT * FROM student
    WHERE english > 90
-- 查询总分大于 200 分的所有同学
SELECT * FROM student
    WHERE (chinese + english + math) > 200
-- 查询 math 大于 60 并且(and) id 大于 4 的学生成绩
SELECT * FROM student
    WHERE math >60 AND id > 4
-- 查询英语成绩大于语文成绩的同学
SELECT * FROM student
    WHERE english > chinese
-- 查询总分大于 200 分 并且 数学成绩小于语文成绩,的姓赵的学生. 
-- 赵% 表示 名字以韩开头的就可以
SELECT * FROM student
    WHERE (chinese + english + math) > 200 AND
        math < chinese AND `name` LIKE '赵%' 
-- 查询英语分数在 80－90 之间的同学。
SELECT * FROM student
    WHERE english >= 80 AND english <= 90;
SELECT * FROM student
    WHERE english BETWEEN 80 AND 90; -- between .. and .. 是 闭区间
-- 查询数学分数为 89,90,91 的同学。
SELECT * FROM student
    WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student
    WHERE math IN (89, 90, 91);
-- 查询所有姓韩的学生成绩。
    SELECT * FROM student
WHERE `name` LIKE '韩%'

使用 order by 子句排序查询结果

-- 演示 order by 使用
-- 对数学成绩排序后输出【升序】。
SELECT * FROM student
    ORDER BY math; 
-- 对总分按从高到低的顺序输出 [降序] -- 使用别名排序
SELECT `name` , (chinese + english + math) AS total_score FROM student
    ORDER BY total_score DESC; 
-- 对姓韩的学生成绩[总分]排序输出(升序) where + order by
SELECT `name`, (chinese + english + math) AS total_score FROM student
    WHERE `name` LIKE '韩%' 
    ORDER BY total_score;

合计/统计函数

count

-- 演示 mysql 的统计函数的使用
-- 统计一个班级共有多少学生？
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于 90 的学生有多少个？
SELECT COUNT(*) FROM student
    WHERE math > 90
-- 统计总分大于 250 的人数有多少？
SELECT COUNT(*) FROM student
    WHERE (math + english + chinese) > 250
-- count(*) 和 count(列) 的区别
-- 解释 :count(*) 返回满足条件的记录的行数
-- count(列): 统计满足条件的某列有多少个，但是会排除 为 null 的情况
CREATE TABLE t15 (
    `name` VARCHAR(20));
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);
SELECT * FROM t15;
SELECT COUNT(*) FROM t15; -- 4
SELECT COUNT(`name`) FROM t15;-- 3
-- 演示 sum 函数的使用
-- 统计一个班级数学总成绩？
SELECT SUM(math) FROM student; 
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student; -- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/ COUNT(*) FROM student;
SELECT SUM(`name`) FROM student; 
-- 演示 avg 的使用
-- 练习：
-- 求一个班级数学平均分？
SELECT AVG(math) FROM student; 
-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student; 
-- 演示 max 和 min 的使用
-- 求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(math + english + chinese), MIN(math + english + chinese)
    FROM student;
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre
    FROM student;

sum

-- 演示 sum 函数的使用
-- 统计一个班级数学总成绩？
SELECT SUM(math) FROM student; 
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student; 
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student; 
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/ COUNT(*) FROM student;
SELECT SUM(`name`) FROM student;

avg

-- 演示 avg 的使用
-- 练习：
-- 求一个班级数学平均分？
SELECT AVG(math) FROM student; 
-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;

max/min

-- 演示 max 和 min 的使用
-- 求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(math + english + chinese), MIN(math + english + chinese)
    FROM student; 
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre
    FROM student;

使用 group by 子句对列进行分组 [先创建测试表]

使用 having 子句对分组后的结果进行过滤

CREATE TABLE dept( /*部门表*/
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    dname VARCHAR(20) NOT NULL DEFAULT "",
    loc VARCHAR(13) NOT NULL DEFAULT ""
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'),
    (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'),
    (40, 'OPERATIONS', 'BOSTON');
SELECT * FROM dept; 
-- 员工表
CREATE TABLE emp
(
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
    ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
    job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
    mgr MEDIUMINT UNSIGNED ,/*上级编号*/
    hiredate DATE NOT NULL,/*入职时间*/
    sal DECIMAL(7,2) NOT NULL,/*薪水*/
    comm DECIMAL(7,2) ,/*红利 奖金*/
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);
-- 添加测试数据
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20),
    (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),
    (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),
    (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),
    (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),
    (7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),
    (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),
    (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),
    (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
    (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),
    (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),
    (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),
    (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
SELECT * FROM emp; 
-- 工资级别
#工资级别表
CREATE TABLE salgrade
(
    grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/
    losal DECIMAL(17,2) NOT NULL, /* 该级别的最低工资 */
    hisal DECIMAL(17,2) NOT NULL /* 该级别的最高工资*/
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
SELECT * FROM salgrade;
SELECT * FROM dept;
SELECT * FROM emp;
# 演示 group by + having
GROUP by 用于对查询的结果分组统计, (示意图)
-- having 子句用于限制分组显示结果. -- ?如何显示每个部门的平均工资和最高工资
-- 老韩分析: avg(sal) max(sal)
-- 按照部分来分组查询
SELECT AVG(sal), MAX(sal) , deptno
    FROM emp GROUP BY deptno; 
-- 使用数学方法，对小数点进行处理
SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno
    FROM emp GROUP BY deptno; 
-- ?显示每个部门的每种岗位的平均工资和最低工资
-- 老师分析 1. 显示每个部门的平均工资和最低工资
--         2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal) , deptno, job
FROM emp GROUP BY deptno, job; 
-- ?显示平均工资低于 2000 的部门号和它的平均工资 // 别名
-- 老师分析 [写 sql 语句的思路是化繁为简,各个击破]
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000
-- 3. 使用别名进行过滤
SELECT AVG(sal), deptno
    FROM emp GROUP BY deptno
        HAVING AVG(sal) < 2000; 
-- 使用别名
SELECT AVG(sal) AS avg_sal, deptno
    FROM emp GROUP BY deptno
        HAVING avg_sal < 2000;

20. 字符串相关函数

-- 演示字符串相关函数的使用 ， 使用 emp 表来演示
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp; 
-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp; 
-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0
-- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('hanshunping', 'ping') FROM DUAL; 
-- UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp; 
-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp; 
-- LEFT (string2 ,length )从 string2 中的左边起取 length 个字符
-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符
SELECT LEFT(ename, 2) FROM emp; 
-- LENGTH (string )string 长度[按照字节]
SELECT LENGTH(ename) FROM emp; 
-- REPLACE (str ,search_str ,replace_str )
-- 在 str 中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;
-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('hsp', 'hsp') FROM DUAL; 
-- SUBSTRING (str , position [,length ])
-- 从 str 的 position 开始【从 1 开始计算】,取 length 个字符
-- 从 ename 列的第一个位置开始取出 2 个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp; 
-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM(' 韩顺平教育') FROM DUAL;
SELECT RTRIM('韩顺平教育 ') FROM DUAL;
SELECT TRIM(' 韩顺平教育 ') FROM DUAL; 
-- 练习: 以首字母小写的方式显示所有员工 emp 表的姓名
-- 方法 1
-- 思路先取出 ename 的第一个字符，转成小写的
-- 把他和后面的字符串进行拼接输出即可
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2)) AS new_name
    FROM emp;
SELECT CONCAT(LCASE(LEFT(ename,1)), SUBSTRING(ename,2)) AS new_name
    FROM emp;

21. 数学相关函数

-- 演示数学相关函数
-- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL; 
-- BIN (decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL; 
-- CEILING (number2 ) 向上取整, 得到比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL; 
-- CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的 8, 转成 2 进制输出
SELECT CONV(8, 10, 2) FROM DUAL; 
-- 下面的含义是 8 是 16 进制的 8, 转成 2 进制输出
SELECT CONV(16, 16, 10) FROM DUAL; 
-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL; 
-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL; 
-- HEX (DecimalNumber ) 转十六进制
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL; 
-- MOD (numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL; 
-- RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 老韩说明
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;

22. 时间日期相关函数

-- 日期时间相关函数
-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL; 
-- CURRENT_TIME ( )当前时间
SELECT CURRENT_TIME() FROM DUAL; 
-- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL; 
-- 创建测试表 信息表
CREATE TABLE mes(
    id INT ,
    content VARCHAR(30),
    send_time DATETIME
);
-- 添加一条记录
INSERT INTO mes
VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());
SELECT * FROM mes;
SELECT NOW() FROM DUAL; 
-- 上应用实例
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间. 
SELECT id, content, DATE(send_time)
FROM mes; 
-- 请查询在 10 分钟内发布的新闻, 思路一定要梳理一下. SELECT *
FROM mes
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()
SELECT *
FROM mes
WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL; 
-- 请用 mysql 的 sql 语句求出你活了多少天? [练习] 1986-11-11 出生
SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL; 
-- 如果你能活 80 岁，求出你还能活多少天.[练习] 1986-11-11 出生
-- 先求出活 80 岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 1986-11-11->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒
-- '1986-11-11' 可以 date,datetime timestamp
SELECT DATEDIFF(DATE_ADD('1986-11-11', INTERVAL 80 YEAR), NOW())
    FROM DUAL;
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL; 
-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL; 
-- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL; 
-- FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过 FROM_UNIXTIME 转换
--SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;
SELECT * FROM mysql.user \G

23. 加密和系统函数

-- 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP
SELECT USER() FROM DUAL; -- 用户@IP 地址
-- DATABASE()查询当前使用数据库名称
SELECT DATABASE(); 
-- MD5(str) 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密
-- root 密码是 hsp -> 加密 md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL; 
-- 演示用户表，存放密码时，是 md5
CREATE TABLE hsp_user
    (id INT , `name` VARCHAR(32) NOT NULL DEFAULT '', pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user
    VALUES(100, '韩顺平', MD5('hsp'));
SELECT * FROM hsp_user; -- csdn
SELECT * FROM hsp_user -- SQL 注入问题
    WHERE `name`='韩顺平' AND pwd = MD5('hsp')
-- PASSWORD(str) -- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密
SELECT PASSWORD('hsp') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC
-- select * from mysql.user \G 从原文密码 str 计算并返回密码字符串
-- 通常用于对 mysql 数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user

24. 流程控制函数

# 演示流程控制语句
# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2) 如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2
SELECT IFNULL( NULL, '韩顺平教育') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果 expr1 为 TRUE,则返回 expr2,如果 expr2 为 t, 返回 expr4, 否则返回 expr5
SELECT CASE
    WHEN TRUE THEN 'jack' -- jack
    WHEN FALSE THEN 'tom' 
    ELSE 'mary' END
-- 1. 查询 emp 表, 如果 comm 是 null , 则显示 0.0
-- 老师说明，判断是否为 null 要使用 is null, 判断不为空 使用 is not
SELECT ename, IF(comm IS NULL , 0.0, comm)
    FROM emp;
SELECT ename, IFNULL(comm, 0.0)
    FROM emp; 
-- 2. 如果 emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename, (SELECT CASE
    WHEN job = 'CLERK' THEN '职员' 
        WHEN job = 'MANAGER' THEN '经理' 
        WHEN job = 'SALESMAN' THEN '销售人员' 
        ELSE job END) AS 'job' 
FROM emp;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

25. mysql 表查询—加强

介绍

-- 查询加强
-- ■ 使用 where 子句
-- ?如何查找 1992.1.1 后入职的员工
-- 老师说明： 在 mysql 中,日期类型可以直接比较, 需要注意格式
SELECT * FROM emp
    WHERE hiredate > '1992-01-01'
-- ■ 如何使用 like 操作符(模糊)
-- %: 表示 0 到多个任意字符 _: 表示单个任意字符
-- ?如何显示首字符为 S 的员工姓名和工资
SELECT ename, sal FROM emp
    WHERE ename LIKE 'S%' 
-- ?如何显示第三个字符为大写 O 的所有员工的姓名和工资
SELECT ename, sal FROM emp
    WHERE ename LIKE '__O%' 
-- ■ 如何显示没有上级的雇员的情况
SELECT * FROM emp
    WHERE mgr IS NULL; -- ■ 查询表结构
DESC emp
-- 使用 order by 子句
-- ?如何按照工资的从低到高的顺序[升序]，显示雇员的信息
SELECT * FROM emp
    ORDER BY sal 
-- ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp
    ORDER BY deptno ASC , sal DESC;

分页查询

-- 分页查询
-- 按雇员的 id 号升序取出， 每页显示 3 条记录，请分别显示 第 1 页，第 2 页，第 3 页
-- 第 1 页
SELECT * FROM emp
    ORDER BY empno
    LIMIT 0, 3; 
-- 第 2 页
SELECT * FROM emp
    ORDER BY empno
    LIMIT 3, 3; 
-- 第 3 页
SELECT * FROM emp
    ORDER BY empno
    LIMIT 6, 3;
-- 推导一个公式
SELECT * FROM emp
    ORDER BY empno
    LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数
-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY job; 
-- 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*) FROM emp WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;

使用分组函数和分组子句

-- 增强 group by 的使用
-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal), job
    FROM emp
    GROUP BY job; 
-- (2) 显示雇员总数，以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非 null, 就是 count(列)，如果该列的值为 null, 是
-- 不会统计 , SQL 非常灵活，需要我们动脑筋. 
SELECT COUNT(*), COUNT(comm)
    FROM emp
-- 老师的扩展要求：统计没有获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
    FROM emp
SELECT COUNT(*), COUNT(*) - COUNT(comm)
    FROM emp
-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr)
    FROM emp; 
-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal)
    FROM emp;
    SELECT * FROM emp;
select * from dept;
-- 应用案例：请统计各个部门 group by 的平均工资 avg，
-- 并且是大于 1000 的 having，并且按照平均工资从高到低排序， order by
-- 取出前两行记录 limit 0, 2
SELECT deptno, AVG(sal) AS avg_sal
    FROM emp
    GROUP BY deptno
    HAVING avg_sal > 1000
    ORDER BY avg_sal DESC
    LIMIT 0,2

数据分组的总结

26. mysql 多表查询

问题的引出(重点，难点)

说明

多表查询练习

-- 多表查询
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*
    老韩分析
    1. 雇员名,雇员工资 来自 emp 表
    2. 部门的名字 来自 dept 表
    3. 需求对 emp 和 dept 查询 ename,sal,dname,deptno
    4. 当我们需要指定显示某个表的列是，需要 表.列表
*/
SELECT ename,sal,dname,emp.deptno
    FROM emp, dept
    WHERE emp.deptno = dept.deptno
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade; 
-- 老韩小技巧：多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为 10 的部门名、员工名和工资
SELECT ename,sal,dname,emp.deptno
    FROM emp, dept
    WHERE emp.deptno = dept.deptno AND emp.deptno = 10
-- ?显示各个员工的姓名，工资，及其工资的级别
-- 思路 姓名，工资 来自 emp 13
-- 工资级别 salgrade 5
-- 写 sql , 先写一个简单，然后加入过滤条件... 
select ename, sal, grade
    from emp , salgrade
    where sal between losal and hisal;

自连接

-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 老韩分析： 员工名字 在 emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp 表的 mgr 列关联
-- 这里老师小结：
-- 自连接的特点 1. 把同一张表当做两张表使用
--               2. 需要给表取别名 表名 表别名
--               3. 列名不明确，可以指定列的别名 列名 as 列的别名
SELECT worker.ename AS '职员名' , boss.ename AS '上级名' 
    FROM emp worker, emp boss
    WHERE worker.mgr = boss.empno;
SELECT * FROM emp;

27. mysql 表子查询

什么是子查询

子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询

单行子查询

单行子查询是指只返回一行数据的子查询语句

请思考：如何显示与 SMITH 同一部门的所有员工?

-- 子查询的演示
-- 请思考：如何显示与 SMITH 同一部门的所有员工?
/*
1. 先查询到 SMITH 的部门号得到
2. 把上面的 select 语句当做一个子查询来使用
*/
SELECT deptno
    FROM emp
    WHERE ename = 'SMITH' 
    -- 下面的答案. 
SELECT *FROM emp
    WHERE deptno = (
        SELECT deptno
            FROM emp
            WHERE ename = 'SMITH' 
    )

多行子查询

多行子查询指返回多行数据的子查询 使用关键字 **in**

-- 课堂练习:如何查询和部门 10 的工作相同的雇员的
-- 名字、岗位、工资、部门号, 但是不含 10 号部门自己的雇员.
/*
1. 查询到 10 号部门有哪些工作
2. 把上面查询的结果当做子查询使用
*/
select distinct job
    from emp
    where deptno = 10; 
    -- 下面语句完整
    select ename, job, sal, deptno
        from emp
        where job in (
            SELECT DISTINCT job
                FROM emp
                WHERE deptno = 10
        ) and deptno <> 10

子查询当做临时表使用

在多行子查询中使用 all 操作符

-- all 和 any 的使用
-- 请思考:显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > ALL(
        SELECT sal
            FROM emp
            WHERE deptno = 30
    )
-- 可以这样写
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > (
        SELECT MAX(sal)
            FROM emp
            WHERE deptno = 30
    )