SQL_MySQL

最新推荐文章于 2024-07-24 14:28:41 发布

Topology_l

最新推荐文章于 2024-07-24 14:28:41 发布

阅读量533

点赞数 1

文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/anonym_T/article/details/105116484

版权

图书管理系统专栏收录该内容

6 篇文章 0 订阅

订阅专栏

学习笔记，备忘及查找用，参考网站
补充内容：SQL
下载地址：MySQL Community Server
MySQL Workbench使用教程

绪论

数据模型

层次模型
层次关系，树
网状模型
图
关系模型
二维表格，映射
数学原理（待补充）

SQL

数据类型

名称	类型	说明
INF	整型	4字节整数类型，+/-2.1x10⁹
BIGINT	长整型	8字节整数类型，+/-9.22x10¹⁸
REAL	浮点型	4字节浮点数，+/-10³⁸
DOUBLE	浮点型	八字节浮点数，+/-10³⁰⁸
DECIMAL(M,N)	高精度小数	由用户指定精度，M总位数N小数点后位数
CHAR(N)	定长字符串	存储指定长度的字符串，N字符串长度
VARCHAR(N)	变长字符串	存储可变长度的字符串，N字符串最大长度
BOOLEAN	布尔类型	True/False
DATE	日期类型
TIME	时间类型
DATETIME	日期和时间类型

SQL核心功能

Structured Query Language
SQL语言定义了这么几种操作数据库的能力：

DDL：Data Definition Language
DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
DML：Data Manipulation Language
DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
DQL：Data Query Language
DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

大小写不敏感，约定：SQL关键字大写，表名、列名小写

运行SQL

mysql -u root -p 连接MySQL服务器
exit 退出MySQL命令行

关系模型

记录Record：逻辑意义上的数据
字段Column：同一个表的每一行记录都拥有相同的若干字段
字段定义了数据类型（包括NULL，但通常情况下应避免允许为NULL）

主键

能唯一区分不同记录的字段（id）
（记录一旦插入表中，主键最好不要再修改）
选取主键的基本原则：不使用任何业务相关的字段作为主键

...
id BIGINT NOT NULL AUTO_INCREMENT,	--id是自增非空的长整型
PRIMARY KEY (id)					--指定主键
...

常见可用作id的字段类型：

自增整数类型：插入数据时自动为每一条记录分配一个自增整数
如果用INT则表的记录上限约2.1x10⁹，使用BIGINT则最多可以约9.22x10¹⁸
BIGINT NOT NULL AUTO_INCREMENT
全局唯一GUID类型：使用一种全局唯一的字符串作为主键
GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

联合主键
两个或更多字段都设置为主键，只要不是所有主键列都重复即可（尽量不使用）

外键

表中可以把数据与另一张表关联起来的字段
通过定义外键约束实现（不一定用，也可能没有外键约束但起到外键的作用，此时需要依靠应用程序的逻辑保证）：

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)			//指定了class_id作为外键
REFERENCES classes (id); 		//指定了这个外键将关联到classes表的id列

通过定义外键约束，关系数据库可以保证无法插入无效的数据
删除外键约束（仅删除约束，不删除外键列）：

ALTER TABLE students
DROP FOREIGN KEY fk_class_id

删除列

DROP COLUMN ...

多对多：多对多关系通过两个一对多关系实现，即通过一个中间表，关联两个一对多关系，就形成了多对多关系
一对一：有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能

索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构
对 score 列创建索引：

ALTER TABLE students
ADD INDEX idx_score(score);		--创建一个名为idx_score的列，使用列score的索引
ADD INDEX idx_name_socre (name, socre);	--多列索引

索引列的值越互不相同，索引效率就越高。一张表可创建多个索引。索引提高了查找效率，但减慢了插入、更新、删除记录的速度
主键会自动创建主键索引
撤销索引：

ALTER TABLE table_name DROP INDEX index_name

唯一索引
在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。
添加唯一索引：

ALTER TABLE students
ADD UNIQUE INDEX uni_name(name);

对某一列添加唯一约束而不创建唯一索引：

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE(name);

修改数据

插入 INSERT

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值a1, 值a2, ...),(值b1,值b2, ...);
	--id字段是自增主键，不需要插入
	--若一个字段有默认值，INSERT中也可以不出现
	--字段顺序不用和数据表字段顺序一致，但字段和值要一一对应

更新 UPDATE

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
	--更新字段可以用表达式
	--若WHERE没有匹配到任何记录，不会报错也不会有记录更新
	--没有WHERE则更新全表
	--最好先测试WHERE条件是否筛选出期望的记录集，再更新

MySQL中，UPDATE会返回更新的行数 and WHERE匹配的行数
Rows matched: Changed:

删除 DELETE

DELETE FROM <表名> WHERE ...;
	--若WHERE没有匹配到任何记录，不会报错也不会有记录删除
	--没有WHERE则删除整个表
	--最好先测试WHERE条件是否筛选出期望的记录集，再删除

MySQL中，DELETE会返回删除的行数 and WHERE匹配的行数
Query OK, 0 row affected

查询数据

运行SQL脚本(命令行)：
$ mysql -u root -p < init-test-data.sql

基本查询 SELECT * FROM

查询表中所有数据：

SELECT * FROM <表名>
	--SELECT是关键字，表示将要执行一个查询; * 表示 所有列 ;FROM表示将要从哪个表查询
	--查询结果是二维表，包含列名和每一行的数据
--eg
SELECT * FROM students;

SELECT可以用来判断当前到数据库的连接是否有效（还可以算数）：

SELECT 1;		--测试数据库连接
SELECT 10+20;	--计算10+20

查询指定列 SELECT

只返回查询记录的特定字段（全表 SELECT *）：

SELECT id, score points, name FROM students;
	--返回id, score, name三列，其中score显示别名points
	
--SELECT+WHERE
SELECT id, score points, name FROM students WHERE gender = 'M';

查询列元素集合 SELECT DISTINCT

返回指定列的内容中的不重复值，即指定列的所有元素构成的集合

SELECT DISTINCT column_name,column_name FROM table_name;

条件查询 WHERE

SELECT * FROM <表名> WHERE <条件表达式>
	--WHERE设定查询条件
SELECT * FROM <表名> WHERE <条件1> AND <条件2>
SELECT * FROM <表名> WHERE <条件1> OR <条件2>
SELECT * FROM <表名> WHERE NOT <条件>
--eg
SELECT * FROM students WHERE score>=80;
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
WHERE score BETWEEN 60 AND 90；

--判断不相等
SELECT * FROM students WHERE score<>80;

优先级由高到低：NOT AND OR

BETWEEN

注意使用的数据库中的 BETWEEN是否包括端点值，不同的数据库该操作符会产生不同的结果

...
WHERE colume_name BETWEEN valuel1 AND valuel2;
	--值可以是数值、文本、日期
	--NOT BETWEEN
--eg
SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

规定多个离散值 IN

SELECT column_name(s) FROM table_name
WHERE column_name IN (value1,value2,...);

--BRTWEEN+IN
SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20) AND country NOT IN ('USA', 'IND');

查询满足特定模式的记录 LIKE

SELECT * FROM students WHERE name LIKE '%bc%';
	--%表示任意字符
SELECT * FROM students WHERE name NOT LIKE 'a%';
	--返回不以a开头的

通配符

通配符	描述
%	替代任意多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
`'^[GFs]'`	以G、F、s开头
`'^[A-H]'`	以A到H的字母开头
[^charlist]或[!charlist]	不在字符列中的任何单一字符

排序 ORDER BY

SELECT id, name, gender, score FROM students ORDER BY score;
	--ORDER BY score 按score从低到高排序（升序：ASC 默认升序可省略）
SELECT id, name, gender, score FROM students ORDER BY score DESC;
	--DESC 倒序，从高到低排序
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
	--先按score倒叙，若有相同值按gender排序
	
--SELECT+WHERE+ORDER BY
SELECT id, name, gender, score FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询 LIMIT

LIMIT <M> OFFSET <N>
	--截取从第 N 条开始的 M 条记录（包括第N条，SQL索引从0开始）
	--如果不足M条就显示实际数量
	--若N超过了最大数量不会报错但会得到一个空的结果集，N越大查询效率越低
	--假设每页x条信息，则查询第n页（从1开始）为：
	LIMIT x OFFSET (n-1)*x;
	--可简写为：
	LIMIT (n-1)*x, x;
	LIMIT x;			--等价于 LIMIT x OFFSET 0；

规定要返回的记录的数目

[MySQL] LIMIT

SELECT column_name(s) FROM table_name	
LIMIT number;

[SQL Server / MS Access] SELECT TOP

SELECT TOP number|percent column_name(s) FROM table_name;	--|????

[Oracle] ROWNUM

SELECT column_name(s) FROM table_name
WHERE ROWNUM <= number;

多表查询/笛卡尔查询

直接拼接，无逻辑关系（感觉这个没啥用）

SELECT * FROM <表1> <表2>
	--查询结果为二维表，及两张表合为一张表显示，列数是两表列数之和，行数是两表行数之积
	--使用时要注意查询结果的记录数可能非常大

--重名属性设置别名
--用 表名.列名 来引用列和设置别名
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;		--给表设置别名
	--可以加 WHERE

设置别名：name n or name AS n

函数 COUNT() SUM AVG MAX MIN（待补充）

查询表中记录数：`COUNT()`

SELECT COUNT(*) FROM students;
	--COUNT(*)整个表的记录数，返回结果为一行一列的二维表，且列名为COUNT(*)
SELECT COUNT(*) num FROM students;
	--返回结果列名为num

--COUNT()+WHERE
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
	--没有匹配到结果，返回0

计算某一列总值（数值类型）：SUM()
平均值（数值类型）：AVG()
最大值：MAX()
最小值：MIN()
*MAX() MIN()不限于数值类型，字符型会返回排序最后和排序最前的字符？？？
上面四个没有匹配到结果，返回NULL

分组分组聚合 GROUP BY

SELECT COUNT(*) num FROM students GROUP BY class_id;
	--GROUP BY class_id按班级分组查询记录数
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
	--查询显示 class_id 和 COUNT(*) num FROM students GROUP BY class_id 两列
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
	--按多个列进行分组，按班级和性别分组将分成 班级数x性别数 个组

在任意一个分组内，若要分组聚合后显示的列在该分组内并不统一，将会报错。分组聚合后生成的表格里显示的只能是一个组内全部元素相同的属性

连接

确定一个主表作为结果集，把其他表依对应关系连接在主表上

内连接 INNER JOIN

等值连接，取交集，返回两表中能够匹配的记录

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s
		--确认主表为students
INNER JOIN classes c 		--需要连接的表
ON s.class_id = c.id;		--连接条件
	--可+WHRER ORDER BY 等

外连接 OUTER JOIN

RIGHT OUTER JOIN	
	--连接规格以连接表为准，即连接表有而主表没有对应内容依旧会显示在结果表中，以NULL填充
LEFT OUTER JOIN
	--连接规格以主表为准
FULL OUTER JOIN
	--两表全部内容都显示，无法对应的记录以NULL填充

合并多个SELECT语句结果集 UNION

要合并的每个SELSCT必须有相同的列

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
	--默认选取不同的值，要允许重复的值使用 UNION ALL

--WHERE+UNION
SELECT country, name FROM Websites WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps WHERE country='CN'
ORDER BY country;

表间复制信息 SELECT INTO / INSERT INTO … SELECT / AS

MySQL不支持 SELECT ... INTO 但支持 INSERT INTO ... SELECT
SELECT ... INTO：要求目标不存在，插入时自动创建

SELECT column_name(s)		--也可以是*，即复制所有列到新表
INTO newtable [IN externaldb]	--复制到新表或者外部数据库？
FROM table1;

--SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可
SELECT *
INTO newtable
FROM table1
WHERE 1=0;

INSERT INTO ... SELECT：两个表都存在，批量复制内容到已存在的表中

INSERT INTO table2
SELECT * FROM table1;
	--复制1中数据插入2
INSERT INTO table2 (column_name(s))
SELECT column_name(s) FROM table1;
	--复制部分列插入2

AS：拷贝表结构及数据

CREATE TABLE <新表>
AS						--AS可省略，见下（as能不能省略存疑）
SELECT * FROM <旧表>

复制当前表数据到新表 CREATE TABLE+SELECT

CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
	--将class_id=1的记录复制到新表students_of_class1中

MySQL

mysql -u root -p
exit（exit仅断开客户顿和服务器的连接，服务器依然继续运行）
在这里插入图片描述

MySQL Client的可执行程序是mysql，MySQL Server的可执行程序是mysqld。
命令行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是mysqld，在后台运行。

管理MySQL

可视化图形界面MySQL Workbench

数据库

列出所有数据库：SHOW DATABASES;
information_schema mysql performance_schema sys是系统库
创建新数据库：CREATE DATABASE <name>;
删除数据库：DROP DATABASE <name>;
删除数据库时数据库内所有表都被删除
切换数据库（对某数据库进行操作）：USE <name>

表

列出所有表：SHOW TABLES;
查看表的结构：DESC <name>;
查看创建表的SQL语句：SHOW CREATE TABLE <name>;
创建表：CREATE TABLE <name>;
删除表：DROP TABLE <name>;
删除表内数据但不删除表：TRUNCATE TABLE table_name
修改表：

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
	--新增，表students新增列birth
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
	--修改，birth列名改为birthday，类型改为VARCHAR(20)
ALTER TABLE students DROP COLUMN birthday;
	--删除列birthday

约束 CONSTRAINT

规定表中的数据规则，创建时规定：CREATE TABLE，创建后规定：ALTER TABLE

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

约束	说明
NOT NULL	不能存储NULL值
UNIQUE	保证某列的每行必须有唯一值
PRIMARY KEY	NOT NULL+UNIQUE
FORIMARY KEY	指定外键，保证一个表中的数据匹配另一个表中的值的参照完整性
CHECK	保证列中的值符合指定条件
DEFAULT	规定没有给列赋值时的默认值
AUTO_INCREMENT	[MySQL]主键自动增长，起始值为1且不回退，必须是整数类型

实用SQL语句

插入或替换 REPLACE

如果记录不存在，插入；如果记录存在，删除并插入新记录

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
	--根据唯一主键判断有无记录存在，只受主键和唯一索引的约束

插入或更新 INSERT INTO … ON DUPLICATE KEY UPDATE …

不存在→插入；存在→更新且更新字段由UPDATE指定

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
	--存在时仅更新name、gender、score字段

插入或忽略 INSERT IGNORE INTO …

不存在→插入；存在→不进行任何操作

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

写入查询结果 INSERT+SELECT

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)					--主键
);										--创建新表
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
	--将平均成绩计算结果按class_id升序存放进新表

强制使用指定索引 FORCE INDEX

强制查询使用指定的索引

SELECT * FROM students FORCE INDEX (idx_class_id) 
WHERE class_id = 1 ORDER BY id DESC;
	--索引idx_class_id必须存在
	--DESC降序

事务

数据库事务：把多条语句作为一个整体进行操作的功能
特性（ACID）：

A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；（？？？）
I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

隐式事务：单条SQL语句
显式事务：手动多条作为一个事务，BEGIN开始，COMMIT结束

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;		--提交事务，若该语句执行失败则整个事务失败

--主动让事务失败可用 ROLLBACK 回滚事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

隔离级别

并发执行的事务涉及到操作同一条记录时，可能会带来不一致性，故SQL标准定义了4种隔离级别

Isolation Level	Dirty Read	Non Repeatable Read	Phantom Read
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

Dirty Read：一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据
Non Repeatable Read 不可重复复读：在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。
Phantom Read 幻读：在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功（在第一次查询和更新两个操作间有别的事务插入并提交了该记录），并且，再次读取同一条记录，它就神奇地出现了

默认隔离级别：在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read

SQL 视图（未完成）

日期处理 Date函数

MySQL内建日期函数

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间的单独部分
DATE_ADD()	向日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：
DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
YEAR - 格式：YYYY 或 YY

Topology_l

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL_MySQL

学习笔记，备忘及查找用，参考网站补充内容：SQL下载地址：MySQL Community Server目录绪论数据模型SQL数据类型SQL核心功能运行SQL关系模型主键外键索引修改数据插入 INSERT更新 UPDATE删除 DELETE查询数据基本查询 SELECT * FROM查询指定列 SELECT查询列元素==集合== SELECT DISTINCT条件查询 WHEREB...
复制链接

扫一扫