VIP31/mysql/day02.sql

-- 删除stu表
drop table if exists stu;

-- 创建stu表
CREATE TABLE stu (
     id int, -- 编号
     name varchar(20), -- 姓名
     age int, -- 年龄
     sex varchar(5), -- 性别
     address varchar(100), -- 地址
     math double(5,2), -- 数学成绩
     english double(5,2), -- 英语成绩
     hire_date date -- 入学时间
);


-- 添加数据
INSERT INTO stu(id,name,age,sex,address,math,english,hire_date)
VALUES
    (1,'马运',55,'男','杭州',66,78,'1995-09-01'),
    (2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
    (3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
    (4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
    (5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
    (6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
    (7,'张学右',22,'女','香港',99,99,'1998-09-01'),
    (8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');


# 基础查询
SELECT  * FROM stu;  # 查询所有

# 名称
SELECT name FROM stu;

#别名  as 省略 表别名
SELECT u.id , u.name , u.sex  FROM stu u;

# 字段别名
SELECT s.id s_id , s.name s_name , s.sex  FROM stu s;

# 去重复
SELECT DISTINCT sex FROM stu;

# select 数据库版本
SELECT version();


# 条件查询
# WHERE 条件
# 并且  AND && 不推荐
# 或者  OR  || 不推荐

# 查询年龄大于等于20
SELECT *
FROM stu
WHERE age >= 20;

# 多条件
SELECT *
FROM stu
WHERE age >= 20 AND sex = '男';

# 条件
#  =  !=  <  <=  >  >=
# null 条件  IS NULL 等于null IS NOT NULL

# 英语成绩为null
SELECT * FROM stu WHERE english IS NULL;
# 英语成绩不为null
SELECT * FROM stu WHERE english IS NOT NULL;

# <=> 安全等于 了解
SELECT * FROM stu WHERE english <=> null;
# <> 不等于
SELECT * FROM stu WHERE stu.math <> 86;
SELECT * FROM stu WHERE stu.math != 86;

# 多个条件 同一条件 年龄   20 - 50
# age >= 20 AND age <= 50

# BETWEEN ... AND ...
SELECT * FROM stu WHERE age BETWEEN 20 AND 50;

# 查询年龄 在一个 集合 区间 (20,30,40,50)
# IN 在集合中

SELECT * FROM stu WHERE age IN( 20,30,40,50);

SELECT * FROM stu WHERE age NOT IN( 20,30,40,50);

# 查询姓张的 学生
# 模糊查询 LIKE
# % 任意多个字符
# _ 任意一个字符

# 查询姓张的 学生
SELECT * FROM stu WHERE name LIKE '张%';

# 第二字符 是 学
SELECT * FROM stu WHERE name LIKE '_学%'


# 排序查询
# 默认 升序
# 升序 ASC
SELECT * FROM stu  ORDER BY english ASC;

# 降序 DESC
SELECT * FROM stu  ORDER BY english DESC;

# 英语成绩相同 age 排序
SELECT * FROM stu  ORDER BY english DESC ,age DESC ;

# 条件
SELECT * FROM stu WHERE 1=1  ORDER BY english DESC ,age DESC ;

# 聚合函数
/*
sum() 求和
avg() 平均值
min() 最小值
max() 最大值
count() 数量
 */

# 求和
SELECT SUM(english) FROM stu;  # 有一个null值

SELECT SUM(math) FROM stu;

# 数量
SELECT COUNT(english) FROM stu; # COUNT 不统计null
SELECT COUNT(math) FROM stu;

# COUNT(*)
SELECT COUNT(*) FROM stu;

# avg
SELECT AVG(english) FROM stu;

# 条件
#  找表 stu 条件  返回结果
SELECT COUNT(*) FROM stu WHERE math > 80;



# 分组查询
# 注意 select 必须是 分组函数和group by 后面的字段 和 聚合函数
# 配合聚合函数使用
SELECT address FROM stu GROUP BY address;

# 聚合函数
SELECT address,COUNT(*) FROM stu GROUP BY address;

# 聚合函数后的条件过滤  HAVING
# 注意 HAVING 必须配合 GROUP BY 使用
# 在分组之后 才能 HAVING 二次过滤
SELECT address,COUNT(*) FROM stu GROUP BY address HAVING  COUNT(*) >= 2 ;

# 别名
SELECT address,COUNT(*) c FROM stu GROUP BY address HAVING  c >= 2 ;

# where 第一次过滤
# having 第二次过滤 在group by后面
# where 能用 where 不用 having


# 分页查询
# LIMIT 最后

# LIMIT 常量数字 获取几条数据
# 获取 age 最大的
SELECT * FROM stu ORDER BY age DESC LIMIT 1;

# 范围内。
# 两个参数
# LIMIT 0,3  # 0 开始  3 记录。
SELECT * FROM stu LIMIT 0,3;

SELECT * FROM stu LIMIT 3,3;

SELECT * FROM stu LIMIT 6,3;

# 当前页
# 每页显示条数
# 1 3
# 公式
# (当前页-1)*每页显示条数 (1-1)*3
SELECT * FROM stu LIMIT 0,3;
# (2-1)*3
SELECT * FROM stu LIMIT 3,3;
# (3-1)*3
SELECT * FROM stu LIMIT 6,3;


#  数学和英语的总成绩
#  数字 + null = null
SELECT (math +  IFNULL(english,0) ) s FROM stu;

# 约束
# 主键 primary key 非空  auto_increment 自动增长。
# 非空 not null
# 唯一 unique
# 校验 check()
# 默认值 default

create table emp(
    id int primary key auto_increment,
    account varchar(20) not null unique ,
    age int not null default 0,
    salary decimal(10,2) not null check(salary > 1000 ),
    name varchar(20)
);

# 添加
insert into emp(account,age,salary,name) values('111',20,10000,'张三');
# 如果是111 不可以插入 唯一验证
insert into emp(account,age,salary,name) values('222',20,10000,'张三');

# 非空
insert into emp(account,age,salary,name) values('333',20,10000,'张三');

#默认值
insert into emp(account,salary,name) values('44',10000,'张三');

# 验证
insert into emp(account,salary,name) values('555',1001,'张三');

# 查看
select * from emp;


alter table person modify name varchar(20) not null  unique ;

insert into person (name,age) values('张三',20);
insert into person (name,age) values('张三',20);

# 用户
# 从表添加外键 forenkey  引用
create table t_dept(
    dept_id int primary key not null  auto_increment,
    dept_name varchar(20)
);

create table t_user (
    id int primary key auto_increment,
    name varchar(20),
    dept_id int not null ,
    -- 外键
    constraint foreign key( dept_id ) references  t_dept(dept_id)
);
# 外键约束 从表添加外键的 列值 必须来自主表。
# 先添加 主表。

insert into t_dept(dept_name)values('开发部');
insert into t_dept(dept_name)values('测试部');

select * from t_dept;
# 引用的是id
insert into t_user(name,dept_id)values('张三',1);

# 删除
# 主表中的数据不能随便删除。 从表中没有才可以删除。
delete from t_dept where dept_id = 1;

# 提高效率。删除和添加 影响。
# 可以从 逻辑上控制， 添加 先查询主表  删除 从表查询