MYSQL学习笔记

最新推荐文章于 2023-10-16 20:58:58 发布

·田同学·

最新推荐文章于 2023-10-16 20:58:58 发布

阅读量108

点赞数

分类专栏：学习文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45811645/article/details/116191082

版权

学习专栏收录该内容

7 篇文章 1 订阅

订阅专栏

MYSQL

DDL （数据定义语言）

对数据库的增删改查

创建Create
- create database [数据库名称]；创建数据库
- create database if not exists [数据库名称]；先判断数据库是否存在，然后再创建数据库
- create database [数据库名称] character set gbk/utf8; 创建数据库并设置字符编码集
查询Retrieve
- show databases; 查询所有数据库的名称
- show create database [数据库名称]；查询创建数据的语句，以及创建数据库使用的编码集
修改Update
- alter database [数据库名称] character set utf8/gbk; 修改数据库的字符集
删除Delete
- drop database [数据库名称]；删除数据库
- drop database if exists [数据库名称]；删除数据之前，先判断数据库是否存在。
使用数据库
- select database(); 查询当前正在使用的数据库名称
- use [数据库名称]；使用数据库

对操作表

创建Create
- create tabel [表名] （
列名数据类型，

列名数据类型，

列名数据类型，

…

列名数据类型

）；
- create table [表名] like [想要复制的表]；复制表操作
查询Retrieve
- select * from [数据库名称]；查询数据库内的所有表格。
- desc [表名]；查询表的结构
修改Update
- alter table [表名] rename to [新的表名]；对表进行重命名。
- alter table [表名] character set gbk/utf8; 修改表的字符集；
- alter table [表名] add 列名数据类型；对表添加一列。
- alter table [表名] change 列名新的列名数据类型；修改表的列名，和数据类型
- alter table [表名] modify 列名数据类型；修改表的某一列的数据类型；
- alter table [表名] drop 列名；删除表的某一列
删除Delete
- drop table [表名]；删除表
- drop table if exists [表名]; 先判断表是否存在，再删除表。

DML （数据表操作语言，增删改查表中的数据）

添加数据
- insert into [表名] （列名，列名，列名，列名）values (值1，值2，值3 ，值4)；像表中对应的列添加值；
- insert into [表名] values（值1，值2，值3，值n）；向表中添加值。
  
  注意：除了int类型，其他数据类型的值添加时都需要用单引号或者双引号。
删除数据
- delete from 表名 where 条件；删除表中满足条件的数据；
- delete from 表名；删除表中所有的数据；（不推荐使用，有多少条数据就会执行多少条删除操作，效率低）
- TRUNCATE TABLE 表名；删除表中所有的数据（先删除表，再创建一张一模一样的表，只用执行删除和创建两次操作，效率高）
修改数据
- update table 表名 set 列名1=值1，列名2=值2，列名3=值3，列名4=值4,…where 条件；修改表中的满足条件的列的数据；
- update table 表名 set 列名1=值1，列名2=值2，列名3=值3，列名4=值4,…；修改表中所有的数据

DQL （数据表查询语言）

selet * from [表名]；查询数据表中的数据
select

字段列表

from

表名列表

where

条件列表

group by

分组字段

having

分组之后的条件

order by

排序

limit

分页限定
- 基础查询
  1. select DISTINCT 列名 from 表名；查询表中的列，去除重复结果集
  2. select 字段1，字段2，字段3，… from 表名；多个字段的查询
  3. select 字段1，字段2，字段1+字段2， from 表名；字段查询的时，字段也可以使用四则运算
  4. select 字段1，字段2，字段1+字段2 as 字段名， from 表名；字段查询的时，字段也可以使用四则运算（修改运算后的别名）
  5. select 字段1，字段2 ifnull(表达式1，表达式2) from 表名；
    
    表达式1：哪个字段需要判断是否为null；
    
    表达式2：如果字段为null后的替换值；
- 条件查询
  1. 运算符
    
    < , > , <=, >=, <>, =,
    
    BETWEEN …AND
    
    IN （集合）
    
    LIKE
    
    IS NULL
    
    and 或 &&
    
    or 或 ||
    
    not 或者！
- 排序查询
  - select * from 表名 order by 排序条件排序方式；
    
    排序方式：
  ASC 升序
  
  DESC 降序
  
  注意：如果有多个排序条件，则当第一个排序条件相同时，才会判断第二个条件
- 聚合函数：将一列数据作为一个整体，进行纵向的计算。
  1. count：计算个数 select count(列名) from 表名；
  2. max:计算最大值 select max(列名) from 表名；
  3. min:计算最小值 select min(列名) from 表名；
  4. sum:计算和
  5. avg:计算平均值
    
    select 聚合函数（列名） from 表名；
- 分组查询
  1. select 列名，列名（或聚合函数） from 表名 group by 分组条件；
    
    例如：select sex, avg(math) from student group by sex;
  2. Having 用于分组之后的条件限定；
    
    where 不可以进行聚合函数的判断，Having可以进行聚合函数的判断
- 分页查询
  1. select * from 表名 LIMIT 0,3; 第一页 (0：代表从id号为0开始查询，3表示每页展示三条数据)
    
    select * from 表名 LIMIT 3,3; 第二页（3:第二页从id号为4开始查询，3代表每页展示三条数据）

约束

主键约束：primary key
- 删除主键：ALTER TABLE 表名 DROP PRIMARY KEY;
  
  ALTER TABLE 表名 MODIFY 列名数据类型；
非空约束：not null
唯一约束：unique
外键约束：foreign key
- 创建表后添加外键 alter table 表名 add constraint constraint 外键名称 foreign key (外键列名称) references 主表名称（主表列名称）
- 在创建表的时候可以添加外键
  
  CREATE TABLE 表名（
  
  …
  
  外键列
  
  constraint 外键名称 foreign key (外键列名称) references 主表名称（主表列名称）
  
  ）；
  - 删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  - 级联更新/删除：
    
    添加级联操作：
```
 									ALTER TABLE  表名  ADD CONSISTENT CONSTRAINT 外键名称 FOREIGN KEY ( 外键列名称 ) 													REFERENCES 主表名称（主表列名称） ON UPDATE CASCADE  ;
```
    删除级联操作：ALTER TABLE 表名 ADD CONSISTENT CONSTRAINT 外键名称 FOREIGN KEY ( 外键列名称 ) REFERENCES 主表名称（主表列名称） ON DELETE CASCADE ;
  - CREATE TABLE department ( id INT PRIMARY KEY AUTO_INCREMENT, dep_name VARCHAR ( 20 ), dep_location VARCHAR ( 20 ) );
    CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR ( 20 ),
    age INT,
    dep_id INT,
    CONSTRAINT emp_dept_fk FOREIGN KEY ( dep_id ) REFERENCES department ( id )
    );
    INSERT INTO department
    VALUES
    ( NULL, ‘研发部’, ‘广州’ ),(
    NULL,
    ‘销售部’,
    ‘深圳’
    );
    INSERT INTO employee ( NAME, age, dep_id )
    VALUES
    ( ‘张三’, 20, 1 );
    INSERT INTO employee ( NAME, age, dep_id )
    VALUES
    ( ‘李四’, 21, 1 );
    INSERT INTO employee ( NAME, age, dep_id )
    VALUES
    ( ‘王五’, 26, 2 );
    INSERT INTO employee ( NAME, age, dep_id )
    VALUES
    ( ‘阿狗’, 21, 2 );
    INSERT INTO employee ( NAME, age, dep_id )
    VALUES
    ( ‘阿猫’, 28, 1 );
    INSERT INTO employee ( NAME, age, dep_id )
    VALUES
    ( ‘阿猪’, 25, 2 );
  ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
  
  ALTER TABLE employee ADD CONSISTENT CONSTRAINT emp_dept_fk FOREIGN KEY ( dep_id ) REFERENCES department ( id ) ON UPDATE CASCADE;
自动增长：auto_increment

创建表时添加主键约束，并完成自动增长

CREATE TABLE STUDET(
```
 		id 	INT    PRIMARY KEY  AUTO_INCREMENT,         --添加主键约束，并设置自动增长
```
name VARCHAR(20)

);

第一范式（1NF）

			每一列都是不可分割的原子数据项

第二范式（2NF）

在第一范式的基础上，非码属性必须完全依赖于候选码

函数依赖：A–>B，如果通过A属性（属性组）的值可以唯一确定B属性的值。则称B属性依赖于A.
完全函数依赖：A–>B,如果A是一个函数组，则B属性值的确定需要完全依赖与A属性组中的所有值。
部分函期依赖：A–>B，如果A是一个函数组，则B属性值的确定只需依赖A属性组中的某些值。
传递函数依赖：A–>B,B–>C,如果通过A属性（属性组）的值可以唯一确定B属性的值。再通过B属性（属性组）的值可以唯一确定C

属性的值。则称C传递函数依赖于B
码：在一个表中，一个属性或者属性组被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码。

数据库的备份

命令行

备份 mysqldump -u用户名 -p密码数据库名称 > 保存的路径

还原：

1.登录数据库

2.创建数据库

3.使用数据库

4.执行文件。source 文件路径
图形化工具
笛卡尔积

有两个集合A B,取这两个集合的所有组成情况
要完成多表查询，需要清除无用的数据

多表查询的分类：

内连接查询：

1.隐式内连接：使用where条件，消除无用数据
1. 显式内连接： select 字段列表 from 表名1 inner join 表名2 on 条件；（注意：inner 可以省略）
外连接查询
1. 左外连接：
  - select 字段列表 from 表1 left outer join 表2 on 条件；（注意：outer 可以省略）
  - 查询的是左表所有数据以及其交集部分。
2. 右外连接：
  - select 字段列表 from 表1 right outer join 表2 on 条件；（注意：outer 可以省略）
  - 查询的是右表所有数据以及交集部分。
子查询

概念：查询中嵌套查询，称嵌套查询为子查询。

例如：查询工资最高的员工信息

SElECT MAX(salary) FROM emp; 1.查询最高的工资是多少

SELECT * FROM emp WHERE emp.‘salary’ =9000; 2.查询员工信息等于9000的。

以上两步可以用子查询，如下：

SELECT * FROM emp WHERE emp.‘salary’=(SELECT MAX(salary) FROM emp);

DCL （数据控制语言）

一、事务定义

Transaction
事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

二、转账操作理解事务

关于银行账户转账操作，账户转账是一个完整的业务，最小的单元，不可再分————————也就是说银行账户转账是一个事务

以下是银行账户表t_act(账号、余额)，进行转账操作

actno       balance
1           500
2           100
123

转账操作

update t_act set balance=400 where actno=1;
update t_act set balance=200 where actno=2;
12

以上两台DML语句必须同时成功或者同时失败。最小单元不可再分，当第一条DML语句执行成功后，并不能将底层数据库中的第一个账户的数据修改，只是将操作记录了一下；这个记录是在内存中完成的；当第二条DML语句执行成功后，和底层数据库文件中的数据完成同步。若第二条DML语句执行失败，则清空所有的历史操作记录，要完成以上的功能必须借助事务

三、事务四大特征(ACID)

原子性(A)：事务是最小单位，不可再分
一致性©：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
隔离性(I)：事务A和事务B之间具有隔离性
持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

四、关于事务的一些术语

开启事务：Start Transaction
事务结束：End Transaction
提交事务：Commit Transaction
回滚事务：Rollback Transaction

五、和事务相关的两条重要的SQL语句(TCL)

commit:提交
rollback：回滚

六、事务开启的标志？事务结束的标志？

开启标志：

- 任何一条DML语句(insert、update、delete)执行，标志事务的开启
1

结束标志(提交或者回滚)：

-  提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
-  回滚：失败的结束，将所有的DML语句操作历史记录全部清空
12

七、事物与数据库底层数据

在事物进行过程中，未结束之前，DML语句是不会更改底层数据，只是将历史操作记录一下，在内存中完成记录。只有在事物结束的时候，而且是成功的结束的时候，才会修改底层硬盘文件中的数据

八、在MySQL中，事务提交与回滚

在MySQL中，默认情况下，事务是自动提交的，也就是说，只要执行一条DML语句就开启了事物，并且提交了事务

以上的自动提交机制是可以关闭的

对t_user进行提交和回滚操作

提交操作(事务成功)

start transaction
DML语句

commit

mysql> start transaction;#手动开启事务
mysql> insert into t_user(name) values('pp');
mysql> commit;#commit之后即可改变底层数据库数据
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
|  3 | pp   |
+----+------+
3 rows in set (0.00 sec)
123456789101112

回滚操作(事务失败)

start transaction
DML语句

rollback

mysql> start transaction;mysql> insert into t_user(name) values('yy');mysql> rollback;mysql> select * from t_user;+----+------+| id | name |+----+------+|  1 | jay  ||  2 | man  ||  3 | pp   |+----+------+3 rows in set (0.00 sec)123456789101112

九、事务四大特性之一————隔离性(isolation)

事物A和事物B之间具有一定的隔离性
隔离性有隔离级别(4个)
- 读未提交：read uncommitted
- 读已提交：read committed
- 可重复读：repeatable read
- 串行化：serializable

1、 read uncommitted

- 事物A和事物B，事物A未提交的数据，事物B可以读取到- 这里读取到的数据叫做“脏数据”- 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别123

2、read committed

- 事物A和事物B，事物A提交的数据，事物B才能读取到- 这种隔离级别高于读未提交- 换句话说，对方事物提交之后的数据，我当前事物才能读取到- 这种级别可以避免“脏数据”- 这种隔离级别会导致“不可重复读取”- Oracle默认隔离级别123456

3、repeatable read

- 事务A和事务B，事务A提交之后的数据，事务B读取不到- 事务B是可重复读取数据- 这种隔离级别高于读已提交- 换句话说，对方提交之后的数据，我还是读取不到- 这种隔离级别可以避免“不可重复读取”，达到可重复读取- 比如1点和2点读到数据是同一个- MySQL默认级别- 虽然可以达到可重复读取，但是会导致“幻像读”12345678

4、serializable

- 事务A和事务B，事务A在操作数据库时，事务B只能排队等待- 这种隔离级别很少使用，吞吐量太低，用户体验差- 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发123

十、隔离级别与一致性关系

十一、设置事务隔离级别

方式一

可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。

该选项值可以是：

– READ-UNCOMMITTED– READ-COMMITTED– REPEATABLE-READ– SERIALIZABLE•   例如：[mysqld]transaction-isolation = READ-COMMITTED12345678

方式二

通过命令动态设置隔离级别
• 隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。
• 其语法模式为：

        SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>        其中的<isolation-level>可以是：    –   READ UNCOMMITTED    –   READ COMMITTED    –   REPEATABLE READ    –   SERIALIZABLE    •   例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;1234567

十二、隔离级别的作用范围

•   事务隔离级别的作用范围分为两种： –   全局级：对所有的会话有效 –   会话级：只对当前的会话有效 •   例如，设置会话级隔离级别为READ COMMITTED ：mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；或：mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；•   设置全局级隔离级别为READ COMMITTED ： mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；123456789

十三、查看隔离级别

•   事务隔离级别的作用范围分为两种： –   全局级：对所有的会话有效 –   会话级：只对当前的会话有效 •   例如，设置会话级隔离级别为READ COMMITTED ：mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；或：mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；•   设置全局级隔离级别为READ COMMITTED ： mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

·田同学·

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL学习笔记

MYSQLDDL （数据定义语言）对数据库的增删改查创建Createcreate database [数据库名称]；创建数据库create database if not exists [数据库名称]；先判断数据库是否存在，然后再创建数据库create database [数据库名称] character set gbk/utf8; 创建数据库并设置字符编码集查询Retrieveshow databases;
复制链接

扫一扫