Mysql基础复习

小熊软糖ིྀ

已于 2022-10-14 16:42:41 修改

阅读量289

点赞数

分类专栏： MYSQL 文章标签： mysql 数据库 sql

于 2022-10-03 10:43:33 首次发布

本文链接：https://blog.csdn.net/qq_53888996/article/details/127149243

版权

MYSQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

SQL操作

DDL:操作数据库、表

操作数据库：CRUD

1.	C(Create):创建
	//创建数据库：
	create database 数据库名称;
	//创建数据库，判断不存在，再创建：
	create database if not exists 数据库名称;
	//创建数据库，并指定字符集
	create database 数据库名称 character set 字符集
名;
2.	R(Retrieve)：查询
	//查询所有数据库的名称:
	show databases;
	//查询某个数据库的字符集:查询某个数据库的创建语句
	show create database 数据库名称;
3.	U(Update):修改
	//修改数据库的字符集
	alter database 数据库名称 character set 字符集
名称;
4.	D(Delete):删除
	//删除数据库，表和库的删除用drop
	drop database 数据库名称;
	//判断数据库存在，存在再删除
	drop database if exists 数据库名称;

数据表操作

1.	C(Create):创建
1.	语法：
create table 表名(
    id int(11)prinmary key,//字段类型 字段名称(长度,小数点后面带多少俗称精度)主键,
   列名1 数据类型1,
   列名2 数据类型2,
   ....
   列名n 数据类型n //最后一行不加逗号
  ）

数据库类型：

int：整数类型
age int,
double:小数类型
score double(5,2)
date:日期，只包含年月日，yyyy-MM-dd
datetime:日期，包含年月日时分秒 yyyy-MM-dd
timestamp:时间错类型:包含年月日时分秒:
yyyy-MM-dd HH:mm:ss
如果将来不给这个字段赋值，或赋值为nu11，则
默认使用当前的系统时间，来自动赋值←
字符串
varchar字符串
name yarchar (20) :姓名最大20个字符

DML：增删改表中数据

在这里插入图片描述
## drop，truncate，delete 三者的区别

//案例：删除id为1的
DELETE FROM student where id =1;

DQL：查询表中的记录

select * from 表名;

1. 语法：  //sfwgh(师傅叫我干活)
	select
		字段列表
	from
		表名列表
	where
		条件列表
	group by
		分组字段
	having
		分组之后的条件
	order by
		排序
	limit
		分页限定

基础查询

在这里插入图片描述

条件查询

3.	条件查询
 where子句后跟条件
 运算符
   > 、< 、<= 、>= 、= 、<>
 	BETWEEN...AND
	IN( 集合)
 	LIKE：模糊查询
 	占位符：
 	_:单个任意字符
 	%：多个任意字符，通配符，前后可以有任意多字符，但是必须包含%后面的哪个字母
 	IS NULL
 	and	或 &&
 	or	或 ||
	not	或 !

模糊查询

NOT IN

DQL:查询语句

排序查询

1.	排序查询
	语法：order by 子句
	order by 排序字段1 排序方式1 ，	排序字段2 排序方式
2...
	排序方式：
	ASC：升序，默认的。
	DESC：降序。

	注意：
	如果有多个排序条件，则当前边的条件值一样时，才会判断第二条
件。

在这里插入图片描述
多个排序条件

聚合函数

2.	聚合函数：将一列数据作为一个整体，进行纵向的计算。
1.	count：计算个数
   		一般选择非空的列：主键
		count(*)
2.	max：计算最大值
3.	min：计算最小值
4.	sum：计算和
5.	avg：计算平均值
 
帮助对表中的记录进行简单的统计
 
 注意：聚合函数的计算，排除null值。解决方案：
1.	选择不包含非空的列进行计算
2.	IFNULL函数

分组查询

3.	分组查询:
1.	语法：group by 分组字段；
2.	注意：
	1.	分组之后查询的字段：分组字段、聚合函数
	2.	where 和 having 的区别？
		1.	where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
		2.	where 后不可以跟聚合函数，having可以进行聚合函数的判断。

分页查询（限制查询）

1.	分页查询
2.	语法：limit 开始的索引,每页查询的条数;
3.	公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
4.	索引默认从0开始

-- 每页显示3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页

5.	limit 是一个MySQL"方言"

在这里插入图片描述

MYSQL分页公式
			a+（n-1）*d
			（page-1）*pagesize
			0+（3-1）*2

约束

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

非空约束not null

非空约束：not null，某一列的值不能为null

1.	创建表时添加约束
CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL -- name为非空
);

2.	创建表完后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

3.	删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);

主键约束：primary key

主键约束：primary key。
1.	注意：
1.	含义：非空且唯一
2.	一张表只能有一个字段为主键
3.	主键就是表中记录的唯一标识

2.	在创建表时，添加主键约束
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);

3.	删除主键
-- 错误 alter table stu modify id int ; ALTER TABLE stu DROP PRIMARY KEY;

4. 创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;

5.	自动增长：
   	   1. 概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长
	   2. 在创建表时，添加主键约束，并且完成主键自增长
			create table stu(
				id int primary key auto_increment,-- 给id添加主键约束
				name varchar(20)
				);
	   3.	删除自动增长
			ALTER TABLE stu MODIFY id INT;
	   4.	添加自动增长
			ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

唯一约束：unique

唯一约束：unique，某一列的值不能重复

1.	注意：
	 唯一约束可以有NULL值，但是只能有一条记录为null

2.	在创建表时，添加唯一约束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 手机号
);

3.	删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;

4.	在表创建完后，添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20)
UNIQUE;

外键约束：foreign key

外键约束：foreign key,让表于表产生关系，从而保证数据的正确性。

1.	在创建表时，可以添加外键
	 语法：
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称)
references 主表名称(主表列名称)
);

2.	删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3.	创建表之后，添加外键
	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY
	(外键字段名称) REFERENCES 主表名称(主表列名称);
	
4.	级联操作
		1.	添加级联操作
			语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称
			FOREIGN KEY (外键字段名称) REFERENCES 主
			表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE；
		2.	分类：
				1.	级联更新：ON UPDATE CASCADE
				2.	级联删除：ON DELETE CASCADE

check约束

数据库的设计原则

1.	多表之间的关系
	1.	分类：
		1.	一对一(了解)：
			*	如：人和身份证
			*	分析：一个人只有一个身份证，一个身份证只能对应一个人
		2.	一对多(多对一)：
			*	如：部门和员工
			*	分析：一个部门有多个员工，一个员工只能对应一个部门
		3.	多对多：
			*	如：学生和课程
			*	分析：一个学生可以选择很多门课程，一个课程也可以被很多
学生选择
	2.	实现关系：
		1.	一对多(多对一)：
			*	如：部门和员工
			*	实现方式：在多的一方建立外键，指向一的一方的主键。
		2.	多对多：
			*	如：学生和课程
			*	实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
		3.	一对一(了解)：
			*	如：人和身份证
			*	实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式
（5NF，又称完美范式）。

分类：
	1.	第一范式（1NF）：每一列都是不可分割的原子数据项
	2.	第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码
				（在1NF基础上消除非主属性对主码的部分函数依赖）
		几个概念：
			1.	函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
				例如：学号-->姓名。	（学号，课程名称） -->分数
			2.	完全函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
				例如：（学号，课程名称） --> 分数
			3.	部分函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
				例如：（学号，课程名称） -- > 姓名
			4.	传递函数依赖：A-->B, B -- >C . 如果通过A属性
				(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
				例如：学号-->系名，系名-->系主任
			5.	码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
				例如：该表中码为：（学号，课程名称）
				*	主属性：码属性组中的所有属性
				*	非主属性：除过码属性组的属性

	3.	第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

数据库的备份与还原

1.	命令行：
		*	语法：
		*	备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路
径
		*	还原：
			1.	登录数据库
			2.	创建数据库
			3.	使用数据库
			4.	执行文件。source 文件路径
2.	图形化工具：

多表查询

多表查询理解
 数据库高级——多表查询
 多表查询（图解join）

多表查询

1.	内连接查询：
只查询a，b，表重符合连接条件的记录，对于表中不符合条件的数据，不会查询，取a，和b的交集。
		1.	隐式内连接
		2.	显式内连接
		3.	内连接查询

2.	外链接查询：
查询主参照表中的所有记录，以及从表中符合连接条件的记录，主表中没有相应字段值的用null显示。
		1.	左外连接
		2.	右外连接

事务

事务的基本介绍

1.	事务的基本介绍
	1.	概念：
	    	如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

	2.	操作：
			1.	开启事务： start transaction;
			2.	回滚：rollback;
			3.	提交：commit;
    3. MySQL数据库中事务默认自动提交
		*	事务提交的两种方式：
		*	自动提交：
		*	mysql就是自动提交的
		*	一条DML(增删改)语句会自动提交一次事务。
		*	手动提交：
		*	Oracle 数据库默认是手动提交事务
		*	需要先开启事务，再提交
		*	修改事务的默认提交方式：
		*	查看事务的默认提交方式：SELECT @@autocommit; --
			1 代表自动提交	0 代表手动提交
			* 修改默认提交方式： set @@autocommit = 0;

在这里插入图片描述

事务的四大特征：

事务的四大特征：
1.	原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2.	持久性：当事务提交或回滚后，数据库会持久化的保存数据。
3.	隔离性：多个事务之间。相互独立。
4.	一致性：事务操作前后，数据总量不变

事务的隔离级别（了解）


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

存在问题：
	1.	脏读：一个事务，读取到另一个事务中没有提交的数据
	2.	不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
	3.	幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别：
	1.	read uncommitted：读未提交
			产生的问题：脏读、不可重复读、幻读
	2.	read committed：读已提交 （Oracle）
			产生的问题：不可重复读、幻读
	3.	repeatable read：可重复读 （MySQL默认）
			产生的问题：幻读
	4.	serializable：串行化
			可以解决所有的问题

1、看题干中需要查询哪些字段
2、分析这些字段是否来源于同一张表(单表查询or多表查询)
3、看看题干中的限制条件如果明确出现了‘按照’‘不同’ ‘分别’ 十有八九是分组查询
4、如果是多表查询，则确定连接条件，找出两张表中共有的字段
5、groupby后面一定跟having

练习

DROP TABLE IF EXISTS `brand`;
CREATE TABLE `brand` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bname` varchar(255) DEFAULT NULL,
  `profile` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `car`;
CREATE TABLE `car` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `color` varchar(255) DEFAULT NULL,
  `model` varchar(255) DEFAULT NULL,
  `carno` varchar(255) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `bid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 添加品牌信息 
insert into brand values (1,'宝马', '你值得拥有');
INSERT into brand (id,bname,'profile') values (2,'奔驰','驰骋千里');
-- 批量插入
INSERT into brand values(3,'劳斯莱斯', '流'),(4,'宾利'，'给予你想要的舒适生活');

-- 把id =1的记录bname改为法拉利
update brand set bname = '法拉利' where id =1;
delete from brand where id =3;

-- 查询所有品牌信息
SELECT * FROM brand;
SELECT * FROM car;

-- 查询品牌表并修改列bname名显示:此举只会修改查询结果中的列名，不会实际更改数据库中的列名 
SELECT bname 品牌名称 FROM brand;

-- 查询品牌编号的值小于3的数据 
SELECT * FROM brand WHERE id<3;

-- 查询品牌编号的值不等于2的数据(!=或者<>都表示不等于) 
SELECT * FROM brand WHERE id!=2;
SELECT * FROM brand WHERE id<>2;

-- 查询品牌编号的大于5并且品牌是奥迪的数据（等于比较时使用的是单等号） 
SELECT * FROM brand WHERE id>5 AND bname='奥迪';
-- 查询品牌为奥迪或者编号大于5的数据 
SELECT * FROM brand WHERE id>5 OR bname='奥迪';

-- 查询品牌编号为5-9之间的数据（2种） 
select * from brand where id>=5 and id<=9;
SELECT * FROM brand WHERE id between 5 and 9;

-- 查询品牌编号为5或9的数据 （2种）
SELECT * FROM brand WHERE id=5 or id=9;
select * from brand where id in (5,9);

-- 查询品牌编号不为5和9的数据 （2种）
SELECT * FROM brand WHERE id!=5 and id!=9;
select * from brand where id not in (5,9);

-- 查询以奥迪开头的简介内容
select * from brand where left(PROFILE,2) = '奥迪';

-- 查询以跑结尾的简介内容 
select * from brand where PROFILE like '%跑';
select PROFILE from brand where PROFILE like '%跑';

-- 查询包含小的简介内容 
select * from brand where PROFILE like '%小%';

-- 查询包含小的简介内容并且让编号升序排序 
select * from brand where PROFILE like '%小%' GROUP BY id ASC;
select PROFILE from brand where PROFILE like '%小%' GROUP BY id ASC;

-- 查询包含小的简介内容并且让编号降序排序
select * from brand where PROFILE like '%小%' GROUP BY id DESC;

-- 普通查询升序排序 
select * from brand GROUP BY id ASC;
-- 普通查询降序排序 
select * from brand GROUP BY id DESC;

-- 查询品牌表中的第一条数据(limit关键字一定出现在sql语句的最后) 
select * from brand LIMIT 0,1;

-- 查询品牌表中的最后一条数据 
select * from brand GROUP BY id DESC LIMIT  0,1;
select * from brand GROUP BY id DESC LIMIT  1;
-- 13条数据，每页显示5条。 
-- 第1页的数据 
-- 第2页的数据 
-- 第3页的数据 
-- 第n页的,每页显示m条 
-- select * from brand GROUP BY id ASC LIMIT (n-1)*m,m; 
select * from brand limit 0,4;
select * from brand limit 4,4;

 
 
-- 添加汽车测试数据 
-- create table 表名(
--     id int(11)prinmary key,
-- -- 		字段类型 字段名称(长度,小数点后面带多少俗称精度)主键,
--    列名1 数据类型1,
--    列名2 数据类型2,
--    ....
--    列名n 数据类型n 
-- -- 	 最后一行不加逗号
--   );


-- 查询汽车信息
select * from car;

-- 查询汽车信息并且打印汽车的品牌
select bid from car;

-- 左外连接查询汽车信息并且打印汽车的品牌(左外连接的意思就是以左边的表为主表，他的内容一定全部展示，不管匹配与否) 
select bid,bname from car INNER JOIN brand ON car.bid=brand.id;
select bid,bname from brand RIGHT JOIN car  ON brand.id=car.bid;

-- 右外连接查询品牌信息并且筛选汽车价格在5000以内的数据 
select bid,bname from car RIGHT JOIN brand ON car.bid=brand.id WHERE price<=5000;

-- 聚合函数 ,求价格平均值
select AVG(price)from car ;

-- 统计行数 

-- 价格总数(可以拼接条件) 

-- 价格平均值 

-- 查询每个品牌的车辆总数(SUM)并显示品牌id(bname)(分组查询时，能用来展示的列只能是聚合函数列或者用来分组的列) 
select COUNT(id),bid FROM car GROUP BY bid;

-- 查询每个品牌的车辆总数要求不看品牌编号为9的数据 
select COUNT(id),bid FROM car GROUP BY bid HAVING bid<>99;

-- 查询车牌号为null的值 
SELECT * FROM car WHERE carnoi IS NULL;

-- 查询车牌号不为null的值 
SELECT * FROM car WHERE carno IS not null;

-- 修改车辆编号为1的数据的价格为自身价格+80
update car set price = price+80 where id =1;

-- 字符串拼接 
SELECT CONCAT('我在前',model,'我在后') FROM car WHERE id=1;
SELECT CONCAT(color,model,'在后') FROM car WHERE id=3;