SQL学习

SQL学习

通过设置foreign key(外键)和其他的表关联（不仅可以跟别的表关联，也可以和自己表上的字段关联）

primary key（主键）可以设置一个或多个防止数据重复。

sql语句

create DATABASE sql_tutorial;

创建数据库,数据库名用反引号包围可以防止和关键字冲突

show databases;

展示数据库

drop database database;

删除数据库

use sql_tutorial

使用数据库

数据库数据类型

INT 整数

DECIMAL(m,n) 小数 m位数，n位小数

VARCHAR(n) n位字符串，最多能存放n个字符

BLOB 二进制文件

DATE "yyyy-mm-dd" 日期

TIMESTAMP "yyyy-mm-dd hh:mm:ss" 时间

创建表格

create table `student`(
	`student_id` INT PRIMARY KEY,
	`name` VARCHAR(20),
    `major` VARCHAR(20)
);

describe `student`;

上面创建了一个关于学生的表，其中包含了‘学生id’，‘姓名’，‘主修’三个字

pimary key(student id)

也可以作为设置主键的方法

drop table `student`

删除表格

altertable table student add gpa decimal(3,2);

增加字段

altertable table student drop column gpa;

删除字段

INSERT INTO STUDENT VALUES(1,小白)

插入值

select * from student;

查询student中的所有资料

INSERT INTO student(name,major,student_id) VALUES('小装','英語',3);

自己定义插入值的顺序

限制约束

字段名 类型 constraints

形式

not null 不许为空

unique 不许重复

default XXXX 预设为xxxx

AUTOINCREMENT 自增

注释

-- 前面加上这个整行视为注释

修改删除资料

set sql_safe_updates = 0;

首先要做这一步设置

UPDATE `student`
SET `major` = '英語文华'
WHERE `major` = '英語';

这一步可以把所有的英語替换成英語文华，也可以在where中设置多个条目。

UPDATE student
set name = 小灰,major = 物理
WHERE  student_id = 1;

设置单一条目的属性值

set major = ‘物理’

所有条目的major属性都改成物理

DELETE FROM student

WHERE xxxxx

删除条目

只写DELETE FROM student 会把表里所有资料都删除

取得资料

select * from student;

前面已经学习过了这个取得资料的方法

select name ,major from student 
(或者写成select * from student,where major = '英语'表示选出主修是英语的条目)
ORDER BY score DESC
LIMIT 3;

取得多个属性 并且按分数排序,由高到低（默认ASC由低到高）,只要前三笔。

where后的多个要用OR或AND分割或者采用WHERE major IN('历史'，'英語'，'生物)；的形式

SELECT DISTINCT `SEX` FROM `EMPLOYEE`;

不可替代的选择

创建一个新表格

下面的操作都是为了创建一个新表单为后续学习作准备

CREATE TABLE `employee`(
`emp_id` INT PRIMARY KEY,
`name` VARCHAR(20),
`birth_date` DATE,
`sex` VARCHAR(1),
`salary` INT,
`branch id` INT,
`sup_id` INT
)；

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

ON DELETE SET NULL外键对应不到就把对应的属性设成空

ALTER TABLE `employee`
ADD FOREIGN KEY(`branch_id`)
REFERENCES `branch`(`branch_id`)
ON DELETE SET NULL;

ALTER TABLE `employee`
ADD FOREIGN KEY(`sup_id`)
REFERENCES `employee` (`emp_id`)
ON DELETE SET NULL;

CREATE TABLE `client`(
`client_id` INT PRIMARY KEY,
`client_name` VARCHAR(20),
`phone` VARCHAR(20)
);

CREATE TABLE `works_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
);

ON DELETE CASCADE 外键对应不到了就删除整条数据，因为emp id既是primary key也是foreign key,所以不能用set null，因为primary 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);
INSERT INTO `employee` VALUES(207,'小绿','1985-09-16','M',29888,2,206);
INSERT INTO `employee` VALUES(208,'小兰','2000-12-19','M',35000,3,206);
INSERT INTO `employee` VALUES(209,'小白','1997-01-22','F',39888,3,207);
INSERT INTO `employee` VALUES(210,'小蘭','1925-11-10','F',84888,1,207);   

​

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,'阿狗','254354335');
INSERT INTO `client` VALUES(401,'阿猫','25633899');
INSERT INTO `client` VALUES(402,'旺來','45354345');
INSERT INTO `client` VALUES(403,'露西','54354365');
INSERT INTO `client` VALUES(404,'艾瑞克','18783783');

聚合函数

SELECT COUNT(*)
FROM `employee`
WHERE `birth_date`>'1970-01-01'AND `sex` = 'F';

计数 count()

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

取平均avg()

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

取总 sum()

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

最高最低 max() min()

万用字元

select * from `client`
where `phone` like '%567';

select * from `client`
where `phone` like '189%';


select * from `employee`
where `brith_date` like '_____10%';

%代表多个字元，_代表一个字元，在查询的时候用

查询多个属性并且合并

SELECT `emp_id` AS `total_id`,`name` AS `total_name`
FROM `employee`
UNION
SELECT `client_id`,`client_name`
FROM `client`;

利用关键字UNION

返回符合条件的

SELECT `employee`.`emp_id`,`employee`.`name`,`branch`.`branch_name`
FROM `employee`
JOIN `branch`
ON `employee`.`emp_id`=`branch`.`manager_id`

on后面是条件

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

子查询

SELECT `name`
FROM `employee`
WHERE `emp_id`=(
SELECT `manager_id`
FROM `branch`
WHERE`branch_name`='研发'
);

相当于查询了emp_id = 206的条目

select `name` from `employee`
where `emp_id`in (
  select `emp_id` from `works_with`
  where `total_sales` > 50000
  );

查询销售额大于50000的员工