【Mysql系列】Mysql基础知识集锦

September 14, 2021 - September 23, 2021

SQL分类

1.数据查询语言（DQL）

数据查询语言（Data Query Language, DQL）是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有FROM，WHERE，GROUP BY，HAVING和ORDER BY。这些DQL保留字常与其他类型的SQL语句一起使用。

2.数据定义语言（DDL）

数据定义语言 (Data Definition Language, DDL) 是SQL语言集中，负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成，最早是由 Codasyl (Conference on Data Systems Languages) 数据模型开始，现在被纳入 SQL 指令中作为其中一个子集。

3.数据操纵语言（DML）

数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。

4.数据控制语言（DCL）

数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。

单行处理函数

lower：转为小写
upper：转为大写
substr：截断

格式1： substr(string string, int a, int b);

a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取） 3、b 要截取的字符串的长度

格式2：substr(string string, int a) ;

从第a个字符开始截取后面所有的字符串。
ifnull

空处理，IFNULL(key, alt_value),key:指定的字段或者值，alt_value：key表达式为 NULL 时返回的值
concat:字符串拼接
length:长度
str_to_date：将字符串varchar类型转为date类型

用法：str_to_date('字符串日期','日期格式')

mysql的日期格式：%Y:年；%m:月;%d:日；%h：时；%i:分；%s:秒

注意：如果日期格式是%Y-%m-%d的格式，则可以直接省略。
date_format：将date类型转为具有一定格式的varchar字符串类型

date_format(日期类型数据,'日期格式')。sql可以自动将数据库中的date类型转换成varchar类型。并且采用的格式是mysql默认的日期格式：%Y-%m-%d
round：四舍五入
rand：随机数
format:数字格式化，format(数字,'格式')

聚合函数

sum：总和
count：数量
avg：平均
max：最
min：最小

特别说明点

null:相当于不存在
分组函数中自动忽略掉null比如求和时包含null时自动不计算。
count(*):统计表当中的总行数，只要行数中有一个值则count++。
count(具体字段):表示统计该字段下所有不为null的总数。

执行顺序

语句顺序：select ... from table where ... group by ...having ... order by ... limit ...
执行顺序：①from ②where ③group by ④having ⑤select ⑥order by
执行顺序：从表中查询数据，先经过where进行筛选出有价值的数据，对这些有价值的数据进行分组，分组之后进行having进一步筛选。select查询出来，最后排序输出指定条数。
分组函数不能用在分组函数之后：如 select * from table where s>min(ss); 虽然没有写group by，那么默认分成了一组。因为where在group by之前执行的，所以不能这样写。

group by

特别说明点

sql语句中，有group by时，select后面只能跟上参加分组的字段，以及分组函数，其他的一律不能跟。因为分组时已经对某个字段进行了分组，如果在select后另加某个字段，就没有意义。

where和having

优化策略：优先使用where，where用不到了再用having

举例：

找出各部门最高的薪资，要求最高薪资大于3000的？

用where更恰当：select deptno,max(sal) from emp where sal>3000 group by deptno

找出各部门平均薪资，要求平均薪资大于2500的？

用having更恰当：select deptno,avg(sal) from emp group by deptno having avg(sal)>2500

distinct

作用：去重

举例：

select distinct job,deptno from emp;

特殊说明

distinct:只能出现在所有字段的前面
出现在多个字段之前，表示两个字段联合起来去重。

连接查询

笛卡尔积

当两张表进行查询时，没有任何的查询条件时，最终查询的结果条数，是两张表条数的乘积。
如何避免笛卡尔积：减少连接次数

内连接

关键字：inner join on,inner可省略
语法：select * from table_a a join table_b b on a.a_id = b.b_id
返回的结果集

两表的交集

Untitled

分类
- 等值连接：select * from table_a a join table_b b on a.a_id = b.b_id
- 非等值连接：select * from table_a a join table_b b on a.a_sal between b.sal and b.hsal;
- 自连接：

外连接

左外连接（又称左连接）
- 将join关键字左边的表作为主表，捎带查询右边的表
- 关键字：left join /left outer join
- 语句:select * from table_a a left join table_b b on a.a_id = b.b_id
右外连接（又称右连接）
- 将join关键字右边的表作为主表，捎带查询左边的表
- 关键字：right join / right outer join
- 语句：select * from table_a a right join table_b b on a.a_id = b.b_id

内连接和外连接的区别

内连接：完成能够匹配上这个条件的数据查询出来。

外连接：不仅仅查出能够匹配上这个条件的数据查询出来，左连接是将左边的表中所有的记录都查出来，右边的表只匹配符合条件的数据行。右连接则与之相反。

子查询

语句：select ...(select). from ...(select). where ...(select).

where 字句的

一步一步来写。

from字句

from后面的子查询，可以将子查询的查询结果当做一张临时表。

union

union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的增加。

但是union可以减少匹配次数，在减少匹配的次数的情况下，还可以完成两个结果集的拼接。

举例：

a表记录是10条，b条记录是10条， c条记录是10条。则匹配次数：101010=1000

而union，a连接b表：1010=100，a表连接c表：1010=100，union的总连接书：100+100=200

相当于union将乘法变成了加法。

union使用注意事项

在使用时union合并结果集时，要求两个结果集的列数相同。

举例：下面的sql是正确的。假如一个返回是ename,job，一个返回ename，则会报错。
```
select ename,job from emp where job = 'it' 
union
select ename,sal from emp where job = 'sale'
```
mysql可以，oracle不可以。要求：结果集合合并时列和列的数据类型也要一致。

limit

作用：分页查询用法：

完整用法：limit startIndex,length ，即length 0,5 取前五条

缺省用法：limit 5 直接取前五条

常用数据类型

varchar

可变长度字符串，可根据实际的数据长度动态分配空间，可节省空间。

char

定长，固定分配空间。

int

数字中的整型，等同于java中的int。

bigint

数字中的整型，等同于java中的long。

float

单精度浮点型数据

double

双精度浮点型数据

date

短日期类型，只包含年月日信息。

datetime

长日期类型，包含年月日时分秒信息。

now()：带有时分秒的日期

表相关的操作

创建表

删除表

drop table if exists 表名;

插入数据（DML)

insert into 表名(字段名1,字段名2,字段名3) values(1,'xx','23');

插入多条记录:

insert into 表名(字段名1,字段名2,字段名3) values (xx,xx,xx),(xx,xx,xx);

更新数据

update 表名 set 字段名1 = 值,字段名2 =值 where 条件;

删除数据

delete from 表名 where 条件;

注意：没有where，则删除整个表的数据。

快速复制表

create table 表名 as select * from 表名;

将查询结果插入一张表

create table 表名 as select 字段名1，字段名2 from 表名 where 字段名=xxx;

insert into 表名 select * from 表名;

快速删除表的数据

delete

表中的数据被删除，但是这个数据在硬盘上的真实存储空间不会被释放。

删除的缺点：删除效率非常低；

删除的优点：支持回滚，可以恢复数据。
truncate

删除效率高，物理删除

优点：快速；缺点：不支持回滚。

truncate table dept_bank;
drop

删除的是表
大表非常大，上亿条数据如何删除

delete删除：可能需要1h执行才能删完，效率很低

truncate删除，只需要不到1s的时间就删除结束。

但truncate删除之前，必须仔细询问客户是否真的要删除。

但truncate删除之前，必须仔细询问客户是否真的要删除。

对表结构进行增删改

删除字段：ALTER TABLE testalter_tbl DROP i;
新增字段：ALTER TABLE testalter_tbl ADD i INT;
修改字段类型及名称：

ALTER TABLE testalter_tbl MODIFY c VARCHAR(10);

ALTER TABLE testalter_tbl CHANGE i j BIGINT;
指定字段 j 为 NOT NULL 且默认值为100 :

ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
修改字段默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
修改表名:ALTER TABLE testalter_tbl RENAME TO alter_tbl;
特别说明点：

删除表，先删子，后删父。

创建表，先创建父，再创建子。

约束

在mysql中，如果一个字段被not null 和unique同时约束，则该字段自动变成主键字段。而oracle不是。

表级约束主要是给多个字段联合起来添加约束。

非空约束：not null

只有列级约束，没有表级约束。
唯一约束：unique

字段不能重复，可为null。联合唯一索引：unique(字段1，字段2)
主键约束：primary key

外键约束：foreign key;

检查约束：check ，mysql不支持，oracle支持

主键

主键：not null + unique,即不能为空，也不能重复。
主键只能有1个。
建议使用：int,bigint,char。
不建议使用：varchar做主键，主键值一般都是数字，一般都是定长的。
主键分类：单一主键，复合主键，自然主键，业务主键
- 自然主键：主键值是个自然数，和业务没关系。
- 业务主键：主键值和业务关系密切。
- 一般建议使用自然主键，因为业务一旦发生变化，可能会影响主键值。

外键

子表中的外键引用的父表中的某个字段，被引用的字段不一定是主键，但至少具有unique约束。

外键也可以为null

存储引擎

存储引擎是mysql的特有术语。实际上存储引擎是一个表存储/组织数据的方式。

不同的存储引擎，表存储数据的方式不同。

mysql默认的存储引擎是InnoDB，默认的字符编码方式是utf8。

如何查看mysql支持哪些存储引擎的命令：show engines \G

mysql> show engines \G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL

mysql支持九大存储引擎，不同版本支持的存储引擎不同。

【Mysql系列】Mysql基础知识集锦

SQL分类

1.数据查询语言（DQL）

2.数据定义语言（DDL）

3.数据操纵语言（DML）

4.数据控制语言（DCL）

单行处理函数

聚合函数

特别说明点

执行顺序

group by

特别说明点

where和having

distinct

特殊说明

连接查询

笛卡尔积

内连接

外连接

内连接和外连接的区别

子查询

where 字句的

from字句

union

union使用注意事项

limit

常用数据类型

varchar

char

int

bigint

float

double

date

datetime

表相关的操作

创建表

删除表

插入数据（DML)

更新数据

删除数据

快速复制表

将查询结果插入一张表

快速删除表的数据

对表结构进行增删改

约束

主键

外键

存储引擎

推荐文章