mysql語句基本簡單查詢

標籤：ber   查看   date   esc   var   注釋   規則   2.7   條件查詢   

mysql語句基本簡單查詢

 

第一節

 

-- or # 單行注釋
/**
*多行注釋
*/

-- c建立資料庫exam
CREATE DATABASE exam;

USE exam;

/*建立部門表*/
CREATE TABLE dept(
deptnoINT PRIMARY KEY,
dnameVARCHAR(50),
loc VARCHAR(50)
);

/*建立僱員表*/
CREATE TABLE emp(
empnoINT PRIMARY KEY,
enameVARCHAR(50),
jobVARCHAR(50),
mgrINT,
hiredateDATE,
salDECIMAL(7,2),
COMM DECIMAL(7,2),
deptnoINT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);

/*建立工資等級表*/
CREATE TABLE salgrade(
gradeINT PRIMARY KEY,
losalINT,
hisalINT
);

/*建立學生表*/
CREATE TABLE stu(
sidINT PRIMARY KEY,
snameVARCHAR(50),
ageINT,
ganderVARCHAR(10),
provinceVARCHAR(50),
tuitionINT
);

 

 

 

/*插入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);

/*插入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‘);

-- 1　基礎查詢
-- 1.1　查詢所有列

SELECT * FROM stu;

-- 1.2　在stu表中查詢指定列 sid, sname, age

SELECT sid, sname, age FROM stu;

 

 

-- 2　條件查詢
-- 2.2　查詢性別為女，並且年齡50的記錄
　SELECT * FROM stu WHERE gender=‘female‘ AND ge<50;

-- 2.3　查詢學號為1，或者姓名為王的記錄
SELECT * FROM stu WHERE sid =‘1‘ OR sname=‘王‘;

-- 2.4　查詢學號為1，2，3的記錄
SELECT * FROM stu WHERE sid IN (‘1‘,‘2‘,‘3‘);

-- 2.5　查詢學號不是1,2,3的記錄
SELECT * FROM tab_student WHERE s_number NOT IN (‘1‘,‘2‘,‘3‘);

-- 2.6　查詢年齡為22的記錄
SELECT * FROM stu WHERE age IS 22;

-- 2.7　查詢年齡在20到24之間的學生記錄
SELECT * FROM stu WHERE age>=20 AND age<=24;
-- 或者
SELECT * FROM stu WHERE age BETWEEN 20 AND 24;
-- 或者
SELECT * FROM stu WHERE age(20,21,22,23,24);

-- 2.8　查詢性別非男的學生記錄
SELECT * FROM stuWHERE gender!=‘男‘;
-- 或者
SELECT * FROM stuWHERE gender<>‘男‘;
-- 或者
SELECT * FROM stuWHERE NOT gender=‘男‘;
-- 或者
SELECT * FROM stuWHERE gender=‘女‘;

-- 2.9　查詢姓名不為null的學生記錄
SELECT * FROM stu WHERE NOT sname IS NULL;
-- 或者
SELECT * FROM stu WHERE sname IS NOT NULL;

 

 

-- 3　模糊查詢
-- 3.1　查詢姓名由5個字母構成的學生記錄
SELECT * FROM stu WHERE sname LIKE ‘_____‘;

-- 3.2　查詢姓名由5個字母構成，並且第5個字母為“i”的學生記錄
SELECT * FROM stu WHERE sname LIKE ‘____i‘;

-- 3.3　查詢姓名以“張”開頭的學生記錄
SELECT * FROM stu WHERE sname LIKE ‘張%‘;

-- 3.4　查詢姓名中第2個字母為“小”的學生記錄
SELECT * FROM stu WHERE sname LIKE ‘_小%‘;

-- 3.5　查詢姓名中包含“麗”字母的學生記錄
SELECT * FROM stu WHERE sname LIKE ‘%麗%‘;

 

-- 4　欄位控制查詢
-- 4.1　去除重複記錄
SELECT DISTINCT sal FROM emp;

-- 4.2　查看僱員的月薪與傭金之和 　
SELECT *,sal+comm FROM emp;

-- comm列有很多記錄的值為NULL，因為任何東西與NULL相加結果還是NULL，所以結算結果可能會出現NULL。下面使用了把NULL轉換成數值0的函數IFNULL：
SELECT *,sal+IFNULL(comm,0) FROM emp;

--- 4.3　給列名添加別名
-- 在上面查詢中出現列名為sal+IFNULL(comm,0)，這很不美觀，現在我們給這一列給出一個別名，為total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

-- 給列起別名時，是可以省略AS關鍵字的：
SELECT *,sal+IFNULL(comm,0) total FROM emp;

 

 

-- 5　排序 ORDER BY
-- 5.1　查詢所有學生記錄，按年齡升序排序
SELECT * FROM stu ORDER BY sage ASC;
-- 或者
SELECT * FROM stu ORDER BY sage;

-- 5.2　查詢所有學生記錄，按年齡降序排序
SELECT * FROM stu ORDER BY age DESC;

-- 5.3　查詢所有僱員，按月薪降序排序，如果月薪相同時，按編號升序排序
SELECT * FROM empORDER BY sal DESC,empno ASC;

 

-- 6.1　COUNT
-- 查詢emp表中記錄數：
SELECT COUNT(*) AS cnt FROM emp;

-- 查詢emp表中有傭金的人數：
SELECT COUNT(comm) cnt FROM emp;

-- 查詢emp表中月薪大於2500的人數：
SELECT COUNT(*) FROM empWHERE sal > 2500;

-- 統計月薪與傭金之和大於2500元的人數：
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

-- 查詢有傭金的人數，以及有領導的人數：
SELECT COUNT(comm), COUNT(mgr) FROM emp;

-- 6.2　SUM和AVG
-- 查詢所有僱員月薪和：
SELECT SUM(sal) FROM emp;

-- 查詢所有僱員月薪和，以及所有僱員傭金和：
SELECT SUM(sal), SUM(comm) FROM emp;

-- 查詢所有僱員月薪+傭金和：
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

-- 統計所有員工平均工資：
SELECT SUM(sal), COUNT(sal) FROM emp;
-- 或者
SELECT AVG(sal) FROM emp;

-- 6.3　MAX和MIN
-- 查詢最高工資和最低工資：
SELECT MAX(sal), MIN(sal) FROM emp;

 

 

-- 7.1　分組查詢
-- 查詢每個部門的部門編號和每個部門的工資和：
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

-- 查詢每個部門的部門編號以及每個部門的人數：
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

-- 查詢每個部門的部門編號以及每個部門工資大於1500的人數：
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

-- 7.2　HAVING子句
-- 查詢工資總和大於9000的部門編號以及工資和：
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

 

-- 8.1　查詢5行記錄，起始行從0開始
SELECT * FROM emp LIMIT 0, 5;

-- 8.2　查詢10行記錄，起始行從3開始
SELECT * FROM emp LIMIT 3, 10;

 

8.3　分頁查詢 規則
（頁數-1）*行數=第二頁的第一個顯示行數。   從幾行開始就寫幾。（前面是起始行數，後面是顯示行數）
8.3　分頁查詢 
查詢語句書寫順序：select – from- where- group by- having- order by-limit 
查詢語句執行順序：from - where -group by - having - select - order by-limit

 

mysql語句基本簡單查詢