MySQL常用SQL语句

发表于 | 分类于 ， |   热度
| 字数统计: 1.5k | 阅读时长 ≈ 6

SQL语句大致可以分为四类，DDL(例如: CREATE、DROP、ALTER), DML(例如: INSERT、DELETE、UPDATE), DCL(例如: GRANT、REVOKE), DQL(例如: SELECT)

DDL数据定义语言
CREATE

CREATE常用搭配：CREATE DATABASE、CREATE TABLE、CREATE INDEX、CREATE USER，分别是创建数据库、创建表、创建索引、创建用户。

  • 创建数据库：
1
2
3
CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name];
-- 例如
CREATE DATABASE database_name;
  • 创建表：
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);
-- 例如
CREATE TABLE table_name (
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  account varchar(500) DEFAULT NULL COMMENT '用户’,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试';
  • 创建索引
1
2
3
4
5
6
7
8
-- 普通索引
CREATE INDEX index_name ON table_name (column1, column2, …);
-- 例如
CREATE INDEX idx_account_level ON table_name (account, level);
-- 唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, …);
-- 例如
CREATE UNIQUE INDEX uk_account_level ON table_name (account, level);
  • 创建用户
1
2
3
CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password';  
-- 例如
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
DROP

DROP常用搭配：DROP DATABASE、DROP TABLE、DROP INDEX、DROP USER，分别是删除数据库、删除表、删除索引、删除用户。

  • 删除数据库
1
2
3
DROP DATABASE [IF EXISTS] db_name；
-- 例如
DROP DATABASE database_name；
  • 删除表
1
2
3
DROP TABLE [IF EXISTS] table_name; 
-- 例如
DROP TABLE table_name；
  • 删除索引
1
2
3
DROP INDEX index_name ON table_name;
-- 例如
DROP INDEX idx_account_level ON table_name;
  • 删除用户
1
2
3
DROP USER [IF EXISTS] user [, user] …
-- 例如
DROP USER 'username'@'localhost';
ALTER

ALTER常用搭配：ALTER DATABASE、ALTER TABLE、ALTER USER，分别是更改数据库、更改表、更改用户。

  • 更改数据库
1
2
3
4
5
6
7
ALTER DATABASE [database_name] alter_option …
-- 更改默认数据库字符集
ALTER DATABASE database_name CHARACTER SET=utf8;
-- 更改默认数据库排序规则
ALTER DATABASE database_name COLLATE utf8_general_ci; 
-- 数据库是否只读，1只读，0非只读
ALTER DATABASE database_name READ ONLY = 1;
  • 更改表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
ALTER TABLE table_name [alter_option [, alter_option] ...] [partition_options]
-- 修改表存储引擎更改为InnoDB
ALTER TABLE table_name ENGINE=InnoDB;
-- 重置当前的自动增量值
ALTER TABLE table_name AUTO_INCREMENT=13;
-- 更改默认表字符集
ALTER TABLE table_name CHARACTER SET=utf8mb4; 
-- 添加或更改表注释
ALTER TABLE table_name COMMENT='comment';
-- 删除表的列或多列 
ALTER TABLE table_name DROP COLUMN a, DROP COLUMN b; 
-- a列重命名为b，并将其定义更改为BIGINT NOT NULL
ALTER TABLE table_name CHANGE a b BIGINT NOT NULL; 
-- 只更改b列的定义不改列名
ALTER TABLE table_name CHANGE b b INT NOT NULL; 
-- 只更改b列的定义不改列名，MODIFY只需要指定一次列名s
ALTER TABLE table_name MODIFY b INT NOT NULL; 
-- b列后面新增c列
ALTER TABLE table_name ADD c varchar(10) DEFAULT '' COMMENT 'xxxxx' AFTER b; 
-- 指定列字符集
ALTER TABLE table_name MODIFY c VARCHAR(10) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'xxxxx'
  • 更改用户
1
2
3
4
5
6
7
8
9
ALTER USER [IF EXISTS] user_identity [IDENTIFIED BY 'password'] [password_option | lock_option] [comment]
-- 修改用户密码
ALTER USER username@'%' IDENTIFIED BY '123';
-- 修改用户注释
ALTER USER username@'%' COMMENT "test user"
-- 锁定用户
ALTER USER username@'%' ACCOUNT LOCK;
-- 解锁用户
ALTER USER username@'%' ACCOUNT UNLOCK;
DML数据操作语言
INSERT
  • 数据插入
1
2
3
4
5
6
-- 插入一条或多条记录
INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …), (value1, value2, value3, …);
-- 复制表t1中所有列数据插入t2
INSERT INTO t2 SELECT * FROM t1 WHERE id=1;
-- 复制表t1中部分列数据插入t2
INSERT INTO t2 (column1, column2, column3, …) SELECT column1, column2, column3, … FROM t1 WHERE id=1;
DELETE
  • 数据删除
1
2
3
4
5
DELETE FROM table_name [WHERE Clause]
-- 删除符合条件的记录
DELETE FROM table_name WHERE id=1;
-- 删除表所有记录
DELETE FROM table_name;
UPDATE
  • 数据更新
1
2
3
UPDATE table_name SET column1=value1, column2=value2, … WHERE condition;
-- 例如
UPDATE table_name SET a=1,b='ok' WHERE id=1;
DCL数据控制语句
GRANT
  • 授权用户数据库权限或角色
1
2
3
4
5
6
7
8
9
10
11
12
13
GRANT privilege1, privilege2, privilege3...ON object_type TO user_or_role1, user_or_role2, user_or_role3...[WITH GRANT OPTION]
-- 赋予用户数据库所有表数据增、删、改、查的权限
GRANT select, insert, update, delete ON dbname.* TO username@'%';
-- 赋予用户修改数据库所有表结构创建、删除、修改的权限
GRANT create,alter,drop ON dbname.* TO username@'%';
-- 赋予用户操作索引的权限
GRANT index ON dbname.* TO username@'%';
-- 赋予用户数据库所有权限
GRANT ALL PRIVILEGES ON dbname TO username@'%';
-- 被赋予权限的用户，如果需要分享权限给其它用户，需要加上 WITH GRANT OPTION;
GRANT select, insert, update, delete ON dbname.* TO username@'%' WITH GRANT OPTION;
-- 刷新权限立即生效
FLUSH PRIVILEGES
REVOKE
  • 回收用户数据库权限或角色
1
2
3
4
5
6
REVOKE privilege1, privilege2, privilege3...ON object_type FROM user_or_role1, user_or_role2, user_or_role3...[WITH GRANT OPTION]
/* 用法与GRANT类似，之需要将关键字TO换成FROM即可 */
-- 回收用户数据库所有表数据增、删、改、查的权限
REVOKE select, insert, update, delete ON dbname.* FROM username@'%';
-- 刷新权限立即生效
FLUSH PRIVILEGES
DQL数据查询语言
SELECT
  • 查询数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 单个表数据查询
SELECT column1, column2, … FROM table_name [WHERE condition] [GROUP BY column_name [ASC | DESC]] [HAVING condition] [ORDER BY column_name [ASC | DESC]] [LIMIT number OFFSET offset];

--- JOIN连接多个表数据关联查询：
/*
INNER JOIN：获取两个表中字段匹配关系的记录,INNER可以省略不写
LEFT JOIN：获取左表所有记录，即使右表没有对应匹配的记录
RIGHT JOIN：获取右表所有记录，即使左表没有对应匹配的记录
*/
SELECT a.column1, b.column2, … FROM table1 a INNER JOIN table2 b ON a.colx = b.coly [WHERE condition];

-- UNION合并多个SELECT查询结果集:
/*
UNION连接两个及以上SELECT结果集并去除重复的记录
每个SELECT语句的列数和对应位置的数据类型必须相同
*/
SELECT column1, column2, … FROM table1 WHERE condition1 UNION [ALL | DISTINCT] SELECT column1, column2, … FROM table2 WHERE condition2 [UNION [ALL | DISTINCT] SELECT ...]
其它
数据删除

DELETE、TRUNCATE、DROP删除表数据的区别

  • DELETE是DML可以回滚，TRUNCATE、DROP是DDL不能回滚
  • DELETE、TRUNCATE只是删除数据，DROP删除数据和表结构
  • 执行速度DROP>TRUNCATE>DELETE
SHOW
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
-- 列出实例数据库
SHOW DATABASES [like_or_where]
-- 查看建库语句
SHOW CREATE DATABASE dbname
-- 查看数据库表详细信息
SHOW TABLE STATUS [FROM db_name] [like_or_where]
-- 列出数据库表
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
-- 查看建表语句
SHOW CREATE TABLE tablename
-- 查看当前打开的非临时表
SHOW OPEN TABLES [FROM db_name] [like_or_where]
-- 查看实例支持的存储引擎
SHOW ENGINES
-- 查看存储引擎状态
SHOW ENGINE engine_name {STATUS | MUTEX}
-- 例如
SHOW ENGINE INNODB STATUS;
-- 查看用户权限
SHOW GRANTS FOR user
-- 例如
SHOW GRANTS FOR username@'%';
-- 查看表索引信息
SHOW INDEX FROM tablename
-- 查看master状态
SHOW MASTER STATUS
-- 查看slave状态
SHOW SLAVE STATUS [FOR CHANNEL channel]
-- 查看实例所有插件信息
SHOW PLUGINS
-- 查看权限表
SHOW PRIVILEGES
-- 查看当前实例活跃线程
SHOW [FULL] PROCESSLIST
----------------本文结束 感谢阅读----------------