【資料庫基礎】01_資料庫概述與MySQL文法基礎1. 資料庫應用2. SQL文法概述3. MySQL簡單的使用4. DQL 資料查詢語言基礎5. 限制 （CONSTRAINTS）索引 index

1. 数据库应用

1.1 概念

1.1.1 什么是数据库

简而言之，就是存储数据，管理数据的仓库。

数据库的好处

• 持久化数据到本地。
• 可以实现结构化查询，方便管理。

DB：数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。

DBMS：数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器。

SQL：结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言。

常见的数据库管理系统分为：

• 关系型数据库， Oracle、MySQL、SQLServer、Access

• 非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

    

  

    

1.1.2 关系型和非关系型

  早期发展的数据库建立在数据的紧密关系基础之上（如：父子关系、师生关系），我们称其为关系型数据库，也称为传统数据库；现今数据库建立在数据的松散关系基础之上（如：中国人和美国人、中国人和印度人、视频、音频），我们称其为非关系型数据库nosql（not only sql）。业界总在争论nosql能否干掉传统数据库，很多初学者也有这个困惑。以我来看，两者没有矛盾，它们各有特点，根据业务情况互补才是真谛。但总的来说原来关系型数据库一统天下的格局早被打破，领土不断被蚕食，规模一再的缩小，虽然无法全面被替代，但却早已风光不在，沦落到一偶之地，Oracle的衰落就是最好的证明，早期只要是全球大企业无一例外都是部署Oracle，但现在都在去Oracle化，阿里就已经全面排斥Oracle。

1.1.3 关系型数据库

  关系型数据库有特定的组织方式，其以行和列的形式存储数据，以便于用户理解。

  关系数据库的表采用二维表格来存储数据，是一种按行与列排列的具有相关信息的逻辑组，它类似于 Excle 工作表。一个数据库可以包含任意多个数据表。表中的一行即为一条记录。数据表中的每一列称为一个字段，表是由其包含的各种字段定义的，每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计。创建数据表时，为每个字段分配一个数据类型，定义它们的数据长度和其他属性。行和列的交叉位置表示某个属性值，如“数据库原理”就是课程名称的属性值。

  关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。

  

  

1.2 数据库存储数据的特点

1）将数据放到表中，表再放到库中。

2）一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。

3）表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。

4）表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的“属性”。

5）表中的数据是按行存储的，每一行类似于java中的“对象”。

  

2. SQL语法概述

2.1 概述

  结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ “S-Q-L”)，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

  SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。

2.2 SQL语言分类

DQL（Data Query Language）：数据查询语言 （SELECT）

DML（Data Manipulation Language)

  数据操纵语句，用于查询与修改数据记录用于添加、删除、修改、查询数据库记录（简称CRUD），并检查数据完整性，包括如下SQL语句：

• INSERT：添加数据到数据库中
• UPDATE：修改数据库中的数据
• DELETE：删除数据库中的数据
• SELECT：选择（查询）数据 (SELECT是SQL语言的基础，最为重要)

DDL（Data Definition Languge）

  数据定义语言，用于定义数据库的结构，比如创建、修改或删除数据库对象，包括如下SQL语句：

• CREATE TABLE：创建数据库表
• ALTER TABLE：更改表结构、添加、删除、修改列长度
• DROP TABLE：删除表
• CREATE INDEX：在表上建立索引
• DROP INDEX：删除索引

DCL（Data Control Language）

  数据库控制语言，用于定义用户的访问权限和安全级别。只有管理员才有相应的权限，包括如下SQL语句：

• GRANT：授予访问权限
• REVOKE：撤销访问权限
• COMMIT：提交事务处理
• ROLLBACK：事务处理回退
• SAVEPOINT：设置保存点
• LOCK：对数据库的特定部分进行锁定

2.3 SQL 的语言规范

• 必须只能包含 A–Z，a–z，0–9， 共 63 个字符
• 字段名必须以字母开头，尽量不要使用拼音，多个单词用下划线隔开，而非java语言的驼峰规则。
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突（如where,order,group）
• 必须不能和用户定义的其他对象重名
• 不能在对象名的字符间留空格
• 值，除了数值型，字符串型和日期时间类型使用单引号（’’）
• 别名，尽量使用双引号（“”），而且不建议省略 as
• 所有标点符号使用英文状态下的半角输入方式
• 必须保证所有()、单引号、双引号是成对结束的
• 数据库、表名不得超过 30 个字符，变量名限制为 29 个（不同数据库，不同版本会有不同）
• 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了
• 在命令行中的要求：一个语句可以分开多行编写，以;或\g 结束
• mysql 对于 SQL 语句不区分大小写，SQL 语句关键字尽量大写。

• 可以使用

  1）#单行注释

  2）–空格单行注释

  3）

2.4 MySQL 的数据类型

常用的数据类型有：

• 整型（xxxint）
• 位类型(bit)
• 浮点型（float 和 double、real）
• 定点数（decimal,numeric）
• 日期时间类型（date,time,datetime,year）
• 字符串（char,varchar,xxxtext）
• 二进制数据（xxxBlob、xxbinary）
• 枚举（enum）
• 集合（set）
• 图片

1）整型

整数列的可选属性有三个：

• M: 宽度(在 0 填充的时候才有意义，否则不需要指定)。
• unsigned: 无符号类型(非负)。
• zerofill: 0 填充,(如果某列是 zerofill，那么默认就是无符号)，如果指定了 zerofill 只是表示不够 M 位时，用 0 在左边填充，如果超过 M 位，只要不超过数据存储范围即可。

原来，在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用4 bytes

2）浮点型

  对于浮点列类型，在 MySQL 中单精度值使用 4 个字节，双精度值使用 8 个字节。

• MySQL 允许使用非标准语法（其他数据库未必支持，因此如果设计到数据迁移，则最好不要这么用）：FLOAT(M,D)或 DOUBLE(M,D)。这里，(M,D)表示该值一共显示 M 位，其中 D 表示小数点后几位，M 和 D 又称为精度和标度。

  例如，定义为 FLOAT(5,2)的一个列可以显示为-999.99-999.99。M 取值范围为 0-255。D 取值范围为 0-30，同时必须<=M。

• 如果存储时，整数部分超出了范围（如上面的例子中，添加数值为 1000.01），MySql 就会报错，不允许存这样的值。如果存储时，小数点部分若超出范围，就分以下情况：若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存，例如在FLOAT(5,2)列内插入999.009，近似结果是999.01。

  若四舍五入后，整数部分超出范围，则 MySql 报错，并拒绝处理。如 999.995 和-999.995 都会报错。

• 说明：小数类型，也可以加 unsigned，但是不会改变数据范围，例如：float(3,2) unsigned 仍然只能表示 0-9.99的范围。
• float 和 double 在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示

• REAL 就是 DOUBLE ，如果 SQL 服务器模式包括 REAL_AS_FLOAT 选项，REAL 是 FLOAT 的同义词而不是 DOUBLE

  的同义词。

  注意：在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用“=”来

判断两个数是否相等。如果希望保证值比较准确，推荐使用定点数数据类型。

3）位类型（了解）

  BIT 数据类型可用来保存位字段值。BIT(M)类型允许存储 M 位值。M 范围为 1~64，默认为 1。

  BIT 其实就是存入二进制的值，类似 010110。如果存入一个 BIT 类型的值，位数少于 M 值，则左补 0。如果存入一个 BIT 类型的值，位数多于 M 值，MySQL 的操作取决于此时有效的 SQL 模式：如果模式未设置，MySQL 将值裁剪到范围的相应端点，并保存裁减好的值。如果模式设置为 traditional(“严格模式”)，超出范围的值将被拒绝并提示错误，并且根据 SQL 标准插入会失败。

  对于位字段，直接使用 SELECT 命令将不会看到结果，可以用 bin()或 hex()函数进行读取。

4）定点型

• DECIMAL 在 MySQL 内部以字符串形式存放，比浮点数更精确。定点类型占 M+2 个字节
• DECIMAL(M,D)与浮点型一样处理规则。M 的取值范围为 0-65，D 的取值范围为 0-30，而且必须<=M，超出范围会报错。
• DECIMAL 如果指定精度时，默认的整数位是 10，默认的小数位为 0。

5）日期时间类型

• 对于 year 类型，输入的是两位，“00-68”表示 2000-2069 年，“70-99”表示 1970-1999 年。记比较麻烦，建议使用 4 位标准格式。

• ‘YYYY-MM-DD HH:MM:SS’或’YY-MM-DD HH:MM:SS’，'YYYY-MM-DD’或’YY-MM-DD’格式的字符串。（允许“不严格”）

  语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，‘98-12-31 11:30:45’、‘98.12.31 11+30+45’、‘98/12/31 113045’ 和 '[email protected]@31 11|30|45’是等价的。

• ‘YYYYMMDD’或’YYMMDD’格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，‘19970523’ 和’970523’被解释为 ‘1997-05-23’，但’971332’是不合法的(它有一个没有意义的月和日部分)，将变为’0000-00-00’。

• 对于包括日期部分间割符的字符串值，如果日和月的值小于 10，不需要指定两位数。‘1979-6-9’与’1979-06-09’ 是相同的。同样，对于包括时间部分间割符的字符串值，如果时、分和秒的值小于 10，不需要指定两位数。

  '1979-10-30 1:2:3’与’1979-10-30 01:02:03’相同。

• 数字值应为 6、8、12 或者 14 位长。如果一个数值是 8 或 14 位长，则假定为 YYYYMMDD 或 YYYYMMDDHHMMSS格式，前 4 位数表示年。如果数字 是 6 或 12 位长，则假定为 YYMMDD 或 YYMMDDHHMMSS 格式，前 2 位数表示年。其它数字被解释为仿佛用零填充到了最近的长度。
• 一般存注册时间、商品发布时间等，不建议使用 datetime 存储，而是使用时间戳，因为 datetime 虽然直观，但不便于计算。而且 timestamp 还有一个重要特点，就是和时区有关。

6）字符

char，varchar，text 的区别

char 是一种固定长度的类型，varchar 则是一种可变长度的类型，它们的区别是：

• char 如果不指定(M)则表示长度默认是 1 个字符。varchar 必须指定(M)。
• char(M)类型的数据列里（最多容纳2000个字符），每个值都占用 M 个字符，如果某个长度小于 M，MySQL 就会在它的右边用空格字符补足。（例如，char(11)存储abc，占11位。在检索操作中那些填补出来的空格字符将被去掉，如果存入时右边本身就带空格，检索时也会被去掉）
• 在 varchar(M)类型的数据列里（最多容纳4000个字符），每个值只占用刚好够用的字符再加上一个到两个用来记录其长度的字节。（例如，varchar(11)存储abc，只占3位。以utf8编码计算的话，一个汉字在u8下占3个字节。即总长度为 L 字符+1/2 字字节，在Oracle中，为varchar2）

  由于某种原因 char 固定长度，所以在处理速度上要比 varchar 快速很多，但相对费存储空间，所以对存储不大，但在速度上有要求的可以使用 char 类型，反之可以用 varchar 类型来实例。

• text 文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用 char，varchar来代替。还有 text 类型不用加默认值，加了也没用。

注：不同数据库版本长度限制可能会有不同

哪些情况使用 char 更好?

1. 存储很短的信息，比如门牌号码 101，201……这样很短的信息应该用 char，因为 varchar 还要占个 byte 用于存储信息长度，本来打算节约存储的现在得不偿失。
2. 固定长度的，比如使用 uuid 作为主键，那用 char 应该更合适。因为他固定长度，varchar 动态根据长度的特性就消失了，而且还要占个长度信息。
3. 十分频繁改变的 column。因为 varchar 每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于 char 来说是不需要的。
4. MyISAM 和 MEMORY 存储引擎中无论使用 char 还是 varchar 其实都是作为 char 类型处理的。除此之外，建议使用 varchar 类型。特别是 InnoDB 存储引擎。

7）二进制值类型（了解）

包括：xxxBLOB 和 xxxBINARY

  BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR 类型，但是不同的是，它们存储的不是字符字符串，而是二进制串。所以它们没有字符集，并且排序和比较基于列值字节的数值值。当保存 BINARY(M)值时，在它们右边填充 0x00(零字节)值以达到指定长度。取值时不删除尾部的字节。比较时所有字节很重要（因为空格和 0x00 是不同的，0x00<空格），包括 ORDER BY 和 DISTINCT 操作。比如插入’a ‘会变成’a \0’。

  BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB和 LONGBLOB。它们只是可容纳值的最大长度不同。分别与四种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT对应有相同的最大长度和存储需求。在 TEXT 或 BLOB 列的存储或检索过程中，不存在大小写转换。BLOB 和 TEXT 列不能有默认值。BLOB 或 TEXT 对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet 变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。

8）枚举（ENUM）

MySql 中的 ENUM 是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值：

• 可以插入空字符串""和 NULL（如果运行 NULL 的话）。
• 如果你将一个非法值插入 ENUM(也就是说，允许的值列之外的字符串)，如果是严格模式，将不能插入，如果是非严格模式，将选用第一个元素代替，并警告。
• ENUM 最多可以有 65,535 个成员，需要 2 个字节存储。
• 当创建表时，ENUM 成员值的尾部空格将自动被删除。

值的索引规则如下：

• 来自列规定的允许的值列中的值从 1 开始编号。
• 空字符串错误值的索引值是 0。
• NULL 值的索引是 NULL。

9）集合（SET）

• SET 和 ENUM 类型非常类似，也是一个字符串对象，里面包含 0~64 个成员。
• SET 和 ENUM 存储上有所不同，SET 是根据成员的个数决定存储的字节数。
• SET 和 ENUM 最主要的区别在于 SET 类型一次可以选择多个成员，而 ENUM 则只能选择一个。

10）特殊的 NULL 类型

Null 类型特征：

1. 所有的类型的值都可以是 null，包括 int、float 等数据类型
2. 空字符串””，不等于 null，0 也不等于 null，false 也不等于 null
3. 任何运算符,判断符碰到 NULL,都得 NULL
4. NULL 的判断只能用 is null,is not null
5. NULL 影响查询速度,一般避免使值为 NULL

为什么建表时,加 not null default ‘’ / default 0

• 不想让表中出现 null 值. 为什么不想要的 null 的值。
• 不好比较,null 是一种类型，比较时，只能用专门的 is null 和 is not null 来比较。 碰到运算符，一律返回 null。
• 效率不高，影响提高索引效果。 因此，我们往往在建表时 not null default ‘’/

11）图片

  blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

2.5 MySQL 的运算符

（1）算术运算符：+ - * /（除也可以写成 div) %（取模可以写成 mod）

（2）比较运算符：= > >= < <= !=（不等于还可以写成<>） <=>（安全等于）

（3）逻辑运算符：&&（逻辑与也可以写成 and） ||（逻辑或也可以写成 or） not（逻辑非）

（4）范围：表达式 between … and … （也可以写成 表达式>=… and 表达式 <=…）

     表达式 not between … and …（也可以写成 表达式<… || 表达式 >…）

（5）集合：in (值，值，值…) not in(值，值，值…)

（6）模糊查询：LIKE NOT LIKE，通配符：%表示 0-n 个字符，_下划线代表一个字符

（7）位运算符：&（按位与） |（按位或）^（按位异或）~（按位取反）>>（右移）<<（左移）

（8）NULL 值判断，is null 或 is not null，如果使用 null=null，null<>null,null=0,null<>0,null=false 等都不对。不过 xxx is null 可以使用 xxx <=> null ，xxx is not null 可以写成 not xxx <=> null

结论：所有的运算符遇到 NULL 结果都是 NULL，除了<=>

#NULL 值判断与处理
#查询奖金百分比不为空的员工编号
SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NOT NULL;
#查询奖金百分比为空的员工编号
SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NULL;
#关于 null 值计算
#所有运算符遇到 null 都是 null
#计算实际的薪资： basic_salary + salary * 奖金百分比
#函数：IFNULL(表达式,用什么值代替)
SELECT eid,basic_salary + performance_salary *(1+ commission_pct) FROM t_salary;#错误的
SELECT eid,basic_salary + performance_salary *(1+ IFNULL(commission_pct,0)) FROM t_salary;
#<=>安全等于
#查询奖金百分比为空的员工编号
SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> NULL;
           

3. MySQL简单的使用

  MySQL 是一种开放源代码的关系型数据库管理系统，开发者为瑞典 MySQL AB 公司。在 2008 年 1 月 16 号被 Sun公司收购。而 2009 年，SUN 又被 Oracle 收购。目前 MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL 作为网站数据库（Facebook, Twitter, YouTube）。阿里提出“去 IOE”，更多网站也开始选择 MySQL。

3.1 MySQL服务的启动和停止

  关系型数据库分为桌面文件共享型数据库，例如 Access，和 C/S 架构的网络共享型数据库，例如：MySQL，Oracle等。MySQL 软件的服务器端必须先启动，客户端才可以连接和使用使用数据库。

  

方式一：图形化方式

• “我的电脑/计算机”–>右键–>“管理”–>“服务”–>启动和关闭 MySQL
• “开始菜单”–>“控制面板”–>“管理工具”–>“服务”–>启动和关闭 MySQL
• “任务管理器”–>“服务”–>启动和关闭 MySQL

方式二：通过管理员身份运行

• net start 服务名（启动服务）
• net stop 服务名（停止服务）

3.2 MySQL服务的登录和退出

方式一：通过mysql自带的客户端（只限于root用户）

方式二：通过windows自带的客户端

• 登录：mysql -h 主机名 -P 端口号 -u 用户名 -p 密码

  例如：mysql -h localhost -P 3306 -u root -proo

• 退出：exit或ctrl+C

3.3 MySQL的常见命令

注 意

• SQL 语言大小写不敏感。

• SQL 可以写在一行或者多行

• 关键字不能被缩写也不能分行

• 各子句一般要分行写。

• 使用缩进提高语句的可读性。

3.3.1 查看服务器的版本

• 方式一：登录到mysql服务端

select version();

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)
           

• 方式二：没有登录到mysql服务端

user当前连接用户

3.3.2 数据库的常见操作

1）创建数据库

create database 库名 DEFAULT CHARACTER SET utf8;(设置默认字符集 UTF-8)

mysql> create database cgb DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.02 sec)
           

2）删除数据库库

drop database 库名;

mysql> drop database cgb;
Query OK, 0 rows affected (0.04 sec)
           

3）查看当前所有的数据库

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| cgb2015            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
           

4）打开指定的库

use 库名;

mysql> use cgb2015;
Database changed
           

5）查看当前正在使用哪个数据库

select database();

mysql> select database();
+------------+
| database() |
+------------+
| cgb2015    |
+------------+
1 row in set (0.00 sec)
           

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

3.3.3 表的常用操作

1）查看当前库的所有表

show tables;

mysql> show tables;
+-------------------+
| Tables_in_cgb2015 |
+-------------------+
| courses           |
| dept              |
| emp               |
| scores            |
| students          |
| tb_door           |
| tb_user           |
| tb_user_addr      |
| teachers          |
| test              |
+-------------------+
10 rows in set (0.01 sec)
           

2）查看指定库中的所有表

show tables from 库名;

mysql> show tables from mysql;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
35 rows in set (0.01 sec)
           

3）创建表

CREATE TABLE 表名称(
字段名 1 数据类型 1 主键 自增长, 
字段名 2 数据类型 2 非空 默认值,
字段名 3 数据类型 3
)
ENGINE=当前表格的引擎 
AUTO_INCREMENT=自增长的起始值 
DEFAULT CHARSET=表数据的默认字符;

create table tb_door(
id int primary key auto_increment,
door_name varchar(100),
tel varchar(50)
);
Query OK, 0 rows affected (0.04 sec)
           

4）查看指定表的结构

desc 表名;

mysql> desc tb_door;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| door_name | varchar(100) | YES  |     | NULL    |                |
| tel       | varchar(50)  | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
           

SHOW CREATE TABLE 表名(查看表的定义)

mysql> SHOW CREATE TABLE tb_door;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                          |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_door | CREATE TABLE `tb_door` (
  `id` int NOT NULL AUTO_INCREMENT,
  `door_name` varchar(100) DEFAULT NULL,
  `tel` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
           

5）删除表

drop table 表名;
注意：数据和结构都被删除

mysql> drop table tb_door;
Query OK, 0 rows affected (0.04 sec)
           

6）重命名表

alter table 表名 rename 新表名;
rename table 表名 to 新表名;

mysql> show tables;
+-------------------+
| Tables_in_cgb2015 |
+-------------------+
| courses           |
| dept              |
| emp               |
| scores            |
| students          |
| tb_door           |
| tb_user           |
| tb_user_addr      |
| teachers          |
| test              |
+-------------------+
10 rows in set (0.01 sec)

mysql> rename table test to data_test;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+-------------------+
| Tables_in_cgb2015 |
+-------------------+
| courses           |
| data_test         |
| dept              |
| emp               |
| scores            |
| students          |
| tb_door           |
| tb_user           |
| tb_user_addr      |
| teachers          |
+-------------------+
10 rows in set (0.00 sec)
           

3.3.4 表内的常用操作

1）向表中插入记录

insert into 表名 value(字段1属性,字段2属性,字段3属性,...);
#向表中添加指定字段记录，没有被指定的字段会添加默认值
insert into 表名(字段名1,字段名2,字段名3) value(字段1属性,字段2属性,字段3属性,...);

#向tb_door表中插入2条记录
mysql> insert into tb_door values(null,'永和大王1店',666);
mysql> insert into tb_door values(null,' 永和大王2店',888);
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
           

1、值的顺序、个数与字段列表中字段的顺序、个数一致

• 如果个数少了就报 Column count doesn’t match value count

• 如果 VALUES 前面的()中没有列出字段，那么默认就是为表中的所有字段赋值，那么个>数与顺序与表结构中字段定义的一致

   

2、关于自增长列，默认值列，允许为 NULL 列的赋值

 

（1）如果字段列表列出了字段名，那么值列表中就要为其赋值，哪怕它是自增长列，有默认值列，可以为 NULL 值的列。

• InnoDB 表的自动增长列可以手动插入合适的值，但是插入的值如果是 NULL 或者 0，则实际插入的将是自动增长后的值；
• 如果列声明了“默认约束”那么对应的位置可以赋值具体的值，也可以使用“DEFAULT”，表示使用默认值；

• 如果列允许了 NULL 值，那么可以为对应的字段可以赋值为具体值也可以赋值为 NULL

   

（2）对于没有列出的字段，像自增列就自动赋值，像默认值列就自动赋默认值，像允许 NULL 的列就自动赋 NULL值

 

3、VALUES 也可以写成 VALUE，但是 VALUES 是标准写法

4、可以同时插入多行

5、如果插入从表的数据，要注意查看主表参照字段的值是否存在

6、值的位置可以是常量值、表达式、函数

2）查询表中记录

select *（全表）/字段名（指定）from 表名;
mysql> select * from tb_door;
+----+-------------------+------+
| id | door_name         | tel  |
+----+-------------------+------+
|  1 | 永和大王1店       | 666  |
|  2 |  永和大王2店      | 888  |
|  3 | 永和大王1店       | 666  |
|  4 |  永和大王2店      | 888  |
+----+-------------------+------+
4 rows in set (0.00 sec)
           

• 如果 SELECT 后面是*，那么表示查询所有字段
• SELECT 后面的查询列表，可以是表中的字段，常量值，表达式，函数
• 查询的结果是一个虚拟的表
• select 语句，可以包含 5 种子句：依次是 where、 group by、having、 order by、limit 必须照这个顺序。

3）删除表中记录

Delete * from 表名 where 条件;

mysql> Delete from tb_door where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> Delete from tb_door where id=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_door;
+----+-------------------+------+
| id | door_name         | tel  |
+----+-------------------+------+
|  1 | 永和大王1店       | 666  |
|  2 |  永和大王2店      | 888  |
+----+-------------------+------+
2 rows in set (0.00 sec)
           

1、如果不加 where 条件，表示删除整张表的数据，表结构保留。

delete from 表名;

删除整张表的数据还可以使用 truncate 表名;

 

区别：

• truncate 相当于删除表再重建一张同名结构的表，操作后得到一张全新表，而 delete 是在原有表中删除数据。如果决定清空一张表的数据，truncate 速度更快一些。

• TRUNCATE 语句不能回滚

   

2、如果删除主表的记录，要注意查看从表的外键是否有依赖该行的值，如果有

（1）如果外键是 on delete RESTRICT 或 on delete NO ACTION，那么要先处理从表的数据，才能删除

（2）如果外键是 on delete SET NULL 或 on delete CASCADE，那么删除时从表的对应记录也会被置空或跟着删除。

3、可以一次删除多个表的数据

例如：两个表没有建立外键，但逻辑上有外键关系，也可以通过删除多个表的数据来实现级联删除

4、所有正在运行的相关事务被提交。

5、所有相关索引被删除。

4）修改记录

update 表名 set 修改后的属性 where 条件;

#修改tb_door表中id为1的记录
mysql> update tb_door set tel=555 where id=1;
mysql> select * from tb_door;
+----+-------------------+------+
| id | door_name         | tel  |
+----+-------------------+------+
|  1 | 永和大王1店       | 555  |
|  2 |  永和大王2店      | 888  |
+----+-------------------+------+
2 rows in set (0.00 sec)
           

1、如果不写 where 条件，会修改所有行。

2、值可以是常量值、表达式、函数。

3、可以同时更新多张表，如果两个表没有建立外键，但逻辑上有外键关系。

4、如果修改从表外键字段的数据，要注意查看主表参照字段的值是否存在。

5、如果修改主表的被参考的字段的值，要注意查看从表的外键是否有依赖该值，如果有

• 如果外键是 on update RESTRICT 或 on update NO ACTION，那么要先处理从表的数据，才能修改
• 如果外键是 on update SET NULL 或 on update CASCADE，那么直接修改，从表的外键字段会自动处理

5）对列操作

#增加一列
alter table 表名 add column 列名 数据类型; #默认在最后
alter table 表名 add column 列名 数据类型 after 某一列;#在某列之后
alter table 表名 add column 列名 数据类型 first;#在首列

mysql> alter table tb_door add column loc varchar(30);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from tb_door;
+----+-------------------+------+------+
| id | door_name         | tel  | loc  |
+----+-------------------+------+------+
|  1 | 永和大王1店       | 555  | NULL |
|  2 |  永和大王2店      | 888  | NULL |
+----+-------------------+------+------+
2 rows in set (0.00 sec)
           

#修改列类型
alter table 表名 modify column 列名 数据类型;
alter table 表名 modify column 列名 数据类型 after 某一列;
alter table 表名 modify column 列名 数据类型 first；
           

#删除列
alter table 表名 drop column 列名;

mysql> alter table tb_door drop column loc;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from tb_door;
+----+-------------------+------+
| id | door_name         | tel  |
+----+-------------------+------+
|  1 | 永和大王1店       | 555  |
|  2 |  永和大王2店      | 888  |
+----+-------------------+------+
2 rows in set (0.00 sec)
           

4. DQL 数据查询语言基础

4.1 准备测试数据

1）部门表 dept

字段名称	数据类型	是否为空	备注
deptno	int	部门编号，PK主键
dname	varchar(20)	Y	部门名称
loc	varchar(13)	Y	部门所在地点

#部门表
CREATE TABLE dept(
depton INT PRIMARY KEY AUTO_INCREMENT,#设置主键，自动递增
dname VARCHAR(20),
loc VARCHAR(13),
);

INSERT INTO dept VALUES(NULL,'accounting','一区');
INSERT INTO dept VALUES(NULL,'research','二区');
INSERT INTO dept VALUES(NULL,'operations','二区');
           

2）员工表 emp

字段名称	数据类型	是否为空	备注
empno	int	员工编号，PK主键
ename	varchar(10)	Y	员工名称
job	varchar(10)	Y	职位
mgr	int	Y	上级编号
hiredate	datetime	Y	å ¥èŒæ—¶é—´
sal	double	Y	月工资
comm	NUMERIC(8,2)	Y	奖金
deptno	int	Y	所属部门,FK外键

CREATE TABLE emp(
empno int primary key auto_increment,
ename VARCHAR(10),
job VARCHAR(10),
mgr int,
hiredate DATE,
sal double,
comm NUMERIC(7,2),#奖金
deptno int
);

INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-1',90000,NULL,1);
INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);
           

4.2 基础查询

语法：

SELECT 要查询的东西

FROM 表名;

 

类似于Java中 :System.out.println(要打印的东西);

特点：

• 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
• 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

字符函数

1）LOWER

lower() --数据转小写

SELECT LOWER(字段名) FROM 表名;
 
SELECT dname,LOWER(dname) FROM dept;

dname       LOWER(dname)  
----------  --------------
accounting  accounting    
operations  operations    
research    research      
           

2）UPPER

upper() --数据转大写

SELECT UPPER(字段名) FROM 表名; 

SELECT dname,UPPER(dname) FROM dept;

dname       UPPER(dname)  
----------  --------------
accounting  ACCOUNTING    
operations  OPERATIONS    
research    RESEARCH      
           

3）LENGTH

length() --数据的长度（底层用了UTF-8字符集，一个字母或数字占1字节，一个汉字占3字节）

SELECT LENGTH(字段名) FROM 表名;

SELECT dname,LENGTH(dname),loc,LENGTH(loc) FROM dept;

dname       LENGTH(dname)  loc     LENGTH(loc)  
----------  -------------  ------  -------------
accounting             10  一区                  6
operations             10  二区                  6
research                8  二区                  6
           

4）SUBSTR

substr(num1,num2) --截取子串从num1到num2截取数据，数据下标从1开始

SELECT SUBSTR(字段名,num1,num2) FROM 表名; 

SELECT dname,SUBSTR(dname,1,3) FROM dept;#从第一个字符开始截取，截取出来3个字符
SELECT dname,SUBSTR(dname,2) FROM dept;#从第二个字符开始都截取完
SELECT dname,SUBSTR(dname,2,5) FROM dept;#从第二个字符开始，截取出来5个字符

SELECT dname,SUBSTR(dname,1,3),SUBSTR(dname,2),SUBSTR(dname,2,5) FROM dept;

dname       SUBSTR(dname,1,3)  SUBSTR(dname,2)  SUBSTR(dname,2,5)  
----------  -----------------  ---------------  -------------------
accounting  acc                ccounting        ccoun              
operations  ope                perations        perat              
research    res                esearch          esear              
           

5）CONCAT

connat() --拼接字符串

select CONCAT(字段名,'要拼接的字符串') from dept; 

SELECT dname,CONCAT(dname,'123','ABC') FROM dept;#在dname字段属性后拼接'123''ABC'

dname       CONCAT(dname,'123','ABC')  
----------  ---------------------------
accounting  accounting123ABC           
operations  operations123ABC           
research    research123ABC             
           

6）REPLACE

replace() --将指定字符串替换

SELECT REPLACE(字段名,'指定被替换的字符串','替换的字符串') FROM 表名;


SELECT dname,REPLACE(dname,'a','666')  FROM dept;#把a字符替换成666

dname       REPLACE(dname,'a','666')  
----------  --------------------------
accounting  666ccounting              
operations  oper666tions              
research    rese666rch                
           

7）DISTINCT

distinct() --使用distinct关键字，去除重复的记录行

SELECT DISTINCT 字段名 FROM 表名;

SELECT loc FROM dept;

loc        
-----------
一区
二区
二区

SELECT DISTINCT loc FROM dept;

loc        
-----------
一区
二区
           

8）\ 转义字符

将 \ 后的sql语句符号转为字符

#' 作为sql语句符号，内容中出现单撇就会乱套，进行转义即可
#单引号是一个SQL语句的特殊字符

#select 'ab'cd' (这样写会报错)

#数据中有单引号时,用一个\转义变成普通字符
SELECT 'ab\'cd' ;#显示字符串ab'cd
           

trim去前后指定的空格和字符

ltrim去左边空格

rtrim去右边空格

lpad左填充

rpad右填充

instr返回子串第一次出现的索引

数学函数

1）ROUND

round() --数值四舍五入，并保留num位

SELECT ROUND(字段名,num) FROM; 表名;#数值四舍五入，并保留num位

SELECT comm,ROUND(comm) FROM emp;#直接四舍五入取整

comm     ROUND(comm)  
-------  -------------
(NULL)   (NULL)       
2000.00  2000         
1000.00  1000         
200.12   200          
200.58   201          

SELECT comm,ROUND(comm,1) FROM emp;#四舍五入并保留一位小数

comm     ROUND(comm,1)  
-------  ---------------
(NULL)   (NULL)         
2000.00  2000.0         
1000.00  1000.0         
200.12   200.1          
200.58   200.6          
           

2）CEIL

ceil() --数值向上取整

SELECT CEIL(字段名) FROM 表名;

SELECT comm,FLOOR(comm) FROM emp;

comm     FLOOR(comm)  
-------  -------------
(NULL)          (NULL)
2000.00           2000
1000.00           1000
200.12             200
200.58             200
           

3）FLOOR

floor() --数值向下取整

SELECT CEIL(字段名) FROM 表名;

SELECT comm,FLOOR(comm) FROM emp;

comm     FLOOR(comm)  
-------  -------------
(NULL)          (NULL)
2000.00           2000
1000.00           1000
200.12             200
200.58             200
           

rand 随机数

mod取余

truncate截断

日期函数

1）NOW

now() --当前系统日期+时间

SELECT NOW();  

NOW()                
---------------------
2021-07-04 21:44:46  
           

2）CURDATE

curdate() --当前系统日期

SELECT CURDATE(); 

CURDATE()   
------------
2021-07-04  
           

3）CURTIME

curtime() --当前系统时间

SELECT CURTIME();

CURTIME()  
-----------
21:46:09   
           

year() å¹´

month() 月

day() 日

hour() 时

minute() 分

second() 秒

SELECT NOW(),
YEAR(NOW()),MONTH(NOW()),DAY(NOW()),
HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());

NOW()                YEAR(NOW())  MONTH(NOW())  DAY(NOW())  HOUR(NOW())  MINUTE(NOW())  SECOND(NOW())  
-------------------  -----------  ------------  ----------  -----------  -------------  ---------------
2021-07-04 21:46:58         2021             7           4           21             46               58

SELECT YEAR('1997-06-02 20:20:20') AS Y,
MONTH('1997-06-02 20:20:20') AS MON,
DAY('1997-06-02 20:20:20') AS D,
HOUR('1997-06-02 20:20:20') AS H,
MINUTE('1997-06-02 20:20:20') AS MIN,
SECOND('1997-06-02 20:20:20') AS S;


     Y     MON       D       H     MIN       S  
------  ------  ------  ------  ------  --------
  1997       6       2      20      20        20
           

4）DATE_FORMAT

date_format 日期格式，将日期转换成字符

#统计2018年以前入职的老员工
SELECT * FROM emp WHERE DATE_FORMAT(hiredate,'%Y-%m-%d')<'2018-01-01';

 empno  ename   job        mgr  hiredate       sal  comm     deptno  
------  ------  ------  ------  ----------  ------  -------  --------
   100  jack    副总      (NULL)  2002-05-01   90000  (NULL)          1
   200  tony    总监         100  2015-02-02   10000  2000.00         2
   300  hana    经理         200  2017-02-02    8000  1000.00         2
           

str_to_date 将字符转换成日期

聚合函数

我们经常需要汇总数据而不用把他们的实际检索出来，为此SQL提供了专门的函数。使用这些函数，SQL查询可用于检索，以便分析和报表生成

AVG(【DISTINCT】 expr) 返回 expr 的平均值

COUNT（【DISTINCT】 expr）返回 expr 的非 NULL 值的数目

MIN(【DISTINCT】 expr) 返回 expr 的最小值

MAX(【DISTINCT】 expr) 返回 expr 的最大值

SUM（【DISTINCT】 expr）返回 expr 的总和

  

特点：

• 以上五个分组函数都忽略null值，除了count(*)
• sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
• 都可以搭配distinct使用，用于统计去重后的结果
• count的参数可以支持：字段、* 、常量值，一般放1,建议使用 count(*)

#聚合所有员工薪水
SELECT MAX(sal),MIN(sal),SUM(sal),AVG(sal) FROM emp;

#max最大值 min最小值 sum求和 avg平均数
max(sal)  MIN(sal)  SUM(sal)  AVG(sal)  
--------  --------  --------  ----------
   90000      3000    114500       22900

#count 个数
SELECT COUNT(*) FROM emp #低效
SELECT COUNT(1) FROM emp #高效

COUNT(1)  
----------
         5

SELECT COUNT(empno) FROM emp 

COUNT(empno)  
--------------
             5

SELECT COUNT(comm) FROM emp #按照字段名统计个数，如果字段值是null将不做统计

COUNT(comm)  
-------------
            4
           

用 count(*)，count(1)，谁好呢?

其实,对于 myisam 引擎的表,没有区别的。这种引擎内部有一计数器在维护着行数。

Innodb 的表，用 count(*)直接读行数，效率很低 , 因为 innodb 真的要去数一遍.

4.3 条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据

 

语法：

SELECT

要查询的字段|表达式|常量值|函数

FROM

表

WHERE

条件 ;

 

分类：

一、条件表达式

示例：salary>10000

条件运算符：

< >= <= = != <>

 

二、逻辑表达式

示例：salary>10000 && salary<20000

逻辑运算符：

• and（&&）:两个条件如果同时成立，结果为true，否则为false
• or(||)：两个条件只要有一个成立，结果为true，否则为false

• not(!)：如果条件成立，则not后为false，否则为true

   

三、模糊查询

示例：last_name like ‘a%’

1）WHERE

where() --用来过滤数据(注意:where中不能使用列别名!!)

SELECT *(表内)/字段名(字段内) 
FROM 表名 
WHERE 条件; 
           

#SQL的执行顺序 from where select
#查询部门编号=1的记录
SELECT * FROM dept WHERE deptno = 1; #只查一条--高效

deptno  dname       loc     
------  ----------  --------
     1  accounting  一区  

#查询在二区的的部门
SELECT * FROM dept WHERE loc = "二区";

deptno  dname       loc     
------  ----------  --------
     3  operations  二区  
     2  research    二区  

#查询在一区的编号是1的部门名称
SELECT dnameFROM dept WHERE loc = "一区" AND depton = 1;#and 高效

dname       
---------- 
accounting       

#查询在一区的部门或者编号是3的部门名称#or 
SELECT dname FROM dept WHERE loc = "一区" OR deptno = 3; #or 低效

dname       
------------
accounting  
operations  
           

2）LIKE

like --模糊查询，其中通配符%代表0到n个字符，通配符下划线_代表1个字符

#like 模糊的条件%占位符匹配0~N个字符
SELECT * FROM 表名 WHERE 字段名 LIKE 'xx%'; #以xx开头的部门
SELECT * FROM 表名 WHERE 字段名 LIKE '%o%';#模糊的条件，查询内容里包含xx的部门
SELECT * FROM 表名 WHERE 字段名 LIKE '%ch'; #以xx结尾的部门
           

#明确的条件，查询名称为accounting的部门
SELECT * FROM dept WHERE dname = 'accounting';

deptno  dname       loc     
------  ----------  --------
     1  accounting  一区  

#查询以ch结尾的部门
SELECT * FROM dept WHERE dname LIKE '%ch'; 

deptno  dname     loc     
------  --------  --------
     2  research  二区  

#查询在一区的部门或者名称包含ting的部门
SELECT * FROM dept WHERE loc="一区" OR dname LIKE '%ting%';

deptno  dname       loc     
------  ----------  --------
     1  accounting  一区  
           

3）NULL

is null --过滤字段值为空的数据

is not null --过滤字段值不为空的数据

select * from 表名 where 字段名 is null --过滤字段值为空的数据
select * from 表名 where 字段名 is not null --过滤字段值不为空的数据

#查询comm 是空的员工的信息
SELECT * FROM emp WHERE comm IS NULL;

 empno  ename   job        mgr  hiredate       sal  comm    deptno  
------  ------  ------  ------  ----------  ------  ------  --------
   100  jack    副总      (NULL)  2002-05-01   90000  (NULL)         1

#查询comm 不为空的员工的信息
SELECT * FROM emp WHERE comm IS NOT NULL;

 empno  ename   job        mgr  hiredate       sal  comm     deptno  
------  ------  ------  ------  ----------  ------  -------  --------
   200  tony    总监         100  2015-02-02   10000  2000.00         2
   300  hana    经理         200  2017-02-02    8000  1000.00         2
   400  leo     员工         300  2019-02-22    3000  200.12          2
   500  liu     员工         300  2019-03-19    3500  200.58          2
           

4）IFNULL

在SQL中null不参与运算，运算中出现null值就为null，需要使用ifnull(字段名,转换值)进行转换

#查询每个员工的月薪
SELECT * , sal + comm FROM emp;

#奖金为null 月薪不为null 相加后变为了null，数据出现了错误
 empno  ename   job        mgr  hiredate       sal  comm     deptno  sal + comm  
------  ------  ------  ------  ----------  ------  -------  ------  ------------
   100  jack    副总      (NULL)  2002-05-01   90000  (NULL)        1        (NULL)
   200  tony    总监         100  2015-02-02   10000  2000.00       2         12000
   300  hana    经理         200  2017-02-02    8000  1000.00       2          9000
   400  leo     员工         300  2019-02-22    3000  200.12        2       3200.12
   500  liu     员工         300  2019-03-19    3500  200.58        2       3700.58


#改进
SELECT *, sal + IFNULL(comm,0) FROM emp;

 #null不参与运算，需要特殊处理
 empno  ename   job        mgr  hiredate       sal  comm     deptno  sal + ifnull(comm,0)  
------  ------  ------  ------  ----------  ------  -------  ------  ----------------------
   100  jack    副总      (NULL)  2002-05-01   90000  (NULL)        1                   90000
   200  tony    总监         100  2015-02-02   10000  2000.00       2                   12000
   300  hana    经理         200  2017-02-02    8000  1000.00       2                    9000
   400  leo     员工         300  2019-02-22    3000  200.12        2                 3200.12
   500  liu     员工         300  2019-03-19    3500  200.58        2                 3700.58
           

5）BETWEEN AND

between 条件1 and 条件2 --在条件1和条件2之间查询

SELECT * FROM 表名 WHERE 字段名 BETWEEN 条件1 AND 条件2;

#查询工资(5000,10000)的员工信息
SELECT * FROM emp WHERE SAL>5000 AND SAL<10000;
#等效于
SELECT * FROM emp WHERE SAL BETWEEN 5000 AND 10000;

 empno  ename   job        mgr  hiredate       sal  comm     deptno  
------  ------  ------  ------  ----------  ------  -------  --------
   300  hana    经理         200  2017-02-02    8000  1000.00         2
           

6）LIMIT

limit --在mysql中，通过limit进行分页查询。

例如：分数最高的记录<按分数排序后，limit n，返回前n条。

SELECT * FROM 表名 LIMIT num;#从表中取num行数据
SELECT * FROM 表名 LIMIT num1,num2;#从表中num1行开始取,取num2行数据

SELECT * FROM emp LIMIT 2;#取前两条

empno  ename   job        mgr  hiredate       sal  comm     deptno  
------  ------  ------  ------  ----------  ------  -------  --------
   100  jack    副总      (NULL)  2002-05-01   90000  (NULL)          1
   200  tony    总监         100  2015-02-02   10000  2000.00         2

SELECT * FROM emp LIMIT 1,3;#从1开始（第2条记录）开始，取三条记录

 empno  ename   job        mgr  hiredate       sal  comm     deptno  
------  ------  ------  ------  ----------  ------  -------  --------
   200  tony    总监         100  2015-02-02   10000  2000.00         2
   300  hana    经理         200  2017-02-02    8000  1000.00         2
   400  leo     员工         300  2019-02-22    3000  200.12          2
           

流程控制函数

if 处理双分支

case语句 处理多分支

情况1：处理等值判断

情况2：处理条件判断

4.4 排序查询

语法：

SELECT

要查询的东西

FROM

表

WHERE

条件

ORDER BY 排序的字段|表达式|函数|别名 【asc | desc】;

ORDER BY

order by – 排序

• ASC 升序（默认的）
• DESC 降序

#order by 排序 升序（默认的）、降序

#按照入职日期排序，默认升序 ASC可不写
SELECT * FROM emp WHERE hiredate < '2018-1-1' ORDER BY hiredate ASC;

 empno  ename   job        mgr  hiredate       sal  comm     deptno  
------  ------  ------  ------  ----------  ------  -------  --------
   100  jack    副总      (NULL)  2002-05-01   90000  (NULL)          1
   200  tony    总监         100  2015-02-02   10000  2000.00         2
   300  hana    经理         200  2017-02-02    8000  1000.00         2
   500  liu     员工         300  2019-03-19    3500  200.58          2
   400  leo     员工         300  2019-02-22    3000  200.12          2

#统计2015年以前入职的老员工，默认升序
SELECT * FROM emp WHERE hiredate < '2018-1-1' ORDER BY hiredate;

 empno  ename   job        mgr  hiredate       sal  comm     deptno  
------  ------  ------  ------  ----------  ------  -------  --------
   100  jack    副总      (NULL)  2002-05-01   90000  (NULL)          1
   200  tony    总监         100  2015-02-02   10000  2000.00         2
   300  hana    经理         200  2017-02-02    8000  1000.00         2

#查询每个员工入职了几年，并按降序排列
SELECT ename,YEAR(NOW())-YEAR(hiredate) AS working_age FROM emp ORDER BY YEAR(NOW())-YEAR(hiredate)  DESC;

ename   working_age  
------  -------------
jack               19
tony                6
hana                4
leo                 2
liu                 2
           

4.5 分组查询

用于对查询的结果进行分组统计

 

语法

SELECT

查询的字段，分组函数

FROM

表

GROUP BY 分组的字段;

 

特点：

1、可以按单个字段分组

2、和分组函数一同查询的字段最好是分组后的字段

3、分组筛选

• 针对的表 位置 关键字
• 分组前筛选： 原始表 group by的前面 where
• 分组后筛选： 分组后的结果集 group by的后面 having

4、可以按多个字段分组，字段之间用逗号隔开

5、可以支持排序

6、having后可以支持别名

GROUP BY

用于对查询的结果进行分组统计group by表示分组, having 子句类似where过滤返回的结果

关于 mysql 的 group by 的特殊：

• 在 SELECT 列表中所有未包含在组函数中的列都应该是包含在 GROUP BY 子句中的，换句话说，SELECT 列表中最好不要出现 GROUP 。
• 如果查询时，出现了聚合列和非聚合列，通常要按照非聚合列分组

#统计 每个职位的人数
SELECT COUNT(1) FROM emp GROUP BY job;

job     COUNT(1)  
------  ----------
副总               1
总监               1
经理               1
员工               2

#查询 每种岗位的平均工资和岗位名称
SELECT AVG(sal),job FROM emp GROUP BY job;#按非聚合列分组

AVG(sal)  job     
--------  --------
   90000  副总  
   10000  总监  
    8000  经理  
    3250  员工  
  
#查询 每个岗位的平均工资 再过滤出<10000的
SELECT job,AVG(sal)
FROM emp 
#可以分组前过滤用where，但是where里不能出现聚合函数
#分组之前需要过滤，使用where --高效
#WHERE AVG(sal)<10000 (运行报错 Invalid use of group function)
GROUP BY job;
#分组后需要过滤，使用having --相对低效
HAVING AVG(sal)>10000;


job     avg(sal)  
------  ----------
经理            8000
员工            3250
           

5. 约束 （CONSTRAINTS）

字段约束

  数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据的完整性要从以下四个方面考虑：

• 实体完整性（Entity Integrity）

  例如，同一个表中，不能存在两条完全相同无法区分的记录

• 域完整性（Domain Integrity）

  例如：年龄范围 0-120，性别范围“男/女”

• 引用完整性（Referential Integrity）

  例如：员工所在部门，在部门表中要能找到这个部门

• 用户自定义完整性（User-defined Integrity）

  例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的 5 倍。

根据约束的特点，分为几种：

• 键约束：主键约束、外键约束、唯一键约束
• Not NULL 约束：非空约束
• Check 约束：检查约束
• Default 约束：缺省约束

主键约束 primary key

主键： Primary key，简称 PK，数据库主键作用保证实体的完整性，可以是一个列或多列的组合。

主键约束： 如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。

主键自增策略： 当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1。

设置主键与查看某个表的约束和索引

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
SHOW INDEX FROM 表名称;
SHOW CREATE TABLE 表名;

SHOW INDEX FROM 表名称;

Table   Non_unique  Key_name     Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment  Visible  Expression  
------  ----------  -----------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------  -------  ------------
dept             0  PRIMARY                 1  deptno       A                    5    (NULL)  (NULL)          BTREE                               YES      (NULL)      
dept             1  dname_index             1  dname        A                    4    (NULL)  (NULL)  YES     BTREE                               YES      (NULL)      
dept             1  fuhe                    1  dname        A                    4    (NULL)  (NULL)  YES     BTREE                               YES      (NULL)      
dept             1  fuhe                    2  loc          A                    4    (NULL)  (NULL)  YES     BTREE                               YES      (NULL)      
                                                                                                                                                                       

SHOW CREATE TABLE 表名;

Table   Create Table                                                                                                                                                                                                                                                                        
------  -----------------------------------------------------------------------
dept    CREATE TABLE `dept` (                                                                                                                                                                                                                                                               
          `deptno` int NOT NULL AUTO_INCREMENT,                                                                                                                                                                                                                                             
          `dname` varchar(20) DEFAULT NULL,                                                                                                                                                                                                                                                 
          `loc` varchar(13) DEFAULT NULL,                                                                                                                                                                                                                                                   
          PRIMARY KEY (`deptno`),                                                                                                                                                                                                                                                           
          KEY `dname_index` (`dname`),                                                                                                                                                                                                                                                      
          KEY `fuhe` (`dname`,`loc`)                                                                                                                                                                                                                                                        
        ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3    
           

如何删除主键？

删除主键约束，不需要指定主键名，一个表只有一个主键

alter table 表名称 drop

添加主键约束，例如将id设置为主键：

#主键是一条记录的唯一标识，具有唯一性，不能重复
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
PRIMARY KEY (id)
);

INSERT INTO tb_user (id,NAME) VALUES(1,'tony');
INSERT INTO tb_user (id,NAME) VALUES(1,'hellen');

#第二句插入就会报错：提示主键1的值已经存在，重复了
Query : INSERT INTO tb_user (id,NAME) VALUES(1,'hellen')
Error Code : 1062
Duplicate entry '1' for key 'PRIMARY'
           

• 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值，如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
• 每个表有且最多只允许一个主键约束。
• MySQL 的主键名总是 PRIMARY，就算自己命名了主键约束名也没用。
• 当创建主键约束时，MySQL 默认在对应的列上建立主键索引。删除主键时，也会直接删除主键索引。

外键约束 forgrein key

外键约束：Foreign key： 简称 FK。外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系，使用关键字references进行连接。

• 在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1；），也可以指定外键约束名。
• 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名
• 删除外键时，关于外键列上的普通索引需要单独删除。

注意：

1. 在从表上建立外键，而且主表要先存在。
2. 从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键）。
3. 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样
4. 一个表可以建立多个外键约束
5. 从表的外键值必须"在主表中能找到"或者为空，从而约束了从表的外键列的值的添加和修改。
6. 当主表的记录被从表参照时，主表中被参考记录的删除和更新也会受到限制。
7. 默认情况下，主表和从表是严格依赖关系 RESTRICT。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。
8. 但是有一种是级联“修改、删除”：

• ON DELETE SET NULL(级联置空)：当外键设置了 SET NULL,当主表的相关记录删除时，从表对应的字段改为NULL。注意从表外键字段得允许为空才行
• ON DELETE CASCADE(级联删除)：当外键设置了 CASCADE（级联），当主表的相关记录删除时，从表对应的行都删除了。

1. 对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
2. 如果要删除表，需要先删除从表，才能删除主表。

DROP TABLE IF EXISTS tb_user_address; #如果表存在则删除，慎用会丢失数据
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据

CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
sex CHAR(2) DEFAULT '男', #默认值
phone CHAR(18),
age INT,
CHECK (age>0 AND age<=200),#check核查 age的数据需要大于0小于200
);

CREATE TABLE tb_user_address (
user_id INT PRIMARY KEY NOT NULL,
address VARCHAR(200),
#tb_user_address中user_id字段录入tb_user表不存在的主键值，将报错
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);

DESC tb_user;
           

如何删除外键约束？

ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;

ALTER TABLE t_emp DROP FOREIGN KEY fk_emp_dept_did;

 

如何删除外键列上的索引？

ALTER TABLE 表名称 DROP INDEX 外键列索引名;

ALTER TABLE t_emp DROP

唯一约束 unique

唯一约束： Unique key，简称 UK，如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

• 同一个表可以有多个唯一约束。
• 唯一约束可以是某一个列的值唯一，也可以多个列组合值的唯一。
• MySQL 会给唯一约束的列上默认创建一个唯一索引。
• 删除唯一键只能通过删除唯一索引的方式删除，删除时需要指定唯一索引名，唯一索引名就是唯一约束名一样。

  如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同，如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

如何删除唯一性约束？

ALTER TABLE 表名称 DROP INDEX 唯一性约束名;

注意：如果忘记名称，可以通过查看表的约束或索引的方式查看

添加唯一约束，例如为username添加唯一约束及非空约束：

  Name字段创建了唯一约束，插入数据时数据库会进行检查，如果插入的值相同，就会检查报错：

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据

CREATE TABLE tb_user(
id INT,
NAME VARCHAR(30) UNIQUE NOT NULL,--唯一约束且非空
phone VARCHAR(20) UNIQUE NOT NULL,--唯一约束且非空
email VARCHAR(30) UNIQUE NOT NULL,--唯一约束且非空
PRIMARY KEY (id)
);

DESC tb_user;
INSERT INTO tb_user (id,NAME) VALUES(1,'tony');--NAME的值要唯一,重复会报错的
INSERT INTO tb_user (id,NAME) VALUES(2,'tony');

#执行上面语句出错：

Query : INSERT INTO tb_user (id,NAME) VALUES(2,'tony')
Error Code : 1062
Duplicate entry 'tony' for key 'name'

#展示表结构：
DESC tb_user;
           

主键和唯一键的区别：

（1）主键是非空，唯一键允许空

（2）主键一个表只能一个，唯一键可以有多个

非空约束 not null

非空约束： 如果为一个列添加了非空约束，那么这个列的值就不能为空，为空会报错，但可以重复。

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据

CREATE TABLE tb_user(
id INT AUTO_INCREMENT,
NAME VARCHAR(30) UNIQUE NOT NULL,
age INT,
phone VARCHAR(20) UNIQUE NOT NULL,
email VARCHAR(30) UNIQUE NOT NULL,
PRIMARY KEY (id)
);

DESC tb_user;

#id为自增主键，null值无效，数据库会自动用下一个id值替代
#age因为运行为null，所以可以设置为null
INSERT INTO tb_user (id,age) VALUES(NULL,NULL);

*********************************************************************************

create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,123;);//OK
           

检查约束 check

注意： MySQL 不支持 check 约束，但可以使用 check 约束，而没有任何效果。

例如：age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据


CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
sex CHAR(2) DEFAULT '男', #默认值
phone CHAR(18),
age INT,

CHECK (age>0 AND age<=200),#录入age超过200将报错
createdTime DATE DEFAULT NOW()
);

DESC tb_user;
           

默认约束 default

默认约束： default，默认值，在插入数据时某列如果没指定其他的值，那么会将默认值添加到新记录。

默认值

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据

CREATE TABLE tb_user (

id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
sex CHAR(2) DEFAULT '男', #default 默认值 如果不添加指定值就默认添加数据 “男”
phone CHAR(18),
age INT,
createdTime DATE DEFAULT NOW()
);

DESC tb_user;
           

自增列（AUTO_INCREMENT）

CREATE TABLE t_stu(
sid INT PRIMARY KEY AUTO_INCREMENT, #给主键设置自增键
sname VARCHAR(100) NOT NULL, 
gender CHAR NOT NULL DEFAULT '男', 
birthday DATE, address VARCHAR(200)
);
           

关于自增长 auto_increment：

1. 整数类型的字段才可以设置自增长。
2. 当需要产生唯一标识符或顺序值时，可设置自增长。
3. 一个表最多只能有一个自增长列
4. 自增长列必须非空
5. 自增长列必须是主键列或唯一键列。
6. InnoDB 表的自动增长列可以手动插入，但是插入的值如果是空或者 0，则实际插入的将是自动增长后的值。

索引 index

定义

  索引：索引是对数据库表中一列或多列的值进行排序的一种结构，是一种排好序的快速查找的数据结构。索引是一个单独的、物理的数据库结构，它帮助数据库高效的进行数据的检索。它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。由此可知，索引是要消耗数据库空间的。而约束是一种逻辑概念。

  一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

  例如：一本字典，如何快速找到某个字，可以给字典加目录，对数据库来说,索引的作用即是给"数据"加目录。

  设有 N 条随机记录,不用索引,平均查找 N/2 次,那么用了索引之后呢。如果是 btree(二叉树)索引，log2N，如果是hash(哈希)索引，时间复杂度是 1。

为何索引快？

  明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。其过程先是到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

• 排序，tree结构，类似二分查找
• 索引表小

优点:

• 索引是数据库优化。
• 表的主键会默认自动创建索引。
• 每个字段都可以被索引。
• 大量降低数据库的IO磁盘读写成本，极大提高了检索速度。
• 索引事先对数据进行了排序，大大提高了查询效率。

缺点:

• 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间。
• 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”。
• 虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件。
• 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引。

MySQL 提供多种索引类型供选择：

• 普通索引：一个索引只包括一个列，一个表可以有多个列
• 复合索引：一个索引同时包括多列
• 唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
• 主键索引：只有一个主键索引
• 全文索引：MySQL5.X 版本只有 MyISAM 存储引擎支持 FULLTEXT，并且只限于CHAR、VARCHAR 和 TEXT 类型的列上创建。

MySQL 的索引方法：

• HASH
• BTREE （MySQL 中多数索引都以 BTREE）

索引的使用原则：

• 不过度索引
• 索引条件列(where 后面最频繁的条件比较适宜索引)
• 索引散列值,过于集中的值不要索引，例如:给性别"男","女"加索引,意义不大

查看索引

创建索引

1）创建索引

创建索引,最左边的列最关键，主键会自动创建索引

create index 索引名 on 表名称 (column_name,[column_name...]);
create index loc_index on dept(loc); 
           

2）修改表结构,添加普通索引

3）创建唯一索引

4）创建复合索引

5）创建复合唯一索引

alter table dept add unique fuhe_index(dname,loc)；
           

删除索引

alter table 表名称 drop index 索引名;
alter table dept drop index fuhe_index；
           

索引扫描类型

• ALL 全表扫描，没有优化，最慢的方式。
• index 索引全扫描，其次慢的方式。
• range 索引范围扫描，常用语<，<=，>=，between等操作。
• ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中。
• eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询。
• const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况。
• null MySQL不访问任何表或索引，直接返回结果。

最左特性

explain
select * from dept where loc='二区' #使用了loc索引
 
explain
select * from dept where dname='研发部'#使用了dname索引
 
explain
select * from dept where dname='研发部' and loc='二区' #使用了dname索引
           

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。