[大数据]sql练习

# sql

36 ：从一个表中复制想要的字段到另一个新创建的表中

思路：

创建一个表，并且将另一个表中的目标字段导入该表

实例：

create table if not exists actor_name
(
    first_name varchar(45) not null,
    last_name varchar(45) not null
);
insert into actor_name
select first_name,last_name
from actor;

扩展：

1.常规创建表
create table if not exists 目标表
2,复制表格
create 目标表格 like 来源表
3，将table1 的部分拿来创建 table2

1，关键字

1，view

? mysql视图表（view）是一种虚拟存在的表，它和真实的表一样，但是它依赖于真实的表，即它做了一些筛选，只展示一个表中它想要的字段，或者只展示多个表中它想要的字段。但是当真实的表发生变化的时候，它也会发生变化。

1,创建视图

create view <视图名> as <select 语句>

2，alter

当我们需要修改数据表名或者删除数据表字段时，就需要用到 mysql 的 alter 命令

基本语法：

删除字段

alter table table_name drop i(字段);

添加字段：

alter table table _name add i(字段) int(类型);

示例：

增加字段：

alter table actor add create_date datetime not null 
default '2020-10-01 00:00:00';

3,drop

删除字段

4，add

增加字段

5，trigger

触发器；

基本语法：

create trigger trigger_name
trigger_time trigger_event on tbl_name
for each row
trigger_stmt

• trigger_name ：触发器名称
• trigger_time ：触发时机，取值为before 或者 after
• trigger_event：触发事件，取值为 insert ，update，delete
• trigger_stmt：触发器程序体，可以是一句sql语句，或者用 begin 和 end 包含的多条语句，每条语句结束要用分号结尾。

new 和 old 详情：

使用方法：
new.columnName(数据库的某一列	)

用来表示触发器所在表中，触发了触发器的哪一行数据

1，在insert 中，new 用来表示将要（before）或者已经（after）插入的新数据
2，在update 中，old用来表示将要或者已经被修改的原数据，new 表示将要或者已经修改为的新数据
3，在delete中，old用来表示将要或者已经被删除的原数据；

实例：

构造一个触发器：

create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end

6，update

更新

输出：结果是受影响行数

语法：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

示例：

update titles_test
set to_date = null ,from_date = '2001-01-01' where 
to_date = '9999-01-01'

两个表联合更新某个字段

update update salaries as s join emp_bonus as e on s.emp_no = e.emp_no
set salary = salary*1.1
where to_date = '9999-01-01'

7,alter

当修改数据库表名或者修改数据库表字段时，使用alter

基本语法:

alter table table_name

实例:

修改表名

alter table table_name rename as/to new_table_name

创建外键

alter table table_name add constraint foreign key table_column references table_name2(table_column)

8,子查询

where in/not in

where and in/not in

实例：

两次查询

update salaries set salary = salary*1.1
where to_date = '9999-01-01'
and salaries.emp_no in (select emp_no from emp_bonus)

9,join on

? 分为：左连接，右连接，内连接

两个乃至多个表联合

基本语法:

table1 as t1 join table2 as t2 on t1.column = t2.column

实例：

两个表关联更改字段

update salaries as s join emp_bonus as e on s.emp_no = e.emp_no
set salary = salary*1.1
where to_date = '9999-01-01'

获取有奖金的员工的信息，以及奖金

select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,(s.salary * eb.btype * 0.1)as bonus
from employees as e join salaries as s on e.emp_no = s.emp_no
join emp_bonus as eb on e.emp_no = eb.emp_no
where s.to_date = '9999-01-01'

求最近用户登录的时间和使用的设备

select u.name as u_n,c.name as c_n,l.date as date
from login as l join (select user_id,max(date) as mdate from login group by user_id) as l1
on l.date = l1.mdate
and l.user_id = l1.user_id
inner join user as u on l.user_id = u.id
inner join client as c on l.client_id = c.id
order by u_n

select u.name as u_n,c.name as c_n,l.date
from login as l
join user as u on l.user_id = u.id
join client as c on l.client_id = c.id
where (l.user_id , l.date) in (
select user_id , max(date) from login group by user_id
)
order by u.name

10,limit

limit 分页

基本语法：

limit x,y;
x是偏移量
y要获取的数据量

实例:

11,exists,in

exists：判断是否存在某种条件的记录，如果存在这种记录就返回true，否则则返回false，相当于in

not exsits 相当于 not in

实例：

查找未分配部门的员工信息

exists

select *
from employees as e
where   not exists(select emp_no from dept_emp as d where d.emp_no = e.emp_no )

in

select *
from employees as e
where emp_no not in(select emp_no from dept_emp as d)

12,case when then else end

分类条件

case when 条件1 then 结果1
when 条件2 then 结果2
else 结果3
end 

case 字段
when 条件1 then 结果1
when 条件2 then 结果2
else 结果3 end 

注意：end 后面加字段，就是case的别名

实例:

select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,
case when eb.btype = 1 then s.salary * 0.1
when eb.btype = 2 then s.salary * 0.2
else s.salary * 0.3
end as bonus
from emp_bonus as eb,employees as e,salaries as s
where eb.emp_no = e.emp_no
and s.emp_no = e.emp_no
and s.to_date = '9999-01-01'
order by e.emp_no

计算某日的失败概率

select e.date,round(
sum(
case e.type when 'no_completed' then 1 else 0 end
) * 1.0 / count(e.type),3
)as p
from email as e
join user as u1 on (e.send_id = u1.id and u1.is_blacklist = 0)
join user as u2 on (e.receive_id = u2.id and u2.is_blacklist = 0)
group by e.date
order by e.date

求每天新登录的人数

select distinct date,sum(
case when (user_id,date) in (
select user_id,min(date)
    from login 
    group by user_id
)
    then 1 else 0 end
)
from login 
group by date
order by date

13，group by having

分组，having是 group by 的 where

实例：

找到总数大于等于3的

select number
from grade
group by number
having count(number)>=3

14.explain

15.like

模糊查询

语法

数据库字段 like concat(concat('%',#{params}),'%')

数据库字段 like '%${params}%'

数据库字段 like "%"+#{params}+"%"

示例：

16.union

? 作用：将两个select的结果集结合：分为 union和 union all，union会自动去重，

? 另外：union 结果集中的列名总是等于union中第一个select语句中的列名。

示例

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

2，聚合函数

窗口函数也称OLAP函数（online analytical processing),意思对数据库进行实时分析。

窗口函数分为:

1. 聚合函数
2. 专用函数

聚合函数

sum,min,max,avg,count,

专用函数

rank,dense_rank,row_number

1. rank函数

计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

例如：有3条记录并列第一时：1，1，1，4

2. dense_rank函数

不会跳过之后的为此：1，1，1，2

3. row_number函数

赋予唯一的连续位次：1，2，3，4

rank

desc_rank

select id,number,dense_rank() over(order by number desc)as t_rank
from passing_number
order by t_rank

select id,number,dense_rank() over(order by number desc)as t_rank
from passing_number

select g.id,l.name,g.score
from language as l join 
(select id,language_id,score,dense_rank() over(partition by language_id order by score desc) as c_rank
 from grade) as g
 on l.id = g.language_id
 where c_rank <= 2
 order by l.name ,g.score desc,g.id

1，min()

拿到字段的最小值

语法：

select min(字段)

示例：

1,去除重复记录，只保留最小的记录

delete from titles_test
where id not in(
select * from (
select min(id)
    from titles_test
    group by emp_no
)as a
)

注意点：mysql中不允许在子查询的同时删除表数据（不能一边查，一边把表给删了）

2，replace（）

replace函数中包含三个参数，第一个参数是某字段的名称，第二个参数是该字段的需要被修改的值，第三个参数为该字段修改后的值

示例：

update titles_test set
emp_no = replace(emp_no,10001,10005)
where id = 5

2，concat

字段连接

基本语法：

concat(column1,"连接字符",column2)

实例：

last_name和first_name,中间用'

update salaries as s join emp_bonus as e on s.emp_no = e.emp_no
set salary = salary*1.1
where to_date = '9999-01-01'

3,Trim

去空格

基本语法：

RTrim():去除字符串右端空格
LTrim():去除字符串左端空格
Trim():去除字符串两端的空格
Trim(column)

3,length,char_length

统计字符串或者字段长度

基本语法：

length("字符串")
length(columb_name)

1. utf-8编码下，一个汉字算三个字符，数字和字母算一个字符
2. 其他编码下，一个汉字算一个字符，数字和字母算一个字符

char_length("字符串")
char_length(column_name)

1. 一个多字节符（例如，汉字）算作一个单字符
2. 汉字，字母，数字都算是一个字符
3. 任何编码下，多字节字符都算是一个字符

实例：

统计字符串:10,A,B中,出现的次数

select (length("10,A,B")-length(replace("10,A,B",",","")))
as cnt

4，right，left，substr，substring

1. right(str,n),从右开始分割，截取n位
2. left(str,n),从左开始分割，截取n位
3. substr(str,pos),从哪一位（pos）开始
4. substr(str,pos,n),从哪一位（pos）开始，截取n位

实例:

取后几位排序

select first_name
from employees
order by  substr(first_name,-2,2)

select first_name
from employees
order by  right(first_name,2)

5,group_concat

group_concat() 函数将group by 产生的分组中的值连接起来，返回一个字符串结果。

group_concat([distinct]要连接的字段，[order by 排序字段 asc/desc][separator '分隔符'])
distinct去重
order by 排序
separator 默认逗号

实例：

select dept_no,group_concat(emp_no) as employees
from dept_emp group by dept_no

6,sum

sum求和

或者分组求和再排序

实例：

select (sum(salary)-max(salary)-min(salary))/(count(salary)-2) as avg_salary
from salaries
where to_date = '9999-01-01'

sum()over()：

sum()over(order by column_name)：求累计和/汇总 语句结构

实例：

求截止到指定日期前的员工工资总和

select s.emp_no,s.salary,(sum(s.salary) over (order by s.emp_no))as running_total
from salaries as s 
where s.to_date = '9999-01-01'

计数：

select distinct date,sum(
case when (user_id,date) in (select user_id,min(date) from login group by user_id )
    then 1 else 0 end
)
from login 
group by date
order by date

求截止到某天的刷题量

select u.name as u_n,p.date,p.ps_num
from (select user_id,date,sum(number) over(partition by user_id order by date)as ps_num from passing_number)as p
join user as u on p.user_id = u.id
order by date ,name

最差是第几名

select grade,sum(number) over( order by grade) as t_rank
from class_grade
order by grade

7，round

round（x，y）四舍五入，对x四舍五入，y是保留小数点后几位

实例：

计算某日失败的概率，保留到小数点后3位

select e.date,round(
sum(
case e.type when 'no_completed' then 1 else 0 end
) * 1.0 / count(e.type),3
)as p
from email as e
join user as u1 on (e.send_id = u1.id and u1.is_blacklist = 0)
join user as u2 on (e.receive_id = u2.id and u2.is_blacklist = 0)
group by e.date
order by e.date

8,max()

求最大值

实例：

求最近用户登录的日期以及使用的设备

select u.name as u_n,c.name as c_n,l.date as date
from login as l join (select user_id,max(date) as mdate from login group by user_id) as l1
on l.date = l1.mdate
and l.user_id = l1.user_id
inner join user as u on l.user_id = u.id
inner join client as c on l.client_id = c.id
order by u_n

9，count（）

oracle 分组求每个分组的数量

• 分组逐个求和用sum
• 分组统计用count

语法：

count(*) over(group by 分组字段)

示例：

select  DISTINCT TERM,
        COUNT(TERM) OVER (PARTITION BY TERM) as termNum
        FROM SYS_COM_DEAL
        WHERE TO_CHAR(TO_DATE(SUBSTR(DEAL_TIME,1,10), 'yyyy-MM-dd'),'yyyy-MM-dd') = TO_CHAR(SYSDATE, 'yyyy-MM-dd')

9，date_add()

求下一天函数

语法：

date_add(date,interval number day)

实例：

求连续登录的概率

select round(count(distinct user_id)*1.0/(select 
                                count(distinct user_id) from login),3)
from login
where (user_id,date) in
(select user_id,date_add(min(date),interval 1 day) from login group by user_id)

查询连续

select a.id,a.user_id,a.product_name,a.status,a.client_id,a.date
from 
(
select *,count(user_id) over(partition by user_id) as num from order_info as o
    where 
    date > "2025-10-15" and product_name in ("C++","Python","Java") and status = "completed"
    ) as a
where 
a.num > 1 
    order by a.id

10,row_number() over （partition by ）分组聚合

分组聚合：就是先分组再排序，还可以加排名；

如果不想分组同时再去重排名也可以。

11.ifnull

基本语法：

ifnull(exp,value)

用于判断第一个表达式是否为空，如果为空就返回第二个参数；如果不为空就返回第一个表达式的值

示例

select
IFNULL((select distinct Salary
from Employee
order by Salary desc
limit 1,1)
,NULL) as SecondHighestSalary 

12.date_format

日期函数格式转换

基本语法

date_format(date,'express')

express:
%Y:年，4位
%m：月（01-12）
%M：月名
%k：小时（0-23）

示例：

1. 按照月份分组输出

select job, date_format(date ,'%Y-%m') as mon,sum(num) as cnt
from resume_info
where date like '2025%'
group by job,mon
order by mon desc,cnt desc	

13.自定义函数

自定义函数一般是分三步

1. 定义变量接收返回值
2. 执行查询条件，并赋值给相应变量
3. 返回结果

语法

create function function_name (Type parameter)
returns type
begin
return()
end;

• create function：创建函数关键字
• function_name：函数名
• （Type parameter）：参数类型，参数名

示例

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N =N-1;
  RETURN (
      select distinct Salary
      from Employee
      order by Salary desc
      limit N,1
  );
END

3，mysql索引

索引效率：

主键索引> 唯一性索引 > 普通索引

1，mysql 索引的建立对于mysql的高效运行是很重要的，索引可以大大提高mysql的检索速度。
2，索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
3，创建索引的时候，你需要保证该索引是应用在sql查询语句的条件（一般作为where子句的条件）
实际上，索引也就是一张表，该表保存了主键与索引字段，并指向实体表的记录。
4，上面都说使用索引的好处，但过多的使用索引将会造成泛滥，因此索引也会有它的缺点：虽然索引提高了查询效率，但是同时也会降低更新表的速度，在 insert，update，delete 的时候，mysql不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

1，普通索引

这是最基本的索引，它没有任何限制，

create index indexName on table_name(column_name)

修改表结构（添加索引）

alter table tableName add index indexName(column_name)

创建表的时候直接指定：

create table mytable(
id int not null,
    username varcher(10) not null,
    index [indexName](username(length))
);

删除索引的语法：

drop index [indexName] on mytable;

2,唯一索引

它与前面的普通索引类似，不同的就是；索引列的值必须唯一，但允许有空值。如果是组合索引，则列值得组合必须唯一。

创建索引：

create unique index indexName on mytable(username(length))

修改表结构：

alter table mytable add unique [indexName] (username(lengtg))

创建表的时候直接指定：

create table mytable(
id int not null,
    username varchar(10) not null,
    unique [indexName] (username(length))
)

3,使用alter 命令添加和删除索引

有四种方式添加数据表的索引：

alter table tbl_name add primary key (column_list)
该语句添加一个主键，这意味着索引值必须是唯一的，但不能为null

alter table tbl_name add unique index_name (column_list)
这条语句创建索引的值必须是唯一的（除了null值以外，null可能会出现多次）

alter table tbl_name add index index_name (column_list)
添加普通索引，索引值可出现多次

alter table tbl_name add fulltext index_name (column_list)
该语句指定了索引为 fulltext ，用于全文索引

实例

alter table testalter_tbl add index (c)

你还可以在alter 命令中使用 drop 子句来删除索引

alter table testalter_tbl drop index c;

使用alter 命令添加和删除主键

主键作用于列上 （可以一个列或者多个列联和主键），添加主键索引时，你需要确保该主键默认不为空(not null)

alter table testalter_tbl modify i int not null;
alter table testalter_tbl add primary key(i);

你也可以使用alter 命令删除主键：

alter table testalter_tbl drop primary key;

删除主键时，只需指定primary key，但在删除索引时，你必须知道索引名

4，显示索引信息

你可以使用 show index 命令来列出表中相关的索引信息，可以通过添加 \g 来格式化输出信息。

实例：

show index from table_name; \g

5,create 和 alter 的区别

1, alter 可以省略 索引名，数据库会默认根据第一个索引列赋予一个名称；create 必须指定索引名称；

2，create 不能用于创建 primary key 索引

3，alter 允许一条语句同时创建多个索引；create 一次只能创建一个索引。

alter table 表名 add primary key （id） ，add index <索引名> (col1,col2,---)

6，索引深入理解

1，为什么使用索引

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，我们遇到最多的还是一些复杂的查询操作，因此对于查询语句的优化显然是比较需要的，所以这个时候就是需要索引了。

2，为什么要有索引呢？

索引在mysql中也叫“键“，就是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能，非常关键，尤其是当表中的数据越来越大的时候，索引对于性能的影响更加重要，索引优化应该就是对查询性能优化最有效的手段。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，直接通过缩小查询范围快速查询。

3，索引的原理

索引的目的在于提高查询效率，与我们查询图书所用的目录是一个道理：先定位到章节，然后再定位到该章节中的某个小节，然后找到页数。类似的有：查字典，查车次

本质都是：通过不断的缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机事件变成顺序的事件，也就是说，有了这种索引机制，我们总是可以用同一种查询方式来锁定数据

1，分段查询，将大量的数据分成相应的段数，当需要查找某个数据时，判断它属于哪个分段的，如此可以省去遍历其他分段的时间。
2，但是当我们遇到千万级的数据时，怎么去分段就成了问题了。可以使用搜索树。
3，数据库实现比较复杂，一方面数据是保存在磁盘上的，另一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树是难以满足复杂的应用场景。

4，磁盘IO与预读

考虑到磁盘IO是非常昂贵的操作，计算机操作系统做了一些优化，当一次IO时，不光是把当前磁盘的地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址数据的时候，与其相邻的数据很快也会被访问到。每次IO读取的数据我们称之为一页（page）。具体一页有多大的数据跟操作系统有关，一般为4k或者8k，也就是我们读取一页内的数据的时候，实际上才发生一次IO，这个理论对于索引的数据结构设计非常有帮助。

5，索引的数据结构

1，任何一种数据结构都不是凭空产生的，它都有它的背景和使用场景。
2，使用场景：每次查找数据的时候把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想如果一个高度可控的多路搜索树是否能够满足需求呢，就这样，B+树应运而生。

B+树的查找过程[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4LjYQUKA-1625821879400)(C:\Users\pengzhang1\Desktop\sql\b+树.jpg)]

1,浅蓝色的块，我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项 17 和 35 ，包含指针 p1,p2,p3, p1表示小于17的磁盘块，p2表示在 17 和 35 之间的磁盘块，p3 表示 大于 35 的磁盘块。真实的数据 存在叶子节点即 3，5，9，10，13，15，28，29，36，60，75，79，90，99。非叶子节点只不存存储真实的数据，只存储指引搜索方向的数据项，如 17，35 并不真实存在于数据表中。

如图所示，如果要查找数据项 29，那么首先会把磁盘块1 加载到内存，此时发生一次IO ，在内存中用二分查找确定 29 在 17和35之间，锁定磁盘块 1 的 p2指针，内存时间因为非常短，（相比磁盘的IO）可以忽略不记，通过磁盘块1 的p2 指针的磁盘地址把磁盘块3加载到内存，发生第二次IO，29在26 和 30 之间，锁定磁盘3 的 p2 指针，通过指针加载磁盘块 8 到内存，发生第三次IO，同时在内存中做二分查找找到 29，结束查询，总计三次IO。真实的情况是，三层的b+树可以表示上百万的数据，如果上百万的数据查询只需要三次IO，那么性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本事非常高。

6，b+树的性质

1，索引字段要尽量的小：

通过上面的分析我们知道IO次数取决于b+树的高度h，假如当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有 h = log（m+1）N，当数量N一定的情况下，m越大，h越小；而 m = 磁盘块的大小/数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占据的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int 占 4 个字节，要比 bigint 8个字节少一半。这也就是为什么b+树要求把真实的数据放到叶子节点，而不是内层节点，一但放到内层节点，磁盘块的数据项就会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2，索引的最左匹配特性（即从左往右匹配）

7,mysql索引管理

1，功能

1,索引的功能就是加速查找
2，mysql 中的 primary key，unique ，联合唯一也都是索引，这些索引除了加速查找功能，还有约束的功能。

2，mysql 的索引分类

普通分类：
1,普通索引 index：加速查找
2，唯一索引
	主键索引：primary key：加速查找+约束（不为空且唯一）
	唯一索引：unique：加速查找+约束（唯一）
3，联合索引
	primary key （id，name）：联合主键索引
	unique（id，name）：联合唯一索引
	index（id，name）：联合普通索引
4，全文索引：fulltext ：用于搜索很长一篇文章的时候，效果最好
5，空间索引：spatial：了解就好，基本不用

8，强制索引

如果查询优化器忽略索引，您可以使用force index来提示它使用索引

基本语法：

select *
from table_name
force index(index_list)
where condition;

示例：

select *
from salaries force index(idx_emp_no)
where emp_no = 10005

4,mysql 视图

1，mysql 视图是什么

简单的来说：view 就是在数据库表上另外封装的一个表，这个表并不是真实存在的，它依赖于真实的表，相当于对数据库表中的字段做了一个筛选，只显示它想要的字段。

技巧：如果经常需要从多个表查询指定字段的数据，可以在这些表上建立一个视图，通过这个视图显示这些字段的数据

mysql 的视图不支持输入参数的功能，因此交互性上存在缺陷。但对于变化不是很大的操作，使用视图可以很大程度上简化用户的操作。

视图不同于数据库表：

• 视图不是数据库中真实存在的表，而是一张虚拟的表，其结构和数据是建立在对数据库中真实表的查询基础上的。
• 存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
• 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
• 视图是数据的窗口，而·表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据。其数据来源还是实际表。
• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些sql语句的集合。从安全的角度来说，视图的数据安全性更高，使用视图的用户不直接接触数据库表，不知道表结构。
• 视图的建立和删除只影响视图本身，不影响对应的基本表。

视图的优点：

视图与表在本质上虽然不相同，但是经过定义以后，可以进行查询，修改，更新，删除操作。

1）定制用户数据，聚焦特定的数据

在实际的应用中，不同的用户可能对不同的数据有不同的要求。
例如：当数据库同时存在时，如学生基本信息表，课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息表的视图，教师查看学生信息表和课程信息表的视图。

2）简化数据操作

在使用查询的时候，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作发生的频繁的话，可以创建视图来简化操作。

3）提高数据的安全性

视图是虚拟的，物理上是不存在的，可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

4）共享所需的数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可用共享数据库中的数据，同样的数据只需要存储一次。

5）更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中

6）重用sql语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或者删除，视图所呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便的重用该视图。

注意点：

• 创建视图需要足够的访问权限
• 创建视图的数目没有限制
• 视图可以嵌套，即从其他视图中检索数据的查询来创建视图
• 视图不能索引，也不能有关联的触发器，默认值或者规则
• 视图可以和表一起使用
• 视图不包含数据，所以每次使用视图时，都必须执行查询中所需的任何一个检索操作，如果多个连接和过滤条件创建了复杂的视图或者嵌套了视图，可能会发现系统运行性能下降的十分严重。因此，在部署大量视图应用时，应该进行系统测试

最后：order by 子句可以用在视图中，但若该视图检索数据的select 语句中也有 order by 子句，则该视图中的 order by 子句将被覆盖

2，mysql创建视图

创建视图是指在已经存在的mysql数据库表上建立视图，视图可建立在一张表上，也可以建立在多张表中。

基本语法：

create view 视图名 as
select 语句 

语法说明：

• 视图名：指定视图的名称，该名称在数据库中必须是唯一的，不能与其他表或者视图同名。
• select 语句：指定创建视图的select 语句，可用于查询多个基础表或者源视图

对于创建视图中的select 语句的指定存在以下限制：

• 用户除了拥有create view 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
• select 语句不能引用系统或者用户变量。
• select 语句不能包含 from 子句中的子查询
• select 语句不能引用预处理语句的参数。

with check option 
的意思就是：修改视图时，检查插入的数据是否符合 where 设置的条件

5，mysql 存储引擎

mysql 默认使用的存储引擎是Innodb

1，innodb

主要特点：

• 1，可以通过自动增长列，方法是 auto_increment
• 2，支持事务。默认的事务隔离级别为可重复读，通过MVCC （并发版本控制）来实现。
• 3，使用的锁粒度为行级锁，可以支持更高的并发
• 4，支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
• 5，配合一些热备工具可以支持在线热备。
• 6，在Innodb 中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度。
• 7，对于 Innodb 类型的表 其物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一起，都位于B+树的叶子节点上。

当然 Innodb 的存储表和索引也有下面两种形式：

(1) 使用共享表空间存储：所有的表和索引放在同一个表空间中。

（2）使用多表空间存储：表结构放在 frm 文件，数据和索引放在 IBD 文件中。分区表的话，每个分区对应单独的 IBD 文件，分区表 的使用可以提升查询效率。

6，存储过程

7，自联结

8.原则

1. 最左前缀匹配原则

9.数据库导出sql

SELECT
t1.Column_Name AS "字段名称",
t2.Comments AS "字段说明",
t1.DATA_TYPE "类型"，
decode(t1.DATA_TYPE,'VARCHAR2','VARCHAR2('||t1.DATA_LENGTH||')' ,'NUMBER' ,'NUMBER('||t1.DATA_PRECISION||','||t1.DATA_SCALE||')' ,t1.DATA_TYPE)  AS "数据类型",
decode(t1.DATA_TYPE,'VARCHAR2',t1.DATA_LENGTH ,'NUMBER' ,t1.DATA_PRECISION ,'')  AS "长度" ,
decode(t1.DATA_TYPE,'VARCHAR2','0' ,'NUMBER' ,t1.DATA_SCALE ,'')  AS "精度" ,
t1.NullAble AS "是否为空"
FROM cols t1
JOIN USER_COL_COMMENTS t2
ON t1.Table_name = t2.Table_name
AND t1.Column_Name = t2.Column_Name
WHERE t1.TABLE_NAME='TRD_CR_RFQ_QS_DL_BOND';

9.1函数

10.sql关键字执行顺序

from  
on  
join  
where  
group by  
having  
select  
distinct  
union  
order by  

11.oracle转mysql

SELECT 
       '  `'||lower(t1.Column_Name) ||'`'
           ||' ' 
           || decode(t1.DATA_TYPE,
                'VARCHAR2','varchar('||t1.DATA_LENGTH||')' 
                ,'NUMBER' ,'decimal'||(
                   case 
                      when t1.DATA_SCALE is not null and t1.DATA_PRECISION is not null then 
                          '('||t1.DATA_PRECISION ||   ',' ||t1.DATA_SCALE||    ')'
                       when t1.DATA_SCALE is not null and t1.DATA_PRECISION is null then 
                          '('  ||t1.DATA_SCALE|| ')'
                       when t1.DATA_SCALE is null and t1.DATA_PRECISION is not null then 
                          '(0, '||t1.DATA_PRECISION ||    ')'
                       when t1.DATA_SCALE is null and t1.DATA_PRECISION is null then 
                          ''
                       end
                )
                ,'FLOAT' ,'float'||(
                   case 
                      when t1.DATA_SCALE is not null and t1.DATA_PRECISION is not null then 
                          '('||t1.DATA_PRECISION ||   ',' ||t1.DATA_SCALE||    ')'
                       when t1.DATA_SCALE is not null and t1.DATA_PRECISION is null then 
                          '('  ||t1.DATA_SCALE|| ')'
                       when t1.DATA_SCALE is null and t1.DATA_PRECISION is not null then 
                          '(0, '||t1.DATA_PRECISION ||    ')'
                       when t1.DATA_SCALE is null and t1.DATA_PRECISION is null then 
                          ''
                       end
                )
                ,'TIMESTAMP(6)' , 'datetime'
                ,'DATE', 'datetime'
                ,'BLOB' , 'text'
                ,'CLOB' ,'text'
                ,'NVARCHAR2','varchar('||t1.DATA_LENGTH||')' 
                ,'*?') 
           || decode(t1.NullAble,'N',' not null')
           || (case when t1.DATA_DEFAULT is not null then ' default ''' || '*?默认值'||'''' else '' end)
           --|| decode (lower(t1.Column_Name) ,'id',' primary key') 
              || decode (t5.CONSTRAINT_TYPE ,'P',' primary key') 
           || (case when t2.Comments is not null then ' comment ''' || t2.Comments||'''' else '' end)
           ||' ,'
        as mysql ,
               t2.Comments AS "字段中文名称",
       t1.Column_Name AS "字段名称",
       decode(t1.DATA_TYPE,'VARCHAR2','VARCHAR2('||t1.DATA_LENGTH||')' ,'NUMBER' ,'NUMBER('||t1.DATA_PRECISION||','||t1.DATA_SCALE||')' ,t1.DATA_TYPE)  
            AS "类型&长度",
       decode(t1.NullAble,'N','是','Y','',t1.NullAble) AS "是否必填",
       t1.DATA_DEFAULT AS "默认值" 
  FROM cols t1
  LEFT JOIN user_col_comments t2 
         ON t1.Table_name = t2.Table_name
        AND t1.Column_Name = t2.Column_Name
  LEFT JOIN user_tab_comments t3 
         ON t1.Table_name = t3.Table_name
  LEFT JOIN user_objects t4 
         ON t1.table_name = t4.OBJECT_NAME
  left join (
    select t11.table_name,t11.COLUMN_NAME, t12.CONSTRAINT_TYPE from  user_cons_columns t11
    inner join user_constraints t12
       on t12.CONSTRAINT_NAME = t11.CONSTRAINT_NAME and t12.CONSTRAINT_TYPE='P'
  ) t5 on t5.TABLE_NAME = t2.Table_name and t5.COLUMN_NAME = t2.Column_Name
  WHERE
  t1.TABLE_NAME='TRD_CR_XREPO_QUOTATION'   -- 表名
  and 
   NOT EXISTS (SELECT t4.Object_Name
          FROM User_objects t4
         WHERE t4.Object_Type = 'TABLE'
           AND t4.Temporary = 'Y'
           AND t4.Object_Name = t1.Table_Name)
  ORDER BY t1.Table_Name, t1.Column_ID;

12.mysql中key与index，primary key与unique key

index与key

• key
  1. key是数据库的物理结构，它包含两层意义和作用
  2. 一是约束（偏重于约束和规范数据库的结构完整性）
  3. 二是索引（辅助查询用的）
  4. 包括primary key，unique key，foreign key等
  5. 可见key是同时具有constrain 和 index的意义
• index
  1. index是数据库的物理结构，它只是辅助查询
  2. 它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储
  3. 索引分类：前缀索引，全文本索引，主键索引，唯一索引，普通索引。

primary key与unique key区别

1. primary key 的1个或者多个列必须为not null，如果列为null，在增加primary key时，列自动更改为not null而unique key对列没有要求。
2. 一个表只能有一个primary key 但是可以有多个unique key。

• primary key
  1. 约束：用来规范一个存储主键和唯一性
  2. 索引：
• unique key
  1. 主要用来防止数据插入的时候的重复

13、oracle添加约束

? 需求：添加非空和主键约束

? oracel 的约束分类

• not null：非空
• unique：唯一
• primary key：主键
• foreign key：外键
• check：检查

非空

ALTER TABLE TRD_OR_ENTRY_QT 
MODIFY ID CONSTRAINT NN_TRD_OR_ENTRY_QT_ID NOT null

主键

ALTER TABLE TRD_OR_ENTRY_QT 
ADD
constraint PK_TRD_OR_ENTRY_QT_ID PRIMARY KEY(ID);

删除约束

ALTER TABLE TRD_CR_NGTT_QT_TALK
drop constraint UN_CB_NGTT_DL_ID

查看一个表的约束

SELECT constraint_name,table_name,column_name
FROM USER_cons_columns
WHERE TABLE_name = 'TRD_OR_ENTRY_QT_MUL'

99,待解决

1，触发器：trigger（已解决）

2，索引引擎：因该叫mysql 数据库存储引擎或者数据库引擎（解决)

3，存储过程：

4. 数据库的数据字典

$测试$

红色

红色

1. 先分组算出每人每天刷了多少，再求和
   1. 两个表先连接求出每人每天刷了多少
2. 先按日期升序排序，再按姓名升序排序

开发者涨薪指南

48位大咖的思考法则、工作方式、逻辑体系