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表示数据被插入或修改之后的字段数据;