# 数据库基础

# 什么是数据库

数据库是用来组织、存储和管理数据的仓库。

# 常见的数据库及分类

MySQL 数据库（目前应用最广泛）
Oracel 数据库（收费）
SQL Server 数据库（收费）
Mongodb 数据库

前面三种是传统型数据库（关系型数据库或SQL数据库），设计理念类似。
mongodb 新型数据库（非关系型数据库或NoSQL数据库），一定程度上弥补了传统型数据库的缺陷。

# 传统型数据库的数据组织结构

结构：

数据库（database）- 表的集合，一个数据库中能够有多个表
数据表（table）- 由行和列组成的二维表格
数据行（row）- 记录
列（field）- 字段

# 需要安装哪些MySQL相关的软件

MySQL官方传送门 (opens new window)

MySQL Server ：专门用来提供数据库存储和服务的软件
MySQL workbench：可视化的MySQL 管理工具，可以方便的操作存储在MySQL Server中数据（自己有用过 Navicat Premium）

# mysql安装

进入官网，按照如下步骤下载安装包
安装包下载好后,安装（傻瓜式安装就行）

# 需要安装哪些MariaDB相关的软件

MariaDB官方传送门 (opens new window) 自己电脑安装过，好像没有记录笔记

# SQL语言基础

定义: SQL是一门数据库编程语言; SQL是结构化查询语言,专门来访问和处理数据库的编程语言,能够让我们以编程的形式,操作数据库里面的数据

注意: 只能在关系型数据库中使用
对大小写不敏感

# SQL语言主要分为

DQL: 数据查询语言，用于对数据进行查询，如select;
DDL: 数据定义语言，进行数据库、表的管理等，如create、drop;
DML: 数据操作语言，对数据进行增、删、改，如insert、update、delete;
TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback;

学习目标:
DQL - 熟练掌握 DDL - 可以看懂

查询数据(select)
插入数据(insert into)
更新数据(update)
删除数据(delete)
语法:where条件; and 、or 运算符; order by排序、count(*)函数

# 新建数据库

注意：字符集 utf-8

# sql注释

单行注释 -- 注释内容 ctrl + /
多行注释 /注释内容/ ctrl + shift + /

# MySQL 常用数据类型

整数：int，有符号范围(-2147483648，2147483647)、无符号范围(0，4294967295); 如 int unsigned,无符号整数。
小整数：tinyint，有符号范围(-128，127)，无符号范围(0，255)，如：tinyint unsigned,代表设置一个无符号的小整数。
小数：decimal，如decimal(5,2)表示共存5位数，小数占2位，不能超过2位；整数占3位，不能超过3位。
字符串：varchar，如varchar(3)表示最多存3个字符，一个中文或一个字母都占一个字符;
日期时间：datetime，范围(1000-01-01 00:00:00 ~ 9999-12-31 23:59:59)

# 字段的约束

主键(primary key)：值不能重复，auto_increment代表值自动增;
非空(not null)：此字段不允许填写空值;
唯一(unique)：此字段的值不允许重复;
默认值(default)：当不填写值时会使用默认值，如果填写时以填写为准。

# 语法

# 创建表

语法：CREATE TABLE 表名 ( 字段名类型, 字段名类型 )

CREATE TABLE students ( 
  id INT UNSIGNED PRIMARY KEY auto_increment,
  name VARCHAR ( 20 ) UNIQUE,
  age INT UNSIGNED,
  height DECIMAL ( 5, 2 ),
  a int DEFAULT 30
);

1
2
3
4
5
6
7

# insert into

向数据表中插入新的数据行

-- 插入表的所有字段 insert into table_name values(值1, 值2, ...); 
-- 插入表的部分字段 insert into table_name(列1, 列2, ...) values(值1, 值2, ...);
-- 插入单行
insert into users (username,password,status) values ("test2","test2",0);
-- 插入多行
INSERT INTO b VALUES( "test1", 145 ),( "test2", 167 );

-- 如果不指定字段，主键自增长字段的值可以用占位符，0或者null
INSERT INTO b VALUES( NULL,"test1", 145 );

1
2
3
4
5
6
7
8
9

# select

从表中查询数据

-- * 表示所有列
select * from my_db_01.users
select * from users
select * from users where id=1

-- 也可以指定列名
select id from users
select id,username from users

1
2
3
4
5
6
7
8

# 字段和表起别名

-- 字段起别名 as可以省略不写
select username as 名字 from users;
select username 名字 from users
-- 表也可以起别名
select username from users as u;
select username from users u;

1
2
3
4
5
6

# DISTINCT 将查询到的结果，去重(概率重复记录)

SELECT DISTINCT name FROM stu
SELECT DISTINCT name,age FROM stu
-- 两个条件一起去重

1
2
3

# update

更新某一行中的一个列

-- 更新users表中id==4的status为1
UPDATE users SET STATUS=1 WHERE id=4
UPDATE users SET username="testUpdate" WHERE id=4
UPDATE b SET height = height + 1 WHERE height > 145
-- 修改一列的多字段
UPDATE users SET username="test2",STATUS=0 WHERE id=4

1
2
3
4
5
6
7

# delete truncate drop

删除表中的行但是一般不建议这么操作（这样会将数据删除掉），一般会用一个字段代表是否删除状态

-- 指定表 根据where条件，删除对应行数据
-- 注意要有where条件 否则会将整张表删除掉
DELETE FROM users WHERE id=4 

-- delete from 表名 : 删除所有数据, 但是不重置主键字段的计数(清空表)
delete from students
-- truncate table 表名 : 删除所有数据, 并重置主键字段的计数(截断表)
truncate table students
-- delete 和 truncate的区别
-- 速度truncate快，但是truncate不能带条件，要删除部分数据就用delete

1
2
3
4
5
6
7
8
9
10

# 删除表 drop

-- drop table 表名 : 删除表(字段和数据均不再存在),表就没有了(删除表)
drop table students
-- 如果表a存在就删除，如果不存在，什么也不做
drop table if EXISTS a

1
2
3
4

# where

# 可以在where子句中使用的运算符

操作符	描述
=	等于
<>	不等于有些地方可以用!=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式
in( , , )	或者满足哪些条件
is null	空
is not null	不是空

-- BETWEEN
-- 注意 不同数据库 边界结果会不一样的 个人建议用>=
SELECT * FROM users WHERE id BETWEEN 1 AND 5
SELECT * FROM users WHERE id NOT BETWEEN 1 AND 5
SELECT * FROM users WHERE id >=1 AND id <=5

-- LIKE
-- like 的通配符有两种:
-- % 表示 任意多个字符（0个 1个 多个字符） 
-- _ 表示 任意一个字符
-- 比如：name以李开头 "李%" ; 第二个和第三个字符是0的值 '_00%'
SELECT * FROM users WHERE username LIKE "test%"

-- in( , , ) 
-- 以下两句结果是一样的
SELECT * FROM test0718 WHERE remark IN ( "测试", "1", "3" );
SELECT * FROM test0718 WHERE remark = "测试" OR remark = "1" OR remark = "3";

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# and or

把两个或多个条件结合起来

SELECT * FROM users WHERE id>=1 AND status=0

# 数据分组 group by

-- SELECT 聚合函数 FROM 表名 where 条件 group by 字段
-- group by 一般就是配合聚合函数用的
SELECT NAME,count( * ) FROM student GROUP BY NAME;
-- where 配合 group by使用
SELECT NAME,count( * ) FROM student WHERE age>20 GROUP BY NAME;

1
2
3
4
5

# 数据排序 order by

根据指定的列对结果集进行排序默认是ASC升序，DESC是降序

-- 查询的结果：根据id降序
SELECT * FROM users ORDER BY id DESC
-- 多重排序 先按照status降序排序，再按照username的字母顺序升序排序
SELECT * FROM users ORDER BY status DESC, username ASC

-- where配合order by 使用
--  select * from 表名 where 条件 order by 字段1,字段2;
--  一定要把 where 写在 order by 前面
select * from students where sex="男" order by class, studentNo desc;

1
2
3
4
5
6
7
8
9

# 分组和排序联合使用 group by AND order by

-- select 字段 from 表 group by 字段 order by 字段
SELECT mclass 班级,max( age ) 最大年龄,min( age ) 最小年龄,AVG( age ) 平均年龄,COUNT( * ) 数量 
FROM
 students 
GROUP BY
 class 
ORDER BY
 class;

1
2
3
4
5
6
7
8

# having 分组聚合之后的筛选

having子句总是出现在group by 后面

SELECT COUNT( * ) FROM students 
GROUP BY
 sex 
HAVING
 sex = "男";

--  having 配合聚合函数使用
--  查询人数大于2的班级
SELECT
	class,
	COUNT( * ) 
FROM
	students 
GROUP BY
	class 
HAVING
	COUNT( * ) >2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# where 和 having 之间的区别

where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
having 是对 group by 的结果进行筛选
having 后面的条件可以用聚合函数，where后面的条件不可以使用聚合函数

# 聚合函数 count(*) max(price) min(price) avg(price)

返回查询结果的总数据条数

-- 查询数量：COUNT(字段)
SELECT COUNT(*) FROM users
-- 最高商品价格: max(字段): 查询最大值
select max(price) from goods;
-- 最低商品价格: min(字段): 查询最小值
select min(price) from goods;
-- 商品平均价格: avg(字段): 求平均值
select avg(price) from goods;

1
2
3
4
5
6
7
8

# limit 可实现数据分页

--  limit 开始行,获取行数 （start索引从0开始，不填默认是0）
--  总是出现在select语句的最后面
select * from 表名 limit start,count
-- 获取前几条数据，一下两句结果一样
select * from users limit 0, 2;
select * from users limit 2;

1
2
3
4
5
6

# 分页

-- 已知，每页显示m条数据，求 查询第n页的数据
select * from student limit (n-1)*m,m ;

1
2

# as 关键字

为列设置别名

SELECT username as uname FROM users

as关键字

# 连接查询内连接左连接右连接

-- 内连接 两张表中对应关系的数据都会显示出来，没有对应关系的数据均不会显示
SELECT * FROM students stu 
INNER JOIN grade gra on stu.grade = gra.id

-- 多表内连接
SELECT stu.name,cla.name,loc.name,gra.name FROM students stu INNER JOIN class cla on stu.class = cla.id
INNER JOIN location loc on loc.id = cla.location
INNER JOIN grade gra on stu.grade = gra.id

-- 如果要保证一张数据表的全部数据都存在，则不能使用内连接

-- 左连接 左边表为主表,全部显示，不对应为null
SELECT * FROM students stu
LEFT JOIN grade gra ON stu.grade = gra.id


-- 右连接
SELECT * FROM students stu
RIGHT JOIN grade gra on stu.grade = gra.id

SELECT * FROM grade gra
RIGHT JOIN students stu on stu.grade = gra.id

-- 带有where条件的内连接
SELECT
	stu.name as 名字,
	gra.name as 班级
FROM
	students stu
	INNER JOIN grade gra ON stu.grade = gra.id 
WHERE
	stu.grade =1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

内连接效果图：

左连接效果图：

右连接效果图：

# 自关联

SELECT * FROM areas a1 INNER JOIN areas a2
ON a1.id = a2.pid

1
2

# 子查询

定义：在一个select语句中,嵌入了另外一个select语句,那么被嵌入的select语句被称之为子查询语句,外面的第一条select语句为主查询。

子查询是嵌套到主查询里面的
子查询做为住查询的数据源或者条件
子查询是独立可以单独运行的查询语句
主查询不能独立运行，依赖子查询的结果

# 标量子查询

定义：子查询返回：只有一行，一列

SELECT name,age from students WHERE age<2.5;
-- 以下和以上结果一样
-- 
SELECT avg(age) FROM students;
SELECT name,age from students WHERE age<(SELECT avg(age) FROM students);

1
2
3
4
5

# 列子查询

定义：子查询返回：一行，多列

SELECT * FROM students WHERE class in (1,2);
-- 以下和以上结果一样
-- 这里返回的结果是：一行，多列
SELECT id FROM class WHERE location = 1;
SELECT * FROM students WHERE class in (SELECT id FROM class WHERE location = 1);

1
2
3
4
5

# 表级子查询

定义：子查询返回：多行多列（一个表）

SELECT *  FROM students INNER JOIN class 
ON students.class = class.id
WHERE height = 1.78;
-- 以下和以上结果一样
-- 这条返回的结果是：多行，多列
SELECT * FROM students WHERE height = 1.78;

SELECT * FROM (SELECT * FROM students WHERE height = 1.78) stu INNER JOIN class
ON stu.class = class.id;

1
2
3
4
5
6
7
8
9

# 深入学习

# MySQL常用内置函数

# 字符串函数 concat length left right substring substring_index

-- 可以用在select的显示的字段中,比如：
SELECT CONCAT( NAME, "-", age ) FROM students

-- concat拼接字符串函数
concat(str1,str2,str3)

-- length 包含字符个数
-- 如果字符串中包含utf8格式的汉字，一个汉字length返回3
length(str)
SELECT NAME FROM students WHERE LENGTH(NAME) = 6

-- left(str,len) 截取字符串,返回字符串str的左端len个字符，中文和英文个数len一致
left(str,len)
SELECT LEFT("测试中测试中",2)
-- 返回的结果是：测试

-- right(str,len) 截取字符串,返回字符串str的右端len个字符
right(str,len)
SELECT RIGHT("测试中测试中",2)
-- 返回的结果是：试中

-- substring(str,pos,len) 指定位置，截取字符串。返回字符串str的位置len个字符，pos从1开始计数；
SELECT SUBSTRING("测试中测试中",3,2)
-- 返回的结果是：中测

-- substring_index(str,delim,count) 字符串，截取数据依据字符，截取字符的位置
SELECT SUBSTRING_INDEX("11-22-33-44",'-',2)
-- 返回的结果是 11-22
SELECT SUBSTRING_INDEX("11-22-33-44",'-',-1)
-- 返回的结果是 44


-- trim 去除空格

-- ltrim 去除字符串左侧空格
SELECT LTRIM("      ceshiceshi     ")
-- 返回的结果是: "ceshiceshi     "

-- rtrim 去除字符串右侧空格
SELECT RTRIM("      ceshiceshi     ")
-- 返回的结果是: "      ceshiceshi"
SELECT CONCAT(RTRIM("A  "),"B")
-- 返回的结果是:AB

-- trim 去除字符串两侧空格
SELECT TRIM("      ceshiceshi     ")
-- 返回的结果是: "ceshiceshi"

-- round(n,d) 四舍五入 n表示原数，d表示小数位，默认0
SELECT ROUND(AVG(age)) FROM students
SELECT ROUND(2.3455)
-- 返回的结果是：2
SELECT ROUND(2.3455，2)
-- 返回的结果是：2.35
SELECT ROUND(2.999)
-- 返回的结果是：3
SELECT ROUND(2.999,1)
-- 返回的结果是：3

-- rand 随机数 值为0-1的浮点数

-- 例子：随机获取一条数据
SELECT * FROM students ORDER BY RAND( ) LIMIT 1

-- 日期和时间函数 返回系统日期、时间、日期与时间、日期与时间
SELECT CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP(),NOW()
-- 对应结果："2023-08-09"、"15:24:27"、"2023-08-09 15:24:27"、"2023-08-09 15:24:27"

-- 例子：插入数据,时间用当前时间
INSERT INTO class VALUES( NULL, "5班", 2, CURRENT_TIMESTAMP ( ) )

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

# 实际使用

# 以特殊字符为依据截取部分数据

-- P3-075+4-O-OV2 截取倒数第二个"-"前的内容
-- 使用三个函数 LENGTH SUBSTRING_INDEX LEFT
SELECT LEFT ( 'P3-075+4-O-OV2',( LENGTH( 'P3-075+4-O-OV2' )- LENGTH( SUBSTRING_INDEX( 'P3-075+4-O-OV2', '-',- 2 ))- 1 ))

1
2
3

# 了解存储过程 procedure

是一条或者多条sql语句的集合

-- 创建一个存储过程
CREATE PROCEDURE stu()
BEGIN 
    SELECT * FROM students;
END

-- 调用存储过程
call stu();

-- 删除存储过程 删除不需要括号
DROP PROCEDURE stu;
DROP PROCEDURE if EXISTS p_student_select

-- 查看存储过程
show procedure status

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 了解视图

是对select语句的封装
可以理解为一张只读的表，针对于视图只能用select，不能用delete和update

-- 创建视图
CREATE VIEW stu_nan_view as
SELECT * FROM students WHERE age>2

-- 删除视图
DROP VIEW IF EXISTS stu_nan_view
DROP VIEW stu_nan_view

1
2
3
4
5
6
7

# 了解事务

事务是多条更改数据操作的sql语句集合
一个集合数据有一致性，那么就都失败，要么就都成功

开启事务：begin : 开启事务后执行修改update或删除delete记录语句，变更会写到缓存中，而不会立刻生效
回滚事务：rollback : 放弃修改
提交事务：commit ：将修改的数据写入实际的表中

注意：没有写begin代表没有事务，没有事务的表操作都是实时生效。
注意：如果只写了begin,没有rollback,也没有commit，系统推出，结果是rollback。

BEGIN;
DELETE FROM students WHERE id = 2;
ROLLBACK;
COMMIT;

1
2
3
4

# 了解索引

index
优点：加快select查询的速度（如果一个表记录很少，几十条，或者几百条，可以不用索引）
缺点：会降低更新表的速度（insert、update、delete）。因为更新表时，不仅保存数据还需要保存索引文件。
实际运用：更新大量数据时，可以先删除索引、再批量更新、最后再添加索引。

-- 创建索引
-- 字段如果不是字符串，可以不填写长度
create index 索引名称 on 表名(字段名称(长度));
-- 例子
CREATE INDEX age_index ON students(age);
CREATE INDEX name_index ON students(name(20));

-- 查看索引
SHOW INDEX FROM 表名;
-- 例子
SHOW INDEX FROM students;

-- 删除索引
DROP INDEX 索引名称 ON 表名;
-- 例子
DROP INDEX age_index ON students;

-- 不需要写调用索引的语句，只要where好后面用到的字段建立了索引，系统会自动调用
SELECT * FROM students WHERE name = "张三"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 掌握基于命令行的SQL使用(cmd)

进入mysql.exe所在的目录 D:\mysql-8.0.15-winx64\bin>
进入mysql.exe所在的目录，输入以下命令： mysql -h[主机名 ip地址] -u[连接用户名] -p 例子本地：省略-h参数，mysql默认本地连接 mysql -u root -p
退出：exit
登录之后使用的命令
1. show databases ：显示系统所有的数据库
2. use 数据库名 ：使用指定的一个数据库
3. show tables : 指定查看数据库有多少表
4. 如果命令行默认字符集与数据库默认字符集不同
  1. 在windows默认字符集是gbk
  2. set names gbk ：告诉mysql，客户端用字符集是gbk
5. desc 表名 显示表结构
命令行，创建和删除数据库
1. 创建数据库： create database 数据库名 default charset 字符集; create database mytest default charset utf-8;
2. 删除数据库：drop database 数据库名 drop database mytest

# 数据库管理相关操作（了解）

添加新用户
修改用户密码

功能	步骤	描述
添加用户
	用root身份登录mysql	`mysql -u root -p`
	添加语句	`grant all on 数据库名.表名 to 用户名@'登录主机' identified by '密码' with grant option;`
	例子	`grant all on . to test@'%' identified by '123456' with grant option;`
具体字段意思	grant all on	代表为用户赋权
	数据库名	可以是*，代表所有数据库
	表名	可以是* 代表所有表
	数据库名.表名	代表所有库和所有表
	to 用户名	创建用户名称
	@‘登录主机’	@localhost 代表只能在本机登录，@‘%’代表可以远程登录
	identified by '密码'	指定用户登录密码
删除用户
	用root身份登录mysql	`mysql -u root -p`
	选择mysql数据库	`use mysql;`
	回收用户test权限	`revoke all on . from test @'localhost';`
		`revoke all on . from test @'%';`
	删除用户test	`delete from user where user ="test";`
	刷新权限	`flush privileges;`

# SQL Server一些使用记录

# SQL Server Management Studio (SSMS) 下载

SSMS 下载地址 (opens new window)

# SQL Server 查询MySQL 数据库内容

select * from openquery(mysql,'SELECT * FROM product_10000055')

← Linux

# 数据库基础

# 什么是数据库

# 常见的数据库及分类

# 传统型数据库的数据组织结构

# 需要安装哪些MySQL相关的软件

# mysql安装

# 需要安装哪些MariaDB相关的软件

# SQL语言基础

# SQL语言主要分为

# 新建数据库

# sql注释

# MySQL 常用数据类型

# 字段的约束

# 语法

# 创建表

# insert into

# select

# 字段和表起别名

# DISTINCT 将查询到的结果，去重(概率重复记录)

# update

# delete truncate drop

# 删除表 drop

# where

# 可以在where子句中使用的运算符

# and or

# 数据分组 group by

# 数据排序 order by

# 分组和排序联合使用 group by AND order by

# having 分组聚合之后的筛选

# where 和 having 之间的区别

# 聚合函数 count(*) max(price) min(price) avg(price)

# limit 可实现数据分页

# 分页

# as 关键字

# 连接查询 内连接 左连接 右连接

# 自关联

# 子查询

# 标量子查询

# 列子查询

# 表级子查询

# 深入学习

# MySQL常用内置函数

# 字符串函数 concat length left right substring substring_index

# 实际使用

# 以特殊字符为依据 截取部分数据

# 了解存储过程 procedure

# 了解视图

# 了解事务

# 了解索引

# 掌握基于命令行的SQL使用(cmd)

# 数据库管理相关操作（了解）

# SQL Server一些使用记录

# SQL Server Management Studio (SSMS) 下载

# SQL Server 查询MySQL 数据库内容

# 连接查询内连接左连接右连接

# 以特殊字符为依据截取部分数据