MySQL数据库
P1 数据库的作用 2022/6/19
1.简介
MySQL是一个[关系型数据库管理系统]由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
2.简单原理图
P2 MySQL5.7 的安装配置 2022/6/19
1.下载链接
MySQL 5.7：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
**注意：**如果安装过程中出现错误，需要删除则需要执行此命令sc delete mysql
2.解压到文件夹
(1)不可以有中文目录或者空格
3.添加环境变量
**添加目的：**让我们更方便的在任意一个目录下都可以操作我们MySQL的相关指令
步骤：
4.在安装目录中创建文件my.ini
- 添加如下内容
[ client ]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
[ mysqld ]
# 设置为自己MySQL的安装目录
basedir=F:\MySQL\mysql-5.7.19-winx64\
# 设置为MySQL的数据目录，这个目录是系统创建
datadir=F:\MySQL\mysql-5.7.19-winx64\data\
# 设置3306端口
port=3306
# 服务端使用的字符集默认为UTF8
default-character-set=utf8
# 跳过安全检查
skip-grant-tables
5.CMD执行安装
- **需要管理员运行cmd,**使用cd切换到安装\bin目录下，
- 提示安装成功
部分系统进入目录指令不同
6.初始化数据库
F:\MySQL\mysql-5.7.19-winx64\bin>mysqld --initialize-insecure --user=mysql
# 执行此条命令，然后会生成一个data文件夹，里面需要有文件和文件夹才算初始化成功
7.启动服务
F:\MySQL\mysql-5.7.19-winx64\bin>net start mysql # 启动服务
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
# 如需要停止服务输入命令
F:\MySQL\mysql-5.7.19-winx64\bin>net stop mysql # 停止服务
8.进入MySQL管理终端
F:\MySQL\mysql-5.7.19-winx64\bin>mysql -u root -p # 用户为root，密码暂时不设置
Enter password: # 先不设置密码，直接回车
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> # 已进入
9.修改用户密码
mysql> use mysql; # 使用MySQL数据库
Database changed # 数据库已更改
mysql> update user set authentication_string=password('zjc') where user='root' and Host='localhost';
# 上面命令为设置当前用户的密码为zjc
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges; # 刷新权限
Query OK, 0 rows affected (0.00 sec)
10.修改my.ini
11.登录测试
# 修改完成后，需要进行重启服务
F:\MySQL\mysql-5.7.19-winx64\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。
F:\MySQL\mysql-5.7.19-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
F:\MySQL\mysql-5.7.19-winx64\bin>mysql -u root -p
Enter password: # 不输入密码很显然已拒绝登录
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
F:\MySQL\mysql-5.7.19-winx64\bin>mysql -u root -p
Enter password: *** # 输入正确密码可以成功登录
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
P3 命令行连接到MySQL 2022/6/20
1.连接指令
C:\Windows\system32>mysql -h 127.0.0.1 -P 3306 -u root -pzjc
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
# 如果没有主机IP，默认为本机IP，如果没有输入端口，则就是默认3306（在实际工作中一般会修改）
P4 navicat 的安装和使用 2022/6/20
1.下载链接
https://www.downkr.com/ruanjian/166750.html#download-all
P5 SQLyog的安装和使用 2022/6/20
1.下载链接
试用版本：https://sqlyog.en.softonic.com/download
社区版本：https://github.com/webyog/sqlyog-community/wiki/Downloads
2.使用方法
- 既可以使用sql语句进行，也可以使用图形化操作，推荐使用sqlyog
P6 MySQL的三层结构 2022/6/26
1.数据库的三层结构
- 所谓安装的MySQL数据库，就是在主机安装一个数据库管理系统（DBMS),这个管理程序可以管理多个数据库。（database manager system）
- 一个数据库中了可以创建多个表，以保存数据（信息）
- 数据库管理系统（DBMS),数据库和表的关系：示意图
2.数据在数据库中的存储方式
列(colum),行(row）
3.sql语句分类
**例如：**添加数据
SELECT * FROM users # 查询
INSERT INTO users VALUES(4,'马超','天津'); # 添加表内容
P 7 java操作MySQL 2022/6/27
1.java操作数据库
P 8 创建数据库 2022/6/27
1.创建
命令操作：
# 数据库的操作
# 创建一个名称为zjc_db01的数据库
# 使用指令创建数据库,如果不指定字符集默认utf8，
# 字符校验规则默认utf8_general_ci
CREATE DATABASE zjc_01;
# 删除数据库指令
DROP DATABASE db01;
# 创建一个使用utf8字符集的zjc_db02数据库
CREATE DATABASE zjc_02 CHARACTER SET utf8
# 创建一个使用utf8字符集，并带校对规则zjc_db03数据库
CREATE DATABASE zjc_03 CHARACTER SET utf8 COLLATE utf8_bin
注意事项：
2.校对规则
# 校对规则 utf8_bin 区分大小写，默认utf8_general_ci 不区分大小写，不指定就是数据库默认的。
# 区别演示
# 这是一条查询sql,select 查询，* 表示所有字段，from表示从那个表查询，
# where 从那个字段进行查询，name='tom',查询的名字是tom
SELECT *
FROM t1
WHERE NAME = 'tom '
# 因为db03是区分大小写的，所以应该是不同结果
区分大小写则只会查询出一条记录；不区分大小写，则会查询出两条记录。如下图所示：
P 9 查询数据库 2022/6/27
1.查看、删除数据库
# 演示删除和查询数据库
# 查看当前数据库服务器中所有数据库
SHOW DATABASES
# 查看当前创建的zjc_db01数据库的定义信息
SHOW CREATE DATABASE `zjc_01`
# 在创建数据库、表的时候，为了规避关键字，可以使用反引号``(tab健上面这个）解决
CREATE DATABASE `CREATE`
DROP DATABASE `CREATE`
# 删除前面创建的zjc_db01数据库
DROP DATABASE `ZJC_01`
P 10 备份恢复数据库 2022/6/27
1.备份与恢复数据库
-
备份：命令行（注意：在DOS执行）> mysqldump -u [用户名 ] -p [密码] -B [数据库1，数据库2，数据库3] >文件名.sql
-
恢复：（注意：进入MySQL命令行执行） Source 文件名.sql
-
练习：database03.sql 备份zjc_01,zjc_02 库中的数据，并恢复
# 备份
C:\Windows\system32>mysqldump -u root -p -B zjc_02 zjc_03 > d:\\mysqlbak\mysqlbak1.sql
Enter password: ***
# 不备份多个数据库，只备份数据库里面的表，不加-B选项
C:\Windows\system32>mysql -u root -p zjc_02 t2 > d:\\mysqlbak\bak1.sql
C:\Windows\system32>
# 恢复
# 第一种方法：通过命令行进行恢复
mysql> source d:\\mysqlbak\bak.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
# 第二种方法：通过复制bak.sql备份文件内容到查询编辑器执行
mysql> show databases; # 重新查询发现数据库zjc_01,zjc_02已经恢复完成
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zjc_02 |
| zjc_03 |
+--------------------+
6 rows in set (0.00 sec)
2.总结命令
# 练习 ：database03.sql 备份zjc_01,zjc_02 库中的数据，并恢复
# 备份，要在Dos下执行mysqldump指令其实在安装目录\bin
# 这个备份的文件就是对应的sql语句
mysqldump -u root -p -B zjc_02 zjc_03 > d:\\bak.sql
DROP DATABASE zjc_03;
# 恢复数据库 （注意：进入MySQL命令行执行）
source d:\\mysqlbak\bak.sql
# 第二个恢复方法：把备份文件bak.sql中的语句进行复制到查询编辑器里面执行
# 只需要备份一个数据库里面的一张表，mysqldump -u root -p [数据库] [表] >d:\\mysqlbak\bak1.sql DOS下执行
P 11 创建表 2022/6/29
1.创建表
2.SQL语句执行
# 指令创建表
# 注意：zjc_02创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类
# 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;
P 12 列类型有哪些 2022/6/29
1.数据类型（列类型）
- 列类型即数据类型
- 有无符号代表正负，有符号的分正负，无符号的只有正的（java）
P 13 列类型之整型 2022/6/30
1.数值型（整数）的基本使用
- 说明使用规范：在能够满足需求而的情况下，尽量选择占用空间小的类型
- 应用实例：
# 演示整型的是一个
# 使用tinyint 有符号的：-128~127 无符号的： 0-255
CREATE DATABASE `db_01` CHARACTER SET utf8 COLLATE utf8_bin # 创建数据库，设置字符集和校验规则
# 表的字符集，校验规则，引擎使用数据库默认的
# 如果没有指定unsigned,则tinyint就是有符号的
CREATE TABLE t3 (
id TINYINT ); # 未指定则是有符号的，范围-128~127
CREATE TABLE t4 (
id TINYINT UNSIGNED ); # 添加unsigned就是无符号的，范围0-255
INSERT INTO t3 VALUES(-128); # 这是简单的添加语句
SELECT * FROM t3
INSERT INTO t4 VALUES(255);
SELECT * FROM t4
P 14 列类型之bit 2022/7/1
1.数值型bit的显示
# 演示bit类型使用
# 1.bit(m) 在1-64,一个字节(byte)为8个比特/位，一个英文字母通常占用一个字节，一个汉字通常占用两个字节。
# 2.添加数据,范围，按照你给的位数来确定 ，比如m=8，表示一个字节，0~255(1,2,4,8,16,32,64,128)
# 3.显示按照bit，
# 4.查询时，任然可以按照数来查询
CREATE TABLE t5 (num BIT (8));
INSERT INTO t5 VALUES(1);
SELECT * FROM t5;
SELECT * FROM t5 WHERE num = 1; # 二进制显示查询结果
P 15 列类型之小数型 2022/7/2
1.小数的基本使用
案例演示：
# 演示decimal类型、float、double使用
# 创建表
CREATE TABLE t6 (
num1 FLOAT, # 数值类型为float
num2 DOUBLE, # 数值类型为double
num3 DECIMAL(30,20)); # decimal可以存放很大的数
# 添加数据
INSERT INTO t6 VALUES(88.12345678912345,88.123456789123456,88.123456789123456)
SELECT * FROM t6;
DROP TABLE t7; # 删除表t7
CREATE TABLE t7(
num DECIMAL(65));
INSERT INTO t7 VALUES(797787897897897897897989789879745946959);
SELECT * FROM t7;
# decimal与bigint对比，t7与t8
CREATE TABLE t8(
num BIGINT UNSIGNED) # 使用bigint对比能不能存放长数据
INSERT INTO t8 VALUES(797787897897897897897989789879745946959);
# bigint查询结果,显示超出范围了，decimal的范围更大
1 queries executed, 0 success, 1 errors, 0 warnings
查询：INSERT INTO t8 VALUES(797787897897897897897989789879745946959)
错误代码： 1264
Out of range value for column 'num' at row 1
执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.002 sec
P 16 列类型之字符串 (1) 2022/7/8
1.字符串的基本使用
# 演示字符串类型使用char,varchar
-- CHAR(size)
-- 固定长度字符串 最大255 字符
-- varchar(size) 0~65535
-- 可变长度字符串 最大65532字节 【utf8编码最大21844字符 ，1-3个字节用于记录大小】
-- 如果表的编码是utf8 varchar(size) ,3个字节表示一个字符，则size=(65535-3)/3=21844
-- 如果表的编码是gbk varchar(size) ,2个字节表示一个字符，则size=(65535-3)/2=32766
CREATE TABLE t09 (
`name` CHAR(255)); # 最大255
CREATE TABLE t10 (
`name` VARCHAR(32766)) CHARSET gbk;
DROP TABLE t10;
CREATE TABLE t11 (
`name` VARCHAR(32767)) CHARSET gbk; # 用于测试varchar编码范围
P 17 列类型之字符串(2) 2022/7/9
1.字符串使用细节(一)
细节演示1：
# 演示字符串列类型的使用细节
# char(4)和varchar(4)这个4表示的是字符，而不是字节
#
# char(4)的测试：
CREATE TABLE t11(
`name` CHAR(4));
INSERT INTO t11 VALUES('abcd');
INSERT INTO t11 VALUES('abcde'); # 超过五个字符报错
INSERT INTO t11 VALUES('你好在干'); # 不区分字符是字母还是汉字
SELECT * FROM t11;
# varchar(4)的测试：
CREATE TABLE t12(
`name` VARCHAR(4));
INSERT INTO t12 VALUES('abcde');
INSERT INTO t12 VALUES('abcd');
INSERT INTO t12 VALUES('你好在干吗');
INSERT INTO t12 VALUES('ab北京');
SELECT * FROM t12;
2.字符串使用细节(二)
3.字符串使用细节(三)
4.字符串使用细节(四)
细节演示：
# 如果varchar不够用，可以考虑使用mediumtext,或者longtext
# 如果想简单一点，可以直接使用text
CREATE TABLE t13( content TEXT, content2 MEDIUMTEXT , content3 LONGTEXT);
# 字符串使用单引号
INSERT INTO t13 VALUES(
'张吉超学习', '张吉超学习100','张吉超学习1000~~' );
SELECT * FROM t13;
P 18 日期类型 2022/7/12
1.日期类型基本使用
代码演示：
# 演示时间相关的案例
# 创建一张表，date,datetime,datestamp
CREATE TABLE t14 (
birthday DATE ,-- 记录 生日，年月日
job_time DATETIME, -- 记录 年月日，时分秒
logi_time TIMESTAMP -- 登录时间,如果希望log_time自动更新，需要配置
NOT NULL -- 非空
DEFAULT CURRENT_TIMESTAMP -- DEFAULT CURRENT_TIMESTAMP 默认当前时间
ON UPDATE CURRENT_TIMESTAMP); -- 在修改的时候自动以当前时间填充
SELECT * FROM t14;
INSERT INTO t14(birthday,job_time)
VALUES('2022-7-12','2022-7-12 10:10:10');
-- 如果我们更新t14 表的某条吉记录，login_time列会自动以当前时间进行更新
INSERT INTO t14(
birthday)
VALUES('2019-7-7');
相关结果截图：
P 19 创建表练习 2022/7/12
1.练习
**练习一：**创建一个员工表emp(课堂练习)，选用适当的数据类型 createtable.sql,字段如下：
代码演示：
# 创建表的课堂练习
字段 属性
-- id 整型
-- name 字符串
-- sex 字符型
-- birthday 日期型 (date)
-- entry_date 日期型(date)
-- job 字符型
-- salary 小数型
-- resume 文本型
CREATE TABLE `emp` (
id INT ,
`name` VARCHAR(32),
sex CHAR(1),
birthday DATE,
entry_date DATETIME,
job VARCHAR(32),
salary DOUBLE,
`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
-- 添加记录
INSERT INTO `emp` VALUES(
100,'小妖怪','男','2002-9-15','2022-7-12','搬砖', 2500,'搬砖挣钱');
SELECT * FROM `emp`;
相关结果截图：
P 20 修改表 2022/7/13
1.修改详细指令
2.应用实例
代码演示：
# 员工表emp的表上增加一个image列，varchar类型(要求在resume后面)
ALTER TABLE emp -- 修改一个表
ADD image VARCHAR(32) NOT NULL DEFAULT ''
-- 增加一个列为image，数据类型为varchar，非空，默认给一个空的字符
AFTER RESUME -- 在resume 后面
SELECT * FROM emp;
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` name_user VARCHAR(32) NOT NULL DEFAULT ''
DESC employee;
相关截图结果：
P 21 insert基本使用 2022/7/14
1.基本使用
代码演示：
# 练习1: insert语句
-- 创建一张商品表goods (id int, goods_name varchar(10),price double );
-- 添加两条记录
CREATE TABLE `goods` (
id INT ,
goods_name VARCHAR(10),
price DOUBLE);
INSERT INTO `goods` (id, goods_name, price) -- 全部添加可不带，单独添加需要列出列名
VALUES(10,'华为手机', 2000);
INSERT INTO `goods` (id ,goods_name, price)
VALUES(20, '苹果手机',3000);
SELECT * FROM goods;
# 练习2：向employee表中添加2个员工信息
SELECT * FROM employee;
INSERT INTO `employee` (id, name_user, birthday,entry_date,job,salary,`resume`)
VALUES(200, '开心超人', '2020-09-15','2022-7-14 16:36:30','打怪兽',5000,'开心每一天');
相关结果截图：
P 22 insert 注意事项 2022/7/16
1.注意事项
# insert 语句的细节
-- 1.插入的数据应与字段的数据类型相同，例如：把'abc'添加到int类型会错误。
INSERT INTO `goods` (id,goods_name,price)
VALUES('30','锤子手机',2500); # 30加上''，是可以被转成整型的
INSERT INTO `goods` (id,goods_name,price)
VALUES('abc','锤子手机',2500); # 执行会错误，然而abc是不可以被转成整型的
-- 2.数据的长度应在列的规范范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
INSERT INTO `goods` (id,goods_name,price)
VALUES(40,'VIVO手机VIVO手机VIVO手机VIVO手机VIVO手机',2500); # varchar(10),太长无法添加
-- 3.在values中列出的数据位置必须与被加入的列的排列位置相对应。
INSERT INTO `goods` (id,goods_name,price)
VALUES('oppo手机',50,2500); # 必须与之相对应，本质还是是否与数据类型相同问题
# id 对应50，goods_name对应 vivo手机，price对应2500
-- 4.字符和日期数据类型应该包含在单引号中。
INSERT INTO `goods` (id,goods_name,price)
VALUES(50,oppo手机,2500); # 未加单引号执行会报错，"未知列“oppo”手机' 在“字段列表”中"
-- 5.列可以插入空值[前提是该字段允许为空]，insert into table_name value(null)
INSERT INTO `goods` (id,goods_name,price)
VALUES(50,'oppo手机',NULL); # 把price为空值，取决于创建price列是否允许为空，
CREATE TABLE zjc01 (
price DOUBLE NOT NULL); # 创建时如果设置NOT NULL非空，则不允许
SELECT * FROM zjc01;
INSERT INTO zjc01
VALUES(NULL); # 执行报错：“价格”列不能为空
-- 6.insert into tab_name (列名...) values(),(),() 形式添加多条记录
INSERT INTO `goods` (id,goods_name,price)
VALUES(60,'天迈手机',4000),(70,'红米手机',5000); # 添加了两条记录
-- 7.如果给表中的所有字段添加数据，可以不写前面的字段名称。
INSERT INTO `goods`
VALUES(80,'小辣椒手机',NULL);
-- 8.默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否组报错。
INSERT INTO `goods` (id,goods_name) # 只对表中添加了两条数据
# 如果某个字段没有指定NOT NULL，当添加数据时没有给定值，会默认给NULL
VALUES(100,'格力手机');
# 如果我们希望指定某个列的默认值，可以在创建表的时候指定
CREATE TABLE `zjc02` (
price DOUBLE NOT NULL DEFAULT 100);
ALTER TABLE `zjc02`
ADD `name` VARCHAR(10);
INSERT INTO `zjc02` (`name`) # 没有给price列指定任何数值，会自动填充默认的值
VALUES('苹果手机');
SELECT * FROM `zjc02`;
P 23 update语句 2022/7/17
1.update 语句用法
- 使用update语句修改表中数据
代码演示：
-- 演示update 语句
-- 要求：在上面创建的employee表中修改表中的记录
SELECT * FROM employee;
-- 1.将所有员工的薪水修改为5000元
UPDATE employee SET salary = 5000 # 没有写where条件统一将所有员工的薪水改为5000(要慎重)
-- 2.将姓名 小妖怪 的员工薪水修改为3000元
UPDATE employee
SET salary = 3000
WHERE name_user = '小妖怪';
-- 3.将 老妖怪 的薪水在原有的基础上增加1000元
INSERT INTO employee (name_user,salary)
VALUES('老妖怪',2000);
UPDATE employee
SET salary = salary + 1000 # 原来的基础增加了1000块
WHERE name_user = '老妖怪';
-- 细节演示：可以修改多个列
UPDATE employee
SET salary = salary + 1000 ,job = '打酱油的' # 同时修改两列
WHERE name_user = '老妖怪';
2.update语句使用细节
P 24 delete语句 2022/7/18
1.delete删除记录
代码演示：
-- delete语句的演示
SELECT * FROM employee;
-- 删除表中名称为老妖怪的记录 。
DELETE FROM employee
WHERE name_user = '老妖怪';
-- 删除表中所有记录。
DELETE FROM employee;
-- delete语句不能删除某一列的值(可以使用update设为null 或者'')
UPDATE employee SET `resume` = '' WHERE name_user = '老妖怪' ; -- 将resume列置空
2.delete语句使用细节
-
如果不使用where语句将删除表中所有数据
-
delete语句不能删除某一列的值(可以使用update设为null 或者’')
-
使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table 语句。drop table 表名；
P 25 select 语句(1) 2022/7/19
1.基本使用方法（一）
代码演示：
-- select 语句【重点、难点】
-- ****创建新的表(student)****
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);
SELECT * FROM student;
-- 练习：
-- 查询表中所有学生信息。
SELECT * FROM student;
-- 查询表中所有学生 姓名 和对应的 英语 成绩。
SELECT `name`,english FROM student;
-- 过滤表中重复数据 distinct 。
SELECT `english` FROM
SELECT DISTINCT `english` FROM student; -- DISTINCT 如果重复的就保留一行【去重】
-- 要查询的记录，每个字段都相同，才会去重。
SELECT DISTINCT `name`,`english` FROM student; -- 需要每一列相同，才会去重，否则不会。
P 26 select 语句(2) 2022/7/19
1.基本使用方法（二）
代码演示：
-- select 语句的使用（二）
-- 统计每一个学生的总分
SELECT `name` ,(chinese + english + math + 10)FROM student; -- 三科加一起
-- 使用别名表示学生分数。
SELECT `name` AS '名字' ,
(chinese + english + math + 10)AS toltal_score -- 在原名AS+别名
FROM student;
相关结果截图：
P 27 select 语句(3) 2022/7/20
1.基本使用方法（三）
- 在where子句中经常使用的运算符
案例演示：使用where语句进行过滤查询
-- 使用where语句进行过滤查询
SELECT * FROM student;
-- 查询姓名为赵云的学生成绩
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 math > chinese;
-- 查询总分大于200 并且 数学成绩大于语文成绩的姓韩的同学
SELECT * FROM student
WHERE (math + chinese + english) > 200 AND
math > chinese AND
`name` LIKE '韩%'; # 后面 % 百分号代表后面不管是谁，匹配任意一个字符
# 代表 0-∞，韩一个字也可以匹配
-- 查询英语分数在80-90之间的同学 （在是一个区间）
SELECT * FROM student
WHERE english >= 80 AND english <=90; -- 方法一：多条件
SELECT * FROM student
WHERE english BETWEEN 80 AND 90; -- 方法二：between … and … 是一个闭区间[80,90]
-- 查询数学分数为89，90 ，91的同学。
SELECT * FROM student
WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student
WHERE math IN (80,90,91);
-- 查询所有姓张的学生成绩。
SELECT * FROM student
WHERE `name` LIKE '张%';
-- 查询数学分大于80，语文分大于80的同学。
SELECT * FROM student
WHERE math > 80 AND chinese > 80;
小练习：
P 28 select 语句(4) 2022/7/21
1.基本使用方法（四）
练习演示：
-- 演示order by的使用
-- 对数学成绩排序后输出【升序】
SELECT * FROM student
ORDER BY math;
-- 对总分按从高到低的顺序输出 【降序】
SELECT * FROM student
ORDER BY (math + chinese + english) DESC;
SELECT `id`,`name`,(chinese + math + english) AS total_score FROM student
ORDER BY total_score DESC;
-- 对姓李的学生成绩排序输出【升序】
SELECT `id`,`name`,(chinese + math + english) AS toltal_name FROM student
WHERE `name` LIKE '张%'
ORDER BY toltal_name; # 按照总分升序排序,将toltal_name换成（chinese + math + english）也可以，但是推荐前者，显示更清晰明了
INSERT INTO student # 重新插入一条记录
VALUES(9,'张良',90,89,78);
P 29 统计函数 2022/7/22
1.统计函数count
代码演示：
-- 演示mysql中统计函数的使用
-- conunt 函数的演示
SELECT * FROM student;
-- 1.统计一个班级共有多少个学生？
SELECT COUNT(*) FROM student; # 统计所有数据
-- 2.统计数学成绩大于90的学生有多少个？
UPDATE student
SET `math` = 99
WHERE `name` = '张良' # 更新一下数据，利于查询
SELECT COUNT(*) FROM student
WHERE `math` > 90
-- 3.统计总分大于250的人数有多少？
SELECT COUNT(*) FROM student
WHERE (chinese + math + english) > 250;
-- count(*) 和 count (列)的区别: 演示如下
-- 解释：
-- count(*) ：返回满足条件记录的 行数
-- count(列) ：统计满足条件的 某列 有多少个，但是会排除 为null ；
CREATE TABLE t15(
`name` VARCHAR(20));
INSERT INTO t15 VALUES('jake');
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);
SELECT COUNT(*) FROM t15; -- 4
SELECT COUNT(`name`) FROM t15; -- 3 ,会排除空值，返回的是非空的，所以为3
2.求和函数sum
**简介：**sum函数满足where条件的行的和，一般使用在数值列
代码演示：
-- 演示sum函数的使用
-- 1.统计一个班数学总成绩
SELECT SUM(math) FROM student;
-- 2.统计一个班语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_totla_score,SUM(english),SUM(chinese) FROM student;
-- 3.统计一个班语文、英语、数学的成绩总和
SELECT SUM(math + chinese + english) AS exam_total FROM student;
-- 4.统计一个班语文成绩平均分
SELECT SUM(chinese)/COUNT(*) FROM student; -- sum计算出总分数除以count(*)计算的总人数得平均成绩
3.平均值函数avg
代码演示：
-- 演示avg的使用
-- 练习：
-- 1.求一个班级数学平均分？
SELECT AVG(math) FROM student;
-- 2.求一个班级总分的平均分？
SELECT AVG(math + english + chinese) FROM student;
4.最大/小值max/min
代码演示：
-- 演示max和min的使用
-- 1.求班级最高分和最低分
SELECT MAX(math + english + chinese),MIN(math + chinese + english) FROM student;
-- 2.求班级的数学最高分和最低分
SELECT MAX(math) AS math_high_score,MIN(math) AS math_low_score FROM student;
P 30 分组统计 2022/7/23
1.group by / having
代码演示及其说明：
-- 演示group by + having
-- group by 子句用于对查询的结果分组统计
-- having子句用于限制分组显示结果（一般情况下，where用于过滤数据行，为having用于过滤分组）
CREATE TABLE dept( /*部门表*/
depno 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,'OPERATTIONS','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-31' , 950.00 , NULL, 30) ,
(7902, ' FORD','IANALYST' , 7566, '1991-12-31' ,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 salgrade; -- 工资级别表
SELECT * FROM emp; -- 员工表
SELECT * FROM dept; -- 部门表
-- 1.如何显示每个部门的最高工资和平均工资？
-- 分析：avg(sal) max(sal)
SELECT AVG(sal),MAX(sal),deptno
FROM emp GROUP BY deptno; -- group by deptno按照部门分组查询
-- 使用数学方法，保留两位小数
SELECT FORMAT(AVG(sal),2),MAX(sal),deptno
FROM emp GROUP BY deptno;
-- 2.显示每个部门的每种岗位的平均工资和最低工资？
-- 分析：* 显示每个部门的平均工资和最低工资
-- * 显示每个部门的每种岗位的平均工资和最低工资
SELECT MIN(sal) AS min_sal,AVG(sal)AS avg_sal,deptno,job -- 首先对部门进行分组，然后再对岗位
FROM emp GROUP BY deptno,job;
-- 3.显示平均工资低于2000的部门号和它的平均工资 //别名
-- 分析：[写sql语句的思路是化繁为简，各个击破]
-- * 显示各个部门的平均工资和部门号
SELECT AVG(sal),deptno
FROM emp GROUP BY deptno;
-- * 在上面分析的记过进行一个过滤，保留 平均工资小于2000的
-- * 使用别名进行过滤
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;
-- 如何理解分组？
-- 答：如果不使用分组，那么函数返回的数据只有一条，统计表内所有数据；
-- 如果使用分组，返回的数据有组的个数条（组个数条=返回的数据条数），函数分别对各组进行统计。
P 31 字符串函数 2022/7/24
1.字符串相关函数
代码演示：
-- 演示字符串相关函数的使用，使用emp表来进行演示
SELECT * FROM emp;
-- 1. CHARSET(str) 返回字符集
SELECT CHARSET(ename) FROM emp; -- ename的字符集是utf8的
-- 2.CONCAT (string2 [,...]) 连接字串，将多个列拼接成一列
SELECT CONCAT(ename,' 工作是 ',job) FROM emp; -- 结果显示 某某 工作是 某某；多列拼接
-- 3.INSTR (string,substring) 返回substring在string中出现的位置，没有返回0
SELECT INSTR ('zhangjichao','chao') FROM DUAL ; -- DUAL是系统做测试的一个表，自己创建一个也可。
-- DUAL 亚元表，是一个系统的表，可以作为测试表使用
-- 4.UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp; -- 将表中ename全部转成大写的
-- 5.LCASW (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp;
-- 6.LEFT (string2 ,length ) 从string2中的左边起取length个字符
SELECT LEFT(ename, 2) FROM emp;
-- 7.RIGHT (string2 ,length ) 从string2中的右边起取length个字符
SELECT RIGHT(ename,2) FROM emp;
-- 8.LRNGTH (string ) string长度 [按照字节返回]
SELECT LENGTH(ename) FROM emp;
-- 9.REPLACE (str ,search_str ,replace_str)
-- 在str中用replace_str替换search_str
-- 例如：如果是 MANAGER 就替换成 经理
SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp;
-- 10.STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('hsp','hsp') FROM DUAL; -- 相同返回的数值则是0
-- 11.SUBSTRING (str , position [,length])
-- 从str 的position开始【从1开始计算】，取length个字符
-- 取出ename 列的第一个位置开始取出2个字符，如不写去多少个字符就是后面取完
SELECT SUBSTRING(ename,1, 2) FROM emp;
-- 12.LTRIM (string2 ) RTRIM (string2 ) 去除前端空格或者后端空格
-- TRIM 两端空格都去
SELECT LTRIM(ename) FROM emp; -- 去除左边空格
SELECT LTRIM(' 小张和小明') FROM DUAL;
SELECT RTRIM(ename) FROM emp; -- 去除右边空格
SELECT RTRIM('小张和小明 ') FROM DUAL;
SELECT TRIM(ename) FROM emp; -- 去除两端空格
SELECT TRIM(' 小张和小明 ') FROM DUAL;
P 32 字符串函数练习 2022/7/25
1.题目解析
-- 题目：以首字母小写的方式显示所有员工emp表的姓名，使用两种方式--函数的嵌套
-- 解析：先取出ename的第一个字符，转成小写的；然后把它后面的字符串拼接输出即可
-- 第一种：
SELECT CONCAT(LCASE(SUBSTRING(TRIM(ename),1,1)),SUBSTRING(ename,2)) AS new_name
FROM emp;
-- 第二种：
SELECT CONCAT((LCASE(LEFT(TRIM(ename),1))),SUBSTRING(TRIM(ename),2)) AS new_name
FROM emp;
P 33 数学函数 2022/7/26
1.数学相关函数
相关代码演示：
-- 演示数学相关函数
-- 1.ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL;
-- 2.BIN(decimal_number) 十进制转二进制
SELECT BIN(10) FROM DUAL;
-- 3.CEILING (number2 ) 向上取整，得到比num2 大的最小整数
SELECT CEILING(1.1) FROM DUAL; -- 返回2（比1.1大的最小整数）
-- 4.CONV(number2,from_base,to_base) 进制转换
-- 解析：下面含义8是十进制的8，转换成二进制输出
SELECT CONV(8,10,2) FROM DUAL;
-- 解析：下面含义是16进制的16，转化成二进制的输出
SELECT CONV(16,16,10) FROM DUAL;
-- 5.FLOOR (number2 ) 向下取整，得到比num2 小的最大整数
SELECT FLOOR(1.1) FROM DUAL; -- 返回1（比1.1小的最大整数）
-- 6.FORMAT (number,decimal_place) 保留小数位数（四舍五入）
SELECT FORMAT(66.438754,2) FROM DUAL; -- 输出66.44
-- 7.HEX (decimal_number ) 转十六进制
SELECT HEX(16) FROM DUAL;
-- 8.LEAST (number , number2 [,...]) 求最小值
SELECT LEAST(0,1,-10,4) FROM DUAL;
-- 9.MOD (numerator,denominator ) 求余
SELECT MOD(10,3) FROM DUAL; -- 余1
-- 10.RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
SELECT RAND() FROM DUAL;
-- 希望随机数固定加一个seed（种子），种子改变，范围改变
SELECT RAND(3) FROM DUAL;
P 34 日期函数（一） 2022/7/27
1.日期时间相关的函数
代码演示及相关结果截图:
-- 日期时间相关函数
-- 1.CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL;
-- 2.CURREN_TIME ( ) 当前时间
SELECT CURRENT_TIME() FROM DUAL;
-- 3.CURRENT_TIMESTAMP 当前时间戳
SELECT CURRENT_TIMESTAMP FROM DUAL;
-- 创建测试表
SELECT * FROM mes;
CREATE TABLE mes(
id INT ,
content VARCHAR(30),
sendtime DATETIME);
INSERT INTO mes
VALUES(1,'北京新闻',CURRENT_TIMESTAMP()); -- 写入数据时写入
INSERT INTO mes
VALUES(2,'上海新闻',NOW());
INSERT INTO mes
VALUES(3,'广州新闻',NOW());
SELECT NOW() FROM DUAL; -- NOW()返回的是当前的时间 年月日 时分秒
-- 应用实例
-- 1.显示所有新闻信息，发布日期只显示日期，不用显示时间。
SELECT id,content,DATE(sendtime) FROM mes;
-- 2.请查询在10分钟内发布的新闻
SELECT * FROM mes
WHERE DATE_ADD(sendtime,INTERVAL 10 MINUTE) >= NOW();
-- 发送时间增加十分钟大于或者等于则是10分钟内发布
SELECT * FROM mes
WHERE sendtime >= DATE_SUB(NOW(),INTERVAL 10 MINUTE);
-- 发送时间大于或等于现在时间减去10分钟则是10分钟内发布
-- 3.请在mysql 的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL; -- date1 减去 date2
-- 4.请用mysql 的sql语句求出你活了多少天？[练习]
SELECT DATEDIFF(NOW(),'2002-09-15') FROM DUAL;
-- 5.如果你能活到80岁，求出你还能活多少天？[练习]
-- 解析：先求出活80时，是什么日期 X；然后在使用datediff(x,now());
SELECT DATEDIFF(DATE_ADD('2002-09-15',INTERVAL 80 YEAR),NOW()) FROM DUAL;
-- 先算出活到80岁是什么时候，然后减去现在的时间日期，就可以得到剩余时间
-- 6.计算出两个时间的差 TIMEDIFF('','')
SELECT TIMEDIFF('11:11:11','6:23:14') FROM DUAL;
P 35 日期函数（二） 2022/7/27
1.细节说明
P 36 日期函数（三） 2022/7/28
1.日期函数补充
代码演示以及相关结果截图：
-- 日期函数（三）
-- 1.YEAR | MONTH | DAY | DATE (datetime)
SELECT YEAR(NOW())FROM DUAL; -- 只得到年
SELECT MONTH(NOW())FROM DUAL; -- 只得到月
SELECT DAY(NOW())FROM DUAL; -- 只得到多少号
SELECT YEAR('2021-09-15') FROM DUAL;
-- 2.unix_timestamp() : 返回的是1970-01-01到现在的秒数
SELECT UNIX_TIMESTAMP()/ FROM DUAL;
-- 3.FROM_UNIXTIME() :可以把unix的秒数转换成日期指定格式的日期
-- %Y-%y-%d %H:%i:%s 是一个固定格式
-- 意义：在开发中可以存放一个整数表示一个时间，通过FROM_UNIXTIME；
-- 在实际开发中我们常用使用int来保存一个unix时间戳，然后使用 from_unixtime()进行转换，还是非常有实用价值的
SELECT FROM_UNIXTIME(1658998724,'%Y-%m-%d %H:%i:%s') FROM DUAL; -- 1970-1-1到现在的
P 37 加密函数 2022/7/29
1.加密和系统函数
代码演示以及相关截图：
-- 演示加密函数和系统函数
-- 1.USER() 查询用户
-- 可以查询登录MySQL的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL; -- 用户@IP地址
-- 2.DATABASE() 查询当前使用数据库名称
SELECT DATABASE();
-- 3.MD5(str) 为字符串算出一个 MD5 32的字符串，常用于（用户密码）加密
-- root 密码是 hsp -> 加密MD5 -> 在数据库中存放的是加密后的密码
SELECT MD5('zjc') FROM DUAL;
SELECT LENGTH(MD5('dhsahfjkhaskgds')) FROM DUAL; -- 无论密码多长都是32位字符串
-- 演示用户表，存放密码，是md5
SELECT * FROM `users`;
CREATE TABLE users(
id INT,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT ''); -- char 定长字符串
INSERT INTO users
VALUES(100,'张三',MD5('zhangsan')); -- 密码未明文存放
-- 查询张三的时候需要使用MD5
SELECT * FROM users -- sql注入问题
WHERE `name` = '张三' AND pwd = MD5('zhangsan');
-- 4.PASSWORD(str) -- 加密函数,mysql数据库的密码就是password加密
SELECT PASSWORD('zjc') FROM DUAL; -- 查询的与数据库密码加密后相同，所以是使用 password加密
-- 数据库密码 *ABDF6F6B02CB7264D724FD6A4753516A9331E01A
-- select * from mysql.user \G 从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表(可以不切换数据库查询)
SELECT * FROM mysql.user
P 38 流程控制函数 2022/7/29
1.流程控制函数演示
需求相关：
相关函数：
代码演示以及相关截图：
# 流程控制语句
-- 1.IF(expr1,expr2,expr3) 如果expr为TURE,则返回expr2，否则返回expr3
SELECT IF(TRUE,'上海','北京') FROM DUAL; -- true 返回的 上海
SELECT IF(FALSE,'上海','北京') FROM DUAL; -- FALSE 返回的 北京
-- 2.IFNULL(expr1,expr2) 如果expr不为空null，则返回expr1,否则则返回expr2
SELECT IFNULL(NULL,'张三') FROM DUAL; -- 为空 返回 张三
SELECT IFNULL('zhangsan','张三') FROM DUAL; -- 不为空返回 zhangsan
-- 3.SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;[类似多重分支]
-- 如果expr1 为expr2 ,则返回expr2，否则若expr3为expr4,则返回expr4,否则返回expr5。
SELECT CASE
WHEN expr1 THEN expr2
WHEN expr3 THEN expr4
ELSE expr5 END;
SELECT CASE
WHEN FALSE THEN 'jack'
WHEN TRUE THEN 'mary' -- 返回mary
ELSE 'tom' END;
-- 案例演示：
-- 1.查询emp表，如果comm是 null，则显示0.0
-- 注意：判断是否为空，要是用is null,判断不为空是用is not null,不可以是用=等号
-- 方法一：
SELECT ename,IF(comm IS NULL,0.0,comm) AS comm FROM emp;
-- 方法二:
SELECT ename,IFNULL(comm,'0.0')AS comm FROM emp;
-- 2.如果emp表 job 是 CLERK 则显示职员，如果是MANAGER 则显示经理
-- 如果 SALESMAN 则显示销售人员，其他显示正常
SELECT * FROM emp;
SELECT ename,(SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS job
FROM emp;
SELECT ename,IF(TRIM(job) = 'CLERK', '职员',
IF(TRIM(job) = 'MANAGER', '经理',
IF(TRIM(job) = 'SALESMAN', '销售人员', job)))
AS job FROM emp;
P 39 查询增强 2022/7/29
1.查询增强练习
简介：
案例一：
代码演示：
-- 查询加强
select * from emp;
select * from dept;
select * from salgrade;
-- ● 是用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 ;
P 40 分页查询 2022/7/29
1.分页查询增强
代码演示以及相关截图：
-- 分页查询
-- 1.按雇员的id号升序取出，每页显示3条记录，请分别显示第一页，第二页，第三页。
-- 第一页
SELECT * FROM emp
ORDER BY empno
LIMIT 0,3;
-- 第二页
SELECT * FROM emp
ORDER BY empno
LIMIT 3,3;
-- 第三页
SELECT * FROM emp
ORDER BY empno
LIMIT 6,3;
-- 推到一个公式
SELECT * FROM emp
ORDER BY empno
LIMIT 每页显示的记录数 * （第几页-1）,每页显示记录数
-- 练习：
-- 按雇员的empno号降序取出，每页显示5条记录。分别显示第三页，第五页对应的sql语句
-- 第三页：
SELECT * FROM emp
ORDER BY empno DESC
LIMIT 10,5;
-- 第五页：
SELECT * FROM emp
ORDER BY empno DESC
LIMIT 20,5;
P 41 分组增强 2022/7/30
1.分组函数加强
案例：
代码演示以及相关截图：
-- 增强group by 的使用
-- 1.显示每种岗位的雇员总数，平均工资。
SELECT COUNT(*),job,AVG(sal)
FROM emp
GROUP BY job;
-- 2.显示雇员的总数，以及获得补助的雇员数。
SELECT COUNT(`comm`),COUNT(*) FROM emp; -- count(列)，如果该列的值为null，不会统计
-- 扩展要求：统计没有获得补助的的雇员数
-- 解析：如果comm为空，则返回1，否则为空，为空count(列)函数就不会进行统计
SELECT COUNT(IF(comm IS NULL,1,NULL)) AS not_comm,COUNT(*) FROM emp;
SELECT COUNT(*) - COUNT(comm) AS not_comm,COUNT(*) FROM emp; -- 总的减去获得奖金的
-- 3.显示管理者的总人数。
SELECT COUNT(DISTINCT mgr) FROM emp; -- distinct 进行去重
SELECT DISTINCT mgr FROM emp;
-- 4.显示雇员工资的最大差额。
-- 分析：max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal) FROM emp;
P 42 多子句查询 2022/7/30
1.数据分组的总结
代码演示以及相关截图：
-- 增强group by 的使用
-- 1.显示每种岗位的雇员总数，平均工资。
SELECT COUNT(*),job,AVG(sal)
FROM emp
GROUP BY job;
-- 2.显示雇员的总数，以及获得补助的雇员数。
SELECT COUNT(`comm`),COUNT(*) FROM emp; -- count(列)，如果该列的值为null，不会统计
-- 扩展要求：统计没有获得补助的的雇员数
-- 解析：如果comm为空，则返回1，否则为空，为空count(列)函数就不会进行统计
SELECT COUNT(IF(comm IS NULL,1,NULL)) AS not_comm,COUNT(*) FROM emp;
SELECT COUNT(*) - COUNT(comm) AS not_comm,COUNT(*) FROM emp; -- 总的减去获得奖金的
-- 3.显示管理者的总人数。
SELECT COUNT(DISTINCT mgr) FROM emp; -- distinct 进行去重
SELECT DISTINCT mgr FROM emp;
-- 4.显示雇员工资的最大差额。
-- 分析：max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal) FROM emp;
-- 应用案例：请统计各个部门(group by)的平均工资(avg)，
-- 并且是大于1000的(having )，并且按照平均工资从高到低排序(order by desc)
-- 取出前两行记录(limit)
SELECT FORMAT(AVG(sal),2) AS avg_sal,deptno FROM emp
GROUP BY deptno
HAVING avg_sal > FORMAT(1000,2) -- format(1000,2)大于1000，保留两位小数
ORDER BY avg_sal DESC
LIMIT 0,2
P 43 多表笛卡尔集 2022/7/30
1.多表查询笛卡尔集
问题的引出：
简介:
代码演示以及相关截图：
-- 多表查询
-- ？显示雇员名，雇员工资以及所在部门的名字[笛卡尔集]
-- 分析：1.雇员名，雇员工资来自 emp 表
-- 2.部门的名字来自 dept 表
-- 3.需要对emp 和dept 查询
-- 4.当我们需要显示某个表的列是，格式需要 表.列表
SELECT ename,sal,dname,emp.deptno -- 表中有两个相同的deptno，需要加上表.列名
FROM emp,dept
WHERE emp.deptno = dept.deptno;
-- 小提示 ：多表查询条件不能少于表的个数减一，否则会出现笛卡尔集
-- 例如：三张表查询，查询条件不能少于3-1=2；四张表查询，查询条件不能少于4-1=3，否则会出现笛卡尔集现象
P 44 多表查询 2022/7/30
1.多表查询练习
-- ？如何显示部门号为10的部门名、员工名和工资。
SELECT ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno AND dept.deptno = 10;
-- ? 显示各个员工的姓名，工资，及其工资级别。
-- 思路：姓名、工资 来自于 emp表；工资级别来自salgrade
-- 写sql,先写简单的，在写过滤条件
SELECT ename,sal,grade FROM emp,salgrade
WHERE sal BETWEEN losal AND hisal;
-- 练习：显示雇员名，雇员工资及所在部门的名字，并按部门排序[降序]
SELECT ename,sal,emp.deptno,dname
FROM emp,dept
WHERE emp.deptno = dept.deptno
ORDER BY dname DESC;
P 45 自连接 2022/7/30
1.自连接
简介：自连接是指在同一张表的连接查询[将同一张表看做两张表]
自连接的特点：
- 把同一张表当做两张表使用
- 需要给表取别名，格式：表 表别名
代码演示以及相关截图：
-- 多表查询的自连接
-- 思考题：显示公司员工名字和他上级的名字
SELECT worker.ename AS '职员名', boss.ename AS '上级名'
FROM emp worker ,emp boss -- 表别名格式 表 表列明；列名取别名需要加AS
WHERE worker.mgr = boss.empno; -- 需要的是工人的管理编号等于老板编号，其他的过滤掉
P 46 多行子查询 2022/7/31
1.子查询
代码演示以及相关截图：
-- 子查询演示
-- 请思考：如何显示与SMITH同一部门的所有员工？
/*
1.先查询到SMITHD部门号得到
2.把查询到的SMITH部门号当做一个子查来使用
*/
SELECT deptno FROM emp
WHERE ename = 'SMITH'; -- 当做子查询,返回一行也就叫做 单行子查询
-- 完整语句：
SELECT * FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH');
-- 练习：如何查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号，但是不含10号部门自己的雇员。
/*
1.查询到10号部门有哪些工作
2.把查出来10号部门的结果当做子查询
*/
SELECT job FROM emp -- 多行子查询
WHERE deptno = 10;
-- 完整语句
SELECT ename,sal,job,deptno
FROM emp
WHERE job IN (
SELECT job FROM emp
WHERE deptno = 10) AND deptno != 10; -- != 或者<> 都为 不等于，都可
P 47 子查询临时表 2022/7/31
1.子查询临时表
代码演示以及相关截图：
-- 查询ecshop中各个类别中，价格最高的商品
-- 查询 商品表
-- 先得到 各个类别中，价格最高的商品 max + group by cat_id
-- 把子查询当做一张临时表可以解决很多很对复杂的查询
SELECT * FROM ecs_goods;
SELECT cat_id,MAX(shop_price)
FROM ecs_goods
GROUP BY cat_id
SELECT goods_id,temp.cat_id,goods_name,shop_price FROM
ecs_goods,( -- 子查询当做临时表来进行查询
SELECT cat_id,MAX(shop_price) AS max_price
FROM ecs_goods
GROUP BY cat_id) temp
WHERE temp.max_price = ecs_goods.shop_price
AND temp.cat_id = ecs_goods.cat_id;
P 48 all和any 2022/7/31
1.多行子查询all和any
代码演示以及相关截图：
-- all 和 any 的使用
-- 在多行子查询中使用all:
-- 请思考：显示工资比部门30的所有员工的工资高的员工姓名、工资和部门号。
SELECT ename,sal,deptno
FROM emp
WHERE sal > ALL( -- 大于全部 子查询30员工工资
SELECT sal
FROM emp
WHERE deptno = 30
)
SELECT ename,sal,deptno
FROM emp
WHERE sal > (
SELECT MAX(sal) -- 大于 30部门的最大工资
FROM emp
WHERE deptno = 30
)
-- 在多行子查询中使用any:
-- 请思考：如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号。
SELECT ename,sal,deptno
FROM emp
WHERE sal > ANY(
SELECT sal -- 大于任意 子查询30员工工资
FROM emp
WHERE deptno = 30
)
SELECT ename,sal,deptno
FROM emp
WHERE sal > (
SELECT MIN(sal) -- 大于 30部门的最低工资
FROM emp
WHERE deptno = 30
)
P 49 多列子查询 2022/7/31
1.多列子查询
以下多列：
代码演示以及相关截图：
-- 多列子查询
-- 请思考如何查询与ALLEN的部门和岗位完全相同的所有雇员(并且不含ALLEN本人)
-- (字段1， 字段2 ... )=(select 字段1，字段2 from 。。。)
-- 分析：1.得到ALLEN的部门和岗位
SELECT deptno,job FROM emp
WHERE `ename` = 'ALLEN';
-- 2.把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配。
SELECT * FROM emp
WHERE (deptno,job) = (
SELECT deptno,job FROM emp
WHERE `ename` = 'ALLEN') AND deptno != 'ALLEN';
-- ？查询和宋江数学、语文、英语成绩完全相同的学生
SELECT * FROM student;
SELECT math,chinese,english FROM student
WHERE `name` = '宋江'
SELECT * FROM student
WHERE (math,chinese,english) = ( -- 数学、语文、英语的顺序需要与多列子查询一致
SELECT math,chinese,english
FROM student
WHERE `name` = '宋江')
P 50 子查询练习 2022/7/31
1.练习
题目一：
代码演示以及相关截图：
-- 请思考：查找每个部门工资高于本部门平均工资的人的资料。
-- 这里要用到数据查询的小技巧，把一个子查询当做一个临时表使用。
-- 1.先得到每个部门的部门号，和对应的平均工资
SELECT deptno,AVG(sal) AS avg_sal FROM emp
GROUP BY deptno;
-- 2.把上面的结果当做一个子查询，和emp表进行多表查询
SELECT ename,avg_sal,sal,temp.deptno FROM emp,(
SELECT deptno,AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno) temp
WHERE emp.deptno = temp.deptno AND temp.avg_sal > emp.sal;
题目二：
代码演示以及相关截图：
-- 题目：？查找每个部门工资最高人的详细资料
SELECT deptno,MAX(sal) FROM emp
GROUP BY deptno;
SELECT ename,sal,temp.max_sal,emp.deptno FROM emp,(
SELECT deptno,MAX(sal) AS max_sal
FROM emp
GROUP BY deptno)temp
WHERE emp.sal = temp.max_sal AND emp.deptno = temp.deptno;
题目三：
代码演示以及相关截图：
-- 题目三：？查询每个部门的信息(包括：部门名，编号，地址)和人员数量。
-- 分析：
-- 1.部门名，编号，地址 来自 dept名
-- 2.各个部门的人员数量-->构建一个临时表
SELECT COUNT(*),deptno
FROM emp
GROUP BY deptno; -- 统计出各个部门的人数
-- 完整语句：
SELECT dname,temp.deptno,loc,per_num AS '人数' FROM dept,(
SELECT COUNT(*)AS per_num,deptno
FROM emp
GROUP BY deptno)temp
WHERE temp.deptno = dept.deptno;
-- 还有一种写法，表.* 表示将该表所有列都显示出来
-- temp.* 将temp表的所有列显示出来，可以简化sql语句
-- 在多表查询中，当多个表的列不重复时，才可以直接写列名
SELECT temp.*,dname,loc FROM dept,(
SELECT COUNT(*)AS per_num,deptno
FROM emp
GROUP BY deptno)temp
WHERE temp.deptno = dept.deptno;
P 51 表复制和去重 2022/8/1
1.表复制
- 自我复制数据（蠕虫复制）
有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。
代码演示以及相关截图：
-- 表的复制
-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。
CREATE TABLE my_tab01
(id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT);
DESC my_tab01;
SELECT * FROM my_tab01;
-- 演示如何自我复制
-- 1.先把emp表的记录复制到my_tab01
INSERT INTO my_tab01
(id ,`name`,sal,job,deptno)
SELECT empno,ename,sal,job,deptno FROM emp;
-- 2.自我复制
INSERT INTO my_tab01
SELECT * FROM my_tab01;
-- ？？？如何删除掉一张表重复记录
-- 1.先创建一张表 my_tab02,
SELECT * FROM my_tab02;
-- 2.让my_tab02有重复记录
CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把emp表的结构(列)，复制到my_tab02
DESC my_tab02;
INSERT INTO my_tab02
SELECT * FROM emp;
-- 3.考虑去重
/*
思路：
(1)先创建一张临时表my_tmp , 该表的结构和my_tab02 一样
(2)把my_tab02的记录通过distinct 关键字处理后 把记录复制到my_tmp
(3)清除掉my_tab02 记录
(4)把my_tmp 表的记录复制到my_tab02
(5)drop掉临时表my_tmp
*/
-- (1)
CREATE TABLE my_tmp LIKE my_tab02;
DESC my_tmp;
-- (2) 这步其实也可删除my_tab02表，重命名my_tmp表为my_tab02
INSERT INTO my_tmp
SELECT DISTINCT * FROM my_tab02;
-- (3)
DELETE FROM my_tab02;
-- (4)
INSERT INTO my_tab02
SELECT * FROM my_tmp;
-- (5)
DROP TABLE my_tmp;
SELECT * FROM my_tab02;
P 52 合并查询 2022/8/1
1.合并查询
代码演示以及相关截图：
-- 合并查询
SELECT ename,sal,job FROM emp WHERE sal>2500; -- 5条记录
SELECT ename,sal,job FROM emp WHERE job ='MANAGER'; -- 3
-- 1.union all 合并多个select语句的结果，不会去除重复项
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION ALL
SELECT ename,sal,job FROM emp WHERE job ='MANAGER';
-- 2.union 合并多个select语句的结果，会去除重复项
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION
SELECT ename,sal,job FROM emp WHERE job ='MANAGER';
P 53 外连接需求 2022/8/1
1.外连接
问题引出：
代码演示以及相关截图：
-- 外连接
-- 比如：？列出部门名称和这些部门的员工名称和工作，同时要求，显示哪些没有员工的部门。
-- 使用之前已经学过的多表查询的SQL，看看效果如何？
SELECT emp.deptno,dname,ename,job
FROM emp,dept
WHERE dept.deptno = emp.deptno
ORDER BY dname; -- 无法显示到没有员工的40部门，？可是需要显示
P 54 左外连右外连 2022/8/1
1.外连接详细
相关语法：
代码演示以及相关截图：
-- 外连接
-- 小提示：但是在实际开发环境中，大多数情况下使用的是前面学过的连接（内连接）。
-- 左外连接，右外连接
-- 创建stu
SELECT * FROM stu;
CREATE TABLE stu
( id INT ,
`name` VARCHAR(32));
INSERT INTO stu
VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
-- 创建exam
SELECT * FROM exam;
CREATE TABLE exam(
id INT,
grade INT);
INSERT INTO exam
VALUES(1,56),(2,76),(11,8);
-- 要求：
-- 1.使用左连接（显示所有人的成绩，如果没有成绩，也要显示该人的姓名和ID号，成绩显示为空）
SELECT `name`,stu.id,grade
FROM stu,exam
WHERE stu.id = exam.id -- 没有显示没有成绩的同学
-- 改成左外连接
SELECT `name`,stu.id,grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id
-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空）
-- 即：右边的表（exam） 和左边的表（stu）没有匹配的记录，也会把右表的记录显示出来
SELECT `name`,stu.id,grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id
-- 题目练习:
-- 列出部门名称和这些部门的员工信息（名字和工作），同时列出那些没有员工的部门名。
-- 使用右外连接
SELECT dname,job,ename,emp.deptno
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno;
-- 使用左外连接
SELECT dname,job,ename,emp.deptno
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno;
P 55 主键 2022/8/2
1.MySQL约束
基本介绍：约束用于确保数据库的数据满足特定商业规则。在mysql中，约束包括：not null、(唯一)unique，(主键)primary key,(外健)foreign key和(检查)check五种。
案例代码演示以及相关截图：
-- 主键的使用
-- 小提示:在实际开发中，每一个表往往都会设计一个主键。
-- id name email
CREATE TABLE t17
(id INT PRIMARY KEY, -- 表示id这一列是主键
`name` VARCHAR(32),
email VARCHAR(32));
-- 主键列的值是不可以重复的
INSERT INTO t17 VALUES(1,'jack','[email protected]');
INSERT INTO t17 VALUES(2,'tom','[email protected]');
INSERT INTO t17 VALUES(1,'zjc','[email protected]'); -- 1不可重复，所以会报错
SELECT * FROM t17;
-- ●主键使用的细节讨论
-- 1.primary key 不能重复而且不能为空 null
INSERT INTO t17 VALUES(NULL,'zjc','[email protected]'); -- 报错不可以为空
-- 2.一张表最多只能有一个主键，但可以是复合主键(比如这个id+name,两列加起来)
CREATE TABLE t18
(id INT PRIMARY KEY,
`name` VARCHAR(32) PRIMARY KEY,
email VARCHAR(32));
-- 演示复合主键(id和name做成一个复合主键)
CREATE TABLE t18
(id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY (id,`name`)
);
INSERT INTO t18 VALUES(1,'jack','[email protected]');
INSERT INTO t18 VALUES(1,'tom','[email protected]'); -- 可以添加
INSERT INTO t18 VALUES(1,'Jack','[email protected]'); -- 不可以添加，违反了复合主键
SELECT * FROM t18;
-- 3.主键的指定方式有两种
-- ● 直接在字段后指定：字段名 primary key
-- ● 在表定义最后些 primary key(列名)
-- 4.使用的desc表名，可以看到primary key 的情况
DESC t18; -- 查看t18表的结果，显示约束情况
P 56 unique 2022/8/2
1.unique ——唯一
代码演示以及相关截图：
-- unique 的使用
CREATE TABLE t21
( id INT UNIQUE, -- 表示id列是不可以重复的
`name` VARCHAR(32),
email VARCHAR(32));
INSERT INTO t21 VALUES(1,'jack','[email protected]');
INSERT INTO t21 VALUES(1,'tom','[email protected]');
SELECT * FROM t21;
-- unique 使用的细节：
-- 1.如果没有指定not null ，则unique字段可以有多个null
-- 如果一个列(字段)，是unique not null 使用效果类似primary key
INSERT INTO t21 VALUES(NULL,'tom','[email protected]');
-- 2.一张表中可以有多个unique字段
CREATE TABLE t22
( id INT UNIQUE, -- 表示id列是不可以重复的
`name` VARCHAR(32) UNIQUE,
email VARCHAR(32)) ;
DESC t22;
P 57 外键 2022/8/2
1.外界约束示意图
P 58 外键使用细节2022/8/2
1.foreign key
代码演示以及相关截图：
-- 外健演示
-- 创建 主表 my_class
CREATE TABLE my_class(
id INT PRIMARY KEY, -- 班级编号
`name` VARCHAR(32) NOT NULL DEFAULT '');
-- 创建 从表 my_stu
CREATE TABLE my_stu(
id INT PRIMARY KEY, -- 学生编号
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT , -- 学生所在班级的编号
-- 下面指定外健关系
FOREIGN KEY (class_id) REFERENCES my_class(id));
-- 测试数据：
SELECT * FROM my_class;
INSERT INTO my_class
VALUES(100,'java'),(200,'web');
INSERT INTO my_class
VALUES(300,'php')
SELECT * FROM my_stu;
INSERT INTO my_stu
VALUES(1,'tom',100);
INSERT INTO my_stu
VALUES(2,'jack',200);
INSERT INTO my_stu
VALUES(4,'mary',400); -- 这条语句会失败，因为400班级不存在
INSERT INTO my_stu
VALUES(5,'king',NULL); -- 可以，外健没有设置not null，null不指向任何地方
-- 细节：一旦建立主外键的关系，数据不能随意删除了。
-- （取决于外健有没有数据指向他，如需要删除，需要删除所有指向他的数据）
2.foreign key ——细节说明
P 59 ckeck 2022/8/2
1.check简介
代码演示以及相关截图：
-- 演示check 的使用
-- mysql 5.7目前还不支持check ,只做语法检验，但不会生效
-- 测试
CREATE TABLE t23 (
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6) CHECK (sex IN ('man','woman')), -- 性别强制为男或者女的
sal DOUBLE CHECK (sal > 1001 AND sal < 2000)); -- sal大于1001，小于2000
-- 添加数据
INSERT INTO t23
VALUES(1,'jack','mid',1);
SELECT * FROM t23; -- 查询然而并没有生效，所以在mysql 5.7 还不会生效
P 60 商品表设计 2022/8/3
1.商品表设计要求(约束练习)
代码演示以及相关截图：
-- 商品表的设计
-- 使用约束的练习
-- 现有一个商店的数据库shop_db，记录客服及其购物情况，由下面三个表组成：
-- 商品goods （商品号goods_id,商品名goods_name,单价unitprice,商品类别category）
-- 供应商provider
-- 客户customer（购买订单号custmer_id,姓名name,地址address，电邮email，性别sex,身份证card_id）；
-- 购买purchase （购买订单号 order_id，客户号customer_id,商品号goods_id,购买数量nums);
-- 建表，在定义中要求声明[进行合理设计]:
-- (1)每个表的主外键；
-- (2)客户的姓名不能为空值；
-- (3)电邮不能重复；
-- (4)客户的性别[男|女] check 枚举
-- (5)单价unitprice 在1.0 - 9999.99之间check
CREATE DATABASE shop_db;
CREATE TABLE goods
(goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 -- decimal 更加精确
CHECK (unitprice >=1.0 AND unitprice <=9999.99),
category INT NOT NULL DEFAULT 0,
privider VARCHAR(64) NOT NULL DEFAULT '');
`goods`
CREATE TABLE customer
( customer_id CHAR(8) PRIMARY KEY, -- 程序员自己决定
`name` VARCHAR(64) NOT NULL DEFAULT '',
address VARCHAR(32) NOT NULL DEFAULT '',
email VARCHAR(32) UNIQUE NOT NULL,
sex CHAR(8)NOT NULL, -- 使用枚举类型，是生效的
card_Id CHAR(18));
CREATE TABLE purchase
( order_id INT UNSIGNED PRIMARY KEY,
customer_id CHAR(8) NOT NULL DEFAULT '', -- 外健约束在后
goods_id INT NOT NULL DEFAULT 0,
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (goods_id) REFERENCES goods(goods_id));
DESC goods;
DESC customer;
DESC purchase;
P 61 自增长 2022/8/4
1.自增长 概述
代码演示以及相关截图：
-- 演示自增长的使用
-- 创建表
CREATE TABLE t24
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT '');
DESC t24
-- 测试自增长的使用
INSERT INTO t24
VALUES(NULL,'[email protected]','jack'); -- 自增长自动分配id为一
INSERT INTO t24 (email,`name`) VALUES('[email protected]','zjc'); -- 自动分配为2
SELECT * FROM t24;
-- 自增长的细节
-- 修改默认的自增长开始值
CREATE TABLE t25
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT '');
ALTER TABLE t25 AUTO_INCREMENT = 100; -- 修改自增长的默认开始值
INSERT INTO t25 (email,`name`) VALUES('[email protected]','zzz');
INSERT INTO t25 (email,`name`) VALUES('[email protected]','jjj');
INSERT INTO t25 VALUES(666,'[email protected]','ccc'); -- 可以写入，优先级最高；如下次写入的值则为667。
INSERT INTO t25 (email,`name`) VALUES('[email protected]','mary');
SELECT * FROM t25;
2.自增长使用细节
P 62 索引优化速度 2022/8/4
1.索引
代码演示以及相关截图：
CREATE DATABASE tmp;
/*
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;
#创建表EMP雇员
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 NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;
#工资级别表
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);
DELIMITER $$
#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
#定义了一个变量 chars_str， 类型 varchar(100)
#默认给 chars_str 初始值 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
# concat 函数 : 连接函数mysql函数
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
#这里我们又自定了一个函数,返回一个随机的部门号
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(10+RAND()*500);
RETURN i;
END $$
#创建一个存储过程， 可以添加雇员
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0
#autocommit = 0 含义: 不要自动提交
SET autocommit = 0; #默认不提交sql语句
REPEAT
SET i = i + 1;
#通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
INSERT INTO emp VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
#commit整体提交所有sql语句，提高效率
COMMIT;
END $$
#添加8000000数据
CALL insert_emp(100001,8000000)$$
#命令结束符，再重新设置为;
DELIMITER ;
*/
-- 在没有创建索引时，我们的查询一条记录
SELECT * FROM emp
WHERE empno = 1234567; -- 查询时间大概3秒
-- 使用索引来优化一下，体验索引的厉害
-- 在没有创建索引前，emp.ibd 文件大小 是 524m
-- 创建索引后 emp.ibd 文件大小是665m [索引本身也会占用空间]
-- emp_index 索引名称
-- ON emp(empno) :表示在emp表的empno 列创建索引
CREATE INDEX empno_index ON emp(empno);
-- 创建索引后，查询的速度如何？
SELECT * FROM emp
WHERE empno = 1234567; -- 创建索引后0.002秒
-- 建立一个索引是否可以解决全部问题？
-- ename上没有建立索引会怎样？
-- 解答：创建索引后，只对创建索引的列有效;对ename无效
SELECT *
FROM emp
WHERE ename = 'PjDLwy';
-- 在ename上创建索引
-- 继续创建ename索引后 emp.ibd 文件大小是827m [索引本身也会占用空间]
-- 创建之后重新查询，速度大幅得到提升。
CREATE INDEX ename_index ON emp(ename);
P 63 索引机制 2022/8/4
1.索引的原理
P 64 创建索引 2022/8/4
1.索引的种类
- 主键索引，主键自动的为主索引（类型primary key）
create tabll t1 (id int primary key);
- 唯一索引（UNIQUE）
create tabll t2 (id int unique); -- id是唯一的，同时也是索引，成为unique索引
- 普通索引（INDEX）
CREATE INDEX empno_index ON emp(empno); -- 可重复
- 全文索引（FULLTEXT）[MylSAM]，一般开发中不使用mysql自带的全文索引，而是开发中考虑使用：全文搜索Solr和ElasticSearch(ES)
2.索引使用
-- 演示mysql索引的使用
-- 创建索引
CREATE TABLE t25 (
id INT ,
`name` VARCHAR(32));
-- 查询表是否有索引
SHOW INDEXES FROM t25;
-- 1.添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id); -- ID列值不会重复，使用唯一索引，例如：身份证号
-- 2.添加普通索引方式一：
CREATE INDEX id_index ON t25 (id); -- 后面会出现重复值，使用普通索引
-- ？如何选择？
-- ● 如果某列的值是不会重复的，则优先考虑unique索引，否则使用普通索引
-- 添加普通索引方式二：
ALTER TABLE t25 ADD INDEX in_index (id);
-- 3.添加主键索引
CREATE TABLE t26 (id INT PRIMARY KEY, `name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id); -- 已经创建了表，再次添加用此语句
SHOW INDEXES FROM t26;
P 65 删除索引 2022/8/4
1.删除索引
代码演示以及相关截图：
-- 删除
-- 1.删除索引
DROP INDEX id_index ON t25;
-- 2.删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY;
SHOW INDEXES FROM t26;
-- 3.修改索引，先删除，在添加新的索引
-- 4.查询索引
-- 方式一：
SHOW INDEX FROM t25;
-- 方式二：
SHOW INDEXES FROM t25;
-- 方式三：
SHOW KEYS FROM t25;
-- 方式四：
DESC t25; -- key列显示的就是一个索引，但是显示没有上面详细。
课后题目练习：
题目解析：
-- 索引课后练习
-- 题目一：(建立索引 主键)
-- 创建一张订单表order（id号，商品名，订购人，数量），要求id号为主键，请使用两种方式来创建主键。
-- （提示：为练习方便，可以是order1，order2）
CREATE TABLE `order` (
id INT,
comm_name VARCHAR(64),
pur_peo VARCHAR(64),
quantity INT,
PRIMARY KEY (id));
CREATE TABLE `order2` (
id INT PRIMARY KEY,
comm_name VARCHAR(64),
pur_peo VARCHAR(64),
quantity INT);
CREATE TABLE `order3` (
id INT,
comm_name VARCHAR(64),
pur_peo VARCHAR(64),
quantity INT);
ALTER TABLE order3 ADD PRIMARY KEY(id);
-- 查询索引
SHOW INDEXES FROM `order`;
SHOW INDEXES FROM `order2`;
SHOW INDEXES FROM `order3`;
-- 题目二：(建立索引 唯一)
-- 创建一张特价菜谱表menu(id号，菜谱名，厨师，点餐人身份证，价格)。
-- 要求ID号为主键，点餐人身份证是unique，请用两种方式创建unique。
CREATE TABLE menu1 (
id INT PRIMARY KEY,
menu_name VARCHAR(32),
cook VARCHAR(32),
card_id CHAR(16) UNIQUE,
price DECIMAL(5,2));
CREATE TABLE menu2 (
id INT PRIMARY KEY,
menu_name VARCHAR(32),
cook VARCHAR(32),
card_id CHAR(16),
price DECIMAL(5,2),
UNIQUE (card_id));
CREATE TABLE menu3 (
id INT PRIMARY KEY,
menu_name VARCHAR(32),
cook VARCHAR(32),
card_id CHAR(16),
price DECIMAL(5,2)
);
ALTER TABLE menu3 ADD UNIQUE (card_id);
DESC menu1;
DESC menu2;
DESC menu3;
-- 题目三：(建立索引 普通)
-- 创建一张运动员sportman(id号，名字，特长)，要求id为主键，名字为普通索引，
-- 请用两种方式创建索引：
CREATE TABLE sportman(
id INT PRIMARY KEY,
`name` VARCHAR(32),
specialty VARCHAR(32));
CREATE TABLE sportman1(
id INT PRIMARY KEY,
`name` VARCHAR(32),
specialty VARCHAR(32));
CREATE INDEX name_index ON sportman (`name`);
ALTER TABLE sportman1 ADD INDEX spec_index(specialty);
SHOW INDEXES FROM sportman;
SHOW INDEXES FROM sportman1;
P 66 创建索引规则 2022/8/5
1.创建索引规则
？哪些列上适合创建索引呢？
- 较频繁作为查询条件字段应该创建索引
select * from emp where empno = 1 -- empno 查询频繁
- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex = '男' -- 唯一性太差，不是男就是女
- 更新非常频繁的字段不适合创建索引
select * from emp where logincount = 1 -- 比如查询某人登录的次数，创建索引意义大
- 不会出现在where子句中字段不该创建索引（就是不会查询，就是用来显示的）
P 67 事务有什么用 2022/8/5
1.事务理解
- 理解图：
- 事务操作示意图：
P 68 事务操作 2022/8/5
1.回退事务与提交事务
代码演示以及相关截图：
-- 事务的一个重要概念和具体操作
-- 看一个图[事务操作示意图]
-- 演示
-- 1.创建一张测试表
CREATE TABLE t27 (
id INT,
`name` VARCHAR(32));
-- 2.开始事务
START TRANSACTION
-- 3.设置保存点
SAVEPOINT a
-- 4.执行dml 操作
INSERT INTO t27 VALUES(100,'tom');
SELECT * FROM t27;
SAVEPOINT b
-- 5.设置保存点后继续执行dml操作
INSERT INTO t27 VALUES(200,'jack');
-- 6.回退到b
ROLLBACK TO b
-- 回退到a，当回到最初的保存点，后面的b,c,d,保存点一律会删除
ROLLBACK TO a
-- 如果这样执行 ，表示直接回到事务开始的状态。
ROLLBACK
P 69 事务注意事项 2022/8/5
1.事务细节讨论
代码演示以及相关截图：
-- 讨论 事务细节
-- 1.如果不开始事务，默认情况下，dml操作都是自动提交，不能回滚。
INSERT INTO t27 VALUES(300,'milan');
SELECT * FROM t27;
-- 2.如果你开启一个事务，你没有创建保存点，你可以执行rollback,
-- 默认就是回退到你事务开始的状态
START TRANSACTION
INSERT INTO t27 VALUES(400,'king');
INSERT INTO t27 VALUES(500,'scott');
ROLLBACK -- 表示直接回退到事务开始的状态
-- 3.你也可以在这个事务中（还没有提交的时候），创建多个保存点，比如：savepoint aaa,
-- 执行dml语句后，在设置保存点savepoint bbb。
-- 4.你可以在事务没有提交前选择回退到哪个保存点。
-- 5.mysql的事务机制需要innodb的存储引擎还可以使用，myisam 不行
-- 6.开始一个事务start transaction ，set autocommit=off 两种方式都可开启一个事务
P 70 4种隔离级别 2022/8/6
1.事务隔离级别
相关理论学习：
P 71 隔离级别演示(一) 2022/8/6
1.事务隔离级别案例
代码演示：
-- 演示mysql的事务隔离级别
-- 1.开了两个mysql的控制台
USE db_01;
-- 2.查看当前mysql的隔离级别
SELECT @@tx_isolation;
-- mysql> SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+
START TRANSACTION; -- 两个控制台都开启事务
-- 3.把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 4.创建表
CREATE TABLE `account`(
id INT ,
`name` VARCHAR(32),
money INT );
# 读未提交：
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- ● 操作： 可重复读 其控制台改变数据不提交事务，另一个控制台也可以查询到数据
-- 这就是 脏读
-- ● 操作：改变数据之后提交事务，另一个控制台，查询的数据无法与第一次匹配
-- ▼第一次查询结果和第二次查询结果对不上，我想读的就是我连接到数据库的一个时间点
-- ，而不是后面时间被另一个连接修改了，再次查询的数据无法匹配，造成了不可重复读，
-- 我连接在查询表的时候，应该是不希望受到其他连接的影响。
# 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 另一个控制台修改数据，第二个控制台因为隔离级别是读已提交，所以不会查询到未提交的数据
P 72 隔离级别演示(二) 2022/8/6
1.级别演示
# 可重复读：
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 操作：更改一个控制台的隔离级别，操作同上，再一次实验，发现无论提交与否，
-- 都不会出现脏读，重复读，幻读。
# 可串行化：
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 相比可重复读，可串行化是加锁了的，当一个事务正在操作还没提交的时候，其他事务想要操作
-- 需要等另一个事务提交，如未提交会一直等待;另一个事务提交会立即查询出。
P 73 设置隔离 2022/8/7
1.隔离命令总结
修改默认的系统隔离级别：
2.事务的acid特性
课后练习：
P 74 存储引擎 (一) 2022/8/7
1.基本介绍
基本引擎：
2.主要存储引擎/表类型的特点
3.细节说明
P 75 存储引擎 (二) 2022/8/7
1.引擎演示
- 如何选择表的存储引擎
代码演示以及相关截图：
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES;
-- （1）innodbd 存储引擎，前面使用过
-- 1.支持事务 2.支持外健 3.支持行级锁
-- （2）myisam 存储引擎
CREATE TABLE t28 (
id INT,
`name` VARCHAR(32)) ENGINE MYISAM;
-- 1.添加速度快 2.不支持外健和事务 3.支持表级锁
START TRANSACTION;
SAVEPOINT t1
INSERT INTO t28 VALUES(1,'jack');
SELECT * FROM t28;
ROLLBACK -- 无法回滚，不支持事务
-- （3）memory 存储引擎
-- 1.数据在内存中[关闭mysql服务数据丢失，但是表架构还在]
-- 2.执行速度很快(没有IO读写) 3.默认支持索引(hash表)
CREATE TABLE t29 (
id INT,
`name` VARCHAR(32)) ENGINE MEMORY;
INSERT INTO t29 VALUES(1,'tom'),(2,'jack'),(3,'zjc');
SELECT * FROM t29;
DESC t29;
-- 修改存储引擎
ALTER TABLE t29 ENGINE = INNODB;
-- 查看数据库中指定表的状态，存储引擎等。
SHOW TABLE STATUS FROM db_01 WHERE NAME = 'emp';
2.memory引擎的实例
P 76 视图原理 2022/8/7
1.视图
- 基本概念：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
- 视图和基表关系示意图：
P 77 视图使用细节 2022/8/7
1.基本使用
2.视图的细节讨论
代码演示以及相关截图：
-- 视图的使用
-- 创建一个视图 emp_view01,只能查询emp表的(empno,ename,job和 deptno) 信息
-- 创建视图
CREATE VIEW emp_view01
AS
SELECT empno,ename,job,deptno FROM emp;
-- 查看视图
DESC emp_view01;
SELECT * FROM emp_view01;
-- 查看创建视图的指令
SHOW CREATE TABLE emp_view01;
-- 删除视图
DROP VIEW emp_view01;
-- 视图的细节
-- 1.创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式：视图名.frm)
-- 2.视图的数据变化会影响到表，基表的数据变化也会影响到视图[insert,update,delete]
-- 修改视图
UPDATE emp_view01 SET job = 'MANAGER' WHERE empno = 7369;
SELECT * FROM emp_view01;
SELECT * FROM emp; -- 查询基表 发现变成MANAGER
-- 反过来，修改基本表也会影响到视图
UPDATE emp SET job = 'SALESMAN' WHERE empno = 7369;
-- 3.视图中可以在使用视图，比如从emp_view01 视图中，选出empno和ename做出一个新视图
CREATE VIEW emp_view02
AS
SELECT empno,ename FROM emp_view01;
SELECT * FROM emp_view02; -- 但是当删除视图1，视图2就会无效
P 78 视图应用实例 2022/8/8
1.视图最佳实践
代码演示以及相关截图：
-- 视图的课堂练习
-- 针对emp,dept和salgrade 三张表，创建一个视图 emp_view03,
-- 可以显示雇员编号，雇员名，雇员部门名称 和 薪水级别[即使用三张表，构建一个视图]
/*
分析：使用三表联合查询，得到结果,
将得到的结果构建成视图
*/
CREATE VIEW emp_view03
AS
SELECT empno,ename,dname,grade
FROM emp,dept,salgrade
WHERE emp.deptno = dept.deptno AND
(sal BETWEEN losal AND hisal);
DESC emp_view03;
SELECT * FROM emp_view03;
P 79 MySQL用户管理 2022/8/8
1.MySQL用户
代码演示以及相关截图：
-- MySQL用户管理
-- 原因：当我们做项目开发时，可以根据不同的开发人员，付给他相应的mysql操作权限
-- 所以MySQL数据库管理人员(root),根据需要创建不同的用户，赋给相应权限，供人员使用
-- 1.创建新的用户
-- 解读：（1）'zjc_edu'@'localhost'表示用户的完整信息; 'zjc_edu'用户名 localhost 登录的IP
-- （2）123456密码，但是注意，存放到mysql.user表时，是password('123456') 加密后的密码
CREATE USER 'zjc_edu'@'localhost' IDENTIFIED BY '123456';
SELECT * FROM mysql.user;
SELECT `host`,`user`,`authentication_string` FROM `user`;
-- 2.删除用户
DROP USER 'zjc_edu'@'localhost';
-- 3.登录
-- 操作另一终端进行登录
-- 总结：不同数据库用户，操作的库和表不相同
-- 4.密码的修改
-- root用户修改 zjc_edu@localhost 密码，root用户权限很高是可以成功
SET PASSWORD FOR 'zjc_edu'@'localhost' = PASSWORD('123456');
usermanager.sql
-- 修改自己的密码,没有任何问题
SET PASSWORD = PASSWORD('abcdef');
-- 修改其他人的密码需要权限
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456'); -- 权限不够，访问被拒绝
P 80 MySQL权限管理 2022/8/8
1.权限管理
2.给用户授权
3.回收用户权限
4.案例演示
代码演示以及相关截图：
-- 演示用户权限管理
-- 1.创建用户zjc 密码123
CREATE USER 'zjc'@'localhost' IDENTIFIED BY '123';
-- 2.使用root用户创建testdb，表 news
CREATE DATABASE testdb;
CREATE TABLE news(
id INT,
content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100,'北京新闻');
SELECT * FROM news;
-- 3.给用户分配查看和添加news表的权限
GRANT SELECT ,INSERT ,UPDATE,DELETE
ON testdb.news
TO 'zjc'@'localhost';
-- 5.修改zjc 的密码为abc
SET PASSWORD FOR 'zjc'@'localhost' = PASSWORD('abc');
-- 6.回收zjc用户在 testdb.news 表的所有权限
REVOKE SELECT ,UPDATE,INSERT ON test.news FROM 'zjc'@'localhost'; -- 第一种
REVOKE ALL ON testdb.news FROM 'zjc'@'localhost'; -- 第二种
-- 7.删除zjc这个用户
DROP USER 'zjc'@'localhsot';
创建用户测试Query：
-- 这里默认情况下，zjc用户只能看到一个默认的系统数据库
SELECT * FROM news;
INSERT INTO VALUES(200,'上海新闻');
-- 4.测试用户是否有这几个权限
-- 能否删除delete，和修改update
UPDATE news SET content = '成都新闻' -- 权限不够
WHERE id = 100;
-- 增加权限之后
UPDATE news SET content = '天津新闻'
WHERE id = 100;
P 81 管理细节 2022/8/9
1.管理细节
-- 说明用户管理的细节
-- 1.在创建用户的时候，如果不指定host,则为%，%百分号表示所有IP都有连接权限
CREATE USER jack;
SELECT `host`,`user` FROM mysql.user;
-- 也可以这样指定
-- create user 'xxx'@'192.168.1.%' 表示 xxx用户在192.168.1.*的网段可以登录mysql
CREATE USER 'smith'@'192.168.1.%';
-- 2.再删除用户的时候，如果host不是 % ,则需要明确指定 '用户'@'host值'
DROP USER jack; -- 默认就是DROP USER 'JACK'@'%'
DROP USER 'smith'@'192.168.1.%';
P 82 MySQL作业1 2022/8/9
1.作业一
-- (1)
SELECT ename,sal*12 "Annual Salary" FROM emp;
P 83 MySQL作业2 2022/8/9
1.作业二
作业结果：
SELECT * FROM emp;
SELECT * FROM dept;
-- 题目一：（使用简单查询语句完成）
-- 1.显示所有部门名称
SELECT dname FROM dept;
-- 2.显示所有雇员名及其全年收入（工资+补助），并指定列别名年收入
SELECT ename,(sal + IF(comm IS NULL,0,comm)) *13 AS '年收入' FROM emp;
SELECT ename,(sal + IFNULL(comm,0))* 13 AS '年收入' FROM emp;
-- 题目二：（限制查询数据）
-- 1.显示工资超过2850的雇员姓名和工资。
SELECT ename,sal FROM emp
WHERE sal > 2850;
-- 2.显示工资不在1500到2850之间的所有雇员名及工资
SELECT ename,sal FROM emp
WHERE sal NOT BETWEEN 1500 AND 2850;
SELECT ename,sal FROM emp
WHERE sal < 1500 OR sal>2850;
SELECT ename,sal FROM emp
WHERE NOT (sal>1500 AND sal<2850);
-- 3.显示编号为7566的雇员姓名及所在部门编号。
SELECT ename,emp.deptno,empno FROM emp,dept
WHERE emp.deptno = dept.deptno
AND emp.empno = 7566;
SELECT ename,deptno FROM emp
WHERE empno = 7566;
-- 4.显示部门10和30中工资超过1500的雇员名及工资
SELECT deptno,ename,sal FROM emp
WHERE (deptno = 10 OR deptno =30)
AND sal > 1500;
-- 5.显示无管理者的雇员名及岗位
SELECT ename,job,mgr FROM emp
WHERE mgr IS NULL;
-- 题目三：（排序数据）
-- 1.显示在1991年2月1日到1991年5月1日之间雇用的雇员名，岗位及雇佣日期，并以雇佣日期进行排序。
SELECT ename,hiredate,job FROM emp
WHERE hiredate BETWEEN '1991-02-01' AND '1991-05-01'
ORDER BY hiredate ;
SELECT ename,hiredate,job FROM emp
WHERE hiredate >='1991-02-01' AND hiredate <='1991-05-01'
ORDER BY hiredate;
-- 2.显示获得补助的所有员工名，工资以及补助，并以工资降序进行排序
-- 思路：先查询对应结果，再考虑排序
SELECT ename,sal,comm FROM emp
WHERE comm IS NOT NULL
ORDER BY sal DESC;
P 84 MySQL作业3 2022/8/9
1.作业三
-- .emp员工表，写出正确的SQL
-- 1.选择部门30中的所有员工
SELECT * FROM emp
WHERE deptno = 30;
-- 2.列出所有办事员(CLREK)的姓名，编号和部门标号
SELECT ename,empno,deptno,job FROM emp
WHERE job = 'CLERK';
-- 3.找出佣金(comm)高于薪金(sal)的员工
SELECT * FROM emp
WHERE IFNULL(comm,0) > sal;
-- 4.找出佣金(comm)高于薪金(sal)60%的员工
SELECT * FROM emp
WHERE IFNULL(comm,0) > sal*0.6 ;
-- 5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料
SELECT * FROM emp
WHERE (job = 'MANAGER' AND deptno = 10 )
OR ( job = 'CLERK' AND deptno = 20);
-- 6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),
-- 还有既不是经理又不不是办事员，但其薪酬大于或等于2000的所有员工详细资料。
SELECT * FROM emp
WHERE (job = 'MANAGER' AND deptno = 10 )
OR ( job = 'CLERK' AND deptno = 20)
OR (job != 'MANAGER' AND job != 'CLERK' AND sal > 2000);
-- 7.找出收取佣金(comm)员工的不同工作
SELECT DISTINCT job FROM emp
WHERE comm IS NOT NULL;
-- 8.找出不收取佣金或者收取佣金低于100的员工
SELECT * FROM emp
WHERE comm IS NOT NULL
OR IFNULL(comm,0) < 100;
-- 9.找出个月倒数第三天受雇的所有员工
-- 提示：last_day(日期),可以返回该日期所在月份最后一天
SELECT LAST_DAY('2022-08-09')
SELECT * FROM emp
WHERE LAST_DAY(hiredate) - 2 = hiredate;
-- 10.找出早于12年前受雇的员工(即入职时间超过12年的)
SELECT * FROM emp
WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW();
-- 11.以首字母小写的方式显示员工的所有姓名
SELECT CONCAT(LEFT(LCASE(ename),1),SUBSTRING(ename,2)) FROM emp;
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) FROM emp;
-- 12.显示正好为5个字符员工的姓名
SELECT * FROM emp
WHERE LENGTH(ename)= 5;
P 85 MySQL作业4 2022/8/10
1.作业四
-- 1.显示不带有"R"员工的姓名。
SELECT * FROM emp
WHERE ename NOT LIKE "%R%";
-- 2.显示所有员工姓名的前三个字符。
SELECT LEFT(ename,3) FROM emp;
SELECT SUBSTRING(ename,1,3) FROM emp;
-- 3.显示所有员工的姓名，用a替换所有“A”。
SELECT REPLACE(ename,'A','a') FROM emp;
-- 4.显示满10年年限的员工的姓名和受雇日期。
SELECT ename,hiredate
FROM emp
WHERE DATE_ADD(hiredate,INTERVAL 10 YEAR) <= NOW();
-- 5.显示员工的详细资料，按姓名排序。
SELECT * FROM emp
ORDER BY ename;
-- 6.显示员工的姓名和受雇日期，根据服务年限，将最老的员工排在最前面(升序)
SELECT ename,hiredate FROM emp
ORDER BY hiredate;
-- 7.显示所有员工的姓名，工作和薪金(sal)，按工作降序排序，若工作相同则按薪金(sal)排序。
SELECT ename,job,sal FROM emp
ORDER BY job DESC,sal ;
-- 8.显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面。
SELECT ename,CONCAT(YEAR(hiredate),'-',MONTH(hiredate)) FROM emp
ORDER BY MONTH(hiredate),YEAR(hiredate);
-- 9.显示在一个月为30天的情况所有员工的日薪金，忽略余数。
SELECT ename,FLOOR(sal/30),sal/30 FROM emp; -- 四舍五入使用ROUND
-- 10.找出在(任何年份)2月受聘的所有员工
SELECT * FROM emp
WHERE MONTH(hiredate) = 2;
-- 11.对于每个员工，显示加入公司的天数
SELECT ename, DATEDIFF(NOW(),hiredate) FROM emp;
-- 12.显示姓名字段的任何位置包含"A"的所有员工的姓名
SELECT * FROM emp
WHERE ename LIKE '%A%';
-- 13.以年月日的方式显示所有员工的服务年限（大概）
-- 思路:1.先求出工作了多少天,% 是余数的意思
SELECT ename, FLOOR(DATEDIFF(NOW(),hiredate)/365) AS '工作年',
FLOOR(DATEDIFF(NOW(),hiredate)% 365 /31) AS '工作月',
DATEDIFF(NOW(),hiredate) % 31 AS '工作日' -- 31的除，输出余数
FROM emp;
P 86 MySQL作业5 2022/8/11
1.作业五
-- homework05
SELECT * FROM dept;
SELECT * FROM emp;
-- 1.列出至少有一个员工的所有部门。(查询每个部门人数大于1的)
-- 思路：先查出各个部门有多少人，然后使用having子句过滤
SELECT COUNT(*) AS c ,deptno
FROM emp
GROUP BY deptno
HAVING c > 1;
-- 2.列出薪金比"SMITH"多的所有员工。
SELECT ename,sal FROM emp
WHERE sal > (SELECT sal FROM emp -- 查出SMITH的薪水作为子查询
WHERE ename = 'SMITH');
-- 3.列出受雇日期晚于其直接上级的所有员工。
-- 思路：先把emp表当做两张表 worker，leader;
-- (1)worker 受雇日期大于 leader 的受雇日期
-- (2)worker.mgr = leader.enpno
SELECT worker.ename AS '员工名',worker.hiredate AS '员工入职时间',
leader.ename AS '上级名',leader.hiredate AS '上级入职时间'
FROM emp worker,emp leader
WHERE worker.hiredate > leader.hiredate
AND worker.mgr = leader.empno;
-- 4.列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门
-- 思路：这里因为需要显示所有部门，因此考虑外连接
SELECT dname,emp.*
FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno; -- 左外连
SELECT dname,emp.*
FROM emp RIGHT JOIN dept ON dept.deptno = emp.deptno; -- 右外连
-- 5.列出所有"CLERK"（办事员）的姓名及其部门名称。
SELECT ename,dept.deptno,job,dname FROM emp,dept
WHERE job = 'CLERK' AND (emp.deptno = dept.deptno);
-- 6.列出最低薪金大于1500的各种工作。
-- 思路：查询各个部门的最低工资;
SELECT MIN(sal) AS min_sal,job FROM emp
GROUP BY job
HAVING MIN(sal) > 1500;
-- 7.列出在部门"SALES"（销售部）工作的员工的姓名
SELECT ename,dname FROM dept,emp
WHERE emp.deptno = dept.deptno
AND dname = 'SALES';
-- 8.列出薪金高于公司平均薪金的所有员工。
SELECT * FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);
P 87 MySQL作业6 2022/8/19
1.作业六
-- 9.列出与'SCOTT'从事相同工作的所有员工。
SELECT * FROM emp
WHERE job = (SELECT job FROM emp
WHERE ename = 'SCOTT');
-- 10.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
-- 思路：先查询出30号部门的最高工资，
SELECT ename,sal FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp
WHERE deptno = 30);
-- 11.列出在每个部门工作的员工数量，平均工资和平均服务年限。(时间单位天，年)
-- 建议：一步一步完成
SELECT COUNT(*) AS "员工数量",FORMAT(AVG(sal),2) AS "平均工资",
FORMAT(AVG(DATEDIFF(NOW(),hiredate)/365),2)AS "平均服务年限",deptno
FROM emp
GROUP BY deptno;
-- 12.列出所有员工的姓名、部门名称、和工资。
SELECT ename,dname,sal FROM emp,dept
WHERE emp.deptno = dept.deptno;
-- 13.列出所有部门的详细信息和部门人数。
-- 思考：先得到各个部门的人数,把下面结果当成一个临时表和dept表
SELECT dept.*,tmp.c FROM dept,(
SELECT COUNT(*) AS c,deptno FROM emp
GROUP BY deptno) tmp
WHERE dept.deptno = tmp.deptno;
-- 14.列出各种工作的最低工资。
SELECT DISTINCT MIN(sal),job FROM emp
GROUP BY job;
-- 15.列出经理(MANAER)的最低薪金。
SELECT MIN(sal)
FROM emp
WHERE job = 'MANAGER';
-- 16.列出所有员工的年工资，按年薪从高到低排序。
-- 思考： 先得到员工的年工资
SELECT ename,(sal + IFNULL(0,comm))* 12 year_sal
FROM emp
ORDER BY year_sal;
P 88 MySQL作业7 2022/8/20
1.作业七
-- 完成最后一个综合练习
CREATE TABLE DEPARTMENT (
departmentid VARCHAR(32) PRIMARY KEY,
deptname VARCHAR(32) UNIQUE NOT NULL);
INSERT INTO DEPARTMENT
VALUES(001,'数学'),(002,'计算机'),(003,'化学'),(004,'中文'),(005,'经济');
CREATE TABLE CLASS (
classid INT PRIMARY KEY ,
`subject` VARCHAR(32) NOT NULL DEFAULT '',
deptname VARCHAR(32) , -- 外健字段，在表定义后指定
enrolltime INT NOT NULL DEFAULT 2000,
num INT NOT NULL DEFAULT 0,
FOREIGN KEY (deptname) REFERENCES DEPARTMENT(deptname));
INSERT INTO CLASS
VALUES(101,'软件','计算机',1995,20),
(102,'微电机','计算机',1996,30),
(111,'无机化学','化学',1995,29),
(112,'高分子化学','化学',1996,25),
(121,'统计数学','数学',1995,20),
(131,'现代语言','中文',1996,20),
(141,'国际贸易','经济',1997,30),
(142,'国际金融','经济',1996,14);
CREATE TABLE zjc_student (
studentid INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
classid INT,
FOREIGN KEY (classid) REFERENCES `CLASS`(classid));
INSERT INTO zjc_student
VALUES(8101,'张三',18,101),
(8102,'钱四',16,121),
(8103,'王玲',17,131),
(8105,'李飞',19,102),
(8209,'赵四',18,141),
(8110,'李可',20,142),
(8201,'张飞',18,111),
(8302,'周瑜',16,112),
(8203,'王亮',17,111),
(8305,'董庆',19,102),
(8409,'赵龙',18,101),
(8510,'李丽',20,142);
P 89 MySQL作业8 2022/8/20
1.作业八
-- 完成以下查询功能
-- 1.找出所有姓李的学生。、
SELECT * FROM zjc_student
WHERE `name` LIKE '李%';
-- 2.找出所有开设超过1个专业的系和名字。
-- 解析：先查出各个系有多少个专业
SELECT COUNT(*) AS nums,deptname
FROM class
GROUP BY deptname
HAVING nums > 1;
-- 3.列出人数大于等于30的 系的编号 和 名字 。
-- 解析：先查促各个系有多少人,得到大于等于30的系；
-- 将第一步结果看做一个临时表，和department进行联合查询。
SELECT * FROM class;
SELECT SUM(num)AS nums,deptname FROM class
GROUP BY deptname
HAVING nums >= 30;
SELECT * FROM department;
SELECT tmp.*,department.departmentid FROM
department,(
SELECT SUM(num)AS nums,deptname FROM class
GROUP BY deptname
HAVING nums >= 30) tmp
WHERE tmp.deptname = department.deptname;
-- 4.学校新增了一个物理系，编号为006（添加一条数据）
INSERT INTO department VALUES('006','物理系');
-- 5.学生张三退学，请更新相关表
SELECT * FROM zjc_student;
-- 分析：张三所在班级-1 ；将张三从学生表里删除；需要使用事务控制
-- 开启事务：
START TRANSACTION
-- 张三所在班级人数减一
UPDATE class SET num = num - 1
WHERE classid = (
SELECT classid
FROM zjc_student
WHERE `name` = '张三');
DELETE FROM zjc_student
WHERE `name` = '张三'; -- （提前张三只有一个人，不是特别严谨）
-- 提交事务
COMMIT;