数据库常用查询2

show databases;
use demo;
select DATABASE();
drop table user;

create table if not EXISTS user(
id int UNSIGNED primary key auto_increment,
name VARCHAR(20),
gender enum('男','女','保密') DEFAULT '保密'
);
show tables;
desc user;

-- 插入数据
insert into user (name) values('Alex');
insert into user (name,gender) values('张三','男'),('李四','女'),('王五','男');

select * from user;

delete from user;
delete from user where id =6;

update user set name = 'lisa';
update user set name = 'lisa' ,gender = '女' where id = 7;

CREATE table if not EXISTS student(
id int UNSIGNED PRIMARY key auto_increment,
name VARCHAR(20) not null,
age TINYINT UNSIGNED not null,
gender enum('男','女','保密') DEFAULT '保密',
class VARCHAR(20),
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP
);
desc student;
INSERT INTO student (name , age ,gender, class ) VALUES ('张三',18,'女','2班'),('李四',19,'男','1班'),('王五',18,'女','2班'),('lisa',19,'女','2班'),('Alex',18,'男','1班');

SELECT * FROM student;
SELECT id,name FROM student;
-- 结果集：
-- 通过查询语句从数据表中查询出来的结果称为结果集
-- 以表的形式呈现
-- 结果集和查询的表不是一张表，结果集来自数据表
-- 查询出来后保存在内存中，而数据表保存在硬盘上
SELECT id,name FROM student where id = 4;

SELECT * FROM student where age > 18 and gender ='男';
SELECT * FROM student where age > 18 or gender ='男';
SELECT * FROM student where not age > 18 ;

select * FROM student WHERE id in (1,3,4);
SELECT * FROM student WHERE id BETWEEN 1 and 4;

UPDATE student set class = null WHERE id =4;
SELECT * FROM student WHERE class is null;
-- %匹配任意个任意字符
-- _匹配一个任意字符
SELECT * from student where name like '张%';
SELECT * from student where name like '%四';
SELECT * from student where name like '%五%';
SELECT * from student where name like '张_';

SELECT * from student ORDER BY age asc;-- 升序
SELECT * from student ORDER BY age desc; -- 降序
SELECT * from student ORDER BY age desc , id asc;-- 先按age降序，再按id升序

-- 分页查询
-- LIMIT 一次查询的条数 OFFSET 偏移量
-- LIMIT 偏移量,一次查询的条数
SELECT * from student limit 2 OFFSET 0;
SELECT * from student limit 2 OFFSET 2;
SELECT * from student limit 2 OFFSET 4;
SELECT * from student limit 0,2;

-- 聚合函数
-- 对表中的数据进行统计和计算，一般结合分组(GROUP BY)来使用，用于统计和计算分组数据
-- COUNT（）计算查询到了多少条数据
-- SUM（）计算查询结果中所有指定字段的和
-- AVG（)计算查询结果中所有指定字段的平均值
-- MAX(）求查询结果中指定字段的最大值
-- MIN（）求查询结果中指定字段的最小值
SELECT count(*) from student;
SELECT count(id) from student;
SELECT sum(age) FROM student;
SELECT avg(age) FROM student;
SELECT max(age) FROM student;
SELECT min(age) FROM student;
-- 别名
SELECT count(*) as total from student;
SELECT count(*) total from student;
-- 分组
-- 在对数据进行分组的时候，SELECT后面必须是分组字段或者聚合函数
SELECT * from student GROUP BY class;
SELECT class from student GROUP BY class;
SELECT class,avg(age) from student GROUP BY class;
SELECT gender,avg(age) avgAge from student GROUP BY gender;

-- HAVING条件查询
-- WHERE 是去数据表中查询符合条件的数据返回结果集
-- HAVING是去结果集中查询符合条件的数据，可以对分组之后查询到的结果进行筛选
SELECT class,avg(age) from student GROUP BY class HAVING avg(age) <19;

-- 多表查询
use demo;
drop TABLE if EXISTS class;
drop TABLE if EXISTS student;

-- 主表
CREATE table if not EXISTS class(  -- 班级表
id TINYINT UNSIGNED PRIMARY key auto_increment,
name VARCHAR(20) not null,
`desc` VARCHAR(255),
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP
);
desc class;
INSERT INTO class (name , `desc` ) VALUES ('1班','火箭班'),('2班','平行班'),('3班','实验班'),('4班','待定班');

-- 从表
CREATE table if not EXISTS student(
id int UNSIGNED PRIMARY key auto_increment,
name VARCHAR(20) not null,
age TINYINT UNSIGNED not null,
gender enum('男','女','保密') DEFAULT '保密',
class_id TINYINT UNSIGNED,
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP,
FOREIGN key(class_id) REFERENCES class(id) on UPDATE CASCADE on DELETE set null -- 当主表更新时，同步更新，当主表删除时，从表置空
);
insert into student (name,age,gender,class_id) VALUES('张三',20,'男',1);
insert into student (name,age,gender,class_id) VALUES('李四',19,'女',1);
insert into student (name,age,gender,class_id) VALUES('王五',21,'女',2);
insert into student (name,age,gender,class_id) VALUES('赵六',19,'男',4);
insert into student (name,age,gender,class_id) VALUES('钱七',18,'男',3);
insert into student (name,age,gender) VALUES('孙八',18,'男');

-- 直接查询
-- 只需要在单表查询基础上增加一张表即可，返回的结果是多张表的表数据个数的乘积，会有冗余数据
SELECT * from student;
SELECT * from class;
SELECT * from student ,class;
SELECT * from student ,class WHERE student.class_id=class.id;

-- 连接查询:

-- 内连接(inner join 或 JOIN)
-- 内连接的查询结果和直接查询的结果是一样的
SELECT * from student;
SELECT * from student join class;
SELECT * from student join class on student.class_id=class.id;
SELECT student.id,class.name from student join class on student.class_id=class.id;
SELECT stu.id,cls.name from student stu join class cls on stu.class_id=cls.id;

-- 左外连接(left OUTER join 或 left join)
-- 左边的表是不看条件的，无论条件是否满足，都会返回左表中的所有数据
-- 只有右边的表会看条件，对于右表，只有满足条件的，才会返回
SELECT * from student LEFT JOIN class on student.class_id=class.id;

-- 右外连接(RIGHT OUTER join 或 RIGHT join)
-- 右边的表是不看条件的，无论条件是否满足，都会返回右表中的所有数据
-- 只有左边的表会看条件，对于左表，只有满足条件的，才会返回
SELECT * from student right JOIN class on student.class_id=class.id;

-- UNION 查询
-- 在纵向上将多张表的查询结果拼接起来返回
-- 必须保证多张表查询的字段个数一致
SELECT id,name from student  UNION SELECT id,name FROM class;

-- 子查询
-- 将一个查询语句查询的结果作为另一个查询语句的条件来使用
-- 必须给子查询起别名
SELECT name from class WHERE id = (SELECT class_id from student WHERE id=3);
SELECT name from class WHERE id in (SELECT class_id from student WHERE id>=3);
-- 将一个查询语句查询结果作为另一个查询语句的表来使用
SELECT * FROM (SELECT name FROM class WHERE id >=2) a;