MySQL 中的数据格式
- INT
- 整数
- DECIMAL(m,n)
- 有小数的数，m是所有数字的个数，n是小数的个数
- VARCHA(n)
- 字符串
- BLOB
- 二进制文件，如图片、影片
- DATE
- 日期，‘YYYY-MM-DD’
- TIMESTAMP
- 时间，‘YYYY-MM-DD HH:MM:SS’
MySQL 中的命令书写习惯
- MySQL 自带命令关键字一般用大写表示，如
SHOU DATABASES - 用户在 MySQL 中自定义的字段名一般用 `` 符号标记，如
sql_tutorial - 要用
;在命令语句结尾标记，表示该语句已结束，如 CREATE DATABASEsql_tutorial;
1、单表格操作：
CREATE DATABASE sql_tutorial;
该命令表示创建数据库
SHOW DATABASES;
该命令表示显示所有数据库
DROP DATABASE sql_tutorial;
该命令表示删除数据库
USE sql_tutorial;
该命令表示使用数据库
DESCRIBE TABLE 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;
在表格中删除名位
gpa的列
ALTER TABLE student MODIFY COLUMN gpa DECIMAL(5,2);
修改
gpa列的数据类型
ALTER TABLE student
ADD PRIMARY KEY (student_id);
添加PRIMARY KEY
ALTER TABLE `student` # 修改已经存在的表结构
ADD CONSTRAINT fk_customer # 增加一个约束，命名为fk_customer方便记忆
FOREIGN KEY (major) # 指定student中的 major 字段作为外键，
REFERENCES customers (major); # 指明参照表及其参照字段，这意味着student中的major的值必须在另一个表中的指定字段**存在**——即为约束。
添加 FOREIGN KEY 。外键本身就是一种约束，闲置了表中可插入的数据值
插入数据
INSERT INTO student VALUES(1,‘小白’,‘历史’);
省略列名的插入数据
INSET INTO student (name,major,student_id) VALUES(‘小蓝’,‘英语’,4);
不省略列名的插入数据（列名顺序可调）
注意，可以同时插入多条数据，数据之间用 (),() 分隔。
可以自己定义顺序，如果没有写某个属性，就把该行资料的该属性定义为NULL
查询数据
查询数据使用 SELECT 命令：
SELECT column1, column2, ...
FROM table_name
[WHERE condition] # 用于添加过滤条件
[ORDER BY column_name [ASC | DESC]] # 用于指定结果的排序顺序，默认是升序(ASC)
[LIMIT number]; # 用于限制返回的行数
SELECT * FROM student;
*表示获取所有数据
SELECT major , name FROM student;
读取 student 表中的特定的列
修改资料
UPDATE `student`
SET SQL_SAFE_UPDATES = 0; #关闭预设自动更新
UPDATE `student`
SET `major` = '英语文学'
WHERE `major` = '英语';
把字段 “英语” 改为 “英语文学”
UPDATE `student`
SET `major` = '英语文学'
WHERE `student_id` = 3;
把第三个数据的 major 字段改为 “英语文学”
UPDATE `student`
SET `major` = '生数'
WHERE `major` = '生物' OR `major`= '数学';
把"生物"或"数学"改为"生数"
UPDATE `student`
SET `major` = '英语文学',`name` = '小灰'
WHERE `student_id` = 1;
把第一个数据的 major 和 name 都改了
UPDATE `student`
SET `major` = '英语文学';
不写 WHERE 条件，默认修改所有 major 字段
删除数据
DELETE FROM `student`
WHERE `student_id` = 4;
把编号为 4 的学生删除
DELETE FROM `student`
WHERE `name` = '小红' AND `major` = '英语文学';
可以用 ADN 并列多个条件
DELETE FROM `student`
WHERE `score` < 71;
可以使用判断语句
限制、约束，预设值
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY, # 表示限制该字段为主键
`name` VARCHAR(20) NOT NULL, # 表示限制该字段为不能空白
`major` VARCHAR(20) DEFAULT '历史' # 表示限制，该字段的默认属性为 "历史"，增加数据时可不填
);
在创建表时增加限制的方法。还有
UNIQUE关键字，表示限制该字段不能重复。AUTO_INCREMENT, id 自动增加，输入时不写 id 不会报错
DROP TABLE student;
CREATE TABLE `student`(
`student_id` INT AUTO_INCREMENT, #AUTO_INCREMENT 表示该主键会自动增加，不用在输入时写id
`name` VARCHAR(20) NOT NULL,
`major` VARCHAR(20) DEFAULT '历史' ,
PRIMARY KEY(`student_id`) # 可以在末尾这样设定主键
);
条件查询
排序 ORDER BY 由低到高；ORDER BY 属性 DESC 由高到低；ORDER BY 属性1,属性2 先按属性1排，如果1一样，再按2排序
在 WHERE 子句中，你可以使用各种条件运算符（如 =, <, >, <=, >=, !=），逻辑运算符（如 AND, OR, NOT），以及通配符（如 %）等。
在 ORDER BY 子句中，ASC 表示升序，DESC 表示降序。可以多列排序：ORDER BY table_id ASC, date DESC; 按顺序，先按 id 排序，再按 date 排序。
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
);
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_idin (
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’);