Java学习-第一部分-第三阶段-第三节：MySQL基础

零基础学MySQL

笔记目录：(https://www.cnblogs.com/wenjie2000/p/16378441.html)

一个问题

淘宝网，京东、微信，抖音都有各自的功能,那么当我们退出系统的时候，下次再访问时，为什么信息还存在? =》数据库

解决之道-文件、数据库

为了解决上述问题，使用更加利于管理数据的东东-数据库，它能更有效的管理数据。

举一个生活化的复列说明
如果说图书馆是保存书籍的,那么数据库就是保存数据的。

数据库的简单原理图

MySQL安装和配置

Mysql下载地址：https://downloads.mysql.com/archives/community/

我安装的版本为MySQL5.7。如果是安装其他版本，请自行百度。不同版本的MySql安装过程存在差别。

特别说明

如果安装过Mysql过程中，出错了或者想重新安装
sc delete mysql 【会删除已经安装好的mysql服务 。慎重使用】

安装步骤

请注意，zip 安装文件是压缩文件，和.exe安装文件是不一样的, 要严格的下面的步骤来执行，否则安装很可能不会成功。

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

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

3. 这里我解压到 D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64 目录下 【可自行指定目录,目录不能有中文，尽量不安装在系统盘】

4. 添加环境变量 : 电脑-属性-高级系统设置-环境变量，在Path 环境变量增加mysql的安装目录\bin目录。如下图所示

   

5. 在D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64目录下下创建 my.ini 文件, 需要我们自己创建（其他非5.7版本会有差异）。用记事本打开该文件，写入以下内容并保存（注意要根据自己的安装位置更改文本中的目录）。

   [client]port=3306default-character-set=utf8[mysqld]# 设置为自己MYSQL的安装目录basedir=D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64\# 设置为MYSQL的数据目录datadir=D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64\data\port=3306character_set_server=utf8#跳过安全检查（登录不需要密码）skip-grant-tables

6. 使用管理员身份打开 cmd , 并切换到 D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64\bin 目录下, 执行mysqld -install

   d:cd D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64mysqld -install

   

   如果入到下面两种情况，是由于你的电脑缺失了相关的系统文件。下载并安装：https://www.microsoft.com/zh-CN/download/details.aspx?id=40784

7. 初始化数据库: mysqld –initialize-insecure –user=mysql

   如果执行成功，会生成 data目录:

8. 启动mysql 服务: net start mysql 【停止mysql服务指令 net stop mysql】, 如果成功:

   

   任务管理器中也会出现MySQL服务

   

9. 进入mysql 管理终端: mysql -u root -p 【当前root 用户密码为 空，下一行要输入密码时直接回车】

10. 修改root 用户密码

    use mysql;  update user set authentication_string=password('123456') where user='root' and Host='localhost';解读: 上面的语句就是修改 root用户的密码为 123456注意：在后面需要带 分号，回车即可执行该指令执行: flush privileges; 刷新权限退出: quit

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

    #跳过安全检查（登录不需要密码）#skip-grant-tables  

12. 重新启动mysql

    net stop mysqlnet start mysql提示: 该指令需要退出mysql ， 在Dos下执行.

13. 再次进入Mysql, 输入正确的用户名和密码

    mysql -u root -p密码正确，进入mysql密码错误，提示如下信息ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

安装过程中，一定要按照老师的步骤来，不然会错误.

如果真的错误了， 清除mysql服务, 再次安装.

连接到Mysql服务(Mysql数据库)的指令

mysql -h 主机IP -P 端口 -u 用户名 -p密码

提醒:

1. -p密码不要有空格
2. -p后面没有写密码，回车会要求输入密码
3. 如果没有写-h主机，默认就是本机
4. 如果没有写-P端口，默认就是3306
5. 在实际工作中，3306一般修改

安装Navicat

http://www.navicat.com.cn/download/navicat-for-mysql

安装过程很简单，此处省略

如果想破解，百度，教程很多

安装后，打开，点击左侧的“连接”，进行如下配置。

数据库

注意：navicat只是方便手动操作和查看，光是会使用它没有太大意义。实际开发中程序员操作数据库还是要通过指令。指令是必须要认真学的。

数据库三层结构-破除 MySQL 神秘

1. 所谓安装Mysql数据库，就是在主机安装一个数据库管理系统(DBMS),这个管理程序可以管理多个数据库。DBMS(database manage system)

2. 一个数据库中可以创建多个表,以保存数据(信息)。

3. 数据库管理系统(DBMS)、数据库和表的关系如图所示:示意图

   

数据在数据库中的存储方式

SQL 语句分类

DDL:数据定义语句[create表，库…]

DML:数据操作语句[增加insert,修update,删除delete]

DQL:数据查询语句[select ]

DCL:数据控制语句[管理数据库:比如用户权限 grant revoke ]

创建

注意：

在mysql数据库中，sql关键字和大小写 是不区分大小写的。

windows下mysql中的数据库名、表名、列名默认是不区分大小写的，但是linux会区分大小写，所以当执行的sql语句有大小写区别时需要注意。

因此，mysql创建数据库、表、列时，尽量使用小写命名，如果名字由多个单词组成可以使用“_”作为间隔符。如果名字和关键字重名可以用反引号“`”括起来。

具体情况可以看这篇博客：https://www.cnblogs.com/chenhaoblog/p/13604727.html

CREATE DATABASE [IF NOT EXISTS] db_name[create_specification [,create_specification]...]create_specification:[DEFAULT]CHARACTER SET charset_namel[DEFAULT]COLLATE collation_name#中括号中的内容为可写可不写的，根据实际情况决定。实际语句的使用中不要写上中括号

1. CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认utf&,

2. COLLATE:指定数据库字符集的校对规则(常用的utf8 bin[区分大小写]、utf8 general ci[不区分大小写（例：A==a）]注意默认是utf8_general_ci)

   # 演示数据库的操作#创建一个名称为 hsp_db01 的数据库。[图形化和指令 演示]#使用指令创建数据库CREATE DATABASE hsp_db01;#删除数据库指令DROP DATABASE hsp_db01#创建一个使用 utf8 字符集的 hsp_db02 数据库CREATE DATABASE hsp_db02 CHARACTER SET utf8#创建一个使用 utf8 字符集，并带校对规则的 hsp_db03 数据库CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin#校对规则 utf8_bin 区分大小 默认 utf8_general_ci 不区分大小写#下面是一条查询的 sql , select 查询 * 表示所有字段 FROM 从哪个表#WHERE 从哪个字段 NAME = 'tom' 查询名字是 tomSELECT * 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 DATABASE `INT`#删除前面创建的 hsp_db01 数据库DROP DATABASE hsp_db01

备份恢复数据库

备份数据库(注意:在DOS执行)

mysqldump -u 用户名 -p[密码] -B 数据库1 数据库2 数据库n > 路径\\文件名.sql-p后面可以加上密码。也可以不写，回车后再输入密码

恢复数据库(注意:cmd进入MySQL命令行再执行)

Source 文件名.sql

#练习 : database03.sql 备份 hsp_db02 和 hsp_db03 库中的数据，并恢复#备份, 要在 Dos 下执行 mysqldump 指令其实在 mysql 安装目录\bin#这个备份的文件，就是对应的 sql 语句mysqldump -u root -p -B hsp_db02 hsp_db03 > d:\\bak.sqlDROP DATABASE ecshop;#恢复数据库(注意：在DOS界面，先进入 Mysql 命令行再执行)source d:\\bak.sql#第二个恢复方法， 直接将 bak.sql 的内容放到查询编辑器中，执行

备份恢复数据库的表

mysqldump -u 用户名 -p[密码] 数据库 表1 表2 表n > 路径\\文件名.sql-p后面可以加上密码。也可以不写，回车后再输入密码恢复方式和上面一样

表创建

CREATE TABLE table_name(field1 datatype,    field2 datatype,    field3 datatype)character set 字符集 collate 校对规则 engine 引擎field:指定列名 datatype:指定列类型(字段类型)character set:如不指定则为所在数据库字符集collate:如不指定则为所在数据库校对规则engine:引擎(这个涉及内容较多，后面单独讲解)

删除

drop table 表名;

修改

如果看了b站 韩顺平老师的课程，注意他的PPT这部分的指令有问题，下面的才是对的。

#添加列ALTER TABLE 表名ADD 列名 datatype [DEFAULT expr];#修改列类型ALTER TABLE 表名MODIFY 列名 datatype [DEFAULT expr];#修改列类型及名称ALTER TABLE 表名 CHANGE 列名 新列名 datatype;#删除列ALTER TABLE 表名;DROP 列名;查看表的结构: desc 表名; --可以查看表的列#修改表名:rename table 表名 to 新表名;#修改表字符集:alter table 表名 character set 字符集;

应用实例：

1. 员工表emp增加一个image列,varchar类型(要求在resume后面).
2. 修改job列，使其长度为60。
3. 删除sex列。
4. 表名改为employee。
5. 修改表的字符集为utf8
6. 列名name修改为user_name

ALTER TABLE empADD image varchar(32) NOT NULL DEFAULT '' AFTER resume;ALTER TABLE empMODIFY job varchar(60);ALTER TABLE empDROP sex;DESC emp;rename table emp to employee;ALTER TABLE employee character set utf8;ALTER TABLE employee CHANGE `name` `user_name` varchar(20);

Mysql数据类型

数值型（整数）的基本使用

说明：在能够满足需求的情况下，尽量选择占用空间小的类型（节省资源）

#演示整型的是一个#使用 tinyint 来演示范围 有符号 -128 ~ 127 如果没有符号 0-255#说明： 表的字符集，校验规则, 存储引擎，老师使用默认#1. 如果没有指定 unsinged , 则 TINYINT 就是有符号#2. 如果指定 unsinged , 则 TINYINT 就是无符号 0-255CREATE 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;

数值型(bit)的使用

1. 基本使用

   create table t02 (num bit(8));insert into t02 (1,3);insert into t02 values(2,65);

2. 细节说明

   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);SELECT * FROM t05;SELECT * FROM t05 WHERE num = 1;

数值型(小数)的基本使用

1. FLOAT/DOUBLE [UNSIGNED]

Float单精度精度，Double 双精度

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

字符串的基本使用

1. CHAR(size)

   固定长度字符串最大255字符

2. VARCHAR(size)

   可变长度字符串最大65532字节【utf8编码最大21844字符（因为(65535-2)/3)=21844.3），1-2个字节用于记录存储数据长度。如果允许为null也要占用一个字节，不允许为空则不占用这一个字节】

字符串使用细节

1. 细节1

   char(4)//这个4表示字符数(最大255)，不是字节数,不管是中文还是字母都是放四个,按字符计算.

   varchar(4)//这个4表示字符数,不管是字母还是中文都以定义好的表的编码来存放数据.

   不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的.

2. 细节2

   char(4)是定长(固定的大小)，就是说，即使你插入’aa’，也会占用分配的4个字符的空间.

   varchar(4)是变长，就是说，如果你插入了’aa’,实际占用空间大小并不是4个字符，而是按照实际占用空间来分配(说明:varchar本身还需要多占用1-3个字节)

3. 细节3

   什么时候使用char,什么时候使用varchar

   1. 如果数据是定长,推荐使用char，比如md5的密码,邮编,手机号，身份证号码等. char(32)
   2. 如果一个字段的长度是不确定，我们使用varchar，比如留言,文章

   查询速度:char > varchar

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

#演示字符串类型的使用细节#char(4) 和 varchar(4) 这个 4 表示的是字符，而不是字节, 不区分字符是汉字还是字母CREATE TABLE t11(`name` CHAR(4));INSERT INTO t11 VALUES('韩顺平123');SELECT * FROM t11;CREATE TABLE t12(`name` VARCHAR(4));INSERT INTO t12 VALUES('韩顺平212');INSERT INTO t12 VALUES('ab 北京');SELECT * FROM t12;#如果 varchar 不够用，可以考试使用 mediumtext 或者 longtext, #如果想简单点，可以使用直接使用 textCREATE TABLE t13( content TEXT, content2 MEDIUMTEXT , content3 LONGTEXT);INSERT INTO t13 VALUES('韩顺平教育', '韩顺平教育 100', '韩顺平教育 1000~~');SELECT * FROM t13;

日期类型的基本使用

CREATE TABLE birthday( t1 DATE, t2 DATETIME,t3 TIMESTAMP NOT NULL DEFAULTCURRENT TIMESTAMP ON UPDATECURRENT TIMESTAMP ); mysql> INSERT INTO birthday(t1,t2)VALUES('2022-11-11','2022-11-11 10:10:10');#timestamp时间戳

日期类型的细节说明

TimeStamp在Insert和update时，自动更新

#演示时间相关的类型#创建一张表, date , datetime , timestampCREATE 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 列会自动的以当前时间进行更新

练习题

-- 自己一定要练习一把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`;

CRUD

C[creat]R[read]U[update]D[delete]

lnsert(添加数据)

INSERT INTO 表名 [(column1 [, column2. . .])]VALUES (value1 [, value2...]);

快速入门案例:

1. 创建一张商品表goods(id int , goods_name varchar(10),price double);

2. 添加2条记录

   create table goods(id int,goods_name varchar(10),price double);INSERT INTO goods VALUES (10,'可乐',4.5);INSERT INTO goods (id,goods_name,price) VALUES (11,'可乐2',4.6);

细节说明

1. 插入的数据应与字段的数据类型相同。

   比如把’abc’添加到int类型会错误

2. 数据的长度应在列的规定范围内，例如:不能将一个长度为80的字符串加入到长度为40的列中。

3. 在values中列出的数据位置必须与被加入的列的排列位置相对应。

4. 字符和日期型数据应包含在单引号中。

5. 列可以插入空值[前提是该字段允许为空]，insert into table value(null)

6. insert into 表名 (列名…) values (),(),()形式添加多条记录

   INSERT INTO `goods` (id,goods_name,price)VALUES(50,'三星手机',2300),(60,'海尔手机',1800);

7. 如果是给表中的所有字段添加数据,可以不写前面的字段名称

   INSERT INTO goods VALUES (10,'可乐',4.5);

8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加,否则报错

Update（更新数据）

UPDATE 表名SET 列名1=expr1 [, 列名2=expr2 ...][WHERE where_definition]

基本使用：

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

1. 将所有员工薪水修改为5000元。

2. 将姓名为小妖怪的员工薪水修改为3000元。

3. 将老妖怪的薪水在原有基础上增加1000元。

   -- 1. 将所有员工薪水修改为 5000 元。[如果没有带 where 条件，会修改所有的记录，因此要小心]UPDATE employee SET salary=5000;-- 2UPDATE employee SET salary=3000 WHERE `user_name`='小妖怪';-- 3UPDATE employee SET salary=salary+1000 WHERE `user_name`='老妖怪';

使用细节:

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

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

3. WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

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

   UPDATE employeeSET salary =salary +1000,job ='出主意的' WHERE user_name ='老妖怪';

Delete（删除数据）

delete from 表名[WHERE where_definition]

快速入门案例(使用employee测试)

1. 删除表中名称为’老妖怪’的记录

2. 删除表中所有记录。

   -- 1DELETE FROM `employee` WHERE user_name='老妖怪';-- 2DELETE FROM `employee`;

使用细节

1. 如果不使用where子句，将删除表中所有数据。
2. Delete语句不能删除某一列的值(可使用update设为null或者”)
3. 使用delete语句仅删除记录，不删除表本身。如要删除表，使用droptable语句。drop table 表名;

Select（查找数据-单表）

SELECT [DISTINCT] * | {column1, column2, column3... }FROM 表名;

注意事项

1. Select 指定查询哪些列的数据。
2. column指定列名。
3. *号代表查询所有列。
4. From指定查询哪张表。
5. DISTINCT可选,指显示结果时,去掉重复数据（此处重复指的是两行内容完全相同）

练习题

先使用以下指令创建表，不只是这题需要用，后面还要用：

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,'李明',80,65,30);

1. 查询表中所有学生的信息。

2. 查询表中所有学生的姓名和对应的英语成绩。

3. 过滤表中重复数据distinct 。

4. 要查询的记录，每个字段都相同，才会去重

   -- 1SELECT * FROM `student`;-- 2SELECT `name`,`english` FROM student;-- 3SELECT DISTINCT * FROM `student`;-- 4SELECT DISTINCT * FROM `student`;

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

SELECT * | { column1 | expression,column2 | expression，...}FROM tablename;

在select语句中可使用as语句

SELECT column_name [as] 别名 from 表名;-- 此处as可加可不加。如果不加就必须中间有空格

练习

1. 统计每个学生的总分

2. 在所有学生总分加10分的情况

3. 使用别名表示学生分数。

   -- 1SELECT `name`,(chinese+english+math) FROM `student`;-- 2SELECT `name`,(chinese+english+math+10) FROM `student`;-- 3SELECT `name` AS '名字',(chinese+english+math) AS total_score FROM `student`;

在where子句中经常使用的运算符

练习

使用where子句，进行过滤查询select

1. 查询姓名为赵云的学生成绩

2. 查询英语成绩大于90分的同学

3. 查询总分大于200分的所有同学

   -- 1SELECT * FROM `student` WHERE `name`='赵云';-- 2SELECT * FROM `student` WHERE `english`>90;-- 3SELECT * FROM `student` WHERE (chinese+english+math)>200;

练习2

使用where子句，练习[5min]:

1. 查询math大于60并且(and) id大于4的学生成绩

2. 查询英语成绩大于语文成绩的同学

3. 查询总分大于200分并且数学成绩小于语文成绩,的姓赵的学生.

   -- 1SELECT * FROM `student` WHERE `math`>60 and `id`>4;-- 2SELECT * FROM `student` WHERE `english`>`chinese`;-- 3#韩% 表示名字以韩开头的就可以，%表示0个到多个字符，可以和Like搭配使用SELECT * FROM `student` WHERE (chinese+english+math)>200 and `math`<`chinese` and `name` LIKE '赵%';

练习3

1. 查询英语分数在80 – 90之间的同学。

2. 查询数学分数为89,90,91的同学。

3. 查询所有姓李的学生成绩。

4. 查询数学分>80，语文分>80的同学。

5. 查询总分为189,190,233的同学。

6. 查询所有姓李或者姓宋的学生成绩。

7. 查询数学比语文多30分的同学。

   -- 1SELECT * FROM `student` WHERE `english`>=80 and `english`80 and `chinese`>80;-- 5SELECT * FROM `student` WHERE (chinese+english+math)=189 OR (chinese+english+math)=190 or (chinese+english+math)=233;SELECT * FROM `student` WHERE (chinese+english+math) in (189,190,233);-- 6SELECT * FROM `student` WHERE `name` Like '李%' or `name` Like '宋%';-- 7SELECT * FROM `student` WHERE `math`-`chinese`>30;

使用order by子句排序查询结果

SELECT column1, column2, column3...FROM tableorder by column4 asc|desc,column5 asc|desc,...

1. Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
2. Asc升序[默认]、Desc降序
3. ORDER BY子句应位于SELECT语句的结尾。

练习:

1. 对数学成绩排序后输出【升序】。

2. 对总分按从高到低的顺序输出

3. 对姓李的学生成绩[总分]排序输出(升序)

   -- 1SELECT * FROM `student` ORDER BY `math`;-- 2#可以使用别名排序SELECT *,(chinese+english+math) AS total_score FROM `student` ORDER BY total_score DESC;-- 3SELECT `name`,(chinese+english+math) AS total_score FROM `student` WHERE `name` Like '李%' ORDER BY total_score;

函数统计函数合计/统计函数- count

Count返回行的总数

Select count(*) | count(列名) from table_name[WHERE where_definition]

练习:

1. 统计一个班级共有多少学生?

2. 统计数学成绩大于90的学生有多少个?

3. 统计总分大于250的人数有多少?

4. count(*)和count(列)的区别

   -- 1Select count(*) FROM `student`;-- 2Select count(*) FROM `student` WHERE `math`>90;-- 3SELECT count(*) FROM `student` WHERE (chinese+english+math)>250;-- 4#count(*)和count(列)的区别-- 解释: count(*)返回满足条件的记录的行数-- count(列):统计满足条件的某列有多少个，但是会排除为null的情况

合计函数-sum

Sum函数返回满足where条件的行的和 一般使用在数值列

select sum(列名){, sum(列名)...} from tablename[WHERE where_definition]

练习

1. 统计一个班级数学总成绩?

2. 统计一个班级语文、英语、数学各科的总成绩

3. 统计一个班级语文、英语、数学的成绩总和

4. 统计一个班级语文成绩平均分

   注意: sum仅对数值起作用，否则没有意义。

   注意:对多列求和，“,”号不能少。

   -- 1SELECT SUM(math) from `student`;-- 2select sum(chinese),sum(english),sum(math) from `student`;-- 3SELECT SUM(chinese+english+math) from `student`;-- 4SELECT SUM(chinese)/count(*) from `student`;

合计函数- avg

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

Select avg(列名){,avg(列名)...]from tablename[WHERE where_definition]

练习:

1. 求一个班级数学平均分?

2. 求一个班级总分平均分

   -- 1select avg(math) from `student`;-- 2select avg(chinese+english+math) from `student`;

合计函数-Max/min

Max/min函数返回满足where条件的一列的最大/最小值

select max(列名) from tablename[WHERE where_definition]

练习:

1. 求班级最高分和最低分(数值范围在统计中特别有用)

   select max(chinese+english+math),min(chinese+english+math) from `student`;

分组统计GROUP BY和HAVING

使用group by子句对列进行分组[先创建测试表]

SELECT column1, column2. column3... FROM tablegroup by column1 [,column2...];

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

SELECT column1, column2, column3 ...FROM tablegroup by column having ...

group by用于对查询的结果分组统计

having子句用于限制分组显示结果.(注意：where是对原始每行的数据过滤，having是对经过分组集成的数据进行进行限制，一般和group by配套使用)

练习

先使用下面的指令创建表

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');#创建表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 ,/*上级编号*/hiredate DATE NOT NULL,/*入职时间*/sal DECIMAL(7,2)  NOT NULL,/*薪水*/comm DECIMAL(7,2) ,/*红利*/deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/);INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  (7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);#工资级别表CREATE TABLE salgrade(grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,losal DECIMAL(17,2)  NOT NULL,hisal DECIMAL(17,2)  NOT NULL);INSERT INTO salgrade VALUES (1,700,1200);INSERT INTO salgrade VALUES (2,1201,1400);INSERT INTO salgrade VALUES (3,1401,2000);INSERT INTO salgrade VALUES (4,2001,3000);INSERT INTO salgrade VALUES (5,3001,9999);

1. 如何显示每个部门的平均工资和最高工资

2. 显示每个部门的每种岗位的平均工资和最低工资

3. 显示平均工资低于2000的部门号和它的平均工资

   -- 1select deptno,avg(sal),max(sal) from emp group by deptno;-- 2select deptno,job,avg(sal),min(sal) from emp group by deptno,job;-- 3-- 分析〔写sql语句的思路是化繁为简，各个击破]-- 3.1 显示各个部门的平均工资和部门号-- 3.2 在1的结果基础上，进行过滤，保留AVG(sal) <2000select deptno,avg(sal) from emp group by deptno;select deptno,avg(sal) 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-- 注意：和java不一样 ，mysql是从1开始计算顺序的-- 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 ename,LEFT(ename, 2),RIGHT(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 ) 逐字符比较两字串大小(不区分大小写) 返回数字0(一样)，-1(string2大),1(string1大)SELECT STRCMP('hsp', 'jsp') FROM DUAL;#输出 -1-- 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;

练习:

1. 以首字母小写的方式显示所有员工emp表的姓名

   SELECT ename,CONCAT(LCASE(LEFT(`ename`,1)),SUBSTRING(ename,2)) from emp;

数学函数

-- 演示数学相关函数-- 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; -- 下面的 16 是 16 进制的 16, 转成 10 进制输出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 ) 转十六进制  (括号内的数是十进制)SELECT HEX(46) FROM DUAL;#2E-- LEAST (number , number2 [,..]) 求最小值SELECT LEAST(0,1, -10, 4) FROM DUAL; -- MOD (numerator ,denominator ) 求余SELECT MOD(10, 3) FROM DUAL; -- RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0-- 说明-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，该随机数也不变了SELECT RAND() FROM DUAL;-- ROUND(number)  四舍五入select round(5.5);

时间日期

上面函数的细节说明:

1. DATE_ADD()中的interval后面可以是year、month、day、hour、minute、second。
2. DATE SUB()中的interval后面可以是year、month、day、hour、minute、second。
3. DATEDIFF(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
4. DATE、DATE_ADD、DATE_SUB、DATEDIFF这四个函数的日期类型可以是date, datetime或者timestamp

-- 日期时间相关函数【1】-- CURRENT_DATE ( ) 当前日期SELECT CURRENT_DATE() FROM DUAL; #2022-09-15-- CURRENT_TIME ( )当前时间SELECT CURRENT_TIME() FROM DUAL; #20:23:10-- CURRENT_TIMESTAMP ( ) 当前时间戳SELECT CURRENT_TIMESTAMP() FROM DUAL; #2022-09-15 20:23:19-- NOW( ) 当前时间-- CURRENT_TIMESTAMP()和NOW()没区别SELECT NOW() FROM DUAL;  #2022-09-15 20:23:20-- LAST_DAY(datetime) 返回datetime当月最后一天的日期LAST_DAY(NOW()); -- 2022-09-15

实例

-- 创建测试表 信息表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;

1. 显示所有留言信息,发布日期只显示日期，不用显示时间.

2. 请查询在10分钟内发布的帖子

3. 请在mysql 的sql语句中求出2011-11-11和1990-1-1相差多少天

4. 请用mysql的sql语句求出你活了多少天?[练习]

5. 如果你能活80岁,求出你还能活多少天.[练习]

   -- 日期时间相关函数【2】-- 1SELECT id,content,DATE(send_time) FROM mes;-- 2 (两种方式)select * FROM mes WHERE TIMEDIFF(NOW(),send_time)= NOW();SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)-- 3SELECT DATEDIFF('2011-11-11','1990-1-1');-- 4SELECT DATEDIFF(NOW(), '2000-08-11'); -- 5SELECT DATEDIFF(DATE_ADD('2000-08-11', INTERVAL 80 YEAR),NOW());

-- 日期时间相关函数【3】-- YEAR|Month|DAY|DATE (datetime )SELECT YEAR(NOW()) FROM DUAL;#2022SELECT MONTH(NOW()) FROM DUAL;#9SELECT DAY(NOW()) FROM DUAL;#15SELECT MONTH('2013-11-10');#11-- 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;

加密和系统函数

-- 演示加密函数和系统函数-- USER() 查询用户-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IPSELECT USER(); -- 用户@IP 地址-- DATABASE()查询当前使用数据库名称SELECT DATABASE();-- MD5(str) 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密-- root 密码是 hsp -> 加密 md5 -> 在数据库中存放的是加密后的密码SELECT MD5('hsp') FROM DUAL;SELECT LENGTH(MD5('hsp')) FROM DUAL; -- 演示用户表，存放密码时，是 md5CREATE TABLE hsp_user(id INT , `name` VARCHAR(32) NOT NULL DEFAULT '', pwd CHAR(32) NOT NULL DEFAULT '');INSERT INTO hsp_user VALUES(100, '韩顺平', MD5('hsp'));SELECT * FROM hsp_user;SELECT * FROM hsp_user WHERE `name`='韩顺平' AND pwd = MD5('hsp');-- PASSWORD(str)-- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密SELECT PASSWORD('123456') FROM DUAL; -- 数据库中存储的密码是 *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9-- select * from mysql.user \G 从原文密码 str 计算并返回密码字符串-- 通常用于对 mysql 数据库的用户密码加密-- mysql.user 表示 '数据库.表'  这样不不需要切换到mysql表再查userSELECT user,user.authentication_string FROM mysql.user;#可看到用户名和加密后的密码select password('123456'); #*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 和数据库中存储的密码对应

流程控制

先看两个需求:

1. 查询emp表,如果comm是null，则显示0.0
2. 如果emp表的job是CLERK则显示职员，如果是 MANAGER 则显示经理如果是SALESMAN则显示销售人员,其它正常显示.

# 演示流程控制语句# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3SELECT IF(TRUE, '北京', '上海') FROM DUAL;# IFNULL(expr1,expr2) 如果 expr1 为空 NULL,返回 expr2,否则返回 expr1SELECT IFNULL( NULL, '韩顺平教育') FROM DUAL;# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]# 如果 expr1 为 TRUE,则返回 expr2,如果 expr2 为 t, 返回 expr4, 否则返回 expr5SELECT CASE WHEN TRUE THEN 'jack'WHEN FALSE THEN 'tom' ELSE 'mary' END; -- jack

练习

1. 查询 emp 表, 如果 comm 是 null , 则显示 0.0
2. 如果 emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理– 如果是 SALESMAN 则显示 销售人员，其它正常显示

-- 1-- 说明，判断是否为 null 要使用 is null, 判断不为空 使用 is notSELECT ename, IF(comm IS NULL , 0.0, comm) FROM emp;SELECT ename, IFNULL(comm, 0.0) FROM emp;-- 2SELECT ename, CASEWHEN job = 'CLERK' THEN '职员' WHEN job = 'MANAGER' THEN '经理' WHEN job = 'SALESMAN' THEN '销售人员' ELSE job END AS 'job' FROM emp;SELECT * FROM emp;

MySQL表查询–加强

先执行以下语句创建表，如果看过函数部分已经创建过（emp、dept、salgrade这三张表）则忽略

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');#创建表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 ,/*上级编号*/hiredate DATE NOT NULL,/*入职时间*/sal DECIMAL(7,2)  NOT NULL,/*薪水*/comm DECIMAL(7,2) ,/*红利*/deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/);INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  (7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);#工资级别表CREATE TABLE salgrade(grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,losal DECIMAL(17,2)  NOT NULL,hisal DECIMAL(17,2)  NOT NULL);INSERT INTO salgrade VALUES (1,700,1200);INSERT INTO salgrade VALUES (2,1201,1400);INSERT INTO salgrade VALUES (3,1401,2000);INSERT INTO salgrade VALUES (4,2001,3000);INSERT INTO salgrade VALUES (5,3001,9999);

使用where子句

1. 如何查找1992.1.1后入职的员工

如何使用like操作符

1. %:表示O到多个字符:表示单个字符
2. 如何显示首字符为S的员工姓名和工资
3. 如何显示第三个字符为大写O的所有员工的姓名和工资

如何显示没有上级的雇员的情况

查询表结构

-- 查询加强-- ■ 使用 where 子句-- ?如何查找 1992.1.1 后入职的员工-- 老师说明： 在 mysql 中,日期类型可以直接比较, 需要注意格式SELECT * FROM emp WHERE hiredate > '1992-01-01'-- ■ 如何使用 like 操作符(模糊)-- %: 表示 0 到多个任意字符 _: 表示单个任意字符-- ?如何显示首字符为 S 的员工姓名和工资SELECT ename, sal FROM emp WHERE ename LIKE 'S%' -- ?如何显示第三个字符为大写 O 的所有员工的姓名和工资SELECT ename, sal FROM emp WHERE ename LIKE '__O%' -- ■ 如何显示没有上级的雇员的情况SELECT * FROM emp WHERE mgr IS NULL; -- 注意这里不能用“=”，要用“is”-- ■ 查询表结构DESC emp;-- 使用 order by 子句-- ?如何按照工资的从低到高的顺序[升序]，显示雇员的信息SELECT * FROM emp ORDER BY sal;-- ?按照部门号升序而同一部门中的雇员的工资降序排列, 显示雇员信息SELECT * FROM emp ORDER BY deptno ASC , sal DESC;

分页查询

1. 按雇员的id号升序取出,每页显示3条记录，请分别显示第一页，第二页，第三页

2. 基本语法:select … limit start, rows

   表示从start+1行开始取,取出rows行, start 从0开始计算

练习题:

1. 按雇员的id号降序取出，每页显示5条记录。请分别显示第3页，第5页对应的sql语句

   -- 分页查询-- 第 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 empORDER BY empnoLIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数;

分组函数和分组子句group by

1. 显示每种岗位的雇员总数、平均工资。

2. 显示雇员总数，以及获得补助(comm非空)的雇员数。

3. 显示管理者的总人数。(即mgr有多少种)

4. 显示雇员工资的最大差额。

   -- 1select job,count(*),avg(sal) from  emp group by job;-- 2-- 思路: 获得补助的雇员数 就是 comm 列为非 null, 就是 count(列).如果此处的值为null,不会统计select count(*),count(comm) from emp;-- 扩展要求：统计没有获得补助的雇员数SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL)) FROM emp;SELECT COUNT(*), COUNT(*) - COUNT(comm) FROM emp-- 3SELECT COUNT(DISTINCT mgr) FROM emp;-- 4SELECT MAX(sal) - MIN(sal) FROM emp;

数据分组的总结

如果select语句同时包含有where,group by ,having , limit,order by那么他们的顺序是where,group by,having , order by,limit

SELECT column1, column2, column3... FROM tablewhere where_definition    group by column    having condition    order by column    limit start, rows;

应用案例：

1. 请统计各个部门的平均工资，并且是大于 1000 的，并且按照平均工资从高到低排序，取出前两行记录

   select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal>1000 order by avg_sal desc limit 0,2;

MySQL多表查询

多表查询是指基于两个和两个以上的表查询.在实际应用中,查询单个表可能不能满足你的需求,(如下面的课堂练习)，需要使用到(dept表和emp表)

多表查询练习

1. 显示雇员名,雇员工资及所在部门的名字【笛卡尔集】

   小技巧:多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集?

   

2. 如何显示部门号为10的部门名、员工名和工资

3. 显示各个员工的姓名，工资,及其工资的级别

   -- 多表查询-- 1. 显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】/*分析    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, 否则会出现笛卡尔集-- 2. 如何显示部门号为 10 的部门名、员工名和工资SELECT ename,sal,dname,emp.deptno    FROM emp, dept    WHERE emp.deptno = dept.deptno AND emp.deptno = 10    -- 3.显示各个员工的姓名，工资，及其工资的级别-- 思路 姓名，工资 来自 emp 13-- 工资级别 salgrade 5-- 写 sql , 先写一个简单，然后加入过滤条件...select ename, sal, grade    from emp , salgrade    where sal between losal and hisal;    -- 练习:显示雇员名(ename),雇员工资(sal)及所在部门的名字(dname),并按部门排序[降序排].select ename,sal,dname from emp,dept where emp.deptno=dept.deptno order by dname desc;

自连接

自连接是指在同一张表的连接查询[将同一张表看做两张表]。

思考题:

1. 显示公司员工和他的上级的名字（给表取别名，使其能当两张表用）

   select worker.ename as '员工名',boss.ename as '上级' from emp worker,emp boss where worker.mgr=boss.empno;

子查询

什么是子查询：

子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询

单行子查询：

单行子查询是指只返回一行数据的子查询语句

请思考:如何显示与SMITH同一部门的所有员工?

多行子查询：

多行子查询指返回多行数据的子查询 使用关键字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;

子查询当做临时表使用

练习题：

先使用ecshop.sql文件创建数据库—- 下载

1. 查询ecshop中各个类别(cat_id)中，价格最高的商品.结果 如下:

   

   提示，可以将子查询当做一张临时表使用

   select * from ecshop.ecs_goods;select goods_id,cat_id,goods_name,shop_price from ecshop.ecs_goods;-- 1-- 先得到 各个类别中，价格最高的商品 max + group by cat_id, 当做临时表select cat_id,Max(shop_price) from ecshop.ecs_goods group by cat_id;-- 把子查询当做一张临时表可以解决很多很多复杂的查询select goods_id,cat_id,goods_name,shop_price from ecshop.ecs_goods;select temp.cat_id,goods_id,goods_name,shop_price from (    select cat_id,Max(shop_price) as max_price      from ecshop.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;

在多行子查询中使用all操作符

请思考:如何显示工资比部门30的所有员工的工资高的员工的姓名工资和部门号

-- 使用max()select ename,sal,deptno from emp where sal >(select max(sal) from emp where deptno=30);-- 使用all()select ename,sal,deptno from emp where sal >all(select sal from emp where deptno=30);

在多行子查询中使用any操作符

请思考:如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号

-- 使用min()select ename,sal,deptno from emp where sal >(select min(sal) from emp where deptno=30);-- 使用any()select ename,sal,deptno from emp where sal >any(select sal from emp where deptno=30);

多列子查询

多列子查序则是指查询返回多个列数据的子查询语句

(字段1,字段2…)=(select 字段1,字段2 from …)

练习题：

1. 请查询student表中和宋江数学，英语，语文完全相同的学生

2. 请思考如何查询emp表中与allen的部门和岗位完全相同的所有雇员(并且不含smith本人)

   -- 1select math,english,chinese from student where name='宋江';select * from student where (math,english,chinese)=(select math,english,chinese from student where name='宋江') and name!='宋江';-- 2select deptno,job from emp where LCASE(ename)='allen';SELECT * FROM emp WHERE (deptno,job)=(select deptno,job from emp where LCASE(ename)='allen') and LCASE(ename)!='allen';

课后练习(先自己做)

1. emp表中，查找每个部门工资高于本部门平均工资的人的资料
2. emp表中，查找每个部门工资最高的人的详细资料
3. 查询每个部部门的信息(包括:部门名,编号,地址(dept表中))和人员数量,

-- 1select deptno,avg(sal) as avg_sal from emp group by deptno;select ename,sal,avg_sal,temp.deptno from emp,(select deptno,avg(sal) as avg_sal from emp group by deptno) temp where emp.deptno=temp.deptno and emp.sal>avg_sal;-- 2select deptno,max(sal) as max_sal from emp group by deptno;select empno,ename,job,sal,emp.deptno from emp,(select deptno,max(sal) as max_sal from emp group by deptno) temp where emp.deptno=temp.deptno and sal=max_sal;-- 3-- 表名.* 表示将该表所有列都显示出来, 可以简化 sql 语句select deptno,count(*) as count_dept from emp group by deptno;select dept.*,count_dept from dept,(select deptno,count(*) as count_dept from emp group by deptno) temp where dept.deptno=temp.deptno;

合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号

1. union all

   该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。（注意：可以理解为把第二个表的内容接在第一个表的下面。不需要数据类型一致，但列数必须相同）

   select ename,sal,job from emp where sal>2500 union select ename,job,sal from emp where job='MANAGER';

2. union

   该操作赋与union all相似，但是会自动去掉结果集中重复行

   select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='manager';

mysql表内连接

select ... from 表1 [inner] join 表2 on 条件 -- inner加不加都一样

获取两个表中字段匹配关系的记录。

个人认为内连接用处不大，它能处理的问题用之前的自连接知识也能处理。

mysql表外连接

提出一个问题

1. 前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示

2. 比如:列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。

3. 使用我们学习过的多表查询的SQL，看看效果如何?

   SELECT dname, ename, jobFROM emp, deptWHERE emp.deptno = dept.deptno;-- 因为dept.deptno为40时，其中没有员工。“emp.deptno = dept.deptno”就会因为emp表中没有deptno为40的员工，导致 不会显示没有员工的部门。

外连接

1. 左外连接

   如果左侧的表完全显示我们就说是左外连接

   select ... from 表1 left join 表2 on 条件 [表1就是左表 表2就是右表]

   

2. 右外连接

   如果右侧的表完全显示我们就说是右外连接

   select ... from 表1 right join 表2 on 条件 [表1:就是左表 表2:就是右表]

   

先创建表：

-- 创建 stu/*id name1 Jack2 Tom3 Kity4 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 grade1 562 7611 8*/CREATE TABLE exam( id INT, grade INT);INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);SELECT * FROM exam;

使用

1. 使用左连接—(显示所有人的成绩,如果没有成绩，也要显示该人的姓名和id号,成绩显示为空)

2. 使用右外连接—(显示所有成绩，如果没有名字匹配，显示空)

   -- 1-- 使用左连接-- （显示所有人的成绩，如果没有成绩，也要显示该人的姓名和 id 号,成绩显示为空）SELECT `name`, stu.id, gradeFROM stu, examWHERE stu.id = exam.id; -- 改成左外连接SELECT `name`, stu.id, grade FROM stu LEFT JOIN exam ON stu.id = exam.id;-- 2-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空)-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来SELECT `name`, stu.id, grade FROM stu RIGHT JOIN exam ON stu.id = exam.id;-- 自己练习：-- 列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门名。5min-- 1.使用左外连接实现-- 2.使用右外连接实现select dname,ename,job from dept left join emp on emp.deptno=dept.deptno;select dname,ename,job from emp right join dept on emp.deptno=dept.deptno;

表复制和表去重

表复制

-- 表的复制-- 为了对某个 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_tab01INSERT 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_tab02DESC 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_tmpinsert into my_tmp select distinct * from my_tab02;-- (3) 清除掉 my_tab02 记录delete from my_tab02; -- (4) 把 my_tmp 表的记录复制到 my_tab02insert into my_tab02 select * from my_tmp; -- (5) drop 掉 临时表 my_tmpdrop table my_tmp;select * from my_tab02;

约束

约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括: not null,unique,primary key,foreign key,和check五种.

primary key(主键)

primary key(主键)-基本使用

字段名 字段类型 primary key

用于唯一的标示表行的数据,当定义主键约束后，该列不能重复

-- 主键使用-- id name emailCREATE 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 t18

primary key(主键)-细节说明

1. primary key不能重复而且不能为null。

2. 一张表最多只能有一个主键,但可以是复合主键

3. 主键的指定方式有两种

   直接在字段名后指定:字段名primary key

   在表定义最后写primary key(列名);

4. 使用desc表名，可以看到primary key的情况

5. 提醒:在实际开发中，每个表往往都有主键!

not nulI(非空)

如果在列上定义了not null,那么当插入数据时，必须为列提供数据。

字段名 字段类型 not null

unique(唯一)

当定义了唯一约束后，该列值是不能重复的.。

字段名 字段类型 unique

unique细节(注意):

1. 如果没有指定not null，则unique字段可以有多个null
2. 一张表可以有多个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 keyINSERT 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 t22;

foreign key(外键)

foreign key (本表字段名) references 主表名(主键名或unique字段名)

用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null(学生/班级图示)

-- 外键演示-- 创建 主表 my_classCREATE TABLE my_class (id INT PRIMARY KEY , -- 班级编号`name` VARCHAR(32) NOT NULL DEFAULT ''); -- 创建 从表 my_stuCREATE 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 nullSELECT * FROM my_class; DELETE FROM my_class WHERE id = 100;-- 一旦建立主外键的关系，数据不能随意删除了

foreign key(外键)—细节说明

1. 外键指向的表的字段，要求是primary key或者是unique
2. 表的类型是innodb,这样的表才支持外键
3. 外键字段的类型要和主键字段的类型一致(长度可以不同)
4. 外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]
5. 一旦建立主外键的关系，数据不能随意删除了.

check【了解就行】

列名 类型 check (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;-- check没有生效

商店售货系统表设计案例【先自己练,再看对答案】

现有一个商店的数据库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);

建表,在定义中要求声明[进行合理设计]:

1. 每个表的主外键;

2. 客户的姓名不能为空值;

3. 电邮不能够重复;

4. 客户的性别[男|女] check 枚举..

5. 单价unitprice在1.0 – 9999.99之间 check

   create table goods(goods_id int primary key,goods_name varchar(50),unitprice double check(unitprice>=1.0 and unitprice<=9999.99),category varchar(20),provider varchar(20));create table customer(customer_id int primary key,name varchar(10)  not null,address varchar(30),email varchar(30) unique,sex enum('男','女'),card_ld char(18));create table purchase(order_id int primary key,customer_id int,goods_id int,nums int,foreign key (customer_id) references customer(customer_id),foreign key (goods_id) references goods(goods_id));

自增长

自增长基本介绍一个问题

在某张表中，存在一个id列(整数),我们希望在添加记录的时候,该列从1开始，自动的增长，怎么处理?

字段名 整数类型 primary key auto_increment

添加自增长的字段的方式

insert into xxx(字段1,字段2...) values(null,'值'...);insert into xxx(字段2...) values('值1','值2'...);insert into xxx values(null,'值1'...);

自增长使用细节

1. 一般来说自增长是和primary key配合使用的
2. 自增长也可以单独使用[但是需要配合一个unique]
3. 自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
4. 自增长默认从1开始,你也可以通过如下命令修改：alter table 表名 auto_increment = 新的开始值;
5. 建议：如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准,如果指定了自增长，一般来说，就按照自增长的规则来添加数据。

-- 演示自增长的使用-- 创建表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 (email, `name`) VALUES('hsp@sohu.com', 'hsp');INSERT INTO t24  VALUES(8,'hsp@sohu.com', 'hsp');-- 可以在自增长的位置插入该列不存在的int值，但不推荐，实际情况不会这样用，会出现一系列问题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, 'hsp@qq.com', 'hsp');SELECT * FROM t25;

索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序,不用调sql，查询速度就可能提高百倍干倍。

这里我们举例说明索引的好处【构建海量表8000000】(快速体验案例index.sql)

先执行以下语句创建数据库tmp (注意有八百万条数据，大概需要花费二十分钟时间，占用600M的空间)

-- 创建测试数据库 tmpCREATE DATABASE tmp;

先切换到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)begindeclare 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))begindeclare 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 -- 我的电脑的查询时间为6.329s-- 使用索引来优化一下， 体验索引的牛-- 在没有创建索引前 , emp.ibd 文件大小 是 524m-- 创建索引后 emp.ibd 文件大小 是 655m [索引本身也会占用空间.]-- 创建 ename 列索引,emp.ibd 文件大小 是 827m-- empno_index 索引名称-- ON emp (empno) : 表示在 emp 表的 empno 列创建索引CREATE INDEX empno_index ON emp (empno);-- 耗时22.726s-- 创建索引后， 查询的速度如何SELECT * FROM emp WHERE empno = 1234578; -- 0.023s 原来是 6.329s-- 创建索引后，只对创建了索引的列有效SELECT * FROM emp WHERE ename = 'PjDlwy'; -- 没有在 ename 创建索引时，时间 7.948sCREATE INDEX ename_index ON emp (ename); -- 在 ename 上创建索引SELECT * FROM emp WHERE ename = 'PjDlwy';-- 0.029S

索引的原理

没有索引为什么会慢? 因为全表扫描.
使用索引为什么会快? 形成一个索引的数据结构，比如二叉树（例：进行n次比较最多能够覆盖范围2ⁿ-1个）

索引的代价

1. 磁盘占用
2. 对表内容的 增、删、改 语句的效率影响（会对索引进行修改维护，对速度有影响。）

告诉你，在我们实际项目中,select[90%]多还是update,delete,insert[10%]操作多?

索引的类型

1. 主键索引，主键自动的为主索引(类型Primary key)

2. 唯一索引(UNIQUE)

3. 普通索引(INDEX)

4. 全文索引 (FULLTEXT) [适用于MylSAM]

   一般开发,不使用mysql自带的全文索引（因为比较拉胯），而是使用：全文搜索Solr和 ElasticSearch (ES)

索引使用

注意：约束中的primary key(主键)和unique(唯一)就是主键索引和唯一索引。创建表时如果设置了主键或unique，也就是创建了此处的主键索引或唯一索引

1. 添加索引（唯一索引/普通索引）

   create [UNIQUE] index index_name on tbl_name (col_name [(length)][ASC|DESC],...);alter table table_name ADD [UNIQUE] INDEX [index_name] (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;-- 加不加es结果都一样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); -- 添加普通索引方式1CREATE INDEX id_index ON t25 (id); -- 如何选择-- 1. 如果某列的值，是不会重复的，则优先考虑使用 unique 索引, 否则使用普通索引-- 添加普通索引方式2ALTER 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 -- 能在显示结果的Key列看到索引，但不能看到详细信息

练习题：

1. 创建一张订单表order (id号，商品名,订购人，数量).要求id号为主键，请使用2种方式来创建主键.(提示:为练习方便，可以是order1 , order2)

2. 创建一张特价菜谱表menu (id号，菜谱名，厨师，点餐人身份证，价格).要求id号为主键，点餐人身份证是unique请使用两种方式来创建unique.(提示:为练习方便，可以是menu1 , menu2)

3. 创建一张运动员表sportman (id号，名字，特长).要求id号为主键，
   名字为普通索引，请使用两种方式来创建索引(提示:为练习方便，可以是不同表名sportman1 , sportman2)

   -- 1CREATE TABLE `order1`(id int primary key,name varchar(50),cou int);CREATE TABLE `order2`(id int,name varchar(50),cou int);alter table order2 add primary key(id);-- 2create  table menu1(id int primary key,menu_name varchar(30),cook varchar(30),user varchar(30) unique,price double);SHOW INDEXES FROM menu1;create  table menu2(id int primary key,menu_name varchar(30),cook varchar(30),user varchar(30),price double);alter table menu2 add unique index uni(user);SHOW INDEX FROM menu2;create  table menu3(id int primary key,menu_name varchar(30),cook varchar(30),user varchar(30),price double);create unique index aa on menu3(user);SHOW Keys FROM menu2;-- 3create table sportman1(id int primary key, name varchar(20),specialty varchar(20));create index iname on sportman1(name);create table sportman2(id int primary key, name varchar(20),specialty varchar(20));alter table sportman2 add index iname(name);

哪些列上适合使用索引

1. 较频繁的作为查询条件字段应该创建索引

   select* from emp where empno =1

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

   select * from emp where sex =’男‘（性别列只有两种内容）

3. 更新非常频繁的字段不适合创建索引

   select * from emp where logincount =1

4. 不会出现在WHERE子句中字段不该创建索引

事务

什么是事务

事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。

事务和锁

当执行事务操作时(dml语句),mysql会在表上加锁,防止其它用户改表的数据.这对用户来讲是非常重要的。

mysql数据库控制台事务的几个重要操作

1. start transaction — 开始一个事务
2. savepoint 保存点名 –设置保存点
3. rollback to 保存点名 –回退事务
4. rollback –回退全部事务
5. commit — 提交事务,所有的操作生效,不能回退

-- 事务的一个重要的概念和具体操作-- 看一个图[看示意图]-- 演示-- 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');-- 回退到 bROLLBACK TO b-- 继续回退 aROLLBACK TO a-- 如果这样, 表示直接回退到事务开始的状态. ROLLBACKCOMMIT

回退事务

在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点.当执行回退事务时，通过指定保存点可以回退到指定的点。回退后会把中间的所有点删掉。

提交事务

使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据【所有数据正式生效】

事务细节讨论

1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2. 如果开始一个事务，你没有创建保存点.你可以执行rollback,默认就是回退到你事务开始的状态.
3. 你也可以在这个事务中(还没有提交时)，创建多个保存点.比如: savepoint aaa;执行dml , savepoint bbb;
4. 你可以在事务没有提交前，选择回退到哪个保存点.
5. mysql的事务机制需要innodb的存储引擎还可以使用,myisam不好使.
6. 开始一个事务start transaction, set autocommit=off;

-- 1. 如果不开始事务，默认情况下，dml 操作是自动提交的，不能回滚INSERT INTO t27 VALUES(300, 'milan'); -- 自动提交 commitSELECT * FROM t27-- 2. 如果开始一个事务，你没有创建保存点. 你可以执行 rollback，-- 默认就是回退到你事务开始的状态START TRANSACTIONINSERT 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事务隔离级别

事务隔离级别介绍

1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。(通俗解释)
2. 如果不考虑隔离性，可能会引发如下问题:
   1. 脏读(dirty read)：当一个事务读取另一个事务尚未提交的改变（增、删、改）时，产生脏读
   2. 不可重复读(nonrepeatable read)：同一查询在同一事务中多次进行，由于其·他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
   3. 幻读(phantom read)：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作,每次返回不同的结果集，此时发生幻读。

事务隔离级别

概念:Mysql隔离级别定义了事务与事务之间的隔离程度。

设置事务隔离级别

1. 查看当前会话隔离级别

   select @@tx_isolation;

2. 查看系统当前隔离级别

   select @@global.tx_isolation;

3. 设置当前会话事务隔离级别(一般在事务未启动时设置，否则需要在提交后才生效)

   set session transaction isolation level repeatable read; -- repeatable read根据情况换成适合的隔离级别

4. 设置系统当前隔离级别

   set global transaction isolation level repeatable read;-- repeatable read根据情况换成适合的隔离级别

5. mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目需求)

隔离级别全局修改，修改mysql.ini配置文件，在最后加上。加上后需要重启mysql服务（不用背,面试官一般不会问）

#可选参数有:READ-UNCOMMITTED,READ-COMMITTED, REPEATABLE-READ,SERIALIZABLE.[mysqld]transaction-isolation = REPEATABLE-READ

事务的 acid 特性

1. 原子性(Atomicity)

   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性(Consistency)

   事务必须使数据库从一个一致性状态变换到另外一个一致性状态

3. 隔离性(lsolation)

   事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

4. 持久性(Durability)

   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有何影响

事务的课堂练习[一定要自己去练习，体会]

1. 登录mysql控制客户端A,创建表dog (id, name)，添加两条记录,开始一个事务;

2. 登录mysql控制客户端B，设置为读未提交,开始一个事务.

3. A客户端修改Dog 一条记录,不要提交。看看B客户端是否看到变化，说明什么问题?

4. 登录mysql客户端C，设置为读已提交，开始一个事务，这时A客户修改一条记录，不要提交，看看C客户端是否看到变化,A提交后，看C客户端是否有变化。说明什么问题?

   -- 1-- Ause db01;CREATE TABLE dog(id int,name varchar(20));INSERT INTO dog values (1,'aa'),(2,'bb');start transaction;-- 2-- Buse db01;set session transaction isolation level read uncommitted;start transaction;-- 3-- AUPDATE dog set name='cc' where id=2;-- BSELECT * FROM dog; -- 可以看到A修改后未提交的内容-- 4-- Cuse db01;set session transaction isolation level read committed;start transaction;-- AUPDATE dog set name='ddd' where id=1;-- Cselect * from dog; -- 不能看到A修改后未提交的内容-- Acommit;-- Cselect * from dog; -- 能看到A修改后提交的内容

mysql表类型和存储引擎

基本介绍

1. MySQL的表类型由存储引擎(Storage Engines)决定,主要包括MyISAM、innoDB、Memory等。

2. MySQL 数据表主要支持六种类型,分别是:CSV、Memory、ARCHIVE、MRG MYISAM、MYISAM、InnoBDB。

3. 这六种又分为两类,一类是”事务安全型”(transaction-safe), 比如:InnoDB;其余都属于第二类，称为”非事务安全型”(non-transaction-safe)[mysiam和memory].

-- 展示引擎类型show engines;

以下为引擎查询结果

主要的存储引擎/表类型特点

细节说明

我这里重点给大家介绍三种: MylSAM、InnoDB、MEMORY

1. MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
3. 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 t29INSERT INTO t29 VALUES(1,'tom'), (2,'jack'), (3, 'hsp');SELECT * FROM t29;-- 自己重启mysql服务再看看数据和表结构是否还在-- 指令修改存储引擎ALTER TABLE `t29` ENGINE = INNODB

如何选择表的存储引擎

1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MylSAM是不二选择,速度快

2. 如果需要支持事务，选择lnnoDB。

3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘I/O的等待,速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态().)

修改存储引擎

ALTER TABLE 表名 ENGINE=储存引擎;

视图（view）

看一个需求

emp表的列信息很多，有些信息是个人重要信息(比如sal, comm,mgr, hiredate)，如果我们希望某个用户只能查询emp表的(empno,ename, job和deptno )信息，有什么办法?=》视图

基本概念

1. 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列,其数据来自对应的真实表(基表)

2. 视图和基表关系的示意图

   

视图的基本使用

1. 创建视图

   create view 视图名 as select语句

2. 更新成新的视图

   alter view 视图名 as select语句

3. 查看创建视图的命令

   SHOW CREATE VIEW 视图名

4. 删除视图

   drop view 视图名1,视图名2

完成前面提出的需求

创建一个视图emp_view01，只能查询emp表的(empno、ename, job和deptno)信息

-- 创建视图CREATE VIEW emp_wiew01 AS SELECT empno,ename,job,deptno from emp;-- 查看视图desc emp_wiew01;select * from emp_wiew01;SELECT empno, job FROM emp_view01; -- 查看创建视图的指令SHOW CREATE VIEW emp_view01-- 删除视图DROP VIEW emp_view01;

视图细节讨论

1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式:视图名.frm)
2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
3. 视图中可以再使用视图

-- 视图的细节-- 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_view01CREATE VIEW emp_view02 AS SELECT empno, ename FROM emp_view01;SELECT * FROM emp_view02;

视图最佳实践

1. 安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时,数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3. 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

MySQL管理

Mysql 用户

mysql中的用户，都存储在系统数据库mysql中user表中

其中user表的重要字段说明:

1. host：允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如:192.168.1.100
2. user：用户名;
3. authentication string：密码，是通过mysql的password()函数加密之后的密码。

创建用户

create user '用户名'@'允许登录位置' identified by '密码';

说明：创建用户，同时指定密码

删除用户

drop user '用户名'@'允许登录位置';

用户修改密码

修改自己的密码:

set password = password('密码');

修改他人的密码(需要有修改用户密码权限):

set password for '用户名'@'登录位置' = password('密码');

mysql 中的权限

给用户授权

基本语法:

grant 权限列表 on 库.对象名 to '用户名'@'登录位置' [identified by '密码']

说明:

1. 权限列表，多个权限用逗号分开

   grant select on ......grant select,delete,create on ......grant all [privileges] on ...... //表示赋予该用户在该对象上的所有权限

2. 特别说明

   *.*：代表本系统中的所有数据库的所有对象(表，视图，存储过程)

   库.*：表示某个数据库中的所有数据对象(表，视图，存储过程等)

3. identified by可以省略，也可以写出.

   (1)如果用户存在，就是修改该用户的密码。

   (2)如果该用户不存在，就是创建该用户!

回收用户授权

revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

权限生效指令

该指令针对5.7以下版本，5.7以及之后的版本给了权限马上就会生效。

FLUSH PRIVILEGES;

课堂练习：

用户管理练习题

1. 创建一个用户(你的名字:拼音)，密码123，并且只可以从本地登录，不让远程登录mysql

2. 创建库和表testdb下的news表，要求:使用root用户创建

3. 给用户分配查看news表和添加数据的权限

4. 测试看看用户是否只有这几个权限

5. 修改密码为abc ,要求:使用root用户完成

6. 重新登录

7. 回收用户的news表的所有权限

8. 使用root 用户删除你的用户

   -- 1create user 'zwj'@'localhost' identified by '123';-- 2create database testdb;use testdb;create table news(id int ,content varchar(32));-- 3grant select,insert on testdb.news to 'zwj'@'localhost';-- 4登录zwj用户SELECT * FROM news;INSERT INTO news VALUES(200,'上海新闻');-- 5set password FOR 'zwj'@'localhost'=password('abc');-- 7REVOKE select,insert on testdb.news from 'zwj'@'localhost';-- 8drop user 'zwj'@'localhost';

●细节说明

1. 在创建用户的时候，如果不指定Host,则为%，%表示表示所有IP都有连接权限
   create user xxx;

2. 你也可以这样指定

   create user ‘xxx’@’192.168.1.%’表示 xxx用户在192.168.1.*的ip可以登录mysql

3. 在删除用户的时候,如果host不是%需要明确指定‘用户’@’host值’