准备：
#建库
CREATE DATABASE chapter04;
#建表
USE chapter04;
CREATE TABLE student(
id INT(3) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
grade FLOAT,
gender CHAR(2)
);
#插入数据
INSERT INTO student(name,grade,gender)
VALUES('songjiang',40,'男'),
('wuyong',100,'男'),
('qinming',90,'男'),
('husanniang',88,'女'),
('sunerniang',66,'女'),
('wusong',86,'男'),
('linchong',92,'男'),
('yanqing',90,NULL）；
1.查询student表中的所有记录
SELECT id,name,grade,gender FROM student;
2.查询student表中的所有记录（*）
SELECT * FROM student;
3.使用select语句查询name字段和gender字段
SELECT name,gender FROM student;
4.查询student表中id为4的学生姓名
SELECT id,name FROM student WHERE id=4;
5.使用select语句查询name字段为wusong的学生性别
SELECT name,gender FROM student WHERE name='wusong';
6.查询表中grade大于80的学生姓名
SELECT name,grade FROM student WHERE grade>80;
7.查询表中id值为1、2、3的记录
SELECT id,grade,name,gender FROM student WHERE id IN(1,2,3);
8.查询表中id值不为1、2、3的记录
SELECT id,grade,name,gender FROM student WHERE id NOT IN(1,2,3);
9.查询表中id值为2-5之间的学生姓名
SELECT id,name FROM student WHERE id BETWEEN 2 AND 5;
10.查询表中id值不在2-5之间的学生姓名
SELECT id,name FROM student WHERE id NOT BETWEEN 2 AND 5;
11.查询表中gender为空值的记录
SELECT id,name,grade,gender FROM student WHERE gender IS NULL;
12.查询表中gender不为空值的记录
SELECT id,name,grade,gender FROM student WHERE gender IS NOT NULL;
13.查询表中gender字段的值，查询记录不能重复
SELECT DISTINCT gender FROM student;
14.查询表中的gender和name字段，使用distinct关键字作用于这两个字段
#插入数据
INSERT INTO student(name,grade,gender)
VALUES('songjiang',20,'男');
#查询
SELECT DISTINCT gender,name FROM student;
15.查询表中name字段值以字符“s”开头的学生
SELECT id,name FROM student WHERE name LIKE "s%";
16.查询表中name字段值以字符“w”开始，以字符“g”结束的学生
SELECT id,name FROM student WHERE name LIKE 'w%g';
17.查询表中name字段值以字符值包含字符为“y”的学生id
SELECT id,name FROM student WHERE name LIKE '%y%';
18.查询表中name字段值以字符值不包含字符为“y”的学生id
SELECT id,name FROM student WHERE name NOT LIKE '%y%';
19.查询表中name字段值以字符wu开始，以字符串ong结束，并且两个字符串之间只有一个字符的记录
SELECT * FROM student WHERE name LIKE 'wu_ong';
20.查询表name字段值包含7个字符，并且以字符串“ing”结束的记录
SELECT * FROM student WHERE name LIKE '____ing';
21.查询表中name字段值包括“%”的记录
#插入记录
INSERT INTO student(name,grade,gender) VALUES('sun%er',95,'男');
#查询
SELECT * FROM student WHERE name LIKE '%\%%';
22.查询表中id字段值小于5，并且gender字段值为“女”的学生姓名
SELECT id,name,gender FROM student WHERE id<5 AND gender='女';
23.查询表中id字段值在1,2,3,4之中，name字段以字符串ng结束，并且grade小于80的记录
SELECT id,name,grade,gender
FROM student
WHERE id in(1,2,3,4) AND name LIKE '%ng' AND grade<80;
24.查询student表中id字段值小于3或者gender字段值为“女”的学生姓名
SELECT id,name,gender FROM student WHERE id<3 OR gender='女';
25.查询student表中满足条件name字段值以字符“h”开始，或者gender字段值为“女”，或者grade为100的记录。
SELECT id,name,grade,gender
FROM student
WHERE name LIKE 'h%' OR gender='女' OR grade=100;
SELECT name,grade,gender
FROM student
WHERE gender='女' OR gender='男' AND grade=100;
26.查询表中一共有多少记录
SELECT COUNT(*) FROM student;
27.求出student表中grade字段值的总和
SELECT SUM(grade) FROM student;
28.求出student表中grade字段值的平均值
SELECT AVG(grade) FROM student;
29.求出student表中所有学生grade字段的最大值
SELECT MAX(grade) FROM student;
30.求出student表中所有学生grade字段的最小值
SELECT MIN(grade) FROM student;
31.查出student表中的所有记录，并按照grade字段进行排序
SELECT * FROM student
ORDER BY grade;
32.查出student表中的所有记录，使用ASC按照grade字段升序方式排序
SELECT * FROM student ORDER BY grade ASC;
33.查出student表中的所有记录，使用DESC按照grade字段降序方式排序
SELECT * FROM student ORDER BY grade DESC;
34.查出student表中的所有记录，按照gender字段的升序和grade字段的降序方式排序
SELECT * FROM student
ORDER BY gender ASC,grade DESC;
35.查询student表中的记录，按照gender字段值进行分组
SELECT * FROM student GROUP BY gender;
36.将student表按照gender字段值进行分组查询，计算出每个分组有多少名学生
SELECT COUNT(*),gender FROM student GROUP BY gender;
37.将student表按照gender字段值进行分组查询，查询出grade字段值之和小于300的分组
SELECT sum(grade),gender FROM student GROUP BY gender HAVING SUM(grade)<300;
38.查询表中的钱4条记录
SELECT * FROM student LIMIT 4;
39.查询表中grade字段值从第5位到第8位的学生（从高到低）
SELECT * FROM student ORDER BY grade DESC LIMIT 4,4;
40.查询表中所有记录，将各个字段值使用下划线“_”连接起来
SELECT CONCAT(id,'_',name,'_',grade,'_',gender) FROM student;
41.查询表中的id和gender字段值，如果gender字段的值为“男”则返回1，如果不为，返回0
SELECT id,IF(gender='男',1,0) FROM student;
42.为student表起一个别名s,并查询表中gender字段值为“女”记录
SELECT * FROM student AS s WHERE s.gender='女';
43.查询表中所有记录的name和gender字段值，并为这两个字段起别名stu_name和stu_gender
SELECT name AS stu_name,gender stu_gender FROM student;
