MySQL语句大全（快速入门）

SQL语句大全（快速学会MySQL语句）

一.MySQL数据库的基本操作

1.创建数据库 CREATE DATABASE [数据库名]

CREATE DATABASE test_db;

2.修改数据库 AlTER DATABASE [数据库名]

ALTER DATABASE用于更改数据库的全局特性。
这些特性储存在数据库目录中的db.opt文件中。要使用ALTER DATABASE，您需要获得数据库ALTER权限。
CHARACTER SET 子句用于更改默认的数据库字符集。
COLLATE 子句用于更改默认的排序规则

 ALTER DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

3.选择数据库 USE 数据库名

USE test_db;

4.删除数据库 DROP DATABASE [IF EXISTS 数据库名]

DROP DATABASE IF EXISTS test_db_del;

5.查看数据库 SHOW DATABASES [LIKE ‘数据库名’]

SHOW DATABASES like 'test_db';
SHOW DATABASES like '%test%';
SHOW DATABASES like '%db';

6.MySQL注释

单行注释使用#或 --，不同之处在于，#后面直接跟注释，–后面加个空格才能写注释
多行注释使用/* */注释符。/用于注释内容的开头，/用于注释内容的结尾。

二.数据表的基本操作

1.创建数据表 CREATE TABLE <表名>

USE test_db;

CREATE TABLE test_tb(id INT8 primary key auto_increment,name VARCHAR(10),salary DOUBLE(10,2),bir TIMESTAMP);

要创建的表的名称不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
数据表中每个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。

primary key auto_increment 指主键自增

DOUBLE(10,2)指总长度为10，精确到小数点后两位

2.修改数据表 ALTER TABLE <表名> [修改选项]

数据表有很多内容可以修改，具体选项如下：
可修改内容ADD COLUMN <列名> <类型>CHANGE COLUMN <旧列名> <新列名> <新列类型>ALTER COLUMN <列名> { SET DEFAULT <默认值>MODIFY COLUMN <列名> <类型>DROP COLUMN <列名>RENAME TO <新表名>CHARACTER SET <字符集名>COLLATE <校对规则名>
例子如下：

ALTER TABLE test_tb 
ADD COLUMN age int;

3.删除数据表 DROP TABLE [IF EXISTS] <表名>

DROP TABLE IF EXISTS test_tb;

4.删除关联的表

数据表之间经常存在外键关联的情况，这时如果直接删除父表，会破坏数据表的完整性，也会删除失败。

先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。
首先我们来创建一个test_tb的子表test_tb2，具有外键约束，其主表test_tb不能直接删除

CREATE TABLE test_tb2
(
id INT(11) PRIMARY KEY,
deptId INT(11),
CONSTRAINT fk_tb1_tb2 FOREIGN KEY (deptId) REFERENCES test_tb(id) 
);

删除掉这个外键，主表就可以删除了

ALTER TABLE test_tb2 DROP FOREIGN KEY fk_tb1_tb2;

5.查看表结构

使用DESCRIBE <表名>以表格的形式展现

使用SHOW CREATE TABLE <表名>以SQL语句的形式展现

DESCRIBE test_tb;
SHOW CREATE TABLE test_tb;

6.数据表添加字段

默认在最后一列添加，ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];

ALTER TABLE test_tb ADD gender VARCHAR(20);

如果想在第一字段添加，在最后加上FIRST，如果想在特定的某一字段添加，在最后加AFTER某一字段。

三.MySQL约束概述

在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。例如，在数据表中存放年龄的值时，如果存入 200、300 这些无效的值就毫无意义了。因此，使用约束来限定表中的数据范围是很有必要的。

1.主键约束

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。\
主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

设置单字段主键在定义字段的同时定义主键，<字段名> <数据类型> PRIMARY KEY [默认值]

id INT(11) PRIMARY KEY

或者是在定义完所有字段之后指定主键 PRIMARY KEY [字段名] \

CREATE TABLE test_tb2
(
    id INT(11),
    deptId INT(11),
    KEY fk_tb1_tb2 (deptId),
    PRIMARY KEY(id)
);

设在创建表时创建联合主键所谓的联合主键，就是这个主键是由一张表中多个字段组成的。当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。\

CREATE TABLE test_tb2
(
    id INT(11),
    deptId INT(11),
    KEY fk_tb1_tb2 (deptId),
    PRIMARY KEY(id，deptId)
);

在修改表时添加主键约束主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。但是需要注意的是，设置成主键约束的字段中不允许有空值。\

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

删除主键约束当一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。 \

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

3.主键自增长

通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。语法格式如下：

字段名 数据类型 AUTO_INCREMENT

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。

字段名 数据类型 AUTO_INCREMENT = 100

4.外键约束

创建时添加外键约束 MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。从表的外键必须是主表的主键主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。创建数据表 tb_emp2，并在表 tb_emp2 上创建外键约束，让它的键 deptId 作为外键关联到表 tb_emp1 的主键 id，SQL 语句如下所示。\

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

CREATE TABLE tb_emp2
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept1
FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

修改表时添加外键约束外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。\

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

删除外键约束

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

5.唯一约束

MySQL 唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。如果其中一条记录的 id 值为‘0001’，那么该表中就不能出现另一条记录的 id 值也为‘0001’。

创建表时添加唯一约束在定义完列之后直接使用 UNIQUE 关键字指定唯一约束，语法格式如下：

<字段名> <数据类型> UNIQUE

修改表时添加唯一约束在修改表时添加唯一约束的语法格式为：

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

删除唯一约束\

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

6.检查约束

MySQL 检查约束（CHECK）是用来检查数据表中字段值有效性的一种手段，可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。\

创建表时设置检查约束一般情况下，如果系统的表结构已经设计完成，那么在创建表时就可以为字段设置检查约束了。创建表时设置检查约束的语法格式如下：

CHECK(<检查约束>)

例：

CREATE TABLE tb_emp2
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT,
    CHECK(salary>0 AND salary<100),
    FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

修改表时设置检查约束

ALTER TABLE tb_emp2 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

删除检查约束

ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

7.MySQL默认值

默认值（Default）的完整称呼是“默认值约束（Default Constraint）”，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。\

在创建表时设置默认值约束\

<字段名> <数据类型> DEFAULT <默认值>;

字段名> <数据类型> DEFAULT <默认值>;

在修改表添加默认值约束\

ALTER TABLE <数据表名>
CHANGE COLUMN <原字段名> 
<新字段名><数据类型> DEFAULT <默认值>;`\
例：`ALTER TABLE test_tb
CHANGE COLUMN age
age1 INT(11) DEFAULT(0);

删除默认值约束将修改时的<默认值>改为NULL；

8.MySQL非空约束

同7.MySQL默认值，但是将Default改为NOT NULL

9.查看表中的约束

SHOW CREATE TABLE <数据表名>;

10.MySQL算数运算符

算术运算符是 SQL 中最基本的运算符，MySQL 支持的运算符包括加、减、乘、除和取余运算，它们是最常用、最简单的一类运算符。下表列出了这些运算符的作用和使用方法。
运算符作用使用方法+加法运算用于获得一个或多个值的和-减法运算用于获得一个或多个值的和*乘法运算用于获得一个或多个值的和/除法运算用一个值除以另一个值得到商% MOD求余运算返回余数用一个值除以另一个值得到余数

11.MySQL逻辑运算符

逻辑运算符又称为布尔运算符，用来确定表达式的真和假。MySQL中支持的逻辑运算符如下表所示。
运算符作用NOT或者!逻辑非AND或者&&逻辑与OR或者||逻辑或XOR逻辑异或

12.MySQL比较运算符

运算符作用=等于<=>安全的等于<> 或者 !=不等于<=小于等于>=大于等于>大于IS NULL 或者 ISNULL判断一个值是否为空IS NOT NULL判断一个值是否不为空BETWEEN AND判断一个值是否落在两个值之间

13.MySQL位运算符

运算符说明使用形式举例|位或a|b5|8&位与a & b5 & 8^位异或a ^ b5 ^ 8~~位取反~~a~5<<位左移a << b5 << 2，表示整数 5 按位左移 2 位>>位右移a >> b5 >> 2，表示整数 5 按位右移 2 位

14.MySQL运算符优先级

运算符的优先级决定了不同的运算符在表达式中计算的先后顺序，下表列出了 MySQL 中的各类运算符及其优先级。
优先级由低到高排列运算符1=(赋值运算）、:=2II、OR3XOR4&&、AND5NOT6BETWEEN、CASE、WHEN、THEN、ELSE7=(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN8|9&10<<、>>11-(减号）、+12*、/、%13^14-(负号）、〜（位反转）15!
在无法确定优先级的情况下，可以使用圆括号“()”来改变优先级，并且这样会使计算过程更加清晰。

15.IN , NOT IN

MySQL 中的 IN 运算符用来判断表达式的值是否位于给出的列表中；如果是，返回值为 1，否则返回值为 0。

NOT IN 的作用和 IN 恰好相反，NOT IN 用来判断表达式的值是否不存在于给出的列表中；如果不是，返回值为 1，否则返回值为 0。
例：

SQL SELECT 2 IN (1,3,5,'thks'),'thks' IN (1,3,5, 'thks');

16.MySQL函数

MySQL 函数是 MySQL 数据库提供的内部函数，这些内部函数可以帮助用户更加方便地处理表中的数据。函数就像预定的公式一样存放在数据库里，每个用户都可以调用已经存在的函数来完成某些功能。
MySQL 函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。这些函数不仅能帮助用户做很多事情，比如字符串的处理、数值的运算、日期的运算等，还可以帮助开发人员编写出简单快捷的 SQL 语句。
SELECT、INSERT、UPDATE 和 DELETE 语句及其子句（例如 WHERE、ORDER BY、HAVING 等）中都可以使用 MySQL 函数。例如，数据表中的某个数据是负数，现在需要将这个数据显示为整数，这时就可以在 SELECT 语句中使用绝对值函数。
常用函数大全

三.MySQL操作表中数据

MySQL 提供了功能丰富的数据库管理语句，包括向数据库中插入数据的 INSERT 语句，更新数据的 UPDATE 语句，以及当数据不再使用时，删除数据的 DELETE 语句。

1.数据表查询语句

在 MySQL 中，可以使用 SELECT 语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
SELECT 的语法格式如下：

{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

查询所有字段

SELECT * FROM 表名;

查询指定的字段

SELECT < 列名 > FROM < 表名 >;

查询多个字段的时候，将列名用逗号隔开

2.MySQL使用DISTINCT去重

在 MySQL 中使用 SELECT 语句执行简单的数据查询时，返回的是所有匹配的记录。如果表中的某些字段没有唯一性约束，那么这些字段就可能存在重复值。为了实现查询不重复的数据，MySQL 提供了 DISTINCT 关键字。
DISTINCT 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据给用户。

SELECT DISTINCT <字段名> FROM <表名>;

因为 DISTINCT 只能返回它的目标字段，而无法返回其它字段，所以在实际情况中，我们经常使用 DISTINCT 关键字来返回不重复字段的条数。

SELECT COUNT(DISTINCT name,age) FROM student;

3.指定别名

当表名很长或者执行一些特殊查询的时候，为了方便操作，可以为表指定一个别名，用这个别名代替表原来的名称。

为表名指定别名

<表名> [AS] <别名>

为字段指定别名

<字段名> [AS] <别名>

注：AS可以省略，省略后要将原字段名和别名中间加空格隔开

4.MySQL限制查询结果的条数

当数据表中有上万条数据时，一次性查询出表中的全部数据会降低数据返回的速度，同时给数据库服务器造成很大的压力。这时就可以用 LIMIT 关键字来限制查询结果返回的条数。

指定初始位置 LIMIT 初始位置记录数

SELECT * FROM tb_students_info LIMIT 3 5;

不指定初始位置不指定初始位置时，默认从第一条记录开始，如果“记录数”的值大于查询结果的总数，则会直接显示查询出来的所有记录。

SELECT * FROM tb_students_info LIMIT 4;

LIMIT和OFFSET混合使用 LIMIT 5 OFFSET 3”意思是获取从第 4 条记录开始的后面的 5 条记录，和“LIMIT 3，5”返回的结果相同。

5.MySQL对查询结果进行排序

为了使查询结果的顺序满足用户的要求，MySQL 提供了 ORDER BY 关键字来对查询结果进行排序。

ORDER BY <字段名> [ASC|DESC]

字段名：表示需要排序的字段名称，多个字段时用逗号隔开。
ASC|DESC：ASC表示字段按升序排序；DESC表示字段按降序排序。其中ASC为默认值。

SELECT * FROM tb_students_info ORDER BY height;

在对多个字段进行排序时，排序的第一个字段必须有相同的值，才会对第二个字段进行排序。如果第一个字段数据中所有的值都是唯一的，MySQL 将不再对第二个字段进行排序。

SELECT name,height FROM tb_student_info ORDER BY height DESC,name ASC;

DESC 关键字只对前面的列进行降序排列，在这里只对 height 字段进行降序。因此，height 按降序排序，而 name 仍按升序排序。如果想在多个列上进行降序排序，必须对每个列指定 DESC 关键字。

6.MySQL条件查询

在 MySQL 中，如果需要有条件的从数据表中查询数据，可以使用 WHERE 关键字来指定查询条件。

SELECT name,height FROM tb_students_info
WHERE height=170;

SELECT name,age FROM tb_students_info
WHERE age<22;

SELECT name,age,height FROM tb_students_info 
WHERE age>21 AND height>=175;

SELECT name,age,height FROM tb_students_info 
WHERE age>21 XOR height>=175;

7.MySQL模糊查询

在 MySQL 中，LIKE 关键字主要用于搜索匹配字段中的指定内容。
格式如下：

[NOT] LIKE '字符串'

NOT ：可选参数，字段中的内容与指定的字符串不匹配时满足条件\
字符串：指定用来匹配的字符串。“字符串”可以是一个很完整的字符串，也可以包含通配符。

LIKE 关键字支持百分号“%”和下划线“_”通配符。“%”是 MySQL 中最常用的通配符，它能代表任何长度的字符串，字符串的长度可以为 0。“_”只能表示一个字符，字符的长度不能为0。

通配符是一种特殊语句，主要用来模糊查询。当不知道真正字符或者懒得输入完整名称时，可以使用通配符来代替一个或多个真正的字符。如果查询内容中包含通配符，可以使用“\”转义符。

SELECT name FROM tb_students_info
WHERE name LIKE 'T%';

SELECT name FROM tb_students_info
WHERE name LIKE '____y';

默认情况下，LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写，可以加入 BINARY 关键字。

SELECT name FROM tb_students_info WHERE name LIKE BINARY 't%';

8.MySQL范围查询

MySQL 提供了 BETWEEN AND 关键字，用来判断字段的数值是否在指定范围内。

SELECT name,age FROM tb_students_info 
WHERE age BETWEEN 20 AND 23;

SELECT name,age FROM tb_students_info 
WHERE age NOT BETWEEN 20 AND 23;

9.MySQL空值查询

MySQL 提供了 IS NULL 关键字，用来判断字段的值是否为空值（NULL）。空值不同于 0，也不同于空字符串。IS NOT NULL相反。

如果字段的值是空值，则满足查询条件，该记录将被查询出来。如果字段的值不是空值，则不满足查询条件。

SELECT name,login_date FROM tb_students_info 
WHERE login_date IS NULL;

10.MySQL使用分组查询

在 MySQL 中，GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。

GROUP BY单独使用单独使用GROUP BY时，只会显示每组的第一条记录

SELECT name,sex FROM tb_students_info 
GROUP BY sex;

GROUP BY 与 GROUP_CONCAT() GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

SELECT sex, GROUP_CONCAT(name) FROM tb_students_info 
GROUP BY sex;

GROUP BY 与聚合函数在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。
函数名作用COUNT()用来统计记录的条数SUM()用来计算字段值的总和AVG()用来计算字段值的平均值MAX()用来查询字段的最大值MIN()用来查询字段的最小值
```
SELECT sex,COUNT(sex) FROM tb_students_info
GROUP BY sex;



- GROUP BY与WITH ROLLUP WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

SELECT sex,GROUP_CONCAT(name) FROM tb_students_info
GROUP BY sex WITH ROLLUP;


#### 10.MySQL过滤分组

在 MySQL 中，可以使用 HAVING 关键字对分组后的数据进行过滤。HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。


- 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
- WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
- WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
- WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
- WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

SELECT name,sex FROM tb_students_info
WHERE height>150;


这里使用WHERE可以查询到,但是使用HAVING就会报错，因为HAVING是针对查询结果进行过滤，没有查询height字段，也就无法过滤height>150的字段。

SELECT GROUP_CONCAT(name),sex,height FROM tb_students_info
GROUP BY height
HAVING AVG(height)>170;


但是如果在 WHERE 查询条件中使用聚合函数，MySQL 会提示错误信息：无效使用组函数。

#### 11.MySQL交叉连接

在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。

交叉连接（CROSS JOIN）一般用来返回连接表的笛卡尔积。笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。

例如，有 A 和 B 两个集合，它们的值如下：

A = {1,2}, B = {3,4,5}


集合 A×B 和 B×A 的结果集分别表示为：

A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) }; B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };


交叉连接语法:

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]

或

SELECT <字段名> FROM <表1>, <表2> [WHERE子句]


多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。

SELECT * FROM tb_course CROSS JOIN tb_students_info
WHERE tb_students_info.course_id = tb_course.id;


如果在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。因此，表的数量较多时，交叉连接会非常非常慢。一般情况下不建议使用交叉连接。

#### 12.内连接

内连接（INNER JOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。

内连接的语法格式如下：

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]


多个表内连接时，在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。

SELECT s.name,c.course_name FROM tb_students_info s
INNER JOIN tb_course c
ON s.course_id = c.id;


在这里的查询语句中，两个表之间的关系通过 INNER JOIN 指定，连接的条件使用 ON 子句给出。

#### 13.外连接

内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

外连接可以分为左外连接和右外连接，下面根据实例分别介绍左外连接和右外连接。


- 左连接 左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

左连接的语法格式如下(OUTER 可以省略)：

SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>



- 与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。

#### 14.MySQL子查询

子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。

子查询在 WHERE 中的语法格式如下：

WHERE <表达式> <操作符> (子查询)


其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

SELECT name FROM tb_students_info
WHERE course_id IN
(SELECT id FROM tb_course
WHERE course_name = 'Java');


MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询。

#### 15.子查询的注意事项

在完成较复杂的数据查询时，经常会使用到子查询，编写子查询语句时，要注意如下事项。


- 子查询语句可以嵌套在 SQL 语句中任何表达式出现的位置
- 只出现在子查询中而没有出现在父查询中的表不能包含在输出列中

#### 15.正则表达式查询

正则表达式主要用来查询和替换符合某个模式（规则）的文本内容。例如，从一个文件中提取电话号码，查找一篇文章中重复的单词、替换文章中的敏感语汇等，这些地方都可以使用正则表达式。正则表达式强大且灵活，常用于非常复杂的查询。

MySQL 中，使用 REGEXP 关键字指定正则表达式的字符匹配模式，其基本语法格式如下：

属性名 REGEXP '匹配方式'


常用匹配方式
选项说明例子匹配值示例^匹配文本的开始字符‘^b’ 匹配以字母b开头的字符串book、big$匹配文本的结束字符‘st$’匹配以st结尾的字符串.匹配任何单个字符‘b.t’ 匹配任何 b 和 t 之间有一个字符bit、bat、but、bite*匹配零个或多个在它前面的字符'f*n’匹配字符 n 前面有任意个字符ffn、fan、faan、fabcn+匹配前面的字符1次或多次'ba+'匹配以b开头，后面至少紧跟一个aba、bay、bare、battle<字符串>匹配包含指定字符的文本匹配包含‘fa’的文本fan、afa、faad[字符集合]匹配字符集合中的任何一个字符'[xz]'匹配x或者zdizzy、zebra、x-ray、extra[^]匹配不在括号中的任何字符'[^abc]'匹配任何不包含 a、b 或 c 的字符串desk、fox、f8ke字符串{n}匹配前面的字符串至少n次'b{2}'匹配2个或更多的bbbb、bbbb、bbbbbbb字符串{n,m}匹配前面的字符串至少n次，至多m次'b{2,4}'匹配最少2个，最多4个bbbb、bbbb

SELECT * FROM tb_students_info
WHERE name REGEXP '^J';

SELECT * FROM tb_students_info
WHERE name REGEXP 'an';

SELECT * FROM tb_students_info
WHERE name REGEXP '[^a-t]';

SELECT * FROM tb_students_info
WHERE name REGEXP 'an|en';


#### 16.MySQL插入数据

基本语法：
 INSERT 语句有两种语法形式，分别是 INSERT…VALUES 语句和 INSERT…SET 语句。


- INSERT…VALUES语句 INSERT VALUES 的语法格式为：

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]VALUES (值1) [… , (值n) ];



- INSERT…SET语句 语法格式为：

INSERT INTO <表名>
SET <列名1> = <值1>,
<列名2> = <值2>,
...


#### 17.MySQL删除数据


- 删除单个表中的数据 使用 DELETE 语句从单个表中删除数据，语法格式为：

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]



- 删除表中的全部数据\

DELETE FROM tb_courses_new;



- 根据条件删除表中的数据

DELETE FROM tb_courses
WHERE course_id=4;


#### 18.MySQL修改数据

UPDATE tb_courses_new
SET course_name='DB',course_grade=3.5
WHERE course_id=2;


#### 18.MySQL清空表记录

TRUNCATE 关键字用于完全清空一个表。其语法格式如下：

TRUNCATE [TABLE] 表名



- DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
- DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
- DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
- DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
- DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。

#### 19.MySQL关联表更新（多表更新）

在开发过程中，有时会遇到需要将某张表的字段值根据条件动态地更新到另一张表字段的问题，即通过一张表的字段修改另一张关联表中的内容。

将表A的 dept_id 字段值根据 user_id 字段同步到表B的 dept_id 字段中，即用表A中的 dept_id 字段数据去更新表B中的 dept_id 字段，条件是表A的 user_id 字段值与表B的 user_id 字段值相等时进行更新。以下有四种方法：

UPDATE test_a a, test_b b
SET b.dept_id = a.dept_id
WHERE
b.user_id = a.user_id;

//通过'INNER JOIN'
UPDATE test_a a
INNER JOIN test_b b ON a.user_id = b.user_id
SET b.dept_id = a.dept_id;

//通过'LEFT JOIN'
UPDATE test_a a
LEFT JOIN test_b b ON a.user_id = b.user_id
SET b.dept_id = a.dept_id;

//通过子查询
UPDATE test_b b
SET dept_id = ( SELECT dept_id FROM test_a WHERE user_id = b.user_id );


#### 20.MySQL处理无效数据

MySQL处理数据的基本原则是“垃圾进来，垃圾出去”，通俗一点说就是你传给 MySQL 什么样的数据，它就会存储什么样的数据。如果在存储数据时没有对它们进行验证，那么在把它们检索出来时得到的就不一定是你所期望的内容。

默认情况下，MySQL 会按照以下规则来处理越界（即超出取值范围）的值和其他非正常值：


- 对于数值列或 TIME 列，超出合法取值范围的那些值将被截断到取值范围最近的那个端点，并把结果值存储起来。
- 对于除 TIME 列以外的其他类型列，非法值会被转换成与该类型一致的“零”值。
- 对于字符串列（不包括 ENUM 或 SET），过长的字符串将被截断到该列的最大长度。
- 给 ENUM 或 SET 类型列进行赋值时，需要根据列定义里给出的合法取值列表进行。如果把不是枚举成员的值赋给 ENUM 列，那么列的值就会变成空字符串。如果把包含非集合成员的子字符串的值赋给 SET 列，那么这些字符串会被清理，剩余的成员才会被赋值给列。

如果需要在插入或更新数据时执行更严格的检查，那么可以启用以下两种 SQL 模式中的一种：

set sql_mode = 'STRINCT_ALL_TABLES';
set sql_mode = 'STRINCT_TRANS_TABLES';


对于支持事务的表，这两种模式都是一样的。如果发现某个值无效或缺失，那么会产生一个错误，并且语句会中止执行，并进行回滚，就像什么事都没发生过一样。
  
 在用于插入或修改多个行的语句里，如果在第一行之后的某个行出现了错误，那么会出现某些行被修改的情况。这两种模式决定着，这条语句此时此刻是要停止执行，还是要继续执行。

STRINCT_ALL_TABLES停止执行；STRINCT_TRANS_TABLES继续执行，防止出现部分更新的情况。

以下为更严格检查的模式：


- ERROR_ FOR_ DIVISION_ BY_ ZERO：在严格模式下，如果遇到以零为除数的情况，它会阻止数值进入数据库。如果不在严格模式下，则会产生一条警告消息，并插入 NULL。
- NO_ ZERO_ DATE：在严格模式下，它会阻止“零”日期值进入数据库。
- NO_ ZERO_ IN_ DATE：在严格模式下，它会阻止月或日部分为零的不完整日期值进入数据库。

可通过select @@sql_mode;命令查看当前是严格模式还是非严格模式。

例如，如果想让所有的存储引擎启用严格模式，并对“被零除”错误进行检查，那么可以像下面这样设置 SQL 模式：

SET sql_mode ‘STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO';


如果想启用严格模式，以及所有的附加限制，那么最为简单的办法是启用 TRADITIONAL 模式：

SET sql_ mode ‘TRADITIONAL';


### 四.MySQL视图，索引

视图在数据库中的作用类似于窗户，用户可以通过这个窗口看到只对自己有用的数据。既保障了数据的安全性，又大大提高了查询效率。

索引是提高数据库性能的重要方式，用来快速找出数据表中的特定记录。如果在表中查询的列有一个索引，MySQL 能快速到达一个位置去搜寻数据，而不必查看所有数据。

#### 1.视图是什么

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

技巧：如果经常需要从多个表查询指定字段的数据，可以在这些表上建立一个视图，通过这个视图显示这些字段的数据。


视图与表在本质上虽然不相同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。同时，视图具有如下优点：

1. 定制用户数据
2. 简化数据操作
3. 提高数据的安全性
4. 共享所需数据
5. 更改数据格式
6. 重用SQL语句：。视图定义后，编写完所需的查询，可以方便地重用该视图。

要注意区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

#### 2.创建视图

创建视图是指在已经存在的 MySQL 数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。

可以使用 CREATE VIEW 语句来创建视图。

语法格式如下：

CREATE VIEW <视图名> AS <SELECT语句>

CREATE VIEW view_students_info
AS SELECT * FROM tb_students_info;


MySQL 中也可以在两个以上的表中创建视图，使用 CREATE VIEW 语句创建。

#### 3.查看视图

查看视图的字段信息与查看数据表的字段信息一样，都是使用 DESCRIBE 关键字来查看的。具体语法如下：

DESCRIBE 视图名;


或简写成：

DESC 视图名;


在 MySQL 中，SHOW CREATE VIEW 语句可以查看视图的详细定义。其语法如下所示：

SHOW CREATE VIEW 视图名;


#### 4.修改视图

修改视图是指修改 MySQL 数据库中存在的视图，当基本表的某些字段发生变化时，可以通过修改视图来保持与基本表的一致性。

可以使用 ALTER VIEW 语句来对已有的视图进行修改。

语法格式如下：

ALTER VIEW <视图名> AS <SELECT语句>


修改视图名称:修改视图的名称可以先将视图删除，然后按照相同的定义语句进行视图的创建，并命名为新的视图名称。

#### 5.删除视图

删除视图是指删除 MySQL 数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。

可以使用 DROP VIEW 语句来删除视图。

语法格式如下：

DROP VIEW <视图名1> [ , <视图名2> …]

DROP VIEW IF EXISTS v_students_info;


#### 6.索引介绍

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

所以索引是 MySQL 中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。

#### 7.创建索引

MySQL 提供了三种创建索引的方法：

1. 使用 CREATE INDEX 语句 可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。 语法格式：```CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])```
2. 使用 CREATE TABLE 语句 索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。语法格式：```CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)```
3. 使用 ALTER TABLE 语句 语法格式：```ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)```

具体见下链接:http://c.biancheng.net/view/2605.html

#### 8.查看索引

索引创建完成后，可以利用 SQL 语句查看已经存在的索引。在 MySQL 中，可以使用 SHOW INDEX 语句查看表中创建的索引。

查看索引的语法格式如下：

SHOW INDEX FROM <表名> [ FROM <数据库名>]


删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

在 MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

1. 使用 DROP INDEX 语句 语法格式：```DROP INDEX <索引名> ON <表名>```
2. 使用 ALTER TABLE 语句 根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。


- DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
- DROP INDEX index_name：表示删除名称为 index_name 的索引。
- DROP FOREIGN KEY fk_symbol：表示删除外键。

### 五.MySQL存储过程和触发器

存储过程是在数据库中定义的一些SQL语句的集合，可以调用这些存储过程来执行已经定义好的SQL语句。避免开发人员重复编写相同的SQL语句的问题

#### 1.MySQL存储过程是什么

存储过程是一组为了完成特定功能的SQL语句集合，使用存储过程的目的是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来。

一个存储过程是一个可以编程的函数，它在数据库中创建并保存，一般由SQL语句和一些特殊的控制结构组成，当需要在不同应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

存储过程具有以下优点：

1. 封装性
2. 可增强 SQL 语句的功能和灵活性
3. 可减少网络流量
4. 高性能 当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能。
5. 提高数据库的安全性和数据的完整性 存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。
6. 使数据独立 程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

#### 2.创建存储过程

MySQL 存储过程是一些 SQL 语句的集合，比如有时候我们可能需要一大串的 SQL 语句，或者说在编写 SQL 语句的过程中需要设置一些变量的值，这个时候我们就完全有必要编写一个存储过程。

编写存储过程并不是件简单的事情，但是使用存储过程可以简化操作，且减少冗余的操作步骤，同时，还可以减少操作过程中的失误，提高效率，因此应该尽可能的学会使用存储过程。

可以使用 CREATE PROCEDURE 语句创建存储过程，语法格式如下：

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>


存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。

MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句

为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下：

DELIMITER $$


语法说明如下：
 $$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
 当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。

CREATE PROCEDURE GetScoreByStu
(IN name VARCHAR(30))
BEGIN
SELECT student_score FROM tb_students_score
WHERE student_name=name;
END


#### 3.查看存储过程

创建好存储过程后，用户可以通过 SHOW STATUS 语句来查看存储过程的状态，也可以通过 SHOW CREATE 语句来查看存储过程的定义。

MySQL 中可以通过 SHOW STATUS 语句查看存储过程的状态，其基本语法形式如下：

SHOW PROCEDURE STATUS LIKE 存储过程名;


MySQL 中可以通过 SHOW CREATE 语句查看存储过程的状态，语法格式如下：

SHOW CREATE PROCEDURE 存储过程名;


#### 4.修改存储过程

MySQL 中通过 ALTER PROCEDURE 语句来修改存储过程。

MySQL 中修改存储过程的语法格式如下：

ALTER PROCEDURE 存储过程名 [ 特征 ... ]


特征指定了存储过程的特性，可能的取值有：


- CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
- NO SQL 表示子程序中不包含 SQL 语句。
- READS SQL DATA 表示子程序中包含读数据的语句。
- MODIFIES SQL DATA 表示子程序中包含写数据的语句。
- SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
- DEFINER 表示只有定义者自己才能够执行。
- INVOKER 表示调用者可以执行。
- COMMENT ‘string’ 表示注释信息。

下面修改存储过程 showstuscore 的定义，将读写权限改为 MODIFIES SQL DATA，并指明调用者可以执行，代码如下：

ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;


#### 5.删除存储过程

存储过程被创建后，就会一直保存在数据库服务器上，直至被删除。当MySQL数据库中存在废弃的存储过程的时候，我们需要将它从数据库中删除。

语法如下：

DROP PROCEDURE [IF EXISTS] <过程名>


#### 6.存储函数

存储函数和存储过程一样，都是在数据库中定义一些SQL语句的集合。存储函数可以通过RETURN语句返回函数值，主要用于计算并返回一个值，而存储过程没有直接返回值，主要用于执行操作。

在MySQL中，使用CREATE FUNCTION 语句来创建存储函数

CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type
[characteristic ...] routine_body


例子如下

DELIMITER //

CREATE FUNCTION func_student(id INT(11))
RETURNS VARCHAR(20)
COMMENT '查询某个学生的姓名'
BEGIN
RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);
END //

mysql> DELIMITER ;


由于存储函数和存储过程的查看、修改、删除等操作几乎相同，将PROCEDURE改为FUNCTION即可，所以我们不再详细讲解如何操作存储函数了。

#### 7.MySQL调用存储过程和函数

存储过程和存储函数是存储在服务器端的SQL语句集合，要想使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式实现。

存储过程使用CALL语句调用，存储函数使用方法与MySQL内部函数的使用方法相同。执行存储过程和函数需要EXCUTE权限（EXECUTE 权限的信息存储在 information_schema 数据库下的 USER_PRIVILEGES 表中）。

1. 调用存储过程 MySQL 中使用 CALL 语句来调用存储过程。调用存储过程后，数据库系统将执行存储过程中的 SQL 语句，然后将结果返回给输出值。

CALL ShowStuScore();


因为存储过程本身也是一种函数，所以要在最后加上()，即使没有参数也要加上。

在 MySQL 中，存储函数的使用方法与 MySQL 内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与 MySQL 内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是 MySQL 开发者定义的。

SELECT func_student(3);


#### 8.MySQL变量

在 MySQL 中，除了支持标准的存储过程和函数外，还引入了表达式。表达式与其它高级语言的表达式一样，由变量、运算符和流程控制来构成。

变量是表达式中最基本的元素，可以用来存储临时数据。在存储过程和存储函数中都可以使用变量。

用户可以使用DECLARE来定义变量，定义后可以为变量赋值。这些变量的作用范围是BEGIN和END程序段中

1. 定义变量```DECLARE var_name[,...] type [DEFAULT value]```

例,定义一个整形变量默认值为0：

DECLARE my_sql INT DEFAULT 0;


1. 为变量赋值 MySQL中可以使用SET关键字来为变量赋值，SET语句的基本语法如下：```SET var_name = expr[,var_name = expr]...```

SET my_sql=30;


MySQL 中还可以使用 SELECT…INTO 语句为变量赋值。其基本语法如下：

SELECT col_name [...] INTO var_name[,...] FROM table_name WEHRE condition

SELECT id INTO my_sql FROM tb_student WEHRE id=2；

```

标签： mysql 数据库 sql

本文转载自: https://blog.csdn.net/y__De/article/details/129262884
版权归原作者 包括像猫的狗 所有，如有侵权，请联系我们删除。