mysql学习笔记

Mysql

SHOW              查看

ALERT              修改

DROP               删除

USE                  切换

CHECK             检查

SELECT             查询表内容

WHERE            条件查询

UPDATE          更新

REMOVE          权限删除

 

1、数据库配置相关

（1）mysql启动与停止服务

使用管理员权限打开cmd

Win + X + A 快捷键打开

启动服务 net start mysql

停止服务 net stop mysql

（2）配置 path变量，cmd登录 mysql

mysql –h localhost –uroot –p

提示：mysql 为登录命令，-h 后面的参数是服务器的主机地址，在这里客户端和服务器在同一台机器上，所以输入 localhost 或者 IP 地址；-u 后面跟登录数据库的用户名称，在这里为 root；-p 后面是用户登录密码。

 

2、创建查看数据库

（1）创建数据库

mysql> CREATE DATABASE IF NOT EXISTS test_db;

创建并设置字符集和校对规则

mysql> CREATE DATABASE IF NOT EXISTS test_db_char

    -> DEFAULT CHARACTER SET utf8

               -> DEFAULT COLLATE utf8_chinese_ci;

（2）查看数据库定义声明（可查看字符集和校对规则）

mysql> SHOW CREATE DATABASE test_db_char;

（3）查看数据库

mysql> SHOW DATABASES;

（4）条件检索查看数据库

mysql> SHOW DATABASES LIKE 'test_db'; 完全匹配的数据库

mysql> SHOW DATABASES LIKE '%test%'; 查看名字中包含 test 的数据库

mysql> SHOW DATABASES LIKE 'db%'; 查看名字以 db 开头的数据库

mysql> SHOW DATABASES LIKE '%db'; 查看名字以 db 结尾的数据库：

（5）修改数据库的字符集和校对规则

mysql> CREATE DATABASE test_db

    -> DEFAULT CHARACTER SET gb2312

             -> DEFAULT COLLATE gb2312_chinese_ci;

（6）删除数据库

mysql> DROP DATABASE IF EXISTS test_db_del;

（7）选择 、 切换数据库

mysql> USE test_db;

（8）显示可用的数据库引擎和默认引擎

         SHOW ENGINES

（9）设置存储引擎

SET default_storage_engine=< 存储引擎名 >

 

3、常见数据类型

在 MySQL 中常见的数据类型如下：

（1）整数类型

包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型 FLOAT 和 DOUBLE，定点数类型 DECIMAL。

（2）日期/时间类型

包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

（3）字符串类型

包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。

（4）二进制类型

包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

 

4、数据表

（1）创建数据表

mysql> CREATE TABLE tb_emp1

    -> (

    -> id INT(11),

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT

        -> );

（2）查看有哪些数据表

mysql> SHOW TABLES;

（3）查看表结构

DESCRIBE/DESC 语句可以查看表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，语法规则如下：

mysql> DESCRIBE tb_emp1;

（4）查看表的详细信息

mysql> SHOW CREATE TABLE tb_emp1;

 

修改数据表

（5）修改语法格式

{ ADD COLUMN <列名> <类型>

| CHANGE COLUMN <旧列名> <新列名> <新列类型>

| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }

| MODIFY COLUMN <列名> <类型>

| DROP COLUMN <列名>

| RENAME TO <新表名> }

（6）添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]；

mysql> ALTER TABLE tb_emp1

-> ADD COLUMN col1 INT FIRST;

提示：“FIRST 或 AFTER 已存在的字段名”用于指定新增字段在表中的位置，如果 SQL 语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列。

mysql> ALTER TABLE tb_emp1

-> ADD COLUMN col2 INT AFTER name;

（7）修改字段数据类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

将 name 字段的数据类型由 VARCHAR(22) 修改成 VARCHAR(30)

mysql> ALTER TABLE tb_emp1

-> MODIFY name VARCHAR(30);

（8）删除字段

ALTER TABLE <表名> DROP <字段名>；

mysql> ALTER TABLE tb_emp1

-> DROP col2;

（9）修改字段名称

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

使用 ALTER TABLE 修改表 tb_emp1 的结构，将 col1 字段名称改为 col3，同时将数据类型变为 CHAR(30)

mysql> ALTER TABLE tb_emp1

-> CHANGE col1 col3 CHAR(30);

（10）修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

其中，TO 为可选参数，使用与否均不影响结果。

mysql> ALTER TABLE tb_emp1

-> RENAME TO tb_emp2;

（11）删除表

DROP TABLE [IF EXISTS] <表名> [ , <表名1> , <表名2>] …

mysql> DROP TABLE tb_emp3;

 

5、数据表主键约束

（1）创建主键约束

主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值。这是唯一性原则。

<字段名> <数据类型> PRIMARY KEY [默认值]

创建 tb_emp 3 数据表，其主键为 id

mysql> CREATE TABLE tb_emp3

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT

        -> );

或者

mysql> CREATE TABLE tb_emp4

    -> (

    -> id INT(11),

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT,

    -> PRIMARY KEY(id)

-> );

（2）设置复合主键

PRIMARY KEY [字段1，字段2，…,字段n]

mysql> CREATE TABLE tb_emp5

    -> (

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT,

    -> PRIMARY KEY(name,deptId)

-> );

（3）修改表时添加主键约束

mysql> ALTER TABLE tb_emp2

-> ADD PRIMARY KEY(id);

 

6、数据表外键约束

（1）[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]

REFERENCES <主表名> 主键列1 [，主键列2，…]

其中：外键名为定义的外键约束的名称，一个表中不能有相同名称的外键；字段名表示子表需要添加外健约束的字段列；主表名即被子表外键所依赖的表的名称；主键列表示主表中定义的主键列或者列组合。

 

创建 tb_dept1 的 SQL 语句运行结果如下所示。

mysql> CREATE TABLE tb_dept1

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(22) NOT NULL,

    -> location VARCHAR(50)

-> );

 

创建数据表 tb_emp6，并在表 tb_emp6 上创建外键约束，让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id，输入的 SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_emp6

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT,

    -> CONSTRAINT fk_emp_dept1

    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)

-> );

 

以上语句执行成功之后，在表 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束，外键名称为 deptId，其依赖于表 tb_dept1 的主键 id。

 

（2）在修改表时添加外键约束

ALTER TABLE <数据表名> ADD CONSTRAINT <索引名>

FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

修改数据表 tb_emp2，将字段 deptId 设置为外键，与数据表 tb_dept1 的主键 id 进行关联

mysql> ALTER TABLE tb_emp2

    -> ADD CONSTRAINT fk_tb_dept1

    -> FOREIGN KEY(deptId)

-> REFERENCES tb_dept1(id);

（3）删除外键约束

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

删除数据表 tb_emp2 中的外键约束 fk_tb_dept1

mysql> ALTER TABLE tb_emp2

-> DROP FOREIGN KEY fk_tb_dept1;

 

7、UNIQUE唯一约束

（1）添加唯一约束

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

mysql> CREATE TABLE tb_dept2

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(22) UNIQUE,

    -> location VARCHAR(50)

        -> );

（2）UNIQUE 和 PRIMARY KEY 的区别

提示：UNIQUE 和 PRIMARY KEY 的区别：一个表可以有多个字段声明为 UNIQUE，但只能有一个 PRIMARY KEY 声明；声明为 PRIMAY KEY 的列不允许有空值，但是声明为 UNIQUE 的字段允许空值的存在。

（3）在修改表时添加唯一约束

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

修改数据表 tb_dept1，指定部门的名称唯一

mysql> ALTER TABLE tb_dept1

-> ADD CONSTRAINT unique_name UNIQUE(name);

（4）删除唯一约束

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

mysql> ALTER TABLE tb_dept1

-> DROP INDEX unique_name;

 

8、检查约束

（1）在创建表时设置检查约束

在 test_db 数据库中创建 tb_emp7 数据表，要求 salary 字段值大于 0 且小于 10000

mysql> CREATE TABLE tb_emp7

    -> (

    -> 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)

-> );

（2）在修改表时添加检查约束

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

修改 tb_dept 数据表，要求 id 字段值大于 0

mysql> ALTER TABLE tb_emp7

    -> ADD CONSTRAINT check_id

-> CHECK(id>0);

（3）删除检查约束

ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

 

9、DEFAULT 默认值

（1）创建表时设置默认值

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

创建数据表 tb_dept3，指定部门位置默认为 Beijing

mysql> CREATE TABLE tb_dept3

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(22),

    -> location VARCHAR(50) DEFAULT 'Beijing'

        -> );

（2）修改表时添加默认值约束

ALTER TABLE <数据表名>

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

修改数据表 tb_dept3，将部门位置的默认值修改为 Shanghai

mysql> ALTER TABLE tb_dept3

    -> CHANGE COLUMN location

-> location VARCHAR(50) DEFAULT 'Shanghai';

（3）删除默认值约束

ALTER TABLE <数据表名>

CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

修改数据表 tb_dept3，将部门位置的默认值约束删除

mysql> ALTER TABLE tb_dept3

    -> CHANGE COLUMN location

-> location VARCHAR(50) DEFAULT NULL;

 

10、非空约束NOT NULL

（1）创建表时添加非空约束

<字段名> <数据类型> NOT NULL;

 

mysql> CREATE TABLE tb_dept4

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(22) NOT NULL,

    -> location VARCHAR(50)

-> );

（2）修改表时添加非空约束

ALTER TABLE <数据表名>

CHANGE COLUMN <字段名>

<字段名> <数据类型> NOT NULL;

mysql> ALTER TABLE tb_dept4

    -> CHANGE COLUMN location

-> location VARCHAR(50) NOT NULL;

（3）删除非空约束

ALTER TABLE <数据表名>

CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;

mysql> ALTER TABLE tb_dept4

    -> CHANGE COLUMN location

-> location VARCHAR(50) NULL;

 

11、查看数据表中的约束语法

SHOW CREATE TABLE <数据表名>;

创建数据表 tb_emp8 并指定 id 为主键约束，name 为唯一约束，deptId 为非空约束和外键约束，然后查看表中的约束，输入SQL语句运行结果如下。

mysql> CREATE TABLE tb_emp8

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(22) UNIQUE,

    -> deptId INT(11) NOT NULL,

    -> salary FLOAT DEFAULT 0,

    -> CHECK(salary>0),

    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)

-> );

12、数据表查询

（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>]

]

其中，各条子句的含义如下：

{*|<字段列名>}包含星号通配符的字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间要用逗号隔开，最后一个字段后不要加逗号。

FROM <表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。

WHERE 子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。

GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。

[ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC）。

[LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。

 

（2）使用“*”查询表中的全部内容

SELECT * FROM 表名;

从 tb_students_info 表中检索所有字段的数据

mysql> use test_db;

Database changed

mysql> SELECT * FROM tb_students_info;

（3）查询表中指定的字段

SELECT < 列名 > FROM < 表名 >;

查询 tb_students_info 表中 name 列所有学生的姓名

mysql> SELECT name FROM tb_students_info;

（4）查询多个字段

SELECT <字段名1>,<字段名2>,…,<字段名n> FROM <表名>;

从 tb_students_info 表中获取 id、name 和 height 三列

mysql> SELECT id,name,height

-> FROM tb_students_info;

13、表数据去重DISTINCT

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

查询 tb_students_info 表中 age 字段的值，返回 age 字段的值且不得重复

mysql> SELECT  DISTINCT age

-> FROM tb_students_info;

14、设置别名AS

（1）为表取别名

<表名> [AS] <别名>

为 tb_students_info 表取别名 stu，注意：在为表取别名时，要保证不能与数据库中的其他表的名称冲突。

mysql> SELECT stu.name,stu.height

-> FROM tb_students_info AS stu;

（2）为列指定别名

<列名> [AS] <列别名>

查询 tb_students_info 表，为 name 取别名 student_name，为 age 取别名student_age

mysql> SELECT name AS student_name,

    -> age AS student_age

-> FROM tb_students_info;

注意：表别名只在执行查询时使用，并不在返回结果中显示，而列定义别名之后，将返回给客户端显示，显示的结果字段为字段列的别名。

 

15、限制查询结果的记录条数 LIMIT

<LIMIT> [<位置偏移量>,] <行数>

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

 

1. 显示 tb_students_info 表查询结果的前 4 行

mysql> SELECT * FROM tb_students_info LIMIT 4;

1. 在 tb_students_info 表中，使用 LIMIT 子句返回从第 4 条记录开始的行数为 5 的记录

mysql> SELECT * FROM tb_students_info LIMIT 3,5;

 

16、对查询结果排序

ORDER BY {<列名> | <表达式> | <位置>} [ASC|DESC]

关键字 ASC 表示按升序分组，关键字 DESC 表示按降序分组，其中 ASC 为默认值。这两个关键字必须位于对应的列名、表达式、列的位置之后。

1. 查询 tb_students_info 表的 height 字段值

mysql> SELECT * FROM tb_students_info ORDER BY height;

1. 多列排序

查询 tb_students_info 表中的 name 和 height 字段，先按 height 排序，再按 name 排序

mysql> SELECT name,height

    -> FROM tb_students_info

    -> ORDER BY height,name;

注意：在对多列进行排序时，首行排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有的值都是唯一的，将不再对第二列进行排序。

1. 升序和降序同时存在

查询 tb_students_info 表，先按 height 降序排序，再按 name 升序排序

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

 

17、条件查询WHERE，数据过滤

WHERE <查询条件> {<判定运算1>，<判定运算2>，…}

其中，判定运算其结果取值为 TRUE、FALSE 和 UNKNOWN。

 

判定运算的语法分类如下：

<表达式1>{=|<|<=|>|>=|<=>|<>|！=}<表达式2>

<表达式1>[NOT]LIKE<表达式2>

<表达式1>[NOT][REGEXP|RLIKE]<表达式2>

<表达式1>[NOT]BETWEEN<表达式2>AND<表达式3>

<表达式1>IS[NOT]NULL

 

1. 单一条件查询

表 tb_students_info 中查询身高为 170cm 的学生的姓名

mysql> use test_db

Database changed

mysql> SELECT name,height

    -> FROM tb_students_info

        -> WHERE height=170;

 （2）多条件查询

可以使用 AND 连接两个甚至多个查询条件，多个条件表达式之间用 AND 分开

在 tb_students_info 表中查询 age 大于 21，并且 height 大于等于 175 的学生的信息

mysql> SELECT * FROM tb_students_info

-> WHERE age>21 AND height>=175;

注意：上例的 WHERE 子句中只包含一个 AND 语句，把两个过滤条件组合在一起，实际上可以添加多个 AND 过滤条件，增加条件的同时增加一个 AND 关键字。

（3）LIKE模糊查询

<表达式1> [NOT] LIKE <表达式2>

1) 百分号（%）

百分号是 MySQL 中常用的一种通配符，在过滤条件中，百分号可以表示任何字符串，并且该字符串可以出现任意次。

2) 下划线（_）

下划线通配符和百分号通配符的用途一样，下画线只匹配单个字符，而不是多个字符，也不是 0 个字符。

 

• 查找所有以“T”字母开头的学生姓名

mysql> SELECT name FROM tb_students_info

-> WHERE name LIKE 'T%';

②在 tb_students_info 表中，查找所有包含“e”字母的学生姓名

mysql> SELECT name FROM tb_students_info

-> WHERE name LIKE '%e%';

该语句查询字符串中包含字母 e 的学生的姓名，只要名字中有字母 e，其前面或后面无论有多少个字符，都满足查询的条件

• 查找所有以字母“y”结尾，且“y”前面只有 4 个字母的学生的姓名

mysql> SELECT name FROM tb_students_info

    -> WHERE name LIKE '____y';

（4）范围查询BETWEEN AND

 

日期字段作为条件，可以使用比较运算符设置查询条件，也可以使用 BETWEEN AND 运算符查询某个范围内的值。

• 在表 tb_students_info 中查询注册日期在 2016-01-01 之前的学生的信息

mysql> SELECT * FROM tb_students_info

-> WHERE login_date<'2016-01-01';

②在表 tb_students_info 中查询注册日期在 2015-10-01 和 2016-05-01 之间的学生的信息

mysql> SELECT * FROM tb_students_info

    -> WHERE login_date

    -> BETWEEN '2015-10-01'

-> AND '2016-05-01';

 

 

18、常用运算符

MySQL 支持 4 种运算符，分别是：

1) 算术运算符

执行算术运算，例如：加、减、乘、除等。

2) 比较运算符

包括大于、小于、等于或者不等于，等等。主要用于数值的比较、字符串的匹配等方面。例如：LIKE、IN、BETWEEN AND 和 IS NULL 等都是比较运算符，还包括正则表达式的 REGEXP 也是比较运算符。

3) 逻辑运算符

包括与、或、非和异或等逻辑运算符。其返回值为布尔型，真值（1 或 true）和假值（0 或 false）。

4) 位运算符

包括按位与、按位或、按位取反、按位异或、按位左移和按位右移等位运算符。位运算必须先将数据转换为二进制，然后在二进制格式下进行操作,运算完成后，将二进制的值转换为原来的类型，返回给用户。

 

不同运算符的优先级是不同的。一般情况下，级别高的运算符优先进行计算，如果级别相同，MySQL 按表达式的顺序从左到右依次计算。

 

另外，在无法确定优先级的情况下，可以使用圆括号“()”来改变优先级，并且这样会使计算过程更加清晰。

 

比较运算符

<表达式1> {= | < | <= | > | >= | <=> | < > | !=} <表达式2>

 

MySQL 支持的比较运算符如下表所示。
 

比较运算符	说明
=	等于
<	小于
<=	小于等于
>	大于
>=	大于等于
<=>	安全的等于，不会返回 UNKNOWN
<> 或!=	不等于
IS NULL 或 ISNULL	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

 

 

19、内连接查询

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

SELECT <列名1，列名2 …>

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

语法说明如下。

<列名1，列名2…>：需要检索的列名。

<表名1><表名2>：进行内连接的两张表的表名。

内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只用关键字 JOIN。使用内连接后，FROM 子句中的 ON 子句可用来设置连接表的条件。

（1）表 tb_students_info 和表 tb_departments 都包含相同数据类型的字段 dept_id，在两个表之间使用内连接查询

mysql> SELECT id,name,age,dept_name

    -> FROM tb_students_info,tb_departments

    -> WHERE tb_students_info.dept_id=tb_departments.dept_id;

 

在这里，SELECT 语句与前面介绍的最大差别是：SELECT 后面指定的列分别属于两个不同的表，id、name、age 在表 tb_students_info 中，而 dept_name 在表 tb_departments 中，同时 FROM 字句列出了两个表 tb_students_info 和 tb_departments。WHERE 子句在这里作为过滤条件，指明只有两个表中的 dept_id 字段值相等的时候才符合连接查询的条件。

 

（2）在 tb_students_info 表和 tb_departments 表之间，使用 INNER JOIN 语法进行内连接查询

mysql> SELECT id,name,age,dept_name

    -> FROM tb_students_info INNER JOIN tb_departments

-> WHERE tb_students_info.dept_id=tb_departments.dept_id;

 

20、外连接查询

MySQL 中内连接是在交叉连接的结果集上返回满足条件的记录；而外连接先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

 

外连接更加注重两张表之间的关系。按照连接表的顺序，可以分为左外连接和右外连接。

 

左外连接又称为左连接，在 FROM 子句中使用关键字 LEFT OUTER JOIN 或者 LEFT JOIN，用于接收该关键字左表（基表）的所有行，并用这些行与该关键字右表（参考表）中的行进行匹配，即匹配左表中的每一行及右表中符合条件的行。

 

在左外连接的结果集中，除了匹配的行之外，还包括左表中有但在右表中不匹配的行，对于这样的行，从右表中选择的列的值被设置为 NULL，即左外连接的结果集中的 NULL 值表示右表中没有找到与左表相符的记录。

1. 在 tb_students_info 表和 tb_departments 表中查询所有学生，包括没有学院的学生

mysql> SELECT name,dept_name

    -> FROM tb_students_info s

    -> LEFT OUTER JOIN tb_departments d

    -> ON s.dept_id = d.dept_id;

 

21、子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

 

子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。

 

子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询也可以使用比较运算符，如“<”、“<=”、“>”、“>=”、“！=”等。

 

在 tb_departments 表中查询 dept_type 为 A 的学院 ID，并根据学院 ID 查询该学院学生的名字

mysql> SELECT name FROM tb_students_info

    -> WHERE dept_id IN

    -> (SELECT dept_id

    -> FROM tb_departments

-> WHERE dept_type= 'A' );

22、分组查询

GROUP BY { <列名> | <表达式> | <位置> } [ASC | DESC]

根据 dept_id 对 tb_students_info 表中的数据进行分组，将每个学院的学生姓名显示出来

mysql> SELECT dept_id,GROUP_CONCAT(name) AS names

    -> FROM tb_students_info

    -> GROUP BY dept_id;

由运行结果可以看出，根据 dept_id 的不同分别统计了 dept_id 相同的姓名。

 

23、指定过滤条件分组

HAVING 子句和 WHERE 子句非常相似，HAVING 子句支持 WHERE 子句中所有的操作符和语法，但是两者存在几点差异：

WHERE 子句主要用于过滤数据行，而 HAVING 子句主要用于过滤分组，即 HAVING 子句基于分组的聚合值而不是特定行的值来过滤数据，主要用来过滤分组。

WHERE 子句不可以包含聚合函数，HAVING 子句中的条件可以包含聚合函数。

HAVING 子句是在数据分组后进行过滤，WHERE 子句会在数据分组前进行过滤。WHERE 子句排除的行不包含在分组中，可能会影响 HAVING 子句基于这些值过滤掉的分组。

 

根据 dept_id 对 tb_students_info 表中的数据进行分组，并显示学生人数大于1的分组信息

mysql> SELECT dept_id,GROUP_CONCAT(name) AS names

    -> FROM tb_students_info

    -> GROUP BY dept_id

-> HAVING COUNT(name)>1;

 

24、正则表达式查询

MySQL 中使用 REGEXP 关键字指定正则表达式的字符匹配模式，下表列出了 REGEXP 操作符中常用的匹配列表。
 

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

 

在 tb_departments 表中，查询 dept_name 字段以字母“C”开头的记录

mysql> SELECT * FROM tb_departments

-> WHERE dept_name REGEXP '^C';

 

 

25、数据表添加数据

由 INSERT 语句的两种形式可以看出：

使用 INSERT…VALUES 语句可以向表中插入一行数据，也可以插入多行数据；

使用 INSERT…SET 语句可以指定插入行中每列的值，也可以指定部分列的值；

INSERT…SELECT 语句向表中插入其他表的数据。

采用 INSERT…SET 语句可以向表中插入部分列的值，这种方式更为灵活；

INSERT…VALUES 语句可以一次插入多条数据。

 

1. 在 tb_courses 表中插入一条新记录，course_id 值为 1，course_name 值为“Network”，course_grade 值为 3，info 值为“Computer Network”。

mysql> INSERT INTO tb_courses

    -> (course_id,course_name,course_grade,course_info)

        -> VALUES(1,'Network',3,'Computer Network');

（2）使用 INSERT 插入数据时，允许列名称列表 column_list 为空，此时值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

 

mysql> INSERT INTO tb_courses

-> VLAUES(3,'Java',4,'Java EE');

 

1. 使用 INSERT INTO…FROM 语句复制表数据

INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中。

 

SELECT 子句返回的是一个查询到的结果集，INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

 

从 tb_courses 表中查询所有的记录，并将其插入 tb_courses_new 表中

mysql> INSERT INTO tb_courses_new

    -> (course_id,course_name,course_grade,course_info)

    -> SELECT course_id,course_name,course_grade,course_info

        -> FROM tb_courses;

 

26、修改数据（更新数据）

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]

[ORDER BY 子句] [LIMIT 子句]

1. 修改表中的数据

在 tb_courses_new 表中，更新所有行的 course_grade 字段值为 4

mysql> UPDATE tb_courses_new

-> SET course_grade=4;

（2）根据条件修改表中的数据

在 tb_courses 表中，更新 course_id 值为 2 的记录，将 course_grade 字段值改为 3.5，将 course_name 字段值改为“DB”

mysql> UPDATE tb_courses_new

    -> SET course_name='DB',course_grade=3.5

-> WHERE course_id=2;

注意：保证 UPDATE 以 WHERE 子句结束，通过 WHERE 子句指定被更新的记录所需要满足的条件，如果忽略 WHERE 子句，MySQL 将更新表中所有的行。

 

27、删除数据

（1）删除单个表中的数据

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

• <表名>：指定要删除数据的表名。
• ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
• WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
• LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。

注意：在不使用 WHERE 条件的时候，将删除所有数据。

1. 删除表中的全部数据

mysql> DELETE FROM tb_courses_new;

1. 根据条件删除表中的数据

在 tb_courses_new 表中，删除 course_id 为 4 的记录

mysql> DELETE FROM tb_courses

-> WHERE course_id=4;

 

28、视图

（1）基本语法

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

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

<SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

（2）在 tb_students_info 表上创建一个名为 view_students_info 的视图

mysql> CREATE VIEW view_students_info

-> AS SELECT * FROM tb_students_info;

（3）定义视图表字段

mysql> CREATE VIEW v_students_info

    -> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)

    -> AS SELECT id,name,dept_id,age,sex,height,login_date

-> FROM tb_students_info;

（4）查询视图

mysql> DESCRIBE v_students_info;

DESCRIBE 一般情况下可以简写成 DESC，输入这个命令的执行结果和输入 DESCRIBE 是一样的

（5）修改视图

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

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

<SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

用户可以通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟的表，没有数据。通过视图更新时转到基本表上进行更新，如果对视图增加或删除记录，实际上是对基本表增加或删除记录。

（6）使用 UPDATE 语句更新视图 view_students_info

mysql> UPDATE view_students_info

-> SET age=25 WHERE id=1;

（7）删除视图

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

其中：<视图名> 指定要删除的视图名。DROP VIEW 语句可以一次删除多个视图，但是必须在每个视图上拥有 DROP 权限。

mysql> DROP VIEW IF EXISTS v_students_info;

 

29、自定义函数

（1）创建并是使用自定义函数

CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )

  RETURNS <类型>

  <函数主体>

 

<函数名>：指定自定义函数的名称。注意，自定义函数不能与存储过程具有相同的名称。

<参数><类型>：用于指定自定义函数的参数。这里的参数只有名称和类型，不能指定关键字 IN、OUT 和 INOUT。

RETURNS<类型>：用于声明自定义函数返回值的数据类型。其中，<类型>用于指定返回值的数据类型。

<函数主体>：自定义函数的主体部分，也称函数体。所有在存储过程中使用的 SQL 语句在自定义函数中同样适用，包括前面所介绍的局部变量、SET 语句、流程控制语句、游标等。除此之外，自定义函数体还必须包含一个 RETURN<值> 语句，其中<值>用于指定自定义函数的返回值。

（2）创建存储函数

创建存储函数，名称为 StuNameById，该函数返回 SELECT 语句的查询结果，数值类型为字符串类型

mysql> CREATE FUNCTION StuNameById()

    -> RETURNS VARCHAR(45)

    -> RETURN

    -> (SELECT name FROM tb_students_info

-> WHERE id=1);

（3）使用自定义函数

SELECT <自定义函数名> ([<参数> [,...]])

mysql> SELECT StuNameById();

（4）删除自定义函数

DROP FUNCTION [ IF EXISTS ] <自定义函数名>

<自定义函数名>：指定要删除的自定义函数的名称。

IF EXISTS：指定关键字，用于防止因误删除不存在的自定义函数而引发错误。

mysql> DROP FUNCTION StuNameById;

 

30、索引

（1）创建索引的三种方式

1) 使用 CREATE INDEX 语句

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

<索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。

<表名>：指定要创建索引的表名。

<列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。

<长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。

ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

（2）使用 CREATE TABLE 语句

索引也可以在创建表（CREATE TABLE）的同时创建。

CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)

（3）使用 ALTER TABLE 语句

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

 

（4）创建一般索引

创建一个表 tb_stu_info，在该表的 height 字段创建一般索引

mysql> CREATE TABLE tb_stu_info

    -> (

    -> id INT NOT NULL,

    -> name CHAR(45) DEFAULT NULL,

    -> dept_id INT DEFAULT NULL,

    -> age INT DEFAULT NULL,

    -> height INT DEFAULT NULL,

    -> INDEX(height)

-> );

（5）创建唯一索引

创建一个表 tb_stu_info2，在该表的 id 字段上使用 UNIQUE 关键字创建唯一索引。

mysql> CREATE TABLE tb_stu_info2

    -> (

    -> id INT NOT NULL,

    -> name CHAR(45) DEFAULT NULL,

    -> dept_id INT DEFAULT NULL,

    -> age INT DEFAULT NULL,

    -> height INT DEFAULT NULL,

    -> UNIQUE INDEX(height)

-> );

（6）查看索引

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

<表名>：要显示索引的表。

<数据库名>：要显示的表所在的数据库。

显示数据库 mytest 的表 course 的索引情况。

mysql> SHOW INDEX FROM course FROM mytest;

 

该语句会返回一张结果表，该表有如下几个字段，每个字段所显示的内容说明如下。

Table：表的名称。

Non_unique：用于显示该索引是否是唯一索引。若不是唯一索引，则该列的值显示为 1；若是唯一索引，则该列的值显示为 0。

Key_name：索引的名称。

Seq_in_index：索引中的列序列号，从 1 开始计数。

Column_name：列名称。

Collation：显示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。

Cardinality：显示索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。

Sub_part：若列只是被部分编入索引，则为被编入索引的字符的数目。若整列被编入索引，则为 NULL。

Packed：指示关键字如何被压缩。若没有被压缩，则为 NULL。

Null：用于显示索引列中是否包含 NULL。若列含有 NULL，则显示为 YES。若没有，则该列显示为 NO。

Index_type：显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment：显示评注。

 

使用 SHOW INDEX 语句查看表 tb_stu_info2 的索引信息

mysql> SHOW INDEX FROM tb_stu_info2\G

 

（7）删除和修改索引

1) 使用 DROP INDEX 语句

DROP INDEX <索引名> ON <表名>

mysql> DROP INDEX height

-> ON tb_stu_info;

 

31、创建用户

可以使用 CREATE USER 语句来创建一个或多个 MySQL 账户，并设置相应的口令。

CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>

（1）使用 CREATE USER 创建一个用户，用户名是 james，密码是 tiger，主机是 localhost。

mysql> CREATE USER 'james'@'localhost'

-> IDENTIFIED BY 'tiger';

（2）C:\Users\USER>mysql -h localhost -u james -p

Enter password: *****

（3）修改用户账号

RENAME USER <旧用户> TO <新用户>

使用 RENAME USER 语句将用户名 james 修改为 jack，主机是 localhost。

mysql> RENAME USER james@'localhost'

    -> TO jack@'localhost';

（4）修改用户口令

SET PASSWORD [ FOR <用户名> ] =

{

    PASSWORD('新明文口令')

    | OLD_PASSWORD('旧明文口令')

    | '加密口令值'

}

语法说明如下。

FOR 子句：可选项。指定欲修改口令的用户。

PASSWORD('新明文口令')：表示使用函数 PASSWORD() 设置新口令，即新口令必须传递到函数 PASSWORD() 中进行加密。

加密口令值：表示已被函数 PASSWORD() 加密的口令值。

注意：PASSWORD() 函数为单向加密函数，一旦加密后不能解密出原明文。

使用 SET PASSWORD 语句应注意以下几点：

在 SET PASSWORD 语句中，若不加上 FOR 子句，表示修改当前用户的口令。若加上 FOR 子句，表示修改账户为 user 的用户口令。

user 必须以 'user_name'@'host_name' 的格式给定，user_name 为账户的用户名，host_name 为账户的主机名。

该账户必须在系统中存在，否则语句执行时会出现错误。

在 SET PASSWORD 语句中，只能使用选项 PASSWORD('新明文口令') 和加密口令值中的一项，且必须使用其中的一项。

 

使用 SET 语句将用户名为 jack 的密码修改为 lion，主机是 localhost。

mysql> SET PASSWORD FOR 'jack'@'localhost'=

-> PASSWORD('lion');

 

（5）删除用户

DROP USER <用户名1> [ , <用户名2> ]…

使用 DROP USER 语句应该注意以下几点：

DROP USER 语句可用于删除一个或多个 MySQL 账户，并撤销其原有权限。

使用 DROP USER 语句必须拥有 MySQL 中的 MySQL 数据库的 DELETE 权限或全局 CREATE USER 权限。

在 DROP USER 语句的使用中，若没有明确地给出账户的主机名，则该主机名默认为“%”。

mysql> DROP USER 'jack'@'localhost';

 

 

32、授予用户权限

（1）USAGE ON*.* 表示该用户对任何数据库和任何表都没有权限。

（2）对于新建的 MySQL 用户，必须给它授权，可以用 GRANT 语句来实现对新建用户的授权

GRANT

<权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ]

ON <对象> <权限级别> TO <用户>

其中<用户>的格式：

<用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>

[ WITH GRANT OPTION]

| MAX_QUERIES_PER_HOUR <次数>

| MAX_UPDATES_PER_HOUR <次数>

| MAX_CONNECTIONS_PER_HOUR <次数>

| MAX_USER_CONNECTIONS <次数>

（3）新建权限语法

使用 GRANT 语句创建一个新的用户 testUser，密码为 testPwd。用户 testUser 对所有的数据有查询、插入权限，并授予 GRANT 权限。

mysql> GRANT SELECT,INSERT ON *.*

    -> TO 'testUser'@'localhost'

    -> IDENTIFIED BY 'testPwd'

-> WITH GRANT OPTION;

（4）使用 SELECT 语句查询用户 testUser 的权限

mysql> SELECT Host,User,Select_priv,Grant_priv

    -> FROM mysql.user

-> WHERE User='testUser';

（4）删除用户权限

语法格式有两种形式，如下所示：

1) 第一种：

REVOKE <权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ]…

ON <对象类型> <权限名> FROM <用户1> [ , <用户2> ]…

2) 第二种：

REVOKE ALL PRIVILEGES, GRANT OPTION

FROM user <用户1> [ , <用户2> ]…

1. 使用 REVOKE 语句取消用户 testUser 的插入权限

mysql> REVOKE INSERT ON *.*

-> FROM 'testUser'@'localhost';

 

33、事务

（1）开始事务

BEGIN TRANSACTION <事务名称> |@<事务变量名称>

@<事务变量名称>是由用户定义的变量，必须用 char、varchar、nchar 或 nvarchar数据类型来声明该变量。

BEGIN TRANSACTION 语句的执行使全局变量 @@TRANCOUNT 的值加 1。

（2）提交事务

COMMIT 表示提交事务，即提交事务的所有操作。具体地说，就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中，事务正常结束。

COMMIT TRANSACTION <事务名称> |@<事务变量名称>

COMMIT TRANSACTION语句的执行使全局变量 @@TRANCOUNT 的值减 1。

（3）撤销事务

ROLLBACK 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。这里的操作指对数据库的更新操作。

 

当事务执行过程中遇到错误时，使用 ROLLBACK TRANSACTION 语句使事务回滚到起点或指定的保持点处。同时，系统将清除自事务起点或到某个保存点所做的所有的数据修改，并且释放由事务控制的资源。因此，这条语句也标志着事务的结束。

 

ROLLBACK [TRANSACTION]

[<事务名称>| @<事务变量名称> | <存储点名称>| @ <含有存储点名称的变量名>

 

（4）回滚到存储点

当条件回滚只影响事务的一部分时，事务不需要全部撤销已执行的操作。可以让事务回滚到指定位置，此时，需要在事务中设定保存点（SAVEPOINT）。保存点所在位置之前的事务语句不用回滚，即保存点之前的操作被视为有效的。保存点的创建通过“SAVING TRANSACTION<保存点名称>”语句来实现，再执行“ROLLBACK TRANSACTION<保存点名称>”语句回滚到该保存点。

若事务回滚到起点，则全局变量 @@TRANCOUNT 的值减 1；若事务回滚到指定的保存点，则全局变量 @@TRANCOUNT 的值不变。

 

34、数据库备份

数据库备份是指通过导出数据或者复制表文件的方式来制作数据库的副本。当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。

 

可以使用 SELECT INTO OUTFILE 语句把表数据导出到一个文本文件中进行备份。

注意：这种方法只能导出或导入数据的内容，而不包括表的结构。若表的结构文件损坏，则必须先设法恢复原来表的结构。

 

【实例】将数据库 test_db 的表 tb_students_info 的全部数据备份到 C 盘的数据备份目录下文件名为 file.txt 的文件中，要求每个字段用逗号分开，并且字符用双引号标注，每行以问号结束。

mysql> SELECT * FROM test_db.tb_students_info

    -> INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/file.txt'

    -> FIELDS TERMINATED BY '"'

-> LINES TERMINATED BY '?';

 

33、数据库恢复

数据库恢复机制设计的两个关键问题是：第一，如何建立冗余数据；第二，如何利用这些冗余数据实施数据库恢复。

 

建立冗余数据最常用的技术是数据转储和登录日志文件。通常在一个数据库系统中，这两种方法是一起使用的。

 

数据转储是 DBA 定期地将整个数据库复制到磁带或另一个磁盘上保存起来的过程。这些备用的版本成为后备副本或后援副本。

 

可使用 LOAD DATA…INFILE 语句来恢复先前备份的数据。

 

将之前导出的数据备份文件 file.txt 导入数据库 test_db 的表 tb_students_copy 中，其中 tb_students_copy 的表结构和 tb_students_info 相同

 

mysql> LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/

Uploads/file.txt'

    -> INTO TABLE test_db.tb_students_copy

    -> FIELDS TERMINATED BY ','

    -> OPTIONALLY ENCLOSED BY '"'

    -> LINES TERMINATED BY '?';