《MySQL入门学习》(p21~p23)

p21 DQL_基础查询

CREATE TABLE student (
id INT,  
NAME VARCHAR(20),
age INT,
sex VARCHAR(5),
address VARCHAR(100),
math INT,
english INT
);

SELECT * FROM student;

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

-- 查询 姓名 和 年龄
SELECT 
	NAME, -- 姓名
	age -- 年龄
FROM 	
	student; -- 学生表


SELECT address FROM student;

-- 去除重复的结果集
SELECT DISTINCT address FROM student;
SELECT DISTINCT NAME,address FROM student;


-- 计算math和english的总分之和
SELECT NAME, math,english,math+english FROM student;

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

SELECT NAME, math,english,math+	IFNULL(english,0) 总分 FROM student;

SELECT NAME, math 数学,english 英语,math+IFNULL(english,0) 总分 FROM student;

p22 DQL_条件查询

-- 条件查询
SELECT * FROM student;

SELECT * FROM student WHERE age>20;

SELECT * FROM student WHERE age>=20;

SELECT * FROM student WHERE age=20;

SELECT * FROM student WHERE age!=20;
SELECT * FROM student WHERE age<>20;


-- 查询年龄大于等于20 ，小于等于30的

SELECT * FROM student WHERE age>=20 && age<=30;  -- 不推荐这种方式
SELECT * FROM student WHERE age>=20 AND age<=30;  -- 推荐这种方式
SELECT * FROM student WHERE age BETWEEN 20 AND 30;  -- 推荐这种方式

-- 查询年龄22,18,25岁的信息

SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN (22,18, 25);

-- 查询没有英语成绩的

SELECT * FROM student WHERE english = NULL; -- 不对的，null值不能使用=(!=)判断


SELECT * FROM student WHERE english IS NULL;

SELECT * FROM student WHERE english IS NOT NULL;

p23 DQL_模糊查询

-- 模糊查询

-- 查询姓马的有哪些？ like
SELECT * FROM student WHERE NAME LIKE '马%';


-- 查询姓名第二个字是“化”的人
SELECT * FROM student WHERE NAME LIKE '_化%';

-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';

-- 查询姓名中包含“德”字的人
SELECT * FROM student WHERE NAME LIKE '%德%';