一、创建数据库
create database if not exists mydb2 default charset charset utf8 collate utf8_general_ci;
二、创建表
create table if not exists user( # if not exists 可以去掉
user_id int unsigned auto_increment, # 自动升序
user_name VARCHAR(100) not null,
birthday date,
primary key(user_id) # 设置主键
) engine=INNODB DEFAULT CHARSET=utf8; #选择字符集
三、三范式设计与反范式设计
1.什么是范式?
范式是符合某一种级别的关系模式的集合。构造数据库必须遵循一定的规则。在关系数据库中,这种规则就是范式。
2.第一范式:要求属性具有原子性,即列不可再分解
举例:学生表中包含(学号、姓名、性别、出生日期)
3.第二范式:列与列之间不存在部分依赖关系,要有主键且每列都与主键相关
错误举例:学生表中包含(学号、姓名、课程号、成绩)成绩依赖于课程
存在的问题:数据冗余,学号与课程号是多对多的关系
正确的做法:拆成学生表、课程表和选课表三张表
4.第三范式:列与列之间不存在传递依赖关系
举例:表中包含学号、姓名、年龄、所在学院、学院电话
5.什么是反范式设计
表设计时以业务应用场景为主,不完全遵守三范式设计规范
四、插入数据语法
1.新增一行数据(效率较低)
insert into 表名(字段名1,字段名2,...字段名N)
VALUES(值1,值2,...值N);
2.新增多行数据
insert into 表名(字段名1,字段名2,...字段名N)
VALUES
(值1,值2,..值N),
(值1,值2,..值N),
(值1,值2,..值N);
五、更新表中全部数据
update 表名 set 列1=新值1,列2=新值2 where 过滤条件;
同时改两个人的信息,用 or
updata user set sex=1 where user_name='小强' or user_name='韩梅梅'
updata user set user_name='xiaoqang' , sex=0 where user_id=5
六、数据删除
删除表中所有数据(右键清空表) : delete from 表名 where 字段名=值名
删除表: drop table [if exists] 表名;
清空表中数据(右键截断表): truncate table user; # 再添加的话id主键重置
七、数据查询
select *from 表名 where 字段名 like ”关键字%关键字“ 模糊查询
select *from 表名 where 字段名 like ”李_“ 查询出姓李的,并且名字只有两个字的同学
SELECT * FROM cme_phone_card where iccid in # int类型不用加引号
('8986112126103311790', 其他类型加上引号。
'8986062133000350439', 也可以用 not in
'898604B21521D0299998',
'898604B21521D0299999')between.. and...
select * from student limit 3,5 # 跳过id前三条,查询出5条信息
如果只有一个数字,就是直接筛选出几条数据
and 和 or 同时出现 就按前后先后执行
case 比较的意思
update user set sex =
( case sex
when 1 then 0 # 比较性别这一列,值为1的时候变成0
else 1 #其余为0的变成1、结束
end
)
八、高级查询
1.左关联:以左表为基础全部显示出来,右侧表只显示出来和左表能产生关联的数据
select s.id '学校id',
s.school_name '学校名称',
s.school_address '学校地址',
g.id '班级id',
g.school_id '所属学校id',
g.grade_name '班级名称'
from
school as s left join grade as g
on s.id = g.school_id
2.右关联:以右表为基础全部显示出来,右侧表只显示出来和左表能产生关联的数据
select s.id '学校id',
s.school_name '学校名称',
s.school_address '学校地址',
g.id '班级id',
g.school_id '所属学校id',
g.grade_name '班级名称'
from
school as s right join grade as g
on s.id = g.school_id # 关联条件
3.全关联:只显示两个表都符合条件的数据
select s.*,g.*
from school s
join grade g
on s.id = g.school_id
4.子查询
select * from school where id #查询出有年级的学校(有的学校没有年级)
in
(select school_id from grade) id = (select school_id from gread)
查询出六年级,全体同学的数学分数
select * from score where course_id =
(select * from course where grade_id
in
(select id from grade where grade_name = '六年级')
and course_name = '数学'
)
5.联合查询:多表查询,所查询的字段个数必须相同
select 字段名 from 表名 where 筛选条件
union
select 字段名 from 表名 where 筛选条件
数据量大的时候一个表会拆成多个表
6.查询结果排序 order by
select * from score order by score asc #asc 正序排列 可以省略
select * from score order by score desc #倒叙
7.分组查询与筛选 group by
select count(id) '课程出现的次数'
, course_name
from course
group by course_name
having count(id)>5
8.自关联查询
select a.name
from salary a
join salary b
on a.manager_id = b.id
and a.salary > b.salary
9.聚合函数
1.查询每个学生的总分
select sum(score) , student_id
from score
group by student_id
2.查询每个学生的最高分
select max(score) , student_id
from score
group by student_id
3.平均分
select avg(score) , student_id
from score
group by student_id
3.最小值
select min(score) , student_id
from score
group by student_id