文章目录
基础篇
MySQL基础篇
通用语法:
在学习具体的SQL语句之前,先来了解一下SQL语言的通用语法。
-
1). SQL语句可以单行或多行书写,以分号结尾。
-
2). SQL语句可以使用空格/缩进来增强语句的可读性。
-
3). MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
-
4). 注释:
-
单行注释:-- 注释内容 或 # 注释内容
-
多行注释:/* 注释内容 */
-
分类:
- DDL(Data DefinitionLanguage): 数据定义语言,用来定义数据库对象(数据库、表、字段)
- DML(Data ManipulationLanguage): 数据操作语言,用来对数据库表中的数据进行增删改
- DQL(Data Query Language ): 数据查询语言,用来查询数据库中表的记录
- DCL(Data Control Language): 数据控制语言,用来创建数据库用户、控制数据库的控制权限
DDL(数据定义语言)
数据库操作
查询所有数据库:
SHOW DATABASES;
查询当前数据库:
SELECT DATABASE();
创建数据库:
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
删除数据库:
DROP DATABASE [ IF EXISTS ] 数据库名;
使用数据库:
USE 数据库名;
注意事项
- UTF8字符集长度为3字节,有些符号占4字节,所以推荐用utf8mb4字符集
表操作
查询当前数据库所有表:
SHOW TABLES;
查询表结构:
`DESC 表名;`
查询指定表的建表语句:
SHOW CREATE TABLE 表名;
创建表:
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
...
字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];
最后一个字段后面没有逗号
修改表结构:
添加字段:
`ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];`
例:`ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';`
修改数据类型:
`ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);`
修改字段名和字段类型:
`ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];`
例:将emp表的nickname字段修改为username,类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';
删除字段:
ALTER TABLE 表名 DROP 字段名;
修改表名:
ALTER TABLE 表名 RENAME TO 新表名
删除表:
DROP TABLE [IF EXISTS] 表名;
删除表,并重新创建该表:
TRUNCATE TABLE 表名;
DQL(数据查询语言)
- 简单查询
- 自连接
- 内连接
- 外连接
- 子查询
DML(数据操作语言)
添加数据
指定字段:
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
全部字段:
INSERT INTO 表名 VALUES (值1, 值2, ...);
批量添加数据:
`INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);`
`INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);`
注意事项
- 字符串和日期类型数据应该包含在引号中
- 插入的数据大小应该在字段的规定范围内
更新和删除数据
修改数据:
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
例:
UPDATE emp SET name = 'Jack' WHERE id = 1;
删除数据:
DELETE FROM 表名 [ WHERE 条件 ];
删除表,并重新创建该表:(类似于删除数据)
TRUNCATE TABLE 表名;
DCL(数据控制语言)
登录MySQL服务器
启动MySQL服务后,可以通过mysql命令来登录MySQL服务器,命令如下:
mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"
DatabaseName参数
指明登录到哪一个数据库中。如果没有该参数,就会直接登录到MySQL数据库中,然后可以使用USE命令来选择数据库。-e参数
后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句,然后退出MySQL服务器。
增删改用户
创建用户
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
举例:
CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 %
CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';
修改用户
UPDATE mysql.user SET USER='li4' WHERE USER='wang5';
FLUSH PRIVILEGES;
删除用户
方式1:使用DROP方式删除(推荐)
DROP USER user[,user]…;
举例:
DROP USER li4 ; # 默认删除host为%的用户
DROP USER 'kangshifu'@'localhost';
方式2:使用DELETE方式删除(不推荐,有残留信息)
DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;
注意:不推荐通过 DELETE FROM USER u WHERE USER=‘li4’ 进行删除,系统会有残留信息保留。而drop user命令会删除用户以及对应的权限,执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。
设置当前用户密码
1. 使用ALTER USER命令来修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password';
2. 使用SET语句来修改当前用户密码
SET PASSWORD='new_password';
1.6 修改其它用户密码
1. 使用ALTER语句来修改普通用户的密码
ALTER USER user [IDENTIFIED BY '新密码']
[,user[IDENTIFIED BY '新密码']]…;
2. 使用SET命令来修改普通用户的密码
SET PASSWORD FOR 'username'@'hostname'='new_password';
权限管理
2.1权限列表
show privileges;
CREATE和DROP权限
,可以创建新的数据库和表,或删除(移掉)已有的数据库和表。如果将MySQL数据库中的DROP权限授予某用户,用户就可以删除MySQL访问权限保存的数据库。SELECT、INSERT、UPDATE和DELETE权限
允许在一个数据库现有的表上实施操作。SELECT权限
只有在它们真正从一个表中检索行时才被用到。INDEX权限
允许创建或删除索引,INDEX适用于已有的表。如果具有某个表的CREATE权限,就可以在CREATE TABLE语句中包括索引定义。ALTER权限
可以使用ALTER TABLE来更改表的结构和重新命名表。CREATE ROUTINE权限
用来创建保存的程序(函数和程序),ALTER ROUTINE权限
用来更改和删除保存的程序,EXECUTE权限
用来执行保存的程序。GRANT权限
允许授权给其他用户,可用于数据库、表和保存的程序。FILE权限
使用户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件,任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件(说明用户可以读任何数据库目录下的文件,因为服务器可以访问这些文件)。
2.2 授予权限的原则
权限控制主要是出于安全因素,因此需要遵循以下几个经验原则
:
1、只授予能满足需要的最小权限
,防止用户干坏事。比如用户只是需要查询,那就只给select权限就可以了,不要给用户赋予update、insert或者delete权限。
2、创建用户的时候限制用户的登录主机
,一般是限制成指定IP或者内网IP段。
3、为每个用户设置满足密码复杂度的密码
。
4、定期清理不需要的用户
,回收权限或者删除用户。
2.3 授予权限
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
- 该权限如果发现没有该用户,则会直接新建一个用户。
- 给li4用户用本地命令行方式,授予atguigudb这个库下的所有表的插删改查的权限。
GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost;
- 授予通过网络方式登录的joe用户 ,对所有库所有表的全部权限,密码设为123。注意这里唯独不包括grant的权限
GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';
2.4 查看权限
- 查看当前用户权限
SHOW GRANTS;
或
SHOW GRANTS FOR CURRENT_USER;
或
SHOW GRANTS FOR CURRENT_USER();
- 查看某用户的全局权限
SHOW GRANTS FOR 'user'@'主机地址';
2.5 收回权限
注意:在将用户账户从user表删除之前,应该收回相应用户的所有权限。
- 收回权限命令
REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
- 举例
收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;
- 注意:
须用户重新登录后才能生效
角色管理
3.1 创建角色
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
角色名称的命名规则和用户名类似。如果host_name省略,默认为%
,role_name不可省略
,不可为空。
3.2 给角色赋予权限
GRANT privileges ON table_name TO 'role_name'[@'host_name'];
上述语句中privileges代表权限的名称ÿ