04.MySQL之单表查询

准备：

#建库

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;