sql笔记

1、数据库

1、数据库概念

数据库是存储的数据的集合，为了方便我们进行数据的管理，使用数据库管理系统程序。

2、数据库分为关系（relational）型数据库和非关系（NoSql）型数据库

关系型数据库中我们将使用关系相互连接的表称为关系数据库

Mysql、SQL server、oracle标准规范是SQL规范。所以大部分SQL是一样的，如今最受欢迎的是MySQL（因为：免费、开源）。

非关系数据库：他们没有表格或关系。不懂SQL他们有自己的查询语言。

3、SQL历史

SQL最初是在IBM开发，70年代成为SEQUEL（Structrred English Query Language）

2、实操

1、表字段的设计

	ID用于唯一标识。

我们在订单表(另一个表）使用用户的ID，通过用户ID查询用户的信息，而不是把用户信息也放一份到订单表中，用户信息修改后查询的还是用户的正确信息，能防止信息的错误。

3、检索即查询信息

USE sql_store;

SELECT *
FROM customers
WHERE customer_id = 1
ORDER BY first_name

使用库，做选择、从哪里、设过滤、搞排序

USE ， SELECT ， FROM ， WHERE ， ORDER BY

4、探讨SELECT语句

1、查询的字段值为数字，可以进行基本的运算（加减乘除）

SELECT 后填写的字段（行名)，查询出来就先显示哪一行。数字的字段（行）可以进行基本的运算。

2、规范写法：

SELECT 
    last_name,
    first_name,
    points,
    points * 10 +100
FROM customers

3、设置别名（AS），‘’ ：单引号表示你想要的会将内容原封不动提交。

AS 后填写别名，单引号表示你想要的，会原封不动提交内容给程序。

4、去重复 DISTINCT

通过使用distinct 去掉重复的内容。

5、学习WHERE

sql语句中大小写不区分，即使的是单引号中也不区分。

1、用等于设置条件

过滤，进行条件判断：state字段内容等于VA

2、用大于、小于、等于符号设置条件

字段名必须正确，包括“—“也要写上，否则报错

SELECT *
FROM customers
WHERE birth_date > '1990-01-01';

3、多条件使用 and or

SELECT * 
FrOM customers
WHERE birth_data > "1990-01-01" or 
	  (points > 1000 and state = 'VA')

4、相反使用 NOT

一开始查出1，3，而使用NOT相反条件后查出2，4

SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01' or points > 1000)

5、在...之间：BETWEEN - AND(包含端点)

SELECT *
FROM customers
WHERE birth_data BETWEEN '1990-01-01' AND '2000-01-01';

6、like 相似

目的：找出顾客表，要求输出地址包含TEAIL或AVENUE的顾客记录

SELECT *
FROM customers
WHERE address LIKE '%TEAIL%' OR 
	  address LIKE '%AVENUE%'
	  
-- % any number of characters
-- _ single character

符号的作用：占位符 % 表示任意长度的字母， _ 表示一个字母。

7、正则表达 REGEXP

目的：查找顾客表,要求输出

1、姓名ELKA或AMBUR的顾客

2、名以EY或ON结尾的顾客

3、名以ＭＹ开头并且包含ＳＥ顾客

４、名中包含Ｂ，且Ｂ之后是Ｒ或Ｕ（多行输出卸载注解里）

SELECT * 
FROM customers
WHERE first_name REGEXP 'elka|ambur'
-- WHERE last_nam REGEXP 'ey$|on$'
-- WHERE last_name REGEXP '^my|se'
-- WHERE last_name REGEXP 'b[ru]'

-- ^ beginning
-- $ end
-- | logical or
-- [abcd]
-- [a-h]

6、面试题

SQL语句的排列顺序：

SELECT -- 选择显示的字段
FROM  -- 从哪张表中获取
WHERE  -- 过滤的条件
GROUP BY  -- 分组，提出的字段内容去重复
HAVING -- 等到结果后再次过滤
ORDER BY  -- 顺序
LIMIT  -- 显示的数据，从哪条开始，显示多少条

1、GROUP BY 分组

SELECT 
	name,
	grade,
	count(*),
	sum(salary),
	avg(salary),
	max(salary),
	min(salary)
FROM student
GROUP BY name;

7、面试实操

学习来源：https://zhuanlan.zhihu.com/p/70896731

1.学生表
Student(SId,Sname,Sage,Ssex)

SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

2.课程表
Course(CId,Cname,TId)
CId 课程编号,Cname 课程名称,TId 教师编号

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

3.教师表
Teacher(TId,Tname)
TId 教师编号,Tname 教师姓名

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

4.成绩表
SC(SId,CId,score)
SId 学生编号,CId 课程编号,score 分数

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

数据库表分析：

学生表（student）：SId,Sname,Sage,Ssex

课程表（Course）：CId,Cname,TId（教师编号）

教师表(Teacher)：TId,Tname

成绩表（SC）：SId, CId, score

1、查询“01”课程比“02”课程成绩高的学生的信息及课程分数

方法一：

-- 查询出 01 课程所有学生的成绩
SELECT *
FROM sc
WHERE cid = '01';

-- 查询出课程“02”所有学生的成绩
SELECT *
FROM sc
WHERE cid = '02';

-- 此处注意，from后填的表可以有两张表，将两张表连接在一起
SELECT * 
FROM (SELECT *
	FROM sc
	WHERE cid = '01') AS a,
	(SELECT *
	FROM sc
	WHERE cid = '02') AS b;

-- 此处注意，from后填的表可以有两张表，将两张表连接在一起
SELECT * 
FROM (SELECT *
	FROM sc
	WHERE cid = '01') AS a,
	(SELECT *
	FROM sc
	WHERE cid = '02') AS b
WHERE 
	a.sid = b.sid AND -- 成绩对比前提是同一个学生
	a.score > b.score; -- 对比成绩

--  有了新表取出需要的列
SELECT a.sid,a.score as class1,b.score as class2
FROM (SELECT *
	FROM sc
	WHERE cid = '01') AS a,
	(SELECT *
	FROM sc
	WHERE cid = '02') AS b
WHERE 
	a.sid = b.sid AND -- 成绩对比前提是同一个学生
	a.score > b.score; -- 对比成绩

JOIN

查询中右并联，某个表用：

SELECT *
FROM table1 
	RIGHT JOIN 
		table2 
	ON 
		table1.id = table2.id;

-- 即表示显示table1表后，在右边接上table2的内容，连接条件是 table1.id =table2.id

--  最后一步，题目是要求学生信息以及课程成绩
SELECT *
FROM student
		RIGHT JOIN
			(SELECT a.sid,a.score as class1,b.score as class2
			FROM (SELECT *
				FROM sc
				WHERE cid = '01') AS a,
				(SELECT *
				FROM sc
				WHERE cid = '02') AS b
			WHERE 
				a.sid = b.sid AND -- 成绩对比前提是同一个学生
				a.score > b.score) AS t1
			ON student.sid = t1.sid;

方法二

SELECT *
FROM student 
	RIGHT JOIN
		 (SELECT s1.sid,s1.score AS score01,s2.score AS score02 
			FROM sc AS s1
				JOIN sc AS s2
				ON s1.sid = s2.sid  -- 并联条件是sid相等
			WHERE s1.cid = '01' AND  -- 过滤条件，每一行中要符合条件 s1.cid = '01'
				s2.cid ='02' AND       -- 每一行中要符合条件 s2.cid = '02'
				s1.score > s2.score) AS t1 -- 每一行中要符合条件 s1.score > s2.score
		ON t1.sid = student.sid;     -- 过滤条件每一行中，要符合条件：student.sid = t.sid

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

-- 按学号分组，查询出每个学生的平均成绩
SELECT sid,AVG(score)
FROM sc
GROUP BY sid
HAVING AVG(score) >= 60;

-- 查找平均成绩大于等于60的学生相关信息
SELECT t1.*,s1.sname -- t1.* 表示t1的所有字段，加上s1表的sname字段
FROM student s1
	RIGHT JOIN
		(SELECT sid,AVG(score)
		 FROM sc
		 GROUP BY SId
		 HAVING AVG(score) >= 60) AS t1
	ON t1.sid = s1.sid;

8、INSERT INTO

insert into语句主要用于向表中插入新记录

INSERT INTO table_name
VALUES (value1,value2,value3,...);
-- 或
INSERT INTO table_name (column1,column2,column3,..)
VALUES (value1,value2,value3,...)

-- 插入多行数据
INSERT INTO table_name (column1,column2,..)
VALUES 
	(value1,value2,...),
	(value1,value2,...),
	(value1,value2,...);

实操：

INSERT INTO student (Sid,Sname,Sage,Ssex)
VALUES 
	(14,'zhou','1997-09-10','男'),
	(15,'yong','1998-01-10','男'),
	(16,'fan','1998-05-25','女');

9、update

update语句用于修改表中已存在的记录

UPDATE table_name
SET 
	column1 = value1,
	column2 = value2,
	column3 = value3
WHERE condition; -- 过滤条件比如 id = 3