MySQL系列(2): 快速了解基本的 SQL 操作
快速了解基本的 SQL 语句。
SQL(Structure Query Language - 结构化查询语言),已是关系数据库的基本标准。
本篇涉及到的 SQL 语法可能并不完整,主要是为了快速构建一个 SQL 操作的概念。实际工作中大多会借用工具来快速操作,如SQLyog、Navicat。
连接到MySQL
登录 MySQL:安装目录 \bin 里包含 MySQL 命令行客户端,可以使用以下命令中任一条来连接到 MySQL服务。
1 | shell> mysql -h <hostname> -P <port> -u <username> -p<password> |
-h 参数:服务器IP,主机名,可省略则默认为 localhost。
-P 参数:大写,用于指定端口,可省略默认为 3306。
-p 参数:小写,用于指定密码,参数后面没有空格。对于密码,= 后面没有空格。
强烈建议不要在命令行中输入密码,可以将系统提示你输入密码的字段保留为空:
1 | shell> mysql --host=localhost --port=3306 --user=root --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;
数据库被创建为数据目录中的一个目录。可以通过执行以下命令获知当前的数据目录。
1
2
3
4
5
6
7mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| datadir | D:\Program Files\mysql-8.0.23-winx64\data\ |
+---------------+--------------------------------------------+
1 row in set (0.50 sec)
表操作
创建表
1
2
3
4
5
6
7
8CREATE 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
文件。查看所有存储引擎
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65mysql> 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;
重命名表
1
2
3
4ALTER 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
3alter 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:字段设置或删除默认值
1
2
3
4alter 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
3alter 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
3alter 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
3alter 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 | INSERT INTO tb_name (field1,field1,field1) values(value1,value2,value3); |
IGNORE:如果该行已存在,并给出了 IGNORE 子句,则新数据将被忽略,INSERT 语句仍会执行成功,同时生成一个警告和重复数据的数目。行的唯一性由主键标识。
1 | mysql> INSERT INTO `sakila`.`actor`(`actor_id`, `first_name`, `last_name`, `last_update`) VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33'); |
插入操作,若不写 字段名,则必须写所有列的值。可空字段、非空但含默认值字段、自增字段可不在字段列表中出现。
一条 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。
1 | -- 不存在行执行,插入,影响一行 |
可以看到第三次执行时改了金额,有两行受到影响,一个重复行被删除,一个新行被插入。
如果想在已经存在行的情况下处理重复项,则需要使用 ON DUPLICATE KEY UPDATE
。如果指定是了 ON DUPLICATE KEY UPDATE
选项,并且 INSERT 语句在 PRIMARY KEY
或 UNIQUE KEY
中引发重复值,则 MySQL 会用新值更新已有行。
例如,支付订单表,订单多次支付,不存在则插入,存在时则金额相加。使用主键作为唯一标识,行数据存在时,执行UPDATE子句。
1 | mysql> REPLACE INTO pay_order(id, `name`, amount, order_id) VALUES(1, 'Tom',22.23, '2023030300001') ; |
例如,支付订单表,订单多次支付,不存在则插入,存在时则金额相加。创建订单号为唯一索引,行数据存在时,执行UPDATE子句。
1 | mysql> REPLACE INTO pay_order(`name`, amount, order_id) VALUES('Tom',22.23, '2023030300001'); |
DQL 数据查询语句
SELECT 查询
1 | -- * 查所有列 |
DISTINCT 去重
1 | SELECT DISTINCT column_name from tb_name |
COUNT(*) 计数
1 | -- 统计全部 |
WHERE 条件过滤
WHERE condition:查匹配条件的数据行
条件操作符:=、>、<、>=、<=、!=、<>、IS NULL、IS NOT NULL、IN、NOT IN、BETWEEN...AND...、NOT BETWEEN...AND...
1 | select * from tb_name where condition ... |
简单模式匹配:LIKE 运算符实现简单模式匹配。使用下画线(_
)来精准匹配一个字符,使用(%
)来匹配任意数量的字符。
1 | -- 左前缀匹配 |
正则表达式:可以利用 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 | select `*` from tb_name [where condition] [order by column_name [asc|desc],order by column_name [asc|desc]] |
ASC指定升序,DESC指定降序。
ORDER BY 除了可以指定列名外,还可以指定 SELECT 语句中提及列的位置。
1 | mysql> SELECT customer_id, amount FROM payment ORDER BY 2 DESC LIMIT 5; |
LIMIT 限定行数
1 | select `*` from tb_name [limit offset_start, row_count] |
GROUP BY 分组
GROUP BY:分组查询,默认输出分组后 id 为最小的数据
1 | select `*` from tb_name group by column_name[,column_name] |
HAVING:对分组的结果再条件过滤,一般和聚合函数配合使用。
1 | select `*` tbu_user group by phone_number having count(phone_number) > 1; |
注:where 是在聚合前对记录进行过滤,通过先用 where 来缩小结果集,再根据逻辑用** having **再过滤。
DCL:数据控制语句
DCL 语句主要用于管理数据库系统中的对象的权象,开发较少使用。
GRANT:授予用户访问权限
1
2GRANT 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 操作
http://blog.gxitsky.com/2019/01/13/MySQL-02-quick-start-sql/