MySql學習筆記06

每一天都要精彩發表於2018-06-21

課程回顧

一對一關聯案例1：查詢每個員工的名字和主管領導的名字

select e.ename 員工姓名,m.ename 領導姓名
from emp e join emp m
on e.mgr=m.empno;

案例2：查詢主管領導名字為blake的所有員工名字

select e.ename 員工姓名,m.ename 領導姓名 from emp e join emp m on e.mgr=m.empno where m.ename=`blake`

案例3：查詢有商品的分類資訊及上級分類資訊往titemcategory表內部插入以下資料 id 162 name 辦公用品 id 229 name 文具 id 913 name 戶外用品 -插入資料

 insert into titemcategory (id,name) values(162,`辦公用品`),(229,`文具`),(913,`戶外用品`);

-查詢所有有商品的分類id

SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL

-查詢分類詳情

select * from titemcategory where id in (SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL)

-查詢上級分類

 select n.*,c.name 上級分類名稱 from (select * from titemcategory where id in (SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL)) n join titemcategory c on n.parentid=c.id

-查詢有商品的分類資訊及上級分類資訊實現步驟：1.通過子查詢查到有商品的分類id 2.把當前分類和上級分類通過內連線建立關係 3.然後把分類的id作為篩選條件 -簡單寫法

select c.*,m.name 上級分類 from titemcategory c join titemcategory m on c.parentid=m.id where c.id in (SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL);

一對多關聯

部門員工分類商品使用者地址 userid -一對多的表中在多的表中新增關係欄位

案例1：查詢每種分類下所對應的所有商品

select *
from t_item_category c left join t_item i
on c.id=i.category_id

案例2：查詢部門平均工資大於所有員工的平均工資的所有部門下的所有員工

SELECT * FROM emp 
WHERE deptno IN (SELECT deptno FROM emp
WHERE deptno IS NOT NULL
GROUP BY deptno
HAVING AVG(sal)>(SELECT AVG(sal) FROM emp))

案例3：上題結果上再多顯示出部門的名字

 select n.*,d.dname from(SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE deptno IS NOT NULL GROUP BY deptno HAVING AVG(sal)>(SELECT AVG(sal) FROM emp)))n join dept d on n.deptno=d.deptno

多對多關聯 -多對多需要單獨通過新建的關係表儲存資料

create table teacher(id int primary key auto_increment,name varchar(20));

insert into teacher values(null,`唐僧`),(null,`趙本山`),(null,`劉老師`); create table student(id int primary key autoincrement,name varchar(10)); insert into student values(null,`八戒`),(null,`悟空`),(null,`宋小寶`),(null,`小瀋陽`),(null,`小明`),(null,`小紅`); create table ts(tid int,sid int); insert into t_s values(1,1),(1,2),(2,3),(2,4),(3,5),(3,6),(1,5),(2,5); 案例1：查詢每個老師對應的所有學生資訊 1. 子查詢 select name from student where id in (select sid from ts where tid in(select id from teacher)) 2. 內連線 SELECT n.name tname,s.name FROM (SELECT * FROM teacher t JOIN ts ts ON t.id=ts.tid)n JOIN student s ON n.sid=s.id

案例2：查詢小明對應的所有老師

SELECT n.name tname,s.name FROM (SELECT * FROM teacher t JOIN t_s ts ON t.id=ts.tid)n JOIN student s ON n.sid=s.id where s.name=`小明`

案例3：劉老師對應的所有學生資訊

SELECT n.name tname,s.name FROM (SELECT * FROM teacher t JOIN t_s ts ON t.id=ts.tid)n JOIN student s ON n.sid=s.id where n.name=`劉老師`;

檢視

檢視概述

-資料庫中存在多種物件，表和檢視都是資料庫中的物件 **表和檢視不能重名**
-檢視是張虛擬的表，是通過sql的查詢語句查詢的結果集的表儲存成了一個檢視 
-檢視中的資料 會隨著真實表中的資料改變而改變
-檢視實際上就是代表了一部分sql語句

-建立檢視

-格式：create view 檢視名 as 子查詢；

create view v_emp_10 as (select * from emp where deptno=10);


案例：建立emp表的部門是20 工資小於3000的 檢視
    只儲存名字 工資 部門id

create view v_emp_20 as (select ename,sal,deptno from emp
    where deptno=20 and sal<3000);


案例：建立emp表 每個部門的工資平均值 的檢視

create view v_emp_dept_avg as (select deptno,avg(sal) from emp group by deptno)

-如果建立檢視的時候使用了別名，那檢視以後的操作只認識別名

-檢視的使用方式和 table 一樣 -格式：select * from 檢視名

 select * from v_emp_10;

修改檢視 -格式：create or replace view 原檢視名 as 新的子查詢

create or replace view v_emp_10 as
(select * from emp where deptno=10 and sal<3000);

檢視的分類 -檢視分為：簡單檢視和複雜檢視 -簡單檢視：在建立檢視的子查詢中不包含：關聯查詢、去重、函式、分組的檢視稱為簡單檢視 -複雜檢視：和簡單檢視相反
對檢視進行dml操作，只針對簡單檢視可以使用因為複雜查詢通常情況只是為了瀏覽資料的，不需要也不能進行增刪改的操作

檢視的欄位遵循原表欄位的約束 – 檢視的資料汙染如果往檢視中插入一條在檢視中不現實但是在原表中顯示的資料，稱之為資料汙染，不建議這樣操作 只有insert命令才會出現資料汙染，因為update和delete命令只能操作檢視中有的資料

with check option create view vemp10 as select * from emp where deptno=10 with check option;
檢視的作用
1. 重用子查詢提高開發效率
2. 限制資料的訪問比如：可以建立一個檢視把原表的敏感資訊過濾掉即可
```
create view v_emp as select empno,ename,deptno,comm,job from emp
```
工作中對檢視一般只進行DQL,不使用DML

刪除檢視

drop view vemp10;

-案例：建立一個複雜檢視：顯示部門的平均工資，最高工資，最低工資，工資總和，部門員工人數；

create view v_emp_deptinfo as
select deptno,avg(sal),max(sal),min(sal),sum(sal),count(*) 
from emp 
group by deptno;

索引原理

索引概述

索引是用來提高查詢速度的技術
如果不使用索引mysql會從第一條資料依次往後查詢，如果資料量很大，非常耗時
新增索引可以提交查詢效能，但是如果資料量很小，新增索引可能會降低效率
索引的生成是資料庫內部生成，如果查詢資料時發現某個欄位已經新增過索引會自動使用
複合索引

建立索引時如果設定多個索引欄位則稱為複合索引

建立表的時候直接新增索引

create table t11 (id int,name varchar(10), age int, index index_name(name(10)));

更改表的索引

alter table t11 add index index_age(age);

刪除索引
```
drop index index_age on t11;
```

索引總結

1. 索引不是越多越好 
2. 資料量小的時候不需要用到索引
3. 經常出現在 where、order by、 distinct的欄位新增索引，效果會更好
4. 不要在經常做增刪改的表中新增索引

MySQL 約束

唯一約束(UNIQUE)

如果資料庫中的資料需要保證唯一性則新增此約束
CREATE TABLE T1( ID INT, AGE INT UNIQUE, NAME VARCHAR(20));

非空約束(NOT NULL)

如果某個欄位的值不能為null 需要使用此約束

create table t2(id int,age int not null,name varchar(10));
-以下兩種都不可以
insert into t2 (id,name)values(2,`a`);
insert into t2 values(3,null,`b`);

預設約束(DEFAULT)

如果需要讓某個欄位插入資料時有預設值時使用此約束
create table t3 (id int,age int default 0,name varchar(10));

主鍵約束(PRIMARY KEY)

1. 非空、唯一
2.如何新增主鍵
-建立表時新增主鍵
    create table t4(id int primary key,age int );
    create table t5(id int,age int,primary key(id));
-建立表之後新增主鍵
    alter table t6 add primary key(id);
    alter table t6 modify id int primary key
-刪除主鍵約束
    alter table t3 drop primary key;
-自增約束 auto_increment
1.當欄位賦值為null的時候，欄位自動增長 
2.如果刪除了某一條資料 自增的數值不回減少
3.如果插入資料時指定了一個特別大的數，下次自增則從這個數基礎上+1

外來鍵約束(FOREIGN KEY)

工作中，除非特殊情況，一般不使用外來鍵，使用程式碼通過邏輯進行限制
外來鍵約束是保證一個或兩個表之間資料的一致性和完整性的
表的外來鍵通常使用的是另一張表的主鍵
外來鍵可以重複、可以是null、但不可以是另一張表不存在的資料
使用外來鍵約束的條件 -必須保證兩張表使用相同的儲存引擎 -儲存引擎必須是innodb，myisam不支援外來鍵約束 -外來鍵和關聯欄位必須有相似的資料型別，數字長度必須相同 -外來鍵約束對應的欄位必須建立索引，如果不存在索引，mysql會自動建立索引

建立外來鍵約束

1. mysql支援外來鍵的列級語法，但是沒有效果，是為了保證和sql的規範的一致性
2. 表級約束的新增方式
-格式：CONSTRAINT 約束名 FOREIGN KEY(當前表新增約束的欄位名) REFERENCES 關聯表表名(關聯表的欄位名)
create table classes(id int primary key auto_increment,name varchar(20));
insert into classes values(null,`一班`),(null,`二班`);
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,         
name VARCHAR(20),                                
class_id INT,
CONSTRAINT fk_class_id FOREIGN KEY(class_id) REFERENCES classes(id)
);
1.建立班級（classes）表 id name
2.建立學生表（student）id name classid
給classid新增外來鍵約束 
3.先往班級表中插入資料 然後往學生表插入資料，

測試插入錯資料和刪除被關聯的班級資料

CHECK約束

mysql語法支援，但是沒有效果
create table t7(id int,name varchar(10),
age int,check(age>20));

什麼是事務

事務是一組原子性的 SQL 查詢, 或者說是一個獨立的工作單元. 在事務內的語句, 要麼全部執行成功, 要麼全部執行失敗

事務的 ACID 性質

**必須背下以下4點 面試常考**
-原子性：最小的單元，不可分割
-一致性： 保證sql執行的一致 要麼都成功，要麼都失敗，保證執行前後資料一致
-隔離性：多個事務併發時，互不影響
-永續性：commit提交之後，資料儲存到資料庫中

MySQL事務

1.檢視事務是否是自動提交
    show variables like `%autocommit%`;
2.關閉自動提交
    set autocommit=0;

begin 開始事務
commit 提交事務
rollback 回滾事務

事務案例

轉賬案例：

1.建立user表
    create table user(id int,name varchar(20), money int);
    insert into user values(1,`蒼老師`,2000)，(2,`劉老師`,100);
2.轉賬sql
    update user set money=money+500 where id=2;
    update user set money=money-500 where id=1;

課程回顧：

1.什麼是檢視實際上檢視就是一段sql語句

2.檢視中資料實際上都是原表中的資料，如果修改檢視中的資料原表資料跟著更改

3.避免出現資料汙染可以通過新增 with check option 避免出現資料汙染

4.檢視通常只進行查詢操作

5.檢視作用：子查詢複用、限制資料的訪問

6.索引index 需要掌握建立命令和刪除命令

7.約束：unique、not null、primary key、default foreign key，check

8.事務背下來四大特性：原子性、一致性、隔離性、永續性

day01:

資料庫相關的sql：show databases create database db1 character set utf8 show create database db1 drop database db1;

表相關： create table show tables; show create table t1; desc t1; rename table t1 to t2; alter table t1 change age age2 int; alter table t1 modify age long first/after id; add age int; drop age; drop table t1;

insert update delete select

day02:

1.主鍵自增 not null 註釋：comment

2.去重 distinct

3.事務

4.sql分類 ddl 資料定義語言 create alter drop truncate:刪除表內所有資料，內部實現原理：先刪除表再建立一個新表執行效率高 dml 資料操作語言 insert update delete select 支援事務 dql 資料查詢語言：select tcl 事務控制語言：begin commit rollback dcl 資料控制語言：控制許可權

5.資料型別整數： int bigint 浮點數： double decimal（m,d）

6.字串： char varchar text

7.日期：date time datetime timestamp

day03:

聚合函式：sum count avg min max

字串：charlength instr(x,y) locate(x,y) insert(str,begin,length,newStr) lower upper left right substring(str,begin,length) trim() replace(str,old,new); repeat(str,2); reverse

日期相關：now date(now()) time(now()) extract(year from now()) month day time hour second minute dateformat(now(),`%Y-%m-%d %H-%i-%s`) strtodate(“,“)

數學：+ - * / % mod(2,3)
數學函式： floor  round() rand()

練習

1.案例：建立一張表customer2，id number(4),
name varchar2(50)，password varchar2(50)
,age number(3),address varchar2(50),修改
customer2表的時候設定主鍵約束
pk_id_name_cus2修飾id和name列。




2.案例：建立一張book3表,id number(4),
name varchar2(50),author varchar2(50),
pub varchar2(50),numinput number(10)。
修改book3的時候，設定主鍵約束
pk_id_name_b3修飾id和name列，設定唯一約束uq_author_pub_b3修飾author和pub列



3.案例:刪除temp中的唯一約束uk_name_pwd

4.案例：在book表中author和pub列上新增索引index_author和index_pub


5.案例：刪除book中在pub和author上的索引


6.案例：建立一個檢視emp_view1，查詢emp表中所有的資料，查詢語句作為檢視emp_view1


7.案例：建立一個檢視dept_view,查詢dept表中所有的資料，查詢語句作為檢視dept_view


8.案例：建立一個檢視emp_view2,查詢emp表中所有員工的編號，姓名，職位，工資，上級領導的編號以及工資的等級，該等級的最低工資和最高工資,查詢語句作為emp_view2


9.案例：查詢emp表中10,20號部門員工的編號，姓名，職位，工資，所屬部門的編號,使用查詢語句來修改檢視emp_view1


10.案例：刪除檢視emp_view1,emp_view2

MySql學習筆記
2020-06-19
MySql筆記
Java基礎-學習筆記06
2024-07-22
Java筆記
【記錄】MySQL 學習筆記
2019-04-21
MySql筆記
[記錄] MySQL 學習筆記
2019-04-21
MySql筆記
MySQL學習筆記2
2020-12-20
MySql筆記
MySQL學習筆記：鎖
2020-12-11
MySql筆記
學習Mysql筆記（一）
2017-08-24
MySql筆記
mysql學習筆記-1
2016-06-26
MySql筆記
mysql學習筆記-2
2016-08-18
MySql筆記
MySQL Events學習筆記
2015-08-27
MySql筆記
mysql學習筆記3
2024-10-17
MySql筆記
2008-06-06shell學習筆記
2010-04-27
筆記
mysql8.0學習筆記
2019-04-12
MySql筆記
MySQL學習筆記之一
2019-01-15
MySql筆記
MySQL學習筆記：索引失效
2020-09-22
MySql筆記索引
MySql 學習筆記二：索引
2016-10-05
MySql筆記索引
MySQL學習筆記--基本操作
2016-10-09
MySql筆記
mysql優化學習筆記
2016-06-21
MySql優化筆記
2008-06-06shell學習筆記 2
2010-04-27
筆記
iOS學習筆記06 手勢識別
2018-06-02
iOS筆記
HTML5學習筆記 - 第06天
2017-01-16
HTML筆記
Android學習筆記06——handler的使用
2012-02-14
Android筆記
MySQL學習筆記【基礎篇】
2020-08-17
MySql筆記
一千行 MySQL 學習筆記
2019-03-18
MySql筆記
Mysql學習筆記（安裝篇）
2018-08-25
MySql筆記
一千行MySQL學習筆記
2018-08-15
MySql筆記
MySQL資料庫學習筆記
2020-12-10
MySql資料庫筆記
MySQL高階學習筆記(二)
2020-11-13
MySql筆記
MySQL學習筆記---入門使用
2020-10-24
MySql筆記
Mysql 優化(學習筆記二十)
2018-05-22
MySql優化筆記
MySQL與MariaDB學習筆記
2017-10-25
MySql筆記
MYSQL學習筆記14: 函式
2024-03-09
MySql筆記函式
git status 命令總結 —— Git 學習筆記 06
2020-04-04
Git筆記
資料庫mysql學習筆記記錄
2021-09-09
資料庫MySql筆記
MySQL定時器EVENT學習筆記
2021-09-09
MySql定時器筆記
MySQL：Innodb恢復的學習筆記
2018-08-27
MySql筆記
MySQL優化學習筆記之索引
2018-10-22
MySql優化筆記索引
MySQL優化學習筆記之explain
2018-10-25
MySql優化筆記AI