很好的複習資料: SQL語句到底怎麼寫 ？

来源:http://www.cnblogs.com/zyuqiang/archive/2017/06/30/7099659.html
-Advertisement-
Play Games

本文用到的資料庫如下： CREATE DATABASE exam; / 創建部門表 / CREATE TABLE dept( deptno INT PRIMARY KEY, dname VARCHAR(50), loc VARCHAR(50) ); / 創建雇員表 / CREATE TABLE em ...


本文用到的資料庫如下：

CREATE DATABASE exam;
/*創建部門表*/
CREATE TABLE dept(
    deptno      INT     PRIMARY KEY,
    dname       VARCHAR(50),
    loc         VARCHAR(50)
);

/*創建雇員表*/
CREATE TABLE emp(
    empno       INT     PRIMARY KEY,
    ename       VARCHAR(50),
    job     VARCHAR(50),
    mgr     INT,
    hiredate    DATE,
    sal     DECIMAL(7,2),
    COMM        DECIMAL(7,2),
    deptno      INT,
    CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno) ,
    //CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES dept(deptno);
    //本來應該添加這個外鍵約束，但是為了以後做案列所以不加這個約束，因為加了這個約束，那麼emp表中deptno的值就必須是dept表中的主鍵的某一值
);

/*創建工資等級表*/
CREATE TABLE salgrade(
    grade       INT     PRIMARY KEY,
    losal       INT,
    hisal       INT
);

/*創建學生表*/
CREATE TABLE stu(
    sid     INT     PRIMARY KEY,
    sname       VARCHAR(50),
    age     INT,
    gander      VARCHAR(10),
    province    VARCHAR(50),
    tuition     INT
);

/*插入dept表數據*/
INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '學工部', '上海');
INSERT INTO dept VALUES (30, '銷售部', '廣州');
INSERT INTO dept VALUES (40, '財務部', '武漢');

/*插入emp表數據*/
INSERT INTO emp VALUES (1009, '曾阿牛', '董事長', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '劉備', '經理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '關羽', '經理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '張飛', '經理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '諸葛亮', '分析師', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '龐統', '分析師', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '黛綺絲', '銷售員', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '殷天正', '銷售員', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '謝遜', '銷售員', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '韋一笑', '銷售員', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '程普', '文員', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '黃蓋', '文員', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '周泰', '文員', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, '甘寧', '文員', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO emp VALUES (1015, '張三', '保潔員', 1009, '2001-09-01', 24500, 50000, 50);  //註意 ，在部門表中根本沒有50部門

/*插入salgrade表數據*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

/*插入stu表數據*/
INSERT INTO `stu` VALUES ('1', '王永', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('2', '張雷', '25', '男', '遼寧', '2500');
INSERT INTO `stu` VALUES ('3', '李強', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('4', '宋永合', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('5', '敘美麗', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('6', '陳寧', '22', '女', '山東', '2500');
INSERT INTO `stu` VALUES ('7', '王麗', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('8', '李永', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('9', '張玲', '23', '女', '廣州', '2500');
INSERT INTO `stu` VALUES ('10', '啊歷', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('11', '王剛', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('12', '陳永', '24', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('13', '李雷', '24', '男', '遼寧', '2500');
INSERT INTO `stu` VALUES ('14', '李沿', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('15', '王小明', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('16', '王小麗', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('17', '唐寧', '22', '女', '山東', '2500');
INSERT INTO `stu` VALUES ('18', '唐麗', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('19', '啊永', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('20', '唐玲', '23', '女', '廣州', '2500');
INSERT INTO `stu` VALUES ('21', '敘剛', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('22', '王累', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('23', '趙安', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('24', '關雷', '25', '男', '遼寧', '2500');
INSERT INTO `stu` VALUES ('25', '李字', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('26', '敘安國', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('27', '陳浩難', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('28', '陳明', '22', '女', '山東', '2500');
INSERT INTO `stu` VALUES ('29', '孫麗', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('30', '李治國', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('31', '張娜', '23', '女', '廣州', '2500');
INSERT INTO `stu` VALUES ('32', '安強', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('33', '王歡', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('34', '周天樂', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('35', '關雷', '25', '男', '遼寧', '2500');
INSERT INTO `stu` VALUES ('36', '吳強', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('37', '吳合國', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('38', '正小和', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('39', '吳麗', '22', '女', '山東', '2500');
INSERT INTO `stu` VALUES ('40', '馮含', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('41', '陳冬', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('42', '關玲', '23', '女', '廣州', '2500');
INSERT INTO `stu` VALUES ('43', '包利', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('44', '威剛', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('45', '李永', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('46', '張關雷', '25', '男', '遼寧', '2500');
INSERT INTO `stu` VALUES ('47', '送小強', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('48', '關動林', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('49', '蘇小啞', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('50', '趙寧', '22', '女', '山東', '2500');
INSERT INTO `stu` VALUES ('51', '陳麗', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('52', '錢小剛', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('53', '艾林', '23', '女', '廣州', '2500');
INSERT INTO `stu` VALUES ('54', '郭林', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('55', '周制強', '23', '男', '湖北', '4500');

emp
dept
salgrade
stu

.sql文件下載

單表查詢與多表查詢 案列

單表查詢練習

/*1\. 查詢出部門編號為30的所有員工*/
SELECT *
FROM emp
WHERE deptno=30;

/*2\. 查詢所有銷售員的姓名、編號和部門編號。*/
SELECT ename, empno, deptno
FROM emp
WHERE job='銷售員';

/*3\. 找出獎金高於工資的員工。*/
SELECT *
FROM emp
WHERE comm > sal

/*4\. 找出獎金高於工資60%的員工。*/
SELECT *
FROM emp
WHERE comm > sal*0.6;

/*5\. 找出部門編號為10中所有經理，和部門編號為20中所有銷售員的詳細資料。*/
SELECT *
FROM emp
WHERE (deptno=10 AND job='經理') OR (deptno=20 AND job='銷售員')

/*6\. 找出部門編號為10中所有經理，部門編號為20中所有銷售員，還有即不是經理又不是銷售員但其工資大或等於20000的所有員工詳細資料。*/
SELECT *
FROM emp
WHERE (deptno=10 AND job='經理') OR (deptno=20 AND job='銷售員') OR (job NOT IN ('經理', '銷售員') AND sal >= 20000)

/*8\. 無獎金或獎金低於1000的員工。*/
SELECT *
FROM emp
WHERE comm IS NULL OR comm < 1000

/*9\. 查詢名字由三個字組成的員工。*/
SELECT *
FROM emp
WHERE ename LIKE '___'

/*10.查詢2000年入職的員工。*/
SELECT *
FROM emp
WHERE hiredate LIKE '2000-%'

/*11\. 查詢所有員工詳細信息，用編號升序排序*/
SELECT * 
FROM emp
ORDER BY empno

/*12\. 查詢所有員工詳細信息，用工資降序排序，如果工資相同使用入職日期升序排序*/
SELECT *
FROM emp
ORDER BY sal DESC, hiredate ASC

/*13\. 查詢每個部門的平均工資*/
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno

/*14\. 查詢每個部門的雇員數量。*/
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno

/*15\. 查詢每種工作的最高工資、最低工資、人數*/
SELECT job, MAX(sal), MIN(sal), COUNT(*)
FROM emp
GROUP BY job

多表查詢練習

/*
1. 查出至少有一個員工的部門。顯示部門編號、部門名稱、部門位置、部門人數。
列：d.deptno, d.dname, d.loc, 部門人數
表：dept d, emp e
條件：e.deptno=d.deptno
*/
SELECT d.*, z1.cnt 
FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z1
WHERE d.deptno = z1.deptno

/*
3. 列出所有員工的姓名及其直接上級的姓名。
列：員工姓名、上級姓名
表：emp e, emp m
條件：員工的mgr = 上級的empno
*/

SELECT e.ename, IFNULL(m.ename, 'BOSS') 領導
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno


/*
4. 列出受雇日期早於直接上級的所有員工的編號、姓名、部門名稱。
列：e.empno, e.ename, d.dname
表：emp e, emp m, dept d
條件：e.hiredate<m.hiredate
*/

SELECT e.empno, e.ename, d.dname
FROM emp e, emp m, dept d
WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno

/*
5. 列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門。
列：* 
表：emp e, dept d
條件：e.deptno=d.deptno
*/
SELECT *
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno

/*
7. 列出最低薪金大於15000的各種工作及從事此工作的員工人數。
列：job, count(*)
表：emp e
條件：min(sal) > 15000
分組：job
*/
SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000


/*
8. 列出在銷售部工作的員工的姓名，假定不知道銷售部的部門編號。
列：e.ename
表：emp
條件：e.deptno=(select deptno from dept where dname='銷售部')
*/

SELECT *
FROM emp e
WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='銷售部')



/*
9. 列出薪金高於公司平均薪金的所有員工信息，所在部門名稱，上級領導，工資等級。
列：* 
表：emp e
條件：sal>(查詢出公司的平均工資)
*/
SELECT e.*, d.dname, m.ename, s.grade
FROM emp e, dept d, emp m, salgrade s
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal

---------------

SELECT e.*, d.dname, m.ename, s.grade
FROM 
  emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno
        LEFT OUTER JOIN emp m ON e.mgr=m.empno
        LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal>(SELECT AVG(sal) FROM emp)


SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;



/*
10.列出與龐統從事相同工作的所有員工及部門名稱。
列：e.*, d.dname
表：emp e, dept d
條件：job=(查詢出龐統的工作)
*/

SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WHERE ename='龐統')



/*
11.列出薪金高於在部門30工作的所有員工的薪金　的員工姓名和薪金、部門名稱。
列：e.ename, e.sal, d.dname
表：emp e, dept d
條件；sal>all (30部門薪金)
*/
SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal > ALL (SELECT sal FROM emp WHERE deptno=30)

/*
13.查出年份、利潤、年度增長比
*/
SELECT y1.*, IFNULL(CONCAT((y1.zz-y2.zz)/y2.zz*100, '%'), '0%') 增長比
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ON y1.year=y2.year+1;

寫在前面： 文章以MySQL為軟體，關於MySQL這個軟體的基本應用，請點擊這兒學習

什麼是SQL

SQL : Structured Query Language (結構化查詢語言)。 作用是： 客戶端使用SQL來操作伺服器。
SQL標註： 是由國際標準化組織（ISO）制定的，對DBMS的統一操作方式。
SQL方言： 每個DBMS都除了會遵循標準之外，會有自己的方言。也就是自己所以特有的特殊的語句或者句法。
SQL語法：

  • SQL語句可以在單行或者多行書寫，以分號結束 ，沒有寫分號，可以換行 ；
  • 可以使用空格或者縮進來增強語句的可讀性 ；
  • MySQL 是不區分大小寫的，不過建議大寫 。
  • 資料庫中所有的字元串類型都需要用單引號，不能使用雙引號

SQL語句的分類

  1. DDL （Data Definition Language ） ： 數據定義語言，用來對資料庫或表的結構進行操作 。 也就是創建、刪除、修改 庫和表結構
  2. DML (Data Manipulation Language ) : 數據操作語言，用來對錶的記錄進行更新 ， 也就是對錶的記錄進行增、刪、改
  3. DQL (Dat Query Language ) : 數據查詢語言 ， 用來對錶的記錄進行查詢
  4. DCL (Dat Control Language ) : 數據控制語言 ， 用來定義訪問許可權和安全級別

資料庫存儲的數據類型

點擊這裡下載數據類型文檔

DDL

DDL _ 對庫的操作

  1. 查看所有資料庫： SHOW DATABASES ;
  2. 切換（選擇要操作的）資料庫：USE 資料庫名 ;
  3. 創建資料庫：CREATE DATABASE mydb1 [CHARSET=utf8] //方框號是表示可選擇的。 語句中沒有方框號
  4. 刪除資料庫：DROP DATABASE mydb1
  5. 修改資料庫編碼：ALTER DATABASE mydb1 CHARACTER SET utf8

DDL _ 對錶的操作

  1. 創建表
  CREATE TABLE  表名(
    列名 列類型,
    列名 列類型,
    ...
    列名 列類型
  );
  1. 查看當前資料庫中所有表名稱：SHOW TABLES;
  2. 查看表結構：DESC 表名;
  3. 刪除表：DROP TABLE 表名;
  4. 修改_添加列：
    ALTER TABLE 表名 
    ADD (
      列名 列類型,
      列名 列類型,
      ...
    );
  1. 修改_修改列類型 ALTER TABLE 表名 MODIFY 列名 列類型;
  2. 修改_修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 列類型;
  3. 修改_刪除列：ALTER TABLE 表名 DROP 列名;
  4. 修改_修改表名稱：ALTER TABLE 原表名 RENAME TO 新表名;

DML

where條件語法

  1. 條件必須是一個boolean類型的值或表達式
  2. where 屬性判斷
  3. 條件的運算符：
運算符 說明舉例 運算符 說明舉例
= 等於 != 不等於
<> 大於小於 > 大於
< 小於 >= 大於等於
<= 小於等於WHERE age >= 18 AND age <= 80 between..and WHERE age BETWEEN 18 AND 80
in(...) WHERE name IN ('zhangSan', 'liSi')包括zhangSan和liSi is null WHERE age IS NULL；選中age為NULL
not 非 or 或者
and 和

WHERE age >= 18 AND age <= 80 選中年齡在大於18和小於80
WHERE age BETWEEN 18 AND 80 選中年齡在大於18和小於80

WHERE name='zhangSan' OR name='liSi' 選中名字是zhanSan或者liSi的
WHERE name IN ('zhangSan', 'liSi') ; 選中名字是zhangSan和liSi
WHERE age IS NULL 選擇年齡值為空的
WHERE age IS NOT NULL選擇年齡值不為空的

插入數據

INTERT INTO 表名(列名1,列名2, ...) VALUES(列值1, 列值2, ...);
值的順序和個數必須與前面指定的列對應，沒有給出具體指自動插入null。 如果沒有給出列名，表示插入所有列

INSERT INTO stu(
  number, name, age, gender
)
VALUES(
  '11111111', 'zhangSan', 28, 'male'
);

修改數據

UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 條件]
方框號表示可以指定條件，實際語句中沒有方框號。如果不加條件，就是所有記錄的該列名的值全部被修改

UPDATE stu SET number=55, name='liSi'  WHERE age BETWEEN 18 AND 80 //把年齡在18~80之間的所有學生number改成55，name改成liSi

刪除數據

DELETE FROM 表名 [WHERE 條件]; 常用刪除語句
TRUNCATE TABLE 表名 : TRUNCATE是DDL語句，它是先刪除drop該表，再create該表。而且無法回滾！！！

DCL 超級用戶root

創建用戶

CREATE USER 用戶名@IP地址 IDENTIFIED BY '密碼'; 用戶只能在指定的IP地址上登錄
CREATE USER 用戶名@'%' IDENTIFIED BY '密碼'; 用戶可以在任意IP地址上登錄
本機的IP地址可以使用localhost

用戶授權

GRANT 許可權1, … , 許可權n ON 資料庫.* TO 用戶名@IP地址 給用戶分派在指定的資料庫上的指定的許可權
GRANT ALL ON 資料庫.* TO 用戶名@IP地址; 給用戶分派指定資料庫上的所有許可權
許可權有： create、alter、drop、insert、update、delete、select
例如；GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;

撤銷授權

REVOKE 許可權1, … , 許可權n ON 資料庫.* FROM 用戶名@IP地址;撤消指定用戶在指定資料庫上的指定許可權
例如；REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

查看許可權

SHOW GRANTS FOR 用戶名@IP地址查看指定用戶的許可權

刪除用戶

DROP USER 用戶名@IP地址刪除用戶

DQL_資料庫查詢語言

基礎查詢

查詢所有的列 ； select * from 表名 ;
查詢指定的列： select 列1 ，列2 ,...,列n from 表名;
如果有重覆記錄，則只顯示一個記錄 ：select distinct * from 表名 ;

列運算

列運算只是對顯示做了運算，並沒有修改資料庫中的內容

  • 加減乘除： select 列1+列2 from 表名 ; 列可以加減乘除數字 。 字元串當0處理。如果某一列中有記錄為NULL，則需要用ifnull(有null值的列，0)將null值轉換為0，當然用ifnull(有null值的列，'XXX')可以將null值轉換成字元串XXX
  • 給列起一個別名，這個別名只是對顯示有效，並沒有修改資料庫中的真實列名： SELECT 列1原名 AS 列1別名, 列2原名 AS 列2別名 FROM 表名; 其中as是可以省略的
  • 字元串的連續運算： select concat('我叫',name,'我的工作是',job) from 表名 ; 其中name和job都是表的列。

條件查詢_where

使用where關鍵字作為條件，可參考where的 條件運算符

模糊查詢_like

  • 查詢員工表emp中姓名ename列中,姓張的，並且只有兩個字的所有員工 ； select * from emp where ename like '張_' ;,_的代表一個模糊的任意字元，一個下劃線匹配一個字元。
  • 查詢員工表emp中姓名ename列中,只有三個字的所有員工 ；select * from emp where ename like '___' ;
  • 查詢員工表emp中姓名ename列中, 姓張的所有員工 ： select * from emp where ename like '張%' ; 其中%號匹配多個字元
  • 查詢員工表emp中姓名ename列中，最後一字是林的所有員工 ： select * from emp where ename like '%林' ;
  • 查詢員工表emp中姓名ename列中，帶小的所有員工 ：select * from emp where ename like '%小%' ;

排序_order by

升序--根據年齡由小到大對所有記錄進行排序 ： select * from where emp order by age asc ; age為表的列，其中asc可以省略
降序--根據工資(sal)由大到小對所有記錄進行排序 ： select * from where emp order by sal desc ; ， desc不可以省略
使用多列作為排序條件---根據年齡對所有進行升序，如果年齡相同的某些記錄，根據工資對這些記錄進行降序排序 ： select * from where emp order by age asc , sal desc ;

聚合函數

聚合函數是用來做某列的縱向運算

  • 查詢行數： 查詢emp表中所有列都不為NULL的記錄的行數 ： select count(*) from emp ;
  • 查最大值/最小值： 查詢工資（sal）最大是多少 : select max(sal) from emp ;
  • 查詢和，NULL和字元串都當成0計算 : 查詢所有人工資的和 ：select sum(sal ) from emp ;
  • 查詢平均值,NULL和字元串都當成0計算 ： 查詢所有人的平均工資 ：select avg(sal) from emp ;
    綜合一下： select count(*) 人數，sum(sal) 總和,max(sal) 最高工資,avg(sal ) 平均工資 from emp ;

分組查詢

分組查詢的意思是把記錄用某一列進行分組，然後查詢信息，查的信息都是組信息，而不是個人信息，組信息一般都是用聚合函數計算出來的

  • 使用部門(deptno)分組，查詢部門編號和每個部門的記錄數： select deptno,count(*) from emp group by deptno ;
  • 使用工作（job）分組，查詢每種工作的最高工資：select job ,max(sal) from emp group by job ;
    組條件：分組前用where，分組後用having
  • 使用部門(deptno)分組，查詢每組記錄數，條件為記錄數的工資大於15000（分組前的條件）:select deptno ,count(*) from emp where sal>15000 group by deptno ;
  • 使用部門(deptno)分組，查詢每組記錄數，條件為每組中的記錄工資大於15000(分組前的條件),並且記錄數要大於等於2(分組後的條件):select deptno ,count(*) from emp where sal>15000 group by deptno having count(*) >=2 ;

limit子句 方言

limit子句是mysql的方言，也就是mysql所特有的語言，作用是用來限定查詢結果的起始行，以及總行數

  • 查詢起始行為第五行，一共查詢3行記錄: select * from emp limit 4,3 ; ，其中4表示第5行，第一行是從0開始的，3表示一共查詢3條記錄
    這個功能用在分頁查詢上非常的方便

關鍵字的書寫順序

用上所有的關鍵字的關鍵字書寫順序和執行順序是： select 、from、 where、group by 、having、order by

約束

主鍵約束與其相關

當表的某一列被指定為主鍵後， 該列就不能為空（非空）、不能出現重覆值（唯一性）、而且能夠被引用（外鍵）
定義主鍵 PRIMARY KEY ，主鍵自增長AUTO_INCREMENT, 某列非空 NOT NULL ， 唯一約束NOT NULL UNIQUE

CREATE TABLE stu(
        sid     INT     PRIMARY KEY AUTO_INCREMENT,   //定義主鍵，自增長
        sname   VARCHAR(20) NOT NULL UNIQUE,        //定義名字唯一性（不重覆）
        age     INT    NOT NULL,                //定義年齡非空
        gender  VARCHAR(10)
  );

修改表時 指定主鍵 ： ALTER TABLE stu ADD PRIMARY KEY (sid ) ;
修改表時設置主鍵自增長：ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;
修改表時刪除主鍵自增長：ALTER TABLE stu CHANGE sid sid INT;
刪除主鍵 ： ALTER TABLE stu DROP PRIMARY KEY ;

建立多對一 外鍵約束

員工表（
   員工編號 ，主鍵
   員工姓名 ，
   。。。。
   員工部門編號 （值是10,20,30，null）  
   CONSTRAINT fk_員工表_部門表 FOREIGN KEY (員工部門編號) REFERENCES 部門表(部門編號)  ；  //添加外鍵約束
）;
部門表（
    部門編號 ，主鍵  
    部門名稱
）；

如上代碼， 員工和所屬部門是多對一的關係 。 員工表中部門這一列就是員工表的外鍵 。
外鍵必須是表（另一個表或者本表都可以）的主鍵值，即 外鍵引用主鍵 。 外鍵內容可以重覆、可以為空。 一張表可以有多個外鍵 。
外鍵的取值必須在部門表的主鍵值的範圍之內，不能出現部門表主鍵沒有的值，否則會報錯誤 。
修改表時添加外鍵約束 ：ALTER TABLE 員工表 ADD CONSTRAINT fk_員工表_部門表 FOREIGN KEY(員工部門編號) REFERENCES 部門表(部門編號);
修改表時刪除外鍵約束 ： ALTER TABLE 員工表 DROP FOREIGN KEY fk_員工表_部門表 ;

建立一對一 外鍵約束

需要要其中一張表的主鍵即是主鍵又是外鍵

丈夫表（
   丈夫編號 ，主鍵
   丈夫姓名 ，
）;
妻子表（
    妻子編號 ，主鍵+外鍵  
    妻子名字
    CONSTRAINT fk_妻子表_丈夫表 FOREIGN KEY (妻子編號) REFERENCES 丈夫表(丈夫編號)  ；  //添加外鍵約束
）；

如上代碼 ， 丈夫和妻子是一對一的關係 。 妻子表中的妻子編號即是主鍵又是外鍵 。

建立多對多 外鍵約束

需要使用三張表 ， 在中間表中使用兩個外鍵，分別引用其他兩個表的主鍵 。

學生表(
    學生編號 ，  主鍵
    學生姓名 
);
教師表(
    教師編號 ， 主鍵
    教師姓名 
);
中間表(
    中間表學生編號  ，
    中間表教師編號
    CONSTRAINT fk_學生表_教師表_添加學生外鍵 FOREIGN KEY(中間表學生編號) REFERENCES 學生表(學生編號),   //添加外鍵約束
    CONSTRAINT fk_學生表_教師表_添加教師外鍵 FOREIGN KEY(中間表教師編號) REFERENCES 教師表(教師編號)     //添加外鍵約束
);

如上述代碼 ， 學生和教師是多對多的關係 。 中間表中的每條記錄都是來說明學生和教師之間的關係 。

多表查詢

連接查詢

內連接

/*
  方言 ， MySQL所特有
  查詢 員工姓名 工資 和所在部門名稱
*/
SELECT e.ename,e.sal,d.dname
FROM  emp AS e,dept AS d
WHERE e.deptno=d.deptno;

/*
  標準  ，以後建議都用標準
  查詢 員工姓名 工資 和所在部門名稱
*/
SELECT e.ename,e.sal,d.dname
FROM  emp AS e INNER JOIN dept AS d
ON e.deptno=d.deptno;

內連接
上述執行就可以發現 ， 查詢出的人名中並沒有張三這個人，因為張三的部門是50，而在部門表中並沒有50這個部門。 如果想要顯示張三，並且對應部門的名稱顯示NULL 的話，需要外連接
外連接

/*
   左鏈接 ，先查詢出左表（以左表為主），然後查詢右表，右表中滿足條件的顯示出來，不滿足條件的顯示NULL  
*/
SELECT e.ename,e.sal,d.dname
FROM emp AS e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno ;

/*
   右鏈接 ，先查詢出右表（以右表為主），然後查詢左表，左表中滿足條件的顯示出來，不滿足條件的顯示NULL  
*/
SELECT e.ename,e.sal,d.dname
FROM emp AS e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno ;
/*
   全外連接 
SELECT e.ename,e.sal,d.dname
FROM emp AS e FULL OUTER JOIN dept d
ON e.deptno=d.deptno ;
但是MySQL資料庫不支持，可以用UNION ALL  的方式來解決
*/
SELECT e.ename,e.sal,d.dname
FROM emp AS e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno 
UNION ALL
SELECT e.ename,e.sal,d.dname
FROM emp AS e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno ;

左外
右外
全外
如上代碼所示， 外連接有左右之分，左鏈接 ，先查詢出左表（以左表為主），然後查詢右表，右表中滿足條件的顯示出來，不滿足條件的顯示NULL，比如，員工中的張三，先把張三查詢出來，然後右表（部門表）中並沒有50號部門，所以顯示NULL 。 右鏈接 ，先查詢出右表（以右表為主），然後查詢左表，左表中滿足條件的顯示出來，不滿足條件的顯示NULL ，比如部門表中有個40號財務部門，先查詢出來，但是左表（員工）中並沒有40號部門的員工，所以左側都顯示為ＮＵＬＬ．

子查詢

子查詢就是嵌套查詢，select中包含多個select 。 子查詢出現的位置 ，在where後，作為條件的一部門 ；在from後，作為被查詢的一條表 。當子查詢出現在where後作為條件時，可以用any、all 關鍵字 。
子查詢的結果集的形式有 ： 單行單列、單行多列、多行單列、多行多列

/*
  子查詢案例一 ： 查詢工資高於關羽工資的員工
  子查詢作為條件 。 形式為單行單列
  子條件是關羽的工資
*/
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename="關羽") ;
/*
   子查詢案例二 ： 工資高於30部門所有人的員工信息
   子查詢作為條件  。 形式為多行單列
   子條件是30部門所有人的工資
*/
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30) ;
/*
   子查詢案例三 ： 查詢工作和工資與殷天正完全相同的員工
   子查詢作為條件 。  形式為單行多列
   子條件是殷天正的工作和工資
*/
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename="殷天正"); 
/*
   子查詢案例四 ： 查詢員工編號為1006的員工名稱、部門名稱、員工工資、部門地址
   子查詢 作為表。 形式為多行多列
   子條件是部門表中的部門名稱、部門地址 ， 部門編號作為去除笛卡爾積的條件也必須被查詢
*/
SELECT e.ename,e.sal,d.dname,d.loc
FROM emp AS e , (SELECT dname ,loc ,deptno FROM dept) AS d 
WHERE e.deptno=d.deptno AND e.empno=1006 ;

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • MySQL函數—數學函數
    絕對值函數ABS() 圓周率PI(),小數預設6位 平方根函數SQRT() 求餘函數MOD(x,y) 向上取整CEIL(x),CEILING(x) 向下取整FLOOR(x) 隨機數RAND(),RAND(x) :產生0~1之間的浮點數，有參數時，相同的參數產生的隨機數相等 四捨五入ROUND(x) ...
  • Memcached
    Memcached是一個自由開源的，高性能，分散式記憶體對象緩存系統。 key-value存儲方式。 Memcached只有String的存儲結構。 ...
  • MySQL 物理文件體繫結構的簡單整理說明
    本文出處：http://www.cnblogs.com/wy123/p/7102128.html （保留出處並非什麼原創作品權利，本人拙作還遠遠達不到，僅僅是為了鏈接到原文，因為後續對可能存在的一些錯誤進行修正或補充，無他） 本文的資料庫版本是MySQL5.7.18，簡單介紹一下MySQL數據文件目 ...
  • ACID 資料庫正確執行四要素
    ACID:資料庫事務正確執行所必須滿足的四個基本要素的縮寫: 原子性（atomicity，或叫不可分割性），一致性（consistency），隔離性（isolation，又稱獨立性），持久性（durability）。 四大特性 原子性：一個事務（transaction）中的所有操作，要麼全部完成，要 ...
  • 淺談資料庫的隔離等級
    資料庫事務的隔離等級，英語叫做 Transaction Isolation Level。 最近在給客戶維護項目的時候，對一個表在兩個進程中同時做更新和查詢時碰到了死鎖（DeadLock），數據表裡有幾百萬上千萬條記錄，上面的處理當時是更新幾千條記錄， 查詢整張表。 這是前提，為了搞明白這個死鎖，大概 ...
  • 基本的查詢語句
    查詢的格式: select [distinct] *(所有)| 欄位名... from 表名 [where 條件過濾]查詢指定欄位信息pname priceselect pname,price from products; 查詢表中所有欄位select * from products; 去除金額重覆 ...
  • 機器學習之分類問題實戰(基於UCI Bank Marketing Dataset)
    導讀： 分類問題是機器學習應用中的常見問題，而二分類問題是其中的典型，例如垃圾郵件的識別。本文基於UCI機器學習資料庫中的銀行營銷數據集，從對數據集進行探索，數據預處理和特征工程，到學習模型的評估與選擇，較為完整的展示瞭解決分類問題的大致流程。文中包含了一些常見問題的處理方式，例如缺失值的處理、非數 ...
  • Redis 記憶體管理與事件處理
    1 Redis記憶體管理 Redis記憶體管理相關文件為zmalloc.c/zmalloc.h，其只是對C中記憶體管理函數做了簡單的封裝，屏蔽了底層平臺的差異，並增加了記憶體使用情況統計的功能。 void *zmalloc(size_t size) { // 多申請的一部分記憶體用於存儲當前分配了多少自己的內 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類