23-02-03笔记

SQL

什么是SQL

SQL即structure qyrery language 结构化查询语言。 是用于访问和处理数据库的标准的计算机语言。

SQL 指结构化查询语言
SQL 使我们有能力访问数据库
SQL 是一种 ANSI 的标准计算机语言

SQL语言

1、DDL：数据定义语言 面试可能会用到。创建库，删除库，创建视图，函数，存储过程，触发器

2、DCL：数据控制语言 创建用户，删除用户，赋予权限，删除权限等。

3、DML：数据操作语言。* insert、update、delete 表

4、DQL：数据查询语言。** select 表

DDL

– 1.查询全部数据，全部行，全部列

SELECT * FROM t_student -- 查询t_student表中的所有信息

– 2.查询指定列

SELECT `name`,`sex`,`birthday` FROM t_student   --查询t_student表中所有姓名、性别、生日的信息

– 3.查询指定列时，起别名，as可以省略
查询到结果后显示的列名由原来表中的列名为起的别名

SELECT `name` as 姓名, `sex` as 性别, `birthday` 出生日期 FROM t_student  --查询t_student表中所有姓名、性别、生日的信息

– 4.表起别名，当只查询一个表的时间，表名前缀可以省略

SELECT `name`,t.`sex`,t.`birthday` FROM t_student as t

– 5.条件查询，使用where来指定条件。精确匹配。

SELECT * FROM t_student WHERE `stu_id`= 'st_0000'  --查询
SELECT * FROM t_student WHERE `id` = 100
SELECT * FROM t_student WHERE `name` = '龙芳'

– 6.比较运算符：> < >= <= = <> !=

SELECT * FROM t_student WHERE `name` != '龙芳'
SELECT * FROM t_student WHERE `sex` <> '男'
SELECT * FROM t_student WHERE `height` > 175

– 7.算术运算符：+ - * / %

SELECT * FROM t_student WHERE `height`+10 > 175
SELECT `height`%10 FROM t_student WHERE `height`%10 > 5

– 8.逻辑运算符：and or not

SELECT * FROM t_student WHERE sex = '男' AND `height` < 170
SELECT * FROM t_student WHERE l`ast_name` = '张' OR (`height` > 170 AND `sex` = '女')
SELECT * FROM t_student WHERE NOT `sex` = '男'
SELECT * FROM t_student WHERE `pinyin` IS NULL
SELECT * FROM t_student WHERE NOT `pinyin` IS NULL
SELECT * FROM t_student WHERE `pinyin` IS NOT NULL

– 9 区间查询：BETWEEN…and …

SELECT * FROM t_student where `weight` BETWEEN 60 AND 80

– 10列运算

SELECT `id`,`stu_id`,`weight`*2 as 斤 FROM t_student WHERE `id` > 100 AND `id` < 105
SELECT `first_name`, `last_name`, CONCAT(`last_name`,`first_name`) FROM t_student

– 11模糊查询

SELECT * FROM `t_student` WHERE `name` like '%官%'
SELECT * FROM `t_student` WHERE `name` LIKE '_晓%'

– 12正则表达式 %代表0或多个字符 _表示一个字符

SELECT * FROM t_student WHERE `name` RLIKE '^.{4,}$'

– 二、子查询
– 1. =子查询

SELECT * FROM t_student WHERE `class_id `= (
	SELECT id FROM t_class where `class_name` = '100115-Java') or class_id = (
	SELECT id FROM t_class where `class_name` = '100802-Java'
)

– 2.in子查询

-- 
SELECT * FROM t_student WHERE `class_id` IN (
	SELECT id FROM t_class where `class_name` IN('100115-Java' ,  '100802-Java')
)

– 3.exists子查询

-- 如果存在班级号为0的便返回所有学生信息
SELECT * FROM t_student WHERE EXISTS (
	SELECT `id` FROM t_class where `class_id` = 0
)

– 4. all和any 函数

--查询身高比所有iq>99且体重>50的女生高的学生信息
SELECT * FROM t_student WHERE `height` > ALL(
	SELECT `height` FROM t_student WHERE sex = '女' AND `iq` > 99 AND `weight` > 50
	)
--查询身高比任一iq>99且体重>50的女生高的学生信息
SELECT * FROM t_student WHERE `height` > ANY(
	SELECT `height` FROM t_student WHERE sex = '女' AND `iq` > 99 AND `weight` > 50
	)

– 5.多层嵌套

SELECT * FROM t_student WHERE `class_id` IN (
	SELECT `id` FROM t_class WHERE EXISTS(
		SELECT `id` from t_class WHERE `major_id` < 3
	)
)

– 6. 表子查询, 子查询所返回的表，必须起别名

SELECT id, `name` FROM (
	SELECT * FROM t_student where `id` >= 900
)t WHERE `id` < 980

– 7. 列子查询

-- 查询t_student 表中的所有 id、（通过查询t_class表中对应班级号获取的班级名）、姓名
SELECT `id`, (SELECT `class_name` FROM `t_class` WHERE `id` = `class_id`) AS 班级名称, `name` FROM t_student

– 8. 相关子查询 性能极差

-- 查询出比所在班级平均身高要高的女生
SELECT * FROM t_student t WHERE sex = '女' AND height > (
	SELECT AVG(height)  FROM t_student where class_id = t.class_id
)

– 三、结果排序 ORDER BY ASC代表升序（可省略）， DESC代表降序

SELECT * FROM t_student WHERE sex = '男' ORDER BY height ASC

– 多列排序

SELECT * FROM t_student WHERE sex = '男' ORDER BY height ASC,
 weight DESC

limit和offset

-- 限定数量查询 limit 和 offset 第一个参数表示从第几行开始查，第二个是表示查几行
SELECT * FROM t_student ORDER BY id desc LIMIT 10,5
SELECT * FROM t_student ORDER BY id DESC LIMIT 10 OFFSET 5 -- 等同于 LIMIT 5,10
``