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

快速了解基本的 SQL 语句。

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

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

连接到MySQL

登录 MySQL:安装目录 \bin 里包含 MySQL 命令行客户端,可以使用以下命令中任一条来连接到 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 语句类别

主要有三类:

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

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

DDL 数据定义语句

数据库操作

  1. 创建数据库

    1
    create database [if not exists] db_name character set utf8 collate utf8_general_ci;

    在创建数据库时,需要指定字符编码和字符排序规则
    cicase insensitive 首字母组合, 即大小写不敏感,a 和 A 在字符判断中被 当做一样的。
    utf8_general_ci 不区分大小写,这个在注册用户名和邮箱的时候有用。
    utf8_general_cs 区分大小写
    utf8_bin 字符串每个字符串用二进制数据编译存储。 区分大小写,而且可以存二进制的内容
    MySQL 有两个支持 unicodecharacter set

    ucs2,使用 16 bits 来表示一个 unicode 字符;
    utf8,使用 1~3 bytes 来表示一个 unicode 字符。

  2. 查看数据库创建信息

    1
    SHOW CREATE database db_name;
  3. 列出有权限访问的所有数据库

    1
    SHOW DATABASES;
  4. 选择数据库

    1
    USE db_name;
  5. 查看当前连接的数据库

    1
    SELECT DATABASES();
  6. 修改数据库

    1
    ALTER database db_name character set utf8 ;
  7. 删除数据库

    1
    drop database [if exists] db_name;
  8. 数据库被创建为数据目录中的一个目录。可以通过执行以下命令获知当前的数据目录。

    1
    2
    3
    4
    5
    6
    7
    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)

表操作

  1. 创建表

    1
    2
    3
    4
    5
    6
    7
    8
    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 文件。

  2. 查看所有存储引擎

    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
    65
    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)
  3. 克隆表结构到新表

    1
    mysql> CREATE TABLE new_table LIKE original_table;
  4. 查看表创建信息

    1
    show create table tb_name;
  5. 查看表信息

    1
    DESC tb_name;
  6. 列出所有表

    1
    show tables;
  7. 选中表

    1
    USE tb_name;
  8. 重命名表

    1
    2
    3
    4
    ALTER table tb_name rename [to] new_tb_name; //mysql特有
    示例:
    ALTER TABLE stu RENAME student;
    ALTER TABLE stu RENAME TO student;
  9. 删除表

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

1
TRUNCATE TABLE tb_name;

字段操作

  1. 增加字段,默认在表尾增加

    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);
  2. ALTER COLUMN:字段设置或删除默认值

    1
    2
    3
    4
    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;
  3. 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;
  4. 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);
  5. DROP COLUMN:删除表字段

    1
    2
    3
    alter table tb_name drop column_name
    -- 示例:
    ALTER TABLE student DROP address;
  6. 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 语句仍会执行成功,同时生成一个警告和重复数据的数目。行的唯一性由主键标识。

1
2
3
4
5
6
7
8
9
10
11
12
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。

1
2
3
4
5
6
7
8
9
10
11
-- 不存在行执行,插入,影响一行
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 KEYUNIQUE KEY 中引发重复值,则 MySQL 会用新值更新已有行。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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子句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 查询

1
2
3
4
5
-- * 查所有列
SELECT * FROM tb_name;

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

DISTINCT 去重

1
SELECT DISTINCT column_name from tb_name

COUNT(*) 计数

1
2
3
4
5
-- 统计全部
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 运算符实现简单模式匹配。使用下画线(_)来精准匹配一个字符,使用(%)来匹配任意数量的字符。

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
-- 左前缀匹配
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 |
+----------+------------+-----------+---------------------+

正则表达式:可以利用 RELIKEREGEXP,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 语句中提及列的位置。

1
2
3
4
5
6
7
8
9
10
11
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 限定行数

1
2
3
4
5
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 语句主要用于管理数据库系统中的对象的权象,开发较少使用。

  1. 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;
  2. REVOKE: 解除用户访问权限

    1
    REVOKE ALTER ON db_name.* FROM 'user_name'@'%';
  3. DENY:拒绝用户访问

相关参考

  1. 官方MySQL Doc:Regular Expressions

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

http://blog.gxitsky.com/2019/01/13/MySQL-02-quick-start-sql/

作者

光星

发布于

2019-01-13

更新于

2023-03-27

许可协议

评论