SQL学习笔记

SQL 是一门 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。SQL主要用于在关系型数据库中进行数据的存储、查询、修改和删除等操作。通过使用SQL语句，可以方便地操作数据库，实现各种数据管理需求。

目前存在着多种不同版本的 SQL 语言，MySQL、Oracle、PostgreSQL、SQL Server

（拓展—非关系型数据库：MogoDB、Redis、DynamoDB、Elaticsearch）

一、创建数据库

1. 创建数据库

CREATE DATABASE test;  //创建数据库

2. 显示所有的数据库

SHOW DATABASES;

在Navicat中运行，可以看到如下运行结果：

3. 删除数据库

DROP DATABASE test;

4. 使用数据库

USE test;

二、创建数据表

数据形态

INT                                              --整数

DECIMAL(m,n)                           --小数                

VARCHAR(n)                              --字符串

BLOB                                          --（Binary Large Object）图片 影片 档案......

DATE                                          --'YYYY-MM-DD' 日期

TIMESTAMP                               --'YYYY-MM-DD HH:MM:SS' 计时日期

1. 创建student表格

CREATE TABLE student (
	student_id INT PRIMARY KEY,  
	name VARCHAR(20) NOT NULL,   //这里加了限制，名字不能为空
	major VARCHAR(20)
);

补充——限制条件

NOT NULL                        --不能为空

UNIQUE                            --唯一

DEFAULT                          --预设值，不填默认为这里预设的值

AUTO_INCREMENT        --自动增1

2. 展示student表

DESCRIBE student;

3. 删除student表

DROP TABLE student;

4. 往student表格中，追加一个gpa属性

ALTER TABLE student ADD gpa DECIMAL(3,2);  //总数为3位数，有两位小数的形式

5. 删除student表格中gpa属性

ALTER TABLE student DROP COLUMN gpa;

三、向表中存入数据

1. 插入数据（insert into）

INSERT INTO student VALUES(1,'小白','历史');
INSERT INTO student(name,major,student_id) VALUES('小紫','生物',2);

2. 从数据表中选取数据（select）

SELECT * FROM student WHERE student_id=1;  //'*'表示选取表中所有列

3. 修改（update）

将student表格中major为“历史”或"生物"的的数据，改为“历史文学”

//AND & OR 运算符用于基于一个以上的条件对记录进行过滤
UPDATE student SET major='历史文学' WHERE major='历史' OR major='生物';

 将student表格中id小于2的数据，name和major属性做修改

UPDATE student SET name='小黑',major='物理' where student_id<2;

补充—WHERE 子句中的运算符 

运算符	描述
=	等于
<>	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式
IN	指定针对某个列的多个可能值

4. 删除（delete）

删除student表格中id为1的数据

DELETE FROM student WHERE student_id=1;

 四、实例

 1.添加表

创建四张表—employee、branch、client、works_with

//创建employee雇员表
CREATE TABLE employee(
    emp_id INT PRIMARY KEY,
    nme VARCHAR(20),
    birth_date DATE,
    sex VARCHAR(1),
    salary INT,
    branch_id INT,
    sup_id INT
);

//创建branch表
CREATE TABLE branch(
    emp_id INT PRIMARY KEY,
    branch_name VARCHAR(20),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employee (emp_id) ON DELETE SET NULL   --级联置空,外键对应不到就删掉
    
);

//创建client表
CREATE TABLE client(
    client_id INT PRIMARY KEY,
    client_name VARCHAR(20),
    phone VARCHAR(20)
);

//创建works_with表
CREATE TABLE works_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
);

 2.插入数据

//向employee表中插入数据
INSERT INTO employee VALUES(206,'小黄','1998-10-08','F',50000,1,NULL);
INSERT INTO employee VALUES(207,'小绿','1985-09-16','M',29000,2,206);
INSERT INTO employee VALUES(208,'小黑','2000-12-19','M',35000,3,206);
INSERT INTO employee VALUES(209,'小白','1997-01-22','F',39000,3,207);
INSERT INTO employee VALUES(210,'小灰','1925-11-10','F',84000,1,207);

//向branch表中插入数据
INSERT INTO employee VALUES(1,'研发',206);
INSERT INTO employee VALUES(2,'行政',207);
INSERT INTO employee VALUES(3,'咨询',208);

//向client表中插入数据
INSERT INTO employee VALUES(400,'阿狗','25434335');
INSERT INTO employee VALUES(401,'阿猫','25633889');
INSERT INTO employee VALUES(402,'旺财','45354335');
INSERT INTO employee VALUES(403,'露西','54356335');
INSERT INTO employee VALUES(404,'艾瑞克','18383783');

//向works_with表中插入数据
INSERT INTO employee VALUES(206,400,7000);
INSERT INTO employee VALUES(207,401,24000);
INSERT INTO employee VALUES(208,402,9800);
INSERT INTO employee VALUES(209,403,24000);
INSERT INTO employee VALUES(210,404,87940);

3.查询

Aggregate 函数：

• AVG() - 返回平均值
• COUNT() - 返回行数
• FIRST() - 返回第一个记录的值
• LAST() - 返回最后一个记录的值
• MAX() - 返回最大值
• MIN() - 返回最小值
• SUM() - 返回总和

（1）取得员工人数

SELECT COUNT(*) FROM employee;  //COUNT返回匹配指定条件的行数

（2）取出所有出生日期在1970-01-01之后的女性员工人数

SELECT COUNT(*)
FROM employee
WHERE birth_date>'1970-01-01' AND sex='女';

（3）取得所有员工的平均薪水

SELECT AVG(salary) FROM employee;

（4）取得所有员工薪水的总和

SELECT SUM(salary) FROM employee;

（5）取得薪水最高的员工

SELECT MAX(salary) FROM employee;

（6）取得薪水最高的员工

SELECT MIN(salary) FROM employee;

通配符

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符列中的任何单一字符

（1）取得电话号码尾号是335的客户

SELECT * FROM client WHERE phone LIKE '%335%';

（2）取得姓艾的客户

SELECT * FROM client WHERE client_name='艾';

（3）取得生日在12月的员工

SELECT * FROM employee WHERE birth_date LIKE '_____12%';  //选取birth_date以一个任意五个字符开始，然后是12的所有客户

并集union

UNION 操作符合并两个或多个 SELECT 语句的结果。

（1）员工名字union客户名字

SELECT name 
FROM employee 
UNION
SELECT client_name
FROM client
UNION
SELECT branch_name
FROM branch;

（2）员工id+员工名字  union 客户id+客户名字

SELECT emp_id,name
FROM employee
UNION
SELECT client_id,name
FROM client;

（3）员工薪水 union 员工金额

SELECT salary AS total_money   //将最后union出来的列表名改为total_money
FROM employee
UNION
SELECT total_sales
FROM works_with;

连接join

（1）取得所有部门经理的名字

SELECT * 
FROM employee
JOIN branch
ON emp_id = manager_id;

子查询subquery

（1）找出研发部门的经理名字

SELECT name
FROM employee
WHERE emp_id=(
    SELECT manager_id
    FROM branch
    WHERE branch_name='研发';
);

（2）找出一位客户销售金额超过50000的员工名字

SELECT name
FROM employee
WHERE emp_id IN(
    SELECT 'emp_id'
    FROM works_with
    WHERE total_sales>50000
);