SQL语言

数据库

创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name;

查看MySQL服务器下所有数据库

SHOW DATABASES;

查看指定数据库的创建信息

SHOW CREATE DATABASE database_name;

选择数据库

USE database_name;

登陆MySQL服务器时选择要使用的数据库

mysql -u administrator -p password database_name

删除数据库

DROP DATABASE [IF EXISTS] database_name;

表

创建表

CREATE TABLE [IF NOT EXISTS]
table_name(
column_definition1 [column_constraint1],
column_definition2 [column_constraint2],
……,
[table_constraint]
);

SQL支持的数据类型

数据类型	含义	示例
CHAR(L)	定长的字符串
VARCHAR(L)	变长的字符串
INT	整数
DECIMAL(p,s)	浮点数
DATE	固定长度的日期	‘YYYY-MM-DD’
TIME	固定长度的时间	‘HH:MM:SS’
DATETIME	日期时间	‘YYYY-MM-DD HH:MM:SS’
BOOLEAN	布尔类型	TRUE FALSE

用子查询创建表

CREATE TABLE table_name
SELECT语句;

查看当前数据库中的所有数据表

SHOW TABLES;

查看表名满足匹配模式的数据表

SHOW TABLES [LIKE 匹配模式]

匹配模式：字符和通配符%构成的字符串，例’c%’

查看数据库中表的状态信息

SHOW TABLE STATUES [FROM database_name] [LIKE 匹配模式];

查看表中所有属性/字段信息

DESCRIBE | DESC table_name;

查看指定属性/字段信息

DESCRIBE | DESC table_name field_name;

查看表结构

SHOW [FULL] COLUMNS FROM table_name [FROM] database_name;

SHOW [FULL] COLUMNS FROM database_name.table_name;

SHOW CREATE TABLE table_name;

修改表名

ALTER TABLE oldname RENAME newname;

RENAME TABLE oldname1 TO newname1 [oldname2 TO newname2] …;

表中添加新列

ALTER TABLE table_name
ADD [COLUMN] column_name data_type
[DEFAULT 默认值];

从表中删除一列

ALTER TABLE table_name
DROP [COLUMN] column_name;

修改表中已有列

ALTER TABLE table_name
MODIFY [COLUMN] column_name
[DEFAULT 默认值];

删除表

DROP TABLE [IF EXISTS] table_name1[,table_name2];

索引

创建索引

CREATE [UNIQUE唯一 | FULLTEXT全文 | SPATIAL] INDEX index_name ON table_name;

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name FROM table_name;

数据库完整性

MySQL提供的约束机制

primary key 主键约束
foreign key 外键约束
unique 唯一约束
check 检查约束
default 默认值
constraint 约束命名
trigger 触发器

完整性约束命名

CONSTRAINT 约束名(类似变量名) 约束内容;

约束内容：primary key字句，foreign key字句，unique字句，not null字句，check字句

定义主键约束

CONSTRAINT 约束名 PRIMARY KEY(列名1[,列名2])

PRIMARY KEY(id) 写在create table里

删除主键约束

ALTER TABLE table_name
DROP PRIMARY KEY;

添加主键约束

ALTER TABLE table_name
ADD CONSTRAINT 约束名 PRIMARY KEY(column_name);

定义外键约束

FOREIGN KEY(column_name1) REFERENCES table_name(column_name2) ON DELETE | UPDATE reference_option

reference_option:CASCADE | SET NULL | RESTRICT |NO ACTION | SET DEFAULT

CASCADE级联：当从父表中删除或更新记录时，自动地修改或更新子表中与之匹配的行

SET NULL：父表中对应的行被删除或更新之后，连带地将子表中的外键取值设为NULL，当然，这种设置需要确保子表中没有将外键列设置NOT NULL 约束

RESTRICT限制：不允许父表的删除和更新

NO ACTION：源于标准SQL的关键字。在MySQL中，它等价于设置成RESTRICT的效果

SET DEFAULT：设置默认行为，如果都不指定，默认行为是NO ACTION

添加外键约束

ALTER TABLE table_name
ADD CONSTRAINT 约束名 PRIMARY KEY(column_name1) REFERENCES table_name(column_name2);

删除外键

ALTER TABLE table_name
DROP FOREIGN KEY;

定义唯一约束

CONSTRAINT 约束名 UNIQUE(column_name)

删除唯一约束

ALTER TABLE table_name
DROP CONSTRAINT 约束名;

检查约束

ALTER TABLE employee
ADD CONSTRAINT age_add_CK
CHECK (age>20 AND age<60 AND address LIKE ‘北京市%’);

单表查询

select查询语句

SELECT [DISTINCT] select_expr

[ FROM table_references

WHERE where_condition

GROUP BY col_name|group_by_expression

HAVINGwhere_condition

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],

…

LIMIT [offset,] row_count ];

pselect_expr：col_name | * | 运算表达式 |列别名表达式

DISTINCT：过滤重复结果

table_references：指定从哪个或哪几个表中查询数据；当指定多个表时，多个表名以逗号分隔，并会对这些表进行连接操作（涉及多表查询）

where_condition：条件表达式，若有多个条件表达式，用 and 或 or 或 not 组合

GROUP BY：按指定的列(属性)的取值对行(记录/元组)进行分组，每组返回一个元组；可指定多列，列名之间以逗号分隔；利用表达式进行元组的分组；通常结合聚合函数一起使用

HAVING：对分组进行过滤，必须与 group by 子句结合使用；Having是针对分组的结果即一组元组进行过滤，where是针对每个元组

ORDER BY：按指定的列(属性)的取值对行(记录/元组)进行排序ASC：升序ascending；DESC：降序descending

offset: 指定返回查询结果的第一行；0：从表中第一个满足条件的行开始返回；1: 从表中第2个满足条件的行开始返回

row_count: 返回的最大行数，poffset, row_count 取0或正整数

表别名

SELECT *|列名 from 表名 [AS] 表别名

条件查询

比较：表达式A<|<=|>|>=|<>|!=|=表达式B

之间判断：表达式 [NOT] BETWEEN 值1 AND 值2

字符串模糊查询：字符串 [NOT] LIKE 匹配模式

空值判断：表达式 IS [NOT] NULL

之间判断：表达式 [NOT] IN (集合)

多条件组合：比较表达式A NOT|AND|OR 比较表达式B（优先级高到低NOT AND OR）

聚合函数

COUNT(*)：计算记录的个数

COUNT(列名)：对某一列的值计算个数

SUM(列名)：求某一列值的总和

AVE(列名)：求某一列值的平均值

MAX(列名)：求某一列值的最大值

MIN(列名)：求某一列值的最小值

能用于：所查询的列，HAVING子句中，ORDER BY子句中

不能用于：WHERE子句中，GROUP BY子句中

除了COUNT(*)之外，其他聚合函数（包括COUNT(<列名>)）都忽略对列值为NULL值的统计

多表查询

table_references：表名以逗号分隔，并会对这些表进行连接操作；或使用join

tab1 join tab2 on 条件

tab1 join tab2 using (列名|属性)

笛卡尔积

SELECT * FROM tab1 , | JOIN | INNER JOIN | CROSS JOIN tab2;

等值连接

SELECT * FROM 表名1, 表名2 WHERE 表名1.列名1=表名2.列名2;

SELECT * FROM 表名1 JOIN 表名2 ON 表名1.列名1=表名2.列名2;

外连接

SELECT * FROM 表名1 LEFT JOIN 表名2 ON 条件 | using(列名|属性);

SELECT * FROM 表名1 RIGHT JOIN 表名2 ON 条件 | using(列名|属性);

自然连接

SELECT * FROM 表名1 JOIN 表名2 USING (column_name)

SELECT * FROM 表名1 NATURAL JOIN 表名2

using 要求连接的表中存在同名的列；

narutral join 不要求存在同名列，但如果没有同名列，会按笛卡尔积算

自然连接结果中，同名列只保留一列，即会去掉重复列

MySQL未支持全外连接

嵌套查询

将一个查询块嵌套在另一个查询块的 where 子句或Having短语的条件中的查询

主查询(外部查询)：对应的外部查询

子查询(内部查询)：嵌套在另一个查询内的查询

在WHERE子句中使用多值子查询时，要使用到以下

谓词和比较运算符

in | not in

all + 比较运算符 sal>all(子查询)：sal大于所有子查询的值

any + 比较运算符 sal>any(子查询)：sal大于任意子查询的值

exists | not exists：若查询结果为空，外层的where子句返回false；若查询结果不为空，返回true

集合查询

并UNION

两个select 查询结果结构完全一致时，可对这两个查询结果执行并运算

SELECT 语句1

UNION [ALL]

SELECT 语句2

union all：保留重复元组

还可以用order by对元组排序

交INTERSECT

可使用DISTINCT运算符和INNER JOIN子句模拟

差EXCEPT

数据更新

表中插入数据

INSERT INTO 表名 [ (列名1[，列名2……]) ]

VALUES (值1[，值2……])

[,(值1[，值2……]),……,(值1[，值2……])]

每条记录（每行数据）放在一对圆括号中，多条记录用逗号分隔；或者用多个 insert into 语句一行一行插入；

利用查询子句添加元组

INSERT INTO 表名 [ (列名1[,列名2……]) ]

SELECT语句

表中记录的更新

UPDATE 表名

SET 列名=值[,列名=值,……]

[WHERE 条件]

利用子查询修改记录

UPDATE 表名

SET 列名= (SELECT语句)

WHERE 条件;

删除元组/行

DELETE FROM 表名

[WHERE 条件];

删除表中所有元组

TRUNCATE TABLE 表名;

用户管理

创建新用户

CREATE USER user_account IDENTIFIED BY password;

user_account：’username’用户名@’hostname’ 主机名（ip地址、主机名或通配符%）；主机名省略则默认为%

如果两个用户的用户名相同，但主机名不同，MySQL 会将它们视为两个用户，并允许为这两个用户分配不同的权限集合。

修改用户名

RENAME USER old_user TO new_user

修改MySQL用户密码

ALTER USER user_account IDENTIFIED BY password;

SET PASSWORD FOR user_account =password;

删除用户

DROP USER [IF EXISTS] user1, [user2],…;

查看用户权限

SHOW GRANTS FOR 用户名;

用户权限：数据库对象和操作类型

对象类型	对象	操作类型
模式	模式	Create database
模式	基本表	Create table， alter table
模式	视图	Create view
模式	索引	Create index
数据	基本表和视图	Select, insert, update, delete, references, all privileges
数据	属性列	Select, insert, update, references, all privileges

权限授予GRANT

GRANT 权限 ON 对象 TO 用户 [WITH GRANT OPTION];

权限类型：all / alter / create / drop / select /update …

表级权限：all [privileges]: 所有权限

列级权限：select(列名)，update(列名) …

对象类型：*.* 或 database.* 或 database.table

**[WITH GRANT OPTION]**：添加之后允许该用户将对应的权限授予给其他用户

授予用户所有权限

GRANT ALL ON *.* TO 用户 [WITH GRANT OPTION];

将用户1的权限赋予新用户2

GRANT 用户1 TO 用户2;

撤销权限REVOKE

REVOKE 权限 ON 对象 FROM 用户；

撤销授予用户的所有权限

REVOKE ALL PRIIVILEGES,GRANT OPTION FROM 用户；

创建角色

CREATE ROLE role_name;

授予角色权限

GRANT 权限 ON 对象 TO 角色;

撤消角色的权限

REVOKE 权限 ON 对象 FROM 角色;

用角色为新用户授权

GRANT 角色名1, 角色名2 TO 用户名1, 用户名2;

显示角色所代表的权限

SHOW GRANTS FOR 用户名 USING 角色名;

查看角色是否被激活

SELECT CURRENT_ROLE();

角色激活

登录应用用户进行手工激活： set role 角色名;

用超级用户执行如下命令：set default role … to …; 表示用户一登录就立马激活角色

强制角色:系统参数mandatory_roles指定对整个数据库起作用的角色；

系统参数activate_all_roles_on_login设置为true，所有用户拥有的角色在登录时都会被自动激活；这个系统参数的默认值是false，默认只有用户的默认角色是激活的。

修改当前会话中的活动角色

语句	作用
SET ROLE 角色名\|ALL;	激活角色；All: 设置活动角色为当前用户被授予的所有角色；
SET ROLE NONE;	关闭活动角色
SET ROLE DEFAULT;	设置活动角色为由SET DEFAULT ROLE语句设置的默认角色
SET ROLE ALL EXCEPT…;
SET DEFAULT ROLE 角色1, 角色2,… TO 账号;	指定账号的哪些角色被默认激活

设置默认角色后,(1) 需重新登陆才生效；(2) 之后用户每次登陆会自动激活该默认角色

删除角色

DROP ROLE role_name;

查询MySQL服务器上创建过的角色

SELECT user AS role_name

FROM mysql.user

WHERE host=’%’ AND NOT LENGTH(authentication_string);

用户和角色信息均存储在系统表mysql.user中；

区别在于：在mysql.user表中用户和角色的属性列 account_locked, password_expired 的取值不同；角色没有密码，用户有密码

视图

从一个或几个基本表或视图中导出的虚表，仅存放视图的定义，不存放视图对应的数据；

定义视图

CREATE [OR REPLACE]

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = user]

[SQL SECURITY { DEFINER | INVOKER }]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

修改视图

CREATE OR REPLACE

ALTER

删除视图

DROP VIEW [IF EXISTS] view_name [, view_name] …

[RESTRICT | CASCADE]

查看数据库中的表和视图

SHOW [FULL] TABLES;

基于视图再创建视图

CREATE VIEW view_name [(column_list)]

AS SELECT…

FROM 已有视图