MySQL之基础

python、mysql、git之各种系统下的安装

🔗mysql-8文档

---

命令

登录mysql

cmd中输入：

mysql -u root -p

.sql文件的导入导出

.sql文件包含表结构和数据。

#导出.sql
# cmd中输入
mysqldump -h localhost -u root -p school >mysql.sql		# 导出数据库 school，mysql.sql存储在cmd的当前目录下
mysqldump -h localhost -u root -p school student >mysql.sql		# 导出表 student


# 导入.sql
# mysql中输入
USE school;
source E:/mysql.sql  

---

数据库

数据库管理系统、数据库

开发人员使用数据库软件（DBMS数据库管理系统）访问数据库。
如Oracle、MySQL等都是数据库管理软件DBMS，不是数据库实体。

数据类型

数

• 有符号与无符号
  默认有符号（可存正负），若只有非负数，加关键字 UNSIGNED

数值
INT	–2${}^{31}$~2${}^{31}$-1， UNSIGNED INT 为 0~2${}^{32}$
DOUBLE	双精度浮点数
FLOAT	单精度浮点数

字符串

串
CHAR	定长字符串，1～255个字符。若不指定，默认为 CHAR (1)。
VARCHAR	变长字符串，1～255个字符。若指定，VARCHAR(n)为0~n-1字符串。
TEXT	变长字符串，1～64 K个字符。

日期和时间

日期和时间
DATE	YYYY-MM-DD
TIME	HH:MM:SS
DATETIME	YYYY-MM-DD HH:MM:SS

二进制

操作数据

查询（单表）

精准匹配

类型	where / having
操作符	=、!=、>、<、>=、<=
is	IS NULL IS NOT NULL
between	BETWEEN 1 AND 100 NOT BETWEEN 1 AND 100
in	IN [‘a’, ‘b’, ‘c’] NOT IN [‘a’, ‘b’, ‘c’]

模糊匹配（like、正则）

类型	where / having
like	LIKE ‘XXX’ LIKE ’%xxx%‘ LIKE ’xxx%‘ LIKE ’%xxx‘
正则表达式	REGEXP
区别：对某个列值内的数据

• like：从头开始匹配， 匹配整个列值
• regxp：从任意位置开始匹配，匹配列值内

正则表达式的通配符	说明
%	任意个字符（0、1、n）
_	1个字符

. ^ $ * + ? { } [ ] \ | ( )
《MySQL必知必会》第9章

select中对列的操作

• 单列：
  处理函数
  聚集函数
• 多列：
  计算字段

处理函数

对列值内的数据进行处理：字符串、数、时间与日期的处理

聚集函数

用于统计

• COUNT(*) ：返回表的行数（包括 NULL）
  COUNT(列)：返回列的行数（排除 NULL）

• 除COUNT()视情况而定外，其余聚集函数均排除为NULL的行。

• MAX()、MIN()能处理任意数据类型的列（包括串、时间、数值等），即按ORDER BY排序后的最后一行或第一行。

计算字段

1. CONCAT
2. ±*/

术语

完全限定名

• 两个数据库中表名相同：用 school.student 代替 student
• 两个表中列名相同：用 student.score 代替 score

group by

• 如果 分组列 中具有NULL值，则NULL将作为一个分组
• 产生的分组顺序随机

having

• where过滤行，having过滤分组。
  所有where子句都可用having写，它支持所有where操作符

子查询

• 多个SELECT子句
• 从内向外处理
• 保证 where 子句与 select 子查询语句具有与中相同数目的列

联合查询

• 在一条SELECT语句中关联多个表
• 应该总是提供联结条件 ON，否则会得出笛卡儿积

关联类型：
若有两个表A、B：

1. 内联结（等值联结）

SELECT A.id
FROM A INNER JOIN B
ON A.id = B.a_id; 		# 1002、1003

SELECT A.id
FROM A, B
WHERE A.id = B.a_id; 		# 等效

# 三个
SELECT A.id
FROM A INNER JOIN B INNER JOIN C
ON A.id = B.a_id AND B.id = C.b_id; 		

2. 左联结

SELECT A.id
FROM A LEFT JOIN B		# 或 LEFT OUTER JOIN
ON A.id = B.a_id; 		

3. 左联结 - 内联结

SELECT A.id
FROM A LEFT JOIN B		
ON A.id = B.a_id
WHERE B.id IS NULL;		# 除了B.a_id之外的列

4. 右外部联结

SELECT A.id
FROM A RIGHT JOIN B		# 或 RIGHT OUTER JOIN
ON A.id = B.a_id; 		

5. 右联结 - 内联结

SELECT A.id
FROM A RIGHT JOIN B		
ON A.id = B.a_id
WHERE A.name IS NULL;		# 除了 A.id 之外的列

6. 外联结

SELECT A.id
FROM A FULL OUTER JOIN B		
ON A.id = B.a_id;	

7. 外联结-内联结

SELECT A.id
FROM A FULL OUTER JOIN B		
ON A.id = B.a_id;	
WHERE A.name IS NULL OR B.id IS NULL;		

组合查询/ 并查询/复合查询

• 多个select子句用 UNION 连接，组合成一个结果
  一个select，多个where ==== 多个select（每个都有一个where）用UNION连接
• 连接的所有select子句的列、聚集函数必须相同（但顺序不必一致）， 列的数据类型必须兼容：DBMS可以隐含地转换的类型
• 合成的结果自动去除了重复的行，UNION ALL不去重
• 只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后

NULL

• NULL值就是没有值或缺值
• 一个字段不是 NOT NULL，就是NULL，若不设置，默认为NULL
• NULL 不等于空串’’

主键

• 单个列的值或多个列的组合值必须唯一
• 只能为 NOT　NULL
• 易更改值的字段不应设为主键， 不更新主键列中的值；
  不重用主键列的值；
  不在主键列中使用可能会更改的值。

外键

• 包含另一个表的主键值

索引

添加索引：
https://www.cnblogs.com/williamjie/p/9759272.html

• 不允许对变长列或一个列的可变部（如：VERCHAR类型的列）进行索引
• 首先导入所有数据，然后再修改表， 定义FULLTEXT。

AUTO_INCREMENT

• 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如设为主键）

引擎

• InnoDB是一个可靠的事务处理引擎，它不支持全文
  本搜索
• MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
• MEMORY在功能等同于MyISAM， 但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）
• 一个数据库中的数据表可以混用多个引擎，如支持事务处理的表用InnoDB，支持全文搜索的表用MyISAM。但外键不能跨引擎。

DBMS（数据库管理系统）

        分为两种。一类为基于共享文件系统的DBMS（用于桌面），另一类为基于客户机 - 服务器的DBMS（MySQL、 Oracle以及Microsoft SQL Server等）。

客户机 - 服务器的DBMS：
        与数据文件打交道的只有服务器软件.
        服务器软件为MySQL的DBMS，客户机软件为MySQL提供的工具（MySQL命令行程序等）、开发语言（C++、Java等）。

模式

用来描述数据库中特定的表以及整个数据库（和其中表的关系）。
SQL与数据库是独立的，能与不同的数据库打交道。

SQL

是一种专门用来与数据库通信的语言（结构化查询语言）。

列

= 字段（表中）

行

= 记录（表中）

全文搜索

• 常用搜索引擎：MyISAM（支持全文搜索）、InnoDB（不支持全文搜索）
• 针对某个列中的词，创建索引。
  第一种：创建表时，FULLTEXT索引列，MySQL自动随着数据的改变不断地重新索引。
  第二种：首先导入所有数据，然后再修改表， 定义FULLTEXT。不要在导入数据时创建索引，费时间
• 对结果排序，具有较高等级的行先返回。
  行的等级值：匹配到的词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来

查询扩展

• 在全文搜索的基础上，查询相关的更多行

# 全文搜索
SELECT <column_name>
FROM <table_name>
WHERE Match(<column_name>) Against('abc');		# Match(FULLTEXT索引列)，返回包含'abc'的行

SELECT <column_name>,
	Match(<column_name>) Against('abc') AS rank			# 列为排序等级值（数越大越高）
FROM <table_name>;

SELECT <column_name>
FROM <table_name>
WHERE Match(<column_name>) Against('abc' WITH QUERY EXPANSION);		# 查询扩展

MySQL数据库优化

SQL语句的规范和优化

• 最好一条SQL语句用“;”结束，且分成多行写，更易阅读。

• 关键字用大写，但数据库名、表名、列名等用小写。

• where：当同时使用AND和OR时，建议用括号

• 能用IN，就不要用OR

• 检索匹配过滤条件：默认不区分大小写

• 通配符：
  不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
  最好不要把它们用在搜索模式的开始处。（如：“%abc”）

• 直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化（在数据库服务器上完成这些操作比在客户机中完成要快得多）：计算字段

• 联结：联结的表越多，性能下降越厉害

• SQL语句不止一种写法时，尤其是分别使用子查询和联结查询，要多测试，看哪种更高效

• 别名：都只能在查询中使用，不存储。列别名返回客户机，表别名不返回

• 列别名好处：缩短SELECT语句长度，尤其是当多次用完全限定名使用时

• 一条SQL语句可改写情况
  子查询 <–> 联结查询
  联结查询的INNSERT JOIN…ON <–> FROM…WHERE
  where条件复杂 <–> 拆成多条select，union

• 搜索
  通配符（like）和正则：慢，需要分别查看每个行，分别分析和处理每个词。
  全文搜索：针对某个列中的词，创建索引

• INSERT、update、delete操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。
  增加关键字：INSERT LOW PRIORITY INTO
  提高性能：多行插入。因为MySQL用 单条INSERT语句处理多个插入 比 使用多条INSERT语句快。

• 删除表中所有数据：
  TRUNCATE TABLE <table_name>;
  删除表：
  truncate

• 使用update和delete的习惯：
  应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
  MySQL没有撤销按钮。应该非常小心地使用UPDATE和DELETE。
  必有where子句（除非处理所有行）。