1、數據庫語言的分類

DDL：數據庫定義語言 data Definition language

用于創(chuàng)建、修改、和刪除數據庫內的數據結構，如：

1：創(chuàng)建和刪除數據庫(CREATE DATABASE || DROP DATABASE)；

2：創(chuàng)建、修改、重命名、刪除表(CREATE TABLE || ALTER TABLE|| RENAME TABLE||DROP TABLE，TRUNCATE TABLE)；

3：創(chuàng)建和刪除索引(CREATEINDEX || DROP INDEX)

DML：數據操控語言 data Manipulation language

修改數據庫中的數據，包括插入(INSERT)、更新(UPDATE)和刪除(DELETE)

DCL：數據控制語言 （管理用戶權限）data Control language

用于對數據庫的訪問，主要包括創(chuàng)建用戶、給用戶授權、對用戶撤銷授權、查詢用戶授權和刪除用戶等

，如：1.創(chuàng)建用戶（create user）2：給用戶授予訪問權限（GRANT）;3：取消用戶訪問權限（REMOKE），4刪除用戶（drop user）。

DQL：數據查詢語言 data Query language

從數據庫中的一個或多個表中查詢數據(SELECT)

下面一一來介紹這四種類型的語言

2、DDL：數據庫定義語言 data Definition language

2.1、創(chuàng)建數據庫，并且以utf8的字符集創(chuàng)建

-- 如果不存在，則創(chuàng)建數據庫,并且以utf的字符集創(chuàng)建
CREATE DATABASE IF NOT EXISTS westos DEFAULT CHARACTER SET = 'utf8'

2.2、如果存在，刪除數據庫

-- 如果存在，則刪除數據庫
DROP DATABASE IF EXISTS westos

2.3、使用數據庫

-- 使用數據庫
USE westos

2.4、查看所有的數據庫

-- 查看所有的數據庫
SHOW DATABASES

2.5、查看所有的數據表

-- 查看所有的數據表
SHOW TABLES

2.6、創(chuàng)建表

-- 創(chuàng)建學生表,如果不存在
CREATE TABLE IF NOT EXISTS student (
	`id` INT NOT NULL AUTO_INCREMENT COMMENT '學生表id',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密碼',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性別',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET = 'utf8'

2.7、查看創(chuàng)建數據庫和數據表的語句

-- 查看創(chuàng)建數據庫的語句
SHOW CREATE DATABASE westos
-- 查看創(chuàng)建數據表的語句
SHOW CREATE TABLE student

2.8、查看表的結構

-- 查看表的具體結構
DESC student

3、DQL：數據查詢語言 data Query language

3.1、修改表名

-- 修改表名
ALTER TABLE student RENAME AS teacher

3.2、增加表的字段

-- 增加表的字段
ALTER TABLE teacher ADD age INT 

3.3、修改表的字段

-- 修改表的字段（重命名，修改約束）
ALTER TABLE teacher MODIFY age VARCHAR(11)	-- 修改約束，不能改字段名
ALTER TABLE teacher CHANGE age is_del INT 	-- 字段重命名

3.4、刪除表的字段

-- 刪除表的字段
ALTER TABLE teacher DROP is_del

3.5、刪除表

 -- 刪除表
 DROP TABLE IF EXISTS student

3.6、插入語句

 -- 插入語句（添加）
 INSERT INTO teacher (`name`) VALUES ('xgp')

3.7、修改語句

-- 修改語句
UPDATE teacher SET `name`='xgp',sex='女' WHERE id=1

3.8、設置數據庫的時間

-- 設置數據庫的時間
UPDATE `teacher` SET `birthday` = CURRENT_TIME WHERE id=1

3.9、刪除指定數據

-- 刪除指定數據
DELETE FROM teacher WHERE id=1

3.10、清空一個數據表

-- 完全清空一個數據庫表，表的結構和索引約束不變,自增會歸零
TRUNCATE TABLE teacher

4、DQL：數據查詢語言 data Query language

4.1、使用別名查詢

-- 查詢（使用別名）
SELECT `name` 姓名,pwd 密碼 FROM teacher 老師表

4.2、拼接字符串

-- 函數 拼接字符串 Concat(a,b)
SELECT CONCAT('姓名：',`name`) 新名字 FROM teacher

4.3、去重

-- 去重
SELECT DISTINCT `name` FROM teacher 

4.4、查看系統(tǒng)版本

-- 查看系統(tǒng)版本
SELECT VERSION()

4.5、計算結果

-- 計算結果
SELECT 100*3-23 計算結果
SELECT pwd+'1' 密碼加1 FROM teacher

4.6、模糊查詢

-- 模糊查詢
-- like結合 %（代表0到任意個字符） _（代表一個字符）
SELECT `name`,pwd FROM teacher
WHERE `name` LIKE '_g_'

-- in
SELECT id,`name`,pwd FROM teacher
WHERE id IN (4,7,10)

4.7、排序查詢

-- 排序：升序 ASC 降序 DESC
-- order by 通過哪個字段排序，怎么排
SELECT * FROM teacher ORDER BY id DESC 
SELECT * FROM teacher ORDER BY id ASC 

4.8、分頁查詢

-- 分頁：
SELECT * FROM teacher ORDER BY id DESC LIMIT 2,10

5、常用函數

5.1、數學函數

-- 常用函數
SELECT ABS(-78) -- 絕對值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT SIGN(-9)	-- 判斷一個數的符號

5.2、字符串函數

-- 字符串函數
SELECT CHAR_LENGTH('哈哈哈') -- 字符串長度
SELECT CONCAT('xgp','aaa')	-- 拼接字符串
SELECT INSERT('asa',1,2,'ss')		-- 替換字符串
SELECT LOWER('AAAAAAAA')	-- 轉小寫
SELECT UPPER('aaaaaaaa')	-- 轉大寫
SELECT INSTR('aaaaa','a')	-- 返回第一次出現子串的索引
SELECT REPLACE('aaaa','a','x')	-- 替換字符串
SELECT SUBSTR('asasasxsssas',4,6)	-- 截取字符串，截取的位置，截取的長度
SELECT REVERSE('aasss') -- 字符串反轉

5.3、時間和日期函數

-- 時間和日期函數（獲取當前日期）
SELECT CURRENT_DATE(); -- 獲取當前日期
SELECT CURDATE();	-- 獲取當前日期
SELECT NOW(); -- 獲取當前的時間
SELECT LOCALTIME(); -- 獲取本地時間
SELECT SYSDATE() -- 獲取系統(tǒng)時間

5.4、系統(tǒng)函數

-- 系統(tǒng)
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5.5、聚合函數

-- 聚合函數
SELECT COUNT(*) FROM teacher
SELECT COUNT(字段) FROM teacher	-- 會忽略null值
SELECT COUNT(1) FROM teacher
SELECT COUNT(主鍵) FROM teacher -- 速度更快

SELECT SUM(pwd) 求和 FROM teacher
SELECT AVG(pwd) 平均 FROM teacher
SELECT MAX(pwd) 最高 FROM teacher
SELECT MIN(pwd) 最低 FROM teacher

6、DCL：數據控制語言 （管理用戶權限）data Control

6.1、創(chuàng)建用戶

-- 創(chuàng)建用戶
CREATE USER xgp123 IDENTIFIED BY '123456'

6.2、修改密碼

-- 修改密碼（修改當用戶密碼）
SET PASSWORD = PASSWORD('111111')
-- 修改密碼（修改自定的用戶的密碼）
SET PASSWORD FOR xgp123 = PASSWORD('123456')

6.3、給用戶重命名

-- 給用戶重命名
RENAME USER xgp123 TO xgp

6.4、用戶授權

-- 用戶授權(授予全部的權限，庫，表)
GRANT ALL PRIVILEGES ON *.* TO xgp

6.5、查看指定用戶權限

-- 查看指定用戶權限
SHOW GRANTS FOR xgp

6.6、查看管理員權限

-- 查看管理員權限
SHOW GRANTS FOR root@localhost

6.7、撤銷權限

-- 撤銷權限(撤銷全部權限)
REVOKE ALL PRIVILEGES ON *_* FROM xgp

7、事務

7.1、開啟/和關閉事務的自動提交，mysql默認是開啟的

-- 事務（測試轉賬）mysql 是默認開啟事務自動提交的
SET autocommit = 0;	-- 先關閉事務提交
SET autocommit = 1;	-- 開啟

7.2、進行手動處理事務

-- 事務開啟
START TRANSACTION -- 標記一個事務的開始

-- 提交
COMMIT

-- 回滾
ROLLBACK

-- 事務結束

-- 了解
SAVEPOINT -- 設置一個事務的保存點
ROLLBACK TO SAVEPOINT 保存點名，會滾到保存點

7.3、通過轉賬案例手動測試事務

-- 編寫轉賬案例
CREATE TABLE account (
id INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET = 'utf8'

INSERT INTO account(`name`,money) VALUES ('張三',2000.00),('李四',10000.00)

-- 模擬轉賬
SET autocommit = 0;	-- 關閉自動提交
START TRANSACTION; -- 開啟事務
UPDATE account SET money = money - 500 WHERE `name` = '張三';
UPDATE account SET money = money + 500 WHERE `name` = '李四';

COMMIT;	-- 提交事務
ROLLBACK; -- 回滾
SET autocommit = 1;	-- 恢復默認值

8、其他技術

8.1、對數據庫進行備份

命令行執(zhí)行導出命令 mysqldump
mysqldump -hlocalhost -uroot -p123456 westos > D:/a.sql

8.2、對數據庫進行導入

-- 導入
source sql文件路徑

8.3、對數據庫進行md5加密

-- 數據庫級別的MD5加密
UPDATE teacher SET pwd = MD5(pwd)
-- 插入時進行加密
INSERT INTO teacher(`name`,pwd) VALUES ('aaa',MD5('123456'))
-- 如何校驗
SELECT * FROM teacher WHERE `name`='aaa' AND pwd = MD5('123456')

9、mysql基礎語法的詳細語法格式文檔

mysql產品的常見命令

1，mysql服務的啟動和停止

方式一：計算機——右鍵管理——服務
方式二：通過管理員身份運行
	net start 服務名（啟動服務）
	net stop 服務名（停止服務）

2，mysql服務的登陸和退出

方式一：通過mysql自帶的客戶端
	只限于root用戶
方式二：通過cmd命令行
	登陸：
		mysql [-h主機名 -P端口號] -u用戶名 -p[密碼]
	退出：
		exit	或者		ctrl+c

3，sql語句的基本使用

1.查看當前所有數據庫
	show databases;
2.打開指定庫
	user 庫名
3.查看當前庫的所有表
	show tables;
4.查看其它庫的所有表
	show tables from 庫名;
5.創(chuàng)建表
	create table 表名 (
		列名 列類型,
		列名 列類型,
		...
	);
6.查看表結構
	desc 表名;
7.查看服務器版本
	方式一：登陸到mysql服務端
		select version();
	方式二：沒有登陸到mysql服務端
		mysql --version		或者 	mysql -V
8.注釋
	單行：#注釋文字
		-- 注釋文字
	多行：/*注釋文字*/

DQL語言的學習

階段一：1，基礎查詢

語法：
	select 查詢列表 from 表名;
查詢列表：
	表中的字段，常量值，表達式，函數
1.查詢表中的單個字段
	select last_name from employees;
2.查詢表中的多個字段
	select last_name,salary,email from employees;
3.查詢表中的所有字段
	select * from employees;

2，其他基礎查詢

1.查詢常量值
	select 100;
	select 'join';
2.查詢表達式
	select 100*98;
	select 100%98;
3.查詢函數
	select version();

3，為字段起別名

1.方式一：（使用as）
	select 100%98 as 結果;
	select last_name as 姓 from employees;
2.方式二：（使用空格）
	select last_name 姓 from employees;

4，去重(distinct)

1.案例：查詢員工表的所有部門的編號
	select distinct
		department_id
	from
		employees;

5，+號的作用

1.案例：查詢員工名和姓連接成一個字段，并顯示為 姓名
	錯誤做法：
		select 
			last_name + first_name as 姓名
		from
			employees;
	正確做法：（使用concat()函數）
		select 
			concat(last_name,first_name) as 姓名
		from 
			employees;

6，ifnull()函數的使用

格式：
	ifnull(判斷是否為空的參數,若為空返回的值)
舉例：
	select ifnull(commission_pct,0) as 獎金率;

案例：查詢員工工資，要求
	部門號=30，顯示工資為1.1倍
	部門號=40，顯示工資為1.2倍
	部門號=50，顯示工資為1.3倍
	其他部門，顯示為原工資

	SELECT 
		salary 原始工資,department_id ,
	CASE 
		department_id = 30 
	WHEN 30 THEN salary*1.1
	WHEN 40 THEN salary*1.2
	WHEN 50 THEN salary*1.3
	ELSE 
		salary
	END 
		AS 新工資
	FROM 
		employees;
3.case()函數的使用二
	case 
	when 條件1 then 要顯示的值1或語句1
	when 條件2 then	要顯示的值2或語句2
	......
	else 要顯示的值n或語句n
	end

案例：查詢員工的工資的情況
	如果工資>20000,A
	如果工資>15000,B
	如果工資>10000,C
	否則 D

	SELECT salary,
	CASE 
	WHEN salary > 20000 THEN 'A'
	WHEN salary > 15000 THEN 'B'
	WHEN salary > 10000 THEN 'C'
	ELSE 'D'
	END AS 工資級別
	FROM employees;

6，聚合函數

1.sum(字段) 求和 avg() 平均值 min() 最小值 max() 最大值 count() 計數
	select sum(salary) from employees;
2.特點：
	1.sum(),avg() 支持數值型
		min(),max(),count() 支持任何類型
	2.上述聚合函數都排除了null值
3.和distinct搭配使用
	select count(distinct salary),count(salary) from employees;
4.count() 函數的詳細介紹
	select count(*) from employees;
	select count(1) from employees;
	效率：
		MYISAM存儲引擎下，count(*) 的效率高
		INNODB存儲引擎下，count(*)和count(1)的效率差不多，比count(字段)要高一些
5.和聚合函數使用，一般都不會帶字段

舉例：
	SELECT 
		NAME,boyName 
	FROM 
		boys,beauty
	WHERE 
		beauty.boyfriend_id = boys.id;

分類：

按年代分類：
	sql92標準：僅僅只支持內連接
	sql99標準【推薦】：支持內連接+外連接（左外和右外） + 交叉連接
按功能分類：
	內連接：
		等值連接
		非等值連接
		自連接
	外連接：
		左外連接
		右外連接
		全外連接
	交叉連接

2，sql92標準

1，等值連接

1.案例1：查詢員工名和對應的部門名
	SELECT 
		last_name,department_name
	FROM
		employees,departments
	WHERE
		employees.department_id = departments.department_id
2.案例2：查詢每個工種的工種名和員工的個數，并且按員工個數升序
	SELECT 
		job_title,COUNT(*)
	FROM 
		employees e,jobs j
	WHERE 
		e.job_id = j.job_id
	GROUP BY
		job_title
	ORDER BY
		COUNT(*) DESC;
3.案例3：查詢員工名、部門名和所在的城市（三表查詢）
	SELECT
		last_name,department_name,city
	FROM 
		employees e,departments d,locations l
	WHERE
		e.department_id = d.department_id
	AND
		d.location_id = l.location_id
	AND
		city LIKE 's%'
	ORDER BY
		department_name DESC;

2，非等值連接
1.案例1：查詢員工的工資和工資級別

	SELECT
		salary,grade_level
	FROM
		employees e,job_grades g
	WHERE
		salary BETWEEN g.lowest_sal AND g.highest_sal;

3，自連接
1.案例：查詢 員工名和上級的名稱

	SELECT 
		e.employee_id,e.last_name,m.employee_id,m.last_name,m.department_id
	FROM
		employees e,employees m
	WHERE 
		e.manager_id = m.employee_id;

3，sql99標準

1.語法：
	select
		查詢列表
	from 
		表1 別名 連接類型
	join
		表2 別名
	on
		連接條件
	[where
		篩選條件]
	[group by
		分組]
	[having
		篩選條件]
	[order by
		排序列表]

2.連接類型
	內連接(*)：inner
	外連接：
		左外(*)：left [outer]
		右外(*)：right [outer]
		全外：full [outer]
	交叉連接：cross join

1，內連接

1.語法：
	select
		查詢列表
	from
		表1 別名
	inner join
		表2 別名
	on 
		連接條件

1，等值連接

1.案例1：查詢哪個部門的員工個數>3的部門名和員工個數，并按照個數降序（添加排序）

	1.查詢每個部門的員工個數
		SELECT 
			COUNT(*),department_name
		FROM 
			employees e
		INNER JOIN
			departments d
		ON
			e.department_id = d.department_id
		GROUP BY 
			department_name
	2.在1的結果上篩選員工個數>3的記錄，并排序
		SELECT 
			COUNT(*),department_name
		FROM 
			employees e
		INNER JOIN
			departments d
		ON
			e.department_id = d.department_id
		GROUP BY 
			department_id
		HAVING 
			COUNT(*) > 3
		ORDER BY
			COUNT(*) DESC;

2，非等值連接

2.案例1：查詢員工的工資級別

	SELECT 
		salary,grade_level
	FROM 
		employees e
	JOIN 
		job_grades g
	ON 
		e.salary 
	BETWEEN 
		g.lowest_sal 
	AND 
		g.highest_sal;