MySQL資料庫進階版
- MySQL資料庫進階版是在MySQL資料庫的基礎操作的基礎上推出的
- MySQL資料庫的基礎操作文章鏈接：MySQL資料庫的基礎操作
一、資料庫的約束
- 1.NULL約束
指定屬性的陳述句不能為NULL
//建表，準備作業
mysql> drop table if exists student;
Query OK, 0 rows affected (0.05 sec)
mysql> create table student(
->id int,
->sn int,
->name varchar(20) not null,
->qq_mail varchar(20)
->);
Query OK, 0 rows affected (0.15 sec)
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| student |
+-------------------------+
1 row in set (0.00 sec)
//此時準備往表里插入元素
mysql> insert into student(id,sn,name,qq_mail)
-> values(1,101,NULL,'123@qq.com');
ERROR 1048 (23000): Column 'name' cannot be null
//但是報錯了，原因如下
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| sn | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
| qq_mail | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
//name這一列不能為NULL，否則報錯，這就是NULL約束
- 2.唯一約束 unique
指定屬性的陳述句不能插入兩次
//建表，準備作業
mysql> drop table if exists student;
Query OK, 0 rows affected (0.02 sec)mysql> create table student(
-> id int,
-> sn int unique,
-> name varchar(20) NOT NULL,
-> qq_mail varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
//添加第一條陳述句
mysql> insert into student(id,sn,name,qq_mail)
-> values(1,101,'bit','123@qq.com');
Query OK, 1 row affected (0.03 sec)
//添加后的效果
mysql> select * from student;
+------+------+------+------------+
| id | sn | name | qq_mail |
+------+------+------+------------+
| 1 | 101 | bit | 123@qq.com |
+------+------+------+------------+
1 row in set (0.00 sec)
//添加sn和第一條陳述句相同的第二條陳述句時：
mysql> insert into student(id,sn,name,qq_mail)
-> values(2,101,'bit2','1232@qq.com');
ERROR 1062 (23000): Duplicate entry '101' for key 'sn'
- 3.默認值約束 default:
在宣告屬性的時候就賦值，如果后面添加成員變數，則系統會默認賦值
//建表，準備作業
mysql> drop table if exists student;
Query OK, 0 rows affected (0.03 sec)
mysql> create table student(
-> id int,
-> sn int unique,
-> name varchar(20) NOT NULL,
-> qq_mail varchar(20) default '110@qq.com'
-> );
Query OK, 0 rows affected (0.05 sec)
//第一次給qq_mail賦值的情況：
mysql> insert into student(id,sn,name,qq_mail)
-> values(1,101,'bit','123@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+------+------+------------+
| id | sn | name | qq_mail |
+------+------+------+------------+
| 1 | 101 | bit | 123@qq.com |
+------+------+------+------------+
1 row in set (0.00 sec)
//第二次給qq_mail賦值為NULL的情況：
mysql> insert into student(id,sn,name,qq_mail)
-> values(2,102,'bit2',NULL);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+------+------+------------+
| id | sn | name | qq_mail |
+------+------+------+------------+
| 1 | 101 | bit | 123@qq.com |
| 2 | 102 | bit2 | NULL |
+------+------+------+------------+
2 rows in set (0.00 sec)
//第三次不給qq_mail賦值的情況：
mysql> insert into student(id,sn,name)
-> values(3,103,'bit3');
Query OK, 1 row affected (0.02 sec)
mysql> select * from student;
+------+------+------+------------+
| id | sn | name | qq_mail |
+------+------+------+------------+
| 1 | 101 | bit | 123@qq.com |
| 2 | 102 | bit2 | NULL |
| 3 | 103 | bit3 | 110@qq.com |
+------+------+------+------------+
3 rows in set (0.00 sec)
//發現不給qq_mail賦值時，系統會自動給qq_mail附上我們最開始設定的默認值
- 4.主鍵約束 primary key
是NOT NULL 和 unique的結合， 也就是說，當一個欄位被primary key
修飾后，那么這個欄位就是不能為空且是獨一無二的！！！ 一般搭配：auto_increment;
//建表，準備作業
mysql> drop table if exists student;
Query OK, 0 rows affected (0.02 sec)
mysql> create table student(
-> id int primary key auto_increment,
-> sn int unique,
-> name varchar(20) NOT NULL,
-> qq_mail varchar(20) DEFAULT '110@qq.com'
-> );
Query OK, 0 rows affected (0.04 sec)
//當創建好表之后，表中沒有任何的資料，當第一次執行插入的時候，當前主鍵，也就是ID，會自動從1開始
mysql> insert into student(sn,name,qq_mail)
-> values(101,'bit','123@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+------------+
| id | sn | name | qq_mail |
+----+------+------+------------+
| 1 | 101 | bit | 123@qq.com |
+----+------+------+------------+
1 row in set (0.00 sec)
//清除資料
mysql> delete from student where id = 1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
Empty set (0.00 sec)
//當我將剛剛插入的資料洗掉后，再次進行插入的時候，就會在原來的基礎，也就是上一次最后插入的陳述句的ID上開始加1
mysql> insert into student(sn,name,qq_mail)
-> values(101,'bit','123@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+------------+
| id | sn | name | qq_mail |
+----+------+------+------------+
| 2 | 101 | bit | 123@qq.com |
+----+------+------+------------+
1 row in set (0.00 sec)
//如果再想讓id=1，則需要洗掉表，重新來！！！
- 5.外鍵約束 foreign key
外鍵用于關聯其他表的主鍵或唯一鍵
- 語法：foreign key (欄位名) references 主表(列)
//1、建表
mysql> drop table if exists classes;
Query OK, 0 rows affected, 1 warning (0.00 sec)
//先建主表
mysql> create table classes(
-> id int primary key auto_increment,
-> name varchar(20),
-> `desc` varchar(30)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)
//再建附表
mysql> create table student(
-> id int PRIMARY KEY AUTO_INCREMENT,
-> sn int unique,
-> name varchar(20) NOT NULL,qq_mail varchar(20) DEFAULT '110@qq.com',
-> classes_id int,
-> foreign key (classes_id) references classes(id)
-> );
Query OK, 0 rows affected (0.05 sec)
//建表成功
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| classes |
| student |
+-------------------------+
2 rows in set (0.00 sec)
//2、插入（可以自增長）
//先插入主表
mysql> insert into classes (name,`desc`) values('java18','今天也要加油鴨！');
Query OK, 1 row affected (0.00 sec)
mysql> select * from classes;
+----+--------+--------------------------+
| id | name | desc |
+----+--------+--------------------------+
| 1 | java18 | 今天也要加油鴨！ |
+----+--------+--------------------------+
1 row in set (0.00 sec)
//再插入附表
mysql> insert into student (sn,name,qq_mail,classes_id) values
(101,'bit','123@qq.com',1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+------+------------+------------+
| 1 | 101 | bit | 123@qq.com | 1 |
+----+------+------+------------+------------+
1 row in set (0.00 sec)
//附表再次插入
mysql> insert into student (sn,name,qq_mail,classes_id) values
(102,'bit','365@qq.com',1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+------+------------+------------+
| 1 | 101 | bit | 123@qq.com | 1 |
| 2 | 102 | bit | 365@qq.com | 1 | //開始自增長
+----+------+------+------------+------------+
2 rows in set (0.00 sec)
//3、洗掉
//先洗掉子表中與主表想關聯的，或者洗掉沒有與子表關聯的主表
mysql> delete from student where id = 1 or id = 2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
Empty set (0.00 sec)
//再洗掉主表
mysql> delete from classes where id = 1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from classes;
Empty set (0.00 sec)
- 6.check約束
mysql> drop table if exists test_user;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table test_user (
-> id int,
-> name varchar(20),
-> sex varchar(1),
-> check (sex ='男' or sex='女') //表示在sex只能插入‘男’or‘女’，否則系統報錯
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| classes |
| student |
| test_user |
+-------------------------+
3 rows in set (0.00 sec)
mysql> insert into test_user values(1,'bit','哈');
Query OK, 1 row affected (0.01 sec)
//但是在MySQL上并不會報錯！！！
二、進階查詢
建表，準備作業
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| huashanzhizai |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use huashanzhizai;
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| classes |
| exam |
| student |
| test_user |
+-------------------------+
4 rows in set (0.00 sec)
mysql> select * from exam;
Empty set (0.00 sec)
mysql> DROP TABLE IF EXISTS exam;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE exam (
-> id INT,
-> name VARCHAR(20),
-> chinese DECIMAL(3,1),
-> math DECIMAL(3,1),
-> english DECIMAL(3,1)
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO exam (id,name, chinese, math, english) VALUES
-> (1,'唐三藏', 67, 98, 56),
-> (2,'孫悟空', 87.5, 78, 77),-> (3,'豬悟能', 88, 98.5, 90),
-> (4,'曹孟德', 82, 84, 67),
-> (5,'劉玄德', 55.5, 85, 45),
-> (6,'孫權', 70, 73, 78.5),
-> (7,'宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孫悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 豬悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 劉玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孫權 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
+------+-----------+---------+------+---------+
7 rows in set (0.00 sec)
- 1.聚合函式
//1、查詢資料的數量
mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.01 sec)
mysql> select count(0) from exam;
+----------+
| count(0) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) from exam;
+----------+
| count(1) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
//count(*)中*并不特指
//又例如：
mysql> insert into exam (id,name,chinese,math,english) values
-> (1,'唐三藏',67,98,56);
Query OK, 1 row affected (0.01 sec)
mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
//2、對計數的列進行去重
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孫悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 豬悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 劉玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孫權 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
mysql> select count(distinct id) from exam; **先去重再計數
+--------------------+
| count(distinct id) |
+--------------------+
| 7 |
+--------------------+
1 row in set (0.01 sec)
//3、sum 加權求和
mysql> select sum(math) from exam;
+-----------+
| sum(math) |
+-----------+
| 679.5 |
+-----------+
1 row in set (0.00 sec)
mysql> select sum(math)from exam where math < 70;
+-----------+
| sum(math) |
+-----------+
| 65.0 |
+-----------+
1 row in set (0.00 sec)
//4、avg 查詢平均成績
mysql> select avg(math) from exam;
+-----------+
| avg(math) |
+-----------+
| 84.93750 |
+-----------+
1 row in set (0.00 sec)
//5、max 查詢最大值
mysql> select max(math) from exam;
+-----------+
| max(math) |
+-----------+
| 98.5 |
+-----------+
1 row in set (0.00 sec)
//6、min 查詢最小值
mysql> select min(math) from exam;
+-----------+
| min(math) |
+-----------+
| 65.0 |
+-----------+
1 row in set (0.00 sec)
//注意點:在where后面，不要出現聚合函式
mysql> select id,name from exam where max(math) > 60;
ERROR 1111 (HY000): Invalid use of group function
- 2.分組 group by
準備資料
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| huashanzhizai |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use huashanzhizai;
Database changed
mysql> drop tables if exists emp;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create table emp(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> role varchar(20) not null,
-> salary numeric(11,2)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into emp(name, role, salary) values
-> ('鵬哥','講師', 1000.20),
-> ('高博','講師', 2000.99),
-> ('老湯','講師', 999.11),
-> ('靜靜','班主任', 333.5),
-> ('莎莎姐','班主任', 700.33),
-> ('隔壁老王','市場', 12000.66);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from emp;
+----+--------------+-----------+
| id | name | role | salary |
+----+--------------+-----------+
| 1 | 鵬哥 | 講師 | 1000.20 |
| 2 | 高博 | 講師 | 2000.99 |
| 3 | 老湯 | 講師 | 999.11 |
| 4 | 靜靜 | 班主任 | 333.50 |
| 5 | 莎莎姐 | 班主任 | 700.33 |
| 6 | 隔壁老王 | 市場 | 12000.66 |
+----+--------------+----------+
6 rows in set (0.00 sec)
查詢每個角色的最高工資、最低工資和平均工資
mysql> select role,max(salary),min(salary),avg(salary) from emp group by role;
+-----------+-------------+-------------+--------------+
| role | max(salary) | min(salary) | avg(salary) |
+-----------+-------------+-------------+--------------+
| 市場 | 12000.66 | 12000.66 | 12000.660000 |
| 班主任 | 700.33 | 333.50 | 516.915000 | //同為班主任，這個類的最高，最低，平均
| 講師 | 2000.99 | 999.11 | 1333.433333 |
+-----------+-------------+-------------+--------------+
3 rows in set (0.03 sec)
//不正確表達
//區別
mysql> select max(salary),min(salary),avg(salary)from emp;
+-------------+-------------+-------------+
| max(salary) | min(salary) | avg(salary) |
+-------------+-------------+-------------+
| 12000.66 | 333.50 | 2839.131667 | //這個是在一個表中的最高，最低，平均
+-------------+-------------+-------------+
1 row in set (0.00 sec)
- 過濾條件 having
mysql> select role,max(salary),min(salary),avg(salary) from emp group by role
having avg(salary) < 1500;
+-----------+-------------+-------------+-------------+
| role | max(salary) | min(salary) | avg(salary) |
+-----------+-------------+-------------+-------------+
| 班主任 | 700.33 | 333.50 | 516.915000 |
| 講師 | 2000.99 | 999.11 | 1333.433333 |
+-----------+-------------+-------------+-------------+
2 rows in set (0.00 sec)
- 3.聯合查詢
（ 兩張表或者兩張以上的表，進行連接查詢）
準備作業
mysql> drop database if exists test0311;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database test0311;
Query OK, 1 row affected (0.01 sec)
mysql> use test0311;
Database changed
mysql> drop table if exists classes;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table classes(
-> id int primary key auto_increment,
-> name varchar(50),
-> `desc` varchar(50)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into classes(name, `desc`) values
-> ('計算機系2019級1班', '學習了計算機原理、C和Java語言、資料結構和演算法'),
-> ('中文系2019級3班','學習了中國傳統文學'),
-> ('自動化2019級5班','學習了機械自動化');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table student(
-> id int primary key auto_increment,
-> sn int,
-> name varchar(30),
-> qq_mail varchar(30),
-> classes_id int
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> insert into student(sn, name, qq_mail, classes_id) values
-> ('09982','黑旋風李逵','xuanfeng@qq.com',1),
-> ('00835','菩提老祖',null,1),
-> ('00391','白素貞',null,1),
-> ('00031','許仙','xuxian@qq.com',1),
-> ('00054','不想畢業',null,1),
-> ('51234','好好說話','say@qq.com',2),
-> ('83223','tellme',null,2),
-> ('09527','老外學中文','foreigner@qq.com',2);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> drop table if exists course;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table course(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into course(name) values
-> ('Java'),('中國傳統文化'),('計算機原理'),('語文'),('高階數學'),('英文');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> drop table if exists score;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table score(
-> id int primary key auto_increment,
-> score DECIMAL,
-> student_id int,
-> course_id int
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into score(score, student_id, course_id) values
-> -- 黑旋風李逵
-> (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-> -- 菩提老祖-> (60, 2, 1),(59.5, 2, 5),
-> -- 白素貞
-> (33, 3, 1),(68, 3, 3),(99, 3, 5),
-> -- 許仙
-> (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-> -- 不想畢業
-> (81, 5, 1),(37, 5, 5),
-> -- 好好說話
-> (56, 6, 2),(43, 6, 4),(79, 6, 6),
-> -- tellme
-> (80, 7, 2),(92, 7, 6);
Query OK, 20 rows affected, 3 warnings (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 3
//查看已建好的表
mysql> show tables;
+--------------------+
| Tables_in_test0311 |
+--------------------+
| classes |
| course |
| score |
| student |
+--------------------+
4 rows in set (0.00 sec)
mysql> select * from classes;
+----+-------------------------+-------------------------------------------------------------------+
| id | name |desc |
+----+-------------------------+-------------------------------------------------------------------+
| 1 | 計算機系2019級1班 | 學習了計算機原理、C和Java語言、資料結構和演算法 |
| 2 | 中文系2019級3班 | 學習了中國傳統文學 |
| 3 | 自動化2019級5班 | 學習了機械自動化 |
+----+-------------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from course;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中國傳統文化 |
| 3 | 計算機原理 |
| 4 | 語文 |
| 5 | 高階數學 |
| 6 | 英文 |
+----+--------------------+
6 rows in set (0.00 sec)
mysql> select * from score;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 1 | 71 | 1 | 1 |
| 2 | 99 | 1 | 3 |
| 3 | 33 | 1 | 5 |
| 4 | 98 | 1 | 6 |
| 5 | 60 | 2 | 1 |
| 6 | 60 | 2 | 5 |
| 7 | 33 | 3 | 1 |
| 8 | 68 | 3 | 3 |
| 9 | 99 | 3 | 5 |
| 10 | 67 | 4 | 1 |
| 11 | 23 | 4 | 3 |
| 12 | 56 | 4 | 5 |
| 13 | 72 | 4 | 6 |
| 14 | 81 | 5 | 1 |
| 15 | 37 | 5 | 5 |
| 16 | 56 | 6 | 2 |
| 17 | 43 | 6 | 4 |
| 18 | 79 | 6 | 6 |
| 19 | 80 | 7 | 2 |
| 20 | 92 | 7 | 6 |
+----+-------+------------+-----------+
20 rows in set (0.00 sec)
mysql> select * from student;
+----+-------+-----------------+------------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+-------+-----------------+------------------+------------+
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 |
| 3 | 391 | 白素貞 | NULL | 1 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 |
| 5 | 54 | 不想畢業 | NULL | 1 |
| 6 | 51234 | 好好說話 | say@qq.com | 2 |
| 7 | 83223 | tellme | NULL | 2 |
| 8 | 9527 | 老外學中文 | foreigner@qq.com | 2 |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.00 sec)
內連接
語法1：select 欄位 from 表1 別名1 [inner] join 表2 別名2 on 連接條件 and 其他條件；
語法2：select 欄位 from 表1 別名1，表2 別名2 where 連接條件 and 其他條 件；
查詢“許仙”同學的 成績（逐漸增加約束條件）
語法1：
//查詢student和score兩個表所有的笛卡爾積
mysql> select * from student inner join score;
...//太多，就不一一展示了
...
...
160 rows in set (0.01 sec)
//顯示student表中id和score表中student_id兩個相等的對應的值
mysql> select * from student inner join score on student.id = score.student_id;
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
| id | sn | name | qq_mail | classes_id | id | score |student_id | course_id |
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 1 | 71 | 1 | 1 |
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 2 | 99 | 1 | 3 |
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 3 | 33 | 1 | 5 |
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 4 | 98 | 1 | 6 |
| 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60 | 2 | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 | 6 | 60 | 2 | 5 |
| 3 | 391 | 白素貞 | NULL | 1 | 7 | 33 | 3 | 1 |
| 3 | 391 | 白素貞 | NULL | 1 | 8 | 68 | 3 | 3 |
| 3 | 391 | 白素貞 | NULL | 1 | 9 | 99 | 3 | 5 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 10 | 67 | 4 | 1 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 11 | 23 | 4 | 3 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 12 | 56 | 4 | 5 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 13 | 72 | 4 | 6 |
| 5 | 54 | 不想畢業 | NULL | 1 | 14 | 81 | 5 | 1 |
| 5 | 54 | 不想畢業 | NULL | 1 | 15 | 37 | 5 | 5 |
| 6 | 51234 | 好好說話 | say@qq.com | 2 | 16 | 56 | 6 | 2 |
| 6 | 51234 | 好好說話 | say@qq.com | 2 | 17 | 43 | 6 | 4 |
| 6 | 51234 | 好好說話 | say@qq.com | 2 | 18 | 79 | 6 | 6 |
| 7 | 83223 | tellme | NULL | 2 | 19 | 80 | 7 | 2 |
| 7 | 83223 | tellme | NULL | 2 | 20 | 92 | 7 | 6 |
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
20 rows in set (0.01 sec)
//顯示student和score兩個表的所有內容
mysql> select * from student inner join score on student.id = score.student_id
and student.id = 4;
+----+------+--------+---------------+------------+----+-------+------------+-----------+
| id | sn | name | qq_mail | classes_id | id | score | student_id |course_id |
+----+------+--------+---------------+------------+----+-------+------------+-----------+
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 10 | 67 | 4 | 1 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 11 | 23 | 4 | 3 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 12 | 56 | 4 | 5 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 13 | 72 | 4 | 6 |
+----+------+--------+---------------+------------+----+-------+------------+-----------+
4 rows in set (0.01 sec)
//顯示student和score兩個表的選定的內容
mysql> select student.id,student.name,score.score from student inner join score
on student.id = score.student_id and student.id = 4;
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 4 | 許仙 | 67 |
| 4 | 許仙 | 23 |
| 4 | 許仙 | 56 |
| 4 | 許仙 | 72 |
+----+--------+-------+
4 rows in set (0.00 sec)
//用score表的course_id將course表也連接起來
mysql> select student.id,student.name,score.score,course.name from student inner
join score on student.id = score.student_id inner join course on score.course_id
= course.id and student.id = 4;
+----+--------+-------+-----------------+
| id | name | score | name |
+----+--------+-------+-----------------+
| 4 | 許仙 | 67 | Java |
| 4 | 許仙 | 23 | 計算機原理 |
| 4 | 許仙 | 56 | 高階數學 |
| 4 | 許仙 | 72 | 英文 |
+----+--------+-------+-----------------+
4 rows in set (0.00 sec)
//修改名字
mysql> select student.id,student.name as 姓名,score.score as 分數,course.name as 課程表
from student inner join score on student.id = score.student_id inner join course
on score.course_id = course.id and student.id = 4;
+----+--------+--------+-----------------+
| id | 姓名 | 分數 | 課程表 |
+----+--------+--------+-----------------+
| 4 | 許仙 | 67 | Java |
| 4 | 許仙 | 23 | 計算機原理 |
| 4 | 許仙 | 56 | 高階數學 |
| 4 | 許仙 | 72 | 英文 |
+----+--------+--------+-----------------+
4 rows in set (0.00 sec)
語法2：
mysql> select student.id,student.name as 姓名,score.score as 分數,course.name as 課程表
-> from student,score,course where student.id = score.student_id
-> and score.course_id = course.id
-> and student.id = 4;
+----+--------+--------+-----------------+
| id | 姓名 | 分數 | 課程表 |
+----+--------+--------+-----------------+
| 4 | 許仙 | 67 | Java |
| 4 | 許仙 | 23 | 計算機原理 |
| 4 | 許仙 | 56 | 高階數學 |
| 4 | 許仙 | 72 | 英文 |
+----+--------+--------+-----------------+
4 rows in set (0.00 sec)
外連接
（ 外連接分為左外連接和右外連接，如果聯合查詢，左側的表完全顯示我們就說是左 外連接；右側的表完全顯示我們就說是右外連接）
查詢所有同學的成績，及同學的個人資訊，如果該同學沒有成績，也需要顯示
①左外連接
mysql> select * from student,score;
...
...//結果太多不一一展示
...
160 rows in set (0.01 sec)
//通過student表中的id進行排序
mysql> select * from student,score group by student.id;
+----+-------+-----------------+------------------+------------+----+-------+------------+-----------+
| id | sn | name | qq_mail | classes_id | id | score |student_id | course_id |
+----+-------+-----------------+------------------+------------+----+-------+------------+-----------+
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 1 | 71 | 1 | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 | 1 | 71 | 1 | 1 |
| 3 | 391 | 白素貞 | NULL | 1 | 1 | 71 | 1 | 1 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 1 | 71 | 1 | 1 |
| 5 | 54 | 不想畢業 | NULL | 1 | 1 | 71 | 1 | 1 |
| 6 | 51234 | 好好說話 | say@qq.com | 2 | 1 | 71 | 1 | 1 |
| 7 | 83223 | tellme | NULL | 2 | 1 | 71 | 1 | 1 |
| 8 | 9527 | 老外學中文 | foreigner@qq.com | 2 | 1 | 71 | 1 | 1 |
+----+-------+-----------------+------------------+------------+----+-------+------------+-----------+
8 rows in set (0.01 sec)
//此時發現student表中的name老外學中文這一項沒有了，即沒有score.student_id與之匹配
mysql> select * from student,score where student.id = score.student_id group by
student.id;
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
| id | sn | name | qq_mail | classes_id | id | score |student_id | course_id |
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 1 | 71 | 1 | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60 | 2 | 1 |
| 3 | 391 | 白素貞 | NULL | 1 | 7 | 33 | 3 | 1 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 10 | 67 | 4 | 1 |
| 5 | 54 | 不想畢業 | NULL | 1 | 14 | 81 | 5 | 1 |
| 6 | 51234 | 好好說話 | say@qq.com | 2 | 16 | 56 | 6 | 2 |
| 7 | 83223 | tellme | NULL | 2 | 19 | 80 | 7 | 2 |
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
7 rows in set (0.00 sec)//利用左外連接可以將其進行顯示
mysql> select * from student left join score on student.id = score.student_id
group by student.id;
+----+-------+-----------------+------------------+------------+------+-------+------------+-----------+
| id | sn | name | qq_mail | classes_id | id | score |student_id | course_id |
+----+-------+-----------------+------------------+------------+------+-------+------------+-----------+
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 1 | 71 | 1 | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60 | 2 | 1 |
| 3 | 391 | 白素貞 | NULL | 1 | 7 | 33 | 3 | 1 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 | 10 | 67 | 4 | 1 |
| 5 | 54 | 不想畢業 | NULL | 1 | 14 | 81 | 5 | 1 |
| 6 | 51234 | 好好說話 | say@qq.com | 2 | 16 | 56 | 6 | 2 |
| 7 | 83223 | tellme | NULL | 2 | 19 | 80 | 7 | 2 |
| 8 | 9527 | 老外學中文 | foreigner@qq.com | 2 | NULL | NULL | NULL | NULL |
+----+-------+-----------------+------------------+------------+------+-------+------------+-----------+
8 rows in set (0.00 sec)
②右外連接
mysql> select * from score right join student on student.id = score.student_id
group by student.id;
+------+-------+------------+-----------+----+-------+-----------------+------------------+------------+
| id | score | student_id | course_id | id | sn | name |qq_mail | classes_id |
+------+-------+------------+-----------+----+-------+-----------------+------------------+------------+
| 1 | 71 | 1 | 1 | 1 | 9982 | 黑旋風李逵 |xuanfeng@qq.com | 1 |
| 5 | 60 | 2 | 1 | 2 | 835 | 菩提老祖 |NULL | 1 |
| 7 | 33 | 3 | 1 | 3 | 391 | 白素貞 |NULL | 1 |
| 10 | 67 | 4 | 1 | 4 | 31 | 許仙 |xuxian@qq.com | 1 |
| 14 | 81 | 5 | 1 | 5 | 54 | 不想畢業 |NULL | 1 |
| 16 | 56 | 6 | 2 | 6 | 51234 | 好好說話 |say@qq.com | 2 |
| 19 | 80 | 7 | 2 | 7 | 83223 | tellme |NULL | 2 |
| NULL | NULL | NULL | NULL | 8 | 9527 | 老外學中文 |foreigner@qq.com | 2 |
+------+-------+------------+-----------+----+-------+-----------------+------------------+------------+
8 rows in set (0.00 sec)
自連接 （ 自連接是指在同一張表連接自身進行查詢）
顯示所有“計算機原理”成績比“Java”成績高的成績資訊
mysql> select * from score as s1,score as s2 //這時候因為兩個表名字一樣，所以需要起別名
-> where s1.student_id = 1
-> and s2.student_id = 3
-> and s1.score < s2.score;
+----+-------+------------+-----------+----+-------+------------+-----------+
| id | score | student_id | course_id | id | score | student_id | course_id |
+----+-------+------------+-----------+----+-------+------------+-----------+
| 3 | 33 | 1 | 5 | 8 | 68 | 3 | 3 |
| 1 | 71 | 1 | 1 | 9 | 99 | 3 | 5 |
| 3 | 33 | 1 | 5 | 9 | 99 | 3 | 5 |
| 4 | 98 | 1 | 6 | 9 | 99 | 3 | 5 |
+----+-------+------------+-----------+----+-------+------------+-----------+
4 rows in set (0.00 sec)
//改進
mysql> select s2.* from score as s1,score as s2
->where s1.student_id = 1
->and s2.student_id = 3
->and s1.score < s2.score;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 8 | 68 | 3 | 3 |
| 9 | 99 | 3 | 5 |
| 9 | 99 | 3 | 5 |
| 9 | 99 | 3 | 5 |
+----+-------+------------+-----------+
4 rows in set (0.00 sec)
子查詢（ 子查詢是指嵌入在其他sql陳述句中的select陳述句，也叫嵌套查詢）
查詢與“不想畢業” 同學的同班同學
mysql> select * from student where classes_id = (select classes_id from student
where name = '不想畢業');
+----+------+-----------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+-----------------+-----------------+------------+
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 |
| 3 | 391 | 白素貞 | NULL | 1 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 |
| 5 | 54 | 不想畢業 | NULL | 1 |
+----+------+-----------------+-----------------+------------+
5 rows in set (0.01 sec)
//這句話等價于
mysql> select * from student where classes_id = 1;
+----+------+-----------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+-----------------+-----------------+------------+
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 |
| 3 | 391 | 白素貞 | NULL | 1 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 |
| 5 | 54 | 不想畢業 | NULL | 1 |
+----+------+-----------------+-----------------+------------+
5 rows in set (0.00 sec)
//多行子查詢：回傳多行記錄的子查詢,查詢“語文”或“英文”課程的成績資訊使用in
mysql> select * from score where course_id in(select id from course where name =
'語文' or name='英文');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 4 | 98 | 1 | 6 |
| 13 | 72 | 4 | 6 |
| 17 | 43 | 6 | 4 |
| 18 | 79 | 6 | 6 |
| 20 | 92 | 7 | 6 |
+----+-------+------------+-----------+
5 rows in set (0.00 sec)
//使用 not in
mysql> select * from score where course_id not in(select id from course where
name != '語文' and name !='英文');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 4 | 98 | 1 | 6 |
| 13 | 72 | 4 | 6 |
| 17 | 43 | 6 | 4 |
| 18 | 79 | 6 | 6 |
| 20 | 92 | 7 | 6 |
+----+-------+------------+-----------+
5 rows in set (0.00 sec)
//使用not exists（只要括號內的運算式為true，則就執行括號外的陳述句）
mysql> select * from student where exists(select id from student where id = 1);
+----+-------+-----------------+------------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+-------+-----------------+------------------+------------+
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 |
| 3 | 391 | 白素貞 | NULL | 1 |
| 4 | 31 | 許仙 | xuxian@qq.com | 1 |
| 5 | 54 | 不想畢業 | NULL | 1 |
| 6 | 51234 | 好好說話 | say@qq.com | 2 |
| 7 | 83223 | tellme | NULL | 2 |
| 8 | 9527 | 老外學中文 | foreigner@qq.com | 2 |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.02 sec)
//聯合查詢（求交集）
mysql> select * from student where id <= 3
-> union
-> select * from student where name = '白素貞';
+----+------+-----------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+-----------------+-----------------+------------+
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 |
| 3 | 391 | 白素貞 | NULL | 1 |
+----+------+-----------------+-----------------+------------+
3 rows in set (0.01 sec)
mysql> select * from student where id <= 3
-> union
-> select * from student where name = '好好說話';
+----+-------+-----------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+-------+-----------------+-----------------+------------+
| 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 |
| 3 | 391 | 白素貞 | NULL | 1 |
| 6 | 51234 | 好好說話 | say@qq.com | 2 |
+----+-------+-----------------+-----------------+------------+
4 rows in set (0.00 sec)
對于資料庫來說，用的最多的就是資料庫的查詢，即使在作業中，使用的最多的也將是資料庫的查詢，因為資料庫的添加和洗掉作業風險系數都會很高，同時參與的機會也不多，最后，希望大家再接再厲，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/278146.html
標籤：其他