Mysql常用命令 - 正删改查

MYSQL入门
数据库类型&约束
- 常用的数据类型
- 数据库约束
数据库基本操作
数据表基本操作
MYSQL操作表中数据
Mysql索引
- 索引是什么？为什么要使用索引？

Mysql命令

参考网站：http://c.biancheng.net/mysql/

MYSQL入门

1、Mysql安装

pass

2、Mysql服务启动

Windows 系统下启动停止 MySQL 服务的方式主要有以下两种：

1、通过计算机管理方式

在图中可以看到，服务已经启动，而且启动类型为自动。如果没有“正在运行”字样，说明 MySQL 服务未启动。

2、通过命令行方式

注意：net start mysql57和net stop mysql57命令中的 mysql57 是 MySQL 服务器名称，如果你的 MySQL 服务名称是 DB 或其它的名字，应该输入net start DB或其它名称，否则提示服务名无效。

3、Mysql连接

1、命令行连接

前置条件：

1、确认mysql数据库服务器的IP 地址；
2、确认mysql数据库服务是否开启

netstat -anptu|grep 3306

连接命令：

mysql -h数据库IP地址 -p端口号 -u用户名 -p密码

# -h 不加时，默认为本机
# -p不加时，默认为3306

2、Navacat连接

4、LINUX安装配置mysql

参考网站：http://c.biancheng.net/view/7616.html

5、my.cnf配置文件详解

Linux 操作系统中 MySQL 的配置文件是 my.cnf，一般会放在 /etc/my.cnf 或 /etc/mysql/my.cnf 目录下。总的来说，my.cnf 类似于 my.ini 配置文件。

如果你使用 rpm 包安装 MySQL 找不到 my.cnf 文件，可参考《在linux下通过rpm安装的mysql找不到my.cnf解决方法》。

my.cnf参数介绍：

[client]
port=3306
socket=/var/run/mysql/mysql.sock
[mysqldump]
quick
max_allowed_packet = 16M

以上参数会被 MySQL 客户端应用读取，参数说明如下：

- port				：MySQL 客户端连接服务器端时使用的端口号，默认为 3306
- socket			：套接字文件所在目录
- quick				：支持较大的数据库转储，导出非常巨大的表时需要此项 。
- max_allowed_packet：服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小（当与大的BLOB字段一起工作时相当必要），每个连接独立的大小，大小动态增加。

[mysqld]

user = mysql
basedir = /usr/local/mysql
datadir = /mydata/mysql/data
port=3306
server-id = 1
socket=/var/run/mysql/mysql.sock

上述参数说明如下：
- user		：mysqld 程序在启动后将在给定 UNIX/Linux 账户下执行。mysqld 必须从 root 账户启动才能在启动后切换到另一个账户下执行。mysqld_safe 脚本将默认使用 - - - - user=mysql 选项来启动 mysqld 程序。
- basedir		：指定 MySQL 安装的绝对路径；
- datadir		：指定 MySQL 数据存放的绝对路径；
- port		：服务端口号，默认为 3306
- server-id	：MySQL 服务的唯一编号，每个 MySQL 服务的 id 需唯一。
- socket		：socket 文件所在目录

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 1

key_buffer_size=16M
max_allowed_packet=8M
no-auto-rehash
sql_mode=TRADITIONAL

# 参数介绍：
- character-set-server	：数据库默认字符集，主流字符集支持一些特殊表情符号（特殊表情符占用 4 个字节）
- collation-server		：数据库字符集对应一些排序等规则，注意要和 character-- - set-server 对应
- init_connect			：设置 client 连接 mysql 时的字符集，防止乱码
- lower_case_table_names	：是否对 sql 语句大小写敏感，1 表示不敏感
- key_buffer_size			：用于指定索引缓冲区的大小
- max_allowed_packet		：设置一次消息传输的最大值
- no-auto-rehash			：仅仅允许使用键值的 UPDATES 和 DELETES
- sql_mode				：表示 SQL 模式的参数，通过这个参数可以设置检验 SQL 语句的严格程度

Mysql安装目录结构

MySQL 5.7 版本的安装配置后，目录结构可能如下图所示：

注意：MySQL 5.7 的 Data 目录和 my.ini 文件有时并不放在 MySQL 的安装目录下，而是在 C:\ProgramData\MySQL\MySQL Server 5.7 下（如上图所示）。一般情况下，C 盘下的 ProgramData 目录是隐藏的，需要取消隐藏

下面介绍 MySQL 安装目录的作用：

bin 目录
- 用于放置一些可执行文件，如 mysql.exe、mysqld.exe、mysqlshow.exe 等。
docs 目录
- 存放一些文档
Data 目录
- 1、登录数据库后，可使用 SHOW GLOBAL VARIABLES LIKE "%Datadir%"; 命令查看 Data 目录位置。
- 2、Data 目录中用于放置一些日志文件以及数据库。我们创建和保存的数据都存在这个目录里。打开 Data 目录，结构如下图所示：
include 目录
- 用于放置一些头文件，如：mysql.h、mysql_ername.h 等。
lib 目录
- 用于放置一系列库文件
share 目录
- 用于存放字符集、语言等信息
my.ini 文件
- my.ini 是 MySQL 默认使用的配置文件，一般情况下，只要修改 my.ini 配置文件中的内容就可以对 MySQL 进行配置。

数据库类型&约束

常用的数据类型

注意：MySql中的 char类型与 varchar类型，区别在于：

char类型是固定长度的：根据定义的字符串长度分配足够的空间。
varchar类型是可变长度的：只使用字符串长度所需的空间

应用场景：

数据库约束

约束的概念：

在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。
注意：约束是针对字段的

常见的约束：

约束名	约束关键字	作用
主键	primary key	数据库的唯一标识
唯一	unique	字段不允许重复
非空	not null	字段不允许填写空值：null 和 “ ”是不一样的
外键	foreign key	维护两个表之间的关联关系

主键约束

作用：某个表的唯一标识

特点：把某个字段设定为主键后，该字典的值不可重复、唯一、非空

那些字段可以作为主键？

1、通常针对业务去设计主键，往往每张表都设计一个主键
2、主键是给数据库和程序使用的，跟最终的客户无关，所以主键没有意义没有关系，，只要能保证不重复就好。
比如：身份证号字段就可以作为主键。如果没有和业务关联太大的可以设计为主键列的话，我们在进行数据库设计的时候往往认为添加一列组为主键列，习惯上起名为id,rid等

命令：

1、添加主键约束

方式1：create TABLE 表名 (字段名 字段类型 primary key);
方式2：create TABLE 表名 (PRIMARY KEY(字段名));	
方式3：ALTER TABLE 表名 ADD PRIMARY KEY(字段名);	# 对已有字段指定主键

2、删除主键约束

ALTER TABLE 表名 DROP PRIMARY KEY;
DESC 表名;

3、自增主键（INT类型）

# 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值.

CREATE TABLE 表名(字段名称 INT PRIMARY KEY AUTO_INCREMENT);
# 关键字 AUTO_INCREMENT,主键类型必须是整数类型

4、修改自增主键的起始值

CREATE TABLE 表名(
    字段名称 INT PRIMARY KEY AUTO_INCREMENT)AUTO_INCREMENT=起始值;

小栗子

需求：创建一个带主键的表

方式1：设置主键唯一非空

CREATE TABLE emp2(
-- 设置主键 唯一 非空
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1)
);

方式2：指定主键为 eid字段

CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1),
-- 指定主键为 eid字段
PRIMARY KEY(eid)
);

方式3：创建的时候不指定主键,然后通过 DDL语句进行设置

CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1)
)
-- 创建的时候不指定主键,然后通过 DDL语句进行设置
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

外键约束

外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

作用：外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

ps：

主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。
从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致

1、添加外键约束：

方式1: CREATE TABLE 表名(
    FOREIGN KEY(从表字段名) REFERENCES 主表名称(主表字段名));	# 创建表时指定外键
方式2: ALTER TABLE 表名 FOREIGN KEY(从表字段名) REFERENCES 主表名称(主表字段名);		# 对已有表添加外键约束

2、删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束字段名称;

小栗子

# 方式1：
mysql> CREATE TABLE tb_emp6
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept1
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)	# 添加外键约束
    -> );
    
# 方式2:
mysql> ALTER TABLE tb_emp2
    -> ADD CONSTRAINT fk_tb_dept1
    -> FOREIGN KEY(deptId)	# 添加外键约束
    -> REFERENCES tb_dept1(id);
    
# 删除外键
mysql> ALTER TABLE tb_emp2
    -> DROP FOREIGN KEY fk_tb_dept1;

非空约束

作用：某⼀列不允许为空

命令：

CREATE TABLE 表名(字段名 类型 NOT NULL);

小栗子

需求: 为 ename 字段添加非空约束

# 非空约束
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 添加非空约束, ename字段不能为空
ename VARCHAR(20) NOT NULL,
sex CHAR(1)
);

唯一约束

作用：表中的某一列的值不能重复( 对null不做唯一的判断 )

命令：

CREATE TABLE 表名(字段名 类型 UNIQUE);

小栗子

#创建emp3表 为ename 字段添加唯一约束
CREATE TABLE emp3(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1)
);

PS：主键约束与唯一约束的区别：

1、主键约束唯一且不能为空（主键约束=非空+唯一）
2、唯一约束数据唯一，但是可以为空
3、一个表只能由一个主键，但是可以有多个唯一约束

默认值

作用：指定某列的默认值

命令：

CREATE TABLE 表名(字段名 类型 DEFAULT '默认值');

小栗子

-- 创建带有默认值的表
CREATE TABLE emp4(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 为ename 字段添加默认值
ename VARCHAR(20) DEFAULT '女',
sex CHAR(1)
);

查看表中的约束

可以使用 SHOW CREATE TABLE 语句来查看表中的约束。

语法：

SHOW CREATE TABLE 数据表名;

数据库基本操作

SQL是什么？

SQL 是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。与其他程序设计语言（如 C语言、Java 等）不同的是，SQL 由很少的关键字组成，每个 SQL 语句通过一个或多个关键字构成。

SQL语言包含以下4个部分

1、数据定义语言（Data Definition Language，DDL）
- 用来创建或删除数据库以及表等对象，主要包含以下几种命令：
  - DROP：删除数据库和表等对象
  - CREATE：创建数据库和表等对象
  - ALTER：修改数据库和表等对象的结构
2、数据操作语言（Data Manipulation Language，DML）
- 用来变更表中的记录，主要包含以下几种命令：
  - SELECT：查询表中的数据
  - INSERT：向表中插入新数据
  - UPDATE：更新表中的数据
  - DELETE：删除表中的数据
3、数据查询语言（Data Query Language，DQL）
- 用来查询表中的记录，主要包含 SELECT 命令，来查询表中的数据。
4、数据控制语言（Data Control Language，DCL）
- 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对数据库中的用户设定权限。主要包含以下几种命令：
  - GRANT：赋予用户操作权限
  - REVOKE：取消用户的操作权限
  - COMMIT：确认对数据库中的数据进行的变更
  - ROLLBACK：取消对数据库中的数据进行的变更

基本书写规则

SQL 语句要以分号;结尾
SQL 语句不区分大小写
字符串要用引号包裹起来，列名除外
数字不需要使用引号
SQL 语句的单词之间必须使用半角空格（英文空格）或换行符来进行分隔。

MySQL查看数据库

在 MySQL 中，可使用 SHOW DATABASES 语句来查看或显示当前用户权限范围以内的数据库。

格式：SHOW DATABASES [LIKE '数据库名'];
# []：代表可选性
# LIKE： 模糊查询
# 数据库名称要用`单引号`包裹

小栗子

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| likeshop           |
| mysql              |
| performance_schema |
| sys                |	
+--------------------+
5 rows in set (0.01 sec)

可以发现，在上面的列表中有 5 个数据库，其中4个，它们都是安装 MySQL 时系统自动创建的，其各自功能如下：

information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等。
mysql：MySQL 的核心数据库，类似于 SQL Server 中的 master 表，主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。
performance_schema：主要用于收集数据库服务器性能参数。
sys：MySQL 5.7 安装完成后会多一个 sys 数据库。sys 数据库主要提供了一些视图，数据都来自于 performation_schema，主要是让开发者和使用者更方便地查看性能问题。

MySQL创建数据库

可以使用 CREATE DATABASE 语句创建数据库，语法格式如下：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

[ ]中的内容是可选的，语法说明如下：

数据库名：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
[DEFAULT] COLLATE：指定字符集的默认校对规则。

上述参数，与Navacat创建数据库时，所需参数一致。如图：

小栗子

mysql> create database houjingang;	# 创建数据库
Query OK, 1 row affected (0.56 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| houjingang         |		# 新创建的数据库
| likeshop           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

MySQL修改数据库

在 MySQL 数据库中只能对数据库使用的字符集和校对规则进行修改，数据库的这些特性都储存在 db.opt 文件中。下面我们来介绍一下修改数据库的基本操作。

在 MySQL 中，可以使用 ALTER DATABASE 来修改已经被创建或者存在的数据库的相关参数。修改数据库的语法格式为：

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

语法说明如下：

ALTER DATABASE 用于更改数据库的全局特性。
使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
数据库名称可以忽略，此时语句对应于默认数据库。
CHARACTER SET 子句用于更改默认的数据库字符集。

小栗子

mysql> show create  database houjingang;		# 查看已创建数据库得规则
+------------+------------------------------------------------------------------------+
| Database   | Create Database                                                        |
+------------+------------------------------------------------------------------------+
| houjingang | CREATE DATABASE `houjingang` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+------------+------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> alter database houjingang			# 修改字符集修改为 gb2312，默认校对规则修改为 gb2312_unicode_ci，
    -> default CHARACTER SET gb2312
    -> DEFAULT COLLATE gb2312_chinese_ci;
Query OK, 1 row affected (0.51 sec)

mysql> show create  database houjingang;
+------------+-----------------------------------------------------------------------+
| Database   | Create Database                                                       |
+------------+-----------------------------------------------------------------------+
| houjingang | CREATE DATABASE `houjingang` /*!40100 DEFAULT CHARACTER SET gb2312 */ |
+------------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL删除数据库

当需要删除已创建的数据库时，可以使用 DROP DATABASE 语句。其语法格式为：

DROP DATABASE [ IF EXISTS ] <数据库名>

语法说明如下：

<数据库名>：指定要删除的数据库名。
IF EXISTS：用于防止当数据库不存在时发生错误。
DROP DATABASE：删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心，以免错误删除。如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

小栗子

mysql> drop database houjingang;
Query OK, 0 rows affected (0.53 sec)

MySQL选择数据库

在mysql中就有很多系统自带的数据库，那么在操作数据库之前就必须要确定是哪一个数据库。

当用 CREATE DATABASE 语句创建数据库之后，该数据库不会自动成为当前数据库，需要用 USE 来指定当前数据库。其语法格式为：

USE <数据库名>

该语句可以通知 MySQL 把<数据库名>所指示的数据库作为当前数据库。该数据库保持为默认数据库，直到语段的结尾，或者直到遇见一个不同的 USE 语句。只有使用 USE 语句来指定某个数据库作为当前数据库之后，才能对该数据库及其存储的数据对象执行操作。

小栗子

mysql> use likeshop
Database changed	# 成功

Mysql注释

1、单行注释
方式1：#注释内容
方式2：-- 注释内容

#和--的区别就是：#后面直接加注释内容，而--的第 2 个破折号后需要跟一个空格符在加注释内容。

2、多行注释
/*
  第一行注释内容
  第二行注释内容
*/

小栗子

1、单行注释
SELECT DISTINCT product_id, purchase_price
-- 从结果中删除重复行。 
FROM Product;

SELECT DISTINCT product_id, purchase_price
#从结果中删除重复行。 
FROM Product;

2、多行注释
SELECT DISTINCT product_id, purchase_price
/* 这条SELECT语句，  
    会从结果中删除重复行。*/ 
    FROM Product;

数据表基本操作

创建数据表

在mysql中，可以使用 CREATE TABLE 语句创建表。其语法格式为：

create table <表名>（[表定义选项] [表选项] [分区选项]）;
表定义选项格式：
	字段名A 类型
	字段名B 类型

CREATE TABLE 语句的主要语法及使用说明如下：

CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。
<表名>：指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。例如，'mydb'.'mytbl' 是合法的，但 'mydb.mytbl' 不合法。
<表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。
默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。

提示：使用 CREATE TABLE 创建表时，必须指定以下信息：

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

小栗子

需求：

mysql> create table tb_emp1(id INT(11), 		# 创建tb_emp1表
                            name VARCHAR(25), 
                            deptld INT(11), 
                            salary FLOAT);
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;	# 查看所有的表
+----------------------+
| Tables_in_houjingang |
+----------------------+
| tb_emp1              |
+----------------------+
1 row in set (0.00 sec)

mysql> desc tb_emp1;		# 查看表结构
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptld | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

修改数据表

使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。

其语法格式如下：

ALTER TABLE <表名> [修改选项]

修改选项的语法格式如下：

# 添加字段
	add  <列名> <类型> [约束类型] [参数]
	参数介绍：
		first：在表头添加字段
# 修改字段名
	change  <旧列名> <新列名> <新列类型>
# 修改字段的数据类型：
	modify  <列名> <类型>
# 删除字段
	drop  <列名>			
# 修改表名：
	rename  <新表名>
# 修改字符集
	CHARACTER SET <字符集名>
# 修改校对规则
	COLLATE <校对规则名> 
# pass
	ALTER  <列名> { SET DEFAULT <默认值> | DROP DEFAULT }

小栗子

1、修改表名

# 修改表名
mysql> alter table tb_emp1 rename demo;	
-- 把tb_emp1表修改名称为demo
Query OK, 0 rows affected (0.34 sec)

2、修改字段的数据类型

# 修改字段的数据类型
mysql> alter table tb_emp1 MODIFY name char(25);	
-- 修改tb_emp1表中name字段的数据类型为char(25)
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、修改字段名

# 修改字段名
mysql> alter table tb_emp1 change name name1 varchar(25);	
-- 修改tb_emp1表中name字段名称为name1，并指定数据类型为varchar(25)
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

4、添加无约束条件的字段

# 添加无约束条件的字段
mysql> alter table tb_emp1 add username varchar(10);
-- 在tb_emp1表中添加username字段，并指定数据类型为varchar
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

5、添加有约束条件的字段

# 添加有约束条件的字段
mysql>  alter table tb_emp1 add password int not null;
-- 在tb_emp1tb_emp1表中，添加password字段，并指定 非空约束
表中，添加Query OK, 0 rows affected (0.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

6、添加额外的约束条件

# 添加额外的约束条件
mysql> alter table tb_emp1 add primary key(id);
# 在tb_emp1表中，设置主键约束为id字段
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

7、删除字段

# 删除字段
mysql> alter table tb_emp1 drop name1;
-- 删除tb_emp1表中的name1字段
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

8、添加外键

pass

9、删除主键

mysql> alter table tb_emp1 drop primary key;
-- 删除tb_emp1表的主键约束
Query OK, 0 rows affected (0.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

10、删除外键约束

mysql> alter table tb_emp1 drop foreign key fk;
-- 删除tb_emp1表的外键约束字段fk
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改/删除字段

修改字段

MySQL 中修改表字段名的语法规则如下：

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

参数介绍：

旧字段名：指修改前的字段名；
新字段名：指修改后的字段名；
新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

小栗子

# 修改字段名
mysql> alter table tb_emp1 change name name1 varchar(25);	
-- 修改tb_emp1表中name字段名称为name1，并指定数据类型为varchar(25)
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改字段数据类型

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

其中：

表名：指要修改数据类型的字段所在表的名称；
字段名：指需要修改的字段；
数据类型：指修改后字段的新数据类型。

小栗子

# 修改字段的数据类型
mysql> alter table tb_emp1 MODIFY name char(25);	
-- 修改tb_emp1表中name字段的数据类型为char(25)
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除字段

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

其中，“字段名”指需要从表中删除的字段的名称。

小栗子

# 删除字段
mysql> alter table tb_emp1 drop name1;
-- 删除tb_emp1表中的name1字段
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除被其他表关联的主表

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

删除父表有以下两种方法：

先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

删除数据表

使用 DROP TABLE 语句可以删除一个或多个数据表，语法格式如下：

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

对语法格式的说明如下：

表名1, 表名2, 表名3 ...表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

小栗子

mysql> drop table demo;
-- 删除demo表
Query OK, 0 rows affected (0.32 sec)

查看表结构

创建完数据表之后，经常需要查看表结构（表信息）。在 MySQL 中，可以使用 DESCRIBE 和 SHOW CREATE TABLE 命令来查看数据表的结构。

DESCRIBE：以表格的形式展示表结构

DESCRIBE/DESC 语句会以表格的形式来展示表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，语法格式如下：

desc <表名>

小栗子

mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

其中，各个字段的含义如下：

Null：表示该列是否可以存储 NULL 值。
Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，如果有，值是多少。
Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

数据表添加字段

MySQL 数据表是由行和列构成的，通常把表的“列”称为字段（Field），把表的“行”称为记录（Record）。随着业务的变化，可能需要在已有的表中添加新的字段。

MySQL 允许在开头、中间和结尾处添加字段。

在末尾添加字段(默认方式)

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件];

对语法格式的说明如下：

<表名> 为数据表的名字；
<新字段名> 为所要添加的字段的名字；
<数据类型> 为所要添加的字段能存储数据的数据类型；
[约束条件] 是可选的，用来对添加的字段进行约束。

在开头添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

在任意位置添加字段

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

MYSQL操作表中数据

MYSQL查询数据表

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

语法：

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

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

{*|<字段列名>}包含星号通配符的字段列表，表示所要查询字段的名称。
<表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。
WHERE <表达式>是可选项，如果选择该项，将限定查询数据必须满足该查询条件。
GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。
[ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。
[LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。

查询表中所以字段

查询所有字段是指查询表中所有字段的数据。MySQL 提供了以下 2 种方式查询表中的所有字段。

使用“*”通配符查询所有字段
列出表的所有字段

使用“*”查询表的所有字段

SELECT 可以使用“*”查找表中所有字段的数据，语法格式如下：

SELECT * FROM 表名;

ps：使用“*”查询时，只能按照数据表中字段的顺序进行排列，不能改变字段的排列顺序。

注意：一般情况下，除非需要使用表中所有的字段数据，否则最好不要使用通配符“”。虽然使用通配符可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。使用“”的优势是，当不知道所需列的名称时，可以通过“*”获取它们。f

查询表中指定的字段

查询表中的某一个字段的语法格式为：

SELECT < 列名 >[,<列名>,...] FROM < 表名 >;

MYSQL去重

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

DISTINCT 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据给用户。

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

其中，“字段名”为需要消除重复记录的字段名称，多个字段时用逗号隔开。

使用 DISTINCT 关键字时需要注意以下几点：

DISTINCT 关键字只能在 SELECT 语句中使用。
在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最前面。
如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下才会被去重。

MYSQL设置别名

为了查询方便，MySQL 提供了 AS 关键字来为表和字段指定别名。本节主要讲解如何为表和字段指定一个别名。

1、为数据表指定别名

<表名> [AS] <别名>

其中各子句的含义如下：
<表名>：数据库中存储的数据表的名称。
<别名>：查询时指定的表的新名称。
AS关键字可以省略，省略后需要将表名和别名用空格隔开。

注意：表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。在条件表达式中不能使用字段的别名，否则会出现“ERROR 1054 (42S22): Unknown column”这样的错误提示信息。

小栗子

select name from demo as d;	# 把表名demo 命名为 d

2、为字段指定别名

在使用 SELECT 语句查询数据时，MySQL 会显示每个 SELECT 后面指定输出的字段。有时为了显示结果更加直观，我们可以为字段指定一个别名。

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

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

<字段名>：为数据表中字段定义的名称。
<字段别名>：字段新的名称。
AS关键字可以省略，省略后需要将字段名和别名用空格隔开。

小栗子

select name as n from demo;	# 把字段name 命名为 n

等同于python 中的as

MYSQL限制查询结果的条数（分页显示）

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

LIMIT 是 MySQL 中的一个特殊关键字，用于指定查询结果从哪条记录开始显示，一共显示多少条记录。

LIMIT 关键字有 3 种使用方式，即指定初始位置、不指定初始位置以及与 OFFSET 组合使用。

1、指定初始位置

limit关键字可以指定查询结果从哪条记录开始显示，显示多少条记录。

limit指定初始位置的基本语法格式如下：

limit 初始位置(行),记录数（每页显示多少行）

其中，

“初始位置”表示从哪条记录开始显示；
“记录数”表示显示记录的条数。第一条记录的位置是 0，第二条记录的位置是 1。后面的记录依次类推。

注意：LIMIT 后的两个参数必须都是正整数。

小栗子

mysql> select id from ls_dev_region limit 5,10;	# 从第五行开始显示，每页显示10行数据
+--------+
| id     |
+--------+
| 110105 |
| 110106 |
| 110107 |
| 110108 |
| 110109 |
| 110111 |
| 110112 |
| 110113 |
| 110114 |
| 110115 |
+--------+
10 rows in set (0.00 sec)

2、不指定初始位置

LIMIT 关键字不指定初始位置时，记录从第一条记录开始显示。显示记录的条数由 LIMIT 关键字指定。

limit 每页显示行数

小栗子

mysql> select id from ls_dev_region limit 20;	# 每页显示20行数据
+--------+
| id     |
+--------+
| 100000 |
| 110000 |
| 110100 |
| 110101 |
| 110102 |
| 110105 |
| 110106 |
| 110107 |
| 110108 |
| 110109 |
| 110111 |
| 110112 |
| 110113 |
| 110114 |
| 110115 |
| 110116 |
| 110117 |
| 110118 |
| 110119 |
| 110120 |
+--------+
20 rows in set (0.00 sec)

3、limit和offset组合使用

limit 记录数 offset 初始位置

参数和 LIMIT 语法中参数含义相同，“初始位置”指定从哪条记录开始显示；“记录数”表示显示记录的条数。

ps：分页显示与limit功能重复。

MYSQL对查询结果排序

通过条件查询语句可以查询到符合用户需求的数据，但是查询到的数据一般都是按照数据最初被添加到表中的顺序来显示。为了使查询结果的顺序满足用户的要求，MySQL 提供了 ORDER BY 关键字来对查询结果进行排序。

在实际应用中经常需要对查询结果进行排序，比如，在网上购物时，可以将商品按照价格进行排序；在医院的挂号系统中，可以按照挂号的先后顺序进行排序等。

ORDER BY 关键字主要用来将查询结果中的数据按照一定的顺序进行排序。其语法格式如下：

order by <字段名> [asc|desc]

语法说明如下。

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

使用 ORDER BY 关键字应该注意以下几个方面：

ORDER BY 关键字后可以跟子查询（关于子查询后面教程会详细讲解，这里了解即可）。
当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待。
ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。

小栗子

1、单字段排序

mysql> SELECT * FROM tb_students_info ORDER BY height;

2、多字段排序

mysql> SELECT name,height FROM tb_students_info ORDER BY height,name;

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

默认情况下，查询数据按字母升序进行排序（A～Z），但数据的排序并不仅限于此，还可以使用 ORDER BY 中的 DESC 对查询结果进行降序排序（Z～A）。

3、多字段降序排序

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

MYSQL条件查询

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

where 查询条件

查询条件可以是：

带比较运算符和逻辑运算符的查询条件
带 BETWEEN AND 关键字的查询条件
带 IS NULL 关键字的查询条件
带 IN 关键字的查询条件
带 LIKE 关键字的查询条件

小栗子

1、单条件查询

在 tb_students_info 数据表中查询身高height为 170cm 的学生姓名，SQL 语句和运行结果如下。
mysql> SELECT name,height FROM tb_students_info
    -> WHERE height=170;
+-------+--------+
| name  | height |
+-------+--------+
| Susan |    170 |
+-------+--------+
1 row in set (0.17 sec)

2、多条件查询

在 WHERE 关键词后可以有多个查询条件，这样能够使查询结果更加精确。多个查询条件时用逻辑运算符 AND（&&）、OR（||）或 XOR 隔开。

AND：记录满足所有查询条件时，才会被查询出来。
OR：记录满足任意一个查询条件时，才会被查询出来。
XOR：记录满足其中一个条件，并且不满足另一个条件时，才会被查询出来。

在 tb_students_info 表中查询 age 大于 21，并且 height 大于等于 175 的学生信息，SQL 语句和运行结果如下。

mysql> SELECT name,age,height FROM tb_students_info 
    -> WHERE age>21 AND height>=175;
+--------+------+--------+
| name   | age  | height |
+--------+------+--------+
| Henry  |   23 |    185 |
| Jim    |   24 |    175 |
| Thomas |   22 |    178 |
+--------+------+--------+
3 rows in set (0.00 sec)

MYSQL LIKE模糊查询

[NOT] LIKE  '字符串'

其中：

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

LIKE 关键字支持百分号“%”和下划线“_”通配符。

通配符是一种特殊语句，主要用来模糊查询。当不知道真正字符或者懒得输入完整名称时，可以使用通配符来代替一个或多个真正的字符。

通配符汇总：

1、%：匹配任意长度的字符串，包括0.
注意：匹配的字符串必须加单引号或双引号。

2、_：匹配单个字符，长度不能为0
3、like binary ：区分大小写。默认情况不区分

下面是使用通配符的一些注意事项：

注意大小写。MySQL 默认是不区分大小写的。如果区分大小写，像“Tom”这样的数据就不能被“t%”所匹配到。
注意尾部空格，尾部空格会干扰通配符的匹配。例如，“T% ”就不能匹配到“Tom”。
注意 NULL。“%”通配符可以到匹配任意字符，但是不能匹配 NULL。也就是说 “%”匹配不到 tb_students_info 数据表中值为 NULL 的记录。
如果查询内容中包含通配符，可以使用“\”转义符。

MYSQL范围查询

BETWEEN AND 需要两个参数，即范围的起始值和终止值。如果字段值在指定的范围内，则这些记录被返回。如果不在指定范围内，则不会被返回。

[NOT] BETWEEN 取值1 AND 取值2

其中：

NOT：可选参数，表示指定范围之外的值。如果字段值不满足指定范围内的值，则这些记录被返回。
取值1：表示范围的起始值。
取值2：表示范围的终止值。

BETWEEN AND 和 NOT BETWEEN AND 关键字在查询指定范围内的记录时很有用。例如，查询学生的年龄段、出生日期，员工的工资水平等。

小栗子

在表 tb_students_info 中查询年龄在 20 到 23 之间的学生姓名和年龄，SQL 语句和运行结果如下。

mysql> SELECT name,age FROM tb_students_info 
    -> WHERE age BETWEEN 20 AND 23;
+--------+------+
| name   | age  |
+--------+------+
| Green  |   23 |
| Henry  |   23 |
| Jane   |   22 |
| John   |   21 |
| Lily   |   22 |
| Susan  |   23 |
| Thomas |   22 |
| Tom    |   23 |
+--------+------+
8 rows in set (0.00 sec)

MYSQL空值查询

作用：判断字段的值是否为空值（NULL）

IS [NOT] NULL

PS：空值不同于 0，也不同于空字符串。

小栗子

下面使用 IS NULL 关键字来查询 tb_students_info 表中 login_date 字段是 NULL 的记录。

select login_date  from tb_students_info where login_date is null;

MYSQL分组查询

作用：根据一个或多个字段对查询结果进行分组。

group by <字段名>

其中，“字段名”表示需要分组的字段名称，多个字段时用逗号隔开。

小栗子

1、group by单独使用

注意事项：查询结果只会显示每个分组的第一条记录

# 需求：下面根据 tb_students_info 表中的 sex 字段进行分组查询，SQL 语句和运行结果如下：
mysql> SELECT name,sex FROM tb_students_info 
    -> GROUP BY sex;
+-------+------+
| name  | sex  |
+-------+------+
| Henry | 女   |
| Dany  | 男   |
+-------+------+
2 rows in set (0.01 sec)

# 结果：结果中只显示了两条记录，这两条记录的 sex 字段的值分别为“女”和“男”。

2、group by与group_concat()函数一起使用

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

# 需求：下面根据 tb_students_info 表中的 sex 字段进行分组查询，使用 GROUP_CONCAT() 函数将每个分组的 name 字段的值都显示出来。SQL 语句和运行结果如下：

mysql> SELECT `sex`, GROUP_CONCAT(name) FROM tb_students_info 
    -> GROUP BY sex;
+------+----------------------------+
| sex  | GROUP_CONCAT(name)         |
+------+----------------------------+
| 女   | Henry,Jim,John,Thomas,Tom  |
| 男   | Dany,Green,Jane,Lily,Susan |
+------+----------------------------+
2 rows in set (0.00 sec)

# 由结果可以看到，查询结果分为两组，sex 字段值为“女”的是一组，值为“男”的是一组，且每组的学生姓名都显示出来了。

3、GROUP BY 与聚合函数

在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。

聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。其中，

COUNT() 用来统计记录的条数；
SUM() 用来计算字段值的总和；
AVG() 用来计算字段值的平均值；
MAX() 用来查询字段的最大值；
MIN() 用来查询字段的最小值。

需求：下面根据 tb_students_info 表的 sex 字段进行分组查询，使用 COUNT() 函数计算每一组的记录数。SQL 语句和运行结果如下：

mysql> SELECT sex,COUNT(sex) FROM tb_students_info 
    -> GROUP BY sex;
+------+------------+
| sex  | COUNT(sex) |
+------+------------+
| 女   |          5 |
| 男   |          5 |
+------+------------+
2 rows in set (0.00 sec)

结果：结果显示，sex 字段值为“女”的记录是一组，有 5 条记录；sex 字段值为“男”的记录是一组，有 5 条记录。

4、GROUP BY 与 WITH ROLLUP

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

需求：下面根据 tb_students_info 表中的 sex 字段进行分组查询，并使用 WITH ROLLUP 显示记录的总和。

mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students_info 
    ->GROUP BY sex WITH ROLLUP;
+------+------------------------------------------------------+
| sex  | GROUP_CONCAT(name)                                   |
+------+------------------------------------------------------+
| 女   | Henry,Jim,John,Thomas,Tom                            |
| 男   | Dany,Green,Jane,Lily,Susan                           |
| NULL | Henry,Jim,John,Thomas,Tom,Dany,Green,Jane,Lily,Susan |
+------+------------------------------------------------------+
3 rows in set (0.00 sec)

结果：查询结果显示，GROUP_CONCAT(name) 显示了每个分组的 name 字段值。同时，最后一条记录的 GROUP_CONCAT(name) 字段的值刚好是上面分组 name 字段值的总和。

MYSQL过滤分组

作用：对分组后的数据进行过滤。

HAVING <查询条件>

HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。

但是 WHERE 和 HAVING 关键字也存在以下几点差异：

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

小栗子

栗子1：分别使用 HAVING 和 WHERE 关键字查询出 tb_students_info 表中身高大于 150 的学生姓名，性别和身高。SQL 语句和运行结果如下。

mysql> SELECT name,sex,height FROM tb_students_info 
    -> HAVING height>150;
+--------+------+--------+
| name   | sex  | height |
+--------+------+--------+
| Dany   | 男   |    160 |
| Green  | 男   |    158 |
| Henry  | 女   |    185 |
| Jane   | 男   |    162 |
| Jim    | 女   |    175 |
| John   | 女   |    172 |
| Lily   | 男   |    165 |
| Susan  | 男   |    170 |
| Thomas | 女   |    178 |
| Tom    | 女   |    165 |
+--------+------+--------+
10 rows in set (0.00 sec)


mysql> SELECT name,sex,height FROM tb_students_info 
    -> WHERE height>150;
+--------+------+--------+
| name   | sex  | height |
+--------+------+--------+
| Dany   | 男   |    160 |
| Green  | 男   |    158 |
| Henry  | 女   |    185 |
| Jane   | 男   |    162 |
| Jim    | 女   |    175 |
| John   | 女   |    172 |
| Lily   | 男   |    165 |
| Susan  | 男   |    170 |
| Thomas | 女   |    178 |
| Tom    | 女   |    165 |
+--------+------+--------+
10 rows in set (0.00 sec)

上述实例中，因为在 SELECT 关键字后已经查询出了 height 字段，所以 HAVING 和 WHERE 都可以使用。但是如果 SELECT 关键字后没有查询出 height 字段，使用HAVING 时，MySQL 就会报错。

栗子2：

根据 height 字段对 tb_students_info 表中的数据进行分组，并使用 HAVING 和 WHERE 关键字分别查询出分组后平均身高大于 170 的学生姓名、性别和身高。SQL 语句和运行结果如下。

mysql> SELECT GROUP_CONCAT(name),sex,height FROM tb_students_info 
    -> GROUP BY height 
    -> HAVING AVG(height)>170;
+--------------------+------+--------+
| GROUP_CONCAT(name) | sex  | height |
+--------------------+------+--------+
| John               | 女   |    172 |
| Jim                | 女   |    175 |
| Thomas             | 女   |    178 |
| Henry              | 女   |    185 |
+--------------------+------+--------+
4 rows in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(name),sex,height FROM tb_students_info WHERE AVG(height)>170 GROUP BY height;
ERROR 1111 (HY000): Invalid use of group function

由结果可以看出，如果在 WHERE 查询条件中使用聚合函数，MySQL 会提示错误信息：无效使用组函数。

MYSQL交叉连接

作用：多表查询

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

语法说明如下：

字段名：需要查询的字段名称。
<表1><表2>：需要交叉连接的表名。
WHERE 子句：用来设置交叉连接的查询条件。

注意：

多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。
当连接的表之间没有关系时，我们会省略掉 WHERE 子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘。需要注意的是，如果每个表有 1000 行，那么返回结果的数量就有 1000×1000 = 1000000 行，数据量是非常巨大的。

小栗子

需求：查询 tb_course 表中的 id 字段和 tb_students_info 表中的 course_id 字段相等的内容， SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_course,tb_students_info 
    -> WHERE tb_students_info.course_id = tb_course.id;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name   | age  | sex  | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | MySQL       |  2 | Green  |   23 | 男   |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | 女   |    185 |         1 |
|  3 | Python      |  4 | Jane   |   22 | 男   |    162 |         3 |
|  2 | MySQL       |  5 | Jim    |   24 | 女   |    175 |         2 |
|  4 | Go          |  6 | John   |   21 | 女   |    172 |         4 |
|  4 | Go          |  7 | Lily   |   22 | 男   |    165 |         4 |
|  5 | C++         |  8 | Susan  |   23 | 男   |    170 |         5 |
|  5 | C++         |  9 | Thomas |   22 | 女   |    178 |         5 |
|  5 | C++         | 10 | Tom    |   23 | 女   |    165 |         5 |
+----+-------------+----+--------+------+------+--------+-----------+
10 rows in set (0.01 sec)

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

MYSQL内连接

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

内连接使用 INNER JOIN 关键字连接两张表，并使用 ON 子句来设置连接条件。如果没有连接条件，INNER JOIN 和 CROSS JOIN 在语法上是等同的，两者可以互换。

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

ps：
- 多个表内连接时，在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。
- 当对多个表进行查询时，要在 SELECT 语句后面指定字段是来源于哪一张表。因此，在多表查询时，SELECT 语句后面的写法是`表名.列名`。另外，如果表名非常长的话，也可以给表设置别名，这样就可以直接在 SELECT 语句后面写上`表的别名.列名`。

语法说明如下。

字段名：需要查询的字段名称。
<表1><表2>：需要内连接的表名。
INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
ON 子句：用来设置内连接的连接条件。

INNER JOIN 也可以使用 WHERE 子句指定连接条件，但是 INNER JOIN ... ON 语法是官方的标准写法，而且 WHERE 子句在某些时候会影响查询的性能。

小栗子

需求：在 tb_students_info 表和 tb_course 表之间，使用内连接查询学生姓名和相对应的课程名称，SQL 语句和运行结果如下。

mysql> select s.name,c.course_name from tb_students_info s join tb_course c on s.course_id = c.id;
+--------+-------------+
| name   | course_name |
+--------+-------------+
| Dany   | Java        |
| Green  | MySQL       |
| Henry  | Java        |
| Jane   | Python      |
| Jim    | MySQL       |
| John   | Go          |
| Lily   | Go          |
| Susan  | C++         |
| Thomas | C++         |
| Tom    | C++         |
+--------+-------------+
10 rows in set (0.00 sec)

注意：当对多个表进行查询时，要在 SELECT 语句后面指定字段是来源于哪一张表。因此，在多表查询时，SELECT 语句后面的写法是表名.列名。另外，如果表名非常长的话，也可以给表设置别名，这样就可以直接在 SELECT 语句后面写上表的别名.列名。

MYSQL外连接

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

外连接可以分为左外连接和右外连接

左连接

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

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

语法说明如下。

- 字段名：需要查询的字段名称。
- <表1><表2>：需要左连接的表名。
- LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
- ON 子句：用来设置左连接的连接条件，不能省略。

上述语法中，“表1”为基表，“表2”为参考表。左连接查询时，可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。如果“表1”的某行在“表2”中没有匹配行，那么在返回结果中，“表2”的字段值均为空值（NULL）。

小栗子

在进行左连接查询之前，我们先查看 tb_course 和 tb_students_info 两张表中的数据。SQL 语句和运行结果如下。

mysql> SELECT * FROM tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

############################################################

mysql> SELECT * FROM tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | Jim    |   24 | 女   |    175 |         2 |
|  6 | John   |   21 | 女   |    172 |         4 |
|  7 | Lily   |   22 | 男   |    165 |         4 |
|  8 | Susan  |   23 | 男   |    170 |         5 |
|  9 | Thomas |   22 | 女   |    178 |         5 |
| 10 | Tom    |   23 | 女   |    165 |         5 |
| 11 | LiMing |   22 | 男   |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)

在 tb_students_info 表和 tb_course 表中查询所有学生姓名和相对应的课程名称，包括没有课程的学生，SQL 语句和运行结果如下。

mysql> select s.name,c.course_name from tb_students_info t left join tb_course c on s.course_id = c.id;

+--------+-------------+
| name   | course_name |
+--------+-------------+
| Dany   | Java        |
| Henry  | Java        |
| NULL   | Java        |
| Green  | MySQL       |
| Jim    | MySQL       |
| Jane   | Python      |
| John   | Go          |
| Lily   | Go          |
| Susan  | C++         |
| Thomas | C++         |
| Tom    | C++         |
| LiMing | NULL        |
+--------+-------------+
12 rows in set (0.00 sec)


可以看到，运行结果显示了 12 条记录，name 为 LiMing 的学生目前没有课程，因为对应的 tb_course 表中没有该学生的课程信息，所以该条记录只取出了 tb_students_info 表中相应的值，而从 tb_course 表中取出的值为 NULL。

右连接

右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

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

语法说明如下。
字段名：需要查询的字段名称。
<表1><表2>：需要右连接的表名。
RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
ON 子句：用来设置右连接的连接条件，不能省略。

与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。

小栗子

在 tb_students_info 表和 tb_course 表中查询所有课程，包括没有学生的课程，SQL 语句和运行结果如下。

mysql> SELECT s.name,c.course_name FROM tb_students_info s RIGHT OUTER JOIN tb_course c 
    -> ON s.`course_id`=c.`id`;
+--------+-------------+
| name   | course_name |
+--------+-------------+
| Dany   | Java        |
| Green  | MySQL       |
| Henry  | Java        |
| Jane   | Python      |
| Jim    | MySQL       |
| John   | Go          |
| Lily   | Go          |
| Susan  | C++         |
| Thomas | C++         |
| Tom    | C++         |
| NULL   | HTML        |
+--------+-------------+
11 rows in set (0.00 sec)

可以看到，结果显示了 11 条记录，名称为 HTML 的课程目前没有学生，因为对应的 tb_students_info 表中并没有该学生的信息，所以该条记录只取出了 tb_course 表中相应的值，而从 tb_students_info 表中取出的值为 NULL。

使用外连接查询时，一定要分清需要查询的结果，是需要显示左表的全部记录还是右表的全部记录，然后选择相应的左连接和右连接。

MYSQL子查询

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

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

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

- IN | NOT IN:
	当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。
	
- EXISTS | NOT EXISTS:
	用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

小栗子

使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名，SQL 语句和运行结果如下。

mysql> SELECT name FROM tb_students_info 
    -> WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.01 sec)

习惯上，外层的 SELECT 查询称为父查询，圆括号中嵌入的查询称为子查询（子查询必须放在圆括号内）。MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询。

合并查询

应用场景：

1、一个结果集的查询的数据来自于多张表。但多张表之间没有任何关联关系
2、where 条件1 or 条件2，当2条件的查询结果有重复的行时，默认输出会去重。想要不去重，就可以使用union all

注意：两个列表中的字段要一样才可以合并(顺序也要一样)

满足条件：

1、两个select查询的列的数量必须相同；

2、每个列的数据类型需要相似；

select 语句1
UNION [ALL]
select 语句2

参数说明：
# UNION 操作符用于合并两个或多个 SELECT 语句的结果集，并消除重复行。
# UNION ALL 不会消除重复行

小栗子

select device_id, gender, age, gpa from user_profile where university='山东大学'
 
union all
 
select device_id, gender, age, gpa from user_profile where gender='male';

注：第一个select的结尾不能有 ;

MYSQL子查询注意事项

pass

MYSQL正则表达式查询

参考网站：http://c.biancheng.net/view/7511.html

MYSQL插入数据

数据库与表创建成功以后，需要向数据库的表中插入数据。在 MySQL 中可以使用 INSERT 语句向数据库已有的表中插入一行或者多行元组数据。

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

INSERT…VALUES语句

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

语法说明如下。
<表名>：指定被操作的表名。
<列名>：指定需要插入数据的列名。若向表中的所有列插入数据，则全部的列名均可以省略，直接采用 INSERT<表名>VALUES(…) 即可。
VALUES 或 VALUE 子句：该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。

INSERT…SET语句

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

此语句用于直接给表中的某些列指定对应的列值，即要插入的数据的列名在 SET 子句中指定，col_name 为指定的列名，等号后面为指定的数据，而对于未指定的列，列值会指定为该列的默认值。

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

使用 INSERT…VALUES 语句可以向表中插入一行数据，也可以插入多行数据；
使用 INSERT…SET 语句可以指定插入行中每列的值，也可以指定部分列的值；
INSERT…SELECT 语句向表中插入其他表的数据。
采用 INSERT…SET 语句可以向表中插入部分列的值，这种方式更为灵活；
INSERT…VALUES 语句可以一次插入多条数据。

在 MySQL 中，用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。

当使用单条 INSERT 语句插入多行数据的时候，只需要将每行数据用圆括号括起来即可。

向表中的全部字段添加值

在 test_db 数据库中创建一个课程信息表 tb_courses，包含课程编号 course_id、课程名称 course_name、课程学分 course_grade 和课程备注 course_info，输入的 SQL 语句和执行结果如下所示。

mysql> CREATE TABLE tb_courses
    -> (
    -> course_id INT NOT NULL AUTO_INCREMENT,
    -> course_name CHAR(40) NOT NULL,
    -> course_grade FLOAT NOT NULL,
    -> course_info CHAR(100) NULL,
    -> PRIMARY KEY(course_id)
    -> );
Query OK, 0 rows affected (0.00 sec)

向表中所有字段插入值的方法有两种：一种是指定所有字段名；另一种是完全不指定字段名。

栗子1：

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

mysql> INSERT INTO tb_courses
    -> (course_id,course_name,course_grade,course_info)
    -> VALUES(1,'Network',3,'Computer Network');
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
+-----------+-------------+--------------+------------------+
1 row in set (0.00 sec)

INSERT 语句后面的列名称顺序可以不是 tb_courses 表定义时的顺序，即插入数据时，不需要按照表定义的顺序插入，只要保证值的顺序与列字段的顺序相同就可以。

栗子2：

在 tb_courses 表中插入一条新记录，course_id 值为 2，course_name 值为“Database”，course_grade 值为 3，info值为“MySQL”。输入的 SQL 语句和执行结果如下所示。

mysql> INSERT INTO tb_courses
    -> (course_name,course_info,course_id,course_grade)
    -> VALUES('Database','MySQL',2,3);
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
+-----------+-------------+--------------+------------------+
2 rows in set (0.00 sec)

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

栗子3：

在 tb_courses 表中插入一条新记录，course_id 值为 3，course_name 值为“Java”，course_grade 值为 4，info 值为“Jave EE”。输入的 SQL 语句和执行结果如下所示。

mysql> INSERT INTO tb_courses
    -> VLAUES(3,'Java',4,'Java EE');
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
|         3 | Java        |            4 | Java EE          |
+-----------+-------------+--------------+------------------+
3 rows in set (0.00 sec)

INSERT 语句中没有指定插入列表，只有一个值列表。在这种情况下，值列表为每一个字段列指定插入的值，并且这些值的顺序必须和 tb_courses 表中字段定义的顺序相同。

注意：虽然使用 INSERT 插入数据时可以忽略插入数据的列名称，若值不包含列名称，则 VALUES 关键字后面的值不仅要求完整，而且顺序必须和表定义时列的顺序相同。如果表的结构被修改，对列进行增加、删除或者位置改变操作，这些操作将使得用这种方式插入数据时的顺序也同时改变。如果指定列名称，就不会受到表结构改变的影响。

向表中指定字段添加值

为表的指定字段插入数据，是在 INSERT 语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值

小栗子

在 tb_courses 表中插入一条新记录，course_name 值为“System”，course_grade 值为 3，course_info 值为“Operating System”，输入的 SQL 语句和执行结果如下所示。

mysql> INSERT INTO tb_courses
    -> (course_name,course_grade,course_info)
    -> VALUES('System',3,'Operation System');
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
|         3 | Java        |            4 | Java EE          |
|         4 | System      |            3 | Operating System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)

可以看到插入记录成功。如查询结果显示，这里的 course_id 字段自动添加了一个整数值 4。这时的 course_id 字段为表的主键，不能为空，系统自动为该字段插入自增的序列值。在插入记录时，如果某些字段没有指定插入值，MySQL 将插入该字段定义时的默认值。

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

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

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

小栗子

在数据库 test_db 中创建一个与 tb_courses 表结构相同的数据表 tb_courses_new，创建表的 SQL 语句和执行过程如下所示。
mysql> CREATE TABLE tb_courses_new
    -> (
    -> course_id INT NOT NULL AUTO_INCREMENT,
    -> course_name CHAR(40) NOT NULL,
    -> course_grade FLOAT NOT NULL,
    -> course_info CHAR(100) NULL,
    -> PRIMARY KEY(course_id)
    -> );
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM tb_courses_new;
Empty set (0.00 sec)

从 tb_courses 表中查询所有的记录，并将其插入 tb_courses_new 表中。输入的 SQL 语句和执行结果如下所示：

mysql> INSERT INTO tb_courses_new
    -> (course_id,course_name,course_grade,course_info)
    -> SELECT course_id,course_name,course_grade,course_info
    -> FROM tb_courses;
Query OK, 4 rows affected (0.17 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM tb_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
|         3 | Java        |            4 | Java EE          |
|         4 | System      |            3 | Operating System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)

MYSQL修改数据

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


语法说明如下：
<表名>：用于指定要更新的表名称。
SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
LIMIT 子句：可选项。用于限定被修改的行数。

注意：修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。

修改表中的数据

在 tb_courses_new 表中，更新所有行的 course_grade 字段值为 4，输入的 SQL 语句和执行结果如下所示。

mysql> UPDATE tb_courses_new
    -> SET course_grade=4;
Query OK, 3 rows affected (0.11 sec)
Rows matched: 4  Changed: 3  Warnings: 0
mysql> SELECT * FROM tb_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            4 | Computer Network |
|         2 | Database    |            4 | MySQL            |
|         3 | Java        |            4 | Java EE          |
|         4 | System      |            4 | Operating System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)

根据条件修改表中的数据

在 tb_courses 表中，更新 course_id 值为 2 的记录，将 course_grade 字段值改为 3.5，将 course_name 字段值改为“DB”，输入的 SQL 语句和执行结果如下所示。

mysql> UPDATE tb_courses_new
    -> SET course_name='DB',course_grade=3.5
    -> WHERE course_id=2;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM tb_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            4 | Computer Network |
|         2 | DB          |          3.5 | MySQL            |
|         3 | Java        |            4 | Java EE          |
|         4 | System      |            4 | Operating System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)

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

MYSQL删除数据

使用 DELETE 语句来删除表的一行或者多行数据。

删除单个表中的数据

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

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

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

删除表中的全部数据

删除 tb_courses_new 表中的全部数据，输入的 SQL 语句和执行结果如下所示。

mysql> DELETE FROM tb_courses_new;
Query OK, 3 rows affected (0.12 sec)
mysql> SELECT * FROM tb_courses_new;
Empty set (0.00 sec)

根据条件删除表中的数据

在 tb_courses_new 表中，删除 course_id 为 4 的记录，输入的 SQL 语句和执行结果如下所示。

mysql> DELETE FROM tb_courses
    -> WHERE course_id=4;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
|         3 | Java        |            4 | Java EE          |
+-----------+-------------+--------------+------------------+
3 rows in set (0.00 sec)

MYSQL清空表记录

TRUNCATE 关键字用于完全清空一个表

TRUNCATE [TABLE] 表名
其中，TABLE 关键字可省略。

小栗子

使用 TRUNCATE 语句清空 tb_student_course 表中的记录，SQL 语句和运行结果如下：

mysql> TRUNCATE TABLE tb_student_course;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM tb_student_course;
Empty set (0.00 sec)

TRUNCATE 和 DELETE 的区别

从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，但是在某些情况下，两者在使用上有所区别。

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

总结

当不需要该表时，用 DROP；当仍要保留该表，但要删除所有记录时，用 TRUNCATE；当要删除部分记录时，用 DELETE。

Mysql索引

索引是什么？为什么要使用索引？

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。本节将详细讲解索引的含义、作用和优缺点。

通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

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

因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

为什么要使用索引

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

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

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1、顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。

顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2、索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。

使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表 tb_students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表。当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

简而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

优点

索引的优点如下：

通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
可以给所有的 MySQL 列类型设置索引。
可以大大加快数据的查询速度，这是使用索引最主要的原因。
在实现数据的参考完整性方面可以加速表与表之间的连接。
在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点

增加索引也有许多不利的方面，主要如下：

创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

使用索引时，需要综合考虑索引的优点和缺点。

索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

posted @ 2023-03-29 22:50 搬砖路上的大马猴阅读(254) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· loguru 异步日志库 - 官方文档汉化

· JMeter常用操作

· mysql进阶

· MySQL基本操作

· mysql进阶

阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）

公告

昵称：搬砖路上的大马猴
园龄： 6年3个月
粉丝： 1
关注： 1

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

随笔档案

文章分类

杂七杂八(1)

阅读排行榜

评论排行榜

1. Python Loguru日志封装 - 装饰器实现(1)

struggleMan

Mysql常用命令 - 正删改查

Mysql命令

MYSQL入门

1、Mysql安装

2、Mysql服务启动

3、Mysql连接

1、命令行连接

2、Navacat连接

4、LINUX安装配置mysql

5、my.cnf配置文件详解

Mysql安装目录结构

数据库类型&约束

常用的数据类型

数据库约束

主键约束

1、添加主键约束

2、删除主键约束

3、自增主键（INT类型）

4、修改自增主键的起始值

小栗子

外键约束

1、添加外键约束：

2、删除外键约束

小栗子

非空约束

小栗子

唯一约束

小栗子

PS：主键约束与唯一约束的区别：

默认值

小栗子

查看表中的约束

数据库基本操作

SQL语言包含以下4个部分

基本书写规则

MySQL查看数据库

小栗子

MySQL创建数据库

小栗子

MySQL修改数据库

小栗子

MySQL删除数据库

小栗子

MySQL选择数据库

小栗子

Mysql注释

小栗子

数据表基本操作

创建数据表

小栗子

修改数据表

小栗子

1、修改表名

2、修改字段的数据类型

3、修改字段名

4、添加无约束条件的字段

5、添加有约束条件的字段

6、添加额外的约束条件

7、删除字段

8、添加外键

9、删除主键

10、删除外键约束

修改/删除字段

修改字段

小栗子

修改字段数据类型

小栗子

删除字段

小栗子

删除被其他表关联的主表

删除数据表

小栗子

查看表结构

DESCRIBE：以表格的形式展示表结构

小栗子

数据表添加字段

在末尾添加字段(默认方式)

在开头添加字段

在任意位置添加字段