1.單表格操作：

?CREATE DATABASE `sql_tutorial`;

?SHOW DATABASES;

?DROP DATABASE `sql_tutorial`;

?

?#創(chuàng)建表格

?USE `sql_tutorial`;

?CREATE TABLE `student`(

?`student_id` INT PRIMARY KEY,

?`name` VARCHAR(20),

?`major` VARCHAR(20)

?);

?

DESCRIBE `student`;

DROP TABLE `student`;

#輸入表格中值

ALTER TABLE `student` ADD gpa DECIMAL(3,2);?#在表格中添加一列g(shù)pa，是小數(shù)，總共3位數(shù)，小數(shù)占2位

ALTER TABLE `student` DROP COLUMN gpa;

INSERT INTO `student` VALUES(1,'小白','歷史');

INSERT INTO `student` VALUES(2,'小黑','歷史');

INSERT INTO `student` VALUES(3,'小綠',NULL);

SELECT * FROM `student`; #從全部student表格搜索

INSERT INTO `student`(`name`,`major`,`student_id`) VALUES('小紅','英語(yǔ)',5); #可以自己定義順序，如果沒(méi)有寫(xiě)某個(gè)屬性，就把該行資料的該屬性定義為NULL

# constraint 限制、約束，預(yù)設(shè)值：DEFAULT

DROP?TABLE `student`;

?CREATE TABLE `student`(

?`student_id` INT PRIMARY KEY,

?`name` VARCHAR(20) NOT NULL, #后面的表示限制，該限制為不能空白

?`major` VARCHAR(20) UNIQUE #該列值要唯一，把UNIQUE改為 DEFAULT '歷史' 表示把major的預(yù)設(shè)值改為歷史，在沒(méi)有major的輸入時(shí)，自動(dòng)表示歷史。

?);

INSERT INTO `student` VALUES(1,'小白','歷史');

INSERT INTO `student` VALUES(2,'小黑','英語(yǔ)');

?DESCRIBE `student`;

?

?CREATE TABLE `student`(

?`student_id` INT PRIMARY KEY,

?`name` VARCHAR(20) NOT NULL, #后面的表示限制，該限制為不能空白

?`major` VARCHAR(20) DEFAULT '歷史'?

?);

?INSERT INTO `student` (`student_id`,`name`) VALUES(2,'小白');

?DESCRIBE `student`;

?SELECT * FROM `student`;

#AUTO_INCREMENT, #id自動(dòng)增加，不用在輸入時(shí)寫(xiě)id

DROP TABLE `student`;?

?CREATE TABLE `student`(

?`student_id` INT AUTO_INCREMENT, #id自動(dòng)增加，不用在輸入時(shí)寫(xiě)id

?`name` VARCHAR(20) NOT NULL, #后面的表示限制，該限制為不能空白

?`major` VARCHAR(20) DEFAULT '歷史' ,

?PRIMARY KEY(`student_id`)

?);

?INSERT INTO `student` (`major`,`name`) VALUES('英語(yǔ)','小白');

?INSERT INTO `student` (`major`,`name`) VALUES('英語(yǔ)','小黑');

?DESCRIBE `student`;

?SELECT * FROM `student`;

?

?## 修改、刪除資料

?#修改 UPDATE

?SET SQL_SAFE_UPDATES = 0; #關(guān)閉預(yù)設(shè)自動(dòng)更新

?DROP TABLE student;

?

?DROP TABLE student;

?CREATE TABLE `student`(

?`student_id` INT AUTO_INCREMENT,

?`name` VARCHAR(20), #后面的表示限制，該限制為不能空白

?`major` VARCHAR(20),

?`score` INT,

?PRIMARY KEY(`student_id`)

?);

?INSERT INTO `student` (`major`,`name`,`score`) VALUES('英語(yǔ)','小白',70);

?INSERT INTO `student` (`major`,`name`,`score`) VALUES('生物','小藍(lán)',72);

?INSERT INTO `student` (`major`,`name`,`score`) VALUES('數(shù)學(xué)','小綠',76);

?INSERT INTO `student` (`major`,`name`,`score`) VALUES('英語(yǔ)','小紅',71);

?SELECT * FROM `student`;

?UPDATE `student`??#把英語(yǔ)改成英語(yǔ)文學(xué)

?SET `major` = '英語(yǔ)文學(xué)'

?WHERE `major` = '英語(yǔ)';

?UPDATE `student`??#把第三個(gè)學(xué)生的major改為英語(yǔ)文學(xué)

?SET `major` = '英語(yǔ)文學(xué)'

?WHERE `student_id` = 3;

?UPDATE `student`??#把生物或者數(shù)學(xué)的改成生數(shù)

?SET `major` = '生數(shù)'

?WHERE `major` = '生物' OR `major`= '數(shù)學(xué)';

?UPDATE `student`??#把第三個(gè)學(xué)生的major改為英語(yǔ)文學(xué),并且同時(shí)把名字也改了

?SET `major` = '英語(yǔ)文學(xué)',`name` = '小灰'

?WHERE `student_id` = 1;

?UPDATE `student`??#不寫(xiě)條件的話(huà)，就會(huì)吧所有的major都改變

?SET `major` = '英語(yǔ)文學(xué)';

?#刪除 DELETE FROM

?DELETE FROM `student`?#把編號(hào)為4的學(xué)生刪掉

?WHERE `student_id` = 4;

?SELECT * FROM `student`;

?DELETE FROM `student`?#可以用and選多個(gè)條件

?WHERE `name` = '小紅'?AND `major` = '英語(yǔ)文學(xué)';

?DELETE FROM `student`?#根據(jù)成績(jī)

?WHERE `score` < 71;???#不等于，不寫(xiě)where就會(huì)把整個(gè)表格刪掉

??

??

?##取得資料?SELECT 、?LIMIT

?#SELECT

?SELECT `major`, `name` FROM?`student`;?#SELECT 后接要搜尋的內(nèi)容，*表示所有

?SELECT * FROM `student`;?

?#排序 ORDER BY 由低到高；ORDER BY 屬性 DESC 由高到低；ORDER BY 屬性1,屬性2 先按屬性1排，如果1一樣，再按2排序

?SELECT * FROM `student`

?ORDER BY `score`;?

?SELECT * FROM `student`

?ORDER BY `score` DESC;?

?SELECT * FROM `student`

?ORDER BY `score`, `student_id`;?

?# LIMIT n 限制回傳的數(shù)量為n筆，就是前n行。

?SELECT * FROM `student`

?ORDER BY `student_id`

?LIMIT 2;?

?SELECT * FROM `student`

?ORDER BY `student_id`

?LIMIT 2;?

?#用WHERE進(jìn)行限制

?SELECT * FROM `student`

?WHERE `major`= '英語(yǔ)' AND `score` <> 71;

?SELECT * FROM `student`

?WHERE `major` IN('英語(yǔ)' , '歷史' , '數(shù)學(xué)');#等價(jià)于下面用or聯(lián)接

?SELECT * FROM `student`

?WHERE `major`= '英語(yǔ)' OR?`major`='歷史' OR?`major`='數(shù)學(xué)';

?# SELECT、where、order任意搭配使用。

2.多表格操作：

-- 創(chuàng)建公司資料表格

?CREATE DATABASE `sql_tutorial`;

?SHOW DATABASES;

?USE `sql_tutorial`;

DROP TABLE `student`;

?SET SQL_SAFE_UPDATES = 0; #關(guān)閉預(yù)設(shè)自動(dòng)更新

?#創(chuàng)建表格

CREATE TABLE `employee`(

?`emp_id` INT PRIMARY KEY,

?`name` VARCHAR(20),

?`brith_date` DATE,

?`sex` VARCHAR(1),

?`salary` INT,

?`branch_id` INT,

?`sup_id` INT

);

DROP TABLE `employee`;

describe TABLE employee;

?#創(chuàng)建部門(mén)表格

?CREATE TABLE `branch`(

?`branch_id` INT PRIMARY KEY,

?`branch_name` VARCHAR(20),

?`manager_id` INT,

?FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL???#FOREIGN KEY表示外鍵；REFERENCES 表示外鍵接的表格名，再加外接的表格的primary key

?);

?

?#對(duì)公司表格補(bǔ)上外鍵foreign key，

?ALTER TABLE `employee`?

?ADD FOREIGN KEY(`branch_id`)?#加外鍵 branch_id屬性

?REFERENCES `branch`(`branch_id`)?#對(duì)應(yīng)到branch表格的branch_id的屬性

?ON DELETE SET NULL;

?

?ALTER TABLE `employee`

?ADD FOREIGN KEY(`sup_id`)?#加外鍵sup_id

?REFERENCES `employee`(`emp_id`)?#對(duì)應(yīng)到branch表格的branch_id的屬性

?ON DELETE SET NULL;?

?

?#創(chuàng)建客戶(hù)表格

CREATE TABLE `client`(

?`client_id` INT PRIMARY KEY,

?`client_name` VARCHAR(20),

?`phone` VARCHAR(20)

?);

??

#創(chuàng)建worh_with表格

?CREATE TABLE `work_with`(

?`emp_id` INT,

?`client_id` INT,

?`total_sales` INT,

?PRIMARY KEY ( `emp_id`, `client_id`),

?FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,

?FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE??

?);

??

?#填寫(xiě)表格數(shù)據(jù)

?#有foreign key時(shí)，對(duì)應(yīng)的表沒(méi)有相應(yīng)資料時(shí)，直接輸入會(huì)報(bào)錯(cuò)，要用NULL代替

?INSERT INTO `branch` VALUES(1,'研發(fā)',NULL);

?INSERT INTO `branch` VALUES(2,'行政',NULL);

?INSERT INTO `branch` VALUES(3,'查詢(xún)',NULL);

?INSERT INTO `branch` VALUES(4,'查詢(xún)',NULL);

?

?INSERT INTO `employee` VALUES(206,'小黃','1998-10-08','F',50000,1,NULL);

?INSERT INTO `employee` VALUES(207,'小綠','1995-10-09','M',51000,2,206);

?INSERT INTO `employee` VALUES(208,'小灰','1993-11-24','M',23000,3,207);

?INSERT INTO `employee` VALUES(209,'小黑','1996-10-08','M',36000,3,208);

?INSERT INTO `employee` VALUES(210,'小紅','1997-07-05','F',50000,1,209);

??

??#修改

?UPDATE `employee`??

?SET `branch_id` = '3'

?WHERE `emp_id` = 209;

?select * from `employee`;

?desc `employee`;

?DROP TABLE `employee`;?

??

?#把原本branch的manager_id=NULL改回來(lái)

?UPDATE `branch`

?SET `manager_id`=206

?where `branch_id`=1;

?UPDATE `branch`

?SET `manager_id`=207

?where `branch_id`=2;

?UPDATE `branch`

?SET `manager_id`=208

?where `branch_id`=3;

??

?INSERT INTO `client` VALUES(400,'阿狗','1234567');

?INSERT INTO `client` VALUES(401,'阿貓','1673692');

?INSERT INTO `client` VALUES(402,'來(lái)福','1894738');

?INSERT INTO `client` VALUES(403,'路西','1746952');

?INSERT INTO `client` VALUES(405,'杰克','9947538');?

?

?INSERT INTO `work_with` VALUES(206,400,70000);

?INSERT INTO `work_with` VALUES(207,400,'56000');

?INSERT INTO `work_with` VALUES(208,402,'35000');

?INSERT INTO `work_with` VALUES(209,403,'54000');

?INSERT INTO `work_with` VALUES(210,404,'64000');

??

?DROP TABLE `worh_with`;?

?desc work_with;

?select * from `work_with`;

##聚合函數(shù) aggregate functions

#取得員工數(shù)目

select count(*) from `employee`;?#表格里有基筆資料

select count(sup_id) from `employee`;?#count()里對(duì)應(yīng)的屬性的資料有幾筆

#取得所有出生在1996-10-01之后出生的女性員工

select count(*) from `employee`

where `brith_date` > '1996-10-01' and `sex`='F';?

#取得所有員工的平均薪水

select avg(`salary`) from `employee`;

#取得所有員工的總和薪水 sum

select sum(`salary`) from `employee`;

#取得最高/低員工的薪水 max,min

select max(`salary`) from `employee`;

##wildcards 萬(wàn)用字元?%表示多個(gè)字元，_表示一個(gè)字元，可以用一個(gè)，也可以前后都用

#取得電話(huà)號(hào)碼尾數(shù)是567的客戶(hù)

select * from `client`

where `phone` like '%567';

#取得電話(huà)號(hào)碼開(kāi)頭是189的客戶(hù)

select * from `client`

where `phone` like '189%';

#取得姓阿的員工

select * from `client`

where `client_name` like '阿%';

#取得生日在10月的員工

select * from `employee`

where `brith_date` like '_____10%';

select * from `employee`;

##union 連集 可以合并搜尋的結(jié)果，合并的屬性資料形態(tài)要一樣，不同表格的屬性數(shù)目要一樣，不能用表格1的2個(gè)屬性去和表格2的1個(gè)屬性合并

#員工名字和客戶(hù)名和部門(mén)名字字合并

select (`name`) from `employee`

union

select (`client_name`) from `client`

union

select (`branch_name`) from `branch`;

select `name`,`emp_id` from `employee`

union

select `client_name`,`client_id` from `client`;

select `name` as `total_name`,`emp_id` as `total_id`??#改變回傳的屬性名稱(chēng)

from `employee`

union

select `client_name`,`client_id` from `client`;

##join 連接

insert into `branch` values(4,'瑜伽',NULL);

#取得所有部門(mén)經(jīng)理的名字，把employee和branch兩個(gè)表格連接起來(lái)，條件是連個(gè)id一樣。

select * from employee???

#select `emp_id`, `name`, `branch_name` from `employee`?

join `branch`

on `emp_id` = `manager_id`;

#如果不同表格有相同屬性，用表格.屬性名稱(chēng)進(jìn)行區(qū)分，說(shuō)明表格屬性來(lái)源

select `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name` from `employee`?

join `branch`

on `employee`.`emp_id` = `branch`.`manager_id`;

#把左/右邊的表格都回傳，右/左邊邊表格符合條件才回傳,否則回傳NULL,?left join/right join

select *?

#from `employee`?left join `branch`

from `employee`?right join `branch`

on `emp_id` = `manager_id`;

select * from `branch`;

delete?from `branch` where `branch_id` = 4;

##subquery?子查詢(xún)，在一個(gè)查詢(xún)語(yǔ)句里面查詢(xún)另外一個(gè)結(jié)果，就是在一個(gè)查詢(xún)語(yǔ)句里面插入另外一個(gè)查詢(xún)結(jié)果

#找出研發(fā)部門(mén)的經(jīng)理名字：先找研發(fā)部門(mén)的manager_id(放在括號(hào)里面的，就是子查詢(xún)的部分），再用id進(jìn)行查找名字

select `name` from `employee`

where `emp_id` = (

select `manager_id` from `branch`

??where `branch_name` = '研發(fā)'

);

???

#找出一位客戶(hù)銷(xiāo)售金額超過(guò)50000的員工名字:當(dāng)子查詢(xún)回傳結(jié)果不止一個(gè)，要用in不用=

select `name` from `employee`

where `emp_id`in (

select `emp_id` from `work_with`

??where `total_sales` > 50000

??);

# on delete 刪除某一行

# on delete set NULL：如果刪掉某個(gè)資料1，其他表格的資料2用外鍵對(duì)應(yīng)的該資料1，在其他表格中把資料2設(shè)為null,當(dāng)資料2為primary key時(shí)，不能設(shè)置為null，只能為on delete cascade

#on delete cascade：如果刪掉某個(gè)資料1，其他表格的資料2用外鍵對(duì)應(yīng)的該資料1，在其他表格中把資料2也刪掉

#創(chuàng)建worh_with表格

?CREATE TABLE `work_with`(

?`emp_id` INT,

?`client_id` INT,

?`total_sales` INT,

?PRIMARY KEY ( `emp_id`, `client_id`),

?FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,

?FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE??

?);

?#創(chuàng)建部門(mén)表格

?CREATE TABLE `branch`(

?`branch_id` INT PRIMARY KEY,

?`branch_name` VARCHAR(20),

?`manager_id` INT,

?FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL???#FOREIGN KEY表示外鍵；REFERENCES 表示外鍵接的表格名，再加外接的表格的primary key

?);

?delete from `employee`

?where `emp_id`= 207;

?

select * from `work_with`;

select * from `branch`;

update `branch`

set `manager_id`=207

where `branch_id`=2;?

INSERT INTO `employee` VALUES(207,'小綠','1995-10-09','M',51000,2,206);

INSERT INTO `work_with` VALUES(207,400,'56000');