牛客SQL数据库实战自选知识补充题

【创建表 】

mysql有三种建表方式：

1.常规创建
create table if not exists 目标表

2.复制表格
create 目标表 like 来源表

3.将table1的部分拿来创建table2
create table if not exists table2
(
first_name varchar(45) not null,
last_name varchar(45) not null
) ;
select first_name, last_name
from table1                                                                                                                                    注： 建表时必须声明列名，如不声明，无法对表进行插入or修改

常规创建例：

create table if not exists actor (
    actor_id smallint(5) not null primary key comment '主键id',
    first_name varchar(45) not null comment '名字',
    last_name varchar(45) not null comment '姓氏',
    last_update date not null comment '日期')

 将table1的部分拿来创建table2 例：

create table 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

create table actor_name
select first_name, last_name from actor

---

【批量插入数据】

insert into 表名 (列名1,列名2) values ('值1', '值2') :插入数据,如果主键重复，则报错

insert repalce:插入替换数据,如果存在主键或unique数据则替换数据

insert ignore:如果存在数据,则忽略。

例1：

insert into actor 
values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
       (2,'NICK','WAHLBERG','2006-02-15 12:34:33')

insert into 
actor (actor_id, first_name, last_name,	last_update)
values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
       (2,'NICK','WAHLBERG','2006-02-15 12:34:33')

例2：

insert ignore into actor
values ('3','ED','CHASE','2006-02-15 12:34:33')

---

【对字段创建索引】

添加索引：

create (unique) index 索引名 on 表名（列名）

alter table 表名 add (unique) index 索引名（列名）

创建表时直接指定指引：

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] ( username(length) )  
);  

删除索引：

drop index [indexName] on mytable;

索引可以大大提高MySQL的检索速度。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

当需要快速找出在某个列中有一特定值的行，适合用索引，这样就不用在这列一行行遍历检索了

索引建立的原则：

（1）定义主键的数据列一定要建立索引。（2）定义有外键的数据列一定要建立索引。（3）对于经常查询的数据列最好建立索引。（4）对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

创建索引 例：

create unique index uniq_idx_firstname on actor (first_name);
create index idx_lastname on actor (last_name);

---

【创建视图】

视图（View）是一种虚表，允许用户实现以下几点：（1）用户或用户组查找结构数据的方式更自然或直观。（2）限制数据访问，用户只能看到有限的数据，而不是完整的表。（3）汇总各种表中的数据，用于生成报告。

create view 视图名 as                                                                                                                        select 字段名1 别名, 字段名2 别名 from 表名

create view 视图名 ( 字段名1别名, 字段名2别名 ) as                                                                        select 字段名1, 字段名2  from 表名

---

【使用索引查询】

强制索引查询使用：force index (索引名);

例：

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

---

【增加列】

插入一行内容：insert into 表名 ( '列标签(可省略)' )  values( '行内容' )

插入一列： alter table 表名 add 列的属性 列名 列类型 default 'default值'

例：

alter table actor 
add column create_date datetime NOT NULL default '2020-10-01 00:00:00' ;

---

【构造触发器】

创建触发器：

create trigger 触发器名称 before / after [ insert / update / add ] on 表名                                      begin 触发器内容 ; end ;

下划部分标识触发事件

new 关键字表示更新后的表的字段 ，old表示更新前的表的字段 

例：

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

---

【删除指定数据】

delete from 表名 where 限制条件

mysql不允许用一个表中查询出来的数据更新同一个表

例：

delete from titles_test
where id not in (
    select min_id 
    from (
        select min(id) as min_id
        from titles_test
        group by emp_no) aa
)

---

【数据更新】

update 表名称 set 列名称 = 新值 where 列名称 = 某值

例1：

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

例2 ：

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

---

【替换数据】

replace into 表名 values ()

replace不能使用where语句

update 表名 set 字段名 = replace (字段名, 旧值, 新值) where 条件

例：

方法1：replace into当遇到primary 或者 unique key 的时候，会首先进行update

replace into titles_test
values (5, '10005', 'Senior Engineer', '1986-06-26', '9999-01-01') ;

方法2：使用update, replace

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

---

【修改表信息】

alter table 表名 change 原列名 新列名 类型;  --修改表的列属性名

alter table 表名 modify 列名 类型;  --修改表的类类型

alter table 表名 drop 列名;  --删除表的某一列

alter table 表名 add 列名 类型; --添加某一列

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

---

【创建外键约束】

alter table <表名>                                                                                                                        add constraint foreign key (<列名>)                                                                                        references <关联表>（关联列）

例：

alter table audit 
add constraint foreign key (emp_no) 
references employees_test (id);

---

【关于引号、转义符】

两个单引号：'字符串'

两个双引号："变量"       （如：表名、字段名）

例：在一些系统中“不得不”对某一字段命名成一个数字：如：123456                                                       select "123456" from table1;

添加一个叫" O' Reilly "的公司：使用单引号进行转义                                                                      insert into company (name) values ( 'O''Reilly' ) ;

当'表示一个字符，也可以" ' "表示

需要转义的字符： '   "   :    ;   (   )   [   ]   |   \   @   .............

---

【length()函数与replace()函数】

length(s) 函数： s是字符串，可以是变量。返回的是所求的字符串s的长度。

replace(a,b,c)： 在字符串a中，将a中出现的b，替换成c。再把这个替换之后的串的结果返回。

例：若c为''，即可将b从a中删去

select id, length(string)-length( replace(string, ',', '') ) as cnt
from strings;

---

【group_concat() 函数】

group_concat( [distinct] 字段 [order by 排序字段 asc/desc] seperator '分隔符')

这是一个分组聚合函数

函数默认的分隔符是 ","，默认可以省略

例：

select dept_no, group_concat(emp_no)
from dept_emp
group by dept_no;

---

【分页查询】

一般分页查询常使用 limit x,y ，显示跳过x行后y行的数据

例： 

select * from employees limit 5,5;

---

【用 exists 关键字查找】

1.什么时候用EXISTS，什么时候用IN？
        主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：
            当主表比从表大时，IN查询的效率较高；
            当从表比主表大时，EXISTS查询的效率较高；
        原因如下：
            in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
            exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

2. exists返回的是Boolean值。它用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

3.将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

例：

select * from employees
where not exists (
    select emp_no from dept_emp
    where employees.emp_no = dept_emp.emp_no);

select *
from employees
where emp_no not in (select emp_no
                    from dept_emp);

---

【case when 函数】

CASE WHEN SCORE = 'A' THEN '优'
           WHEN SCORE = 'B' THEN '良'
           WHEN SCORE = 'C' THEN '中' ELSE '不及格' END

例：sql59

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

---

【窗口函数sum】

sum()over(partition by order by)       可以递进求和

例：

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

---

【综合深化题】

round(x,y)  保留x至y位小数点

case when...then...else...end

普通法： 

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

 聪明法：

select date,
ROUND(AVG(CASE WHEN type = 'no_completed' THEN 1
     ELSE 0 END),3) AS p
from email
where send_id not in (select id from user where is_blacklist = 1)
and receive_id not in (select id from user where is_blacklist = 1)
group by date

---

【count与日期深化题】

count可以不搭配group by使用

date_add (date, interval expr type)       date_sub (date, interval expr type) 

select round( count(distinct user_id) / (select count(distinct user_id) from login), 3) p
from login
where (user_id, date) in (
    select user_id,date_add(date, interval 1 day) from login );

---

【综合深化题2】

select a.date, sum(case when d_rank =1 then 1 else 0 end) new
from (
    select date, 
    row_number()over(partition by user_id order by date) d_rank
    from login ) a
group by date
order by date;

current_date 今天所在日期

---

【count函数深化题】

count函数默认四舍五入

select job, round(count(id)/2,0) strat, round((count(id)+1)/2,0) end
from grade
group by job
order by job;

---

【深化综合题3】

select n1.id, n1.job, n1.score, n1.t_rank
from(
select id, job, score, rank()over(partition by job order by score desc) t_rank
from grade) n1
    left join (select job, round(count(id)/2,0) start, round((count(id)+1)/2,0) end 
               from grade group by job) n2
    on n1.job = n2.job
where n1.t_rank = n2.start or n1.t_rank = n2.end
order by n1.id

---

【深化综合题4】

select a.job, b.mon first_year_mon, b.cnt first_year_cnt,
    a.mon first_year_mon, a.cnt first_year_cnt
from
    (select job, left(date,7) mon, sum(num) cnt, month(date) m
    from resume_info
    where year(date)='2026'
    group by job, mon, m) a
join 
    (select job, left(date,7) mon, sum(num) cnt, month(date) m
    from resume_info
    where year(date)='2025'
    group by job, mon, m) b 
on a.m = b.m and a.job = b.job
order by b.mon desc, a.job desc

---

【sum开窗函数】

sum(字段1) over(partition by 字段2 order by 字段3)

实现分区内累加功能

例：

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

---

【中位数题】

当某一数的正序和逆序均大于或等于整个序列的个数的一半即为中位数

select grade from (
    select grade, (select sum(number) from class_grade) total,
    sum(number)over(order by grade) a1, sum(number)over(order by grade desc) a2
from class_grade) a
where a1>=total/2 and a2 >=total/2
order by grade;

---

【网易真题算法推荐】

 

select distinct music_name
from music
where id in (
    select music_id
    from music_likes
    where user_id in (
        select follower_id
        from follow
        where user_id=1)
        and music_id not in
            (select music_id from music_likes where user_id=1))

---

【with...as...】

with A as (select * from class), B as ()                                                                                            select * from A

先执行select * from class 得到一个结果，将这个结果记录为A ，在执行select *from A 语句。A 表只是一个别名。

也就是将重复用到的大批量 的SQL语句，放到with as 中，加一个别名，在后面用到的时候就可以直接用。对于大批量的SQL数据，起到优化的作用。

 优化前：

select *
from (select user_id, name, 
    sum(case when type='add' then grade_num else 0-grade_num end) grade
    from grade_info g left join user on g.user_id = user.id
    group by user_id, name) a
where a.grade = (
    select max(b.grade)
    from( select user_id, name, 
        sum(case when type='add' then grade_num else 0-grade_num end) grade
        from grade_info g left join user on g.user_id = user.id
        group by user_id, name) b )

 优化后：

with A as (select user_id, name, 
           sum(case when type='add' then grade_num else 0-grade_num end) grade
           from grade_info g left join user on g.user_id = user.id
           group by user_id, name)
select * from A
where A.grade = (select max(A.grade) from A)