day02-约束

约束

非空约束：not null

唯一性约束：unique

默认值约束：default

主键约束：primary key（保证数据的唯一性）

检查约束：check 检查数据是否符合要求

自动增长：auto-increment

CREATE TABLE if not EXISTS t_test2(
    id int PRIMARY KEY auto_increment,
    username VARCHAR(255) UNIQUE not NULL,
    sex char(2) CHECK(sex='男' OR sex='女'),
    age int CHECK(age >= 0 and age <= 150) default 0
    
)ENGINE=INNODB CHARACTER SET 'utf8'
​
SELECT * from t_test2;
​
INSERT into t_test2(username,sex,age) values ('gg1','女',null);
INSERT into t_test2(username,sex,age) values ('zs','男','20');
INSERT into t_test2(username,sex,age) values ('ls','男','21');
INSERT into t_test2(username,sex,age) values ('ww','男','22');
INSERT into t_test2(username,sex,age) values ('zl','男','23');
​
-- 约束的其它添加方式（constraint 约束名写下面）
CREATE TABLE if not EXISTS t_test3(
    id int auto_increment,
    username VARCHAR(255) not null,
    sex char(2),
    age int DEFAULT 0,
    CONSTRAINT a PRIMARY KEY(id),
    CONSTRAINT b UNIQUE(username),
    CONSTRAINT c CHECK(age>=0 and age<=150)
)ENGINE=INNODB CHARACTER set 'utf8'
​
DROP TABLE t_test3;
​
-- 创建表后，添加/修改约束
-- 方式一
ALTER TABLE t_test3 MODIFY age int CHECK(age>=0 and age<=150) not null DEFAULT;
​
-- 方式二
-- 添加
alter table t_test3 add CONSTRAINT a unique(age);
alter table t_test3 drop CONSTRAINT a;
​
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE table_schema='db1'
    and table_name='t_test3';

运算符

-- 别名
SELECT a.id '学生编号' FROM t_test2 a,t_test3 b;
SELECT 666 '6';
SELECT a.id '学生编号','张三' FROM t_test2 a;
​
-- 运算符
-- 算术运算符 +-*/%
SELECT 7/3;
​
-- 比较运算符 >,>=,<,<=,!=或<>
SELECT 1>3;
SELECT 1<>3; -- 等同于！=
​
-- 查询18-25岁的
SELECT * FROM t_test2 WHERE age>=18 and age<=25;
-- BETWEEN
SELECT * FROM t_test2 WHERE age NOT BETWEEN 18 and 25;
​
-- 查询名字叫'张三,李四，王五'的人
SELECT * from t_test2 WHERE username = '张三' or username = '李四' OR username = '王五';
-- in查具体的
SELECT * FROM t_test2 WHERE username not in ('张三','李四','王五');
​
-- like 查询10-19岁的人
SELECT * FROM t_test2 WHERE age>=10 and age<=19;
SELECT * FROM t_test2 WHERE age LIKE '1%'; -- %是任意字符任意长度
​
-- 查询所有姓张的人
SELECT * FROM t_test2 WHERE username like '张%';
​
-- 查询名字中包含张的人
SELECT * FROM t_test2 WHERE username like '%张%';
​
-- 查询名字以五结尾的人
SELECT * FROM t_test2 WHERE username like '%五';
​
-- is null 
-- is not null
SELECT * FROM t_test2 WHERE sex is NOT null;

排序

SELECT 1 XOR 1,0 XOR 0,1 XOR 0,1 XOR NULL,1 XOR 1 XOR 1;
​
-- 聚合函数
--  max：最大值
--          找出表中最小年龄是多少
        SELECT * FROM t_test2;
        SELECT min(age) FROM t_test2;
        
--      sum:和
--      求所有人的年龄之和
        SELECT sum(age) FROM t_test2;
        
        --      avg:平均值
--      求所有人的年龄平均值
        SELECT avg(age) FROM t_test2;
        
        --      count:计数
--      求男性有多少人
        SELECT count(*) FROM t_test2 WHERE sex='男';
        
--      去重
            SELECT DISTINCT(sex) FROM t_test2;
            
--          排序
--                  按年龄从小到大排序(默认从小到大)
                        SELECT * FROM t_test2 ORDER BY age ASC;
                        SELECT * FROM t_test2 ORDER BY age DESC; -- 从大到小
--                      必须先过滤再排序（先where再order by）
                        SELECT * FROM t_test2 WHERE sex='男' ORDER BY age DESC;
            

分组函数和union，union all

-- 分组函数 GROUP BY 列名
​
SELECT * FROM t_user;
​
SELECT DISTINCT(dept) FROM t_user;
​
SELECT sex FROM t_user GROUP BY sex;
​
-- 1.统计各部门有多少人
-- 聚合函数和分组函数一起使用时，聚合函数将作用于每个组
SELECT dept,COUNT(id) FROM t_user GROUP BY dept;
​
-- 2.统计各部门的平均薪资
SELECT dept,avg(money) FROM t_user GROUP BY dept;
​
-- 3.求各地区薪资最高是多少
SELECT address,max(money) FROM t_user GROUP BY address;
​
-- 4.求各地区男女生人数是多少(分组可以写多个条件)
SELECT address,sex,count(id) FROM t_user GROUP BY address,sex;
​
SELECT address,sex,count(id) from t_user where sex='男' GROUP BY address
UNION ALL -- 连接多个结果
SELECT address,sex,count(id) from t_user where sex='女' GROUP BY address;
​
-- union是去重的 union all是可重复的

关联查询

-- 左连接 left join（保证左边的表一定被查出来 即student一定查出来）
SELECT * FROM t_student s
LEFT JOIN t_class c on s.class_id = c.id;
​
-- 右连接 right join
SELECT * FROM t_student s
RIGHT JOIN t_class c on s.class_id = c.id;
​
-- 内连接 inner join
SELECT * FROM t_student s
INNER JOIN t_class c on s.class_id=c.id;
​
-- 左外连接(在左连接基础上加个判断)
SELECT * FROM t_student s
LEFT JOIN t_class c on s.class_id = c.id 
WHERE c.id is null;
​
-- 右外连接(在右连接基础上加个判断)
SELECT * FROM t_student s
RIGHT JOIN t_class c on s.class_id = c.id 
WHERE s.id is null;
​
-- 全连接
SELECT * FROM t_student s
LEFT JOIN t_class c on s.class_id = c.id
union all
SELECT * FROM t_student s
RIGHT JOIN t_class c on s.class_id = c.id 
WHERE s.id is null;
​
-- 全外连接
SELECT * FROM t_student s
LEFT JOIN t_class c on s.class_id = c.id 
WHERE c.id is null
union all
SELECT * FROM t_student s
RIGHT JOIN t_class c on s.class_id = c.id 
WHERE s.id is null;
​
-- 笛卡尔积(加判断就是内连接)
SELECT * FROM t_student s,t_class c
WHERE s.class_id = c.id;