MySql数据库的基本语法

MySQL

0. SQL语言的分类

• 数据库查询语言DQL：select、where、order by、group by、having
• 数据库定义语言DDL：create、alter、drop
• 数据库操作语言DML：update、delete、insert
• 事务处理语言TPC：commit、rollback
• 数据控制语言DCL：grant、revoke

1.SQL语言

通常对数据库的增删改查，简称为C(create)R(read)U(update)D(delete)。

2.基本命令

#创键自定义数据库
CREATE DATABASE 数据库名;
CREATE DATABASE 数据库名 CHARACTER SET 编码格式;
CREATE DATABASE IF NOT EXISTS 数据库名;
# 查看数据库创键信息
SHOW CREATE DATABASE 数据库名;
# 查看当前使用的数据库
SELECT DATABASE()
# 使用数据库
USE 数据库名;

3. 数据查询【DQL】

3.1 数据库表的基本结构

关系数据库是以表格(Table)进行数据存储，表格由"行"和"列"组成

• 经验：执行查询语句返回的结果集是一张虚拟表。

3.2 SQL语句执行顺序

from->on->join->where->group by->having->select->distinct->order by->limit

3.2.1 SQL语句编写顺序

3.2.2 条件查询

语法：SELECT 列名 FROM 表名 WHERE 条件;

1.关系判断(>,<,=,!=,>=,<=,<>【不等于】)

2.逻辑判断(and,or,not)

3.区间判断(between ... and)

4.控制判断(IS NULL、IS NOT NULL)

5.枚举查询(IN(值1，值2...))

6.模糊查询(LIKE _:单个任意字符,%:任意长度字符)

7.分支查询(CASE-WHERE-END)

8.子查询【重点】

1.作枚举查询条件 - 放在WHERE中 - 多行单列

• 注：当子查询结果集为"多行单列"时可以使用ALL或ANY关键字

2.作为一张表 - 放在FROM后 - 多行多列

语法：SELECT 列名 FROM (子查询的结果集) AS 表别名 WHERE 条件;

• 注：将子查询的"多行多列"的结果作为外部查询的一张表,做第二次查询
• 注：子查询作为临时表，可以为其创建一个临时表名

3.2.3 时间查询

语法：SELECT 时间函数([参数列表])

• 经验：执行事件函数查询，会自动生成一张虚表(一行一列)

3.2.3 字符串查询

语法：SELECT 字符串函数([参数列表])

3.3 聚合函数 - 自动忽略null值，不进行统计

语法：SELECT 聚合函数(列名) FROM 表名;

• 经验：对多条数据的单列进行统计，返回统计后的一行结果。
  

3.4 分组查询

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据(列);

• 注：分组查询中，select显示的列只能是分组依据列，或者聚合函数，不能出现其他列。

3.5 分组过滤查询

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则;

3.6 限定查询

语法：SELECT 列名 FROM 表名 LIMIT 起始行,查询行数;

3.7 合并查询

；合并两张表的的结果(去除重复记录)

SELECT * FROM 表名1 UNION SELECT * FROM 表名2；

；合并两张表的结果(不去出重复记录)

SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2;

3.8 表连接查询

语法：SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件；

连接方式:

INNER JOIN:内连接,匹配不到不返回

LEFT JOIN ON:左外连接,以左表为主表依次向右匹配，匹配到返回结果，匹配不到对应列返回NULL值填充

RIGHT JOIN ON:右外连接,以右表为主表依次向左匹配，匹配到返回结果，匹配不到对应列返回NULL值填充

4. DML操作【重点】

4.1 新增(INSERT)

增加一行：INSERT INTO 表名(列1，列2...) VALUES(值1，值2...);

增加多行：INSERT INTO 表名(列1，列2...) VALUES(值1，值2),(值1，值2),...,(值1，值2);

4.2 修改(UPDATE)

UPDATE 表名 SET 列1 = 值1，列2 = 值2，... WHERE 条件；

4.3 删除(DELETE)

DELETE FROM 表名 WHERE 条件；

• 注意：删除时，如如不加WHERE条件，删除的是整张表的数据

4.4 清空整张表 - 清空数据

TRUNCATE TABLE 表名;

• 注：RRUNCATE与DELETE不加WHERE删除整表数据不同，TRUNCATE是把原表销毁，再建一个新表

5. 数据表操作

5.1 数据表的创键(CREATE)

CREATE TABLE 表名(
列名 数据类型 [约束],
列名 数据类型 [约束]
)[charset=utf8] 

5.2 数据表的修改(ALTER)

ALTER TABLE 表名 操作；

5.2.1 向现有表中添加列

ALTER TABLE 表名 ADD 列名 数据类型;

5.2.2 修改表中的列

ALTER TABLE 表名 MODIFY 列名 数据类型;

5.2.3 删除表中的列

ALTER TABLE 表名 DROP 列名;

5.2.4 修改列名

ALTER TABLE 表名 CHANGE 列名 新列名 数据类型;

• 注：修改列名时，在给定列新名称时，要指定列的类型和约束

5.3 数据表的删除(DROP)

# 删除学生表
DROP TABLE subject;

6. 约束

6.1 实体完整性约束 - 限制行

表中的一行数据代表一个实体(entity),实体完整性的作用是标识每一行数据不重复、实体唯一。

6.1.1 主键约束

PRIMARY KEY唯一，标识表中的一行数据，此列的值不可重复，且不能为NULL。

6.1.2 唯一约束

UNIQUE唯一，标识表中的一行数据，不可重复，可以为NULL

6.1.3 自动增长列

AUTO_INCREMENT自动增长，给主键数值列添加自动增长。从1开始，每次加1。不能单独使用和主键配合。

6.2 域完整性约束 - 限制列

限制列的单元格的数据正确性

6.2.1 非空约束

NOT NULL非空，此列必须有值

6.2.2 默认值约束

DEFAULT值为列赋予默认值，当新增数据不指定值时，书写DEFAULT,以指定的默认值进行填充。

6.2.3 引用完整性约束【重点】

语法：CONSTRAINT 引用名 FOREIGN KEY(列名) REFERENCES 被引用表名(列名)

详解：FOREIGN KEY引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值。

• 注意：当两张表存在引用关系，要执行删除操作，一定要先删除从表(引用表)，在删除主表(被引用表)
• 注意：创键关联表时，先创建主表，在创建从表

6.3 常用约束用法

6.3.1 主键自增长

PRIMARY KEY AUTO_INCREMENT

6.3.2 唯一非空约束

UNIQUE NOT NULL

7. 事务【重点】

7.1 事务的概念

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

7.2 事务的边界

开始：连接到数据库，执行一条DML(增，删，改)语句。上一个事务结束后，又输入一条DML语句，即事务的开始。

结束：

1. 提交:

a.显示提交：commit；

b.隐式提交：一条创键、删除的语句，正常退出(客户端退出连接);

2)回滚：

a.显示回滚：rollback；

b.隐式回滚：非正常退出(断电、宕机),执行里创键、删除的语句，但是失败了，会为这个无效的语句执行回滚。

7.3 事务的原理

数据库会为每个客户端都维护一个空间独立的缓存区(回滚段),一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束(commit),才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚(rollback)。

7.4 事务的特性

• Atomicity(原子性)

表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败

• Consistency(一致性)

表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态。

• Isolation(隔离性)

事务查看数据操作是数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

• Durability(持久性)

持久性事务完成之后，它对于系统的影响是永久性的。

7.5 事务应用

应用环境：基于增删改语句的操作结果(均返回操作后受影响的行数)，可通过程序逻辑手动控制事务提交或回滚。

#1.开启事务
START TRANSACTION;
#2.禁止自动提交
set AutoCommit=0;
#3.事务内执行DML语句
#4.事务内语句都成功了,执行commit
COMMIT
#5.事务内如果出现错误，执行ROLLBACK；
ROLLBACK

8. 权限管理

8.1 创建用户

CREATE USER 用户名 IDENTIFIED BY 密码;

8.2 授权 - GRANT

GRANT ALL ON 数据库.表 TO 用户名;

8.3 撤销权限

REVOKE ALL ON 数据库.表 FROM 用户名；

• 注：具体权限可以百度，ALL是所有权限

8.4 删除用户

DROP USER 用户名;

9. 视图 - 就是为了方便查询使用的，一般仅用于查询使用

9.1 视图概念 - 保障数据库系统安全

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

当用户对数据库中的一张或者多张表的某些字段的组合感兴趣，而又不想每次键入这些查询时，用户就可以定义一个视图，以便解决这个问题。视图中列可以来自于表里的不同列，这些列都是用户所感兴趣的数据列。

视图与表不同，它在物理上不是真实存在的，而是一个虚表。在数据库里仅存放视图的定义，而不存放视图对应的数据。视图中的这些数据存放在其对应的表中，如果表中的数据发生了变化，从视图中查询出的数据也会随之发生改变。从这个意义来看，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据及变化。每一次查看视图或引用视图的的时候，都会运行一次视图上的查询。

用户可以使用SELECT语句从视图里查询数据，对于符合一定约束条件的视图，还可以使用INSERT、UPDATE、DELETE、MERGE INTO等语句修改视图对应的基础表里的数据。视图在提供操作方便的同时，还可以保障数据库数据的安全。

9.2 视图特点

• 优点
  • 简单化，数据所见即所得
  • 安全性，用户只能查询或修改他们所能见得到的数据。
  • 逻辑独立性，可以屏蔽真实表结构变化带来的影响。
• 缺点
  • 性能相对较差，简单的查询也会变得稍显复杂。
  • 修改不方便，特别是负责的聚合视图基本无法修改。

9.3 视图的创建

9.3.1 创键视图

语法：CREATE VIEW 视图名 AS 查询数据源表语句；

9.3.2 使用视图

把视图当作表正常操作即可

9.3.3 视图的修改

• 方式一：CREATE OR REPLACE VIEW 视图名 AS 查询语句 ； 如果视图存在则进行修改，反之创键
• 方式二：ALTER VIEW 视图名 AS 查询语句 ； 直接对已存在的视图进行修改

9.3.4 视图的删除

DROP VIEW 视图名

9.3.5 视图的注意事项

1.视图不会独立存储数据，原表发生改变，视图也发生改变。没有任何查询优化。

2.如果试图包含以下结构中的一种，则视图不可更新

• 聚合函数的结果
• DISTINCE去重后的结果
• GROUP BY 分组后的结果
• HAVING 筛选后的结果
• UNION、UNION ALL联合后的结果

9.4 什么时候使用视图

当需要对某的表的一列或几列单独查询时,为了减少麻烦,可以使用视图把这几列单独取出来

10. 索引 - 为了优化查询,一般作用于表的字段上

10.1 索引的作用

索引的目的在于提高查询效率

10.2 创建索引

语法:CREATE INDEX 索引名称 ON 表名(字段名称(长度))

10.3 索引的优缺点

1.使用索引会影响更新和插入速度,因为还要修改索引文件

2.索引要占用内存

3.一般之间以给需要经常查询的表的字段建立索引,加快查询速度