Mysql学习笔记_mysql -u -p6032 -h127.0.0.1

一、Mysql的安装和配置

①.操作提示：

• 安装教程：https://www.bilibili.com/video/BV1NJ411J79W?p=3

• mysql安装推荐下载官网的压缩包形式解压进行安装，如果用.exe安装可能会出现卸载难题，解压之后需要手动配置mysql文件，mysql默认不支持中文，需要修改字符集

• 解决Mysql数据库存储中文的问题：https://blog.csdn.net/u011791611/article/details/88183619

• 需要使用管理员权限打开cmd窗口才可以启动或者停止mysql服务，否则会提示拒绝访问

• 需要先quit;退出mysql才可以net stop mysql退出mysql服务

• 如果安装过Mysql过程中，出错了或者想删除已经安装好的mysql服务: **sc delete mysql **

• mysql输入密码的两种方式

  • mysql -h 被连接的主机的ip地址 -P 端口号 -u 用户名 -p密码

    • -h ip 不填则默认为本机127.0.0.1或localhost
    • -P 端口号 不填则默认为3306
    • -p后面直接输入密码不需要空格

  • mysql -u root -p+密码+回车 -p后面直接写密码，不需要空格，可以显示的看到输入的密码

  • mysql -u root -p+回车 再输入 密码+回车 暗码输入密码，可以隐藏秘密

• 实际工作中会修改mysql密码和端口号防止被攻击

• Linux下启动mysql是在mysql的bin目录下执行如下命令打开mysql程序

  • ./mysql -u root -p密码

②.解压zip包方式的安装步骤：

1.下载Mysql的zip 安装文件

• 解压的路径最好不要有中文和空格,尽量解压到空间大的盘

2.添加环境变量

• 在Path 环境变量增加mysql的安装目录\bin

3.在解压的目录下创建my.ini文件

[mysqld]
#Mysql安装目录
basedir=D:\mysql-5.7.19-winx64\
#Mysql数据目录
datadir=D:\mysql-5.7.19-winx64\data\
#字符集
character_set_server = utf8
#指定被mysql服务监听的端口
port=3306
#跳过安全检查，即不用输入密码也可以登录
#skip-grant-tables
#客户端字符集
[client]
default-character-set = utf8
#服务端字符集
[mysql]
default-character-set = utf8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4.安装mysql服务

• 使用管理员身份打开 cmd , 并切换到 D:\hspmysql\mysql-5.7.19-winx64\bin 目录下, 执行

mysqld -install
1

5.初始化数据库

mysqld --initialize-insecure --user=mysql
1

6.启动mysql 服务

net start mysql
1

7.进入mysql 管理终端

mysql -u root -p				注意需要在my.ini文件中设置 skip-grant-tables 跳过安全检验   此命令行表示当前root用户密码为空
1

8.打开名称为mysql的表修改root用户密码

use mysql;  //打开名称为mysql的表
update user set authentication_string=password('123456') where user='root' and Host='localhost';//修改root用户的密码为123456，需要在mysql语句末尾添加;
flush privileges;//刷新权限
quit;//退出
1
2
3
4

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

#skip-grant-tables   注释掉这行，即表示登录mysql需要进行权限验证，需要输入密码
1

10.停止mysql服务再重新启动mysql

• 注意：停止服务需要先退出mysql命令行,即先quit;退出mysql

net stop mysql  //关闭mysql服务
net start mysql //启动mysql服务
1
2

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

mysql -u root -p+密码+回车     或
mysql -u root -p+回车
密码+回车
1
2
3

• 显示mysql>即成功打开mysql并进入sql语句输入模式

二、数据库管理软件的安装

1.navicat

• 官方下载地址：https://www.navicat.com.cn/products
• 安装教程：https://www.bilibili.com/video/BV1JK411V7bQ

2.Sqlyog

• 官方下载地址：https://sqlyog.en.softonic.com/

三、温馨提示

操作相关：

• Mysql菜鸟教程帮助文档：https://www.runoob.com/mysql/mysql-tutorial.html

• 【韩顺平讲MySQL】零基础一周学会MySQL -sql mysql教程 mysql视频 mysql入门

  • 相关sql文件文档下载：
  • 链接：https://pan.baidu.com/s/1sIf_0SMdkj9vZX5USndTWA
    提取码：ku31

• 指定数据库或表使用的字符集CHARACTER SET 可以简写成CHARSET

• 字符集utf8_bin区分大小写，而字符集untf8_bin不区分大小写

• 存储引擎默认为InnoDB

• 表中的字段不区分大小写，如字段dnum和Dnum是一样的，不可以重复使用

  • 错误代码： 1060 Duplicate column name ‘Dnum’

• Sqlyog的注释快捷键为Ctrl+Shift+C,取消注释是Ctrl+Shift+R

• Navicat的注释快捷键为Ctrl+/

• char(size)类型适合存储固定长度的字符串，查询速度会快于varchar(size)

• sql语句中的字符串可以用单引号或者双引号括起来

• 插入日期的sql语句可以不带前导0

• 数据库建表通常用下划线_命名法

• DUAL 为数据库默认的表 亚元表

• 数据库字符串函数的下标都是从1开始，而不是从0开始

• **注意：**password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。

• Mysql中的不等号： != <>

常用单词：

1. alter	更改、修改
2. show		展示
3. collate 核对、校验
4. delete 删除
5. insert into 插入
6. modify 修改
7. update 更新
8. select 选择
9. drop 丢弃、删掉
10. create 创建
11. table 表
12. character set <==> charset 字符集
13. engine 引擎
14. utf8 gbk 常用的字符集
15. utf8_bin uft8_general_ci 常用的校验规则
16.	database 数据库
17. set 设置
18. where 当
19. timestamp 时间戳
20. after 在..后面
21. desc 描述
22. CRUD   create 增加 retrieve 检索 update 更新 delete 删除
23. distinct 不同的
24. like 像
25. between .. and .. 在..和..之间
26. ascending order 升序
27. descending order 降序
28. group by 分组
29. dual 亚元表
30. union 合并
31. ENUM(枚举常量1，枚举常量2) 枚举
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

四、数据库语句

1.创建数据库

#创建数据库keivn 默认字符集为utf8，排序规则为utf8_general_ci;(不区分大小写排序)
create database 指定要创建的数据库;

#创建数据库kevin01 指定字符集为utf8，排序规则默认为utf8_general_ci;(不区分大小写排序)
create database 指定要创建的数据库 character set utf8;

#创建数据库kevin02 指定字符集为utf8，指定排序规则为utf8_bin;(区分大小写排序)
create database 指定要创建的数据库 character set utf8 collate utf8_bin;
1
2
3
4
5
6
7
8

2.查看数据库

①、查看mysql中已有的数据库

#注意databases需要需要复数形式，查看当前mysql中已经创建的数据库
show databases;
1
2

②、查看创建指定数据库时用的sql语句

#查看创建指定数据库时的mysql语句,这里的database用单数形式
show create database kevin02(指定的数据库);
1
2

3.删除数据库

#如果存在，则删除指定的数据库,如果不加if exists进行判断，如果指定数据库不存在则报错
drop database if exists 指定要删除的数据库名;
1
2

4.备份数据库

#备份数据库需要退出mysql输入模式，mysql安装目录下的bin目录中打开dos命令行执行
#可以一次备份多个指定的数据库到一个sql文件中 需要指定路径
#备份文件其实是很多sql语句，
mysqldump -u 用户名 -p -B 需要备份的数据库1 需要备份的数据库2 ... 需要备份的数据库n > 备份文件保存路径:\\XXX.sql
回车输入密码
1
2
3
4
5

• 备份的sql文件内容

-- MySQL dump 10.13  Distrib 5.7.19, for Win64 (x86_64)
--
-- Host: localhost    Database: kevin01
-- ------------------------------------------------------
-- Server version	5.7.19

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `kevin01`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `kevin01` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `kevin01`;

--
-- Current Database: `kevin02`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `kevin02` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;

USE `kevin02`;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-03-09 17:55:18
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

5.备份数据库表

#退出mysql输入模式，在mysql安装目录的bin目录下打开dos命令行窗口
#注意 命令行中不需要有 -B   这代码备份的都是数据库
mysqldump -u 用户名 -p 需要备份表的数据库名 需要备份的表1 需要备份的表2 ... 需要备份的表n > 备份文件保存路径:\\XXX.sql
回车输入密码
1
2
3
4

6.恢复数据库

①、用命令行模式恢复

#需要在msyql输入模式中执行  需要指定sql文件全路径
scoure 指定的sql备份文件
1
2

②、复制粘贴sql备份文件内容方式恢复

• 文本形式打开备份好的sql文件，在mysql输入模式中粘贴执行
• 或者再navicat、sqlyog的查询窗口中粘贴执行

7.恢复数据库中的某个表

#恢复某个数据库中的表时，需要先切换到指定的数据库 use 数据库名  再恢复表
source 备份的表sql文件
1
2

8.创建表

语法：

• 注意：
  • 表中字段之间使用逗号分隔
  • sql语句末尾需要分号
  • 如果不指定字符集、排序规则、存储引擎等，系统会自动设置表为默认与数据库一样的字符集和排序规则，存储引擎为InnoDB
  • 当表中字段为关键字时，使用反引号

CREATE TABLE(
  	字段1 类型,
  	字段2	类型,
  	字段3 类型,)
  	CHARACTER SET 字符集 
  	COLLATE 核对的排序规则
  	ENGINE 存储引擎;  	
1
2
3
4
5
6
7

代码示例：

#切换到数据库kevin01
USE kevin01;

#创建表
#表名为user,因为user为关键字，所以用反引号
#字段		类型
#id 		int
#name		varchar(255)
#password 	varchar(255)
#birthday	date
#指定字符集为utf8
#指定校验排序规则为utf8_bin
#指定存储引擎为InnoDB
CREATE TABLE `user` (
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	`birthday` DATE)
	CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

五、数据类型概述：

1.数值类型

①、整数

②、小数

2.文本、二进制类型

3.日期、时间类型

六、常用数据类型演示：

1.int：

提示：

• 有符号int类型占用4个字节，总共32位，存储的数据范围为:-2³¹到2³¹-1,即-2147483648~2147483647
• 无符号int类型占用4个字节，总共32位，存储的数据范围为:0到2³²-1,即0 ~ 4294967295
• 如果写sql语句插入的数据超过了所能够存储的数据范围，则会报错，插入失败

有符号：

#1.创建表t2,定义字段id为有符号int类型,所能存储的数据范围为：-2^31 ~ 2^31 - 1
CREATE TABLE t2(
	id INT
)CHARSET utf8 COLLATE utf8_bin; 

#2.插入数据2^31 - 1  成功
INSERT INTO t2 VALUES((1<<31)-1);
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：insert into t2 values((1<<31)-1)

共 1 行受到影响

执行耗时   : 0.001 sec
传送时间   : 0.001 sec
总耗时      : 0.002 sec


#3.插入数据2^31   失败
INSERT INTO t2 VALUES(1<<31);
--------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：INSERT INTO t2 VALUES(1<<31)

错误代码： 1264
Out of range value for column 'id' at row 1

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

无符号：

#1.创建表t3,定义字段id为无符号int类型，数据范围为：0 ~ 2^32 - 1 即 0 ~ 4294967295
CREATE TABLE t3(
	id INT UNSIGNED
);

#2.插入数据2^32 -1  成功
INSERT INTO t3 VALUES((1<<32) - 1);
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：insert into t3 values((1<<32) - 1)

共 1 行受到影响

执行耗时   : 0.001 sec
传送时间   : 0 sec
总耗时      : 0.002 sec


#3.插入数据2^32	失败
INSERT INTO t3 VALUES(1<<32);
--------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：insert into t3 values(1<<32)

错误代码： 1264
Out of range value for column 'id' at row 1

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec


#4.插入数据-1 失败
INSERT INTO t3 VALUES(-1);
--------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：insert into t3 values(-1)

错误代码： 1264
Out of range value for column 'id' at row 1

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

2.bit(M)：

提示：

• 数值显示为位形式，但查询时仍然可以按照十进制数的形式查询
• M为1~64 如果M省略,则默认为1，表示数据范围为0~2^M-1， 即最大为0~2⁶⁴-1

示例：

#1.创建表t4，定义字段id为BIT类型，最多占3位
CREATE TABLE t4(
	id BIT(3)
);


#2.插入数据2 成功
INSERT INTO t4 VALUES(2);
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：insert into t4 values(2)

共 1 行受到影响

执行耗时   : 0.003 sec
传送时间   : 0 sec
总耗时      : 0.004 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

• 显示效果：

#3.插入数据8 失败
INSERT info t4 VALUES(8);
--------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：insert info t4 values(8)

错误代码： 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't4 values(8)' at line 1

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec


#4.插入数据-1  失败
INSERT INTO t4 VALUES(-1);
--------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：INSERT INTO t4 VALUES(-1)

错误代码： 1406
Data too long for column 'id' at row 1

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec

#5.按照十进制数查询Bit类型存储的数据
SELECT * FROM t4 WHERE id = 2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

• 查询结果：

3.float、double、decimal(M,D)：

提示：

• 单精度浮点数float精确到大约7位小数位
• 双精度浮点数double精确到大约15位小数位
• DECIMAL(M,D)
  • 如果D是0，则值没有小数点或分数部分。DECIMAL整数最大位数(M)为65。支持的十进制数的最大位数(D)是30。如果D被省略， 默认是0。如果M被省略， 默认是10。
  • 小数点和(负数)的‘-’符号不包括在M中。M是小数位数(精度)的总数，D是小数点(标度)后面的位数

高精度小数:

#1.创建表t5,定义
#字段fnum,类型为float 	-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38
#字段dnum,类型为double 	-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到  1.7976931348623157E+308
#字段tnum,类型为decimal(20,10)	20是小数位数(精度)的总数，10是小数点(标度)后面的位数
CREATE TABLE t5(
	fnum FLOAT,
	dnum DOUBLE,
	tnum DECIMAL(20,10)
);
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：create table t5( fnum float, dnum double, tnum decimal(20,10) )

共 0 行受到影响

执行耗时   : 0.025 sec
传送时间   : 0 sec
总耗时      : 0.025 sec


#2.往3个字段都插入数据88.12345678912345
INSERT INTO t5 VALUES(88.12345678912345,88.12345678912345,88.12345678912345);
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 1 warnings

查询：insert into t5 values(88.12345678912345,88.12345678912345,88.12345678912345)

共 1 行受到影响， 1 个警告

执行耗时   : 0.010 sec
传送时间   : 0 sec
总耗时      : 0.010 sec

Note Code : 1265
Data truncated for column 'tnum' at row 1 -->翻译：在第1行为列'tnum'截断的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

• 查询结果：

• 将第3个字段tnum的类型修改成 DECIMAL(30,20) 再次插入相同数据

大整数：

#1.创建表t7							   	
#字段num1为 bigInt unsigned 类型，存储范围为：-9223372036854775808~9223372036854775807
#字段num2为 decimal 类型，存储范围为：
CREATE TABLE t7(
	num1 BIGINT UNSIGNED,
	num2 DECIMAL(65)
);

#2.在字段num1插入数据123456789123456789123456789  失败
INSERT INTO t7(num1) VALUES(123456789123456789123456789);

#3.在字段num2插入数据123456789123456789123456789  成功
INSERT INTO t7(num2) VALUES(123456789123456789123456789);



--------------------------------------------------------------
3 queries executed, 2 success, 1 errors, 0 warnings

查询：CREATE TABLE t7( num1 BIgint unsigned, num2 DECIMAL(65) )

共 0 行受到影响

执行耗时   : 0.054 sec
传送时间   : 0 sec
总耗时      : 0.055 sec
-----------------------------------------------------------

查询：insert into t7(num1) values(123456789123456789123456789)

错误代码： 1264
Out of range value for column 'num1' at row 1

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0.001 sec
-----------------------------------------------------------

查询：INSERT INTO t7(num2) VALUES(123456789123456789123456789)

共 1 行受到影响

执行耗时   : 0.001 sec
传送时间   : 0 sec
总耗时      : 0.001 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

7.char(size)、varchar(size)、text(size)：

提示：

• GBK编码每个字符占2个字节，UTF-8字符集每个字符占3个字节
• char(size) 固定size个字符,最多255个字符,定长字符串, 适合存储固定长度的数据
• varchar(size) 字符数根据存储字符串长度而变化，有1~3个字节存储实际存储字符串的长度, 因此最多占用65535-3个字节
• varchar在gbk编码下最多存储32766个字符,在utf8编码下最多存储21844个字符
• 存储时仅关注字符个数，不论是中文字符还是英文字符
• text不能有默认值，而char和varchar可以有默认值
• text类型适合存储留言、文章等不定长的长字符串
• longtext类型不能定义存储的字符大小

示例：

#1.创建表t8
#字段name1，类型为char(4),最多4个字符
#字段name2，类型为varchar(4),最多4个字符
#字段name3，类型为text(4),最多4个字符
CREATE TABLE t8(
	name1 CHAR(4),
	name2 VARCHAR(4),
	name3 TEXT(4)
)CHARSET utf8;

#2.在3个字段分别插入字符串 成功
INSERT INTO t8 VALUES("abcd",'abcd','ab哈哈');
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：insert into t8 values("abcd",'abcd','ab哈哈')

共 1 行受到影响

执行耗时   : 0.009 sec
传送时间   : 0 sec
总耗时      : 0.010 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• 查询结果：

#3.在字段name1中插入字符串 abcde 失败
INSERT INTO t8(name1) VALUES('abcde');
--------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：insert into t8(name1) values('abcde')

错误代码： 1406
Data too long for column 'name1' at row 1

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec
1
2
3
4
5
6
7
8
9
10
11
12
13

8.date、time：

提示：

• date类型支持的范围为’1000-01-01’到’9999-12-31’。MySQL以’YYYY-MM-DD’格式显示DATE值，但允许使用字符串或数字为DATE列分配值。例子：2022-3-11
• time类型支持的范围是’-838:59:59’到’838:59:59’。MySQL以’HH:MM:SS’格式显示TIME值，但允许使用字符串或数字为TIME列分配值。
• 插入日期的sql语句可以不带前导0

示例：

#创建表t9
#字段t,类型为date
#字段t1,类型为time
CREATE TABLE t9(
	t DATE,
	t1 TIME
);

#2.在字段t中插入 '2022-03-11'  成功
INSERT INTO t9(t) VALUES('2022-03-11');

#3.在字段t中插入 '2022-3-11'  成功  
INSERT INTO t9(t) VALUES('2022-3-11');

#4.在字段t中插入 '2022-13-1'  失败
INSERT INTO t9(t) VALUES('2022-13-1');
--------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：INSERT INTO t9(t) VALUES('2022-13-1')

错误代码： 1292
Incorrect date value: '2022-13-1' for column 't' at row 1

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec

#5.在字段t1中插入 '21:12:03' 成功
INSERT INTO t9(t1) VALUES('21:12:03');
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：insert into t9(t1) values('21:12:03')

共 1 行受到影响

执行耗时   : 0.002 sec
传送时间   : 0.002 sec
总耗时      : 0.005 sec

#6.在字段t1中插入 '21:12:60' 失败
INSERT INTO t9(t1) VALUES('21:12:60');
--------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：INSERT INTO t9(t1) VALUES('21:12:60')

错误代码： 1292
Incorrect time value: '21:12:60' for column 't1' at row 1

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

• 查询结果：

9.datetime、timestamp:

提示：

• 支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。MySQL以’YYYY-MM-DD HH:MM:SS’格式显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值。
• 范围是’1970-01-01 00:00:00’到2037年 用于INSERT或UPDATE操作时记录日期和时间 如果你不分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间 TIMESTAMP值返回后显示为’YYYY-MM-DD HH:MM:SS’格式的字符串，显示宽度固定为19个字符
• datetime类型中，YYYY-MM-DD和HH:MM:SS中键的为空格
• timestamp类型常需要定义时间戳的其他属性，如非空、默认值、自动更新等

示例：

#1.创建表t10 
#字段t,类型为  具体时间类型 datetime
#字段t1,类型为 时间戳类型 timestamp
#定义t1时间戳 非空 默认为当前时间 自动更新时间戳
CREATE TABLE t10(
	t DATETIME,  -- 具体时间(年月日 时分秒)
	t1 TIMESTAMP -- 时间戳 
	NOT NULL     -- 不为空
	DEFAULT CURRENT_TIMESTAMP   -- 默认时间戳为：当前时间
	ON UPDATE CURRENT_TIMESTAMP -- 当该条数据更新时，更新时间戳为：当前时间
);

#2.在字段t1中插入 '2022-2-12 10:21:14' 成功
INSERT INTO t10(t) VALUES('2022-2-12 10:21:14');

#3.不插入时间戳直接查询
SELECT * FROM t10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 查询结果：

#4.修改表中的这条数据
UPDATE t10 SET t = '2022-1-1 13:21:5' WHERE t = '2022-2-12 10:21:14';
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：update t10 set t = '2022-1-1 13:21:5' where t = '2022-2-12 10:21:14'

共 1 行受到影响

执行耗时   : 0.005 sec
传送时间   : 0 sec
总耗时      : 0.006 sec
1
2
3
4
5
6
7
8
9
10
11
12

• 查询结果：

10.数据类型综合练习：

#创建员工表emp,选用适当的数据类型
CREATE TABLE emp(
	`id` 		INT, 		-- int
	`name` 		VARCHAR(32),	-- varchar(32)
	`sex`  		CHAR(1),	-- char(1)
	`birthday` 	DATE,		-- date
	`entry_date` 	DATETIME,	-- datetime
	`job` 		VARCHAR(32),	-- varchar(32)
	`salary` 	DOUBLE,		-- double
	`resume`	TEXT		-- text
)CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;

INSERT INTO emp VALUES(1, '张三', '男', '1999-10-1', '2021-3-2 10:21:44', '后端软件工程师', 21234.31, '本科毕业');

--------------------------------------------------------------
2 queries executed, 2 success, 0 errors, 0 warnings

查询：create table emp( `id` int, `name` varchar(32), `sex` char(1), `birthday` date, `entry_date` DATETIME, `job` varchar(32), `salar...

共 0 行受到影响

执行耗时   : 0.029 sec
传送时间   : 0 sec
总耗时      : 0.029 sec
-----------------------------------------------------------

查询：insert into emp values(1, '张三', '男', '1999-10-1', '2021-3-2 10:21:44', '后端软件工程师', 21234.31, '本科毕业')

共 1 行受到影响

执行耗时   : 0.002 sec
传送时间   : 0 sec
总耗时      : 0.003 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

• 查询结果：

七、改变表

1.查看表结构：

desc 要查看的表名;
1

2.添加列：

#sql语法:
ALTER TABLE 要修改的表名 ADD 要添加的列名 列类型 

#员工表 emp 的上增加一个 image 列，varchar 类型(要求在 resume 后面)。
ALTER TABLE `emp` ADD `image` varchar(32) NOT NULL DEFAULT '' AFTER `resume`;
1
2
3
4
5

3.修改列类型：

#sql语法：
ALTER TABLE 要修改的表 MODIFY 要修改的列名 列类型

#1.如果您想要更改列的类型而不是名称， CHANGE语法仍然要求旧的和新的列名称，即使旧的和新的列名称是一样的。例如：
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
#2.也可以使用MODIFY来改变列的类型，此时不需要重命名：
ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

#修改 job 列，使其长度为 60。
ALTER TABLE `emp` MODIFY `job` VARCHAR(60) NOT NULL DEFAULT '';
1
2
3
4
5
6
7
8
9
10

3.删除列：

#sql语法
ALTER TABLE 要删除列的表名 DROP 要删除的列名;

#删除 sex 列
ALTER TABLE `emp` DROP `sex`;
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：ALTER TABLE `emp` DROP `sex`

共 0 行受到影响

执行耗时   : 0.085 sec
传送时间   : 0 sec
总耗时      : 0.085 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4.更改表名：

#sql语法
RENAME TABLE 旧表名 TO 新表名;

#表名emp改为 employee
RENAME TABLE `emp` TO `employee`;
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：RENAME TABLE `emp` TO `employee`

共 0 行受到影响

执行耗时   : 0.014 sec
传送时间   : 0 sec
总耗时      : 0.014 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

5.更改表的字符集：

#sql语法
ALTER TABLE 要修改的表 指定的字符集;

#修改表的字符集为 utf8
ALTER TABLE `employee` CHARSET utf8;
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：ALTER TABLE `employee` CHARSET utf8

共 0 行受到影响

执行耗时   : 0.014 sec
传送时间   : 0 sec
总耗时      : 0.015 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

6.更改列名：

#sql语法
ALTER TABLE 要修改的表 CHANGE 旧列名 新列名 列类型；

#列名 name 修改为 user_name
ALTER TABLE `employee` CHANGE `name` `username` varchar(32) NOT NULL DEFAULT '';
--------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：ALTER TABLE `employee` CHANGE `name` `username` varchar(32) NOT NULL DEFAULT ''

共 1 行受到影响

执行耗时   : 0.052 sec
传送时间   : 0.002 sec
总耗时      : 0.054 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

八、增删改查

1. insert增：

#sql语法
INSERT INTO 要插入的表名(列1，列2,列3...，列n) 
		VALUES(列1值，列2值，列3值...,列n值);

#1. 如果表后面不指定列， 则默认插入的值与所有的列按顺序一一对应，否则报错
#2. 可以交换表后面指定的列的顺序，但VALUES中的值一定要与之对应，否则报错
#3. 日期类型或者字符串类型需要用引号引起来
#4. 插入的数据应与字段的数据类型相同
#5. 数据的长度应在列的规定范围内
#6. 列可以插入空值[前提是该字段允许为空]
#7. insert into tab_name (列名..) values (),(),() 形式添加多条记录
#8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错
	-- 如果我们希望指定某个列的默认值，可以在创建表时指定
	-- 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给 null
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.update:

#sql语法
UPDATE 要更新数据的表 
	SET 列1 = 列1的值 [, 列2 = 列2的值 ...]
	WHERE 筛选条件;

#1. 如果没有where进行筛选，则会更新所有的数据
#2. 在更新列值的时候可以使用表达式
1
2
3
4
5
6
7

3.delete:

#sql语法
DELETE FROM 要删除数据的表 WHERE 筛选条件;

#1. 如果没有where语句，则会删除表中所有数据
#2. delete语句无法删除某一列的数据，可以用update语句将某一列改为NULL或''
#3. delete语句仅能删除数据本身，不能删除表或删除表中某一列，可以用删除表可以用drop table 表名;删除表的指定字段可以用alter table drop 列名;
1
2
3
4
5
6

4.select:

tip:

• 使用别名时，可以不用引号，直接写别名

• WHERE标准 SQL 不允许在子句 中引用列别名 。施加此限制是因为在WHERE评估子句时，可能尚未确定列值。例如，以下查询是非法的：

  SELECT id, COUNT(*) AS cnt FROM tbl_name
    WHERE cnt > 0 GROUP BY id;
  1
  2

• 查询时，某列值为NULL，查询条件应写成：

SELECT * FROM 表名 WHERE 列 IS NULL;
#不能写成
SELECT * FROM 表名 WHERE 列 = NULL;
1
2
3

• 多字句查询顺序：

SELECT 列1， 列2，...列n FROM 表名
	GROUP BY 按列1分组, 再按列2分组
	HAVING 分组列的筛选条件
	ORDER BY 按列1排序 ASC/DESC， 再按列2排序 ASC/DESC
	LIMIT 第一条数据下标(最小为0)， 获取数据条数
1
2
3
4
5

①、基础语法：

SELECT [DISTINCT] 列1, 列2, ...列n  FROM 表名 WHERE 筛选条件;

#1. distinct 表示去重，但需要是select的所有列的数据对应完全相同
#2. 语句select * from table_name; 常用用来查看表中所有数据
1
2
3
4

②、使用表达式对查询的列进行计算：

SELECT 列1, 列2+列3, 列4 FROM 表名 WHERE 筛选条件;

#1. 返回的表中会有新列：列1+列2 该列对应的值为：列1值+列2值
1
2
3

③、对列使用别名：

SELECT 列1， 列2+列3 AS 列的别名, 列4 FROM 表名 WHERE 筛选条件;

#1. 返回的表中会有新列：列的别名 该列对应的值为：列1值+列2值
1
2
3

④、where 子句中的常用运算符：

⑤、select练习:

#1.创建student表
CREATE TABLE student( 
  id INT NOT NULL DEFAULT 1, 
  NAME VARCHAR(20) NOT NULL DEFAULT '', 
  chinese FLOAT NOT NULL DEFAULT 0.0, 
  english FLOAT NOT NULL DEFAULT 0.0, 
  math FLOAT NOT NULL DEFAULT 0.0 
);

#2.插入数据
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

a. 统计每个学生的总分

SELECT `name`, (chinese + english + math) AS total_score FROM student;
1

b. 查询所有学生总分+10的情况

SELECT `name`, (chinese+english+math+10) AS total_score FROM student;
1

c. 在赵云的总分上+60%

SELECT `name`, ((chinese+english+math)*1.6) AS finall_score FROM student 
		where `name` = '赵云';
1
2

d. 统计关羽的总分

SELECT `name`, (chinese+english+math) AS total_score FROM student 
		where `name` = '关羽';
1
2

e. 查询英语成绩大于90的学生

SELECT * FROM student 
		where english > 90;
1
2

f. 查询总分大于200的所有学生

#1.不使用别名
SELECT * FROM student 
		where (chinese+english+math) > 200;
1
2
3

g. 查询math大于60并且id大于4的学生成绩

SELECT * FROM student 
		WHERE math > 60 AND id > 4;
1
2

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

SELECT * FROM student 
		WHERE english > chinese;
1
2

i. 查询总分大于200，并且 数学成绩小于语文成绩 的 姓赵的同学

SELECT * FROM student 
		WHERE (chinese+english+math) > 200 
		AND math < chinese 
		AND `name` LIKE '赵%';
1
2
3
4

j. 查询英语分数在80~90的同学

SELECT * FROM student 
		WHERE english >= 80 AND english <= 90;
1
2

#between .. and .. 是闭区间，包括两端
SELECT * FROM student 
		WHERE english BETWEEN 80 AND 90;
1
2
3

k. 查询数学分数在89，90，91的同学

SELECT * FROM student 
		WHERE math IN(89,90,91);
1
2

SELECT * FROM student
		WHERE math = 89 OR math = 90 OR math = 91;
1
2

l. 查询所有姓韩或者姓宋的同学的成绩

SELECT * FROM student
		WHERE `name` LIKE '韩%' OR `name` LIKE '宋%';
1
2

j. 查询第三个字符为平的同学的成绩

SELECT * FROM student
	WHERE `name` LIKE '__平';
1
2

⑥、order by排序：

#sql语法
SELECT 列名 FROM 表名 
	WHERE 筛选条件
	ORDER BY 排序列 asc/desc;
	
#1. order by指定的排序列，可以是表中原有的列，也可以是select设置的新列的别名
#2. asc升序 desc降序
#3. order by字句应该位于select语句的末尾
1
2
3
4
5
6
7
8

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

SELECT * FROM student ORDER BY math ASC;
1

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

SELECT * ,(chinese+english+math) AS total_score FROM student 
		ORDER BY total_score DESC;
1
2

c.对姓韩的学生的总成绩排序输出

SELECT * FROM student 
		WHERE `name` LIKE '韩%' 
		ORDER BY (chinese+english+math) ASC;
1
2
3

5. 合计/统计函数：

①、COUNT()：

#1. 返回表中数据的条数，即使某条数据的包含NULL
SELECT COUNT(*) FROM table_name;

#2. 返回指定列不为NULL的数据的条数
SELECT COUNT(column_name) FROM table_name;

#3. 返回指定列的不同值的数目
SELECT COUNT(DISTINCT column_name) FROM table_name;
1
2
3
4
5
6
7
8

②、SUM():

#1. 计算表中指定列的总和，如果没有分组，则只返回一条数据
SELECT SUM(column_name) FROM table_name;

#2. 如果统计的列不是数值类型，则无意义，返回0
1
2
3
4

③、AVG():

#1. 返回数值列的平均值，如果没有分组，则只返回一条数据
SELECT AVG(column_name) FROM table_name
1
2

④、MAX() MIN():

#1. MAX()函数返回指定列的最大值
SELECT MAX(column_name) FROM table_name;

#2. MIN() 函数返回指定列的最小值
SELECT MIN(column_name) FROM table_name;
1
2
3
4
5

⑤、使用group by字句对列进行分组查询：

#1. GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
SELECT column_name, aggregate_function(column_name)
	FROM table_name
	WHERE column_name operator value
	GROUP BY column_name;
1
2
3
4
5

⑥、使用HAVING 子句筛选分组后的各组数据：

#1. 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据
SELECT column_name, aggregate_function(column_name)
	FROM table_name
	WHERE column_name operator value
	GROUP BY column_name
	HAVING aggregate_function(column_name) operator value
1
2
3
4
5
6

⑦、练习：

a. 建表

#创建dept部门表
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 NOT NULL DEFAULT 0,/*上级编号*/
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',7223,'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);

SHOW TABLES;

DESC emp;
DESC dept;
DESC salgrade;

SELECT * FROM dept;
SELECT * FROM emp;
SELECT * FROM salgrade;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

b. 查询emp表：

SELECT * FROM emp;
1

c. 根据deptno列分组，求各组的平均sal：

SELECT AVG(sal), deptno FROM emp 
	GROUP BY deptno;
1
2

d. 显示每个部门的平均工资和最高工资：

SELECT deptno, AVG(sal), MAX(sal) FROM emp
	GROUP BY deptno;
1
2

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

#1. 先按deptno分组，再对jbo进行分组
SELECT deptno, job, AVG(sal), MIN(sal) FROM emp
		GROUP BY deptno, job;
1
2
3

g.显示平均工资高于2000的部门号和它的平均工资【别名】：

SELECT deptno, AVG(sal) FROM emp 
	GROUP BY deptno 
	HAVING AVG(sal) > 2000;
	
或
SELECT deptno, AVG(sal) AS avg_sal FROM emp 
	GROUP BY deptno 
	HAVING avg_sal > 2000;
1
2
3
4
5
6
7
8

6. 字符串相关函数：

h. 显示所有员工的名字，首字母大写:

#1. SUBSTR截取字符串下标是中1开始的，而不是从0开始
SELECT ename, CONCAT( LEFT(ename,1), SUBSTR( LCASE(ename),2 ) ) FROM emp;

#2. 
SELECT ename, CONCAT( UCASE(SUBSTR(ename, 1, 1)), LCASE(SUBSTR(ename, 2)) ) FROM emp;
1
2
3
4
5

7.数学相关函数：

8.日期相关函数：

9.加密函数：

10.流程控制函数:

11.分页查询：

#第一条数据的下标最小为0 limit后面的表达式应计算出来数字
SELECT * FROM 表名
	WHERE 筛选条件
	LIMIT 获取到的第一条数据的下标 获取数据的条数
1
2
3
4

• 常用公式：

SELECT * FROM 表名
	WHERE 筛选条件
	LIMIT 每页显示的条数*(页数-1), 每页显示的条数
	
#第2页，每页3条数据 limit 3*(2-1), 3
SELECT * FROM student
	WHERE math > 40
	LIMIT 3, 3
1
2
3
4
5
6
7
8

12.多表查询：

#一般是先直接查询多张表，再对多表的笛卡尔集利用where进行过滤，筛选出需要的数据
SELECT 要查询的列 FROM 表1,表2
	WHERE 筛选条件
1
2
3

emp雇员表：

dept部门表：

salgrade工资级别表：

a. 显示雇员名,雇员工资及所在部门的名字

#多表查询，查询的列如果不唯一，则需要指定是哪个表的列，否则可以不用
SELECT ename, emp.deptno FROM emp,dept
	WHERE emp.deptno = dept.deptno;
1
2
3

b. 显示部门号为 10 的部门名、员工名和工资

SELECT dname AS '部门名', ename, sal
	FROM emp, dept
	WHERE emp.deptno = 10 AND emp.deptno = dept.deptno;
1
2
3

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

SELECT ename, sal, grade
	FROM emp, salgrade
	WHERE sal BETWEEN losal AND hisal;
1
2
3

13.自连接：

• 自连接，本表字段与本表字段相关联，将一张表拆成两张表进行多表查询
• 需要对表起别名进行查询和过滤

d. 显示公司员工名字和他的上级的名字

SELECT worker.ename AS '员工', boss.ename AS '上级'
	FROM emp worker, emp boss
	WHERE worker.mgr = boss.empno;
1
2
3

14.子查询：

①、概念

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

②、单行子查询：

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

e.如何显示与 SMITH 同一部门的所有员工?

#1. 先找到SMITH的部门,返回SMITH的部门号，仅一行数据
SELECT deptno FROM emp 
	WHERE ename = 'SMITH';
#2. 查询emp表，将子查询语句嵌套进去作为筛选条件
SELECT * FROM emp
	WHERE deptno = (SELECT deptno FROM emp 
		WHERE ename = 'SMITH')
		AND ename != 'SMITH';
1
2
3
4
5
6
7
8

③、多行子查询：

多行子查询是指返回多行数据的子查询，晒选条件用 in

f.如何查询和部门编号为10的工作相同的雇员的所有信息，但是不包含部门10的雇员

#1. 先查询部门10中有的所有工作
SELECT DISTINCT job FROM emp
	WHERE deptno = 10;
1
2
3

#2. 再查询emp表，将子查询语句的结果作为筛选条件
SELECT * FROM emp 
	WHERE job IN (SELECT DISTINCT job FROM emp
	WHERE deptno = 10)
	AND deptno != 10;
1
2
3
4
5

④、在多行子查询中使用all或者any进行筛选：

g.显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号

#1. 先查询部门编号为30的所有员工的工资
SELECT sal FROM emp 
	WHERE deptno = 30;
1
2
3

#2. 利用子查询结果，用ALL进行筛选
SELECT ename, sal, deptno FROM emp
	WHERE sal > ALL (SELECT sal FROM emp 
	WHERE deptno = 30);
	
#3. 也先利用MAX函数查询出部门30的最大工资，再进行where筛选
SELECT ename, sal, deptno FROM emp
	WHERE sal > (SELECt MAX(sal) FROM emp
  WHERE deptno = 30);
1
2
3
4
5
6
7
8
9

h.显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号

#1. 利用子查询结果，用ANY进行筛选
SELECT ename, sal, deptno FROM emp
	WHERE sal > ANY (SELECT sal FROM emp 
	WHERE deptno = 30);
	
#2. 也先利用MAX函数查询出部门30的最大工资，再进行where筛选
SELECT ename, sal, deptno FROM emp
	WHERE sal > (SELECT MIN(sal) FROM emp
  WHERE deptno = 30);
1
2
3
4
5
6
7
8
9

⑤、多列子查询：

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

i.查询与ALLEN的部门可岗位完全相同的所有雇员：

#1. 先查询ALLEN的部门和岗位
SELECT deptno, job FROM emp 
	WHERE ename = 'ALLEN';
1
2
3

#2. 再根据查询结果对emp表再查询
SELECT * FROM emp
	WHERE (deptno, job) = (SELECT deptno, job FROM emp 
	WHERE ename = 'ALLEN')
	AND ename <> 'ALLEN';
1
2
3
4
5

⑥、子查询作为临时表：

ecs_goods表：

a.查询 ecshop 中各个类别中，价格最高的商品:

#1. 先查询ecs_goods中的各个类别中的商品
SELECT cat_id, MAX(shop_price) FROM ecs_goods
	GROUP BY cat_id;
1
2
3

#2. 再将子查询的表作为临时表，与ecs_goods表笛卡尔集，再筛选
SELECT goods_id, ecs_goods.cat_id, goods_name, shop_price
	FROM (SELECT cat_id, MAX(shop_price) AS max_sal FROM ecs_goods
	GROUP BY cat_id) tmp, ecs_goods
	WHERE ecs_goods.cat_id = tmp.cat_id
	AND ecs_goods.shop_price = tmp.max_sal;
1
2
3
4
5
6

j.查找每个部门工资高于本部门平均工资的人的资料

#1. 先查询每个部门的平均工资
SELECT deptno, AVG(sal) FROM emp 
	GROUP BY deptno;
1
2
3

#2. 根据临时表的笛卡尔集进行条件筛选
SELECT emp.* FROM emp, (SELECT deptno, AVG(sal) AS avg_sal 
	FROM emp GROUP BY deptno) tmp
	WHERE emp.deptno = tmp.deptno 
	AND emp.sal > tmp.avg_sal;
1
2
3
4
5

k.查找每个部门工资最高的人的详细资料

#1. 先查找每个部门的最高工资
SELECT MAX(sal) max_sal FROM emp 
	GROUP BY deptno;

#2. 再根据子查询的表进行笛卡尔集进行条件筛选
SELECT emp.* FROM emp, (SELECT deptno, MAX(sal) AS max_sal FROM emp 
	GROUP BY deptno) tmp
	WHERE emp.deptno = tmp.deptno
	AND emp.sal = tmp.max_sal;
1
2
3
4
5
6
7
8
9

l.查询每个部门的信息(包括：部门名,编号,地址)和人员数量

#1. 先查询每个部门的编号及对应人数
SELECT deptno, COUNT(DISTINCT ename) FROM emp
	GROUP BY deptno;
 
#2. 再根据子查询结果笛卡尔集进行筛选
SELECT tmp.deptno, dname, loc, tmp.person_num  
	FROM dept, (SELECT deptno, COUNT(DISTINCT ename) AS person_num FROM emp
	GROUP BY deptno) tmp
	WHERE dept.deptno = tmp.deptno;
1
2
3
4
5
6
7
8
9

15.表复制：

①、自我复制(蠕虫复制)：

#将查询本表的数据，将查询结果插入本表
INSERT INTO 表名 
	SELECT * FROM 表名;
1
2
3

②、删除表的重复记录：

#1. 创建一张新表，结构跟原表相同
CREATE TABLE 复制表 LIKE 原表;
#2. 将原表的数据用distinct去重，插入到复制表中
INSERT INTO 复制表 
	SELECT DISTINCT * FROM 原表
#3. 丢弃掉原表
DROP TABLE 原表
#4. 修改复制表的表名为原表
RENAME 复制表名 TO 原表名
1
2
3
4
5
6
7
8
9

16.合并查询：

①、概念：

有时候在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union、union all 进行数据集合并

②、union all:

#union all 不会自动去掉重复数据行
select * from 表1 where 条件1
union all
select * from 表1 where 条件2
1
2
3
4

③、union:

#union 会自动去重重复数据行
select * from 表1 where 条件1
union
select * from 表1 where 条件2
1
2
3
4

17.表的左右外连接：

①、概念：

多表查询时，两张表形成笛卡尔集时，根据关联条件查询结果时，默认匹配不上的，不会显示。如果希望匹配不上的也可以作为查询结果，可以使用左外连接显示左表数据，右表数据为空；或者使用右外连接显示右表，左表数据为空

#学生表
CREATE TABLE stu(
	id INT,
  `name` VARCHAR(32)
);

INSERT INTO stu VALUES (1,'张三'), (2,'李四'), (3，'王五');

SELECT * FROM stu;
1
2
3
4
5
6
7
8
9

#成绩表
CREATE TABLE score(
	id INT,
  math INT
);

INSERT INTO score VALUES (1, 56), (2, 77),(4,80);

SELECT * FROM score;
1
2
3
4
5
6
7
8
9

②、原始多表查询：

#匹配不上的数据不会显示
SELECT stu.id, `name`, math FROM stu, score
	WHERE stu.id = score.id;
1
2
3

③、左外连接：

#sql语句
SELECT * FROM 表1 LEFT JOIN 表2
	ON 筛选条件；

#查询所有学生的数学成绩，及时该学生的数学成绩不存在
SELECT stu.id, `name`, math 
	FROM stu LEFT JOIN score
	ON stu.id = score.id;
1
2
3
4
5
6
7
8

④、右外连接：

#sql语句
SELECT * FROM 表1 RIGHT JOIN 表2
	ON 筛选条件;

#查询所有成绩对应的学生信息，即使该学生不存在
SELECT stu.id, `name`, math 
	FROM stu RIGHT JOIN score
	ON stu.id = score.id;
1
2
3
4
5
6
7
8

九、约束：

1.概念：

约束用于确保数据库的数据满足特定的商业规则。

主要是设置用于限制表数据的插入或者删除的规则，作用于字段

• 在mysql中，约束包括六种：

  • NOT NULL - 指示某列不能存储 NULL 值。

  • UNIQUE - 保证某列的每行必须有唯一的值。

  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

  • CHECK - 保证列中的值符合指定的条件。

  • DEFAULT - 规定没有给列赋值时的默认值。

2.primary key 主键约束：

#主键的作用是唯一且非空
#每张表只有一个主键，或者一个联合主键
#设置主键的方式有两种，一种是在单个字段后添加primary key，另一种是在表末尾添加primary key(列)，可以是多个列作为联合主键
#方式一
CREATE TABLE score(
	id INT PRIMARY KEY, -- 单个字段作为主键，如果新插入的数据与原表的id字段值相同，则插入失败
	math INT
);

#方式二
CREATE TABLE score(
	id INT,
	math INT，
  PRIMARY KEY(id,math) -- 两个字段作为联合主键，如果新插入的数据与原表的某条数据id和math字段值都相同，则插入失败
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3.not null 非空约束：

#在某字段后添加非空约束之后，插入该字段的数据不能为空
1

4.unique 唯一约束：

#当定义了唯一约束之后，该列的值是不能重复的
#需要注意，如果该字段后面没有指定not null, 则unique字段可以有多个null
#一张表可以由多个unique字段
#如果一个字段定义了not null unique， 则相当于primary key
1
2
3
4

5.check 校验约束：

#check用于强制数据行必须满足check设置的条件，假定在sal列上定义了check约束，并要求在sal列值在1000~2000之间，如果插入数据不符合check的条件，则会提示出错
#注意：oracle 和 sql server 均支持check约束，但是mysql 5.7目前还不支持 check约束，但会做语法校验，但不会实际生效

#eg:
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) 
);
1
2
3
4
5
6
7
8
9
10

6.default 默认约束：

#DEFAULT 约束用于向列中插入默认值。
#如果没有规定其他的值，那么会将默认值添加到所有的新记录
1
2

7.foreign key 外键约束：

①、概念：

用于定义主表和从表之间的关系：外键约束作用于从表上，主表则必须具有primary key约束或者unique约束

当定义外键约束后，要求外键列数据必须在主表的主键列存在或者是为null

②、定义外键关系：

#在从表定义时，在末尾添加字段 FOREIGN KEY(从表外键列名) REFERENCES 主表名(主键列名或unique约束列名)
#注意froeign key的外键列一定要加括号
#当定义了主表从表的外键关系之后，如果从表的外键列中有数据，则无法删除与外键列相对应的主键列的数据，只能先删除从表的所有关联主键列的数据，才可以删除对应主键列数据
#外键与主键的字段类型要一致，但长度可以不同
1
2
3
4

③、示例：

#1.创建主表：学生表stu
#定义id为主键列
CREATE TABLE stu(
	id INT PRIMARY KEY,
  `name` VARCHAR(32)
);

#2. 插入值
INSERT INTO stu VALUES (1,'张三'), (2,'李四');
select * from stu;
1
2
3
4
5
6
7
8
9
10

#3.创建从表：成绩表score
CREATE TABLE score(
	id INT,
  math INT,
  FOREIGN KEY id REFERENCES stu(id)
);

#4. 尝试往从表中插入数据 成功， 因为id字段为1的数据在主表的主键列中存在
INSERT INTO score VALUES (1, 57);

#5. 尝试在从表中插入主表的主键列不存在的数据 失败，因为id为3的字段在主表中不存在
INSERT INTO score VALUES (3, 70);
--------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：INSERT INTO score VALUES (3, 70)

错误代码： 1452
Cannot add or update a child row: a foreign key constraint fails (`kevin03`.`score`, CONSTRAINT `score_ibfk_1` FOREIGN KEY (`id`) REFERENCES `stu` (`id`))

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0.006 sec

#5. 尝试删除主键列中在从表外键列还关联着的数据行 失败，因为在从表中id为1的外键列数据还存在，所有主表关联的数据行无法被先删除
#可以先删除从表关联对应主键列的所有数据行，再删除主表对应的主键列数据行
DELETE FROM stu WHERE id = 1;
--------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：DELETE FROM stu WHERE id = 1

错误代码： 1451
Cannot delete or update a parent row: a foreign key constraint fails (`kevin03`.`score`, CONSTRAINT `score_ibfk_1` FOREIGN KEY (`id`) REFERENCES `stu` (`id`))

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0.006 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

8.约束建表练习：

①、需求：

②、sql代码：

#商品表goods
CREATE TABLE goods(
	goods_id INT PRIMARY KEY,
	goods_name VARCHAR(32) NOT NULL DEFAULT '',
	unitprice DOUBLE NOT NULL DEFAULT 0 CHECK (unitprice BETWEEN 1.0 AND 9999.99),
	catagory VARCHAR(32) NOT NULL DEFAULT '',
	provider VARCHAR(255) NOT NULL DEFAULT ''
);

#客户表customer
CREATE TABLE customer(
	customer_id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL,
	address VARCHAR(255),
	email VARCHAR(32) UNIQUE,
	sex CHAR(1) CHECK (sex = '男' OR sex = '女'),
	card_id CHAR(11)
);

#购买表purchase
CREATE TABLE purchase(
	order_id CHAR(32) PRIMARY KEY,
	customer_id INT NOT NULL,
	goods_id INT NOT NULL,
  nums INT NOT NULL DEFAULT 0,
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
	FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28