MySQL基础知识3之创建表

创建部门表

CREATE TABLE dept(deptno INT PRIMARY KEY auto_increment,

deptname CHAR(10) NOT NULL,

loc CHAR(30)) 创建员工表

CREATE TABLE emp(empno INT PRIMARY KEY auto_increment,

ename CHAR(10) NOT NULL,

job CHAR(10) NOT NULL,

mgr INT,

hiredate DATE,

sal DOUBLE DEFAULT 0,

comm DOUBLE DEFAULT 0,

deptno INT, FOREIGN KEY(deptno) REFERENCES dept(deptno))

查询至少有一个员工的部门

SELECT COUNT(*) AS he FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY e.deptno;

列出薪金比“SMITH”多的所有员工

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='程冲')

列出所有员工的姓名及其直接上级的姓名

SELECT ename,(select ename from emp m where m.empno = e.mgr ) FROM emp e

SELECT ename,(SELECT ename from emp m where m.empno=e.mgr) FROM emp e

列出受雇日期早于其直接上级的所有员工

select e.hiredate,e.ename from emp e where e.hiredate < (select m.hiredate from emp m where m.empno=e.mgr)

自连接查询

select e.*,m.* from emp e join emp m on e.mgr = m.empno where e.hiredate < m.hiredate

自己连接自己 把一张表看做两张表

列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT e.*,d.deptname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

列出所有“CLERK”(办事员)的姓名及其部门名称

SELECT e.*,d.deptname FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE job='技术员'

列出最低薪金大于1500的各种工作

SELECT job ,min(sal) m FROM emp GROUP BY job WHERE m>1500;

列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号

SELECT e.* FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE deptname='市场部'

列出薪金高于公司平均薪金的所有员工

SELECT e.* FROM emp e WHERE sal>(SELECT AVG(m.sal) FROM emp m)

列出与“SCOTT”从事相同工作的所有员工

select m.* from emp e join emp m on e.job = m.job where e.ename='陆俊杰'

列出薪金等于销售部中员工的薪金的所有员工的姓名和薪金

SELECT m.* FROM emp e JOIN emp m ON e.sal=m.sal WHERE e.deptno=3 AND m.deptno!=3

列出薪金高于在销售部工作的所有员工的薪金的员工姓名和薪金

select * from emp e where e.sal > (select max(m.sal) from emp m where m.deptno = 3)

列出在每个部门工作的员工数量、平均工资和平均服务期限

SELECT COUNT(*) ,AVG( sal) ,AVG(TIMESTAMPDIFF(YEAR,CURRENT_DATE(),hiredate)) FROM emp e GROUP BY deptno;

----------------------------------------------------------------- -- 创建班级表

CREATE TABLE class(classid INT PRIMARY KEY auto_increment,

calssname CHAR(10) NOT NULL, teacher CHAR(10));

-- 创建课程表

CREATE TABLE course (cid INT PRIMARY KEY auto_increment,

cname CHAR(10) NOT NULL,

xuefen INT);

-- 创建学生表

CREATE TABLE student (stuid INT PRIMARY KEY auto_increment,

stucode INT NOT null,

stuname CHAR(10) NOT NULL,

stusex CHAR(4) DEFAULT '保密', birthday DATE,

stucalssid INT, FOREIGN KEY(stucalssid) REFERENCES class(classid))

-- 创建成绩表

CREATE TABLE mark_tab(mid INT PRIMARY KEY auto_increment,

stuid INT, FOREIGN KEY(stuid) REFERENCES student(stuid), courseid INT,

FOREIGN KEY(courseid) REFERENCES course(cid),

mark DOUBLE );

-- 统计每个班级的男生人数

SELECT c.calssname AS '班级',SUM(s.stusex='男') AS '男', SUM(s.stusex='女') AS '女' FROM student s JOIN class c ON s.stucalssid=c.classid GROUP BY c.classid;

-- 查询指定列的非空 总和;

SELECT COUNT(*) FROM -- 查询指定列的和值

SELECT SUM(mark)FROM mark_tab;

-- 查询指定列的最大值

SELECT MAX(mark) FROM mark_tab;

-- 查询指定列的最小值

SELECT MIN(mark) FROM mark_tab;

-- 查询指定列的平均值

SELECT AVG(mark) FROM mark_tab;

-- SELECT mark,COUNT(*) ,SUM(mark) FROM mark_tab GROUP BY mark;

-- 查询语文成绩,以及不及格的学院信息

SELECT * FROM mark_tab m JOIN student s ON m.stuid=s.stuid JOIN course c ON m.courseid=c.cid WHERE c.cname='语文' AND m.mark<60;

-- 统计Java一班数学成绩不及格的人数 (子查询)

SELECT * ,(SELECT calssname FROM class WHERE calssname='Java一班'),COUNT(*) FROM mark_tab m JOIN student s ON m.stuid=s.stuid JOIN course c ON m.courseid=c.cid WHERE c.cname='数学' AND m.mark<60

-- 拿出指定的行数

SELECT* FROM mark_tab LIMIT 0,5;

-- 统计一年级五班总成绩最高的前十名学生信息，按照总成绩排序。

-- SELECT SUM(要求和的字段) as 别名 FROM 要查询的表名 [别名]

SELECT*,SUM(mark) as sc FROM mark_tab m -- JOIN 插入的表名 [别名] ON 外键=关联表的主键 JOIN student s ON s.stuid=m.stuid

-- JOIN 插入的表名 [别名] ON 外键=关联表的主键

JOIN class c ON s.stucalssid=c.classid

-- WHERE 要筛选的字段='筛选条件'

WHERE c.calssname='Java一班'

-- GROUP BY 要分组的字段 ORDER BY 要排序的字段 DESC LIMIT [从哪行开始,那多少条]

GROUP BY s.stuid ORDER BY sc DESC LIMIT 0,10;

show variables like 'character%';

-- 什么是主键 ? 在创建表的时候 有 PRIMARY KEY 修饰的字段 称为主键

CREATE TABLE class (id int PRIMARY KEY , classname CHAR(10));

-- 什么是外键

CREATE TABLE student(stuid INT PRIMARY KEY, sdjskjd INT ,FOREIGN KEY(sdjskjd) REFERENCES class(id));