数据库基础常用操作

一.数据库常用命令

1.数据库操作DDL常用的语句

create databases 数据库名
show databases
drop databases
use 数据 库名

2.DDL表结构的操作

show tables
desc 表名
添加字段 
   alter table 表名 add 字段名 类型（长度）   
修改数据类型 
    alter table 表名 modfiy 字段名 新数据类型
修改字段
   alter table 表名 change 旧字段名 新字段名 类型（长度） 
删除字段 
   alter table 表名 drop 字段名
修改表名 
   alter table 表名 rename to 新表名
删除表
   drop  table 表名 

3.DML 增删改查

1.>插入语句

INSERT INTO 表名 (字段名,字段名)values(值1,值2)
如果进行批量插入的话 可忽略字段名直接 
insert into 表名 value(,,,),(,,)

2.>修改数据

update 表名 set 字段名1=值1,字段名2=值2 where 条件

3.>删除数据

删除某列数据 :
  delete from 数据库名 where 条件
删除所有数据 :
  delete from 数据库名 

4.DQL查询数据库的数据

1.>常用的几个字段

编写顺序
select from  where
group by 分组字段列表（分组查询）
having 分组后条件列表
order by 排序字段列表（排序查询）
limit 分页参数

执行顺序  
from 表名 > where条件 > group by分组 >
having分组后条件列表 > select  >order by排序  >分页limit

聚合函数（count max min avg sum）NULL值 不参与聚合函数运算
as '' 起别名   as可以省略
distinct 去重 放在字段名前 
is nill 为空  is not nil不为空
！= 不等于 也可以写成 <>
and 可以写成&& 但正常用and 
多选一的话 用in字段  in(,,,)

like模糊查询  like '__'  一个_表示一个字段
   比如查询名字为两个字的员工信息  如上
   如果查身份证最后一位是x like '%X' %代表前面是多少无所谓  
   如果查第一位是X  like 'X%'
   
count 注意 一般用于select后边 如果查全表直接count(*)即可

count 和 sum的区别  count用来统计数量 sum是求和                     

2.>分组查询

select 字段 from 表名  where条件 groupby 分组字段名  having分组后过滤条件

执行顺序 where > 聚合函数 > having
分组之前过滤用where 不满足where 不参与分组 
where 不能对聚合函数进行判断 而having可以

example
在count之前加字段名是为了方便区分,如果不加虽然已经得出来数量 但是无法区分 

3>.排序查询

select 字段 from 表名 order by 字段1 排序方式 ,字段2 排序方式2

ASC  升序 (默认值)
DESC 降序
多字段排序 当第一个字段相同时 才会按照第二个字段排序
 
example
根据年龄排序 如果年龄相同 则按照入职日期进行降序
select *from emp order by age asc ,enteydate desc ;

4>.分页查询

select * from 表名 limit 起始索引,查询记录数

-limit放在最后 
-起始索引从0开始 起始索引=(查询页码-1)*每页显示记录数
-如果查询的是第一页 起始索引可以省略 直接简写 limit 记录数

二.DCL用户权限

1>.用户管理

1.查询用户
use mysql
select * from mysql

2.创建用户 
create user '用户名'@'主机名'  identified by '密码'

主机名-本机访问 localhost 
任意主机访问 用 % 即可

3.修改用户名密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'

4.删除用户
drop user '用户名'@'主机名'

2>.权限控制

常用的权限
ALL,ALL Privileges 所有权限
SELECT  Insert  UPDATE  DELETE  ALTER    Drop         CREATE 
                                 修改表   删除(表/库)    创建(表/库)
    
1.查询权限
show grants for '用户名'@'主机名'       usage 表示只能连接
     
2.授予权限
grant  权限列表(ALL) on  数据库名.表名 to '用户名'@'主机名'   授权时 数据库名和表名都可以用* 来通配
    
3.撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'

三.函数

1>.字符串函数

UPPER(列|字符串)	将字符串每个字符转为大写
LOWER(列|字符串)	将字符串每个字符转为小写
CONCAT(str1,str2,. . . )	将所有字符串连接成一个字符串
REPLACE(列|字符串,旧字符串,新字符串)	使用新字符串替换旧字符串
LENGTH(列|字符串)	求字符串长度
TRIM(str)去掉字符串头部和尾部的空格 但不包括中间的空格
LPAD(str,n,pad)左填充 用字符串pad对str的左边进行填充 达到n个字符串的长度
RPAD(str,n,pad)右填充 同上
SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串,start指的是索引从1开始

左填充应用 : 将员工的编号改为5位,不足的在前面填0

update emp set workno=lpad(workno,5,'0')
select  SUBSTRING('125656dd',2,5);返回的是25656
注意字符串都用单引号  ''

2>.数值函数

cell()向上取整  但只要小数不是0就直接是下一个 (1.1取整是2)
floor()向下取整  1.9取整是1
mod(x,y) 取模 (取余数)
Rand() 返回0-1内的随机数
Round(x,y)参数x的四舍五入的值 保留y位小数

取一个随机6位数
select round(rand()*10000000,0) 但是有bug 当是0.019255时会是5位
解决方法就是填充 不够时补0
select LPAD(round(rand()*10000000,0),6,0)

3>.日期函数

select curdate();日期

select CURTIME();时间

select NOW();日期和时间

Year(date) Month(date) DAY(now()) 获取当前的年月日

DATE_ADD(date ,INTERVAL exptr(数量) type )一个日期加上一个间隔的时间值

DATEDIFF(date1,date2) 返回两个时间间隔的天数 ,前面减去后面

例子:
select date_add(now(),INTERVAL 70 DAY)
select datediff('2021-12-01','2021-10-1')

查询员工的入职时间,并排序 
select name,datediff(current_date(),enjoydate) 'times' from emp order by times

4>.流程函数

实现筛选 从而提高语句的效率
if(value,t,f)value 为true 返回t 否则返回f

ifNULL(value1,value2)value1不为空 返回value1,否则返回value2

case when [val1] then [res1] else[default ] END val1为true 返回res1 否则返回default默认值

case [expr] when [val1] then [res1] else[default ] END  expr的值等于val1 返回res1 否则返回default默认值

ifnull('','default') 返回的是空串
ifnull(null,default )返回的是默认

例子:
当员工地址是北京或者上海的话返回一线城市,其他城市则返回二线城市
select name, (case  woekadress when '北京' then '一线' when '上海' then '一线' else '二线' end) '工作地址'  from emp;

四.约束和多表查询

1>.常见的字段

1.非空约束  not null 
2.唯一约束  unique
3.主键约束  primary key
4.外键约束  foreign key 
默认约束 default '默认值'
检查约束  check(条件) 比如年龄 age int check(age>0&&age<120)

在创建表时添加外键
constraint  外键名称 foreign key (字段名) references 主表(主键列名)

添加外键
alter table 表名 add CONSTRAINT  外键名称 foreign key (外键字段名)references 主表(主表列名)
    
删除外键
alter table 表名 drop foreign key 外键名称

2>.多表查询

-多表关系 一对多 一对一 多对多
-多表查询概述
-内连接  相当于查询AB交集的两部分
-外连接  
  左连接  查询左表数据以及两张表交集的地方
  右连接  查询右表数据以及两张表交集的地方
-自连接   当前表与自身的连接查询 自连接必须使用的是表别名
-子查询

1.一对多  再多的一方建立外键 指向一的一方的主键(部门和员工)
2.多对多  (学生选课)建立一张中间表 中间表包含两个外键 分别关联双方主键
3.一对一 用于表拆分 用户和用户详情 (在任意一方加入外键 关联另一方的主键,并且设置为唯一的)
        必须加唯一的约束,否则就与一对多相同

1.内连接
(如果表名较长的话,可以起别名 from 表名后加别名)
隐式
selcet * from 表1,表2 where 条件
显式
select * from 表1 inner join 表2 on 条件

2.外连接
select * from 表1 left join 表2 on 条件
select * from 表1 right join 表2 on 条件
重点理解
左外和右外的区别
左外的话左边的表全部信息加右边的表  
from 左表  left join 右表

右外的话就是
from 左表  right join 右表 或者(from 右表 left join 左表)


3.自查询
查询员工极其领导对应名字

slect a.name b.name from 表1 a 表2 b where a.id=b.manager.id

3>.联合查询

select 字段列表 from 表1
union all 
select 字段列表 from 表2

字段列表数量要保持一致
union all 表示全部数据合并在一起 union 表示对合并后的数据去重

4>.子查询

1.标量子查询 返回结果是单个值
2.列子查询 any 表示任意一个满足即可 all表示满足全部

五 .事务

要么同时成功要么同时失败

1>.事务操作

查看/设置事务提交方式
select @@autocommit
set  @@autocommit=0    默认是1 手动是0


或者采用

begin/start transaction 
commit 
rollback


事务的四大特性
ACID 
原子性  事务不可分割的最小单元 要么全部成功 要么全部失败
一致性  事务完成时 必须使所有的数据都保持一致
隔离性   保证事务不受并发操作影响在独立的环境下运行
持久性   事务一旦提交或者回滚 它对数据库中的数据的改变是永久的

2>.并发事务引发的问题

脏读 ：一个事务读到另一个事务未提交的数据

不可重复读 ：一个事务先后读同一条记录但两次读取的数据不一致

幻读：一个事务按条件查询时 没有对应的数据 但在插入数据时 发现数据已经存在了

3>.事务隔离级别

隔离级别依次升高 但性能会变差

读未提交 > 读已提交 > 可重复读(mysql默认)Repeatable Read > 串行化(Serializable)

查看事务隔离级别
select @@transaction_isolation

设置事务隔离级别
set session transaction isolation level (隔离级别 )

在可串行化后 解决了幻读,当另外一个事务操作时会一直阻塞
如下图所示 x表示解决了