共计 58897 个字符，预计需要花费 148 分钟才能阅读完成。
文章和代码曾经归档至【Github 仓库：https://github.com/timerring/java-tutorial】或者公众号【AIShareLab】回复 java 也可获取。
零根底学 MySQL
解决之道
文件、数据库
为了解决上述问题, 应用更加利于治理数据的东东 - 数据库，它能更无效的治理数据。
举一个生活化的案例阐明：如果说图书馆是保留书籍的, 那么数据库就是保留数据的。
MySQL 数据库的装置和配置
mysql5.5 mysql5.6 mysqI5.7(稳固) mysql8 更高版本
应用命令行窗口连贯 MYSQL 数据库
- mysql - h 主机名 - P 端口 - u 用户名 - p 明码
-
登录前，保障服务启动
net stop mysql 服务名net start mysql 服务名
操作示意图
- Navicat
介绍: 图形化 MySQL 管理软件
- SQLyog
数据库三层构造
- 所谓装置 Mysql 数据库，就是在主机装置一个数据库管理系统(DBMS)，这个管理程序能够治理多个数据库。DBMS(database manage system)。
- 一个数据库中能够创立多个表, 以保留数据(信息)。
- 数据库管理系统(DBMS)、数据库和表的关系如图所示; 示意图。
数据在数据库中的存储形式
SQL 语句分类
DDL: 数据定义语句[create 表，库 …]
DML: 数据操作语句[减少 insert，批改 update，删除 delete]
DQL: 数据查问语句[select]
DCL: 数据管制语句[治理数据库: 比方用户权限 grant revoke]
创立数据库
- CHARACTER SET: 指定数据库采纳的字符集，如果不指定字符集，默认 utf8
- COLLATE: 指定数据库字符集的校对规定(罕用的 utf8 bin[辨别大小写]
utf8_general_ci[不辨别大小写] 留神默认是 utf8 general_ci )
# 演示数据库的操作
#创立一个名称为 db01 的数据库。[图形化和指令 演示]
#应用指令创立数据库
CREATE DATABASE db01;
#删除数据库指令
DROP DATABASE db01
#创立一个应用 utf8 字符集的 hsp_db02 数据库
CREATE DATABASE db02 CHARACTER SET utf8
#创立一个应用 utf8 字符集，并带校对规定的 hsp_db03 数据库
CREATE DATABASE db03 CHARACTER SET utf8 COLLATE utf8_bin
#校对规定 utf8_bin 辨别大小 默认 utf8_general_ci 不辨别大小写
#上面是一条查问的 sql , select 查问 * 示意所有字段 FROM 从哪个表
#WHERE 从哪个字段 NAME = 'tom' 查问名字是 tom
SELECT *
FROM t1
WHERE NAME = 'tom'查看、删除数据库
显示数据库语句:
SHOW DATABASES显示数据库创立语句:
SHOW CREATE DATABASE db_name数据库删除语句[肯定要慎用]:
DROP DATABASE [IF EXISTS] db_ name# 演示删除和查询数据库
#查看以后数据库服务器中的所有数据库
SHOW DATABASES
#查看后面创立的 hsp_db01 数据库的定义信息
SHOW CREATE DATABASE `hsp_db01`
#在创立数据库, 表的时候，为了躲避关键字，能够应用反引号解决，比方有数据库的名称就叫做 CREATE，那么就须要应用 ` ` 解决。#删除后面创立的 hsp_db01 数据库
DROP DATABASE hsp_db01备份复原数据库
备份数据库 (留神: 在 DOS 执行) 命令行
mysqldump -u 用户名 -p -B 数据库 1 数据库 2 数据库 n > 文件名.sql复原数据库(留神: 进入 Mysql 命令行再执行)
Source 文件名.sql# 练习 : database03.sql 备份 db02 和 db03 库中的数据，并复原
#备份, 要在 Dos 下执行 mysqldump 指令其实在 mysql 装置目录 \bin
#这个备份的文件，就是对应的 sql 语句
mysqldump -u root -p -B db02 db03 > d:\\bak.sql
DROP DATABASE ecshop;
#复原数据库(留神：进入 Mysql 命令行再执行)
source d:\\bak.sql
#第二个复原办法，间接将 bak.sql 的内容放到查问编辑器中，执行，相当于从新执行了一遍备份复原数据库的表
备份库的表
mysqldump -u 用户名 - p 明码 数据库 表 1 表 2 表 n > d:\\ 文件名.sql创立表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype
)character set 字符集 collate 校对规定 engine 存储引擎- field: 指定列名 datatype: 指定列类型(字段类型)
- character set: 如不指定则为所在数据库字符集
- collate: 如不指定则为所在数据库校对规定
- engine: 引擎(这个波及内容较多)
# 指令创立表
#留神：db02 创立表时，要依据需保留的数据创立相应的列，并依据数据的类型定义相应的列类型。例：user 表 (疾速入门案例 create_tab01.sql)
#id 整形 [图形化，指令]
#name 字符串
#password 字符串
#birthday 日期
CREATE TABLE `user` (
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
`birthday` DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;Mysql 罕用数据类型(列类型)
数值型 (整数) 的根本应用
# 演示整型的是一个
#应用 tinyint 来演示范畴 有符号 -128 ~ 127 如果没有符号 0-255
#阐明：表的字符集，校验规定, 存储引擎，老师应用默认
#1. 如果没有指定 unsinged , 则 TINYINT 就是有符号
#2. 如果指定 unsinged , 则 TINYINT 就是无符号 0-255
CREATE TABLE t3 (id TINYINT);
CREATE TABLE t4 (id TINYINT UNSIGNED);
INSERT INTO t3 VALUES(127); #这是非常简单的增加语句
SELECT * FROM t3
INSERT INTO t4 VALUES(255);
SELECT * FROM t4;定义一个无符号的整数
create table t10 (id tinyint);// 默认是有符号的
create table t11 (id tinyint unsigned);// 无符号的（后加 unsigned 就行）数值型 (bit) 的应用
1. 根本应用
mysql > create table t05 (num bit(8));
mysql > insert into t05 (1, 3);
mysql > insert into t05 values(2, 65);2. 细节阐明 bit.sql
- bit 字段显示时，依照位的形式显示.
- 查问的时候依然能够用应用增加的数值
- 如果一个值只有 0，1 能够思考应用 bit(1), 能够节约空间
- 位类型。M 指定位数，默认值 1，范畴 1 -64
# 演示 bit 类型应用
#阐明
#1. bit(m) m 在 1-64
#2. 增加数据 范畴 依照你给的位数来确定，比方 m = 8 示意一个字节 0~255
#3. 显示依照 bit
#4. 查问时，依然能够依照数来查问
CREATE TABLE t05 (num BIT(8));
INSERT INTO t05 VALUES(255); # 按位显示 255 就是全 1（b'11111111'）SELECT * FROM t05;
SELECT * FROM t05 WHERE num = 1;数值型 (小数) 的根本应用
-
FLOAT/DOUBLE [UNSIGNED]
Float 单精度精度，Double 双精度.
- DECIMAL[M,D] [UNSIGNED]
- 能够反对更加准确的小数位。M 是小数位数 (精度) 的总数，D 是小数点 (标度) 前面的位数。
- 如果 D 是 0，则值没有小数点或分数局部。M 最大 65。D 最大是 30。如果 D 被省略, 默认是 0。如果 M 被省略, 默认是 10。
- 倡议: 如果心愿小数的精度高，举荐应用 decimal
# 演示 decimal 类型、float、double 应用
#创立表
CREATE TABLE t06 (
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20));
#增加数据
INSERT INTO t06 VALUES(88.12345678912345, 88.12345678912345,88.12345678912345);
SELECT * FROM t06;
#decimal 能够寄存很大的数
CREATE TABLE t07 (num DECIMAL(65));
INSERT INTO t07 VALUES(8999999933338388388383838838383009338388383838383838383);
SELECT * FROM t07;
CREATE TABLE t08(num BIGINT UNSIGNED)
INSERT INTO t08 VALUES(8999999933338388388383838838383009338388383838383838383);
SELECT * FROM t08;字符串的根本应用
CHAR(size)
固定长度字符串最大 255字符
VARCHAR(size)0~65535
可变长度字符串最大 65532字节【utf8 编码最大 21844 字符（（65535-3）/ 3）其中 1 - 3 个字节用于记录大小】
# 演示字符串类型应用 char varchar
#正文的快捷键 shift+ctrl+c , 登记正文 shift+ctrl+r
-- CHAR(size)
-- 固定长度字符串 最大 255 字符
-- VARCHAR(size) 0~65535 字节
-- 可变长度字符串 最大 65532 字节【utf8 编码最大 21844 字符 1- 3 个字节用于记录大小】-- 如果表的编码是 utf8 varchar(size) size = (65535-3) / 3 = 21844
-- 如果表的编码是 gbk varchar(size) size = (65535-3) / 2 = 32766
CREATE TABLE t09 (`name` CHAR(255));
CREATE TABLE t10 (`name` VARCHAR(32766)) CHARSET gbk;
DROP TABLE t10;字符串应用细节
1. 细节 1
- char(4) // 这个 4 示意字符数(最大 255)，不是字节数, 不论是中文还是字母都是放四个, 按字符计算.
- varchar(4) // 这个 4 示意字符数，不论是字母还是中文都以定义好的表的编码来存放数据
- 不论是中文还是英文字母，都是最多寄存 4 个，是依照字符来寄存的.
2. 细节 2
- char(4)是定长(固定的大小)，就是说，即便你插入 ’aa’，也会占用调配的 4 个字符的空间.
- varchar(4)是变长 (变动的大小)，就是说，如果你插入了 ’aa’, 理论占用空间大小并不是 4 个字符，而是依照理论占用空间来调配(阐明:varchar 自身还须要占用 1 - 3 个字节来记录寄存内容长度)
L(理论数据大小)+(1-3) 字节
3. 细节 3
什么时候应用 char，什么时候应用 varchar
- 如果数据是定长, 举荐应用 char, 比方 md5 的明码, 邮编, 手机号, 身份证号码等. char(32)
- 如果一个字段的长度是不确定, 咱们应用 varchar , 比方留言, 文章
查问速度: char > varchar
4. 细节 4
在寄存文本时，也能够应用 Text 数据类型。能够将 TEXT 列视为 VARCHAR 列，留神 Text 不能有默认值。大小 0 -2^16 字节。如果心愿寄存更多字符，能够抉择
MEDIUMTEXT 0O-2^24 或者 LONGTEXT 0~2^32
# 演示字符串类型的应用细节
#char(4) 和 varchar(4) 这个 4 示意的是字符，而不是字节, 不辨别字符是汉字还是字母
CREATE TABLE t11(`name` CHAR(4));
INSERT INTO t11 VALUES('你好你好');
SELECT * FROM t11;
CREATE TABLE t12(`name` VARCHAR(4));
INSERT INTO t12 VALUES('你好你好');
INSERT INTO t12 VALUES('ab 北京');
SELECT * FROM t12;
#如果 varchar 不够用，能够考试应用 mediumtext 或者 longtext,
#如果想简略点，能够应用间接应用 text
CREATE TABLE t13(content TEXT, content2 MEDIUMTEXT , content3 LONGTEXT);
INSERT INTO t13 VALUES('你好你教育', '你好你教育 100', '你好你教育 1000~~');
SELECT * FROM t13;日期类型的根本应用
CREATE TABLE birthday6( t1 DATE, t2 DATETIME,
t3 TIMESTAMP NOT NULL DEFAULTCURRENT TIMESTAMP ON UPDATE
CURRENT TIMESTAMP ); timestamp 工夫戳
# NOT NULL DEFAULTCURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP 意思是没有默认值的话会依据以后工夫自动更新mysql> INSERT INTO birthday (t1,t2)
VALUES('2022-11-11',2022-11-11 10:10:10');日期类型的细节阐明
TimeStamp 在 Insert 和 update 时，自动更新。
# 演示工夫相干的类型
#创立一张表, date , datetime , timestamp
CREATE TABLE t14 (
birthday DATE , -- 生日
job_time DATETIME, -- 记录年月日 时分秒
login_time TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP); -- 登录工夫, 如果心愿 login_time 列自动更新, 须要配置
SELECT * FROM t14;
INSERT INTO t14(birthday, job_time)
VALUES('2022-11-11','2022-11-11 10:10:10');
-- 如果咱们更新 t14 表的某条记录，login_time 列会主动的以以后工夫进行更新创立表练习
# 创立表的课堂练习
-- 字段 属性
-- Id 整形
-- name 字符型
-- sex 字符型
-- brithday 日期型（date）-- entry_date 日期型 (date)
-- job 字符型
-- Salary 小数型
-- resume 文本型
-- 本人肯定要练习一把
CREATE TABLE `emp` (
id INT,
`name` VARCHAR(32),
sex CHAR(1),
brithday DATE,
entry_date DATETIME,
job VARCHAR(32),
salary DOUBLE,
`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
-- 增加一条
INSERT INTO `emp`
VALUES(100, '小妖怪', '男', '2000-11-11',
'2010-11-10 11:11:11', '巡山的', 3000, '大王叫我来巡山');
SELECT * FROM `emp`;批改表 - 根本介绍
批改表 - 课堂练习
NOT NULL DEFAULT '' 不容许为空 NULL，默认是一个空的 ’ ‘ 字符。
# 批改表的操作练习
-- 员工表 emp 的上减少一个 image 列，varchar 类型(要求在 resume 前面)。ALTER TABLE emp
ADD image VARCHAR(32) NOT NULL DEFAULT ''
AFTER RESUME
DESC employee -- 显示表构造，能够查看表的所有列
-- 批改 job 列，使其长度为 60。ALTER TABLE emp
MODIFY job VARCHAR(60) NOT NULL DEFAULT ''
-- 删除 sex 列。ALTER TABLE emp
DROP sex
-- 表名改为 employee。RENAME TABLE emp TO employee
-- 批改表的字符集为 utf8
ALTER TABLE employee CHARACTER SET utf8
-- 列名 name 批改为 us er_name
ALTER TABLE employee
CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''
DESC employee数据库 C[create]R[read]U[update]D[delete]语句
- Insert 语句 (增加数据)
- Update 语句 (更新数据）
- Delete 语句 (删除数据)
- Select 语句 (查找数据)
Insert 语句
应用 INSERT 语句向表中插入数据。
# 练习 insert 语句
-- 创立一张商品表 goods (id int , goods_name varchar(10), price double );
-- 增加 2 条记录
CREATE TABLE `goods` (
id INT ,
goods_name VARCHAR(10), -- 长度 10
price DOUBLE NOT NULL DEFAULT 100 );
-- 增加数据
INSERT INTO `goods` (id, goods_name, price)
VALUES(10, '华为手机', 2000);
INSERT INTO `goods` (id, goods_name, price)
VALUES(20, '苹果手机', 3000);
SELECT * FROM goods;
CREATE TABLE `goods2` (
id INT ,
goods_name VARCHAR(10), -- 长度 10
price DOUBLE NOT NULL DEFAULT 100 );细节阐明
# 阐明 insert 语句的细节
-- 1. 插入的数据应与字段的数据类型雷同。-- 比方 把 'abc' 增加到 int 类型会谬误
INSERT INTO `goods` (id, goods_name, price)
VALUES('abc', '小米手机', 2000);
-- 2. 数据的长度应在列的规定范畴内，例如：不能将一个长度为 80 的字符串退出到长度为 40 的列中。INSERT INTO `goods` (id, goods_name, price)
VALUES(40, 'vovo 手机 vovo 手机 vovo 手机 vovo 手机 vovo 手机', 3000);
-- 3. 在 values 中列出的数据地位必须与被退出的列的排列地位绝对应。INSERT INTO `goods` (id, goods_name, price) -- 不对
VALUES('vovo 手机',40, 2000);
-- 4. 字符和日期型数据应蕴含在单引号中。INSERT INTO `goods` (id, goods_name, price)
VALUES(40, vovo 手机, 3000); -- 谬误的 vovo 手机 应该 'vovo 手机'
-- 5. 列能够插入空值[前提是该字段容许为空（如果指定 not null 则不能够）]，insert into table value(null)
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, 'vovo 手机', NULL);
-- 6. insert into tab_name (列名..) values (),(),() 模式增加多条记录
INSERT INTO `goods` (id, goods_name, price)
VALUES(50, '三星手机', 2300),(60, '海尔手机', 1800);
-- 7. 如果是给表中的所有字段增加数据，能够不写后面的字段名称
INSERT INTO `goods`
VALUES(70, 'IBM 手机', 5000);
-- 8. 默认值的应用，当不给某个字段值时，如果有默认值就会增加默认值，否则报错
-- 如果某个列 没有指定 not null , 那么当增加数据时，没有给定值，则会默认给 null
-- 如果咱们心愿指定某个列的默认值，能够在创立表时指定
INSERT INTO `goods` (id, goods_name)
VALUES(80, '格力手机');
SELECT * FROM goods;
INSERT INTO `goods2` (id, goods_name)
VALUES(10, '顺平手机');
SELECT * FROM goods2;update 语句
应用 update 语句批改表中数据
根本应用
-- 演示 update 语句
-- 要求: 在下面创立的 employee 表中批改表中的纪录
-- 1. 将所有员工薪水批改为 5000 元。[如果没有带 where 条件，会批改所有的记录，因而要小心]
UPDATE employee SET salary = 5000
-- 2. 将姓名为 小妖怪 的员工薪水批改为 3000 元。UPDATE employee
SET salary = 3000
WHERE user_name = '小妖怪'
-- 3. 将 老妖怪 的薪水在原有根底上减少 1000 元
INSERT INTO employee
VALUES(200, '老妖怪', '1990-11-11', '2000-11-11 10:10:10', '捶背的', 5000, '给大王捶背', 'd:\\a.jpg');
UPDATE employee
SET salary = salary + 1000
WHERE user_name = '老妖怪'
-- 能够批改多个列的值
UPDATE employee
SET salary = salary + 1000 , job = '出主见的'
WHERE user_name = '老妖怪'
SELECT * FROM employee;应用细节
- WHERE 子句指定应更新哪些行。如没有 WHERE 子句，则更新所有的行(记录)。
- 如果须要批改多个字段，能够通过
set 字段 1 = 值 1, 字段 2 = 值 2...
delete 语句
应用 delete 语句删除表中数据
删除只能依照行删除，不能依照列删除。
-- delete 语句演示
-- 删除表中名称为’老妖怪’的记录。DELETE FROM employee
WHERE user_name = '老妖怪';
-- 删除表中所有记录, 老师揭示，肯定要小心
DELETE FROM employee;
-- Delete 语句不能删除某一列的值（可应用 update 设为 null 或者 ''）UPDATE employee SET job = ''WHERE user_name =' 老妖怪 ';
SELECT * FROM employee
-- 要删除这个表
DROP TABLE employee;应用细节
- 如果不应用 where 子句，将删除表中所有数据。
- Delete 语句不能删除某一列的值(可应用 update 设为 null 或者 ””)
- 应用 delete 语句删除记录, 不删除表自身。如要删除表，应用 droptable 语句。
drop table 表名;
select 语句
根本语法
注意事项(创立测试表学生表)
- Select 指定查问哪些列的数据。
- column 指定列名。
- * 号代表查问所有列。
- From 指定查问哪张表。
- DISTINCT 可选, 指显示后果时，是否去掉反复数据。
练习
-- select 语句【重点 难点】CREATE TABLE student(
id INT NOT NULL DEFAULT 1,
NAME VARCHAR(20) NOT NULL DEFAULT '',
chinese FLOAT NOT NULL DEFAULT 0.0,
english FLOAT NOT NULL DEFAULT 0.0,
math FLOAT NOT NULL DEFAULT 0.0
);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);
SELECT * FROM student;
-- 查问表中所有学生的信息。SELECT * FROM student;
-- 查问表中所有学生的姓名和对应的英语问题。SELECT `name`,english FROM student;
-- 过滤表中反复数据 distinct。SELECT DISTINCT english FROM student;
-- 要查问的记录，每个字段都雷同，才会去重
SELECT DISTINCT `name`, english FROM student;应用表达式对查问的列进行运算
在 select 语句中可应用 as 语句
练习
-- select 语句的应用
-- 统计每个学生的总分
SELECT `name`, (chinese+english+math) FROM student;
-- 在所有学生总分加 10 分的状况
SELECT `name`, (chinese + english + math + 10) FROM student;
-- 应用别名示意学生分数。SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score
FROM student;在 where 子句中常常应用的运算符
其中的 BETWEEN … AND 是闭区间。
应用 where 子句，进行过滤查问
_：匹配任意一个字符；%：匹配 0 个或多个字符；[]：匹配 [] 中的任意一个字符(若要比拟的字符是间断的，则能够用连字符“-”表 达)；[^]：不匹配 [] 中的任意一个字符。
-- select 语句
-- 查问姓名为赵云的学生问题
SELECT * FROM student
WHERE `name` = '赵云'
-- 查问英语问题大于 90 分的同学
SELECT * FROM student
WHERE english > 90
-- 查问总分大于 200 分的所有同学
SELECT * FROM student
WHERE (chinese + english + math) > 200
-- 查问 math 大于 60 并且(and) id 大于 4 的学生问题
SELECT * FROM student
WHERE math >60 AND id > 4
-- 查问英语问题大于语文问题的同学
SELECT * FROM student
WHERE english > chinese
-- 查问总分大于 200 分 并且 数学问题小于语文问题, 的姓赵的学生.
-- 赵 % 示意 名字以赵结尾的就能够
SELECT * FROM student
WHERE (chinese + english + math) > 200 AND
math < chinese AND `name` LIKE '赵 %'
-- 查问英语分数在 80-90 之间的同学。SELECT * FROM student
WHERE english >= 80 AND english <= 90;
SELECT * FROM student
WHERE english BETWEEN 80 AND 90; -- between .. and .. 是 闭区间
-- 查问数学分数为 89,90,91 的同学。SELECT * FROM student
WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student
WHERE math IN (89, 90, 91);
-- 查问所有姓李的学生问题。SELECT * FROM student
WHERE `name` LIKE '韩 %'
-- 查问数学分 >80，语文分 >80 的同学应用 order by 子句排序查问后果
- Order by 指定排序的列，排序的列既能够是表中的列名，也能够是 select
语句后指定的列名。 - Asc 升序[默认]、Desc 降序
- ORDER BY 子句应位于 SELECT 语句的结尾。
-- 演示 order by 应用
-- 对数学问题排序后输入【升序】。SELECT * FROM student
ORDER BY math;
-- 对总分按从高到低的程序输入 [降序] -- 应用别名排序
SELECT `name` , (chinese + english + math) AS total_score FROM student
ORDER BY total_score DESC;
-- 对姓韩的学生问题 [总分] 排序输入(升序) where + order by
SELECT `name`, (chinese + english + math) AS total_score FROM student
WHERE `name` LIKE '韩 %'
ORDER BY total_score;共计 / 统计函数
count
-- 演示 mysql 的统计函数的应用
-- 统计一个班级共有多少学生？SELECT COUNT(*) FROM student;
-- 统计数学问题大于 90 的学生有多少个？SELECT COUNT(*) FROM student
WHERE math > 90
-- 统计总分大于 250 的人数有多少？SELECT COUNT(*) FROM student
WHERE (math + english + chinese) > 250
-- count(*) 和 count(列) 的区别
-- 解释 :count(*) 返回满足条件的记录的行数
-- count(列): 统计满足条件的某列有多少个，然而会排除 为 null 的状况
CREATE TABLE t15 (`name` VARCHAR(20));
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);
SELECT * FROM t15;
SELECT COUNT(*) FROM t15; -- 4
SELECT COUNT(`name`) FROM t15;-- 3
-- 演示 sum 函数的应用
-- 统计一个班级数学总成绩？SELECT SUM(math) FROM student;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student;
-- 统计一个班级语文、英语、数学的问题总和
SELECT SUM(math + english + chinese) FROM student;
-- 统计一个班级语文问题平均分
SELECT SUM(chinese)/ COUNT(*) FROM student;
SELECT SUM(`name`) FROM student;
-- 演示 avg 的应用
-- 练习：-- 求一个班级数学平均分？SELECT AVG(math) FROM student;
-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;
-- 演示 max 和 min 的应用
-- 求班级最高分和最低分（数值范畴在统计中特地有用）SELECT MAX(math + english + chinese), MIN(math + english + chinese)
FROM student;
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre
FROM student;sum
Sum 函数返回满足 where 条件的行的和 个别应用在数值列。
avg
AVG 函数返回满足 where 条件的一列的平均值
max/min
Max/min 函数返回满足 where 条件的一列的最大 / 最小值
应用 group by 子句对列进行分组
应用 having 子句对分组后的后果进行过滤
group by 用于对查问的后果分组统计,(示意图)
having 子句用于限度分组显示后果
CREATE TABLE dept( /* 部门表 */
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
SELECT * FROM dept;
-- 员工表
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /* 编号 */
ename VARCHAR(20) NOT NULL DEFAULT "", /* 名字 */
job VARCHAR(9) NOT NULL DEFAULT "",/* 工作 */
mgr MEDIUMINT UNSIGNED ,/* 下级编号 */
hiredate DATE NOT NULL,/* 入职工夫 */
sal DECIMAL(7,2) NOT NULL,/* 薪水 */
comm DECIMAL(7,2) ,/* 红利 奖金 */
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /* 部门编号 */
);
-- 增加测试数据
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
SELECT * FROM emp;
-- 工资级别
#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /* 工资级别 */
losal DECIMAL(17,2) NOT NULL, /* 该级别的最低工资 */
hisal DECIMAL(17,2) NOT NULL /* 该级别的最高工资 */
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
SELECT * FROM salgrade;
SELECT * FROM dept;
SELECT * FROM emp;
# 演示 group by + having
GROUP by 用于对查问的后果分组统计, (示意图)
-- having 子句用于限度分组显示后果.
-- ? 如何显示每个部门的平均工资和最高工资
-- 老韩剖析: avg(sal) max(sal)
-- 依照局部来分组查问
SELECT AVG(sal), MAX(sal) , deptno
FROM emp GROUP BY deptno;
-- 应用数学方法，对小数点进行解决
SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno
FROM emp GROUP BY deptno;
-- ? 显示每个部门的每种岗位的平均工资和最低工资
-- 老师剖析 1. 显示每个部门的平均工资和最低工资
-- 2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal) , deptno, job
FROM emp GROUP BY deptno, job;
-- ? 显示平均工资低于 2000 的部门号和它的平均工资 // 别名
-- 老师剖析 [写 sql 语句的思路是化繁为简, 各个击破]
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在 1 的后果根底上，进行过滤，保留 AVG(sal) < 2000
-- 3. 应用别名进行过滤
SELECT AVG(sal), deptno
FROM emp GROUP BY deptno
HAVING AVG(sal) < 2000;
-- 应用别名
SELECT AVG(sal) AS avg_sal, deptno
FROM emp GROUP BY deptno
HAVING avg_sal < 2000; 字符串相干函数
-- 演示字符串相干函数的应用，应用 emp 表来演示
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp;
-- CONCAT (string2 [,...]) 连贯字串, 将多个列拼接成一列
SELECT CONCAT(ename, '工作是', job) FROM emp;
-- INSTR (string ,substring) 返回 substring 在 string 中呈现的地位, 没有返回 0
-- dual 亚元表, 零碎表 能够作为测试表应用
SELECT INSTR('hanshunping', 'ping') FROM DUAL;
-- UCASE (string2) 转换成大写
SELECT UCASE(ename) FROM emp;
-- LCASE (string2) 转换成小写
SELECT LCASE(ename) FROM emp;
-- LEFT (string2 ,length) 从 string2 中的右边起取 length 个字符
-- RIGHT (string2 ,length) 从 string2 中的左边起取 length 个字符
SELECT LEFT(ename, 2) FROM emp;
-- LENGTH (string) string 长度[依照字节]
SELECT LENGTH(ename) FROM emp;
-- REPLACE (str ,search_str ,replace_str)
-- 在 str 中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;
-- STRCMP (string1 ,string2) 逐字符比拟两字串大小
SELECT STRCMP('hsp', 'hsp') FROM DUAL;
-- SUBSTRING (str , position [,length])
-- 从 str 的 position 开始【从 1 开始计算】, 取 length 个字符
-- 从 ename 列的第一个地位开始取出 2 个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp;
-- LTRIM (string2) RTRIM (string2) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM('韩顺平教育') FROM DUAL;
SELECT RTRIM('韩顺平教育') FROM DUAL;
SELECT TRIM('韩顺平教育') FROM DUAL;
-- 练习: 以首字母小写的形式显示所有员工 emp 表的姓名
-- 办法 1
-- 思路先取出 ename 的第一个字符，转成小写的
-- 把他和前面的字符串进行拼接输入即可
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2)) AS new_name
FROM emp;
SELECT CONCAT(LCASE(LEFT(ename,1)), SUBSTRING(ename,2)) AS new_name
FROM emp; 数学相干函数
rand() 返回一个随机浮点值 v, 范畴在 0 到 1 之间(即, 其范畴为 0 <=v<=1.0)。若已指定一个整数参数 N, 则它被用作种子值, 用来产生反复序列。
-- 演示数学相干函数
-- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL;
-- BIN (decimal_number)十进制转二进制
SELECT BIN(10) FROM DUAL;
-- CEILING (number2) 向上取整, 失去比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;
-- CONV(number2,from_base,to_base) 进制转换
-- 上面的含意是 8 是十进制的 8, 转成 2 进制输入
SELECT CONV(8, 10, 2) FROM DUAL;
-- 上面的含意是 8 是 16 进制的 8, 转成 2 进制输入
SELECT CONV(16, 16, 10) FROM DUAL;
-- FLOOR (number2) 向下取整, 失去比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;
-- FORMAT (number,decimal_places) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL;
-- HEX (DecimalNumber) 转十六进制
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL;
-- MOD (numerator ,denominator) 求余
SELECT MOD(10, 3) FROM DUAL;
-- RAND([seed]) RAND([seed]) 返回随机数 其范畴为 0 ≤ v ≤ 1.0
-- 1. 如果应用 rand() 每次返回不同的随机数，在 0 ≤ v ≤ 1.0
-- 2. 如果应用 rand(seed) 返回随机数, 范畴 0 ≤ v ≤ 1.0, 如果 seed 不变，该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;工夫日期相干函数
-- 日期工夫相干函数
-- CURRENT_DATE ( ) 以后日期
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME ( ) 以后工夫
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP ( ) 以后工夫戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- 创立测试表 信息表
CREATE TABLE mes(
id INT ,
content VARCHAR(30),
send_time DATETIME);
-- 增加一条记录
INSERT INTO mes
VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());
SELECT * FROM mes;
SELECT NOW() FROM DUAL;
-- 上利用实例
-- 显示所有新闻信息，公布日期只显示 日期，不必显示工夫.
SELECT id, content, DATE(send_time)
FROM mes;
-- 请查问在 10 分钟内公布的新闻, 思路肯定要梳理一下.
SELECT *
FROM mes
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()
SELECT *
FROM mes
WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;
-- 请用 mysql 的 sql 语句求出你活了多少天? [练习] 1986-11-11 出世
SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL;
-- 如果你能活 80 岁，求出你还能活多少天.[练习] 1986-11-11 出世
-- 先求出活 80 岁 时, 是什么日期 X
-- 而后在应用 datediff(x, now()); 1986-11-11->datetime
-- INTERVAL 80 YEAR：YEAR 能够是 年月日，时分秒
-- '1986-11-11' 能够 date,datetime timestamp
SELECT DATEDIFF(DATE_ADD('1986-11-11', INTERVAL 80 YEAR), NOW())
FROM DUAL;
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;
-- YEAR|Month|DAY|DATE (datetime)
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL;
-- unix_timestamp() : 返回的是 1970-1-1 到当初的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() : 能够把一个 unix_timestamp 秒数[工夫戳]，转成指定格局的日期
-- %Y-%m-%d 格局是规定好的，示意年月日
-- 意义：在开发中，能够寄存一个整数，而后示意工夫，通过 FROM_UNIXTIME 转换
--
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;
SELECT * FROM mysql.user \G 加密和零碎函数
-- 演示加密函数和零碎函数
-- USER() 查问用户
-- 能够查看登录到 mysql 的有哪些用户，以及登录的 IP
SELECT USER() FROM DUAL; -- 返回 用户 @IP 地址
-- DATABASE() 查问以后应用数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32 的字符串，罕用 (用户明码) 加密
-- root 明码是 timerring -> 加密 md5 -> 在数据库中寄存的是加密后的明码
SELECT MD5('timerring') FROM DUAL;
SELECT LENGTH(MD5('timerring')) FROM DUAL;
-- 演示用户表，寄存明码时，是 md5
CREATE TABLE timerring_user
(id INT ,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO timerring_user
VALUES(100, '谈莫瑞', MD5('timerring'));
SELECT * FROM timerring_user;
SELECT * FROM timerring_user -- SQL 注入问题
WHERE `name`='谈莫瑞' AND pwd = MD5('timerring')
-- PASSWORD(str) -- 加密函数, MySQL 数据库的用户明码就是 PASSWORD 函数加密
SELECT PASSWORD('timerring') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC
-- select * from mysql.user \G 从原文明码 str 计算并返回明码字符串
-- 通常用于对 mysql 数据库的用户明码加密
-- mysql.user 示意 数据库. 表
SELECT * FROM mysql.user流程管制函数
# 演示流程管制语句
# IF(expr1,expr2,expr3) 如果 expr1 为 True , 则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2) 如果 expr1 不为空 NULL, 则返回 expr1, 否则返回 expr2
SELECT IFNULL(NULL, '教育') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [相似多重分支.]
# 如果 expr1 为 TRUE, 则返回 expr2, 如果 expr2 为 t, 返回 expr4, 否则返回 expr5
SELECT CASE
WHEN TRUE THEN 'jack' -- jack
WHEN FALSE THEN 'tom'
ELSE 'mary' END
-- 1. 查问 emp 表, 如果 comm 是 null , 则显示 0.0
-- 老师阐明，判断是否为 null 要应用 is null, 判断不为空 应用 is not
SELECT ename, IF(comm IS NULL , 0.0, comm)
FROM emp;
SELECT ename, IFNULL(comm, 0.0)
FROM emp;
-- 2. 如果 emp 表的 job 是 CLERK 则显示 职员，如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它失常显示
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS 'job'
FROM emp;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;mysql 表查问 – 增强
介绍
-- 查问增强
-- ■ 应用 where 子句
-- ? 如何查找 1992.1.1 后入职的员工
-- 老师阐明：在 mysql 中, 日期类型能够间接比拟, 须要留神格局
SELECT * FROM emp
WHERE hiredate > '1992-01-01'
-- ■ 如何应用 like 操作符(含糊)
-- %: 示意 0 到多个任意字符 _: 示意单个任意字符
-- ? 如何显示首字符为 S 的员工姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%'
-- ? 如何显示第三个字符为大写 O 的所有员工的姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%'
-- ■ 如何显示没有下级的雇员的状况
SELECT * FROM emp
WHERE mgr IS NULL;
-- ■ 查问表构造
DESC emp
-- 应用 order by 子句
-- ? 如何依照工资的从低到高的程序[升序]，显示雇员的信息
SELECT * FROM emp
ORDER BY sal
-- ? 依照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC , sal DESC;分页查问
根本语法: select.... limit start, rows
示意从 start+1 行开始取, 取出 rows 行, start 从 0 开始计算
-- 分页查问
-- 按雇员的 id 号升序取出，每页显示 3 条记录，请别离显示 第 1 页，第 2 页，第 3 页
-- 第 1 页
SELECT * FROM emp
ORDER BY empno
LIMIT 0, 3;
-- 第 2 页
SELECT * FROM emp
ORDER BY empno
LIMIT 3, 3;
-- 第 3 页
SELECT * FROM emp
ORDER BY empno
LIMIT 6, 3;
-- 推导一个公式
SELECT * FROM emp
ORDER BY empno
LIMIT 每页显示记录数 * (第几页 -1) , 每页显示记录数
-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY job;
-- 显示雇员总数，以及取得贴补的雇员数
SELECT COUNT(*) FROM emp WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;应用分组函数和分组子句
-- 加强 group by 的应用
-- (1) 显示每种岗位的雇员总数、平均工资。SELECT COUNT(*), AVG(sal), job
FROM emp
GROUP BY job;
-- (2) 显示雇员总数，以及取得贴补的雇员数。-- 思路: 取得贴补的雇员数 就是 comm 列为非 null, 就是 count(列)，如果该列的值为 null, 是不会统计 , SQL 非常灵活，须要咱们动脑筋.
SELECT COUNT(*), COUNT(comm)
FROM emp
-- 老师的扩大要求：统计没有取得贴补的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
FROM emp
SELECT COUNT(*), COUNT(*) - COUNT(comm)
FROM emp
-- (3) 显示管理者的总人数。小技巧: 尝试写 -> 批改 -> 尝试[正确的]
SELECT COUNT(DISTINCT mgr)
FROM emp;
-- (4) 显示雇员工资的最大差额。-- 思路：max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal)
FROM emp;
SELECT * FROM e
mp;
select * from dept;
-- 利用案例：请统计各个部门 group by 的平均工资 avg，-- 并且是大于 1000 的 having，并且依照平均工资从高到低排序，order by
-- 取出前两行记录 limit 0, 2
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0,2 数据分组的总结
如果 select 语句同时蕴含有 group by ,having , limitorder by 那么他们的程序是group by , having , orderby, limit
mysql 多表查问
多表查问练习
在默认状况下: 当两个表查问时，规定
- 从第一张表中，取出一行和第二张表的每一行进行组合, 返回后果[含有两张表的所有列].
- 一共返回的记录数第一张表行数 * 第二张表的行数（笛卡尔积）
-- 多表查问
-- ? 显示雇员名, 雇员工资及所在部门的名字【笛卡尔集】/*
1. 雇员名, 雇员工资 来自 emp 表
2. 部门的名字 来自 dept 表
3. 需要对 emp 和 dept 查问 ename,sal,dname,deptno
4. 当咱们须要指定显示某个表的列是，须要 表. 列表
*/
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno
select * from emp;
select * from dept;
select * from salgrade;
-- 小技巧：多表查问的条件不能少于 表的个数 -1, 否则会呈现笛卡尔集
-- ? 如何显示部门号为 10 的部门名、员工名和工资
-- 反复的 deptno 要标清是哪个表的
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno and emp.deptno = 10
-- ? 显示各个员工的姓名，工资，及其工资的级别
-- 思路 姓名，工资 来自 emp 13
-- 工资级别 salgrade 5
-- 写 sql , 先写一个简略，而后退出过滤条件...
select ename, sal, grade
from emp , salgrade
where sal between losal and hisal; 自连贯
自连贯是指在同一张表的连贯查问[将同一张表看做两张表]。
-- 多表查问的 自连贯
-- 思考题: 显示公司员工名字和他的下级的名字
-- 老韩剖析：员工名字 在 emp, 下级的名字的名字 emp
-- 员工和下级是通过 emp 表的 mgr 列关联
-- 这里老师小结：-- 自连贯的特点 1. 把同一张表当做两张表应用
-- 2. 须要给表取别名 表名 表别名
-- 3. 列名不明确，能够指定列的别名 列名 as 列的别名
SELECT worker.ename AS '职员名' , boss.ename AS '下级名'
FROM emp worker, emp boss -- 同一张表用不同的别名
WHERE worker.mgr = boss.empno;
SELECT * FROM emp;mysql 表子查问
什么是子查问
子查问是指嵌入在其它 sql 语句中的 select 语句, 也叫嵌套查问
单行子查问
单行子查问是指只返回一行数据的子查问语句
多行子查问
多行子查问指返回多行数据的子查问 应用关键字in
-- 子查问的演示
-- 请思考：如何显示与 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 子查问当做长期表应用
能够将子查问当做一张长期表应用
-- 子查问练习
-- 请思考：查找每个部门工资高于本部门平均工资的人的材料
-- 这里要用到数据查问的小技巧，把一个子查问当作一个长期表应用
-- 1. 先失去每个部门的 部门号和 对应的平均工资
SELECT deptno, AVG(sal) AS avg_sal
FROM emp GROUP BY deptno
-- 2. 把下面的后果当做子查问，作为 temp 表, 和 emp 进行多表查问
SELECT ename, sal, temp.avg_sal, emp.deptno
FROM emp, (SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
) temp
where emp.deptno = temp.deptno and emp.sal > temp.avg_sal
-- 查找每个部门工资最高的人的详细资料
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.deptno = temp.deptno AND emp.sal = temp.max_sal
-- 查问每个部门的信息 (包含：部门名, 编号, 地址) 和人员数量, 咱们一起实现。-- 1. 部门名, 编号, 地址 来自 dept 表
-- 2. 各个部门的人员数量 -》构建一个长期表
select count(*), deptno
from emp
group by deptno;
select dname, dept.deptno, loc , tmp.per_num as '人数'
from dept, (SELECT COUNT(*) as per_num, deptno
FROM emp
GROUP BY deptno
) tmp
where tmp.deptno = dept.deptno
-- 还有一种写法 表.* 示意将该表所有列都显示进去, 能够简化 sql 语句
-- 在多表查问中，当多个表的列不反复时，才能够间接写列名
SELECT tmp.* , dname, loc
FROM dept, (SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno在多行子查问中应用 all 操作符
在多行子查问中应用 any 操作符
-- all 和 any 的应用
-- 请思考: 显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > ALL(
SELECT sal
FROM emp
WHERE deptno = 30
)
-- 能够这样写
SELECT ename, sal, deptno
FROM emp
WHERE sal > (SELECT MAX(sal)
FROM emp
WHERE deptno = 30
)
-- 请思考: 如何显示工资比部门 30 的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > any(
SELECT sal
FROM emp
WHERE deptno = 30
)
SELECT ename, sal, deptno
FROM emp
WHERE sal > (SELECT min(sal)
FROM emp
WHERE deptno = 30
)
-- 查问 ecshop 中各个类别中，价格最高的商品
-- 查问 商品表
-- 先失去 各个类别中，价格最高的商品 max + group by cat_id, 当做长期表
-- 把子查问当做一张长期表能够解决很多很多简单的查问
select cat_id , max(shop_price)
from ecs_goods
group by cat_id
-- 这个最初答案
select goods_id, ecs_goods.cat_id, goods_name, shop_price
from (SELECT cat_id , MAX(shop_price) as max_price
FROM ecs_goods
GROUP BY cat_id
) temp , ecs_goods
where temp.cat_id = ecs_goods.cat_id
and temp.max_price = ecs_goods.shop_price 多列子查问
多列子查序则是指查问返回多个列数据的子查问语句
-- 多列子查问
-- 请思考如何查问与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 自己)
-- (字段 1，字段 2 ...) = (select 字段 1，字段 2 from。。。。)
-- 剖析: 1. 失去 smith 的部门和岗位
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
-- 剖析: 2 把下面的查问当做子查问来应用，并且应用多列子查问的语法进行匹配
SELECT *
FROM emp
WHERE (deptno , job) = (
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
) AND ename != 'ALLEN'
-- 请查问 和宋江数学，英语，语文
-- 问题 完全相同的学生
SELECT *
FROM student
WHERE (math, english, chinese) = (
SELECT math, english, chinese
FROM student
WHERE `name` = '宋江'
)
SELECT * FROM student;在 from 子句中应用子查问
-- 子查问练习
-- 请思考：查找每个部门工资高于本部门平均工资的人的材料
-- 这里要用到数据查问的小技巧，把一个子查问当作一个长期表应用
-- 1. 先失去每个部门的 部门号和 对应的平均工资
SELECT deptno, AVG(sal) AS avg_sal
FROM emp GROUP BY deptno
-- 2. 把下面的后果当做子查问, 和 emp 进行多表查问
--
SELECT ename, sal, temp.avg_sal, emp.deptno
FROM emp, (SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
) temp
where emp.deptno = temp.deptno and emp.sal > temp.avg_sal
-- 查找每个部门工资最高的人的详细资料
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.deptno = temp.deptno AND emp.sal = temp.max_sal
-- 查问每个部门的信息 (包含：部门名, 编号, 地址) 和人员数量, 咱们一起实现。-- 1. 部门名, 编号, 地址 来自 dept 表
-- 2. 各个部门的人员数量 -》构建一个长期表
select count(*), deptno
from emp
group by deptno;
select dname, dept.deptno, loc , tmp.per_num as '人数'
from dept, (SELECT COUNT(*) as per_num, deptno
FROM emp
GROUP BY deptno
) tmp
where tmp.deptno = dept.deptno
-- 还有一种写法 表.* 示意将该表所有列都显示进去, 能够简化 sql 语句
-- 在多表查问中，当多个表的列不反复时，才能够间接写列名
SELECT tmp.* , dname, loc
FROM dept, (SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno表复制
自我复制数据(蠕虫复制)
有时，为了对某个 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;
SELECT COUNT(*) FROM my_tab01;
-- 如何删除掉一张表重复记录
-- 1. 先创立一张表 my_tab02,
-- 2. 让 my_tab02 有反复的记录
CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把 emp 表的构造(列)，复制到 my_tab02
desc my_tab02;
insert into my_tab02
select * from emp;
select * from my_tab02;
-- 3. 思考去重 my_tab02 的记录
/*
思路
(1) 先创立一张长期表 my_tmp , 该表的构造和 my_tab02 一样
(2) 把 my_tmp 的记录 通过 distinct 关键字 解决后 把记录复制到 my_tmp
(3) 革除掉 my_tab02 记录
(4) 把 my_tmp 表的记录复制到 my_tab02
(5) drop 掉 长期表 my_tmp
*/
-- (1) 先创立一张长期表 my_tmp , 该表的构造和 my_tab02 一样
create table my_tmp like my_tab02
-- (2) 把 my_tmp 的记录 通过 distinct 关键字 解决后 把记录复制到 my_tmp
insert into my_tmp
select distinct * from my_tab02;
-- (3) 革除掉 my_tab02 记录
delete from my_tab02;
-- (4) 把 my_tmp 表的记录复制到 my_tab02
insert into my_tab02
select * from my_tmp;
-- (5) drop 掉 长期表 my_tmp
drop table my_tmp;
select * from my_tab02;合并查问
介绍
有时在理论利用中，为了合并多个 select 语句的后果，能够应用汇合操作符号 union , union all。
- union all：该操作符用于获得两个后果集的并集。当应用该操作符时，不会勾销反复行。
- union：该操作赋与 union all 类似, 然而会主动去掉后果集中反复行
-- 合并查问
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union all 就是将两个查问后果合并，不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union 就是将两个查问后果合并，会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3mysql 表外连贯
后面咱们学习的查问，是利用 where 子句对两张表或者多张表，造成的笛卡尔积进行筛选, 依据关联条件，显示所有匹配的记录，匹配不上的，不显示。比方: 列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。
外连贯
- 左外连贯(如果左侧的表齐全显示咱们就说是左外连贯)
- 右外连贯(如果右侧的表齐全显示咱们就说是右外连贯)
- 应用左连贯(显示所有人的问题，如果没有问题，也要显示该人的姓名和 id 号, 问题显示为空)select .. from 表 1 left join 表 2 on 条件[表 1: 就是左表表 2: 就是右表]。
- 应用右外连贯(显示所有问题, 如果没有名字匹配, 显示空)
select .. from 表 1 right join 表 2 on 条件[表 1: 就是左表表 2: 就是右表]
-- 外连贯
-- 比方：列出部门名称和这些部门的员工名称和工作，-- 同时要求 显示出那些没有员工的部门。-- 应用咱们学习过的多表查问的 SQL，看看成果如何?
SELECT dname, ename, job
FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY dname
SELECT * FROM dept;
SELECT * FROM emp;
-- 创立 stu
/*
id name
1 Jack
2 Tom
3 Kity
4 nono
*/
CREATE TABLE stu (
id INT,
`name` VARCHAR(32));
INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');
SELECT * FROM stu;
-- 创立 exam
/*
id grade
1 56
2 76
11 8
*/
CREATE TABLE exam(
id INT,
grade INT);
INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
SELECT * FROM exam;
-- 应用左连贯
--（显示所有人的问题，如果没有问题，也要显示该人的姓名和 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) 和左表没有匹配的记录，也会把右表的记录显示进去
SELECT `name`, stu.id, grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;
-- 列出部门名称和这些部门的员工信息(名字和工作)，-- 同时列出那些没有员工的部门名。5min
-- 应用左外连贯实现
SELECT dname, ename, job
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno
-- 应用右外连贯实现
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptnomysql 束缚
根本介绍
束缚 用于确保数据库的数据满足特定的商业规定。在 mysql 中，束缚包含: not null、unique, primary key, foreign key, 和 check 五种.
primary key(主键)
字段名 字段类型 primary key（示意该字段为主键）
用于惟一的标示表行的数据, 当定义主键束缚后，该列不能反复
- primary key 不能反复而且不能为 null.
- 一张表最多只能有一个主键, 但能够是复合主键
-
主键的指定形式有两种
- 间接在字段名后指定: 字段名 primakry key
- 在表定义最初写 primary key(列名);
- 应用 desc 表名，能够看到 primary key 的状况。
- 在理论开发中，每个表往往都会设计一个主键。
-- 主键应用
-- id name email
CREATE TABLE t17
(id INT PRIMARY KEY, -- 示意 id 列是主键
`name` VARCHAR(32),
email VARCHAR(32));
-- 主键列的值是不能够反复
INSERT INTO t17
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t17
VALUES(2, 'tom', 'tom@sohu.com');
INSERT INTO t17
VALUES(1, 'hsp', 'hsp@sohu.com'); -- 报错
SELECT * FROM t17;
-- 主键应用的细节探讨
-- primary key 不能反复而且不能为 null。INSERT INTO t17
VALUES(NULL, 'hsp', 'hsp@sohu.com');
-- 一张表最多只能有一个主键, 但能够是复合主键(比方 id+name)
CREATE TABLE t18
(id INT PRIMARY KEY, -- 示意 id 列是主键
`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, 'tom', 'tom@sohu.com');
INSERT INTO t18
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t18
VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键
SELECT * FROM t18;
-- 主键的指定形式 有两种
-- 1. 间接在字段名后指定：字段名 primakry key
-- 2. 在表定义最初写 primary key(列名);
CREATE TABLE t19
(id INT ,
`name` VARCHAR(32) PRIMARY KEY,
email VARCHAR(32)
);
CREATE TABLE t20
(id INT ,
`name` VARCHAR(32) ,
email VARCHAR(32),
PRIMARY KEY(`name`) -- 在表定义最初写 primary key(列名)
);
-- 应用 desc 表名，能够看到 primary key 的状况
DESC t20 -- 查看 t20 表的后果，显示束缚的状况
DESC t18not null(非空)
如果在列上定义了 not null, 那么当插入数据时，必须为列提供数据。
字段名 字段类型 not null
unique(惟一)
当定义了惟一束缚后，该列值是不能反复的。
字段名 字段类型 unique
- 如果没有指定 not null，则 unique 字段 能够有多个 null
- 一张表能够有多个 unique 字段
-- unique 的应用
CREATE TABLE t21
(id INT UNIQUE , -- 示意 id 列是不能够反复的.
`name` VARCHAR(32) ,
email VARCHAR(32)
);
INSERT INTO t21
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t21
VALUES(1, 'tom', 'tom@sohu.com');
-- unqiue 应用细节
-- 1. 如果没有指定 not null , 则 unique 字段能够有多个 null
-- 如果一个列(字段)，是 unique not null 应用成果相似 primary key
INSERT INTO t21
VALUES(NULL, 'tom', 'tom@sohu.com');
SELECT * FROM t21;
-- 2. 一张表能够有多个 unique 字段
CREATE TABLE t22
(id INT UNIQUE , -- 示意 id 列是不能够反复的.
`name` VARCHAR(32) UNIQUE , -- 示意 name 不能够反复
email VARCHAR(32)
);
DESC t22foreign key(外键)
用于定义主表和从表之间的关系:
- 外键束缚要定义在从表上，主表则必须具备主键束缚或是 unique 束缚,
- 当定义外键束缚后，要求外键列数据必须在主表的主键列存在或是为 null(学生 / 班级图示)。
-- 外键演示
-- 创立 主表 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))
-- 测试数据
INSERT INTO my_class
VALUES(100, 'java'), (200, 'web');
INSERT INTO my_class
VALUES(300, 'php');
SELECT * FROM my_class;
INSERT INTO my_stu
VALUES(1, 'tom', 100);
INSERT INTO my_stu
VALUES(2, 'jack', 200);
INSERT INTO my_stu
VALUES(3, 'hsp', 300);
INSERT INTO my_stu
VALUES(4, 'mary', 400); -- 这里会失败... 因为 400 班级不存在
INSERT INTO my_stu
VALUES(5, 'king', NULL); -- 能够, 外键 没有写 not null
SELECT * FROM my_class;
-- 一旦建设主外键的关系，数据不能随便删除了
DELETE FROM my_class
WHERE id = 100; check
用于强制行数据必须满足的条件, 假设在 sal 列上定义了 check 束缚, 并要求 sal 列值在 1000 \~2000 之间，如果不在 1000\~2000 之间就会提醒出错。
提醒:oracle 和 sql server 均反对 check , 然而 mysql5.7 目前还不反对 check , 只做语法校验，但不会失效。
-- 演示 check 的应用
-- mysql5.7 目前还不反对 check , 只做语法校验，但不会失效
-- 理解
-- 学习 oracle, sql server, 这两个数据库是真的失效.
-- 测试
CREATE TABLE t23 (
id INT PRIMARY KEY,
`name` VARCHAR(32) ,
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK (sal > 1000 AND sal < 2000)
);
-- 增加数据
INSERT INTO t23
VALUES(1, 'jack', 'mid', 1);
SELECT * FROM t23;商店售货零碎表设计案例
-- 应用束缚的课堂练习
CREATE DATABASE shop_db;
-- 现有一个商店的数据库 shop_db，记录客户及其购物状况，由上面三个表组成：-- 商品 goods（商品号 goods_id，商品名 goods_name，单价 unitprice，商品类别 category，供应商 provider);
-- 客户 customer（客户号 customer_id, 姓名 name, 住址 address, 电邮 email 性别 sex, 身份证 card_Id);
-- 购买 purchase（购买订单号 order_id，客户号 customer_id, 商品号 goods_id, 购买数量 nums);
-- 1 建表，在定义中要求申明 [进行正当设计]：-- (1)每个表的主外键；-- (2)客户的姓名不能为空值；-- (3)电邮不可能反复;
-- (4)客户的性别[男 | 女] check 枚举..
-- (5)单价 unitprice 在 1.0 - 9999.99 之间 check
-- 商品 goods
CREATE TABLE goods (
goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0
CHECK (unitprice >= 1.0 AND unitprice <= 9999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(64) NOT NULL DEFAULT '');
-- 客户 customer（客户号 customer_id, 姓名 name, 住址 address, 电邮 email 性别 sex,
-- 身份证 card_Id);
CREATE TABLE customer(customer_id CHAR(8) PRIMARY KEY, -- 程序员本人决定
`name` VARCHAR(64) NOT NULL DEFAULT '',
address VARCHAR(64) NOT NULL DEFAULT '',
email VARCHAR(64) UNIQUE NOT NULL,
sex ENUM('男','女') NOT NULL , -- 这里老师应用的枚举类型, 是失效
card_Id CHAR(18));
-- 购买 purchase（购买订单号 order_id，客户号 customer_id, 商品号 goods_id,
-- 购买数量 nums);
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;自增长
自增长根本介绍
在某张表中，存在一个 id 列(整数), 咱们心愿在增加记录的时候. 该列从 1 开始, 主动的增长，怎么解决?
自增长应用细节
- 一般来说自增长是和 primary key 配合应用的
- 自增长也能够独自应用[然而须要配合一个 unique]
- 自增长润饰的字段为整数型的(尽管小数也能够然而十分非常少这样应用)
- 自增长默认从 1 开始，你也能够通过如下命令批改
alter table 表名 auto_increment = 新的开始值; - 如果你增加数据时，给自增长字段 (列) 指定的有值，则以指定的值为准,如果指定了自增长，一般来说，就依照自增长的规定（从指定的值开始，再自增长）来增加数据
-- 演示自增长的应用
-- 创立表
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, 'tom@qq.com', 'tom'); -- 没有给值，主动保护，id 自增长
INSERT INTO t24
(email, `name`) VALUES('hsp@sohu.com', 'hsp');
SELECT * FROM t24;
-- 批改默认的自增长开始值
ALTER TABLE t25 AUTO_INCREMENT = 100
CREATE TABLE t25
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32)NOT NULL DEFAULT '',
`name` VARCHAR(32)NOT NULL DEFAULT '');
INSERT INTO t25
VALUES(NULL, 'mary@qq.com', 'mary');
INSERT INTO t25
VALUES(666, 'hsp@qq.com', 'hsp');
SELECT * FROM t25;
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); mysql 索引
索引疾速入门
进步数据库性能，索引是最物美价廉的货色了。不必加 内存，不必改程序，不必调 sql，查问速度就可能进步百倍干倍。
CREATE INDEX ename_index ON emp (ename) -- 在 ename 上创立索引-- 创立测试数据库 tmp
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 COUNT(*) FROM emp;
-- 在没有创立索引时，咱们的查问一条记录
SELECT *
FROM emp
WHERE empno = 1234567
-- 应用索引来优化一下，体验索引的牛
-- 在没有创立索引前 , emp.ibd 文件大小 是 524m
-- 创立索引后 emp.ibd 文件大小 是 655m [索引自身也会占用空间.]
-- 创立 ename 列索引,emp.ibd 文件大小 是 827m
-- empno_index 索引名称
-- ON emp (empno) : 示意在 emp 表的 empno 列创立索引
CREATE INDEX empno_index ON emp (empno)
-- 创立索引后，查问的速度如何
SELECT *
FROM emp
WHERE empno = 1234578 -- 0.003s 原来是 4.5s
-- 创立索引后，只对创立了索引的列无效
SELECT *
FROM emp
WHERE ename = 'PjDlwy' -- 没有在 ename 创立索引时，工夫 4.7s
CREATE INDEX ename_index ON emp (ename) -- 在 ename 上创立索引索引的原理
没有索引为什么会慢? 因为全表扫描.
应用索引为什么会快? 造成一个索引的数据结构，比方二叉树索引（有代价 如下）
- 磁盘占用
- 对 dml(update delete insert)语句的效率影响 删除或者插入将会对数据结构造成影响，可能会重构。
索引的类
- 主键索引，主键主动的为主索引 (类型 Primary key)
- 惟一索引(UNIQUE)
- 一般索引(INDEX)
- 全文索引(FULLTEXT)[实用于 MyISAM]
个别开发，不应用 mysql 自带的全文索引,，而是应用: 全文搜寻的框架：Solr 和 ElasticSearch (ES)
create table t1(
id int primary key, -- 主键，同时也是索引，称为主键索引.
name varchar(32));
create table t2(id int unique, -- id 是惟一的，同时也是索引，称为 unique 索引.索引应用
1. 增加索引(建小表测试 id , name)
create [UNIQUE] index index_name on tbl_name (col_ name [(length)][ASC | DESC],......);
alter table table_name ADD INDEX [index_namel (index_col_name,..)2. 增加主键(索引)
ALTER TABLE 表名 ADD PRIMARY KEY(列名...);3. 删除索引
DROP INDEX index_name ON tbl_name,
alter table table_name drop index index_name;4. 删除主键索引比拟特地:
alter table t_b drop primary key;5. 查问索引(三种形式)
show index(es) from table_name;
show keys from table_name;
desc table_Name;-- 演示 mysql 的索引的应用
-- 创立索引
CREATE TABLE t25 (
id INT ,
`name` VARCHAR(32));
-- 查问表是否有索引
SHOW INDEXES FROM t25;
-- 增加索引
-- 增加惟一索引
CREATE UNIQUE INDEX id_index ON t25 (id);
-- 增加一般索引形式 1
CREATE INDEX id_index ON t25 (id);
-- 如何抉择
-- 1. 如果某列的值，是不会反复的，则优先思考应用 unique 索引, 否则应用一般索引
-- 增加一般索引形式 2
ALTER TABLE t25 ADD INDEX id_index (id)
-- 增加主键索引
CREATE TABLE t26 (
id INT ,
`name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)
SHOW INDEX FROM t25
-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY
-- 批改索引，先删除，在增加新的索引
-- 查问索引
-- 1. 形式
SHOW INDEX FROM t25
-- 2. 形式
SHOW INDEXES FROM t25
-- 3. 形式
SHOW KEYS FROM t25
-- 4 形式
DESC t25哪些列上适宜应用索引
-
较频繁的作为查问条件字段应该创立索引
select * from emp where empno = 1 -
唯一性太差的字段不适宜独自创立索引，即便频繁作为查问条件
select * from emp where sex =‘男‘ -
更新十分频繁的字段不适宜创立索引
select* from emp where logincount = 1 - 不会呈现在 WHERE 子句中字段不该创立索引
mysql 事务
什么是事务
事务用于保证数据的一致性, 它由一组相干的 dml（数据操作语言 增删改）语句组成, 该组的 dml 语句要么全副胜利，要么全副失败。如: 转账就要用事务来解决, 用以保证数据的一致性。
示意图：
-- 事务的一个重要的概念和具体操作
-- 演示
-- 1. 创立一张测试表
CREATE TABLE t27
( id INT,
`name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION
-- 3. 设置保留点
SAVEPOINT a
-- 执行 dml 操作
INSERT INTO t27 VALUES(100, 'tom');
SELECT * FROM t27;
SAVEPOINT b
-- 执行 dml 操作
INSERT INTO t27 VALUES(200, 'jack');
-- 回退到 b
ROLLBACK TO b
-- 持续回退 a
ROLLBACK TO a
-- 如果这样, 示意间接回退到事务开始的状态.
ROLLBACK
COMMIT事务和锁
当执行事务操作时(dml 语句), mysql 会在表上加锁, 避免其它用户改表的数据，这对用户来讲是十分重要的。
mysql 数据厍控制台事务的几个重要操作
start transaction– 开始一个事务savepoint保留点名 – 设置保留点rollback to保留点名 – 回退事务rollback– 回退全副事务commit— 提交事务, 所有的操作失效, 不能回退
回退事务
在介绍回退事务前，先介绍一下保留点(savepoint)，保留点是事务中的点，用于勾销局部事务，当完结事务时(commit)，会主动的删除该事务所定义的所有保留点当执行回退事务时，通过指定保留点能够回退到指定的点。
提交事务
应用 commit 语句能够提交事务. 当执行了 commit 语句子后, 会确认事务的变动、完结事务、删除保留点、开释锁，数据失效。当应用 commit 语句完结事务后, 其它会话 [ 其余连贯 ] 将能够查着到事务变动后的新数据[所有数据就正式失效.]
事务细节探讨
- 如果不开始事务，默认状况下，dml 操作是主动提交的，不能回滚。
- 如果开始一个事务，你没有创立保留点. 你能够执行 rollback, 默认就是回退到你事务开始的状态。
- 你也能够在这个事务中(还没有提交时), 创立多个保留点. 比方: savepoint
aaa; 执行 dml , savepoint bbb; - 你能够在事务没有提交前, 抉择回退到哪个保留点。
- mysql 的事务机制须要innodb 的存储引擎才能够应用，myisam 不好使。
- 开始一个事务 start transaction 或者能够写 set autocommit=off;
-- 探讨 事务细节
-- 1. 如果不开始事务，默认状况下，dml 操作是主动提交的，不能回滚
INSERT INTO t27 VALUES(300, 'milan'); -- 主动提交 commit
SELECT * FROM t27
-- 2. 如果开始一个事务，你没有创立保留点. 你能够执行 rollback，-- 默认就是回退到你事务开始的状态
START TRANSACTION
INSERT INTO t27 VALUES(400, 'king');
INSERT INTO t27 VALUES(500, 'scott');
ROLLBACK -- 示意间接回退到事务开始的的状态
COMMIT;
-- 3. 你也能够在这个事务中(还没有提交时), 创立多个保留点. 比方: savepoint aaa;
-- 执行 dml , savepoint bbb
-- 4. 你能够在事务没有提交前，抉择回退到哪个保留点
-- 5. InnoDB 存储引擎反对事务 , MyISAM 不反对
-- 6. 开始一个事务 start transaction 或者能够写 set autocommit=off;mysql 事务隔离级别
事务隔离级别介绍
- 多个连贯开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保障各个连贯在获取数据时的准确性。(艰深解释：每一个事务看到同一张表的数据不一样)
- 如果不思考隔离性, 可能会引发如下问题:
- 脏读
- 不可反复读
- 幻读
查看事务隔离级别
- 脏读 (dirty read): 当一个事务读取另一个事务尚未提交的扭转(update,insert,delete) 时, 产生脏读。（未 commit）
- 不可反复读(nonrepeatable read): 同一查问在同一事务中屡次进行，因为其余提交事务所做的批改或删除, 每次返回不同的后果集，此时产生不可反复读。（已 commit）
- 幻读(phantom read): 同一查问在同一事务中屡次进行，因为其余提交事务所做的插入操作，每次返回不同的后果集，此时产生幻读。（已 commit）
事务隔离级别
概念: Mysql 隔离级别定义了事务与事务之间的隔离水平。
加锁 会在其余线程操作数据库时，不操作数据库，本人卡住，期待其余线程 commit 后才进入数据库。
mysql 的事务隔离级 – 案例
-- 演示 mysql 的事务隔离级别
-- 1. 开了两个 mysql 的控制台
-- 2. 查看以后 mysql 的隔离级别
SELECT @@tx_isolation;
-- mysql> SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+
-- 3. 把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 4. 创立表
CREATE TABLE `account`(
id INT,
`name` VARCHAR(32),
money INT);
-- 查看以后会话隔离级别
SELECT @@tx_isolation
-- 查看零碎以后隔离级别
SELECT @@global.tx_isolation
-- 设置以后会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置零碎以后隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]设置事务隔离级别
-
查看以后会话隔离级别
select @@tx_isolation; -
查看零碎以后隔离级别
select @@global.tx_isolation; -
设置以后会话隔离级别
set session transaction isolation level repeatable read; -
设置零碎以后隔离级别
set global transaction isolation level repeatable read; - mysql 默认的事务隔离级别是
repeatable read，个别状况下，没有非凡
要求, 没有必要批改(因为该级别能够满足绝大部分我的项目需要)
如果想要批改默认隔离级别，能够全局批改，批改 my.ini 配置文件，在最初加上
# 可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ,SERIALIZABLE.
[mysqld]
transaction-isolation = REPEATABLE-READmysql 事务 ACID
事务的 acid 个性
- 原子性 (Atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都产生，要么都不产生。 - 一致性 (Consistency)
事务必须使数据库从一个二致性状态变换到另外一个一致性状态 - 隔离性 (lsolation)
事务的隔离性是多个用户并发拜访数据库时，数据库为每一个用户开启的事务，不能被其余事务的操作数据所烦扰，多个并发事务之间要互相隔离。 - 持久性 (Durability)
持久性是指一个事务一旦被提交，它对数据库中数据的扭转就是永久性的，接下来即便数据库产生故障也不应该对其有任何影响。
mysql 表类型和存储引擎
根本介绍
- MySQL 的表类型由存储引擎(Storage Engines）决定，次要包含 MylSAM、innoDB、Memory 等。
- MySQL 数据表次要反对六种类型，别离是: CSV.Memory、ARCHIVE.
MRG MYISAM、MYISAM、InnoBDB。 -
这六种又分为两类
- 一类是”事务平安型”(transaction-safe) 反对事务，比方: InnoDB;
- 其余都属于第二类，称为”非事务平安型”(non-transaction-safe) 不反对事务 [mysiam 和 memory].
次要的存储引擎 / 表类型特点
细节阐明
重点介绍三种: MyISAM、InnoDB、MEMORY
- MylSAM 不反对事务、也不反对外键，但其访问速度快，对事务完整性没有要求
- InnoDB 存储引擎提供了具备提交、回滚和解体恢复能力的事务平安。然而比起
MylSAM 存储引擎，InnoDB 写的解决效率差一些并且会占用更多的磁盘空间以保留数据和索引。 - MEMORY 存储引擎应用存在内存中的内容来创立表。每个 MEMORY 表只理论对应
个磁盘文件。MEMORY 类型的表拜访十分得快，因为它的数据是放在内存中的，并且默认应用 HASH 索引。然而 一旦 MySQL 服务敞开，表中的数据就会失落掉, 表的构造还在。
三种存储引擎表应用案例
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎，是后面应用过.
-- 1. 反对事务 2. 反对外键 3. 反对行级锁
-- 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 TO t1
-- memory 存储引擎
-- 1. 数据存储在内存中[敞开了 Mysql 服务，数据失落, 然而表构造还在]
-- 2. 执行速度很快(没有 IO 读写) 3. 默认反对索引(hash 表)
CREATE TABLE t29 (
id INT,
`name` VARCHAR(32)) ENGINE MEMORY
DESC t29 -- 表构造还在
INSERT INTO t29
VALUES(1,'tom'), (2,'jack'), (3, 'hsp');
SELECT * FROM t29
-- 指令批改存储引擎
ALTER TABLE `t29` ENGINE = INNODB如何抉择表的存储引擎
- 如果你的利用不须要事务，解决的只是根本的 CRUD 操作，那么 MylSAM
是不二抉择, 速度快 - 如果须要反对事务, 抉择 lnnoDB。
- Memory 存储引擎就是将数据存储在内存中，因为没有磁盘 I / O 的期待, 速度极快。但因为是内存存储引擎，所做的任何批改在服务器重启后都将隐没。(经典用法用户的在线状态()
批改存储引擎
ALTER TABLE ` 表名 ` ENGINE = 贮存引擎;视图(view)
看一个需要
emp 表的列信息很多，有些信息是集体重要信息 (比方 sal, comm,mgr, hiredate)，如果咱们心愿某个用户只能查问 emp 表的(empno,ename, job 和 deptno) 信息，有什么方法?=> 视图
基本概念
- 视图是一个虚构表，其内容由查问定义。同实在的表一样，视图蕴含列，其数据来自 对应的实在表(基表)
-
视图和基表关系的示意图：
视图的根本应用
- create view 视图名 as select 语句
- alter view 视图名 as select 语句 – 更新成新的视图
- SHOW CREATE VIEW 视图名
- drop view 视图名 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;
SELECT empno, job FROM emp_view01;
-- 查看创立视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;
-- 视图的细节
-- 1. 创立视图后，到数据库去看，对应视图只有一个视图构造文件(模式: 视图名.frm)
-- 2. 视图的数据变动会影响到基表，基表的数据变动也会影响到视图[insert update delete]
-- 批改视图 会影响到基表
UPDATE emp_view01
SET job = 'MANAGER'
WHERE empno = 7369
SELECT * FROM emp; -- 查问基表
SELECT * FROM emp_view01
-- 批改根本表，会影响到视图
UPDATE emp
SET job = 'SALESMAN'
WHERE empno = 7369
-- 3. 视图中能够再应用视图 , 比方从 emp_view01 视图中，选出 empno, 和 ename 做出新视图
DESC emp_view01
CREATE VIEW emp_view02
AS
SELECT empno, ename FROM emp_view01
SELECT * FROM emp_view02视图细节探讨
- 创立视图后，到数据库去看，对应视图只有一个视图构造文件(模式: 视图名.frm)
- 视图的数据变动会影响到基表，基表的数据变动也会影响到视图[insert update delete]
- 视图中能够再应用视图，数据依然来自基表.
视图最佳实际
- 平安。一些数据表有着重要的信息。有些字段是窃密的，不能让用户间接看到。这时就能够创立一个视图，在这张视图中只保留一部分字段。这样，用户就能够查问本人须要的字段, 不能查看窃密的字段。
- 性能。关系数据库的数据经常会分表存储，应用外键建设这些表的之间关系。这时, 数据库查问通常会用到连贯 (JOIN)。这样做岂但麻烦，效率绝对也比拟低。如果建设一个视图，将相干的表和字段组合在一起，就能够防止应用JOIN 查问数据。
- 灵便。如果零碎中有一张旧的表，这张表因为设计的问题，行将被废除。然而，很多利用都是基于这张表，不易批改。这时就能够建设一张视图，视图中的数据间接映射到 新建 的表。这样，就能够少做很多改变，也达到了降级数据表的目标。
视图课堂练习
-- 视图的课堂练习
-- 针对 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_view03Mysql 治理
Mysql 用户
mysql 中的用户，都存储在系数据库 mysql 中user表中
其中 user 表的重要字段阐明:
- host: 容许登录的“地位”，localhost 示意该用户只容许本机登录，也能够指定 ip 地址，比方:192.168.1.100
- user: 用户名;
- authentication string: 明码，是通过 mysql 的 password()函数加密之后
的明码。
创立用户
create user '用户名'@'容许登录地位' identified by '明码'阐明: 创立用户，同时指定明码
删除用户
drop user '用户名'@'容许登录地位';用户批改明码
批改本人的明码:
set password = password('明码');批改别人的明码(须要有批改用户明码权限):
set password for '用户名'@'登录地位'=password('明码');mysql 中的权限
给用户受权
根本语法:
grant 权限列表 on 库. 对象名 to '用户名'@'登录地位'【identified by '明码'】阐明:
-
权限列表，多个权限用逗号离开
grant select on …..
grant select,delete,create on ……
grant all [privileges] on …. // 示意赋予该用户在该对象上的所有权限
-
特地阐明
*.*: 代表本零碎中的所有数据库的所有对象(表，视图，存储过程)库.*: 示意某个数据库中的所有数据对象(表，视图，存储过程等) -
identified by 能够省略，也能够写出.
(1)如果用户存在，就是同时批改该用户的明码。
(2)如果该用户不存在，就是创立该用户!
回收用户受权
根本语法:
revoke 权限列表 on 库. 对象名 from '用户名'@'登录地位';权限失效指令
如果权限没有失效，能够执行上面命令，根本语法:
FLUSH PRIVILEGES;-- Mysql 用户的治理
-- 起因：当咱们做我的项目开发时，能够依据不同的开发人员，赋给他相应的 Mysql 操作权限
-- 所以，Mysql 数据库管理人员(root), 依据须要创立不同的用户，赋给相应的权限，供人员应用
-- 1. 创立新的用户
-- 解读 (1) 'hsp_edu'@'localhost' 示意用户的残缺信息 'hsp_edu' 用户名 'localhost' 登录的 IP
-- (2) 123456 明码, 然而留神 寄存到 mysql.user 表时，是 password('123456') 加密后的明码
-- *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456'
SELECT `host`, `user`, authentication_string
FROM mysql.user
-- 2. 删除用户
DROP USER 'hsp_edu'@'localhost'
-- 3. 登录
-- root 用户批改 hsp_edu@localhost 明码, 是能够胜利.
SET PASSWORD FOR 'hsp_edu'@'localhost' = PASSWORD('123456')课堂练习题
用户治理练习题
- 创立一个用户(你的名字，拼音)，明码 123，并且只能够从本地登录，不让近程登录 mysql
- 创立库和表 testdb 下的 news 表, 要求: 应用 root 用户创立
- 给用户调配查看 news 表和增加数据的权限
- 测试看看用户是否只有这几个权限
- 批改明码为 abc , 要求: 应用 root 用户实现
- 从新登录
- 应用 root 用户删除你的用户
-- 演示 用户权限的治理
-- 创立用户 shunping 明码 123 , 从本地登录
CREATE USER 'shunping'@'localhost' IDENTIFIED BY '123'
-- 应用 root 用户创立 testdb , 表 news
CREATE DATABASE testdb
CREATE TABLE news (
id INT ,
content VARCHAR(32));
-- 增加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;
-- 给 shunping 调配查看 news 表和 增加 news 的权限
GRANT SELECT , INSERT
ON testdb.news
TO 'shunping'@'localhost'
-- 能够减少 update 权限
GRANT UPDATE
ON testdb.news
TO 'shunping'@'localhost'
-- 批改 shunping 的明码为 abc
SET PASSWORD FOR 'shunping'@'localhost' = PASSWORD('abc');
-- 回收 shunping 用户在 testdb.news 表的所有权限
REVOKE SELECT , UPDATE, INSERT ON testdb.news FROM 'shunping'@'localhost'
REVOKE ALL ON testdb.news FROM 'shunping'@'localhost'
-- 删除 shunping
DROP USER 'shunping'@'localhost'细节阐明
-
在创立用户的时候，如果不指定 Host, 则为 %，% 示意示意所有 IP 都有连贯权限
create user XX; - 你也能够这样指定
create user 'xxx'@'192.168.1.%'示意 xx 用户在192.168.1.*的 ip 能够登录 mysql - 在删除用户的时候，如果 host 不是 %, 须要明确指定‘用户 ’@’host 值 ’
-- 阐明 用户治理的细节
-- 在创立用户的时候，如果不指定 Host, 则为 % , % 示意示意所有 IP 都有连贯权限
-- create user xxx;
CREATE USER jack
SELECT `host`, `user` FROM mysql.user
-- 你也能够这样指定
-- create user 'xxx'@'192.168.1.%' 示意 xxx 用户在 192.168.1.* 的 ip 能够登录 mysql
CREATE USER 'smith'@'192.168.1.%'
-- 在删除用户的时候，如果 host 不是 %, 须要明确指定 '用户'@'host 值'
DROP USER jack -- 默认就是 DROP USER 'jack'@'%'
DROP USER 'smith'@'192.168.1.%'本章作业
1. 选择题
(1). 以下哪条语句是谬误的?[D]
A.SELECT empno,ename name,sal salary FROM emp;
B.SELECT empno,ename name,sal AS salary FROM emp;
C.SELECT ename,sal*12 AS "Annual Salary" FROM emp;
D.SELECT ename,sal*12 Annual Salary FROM emp; -- 有空格最好引起来(2). 某用户心愿显示贴补非空的所有雇员信息，应该应用哪条语句?[B]
A.SELECT ename.sal,comm FROM emp WHERE comm<>null;
B.SELECT ename,sal,comm FROM emp WHERE comm IS NOT null;
C.SELECT ename,sal,comm FROM emp WHERE comm<>0;(3). 以下哪条语句是谬误的?[C]
A.SELECT ename,sal salary FROM emp ORDER BY sal;
B.SELECT ename,sal salary FROM emp ORDER BY salary；C. SELECT ename,sal salary FROM emp ORDER BY 3;2. 写础查看 DEPT 表和 EMP 表的构造的 sql 语句
-- 2. 写出 查看 DEPT 表和 EMP 表的构造 的 sql 语句
--
DESC dept
DESC emp3. 应用简略查问语句实现:
(1)显示所有部门名称。
(2)显示所有雇员名及其全年收入 13 月(工资 + 贴补), 并指定列别名 ” 年收入”
-- 3. 应用简略查问语句实现:
-- (1) 显示所有部门名称。SELECT dname
FROM dept;
-- (2) 显示所有雇员名及其全年收入 13 月(工资 + 贴补), 并指定列别名 "年收入"
SELECT ename, (sal + IFNULL(comm,0)) * 13 AS "年收入"
FROM emp
SELECT * FROM emp;4. 限度查问数据。
(1)显示工资超过 2850 的雇员姓名和工资。
(2)显示工资不在 1500 到 2850 之间的所有雇员名及工资。
(3)显示编号为 7566 的雇员姓名及所在部门编号。
(4)显示部门 10 和 30 中工资超过 1500 的雇员名及工资。
(5)显示无管理者的雇员名及岗位。
-- 4. 限度查问数据。-- (1) 显示工资超过 2850 的雇员姓名和工资。SELECT ename, sal
FROM emp
WHERE sal > 2850
-- (2) 显示工资不在 1500 到 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, deptno
FROM emp
WHERE empno = 7566
-- (4) 显示部门 10 和 30 中工资超过 1500 的雇员名及工资。SELECT ename, job
FROM emp
WHERE (deptno = 10 OR deptno = 30) AND sal > 1500
-- (5) 显示无管理者的雇员名及岗位。SELECT ename, job
FROM emp
WHERE mgr IS NULL;5. 排序数据。
1)显示在 1991 年 2 月 1 日到 1991 年 5 月 1 日之间雇用的雇员名, 岗位及雇佣日期, 并以雇佣日期进行排序[默认]。
2)显示取得贴补的所有雇员名, 工资及贴补, 并以工资降序排序
-- 5. 排序数据。-- (1) 显示在 1991 年 2 月 1 日到 1991 年 5 月 1 日之间雇用的雇员名, 岗位及雇佣日期,
-- 并以雇佣日期进行排序[默认]。-- 思路 1. 先查问到对应后果 2. 思考排序
SELECT ename, job, hiredate
FROM emp
WHERE hiredate >= '1991-02-01' AND hiredate <= '1991-05-01'
ORDER BY hiredate
-- (2) 显示取得贴补的所有雇员名, 工资及贴补, 并以工资降序排序
SELECT ename, sal, comm
FROM emp
ORDER BY sal DESC6. 依据:emp 员工表写出正确 SQL
–1. 抉择部门 30 中的所有员工.
–2. 列出所有办事员 (CLERK) 的姓名，编号和部门编号
–3. 找出佣金高于薪金的员工.
–4. 找出佣金高于薪金 60% 的员工.
–5. 找出部门 10 中所有经理 (MANAGER) 和部门 20 中所有办事员 (CLERK) 的详细资料.
–6. 找出部门 10 中所有经理(MANAGER), 部门 20 中所有办事员(CLERK), 还有既不是经理又不是办事员但其薪金大于或等于 2000 的所有员工的详细资料.
–7. 找出收取佣金的员工的不同工作.
–8. 找出不收取佣金或收取的佣金低于 100 的员工.–9. 找出各月倒数第 3 天受雇的所有员工.
–10. 找出早于 12 年前受雇的员工.
–11. 以首字母小写的形式显示所有员工的姓名.
–12. 显示正好为 5 个字符的员工的姓名.
–13. 显示不带有 ”R” 的员工的姓名.
–14. 显示所有员工姓名的前三个字符.
–15. 显示所有员工的姓名, 用 a 替换所有 ”A”
–16. 显示满 10 年服务年限的员工的姓名和受雇日期.
–17. 显示员工的详细资料, 按姓名排序.
–18. 显示员工的姓名和受雇日期, 依据其服务年限, 将最老的员工排在最后面.
–19. 显示所有员工的姓名、工作和薪金, 按工作降序排序, 若工作雷同则按薪金排序.
–20. 显示所有员工的姓名、退出公司的年份和月份, 按受雇日期所在月排序, 若月份雷同则将最早年份的员工排在最后面.
–21. 显示在一个月为 30 天的状况所有员工的日薪金, 疏忽余数.
–22. 找出在(任何年份的)2 月受聘的所有员工。
–23. 对于每个员工, 显示其退出公司的天数.
–24. 显示姓名字段的任何地位蕴含 ”A” 的所有员工的姓名.
–25. 以年月日的形式显示所有员工的服务年限.(大略)
-- homework03
-- ------1. 抉择部门 30 中的所有员工.
SELECT * FROM
emp
WHERE deptno = 30
-- ------2. 列出所有办事员 (CLERK) 的姓名，编号和部门编号.
SELECT ename, empno, deptno, job FROM
emp
WHERE job = 'CLERK'
-- ------3. 找出佣金高于薪金的员工.
SELECT * FROM
emp
WHERE IFNULL(comm, 0) > sal
-- ------4. 找出佣金高于薪金 60% 的员工.
SELECT * FROM
emp
WHERE IFNULL(comm, 0) > sal * 0.6
-- ------5. 找出部门 10 中所有经理 (MANAGER) 和部门 20 中所有办事员 (CLERK) 的详细资料.
--
SELECT * FROM
emp
WHERE (deptno = 10 AND job = 'MANAGER')
OR (deptno = 20 AND job = 'CLERK')
-- ------6. 找出部门 10 中所有经理(MANAGER), 部门 20 中所有办事员(CLERK),
-- 还有既不是经理又不是办事员但其薪金大于或等于 2000 的所有员工的详细资料.
SELECT * FROM
emp
WHERE (deptno = 10 AND job = 'MANAGER')
OR (deptno = 20 AND job = 'CLERK')
OR (job != 'MANAGER' AND job != 'CLERK' AND sal >= 2000)
-- ------7. 找出收取佣金的员工的不同工作.
SELECT DISTINCT job
FROM emp
WHERE comm IS NOT NULL
-- ------8. 找出不收取佣金或收取的佣金低于 100 的员工.
SELECT *
FROM emp
WHERE comm IS NULL OR IFNULL(comm, 0) < 100
-- ------9. 找出各月倒数第 3 天受雇的所有员工.
-- 提醒: last_day(日期)，能够返回该日期所在月份的最初一天
-- last_day(日期) - 2 失去日期所有月份的倒数第 3 天
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(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2))
FROM emp;
-- ------12. 显示正好为 5 个字符的员工的姓名.
SELECT *
FROM emp
WHERE LENGTH(ename) = 5
-- ------13. 显示不带有 "R" 的员工的姓名.
SELECT *
FROM emp
WHERE ename NOT LIKE '%R%'
-- ------14. 显示所有员工姓名的前三个字符.
SELECT LEFT(ename,3)
FROM emp
-- ------15. 显示所有员工的姓名, 用 a 替换所有 "A"
SELECT REPLACE(ename, 'A', 'a')
FROM emp
-- ------16. 显示满 10 年服务年限的员工的姓名和受雇日期.
SELECT ename, hiredate
FROM emp
WHERE DATE_ADD(hiredate, INTERVAL 10 YEAR) <= NOW()
-- ------17. 显示员工的详细资料, 按姓名排序.
-- ------18. 显示员工的姓名和受雇日期, 依据其服务年限, 将最老的员工排在最后面.
--
SELECT ename, hiredate
FROM emp
ORDER BY hiredate
-- ------19. 显示所有员工的姓名、工作和薪金, 按工作降序排序, 若工作雷同则按薪金排序.
SELECT ename, job, sal
FROM emp
ORDER BY job DESC, sal
-- ------20. 显示所有员工的姓名、退出公司的年份和月份, 按受雇日期所在月排序,
-- 若月份雷同则将最早年份的员工排在最后面.
SELECT ename, CONCAT(YEAR(hiredate),'-', MONTH(hiredate))
FROM emp
ORDER BY MONTH(hiredate), YEAR(hiredate)
-- ------21. 显示在一个月为 30 天的状况所有员工的日薪金, 疏忽余数.
SELECT ename, FLOOR(sal / 30), sal / 30
FROM emp;
-- ------22. 找出在(任何年份的)2 月受聘的所有员工。SELECT *
FROM emp
WHERE MONTH(hiredate) = 2
-- ------23. 对于每个员工, 显示其退出公司的天数.
--
SELECT ename, DATEDIFF(NOW(), hiredate)
FROM emp
-- ------24. 显示姓名字段的任何地位蕴含 "A" 的所有员工的姓名.
SELECT *
FROM emp
WHERE ename LIKE '%A%'
-- ------25. 以年月日的形式显示所有员工的服务年限. (大略)
-- 思路 1. 先求出 工作了多少天
SELECT ename, FLOOR(DATEDIFF(NOW(), hiredate) / 365) AS "工作年",
FLOOR((DATEDIFF(NOW(), hiredate) % 365) / 31) AS "工作月",
DATEDIFF(NOW(), hiredate) % 31 AS "工作天"
FROM emp;7. 依据:emp 员工表，dept 部门表，工资 = 薪金 sal+佣金 comm 写出正确 SQL
(1)列出至多有一个员工的所有部门
(2)列出薪金比“SMITH”多的所有员工。
(3)列出受雇日期晚于其间接下级的所有员工。
(4)列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
(5)列出所有“CLERK”(办事员)的姓名及其部门名称。
(6)列出最低薪金大于 1500 的各种工作。
(7)列出在部门“SALES”(销售部)工作的员工的姓名。
(8)列出薪金高于公司均匀薪金的所有员工。
(9)列出与“SCOTT”从事雷同工作的所有员工。
(10)列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金
(11)列出在每个部门工作的员工数量、平均工资和均匀服务期限。
(12)列出所有员工的姓名、部门名称和工资。
(13)列出所有部门的详细信息和部门人数。
(14)列出各种工作的最低工资。
(15)列出 MANAGER(经理)的最低薪金。
(16)列出所有员工的年工资, 按年薪从低到高排序。
--
-- (1)．列出至多有一个员工的所有部门
/*
先查出各个部门有多少人
应用 having 子句过滤
*/
SELECT COUNT(*) AS c, deptno
FROM emp
GROUP BY deptno
HAVING c > 1
-- (2)．列出薪金比“SMITH”多的所有员工。/*
先查出 smith 的 sal => 作为子查问
而后其余员工 sal 大于 smith 即可
*/
SELECT *
FROM emp
WHERE sal > (
SELECT sal
FROM emp
WHERE ename = 'SMITH'
)
-- (3)．列出受雇日期晚于其间接下级的所有员工。/*
先把 emp 表 当做两张表 worker , leader
条件 1. worker.hiredate > leader.hiredate
2. worker.mgr = leader.empno
*/
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
-- (5)．列出所有“CLERK”（办事员）的姓名及其部门名称。SELECT ename, dname , job
FROM emp, dept
WHERE job = 'CLERK' AND emp.deptno = dept.deptno
-- (6)．列出最低薪金大于 1500 的各种工作。/*
查问各个部门的最低工资
应用 having 子句进行过滤
*/
SELECT MIN(sal) AS min_sal , job
FROM emp
GROUP BY job
HAVING min_sal > 1500
-- (7)．列出在部门“SALES”（销售部）工作的员工的姓名。SELECT ename, dname
FROM emp , dept
WHERE emp.deptno = dept.deptno AND dname = 'SALES'
-- (8)．列出薪金高于公司均匀薪金的所有员工。SELECT *
FROM emp
WHERE sal > (SELECT AVG(sal)
FROM emp
)
-- (9)．列出与“SCOTT”从事雷同工作的所有员工。SELECT *
FROM emp
WHERE job = (
SELECT job
FROM emp
WHERE ename = 'SCOTT'
) AND ename != 'SCOTT'
-- (10)．列出薪金高于所在部门 30 的工作的所有员工的薪金的员工姓名和薪金。-- 先查问出 30 部门的最高工资
SELECT ename, sal
FROM emp
WHERE sal > (SELECT MAX(sal)
FROM emp
WHERE deptno = 30
)
-- (11)．列出在每个部门工作的员工数量、平均工资和均匀服务期限(工夫单位)。-- 老师倡议，写 sql 也是一步一步实现的
SELECT COUNT(*) AS "部门员工数量", deptno , AVG(sal) AS "部门平均工资" ,
FORMAT(AVG(DATEDIFF(NOW(), hiredate) / 365 ),2) AS "均匀服务期限(年)"
FROM emp
GROUP BY deptno
-- (12)．列出所有员工的姓名、部门名称和工资。-- 就是 emp 和 dept 联结查问，连贯条件就是 emp.deptno = dept.deptno
-- (13)．列出所有部门的详细信息和部门人数。-- 1. 先失去各个部门人数 , 把上面的后果看成长期表 和 dept 表联结查问
SELECT COUNT(*) AS c , deptno
FROM emp
GROUP BY deptno
-- 2.
SELECT dept.*, tmp.c AS "部门人数"
FROM dept, (SELECT COUNT(*) AS c , deptno
FROM emp
GROUP BY deptno
) tmp
WHERE dept.deptno = tmp.deptno
-- (14)．列出各种工作的最低工资。SELECT MIN(sal), job
FROM emp
GROUP BY job
-- (15)．列出 MANAGER（经理）的最低薪金。SELECT MIN(sal), job
FROM emp
WHERE job = 'MANAGER'
-- (16)．列出所有员工的年工资, 按年薪从低到高排序。-- 1. 先失去员工的年工资
SELECT ename, (sal + IFNULL(comm, 0)) * 12 year_sal
FROM emp
ORDER BY year_sal 8. 设学校环境如下: 一个系有若干个业余，每个业余一年只招一个班，每个班有若干个学生. 现要建设对于系、学生、班级的数据库，关系模式为:
- 班 CLASS (班号 classid，业余名 subject，系名 deptname，退学年份 enrolltime，人数 num)
- 学生 STUDENT(学号 studentid，姓名 name，年龄 age，班号 classid)
- 系 DEPARTMENT (系号 departmentid，系名 deptname)
试用 SQL 语言实现以下性能:
(1)建表，在定义中要求申明:
(1)每个表的主外码。 (2) deptname 是惟一束缚。
(3)学生姓名不能为空。
(2)插入如下数据
DEPARTMENT (001，数学; 002，计算机; 003, 化学;004, 中文;005，经济;)
(3)实现以下查问性能
3.1 找出所有姓李的学生。
3.2 列出所有开设超过 1 个业余的系的名字
3.3 列出人数大于等于 30 的系的编号和名字。
(4)学校又新减少了一个物理系, 编号为 006
(5)学生张三入学，请更新相干的表
-- 实现最初一个综合的练习
-- 8. 设学校环境如下: 一个系有若干个业余，每个业余一年只招一个班，每个班有若干个学生。-- 现要建设对于系、学生、班级的数据库，关系模式为：-- 班 CLASS（班号 classid，业余名 subject，系名 deptname，退学年份 enrolltime，人数 num）-- 学生 STUDENT（学号 studentid，姓名 name，年龄 age，班号 classid）-- 系 DEPARTMENT（系号 departmentid，系名 deptname）-- 试用 SQL 语言实现以下性能：homework05.sql 10min
--
-- (1) 建表，在定义中要求申明：--（1）每个表的主外码。--（2）deptname 是惟一束缚。--（3）学生姓名不能为空。-- 创立表 系 DEPARTMENT（系号 departmentid，系名 deptname）CREATE TABLE DEPARTMENT (departmentid VARCHAR(32) PRIMARY KEY,
deptname VARCHAR(32) UNIQUE NOT NULL);
-- 班 CLASS（班号 classid，业余名 subject，系名 deptname，退学年份 enrolltime，人数 num）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));
-- 学生 STUDENT（学号 studentid，姓名 name，年龄 age，班号 classid）CREATE TABLE hsp_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 department VALUES('001','数学');
INSERT INTO department VALUES('002','计算机');
INSERT INTO department VALUES('003','化学');
INSERT INTO department VALUES('004','中文');
INSERT INTO department VALUES('005','经济');
INSERT INTO class VALUES(101,'软件','计算机',1995,20);
INSERT INTO class VALUES(102,'微电子','计算机',1996,30);
INSERT INTO class VALUES(111,'无机化学','化学',1995,29);
INSERT INTO class VALUES(112,'高分子化学','化学',1996,25);
INSERT INTO class VALUES(121,'统计数学','数学',1995,20);
INSERT INTO class VALUES(131,'古代语言','中文',1996,20);
INSERT INTO class VALUES(141,'国际贸易','经济',1997,30);
INSERT INTO class VALUES(142,'国际金融','经济',1996,14);
INSERT INTO hsp_student VALUES(8101,'张三',18,101);
INSERT INTO hsp_student VALUES(8102,'钱四',16,121);
INSERT INTO hsp_student VALUES(8103,'王玲',17,131);
INSERT INTO hsp_student VALUES(8105,'李飞',19,102);
INSERT INTO hsp_student VALUES(8109,'赵四',18,141);
INSERT INTO hsp_student VALUES(8110,'李可',20,142);
INSERT INTO hsp_student VALUES(8201,'张飞',18,111);
INSERT INTO hsp_student VALUES(8302,'周瑜',16,112);
INSERT INTO hsp_student VALUES(8203,'王亮',17,111);
INSERT INTO hsp_student VALUES(8305,'董庆',19,102);
INSERT INTO hsp_student VALUES(8409,'赵龙',18,101);
SELECT * FROM department
SELECT * FROM class
SELECT * FROM hsp_student
-- (3) 实现以下查问性能
-- 3.1 找出所有姓李的学生。-- 查表 hsp_student , like
SELECT *
FROM hsp_student
WHERE `name` LIKE '李 %'
-- 3.2 列出所有开设超过 1 个业余的系的名字。-- 1. 先查问各个系有多少个业余
SELECT COUNT(*) AS nums, deptname
FROM class
GROUP BY deptname HAVING nums > 1
-- 3.3 列出人数大于等于 30 的系的编号和名字。-- 1. 先查出各个系有多少人, 并失去 >= 30 的系
SELECT SUM(num) AS nums, deptname
FROM class
GROUP BY deptname
HAVING nums >= 30
-- 2. 将下面的后果看成一个长期表 和 department 联结查问即可
SELECT tmp.*, department.departmentid
FROM department , (SELECT SUM(num) AS nums, deptname
FROM class
GROUP BY deptname
HAVING nums >= 30
) tmp
WHERE department.deptname = tmp.deptname;
-- (4) 学校又新减少了一个物理系，编号为 006
-- 增加一条数据
INSERT INTO department VALUES('006','物理系');
-- (5) 学生张三入学，请更新相干的表
-- 剖析：1. 张三所在班级的人数 -1 2. 将张三从学生表删除 3. 须要应用事务管制
-- 开启事务
START TRANSACTION;
-- 张三所在班级的人数 -1
UPDATE class SET num = num - 1
WHERE classid = (
SELECT classid
FROM hsp_student
WHERE NAME = '张三'
);
DELETE
FROM hsp_student
WHERE NAME = '张三';
-- 提交事务
COMMIT;
SELECT * FROM hsp_student;
SELECT * FROM class
正文完
