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安装文件是不一样的, 要严格的下面的步骤来执行，否则安装很可能不会成功。

• 下载后会得到zip 安装文件
  
• 解压的路径最好不要有中文和空格
  
• 这里我解压到 D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64 目录下 【可自行指定目录,目录不能有中文，尽量不安装在系统盘】
  
• 添加环境变量 : 电脑-属性-高级系统设置-环境变量，在Path 环境变量增加mysql的安装目录\bin目录。如下图所示
  
  
• 在D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64目录下下创建 my.ini 文件, 需要我们自己创建（其他非5.7版本会有差异）。用记事本打开该文件，写入以下内容并保存（注意要根据自己的安装位置更改文本中的目录）。
  1. [client]
  2. port=3306
  3. default-character-set=utf8
  4. [mysqld]
  5. # 设置为自己MYSQL的安装目录
  6. basedir=D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64\
  7. # 设置为MYSQL的数据目录
  8. datadir=D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64\data\
  9. port=3306
  10. character_set_server=utf8
  11. #跳过安全检查（登录不需要密码）
  12. skip-grant-tables

  复制代码
• 使用管理员身份打开 cmd , 并切换到 D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64\bin 目录下, 执行mysqld -install
  1. d:
  2. cd D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64
  3. mysqld -install

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

• 初始化数据库:  mysqld --initialize-insecure --user=mysql
  如果执行成功，会生成 data目录:
  
• 启动mysql 服务: net start mysql 【停止mysql服务指令 net stop mysql】, 如果成功:
  
  任务管理器中也会出现MySQL服务
  
  
• 进入mysql 管理终端: mysql -u root -p 【当前root 用户密码为 空，下一行要输入密码时直接回车】
  
• 修改root 用户密码
  1. use mysql;  
  2. update user set authentication_string=password('123456') where user='root' and Host='localhost';
  4. 解读: 上面的语句就是修改 root用户的密码为 123456
  5. 注意：在后面需要带 分号，回车即可执行该指令
  6. 执行: flush privileges; 刷新权限
  7. 退出: quit

  复制代码
• 修改my.ini , 再次进入就会进行权限验证了
  1. #跳过安全检查（登录不需要密码）
  2. #skip-grant-tables  

  复制代码
• 重新启动mysql
  1. net stop mysql
  2. net start mysql
  4. 提示: 该指令需要退出mysql ， 在Dos下执行.

  复制代码
• 再次进入Mysql, 输入正确的用户名和密码
  1. mysql -u root -p
  3. 密码正确，进入mysql
  5. 密码错误，提示如下信息
  6. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

  复制代码

安装过程中，一定要按照老师的步骤来，不然会错误.
如果真的错误了， 清除mysql服务, 再次安装.
连接到Mysql服务(Mysql数据库)的指令

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

复制代码

提醒:

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

安装Navicat


http://www.navicat.com.cn/download/navicat-for-mysql
安装过程很简单，此处省略
如果想破解，百度，教程很多
安装后，打开，点击左侧的“连接”，进行如下配置。

数据库

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

• 所谓安装Mysql数据库，就是在主机安装一个数据库管理系统(DBMS),这个管理程序可以管理多个数据库。DBMS(database manage system)
  
• 一个数据库中可以创建多个表,以保存数据(信息)。
  
• 数据库管理系统(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

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

复制代码

• CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认utf&,
  
• COLLATE:指定数据库字符集的校对规则(常用的utf8 bin[区分大小写]、utf8 general ci[不区分大小写（例：A==a）]注意默认是utf8_general_ci)
  1. # 演示数据库的操作
  2. #创建一个名称为 hsp_db01 的数据库。[图形化和指令 演示]
  3. #使用指令创建数据库
  4. CREATE DATABASE hsp_db01;
  5. #删除数据库指令
  6. DROP DATABASE hsp_db01
  7. #创建一个使用 utf8 字符集的 hsp_db02 数据库
  8. CREATE DATABASE hsp_db02 CHARACTER SET utf8
  9. #创建一个使用 utf8 字符集，并带校对规则的 hsp_db03 数据库
  10. CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
  11. #校对规则 utf8_bin 区分大小 默认 utf8_general_ci 不区分大小写
  13. #下面是一条查询的 sql , select 查询 * 表示所有字段 FROM 从哪个表
  14. #WHERE 从哪个字段 NAME = 'tom' 查询名字是 tom
  15. SELECT * FROM t1 WHERE NAME = 'tom'

  复制代码

查看、删除数据库

1. 显示数据库语句:
2. SHOW DATABASES
4. 显示数据库创建语句:
5. SHOW CREATE DATABASE db_name
7. 数据库删除语句[一定要慎用]:
8. DROP DATABASE [IF EXISTS] db_name

复制代码

1. #演示删除和查询数据库
2. #查看当前数据库服务器中的所有数据库
3. SHOW DATABASES
4. #查看前面创建的 hsp_db01 数据库的定义信息
5. SHOW CREATE DATABASE `hsp_db01`
6. #老师说明 在创建数据库,表的时候，为了规避关键字，可以使用反引号解决
7. CREATE DATABASE `INT`
8. #删除前面创建的 hsp_db01 数据库
9. DROP DATABASE hsp_db01

复制代码

备份恢复数据库

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

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

复制代码

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

1. Source 文件名.sql

复制代码

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

复制代码

备份恢复数据库的表

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

复制代码

表

创建

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

复制代码

删除

1. drop table 表名;

复制代码

修改

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

1. #添加列
2. ALTER TABLE 表名
3. ADD 列名 datatype [DEFAULT expr];
5. #修改列类型
6. ALTER TABLE 表名
7. MODIFY 列名 datatype [DEFAULT expr];
9. #修改列类型及名称
10. ALTER TABLE 表名
11. CHANGE 列名 新列名 datatype;
13. #删除列
14. ALTER TABLE 表名;
15. DROP 列名;
16. 查看表的结构: desc 表名; --可以查看表的列
18. #修改表名:
19. rename table 表名 to 新表名;
21. #修改表字符集:
22. alter table 表名 character set 字符集;

复制代码

应用实例：

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

1. ALTER TABLE emp
2. ADD image varchar(32) NOT NULL DEFAULT '' AFTER resume;
4. ALTER TABLE emp
5. MODIFY job varchar(60);
7. ALTER TABLE emp
8. DROP sex;
9. DESC emp;
11. rename table emp to employee;
13. ALTER TABLE employee character set utf8;
15. ALTER TABLE employee CHANGE `name` `user_name` varchar(20);

复制代码

Mysql数据类型


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

说明：在能够满足需求的情况下，尽量选择占用空间小的类型（节省资源）
类型字节最小值最大值(带符号的/无符号的)(带符号的/无符号的)TINYINT1-128127[unsigned]0255SMALLINT2-3276832767065535MEDIUMINT3-83886088388607016777215INT4-2147483648214748364704294967295BIGINT8-92233720368547758089223372036854775807018446744073709551615

1. #演示整型的是一个
2. #使用 tinyint 来演示范围 有符号 -128 ~ 127 如果没有符号 0-255
3. #说明： 表的字符集，校验规则, 存储引擎，老师使用默认
4. #1. 如果没有指定 unsinged , 则 TINYINT 就是有符号
5. #2. 如果指定 unsinged , 则 TINYINT 就是无符号 0-255
6. CREATE TABLE t3 (id TINYINT);
7. CREATE TABLE t4 (id TINYINT UNSIGNED);
9. INSERT INTO t3 VALUES(127); #这是非常简单的添加语句
10. SELECT * FROM t3;
12. INSERT INTO t4 VALUES(255);
13. SELECT * FROM t4;

复制代码

数值型(bit)的使用

• 基本使用
  1. create table t02 (num bit(8));
  2. insert into t02 (1,3);
  3. insert into t02 values(2,65);

  复制代码
• 细节说明
  bit字段显示时，按照位的方式显示.查询的时候仍然可以用使用添加的数值
  如果一个值只有0,1可以考虑使用bit(1),可以节约空间位类型。M指定位数,默认值1，范围1-64
  使用不多.
  1. #演示 bit 类型使用
  2. #说明
  3. #1. bit(m) m 在 1-64
  4. #2. 添加数据 范围 按照你给的位数来确定，比如 m = 8 表示一个字节 0~255
  5. #3. 显示按照 bit
  6. #4. 查询时，仍然可以按照数来查询
  7. CREATE TABLE t05 (num BIT(8));
  8. INSERT INTO t05 VALUES(255);
  9. SELECT * FROM t05;
  10. SELECT * FROM t05 WHERE num = 1;

  复制代码

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

• FLOAT/DOUBLE [UNSIGNED]
  

Float单精度精度，Double 双精度

• DECIMAL[M,D] [UNSIGNED]
  可以支持更加精确的小数位。M是位数(整数位数+小数位数)的总数，D是小数点(标度)后面的位数。
  如果D是0，则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略，默认是0。如果M被省略，默认是10。
  建议:如果希望小数的精度高，推荐使用decimal
  

1. #演示 decimal 类型、float、double 使用
3. #创建表
4. CREATE TABLE t06 (
5.     num1 FLOAT,
6.     num2 DOUBLE,
7.     num3 DECIMAL(30,20));
8. #添加数据
9. INSERT INTO t06 VALUES(88.12345678912345, 88.12345678912345,88.12345678912345);
10. SELECT * FROM t06;
12. #decimal 可以存放很大的数
13. CREATE TABLE t07 (
14. num DECIMAL(65));
15. INSERT INTO t07 VALUES(8999999933338388388383838838383009338388383838383838383);

复制代码

字符串的基本使用

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

字符串使用细节

• 细节1
  char(4)//这个4表示字符数(最大255)，不是字节数,不管是中文还是字母都是放四个,按字符计算.
  varchar(4)//这个4表示字符数,不管是字母还是中文都以定义好的表的编码来存放数据.
  不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的.
  
• 细节2
  char(4)是定长(固定的大小)，就是说，即使你插入'aa'，也会占用分配的4个字符的空间.
  varchar(4)是变长，就是说，如果你插入了'aa',实际占用空间大小并不是4个字符，而是按照实际占用空间来分配(说明:varchar本身还需要多占用1-3个字节)
  
• 细节3
  什么时候使用char,什么时候使用varchar
  
  
  
  • 如果数据是定长,推荐使用char，比如md5的密码,邮编,手机号，身份证号码等. char(32)
    
  • 如果一个字段的长度是不确定，我们使用varchar，比如留言,文章
    
  查询速度:char > varchar
  
  
• 细节4
  在存放文本时，也可以使用Text 数据类型。可以将TEXT列视为VARCHAR列,注意Text不能有默认值，大小0~216字节
  如果希望存放更多字符，可以选择 MEDIUMTEXT (0~224字节) 或者LONGTEXT (0~232字节)
  

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

复制代码

日期类型的基本使用

1. CREATE TABLE birthday( t1 DATE, t2 DATETIME,
2. t3 TIMESTAMP NOT NULL DEFAULTCURRENT TIMESTAMP ON UPDATE
3. CURRENT TIMESTAMP );
5. mysql> INSERT INTO birthday(t1,t2)
6. VALUES('2022-11-11','2022-11-11 10:10:10');
8. #timestamp时间戳

复制代码

日期类型的细节说明
TimeStamp在Insert和update时，自动更新

1. #演示时间相关的类型
2. #创建一张表, date , datetime , timestamp
3. CREATE TABLE t14 (
4.     birthday DATE , -- 生日
5.     job_time DATETIME, -- 记录年月日 时分秒
6.     login_time TIMESTAMP
7.         NOT NULL DEFAULT CURRENT_TIMESTAMP
8.         ON UPDATE CURRENT_TIMESTAMP); -- 登录时间, 如果希望 login_time 列自动更新, 需要配置
9. SELECT * FROM t14;
10. INSERT INTO t14(birthday, job_time)
11. VALUES('2022-11-11','2022-11-11 10:10:10');
12. -- 如果我们更新 t14 表的某条记录，login_time 列会自动的以当前时间进行更新

复制代码

练习题

1. -- 自己一定要练习一把
2. CREATE TABLE `emp` (id INT,
3.                     `name` VARCHAR(32),
4.                     sex CHAR(1),
5.                     brithday DATE,
6.                     entry_date DATETIME,
7.                     job VARCHAR(32),
8.                     salary DOUBLE,
9.                     `resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
10. -- 添加一条
11. INSERT INTO `emp`
12.         VALUES(100, '小妖怪', '男', '2000-11-11',
13.                 '2010-11-10 11:11:11', '巡山的', 3000, '大王叫我来巡山');
14. SELECT * FROM `emp`;

复制代码

CRUD

C[creat]R[read]U[update]D[delete]
lnsert(添加数据)

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

复制代码

快速入门案例:

• 创建一张商品表goods(id int , goods_name varchar(10),price double);
  
• 添加2条记录
  1. create table goods(id int,goods_name varchar(10),price double);
  3. INSERT INTO goods
  4. VALUES (10,'可乐',4.5);
  6. INSERT INTO goods (id,goods_name,price)
  7. VALUES (11,'可乐2',4.6);

  复制代码

细节说明

• 插入的数据应与字段的数据类型相同。
  比如把'abc'添加到int类型会错误
  
• 数据的长度应在列的规定范围内，例如:不能将一个长度为80的字符串加入到长度为40的列中。
  
• 在values中列出的数据位置必须与被加入的列的排列位置相对应。
  
• 字符和日期型数据应包含在单引号中。
  
• 列可以插入空值[前提是该字段允许为空]，insert into table value(null)
  
• insert into 表名 (列名...) values (),(),()形式添加多条记录
  1. INSERT INTO `goods` (id,goods_name,price)
  2. VALUES(50,'三星手机',2300),(60,'海尔手机',1800);

  复制代码
• 如果是给表中的所有字段添加数据,可以不写前面的字段名称
  1. INSERT INTO goods
  2. VALUES (10,'可乐',4.5);

  复制代码
• 默认值的使用，当不给某个字段值时，如果有默认值就会添加,否则报错
  

Update（更新数据）

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

复制代码

基本使用：
要求:在上面创建的employee表中修改表中的纪录

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

  复制代码

使用细节:

• UPDATE语法可以用新值更新原有表行中的各列。
  
• SET子句指示要修改哪些列和要给予哪些值。
  
• WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。
  
• 如果需要修改多个字段，可以通过 set 字段1=值1,字段2=值2…….
  1. UPDATE employee
  2. SET salary =salary +1000,
  3.         job ='出主意的' WHERE user_name ='老妖怪';

  复制代码

Delete（删除数据）

1. delete from 表名
2. [WHERE where_definition]

复制代码

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

• 删除表中名称为'老妖怪'的记录
  
• 删除表中所有记录。
  1. -- 1
  2. DELETE FROM `employee` WHERE user_name='老妖怪';
  3. -- 2
  4. DELETE FROM `employee`;

  复制代码

使用细节

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

Select（查找数据-单表）

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

复制代码

注意事项

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

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

1. create table student(
2.         id int not null default 1,
3.         name varchar(20) not null default '',
4.         chinese float not null default 0.0,
5.         english float not null default 0.0,
6.         math float not null default 0.0
7. );
9. insert into student(id,name,chinese,english,math) values(1,'韩顺平',89,78,90);
10. insert into student(id,name,chinese,english,math) values(2,'张飞',67,98,56);
11. insert into student(id,name,chinese,english,math) values(3,'宋江',87,78,77);
12. insert into student(id,name,chinese,english,math) values(4,'关羽',88,98,90);
13. insert into student(id,name,chinese,english,math) values(5,'赵云',82,84,67);
14. insert into student(id,name,chinese,english,math) values(6,'欧阳锋',55,85,45);
15. insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);
16. insert into student(id,name,chinese,english,math) values(8,'李明',80,65,30);

复制代码

• 查询表中所有学生的信息。
  
• 查询表中所有学生的姓名和对应的英语成绩。
  
• 过滤表中重复数据distinct 。
  
• 要查询的记录，每个字段都相同，才会去重
  1. -- 1
  2. SELECT * FROM `student`;
  3. -- 2
  4. SELECT `name`,`english` FROM student;
  5. -- 3
  6. SELECT DISTINCT * FROM `student`;
  7. -- 4
  8. SELECT DISTINCT * FROM `student`;

  复制代码

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

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

复制代码

在select语句中可使用as语句

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

复制代码

练习

• 统计每个学生的总分
  
• 在所有学生总分加10分的情况
  
• 使用别名表示学生分数。
  1. -- 1
  2. SELECT `name`,(chinese+english+math) FROM `student`;
  3. -- 2
  4. SELECT `name`,(chinese+english+math+10) FROM `student`;
  5. -- 3
  6. SELECT `name` AS '名字',(chinese+english+math) AS total_score FROM `student`;

  复制代码

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


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

• 查询姓名为赵云的学生成绩
  
• 查询英语成绩大于90分的同学
  
• 查询总分大于200分的所有同学
  1. -- 1
  2. SELECT * FROM `student` WHERE `name`='赵云';
  3. -- 2
  4. SELECT * FROM `student` WHERE `english`>90;
  5. -- 3
  6. SELECT * FROM `student` WHERE (chinese+english+math)>200;

  复制代码

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

• 查询math大于60并且(and) id大于4的学生成绩
  
• 查询英语成绩大于语文成绩的同学
  
• 查询总分大于200分并且数学成绩小于语文成绩,的姓赵的学生.
  1. -- 1
  2. SELECT * FROM `student` WHERE `math`>60 and `id`>4;
  3. -- 2
  4. SELECT * FROM `student` WHERE `english`>`chinese`;
  5. -- 3
  6. #韩% 表示名字以韩开头的就可以，%表示0个到多个字符，可以和Like搭配使用
  7. SELECT * FROM `student` WHERE (chinese+english+math)>200 and `math`<`chinese` and `name` LIKE '赵%';

  复制代码

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

1. -- 1
2. SELECT * FROM `student` WHERE `english`>=80 and `english`<=90;
3. SELECT * FROM `student` WHERE `english` BETWEEN 80 and 90;-- between and是一个闭区间，[80,90]
4. -- 2
5. SELECT * FROM `student` WHERE `math`=89 OR `math`=90 or `math`=91;
6. SELECT * FROM `student` WHERE `math` IN (89,90,91);
7. -- 3
8. SELECT * FROM `student` WHERE `name` Like '李%';
9. -- 4
10. SELECT * FROM `student` WHERE `math`>80 and `chinese`>80;
11. -- 5
12. SELECT * FROM `student` WHERE (chinese+english+math)=189 OR (chinese+english+math)=190 or (chinese+english+math)=233;
13. SELECT * FROM `student` WHERE (chinese+english+math) in (189,190,233);
14. -- 6
15. SELECT * FROM `student` WHERE `name` Like '李%' or `name` Like '宋%';
16. -- 7
17. SELECT * FROM `student` WHERE `math`-`chinese`>30;

复制代码

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

练习:

• 对数学成绩排序后输出【升序】。
  
• 对总分按从高到低的顺序输出
  
• 对姓李的学生成绩[总分]排序输出(升序)
  1. SELECT column1, column2, column3...
  2.         FROM table
  3.         order by column4 asc|desc,column5 asc|desc,...

  复制代码

函数

统计函数

合计/统计函数- count

Count返回行的总数

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

复制代码

练习:

• 统计一个班级共有多少学生?
  
• 统计数学成绩大于90的学生有多少个?
  
• 统计总分大于250的人数有多少?
  
• count(*)和count(列)的区别
  1. Select count(*) | count(列名) from table_name
  2. [WHERE where_definition]

  复制代码

合计函数-sum

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

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

复制代码

练习

• 统计一个班级数学总成绩?
  
• 统计一个班级语文、英语、数学各科的总成绩
  
• 统计一个班级语文、英语、数学的成绩总和
  
• 统计一个班级语文成绩平均分
  注意: sum仅对数值起作用，否则没有意义。
  注意:对多列求和，“,”号不能少。
  1. select sum(列名){, sum(列名)...} from tablename
  2. [WHERE where_definition]

  复制代码

合计函数- avg

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

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

复制代码

练习:

• 求一个班级数学平均分?
  
• 求一个班级总分平均分
  1. Select avg(列名){,avg(列名)...]from tablename
  2. [WHERE where_definition]

  复制代码

合计函数-Max/min

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

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

复制代码

练习:

• 求班级最高分和最低分(数值范围在统计中特别有用)
  1. select max(列名) from tablename
  2. [WHERE where_definition]

  复制代码

分组统计GROUP BY和HAVING

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

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

复制代码

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

1. SELECT column1, column2. column3... FROM table
2. group by column1 [,column2...];

复制代码

group by用于对查询的结果分组统计
having子句用于限制分组显示结果.(注意：where是对原始每行的数据过滤，having是对经过分组集成的数据进行进行限制，一般和group by配套使用)
练习
先使用下面的指令创建表

1. SELECT column1, column2, column3 ...
2. FROM table
3. group by column having ...

复制代码

• 如何显示每个部门的平均工资和最高工资
  
• 显示每个部门的每种岗位的平均工资和最低工资
  
• 显示平均工资低于2000的部门号和它的平均工资
  1. CREATE TABLE dept( /*部门表*/
  2. deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
  3. dname VARCHAR(20)  NOT NULL  DEFAULT "",
  4. loc VARCHAR(13) NOT NULL DEFAULT ""
  5. );
  6. INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');
  8. #创建表EMP雇员
  9. CREATE TABLE emp
  10. (empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
  11. ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
  12. job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
  13. mgr MEDIUMINT UNSIGNED ,/*上级编号*/
  14. hiredate DATE NOT NULL,/*入职时间*/
  15. sal DECIMAL(7,2)  NOT NULL,/*薪水*/
  16. comm DECIMAL(7,2) ,/*红利*/
  17. deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
  18. );
  19. INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20),
  20. (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  
  21. (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  
  22. (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  
  23. (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  
  24. (7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  
  25. (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  
  26. (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  
  27. (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  
  28. (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  
  29. (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  
  30. (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  
  31. (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
  33. #工资级别表
  34. CREATE TABLE salgrade
  35. (
  36. grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  37. losal DECIMAL(17,2)  NOT NULL,
  38. hisal DECIMAL(17,2)  NOT NULL
  39. );
  40. INSERT INTO salgrade VALUES (1,700,1200);
  41. INSERT INTO salgrade VALUES (2,1201,1400);
  42. INSERT INTO salgrade VALUES (3,1401,2000);
  43. INSERT INTO salgrade VALUES (4,2001,3000);
  44. INSERT INTO salgrade VALUES (5,3001,9999);

  复制代码

1. -- 1
2. select deptno,avg(sal),max(sal) from emp group by deptno;
3. -- 2
4. select deptno,job,avg(sal),min(sal) from emp group by deptno,job;
5. -- 3
6. -- 分析〔写sql语句的思路是化繁为简，各个击破]
7. -- 3.1 显示各个部门的平均工资和部门号
8. -- 3.2 在1的结果基础上，进行过滤，保留AVG(sal) <2000
9. select deptno,avg(sal) from emp group by deptno;
10. select deptno,avg(sal) from emp group by deptno HAVING AVG(sal) <2000;

复制代码

加密和系统函数

1. -- 演示字符串相关函数的使用 ， 使用 emp 表来演示
2. -- CHARSET(str) 返回字串字符集
3. SELECT CHARSET(ename) FROM emp;
5. -- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
6. SELECT CONCAT(ename, ' 工作是 ', job) FROM emp;
8. -- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有则返回 0
9. -- 注意：和java不一样 ，mysql是从1开始计算顺序的
10. -- dual 亚元表, 系统表 可以作为测试表使用
11. SELECT INSTR('hanshunping', 'ping') FROM DUAL;
13. -- UCASE (string2 ) 转换成大写
14. SELECT UCASE(ename) FROM emp;
16. -- LCASE (string2 ) 转换成小写
17. SELECT LCASE(ename) FROM emp;
19. -- LEFT (string2 ,length )从 string2 中的左边起取 length 个字符
20. -- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符
21. SELECT ename,LEFT(ename, 2),RIGHT(ename,2) FROM emp;
23. -- LENGTH (string )string 长度[按照字节]
24. SELECT LENGTH(ename) FROM emp;
26. -- REPLACE (str ,search_str ,replace_str )
27. -- 在 str 中用 replace_str 替换所有的 search_str
28. -- 如果是 manager 就替换成 经理
29. SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;
31. -- STRCMP (string1 ,string2 ) 逐字符比较两字串大小(不区分大小写) 返回数字0(一样)，-1(string2大),1(string1大)
32. SELECT STRCMP('hsp', 'jsp') FROM DUAL;#输出 -1
34. -- SUBSTRING (str , position [,length ])
35. -- 从 str 的 position 开始【从 1 开始计算】,取 length 个字符
36. -- 从 ename 列的第一个位置开始取出 2 个字符
37. SELECT SUBSTRING(ename, 1, 2) FROM emp;
39. -- LTRIM (string2) RTRIM (string2 ) TRIM(string)
40. -- 去除前端空格或后端或前后两端空格
41. SELECT LTRIM(' 韩顺平教育') FROM DUAL;
42. SELECT RTRIM('韩顺平教育 ') FROM DUAL;
43. SELECT TRIM(' 韩顺平教育 ') FROM DUAL;

复制代码

流程控制

先看两个需求:

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

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

复制代码

练习

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

1. -- 演示数学相关函数
3. -- ABS(num) 绝对值
4. SELECT ABS(-10) FROM DUAL;
6. -- BIN (decimal_number )十进制转二进制
7. SELECT BIN(10) FROM DUAL;
9. -- CEILING (number2 ) 向上取整, 得到 >=num2 的最小整数
10. SELECT CEILING(-1.1) FROM DUAL;
12. -- CONV(number2,from_base,to_base) 进制转换
13. -- 下面的 8 是十进制的 8, 转成 2 进制输出
14. SELECT CONV(8, 10, 2) FROM DUAL;
15. -- 下面的 16 是 16 进制的 16, 转成 10 进制输出
16. SELECT CONV(16, 16, 10) FROM DUAL;
18. -- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
19. SELECT FLOOR(-1.1) FROM DUAL;
21. -- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
22. SELECT FORMAT(78.125458,2) FROM DUAL;
24. -- HEX (DecimalNumber ) 转十六进制  (括号内的数是十进制)
25. SELECT HEX(46) FROM DUAL;#2E
27. -- LEAST (number , number2 [,..]) 求最小值
28. SELECT LEAST(0,1, -10, 4) FROM DUAL;
30. -- MOD (numerator ,denominator ) 求余
31. SELECT MOD(10, 3) FROM DUAL;
33. -- RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
34. -- 说明
35. -- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
36. -- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，该随机数也不变了
37. SELECT RAND() FROM DUAL;
39. -- ROUND(number)  四舍五入
40. select round(5.5);

复制代码

MySQL表查询--加强

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

1. SELECT column1, column2, column3 ...
2. FROM table
3. group by column having ...

复制代码

使用where子句

• 如何查找1992.1.1后入职的员工
  

如何使用like操作符

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

如何显示没有上级的雇员的情况
查询表结构

1. -- 创建测试表 信息表
2. CREATE TABLE mes(
3.     id INT ,
4.         content VARCHAR(30),
5.     send_time DATETIME);
6. -- 添加记录
7. INSERT INTO mes VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
8. INSERT INTO mes VALUES(2, '上海新闻', NOW());
9. INSERT INTO mes VALUES(3, '广州新闻', NOW());
10. SELECT * FROM mes;

复制代码

分页查询

• 按雇员的id号升序取出,每页显示3条记录，请分别显示第一页，第二页，第三页
  
• 基本语法:select ... limit start, rows
  表示从start+1行开始取,取出rows行, start 从0开始计算
  

练习题:

• 按雇员的id号降序取出，每页显示5条记录。请分别显示第3页，第5页对应的sql语句
  1. -- 日期时间相关函数【2】
  3. -- 1
  4. SELECT id,content,DATE(send_time) FROM mes;
  5. -- 2 (两种方式)
  6. select * FROM mes WHERE TIMEDIFF(NOW(),send_time)<'00:10:00';
  7. SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
  8. SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
  9. -- 3
  10. SELECT DATEDIFF('2011-11-11','1990-1-1');
  11. -- 4
  12. SELECT DATEDIFF(NOW(), '2000-08-11');
  13. -- 5
  14. SELECT DATEDIFF(DATE_ADD('2000-08-11', INTERVAL 80 YEAR),NOW());

  复制代码

分组函数和分组子句group by

• 显示每种岗位的雇员总数、平均工资。
  
• 显示雇员总数，以及获得补助(comm非空)的雇员数。
  
• 显示管理者的总人数。(即mgr有多少种)
  
• 显示雇员工资的最大差额。
  1. -- 日期时间相关函数【3】
  3. -- YEAR|Month|DAY|DATE (datetime )
  4. SELECT YEAR(NOW()) FROM DUAL;#2022
  5. SELECT MONTH(NOW()) FROM DUAL;#9
  6. SELECT DAY(NOW()) FROM DUAL;#15
  7. SELECT MONTH('2013-11-10');#11
  9. -- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数
  10. SELECT UNIX_TIMESTAMP() FROM DUAL;
  12. -- FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的时间
  13. -- %Y-%m-%d 格式是规定好的，表示年月日
  14. -- 意义：在开发中，可以存放一个整数，然后表示时间，通过 FROM_UNIXTIME 转换
  15. SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
  16. SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;

  复制代码

数据分组的总结

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

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

复制代码

应用案例：

• 请统计各个部门的平均工资，并且是大于 1000 的，并且按照平均工资从高到低排序，取出前两行记录
  1. # 演示流程控制语句
  2. # IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
  3. SELECT IF(TRUE, '北京', '上海') FROM DUAL;
  5. # IFNULL(expr1,expr2) 如果 expr1 为空 NULL,返回 expr2,否则返回 expr1
  6. SELECT IFNULL( NULL, '韩顺平教育') FROM DUAL;
  8. # SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
  9. # 如果 expr1 为 TRUE,则返回 expr2,如果 expr2 为 t, 返回 expr4, 否则返回 expr5
  10. SELECT CASE
  11. WHEN TRUE THEN 'jack'
  12. WHEN FALSE THEN 'tom'
  13. ELSE 'mary' END; -- jack

  复制代码

MySQL多表查询

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

• 显示雇员名,雇员工资及所在部门的名字【笛卡尔集】
  小技巧:多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集?
  
  
• 如何显示部门号为10的部门名、员工名和工资
  
• 显示各个员工的姓名，工资,及其工资的级别
  1. -- 1
  2. -- 说明，判断是否为 null 要使用 is null, 判断不为空 使用 is not
  3. SELECT ename, IF(comm IS NULL , 0.0, comm) FROM emp;
  4. SELECT ename, IFNULL(comm, 0.0) FROM emp;
  5. -- 2
  6. SELECT ename, CASE
  7. WHEN job = 'CLERK' THEN '职员' WHEN job = 'MANAGER' THEN '经理' WHEN job = 'SALESMAN' THEN '销售人员' ELSE job END AS 'job' FROM emp;
  8. SELECT * FROM emp;

  复制代码

自连接

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

• 显示公司员工和他的上级的名字（给表取别名，使其能当两张表用）
  1. CREATE TABLE dept( /*部门表*/
  2. deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
  3. dname VARCHAR(20)  NOT NULL  DEFAULT "",
  4. loc VARCHAR(13) NOT NULL DEFAULT ""
  5. );
  6. INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');
  8. #创建表EMP雇员
  9. CREATE TABLE emp
  10. (empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
  11. ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
  12. job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
  13. mgr MEDIUMINT UNSIGNED ,/*上级编号*/
  14. hiredate DATE NOT NULL,/*入职时间*/
  15. sal DECIMAL(7,2)  NOT NULL,/*薪水*/
  16. comm DECIMAL(7,2) ,/*红利*/
  17. deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
  18. );
  19. INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20),
  20. (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  
  21. (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  
  22. (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  
  23. (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  
  24. (7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  
  25. (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  
  26. (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  
  27. (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  
  28. (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  
  29. (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  
  30. (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  
  31. (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
  33. #工资级别表
  34. CREATE TABLE salgrade
  35. (
  36. grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  37. losal DECIMAL(17,2)  NOT NULL,
  38. hisal DECIMAL(17,2)  NOT NULL
  39. );
  40. INSERT INTO salgrade VALUES (1,700,1200);
  41. INSERT INTO salgrade VALUES (2,1201,1400);
  42. INSERT INTO salgrade VALUES (3,1401,2000);
  43. INSERT INTO salgrade VALUES (4,2001,3000);
  44. INSERT INTO salgrade VALUES (5,3001,9999);

  复制代码

子查询

什么是子查询：
子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询
单行子查询：
单行子查询是指只返回一行数据的子查询语句
请思考:如何显示与SMITH同一部门的所有员工?
多行子查询：
多行子查询指返回多行数据的子查询 使用关键字in

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

复制代码

子查询当做临时表使用

练习题：
先使用ecshop.sql文件创建数据库---- 下载

• 查询ecshop中各个类别(cat_id)中，价格最高的商品.结果 如下:
  
  提示，可以将子查询当做一张临时表使用
  1. -- 分页查询
  2. -- 第 1 页
  3. SELECT * FROM emp ORDER BY empno LIMIT 0, 3;
  4. -- 第 2 页
  5. SELECT * FROM emp ORDER BY empno LIMIT 3, 3;
  6. -- 第 3 页
  7. SELECT * FROM emp ORDER BY empno LIMIT 6, 3;
  9. -- 导一个公式
  10. SELECT * FROM emp
  11. ORDER BY empno
  12. LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数;

  复制代码

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

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

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

复制代码

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

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

1. SELECT column1, column2, column3... FROM table
2.         where where_definition
3.     group by column
4.     having condition
5.     order by column
6.     limit start, rows;

复制代码

多列子查询

多列子查序则是指查询返回多个列数据的子查询语句
(字段1,字段2…)=(select 字段1,字段2 from ...)
练习题：

• 请查询student表中和宋江数学，英语，语文完全相同的学生
  
• 请思考如何查询emp表中与allen的部门和岗位完全相同的所有雇员(并且不含smith本人)
  1. select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal>1000 order by avg_sal desc limit 0,2;

  复制代码

课后练习(先自己做)

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

1. -- 多表查询
2. -- 1. 显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
3. /*
4. 分析
5.     1. 雇员名,雇员工资 来自 emp 表
6.     2. 部门的名字 来自 dept 表
7.     3. 需求对 emp 和 dept 查询 ename,sal,dname,deptno
8.     4. 当我们需要指定显示某个表的列时，需要 表名.列名
9. */
10. SELECT ename,sal,dname,emp.deptno
11.     FROM emp, dept
12.     WHERE emp.deptno = dept.deptno;
13.    
14. SELECT * FROM emp;
15. SELECT * FROM dept;
16. SELECT * FROM salgrade;
17. -- 小技巧：多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
18. -- 2. 如何显示部门号为 10 的部门名、员工名和工资
19. SELECT ename,sal,dname,emp.deptno
20.     FROM emp, dept
21.     WHERE emp.deptno = dept.deptno AND emp.deptno = 10
22.    
23. -- 3.显示各个员工的姓名，工资，及其工资的级别
25. -- 思路 姓名，工资 来自 emp 13
26. -- 工资级别 salgrade 5
27. -- 写 sql , 先写一个简单，然后加入过滤条件...
28. select ename, sal, grade
29.     from emp , salgrade
30.     where sal between losal and hisal;
31.    
32. -- 练习:显示雇员名(ename),雇员工资(sal)及所在部门的名字(dname),并按部门排序[降序排].
33. select ename,sal,dname from emp,dept where emp.deptno=dept.deptno order by dname desc;

复制代码

合并查询

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

• union all
  该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。（注意：可以理解为把第二个表的内容接在第一个表的下面。不需要数据类型一致，但列数必须相同）
  1. select worker.ename as '员工名',boss.ename as '上级' from emp worker,emp boss where worker.mgr=boss.empno;

  复制代码
• union
  该操作赋与union all相似，但是会自动去掉结果集中重复行
  1. -- 子查询的演示
  3. -- 单行子查询
  4. -- 请思考：如何显示与 SMITH 同一部门的所有员工?
  5. /*
  6.     1. 先查询到 SMITH 的部门号得到
  7.     2. 把上面的 select 语句当做一个子查询来使用
  8. */
  9. SELECT deptno FROM emp WHERE ename = 'SMITH' ;
  10. -- 下面的答案. SELECT *
  11. FROM emp WHERE deptno = (
  12. SELECT deptno FROM emp WHERE ename = 'SMITH' );
  14. -- 多行子查询
  15. -- 练习:如何查询和部门 10 的工作相同的雇员的
  16. -- 名字、岗位、工资、部门号, 但是不含 10 号部门自己的雇员.
  17. /*
  18.     1. 查询到 10 号部门有哪些工作
  19.     2. 把上面查询的结果当做子查询使用
  20. */
  21. select distinct job from emp where deptno=10;
  22. select ename, job,sal,deptno from emp where job in (
  23.     select distinct job from emp where deptno=10) and
  24.     deptno != 10;

  复制代码

mysql表内连接

1. select * from ecshop.ecs_goods;
2. select goods_id,cat_id,goods_name,shop_price from ecshop.ecs_goods;
4. -- 1
5. -- 先得到 各个类别中，价格最高的商品 max + group by cat_id, 当做临时表
6. select cat_id,Max(shop_price) from ecshop.ecs_goods group by cat_id;
7. -- 把子查询当做一张临时表可以解决很多很多复杂的查询
8. select goods_id,cat_id,goods_name,shop_price from ecshop.ecs_goods;
10. select temp.cat_id,goods_id,goods_name,shop_price
11. from (
12.     select cat_id,Max(shop_price) as max_price  
13.     from ecshop.ecs_goods
14.     group by cat_id
15.         ) temp,ecs_goods
16.     where temp.cat_id=ecs_goods.cat_id
17.     and temp.max_price=ecs_goods.shop_price;

复制代码

获取两个表中字段匹配关系的记录。
个人认为内连接用处不大，它能处理的问题用之前的自连接知识也能处理。

mysql表外连接

提出一个问题

• 前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示
  
• 比如:列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。
  
• 使用我们学习过的多表查询的SQL，看看效果如何?
  1. -- 使用max()
  2. select ename,sal,deptno from emp where sal >(select max(sal) from emp where deptno=30);
  3. -- 使用all()
  4. select ename,sal,deptno from emp where sal >all(select sal from emp where deptno=30);

  复制代码

外连接

• 左外连接
  如果左侧的表完全显示我们就说是左外连接
  1. -- 使用min()
  2. select ename,sal,deptno from emp where sal >(select min(sal) from emp where deptno=30);
  3. -- 使用any()
  4. select ename,sal,deptno from emp where sal >any(select sal from emp where deptno=30);

  复制代码
  
  
• 右外连接
  如果右侧的表完全显示我们就说是右外连接
  1. -- 1
  2. select math,english,chinese from student where name='宋江';
  3. select * from student where (math,english,chinese)=(select math,english,chinese from student where name='宋江') and name!='宋江';
  4. -- 2
  5. select deptno,job from emp where LCASE(ename)='allen';
  6. SELECT * FROM emp WHERE (deptno,job)=(select deptno,job from emp where LCASE(ename)='allen') and LCASE(ename)!='allen';

  复制代码
  
  

先创建表：

1. -- 1
2. select deptno,avg(sal) as avg_sal from emp group by deptno;
3. 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;
4. -- 2
5. select deptno,max(sal) as max_sal from emp group by deptno;
6. 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;
7. -- 3
8. -- 表名.* 表示将该表所有列都显示出来, 可以简化 sql 语句
9. select deptno,count(*) as count_dept from emp group by deptno;
10. select dept.*,count_dept from dept,(select deptno,count(*) as count_dept from emp group by deptno) temp where dept.deptno=temp.deptno;

复制代码

使用

• 使用左连接---(显示所有人的成绩,如果没有成绩，也要显示该人的姓名和id号,成绩显示为空)
  
• 使用右外连接---(显示所有成绩，如果没有名字匹配，显示空)
  1. select ename,sal,job from emp where sal>2500
  2. union
  3. select ename,job,sal from emp where job='MANAGER';

  复制代码

表复制和表去重

表复制

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

复制代码

表去重(面试题)

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

复制代码

约束

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

primary key(主键)-基本使用

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

复制代码

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

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

复制代码

primary key(主键)-细节说明

• primary key不能重复而且不能为null。
  
• 一张表最多只能有一个主键,但可以是复合主键
  
• 主键的指定方式有两种
  直接在字段名后指定:字段名primary key
  在表定义最后写primary key(列名);
  
• 使用desc表名，可以看到primary key的情况
  
• 提醒:在实际开发中，每个表往往都有主键!
  

not nulI(非空)

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

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

复制代码

unique(唯一)

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

1. -- 创建 stu
2. /*
3. id name
4. 1 Jack
5. 2 Tom
6. 3 Kity
7. 4 nono
8. */
9. CREATE TABLE stu (id INT, `name` VARCHAR(32));
10. INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');
11. SELECT * FROM stu;
13. -- 创建 exam
14. /*
15. id grade
16. 1 56
17. 2 76
18. 11 8
19. */
20. CREATE TABLE exam( id INT, grade INT);
21. INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
22. SELECT * FROM exam;

复制代码

unique细节(注意):

• 如果没有指定not null，则unique字段可以有多个null
  
• 一张表可以有多个unique字段
  

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

复制代码

foreign key(外键)

1. -- 表的复制
3. -- 为了对某个 sql 语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
4. CREATE TABLE my_tab01( id INT, `name` VARCHAR(32), sal DOUBLE, job VARCHAR(32), deptno INT);
5. DESC my_tab01;
6. SELECT * FROM my_tab01;
8. -- 演示如何自我复制
9. -- 1. 先把 emp 表的记录复制到 my_tab01
10. INSERT INTO my_tab01(id, `name`, sal, job,deptno)
11. SELECT empno, ename, sal, job, deptno FROM emp;
13. -- 2. 自我复制
14. INSERT INTO my_tab01 SELECT * FROM my_tab01;-- 自我复制， 如果多次使用，最后数据量会很大
15. SELECT COUNT(*) FROM my_tab01;

复制代码

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

1. -- 如何删除掉一张表重复记录
3. -- 1. 先创建一张表 my_tab02,
4. -- 2. 让 my_tab02 有重复的记录
5. CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把 emp 表的结构(列)，复制到 my_tab02
6. DESC my_tab02;
7. INSERT INTO my_tab02 SELECT * FROM emp;
8. SELECT * FROM my_tab02;
10. -- 3. 考虑去重 my_tab02 的记录
11. /*
12.     思路
13.     (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
14.     (2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
15.     (3) 清除掉 my_tab02 记录
16.     (4) 把 my_tmp 表的记录复制到 my_tab02
17.     (5) drop 掉 临时表 my_tmp
18. */
19. -- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
20. create table my_tmp like my_tab02;
21. -- (2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
22. insert into my_tmp select distinct * from my_tab02;
23. -- (3) 清除掉 my_tab02 记录
24. delete from my_tab02;
25. -- (4) 把 my_tmp 表的记录复制到 my_tab02
26. insert into my_tab02 select * from my_tmp;
27. -- (5) drop 掉 临时表 my_tmp
28. drop table my_tmp;
29. select * from my_tab02;

复制代码

foreign key(外键)—细节说明

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

check【了解就行】

1. 字段名 字段类型 primary key

复制代码

用于强制行数据必须满足的条件,假定在sal列上定义了check约束,要求sal列值在1000~2000之间如果不在1000 ~2000之间就会提示出错。
oracle和sql server均支持check ,但是mysql5.7目前还不支持check ,只做语法校验，但不会生效。

1. -- 主键使用
3. -- id name email
4. CREATE TABLE t17
5. (id INT PRIMARY KEY, -- 表示 id 列是主键
6. `name` VARCHAR(32),
7. email VARCHAR(32));
9. -- 主键列的值是不可以重复
10. INSERT INTO t17 VALUES(1, 'jack', 'jack@sohu.com');
11. INSERT INTO t17 VALUES(2, 'tom', 'tom@sohu.com');
12. INSERT INTO t17 VALUES(1, 'hsp', 'hsp@sohu.com');
14. SELECT * FROM t17;
17. -- 主键使用的细节讨论
19. -- primary key 不能重复而且不能为 null。
20. INSERT INTO t17 VALUES(NULL, 'hsp', 'hsp@sohu.com');
22. -- 一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)
23. CREATE TABLE t18
24. (id INT PRIMARY KEY, -- 表示 id 列是主键
25. `name` VARCHAR(32) PRIMARY KEY, -- 错误的
26. email VARCHAR(32));
27. -- 演示复合主键 (id 和 name 做成复合主键)
28. CREATE TABLE t18
29. (id INT , `name` VARCHAR(32),
30. email VARCHAR(32), PRIMARY KEY (id, `name`) -- 这里就是复合主键
31. );
32. INSERT INTO t18 VALUES(1, 'tom', 'tom@sohu.com');
33. INSERT INTO t18 VALUES(1, 'jack', 'jack@sohu.com');
34. INSERT INTO t18 VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键
35. SELECT * FROM t18;
38. -- 主键的指定方式 有两种
39. -- 1. 直接在字段名后指定：字段名 primakry key
40. -- 2. 在表定义最后写 primary key(列名);
41. CREATE TABLE t19(id INT , `name` VARCHAR(32) PRIMARY KEY, email VARCHAR(32));
42. CREATE TABLE t20(id INT , `name` VARCHAR(32) , email VARCHAR(32), PRIMARY KEY(`name`)); -- 在表定义最后写 primary key(列名)
44. -- 使用 desc 表名，可以看到 primary key 的情况
45. DESC t20 -- 查看 t20 表的结果，显示约束的情况
46. DESC t18

复制代码

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

现有一个商店的数据库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);
建表,在定义中要求声明[进行合理设计]:
<ol>每个表的主外键;
客户的姓名不能为空值;
电邮不能够重复;
客户的性别[男|女] check 枚举..
单价unitprice在1.0 - 9999.99之间 check
[code]create table goods(goods_id int primary key,goods_name varchar(50),unitprice double check(unitprice>=1.0 and unitprice