CREAT DATABASE [IF NOT EXISTS] <数据库名>[CHARACTER SET <字符集名>] [COLLATE <排序规则名>] 

#1. 创建一个名称为mkbk_db01的数据库
CREATE DATABASE mkbk_db01;

#删除删除使用图形化工具创建的数据库
DROP DATABASE mkbk_db01;

#2.创建一个使用utf8字符集的mkbk_db02数据库  
CREATE DATABASE mkbk_db02 CHARACTER SET utf8;

#3.创建一个使用utf8字符集，并指定排序规则为utf8_bin的mkbk_db03数据库
CREATE DATABASE mkbk_db03 CHARACTER SET utf8 COLLATE utf8_bin;

#校对规则 utf8_bin 区分大小 默认的 utf8_general_ci 不区分大小

/*4. 使用图形化工具在mkbk_db03数据库中创建一个名称为mkbk的表, 并使用SELECT语句查询该表中的内容，以验证排序规则是否正确。*/
SELECT * FROM mkbk WHERE NAME = 'tom'
#select 查询, * 表示所有字段, FROM 从哪个表, WHERE 从哪个字段, NAME = 'tom' 查询名字是 tom

#查看当前数据库服务器中的所有数据库
SHOW DATABASES;
 
#查看前面创建的 mkbk_db01 数据库的定义信息
SHOW CREATE DATABASE mkbk_db01;
 
#在创建数据库,表的时候，为了规避关键字，可以使用反引号解决
CREATE DATABASE `create`;

#删除前面创建的 create 数据库
DROP DATABASE `create`;

#备份, 要在 Dos 下执行, mysqldump 指令其实在 mysql 安装目录\bin\mysqldump.exe 
mysqldump -u root -p -B mkbk_db02 mkbk_db03 > d:\\bak.sql;

#删除以备份的数据库
DROP DATABASE mkbk_db03;
DROP DATABASE mkbk_db02;

#恢复数据库(注意：进入 Mysql 命令行再执行) 
source d:\\bak.sql 

#第二个恢复方法， 直接将 bak.sql 的内容放到查询编辑器中，执行

use 数据库;
source d:\\文件名.sql;

#演示整型数值类型的使用方式
#使用 tinyint 来演示范围 有符号 -128 ~ 127 如果没有符号(UNSIGNED) 0-255
#说明：表的字符集，校验规则, 存储引擎，使用默认 

CREATE TABLE t1
(
	id TINYINT -- 有符号类型：-128 ~ 127
);
INSERT INTO t1 VALUES(127); -- 添加语句
INSERT INTO t1 VALUES(-129); -- 报错，该值超出了该列所允许的范围
SELECT * FROM t1; # 查询语句

CREATE TABLE t2
(
	id TINYINT UNSIGNED /*无符号类型UNSIGNED：0 ~ 255 */
);
INSERT INTO t2 VALUES(255);
INSERT INTO t2 VALUES(-1); /* 报错，该值超出了该列所允许的范围 */
SELECT * FROM t2;

#演示 BIT 类型使用
#1. bit(m) m 范围是 1 ~ 64 
#2. 添加数据 范围按照给定的位数来确定，比如 m = 8 表示一个字节 0~255 
#3. 显示数据按照二进制显示 
#4. 查询时，仍然可以按照十进制数数来查询

CREATE TABLE t3
(
	num BIT(8) # 表示这个列将存储一个8位的二进制数，也就是一个字节（8比特）,整数范围是 1 ~ 255
);
INSERT INTO t3 VALUES(255); 
INSERT INTO t3 VALUES(256); # 报错，超出二进制所能表示的最大值 
SELECT * FROM t3; # 返回表格 t3 中所有行的所有列(查询到的数据会按二进制显示)

SELECT * FROM t3 WHERE num = 255; # 添加了一个过滤条件 WHERE num = 255 (可以使用十进制整数来查询对应的二进制数值)

-- FLOAT/DOUBLE [UNSIGNED] : 单精度小数和双精度小数

/*
DECIMAL(M,D) [UNSIGNED] : 可以支特更加精确的小数位
M是小数位数（精度）的总数，D是小数点（标度）后面的位数
如果D是0，则值没有小数点或分数部分
M最大65, D最大是30, 如果D被省略，默认是0, 如果M被省略, 默认是10
建议：如果希望小数的精度高，推荐使用decimal
*/

# 创建表
CREATE TABLE t4
(
	num1 FLOAT,
	num2 DOUBLE,
	num3 DECIMAL(30 ,20)
);

# 添加数据
INSERT INTO t4 VALUES(88.012345678912345, 88.012345678912345, 88.012345678912345);
SELECT * FROM t4;

# DECIMAL 可以存很大的数值
CREATE TABLE t5( num DECIMAL(65));
INSERT INTO t5 VALUES(012345678901234564890123456789012345648901234567890123456489);
SELECT * FROM t5;

# BIGINT UNSIGNED (0 ~ 2^64-1)
CREATE TABLE t6( num BIGINT UNSIGNED);
INSERT INTO t6 VALUES(8999999933338388388383838838383009338388383838383838383); # 插入失败，超出范围(8Byte)
SELECT * FROM t6;

#演示字符串类型使用 char varchar
#注释的快捷键 shift+ctrl+c , 取消注释 shift+ctrl+r 

-- CHAR(size) 
-- 固定长度字符串 最大 255 个字符 
-- VARCHAR(size) 0~65535 字节 
-- 可变长度字符串 最大 65532 字节 (utf8 编码最大 21844 字符 1-3 个字节用于记录大小)
-- 如果表的编码是 utf8, 那么最大 size = (65535-3) / 3 = 21844 
-- 如果表的编码是 gbk, 那么最大 size = (65535-3) / 2 = 32766

CREATE TABLE t7 (`name` CHAR(255));
CREATE TABLE t8 (`name` VARCHAR(21844)); -- 使用数据库默认的字符编码(utf8)
CREATE TABLE t9 (`name` VARCHAR(32766) CHARSET gbk); -- 指定使用gbk编码

CREATE TABLE t10 (`name` CHAR(256)); -- 超出范围，CHAR 类型最大长度是255个字符
CREATE TABLE t11 (`name` VARCHAR(21845)); -- 超出范围，utf8编码的 VARCHAR 最大长度是21844个字节
CREATE TABLE t12 (`name` VARCHAR(32767) CHARSET gbk); -- 超出范围，gbk编码的 VARCHAR 最大长度是32766个字节

SELECT * FROM t9;

#演示时间相关的类型 
#创建一张表, date , datetime , timestamp

CREATE TABLE t16
(
birthday DATE,
job_time DATETIME,
login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
SELECT * FROM t16;
INSERT INTO t16(birthday, job_time) VALUES('2008-11-11','2008-11-11 10:11:11');

/*login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP：
这一行定义了一个名为 login_time 的列，其数据类型为 TIMESTAMP。
同时指定了一些额外的属性：
NOT NULL 表示这一列不允许为空。
DEFAULT CURRENT_TIMESTAMP 表示如果没有提供值，则默认使用当前的日期和时间。
ON UPDATE CURRENT_TIMESTAMP 表示当记录被更新时，将自动更新为当前的日期和时间。
*/

CREATE TABLE table_name
(   
    field1  datatype,
    field2  datatype,
    field3  datatype
)
CHARACTER SET 字符集 COLLATE 排序规则  ENGINE 存储引擎;

-- field: 指定列名(字段)
-- datatype: 指定列类型(字段类型)
-- CHARACTER SET: 如不指定则使用所在数据库字符集
-- COLLATE: 如不指定则使用所在数据库排序规则
-- ENGINE: 如不指定则使用默认存储引擎

#创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。

#例：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;

# 创建表练习

CREATE TABLE emp(
    id INT, -- 标识符
    `name` VARCHAR(20), -- 名称
    sex CHAR(1), -- 性别
    brithday DATE, -- 生日
    entry_date DATE, -- 入职日期
    job VARCHAR(20), -- 职位
    salary DOUBLE, -- 工资
    resume TEXT -- 简历
)CHARACTER SET = utf8 COLLATE = utf8_general_ci;

INSERT INTO emp VALUES(100, '张三' ,'男', '1990-01-01', '2017-01-01', 'Java开发', 9000, '**大学');

SELECT * FROM emp;

# 修改表的操作练习

-- 员工表 emp 的上增加一个 image 列，varchar 类型(要求在 resume 后面)。
ALTER TABLE emp ADD image VARCHAR(100) NOT NULL DEFAULT '' AFTER `resume`;

DESC emp; -- 查看表结构
SELCT * FROM emp; -- 查询emp的所有列

-- 修改 job 列，使其长度为 60。
ALTER TABLE emp MODIFY job VARCHAR(60);

-- 删除 sex 列。
ALTER TABLE emp DROP sex;

-- 表名改为 employee。
RENAME  emp TO employee;
DESC employee;

-- 修改表的字符集为 utf8
ALTER TABLE employee CHARACTER SET utf8;
SHOW CREATE TABLE employee;

-- 列名 name 修改为 user_name
ALTER TABLE employee CHANGE `name` user_name VARCHAR(32);

# insert 语句的细节 

-- 1.插入的数据应与字段的数据类型相同。 比如 把 'abc' 添加到 int 类型会错误 
INSERT INTO goods (id, goods_name, price) VALUES('张三', '小米手机', 2000);  -- 报错: 类型不匹配

-- 2. 数据的长度应在列的规定范围内，例如：不能将一个长度为 11 的字符串加入到长度为 10 的列中。
INSERT INTO goods (id, goods_name, price) VALUES(40, '牛牛牛123456789', 3000); -- 报错：数据过长

-- 3. 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。 
INSERT INTO goods (id, goods_name, price) VALUES('牛牛手机', 40, 2000); -- 报错: 类型不匹配

-- 4. 字符和日期型数据应包含在单引号中。 
INSERT INTO goods (id, goods_name, price) VALUES(50, 花为手机, 3000); -- 报错：找不到该列

-- 5. 列可以插入空值[前提是该字段允许为空]
INSERT INTO goods (id, goods_name, price) VALUES(60, 'xiaomi', NULL); 
SELECT * FROM goods;

-- 6. insert into tab_name (列名..) values (),(),() 形式添加多条记录 
INSERT INTO goods (id, goods_name, price) VALUES(80, '三星手机', 2300),(60, '海尔手机', 1800); 

-- 7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称 
INSERT INTO goods VALUES(70, 'IBM 手机', 5000); 

-- 8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错 
CREATE TABLE goods2 (
	id INT,
	goods_name VARCHAR(60),
	price DOUBLE NOT NULL DEFAULT 100 -- 不允许存储空值,未提供price的值时，将被设置为100
);

INSERT INTO goods2 (id, goods_name) VALUES(50, '三星手机');

SELECT * FROM goods2;

-- 演示 update 语句 

-- 要求: 在上面创建的 employee 表中修改表中的纪录 

-- 1. 将所有员工薪水修改为 5000 元。[如果没有带 where 条件，会修改所有的记录，因此要小心]
INSERT INTO employee VALUES(100, '张三' , '1990-01-01', '2017-01-01', 'Java开发', 9000, '南京大学', 'd:\6.png');
SELECT * FROM employee;

UPDATE employee SET salary = 5000;

-- 2. 将姓名为张三的员工薪水修改为 3000 元。
UPDATE employee SET salary = 3000 WHERE user_name = '张三';
SELECT * FROM employee;

-- 3. 将张三的薪水在原有基础上增加 1000 元
UPDATE employee SET salary = salary + 1000 WHERE user_name = '张三';
SELECT * FROM employee;

-- 可以修改多个列的值
UPDATE employee SET salary = salary + 1000, job = 'C++开发' WHERE user_name = '张三';
SELECT * FROM employee;

# 细节

-- 1.UPDATE语法可以用新值更新原有表行中的各列。

-- 2.SET子句指示要修改哪些列和要给予哪些值。

-- 3.WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行(记录)，因此一定小心。

-- 4. 如果需要修改多个字段，可以通过 set 字段1 = 值1, 字段2 = 值2

-- delete 语句演示

-- 删除表中名称为张三的记录。
CREATE TABLE employee(
    id INT, -- 标识符
    `name` VARCHAR(20), -- 名称
    sex CHAR(1), -- 性别
    brithday DATE, -- 生日
    entry_date DATE, -- 入职日期
    job VARCHAR(20), -- 职位
    salary DOUBLE, -- 工资
    RESUME TEXT -- 简历
)CHARACTER SET = utf8 COLLATE = utf8_general_ci;

INSERT INTO emp VALUES(100, '张三' ,'男', '1990-01-01', '2017-01-01', 'Java开发', 9000, '**大学');

DELETE FROM employee WHERE user_name = '张三';

-- 删除表中所有记录, 一定要小心
DELETE FROM employee;

-- Delete 语句不能删除某一列的值（可使用 update 设为 null 或者 ''）
UPDATE employee SET job = NULL;

-- 删除这个表
DROP TABLE employee;

# 细节

-- 如果不使用where子句，将删除表中所有数据

-- Delete语句不能删除某一列的值 (可使用update 设为 null 或者)使用delete语句仅删除记录，不删除表本身。

-- 如要删除表，使用drop table语句。drop table 表名

SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件;

SELECT column_name AS alias_name
FROM table_name;

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
);

SHOW CREATE DATABASE mkbk; -- sqlyog的默认类型不是utf8
ALTER DATABASE mkbk CHARACTER SET utf8;

SHOW CREATE TABLE student;
-- alter table student CHARACTER SET utf8;
ALTER TABLE student CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT INTO student VALUES(1,'韩顺平',89,87,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);


-- 1.查询表中所有学生的信息。
SELECT * FROM student;

-- 2.查询表中所有学生的姓名和对应的英语成绩。
SELECT `name` AS '姓名', english AS '英语' FROM student;

-- 3.过滤表中英语成绩的重复数据distinct
SELECT DISTINCT  english AS '英语' FROM student;

-- 4.要查询的记录，每个字段都相同才会去重
SELECT DISTINCT `name` AS '姓名', english AS '英语' FROM student; -- 可以指定多列

-- SELECT 使用表达式对查询的列进行运算
SELECT * FROM student;

-- 统计每个学生的总分
SELECT `name` AS '姓名', (chinese + english + math) AS '总分' FROM student; 

-- 所有学生总分加 10 分
SELECT `name` AS '姓名', (chinese + english + math + 10) AS '总分' FROM student; 

-- 使用别名表示学生分数。
SELECT `name` AS '姓名', (chinese + english + math) AS '总分' FROM student; 

-- SELECT 使用 where 过滤条件

-- select 语句
SELECT * FROM student;

-- 查询姓名为赵云的学生成绩
SELECT * FROM student WHERE `name` = '赵云';
SELECT `name`, chinese, math, english FROM student WHERE `name` = '赵云'; -- 等价

-- 查询英语成绩大于 90 分的同学
SELECT `name`, english FROM student WHERE english > 90;

-- 查询总分大于 200 分的所有同学
SELECT * FROM student WHERE (math + english + chinese) > 200;
SELECT * , (math + english + chinese) AS '总分' FROM student WHERE (math + english + chinese) > 200; -- 显示总分

-- 查询 math 大于 60 并且(and) id 大于 4 的学生成绩
SELECT `name`, id, math FROM student WHERE math > 60 AND id > 4;
SELECT `name`, id, math FROM student WHERE (math > 60) AND (id > 4); -- 括号 

-- 查询英语成绩大于语文成绩的同学
SELECT `name`, english, chinese FROM student WHERE english > chinese;

-- 查询总分大于 200 分 并且 数学成绩小于语文成绩,的姓赵的学生. -- 赵%
SELECT * FROM student WHERE (english + math + chinese) > 200 AND math < chinese AND `name` LIKE '赵%'; -- LIKE操作符是用于模糊匹配的条件操作符

-- 查询英语分数在 80－90 之间的同学。
SELECT * FROM student WHERE english BETWEEN 80 AND 90; -- 闭区间 BETWEEN ... AND ...
SELECT * FROM student WHERE english > 80 AND english < 90; 

-- 查询数学分数为 89,90,91 的同学。
SELECT `name`, math FROM student WHERE math IN(89, 90, 91); -- IN()函数是用来判断某个字段的值是否在一个给定的值列表中
SELECT `name`, math FROM student WHERE math = 89 OR math = 90 OR math = 91;

-- 查询所有姓韩的学生成绩。
SELECT * FROM student WHERE `name` LIKE '韩%';

-- 查询语文分数在70-80之间的同学。
SELECT `name`, chinese FROM student WHERE chinses BETWEEN 70 AND 80;

-- 查询总分为189,190,191的同学。
SELECT * FROM student WHERE (math + chinese + english) IN (266, 170, 242);

-- 查询所有姓韩或者姓张的学生成绩。
SELECT * FROM student WHERE `name` LIKE '韩%' OR 'name' LIKE '张%';

-- 查询数学比语文多30分的同学。
SELECT * FROM student WHERE math > chinese + 30;

-- 演示 mysql 的统计函数的使用 

-- 统计一个班级共有多少学生？
SELECT COUNT(*) FROM student;

-- 统计数学成绩大于 80 的学生有多少个？
SELECT COUNT(math) FROM student WHERE math > 80;

-- 统计总分大于 250 的人数有多少？
SELECT COUNT(*) FROM student WHERE (chinese +english + math) > 250;
SELECT COUNT(chinese +english + math) FROM student WHERE (chinese +english + math) > 250; -- 等价


CREATE TABLE t17(
	`name` VARCHAR (20)
)CHARACTER SET utf8;

INSERT INTO t17 VALUES('tom');
INSERT INTO t17 VALUES('jack');
INSERT INTO t17 VALUES('mary');
INSERT INTO t17 VALUES(NULL);

SELECT * FROM t17;

-- count(*) 和 count(列) 的区别 
-- count(*) 返回满足条件的记录的行数 
-- count(列): 统计满足条件的某列有多少个，但是会排除 为 null 的情况
SELECT COUNT(`name`) FROM t17; -- 3
SELECT COUNT(*) FROM t17; -- 4

-- 演示 sum 函数的使用
 
-- 统计一个班级数学总成绩？
SELECT SUM(math) AS '数学总分' FROM student;

-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(chinese), SUM(english), SUM(math) FROM student;

-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(chinese + english + math) FROM student;

-- 演示 avg 的使用 

-- 统计一个班级语文成绩平均分
SELECT AVG(chinese) FROM student;
SELECT SUM(chinese) / COUNT(*) FROM student; -- 等价

-- 求一个班级数学平均分
SELECT AVG(math) FROM student;

-- 求一个班级总分平均分
SELECT AVG(math + chinese + english) FROM student;
SELECT SUM(math + chinese + english) / COUNT(*) FROM student; -- 等价

-- 演示 max 和 min 的使用

-- 求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(math + chinese + english), MIN(math + chinese + english) FROM student;

-- 求出班级数学最高分和最低分
SELECT MAX(math), MIN(math) FROM student;

-- 同时查询具有相应分数的学生的姓名
SELECT 
  (SELECT NAME FROM student WHERE math = (SELECT MAX(math) FROM student)) AS max_math_student, 
  MAX(math) AS max_math, 
  (SELECT NAME FROM student WHERE math = (SELECT MIN(math) FROM student)) AS min_math_student,
  MIN(math) AS min_math  
FROM student;

-- 演示 order by 使用 

-- 对数学成绩排序后输出[升序] ASC
SELECT `name`, math FROM student ORDER BY math ASC;

-- 对总分按从高到低的顺序输出 [降序] DESC
SELECT `name` , (english + math + chinese) AS '总分'
FROM student
ORDER BY (english + math + chinese) DESC;

-- 对姓韩的学生成绩[总分]排序输出(升序) where + order by
SELECT `name`, (english + math + chinese) AS '总分' FROM student 
WHERE `name` LIKE '韩%' 
ORDER BY 总分 ASC; -- 别名不能加单引号

/*部门表*/
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');

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);

-- 工资级别 #工资级别表 
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 dept;
SELECT * FROM emp;
SELECT * FROM salgrade;

# 演示 group by + having 

-- GROUP by 用于对查询的结果分组统计
-- having 子句用于限制分组显示结果. 

-- 如何显示每个部门的平均工资和最高工资 
-- 1. avg(sal) max(sal) 
-- 2. 按照部分来分组查询
SELECT * FROM emp;
SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP BY deptno;


-- 显示每个部门的每种岗位的平均工资和最低工资 
-- 1. 显示每个部门的平均工资和最低工资 
-- 2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal), deptno FROM emp GROUP BY deptno;


-- 显示平均工资低于 2000 的部门号和它的平均工资 // 别名
-- 写 sql 语句的思路是化繁为简, 逐个击破
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000
SELECT AVG(sal), deptno FROM emp GROUP BY deptno;
SELECT AVG(sal), deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2000;

-- 3. 使用别名进行过滤(不用重复调用函数，效率更高)
SELECT AVG(sal) AS 平均工资, deptno FROM emp GROUP BY deptno HAVING 平均工资 < 2000;

-- 演示字符串相关函数的使用 ， 使用 emp 表来演示
SELECT * FROM emp;

-- CHARSET(str) 返回字串字符集 
SELECT CHARSET(ename) FROM emp;

-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列 
-- dual 亚元表, 系统表 可以作为测试表使用 
SELECT CONCAT('hello', 'world!') FROM DUAL;

-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0 
SELECT INSTR('hello world!', 'll') FROM DUAL;

-- UCASE (string2 ) 转换成大写 
SELECT UCASE('hello') FROM DUAL;

-- LCASE (string2 ) 转换成小写 
SELECT LCASE('hello') FROM DUAL;

-- LEFT (string2 ,length )从 string2 中的左边起取 length 个字符 
SELECT LEFT('hello', 2) FROM DUAL;

-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符 
SELECT RIGHT('hello', 2) FROM DUAL;

-- LENGTH (string )string 长度[按照字节] 
SELECT LENGTH('hello') FROM DUAL;

-- REPLACE (str ,search_str ,replace_str ) 
-- 在 str 中用 replace_str 替换 search_str 
-- 如果是 manager 就替换成 经理 
SELECT REPLACE('hello', 'llo', 'rt') FROM DUAL;
SELECT REPLACE(job, 'MANAGER', '经理') FROM emp;

-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小 
SELECT STRCMP('T', 'l') FROM DUAL;
SELECT STRCMP('SALESMAN', 'MANAGER') FROM DUAL;

-- SUBSTRING (str , position [,length ]) 
-- 从 str 的 position 开始【从 1 开始计算】,取 length 个字符 
-- 从 ename 列的第一个位置开始取出 2 个字符 
-- 第三个参数为空则表示取完
SELECT SUBSTRING('hello world', 7, 5) FROM DUAL;
SELECT SUBSTRING('hello world', 7, 5) FROM DUAL;

-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string) 
-- 去除前端空格或后端空格 
SELECT LTRIM(' hello world') FROM DUAL;
SELECT RTRIM('hello world ') FROM DUAL;
SELECT TRIM(' hello world ') FROM DUAL;

-- 练习: 以首字母小写的方式显示所有员工 emp 表的姓名 
-- 方法 1 -- 思路先取出 ename 的第一个字符，转成小写的 
-- 把他和后面的字符串进行拼接输出即可
SELECT CONCAT(LCASE(LEFT(ename,1)), SUBSTRING(ename, 2)) FROM emp;
SELECT CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTRING(ename, 2)) FROM emp;

-- 演示数学相关函数 

-- ABS(num) 绝对值 
SELECT ABS(-6) FROM DUAL;

-- BIN (decimal_number )十进制转二进制 
SELECT BIN(10) FROM DUAL;

-- CEILING (number2 ) 向上取整, 得到比 num2 大的最小整数 
SELECT CEILING(1.1) FROM DUAL;
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(8, 16, 2) FROM DUAL;

-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(1.1) FROM DUAL;
SELECT FLOOR(-1.1) FROM DUAL;

-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入) 
SELECT FORMAT(1.123456, 4) FROM DUAL;

-- HEX (DecimalNumber ) 转十六进制 
SELECT HEX(10) FROM DUAL;

-- LEAST (number , number2 [,..]) 求最小值 
SELECT LEAST(10, 12, 9) FROM DUAL;

-- MOD (numerator ,denominator ) 求余 
SELECT MOD(10, 3) FROM DUAL;
SELECT MOD(-10, 3) FROM DUAL;
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 RAND(6) FROM DUAL;

-- 日期时间相关函数

-- 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 DATE_SUB(NOW(), INTERVAL 10 MINUTE) <= send_time; -- 等价

-- 请在mysql 的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11' , '1990-1-1') FROM DUAL;
SELECT DATEDIFF('2011-11-11' , '1990-1-1') / 365 FROM DUAL; -- 换算单位为年

-- 请用mysql 的sql语句求出你活了多少天? 
SELECT DATEDIFF(NOW(), '2000-01-01') 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('2000-01-01', INTERVAL 80 YEAR), NOW()) FROM DUAL;
SELECT DATEDIFF('2023-09-15', '2023-08-15 10:10:36') FROM DUAL; -- DATEDIFF函数只计算日期部分之间的差距，忽略时间部分

-- unix_timestamp() : 返回的是1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;  -- 1694763630 (sec)
SELECT UNIX_TIMESTAMP() / (3600*24*365) FROM DUAL; -- 换算单位为年

-- FROM_UNIXTIME() : 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
SELECT FROM_UNIXTIME(1694763630) FROM DUAL;

-- %Y-%m-%d 格式是规定好的，表示年月日
SELECT FROM_UNIXTIME(1694763630, '%Y-%m-%d %H:%m:%s') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%H:%m:%s') FROM DUAL;

-- 演示加密函数和系统函数

-- USER()	查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL; 

-- DATABASE()	查询当前正在使用数据库名称
SELECT DATABASE() FROM DUAL; 

-- MD5(str)	为字符串算出一个 MD5 32的字符串，常用(用户密码)加密
-- root 密码是 666 -> 加密md5 -> 在数据库中存放的是加密后的密码
-- 演示用户表，存放密码时，是md5
CREATE TABLE users(
	id INT,
	`name` VARCHAR(32) NOT NULL DEFAULT '', 
	pwd CHAR(32) NOT NULL DEFAULT ''
);

INSERT INTO users VALUES(100, '玛卡巴卡', MD5('mkbk'));
INSERT INTO users VALUES(200, '唔西迪西', MD5('wxdi'));

SELECT * FROM users;

SELECT * FROM users WHERE `name` = '玛卡巴卡' AND pwd = 'mkbk'; -- 查不到
SELECT * FROM users WHERE `name` = '玛卡巴卡' AND pwd = MD5('mkbk')  -- SQL注入问题

-- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD()函数加密
SELECT * FROM mysql.user; -- 数据库用户表中的登录密码加密后的密文
# *007D50CA06F69776D307B1BEC71CD73D0EA0999C 
SELECT PASSWORD('666') FROM DUAL; -- 当前使用的密码加密后的密文
# *007D50CA06F69776D307B1BEC71CD73D0EA0999C

# 演示流程控制语句 
SELECT * FROM emp;

# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
SELECT IF(comm, comm, '0.00') FROM emp;
SELECT IF(TRUE, '北京', '上海') FROM DUAL;

# IFNULL(expr1,expr2) 如果expr1为NULL则返回指定的替代值(expr2)，如果不为NULL则返回原始值(expr1)
SELECT ename, comm, IFNULL(comm, 0.00) FROM emp;
SELECT IFNULL(FALSE, 'mkbk') FROM DUAL; -- 0(false)

# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果 expr1 为 TRUE,则返回 expr2,如果 expr2 为 true, 返回 expr4, 否则返回 expr5
SELECT CASE 
	WHEN TRUE THEN 'jack' -- 返回真值
	WHEN FALSE THEN 'tom' 
	ELSE 'mary' 
END AS `name` FROM DUAL;

SELECT ename, sal, CASE 
	WHEN sal >= 3000 THEN '优秀'
	WHEN sal < 3000 AND sal >= 2000 THEN '良好'
        WHEN sal < 2000 AND sal >= 1500 THEN '及格'
        ELSE '不及格'
	END AS result
FROM emp ORDER BY sal DESC; -- 薪水按降序排序

-- 1. 查询 emp 表, 如果 comm 是 null , 则显示 0.0 
-- 判断是否为 NULL 要使用 IS NULL, 判断不为空 使用 IS NOT NULL
SELECT ename, IFNULL(comm, 0.00) AS 奖金 FROM emp;
SELECT ename, IF(comm, comm, 0.00) AS 奖金 FROM emp;

SELECT ename, IF(comm IS NULL, 0.00, comm) 奖金 FROM emp;
SELECT ename, IF(comm IS NOT NULL, comm, 0.00) 奖金 FROM emp;

-- 2. 如果 emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理 
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename, 
	REPLACE(job, 'CLERK', '职员'), 
	REPLACE(job, 'MANAGER', '经理'), 
	REPLACE(job, 'SALESMAN', '销售人员')
FROM emp; -- 不可行

SELECT  ename, CASE
	WHEN job = 'CLERK' THEN '职员'
	WHEN job = 'MANAGER' THEN '经理'
	WHEN job = 'SALESMAN' THEN '销售人员'
	ELSE job -- 没有匹配则返回job列的原始值
	END AS 职位
FROM emp;

SELECT ename, CASE job -- 更简洁的写法
	WHEN 'CLERK' THEN '职员'
	WHEN 'MANAGER' THEN '经理'
	WHEN 'SALESMAN' THEN '销售人员'
	ELSE job 
	END AS 职位 
FROM emp; 

-- 单表查询巩固练习
SELECT * FROM emp;

--  使用 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; -- 注意，是 "IS NULL" ，不是 "= NULL"

-- 查询表结构 DESC emp 
DESC emp; -- DESCRIBE, 返回包含表中每个列的信息的结果集
SHOW CREATE TABLE emp; -- 显示创建指定表的完整创建语句

-- 使用 order by 子句 
-- 如何按照工资的从低到高的顺序[升序]，显示雇员的信息 
SELECT * FROM emp ORDER BY sal ASC; -- Ascending

-- 按照部门号升序而雇员的工资降序排列 , 显示雇员信息 
SELECT * FROM emp ORDER BY deptno ASC, sal DESC; -- Descending

SELECT column1, column2, ...
FROM table
WHERE condition
ORDER BY column
LIMIT offset, count;

-- 分页查询 
SELECT * FROM emp;

-- 按雇员的 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) , 每页显示记录数

-- 练习：按雇员的empno降序取出，每页显示5条记录, 显示第3页
SELECT * FROM emp ORDER BY empno LIMIT 10, 5;

-- 测试
-- 显示雇员总数，以及获得奖金的雇员数
SELECT COUNT(*), COUNT(comm) FROM emp;
SELECT COUNT(*), SUM(CASE WHEN comm IS NOT NULL THEN 1 ELSE 0 END) FROM emp; -- CASE语句检查每个雇员的comm列是否为非空值,然后，SUM函数对这些返回的值进行求和

-- 增强 group by 的使用
SELECT * FROM emp;

-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT job, COUNT(ename), AVG(sal) FROM emp GROUP BY job;

-- (2) 显示雇员总数，以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非 null, 就是 count(列)，如果该列的值为 null, 是 
-- SQL 非常灵活，需要我们多动脑筋
SELECT COUNT(ename), COUNT(comm) FROM emp;
SELECT COUNT(ename), COUNT(IFNULL(comm, NULL)) FROM emp;
SELECT COUNT(ename), COUNT(IF(comm, 1, NULL)) FROM emp;
SELECT COUNT(ename), COUNT(comm) FROM emp WHERE comm IS NOT NULL OR ename IS NOT NULL;

-- 统计没有获得补助的雇员数
SELECT COUNT(ename), COUNT(*) - COUNT(comm) FROM emp;
SELECT COUNT(ename), (COUNT(*) - COUNT(IF(comm, 1, NULL))) FROM emp;
SELECT COUNT(ename), COUNT(comm) FROM emp WHERE comm IS NULL;

-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr) FROM emp;

-- (4) 显示雇员工资的最大差额。 
-- 思路： max(sal) - min(sal) 
SELECT MAX(sal) - MIN(sal) FROM emp;

-- 应用案例：请统计各个部门 group by 的平均工资 avg， 
-- 并且是大于 1000 的 having，并且按照平均工资从高到低排序， order by 
-- 取出前两行记录 limit 0, 2
SELECT deptno, AVG(sal) AS 平均工资
FROM emp 
GROUP BY deptno
HAVING 平均工资 > 1000 
ORDER BY 平均工资 DESC 
LIMIT 0, 2;

-- 多表查询 
SELECT * FROM dept;
SELECT * FROM emp;
SELECT * FROM salgrade;

-- 显示雇员名,雇员工资及所在部门的编号 [笛卡尔积] 
/* 分析
 1. 雇员名(ename),雇员工资(sal) 来自 emp 表
 2. 部门的名字(dname) 来自 dept 表 
 3. 需求对 emp 和 dept 查询 ename,sal和deptno 
 4. 当我们需要指定显示某个表的列是，需要 表.列表 */
SELECT ename, sal, emp.deptno 
FROM dept, emp 
WHERE dept.deptno = emp.deptno;

-- 小技巧：多表查询的条件不能少于 表的个数 -1, 否则会出现笛卡尔集 			 
SELECT * FROM emp, dept; -- 13 * 4 = 52 行

-- 如何显示部门号为 10 的部门名、员工名和工资
SELECT emp.deptno, dname, ename, sal
FROM emp, dept
WHERE dept.deptno = emp.deptno AND emp.deptno = 10;

-- 显示各个员工的姓名，工资，及其工资的级别 
-- 思路 姓名，工资 来自 emp 13 
-- 工资级别 salgrade 5 
-- 写 sql , 先写一个简单，然后加入过滤条件...
SELECT ename, sal, grade
FROM emp, salgrade
WHERE sal >= losal AND sal <= hisal;

SELECT ename, sal, grade
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal; -- 通常情况下，BETWEEN 和 AND 默认是闭区间（inclusive），也就是指定的范围包括了边界值

-- 练习
-- 显示雇员名，雇员工资及所在部门名字，并按照部门排序[降序]
SELECT ename, sal, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY dname DESC; -- ORDER BY 可以对字符进行排序

-- 多表查询的自连接 
SELECT * FROM emp;

-- 思考题: 显示公司员工名字和他的上级的名字 
-- 老韩分析： 员工名字 在 emp, 上级的名字的名字 emp 
-- 员工和上级是通过 emp 表的 mgr 列关联 
SELECT 员工.ename, 上级.ename
FROM emp AS 员工, emp AS 上级
WHERE 员工.mgr = 上级.empno

-- 自连接的特点 
-- 1. 把同一张表当做两张表使用 
-- 2. 需要给表取别名: 表名 表别名 
-- 3. 列名不明确，可以指定列的别名 列名 as 列的别名
SELECT * 
FROM emp AS worker, emp AS boss; -- 笛卡尔积(13*13=169条记录)

SELECT worker.ename AS 职员名, boss.ename AS 上级名 -- 需要查询员工姓名和上级姓名
FROM emp AS worker, emp AS boss;

SELECT worker.ename AS 职员名, boss.ename AS 上级名
FROM emp AS worker, emp AS boss
WHERE worker.mgr = boss.empno; -- 员工(worker)的上级(mgr)等于员工(boss)的编号(empno)

-- 子查询的演示 

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

SELECT * 
FROM emp 
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');

-- 练习:查询和部门 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;