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));

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *