創建部門表
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));