SQL快速入门

本篇为视频课程的笔记整理。

原视频链接：https://www.youtube.com/watch?v=gvRXjsrpCHw&t=40s

如有错误，敬请指正。

1 What’s database?

• 头脑：脑容量过小
• 书本：找特定的东西速度慢
• 电脑：大量资料、速度快

2 资料库管理系统（DSMS）

• 查找特定信息

3 资料库的分类

• 关联式资料库 VS 非关联式资料库

有点像高中技术学考的内容

4 什么是SQL？

• SQL：Structure Query Language

5 Tables and keys

几乎和高中技术一模一样（×）

• 设定为主键的属性可以唯一地表示每一笔资料。
• 有底线的为主键

• 外键：增加表格与表格之间的关联，外键必须对应到主键

• 两个主键的情况：通过同时设定两个主键唯一表示同一笔资料

6 创建资料库

6.1 单表格操作：

CREATE DATABASE sql_tutorial; # 创建资料库

SHOW DATABASES; # 显示资料库

DROP DATABASE sql_tutorial; # 删除资料库

关键字一般用大写；非关键字小写。

6.1.2 创建表格

USE sql_tutorial;

CREATE TABLE student(

student_id INT PRIMARY KEY,

name VARCHAR(20),

major VARCHAR(20)

);

DESCRIBE student; #展示表格

DROP TABLE student;

6.1.3 输入表格中值

ALTER TABLE student ADD gpa DECIMAL(3,2); #在表格中添加一列gpa，是小数，总共3位数，小数占2位

ALTER TABLE student DROP COLUMN gpa; #删除表格中gpa的属性

INSERT INTO student VALUES(1,’小白’,’历史’);

INSERT INTO student VALUES(2,’小黑’,’历史’);

INSERT INTO student VALUES(3,’小绿’,NULL);

SELECT * FROM student; #从全部student表格搜索

INSERT INTO student(name,major,student_id) VALUES(‘小红’,’英语’,5); #可以自己定义顺序，如果没有写某个属性，就把该行资料的该属性定义为NULL

6.1.4 constraint 限制、约束，预设值：DEFAULT

DROP TABLE student;

CREATE TABLE student(

student_id INT PRIMARY KEY,

name VARCHAR(20) NOT NULL, #后面的表示限制，该限制为不能空白

major VARCHAR(20) UNIQUE #该列值要唯一，把UNIQUE改为 DEFAULT ‘历史’ 表示把major的预设值改为历史，在没有major的输入时，自动表示历史。

);

INSERT INTO student VALUES(1,’小白’,’历史’);

INSERT INTO student VALUES(2,’小黑’,’英语’);

DESCRIBE student;

CREATE TABLE student(

student_id INT PRIMARY KEY,

name VARCHAR(20) NOT NULL, #后面的表示限制，该限制为不能空白

major VARCHAR(20) DEFAULT ‘历史’

);

INSERT INTO student (student_id,name) VALUES(2,’小白’);

DESCRIBE student;

SELECT * FROM student;

#AUTO_INCREMENT, #id自动增加，不用在输入时写id

DROP TABLE student;

CREATE TABLE student(

student_id INT AUTO_INCREMENT, #id自动增加，不用在输入时写id

name VARCHAR(20) NOT NULL, #后面的表示限制，该限制为不能空白

major VARCHAR(20) DEFAULT ‘历史’ ,

PRIMARY KEY(student_id)

);

INSERT INTO student (major,name) VALUES(‘英语’,’小白’);

INSERT INTO student (major,name) VALUES(‘英语’,’小黑’);

DESCRIBE student;

SELECT * FROM student;

6.1.5 修改、删除资料

#修改 UPDATE

SET SQL_SAFE_UPDATES = 0; #关闭预设自动更新

DROP TABLE student;

DROP TABLE student;

CREATE TABLE student(

student_id INT AUTO_INCREMENT,

name VARCHAR(20), #后面的表示限制，该限制为不能空白

major VARCHAR(20),

score INT,

PRIMARY KEY(student_id)

);

INSERT INTO student (major,name,score) VALUES(‘英语’,’小白’,70);

INSERT INTO student (major,name,score) VALUES(‘生物’,’小蓝’,72);

INSERT INTO student (major,name,score) VALUES(‘数学’,’小绿’,76);

INSERT INTO student (major,name,score) VALUES(‘英语’,’小红’,71);

SELECT * FROM student;

UPDATE student #把英语改成英语文学

SET major = ‘英语文学’

WHERE major = ‘英语’;

UPDATE student #把第三个学生的major改为英语文学

SET major = ‘英语文学’

WHERE student_id = 3;

UPDATE student #把生物或者数学的改成生数

SET major = ‘生数’

WHERE major = ‘生物’ OR major= ‘数学’;

UPDATE student #把第三个学生的major改为英语文学,并且同时把名字也改了

SET major = ‘英语文学’, name = ‘小灰’

WHERE student_id = 1;

UPDATE student #不写条件的话，就会吧所有的major都改变

SET major = ‘英语文学’;

#删除 DELETE FROM

DELETE FROM student #把编号为4的学生删掉

WHERE student_id = 4;

SELECT * FROM student;

DELETE FROM student #可以用and选多个条件

WHERE name = ‘小红’ AND major = ‘英语文学’;

DELETE FROM student #根据成绩

WHERE score < 71; #不等于，不写where就会把整个表格删掉

##取得资料 SELECT 、 LIMIT

#SELECT

SELECT major, name FROM student; #SELECT 后接要搜寻的内容，*表示所有

SELECT * FROM student;

#排序 ORDER BY 由低到高；ORDER BY 属性 DESC 由高到低；ORDER BY 属性1,属性2 先按属性1排，如果1一样，再按2排序

SELECT * FROM student

ORDER BY score;

SELECT * FROM student

ORDER BY score DESC; # 由高到低

SELECT * FROM student

ORDER BY score, student_id; # 逐项根据属性进行排序

6.1.6 LIMIT n 限制回传的数量为n笔，就是前n行。

SELECT * FROM student

ORDER BY student_id

LIMIT 2;

SELECT * FROM student

ORDER BY student_id

LIMIT 2;

#用WHERE进行限制

SELECT * FROM student

WHERE major= ‘英语’ AND score <> 71;

SELECT * FROM student

WHERE major IN(‘英语’ , ‘历史’ , ‘数学’);#等价于下面用or联接

SELECT * FROM student

WHERE major= ‘英语’ OR major=’历史’ OR major=’数学’;

SELECT、where、order任意搭配使用。

6.2 多表格操作：

6.2.1 创建公司资料表格

CREATE DATABASE sql_tutorial;

SHOW DATABASES;

USE sql_tutorial;

DROP TABLE student;

SET SQL_SAFE_UPDATES = 0; #关闭预设自动更新

#创建表格

CREATE TABLE employee(

emp_id INT PRIMARY KEY,

name VARCHAR(20),

brith_date DATE,

sex VARCHAR(1),

salary INT,

branch_id INT,

sup_id INT

);

DROP TABLE employee;

describe TABLE employee;

#创建部门表格

CREATE TABLE branch(

branch_id INT PRIMARY KEY,

branch_name VARCHAR(20),

manager_id INT,

FOREIGN KEY (manager_id) REFERENCES employee(emp_id) ON DELETE SET NULL #FOREIGN KEY表示外键；REFERENCES 表示外键接的表格名，再加外接的表格的primary key

);

#对公司表格补上外键foreign key，

ALTER TABLE employee

ADD FOREIGN KEY(branch_id) #加外键 branch_id属性

REFERENCES branch(branch_id) #对应到branch表格的branch_id的属性

ON DELETE SET NULL;

ALTER TABLE employee

ADD FOREIGN KEY(sup_id) #加外键sup_id

REFERENCES employee(emp_id) #对应到branch表格的branch_id的属性

ON DELETE SET NULL;

#创建客户表格

CREATE TABLE client(

client_id INT PRIMARY KEY,

client_name VARCHAR(20),

phone VARCHAR(20)

);

#创建worh_with表格

CREATE TABLE work_with(

emp_id INT,

client_id INT,

total_sales INT,

PRIMARY KEY ( emp_id, client_id),

FOREIGN KEY (emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,

FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE );

#填写表格数据

#有foreign key时，对应的表没有相应资料时，直接输入会报错，要用NULL代替

INSERT INTO branch VALUES(1,’研发’,NULL);

INSERT INTO branch VALUES(2,’行政’,NULL);

INSERT INTO branch VALUES(3,’查询’,NULL);

INSERT INTO branch VALUES(4,’查询’,NULL);

INSERT INTO employee VALUES(206,’小黄’,’1998-10-08’,’F’,50000,1,NULL);

INSERT INTO employee VALUES(207,’小绿’,’1995-10-09’,’M’,51000,2,206);

INSERT INTO employee VALUES(208,’小灰’,’1993-11-24’,’M’,23000,3,207);

INSERT INTO employee VALUES(209,’小黑’,’1996-10-08’,’M’,36000,3,208);

INSERT INTO employee VALUES(210,’小红’,’1997-07-05’,’F’,50000,1,209);

#修改

UPDATE employee SET branch_id = ‘3’

WHERE emp_id = 209;

select * from employee;

desc employee;

DROP TABLE employee;

#把原本branch的manager_id=NULL改回来

UPDATE branch

SET manager_id=206

where branch_id=1;

UPDATE branch

SET manager_id=207

where branch_id=2;

UPDATE branch

SET manager_id=208

where branch_id=3;

INSERT INTO client VALUES(400,’阿狗’,’1234567’);

INSERT INTO client VALUES(401,’阿猫’,’1673692’);

INSERT INTO client VALUES(402,’来福’,’1894738’);

INSERT INTO client VALUES(403,’路西’,’1746952’);

INSERT INTO client VALUES(405,’杰克’,’9947538’);

INSERT INTO work_with VALUES(206,400,70000);

INSERT INTO work_with VALUES(207,400,’56000’);

INSERT INTO work_with VALUES(208,402,’35000’);

INSERT INTO work_with VALUES(209,403,’54000’);

INSERT INTO work_with VALUES(210,404,’64000’);

DROP TABLE worh_with;

desc work_with;

select * from work_with;

6.2.2 聚合函数 aggregate functions

#取得员工数目

select count(*) from employee; #表格里有基笔资料

select count(sup_id) from employee; #count()里对应的属性的资料有几笔

#取得所有出生在1996-10-01之后出生的女性员工

select count(*) from employee

where brith_date > ‘1996-10-01’ and sex=’F’;

#取得所有员工的平均薪水

select avg(salary) from employee;

#取得所有员工的总和薪水 sum

select sum(salary) from employee;

#取得最高/低员工的薪水 max,min

select max(salary) from employee;

##wildcards 万用字元 %表示多个字元，_表示一个字元，可以用一个，也可以前后都用

#取得电话号码尾数是567的客户

select * from client

where phone like ‘%567’;

#取得电话号码开头是189的客户

select * from client

where phone like ‘189%’;

#取得姓阿的员工

select * from client

where client_name like ‘阿%’;

#取得生日在10月的员工

select * from employee

where brith_date like ‘_____10%’;

底线代表一个字母，%代表多个。

select * from employee;

6.2.3 union 连集

可以合并搜寻的结果，合并的属性资料形态要一样，不同表格的属性数目要一样，不能用表格1的2个属性去和表格2的1个属性合并

#员工名字和客户名和部门名字字合并

select (name) from employee

union

select (client_name) from client

union

select (branch_name) from branch;

select name,emp_id from employee

union

select client_name,client_id from client;

select name as total_name,emp_id as total_id #改变回传的属性名称

from employee

union

select client_name,client_id from client;

6.2.4 join 连接

insert into branch values(4,’瑜伽’,NULL);

#取得所有部门经理的名字，把employee和branch两个表格连接起来，条件是连个id一样。

select * from employee

#select emp_id, name, branch_name from employee

join branch

on emp_id = manager_id;

#如果不同表格有相同属性，用表格.属性名称进行区分，说明表格属性来源

select employee.emp_id, employee.name, branch.branch_name from employee

join branch

on employee.emp_id = branch.manager_id;

#把左/右边的表格都回传，右/左边边表格符合条件才回传,否则回传NULL, left join/right join

select *

#from employee left join branch

from employee right join branch

on emp_id = manager_id;

select * from branch;

delete from branch where branch_id = 4;

6.2.5 subquery 子查询

在一个查询语句里面查询另外一个结果，就是在一个查询语句里面插入另外一个查询结果

#找出研发部门的经理名字：先找研发部门的manager_id(放在括号里面的，就是子查询的部分），再用id进行查找名字

select name from employee

where emp_id = (

​ select manager_id from branch

where branch_name = ‘研发’

);

#找出一位客户销售金额超过50000的员工名字:当子查询回传结果不止一个，要用in不用=

select name from employee

where emp_idin (

​ select emp_id from work_with

where total_sales > 50000

);

6.2.6 on delete 删除某一行

on delete set NULL：如果删掉某个资料1，其他表格的资料2用外键对应的该资料1，在其他表格中把资料2设为null,当资料2为primary key时，不能设置为null，只能为on delete cascade

#on delete cascade：如果删掉某个资料1，其他表格的资料2用外键对应的该资料1，在其他表格中把资料2也删掉

#创建worh_with表格

CREATE TABLE work_with(

emp_id INT,

client_id INT,

total_sales INT,

PRIMARY KEY ( emp_id, client_id),

FOREIGN KEY (emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,

FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE );

#创建部门表格

CREATE TABLE branch(

branch_id INT PRIMARY KEY,

branch_name VARCHAR(20),

manager_id INT,

FOREIGN KEY (manager_id) REFERENCES employee(emp_id) ON DELETE SET NULL #FOREIGN KEY表示外键；REFERENCES 表示外键接的表格名，再加外接的表格的primary key

);

delete from employee

where emp_id= 207;

select * from work_with;

select * from branch;

update branch

set manager_id=207

where branch_id=2;

INSERT INTO employee VALUES(207,’小绿’,’1995-10-09’,’M’,51000,2,206);

INSERT INTO work_with VALUES(207,400,’56000’);