预处理

create table if not EXISTS user (

id int UNSIGNED PRIMARY KEY auto_increment,

username VARCHAR(20),

gender ENUM('男','女', '保密') DEFAULT '保密'

);

show TABLES;

DESC user;

-- 1、插入

insert INTO user (username) VALUES ('Alex');

insert INTO user (username, gender) VALUES ('张三', '男');

insert INTO user (username, gender) VALUES ('李四', '男'), ('王五', '男');

-- 2、删除

DELETE from user WHERE id = 2;

-- 3、更新

update user set username='mali', gender='女' where user.id = 1;

create table if not EXISTS student (

id int UNSIGNED PRIMARY KEY auto_increment,

name VARCHAR(20) not NULL,

gender ENUM('男','女', '保密') DEFAULT '保密',

class VARCHAR(20),

age INT NOT NULL,

createAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

INSERT INTO student (name, age, gender, class) values ('王五',21,'女','二班');

INSERT INTO student (name, age, gender, class) values ('赵柳',22,'男','一班');

INSERT INTO student (name, age, gender, class) values ('孙琦',20,'女','三班');

INSERT INTO student (name, age, gender) values ('粥吧',19,'男');

INSERT INTO student (name, age, gender, class) values ('及时',18,'女','二班');

INSERT INTO student (name, age, gender, class) values ('李盼',18,'女','一班');

INSERT INTO student (name, age, gender) values ('冯吉', 19,'男');

-- 1. 基础查询

SELECT id,name from student;

SELECT * from student;

-- 2. where条件查询

-- >、>=、<、<=、=、!=

-- AND、OR、NOT

-- IN(值1，值2,...)、BETWEEN...AND

-- IS NULL、IS NOT NULL

-- LIKE 模糊查询

SELECT * from student WHERE student.id=4;

SELECT * from student WHERE student.age>=19 AND gender='男';

SELECT * from student WHERE student.age IN(19,20,21);

SELECT * from student WHERE student.age BETWEEN 19 AND 21;

SELECT * from student WHERE student.class IS NULL;

SELECT * from student WHERE student.name LIKE '王%'; -- % 表示任意个数的任意字符

SELECT * from student WHERE student.name LIKE '王_'; -- _ 表示一个任意字符

SELECT * from student WHERE student.name LIKE '%五%'; -- _ 表示一个任意字符

-- 3.排序

SELECT * FROM student ORDER BY age asc; -- 默认就是升序 ascending

SELECT * FROM student ORDER BY age desc; -- 降序 descending

SELECT * FROM student ORDER BY age desc, id asc; -- 先按照年龄降序，如果年龄相同则按照id升序排列

-- 4.分页查询

-- 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;

-- 5.聚合函数

-- 例如函数 SELECT DATABASE() 非聚合函数;

-- 对表中的数据进行统计和计算，一般结合分组（GROUP BY）来使用,用于统计和计算分组数据

-- COUNT() 计算查询到了多少条数据

-- SUM() 计算查询结果中所有指定字段的和

-- AVG() 计算查询结果中所有指定字段的平均值

-- MAX() 查询结果中指定字段的最大值

-- MIN() 查询结果中指定字段的最小值

SELECT * FROM student;

SELECT COUNT(*) FROM student;

SELECT SUM(age) FROM student;

SELECT AVG(age) FROM student;

SELECT MAX(age) FROM student;

SELECT MIN(age) FROM student;

-- 给查询出来的字段起别名

SELECT COUNT(*) totalRecord FROM student;

-- 6.分组查询 GROUP BY

SELECT * 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;

-- 7.HAVING 条件查询

-- WHERE 是去数据表中查询符合条件的数据返回结果集

-- HAVING 是去结果集中查询符合条件的数据，可以对分组之后查询到的结果进行筛选

-- SELECT class,AVG(age) avgAge FROM student GROUP BY class WHERE avgAge<=19.5; -- where不行会报错

SELECT class,AVG(age) avgAge FROM student GROUP BY class HAVING avgAge<=19.5;

-- 8. 多表查询

-- 8.0.准备

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,

undateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

INSERT into class (name, `desc`) VALUES('一班', '火箭班');

INSERT into class (name, `desc`) VALUES('二班', '飞机班');

INSERT into class (name, `desc`) VALUES('三班', '高铁班');

INSERT into class (name, `desc`) VALUES('四班', '汽车班');

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,

undateAt 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('李四', 21, '女', 3);

INSERT INTO student(name, age,gender, class_id) values('王五', 19, '女', 2);

INSERT INTO student(name, age,gender, class_id) values('赵六', 16, '男', 1);

INSERT INTO student(name, age,gender, class_id) values('宋七', 19, '女', 2);

INSERT INTO student(name, age,gender) values('康良', 19, '女');

-- 8.10 直接查询

-- 秩序在单表查询基础上增加一张表即可，返回的结果是多张表数据局条数的乘积

SELECT * FROM student, class WHERE student.class_id = class.id;

-- 8.2 连接查询

-- 8.2.1 内连接（INNER JOIN 或 JOIN）

-- 内连接的查询结果和和直接查询的结果是一样的

SELECT * FROM student JOIN class; -- SELECT * FROM student,class;

SELECT * FROM student JOIN class ON student.class_id = class.id;

SELECT student.id id,student.name,class.name,class.id cid FROM student JOIN class ON student.class_id = class.id;

-- 8.2.2 外连接查询

-- 8.2.2.1 左外连接查询（LEFT OUTER JOIN 或 LEFT JOIN）

SELECT * FROM student LEFT JOIN class on student.class_id = class.id;

-- 8.2.2.2 右外连接查询 （RIGHT OUTER JOIN 或 RIGHT JOIN）

SELECT * FROM student RIGHT JOIN class on student.class_id = class.id;

-- 8.3 UNION 查询

-- 在纵向上将多张表的查询结果拼接起来返回

-- 必须保证查询的多张表的字段是一致的

SELECT id,name FROM student UNION SELECT id,name from class;

-- 8.4 子查询

-- 8.4.1 将一个查询语句查询的结果作为另一个查询语句的条件来使用

SELECT name, id from class WHERE id = (SELECT class_id FROM student WHERE id = 3)

SELECT name, id from class WHERE id IN (SELECT class_id FROM student WHERE id >= 3)

-- 8.4.2 讲一个查询语句的结果作为另一个查询语句的表来使用

SELECT name from class WHERE id >= 2;

SELECT * FROM (SELECT name from class WHERE id >= 2) t; -- 必须给子查询起个别名

select class,avg(age) from student group by class;

111111biao