文章目录
一，数据库
（一）数据库的原理
如果将数据定义为描述事物的符号记录，那么数据库就是长期存储在计算机内的、有组织的、可共享的、大量的数据集合，
数据库中的数据按一定的数据模型组织、描述和存储。根据数据模型进行数据的组织、描述、获取和维护的角色就是处于用户与操作系统之间的一层软件即数据库管理系统。
由数据库、数据库管理系统、应用程序和数据库管理员组成存储、管理、处理和维护数据的系统就是数据库系统。
（二）数据模型
数据模型就是对实际数据特征的在计算机中的抽象。
这种抽象需要满足：
- 较真实地模拟现实世界的事物。
- 容易被人理解。
- 便于在计算机中实现。
而实际上这三者难以同时达到全部满足，因此就在数据库系统中针对不同的使用对象和使用目的，采用不同的数据模型：
- 概念模型：在数据库设计阶段，从用户的角度对数据与信息进行建模。
- 逻辑模型和物理模型：分别与《数据结构》中数据的逻辑结构和存储结构的概念很象。
从真实世界到信息世界再到机器世界的这一系列的抽象活动，就是数据库系统技术的核心所在。
1，概念模型
概念模型是从真实世界到信息世界的抽象。
首先了解一下信息世界中的一些概念：
- 实体entity：客观存在并能相互区别的事物。如学生、课程、学生的一次选课、学生与课程的关系等。
- 属性attribute：实体所具有的某一特性。如学生实体可以由学号、姓名、性别、出生年月、所在院系、入学时间等属性组成。
- 码key：唯一标识实体的属性集。 例如学号是学生实体的码。
- 实体型entity type：用实体名及其属性名集合抽象的同类实体。例如学生（学号、姓名、性别、出生年月、所在院系、入学时间）就是一个实体型。
- 实体集entity set：同一类型实体的集合。 例如全体学生就是一个实体集。
- 联系relationship：不同实体集之间的联系。实体之间的联系有一对一、 一对多和多对多等多种类型。
概念模型的一种表示方法就是实体-联系方法，具体就是用 E-R 图来描述现实世界的概念模型，
2，逻辑模型
数据模型通常由数据结构、数据操作和数据的完整性约束条件三部分组成：
- 数据结构描述数据库的组成对象以及对象之间的联系。
- 数据操作是指对数据库中各种对象(型)的实例(值)允许执行的操作的集合，包括操作及有关的操作规则。
- 数据的完整性约束条件是一组完整性规则。 完整性规则是给定的数据模型中数据及其联系所具有的制约和依存规则， 用以限定符合数据模型的数据库状态以及状态的变化， 以保证数据的正确、 有效和相容。
数据库领域中主要的逻辑数据模型有：
- 层次模型(hierarchical model)
- 网状模型(network model)
- 关系模型(relational model)
- 面向对象数据模型(object oriented data model)
- 对象关系数据模型(object relational data model)
- 半结构化数据模型(semistructure data model)
关系模型是最重要的一种数据模型。 关系型数据库系统，比如说MySQL，就采用关系模型作为数据的组织方式，。
从用户角度看，关系模型由一组关系组成。 每个关系的数据结构是一张规范化的二维表：
| 学号 | 姓名 | 年龄 | 性别 | 系名 | 年级 |
|:--------😐:--------😐:--------😐:--------😐:--------😐:--------😐:--------😐
| 2013004 | 王小明 | 19 | 女 | 社会学 | 2013 |
| 2013008 | 张文斌 | 18 | 女 | 法律 | 2013 |
| … | … | … | … | … | … |
- 实体以及实体间的联系都是用关系来表示的。 例如导师实体、研究生实体、导师与研究生之间的 X 对 X 联系都可以用一个关系来表示。
因为关系模型建立关系代数和关系演算等数学理论基础上，所以能使用所谓的一次一集合(set-at-a-time)的方式实现关系模型的数据操作，这供了 一组完备的高级关系运算，以支持对数据库的各种操作，主要包括查询、插入、删除和更新数据。
- 关系型数据库系统通常用结构化查询语言（Structured QueryLanguage，SQL）。
这些操作必须满足对关系的某种约束条件即关系的完整性约束条件:
二，MySQL体系结构和存储引擎
（一）MySQL体系结构
首先需要知道的是，MySQL是一种网络数据库系统。网络应用中最典型的架构就是 C/S架构与 B/S架构。MySQL就采用 C/S架构：
从总体来看，MySQL可分为三层：
- 应用连接层：主要是客户端连接器（Client Connectors），主要负责连接管理、授权认证、安全等等。
- MySQL服务层：负责具体的查询处理、事务管理、存储管理、恢复管理，以及其他的附加功能。
- 存储引擎层：负责通过指定的存储引擎对数据进行存储与提取。
- 物理存储层：实际存储到存储介质中的数据与文件。
（1）客户端连接器（Client Connectors）
为JDBC、python等提供基于标准的驱动程序，使开发人员能够用自己选择的语言构建数据库应用程序。
每当一个客户端通过客户端连接器发来一条连接请求，服务器都会创建一条线程来维护通信服务。
（2）连接池（Connection Pool）
为了在无限的客户端的求和有限的数据库客户端连接数量的要求下保持数据库访问性能，就需要一种用来管理MySQL客户端连接任务的线程的机制，这就是数据库连接池。
（3）管理服务和工具组件（Services & utilities）
系统管理和控制工具，例如备份恢复、MySQL 复制、集群等 。
（4）SQL 接口（SQL Interface）
接受用户的 SQL 命令，并且返回用户需要查询的结果。
（5）查询解析器（Parser）
SQL 命令传递到解析器的时候会被解析器验证和解析（权限、语法结构）。
（6）查询优化器（Optimizer）
SQL 语句在查询之前会使用查询优化器对查询进行优化。
（6）缓存（Caches）
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据 。
（二）存储引擎
1，什么是MySQL存储引擎
MySQL属于关系型数据库，而关系型数据库的数据存储是以数据表的形式进行的，MySQL存储引擎就是完成表的创建、数据的存储、检索、更新等技术的实质的方法。
- 在关系数据库中，数据库表是一系列二维数组的集合，用来存储数据和操作数据的逻辑结构。它由纵向的列和横向的行组成。行被称为记录，是组织数据的单位；列被称为字段，每一列表示记录的一个属性，有相应的描述信息，如数据类型、数据宽度等。
插件式的存储引擎选择是MySQL区别于其他数据库的一个最重要特性。从MySQL数据库5.5.8版本开始，默认使用 InnoDB。
由于MySQL数据库的开源特性，用户可以根据MySQL预定义的存储引擎接口编写自定义存储引擎。
2，常用的存储引擎
（1）InnoDB
InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特点是行锁设计、支持外键。
InnoDB提供了良好的事务管理、崩溃修复和并发控制能力，但读写效率相对较差、数据空间占用相对较大。
（2）MyISAM
MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向联机分析处理（OLAP）数据库应用。
MyISAM提供了良好的数据表读性能，但写性能相对较差。
（3）MEMORY
MEMORY存储引擎将表结构保存在磁盘文件中，将表的内容是存储在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。它非常适合用于存储临时数据的临时表，以及数据仓库中的纬度表。
虽然MEMORY存储引擎速度非常快，但在使用上还是有一定的限制，比如，只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型等。
3，如何选择存储引擎
存储引擎各有优势，选择适合的才是最重要的。
4，设置表的存储引擎
1，查看数据库支持那些存储引擎：
mysql> SHOW ENGINES; # 查询支持的存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
2，查看数据库默认使用的存储引擎：
mysql> SHOW VARIABLES LIKE '%storage_engine%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
3，创建表时指定存储引擎：
mysql> CREATE TABLE t1(...) ENGINE=MyISAM;
4，查看某张表使用的存储引擎：
mysql> SHOW CREATE TABLE t1;
5，修改某表的存储引擎：
mysql> ALTER TABLE t1 ENGINE=InnoDB;
- 或编辑配置文件中的 default-storage-engine 参数并重启MySQL。
三，SQL 语言与MySQL数据库的基本操作
（一）SQL 语言
结构化查询语言(Structured Query Language, SQL)是关系数据库的标准语言，也是一个通用的、功能极强的关系数据库语言。
SQL 语句主要可以划分为以下三类：
- DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括
create、drop、alter等。 - DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括
insert、delete、udpate和select等。 - DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的 语句关键字包括
grant、revoke等
（二）创建并查看现有的数据库
CREATE DATABASE dbname； # 创建数据库
SHOW DATABASES; # 查看现有数据库
- 数据库命不要重名、不要使用关键字、最好使用小写命名。
mysql> CREATE DATABASE example;
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| example |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
- mysql存储了系统的用户权限信息。
- information_schema主要存储了系统中的一些数据库对象信息。比如用户表信息、列信息、权限信息、字符集信息、分区信息等。
- performance_schema主要用于收集数据库服务器性能参数。
- sys主要存储系统的元数据信息，为方便地查询重要信息与性能优化提供更直观的数据。
（三）选择与删除指定的数据库
USE dbname； # 选择数据库
DROP DATABASE dbname； # 删除数据库
mysql> USE example;
Database changed # 表示选择数据库成功
mysql> DROP DATABASE example;
Query OK, 0 rows affected (0.22 sec) # 表示删除数据库成功
mysql> SHOW DATABASES; # 删除后再查看一下
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
四，使用帮助
可以使用 MySQL 中的帮助文档以便快速查找各种语法定义：
mysql> help; # 查看命令行命令
For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
https://shop.mysql.com/
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
notee (\t) Don't write into outfile.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.
For server side help, type 'help contents'
mysql> help contents # 查看所有可供查询的的分类
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Components
Compound Statements
Contents
Data Definition
Data Manipulation
Data Types
Functions
Geographic Features
Help Metadata
Language Structure
Plugins
Storage Engines
Table Maintenance
Transactions
User-Defined Functions
Utility
mysql> help SHOW # 查看SHOW命令的用法
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW {REPLICAS | SLAVE HOSTS}
SHOW {REPLICA | SLAVE} STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
like_or_where: {
LIKE 'pattern'
| WHERE expr
}
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
https://dev.mysql.com/doc/refman/8.0/en/extended-show.html.
URL: https://dev.mysql.com/doc/refman/8.0/en/show.html
mysql> help CREATE DATABASE # 查看CREATE DATABASE命令的用法
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
| ENCRYPTION [=] {'Y' | 'N'}
}
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: https://dev.mysql.com/doc/refman/8.0/en/create-database.html
五， 数据类型与运算符
（一）MySQL支持的数据类型
在确定数据表结构，先了解MySQL支持哪些数据类型。数据类型是数据的一种属性， 规定了数据的存储格式、有效范围和相应的约束条件。
不同的MySQL版本支持的数据类型可能会稍有不同，可以通过查询相应版本的帮助文件来获得具体信息。
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Components
Compound Statements
Contents
Data Definition
Data Manipulation
Data Types
Functions
Geographic Features
Help Metadata
Language Structure
Plugins
Storage Engines
Table Maintenance
Transactions
User-Defined Functions
Utility
mysql> help DATA TYPES;
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB DATA TYPE
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DEC
DECIMAL
DOUBLE
DOUBLE PRECISION
ENUM
FLOAT
INT
INTEGER
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
SET DATA TYPE
SMALLINT
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
VARCHAR
YEAR DATA TYPE
或参考官方文档：
（二）MySQL中的运算符
一旦数据表结构确定后，表中的数据代表的意义也就确定了，就能使用MySQL中的运算符对表中的数据进行运算来获取额外的数据。
mysql> help Functions; # 再查看Operators相关信息
You asked for help about help category: "Functions"
For more information, type 'help <item>', where <item> is one of the following
categories:
Aggregate Functions and Modifiers
Bit Functions
Cast Functions and Operators
Comparison Operators
Date and Time Functions
Encryption Functions
Enterprise Encryption Functions
Flow Control Functions
GROUP BY Functions and Modifiers
GTID
Information Functions
Internal Functions
Locking Functions
Logical Operators
Miscellaneous Functions
Numeric Functions
Performance Schema Functions
Spatial Functions
String Functions
Window Functions
XML
或参考官方文档：
六，数据表的基本操作
表是数据库存储数据的基本单位。 一个表包含若干个字段或记录。 表的操作包括创建新表、 修改表和删除表。 这些操作都是数据库管理中最基本， 也是最重要的操作。
（一）创建并查看表
1，创建表的基本语法
CREATE TABLE 表名(属性名 数据类型［完整性约束条件],
属性名 数据类型［完整性约束条件],
...
[表级别约束条件]
);
-
help CREATE TABLE # 查看创建表的完整语法
- 属性名表示表中字段即每一列的名称；
- 数据类型指定字段的数据类型；
- 完整性约束条件指定字段的某些特殊约束条件。 如果不满足完整性约束条件， 数据库系统将不执行用户的操作，其目的是为了保证数据库中数据的完整性。
实例0，创建数据表：
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| example |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> USE example;
Database changed
mysql> CREATE TABLE example0(id INT,
-> name VARCHAR(20),
-> sex BOOLEAN);
Query OK, 0 rows affected (0.74 sec)
2，查看表
mysql> SHOW TABLES; # 查看已存在的数据表
+-------------------+
| Tables_in_example |
+-------------------+
| example0 |
+-------------------+
1 row in set (0.00 sec)
3，主键约束
主键又称主码 ，是表中一列或多列的组合。
主键约束（ Primary Key Constraint）要求主键列的数据唯一且不能为空，
主键能够唯一标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并加快数据库查询的速度。
（1）单字段主键
主键是由一个字段构成时， 可以直接在该字段的后面加上PRIMARY KEY。
实例1，创建带有单字段主键的数据表，存储学生基本信息：
mysql> CREATE TABLE example1
-> (
-> stu_id INT PRIMARY KEY,
-> stu_name VARCHAR(20),
-> stu_sex BOOLEAN
-> );
Query OK, 0 rows affected (0.39 sec)
（2）多字段主键
主键是由多个属性组合而成时，需在属性定义完之后统一设置。
实例2，创建带有多字段主键的数据表，存储学生课程成绩：
mysql> CREATE TABLE example2
-> (
-> stu_id INT,
-> course_id INT,
-> grade FLOAT,
-> PRIMARY KEY(stu_id,course_id)
-> );
Query OK, 0 rows affected (0.68 sec)
4，外键约束
外键首先是表中是一列或多列，
外键对应的是参照完整性，一个表的外键可以为空值，否则每一个外键值必须对应另一个表中的主键且数据类型必须一致。
外键用来在两个表的数据之间建立多对一关系的连接，主要作用是保证数据引用的完整性和一致性。
例如部门表 tb_dept 的主键 id ， 在员工表 tb_stuff 中有一个键 deptld 与前面的 id 关联。
- 主表（父表）：相关联字段中主键所在的那个表即是主表，即部门表。
- 从表（子表）：相关联字段中外键所在的那个表即是从表，员工表。
- 多个员工通过外键关联到一个部门。
[CONSTRAINT 外键别名］
FOREIGN KEY (子表属性1, 子表属性2子表属性n)
REFERENCES 主表名(子表属性1, 子表属性2子表属性n);
实例3，创建带有外键的数据表，存储：
mysql> CREATE TABLE example3
-> (
-> id INT PRIMARY KEY,
-> stu_id INT,
-> course_id INT,
-> CONSTRAINT c_fk FOREIGN KEY (stu_id,course_id) REFERENCES example2(stu_id,course_id)
-> );
Query OK, 0 rows affected (0.79 sec)
5，非空约束
设置表的非空约束是指在创建表时为表的某些特殊字段加上NOT NULL约束条件，保证指定字段的值不能为空值。
实例4，创建带有非空字段的数据表，存储：
mysql> CREATE TABLE example4
-> (
-> id INT NOT NULL PRIMARY KEY,
-> name VARCHAR(20) NOT NULL,
-> stu_id INT,
-> CONSTRAINT d_fk FOREIGN KEY (stu_id) REFERENCES example1(stu_id)
-> );
Query OK, 0 rows affected (0.71 sec)
6，唯一性约束
设置表的唯一性约束是指在创建表时为表的某些特殊字段加上UNIQUE约束条件，保证所有记录中该字段的值不能重复出现。
实例5，创建带有唯一字段的数据表，存储：
mysql> CREATE TABLE example5
-> (
-> id INT PRIMARY KEY,
-> stu_id INT UNIQUE,
-> name VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.73 sec)
7，自增属性
设置自增属性是指在创建表时为表的一个整数类型的主键字段加上AUTO_INCREMENT约束，用于为表中插入的新记录自动生成唯一的从1 开始自增的 ID 值。
实例6，创建带有自增字段的数据表，存储：
mysql> CREATE TABLE example6
-> (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> stu_id INT UNIQUE,
-> name VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.47 sec)
8，默认约束
默认约束（ Default Constraint ）指定某列的默认值。
实例7，创建带有默认值字段的数据表，存储：
mysql> CREATE TABLE example7
-> (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> stu_id INT UNIQUE,
-> name VARCHAR(20) NOT NULL,
-> English VARCHAR(20) DEFAULT 'zero',
-> Math FLOAT DEFAULT 0,
-> Computer FLOAT DEFAULT 0
-> );
Query OK, 0 rows affected (0.36 sec)
（二）查看表结构
查看表结构是指查看数据库中巳存在的表的定义。
1，查看表基本结构
使用DESCRIBE语句可以查看表的基本定义。
- 可以缩写成
DESC。
实例8，查看表的基本定义：
mysql> DESCRIBE example1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id | int | NO | PRI | NULL | |
| stu_name | varchar(20) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
mysql> DESCRIBE example2;
+-----------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------+------+-----+---------+-------+
| stu_id | int | NO | PRI | NULL | |
| course_id | int | NO | PRI | NULL | |
| grade | float | YES | | NULL | |
+-----------+-------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DESCRIBE example3;
+-----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| stu_id | int | YES | MUL | NULL | |
| course_id | int | YES | | NULL | |
+-----------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DESCRIBE example4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| stu_id | int | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DESCRIBE example5;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| stu_id | int | YES | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DESCRIBE example6;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| stu_id | int | YES | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> DESCRIBE example7;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| stu_id | int | YES | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
| English | varchar(20) | YES | | zero | |
| Math | float | YES | | 0 | |
| Computer | float | YES | | 0 | |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
2，查看表详细结构
使用SHOW CREATE TABLE语句可以查看表的详细定义。
- 使用
\G参数让输出更易读。
实例8，查看表的详细定义：
mysql> SHOW CREATE TABLE example3;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| example3 | CREATE TABLE `example3` (
`id` int NOT NULL,
`stu_id` int DEFAULT NULL,
`course_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c_fk` (`stu_id`,`course_id`),
CONSTRAINT `c_fk` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example2` (`stu_id`, `course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.14 sec)
mysql> SHOW CREATE TABLE example3 \G
*************************** 1. row ***************************
Table: example3
Create Table: CREATE TABLE `example3` (
`id` int NOT NULL,
`stu_id` int DEFAULT NULL,
`course_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c_fk` (`stu_id`,`course_id`),
CONSTRAINT `c_fk` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example2` (`stu_id`, `course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
（三）修改表
修改表是指修改数据库中已存在的表的定义。 修改表比重新定义表简单， 不需要重新加载数据， 也不会影响正在进行的服务。
1，修改表名
使用 ALTER TABLE 语旬修改表名：
ALTER TABLE 旧表名 RENAME 新表名;
实例9，修改表名：
mysql> SHOW TABLES;
+-------------------+
| Tables_in_example |
+-------------------+
| example0 |
| example1 |
| example2 |
| example3 |
| example4 |
| example5 |
| example6 |
| example7 |
+-------------------+
8 rows in set (0.00 sec)
mysql> ALTER TABLE example0 RENAME user;
Query OK, 0 rows affected (0.26 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_example |
+-------------------+
| example1 |
| example2 |
| example3 |
| example4 |
| example5 |
| example6 |
| example7 |
| user |
+-------------------+
8 rows in set (0.00 sec)
2，修改字段的数据类型
同样可使用ALTER TABLE语句修改字段的数据类型：
ALTER TABLE 表名 MODIFY 属性名 数据类型；
实例10，修改字段的数据类型：
mysql> DESC user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE user MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
3，修改字段
同样可使用ALTER TABLE语句修改字段：
ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;
实例11，修改字段：
mysql> DESC example1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id | int | NO | PRI | NULL | |
| stu_name | varchar(20) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE example1 CHANGE stu_name name VARCHAR(20); # 只修改字段名
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC example1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE example1 CHANGE stu_sex sex INT(2); # 同时修改字段名与数据类型
Query OK, 0 rows affected, 1 warning (1.16 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> DESC example1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stu_id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
4，增加字段
同样可使用ALTER TABLE语句增加字段：
ALTER TABLE 表名 ADD 属性名1 数据类型［完整性约束条件］ ［FIRST | AFTER 属性名2］;
实例12，增加无完整性约束条件的字段：
mysql> ALTER TABLE user ADD phone VARCHAR(20);
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- 新增字段默认为表的最后一个字段。
实例13，增加有完整性约束条件的字段：
mysql> ALTER TABLE user ADD age INT(4) NOT NULL;
Query OK, 0 rows affected, 1 warning (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> DESC user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| age | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 如果能够加上完整性约束条件， 一定要加上。 这样可以保证此字段的安全性， 甚至可以提高整个表的数据的安全性。
实例14，在表的第一个位置增加字段：
mysql> ALTER TABLE user ADD num INT(8) PRIMARY KEY FIRST;
Query OK, 0 rows affected, 1 warning (0.98 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> DESC user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num | int | NO | PRI | NULL | |
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| age | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
实例15，在表的指定位置之后增加字段：
mysql> ALTER TABLE user ADD address VARCHAR(30) NOT NULL AFTER phone;
Query OK, 0 rows affected (0.86 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| num | int | NO | PRI | NULL | |
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(30) | NO | | NULL | |
| age | int | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
实例16，修改字段的排列位置：
mysql> ALTER TABLE user MODIFY name VARCHAR(30) FIRST;
Query OK, 0 rows affected (0.86 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES | | NULL | |
| num | int | NO | PRI | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(30) | NO | | NULL | |
| age | int | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE user MODIFY sex TINYINT(1) AFTER age;
Query OK, 0 rows affected, 1 warning (0.96 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> DESC user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES | | NULL | |
| num | int | NO | PRI | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(30) | NO | | NULL | |
| age | int | NO | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
5，删除字段
同样可使用ALTER TABLE语句删除字段：
ALTER TABLE 表名 DROP 属性名;
实例17，删除字段：
mysql> ALTER TABLE user DROP id;
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| num | int | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(30) | NO | | NULL | |
| age | int | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
6，删除表的外键约束
同样可使用ALTER TABLE语句删除表的外键约束：
ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;
实例18，删除表的外键约束：
mysql> SHOW CREATE TABLE example3 \G
*************************** 1. row ***************************
Table: example3
Create Table: CREATE TABLE `example3` (
`id` int NOT NULL,
`stu_id` int DEFAULT NULL,
`course_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c_fk` (`stu_id`,`course_id`),
CONSTRAINT `c_fk` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example2` (`stu_id`, `course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> ALTER TABLE example3 DROP FOREIGN KEY c_fk;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE example3 \G
*************************** 1. row ***************************
Table: example3
Create Table: CREATE TABLE `example3` (
`id` int NOT NULL,
`stu_id` int DEFAULT NULL,
`course_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c_fk` (`stu_id`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
（四）删除表
删除表是指删除数据库中已存在的表。 删除表时， 会删除表中的所有数据。 因此， 在删除表时要特别注意。
1，删除没有被关联的普通表
使用DROP TABLE语句直接删除没有被其他表关联的普通表：
DROP TABLE 表名;
实例19，直接删除没有被其他表关联的普通表：
mysql> DROP TABLE example5;
Query OK, 0 rows affected (0.27 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_example |
+-------------------+
| example1 |
| example2 |
| example3 |
| example4 |
| example6 |
| example7 |
| user |
+-------------------+
7 rows in set (0.00 sec)
2，删除被其他表关联的父表
删除被其他表关联的父表，首先需要删除子表的外键约束， 然后再删除父表。
实例20，删除有被子表关联的父表：
mysql> ALTER TABLE example4 DROP FOREIGN KEY d_fk; # 子表example4通过外键d_fk关联到父表example1，所以先删除子表的外键约束
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE example4 \G
*************************** 1. row ***************************
Table: example4
Create Table: CREATE TABLE `example4` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL,
`stu_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `d_fk` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> DROP TABLE example1; # 然后再删除父表
Query OK, 0 rows affected (0.27 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_example |
+-------------------+
| example2 |
| example3 |
| example4 |
| example6 |
| example7 |
| user |
+-------------------+
6 rows in set (0.00 sec)
七，插入、更新与删除数据
（一）插入数据
使用INSERT语句插入数据：
mysql> help INSERT;
Name: 'INSERT'
Description:
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...
|
VALUES row_constructor_list
}
[AS row_alias[(col_alias [, col_alias] ...)]]
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[AS row_alias[(col_alias [, col_alias] ...)]]
SET assignment_list
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
[AS row_alias[(col_alias [, col_alias] ...)]]
{SELECT ... | TABLE table_name}
[ON DUPLICATE KEY UPDATE assignment_list]
value:
{expr | DEFAULT}
value_list:
value [, value] ...
row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]
assignment:
col_name = [row_alias.]value
assignment_list:
assignment [, assignment] ...
INSERT inserts new rows into an existing table. The INSERT ... VALUES,
INSERT ... VALUES ROW(), and INSERT ... SET forms of the statement
insert rows based on explicitly specified values. The INSERT ... SELECT
form inserts rows selected from another table or tables. You can also
use INSERT ... TABLE in MySQL 8.0.19 and later to insert rows from a
single table. INSERT with an ON DUPLICATE KEY UPDATE clause enables
existing rows to be updated if a row to be inserted would cause a
duplicate value in a UNIQUE index or PRIMARY KEY. In MySQL 8.0.19 and
later, a row alias with one or more optional column alises can be used
with ON DUPLICATE KEY UPDATE to refer to the row to be inserted.
For additional information about INSERT ... SELECT and INSERT ... ON
DUPLICATE KEY UPDATE, see [HELP INSERT SELECT], and
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html.
In MySQL 8.0, the DELAYED keyword is accepted but ignored by the
server. For the reasons for this, see [HELP INSERT DELAYED],
Inserting into a table requires the INSERT privilege for the table. If
the ON DUPLICATE KEY UPDATE clause is used and a duplicate key causes
an UPDATE to be performed instead, the statement requires the UPDATE
privilege for the columns to be updated. For columns that are read but
not modified you need only the SELECT privilege (such as for a column
referenced only on the right hand side of an col_name=expr assignment
in an ON DUPLICATE KEY UPDATE clause).
When inserting into a partitioned table, you can control which
partitions and subpartitions accept new rows. The PARTITION option
takes a list of the comma-separated names of one or more partitions or
subpartitions (or both) of the table. If any of the rows to be inserted
by a given INSERT statement do not match one of the partitions listed,
the INSERT statement fails with the error Found a row not matching the
given partition set. For more information and examples, see
https://dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html.
URL: https://dev.mysql.com/doc/refman/8.0/en/insert.html
1，为表的所有字段插入数据
mysql> DESC department;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| d_id | int | NO | PRI | NULL | |
| d_name | varchar(20) | NO | UNI | NULL | |
| d_function | varchar(50) | YES | | NULL | |
| d_address | varchar(60) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM department;
+------+-----------+--------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
+------+-----------+--------------------------+-------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO department VALUES(1004, '法务部','处理公司法务业务','2号楼3层');
Query OK, 1 row affected (0.15 sec)
mysql> SELECT * FROM department;
+------+-----------+--------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
+------+-----------+--------------------------+-------------+
4 rows in set (0.00 sec)
mysql> INSERT INTO department(d_id,d_name,d_function,d_address) VALUES(1005, '市场部','负责产品推广、销售与运营','2号楼4层');
Query OK, 1 row affected (0.17 sec)
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
+------+-----------+--------------------------------------+-------------+
5 rows in set (0.00 sec)
- 为自增字段(AUTOJNCREMENT)赋值：第一种方法是在INSERT语句中不为该字段赋值； 第二种方法是在INSERT语句中将该字段赋值为NULL。这两种情况下， 数据库系统会自动为自增字段赋值。其值是上条记录中该字段的取值+1。
2，为表的指定字段插入数据
mysql> INSERT INTO department(d_id,d_name) VALUES(1006,'客服部');
Query OK, 1 row affected (0.15 sec)
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
| 1006 | 客服部 | NULL | NULL |
+------+-----------+--------------------------------------+-------------+
6 rows in set (0.00 sec)
3，同时插入多条记录
mysql> INSERT INTO department VALUES
-> (1007,'财务部','负责财务结算、开支管理与工资发放','2号楼3层'),
-> (1008,'行政部',NULL,NULL);
Query OK, 2 rows affected (0.15 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
| 1006 | 客服部 | NULL | NULL |
| 1007 | 财务部 | 负责财务结算、开支管理与工资发放 | 2号楼3层 |
| 1008 | 行政部 | NULL | NULL |
+------+-----------+--------------------------------------------------+-------------+
8 rows in set (0.00 sec)
4，将查询结果插入到表中
mysql> CREATE TABLE dptname
-> (
-> id INT(4) PRIMARY KEY,
-> name VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected, 1 warning (0.32 sec)
mysql> DESC dptname
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO dptname VALUES(1,'后勤部');
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM dptname;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 后勤部 |
+----+-----------+
1 row in set (0.00 sec)
mysql> INSERT INTO department(d_id,d_name)
-> SELECT '1000'+(id+8),name FROM dptname;
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
| 1006 | 客服部 | NULL | NULL |
| 1007 | 财务部 | 负责财务结算、开支管理与工资发放 | 2号楼3层 |
| 1008 | 行政部 | NULL | NULL |
| 1009 | 后勤部 | NULL | NULL |
+------+-----------+--------------------------------------------------+-------------+
- 使用查询结果作为数据内容时，必须保证字段个数和每个对应的字段的数据类型是一致的。
（二）更新数据
使用UPDATE语句插入数据：
UPDATE 表名
SET 属性名1=取值1,属性名2=取值2,
...
WHERE条件表达式;
mysql> help UPDATE;
Name: 'UPDATE'
Description:
Syntax:
UPDATE is a DML statement that modifies rows in a table.
An UPDATE statement can start with a WITH clause to define common table
expressions accessible within the UPDATE. See
https://dev.mysql.com/doc/refman/8.0/en/with.html.
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
value:
{expr | DEFAULT}
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET assignment_list
[WHERE where_condition]
For the single-table syntax, the UPDATE statement updates columns of
existing rows in the named table with new values. The SET clause
indicates which columns to modify and the values they should be given.
Each value can be given as an expression, or the keyword DEFAULT to set
a column explicitly to its default value. The WHERE clause, if given,
specifies the conditions that identify which rows to update. With no
WHERE clause, all rows are updated. If the ORDER BY clause is
specified, the rows are updated in the order that is specified. The
LIMIT clause places a limit on the number of rows that can be updated.
For the multiple-table syntax, UPDATE updates rows in each table named
in table_references that satisfy the conditions. Each matching row is
updated once, even if it matches the conditions multiple times. For
multiple-table syntax, ORDER BY and LIMIT cannot be used.
For partitioned tables, both the single-single and multiple-table forms
of this statement support the use of a PARTITION option as part of a
table reference. This option takes a list of one or more partitions or
subpartitions (or both). Only the partitions (or subpartitions) listed
are checked for matches, and a row that is not in any of these
partitions or subpartitions is not updated, whether it satisfies the
where_condition or not.
*Note*:
Unlike the case when using PARTITION with an INSERT or REPLACE
statement, an otherwise valid UPDATE ... PARTITION statement is
considered successful even if no rows in the listed partitions (or
subpartitions) match the where_condition.
For more information and examples, see
https://dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html.
where_condition is an expression that evaluates to true for each row to
be updated. For expression syntax, see
https://dev.mysql.com/doc/refman/8.0/en/expressions.html.
table_references and where_condition are specified as described in
https://dev.mysql.com/doc/refman/8.0/en/select.html.
You need the UPDATE privilege only for columns referenced in an UPDATE
that are actually updated. You need only the SELECT privilege for any
columns that are read but not modified.
The UPDATE statement supports the following modifiers:
o With the LOW_PRIORITY modifier, execution of the UPDATE is delayed
until no other clients are reading from the table. This affects only
storage engines that use only table-level locking (such as MyISAM,
MEMORY, and MERGE).
o With the IGNORE modifier, the update statement does not abort even if
errors occur during the update. Rows for which duplicate-key
conflicts occur on a unique key value are not updated. Rows updated
to values that would cause data conversion errors are updated to the
closest valid values instead. For more information, see
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#ignore-effect-o
n-execution.
URL: https://dev.mysql.com/doc/refman/8.0/en/update.html
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
| 1006 | 客服部 | 提供品售后服务 | NULL |
| 1007 | 财务部 | 负责财务结算、开支管理与工资发放 | 2号楼3层 |
| 1008 | 行政部 | 负责日常行政管理 | NULL |
| 1009 | 后勤部 | 负责日常后勤与安保管理 | NULL |
+------+-----------+--------------------------------------------------+-------------+
9 rows in set (0.00 sec)
mysql> UPDATE department
-> SET d_address='待定'
-> WHERE d_name='客服部';
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
| 1006 | 客服部 | 提供品售后服务 | 待定 |
| 1007 | 财务部 | 负责财务结算、开支管理与工资发放 | 2号楼3层 |
| 1008 | 行政部 | 负责日常行政管理 | NULL |
| 1009 | 后勤部 | 负责日常后勤与安保管理 | NULL |
+------+-----------+--------------------------------------------------+-------------+
9 rows in set (0.00 sec)
mysql> UPDATE department
-> SET d_address='待定'
-> WHERE d_name='行政部' OR d_name='后勤部';
Query OK, 2 rows affected (0.14 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
| 1006 | 客服部 | 提供品售后服务 | 待定 |
| 1007 | 财务部 | 负责财务结算、开支管理与工资发放 | 2号楼3层 |
| 1008 | 行政部 | 负责日常行政管理 | 待定 |
| 1009 | 后勤部 | 负责日常后勤与安保管理 | 待定 |
+------+-----------+--------------------------------------------------+-------------+
9 rows in set (0.00 sec)
- 在更新数据前先使用带
WHERE的SELECT语句查询一下数据是否就是需要修改的数据。
（三）删除数据
使用UPDATE语句插入数据：
DELETE FROM 表名［WHERE条件表达式］;
mysql> help DELETE;
Name: 'DELETE'
Description:
Syntax:
DELETE is a DML statement that removes rows from a table.
A DELETE statement can start with a WITH clause to define common table
expressions accessible within the DELETE. See
https://dev.mysql.com/doc/refman/8.0/en/with.html.
Single-Table Syntax
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
The DELETE statement deletes rows from tbl_name and returns the number
of deleted rows. To check the number of deleted rows, call the
ROW_COUNT() function described in
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html.
Main Clauses
The conditions in the optional WHERE clause identify which rows to
delete. With no WHERE clause, all rows are deleted.
where_condition is an expression that evaluates to true for each row to
be deleted. It is specified as described in
https://dev.mysql.com/doc/refman/8.0/en/select.html.
If the ORDER BY clause is specified, the rows are deleted in the order
that is specified. The LIMIT clause places a limit on the number of
rows that can be deleted. These clauses apply to single-table deletes,
but not multi-table deletes.
Multiple-Table Syntax
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]
Privileges
You need the DELETE privilege on a table to delete rows from it. You
need only the SELECT privilege for any columns that are only read, such
as those named in the WHERE clause.
Performance
When you do not need to know the number of deleted rows, the TRUNCATE
TABLE statement is a faster way to empty a table than a DELETE
statement with no WHERE clause. Unlike DELETE, TRUNCATE TABLE cannot be
used within a transaction or if you have a lock on the table. See [HELP
TRUNCATE TABLE] and [HELP LOCK TABLES].
The speed of delete operations may also be affected by factors
discussed in
https://dev.mysql.com/doc/refman/8.0/en/delete-optimization.html.
To ensure that a given DELETE statement does not take too much time,
the MySQL-specific LIMIT row_count clause for DELETE specifies the
maximum number of rows to be deleted. If the number of rows to delete
is larger than the limit, repeat the DELETE statement until the number
of affected rows is less than the LIMIT value.
Subqueries
You cannot delete from a table and select from the same table in a
subquery.
Partitioned Table Support
DELETE supports explicit partition selection using the PARTITION
option, which takes a list of the comma-separated names of one or more
partitions or subpartitions (or both) from which to select rows to be
dropped. Partitions not included in the list are ignored. Given a
partitioned table t with a partition named p0, executing the statement
DELETE FROM t PARTITION (p0) has the same effect on the table as
executing ALTER TABLE t TRUNCATE PARTITION (p0); in both cases, all
rows in partition p0 are dropped.
PARTITION can be used along with a WHERE condition, in which case the
condition is tested only on rows in the listed partitions. For example,
DELETE FROM t PARTITION (p0) WHERE c < 5 deletes rows only from
partition p0 for which the condition c < 5 is true; rows in any other
partitions are not checked and thus not affected by the DELETE.
The PARTITION option can also be used in multiple-table DELETE
statements. You can use up to one such option per table named in the
FROM option.
For more information and examples, see
https://dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html.
URL: https://dev.mysql.com/doc/refman/8.0/en/delete.html
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
| 1006 | 客服部 | 提供品售后服务 | 待定 |
| 1007 | 财务部 | 负责财务结算、开支管理与工资发放 | 2号楼3层 |
| 1008 | 行政部 | 负责日常行政管理 | 待定 |
| 1009 | 后勤部 | 负责日常后勤与安保管理 | 待定 |
+------+-----------+--------------------------------------------------+-------------+
9 rows in set (0.00 sec)
mysql> SELECT * FROM department WHERE d_id>1007;
+------+-----------+-----------------------------------+-----------+
| d_id | d_name | d_function | d_address |
+------+-----------+-----------------------------------+-----------+
| 1008 | 行政部 | 负责日常行政管理 | 待定 |
| 1009 | 后勤部 | 负责日常后勤与安保管理 | 待定 |
+------+-----------+-----------------------------------+-----------+
2 rows in set (0.00 sec)
mysql> DELETE FROM department WHERE d_id>1007;
Query OK, 2 rows affected (0.08 sec)
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
| 1006 | 客服部 | 提供品售后服务 | 待定 |
| 1007 | 财务部 | 负责财务结算、开支管理与工资发放 | 2号楼3层 |
+------+-----------+--------------------------------------------------+-------------+
八，查询数据
数据查询是数据库中相当重要的一种操作，，前面的索引、视图等都是加快查询速度的机制。
（一）基本查询语句
数据库中可能包含着无数的表， 表中可能包含着无数的记录。 因此， 要获得所需的数据并非易事。 MySQL中可以使用SELECT语句来查询数据。 根据查询的条件的不同， 数据库系统会找到不同的数据。
SELECT 属性列表
FROM表名和视图列表
[WHERE 条件表达式1]
[GROUP BY 属性名1 [HAVING 条件表达式2 ]]
[ORDER BY 属性名 2 [ASC|DESC]];
- 如果有
WHERE子句， 就按照“条件表达式1 ” 指定的条件进行查询； - 如果有
GROUP BY子句， 就按照"属性名1”指定的字段进行分组； 如果GROUP BY子句后带着HAVING关键字， 那么只有满足“条件表达式2”中指定的条件的才能够输出。GROUP BY子句通常和COUNT()、SUM()等聚合函数一起使用; - 如果有
ORDER BY子句， 就按照“属性名2”指定的字段进行排序，默认的情况下是ASC。
（二）单表查询
单表查询是指从一张表中查询所需要的数据。
1，查询所有字段
mysql> SELECT num,d_id,name,sex,homeaddress FROM worker; # 方法一，列出所有字段
+-----+------+-----------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
+-----+------+-----------+------+------------------------------------------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM worker; # 方法二，使用通配符 *
+-----+------+-----------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
+-----+------+-----------+------+------------------------------------------+
11 rows in set (0.00 sec)
2，查询指定字段
mysql> SELECT name,sex,d_id FROM worker;
+-----------+------+------+
| name | sex | d_id |
+-----------+------+------+
| A.H.James | male | 1001 |
| 胡图图 | 男 | 1001 |
| 张益达 | 男 | 1004 |
| 李益达 | 男 | 1002 |
| 章益达 | 男 | 1002 |
| 方益达 | 女 | 1003 |
| 张小丽 | 女 | 1003 |
| 胡英俊 | 男 | 1005 |
| 佩奇 | 女 | 1006 |
| 浦西 | 女 | 1006 |
| 和珅 | 男 | 1007 |
+-----------+------+------+
11 rows in set (0.00 sec)
3，查询指定记录
使用WHERE子句指定查询条件：
WHERE条件表达式
mysql> SELECT * FROM worker WHERE d_id=1006;
+-----+------+--------+-----+--------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+--------+-----+--------------------+
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
+-----+------+--------+-----+--------------------+
2 rows in set (0.03 sec)
4，带IN关键字的集合查询
IN关键字可以判断某个字段的值是否在指定的集合中。 如果字段的值在集合中， 则满足查询条件， 该记录将被查询出来：
[NOT] IN (元素1,元素2,...,元素n)
mysql> SELECT * FROM worker WHERE d_id IN ( 1001, 1004 );
+-----+------+-----------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
+-----+------+-----------+------+------------------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM worker WHERE name NOT IN ('张益达','李益达','章益达','方益达');
+-----+------+-----------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
+-----+------+-----------+------+------------------------------------------+
7 rows in set (0.00 sec)
5，带BETWEEN AND的范围查询
BETWEEN AND关键字可以判读某个字段的值是否在指定的范围内。：
[NOT] BETWEEN 取值1 AND 取值2
mysql> SELECT * FROM worker WHERE d_id BETWEEN 1002 AND 1005;
+-----+------+-----------+-----+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+-----+------------------------------------------+
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
+-----+------+-----------+-----+------------------------------------------+
6 rows in set (0.33 sec)
6，带LIKE的字符匹配查询
LIKE关键字可以匹配字符串是否相等：
[NOT] LIKE '字符串'
- “字符串” 参数的值可以是一个完整的字符串， 也可以用
%匹配任意多个字符，或_匹配任意一个字符。
mysql> SELECT * FROM worker WHERE name LIKE '胡%';
+-----+------+-----------+-----+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+-----+------------------------------------------+
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
+-----+------+-----------+-----+------------------------------------------+
2 rows in set (0.03 sec)
7，查询空值
IS NULL关键字可以用来判断字段的值是否为空值(NULL)：
IS [NOT] NULL
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
| 1006 | 客服部 | 提供品售后服务 | NULL |
| 1007 | 财务部 | 负责财务结算、开支管理与工资发放 | 2号楼3层 |
+------+-----------+--------------------------------------------------+-------------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM department WHERE d_address IS NULL;
+------+-----------+-----------------------+-----------+
| d_id | d_name | d_function | d_address |
+------+-----------+-----------------------+-----------+
| 1006 | 客服部 | 提供品售后服务 | NULL |
+------+-----------+-----------------------+-----------+
1 row in set (0.00 sec)
8，带AND的多条件查询
AND关键字可以用来联合多个条件进行查询：
条件表达式1 AND 条件表达式2 [... AND 条件表达式n]
mysql> SELECT * FROM worker;
+-----+------+-----------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 |
+-----+------+-----------+------+------------------------------------------+
12 rows in set (0.00 sec)
mysql> SELECT * FROM worker
-> WHERE d_id>1002 AND name LIKE '张_达';
+-----+------+-----------+-----+---------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+-----+---------------------------------------+
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 |
+-----+------+-----------+-----+---------------------------------------+
2 rows in set (0.00 sec)
9，带OR的多条件查询
OR关键字也可以用来联合多个条件进行查询， 但是与AND关键字不同的是，只要满足这几个查询条件的其中一个，记录就会被查询出来。：
条件表达式1 OR 条件表达式2 ［...OR 条件表达式n］
mysql> SELECT * FROM worker
-> WHERE name LIKE '胡%' OR name LIKE '张%';
+-----+------+-----------+-----+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+-----+------------------------------------------+
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 |
+-----+------+-----------+-----+------------------------------------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM worker
-> WHERE name LIKE '胡%' AND d_id BETWEEN 1003 AND 1006 OR homeaddress LIKE '翻斗大街%';
+-----+------+-----------+-----+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+-----+------------------------------------------+
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
+-----+------+-----------+-----+------------------------------------------+
3 rows in set (0.00 sec)
- 与
AND一起使用时，AND会优先运算。
10，查询结果不重复
使用DISTINCT关键字来消除重复的记录:
SELECT DISTINCT 属性名
mysql> SELECT DISTINCT d_id FROM worker;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
| 1006 |
| 1007 |
+------+
7 rows in set (0.00 sec)
mysql> SELECT DISTINCT homeaddress FROM worker;
+------------------------------------------+
| homeaddress |
+------------------------------------------+
| proes street 386 pretoria central |
| 翻斗大街翻斗花园二号楼1001室 |
| 杨浦区国顺东路爱情公寓3602 |
| 杨浦区国顺东路爱情公寓3603 |
| 杨浦区国顺东路爱情公寓3601 |
| 杨浦区国顺东路爱情公寓3613 |
| 一号圆形山顶 |
| 二号圆形山顶 |
| 西城区柳荫街甲14号 |
| 杨浦区国顺东路爱情公寓3606 |
+------------------------------------------+
10 rows in set (0.00 sec)
11，对查询结果排序
使用ORDER BY关键字对记录进行排序：
ORDER BY 属性名［ASC|DESC］
mysql> SELECT name FROM worker WHERE d_id=1001 ORDER BY name ASC;
+------------+
| name |
+------------+
| A.H.James |
| B.E.Wilson |
| 胡图图 |
+------------+
3 rows in set (0.00 sec)
- 按升序排序时， 含空值的记录将最先显示;按降序排列时，含空值的记录将最后显示。
12，分组查询
GROUP BY关键字可以将查询结果按某个字段或多个字段进行分组：
GROUP BY 属性名［HAVING 条件表达式］［WITH ROLLUP ］
- “属性名” 是指按照该字段的值进行分组，且该属性名要出现在
SELECT后。 HAVING条件表达式用来限制分组后的显示，满足条件表达式的结果将被显示；WITH ROLLUP关键字将会在所有记录的最后加上一条记录。 该记录是上面所有记录的总和。
（1）单独使用GROUP BY关键字来分组
mysql> SELECT sex FROM worker GROUP BY sex;
+------+
| sex |
+------+
| male |
| 男 |
| 女 |
+------+
3 rows in set (0.00 sec)
GROUP BY关键字单独使用时， 只能查询出每个分组的一条记录。 这样使用的意义不大，所以通常与集合函数一起使用。
（2）与GROUP_CONCAT()函数一起使用
GROUP BY关键字与GROUP_CONCAT()函数一起使用时， 每个分组中指定字段值都显示出来：
mysql> SELECT sex, GROUP_CONCAT(name,'(',d_id,')')as info FROM worker GROUP BY sex;
+------+-------------------------------------------------------------------------------------------------------------------------------+
| sex | info |
+------+-------------------------------------------------------------------------------------------------------------------------------+
| male | A.H.James(1001) |
| 女 | 方益达(1003),张小丽(1003),佩奇(1006),浦西(1006) |
| 男 | 胡图图(1001),张益达(1004),李益达(1002),章益达(1002),胡英俊(1005),和珅(1007),张益达(1005),B.E.Wilson(1001) |
+------+-------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
- 可以在
GROUP_CONCAT()函数中向上面那样进行字段拼接与别名命名，让查询结果更符合输出要求。
（3）与集合函数一起使用
GROUP BY关键字与集合函数一起使用时， 可以通过集合函数计算分组中的总记录：
mysql> SELECT sex, COUNT(sex) FROM worker GROUP BY sex;
+------+------------+
| sex | COUNT(sex) |
+------+------------+
| male | 1 |
| 男 | 8 |
| 女 | 4 |
+------+------------+
3 rows in set (0.00 sec)
mysql> SELECT sex, COUNT(sex) AS number FROM worker GROUP BY sex;
+------+--------+
| sex | number |
+------+--------+
| male | 1 |
| 男 | 8 |
| 女 | 4 |
+------+--------+
3 rows in set (0.00 sec)
- 集合函数包括
COUNT()、SUM()、AVG()、MAX()和MIN()。通常先使用GROUP BY关键字将记录分组，然后每组都使用集合函数进行计算。 在统计时经常需要使用GROUP BY关键字和集合函数。
（4）与HAVING—起使用
mysql> SELECT sex, COUNT(sex) FROM worker
-> GROUP BY sex HAVING COUNT(sex)>=5;
+-----+------------+
| sex | COUNT(sex) |
+-----+------------+
| 男 | 8 |
+-----+------------+
1 row in set (0.00 sec)
（5）按多个字段进行分组
例如先按照 d_id 字段进行分组。 遇到 d_id 字段的宿相等的情况时，再按照 name 字段进行分组：
mysql> select * from worker;
+-----+------+------------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+------------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 |
| 13 | 1001 | B.E.Wilson | 男 | 137 W San Bernardino Rd |
| 14 | 1003 | 张小强 | 男 | 翻斗大街翻斗花园一号楼5004室 |
+-----+------+------------+------+------------------------------------------+
14 rows in set (0.00 sec)
mysql> SELECT d_id, sex, GROUP_CONCAT(name) AS name FROM worker GROUP BY d_id,sex;
+------+------+----------------------+
| d_id | sex | name |
+------+------+----------------------+
| 1001 | male | A.H.James |
| 1001 | 男 | 胡图图,B.E.Wilson |
| 1002 | 男 | 李益达,章益达 |
| 1003 | 女 | 方益达,张小丽 |
| 1003 | 男 | 张小强 |
| 1004 | 男 | 张益达 |
| 1005 | 男 | 胡英俊,张益达 |
| 1006 | 女 | 佩奇,浦西 |
| 1007 | 男 | 和珅 |
+------+------+----------------------+
9 rows in set (0.00 sec)
13，限制查询结果的数量
LIMIT关键字指定查询结果从哪条记录开始显示。 还可以指定一共显示多少条记录。
LIMIT 记录数
LIMIT初始位置，记录数
- “初始位置” 参数指定从哪条记录开始显示，第一条记录是0；
- “记录数” 参数表示显示记录的条数。
mysql> SELECT * FROM worker;
+-----+------+------------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+------------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 |
| 13 | 1001 | B.E.Wilson | 男 | 137 W San Bernardino Rd |
| 14 | 1003 | 张小强 | 男 | 翻斗大街翻斗花园一号楼5004室 |
+-----+------+------------+------+------------------------------------------+
14 rows in set (0.00 sec)
mysql> SELECT * FROM worker LIMIT 4;
+-----+------+-----------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
+-----+------+-----------+------+------------------------------------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM worker LIMIT 1,4;
+-----+------+-----------+-----+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+-----------+-----+------------------------------------------+
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
+-----+------+-----------+-----+------------------------------------------+
4 rows in set (0.00 sec)
（三）使用集合函数查询
1，COUNT函数
表中的记录先通过GROUP BY关键字进行分组。 然后再统计每个分组的记录数:
mysql> SELECT * FROM student;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 张三 | 男 |
| 2 | 李四 | 男 |
| 3 | 王五 | 女 |
+----+--------+------+
3 rows in set (0.00 sec)
mysql> SELECT SEX, COUNT(*) AS total FROM student GROUP BY sex;
+------+-------+
| SEX | total |
+------+-------+
| 男 | 2 |
| 女 | 1 |
+------+-------+
2 rows in set (0.00 sec)
2，SUM函数
表中的记录先通过GROUP BY关键字进行分组。 然后再求某个字段取值的总和:
mysql> SELECT * FROM grade;
+--------+--------+-------+
| stu_id | course | score |
+--------+--------+-------+
| 1 | 数学 | 100 |
| 1 | 语文 | 90 |
| 1 | 英语 | 93 |
| 2 | 数学 | 99 |
| 2 | 语文 | 98 |
| 2 | 英语 | 93 |
| 3 | 数学 | 98 |
| 3 | 语文 | 92 |
| 3 | 英语 | 93 |
+--------+--------+-------+
9 rows in set (0.00 sec)
mysql> SELECT stu_id, SUM(score) FROM grade GROUP BY stu_id; # 查看学生总成绩
+--------+------------+
| stu_id | SUM(score) |
+--------+------------+
| 1 | 283 |
| 2 | 290 |
| 3 | 283 |
+--------+------------+
3 rows in set (0.02 sec)
3， AVG函数
表中的记录先通过GROUP BY关键字进行分组。 然后再求某个字段取值的平均值:
mysql> SELECT course, AVG(score) FROM grade GROUP BY course; # 查看学科平均成绩
+--------+------------+
| course | AVG(score) |
+--------+------------+
| 数学 | 99.0000 |
| 语文 | 93.3333 |
| 英语 | 93.0000 |
+--------+------------+
3 rows in set (0.02 sec)
mysql> SELECT stu_id,AVG(score) FROM grade GROUP BY stu_id ORDER BY AVG(score) DESC; # 按平均成绩降序排列学生
+--------+------------+
| stu_id | AVG(score) |
+--------+------------+
| 2 | 96.6667 |
| 1 | 94.3333 |
| 3 | 94.3333 |
+--------+------------+
3 rows in set (0.00 sec)
4，MAX函数与MIN函数
mysql> SELECT course, MIN(score) FROM grade GROUP BY course;
+--------+------------+
| course | MIN(score) |
+--------+------------+
| 数学 | 98 |
| 语文 | 90 |
| 英语 | 93 |
+--------+------------+
3 rows in set (0.00 sec)
mysql> SELECT course, MAX(score) FROM grade GROUP BY course;
+--------+------------+
| course | MAX(score) |
+--------+------------+
| 数学 | 100 |
| 语文 | 98 |
| 英语 | 93 |
+--------+------------+
3 rows in set (0.00 sec)
（四）连接查询
连接是关系数据库模型的主要特点，是关系数据库中最主要的查询操作，主要包括内连接、外连接等。
通过连接运算符可以实现多个表查询：在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。
1，内连接查询
内连接使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。
mysql> SELECT * FROM department;
+------+-----------+--------------------------------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1004 | 法务部 | 处理公司法务业务 | 2号楼3层 |
| 1005 | 市场部 | 负责产品推广、销售与运营 | 2号楼4层 |
| 1006 | 客服部 | 提供品售后服务 | NULL |
| 1007 | 财务部 | 负责财务结算、开支管理与工资发放 | 2号楼3层 |
+------+-----------+--------------------------------------------------+-------------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM worker;
+-----+------+------------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+------------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 |
| 13 | 1001 | B.E.Wilson | 男 | 137 W San Bernardino Rd |
| 14 | 1003 | 张小强 | 男 | 翻斗大街翻斗花园一号楼5004室 |
+-----+------+------------+------+------------------------------------------+
14 rows in set (0.00 sec)
查询结果显示， worker表和department表的 d_id 字段都是表示部门号，通过 d_id 字段可以将 worker 表和 department 表进行内连接查询：
mysql> SELECT num,name,department.d_id,d_name,d_function
-> FROM worker,department
-> WHERE worker.d_id=department.d_id;
+-----+------------+------+-----------+--------------------------------------------------+
| num | name | d_id | d_name | d_function |
+-----+------------+------+-----------+--------------------------------------------------+
| 1 | A.H.James | 1001 | 科研部 | 新产品研发 |
| 2 | 胡图图 | 1001 | 科研部 | 新产品研发 |
| 13 | B.E.Wilson | 1001 | 科研部 | 新产品研发 |
| 4 | 李益达 | 1002 | 生产部 | 管理公司生产作业 |
| 5 | 章益达 | 1002 | 生产部 | 管理公司生产作业 |
| 6 | 方益达 | 1003 | 人事部 | 管理公司人事变动 |
| 7 | 张小丽 | 1003 | 人事部 | 管理公司人事变动 |
| 14 | 张小强 | 1003 | 人事部 | 管理公司人事变动 |
| 3 | 张益达 | 1004 | 法务部 | 处理公司法务业务 |
| 8 | 胡英俊 | 1005 | 市场部 | 负责产品推广、销售与运营 |
| 12 | 张益达 | 1005 | 市场部 | 负责产品推广、销售与运营 |
| 9 | 佩奇 | 1006 | 客服部 | 提供品售后服务 |
| 10 | 浦西 | 1006 | 客服部 | 提供品售后服务 |
| 11 | 和珅 | 1007 | 财务部 | 负责财务结算、开支管理与工资发放 |
+-----+------------+------+-----------+--------------------------------------------------+
14 rows in set (0.00 sec)
- 因为worker表和department表中有相同的字段 d_id，因此在比较和查询的时候需要完全限定表名（格式为“表名.列名”）。
还有一种被称为称为自连接的查询，实际上就是将物理意义上的同一张表用作逻辑意义上不同的多张张表：
mysql> SELECT * FROM worker;
+-----+------+------------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+------------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 |
| 13 | 1001 | B.E.Wilson | 男 | 137 W San Bernardino Rd |
| 14 | 1003 | 张小强 | 男 | 翻斗大街翻斗花园一号楼5004室 |
+-----+------+------------+------+------------------------------------------+
14 rows in set (0.14 sec)
mysql> SELECT T1.num,T1.d_id,T1.name,T2.sex,T2.homeaddress
-> FROM worker AS T1,worker AS T2
-> WHERE T1.num=T2.num;
+-----+------+------------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+------------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 |
| 13 | 1001 | B.E.Wilson | 男 | 137 W San Bernardino Rd |
| 14 | 1003 | 张小强 | 男 | 翻斗大街翻斗花园一号楼5004室 |
+-----+------+------------+------+------------------------------------------+
14 rows in set (0.14 sec)
2，外连接查询
外连接也使用比较运算符进行表间某（些）列数据的比较操作，并根据关键字选择左连接还是右连接，并将相匹配的数据行组合成新的记录。
SELECT 属性名列表
FROM 表名1 LEFT | RIGHT JOIN 表名2
ON 表名1.属性名1=表名2.属性名2;
mysql> SELECT * FROM student;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 张三 | 男 |
| 2 | 李四 | 男 |
| 3 | 王五 | 女 |
| 4 | 赵六 | 女 |
+----+--------+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM grade;
+--------+--------+-------+
| stu_id | course | score |
+--------+--------+-------+
| 1 | 数学 | 100 |
| 1 | 语文 | 90 |
| 1 | 英语 | 93 |
| 2 | 数学 | 99 |
| 2 | 语文 | 98 |
| 2 | 英语 | 93 |
| 3 | 数学 | 98 |
| 3 | 语文 | 92 |
| 3 | 英语 | 93 |
| 4 | 数学 | 100 |
| 5 | 语文 | 100 |
| 6 | 英语 | 100 |
+--------+--------+-------+
12 rows in set (0.00 sec)
mysql> SELECT id,name,course,score
-> FROM student LEFT JOIN grade # 左连接
-> ON student.id=grade.stu_id;
+----+--------+--------+-------+
| id | name | course | score |
+----+--------+--------+-------+
| 1 | 张三 | 数学 | 100 |
| 1 | 张三 | 语文 | 90 |
| 1 | 张三 | 英语 | 93 |
| 2 | 李四 | 数学 | 99 |
| 2 | 李四 | 语文 | 98 |
| 2 | 李四 | 英语 | 93 |
| 3 | 王五 | 数学 | 98 |
| 3 | 王五 | 语文 | 92 |
| 3 | 王五 | 英语 | 93 |
| 4 | 赵六 | 数学 | 100 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)
mysql> SELECT id,name,course,score
-> FROM student RIGHT JOIN grade # 右连接
-> ON student.id=grade.stu_id;
+------+--------+--------+-------+
| id | name | course | score |
+------+--------+--------+-------+
| 1 | 张三 | 数学 | 100 |
| 1 | 张三 | 语文 | 90 |
| 1 | 张三 | 英语 | 93 |
| 2 | 李四 | 数学 | 99 |
| 2 | 李四 | 语文 | 98 |
| 2 | 李四 | 英语 | 93 |
| 3 | 王五 | 数学 | 98 |
| 3 | 王五 | 语文 | 92 |
| 3 | 王五 | 英语 | 93 |
| 4 | 赵六 | 数学 | 100 |
| NULL | NULL | 语文 | 100 |
| NULL | NULL | 英语 | 100 |
+------+--------+--------+-------+
12 rows in set (0.00 sec)
3，复合条件连接查询
在连接查询时， 也可以增加其他的限制条件。 通过多个条件的复合查询，
mysql> SELECT id,name,course,score
-> FROM student,grade
-> WHERE student.id=grade.stu_id AND grade.score>95;
+----+--------+--------+-------+
| id | name | course | score |
+----+--------+--------+-------+
| 1 | 张三 | 数学 | 100 |
| 2 | 李四 | 数学 | 99 |
| 2 | 李四 | 语文 | 98 |
| 3 | 王五 | 数学 | 98 |
| 4 | 赵六 | 数学 | 100 |
+----+--------+--------+-------+
5 rows in set (0.00 sec)
mysql> SELECT id,name,course,score
-> FROM student LEFT JOIN grade# 右连接
-> ON student.id=grade.stu_id AND score>95;
+----+--------+--------+-------+
| id | name | course | score |
+----+--------+--------+-------+
| 1 | 张三 | 数学 | 100 |
| 2 | 李四 | 数学 | 99 |
| 2 | 李四 | 语文 | 98 |
| 3 | 王五 | 数学 | 98 |
| 4 | 赵六 | 数学 | 100 |
+----+--------+--------+-------+
5 rows in set (0.00 sec)
（五）子查询
子查询就是嵌套在另一个查询语句中的查询语句。 内层的子查询语句的查询结果作为外层查询语句的查询条件。
- 子查询的select 语句不能使用order by子句，order by不要只能对最终查询结果排序。
1，带IN关键字的子查询
一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这可以通过IN关键字来判断：
mysql> SELECT * FROM student;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 张三 | 男 |
| 2 | 李四 | 男 |
| 3 | 王五 | 女 |
| 4 | 赵六 | 女 |
| 5 | 钱七 | 女 |
+----+--------+------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM grade;
+--------+--------+-------+
| stu_id | course | score |
+--------+--------+-------+
| 1 | 数学 | 100 |
| 1 | 语文 | 90 |
| 1 | 英语 | 93 |
| 2 | 数学 | 99 |
| 2 | 语文 | 98 |
| 2 | 英语 | 93 |
| 3 | 数学 | 98 |
| 3 | 语文 | 92 |
| 3 | 英语 | 93 |
+--------+--------+-------+
9 rows in set (0.00 sec)
# 查看数学成绩超过98的学生信息：
mysql> SELECT * FROM student
-> WHERE id IN
-> (SELECT stu_id FROM grade WHERE course='数学' AND score>98);
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 张三 | 男 |
| 2 | 李四 | 男 |
+----+--------+------+
2 rows in set (0.02 sec)
2，带比较运算符的子查询
比较运算符包括=、 !=、 ＞、 ＞=、 ＜、 ＜=和＜＞等:
mysql> SELECT * FROM standerd;
+----+-------+
| id | level |
+----+-------+
| 1 | 99 |
| 2 | 95 |
| 3 | 90 |
+----+-------+
3 rows in set (0.00 sec)
# 查询成绩达到2等的学生的成绩信息：
mysql> SELECT id,name,course,score
-> FROM student,grade
-> WHERE student.id=grade.stu_id AND score>
-> (SELECT level FROM standerd WHERE id=2);
+----+--------+--------+-------+
| id | name | course | score |
+----+--------+--------+-------+
| 1 | 张三 | 数学 | 100 |
| 2 | 李四 | 数学 | 99 |
| 2 | 李四 | 语文 | 98 |
| 3 | 王五 | 数学 | 98 |
+----+--------+--------+-------+
4 rows in set (0.00 sec)
3，带EXISTS关键字的子查询
使用EXISTS关键字时， 如果内层查询语句查询到满足条件的记录时 就返回true，则外层查询语句将进行查询；否则，外层查询语句不进行查询或者查询不出任何记录。
mysql> SELECT * FROM worker;
+-----+------+------------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+------------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 |
| 13 | 1001 | B.E.Wilson | 男 | 137 W San Bernardino Rd |
| 14 | 1003 | 张小强 | 男 | 翻斗大街翻斗花园一号楼5004室 |
+-----+------+------------+------+------------------------------------------+
14 rows in set (0.00 sec)
mysql> SELECT * FROM worker;
+-----+------+------------+------+------------------------------------------+
| num | d_id | name | sex | homeaddress |
+-----+------+------------+------+------------------------------------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 |
| 13 | 1001 | B.E.Wilson | 男 | 137 W San Bernardino Rd |
| 14 | 1003 | 张小强 | 男 | 翻斗大街翻斗花园一号楼5004室 |
+-----+------+------------+------+------------------------------------------+
14 rows in set (0.00 sec)
mysql> SELECT * FROM worker
-> WHERE EXISTS
-> (SELECT * FROM department WHERE d_id=1000);
Empty set (0.00 sec)
4，带ANY关键字的子查询
使用ANY关键字时， 只要满足内层查询语句返回的结果中的任何一个， 就可以通过该条件来执行外层查询语句。
mysql> SELECT * FROM grade;
+--------+--------+-------+
| stu_id | course | score |
+--------+--------+-------+
| 1 | 数学 | 100 |
| 1 | 语文 | 90 |
| 1 | 英语 | 93 |
| 2 | 数学 | 99 |
| 2 | 语文 | 98 |
| 2 | 英语 | 93 |
| 3 | 数学 | 98 |
| 3 | 语文 | 92 |
| 3 | 英语 | 93 |
| 4 | 数学 | 89 |
| 4 | 语文 | 89 |
+--------+--------+-------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM standerd;
+----+-------+
| id | level |
+----+-------+
| 1 | 99 |
| 2 | 95 |
| 3 | 90 |
+----+-------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM grade
-> WHERE score>=ANY
-> (SELECT level FROM standerd);
+--------+--------+-------+
| stu_id | course | score |
+--------+--------+-------+
| 1 | 数学 | 100 |
| 1 | 语文 | 90 |
| 1 | 英语 | 93 |
| 2 | 数学 | 99 |
| 2 | 语文 | 98 |
| 2 | 英语 | 93 |
| 3 | 数学 | 98 |
| 3 | 语文 | 92 |
| 3 | 英语 | 93 |
+--------+--------+-------+
9 rows in set (0.00 sec)
5，带ALL关键字的子查询
使用ALL关键字时， 只有满足内层查询语句返回的所有结果， 才可以执行外层查询语句。
mysql> SELECT id,name,course,score
-> FROM student,grade
-> WHERE id=grade.stu_id AND score>=ALL
-> (SELECT level FROM standerd WHERE id=1);
+----+--------+--------+-------+
| id | name | course | score |
+----+--------+--------+-------+
| 1 | 张三 | 数学 | 100 |
| 2 | 李四 | 数学 | 99 |
+----+--------+--------+-------+
2 rows in set (0.00 sec)
（六）合并查询结果
合并查询结果是将多个SELECT语句的查询结果合并到一起。
使用UNION关键字时， 数据库系统会将所有的查询结果合并到一起， 然后去除掉相同的记录。 而UNION ALL关键字则只是简单的合并到一起:
SELECT 语句 1
UNION | UNION ALL
SELECT 语句 2
UNION | UNION ALL ...
SELECT 语句 n ;
mysql> SELECT * FROM student
-> UNION
-> SELECT * FROM grade;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 张三 | 男 |
| 2 | 李四 | 男 |
| 3 | 王五 | 女 |
| 4 | 赵六 | 女 |
| 5 | 钱七 | 女 |
| 1 | 数学 | 100 |
| 1 | 语文 | 90 |
| 1 | 英语 | 93 |
| 2 | 数学 | 99 |
| 2 | 语文 | 98 |
| 2 | 英语 | 93 |
| 3 | 数学 | 98 |
| 3 | 语文 | 92 |
| 3 | 英语 | 93 |
| 4 | 数学 | 89 |
| 4 | 语文 | 89 |
+----+--------+------+
16 rows in set (0.00 sec)
（七）为表和字段取别名
在查询时， 可以为表和字段取一个别名。 这个别名可以代替其指定的表和字段。
1，为表取别名
表名 表的别名
mysql> SELECT * FROM grade g
-> WHERE g.score>=99;
+--------+--------+-------+
| stu_id | course | score |
+--------+--------+-------+
| 1 | 数学 | 100 |
| 2 | 数学 | 99 |
+--------+--------+-------+
2 rows in set (0.00 sec)
2，为字段取别名
属性名［AS］别名
mysql> SELECT *,d_id AS department_id FROM worker;
+-----+------+------------+------+------------------------------------------+---------------+
| num | d_id | name | sex | homeaddress | department_id |
+-----+------+------------+------+------------------------------------------+---------------+
| 1 | 1001 | A.H.James | male | proes street 386 pretoria central | 1001 |
| 2 | 1001 | 胡图图 | 男 | 翻斗大街翻斗花园二号楼1001室 | 1001 |
| 3 | 1004 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3602 | 1004 |
| 4 | 1002 | 李益达 | 男 | 杨浦区国顺东路爱情公寓3603 | 1002 |
| 5 | 1002 | 章益达 | 男 | 杨浦区国顺东路爱情公寓3601 | 1002 |
| 6 | 1003 | 方益达 | 女 | 杨浦区国顺东路爱情公寓3613 | 1003 |
| 7 | 1003 | 张小丽 | 女 | 翻斗大街翻斗花园二号楼1001室 | 1003 |
| 8 | 1005 | 胡英俊 | 男 | 翻斗大街翻斗花园二号楼1001室 | 1005 |
| 9 | 1006 | 佩奇 | 女 | 一号圆形山顶 | 1006 |
| 10 | 1006 | 浦西 | 女 | 二号圆形山顶 | 1006 |
| 11 | 1007 | 和珅 | 男 | 西城区柳荫街甲14号 | 1007 |
| 12 | 1005 | 张益达 | 男 | 杨浦区国顺东路爱情公寓3606 | 1005 |
| 13 | 1001 | B.E.Wilson | 男 | 137 W San Bernardino Rd | 1001 |
| 14 | 1003 | 张小强 | 男 | 翻斗大街翻斗花园一号楼5004室 | 1003 |
+-----+------+------------+------+------------------------------------------+---------------+
14 rows in set (0.00 sec)
mysql> SELECT d_id AS department_id FROM worker;
+---------------+
| department_id |
+---------------+
| 1001 |
| 1001 |
| 1001 |
| 1002 |
| 1002 |
| 1003 |
| 1003 |
| 1003 |
| 1004 |
| 1005 |
| 1005 |
| 1006 |
| 1006 |
| 1007 |
+---------------+
14 rows in set (0.00 sec)
九，索引
查询操作可以说是数据表中最重要的操作，为题加快查询速度，就诞生了索引机制。
（一）索引简介
1，什么是索引
索引是创建在表上的， 是对数据库表中一列或多列的值进行排序的一种结构。
索引用于快速查找具有特定列值的行数据。 如果没有索引，MySQL 必须从第一行开始，然后通读整个表以找到相关的行。
索引的优点主要如下 ：
- 通过创建唯一索引 可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的查询速度。
- 在实现数据的参考完整性方面可以加速表和表之间的连接 。
- 在使用分组和排序子旬进行数据查询时也可以显著减少查询中分组和排序的时间。
增加索引缺点主要如下 ：
- 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加 。
- 索引需要占碰盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间，如果有大量的索引 ， 索引文件可能比数据文件更快达到最大文件尺寸 。
- 当对表中的数据进行增加、删除和修改的时候索引也要动态维护，这样就降低了数据的维护速度。
2，索引的分类
（1）普通索引
在创建普通索引时， 不附加任何限制条件。 这类索引可以创建在任何数据类型中， 其值是否唯一和非空由字段本身的完整性约束条件决定。
（2）唯一性索引
使用UNIQUE参数可以设置索引为唯一性索引。 在创建唯一性索引时， 限制该索引的值必须是唯一的。 主键就是一种特殊唯一性索引
（3）全文索引
使用FULLTEXT参数可以设置索引为全文索引。 全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。 查询数据量较大的字符串类型的字段时， 使用全文索引可以提高查询速度。
（4）单列索引
在表中的单个字段上创建索引。 单列索引只根据该字段进行索引。 单列索引可以是普通索引， 也可以是唯一性索引， 还可以是全文索引。 只要保证该索引只对应一个字段即可。
（5） 多列索引
多列索引是在表的多个字段上创建一个索引。 该索引指向创建时对应的多个字段， 可以通过这几个字段进行查询。 但是， 只有查询条件中使用了这些字段中第一个字段时， 索引才会被使用。
（6）空间索引
使用SPATIAL参数可以设置索引为空间索弓I。 空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数据的效率。
3，索引的设计原则
- 选择唯一性索引。
- 为经常需要排序、 分组和联合操作的字段建立索引。
- 为常作为查询条件的字段建立索引。
- 限制索引的数目。
- 尽量使用数据量少的索引。
- 尽量使用前缀来索引。
- 删除不再使用或者很少使用的索引。
（二）创建索引
1，创建表的时候创建索引
CREATE TABLE 表名(
属性名 数据类型 [完整性约束条件],
属性名数据类型［完整性约束条件］,
...
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [别名] (属性名1[(长度)] [ASC | DESC])
);
UNIQUE为唯一性索引；FULLTEXT为全文索引；SPATIAL为空间索引；INDEX和KEY参数指定索引字段，作用是一样的；- “别名” 是可选参数，用来给创建的索引取的新名称；
- “属性1”参数指定索引对应的字段的名称， 该字段必须为前面定义好的字段； “长度” 是可选参数， 其指索引的长度， 必须是字符串类型才可以使用；
ASC和DESC都是可选参数，前者表示升序排列，后者表示降序排列。
（1）创建普通索引
实例1，创建普通索引：
mysql> CREATE TABLE index1
-> (
-> id INT,
-> name VARCHAR(20),
-> sex BOOLEAN,
-> INDEX (id) # 创建普通索引，没有额外的参数
-> );
Query OK, 0 rows affected (2.13 sec)
mysql> DESC index1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.15 sec)
mysql> SHOW CREATE TABLE index1 \G
*************************** 1. row ***************************
Table: index1
Create Table: CREATE TABLE `index1` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
KEY `id` (`id`) # 表明索引创建成功
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM indexl where id=1 \G # 查看索引能否被使用
ERROR 1146 (42S02): Table 'example.indexl' doesn't exist
mysql> EXPLAIN SELECT * FROM index1 where id=1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: index1
partitions: NULL
type: ref
possible_keys: id # 能被使用
key: id # 能被使用
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.10 sec)
（2）创建唯一性索引
使用UNIQUE参数。
实例2，创建唯一性索引：
mysql> CREATE TABLE index2
-> (
-> id INT UNIQUE,
-> name VARCHAR(20),
-> UNIQUE INDEX index2_id (id ASC)
-> );
Query OK, 0 rows affected, 1 warning (0.56 sec)
mysql> SHOW CREATE TABLE index2 \G
*************************** 1. row ***************************
Table: index2
Create Table: CREATE TABLE `index2` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
UNIQUE KEY `id` (`id`),
UNIQUE KEY `index2_id` (`id`) # 确实创建成功，但实际上又没没啥用
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
（3）创建全文索引
文索’引只能创建在CHAR、VARCHAR或TEXT类型的字段上，innodb（从MySQL5.6开始）和MylSAM存储引擎都支持全文索引。
实例3，创建全文索引：
mysql> CREATE TABLE index3
-> (
-> id INT,
-> info VARCHAR(20),
-> FULLTEXT INDEX index3_info (info)
-> );
Query OK, 0 rows affected (3.24 sec)
mysql> SHOW CREATE TABLE index3 \G
*************************** 1. row ***************************
Table: index3
Create Table: CREATE TABLE `index3` (
`id` int DEFAULT NULL,
`info` varchar(20) DEFAULT NULL,
FULLTEXT KEY `index3_info` (`info`) # 创建成功
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
（4）创建单列索引
实例4，创建单列索引：
mysql> CREATE TABLE index4
-> (
-> id INT,
-> subject VARCHAR(30),
-> INDEX index4_st (subject(10))
-> );
Query OK, 0 rows affected (0.51 sec)
mysql> SHOW CREATE TABLE index4 \G
*************************** 1. row ***************************
Table: index4
Create Table: CREATE TABLE `index4` (
`id` int DEFAULT NULL,
`subject` varchar(30) DEFAULT NULL,
KEY `index4_st` (`subject`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.10 sec)
- subject字段长度为20,而index4_st索引的长度只有10。 这样做的目的还是为了提高查询速度。 对于字符型的数据可以不用查询全部信息，而只查询其前面的若干字符信息。
（5）创建多列索引
实例5，创建多列索引：
mysql> CREATE TABLE index5
-> (
-> id INT,
-> name VARCHAR(20),
-> sex CHAR(4),
-> INDEX index5_ns ( name, sex )
-> );
Query OK, 0 rows affected (0.62 sec)
mysql> SHOW CREATE TABLE index5 \G
*************************** 1. row ***************************
Table: index5
Create Table: CREATE TABLE `index5` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` char(4) DEFAULT NULL,
KEY `index5_ns` (`name`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> EXPLAIN select * from index5 where name='DDDD' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: index5
partitions: NULL
type: ref
possible_keys: index5_ns
key: index5_ns
key_len: 63
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
- 只有查询条件中使用到name字段时， 多列索引才会被使用,因此在优化查询速度时， 可以考虑优化多列索引。
（6）创建空间索引
使用SPATIAL参数创建空间索引。
实例6，创建空间索引：
mysql> CREATE TABLE index6
-> (
-> id INT,
-> space GEOMETRY NOT NULL,
-> SPATIAL INDEX index6_sp (space)
-> );
Query OK, 0 rows affected, 1 warning (0.50 sec)
mysql> SHOW CREATE TABLE index6 \G
*************************** 1. row ***************************
Table: index6
Create Table: CREATE TABLE `index6` (
`id` int DEFAULT NULL,
`space` geometry NOT NULL,
SPATIAL KEY `index6_sp` (`space`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
2，在已经存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名(属性名 [(长度)] [ASC | DESC]);
实例7，在已经存在的表上创建普通索引：
mysql> SHOW CREATE TABLE user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`name` varchar(30) DEFAULT NULL,
`num` int NOT NULL,
`phone` varchar(20) DEFAULT NULL,
`address` varchar(30) NOT NULL,
`age` int NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> DESC user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES | | NULL | |
| num | int | NO | PRI | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(30) | NO | | NULL | |
| age | int | NO | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> CREATE INDEX index_name ON user (name);
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`name` varchar(30) DEFAULT NULL,
`num` int NOT NULL,
`phone` varchar(20) DEFAULT NULL,
`address` varchar(30) NOT NULL,
`age` int NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`num`),
KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> DESC user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES | MUL | NULL | |
| num | int | NO | PRI | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(30) | NO | | NULL | |
| age | int | NO | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
3，用ALTER TABLE语句创建索引
同样可使用ALTER TABLE语句创建索引：
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL] INDEX
索引名(属性名[(长度)] [ASC | DESC]):
实例8，用ALTER TABLE语句创建唯一性索引：
mysql> ALTER TABLE user ADD UNIQUE INDEX index_phone(phone);
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES | MUL | NULL | |
| num | int | NO | PRI | NULL | |
| phone | varchar(20) | YES | UNI | NULL | |
| address | varchar(30) | NO | | NULL | |
| age | int | NO | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
（三）查看索引
使用SHOW INDEX查看索引：
SHOW INDEX FROM table_name [FROM db_name]
SHOW INDEX FROM [db_name.]table_name
- 或建表后使用
SHOW CREATE TABLE。
实例9，查看索引：
mysql> SHOW INDEX FROM user \G
*************************** 1. row ***************************
Table: user
Non_unique: 0
Key_name: PRIMARY # 主键是默认索引
Seq_in_index: 1
Column_name: num
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: user
Non_unique: 0
Key_name: index_phone
Seq_in_index: 1
Column_name: phone
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: user
Non_unique: 1 # 唯一性索引
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.12 sec)
（四）删除索引
使用DROP删除索引：
DROP INDEX 索引名 ON 表名；
实例10，删除索引：
mysql> SHOW CREATE TABLE user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`name` varchar(30) DEFAULT NULL,
`num` int NOT NULL,
`phone` varchar(20) DEFAULT NULL,
`address` varchar(30) NOT NULL,
`age` int NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`num`),
UNIQUE KEY `index_phone` (`phone`),
KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> DROP INDEX index_name ON user;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`name` varchar(30) DEFAULT NULL,
`num` int NOT NULL,
`phone` varchar(20) DEFAULT NULL,
`address` varchar(30) NOT NULL,
`age` int NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`num`),
UNIQUE KEY `index_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> DROP INDEX index_phone ON user;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`name` varchar(30) DEFAULT NULL,
`num` int NOT NULL,
`phone` varchar(20) DEFAULT NULL,
`address` varchar(30) NOT NULL,
`age` int NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
（五）修改索引
在MySQL中并没有提供修改索引的直接指令，一般情况下，需要先删除掉原索引，再根据需要创建一个同名的索引，从而变相地实现修改索引操作。
原因是mysql在创建索引时会对字段建立关系长度等，只有删除之后创建新的索引才能创建新的关系保证索引的正确性。
十，视图
视图既能保障数据的安全性，又能提高查询效率，所以在数据库程序开发中视图被广泛使用 。
（一）视图简介
1，什么是视图
视图是一个由查询语旬定义数据内容的表，表中的数据内窑就是 SQL 查询语旬的结果集，行和列的数据均来自 SQL 查询语句中使用的数据表的一部分，所以说视图是虚拟的表。视图也能从其它视图那里获取数据。
视图一经定义便存储在数据库中，与其相对应的数据则是在引用视图时动态生成的。由于是即时引用，视图的内容总是与真实表的内容一致。
mysql> CREATE TABLE department
-> (
-> d_id INT(4) NOT NULL PRIMARY KEY,
-> d_name VARCHAR(20) NOT NULL UNIQUE,
-> d_function VARCHAR(50),
-> d_address VARCHAR(60)
-> );
Query OK, 0 rows affected, 1 warning (0.49 sec)
mysql> DESC department;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| d_id | int | NO | PRI | NULL | |
| d_name | varchar(20) | NO | UNI | NULL | |
| d_function | varchar(50) | YES | | NULL | |
| d_address | varchar(60) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE department \G
*************************** 1. row ***************************
Table: department
Create Table: CREATE TABLE `department` (
`d_id` int NOT NULL,
`d_name` varchar(20) NOT NULL,
`d_function` varchar(50) DEFAULT NULL,
`d_address` varchar(60) DEFAULT NULL,
PRIMARY KEY (`d_id`),
UNIQUE KEY `d_name` (`d_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> CREATE TABLE worker
-> (
-> num INT(10) NOT NULL PRIMARY KEY,
-> d_id INT(4),
-> name VARCHAR(20) NOT NULL,
-> sex VARCHAR(4) NOT NULL,
-> birthday DATETIME,
-> homeaddress VARCHAR(60),
-> FOREIGN KEY (d_id) REFERENCES department(d_id)
-> );
Query OK, 0 rows affected, 2 warnings (0.55 sec)
mysql> DESC worker;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| num | int | NO | PRI | NULL | |
| d_id | int | YES | MUL | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | varchar(4) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
| homeaddress | varchar(60) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE worker \G
*************************** 1. row ***************************
Table: worker
Create Table: CREATE TABLE `worker` (
`num` int NOT NULL,
`d_id` int DEFAULT NULL,
`name` varchar(20) NOT NULL,
`sex` varchar(4) NOT NULL,
`birthday` datetime DEFAULT NULL,
`homeaddress` varchar(60) DEFAULT NULL,
PRIMARY KEY (`num`),
KEY `d_id` (`d_id`),
CONSTRAINT `worker_ibfk_1` FOREIGN KEY (`d_id`) REFERENCES `department` (`d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
由于各部门的领导的权力范围不同。 因此各部门的领导只能查看该部门的员工的信息，但领导可能不关心员工的生日和家庭住址，所以可以为各部门的领导建立一个视图。 通过该视图，就只能看到本部门的员工的指定信息。
使用视图查询数据表的优势：
- 简单：操作视图和操作数据表完全是两个概念，用户不用理清数据表之间复杂的逻辑关系。而且将经常使用的 SQL 数据查询语旬定义为视图，可以有效地避免代码重复，从而减少工作量。
- 安全：用户只访问到视图给定的内容集合，这些都是数据表的某些行和列，避免用户直接操作数据表引发的一系列错误。
- 相对独立：应用程序访问是通过视图访问数据表，从而程序和数据表之间被视图分膏。 如果数据表有变化，完全不用去修改 SQL语旬，只需要调整视图的定义内容，不用调整应用程序代码。
- 能处理复杂的查询需求：可以进行问题分解，然后创建多个视图获取数据，将视图联合起来就能得到需要的结果了。
操作视图需要有CREATE VIEW权限、有查询涉及的列的SELECT权限、有查看视图的权限和删除使徒的权限。
实例1，查看用户权限：
mysql> SELECT Select_priv, Create_view_priv, Show_view_priv, Drop_priv FROM mysql.user WHERE user='root';
+-------------+------------------+----------------+-----------+
| Select_priv | Create_view_priv | Show_view_priv | Drop_priv |
+-------------+------------------+----------------+-----------+
| Y | Y | Y | Y |
+-------------+------------------+----------------+-----------+
1 row in set (0.00 sec)
（二）创建视图
使用CREATE VIEW语句创建视图：
CREATE [ALGORITHM={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW 视图名[ (属性清单) ] AS
SELECT column_name(s) FROM table_name [WITH [ CASCADED | LOCAL ] CHECK OPTION ]:
ALGORITHM是可选参数， 表示视图选择的算法。UNDEFINED:表示MySQL 将自动选择所要使用的算法；MERGE表示将视图的语旬与视图定义合并起来，使得视图定义的某一部分取代语旬的对应部分；TEMPTABLE表示将视图的结果存入临时表，然后使用临时表执行语句。- “视图名” 参数表示要创建的视图的名称； "属性清单” 是可选参数，指定视图中各个属性列表， 默认情况下与
SELECT语句中查询的属性相同。 SELECT语句参数是一个完整的查询语句，表示从某个表中查出某些满足条件的记录，将这些记录导入视图中；WITH CHECK OPTION可选参数，表示更新视图时要保证在视图的权限范围内；CASCADED表示在更新视图时满足所有相关视图和表的条件才进行更新；LOCAL表示在更新视图时满足该视图本身定义的条件即可更新 。
1，在单表上创建视图
实例2，在单表上创建并查看视图：
mysql> CREATE VIEW department_view1 AS SELECT * FROM department;
Query OK, 0 rows affected (0.10 sec)
mysql> CREATE VIEW
-> department_view2 ( name, fuction, location ) AS
-> SELECT d_name,d_function,d_address FROM department;
Query OK, 0 rows affected (0.17 sec)
- 使用视图时， 用户接触不到实际操作的表和字段，这样可以保证数据库的安全。
- 这里的SELECT语句仅仅执行单表查询。
2，在多表上创建视图
实例3，在多表上创建并查看视图：
mysql> CREATE ALGORITHM=MERGE VIEW
-> worker_view1 ( name, department,sex, age,address ) AS
-> SELECT name,d_name, sex, 2021-birthday, d_address
-> FROM worker, department WHERE worker.d_id=department.d_id
-> WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.16 sec)
- 在多表上创建视图的目的就是将多表的指定数据进行汇总。尽管只用
SELECT语句就能进行这种汇总操作，但使用视图就多了一屏蔽效果，达到一定的安全目的。
（三）查看视图
有四种方法查看视图信息：
DESCRIBE 视图名;
SHOW CREATE VIEW 视图名;
SHOW TABLE STATUS LIKE '视图名';
SELECT * FROM information_schema.views; # 在views表中查看视图详细信息
实例4，查看视图：
mysql> DESC worker_view1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| department | varchar(20) | NO | | NULL | |
| sex | varchar(4) | NO | | NULL | |
| age | bigint | YES | | NULL | |
| address | varchar(60) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> SHOW CREATE VIEW worker_view1;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| worker_view1 | CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `worker_view1` (`name`,`department`,`sex`,`age`,`address`) AS select `worker`.`name` AS `name`,`department`.`d_name` AS `d_name`,`worker`.`sex` AS `sex`,(2021 - `worker`.`birthday`) AS `2021-birthday`,`department`.`d_address` AS `d_address` from (`worker` join `department`) where (`worker`.`d_id` = `department`.`d_id`) WITH LOCAL CHECK OPTION | utf8 | utf8_general_ci |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 'worker_view1';
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| worker_view1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2021-09-29 16:20:44 | NULL | NULL | NULL | NULL | NULL | VIEW |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.11 sec)
mysql> SELECT * FROM information_schema.views;
+---------------+--------------+-----------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+---------------------+---------------+----------------------+----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION | CHECK_OPTION | IS_UPDATABLE | DEFINER | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+---------------+--------------+-----------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+---------------------+---------------+----------------------+----------------------+
| def | example | department_view1 | select `example`.`department`.`d_id` AS `d_id`,`example`.`department`.`d_name` AS `d_name`,`example`.`department`.`d_function` AS `d_function`,`example`.`department`.`d_address` AS `d_address` from `example`.`department` | NONE | YES | root@localhost | DEFINER | utf8 | utf8_general_ci |
| def | example | department_view2 | select `example`.`department`.`d_name` AS `d_name`,`example`.`department`.`d_function` AS `d_function`,`example`.`department`.`d_address` AS `d_address` from `example`.`department` | NONE | YES | root@localhost | DEFINER | utf8 | utf8_general_ci |
| def | example | worker_view1 | select `example`.`worker`.`name` AS `name`,`example`.`department`.`d_name` AS `d_name`,`example`.`worker`.`sex` AS `sex`,(2021 - `example`.`worker`.`birthday`) AS `2021-birthday`,`example`.`department`.`d_address` AS `d_address` from `example`.`worker` join `example`.`department` where (`example`.`worker`.`d_id` = `example`.`department`.`d_id`) | LOCAL | YES | root@localhost | DEFINER | utf8 | utf8_general_ci |
+---------------+--------------+-----------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+---------------------+---------------+----------------------+----------------------+
103 rows in set (0.01 sec)
（四）修改视图
修改视图是指修改数据库中已存在的表的定义。 当基本表的某些字段发生改变时， 可以通过修改视图来保持视图和基本表之间一致。
CREATE OR REPLACE [ ALGORITHM={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW 视图名[(属性清单)] AS
SELECT语句 [WITH [ CASCADED | LOCAL ] CHECK OPTION ]:
ALTER [ ALGORITHM=( UNDEFINED | MERGE | TEMPTABLE )]
VIEW 视图名[(属性清单)]AS
SELECT语句 [WITH [ CASCADED | LOCAL ] CHECK OPTION ];
实例5，修改视图：
mysql> DESC department_view1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| d_id | int | NO | | NULL | |
| d_name | varchar(20) | NO | | NULL | |
| d_function | varchar(50) | YES | | NULL | |
| d_address | varchar(60) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> CREATE OR REPLACE ALGORITHM=TEMPTABLE
-> VIEW department_view1 (department, dfunction,dlocation) AS
-> SELECT d_name, d_function, d_address FROM department;
Query OK, 0 rows affected (0.18 sec)
mysql> DESC department_view1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| department | varchar(20) | NO | | NULL | |
| dfunction | varchar(50) | YES | | NULL | |
| dlocation | varchar(60) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DESC department_view2;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| fuction | varchar(50) | YES | | NULL | |
| location | varchar(60) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER VIEW department_view2 ( department, name, sex, location ) AS
-> SELECT d_name, worker.name, worker.sex, d_address
-> FROM department, worker WHERE department.d_id=worker.d_id
-> WITH CHECK OPTION;
Query OK, 0 rows affected (0.14 sec)
mysql> DESC department_view2;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| department | varchar(20) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | varchar(4) | NO | | NULL | |
| location | varchar(60) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
（五）更新视图
更新视图是指通过视图来插入(INSERT)、 更新(UPDATE)和删除(DELETE)表中的数据。 因为视图是从数据表中抽取并组合出来的虚拟表，对视图的更新亦将更新到数据表中。
实例6，更新视图：
mysql> SELECT * FROM department; # 先查看原表的内容
+------+-----------+--------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------+-------------+
| 1001 | 人事部 | 管理公司人事变动 | 2号楼3层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
+------+-----------+--------------------------+-------------+
2 rows in set (0.00 sec)
mysql> CREATE VIEW # 创建视图
-> department_view3(dname, dfunction, daddress) AS
-> SELECT d_name,d_function,d_address FROM department WHERE d_id=1001;
Query OK, 0 rows affected (0.39 sec)
mysql> DESC department_view3; # 查看视图结构
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dname | varchar(20) | NO | | NULL | |
| dfunction | varchar(50) | YES | | NULL | |
| daddress | varchar(60) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM department_view3; # 查看视图数据内容
+-----------+--------------------------+-------------+
| dname | dfunction | daddress |
+-----------+--------------------------+-------------+
| 人事部 | 管理公司人事变动 | 2号楼3层 |
+-----------+--------------------------+-------------+
1 row in set (0.11 sec)
mysql> UPDATE department_view3 SET dname='科研部',dfunction='新产品研发',daddress='2号楼5层'; # 更新视图数据内容
Query OK, 1 row affected (0.20 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM department_view3; # 查看视图数据内容，发生变化
+-----------+-----------------+-------------+
| dname | dfunction | daddress |
+-----------+-----------------+-------------+
| 科研部 | 新产品研发 | 2号楼5层 |
+-----------+-----------------+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM department; # # 查看原表数据内容，发生变化
+------+-----------+--------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
+------+-----------+--------------------------+-------------+
2 rows in set (0.00 sec)
并不是所有的视图都可以更新的：
- 视图中包含 SUM()、COUNT()、MAX()和 MIN() 等函数.
- 视图中包含 UNION、UNION ALL、DISTINCT、GROUP BY 和 HAVIG 等关键字.
- 常量视图.
- 视图中的SELECT中包含子查询.
- 由不可更新的视图导出的视图.
- 创建视图时， ALGORITHM为TEMPTABLE类型.
- 视图对应的表上存在没有默认值的列， 而且该列没有包含在视图里.
（六）删除视图
使用DROP VIEW语句删除视图时，只能删除视图的定义，不会删除数据。
DROP VIEW [ IF EXISTS] 视图名列表 [RESTRICT | CASCADE]
实例7，删除视图：
mysql> DROP VIEW IF EXISTS department_view2,department_view3;
Query OK, 0 rows affected (0.14 sec)
mysql> SHOW TABLE STATUS LIKE 'department_view2';
Empty set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 'department_view3';
Empty set (0.00 sec)
十一，存储过程和函数
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
（一）创建存储过程和函数
创建存储过程和函数是指将经常使用的一组SQL语句的组合在一起， 并将这些SQL语句当作一个整体存储在MySQL服务器中。
1，创建存储过程
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
- sp_name参数是存储过程的名称；
- proc_parameter参数存储过程的参数列表；
- characteristic参数指定存储过程的特性；
- routine body参数是SQL代码的内容， 可以用BEGIN…END来标志SQL代码的开始和结束。
mysql> DELIMITER &&
mysql> CREATE PROCEDURE num_from_worker (IN worker_id INT, OUT count_num INT )
-> READS SQL DATA
-> BEGIN
-> SELECT COUNT(*) INTO count_num
-> FROM worker
-> WHERE d_id=worker_id;
-> END &&
Query OK, 0 rows affected (0.19 sec)
mysql> DELIMITER ;
- 存储过程名称为num_from_worker；输入变量为worker_id；输出变量为count_num；
- SELECT语句从 worker 表查询 d_id 值等于 worker_id 的记录，并用 COUNT(* )计算 d_id 值相同的记录的条数，最后将计算结果存入count_num中。
2，创建存储函数
CREATE
[DEFINER = user]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
- func_parameter表示存储函数的参数列;
- ETURNS type指定返回值的类型；
mysql> DELIMITER &&
mysql> CREATE FUNCTION name_from_worker(worker_id INT )
-> RETURNS VARCHAR(20) DETERMINISTIC
-> BEGIN
-> RETURN (SELECT name FROM worker WHERE num=worker_id);
-> END &&
Query OK, 0 rows affected (0.17 sec)
mysql> DELIMITER ;
MySQL stored procedure vs function, which would I use when?、存储过程与函数的区别与联系
3，变量的使用
在BEGIN-END程序段中可以使用DECLARE关键字来定义和使用局部变量：
DECLARE var_name(...] type [DEFAULT value] # 定义变量
SET var_name=expr[, var_name=expr]... # 变量赋值
mysql> CREATE PROCEDURE count_worker()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> SET done=(SELECT COUNT(*) FROM worker); # 或者用SELECT COUNT(*) INTO done FROM worker;
-> SELECT done; # 查看变量
-> END &&
Query OK, 0 rows affected (0.10 sec)
mysql> DELIMITER ;
mysql> CALL count_worker();
+------+
| done |
+------+
| 14 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
4，定义条件和处理程序
定义条件和处理程序允许事先定义程序执行过程中可能遇到的问题，并在处理程序中定义解决这些问题的办法：
# 定义条件 DECLARE ... CONDITION 语句
DECLARE condition_name CONDITION FOR condition_value
condition_value: {
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
}
# 定义处理程序 DECLARE ... HANDLER语句
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action: {
CONTINUE
| EXIT
| UNDO
}
condition_value: {
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
}
捕获”删除一个不存在的表“的报错，然后进行处理：
DECLARE no_such_table CONDITION FOR 1051; # 已知MySQL 错误代码“ unknown table ” 是 1051
DECLARE CONTINUE HANDLER FOR no_such_table
BEGIN
-- body of handler
END;
或者
DECLARE no_such_table CONDITION FOR SQLSTATE '42S02'; # 基于相应的 SQLSTATE 值
DECLARE CONTINUE HANDLER FOR no_such_table
BEGIN
-- body of handler
END;
5，游标的使用
查询语句可能查询出多条记录， 在存储过程和函数中最好使用游标来逐条读取查询结果集中的记录。
（1）声明游标
DECLARE cursor_name CURSOR FOR select_statement;
DECLARE cur_worker CURSOR FOR SELECT * FROM worker;
（2）打开游标
OPEN cursor_name；
（3）使用游标
FETCH cursor_name INTO var_name[,var_name...];
var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。必须在声明光标之前就定义。
（4）关闭游标
游标使用完后一定要关闭：
CLOSE cursor_name;
官方示例：
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE; # 先定义游标中要使用的局部变量
DECLARE a CHAR(16);
DECLARE b, c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; # 再定义游标
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1; # 打开游标
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO a, b; # 使用游标
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;
CLOSE cur1; # 关闭游标
CLOSE cur2;
END;
6，流程控制的使用
存储过程和函数中可以使用流程控制来控制语句的执行。
（1） IF语句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
- 如果给定的
search_condition计算结果为真，则执行相应的THEN或ELSEIF子句的 statement_list。如果没有search_condition匹配项，则执行该ELSE子句 statement_list。 - 每个statement_list 必须由一个或多个 SQL 语句组成。
mysql> DELIMITER ;
mysql> DELIMITER &&
mysql> CREATE FUNCTION SimpleCompare(n INT, m INT)
-> RETURNS VARCHAR(20) DETERMINISTIC
-> BEGIN
-> DECLARE s VARCHAR(20);
-> IF n > m THEN SET s = '>';
-> ELSEIF n = m THEN SET s = '=';
-> ELSE SET s = '<';
-> END IF;
-> SET s = CONCAT(n, ' ', s, ' ', m);
-> RETURN s;
-> END &&
Query OK, 0 rows affected (0.62 sec)
mysql> DELIMITER ;
mysql> SELECT SimpleCompare(2,4);
+--------------------+
| SimpleCompare(2,4) |
+--------------------+
| 2 < 4 |
+--------------------+
1 row in set, 1 warning (0.37 sec)
mysql> SELECT SimpleCompare(2,2);
+--------------------+
| SimpleCompare(2,2) |
+--------------------+
| 2 = 2 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SimpleCompare(2,1);
+--------------------+
| SimpleCompare(2,1) |
+--------------------+
| 2 > 1 |
+--------------------+
1 row in set (0.00 sec)
IF ... END IF块也可以嵌套在其他流控制构造中:
DELIMITER //
CREATE FUNCTION VerboseCompare (n INT, m INT)
RETURNS VARCHAR(50)
BEGIN
DECLARE s VARCHAR(50);
IF n = m THEN SET s = 'equals';
ELSE
IF n > m THEN SET s = 'greater';
ELSE SET s = 'less';
END IF;
SET s = CONCAT('is ', s, ' than');
END IF;
SET s = CONCAT(n, ' ', s, ' ', m, '.');
RETURN s;
END //
DELIMITER ;
（2）CASE语句
CASE语句可以实现比IF语句更复杂的条件判断：
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
- case_value 是一个表达式。该值与when_value每个
WHEN子句中的表达式进行比较，直到它们中的一个相等，相应的THEN子句的 statement_list 就会执行；否则，执行ELSE子句的statement_list（如果有）。
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
- 每个
WHEN子句 search_condition 表达式都会被评估，直到一个为真，此时执行其对应THEN子句的 statement_list；否则，执行ELSE子句的 statement_list（如果有）。
DELIMITER |
CREATE PROCEDURE p()
BEGIN
DECLARE v INT DEFAULT 1;
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE
BEGIN
END;
END CASE;
END;
|
（3）LEAVE和ITERATE语句
LEAVE语句用于退出给定标签的控制流程，可用于 BEGIN ... END或 循环结构 ( LOOP, REPEAT, WHILE)：
LEAVE label
ITERATE语句跳出本次循环，进入下一次循环，可用于 循环结构 ( LOOP, REPEAT, WHILE)：
ITERATE label
（4）LOOP语句
LOOP语句实现一个简单的循环，直到遇到LEAVE等语句才能停止循环：
[begin_label:] LOOP
statement_list
END LOOP [end_label]
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END;
（5）REPEAT语句
REPEAT语句是有条件控制的循环语句。当满足特定条件时， 跳出循环：
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT
SET @x = @x + 1;
UNTIL @x > p1 END REPEAT;
END
//
Query OK, 0 rows affected (0.00 sec)
mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
（6）WHILE语句
WHILE语句也是有条件控制的循环语句，当满足条件时， 执行循环内的语句：
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
END;
（二）调用存储过程和函数
执行存储过程和存储函数需要拥有EXECUTE权限。 EXECUTE权限的信息存储在information_schema数据库的USER_PRIVILEGES表中。
存储过程可通过CALL语句被调用:
mysql> delimiter //
mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
BEGIN
SELECT COUNT(*) INTO cities FROM world.city
WHERE CountryCode = country;
END//
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> CALL citycount('JPN', @cities); -- cities in Japan # 调用存储过程，确定输入与输出
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @cities; # 查看输出内容
+---------+
| @cities |
+---------+
| 248 |
+---------+
1 row in set (0.00 sec)
mysql> CALL citycount('FRA', @cities); -- cities in France
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @cities;
+---------+
| @cities |
+---------+
| 40 |
+---------+
1 row in set (0.00 sec)
存储函数的使用方法与MySQL内部函数的使用方法一样，可被SELECT语句调用：
mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
（三）查看存储过程和函数
存储过程和函数创建以后， 用户可以查看存储过程和函数的状态和定义。
# 方法一：
SHOW { PROCEDURE | FUNCTION } STATUS [LIKE ' pattern '];
# 方法二：
SHOW CREATE ( PROCEDURE | FUNCTION } sp_name ;
# 方法三：
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';
mysql> SHOW PROCEDURE STATUS LIKE 'count_worker' \G
*************************** 1. row ***************************
Db: example
Name: count_worker
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-10-12 23:54:43
Created: 2021-10-12 23:54:43
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> SHOW PROCEDURE STATUS \G #查看所有存储过程
*************************** 1. row ***************************
Db: example
Name: count_worker
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-10-12 23:54:43
Created: 2021-10-12 23:54:43
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Db: example
Name: num_from_worker
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-10-12 21:24:09
Created: 2021-10-12 21:24:09
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 3. row ***************************
Db: sys
Name: create_synonym_db
...
mysql> SHOW FUNCTION STATUS \G # 查看所有存储函数
*************************** 1. row ***************************
Db: example
Name: name__from_worker
Type: FUNCTION
Definer: root@localhost
Modified: 2021-10-12 21:40:46
Created: 2021-10-12 21:40:46
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Db: example
Name: SimpleCompare
Type: FUNCTION
Definer: root@localhost
Modified: 2021-10-14 09:15:21
Created: 2021-10-14 09:15:21
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 3. row ***************************
Db: sys
Name: extract_schema_from_file_name
...
mysql> SHOW CREATE PROCEDURE count_worker \G
*************************** 1. row ***************************
Procedure: count_worker
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `count_worker`()
BEGIN
DECLARE done INT DEFAULT 0;
SELECT COUNT(*) INTO done FROM worker;
SELECT done;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='count_worker' \G
*************************** 1. row ***************************
SPECIFIC_NAME: count_worker
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: example
ROUTINE_NAME: count_worker
ROUTINE_TYPE: PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
DECLARE done INT DEFAULT 0;
SELECT COUNT(*) INTO done FROM worker;
SELECT done;
END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: SQL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2021-10-12 23:54:43
LAST_ALTERED: 2021-10-12 23:54:43
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
（四）修改存储过程和函数
MySQL中通过ALTER PROCEDURE语句来修改存储过程：
ALTER PROCEDURE proc_name [characteristic ...]
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
COMMENT 'string'是注释信息。characteristic参数指定存储函数的特性；- 修改读写权限，
CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含SQL语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句； SQL SECURITY { DEFINER | INVOKER }修改执行权限。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。
mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE FROM information_schema.Routines WHERE ROUTINE_NAME='count_worker';
+---------------+-----------------+---------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS | SECURITY_TYPE |
+---------------+-----------------+---------------+
| count_worker | CONTAINS SQL | DEFINER |
+---------------+-----------------+---------------+
1 row in set (0.00 sec)
mysql> SHOW PROCEDURE STATUS LIKE 'count_worker' \G
*************************** 1. row ***************************
Db: example
Name: count_worker
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-10-12 23:54:43
Created: 2021-10-12 23:54:43
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> SHOW CREATE PROCEDURE count_worker \G
*************************** 1. row ***************************
Procedure: count_worker
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `count_worker`()
BEGIN
DECLARE done INT DEFAULT 0;
SELECT COUNT(*) INTO done FROM worker;
SELECT done;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> ALTER PROCEDURE count_worker
-> SQL SECURITY INVOKER;
Query OK, 0 rows affected (0.09 sec)
mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE FROM information_schema.Routines WHERE ROUTINE_NAME='count_worker';
+---------------+-----------------+---------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS | SECURITY_TYPE |
+---------------+-----------------+---------------+
| count_worker | CONTAINS SQL | INVOKER |
+---------------+-----------------+---------------+
1 row in set (0.00 sec)
mysql> SHOW PROCEDURE STATUS LIKE 'count_worker' \G
*************************** 1. row ***************************
Db: example
Name: count_worker
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-10-14 21:04:13
Created: 2021-10-12 23:54:43
Security_type: INVOKER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> SHOW CREATE PROCEDURE count_worker \G
*************************** 1. row ***************************
Procedure: count_worker
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `count_worker`()
SQL SECURITY INVOKER
BEGIN
DECLARE done INT DEFAULT 0;
SELECT COUNT(*) INTO done FROM worker;
SELECT done;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
通过ALTER FUNCTION语句来修改存储函数，主体无异。
（五）删除存储过程和函数
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
mysql> DROP PROCEDURE IF EXISTS num_from_worker;
Query OK, 0 rows affected (0.19 sec)
mysql> DROP FUNCTION IF EXISTS name__from_worker;
Query OK, 0 rows affected (0.16 sec)
十二，触发器
（一）触发器简介
触发器(TRIGGER)是指定由某事件来触发某些操作的操作，当当数据库系统执行这些事件时，就会激活触发器执行相应的操作，这样做可以保证某些操作之间的一致性。
触发器实际上就是一种特殊的存储过程。
例如，当学生表中增加了一个学生的信息时，学生的总数就必须+1。可以在这里创建一个触发器，每次增加一个学生的记录时， 就执行一次计算学生总数的操作。这样就可以保证每次增加学生的记录后，学生总数是与记录数是一致的。 触发器触发的执
行语句可能只有一个，也可能有多个。
（二）创建触发器
1，创建只有一个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件
ON 表名 FOR EACH ROW
执行语句;
BEFORE和AFTER参数指定了触发器执行的时间；- “触发事件” 参数指触发的条件，包括
INSERT,UPDATE和DELETE； - “表名"参数指触发事件操作的表的名称：
FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器；- “执行语句” 参数指触发器被触发后执行的操作。
- 为了避免触发器无限循环执行，不能对本表进行更新操作 。
- 对相同的表、相同的事件只能创建一个触发器
实例1，创建只有一个执行语句的触发器：
mysql> CREATE TABLE trigger_time # 用于保存当前时间
-> (
-> time DATETIME NOT NULL
-> );
Query OK, 0 rows affected (1.00 sec)
mysql> DESC trigger_time;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| time | datetime | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TRIGGER dept_trig1 BEFORE INSERT
-> ON department FOR EACH ROW
-> INSERT INTO trigger_time VALUES(NOW()); # 在插入数据前保存当前时间
Query OK, 0 rows affected (0.45 sec)
mysql> SHOW * FROM department;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM department' at line 1
mysql> SELECT * FROM department;
+------+-----------+--------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
+------+-----------+--------------------------+-------------+
2 rows in set (0.08 sec)
mysql> INSERT INTO department VALUES(1003, '人事部','管理公司人事变动','2号楼3层'); # 将激活触发器
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM trigger_time;
+---------------------+
| time |
+---------------------+
| 2021-10-01 15:48:52 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM department;
+------+-----------+--------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
| 1003 | 人事部 | 管理公司人事变动 | 2号楼3层 |
+------+-----------+--------------------------+-------------+
3 rows in set (0.00 sec)
2，创建有多个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE \ AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END
- MySQL默认是以
;作为结语句束执行符号。 用DELIMITER语句将;进行转化， 如 DELIMITER && 将结束符号变成 && 。 当触发器创建完成后再用 DELIMITER ; 来将结束符号还原为 ; 。
实例2，创建只有多个执行语句的触发器：
mysql> DELIMITER &&
mysql> CREATE TRIGGER dept_trig2 AFTER DELETE
-> ON department FOR EACH ROW
-> BEGIN
-> INSERT INTO trigger_time VALUES(NOW());
-> INSERT INTO trigger_time VALUES(NOW());
-> END
-> &&
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER ;
mysql> DELETE FROM department WHERE d_id=1003;
Query OK, 1 row affected (0.10 sec)
mysql> SELECT * FROM trigger_time;
+---------------------+
| time |
+---------------------+
| 2021-10-01 15:48:52 |
| 2021-10-01 16:26:37 |
| 2021-10-01 16:26:37 |
+---------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM department;
+------+-----------+--------------------------+-------------+
| d_id | d_name | d_function | d_address |
+------+-----------+--------------------------+-------------+
| 1001 | 科研部 | 新产品研发 | 2号楼5层 |
| 1002 | 生产部 | 管理公司生产作业 | 2号楼4层 |
+------+-----------+--------------------------+-------------+
2 rows in set (0.00 sec)
（三）查看触发器
有两种方法查看触发器信息：
SHOW TRIGGERS;
SELECT * FROM information_schema.triggers;
实例3，查看触发器信息：
mysql> SHOW TRIGGERS \G
*************************** 1. row ***************************
Trigger: dept_trig1
Event: INSERT
Table: department
Statement: INSERT INTO trigger_time VALUES(NOW())
Timing: BEFORE
Created: 2021-10-01 15:38:59.93
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Trigger: dept_trig2
Event: DELETE
Table: department
Statement: BEGIN
INSERT INTO trigger_time VALUES(NOW());
INSERT INTO trigger_time VALUES(NOW());
END
Timing: AFTER
Created: 2021-10-01 16:26:04.58
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
2 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.triggers \G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: sys
TRIGGER_NAME: sys_config_insert_set_user
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: sys
EVENT_OBJECT_TABLE: sys_config
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
SET NEW.set_by = USER();
END IF;
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2021-03-21 13:22:28.27
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
DEFINER: mysql.sys@localhost
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
DATABASE_COLLATION: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: sys
TRIGGER_NAME: sys_config_update_set_user
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: sys
EVENT_OBJECT_TABLE: sys_config
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
SET NEW.set_by = USER();
END IF;
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2021-03-21 13:22:28.37
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
DEFINER: mysql.sys@localhost
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
DATABASE_COLLATION: utf8mb4_0900_ai_ci
*************************** 3. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: example
TRIGGER_NAME: dept_trig1
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: example
EVENT_OBJECT_TABLE: department
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: INSERT INTO trigger_time VALUES(NOW())
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2021-10-01 15:38:59.93
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
*************************** 4. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: example
TRIGGER_NAME: dept_trig2
EVENT_MANIPULATION: DELETE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: example
EVENT_OBJECT_TABLE: department
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
INSERT INTO trigger_time VALUES(NOW());
INSERT INTO trigger_time VALUES(NOW());
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2021-10-01 16:26:04.58
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
4 rows in set (0.00 sec)
（四）删除触发器
使用DROP TRIGGER语句删除触发器：
DROP TRIGGER 触发器名；
- 需要 SUPER 权限。
- 删除一个表的同时，也会自动删除该表上的触发器。
- 触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，再重新创建。
- 如果不再需要某个触发器时， 一定要将这个触发器删除。
实例4，删除触发器：
mysql> DROP TRIGGER dept_trig1;
Query OK, 0 rows affected (0.10 sec)
mysql> DROP TRIGGER dept_trig2;
Query OK, 0 rows affected (0.22 sec)
mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='dept_trig1' \G # 查看触发器是否还存在
Empty set (0.00 sec)
mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='dept_trig2' \G
Empty set (0.00 sec)
mysql> SELECT * FROM trigger_time;
+---------------------+
| time |
+---------------------+
| 2021-10-01 15:48:52 |
| 2021-10-01 16:26:37 |
| 2021-10-01 16:26:37 |
| 2021-10-01 16:44:23 |
| 2021-10-01 16:45:19 |
| 2021-10-01 16:45:42 |
| 2021-10-01 16:47:42 |
+---------------------+
7 rows in set (0.00 sec)
mysql> INSERT INTO department VALUES(1003, '人事部','管理公司人事变动','2号楼3层');
Query OK, 1 row affected (0.20 sec)
mysql> SELECT * FROM trigger_time;
+---------------------+
| time |
+---------------------+
| 2021-10-01 15:48:52 |
| 2021-10-01 16:26:37 |
| 2021-10-01 16:26:37 |
| 2021-10-01 16:44:23 |
| 2021-10-01 16:45:19 |
| 2021-10-01 16:45:42 |
| 2021-10-01 16:47:42 |
+---------------------+
7 rows in set (0.00 sec)
1021
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
