MySQL資料庫技術

六、DCL(資料控制語言)操作管理用戶、授權

DBA資料管理員

1.管理用戶

1）添加用戶

? 語法：create user 用戶名@‘主機名IP地址’ identified by’密碼’;

-- 創建用戶，用戶只能在指定IP 地址上登錄
create user magic@'192.168.1.113' identified by'';
--用戶可以在任意IP地址下登錄
create user magic@'%' identified by'';

2）查詢用戶，mysql下的資料表

select * from user;

select host,user from user;

3）洗掉用戶

drop user 用戶名@‘主機名IP地址’

4）修改用戶密碼

5.7以后密碼password欄位改為authentication_string

①update user set password = password(‘新密碼’) where user= ‘用戶名’;

②set password for用戶名@‘主機名IP地址’ = password(‘新密碼’)

5）重繪權限指令

flush privileges;

6）忘記密碼

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-sNxks4H1-1608272878707)(C:\Users\24582\AppData\Roaming\Typora\typora-user-images\image-20201215150408764.png)]

2.授權管理

此時，用戶可以連接mysql資料庫，但是只能使用很少的權限，因此需要其他用戶給其權限

1）查詢權限

show grants for 用戶名@‘主機名IP地址’

2）授予權限

grant 權限串列 on 資料庫名.表名 to 用戶名@‘主機名IP地址’

–賦多個權限給用戶

eg：grant select,update,insert create on mydb1.student to magic@’%’;

–給magic用戶賦予所有權限，在任意資料庫任意表上進行各種操作，權限相當于root用戶

grant all on*.* to’用戶名’@‘主機名‘；

3）撤銷權限

revoke 權限串列 on 資料庫名.表名 from 用戶名@‘主機名‘；

–撤銷用戶所有的權限

revoke all on 資料庫名.* from 用戶名@‘主機名‘；

七、圖形化管理資料庫的工具：SQLYog客戶端管理工具

1.概念：

SQLyog是一個快速而簡潔餓的圖形化管理MYSQL資料庫的工具，它能夠在任何地點有效地管理你的資料庫，由業界著名的Webyog公司出品

2.安裝：

自行百度安裝即可

八、DQL查詢陳述句

1.簡單查詢：

查詢不會對資料庫的資料進行修改，沒有影響，只是一種顯示資料的方式

語法：

-- select 欄位串列 from 表名串列;
-- select 欄位串列 from 表名串列 where 條件;
desc student;-- 查詢student表結構

select sname,age from student; -- 查詢指定欄位，名字年齡

select * from student; -- 查詢表的所有列

-- 查詢中出現重復，則洗掉重復 distinct
select distinct address from student; -- 去掉重復資訊

-- 起別名 as 也可以省略，表上也可以使用
select name as 姓名,age AS 年齡 from student;

-- 計算列 加減乘除
-- eg：所有年齡加5
select age+5 from student;

2.條件查詢：

> < >= <= = != <>
and 或 &&:  SQL中建議使用前者,
or  或 ||
not 或 !

between... and...:在一定范圍內
in(多個值): 表示多個資料,使用逗號分隔
like: 模糊查詢
		點位符:_:單個任意字符
			  %:多個任意字符
is null:空值

-- 查詢年齡大于19的
SELECT * FROM student WHERE age>=19;

-- 查詢年齡大于18，小于20的
SELECT * FROM student WHERE age>18 AND age<20;

-- 查詢年齡一定范圍內
SELECT * FROM student WHERE age  BETWEEN 18 AND 20;

-- 滿足其中一個就可以
SELECT * FROM student WHERE age IN(18,19);

-- 查詢姓名中帶o的同學
SELECT * FROM student WHERE sname LIKE '%o%';

3.排序查詢：

語法：
	order by 排序欄位1 排序方式1... -- 多個欄位進行排序
	asc：升序
	desc：降序
	
-- 查詢記錄時，按照年齡升序輸出
SELECT * FROM student ORDER BY age ASC;

-- 查詢記錄時，按照年齡降序輸出
SELECT * FROM student ORDER BY age DESC;

-- 按照年齡升序排序，若年齡相同，按照id降序排列
SELECT * FROM student ORDER BY age ASC,id DESC;

4.聚合函式：

將一列資料作為一個整體，進行縱向計算

count():計算個數
max():計算最大值
min():計算最小值
sum():計算和
avg():計算平均分


-- 查詢學生總數，null不會被統計
SELECT COUNT(id) FROM student;

SELECT COUNT(*) FROM student;-- 表示只有一列資料有一個不為null值，就算一行記錄（常用*）

-- 查詢計算年齡最大值、最小值
SELECT MAX(age) FROM student;
SELECT MIN(age) FROM student;

-- 查詢年齡總和
SELECT SUM(age) FROM student;

--查詢年齡的平均年齡
SELECT AVG(age) FROM student;

5.分組查詢

-- 按照性別查詢，按照男女分別多少人？
語法：
group by -- 用于對查詢的結果進行分組
having -- 子句，用于限制分組顯示結果，對結果再次篩選
SELECT sex,COUNT(*) FROM student GROUP BY sex;

-- 按照性別查詢，按照男女分別多少人，且平均年齡？
SELECT sex,COUNT(*),AVG(age) FROM student GROUP BY sex;

-- 按照性別查詢，按照男女分別多少人，且平均年齡，年齡低于18的不參加分組？
SELECT sex,COUNT(*),AVG(age) FROM student WHERE age>18 GROUP BY sex;

--where和having區別？
	①where在分組之前進行限定，如果不滿足條件，則不參與分組
	having在分組之后進行限定，如果不滿足條件，不會被查詢出來
	②where后不可以跟聚合函式
	having后可以

九、資料表的約束

1.概念：

是對表中的資料進行限定，從而保證資料的正確性、有效性、完整性

一個表如果添加了約束條件則不正確資料不能添加進去

2.分類：

主鍵約束：primary key

非空約束：not null

唯一約束：unique

外鍵約束：foreign key

3.主鍵約束：primary key

-- 用來唯一標識資料庫中的每一條記錄，當定義了主鍵約束后，該列不但不能重復而且不能為null

--創建表時，添加主鍵約束
CREATE TABLE stu(
	id INT PRIMARY KEY,-- 定義列時指定主鍵
	sname VARCHAR(50)
)

CREATE TABLE stu(
	id INT,
	sname VARCHAR(50),
    PRIMARY KEY(id) --定義完所有列后添加主鍵
)
-- 洗掉主鍵的方法
ALTER TABLE stu DROP PRIMARY KEY;

--創建完成后，添加主鍵
ALTER TABLE stu ADD PRIMARY KEY(id);

-- 注意：
主鍵的含義是非空且唯一
一張表只能有一個欄位為主鍵
主鍵就是表中記錄的唯一標識

--插入重復的值
INSERT INTO stu(id,sname) VALUES(1,'tom');
INSERT INTO stu(id,sname) VALUES(1,'tom');

錯誤代碼： 1062
Duplicate entry '1' for key 'PRIMARY'

--插入空值
INSERT INTO stu(id,sname) VALUES(NULL,'tom');

錯誤代碼： 1048
Column 'id' cannot be null

自動增長

關鍵字：auto_increment

在創建表時，添加主鍵約束并且完成主鍵的增長

CREATE TABLE stu3(
	id INT PRIMARY KEY AUTO_INCREMENT,-- 給id添加主鍵約束，設定為自動增長
	sname VARCHAR(50)
)

-- 洗掉自動增長
ALTER TABLE stu3 MODIFY id INT;

-- 創建表之后，再添加自動增長
ALTER TABLE stu3 MODIFY id INT AUTO_INCREMENT;

-- 使用
INSERT INTO stu3(sname) VALUES ('tom');
INSERT INTO stu3(id,sname) VALUES(NULL,'rose');

-- 修改主鍵自增從200開始
ALTER TABLE stu3  AUTO_INCREMENT=200;

delete：洗掉洗掉所有的記錄之后，自增長沒有影響，從200--201
truncate：洗掉之后，自增長從0開始

4.非空約束：not null

概念

資料表的某一列不能為null，如果在列上定義了not null，插入資料時，必須為該列提供資料，為空就沒意義了

格式

-- 創建表時添加約束
create table stu(
	id int,
    sname varchar(20) not null -- 創建了非空約束
)

-- 創建表結束后，添加非空約束
alter table 表名 modify sname varchar(20) not null;

-- 洗掉欄位的非空約束
alter table 表名 modify sname varchar(20);

-- 案例
INSERT INTO stu3 VALUES(1,NULL);
-- 錯誤代碼： 1048
Column 'sname' cannot be null

5.默認值：為某欄位設定默認值

語法

create table stu(
	id int primary key,
    sname varchar(20) not null,
    sex varchar(10) default 'man' -- 默認為男
)

INSERT INTO stu2(id,sname,sex)VALUES(1,'zjd','woman');
INSERT INTO stu2(id,sname,sex)VALUES(2,'txw',NULL);
INSERT INTO stu2(id,sname,sex)VALUES(3,'txb',DEFAULT);

6.唯一約束：unique

概念

資料表中某一列不可出現重復的值，必須保證唯一性，當某一列資料定義了唯一約束之后，不能重復

語法

-- 創建表示添加唯一約束
CREATE TABLE stu(
	id INT PRIMARY KEY,
	sname VARCHAR(20) UNIQUE
)

-- 創建之后添加唯一約束
alter table 表名 modify 欄位名 欄位型別 unique
ALTER TABLE stu MODIFY sname VARCHAR(20) UNIQUE;

-- 洗掉欄位的唯一約束
ALTER TABLE stu DROP INDEX sname;

--案例
INSERT INTO stu(id,sname) VALUES(1,'tom');
INSERT INTO stu(id,sname) VALUES(2,'tom');
-- 錯誤代碼： 1062
Duplicate entry 'tom' for key 'sname'

-- 注意：
當插入的資料都為null值，則可以重復
INSERT INTO stu(id,sname)VALUES(3,NULL);
INSERT INTO stu(id,sname) VALUES(4,NULL);
mysql資料庫中，唯一約束限定的列的值可以有多個null值

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-VPG40bjc-1608272878709)(C:\Users\24582\AppData\Roaming\Typora\typora-user-images\image-20201217145207964.png)]

擴展

如果一個欄位設定了唯一約束，該欄位與主鍵有什么區別？

主鍵：一張表最多只能有一個主鍵 不能為null 加自增長

唯一：一張表可以有多個唯一約束 可以有多個null 不可以加自增長

7.外鍵約束：foreign key

前言

單表存盤資料時的缺點：

①資料重復、出現冗余的資料

②后期出現增刪改時，存在問題

解決方法：

拆分表、拆成多個表

CREATE TABLE class(-- 主表
	cno INT PRIMARY KEY AUTO_INCREMENT, -- 班級編號
	cname VARCHAR(50), -- 班級名稱
	address INT -- 班級對應教室號
)
CREATE TABLE student(-- 從表
	id INT PRIMARY KEY AUTO_INCREMENT, -- 學生編號
	sname VARCHAR(50), -- 姓名
	age INT, -- 年齡
	cno INT -- 班級
)
-- 添加兩個班級
INSERT INTO class VALUES(1,'classone',101),(2,'classtwo',102);
-- 添加學生
INSERT INTO student(id,sname,age,cno) VALUES(1801,'tom',18,1);
INSERT INTO student(id,sname,age,cno) VALUES(1802,'Jack',19,1);
INSERT INTO student(id,sname,age,cno) VALUES(1803,'rose',18,1);

INSERT INTO student(id,sname,age,cno) VALUES(1804,'bib',19,2);
INSERT INTO student(id,sname,age,cno) VALUES(1805,'lily',18,2);
INSERT INTO student(id,sname,age,cno) VALUES(1806,'cir',19,2);

外鍵約束概念

用于定義主表和從表之間的關系，外鍵約束要定義在從表上，主表則必須具有主鍵約束，從而保證資料的正確性和有效性

主表：一方，用來約束別人的表

從表：多方，被別人約束的表

語法

create table 表名(
	列名 資料型別,
	……
	外鍵列
	constraint 外鍵名稱 foreign key(外鍵列名稱) references 主表名稱(主表列名稱)
)

CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 學生編號
	sname VARCHAR(50), -- 姓名
	age INT, -- 年齡
	cno INT, -- 班級
	CONSTRAINT student_fk FOREIGN KEY(cno) REFERENCES class(cno)
)

-- 洗掉外鍵
alter table 表名 drop foreign key 外鍵名稱;
ALTER TABLE student DROP FOREIGN KEY student_fk;

-- 創建表之后添加外鍵
alter table 表名  add
constraint 外鍵名稱 foreign key(外鍵列名稱) references 主表名稱(主表列名稱)

ALTER TABLE student  ADD
CONSTRAINT student_fk FOREIGN KEY(cno) REFERENCES class(cno);

--案例：插入不存在的班級報錯
錯誤代碼： 1452
Cannot add or update a child row: a foreign key constraint fails (`mystudy`.`student`, CONSTRAINT `student_fk` FOREIGN KEY (`cno`) REFERENCES `class` (`cno`))

別人約束的表

語法

create table 表名(
	列名 資料型別,
	……
	外鍵列
	constraint 外鍵名稱 foreign key(外鍵列名稱) references 主表名稱(主表列名稱)
)

CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 學生編號
	sname VARCHAR(50), -- 姓名
	age INT, -- 年齡
	cno INT, -- 班級
	CONSTRAINT student_fk FOREIGN KEY(cno) REFERENCES class(cno)
)

-- 洗掉外鍵
alter table 表名 drop foreign key 外鍵名稱;
ALTER TABLE student DROP FOREIGN KEY student_fk;

-- 創建表之后添加外鍵
alter table 表名  add
constraint 外鍵名稱 foreign key(外鍵列名稱) references 主表名稱(主表列名稱)

ALTER TABLE student  ADD
CONSTRAINT student_fk FOREIGN KEY(cno) REFERENCES class(cno);

--案例：插入不存在的班級報錯
錯誤代碼： 1452
Cannot add or update a child row: a foreign key constraint fails (`mystudy`.`student`, CONSTRAINT `student_fk` FOREIGN KEY (`cno`) REFERENCES `class` (`cno`))