MySQL基础
1. 一个问题
某宝网、某东、微信、抖音都有各自的功能，那么我们退出系统的时候，下次再访问的时候，下次在访问时，为什么信息还存在？–>数据库
2. 解决之道
1. 解决之道-文件、数据库
为了解决上述问题，使用更加利于管理数据的东西-数据库，它能更有效的管理数据。
举一个生活化的案例说明
如果说图书馆是保存书籍的，那么数据库就是保存数据的。
2. MySQL数据库的安装和配置
mysql5.5 mysql5.6 mysql5.7(稳定) mysql8 更高版本
MySQL的安装教程有很多，这里不多做解释（后面的讲解中采用5.7版本）
3. 使用命令窗口连接MYSQL数据库
- mysql -h 主机名 -P 端口 -u 用户名 -p密码
- 登录前，保证服务启动
启动mysql数据库常用的方式：[Dos命令]
- 服务方式启动（界面）
- net stop myql服务名
- net start mysql服务名
注意：
- -p密码不要有空格
- -p后面没有写密码，回车会要求输入密码
- 如果没有写-h 主机，默认就是本机
- 如果没有-P 端口，默认就是3306
- 端口要-P要大写
- 在实际工作中，3306一般修改
3. Navicat安装和使用
- 介绍：图行化MySQL管理软件
- 同样的在这里不多做解释，教程很多
- 图形化的管理会非常的方便，但是我们需要学习指令，指令所用的地方非常多
4. SQLyog安装和使用
- 介绍：图形化MySQL管理软件
- 和Navicat一样是一种图形化管理软件，可以根据自己的喜好使用Navicat和SQLyog（这里只做推荐）
5. 数据库三层结构
- 所谓的安装MySQL数据库，就是在主机安装一个数据库管理工具（DBMS），这个管理程序可以管理多个数据库。DBMS（database manage system）
- 一个数据库中可以创建多个表，以保存数据（信息）。
- 数据库管理系统（DBMS）、数据库和表的关系如图所示：
6. 数据在数据库中的存储方式
在 MySQL 中，数据以表的形式存储。表是由行和列组成的二维数据结构，行代表记录，列代表字段。
表的一行称之为一条记录 -> 在Java程序中，一行记录往往使用对象表示
7. SQL语句分类
- DDL: 数据定义语句[create 表, 库…]
- DML: 数据操作语句[增加 insert, 修改 update, 删除 delete]
- DQL: 数据库查询语句[select]
- DCL: 数据库控制语句[管理数据库: 比如用户权限 grant revoke]
8. 数据库操作
1. 创建数据库
- CHARACTER SET: 指定数据库采用的字符集，如果不指定字符集，默认utf-8
- COLLATE: 指定数据库字符集的校对规则(常用的utf8_bin[区分大小写]、utf8_general_cl[不区分大小写] 注意默认是 utf8_general_ci)
练习：
- 创建一个名称为yzj_db01的数据库
- 创建一个使用utf8字符集的yzj_db02数据库
- 创建一个使用utf8字符集，并带校对规则的yzj_db03数据库
#创建一个名称为yzj_db01的数据库
CREATE DATABASE yzj_db01;
#创建一个使用utf8字符集的yzj_db02数据库
CREATE DATABASE yzj_db02 CHARACTER SET utf8
#创建一个使用utf8字符集，并带校对规则的yzj_db03数据库
CREATE DATABASE yzj_db03 CHARACTER SET utf8 COLLATE utf8_bin
2. 查看、删除数据库
- 显示数据库语句：
SHOW DATABASES - 显示数据库创建语句：
SHOW CREATE DATABASE db_name - 数据库删除语句：
DROP DATABASE [IF EXISTS] db_name
练习
- 查看当前数据库服务器中所有的数据库
- 查看前面创建的yzj_db01数据库中定义的信息
- 删除前面创建的hsp_db01数据库
#演示删除和查询数据库
#1. 查看当前数据库服务器中所有的数据库
SHOW DATABASES
#2. 查看前面创建的yzj_db01数据库中定义的信息
SHOW CREATE DATABASE db01
#说明：在创建数据库，表的时候，为了规避关键字，可以使用反引号解决
CREATE DATABASE `db01`
#3. 删除前面创建的hsp_db01数据库
DROP DATABASE yzj_db01
3. 备份和恢复数据库
- 备份数据库(注意: 在DOS执行) 命令行
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
说明：在Navicat中备份和恢复非常简单，没有描述的必要 - 恢复数据库(注意:进入Navicat再执行)
Source 文件名.sql - 备份数据库的表
mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > 文件名.sql
练习：
- 备份yzj_db02 和 yzj_db03 库中的数据，并恢复
#练习 : database03.sql 备份 yzj_db02 和 yzj_db03 库中的数据，并恢复 #备份, 要在 Dos 下执行 mysqldump 指令其实在 mysql 安装目录\bin #这个备份的文件，就是对应的 sql 语句
mysqldump -u root -p -B hsp_db02 hsp_db03 > d:\\bak.sql DROP DATABASE ecshop;
#恢复数据库(注意：进入 Mysql 命令行再执行)
source d:\\bak.sql
#第二个恢复方法， 打开备份的文件-直接将 bak.sql 的内容放到查询编辑器中，执行
4. 创建表
注意：创建表时，要根据保存的数据创建相对应的列(字段)，并根据数据类型定义相对应的列类型。
练习
在yzj_db02中创建一个这样的表（指令创建）
| 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
5. MySQL常用数据类型(列类型)
- MySQL列类型-即MySQL的数据类型
1. 数值型(整数)的基本使用
- 说明，使用规范：在能够满足需求的情况下，尽量选择占用空间小的
应用实例
#演示整型的是一个
#使用 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
2. 数值型(bit)的使用
1. 基本使用
mysql> create table t02(num bit(8));
mysql> insert into t02(1, 3);
mysql> insert into t02 values(2, 65)
2. 细节说明
- bit字段显示时，按照位的方式显示
- 查询的时候仍然可以使用 添加的数值
- 如果一个值只有0，1可以考虑使用bit(1)，可以节约空间位类型。M指定位数，默认值1，范围1-64，使用不多
3. 案例
##演示 bit 类型使用
#说明
#1. bit(m) m 在 1-64
#2. 添加数据 范围 按照你给的位数来确定，比如 m = 8 表示一个字节 0~255
#3. 显示按照 bit #4. 查询时，仍然可以按照数来查询
CREATE TABLE t02 (num BIT(8));
INSERT INTO t02 VALUES(1);
SELECT * FROM t02
SELECT * FROM t02 WHERE num=1;
3. 数值型（小数）的基本使用
- 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 t03 (
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20)
);
#添加数据
INSERT INTO t03 VALUES(88.12345678912345, 88.12345678912345, 88.12345678912345);
SELECT * FROM t03;
#decimal可以存放很大的数字、
CREATE TABLE t04 (
num DECIMAL(65)
);
INSERT INTO t04 VALUES(888888888439843948394839483984938484348390);
SELECT *FROM t04;
4. 字符串的基本使用
- CHAR(size)
固定长度字符串 最大255字符 - VARCHAR(size) ）0~65532
可变字符串 最大65532字节【utf8编码的最大21844字符 1-3个自己用于记录大小】
1. 案例：
#演示字符串类型使用char varchar
#注释快捷键 shift+ctrl+c，注销注释 shift+ctrl+r（SQL yog中）
CREATE TABLE t09 (
`name` CHAR(255));
CREATE TABLE T10 (
`name` VARCHAR(21844)); -- 如果表的编码是utf8 varchar(size) size=(65535-3) / 3 = 21844
2. 字符串使用细节
- 细节1
char(4) //这个4表示字符数，不管是字母还是中文都已定义好地 表的编码来存放数据
varchar(4) //这个4表示字符数,不管是字母还是中文都以定义好的表的编码来存放数据（不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的） - 细节2
char(4)是定长(固定的大小)，就是说，即使你插入’aa’ ,也会占用分配的4个字符的空间.
varchar(4)是变长(变化的大小)，就是说，如果你插入了’aa’,实际占用空间大小并不是4个字符，而是按照实际占用空间来分配(说明:varchar本身还需要占用1-3个字节来记录存放内容长度) L(实际数据大小)+(1-3)字节 - 细节3
什么时候使用char ,什么时候使用varchar- 如果数据是定长，推荐使用char,比如md5的密码,邮编，手机号，身份证号码等. char(32)
- 如果一个字段的长度是不确定，我们使用varchar ,比如留言,文章
查询速度: char > varchar
- 细节4
在存放文本时，也可以使用Text数据类型.可以将TEXT列视为VARCHAR列，注意Text不能有默认值.大小0-2^16字节如果希望存放更多字符，可以选择MEDIUMTEXT 0-2^24 或者LONGTEXT 0~2^32
3. 案例
#演示字符串类型的使用细节
#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('我真的麻了', '我配得上这个呦', '新火试新茶 1000~~');
5. 日期类型的基本使用
- 日期类型的细节说明
TimeStamp在Insert和update时，自动更新
#演示时间相关的类型
#创建一张表，date，datatime，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('2023-11-11','2023-11-11 10:10:10');
-- 如果我们更新 t14表的某条记录，login_time列会自动的以当前时间进行更新
6. 创建表的练习
创建一个员工表emp(课堂练习)，选用适当的数据类型
| 字段 | 属性 |
|---|---|
| id | 整型 |
| name | 字符型 |
| sex | 字符型 |
| birthday | 日期型 |
| entry_date | 日期型 |
| job | 字符型 |
| Salary | 小数型 |
| resume | 文本型 |
自己练习一下
#创建表的练习
-- | id | 整型 |
-- | name | 字符型 |
-- | sex | 字符型 |
-- | birthday | 日期型 |
-- | entry_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, '小王', '男','2001-10-29', '2023-10-9 10:10:10', '学习知识', 1500, '我还只是个学生,你还要我怎样');
SELECT * FROM `emp`
7. 修改表
1. 基本介绍
- 修改表名：Runame table 表名 to 新表名
- 修改表字符集: alter table 表名 character set 字符集
2. 应用案例
员工表emp的上增加一个image列，varchar 类型(要求在resume后面)。
- 修改job列，使其长度为60。
- 删除sex列。
- 表名改为employee。
- 修改表的字符集为utf8
- 列名name修改为user_ name
- alter table user change column name username varchar(20);
#修改表的操作练习
-- 员工表 emp 的上增加一个 image 列，varchar 类型(要求在 resume 后面)。
ALTER TABLE emp
ADD image VARCHAR(32) NOT NULL DEFAULT''
AFTER RESUME
DESC emp
-- 修改 job 列，使其长度为 60。
ALTER TABLE emp
MODIFY job VARCHAR(60) NOT NULL DEFAULT''
-- 删除 sex 列。
ALTER TABLE emp
DROP sex
-- 修改表名为employee
RENAME TABLE emp TO employee
-- 修改字符集为utf8
ALTER TABLE employee CHARACTER SET utf8
-- 将name修改为user_name
ALTER TABLE employee
CHANGE `name`user_name VARCHAR(32) NOT NULL DEFAULT''
DESC employee
9. 数据库 C[create]R[read]U[update]D[delete]语句
- Inssert语句（添加数据）
- Update语句（更新数据）
- Delete语句（删除数据）
- Select语句（查找数据）
1. Insert 语句
1. 使用INSERT语句向表中插入数据
2. 快速入门案例
- 创建一-张商品表goods (id int , goods namevarchar(10), price double);
- 添加2条记录
#练习 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(10, '小米', 3000);
SELECT * FROM goods;
CREATE TABLE `goods2` (
id INT , g
oods_name VARCHAR(10), -- 长度 10
price DOUBLE NOT NULL DEFAULT 100
);
3. 细节说明
- 插入的数据应与字段的数据类型相同。
- 数据的长度应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
- 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。
- 字符和日期型数据应包含在单引号中。
- 列可以插入空值[前提是该字段允许为空]，insert into table value(null)
- . insert into tab_name (列名…) values (),(),() 形式添加多条记录
- 如果是给表中的所有字段添加数据，可以不写前面的字段名称
- 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错
– 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给 null
– 如果我们希望指定某个列的默认值，可以在创建表时指定
#说明 insert 语句的细节
-- 1.插入的数据应与字段的数据类型相同。
-- 比如 把 'abc' 添加到 int 类型会错误
INSERT INTO `goods` (id, goods_name, price)
VALUES('yzj', '小米手机', 2000);
-- 2. 数据的长度应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, 'vovo 手机 vovo 手机 vovo 手机 vovo 手机 vovo 手机', 3000);
-- 3. 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。
INSERT INTO `goods` (id, goods_name, price) -- 不对
VALUES('vovo 手机',40, 2000);
-- 4. 字符和日期型数据应包含在单引号中。
INSERT INTO `goods` (id, goods_name, price) VALUES(40, vovo 手机, 3000); -- 错误的 vovo 手机 应该 'vovo 手机'
-- 5. 列可以插入空值[前提是该字段允许为空]，
insert into table value(null) INSERT INTO `goods` (id, goods_name, price)
VALUES(40, 'vovo 手机', NULL);
-- 6. insert into tab_name (列名..) values (),(),() 形式添加多条记录
INSERT INTO `goods` (id, goods_name, price) VALUES(50, '三星手机', 2300),(60, '海尔手机', 1800);
-- 7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称
INSERT INTO `goods` VALUES(70, 'IBM 手机', 5000);
-- 8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错
-- 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给 null
-- 如果我们希望指定某个列的默认值，可以在创建表时指定
INSERT INTO `goods` (id, goods_name)
VALUES(80, '格力手机'); SELECT * FROM goods;
INSERT INTO `goods2` (id, goods_name)
VALUES(10, '老婆手机'); SELECT * FROM goods2;
2. Update语句
1. 使用update语句修改表中的数据
2. 基本使用
要求：在上面创建的update语句修改表中数据
- 将所有员工薪水修改为5000元。
- 将姓名为小妖怪的员工薪水修改为3000元。
- 将老妖怪的薪水在原有基础上增加1000元。
#演示update语句
-- 1. 将所有员工薪水修改为5000元。
-- 2. 将姓名为小妖怪的员工薪水修改为3000元。
-- 3. 将老妖怪的薪水在原有基础上增加1000元。
UPDATE employee SET salary = 5000;
UPDATE employee SET salary =3000
WHERE user_name = '小王';
ALTER TABLE employee
DROP image
INSERT INTO employee
VALUES(200, '大王', '1996-10-29', '2020-10-19 10:10:10', '工作', 8000, '开始工作了');
UPDATE employee SET salary = salary + 1000
WHERE user_name = '大王';
SELECT * FROM employee
3. 使用细节
- UPDATE语法可以用新值更新原有表行中的各列
- SET子句指示要求修改那些类和要给予那些值
- WHERE子句指定应该更新那些行。如没有WHERE语句，则更新所有的行（因此一定要小心）
- 如果需要修改多个字段，可以动过set 字段1=值1, 字段2= 值2
#演示update语句
-- 1. 将所有员工薪水修改为5000元。
-- 2. 将姓名为小妖怪的员工薪水修改为3000元。
-- 3. 将老妖怪的薪水在原有基础上增加1000元。
UPDATE employee SET salary = 5000;
UPDATE employee SET salary =3000
WHERE user_name = '小王';
ALTER TABLE employee
DROP image
INSERT INTO employee
VALUES(200, '大王', '1996-10-29', '2020-10-19 10:10:10', '工作', 8000, '开始工作了');
UPDATE employee SET salary = salary + 1000
WHERE user_name = '大王';
UPDATE employee SET salary = salary + 1000, job = '出主意'
WHERE user_name = '大王';
SELECT * FROM employee
3. Delete语句
1. 使用delete语句删除表中的数据
2. 快速入门案例
- 删除表中名称为‘大王’的记录
- 删除表中所有的记录
#Delete语句演示
-- 删除表中名称为'老王'的记录
DELETE FROM employee
WHERE user_name = '大王';
-- 删除表中所有的数据 一定要小心
DELETE FROM employee
-- 要删除这个表
DROP TABLE employee;
SELECT * FROM employee
3. 使用细节
- 如果不使用where子句，将删除表中所有的数据
- Delete语句不能删除某一列的值（可以使用update 设为null 或者’'）
- 使用Delete语句仅删除记录，不删除表本身。如果要删除表，使用drop table语句。 drop table 表名；
4. Select语句01
1. 基本语法
2. 注意事项（创建测试表学生表）
- Select指定查询那些列的数据
- colummn指定列名
- *号代表查询所有的列
- From指定查询哪张表
- DISTINCT可选，指显示结果时，是否去掉重复的数据
3. 案例
- 查询表中所有学生的信息
- 查询表中所有学生的姓名和对应的英语成绩
- 过滤表中重复的数据 distinct
- 要查询的记录，每个字段都相同，才会去重
#select 语句【重点】
CREATE TABLE student (
id INT NOT NULL DEFAULT 1,
`name` VARCHAR(32) 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),
(2,'张飞',67,98,56),
(3,'宋江',87,78,77),
(4,'关羽',88,98,90),
(5,'赵云',82,84,67),
(6,'欧阳锋',55,85,45),
(7,'黄蓉',75,65,30),
(8,'韩信',45,65,99);
-- 1. 查询表中所有学生的信息
SELECT * FROM student;
-- 2. 查询表中所有学生的姓名和对应的英语成绩
SELECT `name`, english FROM student;
-- 3. 过滤表中重复的数据 distinct
SELECT DISTINCT english FROM student;
-- 4. 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT `name`,english FROM student;
5. Select语句 02
1. 使用表达式对查询的列进行计算
2. 在select语句中可使用as语句
3. 练习
- 统计每个学生的总分
- 在所有学生总分加10分的情况
- 使用别名表示学生分数
#select语句的使用
-- 1. 统计每个学生的总分
SELECT `name`,(chinese + math + english)FROM student
-- 2. 在所有学生总分加10分的情况
SELECT `name`,(chinese + math + english + 10)FROM student
-- 3. 使用别名表示学生分数
SELECT `name` AS '名字',(chinese + math + english) AS '总分' FROM student
6. Select语句 03
1. 在 where 子句中经常使用的运算符
2. 使用 where 子句，进行过滤查询（查询学生表）
- 查询姓名为赵云的学生成绩
- 查询英语成绩大于90分的同学
- 查询总分大于200分的所有同学
- 查询math大于60并且(and) id大于4的学生成绩
- 查询英语成绩大于语文成绩的同学
- 查询总分大于200分并且数学成绩小于语文成绩，的姓关的学生
#select语句的使用
-- 1. 查询姓名为赵云的学生成绩
SELECT * FROM student
WHERE `name` = '赵云';
-- 2. 查询英语成绩大于90分的同学
SELECT * FROM student
WHERE english > 90;
-- 3. 查询总分大于200分的所有同学
SELECT * FROM student
WHERE (chinese + math + english) > 200;
-- 4. 查询math大于60并且(and) id大于4的学生成绩
SELECT * FROM student
WHERE math > 60 AND id > 4;
-- 5. 查询英语成绩大于语文成绩的同学
SELECT * FROM student
WHERE english > chinese;
-- 6. 查询总分大于200分并且数学成绩小于语文成绩，的姓赵的学生
-- 赵% 表示 名字以赵开头的就可以
SELECT * FROM student
WHERE (chinese + math + english) > 200 AND
math < chinese AND `name` LIKE '赵%'
7. Select语句 04
1. 使用 order by 子句排序查询结果
- Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
- Asc升序[默认]、Desc降序
- ORDER BY子句应位于SELECT语句的结尾。
2. 练习
- 对数学成绩排序后输出[升序]。
- 对总分按从高到低的顺序输出[降序]
- 对姓李的学生成绩排序输出(升序)
#演示order by的使用
-- 1. 对数学成绩排序后输出[升序]。
SELECT `name`,math FROM student
ORDER BY math ASC;
-- 2. 对总分按从高到低的顺序输出[降序]
SELECT `name` AS '名字', (chinese + math + english) AS 'table_score' FROM student
ORDER BY table_score DESC;
-- 3. 对姓张的学生成绩排序输出(升序)
INSERT INTO student (id, `name`, chinese, english, math)
VALUES(9, '小白', 99, 99, 99);
SELECT `name` AS '名字', (chinese + math + english) AS 'table_score' FROM student
WHERE `name` LIKE '小%';
ORDER BY table_score;-- ASC可以省略
10. 函数
1. 合计/统计函数
1. count-统计函数
返回行的总数
练习
- 统计一个班级共有多少学生?
- 统计数学成绩大于90的学生有多少个?
- 统计总分大于250的人数有多少?
- count(*)和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
2. sum-统计函数
Sum函数返回满足where条件行的和 - 一般用在数据值列
练习
- 统计一个班级数学总成绩?
- 统计一个班级语文、英语、数学各科的总成绩
- 统计一个班级语文、英语、数学的成绩总和
- 统计一个班级语文成绩平均分
-- 演示 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;
注意: sum仅对数值起作用，没有意义。
注意: 对多列求和逗号不能少。
3. avg
avg函数返回满足where条件的一列平均值
-- 演示 avg 的使用
-- 练习： -- 求一个班级数学平均分？
SELECT AVG(math) FROM student;
-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;
4. max/min
Max/min函数返回满足where条件的一列的最大/最小值
练习
求班级最高分和最低分(数值范围在统计中特别有用)
-- 演示 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;
2. 分组统计
1. 使用 group by 子句对列进行分组
2. 使用 having 子句对分组后的结果进行过滤
3. 练习
- 创建测试表
- group by用于对查询的结果分组统计，(示意图)
- having子句用于限制分组显示结果
- 如何显示每个部门的平均工资和最高工资
- 显示每个部门的每种岗位的平均工资和最低工资
- 显示平均工资低于2000的部门号和它的平均工资//别名
CREATE TABLE dept(/*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT '',
loc VARCHAR(12) NOT NULL DEFAULT ''
);
INSERT INTO dept
VALUES
( 10, 'ACCOUNTING', 'NEW YORK' ),
( 20, 'RESEARCH', 'DALLAS' ),
( 30, 'SALES', 'CHICAGO' ),
( 40, 'OPERATIONS', 'BOSTON' );
-- 员工表
CREATE TABLE emp01
(
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 emp01
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 emp01
-- 工资级别
#工资级别表
CREATE TABLE salgrade (
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*工资级别*/
losal DECIMAL ( 17, 2 ) NOT NULL,/* 该级别的最低工资 */
hisal DECIMAL ( 17, 2 ) NOT NULL /* 该级别的最高工资*/
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
SELECT * FROM dept;
SELECT * FROM emp01;
SELECT * FROM salgrade;
-- 1. 如何显示每个部门的平均工资和最高工资
SELECT AVG(sal), MAX(sal) , deptno
FROM emp01 GROUP BY deptno;
SELECT FORMAT(AVG(sal),2), MAX(sal), deptno
FROM emp01 GROUP BY deptno;
-- 2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal) , deptno, job
FROM emp01 GROUP BY deptno, job;
-- 3. 显示平均工资低于2000的部门号和它的平均工资//别名
SELECT AVG(sal), deptno
FROM emp01 GROUP BY deptno
HAVING AVG(sal) < 2000;
-- 使用别名
SELECT AVG(sal) AS avg_sal, deptno
FROM emp01 GROUP BY deptno
HAVING avg_sal < 2000;
3. 字符串相关函数
练习
-- 演示字符串相关函数的使用 ， 使用 emp01 表来演示
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp01
-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename,'工作是',job) FROM emp01
-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0
-- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('BAILAOHAN','AN') FROM DUAL;
-- UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp01
-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp01;
-- LEFT (string2 ,length )从 string2 中的左边起取 length 个字符
-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符
SELECT LEFT(ename, 2) FROM emp01;
-- LENGTH (string )string 长度[按照字节]
SELECT LENGTH(ename) FROM emp01;
-- REPLACE (str ,search_str ,replace_str )
-- 在 str 中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER','经理')FROM emp01
-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('lao', 'abb') FROM DUAL;
-- SUBSTRING (str , position [,length ])
-- 从 str 的 position 开始【从 1 开始计算】,取 length 个字符 -- 从 ename 列的第一个位置开始取出 2 个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp01
-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM(' Ricardo') FROM DUAL;
SELECT RTRIM('Ricardo ') FROM DUAL;
SELECT TRIM(' Ricardo ') FROM DUAL;
-- 练习: 以首字母小写的方式显示所有员工 emp 表的姓名
-- 方法 1 -- 思路先取出 ename 的第一个字符，转成小写的
-- 把他和后面的字符串进行拼接输出即可
SELECT
CONCAT(LCASE(SUBSTRING( ename, 1, 1 )),
SUBSTRING( ename, 2 ))
FROM emp01
SELECT
CONCAT(LCASE(LEFT(ename,1)),
SUBSTRING(ename,2))
FROM emp01;
4. 数学相关函数
注意：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.237774,2) FROM DUAL
-- HEX (DecimalNumber ) 转十六进制
SELECT HEX(300) FROM DUAL
-- 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;
5. 时间日期相关函数
练习
-- 日期时间相关函数
-- 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());-- NOW()显示当前时间
INSERT INTO mes
VALUES(3,'广州新闻',NOW());
-- 应用实例
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间.
SELECT id, content, DATE(send_time)
FROM mes;
-- 请查询在 20 分钟内发布的新闻, 思路一定要梳理一下.
SELECT * FROM mes
WHERE DATE_ADD(send_time, INTERVAL 20 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;
SELECT * FROM mes;
SELECT NOW() 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;
备注：
- DATE ADD() 中的interval 后面可以是year minute second day等
- DATE SUB()中的interval 后面可以是year minute second hour day等
- DATEDIFF(date1,date2) 得到的是天数， 而且是date1-date2的天数，因此可以取负数
- 这四个函数的日期类型可以是 date, datetime或者timestamp
6. 加密和系统函数
| USER() | 查询用户 |
|---|---|
| DATABASE() | 数据库名称 |
| MD5(str) | 为字符串算出一个 MD5 32的字符（用户密码）加密 |
| PASSWORD(str) select * from mysql.user | 从原文密码str计算并返回密码字符串，通常用于对mysql数据库的用户密码加密 |
1. 基本使用
mysql> create table users(id int, name varchar(32) not null default’‘, pwd char(32) not null default’');
添加一个用户名
2. 练习
# 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP
SELECT USER() FROM DUAL;-- 用户@IP 地址
-- DATABASE()查询当前使用数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密
-- root 密码是 hsp -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('yzj') FROM DUAL;
SELECT LENGTH(MD5('yzj')) FROM DUAL;
-- 演示用户表，存放密码时，是 md5
CREATE TABLE yzj_user (
id INT,
`name` VARCHAR ( 32 ) NOT NULL DEFAULT '',
pwd CHAR ( 32 ) NOT NULL DEFAULT ''
);
INSERT INTO yzj_user
VALUES(100, '小小白', MD5('yzj'));
SELECT * FROM yzj_user;
SELECT * FROM hsp_user -- SQL 注入问题
WHERE `name`='小小白' AND pwd = MD5('yzj')
-- PASSWORD(str)
-- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密
SELECT PASSWORD('yzj') FROM DUAL;
-- select * from mysql.user \G 从原文密码 str 计算并返回密码字符串 -- 通常用于对 mysql 数据库的用户密码加密 -- mysql.user 表示 数据库.表
SELECT * FROM mysql.user
7. 流程控制函数
两个需求
- 查询emp表如果comm是null，则显示0.0
- 如果emp表的job是CLERK则显示职员，如果是MANAGER则显示经理如果是SALESMAN则显示销售人员，其它正常显示
| IF(expr1, expr2, expr3) | 如果expr1为true，则返回expr2否则返回expr3 |
|---|---|
| IFNULL(expr1，expr2) | 如果expr1不为空NULL，则返回expr1，否则返回expr2 |
| SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 WHEN expr3 THEN expr4 ELSE expr5 END;[类似多重分支] | 如果expr1为true，则返回expr2，如果expr3为true，返回expr4，否则返回expr5 |
演示
# 演示流程控制语句
# 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 emp01;
SELECT ename, IFNULL(comm, 0.0)
FROM emp01;
-- 2. 如果 emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS 'job'
FROM emp01
11. mysql 表查询–加强
1. 介绍
在前面讲过mysql表的基本查询，但是都是对一-张表进行的查询，这在实际的软件开发中，还远远的不够。下面我们讲解的过程中，将使用前面创建三张表？(emp01,dept,salgrade)为大家演示如何进行多表查询
- 使用where子句
如何查找1992.1.1后入职的员工? - 如何使用like操作符
%:表示0到多个字符: 表示单个字符
如何显示首字符为S的员工姓名和工资?
如何显示第三个字符为大写0的所有员工的姓名和工资? - 如何显示没有上级的雇员的情况
- 查询表结构selectinc.sql
- 使用order by子句
如何按照3 C资的从低到高的顺序，显示雇员的信息?
按照部门号升序而雇员的工资降序排列,显示雇员信息?
-- 查询加强
-- 使用 where 子句
-- 如何查找 1992.1.1 后入职的员工?
-- 老师说明： 在 mysql 中,日期类型可以直接比较, 需要注意格式
SELECT * FROM emp01
WHERE hiredate > '1992-1-1';
-- 如何使用 like 操作符(模糊)
-- %: 表示 0 到多个任意字符 _: 表示单个任意字符
-- 如何显示首字符为 S 的员工姓名和工资?
SELECT ename, sal FROM emp01
WHERE ename LIKE 'S%'
-- 如何显示第三个字符为大写 O 的所有员工的姓名和工资?
SELECT ename, sal FROM emp01
WHERE ename LIKE '__O%'
-- 如何显示没有上级的雇员的情况
SELECT * FROM emp01
WHERE mgr IS NULL;
-- 查询表结构
DESC emp01
-- 使用 order by 子句
-- 如何按照工资的从低到高的顺序[升序]，显示雇员的信息?
SELECT * FROM emp01
ORDER BY sal
-- 按照部门号升序而雇员的工资降序排列 , 显示雇员信息?
SELECT * FROM emp01
ORDER BY deptno ASC, sal DESC;
2. 分页查询
-
按雇员的id号升序取出，每页显示3条记录， 请分别显示第1页
-
基本语法select… limit start, rows
表示从start+ 1行开始取，取出rows行，start从0开始计算 -
公式
-- 推到一个公式 SELECT * FROM emp01 ORDER BY empno LIMIT 每页显示记录数 * (第几页-1), 每页显示记录数
练习
按雇员的empno号降序取出， 每页显示5条记录。请分别显示第3页，第5页对应的sq|语句
第5页:
select * from emp order by empno desc limit 20, 5
-- 分页查询
-- 按雇员的id号升序取出，每页显示3条记录，请分别显示 第1页，第2 页，第3页
-- 第一页
SELECT * FROM emp01
ORDER BY empno
LIMIT 0, 3;
-- 第二页
SELECT * FROM emp01
ORDER BY empno
LIMIT 3, 3;
-- 第三页
SELECT * FROM emp01
ORDER BY empno
LIMIT 6, 3;
-- 显示雇员总数，以及获得补助的雇员数
SELECT job,COUNT(*) FROM emp01
WHERE mgr IS NOT NULL;
-- 按雇员的empno号降序取出， 每页显示5条记录。请分别显示第3页，第5页对应的sq|语句
SELECT * FROM emp01
ORDER BY empno DESC
LIMIT 10, 5;
SELECT * FROM emp01
ORDER BY empno DESC
LIMIT 20, 5;
3. 使用分组函数和分组子句 group by
(1) 显示每种岗位的雇员总数、平均工资。
(2) 显示雇员总数，以及获得补助的雇员数。
(3) 显示管理者的总人数。
(4)显示雇员工资的最大差额。
1. 练习
-- 增强 group by 的使用
-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal) FROM emp01
GROUP BY job
-- (2) 显示雇员总数，以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非 null, 就是 count(列)，如果该列的值为 null, 是
-- 不会统计 , SQL 非常灵活，需要我们动脑筋.
SELECT COUNT(*), COUNT(comm) FROM emp01
-- 扩展要求：统计没有获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
FROM emp01
-- 或者
SELECT COUNT(*), COUNT(*) - COUNT(comm)
FROM emp01
-- (3)显示管理者的总人数。
SELECT COUNT(DISTINCT mgr) FROM emp01;
-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal) FROM emp01;
SELECT * FROM emp01
2. 数据分组的总结
如果select语句同时包含有group by ,having , limit, order by那么他们的顺序是group by , having , order by,limit
应用案例: 请统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序，取出前两行记录.
-- 应用案例:请统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序，取出前两行记录.
SELECT deptno, AVG(sal) AS avg_sal FROM emp01
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0, 2
12. mysql 多表查询
1. 问题的引出(重点，难点)
2. 说明
多表查询是指基于两个和两个以上的表查询.在实际应用中,查询单个表可能不能满足你的需求，(如下面的课堂练习)，需要使用到(dept表和emp01表)
在默认情况下：当两个表查询时，规则：
- 从第一张表中，取出一行和第二张表的每一行进行组合，返回结果[含有两张表的所有列]
- 一共返回的记录数第一张表行数*第二章表行数
- 这样多表查询默认处理返回的结果，称为笛卡尔集
- 解决这个表的关键就是写出正确的过滤条件
3. 多表查询练习
- 显示雇员名.雇员工资及所在部门的名字[笛卡尔集]？
- 小技巧:多表查询的条件不能少于表的个数-1,否则会出现笛卡尔集
如何显示部门号为10的部门名、员工名和工资? - 显示各个员工的姓名，工资，及其工资的级别?
-- 多表查询
-- 1. 显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】?
/*
分析
1.雇员名,雇员工资 来自emp01表
2.部门的名字 来自 dept 表
3.需求对 emp01 和 dept 查询 ename,sal,dname,deptno
4.当我们需要指定显示某个表的列是，需要 表.列表
*/
SELECT ename, sal, dname, emp01.deptno
FROM emp01,dept
WHERE emp01.deptno = dept.deptno
-- 小技巧:多表查询的条件不能少于表的个数-1,否则会出现笛卡尔集
-- 2. 如何显示部门号为10的部门名、员工名和工资?
SELECT emp01.deptno, dname, ename, sal
FROM emp01, dept
WHERE emp01.deptno = dept.deptno AND emp01.deptno = 10;
-- 3. 显示各个员工的姓名，工资，及其工资的级别?
-- 思路 姓名，工资 来自 emp01
-- 工资级别 salgrade
-- 写 sql , 先写一个简单，然后加入过滤条件...
SELECT ename, sal, grade
FROM emp01, salgrade
WHERE sal BETWEEN losal AND hisal
ORDER BY grade
4. 自连接
自连接是指在同一张表的连接查询[将同-张表看做两张表]。
思考题:显示公司员工和他的上级的名字
-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 分析： 员工名字 在 emp01, 上级的名字的名字 emp01
-- 员工和上级是通过 emp01 表的 mgr 列关联 -- 这里老师小结：
-- 自连接的特点 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确，可以指定列的别名 列名 as 列的别名
SELECT worker.ename AS '职员名', boss.ename '上级名'
FROM emp01 worker, emp01 boss
WHERE worker.mgr = boss.empno;
SELECT * FROM emp01
13. mysql 表子查询
1. 什么是子查询
子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询
2. 单行子查询
单行子查询是指只返回一行数据的子查询语句
-- 子查询的演示
-- 请思考：如何显示与 SMITH 同一部门的所有员工?
/*
1. 先查询到 SMITH 的部门号得到
2. 把上面的 select 语句当做一个子查询来使用
*/
SELECT *
FROM emp01
WHERE deptno = (
SELECT deptno
FROM emp01
WHERE ename = 'SMITH'
)
3. 多行子查询
多行子查询指返回多行数据的子查询 使用关键字 in
-- 课堂练习:如何查询和部门 10 的工作相同的雇员的
-- 名字、岗位、工资、部门号, 但是不含 10 号部门自己的雇员.
/*
1. 查询到 10 号部门有哪些工作
2. 把上面查询的结果当做子查询使用
*/
SELECT DISTINCT job
FROM emp01
WHERE deptno = 10;
-- 下面语句完整
SELECT ename, job, sal, deptno
FROM emp01
WHERE job IN (
SELECT DISTINCT job
FROM emp01
WHERE deptno = 10
) AND deptno != 10
4. 子查询当做临时表使用
- 查询ecshop中各个类别中，价格最高的商品
- 注意：我会将使用的数据库放入文章的末尾
5. 在多行子查询中使用all和any操作符
1. ALL
请思考:显示工资比部门30的所有员工的工资高的员工的姓名、工
资和部门号
-- 请思考:显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp01
WHERE sal > ALL(
SELECT sal FROM emp01
WHERE deptno = 30
);
扩展要求:大家想想还有没有别的查询方法.
SELECT ename, sal, deptno
FROM emp01
WHERE sal > (
SELECT MAX(sal) FROM emp01
WHERE deptno = 30
);
2. ANY
请思考:如何显示工资比部门 30 的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp01
WHERE sal > ANY (
SELECT sal FROM emp01
WHERE deptno = 30
);
或者
SELECT ename, sal, deptno
FROM emp01
WHERE sal > (
SELECT MIN(sal) FROM emp01
WHERE deptno = 30
);
6. 多列子查询
- 多列子查序则是指查询返回多个列数据的子查询语句
- 请思考如何查询与allen的部广]和岗位完全相同的所有雇员(并且不含allen本人)
- (字段1,字段…) = (select字段1,字段2 from…)
-- 多列子查询
-- 请思考如何查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人) -- (字段 1， 字段 2 ...) = (select 字段 1，字段 2 from 。。。。)
-- 分析: 1. 得到 smith 的部门和岗位
SELECT deptno , job
FROM emp01
WHERE ename = 'ALLEN'
-- 分析: 2 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT *
FROM emp01
WHERE (deptno, job) = (
SELECT deptno , job
FROM emp01
WHERE ename = 'ALLEN'
)
7. 在 from 子句中使用子查询
请思考: 查找每个部门工资高于本部门平均工资的人的资料
这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用
请思考: 查找每个部门工资最高的人的详细资料
练习
-- 子查询练习
-- 请思考：查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用 -- 1. 先得到每个部门的 部门号和 对应的平均工资
SELECT deptno, AVG(sal) AS avg_sal
FROM emp01 GROUP BY deptno
-- 2. 把上面的结果当做子查询, 和 emp01 进行多表查询
SELECT ename, sal, temp.avg_sal, emp01.deptno
FROM emp01,(
SELECT deptno, AVG(sal) AS avg_sal
FROM emp01
GROUP BY deptno
) temp
WHERE emp01.deptno = temp.deptno AND emp01.sal > temp.avg_sal
-- 查找每个部门工资最高的人的详细资料
SELECT ename, sal, temp.max_sal, temp.avg_sal, emp01.deptno
FROM emp01, (
SELECT deptno, MAX(sal) AS max_sal,AVG(sal) AS avg_sal
FROM emp01
GROUP BY deptno
) temp
WHERE emp01.deptno = temp.deptno AND emp01.sal = temp.max_sal
-- 查询每个部门的信息(包括：部门名,编号,地址)和人员数量,我们一起完成。
-- 1. 部门名,编号,地址 来自 dept 表
-- 2. 各个部门的人员数量 -》 构建一个临时表
SELECT COUNT(*)
FROM emp01
GROUP BY deptno;
SELECT dname, dept.deptno, loc, temp.per_num AS '人数'
FROM dept,(
SELECT COUNT(*) AS per_num, deptno
FROM emp01
GROUP BY deptno
)temp
WHERE dept.deptno = temp.deptno
-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化 sql 语句
-- 在多表查询中，当多个表的列不重复时，才可以直接写列名
SELECT temp.*, dname, loc
FROM dept,(
SELECT COUNT(*) AS per_num, deptno
FROM emp01
GROUP BY deptno
)temp
WHERE dept.deptno = temp.deptno
14. 表复制
1. 自我复制数据（蠕虫复制）
有时，为了对某个sql语句进行效率测试，我们需要海量量数据时，可以使用此法为表创建海量数据。
-- 表的复制
-- 为了对某个 sql 语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
CREATE TABLE my_table (
id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT
);
DESC my_table
SELECT * FROM my_table
-- 演示如何自我复制
-- 1. 先把 emp 表的记录复制到 my_tab01
INSERT INTO my_table
(id, `name`, sal, job, deptno)
SELECT empno, ename, sal, job, deptno FROM emp01
-- 2. 自我复制
INSERT INTO my_table
SELECT * FROM my_table
-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02,
-- 2. 让 my_tab02 有重复的记录
DROP TABLE my_tab02
CREATE TABLE my_tab02 LIKE emp01
INSERT INTO my_tab02
SELECT * FROM emp01
INSERT INTO my_tab02
SELECT * FROM my_tab02
-- 3. 考虑去重 my_tab02 的记录
/*
思路
(1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
(2) 把 my_tab02 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
(3) 清除掉 my_tab02 记录
(4) 把 my_tmp 表的记录复制到 my_tab02
(5) drop 掉 临时表 my_tmp
*/
CREATE TABLE my_tmp LIKE my_tab02
INSERT INTO my_tmp
SELECT DISTINCT * FROM my_tab02
DELETE FROM my_tab02
INSERT INTO my_tab02
SELECT * FROM my_tmp
DROP TABLE my_tmp
15. 合并查询
1. 介绍
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union , union all
1. union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。
2. union
该操作越与union all相似，但是会自动去掉结果集中重复行
3. 练习
-- 合并查询
SELECT ename, job FROM emp01 WHERE sal > 2500 -- 5
SELECT ename, job FROM emp01 WHERE job = 'MANAGER' -- 3
-- union all 就是将两个查询结果合并，不会去重
SELECT ename, job FROM emp01 WHERE sal > 2500 -- 5
UNION ALL
SELECT ename, job FROM emp01 WHERE job = 'MANAGER' -- 3
-- union 就是将两个查询结果合并，会去重
SELECT ename, job FROM emp01 WHERE sal > 2500 -- 5
UNION
SELECT ename, job FROM emp01 WHERE job = 'MANAGER' -- 3
16. mysql表外连接
1. 提出一个问题
- 前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示
- 比如:列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。
- 使用我们学习过的多表查询的SQL,看看效果如何?
-- 比如：列出部门名称和这些部门的员工名称和工作，
-- 同时要求 显示出那些没有员工的部门。
-- 使用我们学习过的多表查询的 SQL， 看看效果如何?
SELECT emp01.deptno, dname, ename, job
FROM emp01, dept
WHERE emp01.deptno = dept.deptno
ORDER BY dname
SELECT * FROM dept;
SELECT * FROM emp01;
我们会发现，我们没有办法做到在多表查询中，显示出那些没有员工的部门
2. 外连接
- 左外连接(如果左侧的表完全 显示我们就说是左外连接)
- 右外连接(如果右侧的表完全 显示我们就说是右外连接)
我们举例说明。 - 首先我们需要新建两张表
- 使用左外连接(显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号成绩显示为空)
select … from表1 left join表2 on条件[表1:就是左表 表2:就是右表] - 使用右外连接(显示所有成绩，如果没有名字匹配，显示空)
select … from表1 right join表2 on条件[表1:就是左表 表2:就是右表]
3. 案例
-- 创建 stu
CREATE TABLE stu (
id INT,
`name` VARCHAR(32)
);
INSERT INTO stu
VALUES(1,'Jack'),(2,'Tom'),(3,'kity'),(4,'nono');
SELECT * FROM stu;
-- 创建 exam
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 LEFT JOIN exam
ON stu.id = exam.id;
-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空)
-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
SELECT `name`, stu.id, grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;
4. 练习
列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工
的部门名。
- 使用左外连接实现
- 使用右外连接实现
-- 列出部门名称和这些部门的员工信息(名字和工作)，
-- 同时列出那些没有员工的部门名。
-- 使用左外连接实现
SELECT dname, ename, job
FROM dept LEFT JOIN emp01
ON dept.deptno = emp01.deptno
SELECT dname, ename, job
FROM emp01 RIGHT JOIN dept
ON dept.deptno = emp01.deptno
17. MySQL约束
1. 基本介绍
约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括: not null、unique,primary key,foreign key,和check五种.
2. primary key(主键)
1. primary key(主键)-基本使用
用于唯一的标示表行的数据，当定义主键约束后，该列不能重复
字段名 字段类型 primary key
2. primary key(主键)-细节说明
- primary key不能重复而且不能为null。
- 一张表最多只能有一个主键，但可以是复合主键
- 主键的指定方式有两种
- 直接在字段名后指定:字段名primakry key
- 在表定义最后写primary key(列名);
- 使用desc表名，可以看到primary key的情况.
- 在实际开发中，每个表往往都会设计一个主键.
3. 案例
-- 主键使用
-- id name email
CREATE TABLE t17 (
id INT PRIMARY KEY,
`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, 'nono', 'nono@sohu.com');-- 会报错
-- 主键使用的细节讨论
-- 1. primary key 不能重复而且不能为 null。
INSERT INTO t17
VALUES(NULL, 'nono', 'nono@sohu.com');-- 会报错
-- 2. 一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)
CREATE TABLE t18 (
id INT PRIMARY KEY, -- 表示 id 列是主键
`name` VARCHAR(32) PRIMARY KEY, -- 错误的
email VARCHAR(32)
);
-- 2.1 演示复合主键 (id 和 name 做成复合主键)
CREATE TABLE t18 (
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id, `name`)
);
-- 注意，当id 和 name 都相同时才会无法添加
INSERT INTO t18
VALUES(1, 'jack', 'jack@sohu.com');-- 成功
INSERT INTO t18
VALUES(2, 'jack', 'tom@sohu.com');-- 成功
INSERT INTO t18
VALUES(1, 'jack', 'nono@sohu.com');-- 失败
SELECT * FROM t18
-- 3. 主键的指定方式 有两种
-- (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 表的结果，显示约束的情况
3. not null 非空
如果在列上定义了not null,那么当插入数据时，必须为列提供数据。
字段名 字段类型 not null
4. unique 唯一
当定义了唯一约束后，该列值是不能重复的。
字段名 字段类型 unique
1. unique细节(注意)
- 如果没有指定not null,则unique字段可以有多个
- 一张表可以有多个unique字段
2. 练习
-- unique 的使用
CREATE TABLE t21 (
id INT UNIQUE,
`name` VARCHAR(32),
email VARCHAR(32)
);
INSERT INTO t21
VALUES(1, 'jack', 'jack@shou.com');-- 可以添加
INSERT INTO t21
VALUES(1, 'tom', 'tom@shou.com');-- 不能添加
-- unqiue 使用细节-- 1. 如果没有指定 not null , 则 unique 字段可以有多个 null
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
INSERT INTO t21
VALUES(NULL, 'tom', 'tom@shou.com');
SELECT * FROM t21
-- 2. 一张表可以有多个 unique 字段
CREATE TABLE t22 (
id INT UNIQUE , -- 表示 id 列是不可以重复的.
`name` VARCHAR(32) UNIQUE , -- 表示 name 不可以重复
email VARCHAR(32)
);
5. foreign key（外键）
1. 基本介绍
用于定义主表和从表之间的关系: 外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null (学生/班级图示)
2. 基本语法
FOREIGN KEY (本字段表名) REFERENCES
主表名(主表名或unique字段名)
3. 细节说明
- 外键指向的表的字段，要求是primary key或者是unique
- 表的类型是innodb, 这样的表才支持外键
- 外键字段的类型要和主键字段的类型一致(长度可以不同)
- 外键字段的值，必须在主键宇段中出现过，或者为null [前提是外键字段允许为null]
- 一旦建立主外键的关系，数据不能随意删除了.
4. 演示
-- 外键演示
-- 创建 主表 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, -- 创建 从表 my_stu
FOREIGN KEY(class_id) REFERENCES my_class(id)
);
-- 测试数据
INSERT INTO my_class
VALUES(100, 'java'), (200, 'web'),(300, 'php');
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_stu
-- 一旦建立主外键的关系，数据不能随意删除了
DELETE FROM my_class WHERE id = 100;
6. check
用于强制行数据必须满足的条件假定在sal列上定义了check约束, 并要求sal列值在1000 ~ 2000之间如果不再1000 ~ 2000之间就会提示出错。
提示: oracle和sql server均支持check ,但是mysq15.7不支持check ,只做语法校验，但不会生效。在之后的8.0版本中就可以支持
-- 演示 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;
7. 商店售货系统表设计案例
现有一个商店的数据库shop_ db, 记录客户及其购物情况，由下面三个表组成:
商品goods (商品号goods_ id, 商品名goods_name, 单价unitprice, 商品类别category, 供应商provider);
客户customer (客户号customer_ id,姓 名name,住址address,电邮email性别sex,身份证card ld);
购买purchase (购买订单号order_ id,客户号customer_ id,商品号goods_ id,购买数量nums ):
建表，再定义中要求声明【进行合理设计】
- 每个表的主外键;
- 客户的姓名不能为空值;
- 电邮不能够重复;
- 客户的性别[男|女] check枚举…
- 单价unitprice在1.0 - 9999.99之间check
CREATE TABLE goods(
goods_id INT PRIMARY KEY,
goods_name VARCHAR(32) NOT NULL DEFAULT'',
unitprice DECIMAL(10, 4) NOT NULL DEFAULT 0
CHECK(unitprice >= 1.0 AND unitprice <= 999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(32) NOT NULL DEFAULT''
);
CREATE TABLE customer (
customer_id CHAR(8) PRIMARY KEY,
`name` VARCHAR(32) NOT NULL,
address VARCHAR(64) NOT NULL DEFAULT'',
email VARCHAR(64) UNIQUE NOT NULL,
sex ENUM('男','女') 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;
18. 自增长
1. 自增长基本介绍 一个问题
在某张表中，存在一个id列(整数),我们希望在添加记录的时候，该列从1开始，自动的增长，怎么处理?
字段名 整型 primary key auto_increment
添加 自增长的字段方式
insert into xXx (字段1,字段2....) values(null, '值'..);
insert into xXx (字段2....) values('值1', '值'....);
insert into XXX values(null, '值1' ....)
2. 自增长使用细节
- 一般来说自增长是和primary key配合使用的
- 自增长也可以单独使用[但是需要配合一个unique]
- 自增长修饰的字段为整数型的(虽然小数也可以但是非常非常
少这样使用) - 自增长默认从1开始，你也可以通过如下命令修改alter table 表名 auto increment = 新的开始值;
- 如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据.
3. 演示
-- 演示自增长的使用
-- 创建表
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');
INSERT INTO t24
VALUES(NULL, 'yzj@qq.com', 'yzj');
INSERT INTO t24
(email, `name`) VALUES ('nono@qq.com', 'nono');
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
VALUES(NULL, 'mary@qq.com', 'mary');
INSERT INTO t25
VALUES(666, 'yzj@qq.com', 'yzj');
SELECT * FROM t25
19. MySQL索引
1. 索引快速入门
说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql,查询速度就可能提高百倍干倍。
这里我们举例说明索引的好处[构建海量表]
- 可以直接复制，后面会详细说
- 先运行创建数据库，再运行后面的语句
-- 创建测试数据库 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 *
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 原来是 2.8s
-- 创建索引后，只对创建了索引的列有效
SELECT *
FROM emp
WHERE ename = 'PjDlwy'
-- 没有在 ename 创建索引时，时间 4.7s
CREATE INDEX ename_index ON emp (ename) -- 在 ename 上创建索引
2. 索引的原理
- 没有索引为什么会慢?
因为全表扫描. - 使用索引为什么会快?
形成一个索引的数据结构，比如二叉树 - 索引的代价
磁盘占用
对dml(update delete insert)语句的效率影响 - 在项目中我们使用，select的频率是90%，update，delete，insert的频率是10%
3. 索引的类型
- 主键索引，主键自动的为主索引（类型primary key）
- 唯一索引（UNIQUE)
- 普通索引（INDEX)
- 全文索引（FULLTEXT）[适用于MySAM]
一般开发，不使用mysql自带的全文索引，而是使用：全文搜索 Solr 和 ElasticSearch(ES)
4. 索引的使用
- 添加索引(建小表测试id，name )
- create [UNIQUE] index index_name on tbl_name ( col_name [(length)] [ASC | DESC] , …);
- alter table table_name ADD INDEX [index_name] (index_col_name, …)
- 添加主键(索引)
ALTER TABLE表名ADD PRIMARY KEY(列名…); - 删除索引
- DROP INDEX index_name ON tbl_name;
- alter table table_name drop index index_name;
- 删除主键索引 比较特别:
alter table t b drop primary key; - 查询索引(三种方式):
- 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_index2 ON t25(id);
-- 如何选择
-- 1. 如果某列的值，是不会重复的，则优先考虑使用 unique 索引, 否则使用普通索引
-- 添加普通索引方式 2
ALTER TABLE t25 ADD INDEX id_index03(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
5. 哪些列上适合使用索引
- 较频繁的作为查询条件字段应该创建索引
select * from emp where empno = 1 - 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex = '男 – 这个就没有意义 - 更新非常频繁的宇段不适合创建索引
select * from emp where logincount = 1 – 这个也没有意义 - 不会出现在WHERE子句中字段不该创建索引
20. mysql事务
1. 事务的基本操作
1. 什么是事务
事务用于保证数据的一致性, 它由一组相关的dml语句组成, 该组的dml语句要么全部成功,要么全部失败. 如:转账就要用事务来处理，用以保证数据的一致性。
2. 事务和锁
当执行事务操作时(dmI语句), mysqI会在表上加锁, 防止其它用户改表的数据.这对用户来讲是非常重要的
mysql数据库控制台事务的几个重要的操作
- start transaction – 开始一个事务
- savapoint保存点名 – 设置保存点
- rollback to保存点名 – 回退事务
- rollback --回退全部事务
- commit --提交事务,所有的操作生效,不能回退
-- 事务的一个重要的概念和具体操作
-- 演示
-- 1. 创建一张测试表
CREATE TABLE t27 (
id INT,
`name` VARCHAR(32)
);
-- 2. 开始事务
START TRANSACTION
-- 3. 设置保存点
SAVEPOINT a
-- 执行 dml 操作
INSERT INTO t27
VALUES(3000, 'tom');
SELECT * FROM t27
SAVEPOINT b
-- 执行 dml 操作
INSERT INTO t27
VALUES(1000, 'jack');
-- 回退到 b ROLLBACK TO b
ROLLBACK TO b
-- 继续回退 a ROLLBACK TO a
ROLLBACK a
-- 如果这样, 表示直接回退到事务开始的状态.
ROLLBACK
COMMIT -- 当你执行这个语句后，事务就不能回退
3. 回退事务
在介绍回退事务前，先介绍一下保存点(savepoint). 保存点是事务中的点.用于取消部分事务，当结束事务时(commit) ,会自动的删除该事务所定义的所有保存点. 当执行回退事务时，通过指定保存点可以回退到指定的点.
4. 提交事务
使用commit语句可以提交事务.当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效.]
5. 事务细节
- 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
- 如果开始一个事务，你没有创建保存点.你可以执行rollback,默认就是回退到你事务开始的状态
- 你也可以在这个事务中(还没有提交时)，创建多个保存点比如: savepoint
aaa; 执行dmI , savepoint bbb; - 你可以在事务没有提交前，选择回退到哪个保存点.
- mysqI的事务机制需要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, 'jack');
ROLLBACK -- 表示直接回退到事务开始的的状态
COMMIT;
-- 3. 你也可以在这个事务中(还没有提交时), 创建多个保存点.比如: savepoint aaa;
-- 执行 dml , savepoint bbb
-- 4. 你可以在事务没有提交前，选择回退到哪个保存点
-- 5. InnoDB 存储引擎支持事务 , MyISAM 不支持
-- 6. 开始一个事务 start transaction, 第二种方式：set autocommit=off;
2. mysql事务隔离级别
1. 事务的隔离级别介绍
- 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。(通俗解释)
- 如果不考虑隔离性，可能会引发如下问题:
- 脏读
- 不可重复读
- 幻读
2. 查看事务隔离级别
- 脏读(dirty read):当一个事务读取另一个事务向未提交的改变(update,insert,delete)时，产生脏读
- 不可重复读(nonrepeatable read):同查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
- 幻读(phantom read):同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。
3. 事务隔离级别
概念：mysql 隔离级别定义了事务与事务之间的隔离程度
开启两个控制台分别开启事务
-- 演示 mysql 的事务隔离级别
-- 1. 开了两个 mysql 的控制台
-- 2. 查看当前 mysql 的隔离级别
SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+
-- 3.把其中一个控制台的隔离级别设置 Read uncommitted 在控制台输入
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TABLE account (
id INT,
`name` VARCHAR(32),
money INT
)
-- 查看当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
-- SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]
4. 设置事务隔离级别
- 查看当前会话隔离级别
select @@tx_isolation; - 查看系统当前隔离级别
select @@global.tx_isolation; - 设置当前会话隔离级别
set session transaction isolation level repeatable read; - 设置系统当前隔离级别
set global transaction isolation level repeatable read; - mysql 默认的事务隔离级别是repeatable read ,-般情况下，没有特殊
要求，没有必要修改(因为该级别可以满足绝大部分项目需求)
5. mysql事务 ACID
1. 事务acid特性
- 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 - 一致性(Consistency)
事务必须使数据库从一个致性状态变换到另外一个一致性状态 - 隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。 - 持久性(Durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
6. mysql表类型和存储引擎
1. 基本介绍
- MySQL的表类型由存储引擎(Storage Engines)决定，主要包括MyISAM、innoDB、Memory等。
- MySQL数据表主要支持六种类型,分别是: CSV、 Memory, ARCHIVE、MRG MYISAM、MYISAM、InnoBDB。
- 这六种又分为两类，一类是”事务安全型”(transaction-safe), 比如:InnoDB; 其余都属于第二类，称为”非事务安全型”(non-transaction
safe)[mysiam和memory].
2. 主要的存储引擎/表类型特点
3. 细节说明
这里重点介绍三种: MyISAM、InnoDB、MEMORY
- MylSAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
- InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
- MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应i个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是-旦MySQL服务关闭，表中的数据就会丢失掉，表的结构还在。
4. 三种存储引擎表使用案例
对前面我们提到的三种存储引擎，我们举例说明：
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES
-- 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;
SHOW TABLE STATUS LIKE 't28';
-- 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
SHOW TABLE STATUS LIKE 't29';
5. 如何选择表的存储引擎
- 如果你的应用不需要事务,处理的只是基本的CRUD操作，那么MyISAM
是不二选择,速度快 - 如果需要支持事务，选择InnoDB。
- Memory 存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态())
- – 指令修改存储引擎
ALTER TABLEt29ENGINE = INNODB
21. 视图
1. 看一个需求
emp表的列信息很多,有些信息是个人重要信息(比如sal, comm,mgr, hiredate) ,如果我们希望某个用户只能查询emp表的(empno、ename, job和deptno )信息，有什么办法? =>视图
2. 基本概念
- 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
3. 视图的总结内容
- 视图是根据基表（可以是多个基表）来创建的，视图是虚拟的表
- 视图也有列，数据来自基表
- 通过视图可以修改基表的数据
- 基表的改变，也会影响视图的数据
4. 视图的基本使用
- create view 视图名 as select语句
- alter view 视图名 as select语句 – 更新成新的视图
- SHOW CREATE VIEW 视图名
- drop view 视图名1，视图名2
5. 完成前面的需求
-- 视图的使用
-- 创建一个视图 emp_view01，只能查询 emp 表的(empno、ename, job 和 deptno ) 信息
-- 创建视图
CREATE VIEW emp_view01
AS
SELECT empno,ename,job,deptno FROM emp01;
-- 查看视图
DESC emp_view01
SELECT * FROM emp_view01
SELECT empno, job FROM emp_view01;
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;
6. 视图细节讨论
- 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
- 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
- 视图中可以再使用视图，数据仍然来自基表
-- 视图的使用
-- 创建一个视图 emp_view01，只能查询 emp 表的(empno、ename, job 和 deptno ) 信息
-- 创建视图
CREATE VIEW emp_view01
AS
SELECT empno,ename,job,deptno FROM emp01;
-- 查看视图
DESC emp_view01
SELECT * FROM emp_view01
SELECT empno, job FROM emp_view01;
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;
-- 修改视图 会影响到基表
UPDATE emp_view01
SET job = 'MANAGER'
WHERE empno = 7369
SELECT * FROM emp01; -- 查询基表
SELECT * FROM emp_view01
-- 修改基本表， 会影响到视图
UPDATE emp01
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
7. 视图的最佳实践
1.安全：一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2.性能：关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN) 。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3.灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
22. Mysql 管理
1. Mysql 用户
mysql中的用户，都存储在系统数据库mysql中 user表中
其中user表的重要字段说明：
- host：允许登录的"位置”, localhost表示该用户只允许本机登录，也
可以指定ip地址，比如:192.1 68.1.100 - user : 用户名;
- authentication string:密码，是通过mysq|的password(函数加密之后
的密码。
2. 创建用户
create user’用户名@'允许登录位置’ identified by ‘密码’
说明:创建用户，同时指定密码
3. 删除用户
drop user ‘用户名’@‘允许登录位置’;
4. 用户修改密码
修改自己的密码:
set password = password(‘密码’);
修改他人的密码(需要有修改用户密码权限):
set password for ‘用户名’@’登录位置‘ = password(‘密码’);
-- mysql用户的管理
-- 原因，当我们做项目开发时，可以根据不同的开发人员，赋给他相应的mysql操作权限
-- 所以，mysql数据库管理人员（root），根据需要创建不同的用户，赋给相同的权限，供人员使用
-- 1. 创建新的用户
-- 1.1 'yzj_edu'@'localhost' 表示用户的完整信息'yzj_edu' 用户名 'localhost' 登录的ip
-- 1.2 '123456'密码， 但是注意 存放到mysql.user表时，是password('123456')加密后的密码
CREATE USER 'yzj_edu'@'localhost' IDENTIFIED BY '123456'
SELECT `host`,user,authentication_string
FROM mysql.user
-- 2. 删除用户
DROP USER 'yzj_edu'@'localhost'
-- 3. 修改自己的密码
SET PASSWORD = PASSWORD('abcde')
-- 4. 修改其他人的密码
SET PASSWORD FOR 'yzj_edu'@'localhost' = PASSWORD('abcde')
5. mysql中的权限
6. 给用户授权
基本语法
grant 权限列表 on 库.对象名 to ‘用户名’@‘登录位置’ [identified by ‘密码’]
说明:
- 权限列表,多个权限用逗号分开
grant select on …
grant select, delete, create on …
grant all [privileges] on… //表示赋予该用户在该对象上的所有权限 - 特别说明
*.*: 代表本系统中的所有数据库的所有对象(表，视图，存储过程)
库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等) - identified by 可以省略,也可以写出
(1) 如果用户存在，就是修改该用户的密码。
(2) 如果该用户不存在，就是创建该用户!
7. 回收用户授权
基本语法
revoke 权限列表 on 库.对象名 from ‘用户名’@‘登录位置’;
权限生效指令
如果权限没有生效,可以执行下面指令:
基本语法:
FLUSH PRIVILEGES;
8. 细节说明
- 在创建用户的时候，如果不指定Host,则为% , %表示表示所有IP都有连接权限create user xxx;
- 你也可以这样指定create user ‘xx’ @'192.168.1.%'表示xxx用户在192.1 68.1.*的ip可以登录mysql
- 在删除用户的时候，如果host不是%，需要明确指定’用户@‘host值’
3100
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
