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;