SQL刷题记录（二）

SQL刷题记录（二）

答主刚开始自学SQL，本专栏记录的是答主在牛客上刷题的过程。疑惑和部分思路都会写在习题下方。
题目来源：牛客网刷题网址

自学网站：SQL教程

本文内容：T226-T250

T226.

select
  concat(last_name, ' ', first_name)
from
  employees

concat()是支持MySQL的字符串连接函数

T227.

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

create table 表名() 建表语言

T228.

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')

mysql中常用的三种插入数据的语句:
insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；

T229.

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

见上

T230.

create table if not exists actor_name(
  first_name varchar(45) not null comment '名字',
  last_name varchar(45) not null comment '姓氏'
)
select
  first_name,
  last_name
from
  actor

本题主要考察sql创建表的方式： 1、create table if not exists table_name(); (直接创建)
2、create table table_name like table_name1;(复制表结构）
3、create teble if not exists table_name() select * from table table_name1;(数据复制)

T231.

alter table actor add unique uniq_idx_firstname(first_name);
alter table actor add index idx_lastname(last_name);

添加主键
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
添加唯一索引
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
// 这条语句创建索引的值必须是唯一的。
添加普通索引
ALTER TABLE tbl_name ADD INDEX index_name (col_list);
// 添加普通索引，索引值可出现多次。
添加全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
// 该语句指定了索引为 FULLTEXT ，用于全文索引。
删除索引的语法：
DROP INDEX index_name ON tbl_name;
// 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;

T232.

create view actor_name_view(first_name_v, last_name_v) AS
select
  first_name,
  last_name
from
  actor

视图相关知识：创建视图：
create view name(column1,column2…)
AS
select …
from …

T233.

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

FORCE INDEX()强制索引，优先执行该索引，大概是可以增加查询效率

T234.

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

最后一列增加列：alter table 表名 add column ……
指定位置增加列：alter table 表名 add column ……after列名
在第一列增加列：alter table 表名 add column ……first

T235.

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

触发器还没看到

T236.

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

MySQL中不允许在子查询的同时删除表数据（不能一边查一边把查的表删了）所以要多写一步，将select得到的结果，再通过中间表select一遍，这样就规避了错误，这个问题只出现于mysql，mssql和oracle不会出现此问题。

T237.

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

update
  titles_test
set
  from_date = '2001-01-01'

这里我用了两条update语句，因为一个有筛选条件另一个没有筛选条件。
如果是我自己理解错题意了那只需要写一条就可以，更简单

T238.

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

当我们需要将字段中的特定字符串批量修改为其他字符串时，可已使用以下操作：UPDATE table_name SET field=REPLACE(field, ‘old-string’, ‘new-string’)
[WHERE Clause]

T239.

ALTER table
  titles_test rename to titles_2017

alter函数的用法

T240.

ALTER TABLE audit
ADD CONSTRAINT FOREIGN KEY (emp_no)
REFERENCES employees_test(id);

创建外键语句结构：
ALTER TABLE <表名>
ADD CONSTRAINT FOREIGN KEY (<列名>)
REFERENCES <关联表>（关联列）

T242.

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

是一个常规的修改表中内容的语句，注意员工必须在上边那个表里，不然是没加薪的

T244.

select
  concat(last_name, "'", first_name)
from
  employees

mysql的concat()函数拼接字符串

T245.(巧妙)

select
  id,
  char_length(string) - char_length(replace(string, ",", "")) as cnt
from
  strings
group by
  id

巧用字符串计数函数char_length()，把",“换成”“，然后两者相减就是”,"的个数了

T246.

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

注意字符串的相关函数

T247.(新函数)

select
  dept_no,
  group_concat(
    distinct emp_no
    order by
      emp_no separator ','
  ) as employees
from
  dept_emp
group by
  dept_no

知识点总结：group_concat（）函数将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

T248.

select
  avg(salary)
from
  salaries
where
  to_date = '9999-01-01'
  and salary not in (
    select
      max(salary)
    from
      salaries
    where
      to_date = '9999-01-01'
  )
  and salary not in (
    select
      min(salary)
    from
      salaries
    where
      to_date = '9999-01-01'
  )

第一点，如果子查询作为筛选条件而不作为表连接的话，只可以返回一个字段的一堆值，这就是为什么我写了两个salary not in子查询

第二点，子查询和外层的筛选条件都需要选择to_date = ‘9999-01-01’，都需要把不在职的排除掉

T249.

select
  *
from
  employees
limit
  5 offset 5

巧妙地利用limit offeset 进行页数的选取