数据库的学习和应用

数据库

1.SQL语言分类

• 数据查询语言DQL:select、where、order by、group by、having
• 数据定义语言DDL:create、alter、drop
• 数据操作语言DML：insert、update、delete
• 事务处理语言TPL:commit、rollback
• 数据控制语言DCL:grant、revoke

数据查询语言DQL(Data Query Lanuage)

-- 查询表中所有信息，开发人员避免使用，效率低下
select * from 表名;

-- 投影查询，查询某列信息
select 字段名1,字段名2 from 表名;

-- 列运算，影响的是显示结果，不影响数据本身 运算符（+ - * / %）
select 数字类型字段 + 数值 from 表名

-- 查询结果去重
select distinct 字段列表 from 表名

-- 字段别名
select 字段1 as 别名，字段2 别名 from 表名

-- 单列排序 asc 升序 desc降序 默认升序 注意字段的类型
select 字段列表 from 表名 order by 字段 [asc] [desc]

-- 多列排序 asc 升序 desc降序 默认升序
select 字段列表 from 表名 order by 字段1，字段2 [asc] [desc]

-- 条件查询 条件为具体需求，比如名字等谁
select 字段列表 from 表名 where 条件

-- 条件查询:逻辑判断
select 字段列表 and er not(!=) 一般用于连接多个判断条件
SELECT 字段列表 FROM 表名 WHERE 字段名 = 字段值 AND 字段名 = 字段值;
SELECT 字段列表 FROM 表名 WHERE 字段名 = 字段值 OR 字段名 = 字段值;
SELECT 字段列表 FROM 表名 WHERE NOT 字段名 = 字段值 AND 字段名 = 字段值

-- 条件查询:不等值判断判断 等值判断=
select 字段列表 from 表名 where 字段 < 数值
select 字段列表 from 表名 where 字段 <= 数值
select 字段列表 from 表名 where 字段 > 数值
select 字段列表 from 表名 where 字段 >= 数值
select 字段列表 from 表名 where 字段 != 数值
select 字段列表 from 表名 where 字段 <> 数值
select 字段列表 from 表名 where 字段 = 数值

-- 条件查询：区间判断
select 字段列表 from 表名 where 字段名 between 数字 and 数字

-- null值判断
select 字段列表 from 表名 where 字段名 is null;

-- 非空判断
select 字段列表 from 表名 where 字段名 is null;

-- 枚举判断 in
select 字段列表 from 表名 where 字段名 in(数值1,数值2,数值3);

-- 模糊查询 like 'S%' 以什么开头  ‘S____’一个下划线为一个长度  '%S%' 包含某个字符
select 字段列表 from 表名 where 字段名 like 'S%';

-- 分支结构查询  
select 字段列表,
case 
     when 条件列的条件 then 返回值（比如'A'）
      when 条件列的条件 then 返回值（比如'B'）
       when 条件列的条件 then 返回值（比如'C'）
       else 返回值
 end as 别名
-- 日期查询
select now(); -- 当前系统日期时间
select CURRENT_DATE();-- 当前日期
select CURRENT_TIME(); -- 当前时间

-- 当前年、月、日
select YEAR(now());
select MONTH(now());
select DAY(now());

 -- 当前日期添加两天interval 数量 day
 ADDATE(now(),INTERVAL 2 DAY);

 -- 计算时间
 select DATEDIFF(NOW(),ADDATE(now(),INTERVAL 2 DAY));
 
-- 1.查询指定时间列的数据 year
select 参数列表 from 表名 where YEAR(时间列)=具体时间（或年，或月，或日）
-- 查询什么的天数 datediff
select 参数列表  DATEDIFF(now(),时间列)  from 表名;

-- 列拼接 concat
select concat(参数列表) as 别名,列名 from 表名;

-- 替换列名
select 参数列表，replace(列名，列名中要被换字符，要换的字符),列名 from 表名

-- 小写，大写，字符串截取
select lower("hellow");
select upper("hellow");
select substring("hellow",4,5);
select substring("hellow",4,5);

--聚合函数SUM()、AVG()、COUNT() max() min()
select count(字段) from 表名;  -- 自动过滤字段空的行
select count(*) from 表名;    //统计行数
select max(字段（数值）) from 表名；
select min(字段（数值）) from 表名；
select sum(字段（数值）) from 表名； //累加求和
select avg(字段（数值）) from 表名； //平均值

-- 分组查询  group by

select 字段列表 from 表名 where 条件 group by 分组依据（列）;

-- 对统计结果再进行赛选 having 
select 字段列表,count(列名) from 表名 where 条件 group by 分组依据（列）having count(列名) > ?;    -- (< >= <= ! <>)

-- 限定结果 limil （偏移量）跳过行数，显示行数
select 字段列表 from 表名 0，5；

-- 编写顺序
from > where >  group by  > hanving > order by >limit

数据操作语言DML（Data Maninputlation language）：insert、update、delete

-- insert
-- 单行插入
insert into 表名(Colum1,Colum2……，Column) values(v1,v2……，vn);
-- 批量插入
insert into 表名(Colum1,Colum2……，Column) values(v1,v2……，vn),(v1,v2……，vn);

-- update 修改
update 表 set 字段1 = 值1，字段2 = 值2,…… where 条件; 

-- delete 
delete from 表 where 条件；

数据定义语言DDL:create、alter、drop

-- create
-- 创建库
create database 数据库名;

-- 表创建
create table 表名(
列名 数据类型[约束],
列名 数据类型[约束],
    ……
列名 数据类型[约束]
)

-- drop
-- 删除库
drop database 数据库名;
-- 删除表
drop table 表名

-- alter
-- 修改表列 类型[约束]
alter table 表名 modify 字段名 类型[约束];

-- 修改列名
alter table 表名 change 原列名 新列名 新列类型[约束];

-- 修改列 删除列
alter table 表名 drop column 列名；

-- 修改表名
alter table 表名 rename 新表名

-- 对表字段的操作
alter table drop column 字段名
alter table add column 字段名 数据类型[约束]
alter table modify 列名 数据类型[约束]
alter table change 原列名 新列名 数据类型[约束]

-- 添加表主键
alter table 表名 add primary key 字段
-- 添加复合主键
alter table 表名 add primary key (字段1,字段2)
-- 添加外键
alter table 表名 add foreign key fk_从表_主表 (字段) references 主表(字段) 

事务处理语言TPL:commit、rollback

事务概念：
事务是一个原子操作。是一个最小执行单元。可以用一个或多个SQL语句组成，在同一个事物当中，所有的sql语句执行成功时，整个事物成功，有一个SQL语句执行失败，整个事物执行失败
事务边界：

• 事物的开始，执行一条DML语句，上一个事物结束后，有输入了一条DML语句，机事物的开始
• 结束
  1. 提交：
     • 显示提交：commit
     • 隐式提交：一条创建、删除的语句，正常退出（客户端退出连接）
  2. 回滚
     • 显示回滚：rollback
     • 隐式回滚：非正常退出，执行创建、删除的语句，但是失败了，会为这个无效的语句执行回滚

事务的特性

• 原子性
  标识事务的所有操作是一个整体，要么全部成功，要么全部失败
• 一致性
  表示一个事务有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态
• 隔离性
  事务查看数据操作时数据所处的状态，要么另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态事务不会查看中间状态的数据

1.读未提交 ：隔离性低，对数据可见性高，数据一变化就可以读取到，可能会出现脏读

2.读已提交 ：只能读取到提交后的数据，默认隔离级别

3.可重复读 ：保证一个事务中多次读取，读取到的数据是一致的
不可重复读现象：一个事务中两次读取的数据不一致

4.序列化读：对读取数据操作上上锁，统一时间只允许一个事务读取数据，和同步代码块一样效率低。

• 持久性：持久性事务完成之后，他对于系统的影响是永久性的

-- 模拟转账
start transation;
修改语句 money-1000
修改语句 money+1000

-- 成功执行commit
commit;
-- 失败执行rollback
rollback;

数据控制语言DCL:grant、revoke

-- 远程访问  with grant option可以把权限分给别人
grant all privileges  on * . *To 'root' @ '%' identified by 密码 with grant option; 

-- 创建用户  @'%'该用户可以拿任意IP访问登录
create user 用户名[@'%']  identified by 密码;

-- 授权  *代表所有表
grant all on 数据库.表 to 用户名;
-- 撤销权限
revoke all on  数据库.表 from 用户名;

-- 删除用户
drop user 用户名;

-- 查询 [增加]权限
grant select[insert] on 数据库.表 to 用户名;

视图创建

-- 视图本身不存数据，只是对数据进行展示
create view v_视图名 as 虚表;
-- 修改视图
alter view 视图名 as 虚表