Day2 MySQL修改语句

Day2_数据库初认识

设计表

1. ER图 (Entity Relationship Diagram)

   • 实体:矩形框 ->表
   • 属性:椭圆框 ->列(字段,属性,特征)
   • 关系:菱形框 ->中间表
   • 重数:一对一(1:1),多对多(m:n),一对多(1:n)

2. EER图(Extended ER图)

   • 正向工程:先设计EER图,然后根据EER图生成数据库和表.
   • 反向工程:用设计好的数据库和表生成EER图.
   • regular expression ->regexp

3. 统计学:

   • 描述型统计:能拿到全量数据

     集中趋势:均值,中位数,众数

     离散趋势:极差(ptp) ,方差,标准差

     相关性:协方差,相关系数(spearman,pearson,kindall)

   • 推断型统计:

     t检验和F检验:样本的均值和方差能不能代表总体的均值和方差

     方差分析:检查数据的改变是否是随机波动造成的,是否具体显著性

4. SQL中获取数据的描述统计信息的函数

   • sum /avg /min /max /count /stddev /variance

5. SQL 读操作格式:

   select ..., ..., ...
   from ..., ...
   where ... and ... or ...
   group by ..., ...
   having ...
   order by ... asc, ... desc
   

常用MySQL查询语句

-- 查询所有学生所有信息
select * from tb_student; 
select stu_id,stu_sex,stu_name,stu_birth,stu_addr from tb_student;  -- 推荐这种

-- 查询所有课程名称及学分(投影和别名)
select cou_name ,cou_credit from tb_course;

-- 查询所有女学生的姓名和出生日期(筛选)
select stu_name as 姓名,stu_birth as 出生日期 from tb_student where stu_sex = 0;

-- 查询所有80后女学生的姓名、性别和出生日期(筛选)
select stu_name as 姓名 ,stu_sex as 性别,stu_birth as 出身日期 from tb_student
where stu_birth between '1980-1-1' and '1989-12-31' and stu_sex = 0; 

-- 查询所有80后或者女学生的姓名、性别和出生日期(筛选)
select 
	stu_name as 姓名,
	case stu_sex when 1 then '男' else '女' end as 性别 ,
	stu_birth as 出生日期
from tb_student where stu_birth between '1980-1-1' and '1989-12-31' or stu_sex = 0;

-- MySQL方言 (因为其他的数据库可能没有if函数)
-- 例如:Oracle中做同样事情的函数叫做decode
select 
	stu_name as 姓名,
	if (stu_sex,'男','女') as 性别,
	stu_birth as 出生日期
from tb_student where stu_birth between '1980-1-1' and '1989-12-31' or stu_sex = 0;

-- 查询姓”杨“的学生姓名和性别(模糊)
-- 在SQL中通配符%可以匹配零个或者任意多个字符;
-- 在SQL中通配符_可以匹配一个字符
select stu_name ,stu_sex from tb_student where stu_name like '杨_'; -- 查询'杨X'
select stu_name ,stu_sex from tb_student where stu_name like '杨%'; -- 查询'杨','杨X',...

-- 前面带%的模糊查询性能基本上都是很差的,则会使用正则表达式查询
select stu_name ,stu_sex from tb_student where stu_name regexp '^杨.{1,2}$';

-- 查询没有录入家庭住址的学生姓名(空值)
-- null作任何运算结果也是产生null,null是相当于是条件不成立
select stu_name from tb_student where stu_addr is null;

-- 查询录入了家庭住址的学生姓名(空值)
select stu_name from tb_student where stu_addr is not null;

-- 查询学生的家庭住址(去重)
select distinct stu_addr from tb_student where stu_addr is not null;

-- 查询男学生的姓名和生日按年龄从大到小排列(排序) 
-- asc - 升序, desc - 降序
select stu_name,stu_birth from tb_student where stu_sex = 1 order by stu_birth asc;
select stu_name ,stu_birth from tb_student where stu_sex =1 order by stu_birth desc;

-- 查询年龄最大的学生的出生日期和年龄(聚合函数) -- 找出最小的生日
-- select now() -当前时间;select curdate() - 当前日期;
select 
	min(stu_birth) as 出生日期,
	floor((datediff(curdate(),min(stu_birth))/365)) as 年龄
from tb_student;

-- 查询年龄最小的学生的出生日期和年龄(聚合函数)
select 
	max(stu_birth) as 出生日期,
	floor((datediff(curdate(),max(stu_birth))/365)) as 年龄
from tb_student;

-- 查询所有学生的所有考试的平均成绩
-- 聚合函数在遇到null会自动忽略
-- 如果做计数操作,建议使用count(*) 
select avg(score) as 平均分 from tb_record;
 
-- 查询课程号为1111的课程的平均分
select avg(score) as 平均分 from tb_record where cid = 1111;

-- 查询学号为1001的学生所有课程的平均分
select avg(score) as 平均分 from tb_record where sid =1001;

-- 查询男女学生的人数(分组和聚合函数) 
-- SAC (Split - Aggregate - Combine)
select 
	if (stu_sex,'男','女') as 性别, 
	count(*) as 人数
from tb_student group by stu_sex;

-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select 
	sid as 学号,
	round(avg(score)) as 平均成绩
from tb_record group by sid;
-- 查询平均成绩大于等于90分的学生的学号和平均成绩
-- 分组之前的筛选用where子句,分组之后的筛选用having子句
select 
	sid as 学号,
	round(avg(score)) as 平均成绩
from tb_record group by sid having 平均成绩>=90;

MySQL常用操作语句

-- 插入学生数据
insert into `tb_student` 
    (`stu_id`, `stu_name`, `stu_sex`, `stu_birth`, `stu_addr`, `col_id`) 
values
    (1001, '杨过', 1, '1990-3-4', '湖南长沙', 1),
    (1002, '任我行', 1, '1992-2-2', '湖南长沙', 1),
    (1033, '王语嫣', 0, '1989-12-3', '四川成都', 1)
-- 更新数据
update tb_student set stu_name = '杨过' where stu_id =1001;
-- 删除数据
delete from tb_student stu_id =1001;