第七周 Java語法總結之資料庫大全_DDL_DML_DQL_約束_備份與還原_表的關系_三大范式_多表查詢(內連接_外連接_子查詢)_musql事務

文章目錄

- 資料庫
- - - DDL陳述句:資料庫定義陳述句
    - 1.庫的增刪查改
    - - 1.查詢當前mysql下所有的資料庫
        2.創建資料庫
        3.查看創建資料庫的默認的字符集(了解)
        4.修改資料庫的字符集(了解)
        5.洗掉庫
        6.模糊查詢mysql服務中所有的帶character字符集的全域變數
    - 2.表的增刪改查
    - - 1.創建表
        2.查詢當前資料庫中有哪些表
        3.查詢當前表的結構
        修改表
        4.給student表添加一列
        5.修改表中的欄位型別
        6.修改表中的欄位名稱
        7.洗掉某一列欄位
        8.修改表的表名
        9.復制一張表結構和以前表的結果一樣
        10.洗掉表
        11.插入陳述句
        12.最基本的查詢:查全表資料
        13.修改表的資料
        14.洗掉表中資料
    - 3.查詢表的資料
    - - 1.where條件查詢
      - 查詢null
        欄位去重 distinct
      - 2.模糊查詢
      - 3.聚合函式查詢
      - 4.order by
      - 5.group by
      - 6.HAVING
      - having 與 where 的區別
      - 7.分頁查詢limit
    - 4.資料庫約束
    - - 1)默認約束
      - 2)非空約束
      - 3)唯一約束
      - 4)主鍵約束
      - 5.外鍵約束
      - 外鍵的洗掉與設定
        級聯操作
    - 5.資料庫的備份與還原
    - 6.表與表的關系
    - 7.資料庫的三大范式
    - - 1NF
        2NF
        3NF
    - 8.多表查詢
    - - 1.內連接
      - 1）隱式內連接
        2）顯示內連接
      - 2.外連接
      - 1）左外連接
      - 3.子查詢
      - 1）單行單列
        2）多行多列
        3）虛表
    - 9.mysql事務
    - 10.隔離級別

資料庫

在這里插入圖片描述

DDL陳述句:資料庫定義陳述句

1.庫的增刪查改

1.查詢當前mysql下所有的資料庫

mysql8.0  自帶的  跟5.5自帶的不一樣
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |    mysql默認的一些配置
| mysql              |    mysql庫里面包含user表用戶表 (權限用戶)
| performance_schema |    mysql性能相關的庫
| sakila             |    提供其他三個庫 sakila sys world  提供了一些例庫   練習sql陳述句
| sys                |
| world              |
| zy                 |  自定義的庫(開發者自定義)
+--------------------+
7 rows in set (0.00 sec)

2.創建資料庫

create database 庫名 ;

方式1
mysql> create database mydb_01;  
Query OK, 1 row affected (0.02 sec)

create database if not exists 庫名;

方式2
mysql> create database if not exists mydb_02;
Query OK, 1 row affected (0.02 sec)

3.查看創建資料庫的默認的字符集(了解)

show create database 庫名;

mysql> show create database mydb_01;
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                   |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| mydb_01  | CREATE DATABASE `mydb_01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.修改資料庫的字符集(了解)

alter database 庫名 (default)可以省略 character set=字符集;

mysql> alter database mydb_01 default character set gbk ;
Query OK, 1 row affected (0.01 sec)
mysql> show create database mydb_01;
+----------+----------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                    |
+----------+----------------------------------------------------------------------------------------------------+
| mydb_01  | CREATE DATABASE `mydb_01` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

5.洗掉庫

drop database 庫名;

drop database if exists 庫名;

mysql> drop database mydb_02;
Query OK, 0 rows affected (0.02 sec)

mysql> drop database if exists mydb_03;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb_01            |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
| zy                 |
+--------------------+

6.模糊查詢mysql服務中所有的帶character字符集的全域變數

show variables like '%character%' ;

mysql> show variables like '%character%' ;
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                      |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

如果出現中文亂碼:
 character_set_client 客戶端  utf8
 character_set_results  結果集 utf8

改動字符集:臨時解決中文亂碼
mysql> set character_set_client=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> set character_set_results=utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%character%' ;
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8mb3                                                 |
| character_set_connection | gbk                                                     |
| character_set_database   | ut
f8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8mb3                                                 |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

2.表的增刪改查

1.創建表

create table 表明(
	列名1 欄位型別1,
    列名2 欄位型別2,
    ...
    列名n 欄位型別n
) ; 

資料庫常用的欄位型別
int------>默認int(11)  取的是當前實際長度 (推薦)
		id int,---編號			1 
		
int(長度):指定長度
			id int(3)   		 1---->001 

varchar(字符長度):字串型別資料   '' "" 不寫 引號  ,習慣去使用''或者雙引號
double(值1,值2):小數型別  
   舉例
   		double(3,2) ----小數型別,3位數,小數點后保留2位
   					123.56
日期型別
  date  僅僅表示日期,不表示時分秒
  		"2021-8-12"
  datatime  不僅表示日期具體的時間
  		"2021-8-12 17:31:00"
  timestap:時間戳  
  		給表中插入資料, 顯示當前插入/修改/洗掉/查詢資料那一刻時間 (具體到秒"2021-8-12 18:00:01")	
    
 注意事項:就是給那一個庫中創建表  使用哪個庫(選擇資料庫名)
 use 庫名;
mysql> use mydb_01;
Database changed
mysql>

mysql> create table student(
    -> id int,
    -> name varchar(20),
    -> gender varchar(10),
    -> address varchar(50),
    -> age int
    -> )
    -> ;
Query OK, 0 rows affected (0.05 sec)

2.查詢當前資料庫中有哪些表

show tables ;

mysql> show tables;
+-------------------+
| Tables_in_mydb_01 |
+-------------------+
| student           |
+-------------------+
1 row in set (0.01 sec)

3.查詢當前表的結構

desc 表名 ;

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| gender  | varchar(10) | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| age     | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

修改表

4.給student表添加一列

alter table 表名 add 欄位名稱欄位型別;

mysql> alter table student add email varchar(50) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| gender  | varchar(10) | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| age     | int         | YES  |     | NULL    |       |
| email   | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

5.修改表中的欄位型別

alter table 表名 modify 欄位名稱更改后的欄位型別;

mysql> alter table student modify address varchar(30) ;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| gender  | varchar(10) | YES  |     | NULL    |       |
| address | varchar(30) | YES  |     | NULL    |       |
| age     | int         | YES  |     | NULL    |       |
| email   | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

6.修改表中的欄位名稱

alter table 表名 change 舊列名新列名欄位型別;

mysql> alter table student change gender sex varchar(10) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| sex     | varchar(10) | YES  |     | NULL    |       |
| address | varchar(30) | YES  |     | NULL    |       |
| age     | int         | YES  |     | NULL    |       |
| email   | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

7.洗掉某一列欄位

alter table 表名 drop 欄位名稱;

mysql> alter table student drop email;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| sex     | varchar(10) | YES  |     | NULL    |       |
| address | varchar(30) | YES  |     | NULL    |       |
| age     | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

8.修改表的表名

alter table 舊表名 renameto 新表名;

mysql> alter table student rename to stu;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_mydb_01 |
+-------------------+
| stu               |
| teacher           |
+-------------------+
2 rows in set (0.01 sec)

9.復制一張表結構和以前表的結果一樣

create table 新表名 like 舊表名;

mysql> create table teacher like student; //欄位復制
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_mydb_01 |
+-------------------+
| student           |
| teacher           |
+-------------------+
2 rows in set (0.01 sec)

10.洗掉表

drop tabble 表名;

drop table if exists 表名;

mysql> drop table if exists teacher;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_mydb_01 |
+-------------------+
| stu               |
+-------------------+
1 row in set (0.00 sec)

DML陳述句:操作表的記錄

11.插入陳述句

1）**insert into 表名 values(值1,值2,值3…) ;**插入全表資料 :那么每一個引數值必須和欄位型別匹配! (不會報錯,警告)

mysql> insert into stu values(1,'高圓圓','女','西安市',30) ;
Query OK, 1 row affected (0.01 sec)

插入全表:一次性插入多條資料

2）insert into 表名 values(值1,值2…),(值1,值2…),(值1,值2…) ;

  INSERT INTO 
  	  stu 
  VALUES 
      (3,'文章','男','西安市',35),
      (4,'馬伊琍','女','上海市',42),
      (5,'德瑪西亞','男','艾歐尼亞',25) ;

插入部分欄位

3） insert into 表名(部分欄位1,欄位2…) values(值1,值2…)

INSERT INTO 
stu(NAME,sex,address) VALUES('王寶強','男','咸陽市') ;

-- 注意:插入部分欄位,必須欄位數量和插入值的數量必須匹配
-- 不管插入部分欄位還全部欄位,那么必須要和當前型別一致

12.最基本的查詢:查全表資料

select * from 表名;

mysql> insert into stu values(1,'高圓圓','女','西安市',30) ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu ;
+------+--------+------+---------+------+
| id   | name   | sex  | address | age  |
+------+--------+------+---------+------+
|    1 | 高圓圓 | 女   | 西安市  |   30 |
+------+--------+------+---------+------+
1 row in set (0.01 sec)

如果中文亂碼
改動下面的字符集編碼即可
mysql> show variables like '%character%' ;
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | gbk                                                     |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

mysql> set character_set_client = utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set character_set_results = utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

13.修改表的資料

語法:按條件修改 update

1）update 表名 set 欄位名稱 = 值 where 欄位名稱= 值;

-- 修改name是王寶強的將他的id設定為7
UPDATE stu SET id = 7 WHERE NAME = '王寶強' ;

-- 修改id為7的age設定為37
UPDATE stu SET age = 37 WHERE id =  7 ;

一次修改多個欄位
2）update 表名 set 欄位名稱1 =值1 ,欄位名稱2 =值2 where 條件;

-- 將id為的6的姓名修改為 '張三豐' ,性別修改為'男' 
UPDATE 
	stu
SET 
	NAME = '張三豐',
	sex = '男'
WHERE	
	id =  6 ;

3）如果不帶條件修改:就是批量修改

-- 將修改name為 '高圓圓'UPDATE stu SET NAME = '高圓圓' ;    -- 一般都是帶條件進行修改!

14.洗掉表中資料

1）帶條件來洗掉
delete from 表名 where 欄位名稱 = 值; //洗掉指定的記錄

-- 洗掉id = 6的這條記錄
DELETE FROM stu WHERE id = 6 ;
-- 洗掉id = 6的這條記錄
DELETE FROM stu WHERE id = 6 ;

2）洗掉全表資料
delete from 表名;

DELETE FROM stu ;

truncate table 表名;

TRUNCATE TABLE stu ;

洗掉全表的語法

delete from 表名;
truncate table 表名; 兩個區別

	1)delete from 表名:只是洗掉全表資料;表的結構還存在,
		如果表中 存在主鍵并且自增長約束,那么不會受影響,下一次在插入資料
		繼續在之前的基礎上繼續自增長!
		
	2)truncate table 表名 ; 
		將表中資料洗掉的同時洗掉了表,然后在創建一張一模一樣空表,
		肯定影響自增長主鍵的值,再次插入資料,自增長從1開始...
		等價于
			drop table my_use;
			創建一個當前一模一樣的表結構

-- 創建一張新的表 my_user表
CREATE TABLE my_user(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 用戶編號 主鍵加增長
	NAME VARCHAR(20),
	age INT ,
	gender VARCHAR(5)
) ;
INSERT INTO my_user(NAME ,age ,gender) 
	VALUES('張三',20,'男'),('李四',22,'女'),('文章',25,'男')  ;
INSERT INTO my_user(NAME,age,gender) VALUES('王五',18,'男') ;
-- delete from my_user where id =  8 ;

-- 洗掉全表資料
DELETE FROM my_user ;	

-- 洗掉全部資料
TRUNCATE TABLE my_user ;

SELECT * FROM my_user;

3.查詢表的資料

DQL之基本的查詢陳述句

1）查詢全表資料

? SELECT * FROM 表名 ;

實際開發中,* (通配符),一般查詢全表資料,需要帶全部欄位

2）select 欄位名稱串列 from 表名 ;

SELECT   -- 指定全部欄位
	id ,
	NAME,
	sex,
	address,
	age
FROM  stu ;	

-- 查詢指定的欄位
-- select 欄位名稱串列 from 表名;
SELECT 
	id ,
	NAME ,
	address,
	age
FROM stu ;

3）查詢指定欄位時可以通過as 關鍵字指定別名,as可以省略

SELECT
	id AS '學生編號',
	NAME AS '姓名',
	address AS '地址',
	age AS '年齡'
FROM stu ;

-- as 可以省略
SELECT
	id  '學生編號',
	NAME  '姓名',
	address  '地址',
	age  '年齡'
FROM stu ;

1.where條件查詢

DQL帶條件查詢
where條件查詢
      可以基本運算子:比較運算子(<,>,<=,>=,!=)/邏輯運算子(|| && /and /or)/賦值運算子 =
      where后面多個or   in(集合資料)    在兩個值之間 between 值1 and 值2
      mysql 判斷某個欄位為null , is null  /is not null
 模糊查詢 like
 聚合函式 count(列名)/max(列名)/min(列名)/sum(列名)/avg(列名)
 排序查詢 order by
 分組查詢 group by
 篩選查詢 having
 分頁查詢limit

-- 創建一個學生表
CREATE TABLE student ( 
 id INT, -- 編號
 NAME VARCHAR(20), -- 姓名
 age INT, -- 年齡 
 sex VARCHAR(5), -- 性別 
 address VARCHAR(100), -- 地址 
 math INT, -- 數學
 english INT -- 英語 
 );
 INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES 
 (1,'馬云',55,'男',' 杭州',66,78),
 (2,'馬化騰',45,'女','深圳',98,87),
 (3,'馬景濤',55,'男','香港',56,77),
 (4,'柳巖 ',20,'女','湖南',76,65),
 (5,'柳青',20,'男','湖南',86,NULL),
 (6,'劉德華',57,'男','香港 ',99,99),
(7,'馬德',22,'女','香港',99,99),
(8,'德瑪西亞',18,'男','南京',56,65);

-- 條件查詢   
-- 查詢年齡大于20歲的人的全部資訊
SELECT 
	id  '編號',
	NAME '姓名',
	age '年齡',
	sex '性別',
	address '地址',
	
	math '數學成績',
	english '英語成績'
FROM 
	student 
WHERE
	age > 20 ;

-- 查詢年齡在20歲和30歲之間的學生資訊
SELECT		
	*
FROM
	student
WHERE
	age >= 20 && age <=30 ; -- &&:Java的邏輯運算子
	
-- 另一種語法	
SELECT		
	*
FROM
	student
WHERE
	age >= 20 AND  age <=30 ;-- and mysql表示并列關系
	
-- 另一種語法:mysql 	在兩個值之間 between 值1 and 值2
SELECT -- 查詢部分欄位 并且年齡在20-30
	NAME '姓名',
	age '年齡',
	address '地址',
	math '數學成績',
	english '英語成績'
FROM 
	student 
WHERE
	age 
BETWEEN 20 AND 30 ;

-- 查詢年齡是18或者20或者30歲的學生的編號,姓名,年齡以及地址   (|| 或者 or)
SELECT
	id  '編號',
	NAME '姓名',
	age '年齡',
	address '地址'
FROM 
	student 
WHERE 
	age = 18 OR age = 20 OR age = 30 ;

-- mysql另一種語法 where 欄位名稱 in(多個值)
SELECT
	
	id  '編號',
	NAME '姓名',
	age '年齡',
	address '地址'
FROM 
	student 
WHERE 
	age IN(18,20,30) ;

查詢null

-- 查詢英語成績為null的學號編號,姓名,性別,地址.數學成績資訊
/* 
 select
	id ,
	name,
	sex,
	address,
	math
from
	student
where 
     english == null ; -- java中的用法可以這樣用   == = (都不行)
*/				
SELECT
	id ,
	NAME,
	sex,
	address,
	math,
	english
FROM
	student
WHERE 
     english IS NULL ; -- mysql用法

-- 查詢英語成績不為null的人 ,is not null 的用法
SELECT
	id ,
	NAME,
	sex,
	address,
	math,
	english
FROM
	student
WHERE 
     english IS NOT NULL ;

-- 查詢英語和數學成績總分的學生新
SELECT 
	id 編號 ,
	NAME 姓名 ,
	sex 性別 ,	
	address	'地址',
	-- (math+english) as '總分'
	(math+IFNULL(english,0)) '總分'
FROM 
	student ;

**mysql 內置函式ifnull(值1,值2) ; **

IFNULL(english,0) :
-- 如果當前英語成績為null,給默認值0ql

欄位去重 distinct

-- 查詢欄位,對欄位去重 (distinct)
SELECT 
    DISTINCT address 地址 
    FROM student ;

-- 查詢年齡不是20歲的學生資訊
 SELECT	
	*
FROM  student
WHERE age != 20 ;  -- != Java中這種語法 !=
-- mysql中的不等于 <> 
SELECT 
	*
FROM 
	student
WHERE
	age <> 20 ;

2.模糊查詢

糊查詢mysql服務中帶字符集相關的變數 show variables  like '%character%' ; 
模糊查詢 like
select 欄位串列  from 表名 where 欄位名稱 like  '%字符%' ;
	% :包含的指定的字符  使用'%字符值%' 模糊查詢包含指定字符的資訊
	_ :代表單個字符(一個_下劃線代表一個字符)
	兩個相結合使用: '_%字符值%_'  三個字符:中間字符包含指定的值進行模糊查詢

-- 查詢當前學生表中姓名包含馬的學生資訊
SELECT 
	*
FROM 
	student
WHERE
	NAME 
LIKE 
	'%馬%' ;

-- 查詢第二個字符包含化的學生資訊
SELECT
	*
FROM
	student
WHERE 
	NAME
LIKE
	'_化%' ;

-- 查詢姓名是三個字符的人
SELECT 
	*
FROM 
	student
WHERE 
	NAME
LIKE
	'___' ;
	
-- 應用場景: 搜索框中輸入關鍵字查詢---使用到模糊查詢

3.聚合函式查詢

聚合函式查詢---- >查詢結果:單行單列的資料
    count(列名) :總記錄數     count(列名)查詢總記錄數的時候,一般都使用非業務欄位查詢
    max(列名): 最大值
    min(列名欄位):最小值
    sum(欄位名稱):求和
    avg(列名):平均分
    select 聚合函式(列名) from 表名;

查詢當前student這個表的全部總記錄數

-- 如果使用english欄位查詢總記錄數
SELECT 
	COUNT(english) -- 使用業務欄位查詢(可能某個值為null,不會進行記錄)
FROM 
	student ;  -- 7條記錄

-- 可以使用函式設定如果當前某個值null,給默認值
SELECT	
	COUNT(IFNULL(english,0)) 總記錄數
FROM
	student;

-- 建議id來查詢
SELECT
	COUNT(id) 總條數
FROM 
	student ;
	
-- 查詢數學的平均成績 avg(列名)
SELECT
	AVG(math) '數學平均成績'
FROM
	student ;	-- 79.5000
	
-- 查詢英語成績---總成績(求和 sum(列名))
SELECT
	SUM(IFNULL(english,0)) 英語總分
	
FROM
	student ;
	
-- 查詢英語成績的最高成績
SELECT
     
      MAX(IFNULL(english,0)) 英語最高分
FROM 
	student ;

-- 查詢數學成績最低分
SELECT
   MIN(math) 數學最低分
 FROM
	student ;
-- 聚合函式使用最多的:count函式,avg函式

4.order by

排序查詢:order by 欄位名稱  asc/desc (升序/降序)
select 欄位串列 from 表名 order by 欄位名 排序規則; -- 單個欄位進排序

-- 數學成績安裝升序排序
SELECT
       *
FROM
	student 
ORDER BY math  ;  -- 如果欄位名稱后面沒有帶排序規則:則默認升序排序

-- 英語降序排序
SELECT 
	NAME ,
	age,
	sex,
	address,
	IFNULL(english,0) 英語成績
FROM 
      student
ORDER BY english DESC ; -- 降序

-- 針對多個欄位同時排序,當前第一欄位值相等,則按照第二個欄位的排序規則執行
-- select  欄位串列  from 表名 order by 欄位名稱1 升序1 ,欄位名稱2 升序2;
-- 查詢全表資料,數學成績降序,英語成績升序
SELECT
	*
FROM
	student 
ORDER BY   
	math DESC,
	english ASC ;

5.group by

分組查詢:group by  
    分組group by 分組欄位;
    查詢的時候可以查詢分組欄位, 
    group by 后面不能使用聚合函式

-- 現在需要按照性別分組-----分組之后查詢出總人數 
-- 性別-- 男/女 
SELECT   -- 查詢分組欄位
       sex '性別',
       COUNT(id) '人數'
FROM
	student
GROUP BY 
	sex  ; -- 性別

-- 現在需要按照性別分組-----分組之后查詢出總人數,數學的平均分
-- 條件:數學成績不大于70分的人不參與分組;
SELECT
	sex '性別', -- 查詢分組欄位
	COUNT(id) '總人數',
	AVG(math) '數學平均成績'
FROM
	student
WHERE  	
	math > 70 -- 條件:數學成績大于70分人參與分組
GROUP BY 
	sex ; -- 性別分組

6.HAVING

篩選 having
	having 必須置于group by 之后,where 置于 group by 之前
	group by不能聚合函式,但是having后面可以聚合函式

-- 現在需要按照性別分組-----分組之后查詢出總人數,數學的平均分
-- 條件:數學成績不大于70分的人不參與分組 
-- 篩選條件:總人數大于2的一組
SELECT
	sex '性別', -- 查詢分組欄位
	COUNT(id) '總人數',
	AVG(math) '數學平均成績'
FROM
	student
WHERE  	
	math > 70 -- 條件:數學成績大于70分人參與分組
GROUP BY 
	sex  -- 性別分組
HAVING  
	COUNT(id) > 2 ;

-- 優化	
SELECT
	sex 性別, -- 查詢分組欄位
	COUNT(id) 總人數,
	AVG(math) 數學平均成績
FROM
	student
WHERE  	
	math > 70 -- 條件:數學成績大于70分人參與分組
GROUP BY 
	sex  -- 性別分組
HAVING  
	總人數 > 2 ;

having 與 where 的區別

where 子句
    1) 對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾資料，即先過濾再分組， 
    2) where 后面不可以使用聚合函式 
having 子句 
    1) having子句的作用是篩選滿足條件的組，即在分組之后過濾資料，即先分組再過濾， 
    2) having后面可以使用聚合函式

7.分頁查詢limit

select 欄位串列 from 表名  limit 起始行數,每頁顯示多少條;

limit 起始行數=(當前頁碼數-1)*每頁顯示的條數,每頁顯示條數;

-- 每頁顯示3條記錄
-- 查詢第一頁的資料
-- limit 起始行數=(當前頁碼數-1)*每頁顯示的條數,每頁顯示條數;
SELECT * FROM student LIMIT 0,3 ;
-- 查詢第二頁的資料
SELECT * FROM student LIMIT 3 ,3 ;
-- 查詢第三頁資料
SELECT * FROM student LIMIT 6,3 ;
-- 第四頁資料
SELECT * FROM student LIMIT 9,3 ;

-- 查詢全表
SELECT * FROM student ;

4.資料庫約束

約束用戶操作表的一種行為

1)默認約束

默認約束：防止出現非法資料null(沒有插入造成null值)
當前沒有給那個欄位設定值的時候,此時默認約束就會起作用
	gender VARCHAR(2) DEFAULT '女'
如果沒有默認約束：
    可能用戶操作資料庫的時候,插入非法資料(沒有意義的資料)
    如果沒有給某個欄位賦值,默認值null

-- 創建一個新的表
CREATE TABLE test(
	id INT , -- 編號
	NAME VARCHAR(10) , -- 姓名
	gender VARCHAR(2) DEFAULT '女'  -- 性別  -- 默認約束 防止出現非法資料null(沒有插入造成null值)
) ;

INSERT INTO test VALUES(1,'張三','男') ;
-- 可能用戶操作資料庫的時候,插入非法資料(沒有意義的資料)

-- 通過sql陳述句修改表的型別,洗掉默認約束
ALTER TABLE  test MODIFY gender VARCHAR(2) ;

-- 修改表加入默認約束
ALTER TABLE  test MODIFY gender VARCHAR(2)  DEFAULT '女' ;
 DELETE FROM test WHERE id = 3 ;

INSERT INTO test VALUES(1,'張三','男') ;
-- 可能用戶操作資料庫的時候,插入非法資料(沒有意義的資料)

-- 通過sql陳述句修改表的型別,洗掉默認約束
ALTER TABLE  test MODIFY gender VARCHAR(2) ;

-- 修改表加入默認約束
ALTER TABLE  test MODIFY gender VARCHAR(2)  DEFAULT '女' ;
 DELETE FROM test WHERE id = 3 ;

2)非空約束

DROP TABLE test ;
CREATE TABLE test(
	id INT ,
	NAME VARCHAR(10) NOT NULL  -- 非空約束
);

INSERT INTO test VALUES(1,NULL) ; -- 直接插入null值
-- insert into test (id) values(1) ; 沒有給姓名賦值

INSERT INTO test VALUES(1,'') ; -- 存在值,只是空字符 和null不一樣

-- 洗掉非空約束
ALTER TABLE test MODIFY NAME VARCHAR(10) ;
UPDATE test SET NAME = '高圓圓' WHERE id = 1 ;
INSERT INTO test VALUES(2,NULL) ; -- Column 'NAME' cannot be null

-- 修改表,加入非空約束
ALTER TABLE test MODIFY NAME VARCHAR(10) NOT NULL  ;
DELETE FROM test WHERE id = 2 ;

3)唯一約束

nuique

DROP TABLE test;
CREATE TABLE test(
	id INT ,
	NAME VARCHAR(10),
	phone VARCHAR(11) UNIQUE -- 唯一約束 :可以有null值,不能重復
) ;

INSERT INTO test VALUES(1,'張三','13666668888') ;
-- INSERT INTO test VALUES(1,'張三',null) ;

INSERT INTO test VALUES(2,'李四','13666668889') ;
-- INSERT INTO test VALUES(2,'李四','13666668888') ;

-- 通過陳述句洗掉唯一約束
-- alter table test modify phone varchar(11) ; 錯誤語法
-- 洗掉唯一約束的sql alter table test drop index 欄位名稱;
ALTER TABLE test DROP INDEX phone ;

INSERT INTO test VALUES(4,'趙六','13666668878') ;
DELETE FROM test WHERE id = 4 ;

-- 添加唯一約束
ALTER TABLE test MODIFY phone VARCHAR(11) UNIQUE ;

4)主鍵約束

primary key

主鍵約束 (非空+唯一特點) primary key 
都會給當前非業務欄位去設定主鍵(xxid)

DROP TABLE test  ;
CREATE TABLE test(
	id INT PRIMARY KEY ,   -- 非業務欄位
	NAME VARCHAR(10),
	gender VARCHAR(2)
) ;

INSERT INTO test VALUES(1,'洪學佳','男'),(2,'馬三奇','男') ;
-- insert into test values(1,'馬嘉鈺','男') ;  id值重復 -- Duplicate entry '1' for key 'test.PRIMARY'
-- insert into test values(null,'雷郁','男') ;--  id直接插入null值 Column 'id' cannot be null

-- 洗掉test 表的主鍵 
alter table test drop primary key;
-- 添加主鍵 
alter table test add primary key(id);

5.外鍵約束

foreign key

-- 單獨創建一個部門表,來存盤部門
-- 一般一個表中:都會id欄位(非業務欄位:自增的主鍵約束都會設定在這個上面)
CREATE TABLE dept(	-- 部門表
	id INT PRIMARY KEY AUTO_INCREMENT, -- 部門編號
	dept_name VARCHAR(20) -- 部門名稱
);
-- 插入資料
INSERT INTO dept VALUES(1,'開發部'),(2,'測驗部'),(3,'運維部') ;

-- 創建表:員工表
CREATE TABLE emp( 
	id INT PRIMARY KEY AUTO_INCREMENT, -- 員工編號
	NAME VARCHAR(20), -- 員工姓名
	gender VARCHAR(10), -- 性別
	dept varchar(20), -- 部門  
) ;

在這里插入圖片描述

-- 創建表:員工表
-- 創建員工表的時候:員工表的dept_id應該關于部門表主鍵id 
-- 創建員工表的時候就需要添加外鍵(外鍵所在表稱為"從表")
CREATE TABLE emp( 
	id INT PRIMARY KEY AUTO_INCREMENT, -- 員工編號
	NAME VARCHAR(20), -- 員工姓名
	gender VARCHAR(10), -- 性別
	dept_id INT, -- 部門編號   
	CONSTRAINT  fk_demp_emp  -- 宣告 外鍵名稱
	FOREIGN KEY (dept_id) -- 作用在指定從表的指定欄位
	REFERENCES 	-- 關聯
	dept(id)    -- 部門表的主鍵id
) ;

-- 插入員工資料
INSERT INTO emp VALUES(1,'高圓圓','女',1),
(2,'趙又挺','男',1),
(3,'文章','男',2),
(4,'馬伊琍','女',3),
(5,'姚笛','女',2),
(6,'馬三奇','男',1) ;

-- 插入一個不存員工,而且插入一個不存在部門編號
INSERT INTO emp (NAME,gender,dept_id) VALUES('王寶強','男',4) ;
-- 非法資料了,因為部門表中并沒有4號部門

針對員工表(從表:外鍵所在的表)的插入/修改/洗掉不能直接操作 從表 
										    部門表:主表
可以先洗掉外鍵在進行操作

外鍵的洗掉與設定

外鍵約束(兩張表設定一個外鍵):兩種設定方式
	1)創建表的直接添加外鍵
	2)通過alter table 表名 add .... 方式添加外鍵

-- 洗掉外鍵約束 ALTER TABLE emp DROP FOREIGN KEY 外鍵名稱
ALTER TABLE emp DROP FOREIGN KEY  fk_demp_emp ;
-- 洗掉id為8的員工
DELETE FROM emp WHERE id =  8 ;

-- 通過修改表添加外鍵
ALTER TABLE emp ADD 
CONSTRAINT --  宣告
 	fk_demp_emp  -- 外鍵名稱
FOREIGN KEY (dept_id) -- 作用在指定從表的指定欄位
REFERENCES 	-- 關聯
    dept(id)  ;  -- 部門表的主鍵id

有了外鍵有關聯關系,所以不直接操作主表;修改/洗掉 (很麻煩)
	1)修改或者從表的資料,讓這個資料和主表沒有關聯
	2)修改/洗掉 主表的資料

級聯操作

級聯操作:CASCADE
	級聯修改 ON UPDATE CASCADE 
		當前修改主表的資料,那么從表和主表相關關聯的資料一會被隨著更改掉
	級聯洗掉 ON DELETE CASCADE,
		當洗掉主表的資料,那么從表和主表關聯的資料一會被隨著洗掉掉

-- 先sql陳述句將外鍵約束洗掉,然后添加外鍵并且添加級聯操作
ALTER TABLE emp DROP FOREIGN KEY fk_demp_emp ;

ALTER TABLE emp ADD 
CONSTRAINT fk_dept_emp
FOREIGN KEY  (dept_id)
REFERENCES dept(id) 
ON UPDATE CASCADE -- 添加級聯修改
ON DELETE CASCADE ; -- 添加級聯洗掉

-- 直接修改主表的資料:
-- 將開發部的部門編號更改為4
UPDATE dept SET id = 4 WHERE id = 1 ;
-- 洗掉主表的資料,從表的隨之洗掉
-- 洗掉id為4號部門的開發部
DELETE FROM dept WHERE id = 4;

5.資料庫的備份與還原

資料庫的備份和還原
兩種方式:
1）圖形界面話:簡單直觀
  	選擇資料庫---右鍵----備份---->選擇匯出的路徑(結構以及資料都保存)
  	將之前存在庫,然后選擇 執行指定sql腳本----> 選擇指定的sql腳本---進行執行即可!
2）命令列方式
	備份:mysqldump -uroot -p密碼 備份資料庫名稱 >  保存的本地地址
	還原:
		將原來洗掉,新建庫,使用庫, source 將保存的本地地址

6.表與表的關系

一對一的關系:是一種特例 (應用場景不多)
	舉例:  人和身份證 
		一個人對應一張身份證 
		一個身份證屬于某個人的	
一對多的關系 
	舉例:  員工表和部門表
		一部門可以有多個員工,一個員工屬于某一個部門的(員工表中有一個外鍵)
多對多的關系

在這里插入圖片描述

7.資料庫的三大范式

1NF

1NF :資料庫表的每一列都是不可分割的原子資料項	不能出現復合項

在這里插入圖片描述

2NF

2NF:第二范式就是在第一范式的基礎上所有列(所有欄位)完全 依賴于主鍵列(主鍵欄位)，

在這里插入圖片描述

3NF

3NF:在第二范式2NF基礎上,非主鍵欄位不能傳遞依賴于主鍵(外鍵解決)

在這里插入圖片描述

8.多表查詢

-- 部門表
CREATE TABLE dept(
 	id INT PRIMARY KEY AUTO_INCREMENT, -- 部門編號
	 NAME VARCHAR(20)  -- 部門名稱
 );
 INSERT INTO dept (NAME) VALUES ('開發部'),('市場部'),('財務部');

-- 員工表
CREATE TABLE emp (
 id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10), 
  gender CHAR(1), -- 性別 
  salary DOUBLE, -- 工資 
  join_date DATE, -- 入職日期 
  dept_id INT, 
 FOREIGN KEY (dept_id)
 REFERENCES dept(id) -- 外鍵，關聯部門表(部門表的主鍵)
 );
INSERT INTO emp(NAME,gender,salary,join_date,dept_id)VALUES('孫悟空','男',7200,'2013-02-24',1); 
INSERT INTO emp(NAME,gender,salary,join_date,dept_id)VALUES('豬八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id)VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id)  VALUES('蜘蛛精','女',4500,'2011-03-14',1);

-- 需求:查詢員工表和部門表的所有資訊
SELECT *
FROM emp ,  dept ;
-- 查詢多張表:員工表5條記錄,部門表有3條記錄  (A*B=15)
-- 出現迪爾卡乘積的原因:沒有連接條件
-- 通過外鍵解決欄位冗余問題 員工表的部門dept_id關聯與部門表的主鍵id

1.內連接

1）隱式內連接

隱式內連接-----通過where 陳述句作為連接條件來查詢(按照外鍵關聯關系)
操作步驟:
	1)查詢哪張表
	2)要查詢的是指定表中的哪個欄位
	3)表和表之間的關聯關系問題

-- 1)員工表和部門表
-- 2)查詢員工表的中name,gender,salary以及部門表的部門名稱
-- 3)連接條件:dept_id= 部門表的id
SELECT
	e.name  '員工姓名' ,	
	e.`gender` '性別',
	e.`salary` '工資',
	e.`dept_id` ,
	d.id ,
	d.`name` '部門名稱'
FROM	
	emp e,dept d -- 員工表的別名e  ,部門表的別名d
WHERE 
	e.`dept_id` = d.`id` ;

2）顯示內連接

(實際開發中,多去使用where條件查詢,查詢速度快 ,sql優化的一種方式)
    select <欄位串列> 
    from 表名1 
    (inner) join 
    表名2  
    on 關聯關系(連接條件);

SELECT 
	t1.*,-- 員工表中的所有欄位 (實際開發中,* 不建議使用!)
	t2.`name` -- 部門表的部門名稱
FROM
	emp t1
-- inner  可以省略,不寫
JOIN  dept t2
ON 
	t1.`dept_id` = t2.`id` ;

2.外連接

-- 給員工表插入一條資料
INSERT INTO emp(NAME,gender,salary,join_date) 
VALUES('高圓圓','女',12000,'2021-8-14'); 

-- 要查詢所有的員工表資訊的同時關聯查詢部門表資訊(部門名稱即可)
-- 即使員工沒有部門資訊,也得查詢出來...
SELECT 
     t1.`name` '員工名稱',
     t1.`gender` '性別',
     t1.`salary` '工資',
     t1.`join_date` '入職日期',
     t2.`name` '部門名稱'
FROM 
     emp t1
INNER JOIN dept t2
ON
      t1.`dept_id` = t2.`id` ; -- 交集的條件

-- 通過內連接查詢存在問題 :只是查出了交集部分的資料,沒有部門編號的員工并沒有查詢出來
-- 內連接就不能使用了
-- 使用 多表查詢之外連接查詢

1）左外連接

使用 多表查詢之外連接查詢
    左外連接 left (outer) join: 通用
            將兩種表中左表的資料全部進行查詢并且以及他們交集部分的資料全部查詢
    右外連接 right (outer) join
語法格式：
    select
    欄位串列
    from
    表名1 -- 左表
    left outer join 表名2 
    on 連接條件 ;

在這里插入圖片描述

SELECT 
	t1.`name` '姓名',
	t1.`gender` '性別',
	t1.`salary` '工資',
	t1.`join_date` '入職日期',
	t1.`dept_id` ,
	t2.`name` '部門名稱'
FROM
	emp  t1 -- 左表(見圖中的A表)
LEFT 
-- outer
JOIN   
  dept t2   -- (圖中的B表)
ON 
    t1.`dept_id` = t2.`id` ; -- 交集部分資料
    
SELECT 
	*
FROM
    dept t1
RIGHT OUTER JOIN  emp t2
ON
    t2.`dept_id` = t1.`id` ;

3.子查詢

1）單行單列

在這里插入圖片描述

-- 子查詢
-- 情況1:單行單列的情況:通用使用運算子進行操作
-- 需求:查詢最高工資的員工資訊
-- 1)查詢最高工資是多少 : 聚合函式
/*
select
	max(salary)
from 
	emp ;   -- 12000
*/
-- 2) 查詢等于最高工資的員工資訊
/*
select
	emp.`name`,
	emp.`salary`,
	emp.`gender`,
	emp.`join_date`
from
	emp 
where 
	salary = 12000 ;
	*/
	
-- 一步走: 將12000的sql陳述句代入過來即可
SELECT
	emp.`id`,
	emp.`name`,
	emp.`salary`,
	emp.`gender`,
	emp.`join_date`
FROM
	emp 
WHERE 
	salary = (SELECT MAX(salary) FROM  emp ) ;

-- 查詢工資小于平均工資的員工資訊
-- 1)查詢平均工資是多少
/*
select 
	avg (salary)
	 from emp ;
*/
-- 2)查詢員工工資小于	6883.333....的員工資訊
8
SELECT  
	*
FROM 
	emp
WHERE 
	salary < (SELECT AVG (salary) FROM emp ) ;

2）多行多列

-- 子查詢的第二種情況 :多行多列的情況 ---使用關鍵字in(集合資料)
-- 需求:查詢 在財務部或者市場部的員工資訊
-- 分步驟
-- 1)查部門表中財務部和市場部的id是多少
/*
select
	id 
from
	dept 
where 
	name = '市場部' or name = '財務部' ;-- 2 , 3
*/	
-- 2)在員工表中查 id為 財務部 (3) 或者市場部(2)的員工資訊 
     
 -- 優化  步驟)sql陳述句 or---->in(集合資料) 
 -- 在xxx資料內  /not in exists /not exists /any(sql)
SELECT
	t.`name` '姓名',
	t.`gender` '性別',
	t.`salary` '工資',
	t.`join_date` '入職日期',
	t.`dept_id` '部門編號'
FROM
	emp t
WHERE 
      t.`dept_id` IN(
		SELECT
			id 
		FROM
			dept 
		WHERE 
		NAME = '市場部' 
		OR 
		NAME = '財務部') ;

3）虛表

-- 子查詢的情況3:
-- 使用select陳述句查詢的結果作為一個虛表,使用這個虛表然后繼續和其他表進行查詢

-- 查詢入職日期大于'2011-3-14號的員工資訊以及所在的部門資訊
-- 入職日期 join_date > '2011-03-14' 將查詢作為虛表 和部門表繼續進行查詢
	SELECT   * FROM emp WHERE join_date > '2011-03-14';
-- 可以使用左外連接
SELECT
	t2.*, -- 員工的所有資訊
	t1.`name` '部門名稱'	
FROM
	dept t1 -- 部門表
LEFT OUTER JOIN 
	(SELECT   * FROM emp WHERE join_date > '2011-03-14') t2
ON
	t1.`id` = t2.`dept_id` ;

-- 最基本隱式內連接	
SELECT
    t1.* , -- 員工所有資訊
    t2.`name` -- 部門名稱 
FROM
     emp t1 ,
     dept t2 
WHERE 
     t1.`dept_id` = t2.`id` 
     AND
t1.`join_date` > '2011-03-14' ;

9.mysql事務

在這里插入圖片描述

事務:在業務操作程序中,一次性可能同時操作多個sql陳述句,防止
    操作多個sql陳述句時候出現問題,將這整個業務操作看成一個整體,進行處理
    這些多個sql要么一次性全部執行成功,要么同時失敗!
    
后期所有業務操作: 增刪改  都需要使用事務進行管理
增刪改:一次性執行多張表的多個sql陳述句(同時操作)------>SpringAOP 面向切面編程    基于OOP(面向物件)

事務的特點:ACID
    原子性:就是事務操作業務的中sql,要么同時執行成功,要么同時失敗!
    一致性:操作sql陳述句的前后,總數量保持不變
        	轉賬之前:1000 1000
        	轉賬之后:500  1500 
    隔離性:
        將業務使用事務管理,業務和業務之間分離的,
        事務和事務之間不能相互影響---->事務是獨立的
    持久性: 如果一旦事務被提交了,是永久存盤的,即使關機也還存在!

-- 轉賬 
-- 創建一張賬戶表
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 賬戶編號
	NAME VARCHAR(20), -- 賬戶名稱
	balance INT -- 賬戶余額
) ;

INSERT INTO account(NAME ,balance) VALUES('zhangsan',1000),('lisi',1000) ;
SELECT * FROM account ;

-- 沒有開啟事務,需要管理多個操作(一次執行多個sql陳述句),可以將多個操作
-- 使用事務管理,開啟事務
-- 沒有使用事務之前
-- zhangsan- 500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan' ;
出問題了
UPDATE account SET balance = balance + 500  WHERE NAME = 'lisi' ;

-- 將轉賬業務操作看成整體,開啟事務
START TRANSACTION ;  -- 開啟手動提交事務(默認自動提交)
-- 執行業務 操作
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan' ;	
-- 中間出問題了
UPDATE account SET balance = balance + 500  WHERE NAME = 'lisi' ;
-- 事務回滾:回滾到在操作陳述句之前的狀態 (余額都是1000)
ROLLBACK ;
-- 如果轉賬中沒問題,將手動事務進行提交
COMMIT;

10.隔離級別

read uncommitted ; 讀未提交  安全性最差  不能有效防止臟讀
read committed ;讀已提交  安全相對第一個高一些,能夠有效臟讀,不能夠防止可重復讀的問題
repeatable read :可重復讀  能夠有效防止臟讀,可重復讀(MySQL默認隔離界別)
serializable 串行話  安全性最高，兩個事物，當一個事務沒有提交，這個事務不能操作其他事情

查看全域的隔離級別的命令
    查詢隔離級別 
        select @@tx_isolation;  mysql5.5/5.7都可以 
        SELECT @@transaction_isolation; mysql8.0
	設定隔離級別 
        set global transaction isolation level 級別字串;
        SELECT @@transaction_isolation; 

隔離級別不同,會出現不同的問題
    臟讀:一個事務讀取另一個沒有提交的事務(最嚴重的問題)
    不可重復讀: 一般都是update陳述句影響,兩個事務中,一個事務讀取的前后的內容不一致!
    幻讀:一般insert/delete :影響兩個事務中,前后數量不一致!

1）第一種隔離級別：Read uncommitted(讀未提交)
如果一個事務已經開始寫資料，則另外一個事務不允許同時進行寫操作，但允許其他事務讀此行資料，該隔離級別可以通過“排他寫鎖”，但是不排斥讀執行緒實作，這樣就避免了更新丟失，卻可能出現臟讀，也就是說事務B讀取到了事務A未提交的資料
解決了更新丟失，但還是可能會出現臟讀

2）第二種隔離級別：Read committed(讀提交)
如果是一個讀事務(執行緒)，則允許其他事務讀寫，如果是寫事務將會禁止其他事務訪問該行資料，該隔離級別避免了臟讀，但是可能出現不可重復讀，事務A事先讀取了資料，事務B緊接著更新了資料，并提交了事務，而事務A再次讀取該資料時，資料已經發生了改變，
解決了更新丟失和臟讀問題

3）第三種隔離級別：Repeatable read(可重復讀取)
可重復讀取是指在一個事務內，多次讀同一個資料，在這個事務還沒結束時，其他事務不能訪問該資料(包括了讀寫)，這樣就可以在同一個事務內兩次讀到的資料是一樣的，因此稱為是可重復讀隔離級別，讀取資料的事務將會禁止寫事務(但允許讀事務)，寫事務則禁止任何其他事務(包括了讀寫)，這樣避免了不可重復讀和臟讀，但是有時可能會出現幻讀，(讀取資料的事務)可以通過“共享讀鏡”和“排他寫鎖”實作，
解決了更新丟失、臟讀、不可重復讀、但是還會出現幻讀

4）第四種隔離級別：Serializable(可序化)
提供嚴格的事務隔離，它要求事務序列化執行，事務只能一個接著一個地執行，但不能并發執行，如果僅僅通過“行級鎖”是無法實作序列化的，必須通過其他機制保證新插入的資料不會被執行查詢操作的事務訪問到，序列化是最高的事務隔離級別，同時代價也是最高的，性能很低，一般很少使用，在該級別下，事務順序執行，不僅可以避免臟讀、不可重復讀，還避免了幻讀

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/295606.html

標籤：其他

上一篇：一招解決微信小程式中的H5快取問題

下一篇：keepalived+haproxy搭建LNMP架構并做資料同步

第七周 Java語法總結之資料庫大全_DDL_DML_DQL_約束_備份與還原_表的關系_三大范式_多表查詢(內連接_外連接_子查詢)_musql事務_隔離級別

文章目錄

資料庫

DDL陳述句:資料庫定義陳述句

1.庫的增刪查改

1.查詢當前mysql下所有的資料庫

2.創建資料庫

3.查看創建資料庫的默認的字符集(了解)

4.修改資料庫的字符集(了解)

5.洗掉庫

6.模糊查詢mysql服務中所有的帶character字符集的全域變數

2.表的增刪改查

1.創建表

2.查詢當前資料庫中有哪些表

3.查詢當前表的結構

修改表

4.給student表添加一列

5.修改表中的欄位型別

6.修改表中的欄位名稱

7.洗掉某一列欄位

8.修改表的表名

9.復制一張表結構和以前表的結果一樣

10.洗掉表

11.插入陳述句

12.最基本的查詢:查全表資料

13.修改表的資料

14.洗掉表中資料

3.查詢表的資料

1.where條件查詢

查詢null

欄位去重 distinct

2.模糊查詢

3.聚合函式查詢

4.order by

5.group by

6.HAVING

having 與 where 的區別

7.分頁查詢limit

4.資料庫約束

1)默認約束

2)非空約束

3)唯一約束

4)主鍵約束

5.外鍵約束

外鍵的洗掉與設定

級聯操作

5.資料庫的備份與還原

6.表與表的關系

7.資料庫的三大范式

1NF

2NF

3NF

8.多表查詢

1.內連接

1）隱式內連接

2）顯示內連接

2.外連接

1）左外連接

3.子查詢

1）單行單列

2）多行多列

3）虛表

9.mysql事務

10.隔離級別