MySQL

最新推荐文章于 2024-09-05 09:00:00 发布

需要来一杯吗？

最新推荐文章于 2024-09-05 09:00:00 发布

阅读量108

点赞数

文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/weixin_44771644/article/details/103572334

版权

MYSQL

DDL

1.创建数据库

create database 库名;
create database if not exists 库名;
create database 库名 character set 编码方式;

2.查看数据库

查看所有数据库

show databases;

查看建库语句

show create database 库名;

3.修改数据库编码

alter database 库名 character set 编码方式;

4.删除数据库

drop database 库名;
drop database if exists 库名;

5.进入指定库内

use 库名；

6.查看所在当前所在库位置

select database();

####################################

7.创建表

create table 表名 （
	字段名 数据类型,
	字段名 数据类型,
	...
);

常见数据类型：

整型 int bigint
浮点型 float double
字符串 varchar(长度) 1~65535
日期 date datetime

8.复制表like

create table 新表 like 旧表;

9.查询所有表

show tables;

10.查看建表语句

show create table 表名;

11.查看表结构 desc+表名

desc 表名;

DML[增删改]

12.添加记录

insert into 表名(字段1,字段2，…) values(值1,值2, …);
- 注意：
  
  1）字段名与值的类型要求一一对应
  2）字符串类型，可以使用单双引，推荐使用单引号
  3）字符串类型可以插入一切数据，数据库底层实现了隐式转换
  4）如果插入所有字段，我们可以省略字段

13.蠕虫复制(用于压力测试)

insert into 新表 select * from 旧表;

14.修改记录

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

15.删除记录

delete from 表名 where 条件;

注意：

delete from 表名 不加where条件会直接删除掉表

truncate摧毁表

truncate table 表名;

DQL

16.查询

select * from 表名 where 条件筛选（过滤）;

注意：

查询时，进行数学运算，不会改变表中原有的记录。
NULL值与其他数值进行运算，结果都为NULL。
IFNULL(字段名，指定默认值) 如果字段有值就直接返回，如果为null那么返回指定的默认值。

业务条件一般跟where

and后面一般跟的都是表的连接条件，不能出现业务条件

in

（集合）

between

(范围查询）（包头包尾）

like

（模糊查询）（_单；%多）

17.去重关键字

select distinct 字段1,字段2, … from 表名;

18.别名

select 列名 [as] 列别名，列名 [as] 列别名, … from 表名 [as] 表别名;

19.排序

select … from 表名 order by 列名[asc|desc] , 列名[asc|desc];
多条排序，在上一个排序的基础之上，再进行内容排序.

20.聚合函数

count
max
min
avg
sum

21.分组

select 分组字段 from 表名 group by 分组字段 having 分组后条件筛选;

注意：

where在分组钱对条件筛选，不能使用聚合函数
having在分组后对条件筛选，可以使用聚合函数

22.分页

select … from 表名 limit 索引开始数,每页显示个数

注意：

索引从0开始，默认值也为0
分页规律：索引=（当前页 -1）x 每页显示个数

数据库约束

对表中的数据进行先定，保证数据的正确性、有效性和完整性。

23.主键约束

限定某一列的值为空切唯一，主键就是表中记录的唯一标识

1. 添加主键
	1）创建表
		create table 表名(
			id int primary key,
			....
		);
	2）已有表
		alter table 表名 add primary key(id);
		
2. 注意：
		一张表只能有一个主键，我们可以通过联合主键设置多个字段，一般也就二个
		
3. 自增器
	1）创建表
		create table 表名(
			id int primary key auto_increment,
			....
		);	
     2）特点：
     	自增器起始值从1开始
     3）修改自增器起始值：
     	alter table 表名 auto_increment=10000

4. 删除主键约束
	语法：
		alter table 表名 drop primary key;
	注意：
		删除主键之前先清除自增器
		alter table 表名 modify id int;

24.唯一约束

限定某一列的值不能重复，用户名

1. 添加唯一约束
	1）创建表
		create table 表名(
			列名 数据类型 unique,
			....
		);
	2）特点：
		可以出现多个null

25.非空约束

1. 添加非空约束
	1）创建表
		create table 表名(
			列名 数据类型 not null,
			....
		);

26.默认值

先定某一列的默认值，没有指定的情况下默认值为null

1. 添加默认值
	1）创建表
		create table 表名(
			列名 数据类型 default 指定默认值,
			....
		);

表关系

一对多
- 添加一个外键字段
多对多
- 创建中间表，联合主键
一对一
- 主键作为外键

27.外键约束

限定两张表有关系的数据，保证数据的正确性、有效性和完整性

在从表的外键添加约束

创建表

create table 表名(
	外键字段 数据类型 [constraint][约束名] foreign key(外键字段) references 主表(主键字段);
)；

已有表

alter table 表名 add [constraint] [约束名] foreign key(外键字段) references 主表(主键字段);

删除外键约束：
- alter table 表名 drop foreign key 约束名;

特点：

主表不能删除从表已引用的数据
从表不能添加主表为拥有的数据
闲谈家主表数据再添加从表数据
先删除从表数据再删除主表数据

数据库备份与还原

1. dos命令行
	备份：mysqldump -u用户名 -p密码 指定库的名称 > 指定导出的文件路径 (*.sql)
		缺点：没有建库语句，需要我们手动创建库
	还原：mysql -u用户名 -p密码 < 指定导入的文件路径;

2. 图形化工具

DQL多表查询

28.笛卡尔积

两张表所有记录的交集，也称为笛卡尔积

select … from 左表，右表;

29.内连接

拿左表的记录去匹配右表的记录，若匹配上就显示（二表的交集）

隐式内连接

select … from 左表 , 右表 where 连接条件;
显示内连接【推荐】

select … from 左表 [inner] join 右表 on 连接条件;

内连接表的顺序无所谓

30.外连接

左外连接【重点】
- 功能：展示左表的全部记录，拿左表记录去匹配右表记录，若匹配上显示数据，若没匹配上显示NULL
- 语法:
  
  select … from 左表 lift [outer] join 右表 on 连接条件;
右外连接
- 功能：展示右表的全部记录，拿右表的记录去匹配左表记录，若匹配上显示数据，若没匹配上显示NULL
- 语法：
  
  select … from 左表 right [outer] join 右表 on 连接条件;

31.子查询

一条sql语句的执行结果，作为另一条select语法的一部分

语法：

单值
单列多行
多列多行

查询结果为单列，作为条件在where后面使用
- select … from 表名 where 指定列 = or in(子查询);
查询结果为多列，作为虚拟表在from后面使用
- select … from (子查询) as 表别名关联其他表… ;

多表查询规律

====

多表查询会出现笛卡尔积
消除笛卡尔积规律：2张表1个连接条件、3张表2个连接条件

（条件 = 表个数 - 1）

编写步骤：
- 1 确定几张表
- 2 确定连接条件（主外键）
- 3 确定显示字段
- 4 确定业务条件

TCL事务安全

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败

事务的原理：临时日志文件

32.手动提交事务

开启事务【对DML语句操作】

start transaction;
提交事务

commit;
回滚事务

rollback;

33.自动提交事务【默认开启】

mysql数据库每一条DML语句都是一个独立的事务，会自动提交

查看mysql是否开启自动提交

show variables like ‘%commit%’ ;
临时关闭自动提交

set autocommit=off;

34.回滚点

当事务开启后，一部分sql执行成功，添加一个回滚点，后续操作报错了，直接回到回滚点，保证之前的操作可以成功提交

设置保存点

savepoint 保存点名;
回滚到保存点

rollback to 保存点;

35.事务特性【ACID】

原子性：A atomicity
- 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
一致性：C consistency
- 事务执行前后，保证数据的一致
隔离性：I isolation
- 多个事务之间，互不干扰
持久性：D durability
- 事务一旦提交，持久化到数据库磁盘文件，不可逆

36.事务隔离性

多个事务之间是隔离的、相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题。设置不同的隔离级别可以解决这些问题。

脏读：一个事务中，读取到另一个事务未提交时的数据
不可重复读：一个事务中，两次读取的内容不一致(update)
幻读：一个事务中，两次读取的数量不一致(insert、delete)

数据库隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

数据库性能：1>2>3>4

数据库安全：4>3>2>1

综合考虑：2 or 3

范式

1NF

每一列不能再拆分

2NF

一张表只描述一件事情
表中的每一个普通列都依赖于主键

3NF

从表的外键必须使用主表的主键

索引

索引是帮助MySQL高效获取数据排好序的数据结构。

创建索引和删除索引【看】

-- 1.建表时添加索引
create table 表名(
   id int,  -- 主键
   username varchar(32), -- 唯一
   age int, -- 普通
   primary key(id),
   unique(username),
   index(age)
);

-- 2.添加和删除索引
alter table 表名 add index|unique(列名);
alter table 表名 drop index 索引名;

37.直接创建索引【不看】

-- 创建普通索引
create index 索引名 on 表名(列名);
-- 创建唯一索引
create unique index 索引名 on 表名(列名);
-- 创建普通组合索引
create index 索引名 on 表名(列名1,列名2);
-- 创建唯一组合索引
create unique index 索引名 on 表名(列名1,列名2);

38.修改表时指定

-- 添加一个主键，这意味着索引值必须是唯一的，切不能为NULL
alter table 表名 add primary key(id);
-- 添加唯一索引（除了NULL外，NULL可能会穿多次）
alter table 表名 add unique(列名); -- 这个列名就是索引名
-- 添加普通索引，索引值可以出现多次
alter table 表名 add index(列名); -- 这个列名就是索引名

39.创建表时指定

create table 表名(
	id int, -- 主键
    username varchar(32), -- 唯一
    age int, -- 普通
    primary key(id),
    unique(username),
    index(age)
);

40.删除索引

-- 直接删除
drop index 索引名 on 表名;
-- 修改表时删除【掌握】
alter table 表名 drop index 索引名;

41.索引创建原则

在经常需要搜索的列上建索引，
在经常需要连接的列上建索引，
在经常需要排序的列上建索引，

注意：

是不是在数据库字段中尽量多建索引好呢？
- 否，因为索引的建立和维护都是需要耗时的，
- 创建表时需要通过数据库去维护索引，添加记录、更新、修改时，也需要更新索引，会间接影响数据库的效率。

索引数据结构

索引= 排序后的数据结构

42.索引数据结构

二叉树左边节点小于父节点，右边节点大于父节点
红黑树（平衡二叉树）左旋、右旋实现平衡
Hash
1. jdk1.7 数组+链表
2. jdk1.8 数组+红黑树（链表数据小于8，大于8使用红黑树）
B-Tree（多路平衡搜索树）
B+Tree【MySQL使用】

43.数据库存储引擎

MySQL存储引擎的不同，那么索引文件保存的方式也有所不同，常见的有二种存储引擎MyISAM和InnoDB。

MyISAM

MySQL数据库5.5版本之前的默认存储引擎。不支持事务

CREATE TABLE myisam_tb(
 id INT,
 username VARCHAR(32)
)ENGINE = MYISAM

MyISAM 存储引擎数据文件和索引文件是分离（非聚集索引）

表结构文件.frm
数据文件.MYD
索引文件.MYI

InnoDB

MySQL数据库5.5版本之后的默认存储引擎。支持事务

CREATE TABLE innodb_tb(
 id INT,
 username VARCHAR(32)
)ENGINE = INNODB;

InnoDB 索引和数据文件在一起（聚集索引）

表结构.frm
索引和数据文件.ibd

MySQL函数

为了简化操作，mysql提供了大量函数（比如你想输入当前时间，可以调用now()函数）

函数可以出现的位置：插入语句的values()中，更新语句中，删除语句中，查询语句及其子句中

44.字符串函数

1.  函数：CONCAT(s1,s2...sn)
	描述：字符串 s1,s2 等多个字符串合并为一个字符串
	实例：SELECT CONCAT("我", "爱", "中", "国");
	
2.  函数：CHAR_LENGTH(str)
	描述：返回字符串 str 的字符数
	实例：SELECT CHAR_LENGTH("我爱中国");
	
3.  函数：LENGTH(str)
	描述：返回字符串 s 的字节数
	实例：SELECT LENGTH("我爱中国") ;
	
4.  函数：UCASE(s) | UPPER(s)
	描述：将字符串转换为大写
	实例：SELECT UCASE("itcast");
	
5.  函数：LCASE(s) | LOWER(s)
	描述：将字符串转换为小写
	实例：SELECT LCASE("ITCAST");
	
6.  函数：LOCATE(s1,s)
	描述：从字符串 s 中获取 s1 的开始位置
	实例：SELECT LOCATE('he','itheima');
	
7.  函数：TRIM(str) | LTRIM(str) | RTRIM(str)
	描述：字符串去空格
	实例：SELECT TRIM("  青春期");
	
8.  函数：REPLACE(s,s1,s2)
	描述：将字符串 s2 替代字符串 s 中的字符串 s1
	实例：SELECT REPLACE('abc','a','x');
	
9.  函数：SUBSTR(s, start, length)
	描述：从字符串 s 的 start 位置截取长度为 length 的子字符串
	实例：SELECT SUBSTR("itcast", 2, 3);
	
10. 函数：STRCMP(str1,str2)
	描述：比较字符串大小,左大于右时返回1，左等于右时返回0，，左小于于右时返回-1，
	实例：SELECT STRCMP("a","b");

45.日期函数

1.  函数：NOW() | CURDATE() | CURTIME()
	描述：获取系统当前日期时间、日期、时间
	实例：SELECT NOW();
	
2.  函数：YEAR(DATE) | MONTH(DATE) | DAY(DATE)
	描述：从日期中选择出年、月、日
	实例：SELECT YEAR(NOW()); 
	
3.  函数：LAST_DAY(DATE)
	描述：返回月份的最后一天
	实例：SELECT LAST_DAY(NOW());
	
4.  函数：ADDDATE(DATE,n) | SUBDATE(DATE,n)
	描述：计算起始日期 DATE 加（减） n 天的日期
	实例：SELECT ADDDATE(NOW(),10);
	
5.  函数：QUARTER(DATE)
	描述：返回日期 DATE 是第几季节，返回 1 到 4
	实例：SELECT QUARTER(NOW());
	
6.  函数：DATEDIFF(d1,d2)
	描述：计算日期 d1->d2 之间相隔的天数
	实例：SELECT DATEDIFF('2019-08-01','2019-07-01');
	
7.  函数：DATE_FORMAT(d,f)
	描述：按表达式 f的要求显示日期 d
	实例：SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

46.数字函数

1.  函数：ABS(x)
	描述：返回 x 的绝对值　　
	实例：SELECT ABS(-1);
	
2.  函数：CEIL(x) | FLOOR(x)
	描述：向上（下）取整
	实例：SELECT CEIL(1.5);
	
3.  函数：MOD(x,y)
	描述：返回x mod y的结果，取余
	实例：SELECT MOD(5,4);
	
4.  函数：RAND()
	描述：返回 0 到 1 的随机数
	实例：SELECT RAND();
	
5.  函数：ROUND(x)
	描述：四舍五入
	实例：SELECT ROUND(1.23456);
	
6.  函数：TRUNCATE(x,y)
	描述：返回数值 x 保留到小数点后 y 位的值
	实例：SELECT TRUNCATE(1.23456,3);

47.高级函数

case

在查询代码的过程中，可能需要对查询的结果进行判断。

语法

	SELECT 
		CASE [字段,值] 
			WHEN 判断条件1 
				THEN 希望的到的值1
			WHEN 判断条件2 
				THEN 希望的到的值2
			ELSE 前面条件都没有满足情况下得到的值 
		END []
	FROM
		table_name;

if

语法

SELECT IF(1 > 0,'true','false') from table_name;

cast

类型转换

语法

SELECT CAST(参数 AS 类型);

-- 类型
    字符型：CHAR
    日期 : DATE     
    时间: TIME     
    日期时间型 : DATETIME     
    浮点数 : DECIMAL      
    整数 : SIGNED

需要来一杯吗？

关注

0
点赞
踩
0

收藏

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

MYSQLDDL1.创建数据库create database 库名;create database if not exists 库名;create database 库名 character set 编码方式;2.查看数据库查看所有数据库show databases;查看建库语句show create database 库名;3.修改数据库编码alter da...
复制链接

扫一扫