SQL的一些学习笔记

最新推荐文章于 2024-06-14 08:50:02 发布

再学会吧

最新推荐文章于 2024-06-14 08:50:02 发布

阅读量357

点赞数 5

文章标签： sql 学习笔记

本文链接：https://blog.csdn.net/weixin_61866295/article/details/135081421

版权

数据库

资料库分为了开放式资料库（SQL）和非开放式资料库（noSQL / not just SQL）

开放式资料库管理系统（RDBMS）

MySQL
Oracle
PostgreSQL
SQL Server

非开放式资料库管理系统（NRDBMS）

MogoDB
Redis
DynamoDB
Elaticsearch

tables and key

设置成主键（primary key）的那个属性可以唯一的表示每一笔资料，eg：id。

主键的值不能为NULL

外键（foreign key）使表格与表格之间产生关联。外键对应到另一个表格的主键。

外键除了可以对应到其他表格，同时也可以对应到自身。

某个属性可以既是主键也是外键。

创建数据库CREATE DATABASE `sql_name`; 一般要将自己命名的数据库名字用 ` `括起来，防止与预设关键字冲突；

显示所有数据库：

SHOW DATABASES;

删除数据库：

DROP DATABASE sql_name; 	# 删除数据库sql_name;

创建表格之前需要先使用数据库USE `sql_name`;

在创建表格的时候需要设置每一个属性的资料形态，以下为常用的六种资料形态：

INT --整形
DECIMAL(m,n) --有小数点的数

DECIMAL(3,2) – 2.33总共有三位小数，小数点占了两位
VARCHAR(n) --字串

VARCHAR(10) – 最多可以存放字的长度，10表示最多可以存放长度为10
BLOB --（Binary Large Object）图片影片档案. . . ，存放二进制资
DATE --‘YYYY-MM-DD’ 日期
TIMESTAMP --‘YYYY-MM-DD HH:MM:SS’ 记录时间

创建表格：

CREATE TABLE `student`(
    `student_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `major` VARCHAR(20)
);
###############################################
CREATE  TABLE  `student`(
    `student_id` INT,
    `name` VARCHAR(20),
    `major` VARCHAR(20),
    PRIMARY KEY(`student_id`)
);
# 上下都可以

显示表格：

DESCRIBE `student`;		# 显示student表格

删除表格：

DROP TABLE `student`;	# 删除student表格

增加表格属性：

ALTER TABLE `student` ADD gpa DECIMAL(3,2);		
# 给student表格中增加gpa属性，格式为有小数点的数，3位数，小数点后两位

删除表格属性：

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

像表格中存入资料：

INSERT INTO `student` VALUES(1, '小白', '历史');
# 填入顺序和创建表格时的顺序保持一致
# 可以将不确定的信息设置成 null 
INSERT INTO `student`('name', 'major', 'student_id') VALUES('小白', '历史', 1);
# 也可以自定义顺序填写
INSERT INTO `student`('major', 'student_id') VALUES('历史', 1); 与 INSERT INTO `student`('name', 'major', 'student_id') VALUES(null, '历史', 1); 等价，在'name'这栏显示为null

在创建表格的时候可以给需要的属性增加constraints（限制、约束）：

CREATE TABLE `student`(
    `student_id` INT PRIMARY KEY AUTO_INCREMENT,	# 新增资料时自动+1
    `name` VARCHAR(20) NOT NULL,		# 不能为空
    `major` VARCHAR(20) UNIQUE			# 不能重复
    # `major` VARCHAR(20) DEFAULT `历史` 	# 设置默认值为历史
);

修改表格中属性内容：

UPDATE `student`
SET `major` = '英语文学'
# 多条件时 SET `name` = '小灰', `major` = '物理'
WHERE `major` = '英语';	
# 多条件时 WHERE `major` = '生物' OR `major` = '化学';
# 如果不加 WHERE，那么就是所有

删除表格中的某项：

DELETE FROM `student`
WHERE `student_id` = 4;
# 多条件时 WHERE `name` = `小灰` AND `major` = '物理';
# WHERE `score` < 60;
# 如果不加 WHERE，那么就是所有
'''
	>	大于
	<	小于
	>=	大于等于
	<=	小于等于
	=	等于
	<>	不等于
'''

取得资料：

SELECT * FROM `student`;	# 取得所有属性
SELECT `name`, `major` FROM `student`;	# 取得name和major属性
SELCET DISTINCT `sex` FROM `employee`;	# 不重复地返回sex属性
SELECT * FROM `student` ORDER BY `score` ASC;	# 根据score由小到大取出
# ASC 为默认，可以不写
SELECT * FROM `student` ORDER BY `score` DESC;	# 根据score由大到小取出
SELECT * FROM `student` ORDER BY `score`， `student_id`;	# 优先根据score排序，相同时按student_id排序
LIMIT 3；	# 取得前3笔资料
SELECT * FROM `student` ORDER BY `score` DESC LIMIT 2;	# 回传分数最高的前2个资料
SELECT * FROM `student` WHERE `major` = '英语' AND `student_id` = 1;	# 回传major是英语且student_id为1的资料
SELECT * FROM `student` WHERE `major` IN('历史', '英语', '生物');	
# 等价于下面这个
SELECT * FROM `student` WHERE `major` = '历史' OR `major` = '英语' OR `major` = '生物';

业务表格
设置外键：

# 创建表格时设置
create table `branch`(
    ···,
	···,
    foreign key (`manager_id`) references `employee`(`emp_id`) on delete set null,
    ···
);

# 随后添加
alter table `employee` add foreign key (`branch_id`) references `branch`(`branch_id`) on delete set null;

如果在给A表格添加内容的时候，其中的外键对应B表格的主键，但此时B表格还没有内容，此时是添加不上去的，可以优先考虑添加B表格或者把对应缺失的外键先设置为null之后在根据需要设置回正常内容。

聚合函数：

-- 1.取得员工人数
SELECT COUNT(*) FROM `employee`;	# 取得所有员工人数
SELECT COUNT(`sup_id`) FROM `employee`;	# 取得有sup_id属性的人数
-- 2.取得所有出生于1970-01-01之后的女性员工人数
SELECT COUNT(*) FROM `employee` WHERE `birth_data` > '1970-01-01' AND `sex` = 'F';
# COUNT()函数就是计数函数
-- 3.取得所有员工的平均薪水
SELECT AVG(`salary`) FROM `employee`;
# AVG()函数是计算平均值
-- 4.取得所有员工薪水的总和
SELECT SUM(`salary`) FROM `employee`;
# SUM()函数是计算总和
-- 5.取得薪水最高的员工
SELECT MAX(`salary`) FROM `employee`;
# MAX()函数可以取得指定属性的最大值
-- 6.取得薪水最低的员工
SELECT MIN(`salary`) FROM `employee`;
# MIN()函数可以取得指定属性的最小值

wildcards万用字元（字符？），%代表多个字元，_代表一个字元。

个人理解：%有点像*，意思是若干个字符；_则只能代表一个字符

-- 1.取得电话号码尾数是335的客户
SELECT * FROM `client` WHERE `phone` LIKE '%335';
SELECT * FROM `client` WHERE `phone` LIKE '254%'; # 取得开头是254的
# LIKE 是模糊查询， = 是精准查询
-- 2.取得姓艾的客户
SELECT * FROM `client` WHERE `client_name` LIKE '艾%';
-- 3.取得生日在12月的员工
SELECT * FROM `employee` WHERE `birth_data` LIKE '_____12%';	
# 五个下划线，因为 data 的格式是 xxxx-xx-xx, _____ 代表了xxxx-, % 代表了 -xx

union联集，可以把两个搜寻的结果合并在一起

-- 1.员工名字 union 客户名字
SELECT `name` FROM `employee` UNION SELECT `client_name` FROM `client`;
# 也可以再多合并，比如：
SELECT `name` FROM `employee`
UNION
SELECT `client_name` FROM `client`
UNION
SELECT `branch_name` FROM `branch`;
'''
UNION 只能属性的数目是一样的，一个和一个做合并，两个和两个做合并，如果属性的数目存在不一致则不能使用；另外，做合并的属性数据要是一致的，比如都是INT或者都是VARCHAR，否则是不能合并的。
'''
-- 2.员工id + 员工名字 union 客户id + 客户名字
SELECT `emp_id`, `name` FROM `employee` 
UNION 
SELECT `client_id`, `client_name` FROM `client`;
'''
回传的属性名称用的是第一个属性的名称，以上述为例，为`emp_id`和`name`,当然也可以改掉：
SELECT `emp_id` AS `total_id`, `name` AS `total_name `FROM `employee` 
UNION 
SELECT `client_id`, `client_name` FROM `client`;
'''
-- 3.员工薪水 union 销售金额
SELECT `salary` AS `total_money` FROM `employee` 
UNION 
SELECT `total_sales` FROM `works_with`;

join连接，可以将两个表格连接在一起

-- 取得所有部门经理的名字
SELECT `emp_id`, `name`, `branch_name` FROM `employee` JOIN `branch` ON `emp_id` = `manager_id`;
'''
branch表格中的外键连接到employee表格中,可以通过branch中的外键manager_id对应到employee中的emp_id查询
SELECT * FROM `employee` JOIN `branch` ON `emp_id` = `manager_id`;
employee 中有7个属性，branch 中有3个属性，最后取得的是相加起来10个属性
'''
如果两个表格中存在相同名称的属性，比如employee中的name和branch中的branch_name都是name,可以用以下方式来处理
SELECT `emp_id`, `name`, `branch_name` 
FROM `employee` 
JOIN `branch` 
ON `employee`.`emp_id` = `branch`.`manager_id`;
通过 . 来表示是属于哪个表格中的属性

-- LEFT JOIN
SELECT `emp_id`, `name`, `branch_name` 
FROM `employee` LEFT JOIN `branch` 
ON `employee`.`emp_id` = `branch`.`manager_id`;
# LEFT JOIN 无论条件是否成立，都会将左边(`employee`)的属性回传，右边的表格(`branch`)只有条件成立时才会回传，否则回传 NULL
# LEFT JOIN 和 RIGHT JOIN 用法相同，意思相反

subquery子查询，意思是可以在一个查询语句里使用另一个查询语句的结果

-- 1.找出研发部门的经理名字
SELECR `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
);
# 当 WHERE 条件后的 KEY 可取值不只有一个时，用 IN 而不是 =

ON DELETE

之前创建表格，在设置外键时：
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
);
# 当 `employee`中`emp_id` 删除时，`branch`中`manager_id` 设置为 NULL

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
);
# 当 `employee`中`emp_id` 删除时, `works_with`中对应`emp_id`的那一笔资料一起删除
# 当 `client`中`client_id`删除时, `works_with`中对应`client_id`的那一笔资料一起删除