操作数据库中表结构,表中字段
-
数据库操作
create database 数据库名;
use 数据库名;
select database();
drop database 数据库名;
show databases;
- 表操作
show tables;
create table 表名(字段 字段类型);
desc 表名;
show create table 表名;
alter table 表名 add/modify/change/drop/rename to
drop table 表名;
-
DML
完成数据的增删改
- 添加
insert into 表名 (字段1,字段2,...) values (值1,值2,...);
- 修改
update 表名 set 字段1=值1,字段2=值2 [where 条件];
- 删除
delete from 表名 [where 条件];
-
DQL
基本查询
- 查询多个字段
select 字段1,字段2,字段3...from 表名;
select * from 表名;
- 设置别名
select 字段1 [AS 别名1],字段2 [AS 别名]... from 表名;
- 去除重复记录
select distinct 字段列表 from 表名;
条件查询
select 字段列表 from 表名 where 条件列表;
聚合函数
将一列数据作整体,进行纵向计算
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
select 聚合函数(字段列表) from 表名;
分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组过滤条件];
排序查询
select 字段列表 from 表名 order by 字段1 排序方式 1,字段2 排序方式2;
asc:升序
desc:降序
分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数;
-
DCL
数据库控制语言,用来管理数据库用户,控制数据库的访问权限
管理用户
- 查询用户
use mysql;
select * from user;
- 创建用户
create user '用户名'@‘主机名’ identified by ‘密码’;
- 修改用户密码
alter user ‘用户名’@‘主机名’ identified with mysql_native_password by '新密码';
- 删除用户
drop user '用户名'@‘主机名’;
localhost 只能够在当前主机访问
% 可以在任意主机访问该数据库
权限 说明
all,all privileges 所有权限
select 查询权限
insert 插入权限
update 修改数据
delete 删除数据
alter 修改表
drop 删除数据库/表/视图
create 创建数据库/表/视图
- 查询权限
show grants for ‘用户名’@‘主机名’;
- 授予权限
grant 权限列表 on 数据库.表名 to '用户名'@‘主机名’;
- 撤销权限
revoke 权限列表 on 数据库.表名 from '用户名'@‘主机名’;
多个权限可用逗号隔开
可用*通配
-
函数
字符串函数
concat (s1,s2...sn) 字符串拼接
lower (str) 转小写
upper(str) 转大写
lpad (str,n,pad) 左填充,用pad对str左填充达到n个
rpad(str,n,pad) 右填充
trim (str) 去掉头部和尾部空格
substring(str,start,len) 从strat起len个字符
select 函数(参数);
函数
ceil(x) 向上取整
floor(x) 向下取整
mod(x,y) 返回x/y的摸
rand() 返回0~1内随机数
round(x,y) 求x的四舍五入的值,保留y位小数
日期函数
curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前日期和时间
year(date) 获取指定date的年份
month(date) 月份
day(date) 日期
date_add(date,interval exper type) 返回一个日期/时间值加上一个时间间隔exper后的时间值
datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数
流程函数
if(values,t,f) 如果value为true,则返回t,否则返回f
ifnull(value 1,value 2) 如果value 1不为空,返回value1,否则返回value2
case when [vall] then [res1] ...else [default] end 如果vall为true,返回res1,...否则返回default
case [expr] when [vall] then [res1]...else[default] end 如果expr的值等于vall,返回res1,否则返回default
-
约束
非空约束 限制该字段的数据不为null not null
唯一约束 保证该字段唯一 unique
主键约束 主键是一行数据唯一标识,非空且唯一 primary key
默认约束 未指定该字段值,则用默认值 default
检查约束 满足一个条件 check
外键约束 用来让两张表数据之间建立连接 foreign key
- 添加外键
create table 表名(
字段名 数据类型
...
[constraint] [外键名称] foreign key (外键字段名)references 主表(主表列名)
);
alter table 表名 add constraint 外键名称 foreign key(外键字段名)references 主表(主表列名);
- 删除外键
alter table 表名 drop foreign key 外键名称;
外键约束
no action 当父表删/更新对应记录,实现检查是否对应外键,如果有则不允许删/更
restrict 同上
cascade 如果有,也删/更子表中记录
set null 有,则子表外键值为null
set default 默认值
- 删除/更新外键
alter table 表名 add constraint 外键名称 foreign key(文件字段)references 主表名(主表字段名) on update 外键约束 on delete 外键约束;
-
连接查询
内连接
隐式内连接:
select 字段列表 from 表1,表2 where 条件...;
显式内连接:
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
外连接
左外连接:
select 字段列表 from 表1 left[outer] join 表2 on 条件...;
查询左表所有数据包含表1和表2交集数据
右外连接:
selcet 字段列表 from 表1 right[outer] join 表2 on 条件...;
自连接
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
-
联合查询
select 字段列表 from 表A...;
union [all]
select 字段列表 from 表B...;
列表的列数和字段类型必须一致
union all会将全部数据合并
union 去重后合并
-
子查询
标量子查询
select * from t1 where column1=(select column1 from t2);
列子查询
操作符 描述
in 在指定范围内,多选一
not in 不在范围内
any 子查询返回,有一个满足即可
some 同上
all 子查询返回所有值必须满足
eg:select * from emp where dept_id in(select id from dept where name =‘销售部’or name=”‘市场部’);
行子查询
eg:select * from emp where(salary,managerid)=(select salary,managerid from emp where name=‘张愿意’);
表子查询
eg:select * from emp where (job,salary) in (select job,salary from emp where name='路权克'or name =‘宋远桥’);
-
事务
是一组操作的集合,要么同时成功,要么同时失败
事务操作
- 查看/设置事务提交方式
select @@autocommit;
set @@autocommit=0;
0是手动提交
1是自动提交
- 提交事务
commit;
- 回滚事务
rollback;
- 开启事务
strat transaction 或begain;
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
read uncommitted | √ | √ | √ |
read committed | × | √ | √ |
repeatable read(默认) | × | × | √ |
serializable | × | × | × |
- 查看事务隔离级别
select @@transaction_isolation;
- 设置事务隔离级别
set [session/global] transaction isolation level {read uncommitted|read committed|repeatable read|serializable};