MySQL 指令速查_mysql快速查询
赞
踩
目录
数据库的基本操作
创建数据库：create database 数据库名；
选择数据库：use 数据库名
查询所有数据库：show databases;
删除数据库：drop database 数据库名;
表的基本操作
创建表：create table 表名{列名 数据类型 [属性],......}
利用子查询创建表create table 表名 select 语句;
修改表名：
alter table 旧名 rename to 新名;
列的属性：
主键primary key
自增类型字段auto_increment
修改表结构：
新增列：alter table 表名 add 列名 数据类型 [属性];
修改已有列属性：alter table 表名 modify 列名 数据类型 [属性];
删除已有列：alter table 表明 drop 列名;
查询表：
查询表结构：desc 表明;
查询数据库中所有表：show tables;
删除表：drop table 表名;
截断表：truncate table 表名;（删除表中所有数据并保留表的格式）
注：假如要删除的表中有自增长列，如果用delete 删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始
数据的基本操作
添加数据：
insert into 表名 values (数据1,数据2,......);
指定列添加数据：insert into 表名(列名1, 列名2,......) values (列1的值,列2的值,......);
将一个表中的数据插入到另一个表中：insert into 表名 select * from 表名;
添加数据的三种方式：
insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
修改数据：update 表名 set 列名 = 数据 [where 条件];
注意：当不添加条件时，默认修改此表此列的所有值
替换数据：replace(列名，旧值，新值)
例：update titles_test set emp_no = replace (emp_no, 10001, 10005) where id = 5;
删除数据：delete from 表名 [where 条件]
注意：不加条件时默认删除表中所有数据
查询数据：
查询表中所有数据：select * from 表名;
查询表中特定列数据：select 列名1, 列名2, ...... from 表名;
为查询列起别名：原字段名 [as] 列别名
去掉重复行：distinct/distinctrow
例：select distinct * from 表名;
拼接字符串：concat
例：select concat(last_name, '\'', first_name) from employees;
条件查询：
比较远算符：
算术比较运算符：< <= > >= != = 特殊：<>：两边不相等 <=>：两边相等或都为空
逻辑比较判断：not or and
之间判断：[not]between 值1 and 值2 注：值1小于值2
字符串模糊判断：[not] like 字符串 '%'表示任意个字符，'_'表示任意一个字符
空值判断：is [not] null
exists关键字：
例：select * from employees where not exists(select emp_no from dept_emp where emp_no = employees.emp_no);
之内判断：[not] in (集合)
对查询结果进行排序：order by 列1,列2,......
注：1.子句必须放到对吼一个子句。2.默认列是升序（asc）排列，在列名后面加desc则进行降序排列
分组查询：
聚合函数：
count(*)：计算记录的个数
count(列名)：计算一列中的个数
sum(列名)：求一列的总和
avg()：求一列的平均值
max()：求一列的最大值
min()：求一列的最小值
group_concat(列名[, 分隔符])：可以将一列值拼接成字符串，以分割符分割（分隔符不写时默认使用逗号）。
例：SQL53: select dept_no, group_concat(emp_no) employees from dept_emp group by dept_no;
concat(字符串1，字符串2[，字符串3...，字符串n])：拼接字符串
length(字符串)：计算字符串的长度
substr(x，y[，z])：提取字串，x为字符串，y为开始的位置，z为长度（省略不写时，提取从y开始到结束的子串）。
replace(列名，需要替换的子串，新的子串)：字符串的替换
按单列分组：group by 列名
按多列分组：group by 列名1, 列名2
having子句：having子句和where功能相同，但having 子句后面可以加聚合函数的条件，而where后面不能跟聚合函数条件。
连接查询：
相等连接（内连接）：from 表名1 inner join 表名2 on 表名1.列=表名2.列
自身连接：from 表名 表别名1, 表名 表别名2 where 别名1.列=别名2.列 and 条件
不等连接：
例：select e.ename, e. sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal and e.deptno=20;
左外连接：from 表1 left outer join 表2 on 表1.列= 表2.列
右外连接：from 表1 right outer join 表2 on 表1.列=表2.列
子查询：
返回单值的子查询：
例：select * from emp where job = (select job from emp where ename = 'scott');
返回多值的子查询：
in操作符：比较子查询返回列表中的每一个值，并任何相等的数据行
all操作符：比较子查询返回列表中的每一个值，<all 为小于最小的，>all 为大于最大的
any操作符：比较子查询返回列表中每一个值，<any 为小于最大的，>any 为大于最小的
exists操作符：判断子查询得到的结果集是否是一个空集，如果不是，则返回 True，如果是，则返回 False
not exists操作符：作用与 EXISTS 正相反
合并查询结果：select 语句1 union[all] select 语句2;
注：使用union默认删除合并数据中的重复行，在使用union all 的时候结果中不会删除重复行
游标
声明游标：declare 游标名 cursor for select 语句;
例：DECLARE avg_cur CURSOR FOR SELECT avg(分数) FROM grade g,curriculum c WHERE g.课程编号=c.课程编号 and 课程名称=cname;
打开游标：open 游标名;
例：OPEN avg_cur;
提取数据：fetch 游标名 into 变量1[,变量2,......];
例：FETCH avg_cur INTO v_avg;
关闭游标：close 游标名;
例：CLOSE avg_cur;
存储函数
存储函数的创建：
delimiter @@
create function 函数名([参数名 参数数据类型[,......]])
returns 函数返回值的数据类型
begin
函数体;
return 语句;
end
delimiter ;
例：delimiter @@
create function name_fn(dno decimal(2))
returns varchar(14)
begin
return(select dname from dept where deptno=dno);
end@@
delimiter ;
调用存储函数：select 函数名([参数值[,......]]);
例：select name_fn(20);
删除存储函数：drop function 函数名;
例：drop function name_fn;
创建存储过程
delimiter @@
create procedure 存储过程名()
begin
函数体;
end@@
delimiter ;
例：delimiter @@
create procedure emp_p()
begin
select ename, job from emp where empno=7369;
end@@
delimiter ;
调用存储过程：call 存储过程名();
例：call emp_p();
存储过程的参数：
delimiter@@
create procedure 存储过程名(
[in/out/inout] 参数1 数据类型,
[in/out/inout] 参数2 数据类型,
......
)
begin
过程体;
end@@
delimiter ;
in参数：输入参数，该参数值由调用者传入，并且只能够被存储过程读取
out参数：输出参数，该参数由存储过程写入，用于存储过程向调用者返回多条信息
inout参数：具有in参数和out参数的特性，在过程中可以读取和写入该类型参数
删除存储过程：drop procedure 存储过程名;
例：drop procedure emp_p;
索引和视图
索引
添加索引：alter table 表名 add unique/index 索引名(列名);
例：添加唯一索引：alter table tbl_name add unique index_name (col_list);
添加普通索引：alter table tbl_name add index index_name (col_list);
创建索引：create index 索引名 on 表名(列名[,列名,......])
查看索引：show index from 表名;
删除索引：drop index 索引名on 表名;
强制使用索引检索：force index(索引名);
例：select * from salaries force index (idx_emp_no) where emp_no = 10005;
视图
创建视图：create view 视图名 as select 语句;
修改视图：alter view 视图名 as select 语句;
删除视图：drop view 视图名[,视图名,......]
约束
主键约束
值唯一且不为空
列级主键约束
方式一：
例：create table employee(
empno declimal(2) primary key,
name varchar(8),
age decimal(3),
deptno decimal(2)
);
方式二：constraint 主键约束名 primary key(列名1,列名2)
例：create table employee(
empno declimal(2),
name varchar(8),
age decimal(3),
deptno decimal(2),
consstraint pk_no primary key (empno)
);
表级主键约束(多个主键)：constraint 主键约束名 primary key(列名1,列名2)
例：create table employee(
empno declimal(2),
name varchar(8),
age decimal(3),
deptno decimal(2),
constraint pk_no primary key (empno, deptno)
);
为已有表内添加主键：alter table 表名 add constraint 主键名 primary key(需要加主键的列);
删除主键：alter table 表名 drop primary key;
唯一约束
值唯一，可以有一个空值
建立唯一约束：constraint 唯一约束名 unique(约束列名)
create table employee(
empno declimal(2) primary key,
name varchar(8),
age decimal(3),
deptno decimal(2),
constraint emp_phone unique(phone)
);
为已有表建立唯一约束：alter table 表名 add constraint 唯一约束名 unique(需要添加唯一约束的列名);
删除唯一约束：alter table 表名 drop index 约束名;
检查约束（无实际作用，不总结）
外键约束：表1列中的数据必须在表2主键列数据之中
添加外键约束：constraint 外键名 foreign key(表1中非主键列) references 表2名(表2主键列)
例：create table department(
deptno decimal(5) primary key,
dept_name varchar(16)
);
create table employee(
empno declimal(2) primary key,
name varchar(8),
age decimal(3),
deptno decimal(2),
constraint fk_id foreign key(deptno) references department(deptno) on update cascade
);
注：on update cascade 指定为级联更新（即相关的表都做出相关的更新）
为已有表添加外键：alter table 表1名 add constraint 外键名 foreign key(表1非主键列) references 表2名(表2主键列) [on delete cascade];
注：on delete cascade 指定为级联删除（即相关的表都做出相关的删除）
删除外键：alter table 表名 drop foreign key 外键名;
触发器
创建触发器：create trigger 触发器名 before/after Insert/delete/update on 表名 for each
now begin <触发体> end;
例：create trigger audit_log after insert on employees_test for each row begin insert into audit values (new.id, new.name); end;
MySQL的安全设置
用户管理
创建登录用户：
create user 用户 identified by 密码 [, 用户 identified by 密码......];
用户格式：用户名@localhost
例：create user teampuser@localhost identified by 'temp';
修改用户密码：set password for 用户 = '新密码';
修改用户名：rename user 旧用户名 to 新用户名;
删除用户：drop user 用户;
权限管理
授予权限：
授予MySQL字段级别权限：grant 权限名称 (列名[,列名,......])[,权限名称 (列名[,列名,......]),......] on table 数据库名.表名或视图 to 用户[, 用户,......] [with grant option]
授予MySQL表级别权限：grant 权限名称[,权限名称,......] on table 数据库名.表名或视图 to 用户[, 用户,......] [with grant option]
授予MySQL存储程序级别权限：grant 权限名称[,权限名称,......] on function/procedure 数据库名.函数名/数据库名.存储过程名 to 用户[, 用户,......] [with grant option]
授予MySQL数据库级别权限：grant 权限名称[,权限名称,......] on 数据库名.* to 用户[, 用户,......] [with grant option]
授予MySQL服务器管理员级别权限：grant 权限名称[,权限名称,......] on *.* to 用户[, 用户,......] [with grant option]
MySQL数据库中
1、Column_priv表中记录了用户MySQL字段级别权限的验证信息
2、Table_priv表中记录了用户MySQL表级别权限的验证信息
3、Proc_priv表中记录了用户MySQL存储程序级别权限的验证信息
4、db表中记录了用户MySQL数据库级别权限的验证信息
5、user表中记录了用户MySQL服务器管理员级别权限的验证信息
撤销权限
撤销指定权限revoke 权限名称 (列名[,列名,......])[,权限名称 (列名[,列名,......]),......] on *.*/数据库名.*/数据库名.表名或视图 from 用户[, 用户,......]
撤销所有权限revoke all privileges, grant option from 用户[,用户,......]
角色管理
角色格式：'角色名'@'主机名'
创建角色：create role 角色;
授予角色权限：grant 权限名称[,权限名称,......] on 数据库名.* to 角色[,角色,......]
授予用户角色：grant 角色[,角色,......] to 用户[,用户,......]
注：用户在使用角色权限前必须先激活角色
激活指令：set global activate_all_roles_on_login=on;
撤销用户角色：revoke 角色[,角色,......] from 用户[,用户,......]
删除角色：drop role 角色[,角色,......]
