432 阅读 2020-08-25 09:12:02 上传
以下文章来源于 语料数据库
SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。
一、用户相关操作-DCL
新建用户
CREATE USER 'user_name'@'host' IDENTIFIED BY 'password';
用户授权
GRANT 权限名 ON 库名(*全部).表名(*全部) TO '用户名'@'%'(%表示所有的IP，可以一个IP) IDENTIFIED BY "密码";
授权所有主机：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
授权指定主机：GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.22.250' IDENTIFIED BY 'password' WITH GRANT OPTION;
授权本地主机：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
授权指定数据库：GRANT ALL PRIVILEGES ON databasename.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
授权指定操作权限：GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
撤销用户权限：REVOKE ALL PRIVILEGES ON databasename.* FROM 'username'@'host';
授权完后刷新权限：FLUSH PRIVILEGES;
删除用户：DROP USER 'username'@'host';
修改用户密码：
UPDATE mysql.user SET authentication_string=PASSWORD('root') WHERE user='root' ;
二、数据库操作-DDL
创建数据库：CREATE DATABASE databasename;
查看数据库：SHOW DATABASES;
使用数据库：USE databasename;
删除数据库：DROP DATABASE databasename;
三、表、视图、索引操作-DDL
创建表
CREATE TABLE tablename (
id int(10) NOT NULL AUTO_INCREMENT,
字段名2 varchar(50) DEFAULT NULL,
字段名3 int(3) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
或者CREATE TABLE IF NOT EXISTS tablename
复制表：CREATE TABLE tablename2 SELECT * FROM tablename...
创建临时表：CREATE TEMPORARY TABLE
查看数据库中可用的表：SHOW TABLES;
查看表的结构：DESCRIBE tablename; 或者SHOW COLUMNS FROM tablename;
删除表：DROP [ TEMPORARY ] TABLE [ IF EXISTS ] tablename1[ ,tablename2…….];
重命名表：RENAME TABLE name_old TO name_new; 或者ALTER TABLE name_old RENAME name_new;
新增表字段：ALTER TABLE 表名 ADD COLUMN 字段名 字段类型...
删除表字段：ALTER TABLE 表名 DROP 字段名
修改表字段：ALTER TABLE 表名 CHANGE 字段名 字段名 字段类型...
创建视图：CREATE VIEW viewname AS SELECT ...
视图的特殊作用：
a、简化表之间的联结（把联结写在select中）；
b、重新格式化输出检索的数据（TRIM，CONCAT等函数）；
c、过滤不想要的数据（select部分）
d、使用视图计算字段值，如汇总这样的值。
创建索引：
主键索引：ALTER TABLE 表名 ADD PRIMARY KEY (`列名`)
复合主键索引：ALTER TABLE 表名 ADD PRIMARY KEY (`列名1`, `列名2`)
唯一索引：ALTER TABLE 表名 ADD UNIQUE (`列名`)
单列索引：ALTER TABLE 表名 ADD INDEX 索引名(`列名`)
全文索引：ALTER TABLE 表名 ADD FULLTEXT (`列名`)
复合索引：ALTER TABLE 表名 ADD INDEX 索引名(`列名1`,`列名2`)
查看索引：SHOW INDEX FROM 表名
删除索引：DROP INDEX 索引名 ON 表名
四、常用DMQ和DML语句
查询语句
a.无条件查询：SELECT * FROM 表名
b.排序查询：SELECT * FROM 表名 ORDER BY 字段名 [ASC | DESC]
c.WHERE过滤：SELECT * FROM 表名 WHERE 条件语句
条件语句包括[比较运算符| IN | NOT IN | BETWEEN ... AND | OR | AND| LIKE | REGEXP]
d.分组GROUP BY：SELECT * FROM 表名 GROUP BY 列名
e.过滤分组HAVING：SELECT * FROM 表名 GROUP BY 列名 HAVING 条件
f.HAVING和WHERE差别：WHERE在数据分组前过滤，HAVING在数据分组后过滤
g.函数
| left() | 返回串左边的字符 |
| length() | 返回串的长度 |
| locate() | 找出串的一个子串 |
| lower() | 将串转换为小写 |
| ltrim() | 去掉串左边的空格 |
| right() | 返回串右边的字符 |
| rtrim() | 去掉串右边的空格 |
| soundex() | 返回串的soundex值 |
| substring() | 返回子串的字符 |
| upper() | 将串转换为大写 |
| concat() | 字符串连接 |
| adddate() | 增加日期，天，周 |
| addtime() | 增加时间，时，分 |
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| date() | 返回日期时间的日期部分 |
| datediff() | 计算两个日期之差，返回int |
| date_add() | 日期运算，date_add(now(),interval 3 month); |
| date_format() | 返回指定格式的日期 |
| day() | 返回日期天数的部分 |
| dayofweek() | 返回日期对应的星期 |
| hour() | 返回时间的小时部分 |
| minute() | 返回时间的分钟部分 |
| second() | 返回时间秒的部分 |
| month() | 返回日期的月数 |
| year() | 返回日期的年 |
| time() | 返回日期时间的部分 |
| now() | 返回当前日期时间 |
| abs() | 返回一个数的绝对值 |
| cos() | 返回角度的余弦 |
| sin() | 返回角度的正弦 |
| tan() | 返回角度的正切 |
| exp() | 返回数的指数值 |
| mod() | 返回除操作的余数 |
| pi() | 返回圆周率 |
| rand() | 返回一个随机数 |
| sqrt() | 返回一个数的平方根 |
| avg() | 返回某列的平均值 |
| count() | 返回某列的行数 |
| max() | 返回某列的最大值 |
| min() | 返回某列的最小值 |
| sum() | 返回某列之和 |
五、存储过程
创建存储过程
CREATE PROCEDURE sp_name([proc_parameter[,...]])
[characteristic ...] routine_body
创建函数
CREATE FUNCTION fun_name(func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
修改存储过程或函数：ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
调用存储过程：CALL sp_name([parameter[,...]])
查看存储过程或函数状态：SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern'] \G
查看存储过程或者函数的定义：SHOW CREATE {PROCEDURE | FUNCTION} sp_name \G
查询存储过程或者函数的信息：SELECT * FROM information_schema.routines WHERE routine_name = 'sp_name' \G
删除存储过程或函数：DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
参数说明：
1. proc_parameter: [ IN | OUT | INOUT ] param_name type 存储过程入参
2. func_parameter: param_name type 函数入参
3. type：有效的MySQL数据类型
4. characteristic：
- LANGUAGE SQL：表示此存储过程或函数的创建语言
- [NOT] DETERMINISTIC：输入相同参数返回相同结果，反之是输入相同参数返回不同结果，默认是NOT DETERMINISTIC
- CONTAINS SQL：子程序不包含读或写数据的语句（默认）
- NO SQL：子程序不包含SQL语句
- READS SQL DATA：子程序包含读数据的语句
- MODIFIES SQL DATA：子程序包含写数据的语句
- SQL SECURITY {DEFINER | INVOKER}：子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认是DEFINER。
- COMMENT 'string'：存储过程或函数的注释信息
5. routine_body：有效的SQL语句
六、触发器
触发器是指在进行某项指定操作时，触发触发器内指定的操作，支持触发器的语句有DELETE、INSERT、UPDATE,其他均不支持。
创建触发器
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
删除触发器：DROP TRIGGER trigger_name;
参数说明：
trigger_name：触发器的名称，不能与已经存在的触发器重复；
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发；trigger_event:：{ INSERT |UPDATE | DELETE }，触发该触发器的具体事件；
tbl_name：该触发器作用在tbl_name上；
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name，通过指定FOLLOWS/PRECEDES改变执行顺序，即FOLLOWS时表示新创建的触发器执行，PRECEDES则表示新触发器先执行；
trigger_body 表示触发器触发之后要执行的一个或多个语句，在内部可以引用涉及表的字段，OLD.col_name表示行数据被修改或删除之前的字段数据，NEW.col_name表示数据被插入或修改之后的字段数据；
