结构化查询语言 - SQL

Structured Query Language - 结构化查询语言

警告

1. 数据库中所有字符串类型必须使用 单引号 ，不能使用 双引号 ；日期类型也要使用 单引号

内容定义

定义	解释
db_name	数据库名
tab_name	数据表名
tab_name_new	新数据表名
charset_name	字符集名
field_name	字段名
field_name_new	新字段名
field_type	字段类型
ct_name	约束名
ct_type	约束类型：PRIMARY KEY、UNIQUE
ip_addr	ip地址
field_list	字段列表，包括*

数据定义语言 - DDL

Data Definition Language - 数据定义语言

数据库

基本操作

• 查询数据库

  SHOW DATABASE;
  

• 切换数据库

  USE db_name;
  

• 新建数据库

  CREATE DATABASE [IF NOT EXISTS] db_name [CHARSET=utf8];
  

• 删除数据库

  DROP DATABASE [IF EXISTS] db_name;
  

• 修改数据库

  ALTER DATABASE db_name CHARACRER SET utf8;
  

数据表

数据类型

分类	数据类型	含义（有符号）	备注
整形	tinyint(m)	1个字节 范围(-128~127)	m 显示位宽，不影响数据范围
	mediumint(m)	2个字节 范围(-32768~32767)
	smallint(m)	3个字节 范围(-8388608~8388607)
	int(m)	4个字节 范围(-2147483648~2147483647)
	bigint(m)	8个字节 范围(+-9.22*10的18次方)
浮点型	float(m,d)	单精度浮点型 8位精度(4字节) m总位数，d小数位	m,d 显示位宽，不影响数据范围
	double(m,d)	双精度浮点型 16位精度(8字节) m总位数，d小数位
	float(m,d)	单精度浮点型 8位精度(4字节) m总位数，d小数位
字符串	char(n)	固定长度，最多255个字符	n 数据长度
	varchar(n)	固定长度，最多65535个字符
	tinytext	可变长度，最多255个字符
	text	可变长度，最多65535个字符
	mediumtext	可变长度，最多2的24次方-1个字符
	longtext	可变长度，最多2的32次方-1个字符
二进制	_BLOB	存储的数据只能整体读出	-
	_TEXT	可以指定字符集，英文存储区分大小写
日期时间	date	日期 '2008-12-2'	-
	time	时间 '12:25:36'
	datetime	日期时间 '2008-12-2 22:06:44'
	timestamp	自动存储记录修改时间

属性关键字

关键字	含义
NULL	数据列可包含NULL值
ZEROFILL	零填充
DEFAULT	默认值
UNSIGNED	无符号
NOT NULL	非空约束，数据列不允许包含NULL值
PRIMARY KEY	主键约束，非空、唯一、被引用(外键)
AUTO_INCREMENT	自增约束，自动递增，适用于整数类型
UNIQUE	唯一约束，唯一、不可重复，可为NULL
FOREIGN KEY	外键约束，必须是引用一张表的主键，可重复，可为空，可引用多个
CHARACTER SET charset_name	指定一个字符集

基本操作

• 查看当前数据库所有表

  SHOW TABLES;
  

• 查看表索引

  SHOW INDEX FROM tab_name;
  

• 创建表

  CREATE TABLE tab_name (
  	field_name field_type [ props ],
  	...
      [[CONSTRAINT ct_name] ct_type field_name ]
  );
  

• 查看创建表的SQL语句

  SHOW CREATE TABLE tab_name;
  

• 查看表结构

  DESC tab_name;
  

• 删除表

  DROP TABLE tab_name;
  

• 清空数据

  TRUNCATE TABLE tab_name;  # 先DROP，再CREATE，无法回滚
  

• 修改表 - ALTER TABLE

  • 增加新字段

    ALTER TABLE tab_name 
    ADD(
    field_name field_type [props],
    );
    

  • 修改字段类型

    ALTER TABLE tab_name 
    MODIFY field_name field_type;
    

  • 修改字段定义

    ALTER TABLE tab_name 
    CHANGE field_name field_name_new field_type [ props ];
    

  • 删除字段

    ALTER TABLE tab_name 
    DROP field_name;
    

  • 修改表名

    ALTER TABLE tab_name 
    RENAME TO tab_name_new;
    

  • 添加字段约束

    ALTER TABLE tab_name ADD CONSTRAINT ct_name PRIMARY KEY( field_name );  # 添加主键约束
    ALTER TABLE tab_name ADD CONSTRAINT ct_name UNIQUE( field_name );  # 添加唯一约束
    ALTER TABLE tab_name ADD CONSTRAINT ct_name CHECK( field_name > 0 );  # 添加范围约束
    ALTER TABLE tab_name ADD CONSTRAINT ct_name DEFAULT value FOR field_name;  # 添加默认约束
    

约束操作

• **PRIMARY KEY ** - 主键

  # 建表时指定字段 主键 属性
  CREATE TABLE tab_name(
  	field_name field_type PRIMARY KEY,
      ...
  );
  
  # 建表时通过 主键约束 指定字段
  CREATE TABLE tab_name(
  	field_name field_type,
      ...
      PRIMARY KEY( field_name )
  );
  
  # 修改表指定主键
  ALTER TABLE tab_name
  ADD PRIMARY KEY( field_name );
  
  # 修改表字段定义
  ALTER TABLE tab_name
  CHANGE field_name field_name_new field_type PRIMARY KEY;
  
  # 删除主键
  ALTER TABLE tab_name
  DROP PRIMARY KEY;
  

• AUTO_INCREMENT - 自增

  # 建表时指定字段 自增 属性
  CREATE TABLE tab_name(
  	field_name field_type PRIMARY KEY AUTO_INCREMENT,
      ...
  );
  
  # 修改表字段定义时设置自增
  ALTER TABLE tab_name
  ADD PRIMARY KEY( field_name );
  
  # 删除自增
  ALTER TABLE tab_name
  DROP PRIMARY KEY;
  

• UNIQUE - 唯一

  # 建表时指定字段 唯一 属性
  CREATE TABLE tab_name(
  field_name field_type UNIQUE,
      ...
  );
  
  # 建表时指定字段 联合唯一 属性
  CREATE TABLE tab_name(
  	field_name1 field_type,
      field_name2 field_type,
      ...
      UNIQUE KEY (field_name1, field_name2)
  );
  
  # 修改表字段定义时设置自增
  ALTER TABLE tab_name
  ADD PRIMARY KEY( field_name );
  
  # 删除自增
  SHOW CREATE TABLE tab_name;  # 产看 
  

数据库操作语言 - DML

Data Manipulation Language - 数据库操作语言

条件表达式运算符

条件表达式运算符	含义
=	等于
!=	不等于
<>	不等于，不包含
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN m AND n	[m,n] 左右闭区间; 字符串比较是从左到右按位按照其ASCII值来进行比较
IN(o,p,q)	字段值为 o,p,q 中任一为真
NOT 条件表达式	非，取反
条件表达式 AND 条件表达式	与，两条真则真
条件表达式 OR 条件表达式	或，任一真为真
IS NULL	字段内容为 NULL 则真
IS NOT NULL	字段内容非 NULL 则真
LIKE	_:占位符；%:通配符

基本操作

• 插入数据 - INSERT INTO

  • 插入所有字段数据

    INSERT INTO tab_name
    VALUES( value1, value2, ... );
    

  • 插入部分字段数据

    INSERT INTO tab_name(field_name1, field_name2) 
    VALUES(value1, value2);
    

• 更新数据 - UPDATE

  UPDATE tab_name
  SET field_name=value,...
  WHERE 条件表达式
  

• 删除数据 - DELETE FROM

  DELETE FROM tab_name
  [WHERE 条件表达式]
  

数据控制语言 - DCL

Data Control Language - 数据控制语言

用户管理

约定

1. 一个项目创建一个用户，一个项目对应的数据库只有一个

基本操作

• 创建用户

  CREATE USER user_name@ip_addr IDENTIFIED BY 'pass_wd';  # ip_addr -> '%' 为任意ip_addr
  

• 删除用户

  DROP USER user_name@ip_addr
  

权限管理

权限表

Privilege	Grant Table Column	Context
ALL [PRIVILEGES]	Synonym for “all privileges”	Server administration
ALTER	Alter_priv	Tables
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE	Create_priv	Databases, tables, or indexes
CREATE ROUTINE	Create_routine_priv	Stored routines
CREATE TABLESPACE	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables
CREATE USER	Create_user_priv	Server administration
CREATE VIEW	Create_view_priv	Views
DELETE	Delete_priv	Tables
DROP	Drop_priv	Databases, tables, or views
EVENT	Event_priv	Databases
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
GRANT OPTION	Grant_priv	Databases, tables, or stored routines
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or columns
LOCK TABLES	Lock_tables_priv	Databases
PROCESS	Process_priv	Server administration
PROXY	See proxies_priv table	Server administration
REFERENCES	References_priv	Databases or tables
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SELECT	Select_priv	Tables or columns
SHOW DATABASES	Show_db_priv	Server administration
SHOW VIEW	Show_view_priv	Views
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration
TRIGGER	Trigger_priv	Tables
UPDATE	Update_priv	Tables or columns
USAGE	Synonym for “no privileges”	Server administration

权限操作

• 查看用户权限

  SHOW GRANT FOR user_name@ip_addr;
  

• 授予权限

  GRANT 权限清单 ON db_name.tab_name TO user_name@ip_addr;  # db_name.* -> 数据库 db_name 下所有表
  

• 撤销权限

  REVOKE 权限清单 ON db_name.tab_name FROM user_name@ip_addr;
  

• 刷新权限

  flush privileges;
  

数据查询语言 - DQL

Data Query Language - 数据查询语言

基本查询

SELECT *
FROM tab_name
WHERE 条件表达式

排序 - ORDER BY

SELECT *
FROM tab_name
WHERE 条件表达式
ORDER BY field_name [ASC | DESC] [, field_name [ASC | DESC] ... ]

# ASC 升序， DESC 降序
# 如果 排序字段出现相等的值，则该部分按后续排序规则进行排序

字段控制

• 完全重复的记录只显示一次

  SELECT DISTINCT field_list 
  FROM tab_name;
  
  # 当查询的记录出现完全一致时，只显示一次，多数出现在只查询部分列的场景
  

• 字段运算

  • 字段类型为 数量类型 时可进行 四则运算

    SELECT field_name + [-,*,/] value
    FROM tab_name
    

  • 字段类型为 字符串类型 时可进行 连接运算

    SELECT CONCAT(str,str,...)
    FROM tab_name
    
    # 可将合适位置的 str 换成 字段名 进行字符串拼接
    

  • NULL 值转换

    SELECT IFNULL(field_name,value)
    FROM tab_name;
    
    # 将字段值为NUll的用value替换
    

  • 给字段起别名

    SELECT field_name as field_name_new
    FROM tab_name;
    
    # field_name 可为 SELECT 函数产生的列名；
    # field_name field_name_new  <=>  field_name as field_name_new
    

• 聚合函数

  • COUNT( ) - 计数

    # 返回表中总行数，不忽略 NULL，COUNT(1)效果类似
    SELECT COUNT(*)
    FROM tab_name;
    
    # 返回字段除值为 NULL 外的总行数
    SELECT COUNT( field_name )
    FROM tab_name;
      
      # 返回字段值不重复，且忽略 NULL的总行数
      SELECT COUNT( DISTINCT field_name )
      FROM tab_name;
    

  • MAX( field_name ) - 字段最大值

    SELECT MAX( field_name )
    FROM tab_name
    

  • MIN( field_name ) - 字段最小值

    SELECT MIN( field_name )
    FROM tab_name
    

  • SUM( field_name ) - 字段和

    SELECT SUN( field_name )
    FROM tab_name
    
    #忽略 NULL，NULL + 1 = NULL
    

  • AVG( field_name ) - 字段平均值

    SELECT AVG( field_name )
    FROM tab_name;
    
    #忽略 NULL 行
    

分组查询 - GROUP BY

SELECT field_name [, 聚合函数 ]
FROM tab_name
WHERE 条件表达式
GROUP BY field_name

# 查询的字段应该和分组字段保持一致，可组合的列名为聚合函数，在逻辑层面才有意义

LIMIT - MySQL方言

SELECT field_list
FROM tab_name
WHERE 条件表达式
LIMIT m,n

# 查询结果从下标m开始，取n行，类似分页效果

多表查询

• 合并结果集

  对两个结果集进行并集操作

  1. SELECT 语句列的数量、顺序必须相同，字段的类型也要求相似( 合并的是 SELECT 的结果集合 )；

  2. 结果集的列名为第一个SELECT语句中的字段名；

  3. 可带 WHERE 和 ORDER BY 关键字：

  SELECT field_name FROM tab_name
  UNION
  SELECT field_name FROM tab_name
  WHERE 条件表达式
  ORDER BY field_name
  
  # UNION  不包括重复行,相当于 DISTINCT 属性
  # UNION ALL 包括重复的记录
  

• 连接查询

  • 内连接

    只显示满足连接条件的结果集

    # 标准 - INNER JOIN
    SELECT field_list
    FROM tab_name_1 alias_name_1 INNER JOIN tab_name_2 alias_name_2
    ON alias_name_1.field_name = alias_name_2.field_name
    
    # 自然 - NATURAL JOIN  (自动寻找两张表相同的字段，筛选笛卡儿积。)
    SELECT field_list
    FROM tab_name_1 alias_name_1 NATURAL JOIN tab_name_2 alias_name_2
    
    # MySQL 方言，使用 "," 连接表，通过 WHERE 筛选条件
    SELECT field_name
    FROM tab_name_1, tab_name_2
    WHERE 条件表达式
    

  • 外连接

    一主一从，当主表存在不满足连接条件的记录，用NULL补全从表显示

    # LEFT OUTER JOIN - 左表为主表，左表存在右表不存在的记录时，右表查询字段用NULL补全
    SELECT field_list
    FROM tab_name_1 LEFT OUTER JOIN tab_name_2
    ON alias_name_1.field_name = alias_name_2.field_name 
    
    # RIGHT OUTER JOIN - 右表为主表，左表存在右表不存在的记录时，左表查询字段用NULL补全
    SELECT field_list
    FROM tab_name_1 RIGHT OUTER JOIN tab_name_2
    ON alias_name_1.field_name = alias_name_2.field_name 
    

  • 自然连接

    # 内连接
    NATURAL JOIN
    
    # 外连接
    NATURAL LEFT OUTER JOIN
    NATURAL RIGHT OUTER JOIN
    

• 子查询

  查询中有查询

  1. WHERE 后作为条件

     • 单行单列

       SELECT field_list
       FROM tab_name
       WHERE field_name [ =, >, <, >=, <=, != ] ( SELECT ... FROM ... WHERE... )
       

     • 多行单列

       SELECT field_list
       FROM tab_name
       WHERE field_name [ IN, ALL, ANY ] ( SELECT ... FROM ... WHERE... )
       

     • 单行多列

       SELECT field_list
       FROM tab_name
       WHERE field_name IN( SELECT ... FROM ... WHERE... )
       

  2. FROM 后作为数据表，需要使用别名

     • 多行多列

       SELECT alias.field_list
       FROM ( SELECT ... FROM ... WHERE... ) alias
       WHERE 条件表达式
       

概念模型

序号	概念	理解
1	一对多	一张表的一个字段的值取值范围是另一张表的一个字段值
2	多对多	两张表相互存在一对多关系，通过中间表建立关系
3	一对一	副表的主键的取值范围是另一张表的主键(唯一，非空，被引用)

维护

编码

• 查看数据库编码

  SHOW VARIABLES LIKE 'char%';
  

  Variable_name	Value	标注
  character_set_client	utf8mb4	所有客户端发过来的数据，都以 Value 设定的字符集存储
  character_set_connection	utf8mb4
  character_set_database	utf8mb4
  character_set_filesystem	binary
  character_set_results	utf8mb4	服务器把所有查询结果以 Value 设定的字符集发给客户端
  character_set_server	utf8mb4
  character_set_system	utf8mb3
  character_sets_dir	/usr/share/mysql/charsets/

• 临时改编码

  set character_set_client=gbk;
  set character_set_results=gbk;
  
  # 只在当前窗口有效
  

• 永久改编码

  [mysql]
  default-character-set=gbk; 
  
  #同时设定 client,results,connection 字符集
  

备份与恢复

• 备份

  # 通过 shell命令 备份
  mysqldump -uroot -p123 db_name > sql_path
  

• 恢复

  # 通过 shell命令 恢复
  mysql -uroot -p123 db_name < sql_path
  
  # 通过 MySQL客户端 恢复
  MySQL > USE db_name; SOURCE sql_path;