【数据库】SQL 3小时快速入门 #数据库教程 #SQL教程 #MySQL教程
1.单表格操作：
CREATE DATABASE `sql_tutorial`;
SHOW DATABASES;
DROP DATABASE `sql_tutorial`;
#创建表格
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); #在表格中添加一列gpa，是小数，总共3位数，小数占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('小红','英语',5); #可以自己定义顺序，如果没有写某个属性，就把该行资料的该属性定义为NULL
# constraint 限制、约束，预设值：DEFAULT
DROP TABLE `student`;
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY,
`name` VARCHAR(20) NOT NULL, #后面的表示限制，该限制为不能空白
`major` VARCHAR(20) UNIQUE #该列值要唯一，把UNIQUE改为 DEFAULT '历史' 表示把major的预设值改为历史，在没有major的输入时，自动表示历史。
);
INSERT INTO `student` VALUES(1,'小白','历史');
INSERT INTO `student` VALUES(2,'小黑','英语');
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自动增加，不用在输入时写id
DROP TABLE `student`;
CREATE TABLE `student`(
`student_id` INT AUTO_INCREMENT, #id自动增加，不用在输入时写id
`name` VARCHAR(20) NOT NULL, #后面的表示限制，该限制为不能空白
`major` VARCHAR(20) DEFAULT '历史' ,
PRIMARY KEY(`student_id`)
);
INSERT INTO `student` (`major`,`name`) VALUES('英语','小白');
INSERT INTO `student` (`major`,`name`) VALUES('英语','小黑');
DESCRIBE `student`;
SELECT * FROM `student`;
## 修改、删除资料
#修改 UPDATE
SET SQL_SAFE_UPDATES = 0; #关闭预设自动更新
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('英语','小白',70);
INSERT INTO `student` (`major`,`name`,`score`) VALUES('生物','小蓝',72);
INSERT INTO `student` (`major`,`name`,`score`) VALUES('数学','小绿',76);
INSERT INTO `student` (`major`,`name`,`score`) VALUES('英语','小红',71);
SELECT * FROM `student`;
UPDATE `student` #把英语改成英语文学
SET `major` = '英语文学'
WHERE `major` = '英语';
UPDATE `student` #把第三个学生的major改为英语文学
SET `major` = '英语文学'
WHERE `student_id` = 3;
UPDATE `student` #把生物或者数学的改成生数
SET `major` = '生数'
WHERE `major` = '生物' OR `major`= '数学';
UPDATE `student` #把第三个学生的major改为英语文学,并且同时把名字也改了
SET `major` = '英语文学',`name` = '小灰'
WHERE `student_id` = 1;
UPDATE `student` #不写条件的话，就会吧所有的major都改变
SET `major` = '英语文学';
#删除 DELETE FROM
DELETE FROM `student` #把编号为4的学生删掉
WHERE `student_id` = 4;
SELECT * FROM `student`;
DELETE FROM `student` #可以用and选多个条件
WHERE `name` = '小红' AND `major` = '英语文学';
DELETE FROM `student` #根据成绩
WHERE `score` < 71; #不等于，不写where就会把整个表格删掉
##取得资料 SELECT 、 LIMIT
#SELECT
SELECT `major`, `name` FROM `student`; #SELECT 后接要搜寻的内容，*表示所有
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 限制回传的数量为n笔，就是前n行。
SELECT * FROM `student`
ORDER BY `student_id`
LIMIT 2;
SELECT * FROM `student`
ORDER BY `student_id`
LIMIT 2;
#用WHERE进行限制
SELECT * FROM `student`
WHERE `major`= '英语' AND `score` <> 71;
SELECT * FROM `student`
WHERE `major` IN('英语' , '历史' , '数学');#等价于下面用or联接
SELECT * FROM `student`
WHERE `major`= '英语' OR `major`='历史' OR `major`='数学';
# SELECT、where、order任意搭配使用。
2.多表格操作：
-- 创建公司资料表格
CREATE DATABASE `sql_tutorial`;
SHOW DATABASES;
USE `sql_tutorial`;
DROP TABLE `student`;
SET SQL_SAFE_UPDATES = 0; #关闭预设自动更新
#创建表格
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;
#创建部门表格
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
);
#对公司表格补上外键foreign key，
ALTER TABLE `employee`
ADD FOREIGN KEY(`branch_id`) #加外键 branch_id属性
REFERENCES `branch`(`branch_id`) #对应到branch表格的branch_id的属性
ON DELETE SET NULL;
ALTER TABLE `employee`
ADD FOREIGN KEY(`sup_id`) #加外键sup_id
REFERENCES `employee`(`emp_id`) #对应到branch表格的branch_id的属性
ON DELETE SET NULL;
#创建客户表格
CREATE TABLE `client`(
`client_id` INT PRIMARY KEY,
`client_name` VARCHAR(20),
`phone` VARCHAR(20)
);
#创建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
);
#填写表格数据
#有foreign key时，对应的表没有相应资料时，直接输入会报错，要用NULL代替
INSERT INTO `branch` VALUES(1,'研发',NULL);
INSERT INTO `branch` VALUES(2,'行政',NULL);
INSERT INTO `branch` VALUES(3,'查询',NULL);
INSERT INTO `branch` VALUES(4,'查询',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改回来
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,'来福','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`;
##聚合函数 aggregate functions
#取得员工数目
select count(*) from `employee`; #表格里有基笔资料
select count(sup_id) from `employee`; #count()里对应的属性的资料有几笔
#取得所有出生在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 万用字元 %表示多个字元，_表示一个字元，可以用一个，也可以前后都用
#取得电话号码尾数是567的客户
select * from `client`
where `phone` like '%567';
#取得电话号码开头是189的客户
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 连集 可以合并搜寻的结果，合并的属性资料形态要一样，不同表格的属性数目要一样，不能用表格1的2个属性去和表格2的1个属性合并
#员工名字和客户名和部门名字字合并
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` #改变回传的属性名称
from `employee`
union
select `client_name`,`client_id` from `client`;
##join 连接
insert into `branch` values(4,'瑜伽',NULL);
#取得所有部门经理的名字，把employee和branch两个表格连接起来，条件是连个id一样。
select * from employee
#select `emp_id`, `name`, `branch_name` from `employee`
join `branch`
on `emp_id` = `manager_id`;
#如果不同表格有相同属性，用表格.属性名称进行区分，说明表格属性来源
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 子查询，在一个查询语句里面查询另外一个结果，就是在一个查询语句里面插入另外一个查询结果
#找出研发部门的经理名字：先找研发部门的manager_id(放在括号里面的，就是子查询的部分），再用id进行查找名字
select `name` from `employee`
where `emp_id` = (
select `manager_id` from `branch`
where `branch_name` = '研发'
);
#找出一位客户销售金额超过50000的员工名字:当子查询回传结果不止一个，要用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：如果删掉某个资料1，其他表格的资料2用外键对应的该资料1，在其他表格中把资料2设为null,当资料2为primary key时，不能设置为null，只能为on delete cascade
#on delete cascade：如果删掉某个资料1，其他表格的资料2用外键对应的该资料1，在其他表格中把资料2也删掉
#创建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
);
#创建部门表格
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');
