目录
数据库的基本操作
创建数据库: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 角色[,角色,......]