一、DDL
DATA DEFINITION LANGUAGES (数据定义语言),这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括 CREATE、DROP、ALTER 等。
1. 数据库定义
1.1 查看数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
1.2 创建数据库
# 创建名为learn_mysql的数据库
mysql> CREATE DATABASE learn_mysql;
Query OK, 1 row affected (0.00 sec)
1.3 删除数据库
# 删除数据库
mysql> DROP DATABASE learn_mysql;
Query OK, 1 row affected (0.27 sec)
1.4 使用数据库
# 使用learn_mysql数据库
mysql> USE learn_mysql;
Database changed
2. 数据表定义
2.1 列出当前数据库所有数据表
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_learn_mysql |
+-----------------------+
| test1 |
+-----------------------+
1 row in set (0.39 sec)
2.2 创建数据表
mysql> CREATE TABLE `test1` (
# 字段名 数据类型 约束条件
-> `id` int(11) NOT NULL,
-> `name` char(10) NOT NULL,
-> `age` int(11) DEFAULT NULL
# ENGINE(存储引擎) CHARSET(字符集)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2.3 查看表详情(字段信息)
mysql> DESC test1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2.4 查看表定义
mysql> SHOW CREATE TABLE test1;
+-------+------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------+
1 row in set (0.00 sec)
2.5 删除表
DROP TABLE table_name
2.6 修改表
-
修改字段类型
ALTER TABLE table_name MODIFY [COLUMN] col_def [FIRST | AFTER col_name]
-
添加字段
ALTER TABLE table_name ADD [COLUMN] col_def [FIRST | AFTER col_name]
-
删除字段
ALTER TABLE table_name DROP [COLUMN] col_name
-
字段重命名
ALTER TABLE table_name CHANGE old_col_name col_def
-
修改表名
ALTER TABLE table_name RENAME [TO] new_name
二、DML
Data Manipulation Language (数据操纵语句),用于添加、删除、更新和查询数据库记录,并检查数据完整性。常用的语句关键字主要包括 INSERT、DELETE、UPDATE、SELECT 等。
1. 插入数据 (INSERT)
1.1 插入单条记录
INSERT INTO table_name(col_name,…) VALUES(col_value,…);
1.2 插入多条记录
INSERT INTO table_name(col_name,…) VALUES(col_value,…),(col_value,…),…;
2. 修改数据 (UPDATE)
2.1 修改单表数据
UPDATE table_name SET field1=val1,field2=val2,… [WHERE CONDITION];
2.2 多表数据同时修改
UPDATE t1,t2,…,tn SET t1.field1=val1 ,t2.filed1=val2,…[WHERE CONDITION];
3. 删除数据 (DELETE)
3.1 删除单表数据
DELETE FROM TABLE [WHERE CONDITION];
3.2 多表数据同时删除
DELETE FROM t1,t2,…,tn [WHERE CONDITION];
4. 查询
4.1 简单查询
-
查询所有记录
SELECT * FROM table_name
-
记录去重
SELECT DISTINCT col_name(可以为*) FROM table_name
# 查询所有记录 mysql> SELECT * FROM test1; +----+------+------+ | id | name | age | +----+------+------+ | 1 | ccy1 | 12 | | 2 | ccy2 | 13 | | 3 | ccy | 16 | | 4 | ccy | 16 | +----+------+------+ 4 rows in set (0.00 sec) # 查询所有记录并去除完全重复记录 mysql> SELECT DISTINCT * FROM test1; +----+------+------+ | id | name | age | +----+------+------+ | 1 | ccy1 | 12 | | 2 | ccy2 | 13 | | 3 | ccy | 16 | | 4 | ccy | 16 | +----+------+------+ 4 rows in set (0.00 sec) # 查询name字段数据并去重 mysql> SELECT DISTINCT name FROM test1; +------+ | name | +------+ | ccy1 | | ccy2 | | ccy | +------+ 3 rows in set (0.00 sec)
-
条件查询
SELECT * FROM table_name [WHERE CONDITION]
条件运算符 说明 = 相等 > 大于 >= 大于等于 < 小于 <= 小于等于 != (<>) 不等于 mysql> SELECT * FROM test1 WHERE name='ccy'; +----+------+------+ | id | name | age | +----+------+------+ | 3 | ccy | 16 | | 4 | ccy | 16 | +----+------+------+ 2 rows in set (0.00 sec)
-
排序
关键字ORDER BY来实现,DESC和ASC是排序顺序关键字,DESC表示按照字段进行降序排列,ASC则表示升序排列,如果不写此关键字默认是升序排列。ORDER BY后面可以跟多个不同的排序字段,并且每个排序字段可以有不同的排序顺序,按照字段顺序作为排序依据
# 语法格式 # SELECT * FROM table_name [WHERE CONFITION] [ORDER BY] col_name # [DESC|ASC],col_name2 [DESC|ASC],...,col_namen [DESC|ASC] mysql> SELECT * FROM test1 ORDER BY id DESC; +----+------+------+ | id | name | age | +----+------+------+ | 4 | ccy | 16 | | 3 | ccy | 16 | | 2 | ccy2 | 13 | | 1 | ccy1 | 12 | +----+------+------+ 4 rows in set (0.00 sec)
-
记录条数限制
SELECT * FROM table_name [LIMIT start, end]
# 默认情况下,其实偏移量为0,数字为记录条数 mysql> SELECT * FROM test1 LIMIT 2; +----+------+------+ | id | name | age | +----+------+------+ | 1 | ccy1 | 12 | | 2 | ccy2 | 13 | +----+------+------+ 2 rows in set (0.00 sec) # 从第一条到第三条(从0开始计算) mysql> SELECT * FROM test1 LIMIT 1,3; +----+------+------+ | id | name | age | +----+------+------+ | 2 | ccy2 | 13 | | 3 | ccy | 16 | | 4 | ccy | 16 | +----+------+------+ 3 rows in set (0.00 sec)
-
聚合函数
# 语法 # SELECT [col_1,col_2,...,col_n] fun_name FROM table_name # [WHERE CONDITION] [GROUP BY col_1,...,col_n [WITH ROLLUP]] # [HAVING where_condition] mysql> SELECT COUNT(*) FROM test1; +----------+ | COUNT(*) | +----------+ | 4 | +----------+ 1 row in set (0.03 sec)
4.2 多表查询
-
表连接
表连接分为内连接和外连接,它们之间的最主要区别是,内连接仅选出两张表中互相匹配的记录,而外连接会选出其他不匹配的记录。常用内连接
-
内连接
SELECT * FROM t1, t2 WHERE t1.c1=t2.c1
mysql> SELECT * FROM test1,test2 WHERE test1.id=test2.id; +----+------+------+----+-------+ | id | name | age | id | name | +----+------+------+----+-------+ | 1 | ccy1 | 12 | 1 | ccy | | 2 | ccy2 | 13 | 2 | ccy | +----+------+------+----+-------+ 2 rows in set (0.28 sec)
-
外连接
-
左连接
包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
mysql> SELECT * FROM test1 left join test2 on test1.id=test2.id; +----+------+------+------+-------+ | id | name | age | id | name | +----+------+------+------+-------+ | 1 | ccy1 | 12 | 1 | ccy | | 2 | ccy2 | 13 | 2 | ccy | | 3 | ccy | 16 | NULL | NULL | | 4 | ccy | 16 | NULL | NULL | +----+------+------+------+-------+ 4 rows in set (0.00 sec)
-
右连接
包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
mysql> SELECT * FROM test1 right join test2 on test1.id=test2.id; +------+------+------+----+-------+ | id | name | age | id | name | +------+------+------+----+-------+ | 1 | ccy1 | 12 | 1 | ccy | | 2 | ccy2 | 13 | 2 | ccy | +------+------+------+----+-------+ 2 rows in set (0.00 sec)
-
4.3 子查询
当进行查询的时候,需要的条件是另外一个 select 语句的结果,这个时候,就要用到子查询。用于子查询的关键字主要包括 in、not in、=、!=、exists、not exists 等。
# 查询test1中与test2的id相同的记录
mysql> SELECT * FROM test1 WHERE id in (SELECT id FROM test2);
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | ccy1 | 12 |
| 2 | ccy2 | 13 |
+----+------+------+
2 rows in set (0.03 sec)
4.4 记录联合
两个表的数据按照一定的查询条件查询出来后,将结果合并到一起显示出来,这个时候,就需要用 union 和 union all 关键字来实现这样的功能
-
UNION ALL
mysql> SELECT name FROM test1 UNION ALL SELECT name FROM test2; +-------+ | name | +-------+ | ccy1 | | ccy2 | | ccy | | ccy | | ccy | | ccy | +-------+ 6 rows in set (0.02 sec)
-
UNION
UNION ALL结果集去重后数据集
mysql> SELECT name FROM test1 UNION SELECT name FROM test2; +-------+ | name | +-------+ | ccy1 | | ccy2 | | ccy | | ccy | +-------+ 4 rows in set (0.05 sec)
三、DCL
Data Control Language (数据控制语句),用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 DRANT、REVOKE 等。
1. 创建数据库用户并指定权限
# GRANT [SELECT|INSERT] ON database_name.table_name to user_name IDENTIFIED
# BY password
# 创建用户并赋予权限
mysql> GRANT SELECT ON learn_mysql.* TO 'ccy'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.06 sec)
# 以该用户登陆后数据库状态
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| learn_mysql |
+--------------------+
2 rows in set (0.00 sec)
# 插入数据是提示无权限
mysql> insert into test1 values(10,'cc',10);
ERROR 1142 (42000): INSERT command denied to user 'ccy'@'localhost' for table 'test1'
# ROOT用户下修改权限
mysql> GRANT INSERT,SELECT ON learn_mysql.* TO 'ccy'@'localhost';
Query OK, 0 rows affected (0.00 sec)
# 插入成功
mysql> insert into test1 values(10,'cc',10);
Query OK, 1 row affected (0.12 sec)
2. 回收权限
# REVOKE [INSERT|SELECT] ON database_name.table_name FROM user_name;
# ROOT用户权限下
mysql> REVOKE INSERT ON learn_mysql.* FROM 'ccy'@'localhost';
Query OK, 0 rows affected (0.00 sec)