MySQL 数据库 - SQL

SQL通用语法

SQL通用语法

1. SQL语句可以单行或者多行书写，以分号结尾。
2. SQL语句可以使用空格/缩进来增强语句的可读性。
   注意：空格和缩进的个数是没有限制的，可以是 “一个” 也可以是 “多个”。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 注释：
   单行注释：-- 注释内容 或者 # 注释内容（MySQL特有）
   多行注释：/* 注释内容 */

SQL分类

SQL分类

• DDL
  数据定义语句，用来定义数据库对象（数据库，表，字段）
• DML
  数据操作语言，用来对数据库表中的数据进行 增删改
• DQL
  数据查询语言，用来查询数据库表中的记录
• DCL
  数据控制语言，用来创建数据库用户，控制数据库的访问权限

DDL

DDL

• 查询
  查询所有数据库

  SHOW DATABASES;
  

• 查询当前数据库

  SELECT DATABASE();
  

• 创建

  CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
  

[IF NOT EXISTS] 如果不存在，则执行

[DEFAULT CHARSET 字符集] 给数据库添加字符集 

• 删除

  DROP DATABASE [IF EXISTS] 数据库名;
  

• 使用

  USE 数据库名;
  

DDL - 表操作 - 查询

DDL - 表操作 - 查询

• 查询当前数据库所有表

show tables;

• 查询表结构

desc 表名;

• 查询指定表的建表语句

show create table 表名;

DDL-表操作-创建

DDL - 表操作 - 创建

create table 表名{
	字段1 字段1类型 [comment 字段1注释],
	字段2 字段2类型 [comment 字段2注释],
	字段3 字段3类型 [comment 字段3注释],
	字段n 字段n类型 [comment 字段n注释]
}[comment 表注释];

注意：[]为可选参数，最后一个字段后面没有 “逗号”

DDL-表操作-数据类型

DDL - 表操作 - 数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型，字符串类型，日期时间类型。

• 数值类型

类型：tinyint		大小：1 byte		有符号（signed）范围 ：（-128,127）					无符号（unsigned）范围：（0,255）				描述：小整数值
类型：smallint		大小：2 byte		有符号（signed）范围 ：(-32768，32767)					无符号（unsigned）范围：(0，65535)				描述：大整数值
类型：mediumint  	大小：3 byte		有符号（signed）范围 ：(-8388608，8388607)				无符号（unsigned）范围：(0，16777215)			描述：大整数值
类型：int或integer	大小：4 byte		有符号（signed）范围 ：(-2147483648，2147483647)		无符号（unsigned）范围：(0，4294967295)		描述：大整数值
类型：bigint			大小：8 byte		有符号（signed）范围 ：(-2^63，2^63-1)					无符号（unsigned）范围：(0，2^64-1)			描述：极大整数值
类型：float			大小：4 byte		有符号（signed）范围 ：(-3.402823466 E+38，3.402823466351 E+38)					无符号（unsigned）范围：0 和 (1.175494351 E-38，3.402823466 E+38)						描述：单精度浮点数值
类型：double			大小：8 byte		有符号（signed）范围 ：(-1.7976931348623157 E+308，1.7976931348623157 E+308)		无符号（unsigned）范围：0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)		描述：双精度浮点数值
类型：decimal		有符号（signed）范围 ：依赖于M(精度)和D(标度)的值							无符号（unsigned）范围：依赖于M(精度)和D(标度)的值			描述：小数值（精确定点数）

• 字符串类型

类型：char		大小：0-255 bytes 			描述：定长字符串
类型：varchar	大小：0-65535 bytes 			描述：变长字符串
类型：tinyblob	大小：0-255 bytes			描述：不超过255个字符的二进制数据
类型：tinytext	大小：0-255 bytes			描述：短文本字符串
类型：blob		大小：0-65 535 bytes 		描述：二进制形式的长文本数据
类型：text		大小：0-65 535 bytes			描述：长文本数据
类型：mediumblob 大小：0-16 777 215 bytes 	描述：二进制形式的中等长度文本数据
类型：mediumtext 大小：0-16 777 215 bytes		描述：中等长度文本数据
类型：longblob	大小：0-4 294 967 295 bytes	描述：二进制形式的极大文本数据
类型：longtext	大小：0-4 294 967 295 bytes	描述：极大文本数据

• 日期类型

类型：date		大小：3		范围：1000-01-01 至 9999-12-31			格式：YYYY-MM-DD			描述：日期值
类型：time		大小：3		范围：'-838:59:59 至 838:59:59			格式：HH:MM:SS			描述：时间值或持续时间
类型：year		大小：1		范围：1901 至 2155						格式：YYYY				描述：年份值
类型：dateTime	大小：8		范围：1000-01-01 00:00:00 至 9999-12-31 23:59:59				格式：YYYY-MM-DD HH:MM:SS			描述：混合日期和时间值
类型：timestamp	大小：4		范围：1970-01-01 00:00:01 至 2038-01-19 03:14:07				格式：YYYY-MM-DD HH:MM:SS			描述：混合日期和时间值，时间戳

案例：

create table emp(
	id			int 			 comment '编号',		// 纯数字
	workno		varchar(10)		 comment '员工工号',	// 字符串类型，长度不超过10位
	name		varchar(10) 	 comment '姓名',		// 字符串类型，长度不超过10位
	gender		char(1)			 comment '性别',		// 男/女，存储一个汉字
	age			tinyint unsigned comment '年龄',		// 正常年龄，不可能存储负数
	idcard  	char(18)		 comment '身份证号',	// 18位
	entrydate	date			 comment '入职时间'	// 取年月日即可
) comment '员工表';

DDL-表操作-修改

DDL - 表操作 - 修改（字段）

• 添加字段

alter table 表名 add 字段名 字段类型（长度） [comment 注释] [约束];

案例：
为emp表增加一个新的字段 “昵称” 为 nickname，类型为 varchar(20)

• 修改数据类型

alter table 表名 modify 字段名 新的字段类型（长度）;

• 修改字段名和字段类型

alter table 表名 change 旧字段名 新的字段名 字段类型（长度） [comment 注释] [约束];

案例：
将 emp 表中的 nickname 修改为 username ，类型为 varchar(30)

• 删除字段

alter table 表名 drop 字段名;

案例：
删除 emp 表中的 username 字段；

DDL-表操作-修改（表）

DDL - 表操作 - 修改（表）

• 修改表名

alter table 表名 rename to 新的表名;

案例：
将 emp 表的表名修改为 employee

• 删除表

drop table [if exists] 表名;

• 删除指定表，并重新创建该表

truncate table 表名;

注意：在删除表时，表中的全部数据也会被删除。

DML

DML - 添加数据

• 给指定的字段添加数据

insert into 表名 (字段名1,字段名2,...) values (值1,值2,...);

• 给全部字段添加数据

insert into 表名 values (值1,值2,...);

• 批量添加数据

insert into 表名 (字段名1,字段名2,...) values (值1,值2,...),(值1,值2,...);
insert into 表名 values (值1,值2,...),(值1,值2,...);

注意：

• 插入数据时，指定字段顺序需要与值的顺序是一一对应的。
• 字符串和日期数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。

DML-修改数据

DML - 修改数据

update 表名 set 字段名1=值1,字段名2=值2,....[where 条件];

DML-删除数据

DML - 删除数据

delete from 表名 [where 条件];

注意：

• delete 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的数据。
• delete 语句不能删除某一个字段的值（可以使用update）。

DQL - 数据查询语言，用来查询数据库中表的记录

DQL - 数据查询语言，用来查询数据库中表的记录

• DQL-语法

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

DQL - 基本查询

1. 查询多个字段

select 字段1,字段2,字段3 from 表名;

select * from 表名;

2. 设置别名

select 字段1 [as 别名1], 字段2 [as 别名2] ... from 表名;

3. 去除重复记录

select distinct 字段列表 from 表名;

DQL - 条件查询

1. 语法

select 字段列表 from 表名 where 条件列表;

2. 条件
   

DQL - 聚合函数

1. 介绍
   将一列数据作为整体，进行纵向的计算

2. 常见的聚合函数
   

3. 语法

select 聚合函数（字段列表）from 表名;

注意：所有的 null 值是不参与聚合函数的运算的。

DQL - 分组查询

1. 语法

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

2. where 与 having 区别

• 执行时机不同：where 是分组之前进行过滤，不满足where条件，不参与分组；而 having 是分组之后对结果进行过滤。
• 判断条件不同：where 不能使用聚合函数进行判断，而 having 可以。

注意：

• 执行顺序：where > 聚合函数 > having。
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段没有意义。

DQL - 排序查询

1. 语法

select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；

2. 排序方式

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

DQL - 分页查询

1. 语法

select 字段列表 from 表名 limit 起始索引,查询记录数;

注意：

• 起始索引从 0 开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是 limit 。
• 如果查询的是第一页数据，其实索引可以省略，直接简写为 limit 10 。

语句练习：

DQL - 执行顺序

DCL - 用来管理数据库用户，控制数据库的访问权限

DCL - 管理用户

1. 查询用户

use mysql;
select * from user;

2. 创建用户

create user ‘用户名’@‘主机名’ identified by '密码';

3. 修改用户密码

alter user ‘用户名’@‘主机名’ identified with mysql_native_password by '新密码';

4. 删除用户

drop user ‘用户名’@‘主机名’;

注意：

• 主机名可以使用 % 通配。
• 这类SQL开发人员操作的比较少，主要是 DBA（数据库管理员） 使用。

DCL - 权限管控

MySQL中定义了很多权限，但是常用的就一下集中：

1. 查询权限

show grants for '用户名'@'主机名';

2. 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

3. 撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

注意：

• 多个权限之间，使用逗号隔开。
• 授权时，数据库名和表名可以使用 * 进行通配，代表所有。