SQL语言1

 创建数据库 CREATE DATABASE

展示数据库 SHOW DATABASE

整数 INT 

有小数点的数 DECIMA(m, n) #m是有几位数，n是有几位小数

字符串 VARCHAR(n)

（Binary Large Object）图片 影片 BLOB

‘YYYY-MM-DD’日期  DATA

'YYYY-MM-DD HH:MM:SS' 记录时间 TIMESTAMP

数据库中加入表格

CREATE DATABASE `sql_tutorial`;
SHOW DATABASES;
USE `sql_tutorial`;

CREATE TABLE student(
  `student_id` INT PRIMARY KEY,
  `name` VARCHAR(20)，
    `major`VARCHAR(20)
  --PRIMARY KEY(student)
);
 DESCRIBE `student`;--显示表格
DROP TABLE 'student'; --删除表格

ALTER TABLE `student` ADD gpa DECIMAL(3,2); --增加属性
ALTER TABLE `student` DROP COLUMN gpa；--删除属性

--存入数据
INSERT INTO `student` VALUES(1,'小白'，'历史');
INSERT INTO `student` ('name','major','student_id')VALUES('小白'，'历史',1);
SELECT * FROM 'student'--列出全部数据

--constraints 限制

CREATE TABLE student(
  `student_id` INT PRIMARY KEY,
  `name` VARCHAR(20) NOT NULL，--数据不能为空
    `major`VARCHAR(20) UNIQUE,--数据不能重复
  `major_1`VARCHAR(20) DEFAULT'历史',--新增预设值
  `name` VARCHAR(20) AUTO_INCREMENT，--自动增加
);

--删除表格
DROP TABLE `student`

SET SQL_SAFE_UPDATAS = 0; --关闭预设模式

--更改表格内容
UPDATA `student` SET `major` = `英语文学` WHERE `major` = `英语`;

UPDATA `student` SET `major` = `生化` WHERE `major` = `生物` OR `major` = `化学`;

UPDATA `student` SET `name`=`小惠`，`major` = `生化` WHERE `student` =1 ;

UPDATA `student` SET `name`=`小惠`；--name属性全改为小惠

--删除数据

DELETE FROM `student` WHERE `name`=`小惠` ;

DELETE FROM `student` WHERE `score`>60;

DELETE FROM `student` --删除整个表格

--取得资料，排序
SELECT `name`,`major` FROM `student`；
SELECT * FROM `student`；--取得所有数据
SELECT * FROM `student` ORDER BY `score` ASC; --若什么都没写则预设ASC，由低到高。DESC由高到低。asc(ascend )是升序，就是这个值按照从小到大进行排序；desc(descend)是降序，就是这个值按照从大到小进行排序。

SELECT * FROM `student` ORDER BY `score` ,`student_id`;--先根据score做排序，如果score相同，再根据student_id做排序。
SELECT * FROM `student` LIMIT 2；--回传前两笔资料。
SELECT * FROM `student` ORDER BY `score` LIMIT 2; --先按照score做排序，然后回传前两笔资料。
SELECT * FROM `student` WHERE`major`=`英语`OR`score`>=70;--回传主修是英语或者分数大于等于70
SELECT * FROM `student` WHERE`major` IN（`历史`,`英语`,`生物`）；

大于 >

小于 <

大于等于 >=

等于=

不等于<>

-- 创建公司资料表格

 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 `employee` VALUES(206,'小黄','1998-10-08','F',50000,1,NULL); 


--把原本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'); 

--增加work_with 信息
NSERT INTO `work_with` VALUES(206,400,70000);

--取得员工资料
SELECT * FROM`employee`;

--按薪水由低到高取得员工资料
SELECT * FROM `employee`OREDER BY`salary`;
--取得薪水前3高的员工
SELECT * FROM `employee`OREDER BY`salary`DESC LIMIT3;
--取得所有员工的名字
SELECT `name` FROM `employee`;
--取得所有员工的性别
SELECT DISTINCT `sex`FROM`employee`--distinct显示不重复的。
--取得客户资料
SELECT * FROM`client`;
--取得员工人数
SELECT COUNT(`sup_id`)FROM`employee`；--输出是员工数量


--取得所有出生在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`; 

--widcards 万用字元 %代表多个字元；_代表一个字元
--1、取得电话号码尾数是335的客户
SELECT * FROM `client` WHERE `phone` LIKE`%335`;
--2、取得电话号码开头是245的客户
SELECT * FROM `client` WHERE `phone` LIKE`245%`;
--3、取得电话号码中间是245的客户
SELECT * FROM `client` WHERE `phone` LIKE`%245%`;
--4、取得姓艾的客户
SELECT * FROM `client` WHERE `client_name` LIKE`艾%`;
--取得生日在10月的员工

SELECT * FROM `employee`WHERE `brith_date` like '_____10%'; --五个_

--union 连集 可以合并搜寻的结果，合并的属性资料形态要一样，不同表格的属性数目要一样，不能用表格1的2个属性去和表格2的1个属性合并

--员工名字和客户名和部门名字字合并 

SELECT `name` FROM `employee` UNION SELECT `client_name` FROM `client`;

SELECT `name` FROM `employee` UNION SELECT `client_name` FROM `client`UNION SELECT `branch_name` FROM `branch`;

--员工id+员工名字UNION 客户id+客户名字
SELECT `emp_id`,`name` FROM`employee`UNION SELECT `client_id`,'client_name' FROM`client`;
--员工id+员工名字UNION 客户id+客户名字,并且改名字
SELECT `emp_id`AS`total_id`,`name`AS`total_name` FROM`employee`UNION SELECT `client_id`,'client_name' FROM`client`;

--员工薪水union销售金额
SELECT `salary` FROM`employee`UNION SELECT `total_sales` FROM`works_with`;

--join 链接 在join左边是左边表格，在join右边是右边表格，右边表格条件成立才回传资料，左边表格全部资料都会回传，没有对应右边的，则显示NULL
--right join则相反
INSERT INTO `branch`VALUES(4,`偷懒`，NULL);--新增一笔资料
--取得所有部门经理的名字
SELECT `emp_id`,`name`,`branch_name` FROM `employee` JOIN `branch` ON `emp_id`=`manager_id`；--在属性前面加上表格名称。`employee`.`emp_id`,防止多个表格存在相同属性


--subquery 子查询
--1、找出研发部门的经理名字
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;（对应不到，设置为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 --primary ley同时又是foreign key不能设置为null 

 );

 --创建部门表格

 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');