2019-01-13发表2023-03-27更新MySQL系列 / SQL操作23 分钟读完 (大约3435个字)

MySQL系列(2)：快速了解基本的 SQL 操作

快速了解基本的 SQL 语句。

SQL(Structure Query Language - 结构化查询语言)，已是关系数据库的基本标准。

本篇涉及到的 SQL 语法可能并不完整，主要是为了快速构建一个 SQL 操作的概念。实际工作中大多会借用工具来快速操作，如SQLyog、Navicat。

连接到MySQL

1
2
3

shell> mysql -h <hostname> -P <port> -u <username> -p<password> 
shell> mysql --host=localhost --port=3306 --user=root --password=<password>
shell> mysql --host localhost --port 3306 --user root --password=<password>

-h 参数：服务器IP，主机名，可省略则默认为 localhost。
-P 参数：大写，用于指定端口，可省略默认为 3306。
-p 参数：小写，用于指定密码，参数后面没有空格。对于密码，= 后面没有空格。

强烈建议不要在命令行中输入密码，可以将系统提示你输入密码的字段保留为空：

1 2	shell> mysql --host=localhost --port=3306 --user=root --password Enter Password:

断开连接，按 Ctrl + D组合键或键入 exit：

1	mysql> exit;

分隔符结尾的命令

1	mysql> SELECT 1;

SQL 语句可以以 ;、\g 或 \G结尾。

;或\g 对应的输出水平显示，\G对应输出垂直显示。

要撤销命令，按 Ctrl + C组合键或键入 \c。

1	mysql> SELECT \c

SQL 语句类别

主要有三类：

DDL：Data Definition Language， 数据定义语句，定义数据库对象，包含 数据库、表、列、索引 等，常用操作关键字包含 create、drop、alter。
DML：Data Manipulation Language，数据操纵语句，对表中数据行、列、类型等执行 添加、删除、更新 操作，核心关键字包括 insert, update, delete。
DCL：Data Control Language，数据控制语句，定义数据库、表、用户的访问权限和安全级别，关键字包括 grant, revoke。

另外：由 select 关键字构成的DQL查询语句。

DDL 数据定义语句

数据库操作

创建数据库
1
create database [if not exists] db_name character set utf8 collate utf8_general_ci;
在创建数据库时，需要指定字符编码和字符排序规则
ci 是 case insensitive 首字母组合, 即大小写不敏感，a 和 A 在字符判断中被当做一样的。
utf8_general_ci 不区分大小写，这个在注册用户名和邮箱的时候有用。
utf8_general_cs 区分大小写
utf8_bin 字符串每个字符串用二进制数据编译存储。区分大小写，而且可以存二进制的内容
MySQL 有两个支持 unicode 的 character set：

ucs2，使用 16 bits 来表示一个 unicode 字符；
utf8，使用 1~3 bytes 来表示一个 unicode 字符。
查看数据库创建信息
1
SHOW CREATE database db_name;
列出有权限访问的所有数据库
1
SHOW DATABASES;
选择数据库
1
USE db_name;
查看当前连接的数据库
1
SELECT DATABASES();

修改数据库

1	ALTER database db_name character set utf8 ;

删除数据库
1
drop database [if exists] db_name;

数据库被创建为数据目录中的一个目录。可以通过执行以下命令获知当前的数据目录。

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| datadir       | D:\Program Files\mysql-8.0.23-winx64\data\ |
+---------------+--------------------------------------------+
1 row in set (0.50 sec)

表操作

创建表

CREATE TABLE IF NOT EXISTS `sakila`.`actor` (
  `id` long unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建了表，MySQL会在数据目录创建 .ibd 文件。

查看所有存储引擎

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

克隆表结构到新表

1	mysql> CREATE TABLE new_table LIKE original_table;

查看表创建信息
1
show create table tb_name;
查看表信息
1
DESC tb_name;
列出所有表
1
show tables;
选中表
1
USE tb_name;

重命名表

ALTER table tb_name rename [to] new_tb_name; //mysql特有
示例：
ALTER TABLE stu RENAME student;
ALTER TABLE stu RENAME TO student;

删除表
1
DROP table tb_name;
重建表(先清空,再重建表结构)，数据不能被回滚

1	TRUNCATE TABLE tb_name;

字段操作

增加字段，默认在表尾增加

1
2
3

alter table tb_name add(column1_name column1_type(length) default 'value' after column_name, column2_name...);
-- 示例：
ALTER TABLE user ADD(address VARCHAR(100) DEFAULT NULL);

ALTER COLUMN：字段设置或删除默认值

alter table tb_name alter column column_name set default 'value'; 
-- 示例：
ALTER TABLE student ALTER COLUMN home_address SET DEFAULT 'ShenZhen'; 
ALTER TABLE student ALTER COLUMN home_address DROP DEFAULT;

CHANGE COLUMN：字段重命名、修改列类型、移动列位置

1
2
3

alter table tb_name change old_column new_column column_type(length) after column_name;
-- 示例：
ALTER TABLE student CHANGE home_address address VARCHAR(100) AFTER age;

MODIFY COLUMN：除了不能修改列名，其它与 CHANGE COLUMN一样操作

1
2
3

alter table tb_name modify column_name column_type(length) not/default 'value' after/before column_name;
-- 示例：
ALTER TABLE stu MODIFY address VARCHAR(200);

DROP COLUMN：删除表字段

1
2
3

alter table tb_name drop column_name
-- 示例：
ALTER TABLE student DROP address;

FIRST|AFTER：修改字段顺序
- FIRST：放在表最前。
- AFTER COLUMN_NAME：放在指定字段后面

DML 数据操作语句

DML 数据操纵语句，是开发日常使用 SQL 语句，主要通过insert、update、delete关键字对表数据进行操作。

INSERT 插入数据

1
2
3

INSERT INTO tb_name (field1,field1,field1) values(value1,value2,value3);

INSERT IGNORE INTO tb_name (field1,field1,field1) values(value1,value2,value3);

IGNORE：如果该行已存在，并给出了 IGNORE 子句，则新数据将被忽略，INSERT 语句仍会执行成功，同时生成一个警告和重复数据的数目。行的唯一性由主键标识。

mysql> INSERT INTO `sakila`.`actor`(`actor_id`, `first_name`, `last_name`, `last_update`) VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33');
1062 - Duplicate entry '1' for key 'actor.PRIMARY'
mysql> INSERT IGNORE INTO `sakila`.`actor`(`actor_id`, `first_name`, `last_name`, `last_update`) VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'actor.PRIMARY' |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

插入操作，若不写 字段名，则必须写所有列的值。可空字段、非空但含默认值字段、自增字段可不在字段列表中出现。
一条 insert into 语句可插入多条数据：

1	INSERT INTO tb_name (field1,field1,field1) values(value1,value2,value3)，(value1,value2,value3)，(value1,value2,value3)

UPDATE 更新数据行

1	UPDATE tb_name SET column_name=value[,column_name=value] [WHERE condition ...];

DELETE 删除数据行

1	DELETE FROM tb_name [WHERE condition ...]

WHERE：用于过滤子句，在 WHERE 子句后指定的任何条件都会用于过滤，被筛选出来的行都会被更新或被删除。没用指定 WHERE 子句，则会更新或删除表所有数据行，谨慎操作。

REPLACE 重复删除插入

在很多情况下，需要处理重复项。行的唯一性由主键或唯一索引标识（Duplicate Key）。如果行已存在，则 REPLACE 会简单地删除行并插入新行；如果行不存在，则 REPLACE 等同于 INSERT。

-- 不存在行执行，插入，影响一行
mysql> REPLACE INTO pay_order(id, `name`, amount, order_id) VALUES(1, 'Tom',23.23, '2023030300001') ;
Query OK, 1 row affected (0.10 sec)

-- 已存在行执行重复SQL，更新，影响一行
mysql> REPLACE INTO pay_order(id, `name`, amount, order_id) VALUES(1, 'Tom',23.23, '2023030300001') ;
Query OK, 1 row affected (0.06 sec)

-- 已存在执行，改了字段值，有两行受影响，一个重复行被删除，一个新行被插入
mysql> REPLACE INTO pay_order(id, `name`, amount, order_id) VALUES(1, 'Tom',24.23, '2023030300001') ;
Query OK, 2 rows affected (0.08 sec)

可以看到第三次执行时改了金额，有两行受到影响，一个重复行被删除，一个新行被插入。

如果想在已经存在行的情况下处理重复项，则需要使用 ON DUPLICATE KEY UPDATE。如果指定是了 ON DUPLICATE KEY UPDATE选项，并且 INSERT 语句在 PRIMARY KEY 或 UNIQUE KEY 中引发重复值，则 MySQL 会用新值更新已有行。

例如，支付订单表，订单多次支付，不存在则插入，存在时则金额相加。使用主键作为唯一标识，行数据存在时，执行UPDATE子句。

mysql> REPLACE INTO pay_order(id, `name`, amount, order_id) VALUES(1, 'Tom',22.23, '2023030300001') ;
Query OK, 2 rows affected (0.10 sec)

mysql> SELECT * FROM pay_order WHERE id = 1;
+----+------+--------+---------------+
| id | name | amount | order_id      |
+----+------+--------+---------------+
|  1 | Tom  |  22.23 | 2023030300001 |
+----+------+--------+---------------+
1 row in set (0.00 sec)

mysql> INSERT INTO pay_order(id, `name`, amount, order_id) VALUES(1, 'Tom',10.23, '2023030300001') ON DUPLICATE KEY UPDATE amount=amount+VALUES(amount);
Query OK, 2 rows affected, 1 warning (0.40 sec)

mysql> SELECT * FROM pay_order WHERE id = 1;
+----+------+--------+---------------+
| id | name | amount | order_id      |
+----+------+--------+---------------+
|  1 | Tom  |  32.46 | 2023030300001 |
+----+------+--------+---------------+
1 row in set (0.00 sec)

例如，支付订单表，订单多次支付，不存在则插入，存在时则金额相加。创建订单号为唯一索引，行数据存在时，执行UPDATE子句。

mysql> REPLACE INTO pay_order(`name`, amount, order_id) VALUES('Tom',22.23, '2023030300001');
Query OK, 1 row affected (0.13 sec)

mysql> SELECT * FROM pay_order WHERE id = 1;
+----+------+--------+---------------+
| id | name | amount | order_id      |
+----+------+--------+---------------+
|  1 | Tom  |  22.23 | 2023030300001 |
+----+------+--------+---------------+
1 row in set (0.00 sec)

mysql> INSERT INTO pay_order(`name`, amount, order_id) VALUES('Tom',10, '2023030300001') ON DUPLICATE KEY UPDATE amount=amount+VALUES(amount);
Query OK, 2 rows affected, 1 warning (0.46 sec)

mysql> SELECT * FROM pay_order WHERE id = 1;
+----+------+--------+---------------+
| id | name | amount | order_id      |
+----+------+--------+---------------+
|  1 | Tom  |  32.23 | 2023030300001 |
+----+------+--------+---------------+
1 row in set (0.00 sec)

DQL 数据查询语句

SELECT 查询

-- * 查所有列
SELECT * FROM tb_name;

-- 查指定的列
SELECT id, first_name, second_name FROM actor;

DISTINCT 去重

1	SELECT DISTINCT column_name from tb_name

COUNT(*) 计数

-- 统计全部
SELECT COUNT(*) FROM actor;

-- 分组统计
SELECT COUNT(1) AS total, category_id FROM film_category GROUP BY category_id;

WHERE 条件过滤

WHERE condition：查匹配条件的数据行

条件操作符：=、>、<、>=、<=、!=、<>、IS NULL、IS NOT NULL、IN、NOT IN、BETWEEN...AND...、NOT BETWEEN...AND...

1	select * from tb_name where condition ...

简单模式匹配：LIKE 运算符实现简单模式匹配。使用下画线（_）来精准匹配一个字符，使用（%）来匹配任意数量的字符。

-- 左前缀匹配
mysql> SELECT * FROM actor WHERE first_name LIKE 'JE%';
+----------+------------+-------------+---------------------+
| actor_id | first_name | last_name   | last_update         |
+----------+------------+-------------+---------------------+
|        4 | JENNIFER   | DAVIS       | 2006-02-15 04:34:33 |
|       67 | JESSICA    | BAILEY      | 2006-02-15 04:34:33 |
|      180 | JEFF       | SILVERSTONE | 2006-02-15 04:34:33 |
+----------+------------+-------------+---------------------+
3 rows in set (0.03 sec)

-- 右后缀匹配
mysql> SELECT * FROM actor WHERE first_name LIKE '%FER';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        4 | JENNIFER   | DAVIS     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.04 sec)

-- 指定前缀和后缀匹配
mysql> SELECT * FROM actor WHERE first_name LIKE 'JE%FER';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        4 | JENNIFER   | DAVIS     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.04 sec)

-- 包含匹配
mysql> SELECT * FROM actor WHERE first_name LIKE '%SSI%';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       67 | JESSICA    | BAILEY    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.04 sec)

-- 指定任意两个字符开头，后面跟随 NN，再以 TH 结尾的数据行
mysql> SELECT * FROM actor WHERE first_name LIKE '__NN%TH';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       69 | KENNETH    | PALTROW   | 2006-02-15 04:34:33 |
|       88 | KENNETH    | PESCI     | 2006-02-15 04:34:33 |
|       94 | KENNETH    | TORN      | 2006-02-15 04:34:33 |
|      169 | KENNETH    | HOFFMAN   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

正则表达式：可以利用 RELIKE 或 REGEXP，NOT REGEXP 运算符在 WHERE 子句中使用正则表达式。

表达式	描述	示例
*	零次或多次重复
+	一个或多个重复
?	可选字符
.	任何字符
\.	区间
^	以…..开始	SELECT * FROM actor WHERE first_name REGEXP ‘^JEN’;
$	以…..结束	SELECT * FROM actor WHERE first_name REGEXP ‘TH$’;
[abc]	只有工，b 或 c
[^abc]	非 a, 非 b, 亦非 c	SELECT * FROM actor WHERE first_name NOT REGEXP ‘[AEFNOUMP]’;
[a-z]	字符 a 到 z
[0-9]	数字 0 到 9
^…$	开始和结束
\d	任何数据
\D	任何非数字字符
\s	任何空格
\S	任何非空白字符
\w	任何字母数字字符
\W	任何非字母数字字符
{m}	m 次重复
{m,n}	m 到 n次重复

ORDER BY 排序

1
2
3

select `*` from tb_name [where condition] [order by column_name [asc|desc],order by column_name [asc|desc]]
-- 示例：若第一个字段相同，则按第二个字段排序，依次类推
SELECT * FROM actor ORDER BY first_name ASC, last_name DESC;

ASC指定升序，DESC指定降序。

ORDER BY 除了可以指定列名外，还可以指定 SELECT 语句中提及列的位置。

mysql> SELECT customer_id, amount FROM payment ORDER BY 2 DESC LIMIT 5;
+-------------+--------+
| customer_id | amount |
+-------------+--------+
|         196 | 11.99  |
|         195 | 11.99  |
|         116 | 11.99  |
|          13 | 11.99  |
|         204 | 11.99  |
+-------------+--------+
5 rows in set (0.05 sec)

LIMIT 限定行数

select `*` from tb_name [limit offset_start, row_count]
-- **offset_start：**起始偏移量，即从第几行数据开始查询，默认为 **0**
-- **row_count：**指定要查询的行数
-- 示例：
SELECT `*` FROM actor ORDER BY first_name ASC, last_name DESC LIMIT 3,3;

GROUP BY 分组

GROUP BY：分组查询，默认输出分组后 id 为最小的数据

1
2
3

select `*` from tb_name group by column_name[,column_name]
-- 示例：
SELECT `*` FROM film GROUP BY type;

HAVING：对分组的结果再条件过滤，一般和聚合函数配合使用。

1	select `*` tbu_user group by phone_number having count(phone_number) > 1;

注：where 是在聚合前对记录进行过滤，通过先用 where 来缩小结果集，再根据逻辑用** having **再过滤。

DCL:数据控制语句

DCL 语句主要用于管理数据库系统中的对象的权象，开发较少使用。

GRANT：授予用户访问权限

1
2

GRANT SELECT ON `*.*` TO 'user_name'@'%'; 
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON 'db_name'.* TO 'user_name'@'%' WITH GRANT OPTION;

REVOKE： 解除用户访问权限

1	REVOKE ALTER ON db_name.* FROM 'user_name'@'%';

DENY：拒绝用户访问

MySQL系列(2)：快速了解基本的 SQL 操作

连接到MySQL

SQL 语句类别

DDL 数据定义语句

数据库操作

表操作

字段操作

DML 数据操作语句

INSERT 插入数据

UPDATE 更新数据行

DELETE 删除数据行

REPLACE 重复删除插入

DQL 数据查询语句

SELECT 查询

DISTINCT 去重

COUNT(*) 计数

WHERE 条件过滤

ORDER BY 排序

LIMIT 限定行数

GROUP BY 分组

DCL:数据控制语句

相关参考

作者

发布于

更新于

许可协议

喜欢这篇文章？打赏一下作者吧

评论

目录

MySQL系列(2)： 快速了解基本的 SQL 操作

连接到MySQL

SQL 语句类别

DDL 数据定义语句

数据库操作

表操作

字段操作

DML 数据操作语句

INSERT 插入数据

UPDATE 更新数据行

DELETE 删除数据行

REPLACE 重复删除插入

DQL 数据查询语句

SELECT 查询

DISTINCT 去重

COUNT(*) 计数

WHERE 条件过滤

ORDER BY 排序

LIMIT 限定行数

GROUP BY 分组

DCL:数据控制语句

相关参考

作者

发布于

更新于

许可协议

喜欢这篇文章？打赏一下作者吧

评论

目录

MySQL系列(2)：快速了解基本的 SQL 操作