mysql基础（四）之--DQL（Data Query Language）

目标：DQL数据查询语言（SELECT、WHERE、 HAVING）

一、SELECT关键字语法

1.说明

• 数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
• 查询返回的结果集是一张虚拟表。

2.语法

SELECT selection_list           /*要查询的列名称*/
FROM table_list                 /*要查询的表名称*/
WHERE condition                 /*行条件*/
GROUP BY grouping_columns       /*对结果分组*/
HAVING condition                /*分组后的行条件*/
ORDER BY sorting_columns        /*对结果排序*/
LIMIT offset_start, row_count   /*结果限定*/

二、准备数据

1.学生表

CREATE TABLE stu (
	sid CHAR(6),
	sname VARCHAR(50),
	age INT,
	gender VARCHAR(6)
);

INSERT INTO stu VALUES('S_1001', '嘻嘻', 35, 'male');
INSERT INTO stu VALUES('S_1002', '哈哈', 15, 'female');
INSERT INTO stu VALUES('S_1003', '矮矮', 95, 'male');
INSERT INTO stu VALUES('S_1004', '胖胖', 65, 'female');
INSERT INTO stu VALUES('S_1005', '菲菲', 55, 'male');
INSERT INTO stu VALUES('S_1006', '默默', 75, 'female');
INSERT INTO stu VALUES('S_1007', '翘翘', 25, 'male');
INSERT INTO stu VALUES('S_1008', '泡泡', 45, 'female');
INSERT INTO stu VALUES('S_1009', '皮皮', 85, 'male');
INSERT INTO stu VALUES('S_1010', '屁屁', 5, 'female');
INSERT INTO stu VALUES('S_1011', '欧欧', NULL, NULL);
INSERT INTO stu VALUES('S_1009', '嗯嗯', 85, 'male');
INSERT INTO stu VALUES('S_1010', '嘿嘿', 5, 'female');
INSERT INTO stu VALUES('S_1011', '卡卡西', NULL, NULL);

2.员工表

CREATE TABLE emp(
	empno INT,
	ename VARCHAR(50),
	job VARCHAR(50),
	mgr INT,
	hiredate DATE,
	sal DECIMAL(7,2),
	comm decimal(7,2),
	deptno INT
);

INSERT INTO emp values(7369,'成功','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'顺利','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'常开'