MySQL DQL语句-1

CREATE TABLE student3 (
	id INT,	-- 编号
	NAME VARCHAR(20), -- 姓名
	age INT, -- 年龄
	sex VARCHAR(5),	-- 性别
	address VARCHAR(100),	-- 地址
	math INT, -- 数学
	english INT -- 英语
);

INSERT INTO 
	student3(id,NAME,age,sex,address,math,english) 
VALUES 
	(1,'马云',55,'男','杭州',66,78),
 	(2,'马化腾',45,'男','深圳',98,87),
	(3,'马景涛',55,'男','香港',56,77),
	(4,'柳岩',20,'女','湖南',76,65),
	(5,'柳青',20,'男','湖南',86,NULL),
	(6,'刘德华',57,'男','香港',99,99),
	(7,'马德',22,'女','香港',99,99),
	(8,'德玛西亚',18,'男','南京',56,65);

SELECT * FROM student3;

-- 查询 姓名 和 年龄
SELECT 
	NAME,	-- 姓名
	age 	# 年龄
FROM 
	student3;

-- 查询所有的地址
SELECT address FROM student3;

SELECT DISTINCT address FROM student3;

SELECT NAME,math,english,math + english FROM student3;

SELECT NAME,math,english,math + IFNULL(english,0) FROM student3;

SELECT NAME 姓名,math 数学,english 英语, math+IFNULL(english,0) AS 总分 FROM student3;

-- 查询年龄大于20
SELECT * FROM student3 WHERE age > 20;

-- 查询年龄大于等于20
SELECT * FROM student3 WHERE age >= 20;

-- 查询年龄等于20
SELECT * FROM student3 WHERE age = 20;

-- 查询年龄不等于20
SELECT * FROM student3 WHERE age <> 20;		# 方式1
SELECT * FROM student3 WHERE age != 20;		# 方式2

-- 查询年龄大于等于20 小于等于30
SELECT * FROM student3 WHERE age >= 20 && age <= 30;	# 方式1
SELECT * FROM student3 WHERE age >= 20 AND age <= 30;	# 方式2
SELECT * FROM student3 WHERE age BETWEEN 20 AND 30;		# 方式3，推荐

-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM student3 WHERE age = 22 OR age = 18 OR age = 25;		# 方式1
SELECT * FROM student3 WHERE age IN (22,18,25);		# 方式2

-- 查询英语成绩为null
SELECT * FROM student3 WHERE english IS NULL;

-- 查询英语成绩不为null
SELECT * FROM student3 WHERE english IS NOT NULL;

-- 查询姓马的有哪些
SELECT * FROM student3 WHERE NAME LIKE "马%";

-- 查询姓名第二个字是化的人
SELECT * FROM student3 WHERE NAME LIKE "_化%";

-- 查询姓名是三个字的人
SELECT * FROM student3 WHERE NAME LIKE "___";

-- 查询姓名中包含德的人
SELECT * FROM student3 WHERE NAME LIKE "%德%";