update

1.查询所有教师授课的学生上课平均人数超过全部课程平均人数的教师姓名

表定义 1: teachers (教师表)

列名	类型	注释
id	int unsigned	主键
name	varchar	讲师姓名
email	varchar	讲师邮箱
age	int	讲师年龄
country	varchar	讲师国籍

表定义 2: courses (课程表)

列名	类型	注释
id	int unsigned	主键
name	varchar	课程名称
student_count	int	学生总数
created_at	date	课程创建时间
teacher_id	int unsigned	讲师 id

select t.name from teachers t

inner join courses c

on t.id=c.teacher_id

group by c.teacher_id

having avg(student_count)>(

    select avg(student_count)

from courses   )

 

2.stuff函数

select STUFF('abcdefg',1,0,'1234')       --结果为'1234abcdefg'

select STUFF('abcdefg',1,1,'1234')       --结果为'1234bcdefg'

select STUFF('abcdefg',2,1,'1234')       --结果为'a1234cdefg'

select STUFF('abcdefg',2,2,'1234')       --结果为'a1234defg'

相当于从第几个字符开始，替换掉几个字符成后面的

STUFF(原字符, 开始位置, 删除长度, 插入字符)

从指定的起点处开始删除指定长度的字符，并在此处插入另一组字符

 

3.rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位（如：1、3、3、6、9）

dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位（如：1、2、2、3、3、4）

row_number() 按照值排序时产生一个自增编号，不会重复（如：1、2、3、4、5、6）

 

4.sql修改

UPDATE 表名 SET 列1=新值1,列2=新值2 WHERE 过滤条件

假如要修改李四的年龄为21岁

UPDATE t_student SET student_age=21 WHERE student_name='李四';

 

 

 

注：修改多个列的值时用逗号隔开。要想设置某一列的值为空，只需让<列名>=NULL 即可。WHERE表示过滤条件。

 

5：请编写 SQL 语句，将课程表 courses 中人工智能课 (Artificial Intelligence) 的学生人数修改为 500 人。

表定义: courses

列名 类型 注释

id int 主键

name varchar 课程名称

student_count int 学生总数

created_at date 开课时间

teacher_id int 讲师 ID

代码：

update courses set student_count = 500

where name = 'Artificial Intelligence'

 

6.这里提供了一个可更新的视图 v_teachers，请你编写 SQL 语句对 Linghu Chong 老师的年龄修改为 30 岁。

update v_teachers set age =30

where name = 'Linghu Chong'

 

7.复制表数据到另一张表（Insert）

1.完整表元素进行复制（必须得是两张表结构相同）

insert into 表1 select * from 表2

例如:

insert into test select * from test1;  

注意：这里的test和test1 必须保证表结构相同

2.将同一数据库下的一张表部分列复制到另一张表中

insert into 表1(列1,列2 ,…) select 列1,列2,列3… from 表2

例如:

insert into test(name,age) select name,age from test1;

注意：如果对应的列名不同可以使用 as 关键字进行转换

例如:

INSERT INTO test1 (myname,myage) SELECT NAME AS myname,age AS myage FROM test;

3.将一个数据库中的表复制到另一个数据中的一个表中

insert into 数据库A.表1(列1,列2…) select 列1,列2,… from 数据库B.表2

例如：

insert into sdb.test(name,age) select name,age from sdb1.test1;

注意：这里的sdb和sdb1分别对应的是两个数据库

例题一：将教师表中年龄大于 20 的数据复制到另一张表中

insert into teachers_bkp select * from teachers

where age > 20

 

8.某处摆放着一些标有 id 的箱子，其中部分箱子是装有东西的，部分箱子是空闲的。
请编写 SQL 语句，找到空且连续的箱子，并将它们按照 id 递增排序后返回。

样例一：

表内容: boxes

id	is_empty
1	1
2	0
3	1
4	1
5	1

在运行你的 SQL 语句之后，表应返回：

id

3

4

5

代码：

select distinct a.id

from boxes a,boxes b

where abs(a.id-b.id) = 1 and

a.is_empty = 1 and b.is_empty = 1

order by  a.id