MySQL
一、字段 增删改
1.删除字段
公式:alter table 表名 drop column 字段名
2.新增字段
公式:alter table 表名 add 字段名 字段类型 备注
例:alter table `szd_wx_menu` add text varchar(500) comment '文本信息的内容';
3.修改
3.1修改字段名(change方法)
公式:alter table 表名 change column 原字段名 新字段名 字段类型 备注
3.2修改字段信息(modify方法)
公式:alter table 表名 modify 字段名 字段类型 备注
二、数据 增删改
1.新增
INSERT INTO student VALUES
(NULL,'马邦德','男',1010,4),
(NULL,'张麻子','男',1011,4);
INSERT INTO score VALUES
(1007,5,59),(1007,4,69),(1007,3,49),
(1008,1,99),(1008,2,79),(1008,3,100);
2.修改
修改数据用update关键字使用,注意的是update经常和where一起使用
-- 修改所有男同学的成绩,让成绩+10
UPDATE score SET score=score+10
WHERE student_id IN (SELECT id FROM student WHERE gender='男');
3.删除
删除用的是delete 关键字实现
公式:delete from 表名 where 条件
-- 删除myname为小红的数据
DELETE FROM `aaa` WHERE `myname`='小红';
说到删除就必须说到数据删除表数据常用的另一个关键字truncate
truncate语句很简单,就是后面跟一个表名即可
-- 删除aaa表中所有的数据
TRUNCATE aaa;
如果我们要删除表中所有数据的话,一般使用truncate
delete 和 truncate的区别
truncate会重新设置自增序列,计数器归零,不会影响事务
delete不会重新设置自增序列
三、聚合函数
1.select count(*) from table;这个是统计查询出来的数据数量
2.select min(id) from table ;取出数据中id最小的值
3.select max(id) from table;取出数据中id最大的值
4.select MOD(125,10);取余数
5.select floor(columns) from table where condition; 从取出的数据中向下取整,比如你取到的数据是45.8,那么通过floor函数处理之后,打印出来的就是45
6.select ceil(columns) from table where condition;从取出的数据中向上取整,比如你取到的数据是45.8,那么通过ceil函数处理之后,打印出来的就是46
7.select round(columns,num) from table where condition;这个是四舍五入取数,第一位参数就是取到的数据,第二位可以设置保留几位小数,例如round(45.66,1),打印出来就是45.7。
8.select avg(id) from table; 从取出的数据中算出平均数打印出来。默认保留四位小数。
9.select substr(string,start,length) from table;从取出来的数据中截取从start(start>=1)开始,length个长度的字符串,然后打印出来。
10.select left(string,length) from table;从取出来的数据中,从左第一位往右截取length个长度,然后打印出来。
11.select rigth(string,length) from table;从取出来的数据中,从右最后一位,往前截取length个长度,然后按从左往右的顺序打印出来。
12.select length(string) from table;获取数据的字节长度,一个汉字为3个字节。
13.select upper(string) from table;将获取的字母数据全部大写。
14.select lower(string) from table;将获取的字母数据全部小写。
15.select concat(string1,string2,string3,…)from table;可以将字符串拼接在一起。
四、分组
语法:
select 列名,... from 表
group by 列1,列2,...
having 过滤条件; -- 对分组后聚合函数得到的数据进行条件过滤
select count(*) from emp_info;
select * from emp_info WHERE gender ='男';
select * from emp_info WHERE gender ='女';
-- 如果没有
select gender, count(*) from emp_info
GROUP BY gender;
-- 查询 每个部门 不同性别 不同出生世纪 的员工的平均工资
SELECT depte_name, gender, avg(salary)
FROM emp_info GROUP BY depte_name, gender;
SELECT depte_name, gender,
CASE
WHEN YEAR(birthday) <2000 THEN '20世纪'
ELSE '21世纪'
END as century, avg(salary)
FROM emp_info
GROUP BY depte_name, gender, century;
-- 部门人数大于5人的部门名称
-- having 定义分组以后的过滤条件
SELECT depte_name, count(*)
from emp_info
GROUP BY depte_name
having count(*)>10;
-- 平均工资小于20000大于10000的部门
sql编写顺序
select
from
where
group by
having
order by
limit
-- 部门人数大于5人的部门名称
-- having 定义分组以后的过滤条件
SELECT depte_name, count(*)
from emp_info
GROUP BY depte_name
having count(*)>10;
sql执行顺序
-
from
-
where
-
group by
-
having
-
select
-
order by
-
limit
-- 男员工平均工资大于20000的部门名称
SELECT depte_name, avg(salary) asa
from emp_info
WHERE gender = '男' -- 性别为男的条件过滤 必须写在这里
GROUP BY depte_name
HAVING asa >20000
ORDER BY asa DESC;
limit 分页
由于一个表中的数据可能是几万十几万条,我们不可能每一次都把全部查询出来,这样太消耗性能。
而是一次查询指定条的数据(10,20),第一个几条数据,我们可以说第一页的数据。
-- limit 起始行的下标, 查询的条数
-- mysql第一行的下标为0,依次递增1
-- 包含起始行
SELECT * FROM emp_info;
SELECT * FROM emp_info LIMIT 0,3; -- 123
SELECT * FROM emp_info LIMIT 3,3; -- 456 789
SELECT * FROM emp_info LIMIT 6,3;
-- 第一个五条 第二个五条 第三个五条
SELECT * FROM emp_info LIMIT 0,5;
SELECT * FROM emp_info LIMIT 5,5;
SELECT * FROM emp_info LIMIT 10,5;
-- 分页概念 查询第1235个五条
SELECT * FROM emp_info LIMIT 0,5; -- 第一页 (1-1)*5,5
SELECT * FROM emp_info LIMIT 5,5; -- 第二页 (2-1)*5,5
SELECT * FROM emp_info LIMIT 10,5; -- 第三页 (3-1)*5,5
-- 总结规律: 查询第n页,一页显示m条数据 limit (n-1)*m,m
-- 一页显示3条数,查询第8页
SELECT * FROM emp_info LIMIT 21,3;
-- 一页显示4条数,查询第5页
SELECT * FROM emp_info LIMIT 16,4;
五、子查询
一个查询的结果是另一个查询的条件
-
查询是一个具体的值(一行一列)
-
= , >, <, >=, <=
SELECT salary FROM emp_info WHERE name = '小李'; SELECT * FROM emp_info WHERE salary >(SELECT salary FROM emp_info WHERE name = '小李');
-
-
查询的结果是多个值(多行一列)
-
in
-- 查询姓李员工的信息 -- 子查询:一个查询的结果是另外一个查询的条件,结果为一列多行 -- 此时可以使用in关键字,表示等于任何一行的值都是满足条件的 SELECT id FROM emp_info WHERE `name` LIKE '李%'; SELECT * FROM emp_info WHERE id in (SELECT id FROM emp_info WHERE `name` LIKE '李%');
-
六、表关系
mysql是关系型数据库,表和表是有关系的
-
班级表
-
主键 班级名称
-
1 java2203
-
2 java2202
-
3 java2201
-
-
课程表
-
主键 课程名称 老师id
-
1 java 1
-
2 军事 3
-
3 心学 3
-
4 谋略 2
-
-
老师表
-
主键 老师姓名
-
1 詹姆斯高斯林
-
2 曹操
-
3 王守仁
-
-
学生表
-
主键 姓名 性别 学号 班级id
-
1 马云 男 0001 1
-
2 李云 女 0002 1
-
3 王云 男 0001 3
-
-
学生科目关联表(成绩表)
-
学生id 科目id 成绩
-
1 1 59
-
1 2 80
-
2 2 90
-
一对多,多对一
-
一个学生只能对应一个班级,多个学生可以对应同一个班级
-
从学生到班级,是一个对应一个
-
从班级到学生,是一个可以对应多个
这种关系就叫做一对多,多对一
-
员工和部门之间的关系
-
人和家乡之间的关系
如果维护(记录)他们之间的关系:就在多的那一方,添加一列记录所属班级的主键
多对多
-
一个员工对应多个职位,多个员工对应同一个职位
-
从员工表到职位表,一个员工对应多个职位
-
从职位表到员工表,一个职位对应多个员工
这种关系就是多对多
表设计:添加一个关联表,表一般会有两个字段,分别是两个表的主键id
一对一
一对一的关系,在任何一个表中,添加对方表的主键id字段都可。
七、关联查询
1. 内连接
语法:
-- 标准写法
select * from 表1 inner join 表2 on 关联条件;
select * from 表1
inner join 表2 on 关联条件1
inner join 表3 on 关联条件2
inner join 表4 on 关联条件3
inner join 表5 on 关联条件4
....
inner join 表n on 关联条件n;
-- 简化写法
select * from 表1, 表2, 表3,...表n
where 关联条件1 and 关联条件2 and 关联条件3 ... and 关联条件n;
-
表1和表2中任意两条数据只要符合关联条件那么就会拼接成一条数据,作为本次查询的结果
-
当多表关联查询时,就是n个表中的n条数据,如果满足这n个关联条件,就会组成一条数据,作为本次查询的一条结果。
-
如果一个表的一条数据,在关联表中没有符合条件的数据,那么此条数据就不会被查询出来
-- 查询 学生姓名 性别 学号 所在班级
SELECT * FROM clazz
INNER JOIN student ON student.class_id = clazz.id;
SELECT * FROM student;
SELECT * FROM clazz;
-- 查询 学生姓名 性别 学号 所在班级
SELECT * FROM clazz
INNER JOIN student ON student.class_id = clazz.id;
-- 如果两个关联表有相同名称的列名,在查询时,需要注明查询的是哪个表中
SELECT student.`name`, student.gender, student.`code`,
clazz.`name` FROM clazz
INNER JOIN student ON student.class_id = clazz.id;
-- 为关联查询的表起别名 一旦起了别名 就必须使用别名
SELECT s.`name`, s.gender, s.`code`, c.`name`
FROM clazz c
INNER JOIN student s ON s.class_id = c.id;
-- 查询 科目名称及代课老师
SELECT * FROM `subject` ;
SELECT * FROM teacher ;
SELECT * FROM `subject` s INNER JOIN teacher t ON s.teacher_id = t.id;
-- 学生姓名 学号 性别 学习科目 成绩 代课老师
-- 这些列 分别在哪些表中 学生表 科目表 成绩表 老师表
-- 根据关联条件把这表关联起来(我怎么知道关联条件是啥,你不知道是啥,你写啥)
SELECT * FROM student s
INNER JOIN score sc ON s.id = sc.student_id
INNER JOIN `subject` su ON sc.subject_id = su.id
INNER JOIN teacher t ON su.teacher_id = t.id;
SELECT s.`name`, s.gender, s.`code`, su.`name`, sc.score, t.`name`
FROM student s
INNER JOIN score sc ON s.id = sc.student_id
INNER JOIN `subject` su ON sc.subject_id = su.id
INNER JOIN teacher t ON su.teacher_id = t.id;
-- 简化写法
SELECT s.`name`, s.gender, s.`code`, su.`name`, sc.score, t.`name`
FROM student s ,score sc ,`subject` su, teacher t
WHERE s.id = sc.student_id and sc.subject_id = su.id and su.teacher_id = t.id;
-- 学生姓名 学号 性别 学习科目 成绩 代课老师 所在班级
-- 涉及到哪些表,关联条件一加,选择显示的列 结束
2. 外连接
-
左外连接
-
left join 的左边是主表
-
主表的数据是一定要被查询出来,无论副表有无关联数据
-
-
left join 的右边是副表
-
副表如果和主表有关联数据,则会被查询出来,如果没有,全部以null值填充
-
-
-
右外连接
select * from 表1 left join 表2 on 关联条件;
select * from 表1
left join 表2 on 关联条件1
left join 表3 on 关联条件2
...
left join 表n on 关联条件n;
select * from 表1 right join 表2 on 关联条件;
-- 查询每个班级的人数
SELECT *
FROM student s , clazz c
WHERE c.id = s.class_id
-- 外连接
SELECT * FROM clazz c LEFT JOIN student s ON c.id = s.class_id;
SELECT * FROM student s RIGHT JOIN clazz c ON c.id = s.class_id;
SELECT c.id, c.name, count(s.id)
FROM clazz c LEFT JOIN student s ON c.id = s.class_id
GROUP BY c.id, c.name;
-- 每个老师 姓名 教授科目的个数
SELECT t.id , t.`name`, count(s.id)
FROM teacher t
LEFT JOIN `subject` s ON t.id = s.teacher_id
GROUP BY t.id , t.`name`;
-- 每个老师 姓名 教授学生数
-- count(DISTINCT sc.student_id) 统计student_id不相同的条数
SELECT t.id, t.`name`, count(DISTINCT sc.student_id)
FROM teacher t
LEFT JOIN `subject` s ON t.id = s.teacher_id
LEFT JOIN score sc ON s.id = sc.subject_id
GROUP BY t.id, t.`name`;
-- 每个学生学习的科目数
SELECT s.id, s.`name`, count(sc.subject_id)
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
GROUP BY s.id, s.`name`;
八、子查询
-
一个查询的结果是另外一个查询的条件
-
结果可以为一个值(一行一列) = > < <= <>
-
结果为多行一列 in
-
-
一个查询是另外一个查询的数据源(表)
select * from (子查询) 表名1, (子查询) 表名2, 表3 where 关联条件1 and 关联条件2 and ... -- 查询 java 成绩比 马云 高的同学的信息 -- 姓名,学号,性别,成绩 SELECT s.`name`, s.`code`, sc.score FROM student s, `subject` su, score sc WHERE s.id = sc.student_id AND sc.subject_id = su.id and su.`name` = 'java' AND sc.score > (SELECT sc.score FROM student s, `subject` su, score sc WHERE s.id = sc.student_id AND sc.subject_id = su.id AND s.`name` = '马云' and su.`name` = 'java'); -- 查询学了马云学习科目至少有一门相同的学生信息 姓名,学号,班级 SELECT s.* FROM student s, score sc WHERE s.id = sc.student_id AND sc.subject_id in(SELECT sc.subject_id FROM student s, score sc WHERE s.id = sc.student_id AND s.`name` = '马云') AND s.`name` <> '马云'; -- java成绩比心学成绩好的同学 SELECT s.*, t1.score, t2.score FROM (SELECT student_id, score FROM score sc, `subject` su WHERE su.id = sc.subject_id AND su.`name` = 'java') t1 , (SELECT student_id, score FROM score sc, `subject` su WHERE su.id = sc.subject_id AND su.`name` = '心学') t2, student s WHERE t1.student_id = t2.student_id AND t1.score > t2.score AND t1.student_id = s.id
九、数据库操作
1.添加
insert into 表名(列1, 列2,列3,...) value(值1, 值2,值3,....); insert into 表名 value(值1, 值2,值3,....); insert into 表名(列1, 列2,列3,...) value(值1, 值2,值3,....),(值1, 值2,值3,....),...;
-
列和值一一对应
-
主键可以赋值为0或者null,让其自增
-
列可以省略,默认为创建表时,定义的列顺序和个数一致
-- 999 易烊千玺 男 999777 4 -- 本质就是添加一条数据 -- 添加一条数据,就是为一条数据中列赋值 -- 添加的值和列一一对应 INSERT INTO student(id,name,gender,code, class_id) VALUE(999,'易烊千玺','男','999777',4); -- 主键不用赋值,自增 主键这一列可以赋值为 0, NULL INSERT INTO student(id,name,gender,code, class_id) VALUE(null,'彭于晏','男','999982',4); -- 可以只对指定列赋值(没有赋值的列需要允许为空,或者有默认值) INSERT INTO student(id,name) VALUE(null,'葛优'); -- 可不定义列,默认列的顺序和定义表时,列的顺序一致 INSERT INTO student VALUE(null,'姜文','男','555777',2); -- 一次可以添加多条 一个小括号代表一条数据,括号和括号之间使用逗号分隔 INSERT INTO student VALUE (null,'刘德华','男','333',1), (null,'古天乐','男','222',3); -- 添加两条学生记录 为每个学生添加3门课程的成绩
2. 修改
update 表名 set 列1 = 值1, 列2 = 值2, ... where 过滤条件;
-
修改符合过滤条件的条数据列的值
-- 修改一个表一列的数据 -- 本质上修改所有符合条件的哪些条此列的数据 UPDATE student SET `name`='马云云'; -- 可以添加过滤添加 使用where定义过滤添加 UPDATE student SET `name`='齐德龙' WHERE gender = '男'; -- 修改多列, 列和列之间使用逗号分隔 UPDATE student SET `name`='齐东强', `code` = CONCAT(`code`,'_qf') WHERE gender = '男'; -- 把男同学,所有的分数加10
3. 删除
delete from 表; -- 清空表 delete from 表 where 过滤条件; -- 删除符合条件的数据
-
正式开发中,这种删除叫做物理删除,我们一般选择状态删除
-- 把性别为null删除 DELETE FROM student WHERE gender is null;
十、约束
-
主键不能重复
-
字段长度
-
数据类型
-
是否为空
1. 外键约束
-
约束了该列的取值范围
-
外键也不能随意删除
2. 索引
索引的作用就是为了查询速度快,会影响添加,修改的速度
索引当你的查询速度慢的时候,你想到的第一件时间,就是为列添加索引
一个表添加多少索引是合适的呢?添加6个左右
2.1 索引的类型
-
普通索引
-
Nomal
-
-
唯一索引
-
Unique
-
-
全文索引
-
full text
-
-
主键索引
-
主键也是一种索引
-
-
组合索引
-
有联合主键,也就有联合索引
-
2.2 索引的方式
-
hash
-
之能使用等值查询 = 时间复杂度0
-
-
b-tree 平衡二叉树
-
范围查询 (< >) 后模糊查询
-
十一、创建表
CREATE TABLE 表名( 类名 数据类型 长度 是否为空 约束, 类名 数据类型 长度 是否为空 约束, 类名 数据类型 长度 是否为空 约束 ); CREATE TABLE stu( id int not NULL auto_increment, name varchar(20) not null, gender char(1), code varchar(10), class_id int, PRIMARY key(id) ); CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `gender` char(1) DEFAULT NULL, `code` varchar(10) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), -- 设置主键 UNIQUE KEY `code` (`code`) USING HASH, --设置唯一索引 KEY `xxx` (`name`) USING HASH, --设置普通索引 KEY `class_id` (`class_id`), --设置外键 CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `clazz` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
-