sql基础语法学习

查询

查询所有列

1
SELECT * FROM table_name

查询特定列

1
SELECT column_name... FROM table_name

限制行数

1
SELECT column_name... FROM table_name LIMIT rows_number

返回后的列重新命名

1
SELECT column_name as column_name2 FROM table_name

条件查询

1
SELECT column_name FROM table_name WHERE condition

某个范围条件查询

1
2
SELECT device_id,gender,age FROM user_profile WHERE age between 20 and 23
//包含上下限

某个集合条件查询

1
SELECT device_id,gender,age,university FROM user_profile WHERE university NOT IN ("复旦大学")

IN

用来查找属性值属于指定集合的元组。

格式为：

1 列名 [NOT] IN（常量1, 常量2,…常量n）

IN：当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录。

NOT IN：当列中的值与某个常量值相等时，结果为False，表明此记录为不符合查询条件的记录。

例17.查询所有班名是“电子信息工程技术”、“电子声像”或“电子组装技术”的班号和班名。

12 SELECT 班号, 班名 **FROM** 班级表**WHERE** 班名 IN ('电子信息工程技术' , '电子声像‘, '电子组装技术')

等价于：

1234 SELECT 班号,班名 **FROM** 班级表**WHERE** 班名= ‘电子信息工程技术’ OR班名 = ‘电子声像’ OR班名 = ‘电子组装技术’

例18.查询班名既不是“电子信息工程技术”、也不是“电子声像”和“电子组装技术”的班号和班名。

12 SELECT 班号,班名 **FROM** 班级表**WHERE** 班名 NOT IN ('电子信息工程技术' , '电子声像‘, '电子组装技术')

等价于：

1234 SELECT 班号,班名 **FROM** 班级表**WHERE** 班名!= ‘电子信息工程技术’ AND班名!= ‘电子声像’ AND班名!= ‘电子组装技术’

空值和滤空值

1
SELECT device_id,gender,age,university FROM user_profile WHERE age is not null

多个条件查询(AND优先级高于OR)

1
SELECT device_id,gender,age,university,gpa FROM user_profile WHERE gender ="male" and gpa>3.5
1
SELECT device_id,gender,age,university,gpa FROM user_profile WHERE gender ="male" and gpa>3.5

字符匹配查询

1
SELECT device_id,age,university FROM user_profile WHERE university LIKE '%北京%'

LIKE

一般形式为：

列名 [NOT ] LIKE

匹配串中可包含如下四种通配符：

_：匹配任意一个字符；

%：匹配0个或多个字符；

  • [ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；

[^ ]：不匹配[ ]中的任意一个字符。

例23．查询学生表中姓‘张’的学生的详细信息。

1 SELECT * **FROM** 学生表 **WHERE** 姓名 LIKE ‘张%’

例24．查询姓“张”且名字是3个字的学生姓名。

1 SELECT * **FROM** 学生表 **WHERE** 姓名 LIKE '张__’

如果把姓名列的类型改为nchar(20)，在SQL Server 2012中执行没有结果。原因是姓名列的类型是char(20)，当姓名少于20个汉字时，系统在存储这些数据时自动在后边补空格，空格作为一个字符，也参加LIKE的比较。可以用rtrim()去掉右空格。

1 SELECT * **FROM** 学生表 **WHERE** rtrim(姓名) LIKE '张__'

例25.查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。

1 SELECT * **FROM** 学生表 **WHERE** 姓名 LIKE '[张李刘]%’

例26.查询学生表表中名字的第2个字为“小”或“大”的学生的姓名和学号。

1 SELECT 姓名,学号 **FROM** 学生表 **WHERE** 姓名 LIKE '_[小大]%'

例27.查询学生表中所有不姓“刘”的学生。

1 SELECT 姓名 **FROM** 学生 **WHERE** 姓名 NOT LIKE '刘%’

例28.从学生表表中查询学号的最后一位不是2、3、5的学生信息。

1 SELECT * **FROM** 学生表 **WHERE** 学号 LIKE '%[^235]'

查找列中数据的最大值

1
SELECT MAX(gpa) as gpa FROM user_profile WHERE university = '复旦大学'

计数以及平均值（round保留一位小数）

1
SELECT count(gender) as male_num,round(avg(gpa),1) as avg_gpa FROM user_profile WHERE gender="male"

计数，平均值以及组合排序

1
SELECT gender,university,count(device_id) as user_num,avg(active_days_within_30) as avg_active_day,avg(question_cnt) as avg_question_cnt FROM user_profile group by gender,university ORDER BY gender

聚合函数

使用聚合函数汇总数据

SQL提供的统计函数有：

COUNT(【Shift+8】)：统计表中元组个数；

COUNT([DISTINCT] <列名>)：统计本列列值个数；

SUM( <列名> )：计算列值总和；

AVG( <列名> )：计算列值平均值；

MAX( <列名> )：求列值最大值；

MIN( <列名> )： 求列值最小值。

上述函数中除COUNT(【Shift+8】)外，其他函数在计算过程中均忽略NULL值。

统计函数不能出现在WHERE子句中。

例如，查询成绩最高的学生的学号，如下写法是错误的：

SELECT 学号 FROM 成绩表

WHERE 成绩 = MAX(成绩)

例29.统计学生总人数。

1 SELECT COUNT(*) **FROM** 学生表

例30.统计选修了课程的学生的人数。

12 SELECT COUNT (**DISTINCT** 学号)**FROM** 成绩表

例31.计算学号为“11214D24”的学生的考试总成绩之和。

12 SELECT SUM(成绩) **FROM** 成绩表**WHERE** 学号 = ‘11214D24 '

例32.计算“M01F011”课程的学生的考试平均成绩。

12 SELECT AVG(成绩) **FROM** 成绩表**WHERE** 课程号 = ‘M01F011 ‘

例33.查询选修了“M01F011” 课程的最高分和最低分。

123 SELECT**MAX**(成绩) 最高分,**MIN**(成绩) 最低分 **FROM** 成绩表**WHERE** 课程号 = ‘M01F011 '

分组过滤HAVING

1
2
3
4
SELECT university,AVG(question_cnt)avg_quesition_cnt,AVG(answer_cnt)avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING avg_quesition_cnt < 5 OR avg_answer_cnt < 20

分组过滤

使用HAVING

HAVING子句用于对分组后的结果再进行过滤，

它的功能有点像WHERE子句，但它用于组而不是单个记录。

在HAVING子句中可以使用统计函数，但在WHERE子句中则不能。HAVING通常与GROUP BY子句一起使用。

例36.查询学生表中人数大于等于3的班号和人数。

1234 SELECT 班号, COUNT(*) 人数**FROM** 学生表**GROUP** BY 班号**HAVING** COUNT(*) >= 3

例37.查询平均成绩大于等于80的学生的学号、选课门数和平均成绩。

1234 SELECT 学号, COUNT(*) 选课门数,AVG(成绩) 平均成绩 **FROM** 成绩表**GROUP** BY 学号**HAVING** AVG(成绩) >= 80

多表查询

1
select q.device_id,question_id,result from question_practice_detail as q JOIN user_profile as u ON q.device_id=u.device_id WHERE university="浙江大学"

子查询

1
2
3
4
5
6
7
select device_id, question_id, result
from question_practice_detail
where device_id in (
    select device_id from user_profile
    where university='浙江大学'
)
order by question_id

多表连接

内连接

内连接语法如下：

1234 SELECT …**FROM** 表名[**INNER**] JOIN 被连接表**ON** 连接条件

例39.查询每个学生及其班级的详细信息。

12 SELECT * **FROM** 学生表**INNER** JOIN 班级表 **ON** 学生表.班号=班级表.班号

结果中有重复的列：班号。

例40.去掉例39中的重复列。

12 SELECT 学号, 姓名,班级表.班号, 班名 **FROM** 学生表 JOIN 班级表**ON** 学生表.班号=班级表.班号

例41.查询重修学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。

1234 SELECT 姓名, 课程号, 成绩**FROM** 学生表 JOIN 成绩表**ON** 学生表.学号 = 成绩表.学号**WHERE** 状态 = '重修'

执行连接操作的过程

首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，

找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。 表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2， …

重复这个过程，直到表1中的全部元组都处理完毕为止。

表别名

可以为表提供别名，其格式如下：

<源表名> [ AS ] <表别名>

使用别名时例41可写为如下形式：

1234 SELECT 姓名, 课程号, 成绩**FROM** 学生表 S JOIN 成绩表 g**ON** S.学号 = g.学号**WHERE** 状态 = ‘重修’

注：如果为表指定了别名，则查询语句中其他所有用到表名的地方都要使用别名

例42.查询所有学生的姓名、班名和系名。

1234 SELECT 姓名,班名,系名**FROM** 学生表 s JOIN 班级表 bjb**ON** s.班号 = bjb.班号JOIN 系表 xb **ON** bjb.系号 = xb.系号

例43.查询软件工程系所有学生的情况，要求列出学生姓名和所在的系。

12345 SELECT 姓名, 系名**FROM** 学生表 s JOIN 班级表 bjb**ON** s.班号 = bjb.班号JOIN 系表 xb **ON** bjb.系号 = xb.系号**WHERE** 系名= '软件工程系

例44.有分组的多表连接查询。查询每个班的学生的考试平均成绩。

12345 SELECT 班号,AVG(成绩) **AS** 班平均**FROM** 学生表 S JOIN 成绩表 g**ON** S.学号 = g.学号**GROUP** BY 班号

例45.有分组和行过滤的多表连接查询。查询21226P班每门课程的选课人数、平均成绩、最高成绩和最低成绩。

12345678 SELECT 课程号, COUNT(*) **AS** Total,AVG(成绩) **AS** Avg成绩,**MAX**(成绩) **AS****Max**成绩,**MIN**(成绩) **AS****Min**成绩**FROM** 学生表 S JOIN 成绩表 g**ON** S.学号 = g.学号**WHERE** 班号 = '21226P'**GROUP** BY 课程号

题解

题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

123 SELECT q.device_id,question_id,result**FROM** question_practice_detail q JOIN user_profile u **ON** q.device_id=u.device_id**WHERE** university='浙江大学'

分组查询

1
2
3
4
5
select case when age<25 or age is null then "25岁以下"
         when age>=25 then "25岁及以上"
        end age_cut,count(*)number//定义分类结果的列名
FROM user_profile
group by age_cut

case函数

CASE函数

是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。

可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。

分为：

简单CASE函数

搜索CASE函数

简单 CASE函数

123456 CASE 测试表达式**WHEN** 简单表达式1 **THEN** 结果表达式1**WHEN** 简单表达式2 **THEN** 结果表达式2 …**WHEN** 简单表达式n **THEN** 结果表达式n[ **ELSE** 结果表达式n+1 ]**END**

计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。

如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。

如果所有简单表达式的值与测试表达式的值都不相等，

若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；

若没有指定ELSE子句，则返回NULL。

例48. 查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：

当系号为1时，显示 “计算机系”；

当系号为2时，显示 “软件工程系”；

当系号为3时，显示 “物联网系”。

1234567 SELECT 班号 ,班名,CASE 系号**WHEN** 1 **THEN** '软件工程系'**WHEN** 2 **THEN** '计算机系'**WHEN** 3 **THEN** '物联网系'**END** AS 系号,班主任号**FROM** 班级表

搜索CASE函数

123456 CASE**WHEN** 布尔表达式1 **THEN** 结果表达式1**WHEN** 布尔表达式2 **THEN** 结果表达式2 …**WHEN** 布尔表达式n **THEN** 结果表达式n[ **ELSE** 结果表达式n+1 ]**END**

按从上到下的书写顺序计算每个WHEN子句的布尔表达式。

返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。

如果没有取值为TRUE的布尔表达式，

则当指定了ELSE子句时,返回ELSE子句中指定的结果；

如果没有指定ELSE子句，则返回NULL。

例48用搜索CASE来做：

1234567 SELECT 班号 ,班名,CASE**WHEN** 系号=1 **THEN** '软件工程系'**WHEN** 系号=2 **THEN** '计算机系'**WHEN** 系号=3 **THEN** '物联网系'**END** AS 系号,班主任号**FROM** 班级表

例49.查询“M01F011”号课程的考试情况，列出学号、课程号和成绩，同时将百分制成绩显示为等级。

12345678910 SELECT 学号,课程号,CASE**WHEN** 成绩 >= 90 **THEN** '优'**WHEN** 成绩 BETWEEN 80 AND 89 **THEN** '良'**WHEN** 成绩 BETWEEN 70 AND 79 **THEN** '中'**WHEN** 成绩 BETWEEN 60 AND 69 **THEN** '及格'**WHEN** 成绩 <60 **THEN** '不及格'**END** 成绩**FROM** 成绩表**WHERE** 课程号 = 'M01F011'

CASE函数(续)

例50.统计每个班男生和女生的数量各是多少，统计结果的表头为，班号，男生数量，女生数量。

1234 SELECT 班号,COUNT(CASE WHEN 性别=‘男’ **THEN** ‘男’ **END**) 男生数,COUNT(CASE WHEN 性别=‘女’ **THEN** ‘女’ **END**) 女生数**FROM** 学生表 **GROUP** BY 班号

例51.判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。

123456789101112131415 **SELECT**CASE**WHEN** GRADE BETWEEN 85 AND 100 **THEN** '优'**WHEN** GRADE BETWEEN 70 AND 84 **THEN** '良'**WHEN** GRADE BETWEEN 60 AND 69 **THEN** '及格'**ELSE** '不及格'**END** 等级, COUNT(*) 人数**FROM** SC**GROUP****BY**CASE**WHEN** GRADE BETWEEN 85 AND 100 **THEN** '优'**WHEN** GRADE BETWEEN 70 AND 84 **THEN** '良'**WHEN** GRADE BETWEEN 60 AND 69 **THEN** '及格'**ELSE** '不及格'**END**

—表示注释

连接

join表示连接哪个表，on表示条件

1
2
3
4
SELECT order_id,customers.first_name
from orders
    join customers
    on orders.customer_id=customers.customer_id

跨数据库表格连接

1
2
3
4
5
6
use sql_store;
select *
from order_items as oi
-- sql_inventory是另一个数据库
join sql_inventory.products as p
on oi.product_id=p.product_id

自连接

1
2
3
4
5
6
use sql_hr;

select e.employee_id,e.first_name,m.first_name
from employees as e
join employees as m
 on e.reports_to=m.employee_id

多表连接，多个join和on

1
2
3
4
5
6
select  o.order_id,o.order_date,c.first_name,c.last_name,o.status
from orders o
join customers c
    on o.customer_id=c.customer_id
join order_statuses as os
    on o.status=os.order_status_id

多条件连接，加and语句

1
2
3
4
5
select *
from order_items oi
join order_item_notes oin
 on oi.order_id=oin.order_Id
 and oi.product_id=oin.product_id

外连接

1
2
3
4
5
6
select *
from customers c
--这里的left指的就是customers，表示返回所有customers
left join orders o
on c.customer_id=o.customer_id
order by c.customer_id
1
2
3
4
5
6
select *
from customers c
--这里的right指的是orders，表示返回所有的orders
right join orders o
on c.customer_id=o.customer_id
order by c.customer_id

多表外连接

1
2
3
4
5
6
7
select order_date,order_id,first_name,name as shipper,status
    from orders
left join shippers
    on orders.shipper_id = shippers.shipper_id
join customers
        on orders.customer_id = customers.customer_id

USING 相当于简化了on这个条件，前提是两张表里的列名字要一样

1
2
3
4
5
6
7
select order_date,order_id,first_name,name as shipper,status
    from orders
left join shippers
    using (shipper_id)
join customers
        using (customer_id)

交叉连接，就是把两张表里所有可能的组合情况列出来

1
2
3
select *
from shippers
cross join products

union可以合并多个查询语句的结果

1
2
3
4
5
select first_name
    from customers
union
select name
    from shippers

列属性

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
insert into customers(
                      customer_id,
                       first_name,
                        last_name, 
                        birth_date,
                         phone,
                          address,
                           city,
                            state,
                             points
)
VALUES (
DEFAULT,
'Smith',
'Join',
'1990-01-01',
'123456',
'address',
'newYork',
1,
DEFAULT)

插入多行

1
2
3
4
INSERT INTO shippers (name)
VALUES ('shipper1'),
       ('shipper2'),
       ('shipper3')

插入分层行

1
2
3
4
5
6
7
insert into orders (customer_id,order_date,status)
VALUES (1,'2019-01-02',1);
insert into order_items
VALUES
--last_insert_id()自动获取上次插入的id
    (last_insert_id(),1,1,2.95),
    (last_insert_id(),2,1,3.95)

创建复制表

1
2
create table orders_archived as
select * from orders

更新数据

1
2
3
update invoices
set payment_total=10,payment_date='2019-03-01'
where invoice_id=1

子查询

1
2
3
4
5
--子查询不需要去翻表看数值，只需要写语句记住名字就行
update invoices
set payment_total=10,payment_date='2019-03-01'
where client_id=
      (select client_id from clients where name='Myworks')

删除列

1
2
delete from invoices
where invoice_id=1
编程语言
#sql
sql基础语法学习
http://example.com/2024/01/10/sql基础语法学习/
作者
WoodQ
发布于
2024年1月10日
许可协议