--這后邊就是注釋
/*
這里是注釋
這里是第二行注釋
*/

--建議關(guān)鍵字大寫

--當(dāng)你決定不想執(zhí)行某條命令時(shí)
\c --取消它

--顯示有哪些數(shù)據(jù)庫
SHOW DATABASES;

--使用連接某個(gè)數(shù)據(jù)庫
USE edrain01;

--查看你正在使用的是那一個(gè)數(shù)據(jù)庫
SELECT DATABASE();

--查看使用的數(shù)據(jù)庫中有哪些表
SHOW TABLES;

--查看表結(jié)構(gòu)
DESCRIBE employee;

--查看索引(如果表有索引的話)
SHOW INDEX FROM employee;

--退出
QUIT
EXIT

--創(chuàng)建數(shù)據(jù)庫(注意DARABA后面沒有S)
CREATE DATABASE edrain01;

--創(chuàng)建表
CREATE TABLE test
(
id int(10),
name char(20),
phone int(12)
);

--在表格中插入數(shù)據(jù)
INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);
INSERT INTO employee VALUES(02,'Jack',119119119);
INSERT INTO employee(id,name) VALUES(04,'Rose');

--檢索單個(gè)列
SELECT id
FROM employee;

--檢索多個(gè)列
SELECT id, name, phone
FROM employee;

--檢索所有列
SELECT *
FROM employee;
-- * 叫做通配符

--檢索去重
--使用DISTINCT關(guān)鍵字
SELECT DISTINCT *
FROM employee;

--限制只顯示結(jié)果中的前2行
SELECT * FROM employee LIMIT 2;

--查看某個(gè)表的指定列
SELECT id,name FROM employee;

--數(shù)學(xué)符號(hào)條件(=,<,>,>=,<=)
SELECT id,name FROM employee WHERE id>2;
SELECT id,name,phone FROM employee WHERE name='Tom';
SELECT id,name FROM employee WHERE id<2 OR id>3;
SELECT name,age FROM employee WHERE age>25 AND age<30;

-- IN 和 NOT IN 篩選“在” 或 “不在”某個(gè)范圍內(nèi)的結(jié)果
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');

--通配符
--SQL中的通配符是 _ 和 %
-- % 代表不定個(gè)未指定字符。
SELECT id, name
FROM employee
WHERE name LIKE '%om%';

--下劃線_通配符
-- _ 代表一個(gè)未指定字符
SELECT id, name
FROM employee
WHERE name LIKE '__m';

--用CONTAINS進(jìn)行搜索
SELECT id,name
FROM employee
WHERE CONTAINS(id,'1');
/CONTAINS搜索通常比LIKE更快，表越大越如此
CONTAINS 中可以使用通配符：CONTAINS(note_text, '"anvil"');
表示匹配任何以anvil開始的詞/

--通配符使用技巧
--不要過度使用通配符，如果能夠用其他操作符達(dá)到相同的目的，就用其他操作符
--盡量不要把通配符放在搜索模式的開始處，因?yàn)檫@樣是搜索最慢的
--仔細(xì)注意通配符的位置。如果放錯(cuò)地方，可能不會(huì)返回想要的數(shù)據(jù)

--對(duì)結(jié)果進(jìn)行排序
--ORDER BY 語句 升序排列 ASC；
SELECT name
FROM employee
ORDER BY name;
--會(huì)以字母順序排序

--按照多個(gè)列排序
SELECT id, phone, name
FROM employee
ORDER BY phone, name;
--先按電話排序，后按名稱排序

--降序排列 DESC
SELECT id, phone, name
FROM employee
ORDER BY phone DESC;
--會(huì)以電話降序排列

--SQL 內(nèi)置函數(shù)和計(jì)算
Count() --返回某列的行數(shù)
Sum() --返回某列值的和
Avg() --返回某列的平均值
Max() --返回某列的最大值
Min() --返回某列的最小值

--Count()函數(shù) --返回表中行的數(shù)目或者復(fù)合特定條件的行的數(shù)目
--包括了NULL行
--能夠使用Count()對(duì)表中的行數(shù)目進(jìn)行計(jì)數(shù)
SELECT COUNT(cust_email) AS num_cust FROM customers;
SELECT COUNT() FROM employee;
--選取具有電子郵件的客戶計(jì)數(shù),計(jì)數(shù)值在以num_cust命名的列中返回
--如果指定列名，則指定列的值為空的行被Count()函數(shù)忽略，但是如果Count()函數(shù)中用的星號(hào)(*),則不忽略

--Sum()用來返回指定列值的和
SELECT Sum(item_price * quantity) AS total_price
FROM prderitems
WHERE order_num = 20005;
--利用標(biāo)準(zhǔn)的算術(shù)操作符，所有聚集函數(shù)都可以用來執(zhí)行多個(gè)列上的計(jì)算

--Avg()函數(shù) --Avg()忽略列值為NULL的行
SELECT Avg(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

--Max()函數(shù) --返回指定列中的最大值
--Max()函數(shù)忽略列值為NULL的行
--如果對(duì)于非數(shù)值數(shù)據(jù)使用Max()，在用于文本數(shù)據(jù)時(shí)，如果數(shù)據(jù)按照相應(yīng)的列排序，則Max()返回最后一行

--Min()函數(shù) --和上一條類比
--如果對(duì)于非數(shù)值數(shù)據(jù)，返回最前面的行

--計(jì)算出salary的最大、最小值
--使用AS關(guān)鍵詞可以給值重命名
SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

--子查詢
/*
想要知道名為 "Tom" 的員工所在部門做了幾個(gè)工程。
員工信息儲(chǔ)存在 employee 表中，但工程信息儲(chǔ)存在project 表中。
*/
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

image.png

--連接查詢
/*
在處理多個(gè)表時(shí)，子查詢只有在結(jié)果來自一個(gè)表時(shí)才有用。
但如果需要顯示兩個(gè)表或多個(gè)表中的數(shù)據(jù)，這時(shí)就必須使用連接 (join) 操作。
連接的基本思想是把兩個(gè)或多個(gè)表當(dāng)作一個(gè)新的表來操作
/
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
--另一個(gè)連接語句格式是使用 JOIN ON 語法
SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;
/
查詢出的是，各員工所在部門的人數(shù)，
其中員工的 id 和 name 來自 employee 表，people_num 來自 department 表：
*/

修改和刪除

• 數(shù)據(jù)庫操作
• 數(shù)據(jù)表操作
• 更新和刪除數(shù)據(jù)

--重命名一張表
RENAME TABLE 原名 TO 新名字;
ALTER TABLE 原名 RENAME 新名;
ALTER TABLE 原名 RENAME TO 新名;

--刪除一張表,數(shù)據(jù)結(jié)構(gòu)會(huì)被刪除了
DROP TABLE 表名字;

--增加一列
ALTER TABLE 表名字 ADD COLUMN 列名字 數(shù)據(jù)類型 約束;
ALTER TABLE 表名字 ADD 列名字 數(shù)據(jù)類型 約束;
約束默認(rèn)值為2，eg:
ALTER TABLE employee ADD price INT(8) DEFAULT 2;
--如果想放在第一列的位置 FIRST
ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;
--新增的列加在某位置的后面 AFTER
ALTER TABLE employee ADD test INT(10) DEFAULT 8 AFTER price;

--刪除一列
ALTER TABLE 表名字 DROP COLUMN 列名字;
ALTER TABLE 表名字 DROP 列名字;

--重命名一列
ALTER TABLE 表名字 CHANGE 原列名 新列名 數(shù)據(jù)類型 約束;
eg:
ALTER TABLE employee CHANGE price jiage INT(8) DEFAULT 2;

--改變數(shù)據(jù)類型
ALTER TABLE 表名字 MODIFY 列名字 新數(shù)據(jù)類型;
/修改數(shù)據(jù)類型必須小心，因?yàn)檫@可能會(huì)導(dǎo)致數(shù)據(jù)丟失。
在嘗試修改數(shù)據(jù)類型之前，請(qǐng)慎重考慮/

--修改表中某個(gè)值
UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 條件;
eg:
UPDATE employee SET name='EDRAINtest',phone=1888666 WHERE name='Tom';

--刪除一行記錄
DELETE FROM 表名字 WHERE 條件;
eg：
DELETE FROM employee WHERE name='Rose';
/刪除表中的一行數(shù)據(jù)，也必須加上WHERE條件，否則整列的數(shù)據(jù)都會(huì)被刪除。/

索引、視圖，導(dǎo)入和導(dǎo)出，備份和恢復(fù)

• 索引
• 視圖
• 導(dǎo)入和導(dǎo)出
• 備份和恢復(fù)

--顯示表中有哪些索引
SHOW INDEX FROM 表名字
eg:
SHOW INDEX FROM employee;

--在表中添加索引值
ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
CREATE INDEX 索引名 ON 表名字 (列名);
eg：
--在employee表的id列上建立名為idx_id的索引
ALTER TABLE employee ADD INDEX idx_id (id);
--在employee表的name列上建立名為idx_name的索引
CREATE INDEX idx_name ON employee (name);

--創(chuàng)建視圖
CREATE VIEW 視圖名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;
CREATE VIEW v_emp (v_name,v_age,v_phone) AS SELECT name,age,phone FROM employee;

--導(dǎo)入
LOAD DATA INFILE '文件路徑和文件名' INTO TABLE 表名字;
--將文件中的數(shù)據(jù)導(dǎo)入到employee表中
LOAD DATA INFILE '/tmp/SQL6/in.txt' INTO TABLE employee;
--如果用Windows中的編輯器（使用\r\n做為行的結(jié)束符）創(chuàng)建文件，應(yīng)使用：
LOAD DATA INFILE '文件路徑' INTO TABLE employee LINES
TERMINATED BY '\r\n';
--在運(yùn)行OS X的蘋果電腦上，應(yīng)使用行結(jié)束符\r

--導(dǎo)出
SELECT * INTO OUTFILE '導(dǎo)出生成的文件路徑和文件名' FROM 表名字;
--將整個(gè)employee表的數(shù)據(jù)導(dǎo)出到　/tmp　目錄下
SELECT * INTO OUTFILE '/tmp/out.txt' FROM employee;

--備份
mysqldump -u root 數(shù)據(jù)庫名>備份文件名; --備份整個(gè)數(shù)據(jù)庫
mysqldump -u root 數(shù)據(jù)庫名 表名字>備份文件名; --備份整個(gè)表
eg:
mysqldump -u root mysql_shiyan > bak.sql;

--第一種恢復(fù)數(shù)據(jù)庫的方法
source /tmp/SQL6/MySQL-06.sql
--另一種恢復(fù)數(shù)據(jù)庫的方法
--先使用命令新建一個(gè)空的數(shù)據(jù)庫 test：
mysql -u root --因?yàn)樵谏弦徊揭呀?jīng)退出了MySQL，現(xiàn)在需要重新登錄
CREATE DATABASE test; --新建一個(gè)名為test的數(shù)據(jù)庫
mysql -u root test < bak.sql --剛才備份的 bak.sql 恢復(fù)到 test 數(shù)據(jù)庫：

/*
1. 索引：可以加快查詢速度
2. 視圖：是一種虛擬存在的表
3. 導(dǎo)入：從文件中導(dǎo)入數(shù)據(jù)到表
4. 導(dǎo)出：從表中導(dǎo)出到文件中
5. 備份：mysqldump 備份數(shù)據(jù)庫到文件
6. 恢復(fù)：從文件恢復(fù)數(shù)據(jù)庫
*/

--查詢MySQL版本號(hào)
SELECT VERSION();
--查詢當(dāng)前日期
CURRENT_DATE;
--查詢當(dāng)前時(shí)間
SELECT NOW();
--查詢當(dāng)前用戶
SELECT user();

--簡單計(jì)算功能
SELECT SIN(PI()/4),(4+1)*5;

--查詢表中的名稱或類型
DESCRIBE department;
--查看表的數(shù)據(jù)結(jié)構(gòu)
SHOW CREATE TABLE department;
--查看表中字段的信息
SHOW FULL COLUMNS FROM department;

--更新需要修改的值
UPDATE 表名字 SET 更新的地方 WHERE 條件;
eg:
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

--日期計(jì)算
--函數(shù)TIMESTAMPDIFF()計(jì)算當(dāng)前日期的年和出生日期之間的差
--函數(shù)CURDATE()是計(jì)算當(dāng)前的日期
SELECT name, birth, CURDATE(),
TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
FROM pet;
--(YEAR(CURDATE())-YEAR(birth))也可以計(jì)算當(dāng)前日期的年和出生日期之間的差
SELECT name, birth, CURDATE(),
(YEAR(CURDATE())-YEAR(birth))

• (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age
  FROM pet;
  /*
  此處，YEAR()提取日期的年部分，RIGHT()提取日期最右面5個(gè)字符的MM-DD (月份和日期)部分。
  MM-DD值的表達(dá)式部分的值一般為1或0，如果CURDATE()的年比birth的年早，則年份應(yīng)減去1。
  整個(gè)表達(dá)式看起來有些難懂，使用age來使輸出的列標(biāo)記更有意義。
  */

--NULL值操作
/*
概念上，NULL意味著“沒有值”或“未知值”，并且它被看作使與眾不同的值。
為了測試NULL，你不能使用算術(shù)比較操作符例如=、<或!=
/
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
/
很顯然你不能通過這些得到有意義的結(jié)果，因?yàn)槿魏问褂盟銛?shù)比較操作符對(duì)NULL進(jìn)行比較的結(jié)果都是NULL。
因此使用IS NULL和IS NOT NULL操作符：
*/
SELECT 1 IS NULL, 1 IS NOT NULL;
--在MySQL中，0或NULL意味著假而其它值意味著真。布爾運(yùn)算的默認(rèn)真值是1。

--使用“^”和“$”匹配名字的開始和結(jié)尾，和5個(gè)“.”實(shí)例在兩者之間：
SELECT * FROM pet WHERE name REGEXP '^.....$';
--使用“{n}”重復(fù)n次操作符,重寫前面的查詢
SELECT * FROM pet WHERE name REGEXP '^.{5}$';

--COUNT()計(jì)算和GROUP BY分組以各種形式分類你的數(shù)據(jù)
--查看每種動(dòng)物的數(shù)量：
SELECT species, COUNT() FROM pet GROUP BY species;
--按種類和性別組合分類的動(dòng)物數(shù)量：
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

--尋找列的最大值
SELECT MAX(article) as article FROM shop;

--查詢某列最大值所在的行
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);

--對(duì)所有行進(jìn)行價(jià)格的降序排列，然后使用MySQL特定的LIMIT子句顯示其中一行
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

--按組顯示列的最大值
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article;

--使用用戶變量
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

-- 使用AUTO_INCREMENT語句
-- 在定義列屬性的時(shí)候添加AUTO_INCREMENT語句可以使得每條記錄都能被唯一標(biāo)識(shí)：
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');
SELECT * FROM animals;
-- 要想AUTO_INCREMENT語句生成的起始值不是1，可以通過CREATE TABLE或ALTER TABLE來設(shè)置該值
ALTER TABLE animals AUTO_INCREMENT = 100;

--使用外鍵
/* 在連接兩個(gè)表的時(shí)候并不需要外鍵約束。
對(duì)于除InnoDB表以外的表，可以使用REFERENCES tbl_name(col_name)語句定義將它的列設(shè)置為外鍵，
但是該語句并沒有實(shí)際的作用，只是作為備注來提醒你現(xiàn)在正在定義的列指向另外一個(gè)表的列。 */
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
--
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
--
INSERT INTO person VALUES (NULL, 'Antonio Paz');
--
SELECT @last := LAST_INSERT_ID();
--
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
--
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
--
SELECT @last := LAST_INSERT_ID();
--
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

--查看表的數(shù)據(jù)結(jié)構(gòu)
SHOW CREATE TABLE shirt;
SHOW CREATE TABLE shirt\G
DESCRIBE shirt;

--使用兩個(gè)關(guān)鍵字進(jìn)行搜索
-- 這里面的test_table可以是任何一個(gè)表，關(guān)鍵詞也是類似
-- 充分利用OR連接兩個(gè)關(guān)鍵字（AND也是一樣的道理）
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1';
eg：
SELECT id, name FROM employee
WHERE id = '1' OR name = 'Rose';

-- 計(jì)算每個(gè)月的訪問量
-- 創(chuàng)建表
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);
-- 使用BIT_COUNT函數(shù)計(jì)算每個(gè)月中某用戶訪問網(wǎng)頁的天數(shù)：
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;