SQL語言

使用Navicate圖形化界面工具

官網(wǎng)地址：http://www./download/direct-download?product=navicat_mysql_cs_x64.exe&location=1&support=Y
網(wǎng)盤地址：
https://pan.baidu.com/s/1_z9TbieMX9Kemnyj5WWrBQ

字符集

1. 字符集的由來：

1. 計算機只能識別二進(jìn)制代碼無論是計算機程序還是數(shù)據(jù)，最終都會轉(zhuǎn)換成二進(jìn)制，計算機才能認(rèn)識。
2. 為了計算機不只能做科學(xué)計算，也能處理文字信息。
   人們想出了給每一個文字符號編碼以便于計算識別處理的辦法，這就是計算機字符集的由來。

2. ASSCII

一套文字符號及其編碼,比較規(guī)則 的集合。 20世紀(jì)60年代初。美國標(biāo)準(zhǔn)化組織ANSI發(fā)布了第一個字符集。ASCII
后來又進(jìn)一步變成了國際標(biāo)準(zhǔn)ISO-646

1. 各大字符集:
   自ASCII后。為了處理不同的文字。各大計算機公司，各國，標(biāo)準(zhǔn)化政府，組織先后發(fā)明了幾百種字符集。

1. ISO-8859
2. GB2312-80
3. GBK
4. BIG5

2. unicode

1. 為了統(tǒng)一字符編碼。國際標(biāo)準(zhǔn)化組織ISO的一些成員國于1984年發(fā)起制定了新的國際字符集標(biāo)準(zhǔn)。容納全世界各種語言，文字，和 符號。最后這個標(biāo)準(zhǔn)ISO-10646
2. ISO-10646發(fā)布后，遭到了美國計算機公司的反對。
3. 1988年，Xerox公司提議制定了新的以16位編碼人統(tǒng)一字符集。并聯(lián)合不Apple,IBM,SUN，Microsoft等公司成立了Unicode技術(shù)委員會。專門負(fù)責(zé)收集，整理，和編碼。于1991年推出了Unicode1.0
4. 都是為了字符編碼統(tǒng)一問題，ISO和Unicode協(xié)會推出了連個不同人標(biāo)準(zhǔn)。這顯然是不利的。后來雙方開始談判。1991年10月達(dá)成協(xié)議。ISO將Unicode收編。起了個名BMP

3. UTF-16

1. ISO-10646編碼空間足以容納從古自今使用過的文字和字符。但很多文字字符已經(jīng)很少用了。 超過99%的在用文字字符都編入了BMP.因此，絕大部分情況下。
   Unicode雙字節(jié)方式都能滿足需求。而且比雙字節(jié)編碼方式4字節(jié)原始編碼來說，更節(jié)省內(nèi)存和處理時間 。這也是Unicode流行的原因。
2. 萬一使用了BMP以這后文字怎么辦？ Unicode提出了UTF-16的解決辦法。

4. UTF-8

1. 雖然UTF-16解決了上面問題。但當(dāng)時的計算機和網(wǎng)絡(luò)世界還是ASCII的天下。只能處理單字節(jié)數(shù)據(jù)流。UTF-16離開了Unicode環(huán)境后。在傳輸和處理中，都存在問題。
2. 于是又提出了UTF-8的解決文案，
3. UTF-8按一定的規(guī)則，將一個ISO10646或Unicode轉(zhuǎn)換成1至4個字節(jié)的編碼
4. 其中ASCII轉(zhuǎn)成單字節(jié)編碼。也就嚴(yán)格兼容了ASCII字符集。
5. UTF-8的2，3，4字節(jié)用以轉(zhuǎn)換ISO-10646標(biāo)準(zhǔn)的UCS-4原始碼。

5. 漢字的一些常見字符集

1. GB2312
2. GB13000
3. GBK
4. GB18030

什么是SQL

1. SQL是Structured Quevy Language(結(jié)構(gòu)化查詢語言)的縮寫。
2. SQL是專為數(shù)據(jù)庫而建立的操作命令集，是一種功能齊全的數(shù)據(jù)庫語言。
3. 在使用它時，只需要發(fā)出“做什么”的命令，“怎么做”是不用使用者考慮的。

sql功能分類

1. DDL:數(shù)據(jù)定義語言

用來定義數(shù)據(jù)庫對象：創(chuàng)建庫，表，列等。

2. DML：數(shù)據(jù)操作語言

用來操作數(shù)據(jù)庫表中的記錄

3. DQL：數(shù)據(jù)查詢語言

用來查詢數(shù)據(jù)

4. DCL：數(shù)據(jù)控制語言

用來定義訪問權(quán)限和安全級別

SQL數(shù)據(jù)類型

MySQL中定義數(shù)據(jù)字段的類型對你數(shù)據(jù)庫的優(yōu)化是非常重要的。
MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。

MySQL支持多種類型，大致可以分為三類:

1. 數(shù)值類型

2. 字符串類型

3. 日期和時間類型

常用數(shù)據(jù)類型

1. double：浮點型，例如double(5,2)表示最多5位，其中必須有2位小數(shù)，即最大值為999.99；
2. char：固定長度字符串類型； char(10) 'abc '
3. varchar：可變長度字符串類型；varchar(10) 'abc'
4. text：字符串類型;
5. blob：二進(jìn)制類型；
6. date：日期類型，格式為：yyyy-MM-dd；
7. time：時間類型，格式為：hh:mm:ss
8. datetime:日期時間類型 yyyy-MM-dd hh:mm:ss

在mysql中，字符串類型和日期類型都要用單引號括起來。'Joker' '2020-01-01'

SQL語句

使用Navicate圖形化界面工具
連接數(shù)據(jù)庫：

DDL:數(shù)據(jù)定義語言

1. 創(chuàng)建數(shù)據(jù)庫

create database 數(shù)據(jù)庫名  character set utf8;

2. 創(chuàng)建學(xué)生表

use joker;
create table student( -- 創(chuàng)建數(shù)據(jù)表
Id bigint,
name varchar(20),
age int
);

3. 添加一列

ALTER TABLE 表名 ADD 列名 數(shù)據(jù)類型;

4. 查看表的字段信息

DESC 表名;

5. 修改一個表的字段類型

ALTER TABLE 表名 MODIFY 字段名  數(shù)據(jù)類型;

6. 刪除一列

ALTER TABLE 表名 DROP 字段名;

7. 修改表名

RENAME TABLE 原始表名 TO  要修改的表名; 

8. 查看表的創(chuàng)建細(xì)節(jié)

SHOW CREATE TABLE 表名;

9. 修改表的列名

ALTER TABLE 表名 CHANGE 原始列名 新列名 數(shù)據(jù)類型;

10. 刪除表

DROP TABLE 表名;

1. 修改表的字符集為gbk

ALTER TABLE 表名 CHARACTER SET 字符集名稱;

DML：數(shù)據(jù)操作語言

1. 查詢表中的所有數(shù)據(jù)

SELECT * FROM 表名;

2. 插入操作

INSERT INTO 表名（列名1，列名2 ...）VALUE (列值1，列值2...);

注意事項:

1. 列名與列值的類型、個數(shù)、順序要一一對應(yīng)。
2. 值不要超出列定義的長度。
3. 插入的日期和字符一樣，都使用引號括起來。

批量插入

INSERT INTO 表名（列名1，列名2 ...）VALUES (列值1，列值2...)，(列值1，列值2...);

3. 更新操作

UPDATE 表名 SET 列名1=列值1，列名2=列值2 ... WHERE 列名=值

1. 把所有學(xué)生的年齡改為20
   

update students set age=20

2. 把姓名為小鍵的年齡改為18
   

update students set age=18 where name='小鍵';

3. 把小吳的年齡在原來基礎(chǔ)上加1歲
   

update students set age=age+1 where name='小吳';

4. 修改數(shù)據(jù)庫密碼
   mysql8之前:

use mysql;
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
flush privileges;刷新MySQL的系統(tǒng)權(quán)限相關(guān)表

mysql8:

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密碼';

使用mysqladmin修改密碼:

mysqladmin -u root -p password 123456

4. 刪除操作

1. 刪除表

DELETE FROM 表名 【W(wǎng)HERE 列名=值】

TRUNCATE TABLE 表名;

DELETED 與TRUNCATE的區(qū)別

1. DELETE 刪除表中的數(shù)據(jù)，表結(jié)構(gòu)還在;刪除后的數(shù)據(jù)可以找回
2. TRUNCATE 刪除是把表直接DROP掉，然后再創(chuàng)建一個同樣的新表。
   刪除的數(shù)據(jù)不能找回。執(zhí)行速度比DELETE快。

DQL：數(shù)據(jù)查詢語言

1. 查詢所有列

SELECT * FROM 表名;

2. 結(jié)果集

數(shù)據(jù)庫執(zhí)行DQL語句不會對數(shù)據(jù)進(jìn)行改變，而是讓數(shù)據(jù)庫發(fā)送結(jié)果集給客戶端。
結(jié)果集：

1. 通過查詢語句查詢出來的數(shù)據(jù)以表的形式展示我們稱這個表為虛擬結(jié)果集。存放在內(nèi)存中。
2. 查詢返回的結(jié)果集是一張?zhí)摂M表。

3. 查詢指定列的數(shù)據(jù)

SELECT 列名1，列表2... FROM 表名;

4. 條件查詢

條件查詢就是在查詢時給出WHERE子句，在WHERE子句中可以使用一些運算符及關(guān)鍵字：

1. =（等于）、!=（不等于）、<>（不等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）；
2. BETWEEN…AND；值在什么范圍
3. IN(set)；固定的范圍值
4. IS NULL；（為空） IS NOT NULL（不為空）
5. AND；與
6. OR；或
7. NOT； 非

1. 查詢性別為男，并且年齡為20的學(xué)生記錄

SELECT * FROM students WHERE gender='男' AND age=20;

2. 查詢學(xué)號為1001 或者 名為zs的記錄

SELECT * FROM students WHERE id ='1001' OR name='zs';

3. 查詢學(xué)號為1001，1002，1003的記錄

SELECT * FROM students WHERE id='1001' OR id='1002' OR 1001='1003';

SELECT * FROM students WHERE id  IN('1001','1002','1003');

4. 查詢年齡為null的記錄

SELECT * FROM students WHERE age IS NULL;

5. 查詢年齡在18到20之間的學(xué)生記錄

SELECT * FROM students WHERE age>=18 AND age<=20;

SELECT * FROM students WHERE age BETWEEN 18 AND 20;

6. 查詢性別非男的學(xué)生記錄

SELECT * FROM students WHERE gender !='男';

7. 查詢姓名不為null的學(xué)生記錄

SELECT * FROM students WHERE  name IS NOT  NULL;

5. 模糊查詢

根據(jù)指定的關(guān)鍵進(jìn)行查詢, 使用LIKE關(guān)鍵字后跟通配符
通配符:

_ ：任意一個字符
%：任意0~n個字符

1. 查詢姓名由5個字母構(gòu)成的學(xué)生記錄

SELECT * FROM students WHERE name LIKE '_____';
-- 模糊查詢必須使用LIKE關(guān)鍵字。其中 “_”匹配任意一個字母，5個“_”表示5個任意字母。

2. 查詢姓名由5個字母構(gòu)成，并且第5個字母為“s”的學(xué)生記錄

SELECT * FROM students WHERE name LIKE '____s';

3. 查詢姓名以“m”開頭的學(xué)生記錄

SELECT * FROM students WHERE name LIKE 'm%';
-- 其中“%”匹配0~n個任何字母。

4. 查詢姓名中第2個字母為“u”的學(xué)生記錄

SELECT * FROM students WHERE name LIKE '_u%';

5. 查詢姓名中包含“s”字母的學(xué)生記錄

SELECT * FROM stu WHERE name LIKE '%s%';

6. 字段控制查詢

1. 去除重復(fù)記錄

SELECT DISTINCT name FROM students;

2. 把查詢字段的結(jié)果進(jìn)行運算，必須都要是數(shù)據(jù)型

SELECT *,字段1+字段2 FROM 表名;

列有很多記錄的值為NULL，
因為任何東西與NULL相加結(jié)果還是NULL，所以結(jié)算結(jié)果可能會出現(xiàn)NULL。
下面使用了把NULL轉(zhuǎn)換成數(shù)值0的函數(shù)IFNULL：

SELECT *,age+IFNULL(score,0) FROM students;

3. 對查詢結(jié)果起別名

在上面查詢中出現(xiàn)列名為sx+IFNULL(yw,0)，這很不美觀，現(xiàn)在我們給這一列給出一個別名，為total：

SELECT *, yw+IFNULL(sx,0) AS total FROM score;

SELECT *, yw+IFNULL(sx,0)  total FROM score; -- 省略 AS

7. 排序

創(chuàng)建表:

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  `performance` double(255,0) DEFAULT NULL,
  `manage` double(255,0) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee` VALUES (1001, '張三', '男', '1991-7-25', 2000, 200, 500, '營銷部');
INSERT INTO `employee` VALUES (1002, '李四', '男', '2017-7-5', 4000, 500, NULL, '營銷部');
INSERT INTO `employee` VALUES (1003, '王五', '女', '2018-5-1', 6000, 100, 5000, '研發(fā)部');
INSERT INTO `employee` VALUES (1004, '趙六', '男', '1991-6-1', 1000, 3000, 4000, '財務(wù)部');
INSERT INTO `employee` VALUES (1005, '孫七', '女', '2018-3-23', 8000, 1000, NULL, '研發(fā)部');
INSERT INTO `employee` VALUES (1006, '周八', '男', '2010-9-8', 5000, 500, 1000, '人事部');
INSERT INTO `employee` VALUES (1007, '吳九', '女', '2017-7-5', 8000, 601, NULL, '研發(fā)部');
INSERT INTO `employee` VALUES (1008, '鄭十', '女', '2014-4-6', 4000, 1801, NULL, '人事部');

對查詢的結(jié)果進(jìn)行排序
使用關(guān)鍵字ORDER BY
排序類型

1. 升序ASC 從小到大 默認(rèn)
2. 降序DESC 從大到小

1. 對所有員工的薪水進(jìn)行排序

SELECT *FROM employee ORDER BY salary ASC;

2. 查詢所有雇員，按月薪降序排序，如果月薪相同時，按編號升序排序

SELECT * FROM employee ORDER BY salary DESC, id ASC;

8.聚合函數(shù)

對查詢的結(jié)果進(jìn)行統(tǒng)計計算
常用聚合函數(shù):

1. COUNT()：統(tǒng)計指定列不為NULL的記錄行數(shù)；
2. MAX()：計算指定列的最大值，如果指定列是字符串類型，那么使用字符串排序運算；
3. MIN()：計算指定列的最小值，如果指定列是字符串類型，那么使用字符串排序運算；
4. SUM()：計算指定列的數(shù)值和，如果指定列類型不是數(shù)值類型，那么計算結(jié)果為0；
5. AVG()：計算指定列的平均值，如果指定列類型不是數(shù)值類型，那么計算結(jié)果為0；

1. COUNT

1. 查詢employee表中記錄數(shù)：

SELECT COUNT(*) AS total_record FROM employee;

2. 查詢員工表中有績效的人數(shù)

SELECT COUNT(performance)  FROM employee;

3. 查詢員工表中月薪大于2500的人數(shù)：

SELECT COUNT(*) FROM employee WHERE salary > 2500;

4. 統(tǒng)計月薪與績效之和大于5000元的人數(shù)：

SELECT COUNT(*)  FROM employee WHERE salary+IFNULL(performance,0) > 5000;

5. 查詢有績效的人數(shù)，和有管理費的人數(shù)：

SELECT COUNT(performance), COUNT(manage) FROM employee;

2. SUM和AVG

1. 查詢所有雇員月薪和：

SELECT SUM(salary) FROM employee;

2. 查詢所有雇員月薪和，以及所有雇員績效和

SELECT SUM(salary), SUM(performance) FROM employee;

3. 查詢所有雇員月薪+績效和：

SELECT SUM(salary+IFNULL(performance,0)) FROM employee;

4. 統(tǒng)計所有員工平均工資：

SELECT AVG(salary) FROM employee;

3. MAX和MIN

查詢最高工資和最低工資：

SELECT MAX(salary), MIN(salary) FROM employee;

9. 分組查詢

什么是分組查詢

將查詢結(jié)果按照1個或多個字段進(jìn)行分組，字段值相同的為一組

分組使用

SELECT gender from employee GROUP BY gender;

根據(jù)gender字段來分組，gender字段的全部值只有兩個('男'和'女')，所以分為了兩組 當(dāng)group by單獨使用時，只顯示出每組的第一條記錄 所以group by單獨使用時的實際意義不大

1. group by + group_concat()

group_concat(字段名)可以作為一個輸出字段來使用
表示分組之后，根據(jù)分組結(jié)果，使用group_concat()來放置每一組的某字段的值的集合

SELECT gender,GROUP_CONCAT(name) from employee GROUP BY gender;

2. group by + 聚合函數(shù)

通過group_concat()的啟發(fā)，我們既然可以統(tǒng)計出每個分組的某字段的值的集合，那么我們也可以通過集合函數(shù)來對這個"值的集合"做一些操作

查詢每個部門的部門名稱和每個部門的工資和

SELECT department,SUM(salary) FROM employee GROUP BY department;

查詢每個部門的部門名稱以及每個部門的人數(shù)

SELECT department,COUNT(*) FROM employee GROUP BY department;

查詢每個部門的部門名稱以及每個部門工資大于1500的人數(shù)

SELECT department,COUNT(salary) FROM employee WHERE salary > 1500 GROUP BY department;

3. group by + having

用來分組查詢后指定一些條件來輸出查詢結(jié)果
having作用和where一樣，但having只能用于group by

查詢工資總和大于9000的部門名稱以及工資和

SELECT department,GROUP_CONCAT(salary) FROM employee GROUP BY department;
	
SELECT department,SUM(salary) FROM employee GROUP BY department;
	

總和大于9000

SELECT department,SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>9000;
		

4. having與where的區(qū)別

having是在分組后對數(shù)據(jù)進(jìn)行過濾.
where是在分組前對數(shù)據(jù)進(jìn)行過濾
having后面可以使用分組函數(shù)(統(tǒng)計函數(shù))
where后面不可以使用分組函數(shù)
WHERE是對分組前記錄的條件，如果某行記錄沒有滿足WHERE子句的條件，那么這行記錄不會參加分組；而HAVING是對分組后數(shù)據(jù)的約束。

查詢工資大于2000的，工資總和大于6000的部門名稱以及工資和

1. 查詢工資大于2000的

SELECT * FROM employee WHERE salary >2000;	

2. 各部門工資

SELECT department, GROUP_CONCAT(salary) FROM employee WHERE salary >2000 GROUP BY department;

3. 各部門工資總和

SELECT department, SUM(salary) FROM employee WHERE salary >2000 GROUP BY department;

4. 各部門工資總和大于6000

SELECT department, SUM(salary) FROM employee WHERE salary >2000 GROUP BY department HAVING SUM(salary)>6000;

5. 各部門工資總和大于6000降序排列

SELECT department, SUM(salary) FROM employee 
WHERE salary >2000 
GROUP BY department 
HAVING SUM(salary)>6000 
ORDER BY SUM(salary) DESC;
	

10. LIMIT

從哪一行開始查，總共要查幾行
Limit 參數(shù)1，參數(shù)2

1. 參數(shù)1：從哪一行開始查
2. 參數(shù)2：一共要查幾行

角標(biāo)是從0開始

格式：

select * from 表名 limit 0,3;

11. 書寫順序

1. 書寫順序
   
2. 執(zhí)行順序