MySQL

MySQL

1. MySQL5.7的安装和配置

• MySQL的版本：mysql5.5，mysql5.6，mysql5.7（稳定版），mysql8（更高版本）

• MySQL的安装和配置 ：

  • mysql5.7下载地址：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

  • 特别说明：如果安装MySQL过程中，出现错误，可以使用 sc delete mysql 删除已经安装好的mysql

  • 安装步骤：

    1. 下载后得到zip格式的安装文件

    2. 解压的路径最好不要有中文和空格

       

    3. 添加环境变量：此电脑-属性-高级系统设置-环境变量，在Path环境变量增加mysql的安装目录和\bin目录

       

    4. 在 D:\mysql5.7\mysql-5.7.19-winx64 目录下创建 my.ini 文件 （8.0以上版本不需要配置）

       [client]
       port=3306
       default-character-set=utf8
       [mysqld] 
       # 设置为自己MYSQL的安装目录 
       basedir=D:\mysql5.7\mysql-5.7.19-winx64\
       # 设置为MYSQL的数据目录 （该目录由系统创建）
       datadir=D:\mysql5.7\mysql-5.7.19-winx64\data\
       port=3306
       character_set_server=utf8
       # 跳过安全检查
       skip-grant-tables
       

    5. 使用**管理员身份**打开cmd，并切换到 D:\mysql5.7\mysql-5.7.19-winx64\bin 目录下，执行 mysqld -install命令

    6. 初始化数据库：mysql --initialize-insecure --user=mysql，如果执行成功会生成 data 目录

       

       

    7. 启动 mysql 服务：net start mysql 【停止 mysq l服务：net stop mysql】，如果成功：
       

    8. 进入 mysql 管理终端：mysql -u root -p 【当前 root 用户密码为空】

    9. 修改 root 用户密码

       user mysql;
       updata user set authentication_string=password('hsp') where user='root' and Host='localhost';
       flush privileges;
       quit
       

       注意：在后面需要带上分号，回车即可执行该指令；flush privileges：刷新权限；quit：退出

    10. 修改 my.ini，再次进入就会进行权限验证了

           #skip-grant-tables
        

    11. 重新启动mysql

           net stop mysql
           net start mysql
        

        注意：该指令需要 quit 退出 mysql ，在 DOS 下执行

    12. 再次进入 mysql ，输入正确的用户名和密码

          mysql -u root -p密码
        

2. 命令行连接到MySQL

• 使用命令行窗口连接MySQL数据库

  1. mysql -h 主机名 -P端口 -u用户名 -p密码
     • -p密码不要有空格
     • -p后面没有写密码，回车会要求输密码
     • 如果没有写-h主机，默认就是本机
     • 如果没有写-P端口，默认就是3306
     • 在实际工作中，端口3306一般修改
  2. 登录前，保证服务启动

3. Navicat安装和使用

• Navicat介绍：图形化MySQL管理软件
  

• Navicat官网下载地址：http://www.navicat.com.cn/products

• 安装步骤（Navicat安装非常简单，傻瓜式安装）

  1. 下载后会得到 exe 安装文件

  2. 使用管理员身份安装

  3. 这里我安装到 **D:\Navicat150\Navicat 15 for MySQL **目录下

  4. 双击运行，配置连接
     

  5. 输入正确的密码即可登录MySQL

4.数据库的三层结构

• 所谓安装MySQL数据库，就是在主机安装一个数据库管理系统（DBMS），这个管理程序可以管理多个数据库。

  DBMS：database manage system

• 一个数据库中可以创建多个表，以保存数据（信息）

• 数据库管理系统（DBMS），数据库和表的关系如图所示：
  

  普通表的本质仍然是文件

5. java操作MySQL

public class JavaMysql {

    public static void main(String[] args) throws SQLException, ClassNotFoundException{
        //演示Java程序如何操作数据库
        //加载类，得到mysql连接
        class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db01","root","hsp");

        String sql="create table hsp_goods (id int, name varchar(32), price double, introduction text)";

        //得到statement对象，把 sql 语法发送给mysql执行
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);

        //关闭连接
        statement.close();
        connection.close();
        System.out.println("成功~");
    }
}

6. 创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name

​ [create_specification [, create_specification] …]

create_specification:

[DEFAULT] CHARACTER SET charset_name

[DEFAULT] COLLATE collation_name

• CHARACTER SET：指定数据库采用的字符集，如果不指定字符集，默认UTF8
• COLLATE：指定数据库字符集的校对规则（常用utf_8、utf_general_ci），默认为 utf_general_ci，即不区分大小写

#创建一个名称为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

7. 查看、删除数据库

显示数据库语句：

SHOW DATABASE

显示数据库创建语句：

SHOW CREATE DATABASE db_name

数据库删除语句**【一定要慎用】**：

DROP DATABASE [IF EXISTS] db_name

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

8. 备份恢复数据库

• 备份数据库（注意：在DOS命令行执行）

  mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > D:\文件名.sql

• 恢复数据库（注意：进入MySQL命令行在执行）

  Source 文件名.sql

• 备份库的表

  mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > D:\\文件名.sql

#练习：database03.sql 备份 hsp_db02 和 hsp_db03 库中的数据，并恢复
#备份，要在 DOS命令行 下执行mysqldump指令
mysqldump -u root -p -B hsp_db02 hsp_db03 > D:\\bak.sql

DROP DATABASE hsp_db03;

#恢复数据库（注意：进入mysql命令行再执行）
source D:\\bak.sql
#第二个恢复数据的方法
- 打开bak.sql文件，复制文件中的sql语句到查询页面执行一次

#备份库的表
mysqldump -u root -p -B ecshop > D:\\ecshop.sql

9. 表的创建

CREATE TABLE table_name

(

​ field1 datatype

​ field2 datatype

​ field2 datatype

)CHARACTER set 字符集 COLLATE 校对规则 ENGINE 引擎

field：指定列名 datatype：指定列类型（字段类型）

CHARACTER SET：如不指定则为所在数据库字符集

COLLATE：如不指定则为所在数据库校对规则

ENGINE：引擎（涉及较多，单独讲解）

#表的创建
#创建表user，根据需要保存的数据创建相应的列，并根据数据的类型定义相应的列类型
#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

10.Mysql常用的数据类型(列类型)

11. 列类型之 整型

使用规范：在能够满足需求的情况下，尽量选择占用空间小的类型

类型	字节	最小值	最大值
		（带符号的）/（无符号的）	（带符号的）/（无符号的）
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147493648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

#如何定义一个无符号的整数
CREATE TABLE t10 (id TINYINT); //默认是有符号的
CREATE TABLE t11 (id TINYINT UNSIGNED);// 无符号的

12. 列类型之 BIT(M)

• 基本使用：

mysql> create table t02 (num1 bit(8), num2 bit(8));

mysql> insert into t02(1, 3);

mysql> insert into t02 values(2, 65);

• 细节说明：
  1. bit字段显示时，按照 位 的方式显示
  2. 查询的时候仍然可以使用 添加的数值
  3. 如果一个值只有0,1 可以考虑使用bit(1)，可以节约空间
  4. 位类型。M指定位数，默认值1，范围1-64
  5. 使用不多

13. 列类型之 小数型

• FLOAT/DOUBLE [UNSIGNED]

  FLOAT 单精度，DOUBLE 双精度

• DECIMAL[M, D] [UNSIGNED]

  • 可以支持更加精确的小数位。M是小数位数（精度）的总数，D是小数点（标度）后面的位数
  • 如果D=0，则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略，默认是0。如果M被省略，默认是10。
  • 建议：如果希望小数的精度高，推荐使用 DECIMAL

14. 列类型之 字符串

• 字符串的基本使用

  • CHAR(size)：固定长度字符串，最大255字符

  • VARCHAR(size)：可变长度字符串，最大65535字节

    UTF-8 编码最大size= (65535 - 3) / 3 = 21844字符， 1-3个字节用于记录大小

    GBK 编码最大size= (65535 - 3) / 2 = 32766字符， 1-3个字节用于记录大小

• 字符串使用的细节

  • CHAR(4)中，size = 4表示字符数(最大255），不是字节数，不管是中文还是字母都是放四个，按字符计算

    VARCHAR(4)，size = 4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据

  • CHAR(4)是定长（固定的大小），也就是说，如果你插入了 ’aa’，也会占用分配四个字符的空间

    VARCHAR(4)是变长，就是说，如果你插入了‘aa’，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配**（说明：varchar本身还需要占用1-3个字节来记录存放内容长度）**

  • 如果数据是定长，推荐使用CHAR，比如MD5的密码，邮编，手机号，身份证号码等

    如果一个字段的长度是不确定的，我们使用varchar，比如留言，文章

    查询速度：CHAR > VARCHAR

  • 在存放文本时，也可以会用 TEXT 数据类型，可以将 TEXT 列视为 VARCHAR 列，注意 TEXT不能有默认值，大小0 - 2¹⁶字节，如果希望存放更多字符，可以选择MEDIUMTEXT 0-2²⁴ 或者 LONGTEXT 0-2³²

15. 列类型之 日期类型

• 日期类型的基本使用

CREATE TABLE `birthday6`(
	t1 DATE,
	t2 DATETIME,
	t3 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CYRRENT_TIMESTAMP
);//TIMESTAMP 时间戳

INSERT INNTO birthday(t1, t2) VALUES('2022-11-11','2022-11-11 10:10:10');

• 日期类型的细节说明

  TIMESTAMP 在 INSERT 和 UPDATE 时，自动更新

16.练习：创建一个员工表

创建一个员工表emp（课堂练习），选择适当的数据类型

--	字段			属性
--	id		 	 整型
--	name	 	 字符集
--	sex		 	 字符集
--	birthday 	 日期型（DATE）
--	entry_date	 日期型（DATE）
--  job			 字符型
--  salary		 小数型
--  resume		 文本型
-- 创建一个 emp 表
CREATE TABLE `emp`(
	id INT,
    `name` VARCHAR(32),
    sex CHAR(1),
    birthday DATE,
    entry_date DATETIME,
    job VARCHAR(32),
    salary DOUBLE,
    `resume` TEXT
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
-- 添加一条数据
INSERT INTO `emp` VALUES(100, '小妖怪'，'男'，'2000-11-11',
                         '2010-11-10 11:11:11', '巡山的', 3000, '大王叫我来巡山')

17. 表的修改

使用 ALTER TABLE 语句追加，修改，或删除列的语法

ALTER TABLE tablename

  ADD (column datatype [DEFAULT expr] [, column datatype] … );

ALTER TABLE tablename

  MODIFY (column datatype [DEFAULT expr] [, column datatype] … );

ALTER TABLE tablename

  DROP (column );

• 查看表的结构：DESC 表名;
• 修改表名：RENAME TABLE 表名 to 新表名;
• 修改表字符集：ALTER TABLE 表名 CHARACTER SET 字符集；

#修改表的操作练习
-- 员工表emp的上增加一个image列，varchar类型（要求在resume后面）
ALTER TABLE emp ADD image VARCHAR(32) NOT DULL DEFAULT "" AFTER RESUME
-- 显示表结构，可以查看表的所有类
DESC emp
-- 修改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(32) NOT NULL DEFAULT ""

18. CRUD之 INSERT语句

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

INSERT INTO table_name [ ( column [ , column … ] ) ]

  VALUES (value [ , value … ] );

#练习 INSERT 语句
-- 创建一张商品表goods
-- 添加两条记录
CREATE TABLE 'goods'(
	id INT,
    good_name VARCHAR(10),
    price DOUBLE
);
INSERT INTO 'goods' (id, good_name, price) VALUES (10, '华为手机', 2000);
INSERT INTO 'goods' (id, good_name, price) VALUES (20, '苹果手机', 3000);
SELECT * FROM 'goods';

• 细节说明
  1. 插入的数据应与字段的数据类型相同。比如：把 ‘abc’ 添加到 int 类型会错误
  2. 数据的长度应在列的规则范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中
  3. 在values中列出的数据位置必须与被加入的列的排列位置相呼应
  4. 字符和日期型数据应包含在单引号中
  5. 列可以插入空值【前提是该字段允许为空】，insert into table value(null)
  6. insert into table_name (列名) values(),(),() 形式添加多条纪录
  7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称
  8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错
     • 如果某个列，没有指定 not null，那么当添加数据时，没有给定值，会默认null
     • 如果我们希望指定某个列的默认值，可以在创建表时指定

19. CRUD之 UPDATE语句

• 使用UPDATE语句修改表中的数据

  UPDATE tb1_name

  ​   SET col_name1=expr1 [, col_name2=expr2 … ]

  ​    [WHERE where_definition]

  #练习 UPDATE 语句
  -- 在上面创建的employee表中修改表中的纪录
  -- 将所有员工薪水修改为5000元
  UPDATE employee SET salary = 5000;
  -- 将姓名为 小妖怪 员工薪水修改为3000元
  UPDATE employee SET salary = 3000 WHERE `user_name` = '小妖怪';
  -- 在 老妖怪 的薪水基础上为其再增加1000元
  UPDATE employee SET salary = salary + 1000 WHERE `user_name` = '小妖怪';
  

• 使用细节

  1. UPDATE语句可以用新值更新原有表行中的各列
  2. SET子句指示要修改那些列和要给予那些值
  3. WHERE子句指定应更新那些列。如果没有WHERE子句，则更新所有的行
  4. 如果需要修改多个字段，可以通过 set 字段1=值1，字段2=值2 …

20. CRUD之 DELETE语句

使用DELETE语句删除表中的数据

UPDATE FROM tb1_name
   [WHERE where_definition]

#练习 DELETE 语句(使用employee测试)
-- 删除表中名为 '小妖怪' 的纪录
DELETE FROM employee WHERE user_name = '小妖怪';
-- 删除表中所有的纪录
DELETE FROM employee;

• 使用细节
  • 如果不使用where子句，将删除表中所有的数据
  • DELETE语句不能删除某一列的值（可使用UPDATE设为null ，或者 ’‘ ）
  • 使用DELETE语句仅删除纪录，不删除表本身。如要删除表，使用DROP TABLE语句。即：DROP TABLE 表名;

21. CRUD之 SELECT语句

21.1. SELECT基本语法

SELECT [DISTINCT] * |{column1, column2, column3 … }

  FROM tablename

• 注意事项

  1. SELECT指定查询那些列的数据
  2. column指定列名
  3. *代表查询所有列
  4. FROM指定个查询那张表
  5. DISTINCT可选，指显示结果时，是否去掉重复数据

  #练习 SELECT 语句
  -- 查询表中所有学生的信息
  SELECT * FROM student；
  -- 查询表中所有的学生姓名和对应的学习成绩
  SELECT 'name', english FROM student；
  -- 过滤表中重复数据
  SELECT DISTINCT english FROM student；
  -- 要查询的纪录，每个字段都相同，才会去重
  SELECT DISTINCT 'name', english FROM student;
  

21.2. SELECT特殊查询语句

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

SELECT [DISTINCT] * |{column1, column2, column3 … }

  FROM tablename

• 使用 AS 语句

SELECT columnname AS 别名 FROM 表名;

#练习 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

21.3. WHERE子句过滤查询

# WHERE子句 过滤查询
-- 查询姓名为赵云的学生成绩
SELECT * FROM student WHERE 'name' = '赵云'
-- 查询英语成绩大于90分的同学
SELECT * FROM student WHERE english > 90
-- 查询总分大于200分的所有同学
SELECT * FROM student WHERE (english + chinese + 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 (english + chinese + math) > 200 AND math < chinese AND `name` LIKE '韩%'
-- 查询英语分数在80~90之间的同学
SELECT * FROM student WHERE english BETWEEN 80 AND 90
-- 查询数学分数为89,90,91的同学
SELECT * FROM student WHERE english IN (89, 90, 91)

21.4. ORDER BY子句

• 使用ORDER BY子句进行排序查询结果

SELECT column1, column2, column3 …

  FROM table

  ORDER BY column asc| desc, …

• 注意事项
  1. ORDER BY 指定排序的列，排序的列既可以是表中的列名，也可以是 SELECT 语句后指定的列名
  2. ASC 升序（默认）、DESC 降序
  3. ORDER BY 子句应位于SELECT 语句的结尾

# ORDER BY 子句的使用
-- 对数学成绩进行升序输出
SELECT * FROM student ORDER BY math
-- 对总分按照降序输出
SELECT `name` AS '名字', (chinese+english+math) AS total_score FROM student ORDER BY total_score DESC
-- 对姓李的学生数学成绩升序输出
SELECT (chinese+english+math) AS total_score FROM student WHERE `name` LIKE '李%' ORDER BY total_score

22. 合计 / 统计函数

22.1 统计函数COUNT

• COUNT 返回行的总数

SELECT COUNT(*) | COUNT(列名) FROM tablename

  [WHERE where_definition]

• COUNT(*) 和 COUNT(列) 的区别
  1. COUNT(*) 返回满足条件的纪录的行数
  2. COUNT(列) 统计满足的某列有多少个，但是会排除为 null 的情况

# 统计函数的使用
-- 统计一个班究竟有多少学生
SELECT COUNT(*) FROM student
-- 统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student WHERE math > 90
-- 统计总分大于250的人数有多少个
SELECT COUNT(*) FROM student WHERE (math + english + chinese) > 250
-- 返回非空 name 有多少行
SELECT COUNT(`name`) FROM t15

22.2 合计函数SUM

• SUM 函数返回满足 WHERE条件的行的和

SELECT SUM (列名) {, SUM(列名) … } FROM tablename

  [ WHERE where_definition]

• 注意：
  1. SUM 仅对数值起作用，否则会报错
  2. 对多列求和，" , " 号不能少

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

22.3. 合计函数AVG

• AVG函数返回满足WHERE条件的一列的平均值

SELECT AVG (列名) {, AVG(列名) … } FROM tablename

  [ WHERE where_definition]

# 合计函数AVG的使用
-- 求一个班数学的平均分
SELECT AVG(math) FROM student
-- 求一个班级的总分平均分
SELECT AVG(math + english +chinese) FROM student

22.4. 合计函数MAX / MIN

• MAX / MIN函数返回满足WHERE条件一列的最大/最小值

SELECT MAX / MIN (列名) FROM tablename

  [ WHERE where_definition]

# 合计函数 MAX / MIN 的使用
-- 求班级的最高分和最低分
SELECT MAX(math + english +chinese), MIN(math + english +chinese) FROM student

23. 分组统计函数

• 使用GROUP NY 子句对列进行分组

SELECT column1, column2, column3… FROM table

  GROUP BY column

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

SELECT column1, column2, column3… FROM table

  GROUP BY column HAVING …

# GROUP BY子句 和 HAVING子句的使用
-- 创建一个部门表
CREATE TABLE dept(
    deptno MEDIUMINT 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','BOSHTON');
-- 创建一个员工表
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 NULLl,
	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',7968,'1991-2-22',1250.00,500.00,30),
    (7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
    (7654,'MARTIN','SALESMAN',7968,'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,'1991-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,'1991-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),
    (2,1201,1400),
    (3,1401,2000),
    (4,2001,3000),
    (5,3001,9999);
-- 显示每个部门的平均工资和最高工资
SELECT AVG(sal), MAX(sal), deptno FROM emp GRUOP BY deptno
-- 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MAX(sal), deptno, job FROM emp GRUOP BY deptno, job
-- 显示平均工资低于2000的部门号和它的平均工资（使用别名）
SELECT AVG(sal) AS avg_sal, deptno FROM emp GRUOP BY deptno HAVING avg_sal < 2000

24. 字符串相关函数

# 演示字符串相关函数的使用
-- CHARSET(str) 返回字串字符集、
SELECT CHARSET(ename) FROM emp;
-- CONCAT(string2 [, ...]) 连接字串 将多个列拼接成一列
SELECT CONCAT(name,' 工作是 ', job) FROM emp
-- INSTR(string, substring) 返回substring在string中出现的位置，没有则返回0
SELECT INSERT ('hanshunping', 'ping') FROM DUAL
-- 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
SELECT ename, REPLACE(job, 'MANAGER', '经理') FROM emp；
-- STRCMP(string1, string2) 逐字符比较两字串大小
SELECT STECMP('hsp', 'hsm') FROM DUAL
-- SUBSTRING(str, position, [length]) 从str的position开始【从1开始计算】，取length个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp
-- LTRIM / RTRIM / TRIM(string2) 去除前端空格或后端空格
SELECT LTRIM('  韩顺平教育') FROM DUAL
SELECT LTRIM('韩顺平教育  ') FROM DUAL
SELECT TRIM('  韩顺平教育  ') FROM DUAL
-- 以首字母小写的方式显示所有员工emp表的姓名（以两种方式）
-- 先写出 ename 的第一个字符，转换成小写
-- 把他和后面的字符串进行拼接输出
SELECT CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTRING(name, 2)) AS new_name FROM emp
SELECT CONCAT(LCASE(LEFT(ename, 1)), SUBSTRING(name, 2)) AS new_name FROM emp

25. 数学相关函数

• 注意：RAND() 返回一个随机浮点值v；范围在0 ~ 1之间（即其范围为 0 <= v <= 1.0）。若已指定一个整数参数N，则它被用作种子值，用来产生重复序列。

# 演示数学相关函数
-- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL
-- BIN(decimal_number) 十进制转成二进制
SELECT BIN(10) FROM DUAL
-- CELLING(number2) 向上取整，得到比num2大的最小整数
SELECT CELLING(1.2) FROM DUAL
-- FLOOR(number2) 向下取整，得到比num2小的最大整数
SELECT FLOOR(1.2) FROM DUAL
-- CONV(number2, from_base, to_base) 进制转换
SELECT CONV(10, 10, 2) FROM DUAL
-- FORMAT(number, decimal_places) 保留小数位数
SELECT FROMAT(18.153466, 2) FROM DUAL
-- HEX(DecimalNumber) 转十六进制
SELECT HEX(10) FROM DUAL
-- LEAST(number1, number2, ...) 求最小值
SELECT LEAST(0, 1, -10, 4) FROM DUAL
-- MOD(numerator, denominator) 求余
SELECT MOD(10, 3) FROM DUAL
-- RAND([seed]) 范围为  0 <= v <= 1.0
SELECT RAND() FROM DUAL
-- 若已指定一个整数参数N，如果N值不变，则产生的随机数也不变
SELECT RAND(1) FROM DUAL

26. 日期相关函数

细节说明：

• DATE_ADD() 中的 interval后面可以是 year、minute、second、day等
• DATE_SUB() 中的 interval后面可以是 year、minute、second、day等
• DATEDIFF(date1, date2)得到的是天数，而且是date1- date2的天数，因此可以取负数
• 第三到第七的函数的日期类型可以使date，datetime 或者 timestamp

# 演示日期时间相关函数
-- 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_DATE())
INSERT INTO mes VALUES(2, '上海新闻', NOW())
INSERT INTO mes VALUES(3, '广州新闻', NOW())

-- 显示所有的新闻信息，发布时间只显示日期，不显示时间
SELECT id, content, DATE(send_time) FROM mes
-- 查询十分钟内发布的新闻
SELECT * FROM mes WHERE DATA_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()
SELECT * FROM mes WHERE DATA_SUB(NOW(), INTERVAL 10 MINUTE) <= send_time
-- 计算2011-11-11 和1990-1-1相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL
-- 使用sql语句算出你活了多少天 1956-11-11 出生
SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL
-- 如果你能活80岁，求出你还能活多少天
SELECT DATEDIFF(DATE_ADD('1986-11-11', INTERVAL 80 YEAR), NOW) FROM DUAL 
-- 求取两个时间差
SELECT TIMEDIFF('10:11:11', "07:10:10") FROM DUAL

-- 得到当前时间的年份
SELECT YEAR(NOW()) FROM DUAL;
-- UNIX_TIMESTAMP 返回的是1970-01-01 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL
-- FROM_UNIXTIME 可以把一个unix_timestamp 秒数，转换成指定格式的日期
-- 在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
SELECT FROM_UNIXTIME(161846284, '%Y-%m-%d %H:%i:%s') FROM DUAL

27. 加密和系统函数

# 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL
-- DATABASE() 查询当前使用的数据库名称
SELECT DATABASE()
-- MD5(str) 为字符串算出一个MD5 32的字符串，常用（用户密码）加密
-- root 密码是 123456 -> 加密MD5 -> 在数据库中存放的是加密后的密码
SELECT MD5('123456') 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'))

-- PASSWORD(str) 加密函数
SELECT PASSWORD('hsp') FROM DUAL

28. 流程控制函数

# 演示流程控制语句
-- 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；如果expr3为TRUE，则返回expr4；否则返回expr5
SELECT CASE
	WHEN TRUE THEN 'jack'
	WHEN FALSE THEN 'tom'
	ELSE 'mary' END -- 结果返回jack

-- 1.查询emp表，如果 comm 是null，则显示0.0
-- 判断是否为NULL，使用 IS NULL；判断不为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;