数据库操作(DDL)

数据库的创建

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [[DEFAULT] CHARACTER SET [=] charset_name]

例如：

CREATE DATABASE IF NOT EXISTS  test_db DEFAULT CHARACTER SET utf8

查看当前服务器下的数据库列表

SHOW { DATABASES | SCHEMAS }

查看数据库的定义

SHOW CREATE  { DATABASE | SCHEMA } db_name

查看上一步操作的警告信息

SHOW WARNINGS

删除数据库

DROP  { DATABASE | SCHEMA } [IF EXISTS] db_name

打开数据库

USE db_name

修改数据库的编码方式

ALTER { DATABASE | SCHEMA }  db_name [DEFAULT] CHARACTER SET [=] charset_name

数据表操作

MYSQL中的数据类型

整数类型

浮点类型

字符串类型

日期时间类型

二进制类型

JSON类型(ver5.7新增)

存储引擎

查看MYSQL支持的存储引擎

SHOW ENGINES

查看支持的存储引擎信息

SHOW VARIABLES LIKE 'have%'

查看默认的存储引擎

SHOW VARIABLES LIKE '%storage_engine%'

常用存储引擎(注意各引擎优缺点)

• InnoDB
• MyISAM
• Memory

约束条件

一个表只能有一个主键(PRIMATY KEY),但是能够有多个唯一(UNIQUE KEY)，唯一的字段的值不允许出现重复，但是NULL值不算做重复的值

• PRIMARY KEY 主键，PRIMARY可省略 (注意：PRIMARY KEY(a,b,c,...) 来定义一个主键时,由a,b,c,...所有的值才能确定一个主键，即一个表包括a,b,c...等字段，其中单独的a,b,c字段相等，是可以的，但不能全部相等，全部相等表明主键相等，不可插入。）
• AUTO_INCREMENT 自增,需要和主键(PRIMARY KEY)搭配使用。可在创建表时自定义从某个数开始,如CREATE TABLE tbl_name(id INT key AUTO_INCREMENT,......)AUTO_INCREMENT=数值,表示内部自增字段从此数值开始。(也可通过ALTER TABLE tbl_name AUTO_INCREMENT=数值修改)
• FOREIGN KEY 外键
• NOT NULL 非空
• UNIQUE KEY 唯一，KEY 可省略
• DEFAULT 默认值 插入数据时可使用DEFAULT

数据表的创建

CREATE TABLE [IF NOT EXISTS] tbl_name (
     字段名称 字段类型 [完整性约束条件] ##完整性约束条件顺序为: [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY | UNIQUE [KEY]] AUTO_INCREMENT
     ......
)ENGINE = 引擎名称 CHARSET = 编码方式；

查看当前数据库下的数据表

SHOW TABLES;

查看表结构及创建信息

DESC tbl_name
DESCRIBE tbl_name
SHOW COLUMNS FROM tbl_name
SHOW CREATE TABLE tbl_name

修改表结构

修改表名

ALTER TABLE tbl_name RENAME [TO|AS] new_name

或者

RENAME TABLE tbl_name TO new_name

添加字段

ALTER TABLE tbl_name ADD 字段名称 字段类型 [完整性约束条件]  [ FIRST | (AFTER 字段名称)] 

若需要添加多个字段，需要多个ADD操作，以“，”号分隔

删除字段

ALTER TABLE tbl_name DROP 字段名称

若需要删除多个字段，需要多个DROP操作，以“，”号分隔。

注意:添加字段(ADD)与删除字段(DROP)可以在同一个语句里面(ALTER TABLE tbl_name ADD .....,DROP .....)

修改字段

ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [完整性约束条件] [FIRST | (AFTER  字段名称)]

修改字段名称

ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST | (AFTER  字段名称)]

添加默认值

ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值

删除默认值

ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT

添加主键

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (字段名称,...) 

删除主键

ALTER TABLE tbl_name DROP PRIMARY KEY

添加唯一

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [索引名称] (字段名称,...)

删除唯一

ALTER TABLE tbl_name DROP  {INDEX| KEY} index_name

修改表的存储引擎

ALTER TABLE tbl_name ENGINE=存储引擎名称

修改表的自增长值

ALTER TABLE tbl_name AUTO_INCREMENT= 值

删除数据表

DROP TABLE [IF EXISTS] tbl_name[,tbl_name,...]

数据操作(DML)

插入数据

不指定具体的字段

INSERT [INTO] tbl_name VALUE | VALUES (值,......)

列出指定字段

INSERT [INTO] tbl_name (字段名称1,......) VALUE | VALUES (值1,......)

同时插入多条记录

INSERT [INTO] tbl_name   [(字段名称1,......)]  VALUE | VALUES (值,......), (值,......),......

通过SET形式插入记录

INSERT [INTO] tbl_name SET 字段名称=值,......

将查询结果插入到表中

INSERT [INTO] tbl_name   [(字段名称1,......)]  SELECT 字段名称 FROM tbl_name  [WHERE 条件]

更新数据

UPDATE tbl_name  SET 字段名称=值,...... [WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条件]

删除数据

(DELETE FROM tbl_name  [WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条件])

或者彻底清空数据表

(TRUNCATE [TABLE ] tbl_name)

查询数据操作(DQL)

查询记录

SELECT select_expr[,select_expr,...]
[ 
    FROM table_reference
    [WHERE 条件]
    [GROUP BY {col_name | position } [ ASC | DESC], ... 分组]
    [HAVING 条件 对分组结果进行二次筛选]
    [ORDER BY {col_name | position } [ ASC | DESC], ... 排序]
    [LIMIT 限制显示条数]
]

查询表达式

每个表达式表示想要的一列，必须至少有一列，多个列以","号分隔； "*" 表示所有列，tbl_name.*表示命名表的所有列。查询表达式可以使用 [AS] alias_name 为其赋予别名。

WHERE条件

模糊查询

• %：代表0个，1个或者多个任意字符
• _ ：代表1个字符

GROUP BY 查询结果分组

默认情况下，GROUP BY 查询结果分组得到每组的第一个值
配合GROUP_CONCAT()得到分组详情
配合聚合函数

• COUNT() NULL值不算一条记录
• MAX()
• MIN()
• AVG()
• SUM()

配合WITH ROLLUP记录上面所有记录的总和

5.通过HAVING子句对分组结果进行二次筛选

配合GROUP BY 实现，比如：

SELECT Region,COUNT(*),MAX(Population),MIN(Population),SUM(Population),AVG(Population) FROM country GROUP BY Region HAVING MAX(Population) > 1000000

通过ORDER BY 进行排序

默认升序排列(ASC),可省略。降序排列(DESC),可排序多个字段通过","号连接

SELECT * FROM country ORDER BY Population ASC,SurfaceArea ASC
SELECT * FROM country ORDER BY 3 ASC
SELECT * FROM country ORDER BY RAND() #随机记录

LIMIT限制查询结果显示条数

• LIMIT 显示条数
• LIMIT 偏移量，显示条数

(SELECT * FROM country LIMIT 2,1)

连接查询

什么是链接查询

连接查询是将两个或者两个以上的表按照某个条件连接起来，从中选取需要的数据。连接查询是同时查询两个或者两个以上的表时使用的。当不同的表中存在相同意义的字段时，可通过该字段连接这几个表。

内连接查询

1. JOIN | CROSS JOIN | INNER JOIN
2. 通过ON 连接条件
3. 显示两个表中符合连接条件的记录

注意：三张表或者三张表以上查询可以直接加入更多的JOIN ...ON ...来连接多张表，注意找准表之间的连接条件

例如:

SELECT 
    user_info.id, user_info.name, city_info.pro_name
FROM
    user_info
        JOIN
    city_info ON user_info.pro_id = city_info.pro_id;

外连接查询

左外连接

(LEFT [OUTER] JOIN)

显示左表的全部记录及右表符合连接条件的记录

右外连接

(RIGHT [OUTER] JOIN)

显示右表的全部记录及左表符合连接条件的记录

外键

概览

外键是表的一个特殊字段，被参照的表是主表，外键所在字段的表为子表。设置外键的原则需要记住，就是依赖于数据库中已存在的表的主键。外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时，子表中与之对应的信息也应有相应的改变。
外键的作用是保持数据库的一致性和完整性。
可以实现一对一或者一对多的关系

注意

1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
2. 数据库的存储引擎只能为InnoDB。
3. 外键列和参照列必须具有相似的数据类型其中数字的长度或是否有符号位必须相同：而字符的长度则可以不同。
4. 外键列和参照列必须创建索引。如果外键不存在索引的话，MYSQL将自动创建索引。

外键约束的参照操作：

• CASCADE:从父表删除或者更新且自动删除或者更新子表中匹配的行。
• SET NULL:从父表删除或者更新行，并设置子表的中的外键列为NULL。如果使用该选项必须保证子表列中没有指定NOT NULL。
• RESTRICT:拒绝对父表的更新或者删除操作。
• NOT ACTION: 标准SQL的关键字，在MYSQL中与RESTRICT相同

创建外键(先有主表)

CREATE TABLE [IF NOT EXISTS] tbl_name (
 字段名称 字段类型 [完整性约束条件] ##完整性约束条件顺序为: [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY | UNIQUE [KEY]] AUTO_INCREMENT
...
[CONSTRAINT 外键名称] FOREIGN KEY(字段名) REFERENCES 父表(字段名) [ON {DELETE | UPDATE } {CASCADE | SET NULL }] [ON {DELETE | UPDATE } {CASCADE | SET NULL}]
)

删除外键

(ALTER TABLE tbl_name DROP FOREIGN KEY)

添加外键

(ALTER TABLE tbl_name ADD [CONSTRAINT 外键名称]  FOREIGN KEY(字段名) REFERENCES 父表(字段名))

示例：

部门表(父表)

CREATE TABLE IF NOT EXISTS department (id TINYINT UNSIGNED AUTO_INCREMENT KEY,depName VARCHAR(20) NOT NULL UNIQUE)ENGINE=INNODB;

员工表(子表)

CREATE TABLE IF NOT EXISTS employee (id TINYINT UNSIGNED AUTO_INCREMENT KEY,userName VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED)ENGINE=INNODB;

如果只删除父表中部门4，那么员工表中还有属于部门4的员工存在，这显然是不合适的，这时，可以使用外键来解决

创建外键(先有主表)：

CREATE TABLE IF NOT EXISTS employee (id TINYINT UNSIGNED AUTO_INCREMENT KEY,userName VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED ， FOREIGN KEY(depId) REFERENCES deparment(id) )ENGINE=INNODB;

联合查询

UNION

(SELECT 语句 UNION SELECT 语句)

UNION ALL

(SELECT 语句 UNION SELECT 语句)

注意：UNION 和 UNION ALL 的区别是UNION 去掉相同记录，UNION是简单的合并到一起

子查询

什么是子查询

子查询是将一个查询语句嵌套在另一个查询语句之中。内层查询语句的查询结果，可以为外层查询语句提供条件

引发子查询的情况

• 使用 [NOT] IN 的子查询
• 使用比较运算符的子查询 = > < >= <= <> != <=>
• 使用 [NOT] EXISTS 的子查询
• 使用 ANY | SOME 或者 ALL的子查询

示例：

SELECT name FROM user where depId IN(SELECT id FROM ids)
SELECT name FROM user where depId >=(SELECT id FROM ids)
SELECT name FROM user where EXISTS(SELECT id FROM ids)

将查询结果写入到数据表

(INSERT [INTO] tbl_name  [(col_name),......] SELECT ...)

创建数据表的同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tbl_name (
  [(creat_defination)]
  select_statement

正则表达式查询

REGEXP '匹配方式'

(SELECT * FROM user WHERE REGEXP '^[1-9]$')

常用匹配方式

运算符

算数运算符

比较运算符

逻辑运算符

运算符的优先级

注意: 可以使用()改变优先级

MYSQL中的函数

数学函数

字符串函数

日期时间函数

条件判断函数

系统信息函数

加密函数

其他常用函数

索引

什么是索引

• 索引由数据库中一列或者多列组成，其作用是提高对表中数据的查询速度
• 索引的优点是可以提高检索数据的速度
• 索引的缺点是创建和维护索引需要耗费时间
• 索引可以提高查询速度，减慢写入速度

索引分类

• 普通索引
• 唯一索引(UNIQUE)
• 全文索引(FULLTEXT)
• 单列索引
• 多列索引
• 空间索引(数据类型为GEOMETRY,存储引擎为MyISAM)SPATIAL

创建索引

创建表的时候创建索引

CREATE TABLE [IF NOT EXISTS] tbl_name (
     字段名称 字段类型 [完整性约束条件] ##完整性约束条件顺序为: [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY | UNIQUE [KEY]] AUTO_INCREMENT
     ......
 [UNIQUE | FULLTEXT | SPATIAL ] INDEX|KEY [索引名称](字段名称[(长度)] [ASC|DESC]) 
)ENGINE = 引擎名称 CHARSET = 编码方式；

在已存在的表上创建索引

(CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX  索引名称 ON 表名 {字段名称[(长度)]  [ASC|DESC] ) })

或者

(ALTER TABLE tbl_name ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名称(字段名称[(长度)] [ASC|DESC]) )

删除索引

(DROP INDEX 索引名称 ON tbl_name)

其他

注释

• 以‘-- ’ 为前缀
• 以'#' 为前缀
• 字段注释通过COMMENT '.....'进行注释