mysql表操作與查詢功能詳解

更新時(shí)間：2025年06月28日 11:50:11 作者：QQ_437664314

本文系統(tǒng)講解MySQL表操作與查詢,涵蓋創(chuàng)建、修改、復(fù)制表語(yǔ)法,基本查詢結(jié)構(gòu)及WHERE、GROUP BY等子句,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧

01.表的操作

1.1表操作概覽

1.2創(chuàng)建表

語(yǔ)法： 語(yǔ)句以;結(jié)尾。

#  []表示里面內(nèi)容可選，使用時(shí)不帶[]本身
CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
) [ENGINE=storage_engine] [DEFAULT CHARSET=charset];

數(shù)據(jù)類型：
- 數(shù)值類型：INT、BIGINT、FLOAT、DOUBLE、DECIMAL(10,2)
- 字符串類型：VARCHAR(255)、TEXT、CHAR(10)
- 日期時(shí)間：DATE、TIME、DATETIME、TIMESTAMP
- 其他類型：
  - BOOLEAN
  - ENUM (’ opt1’,’ opt2’)只能存儲(chǔ)定義時(shí)指定的值之一,單選
  - SET (’ opt1’,’ opt2’,’ opt3’)，多選
列約束：
- NOT NULL`：該字段不能為空
- UNIQUE：該字段值必須唯一
- DEFAULT：設(shè)置默認(rèn)值
- AUTO_INCREMENT：自增主鍵（通常用于 ID 字段）

表約束： 主外鍵

PRIMARY KEY (列1, ...),
FOREIGN KEY (字段名) REFERENCES 主表(列),

創(chuàng)建主表： 主鍵時(shí)表的身份證，唯一，可由多個(gè)字段組成。

create table myclass(
	id int primary key,
	name varchar(30)not null comment'班級(jí)名'
);

創(chuàng)建從表： 外鍵是與其他表的關(guān)系紐帶。

create table stu(
	id int primary key,
	name varchar(30) not nu1l comment'學(xué)生名',
    class_id int ,
	foreign key(class_id) references myclass(id)
	);   #插入數(shù)據(jù)過(guò)程省略

創(chuàng)建表代碼示例：

-- 1：創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE sql_stu;
-- 2：使用數(shù)據(jù)庫(kù)
USE sql_stu;
-- 3：創(chuàng)建表
CREATE TABLE IF NOT EXISTS table_stu (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    age INT DEFAULT 18,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 使用 DESCRIBE 表名 或 SHOW CREATE TABLE 表名 查看表
DESCRIBE table_name;-- 或簡(jiǎn)寫為：DESC table_name;

1.3修改表

語(yǔ)法：

ALTER TABLE 表名
    操作1,
    ...;

修改表的操作：

ALTER TABLE employees 
#添加列
ADD COLUMN 列名 數(shù)據(jù)類型 AFTER 某個(gè)列;
#修改列數(shù)據(jù)類型與重命名
MODIFY COLUMN 列名 數(shù)據(jù)類型;
CHANGE COLUMN 老列名 新列名 數(shù)據(jù)類型;
#刪除列
DROP COLUMN 列名;
#添加約束
ADD PRIMARY KEY (emp_id);
---
外鍵？
#刪約束
DROP FOREIGN KEY 外鍵名;
DROP PRIMARY KEY;

1.4復(fù)制表

#僅復(fù)制結(jié)構(gòu)
CREATE TABLE 新表 LIKE 源表;
#復(fù)制結(jié)構(gòu)+數(shù)據(jù)
CREATE TABLE 新表 AS 
SELECT * FROM 源表;
#選擇復(fù)制結(jié)構(gòu)+數(shù)據(jù)
CREATE TABLE 新表 AS 
SELECT 列1，列2，列3 
FROM 源表
WHERE 條件;

-- 創(chuàng)建表
CREATE TABLE stuinfo (
    stuid INT PRIMARY KEY,
    stuname VARCHAR(20) UNIQUE NOT NULL,
    stugender CHAR(1) DEFAULT '男',
    email VARCHAR(20) NOT NULL,
    age INT,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)
);
-- 修改表結(jié)構(gòu)
ALTER TABLE stuinfo RENAME TO stuinfo1;  -- 重命名表
ALTER TABLE stuinfo1 ADD COLUMN borndate TIMESTAMP NOT NULL;  -- 添加列
ALTER TABLE stuinfo1 CHANGE COLUMN borndate birthday DATETIME;  -- 修改列名
ALTER TABLE stuinfo1 MODIFY COLUMN birthday DATE;  -- 修改數(shù)據(jù)類型
ALTER TABLE stuinfo1 DROP COLUMN age;  -- 刪除列
-- 復(fù)制表
CREATE TABLE newTable LIKE stuinfo;  -- 僅復(fù)制結(jié)構(gòu)
CREATE TABLE emp_copy SELECT * FROM employees;  -- 復(fù)制結(jié)構(gòu)和數(shù)據(jù)

02.基本查詢操作

基礎(chǔ)結(jié)構(gòu)：

SELECT [DISTINCT] 列1, 列2, ...  --選擇需要顯示的列
FROM 表名    				     --首先確定數(shù)據(jù)來(lái)源
[WHERE 條件]     	 		      --對(duì)原始數(shù)據(jù)進(jìn)行篩選
[GROUP BY 分組列]   			 --對(duì)篩選后的數(shù)據(jù)分組
[HAVING 分組條件]   		    --對(duì)分組后的數(shù)據(jù)進(jìn)行篩選
[ORDER BY 排序列 [ASC|DESC]]     --對(duì)結(jié)果進(jìn)行排序
[LIMIT [偏移量,] 行數(shù)];          --限制返回結(jié)果數(shù)量

執(zhí)行順序 ：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

2.1 SELECT選擇列

-- 查詢所有列
SELECT * FROM users;
-- 查詢指定列
SELECT id, ... FROM users;
-- 列別名
SELECT id AS user_id, username AS name FROM users;--（AS可省略）
-- 去重查詢（DISTINCT）
SELECT DISTINCT country FROM customers;
-- 連接字段 CONCAT
SELECT CONCAT(last_name, first_name) AS 姓名 FROM employees;

2.2 FROM指定表

-- 單表查詢
SELECT * FROM products;
-- 多表連接查詢（內(nèi)連接）
SELECT * 
FROM orders
INNER JOIN users ON orders.user_id = users.id;
SELECT IFNULL(commission_pct, 0.00) AS 獎(jiǎng)金, commission_pct
FROM employees;

2.3 WHERE條件過(guò)濾

–> 在分組前過(guò)濾行

2.3.1 基本條件表達(dá)式

/* 語(yǔ)法結(jié)構(gòu)：
   SELECT 查詢列表
   FROM 表名
   WHERE 篩選條件   */
-- 比較運(yùn)算符：> < = != <> >= <=
SELECT * FROM employees WHERE salary > 12000;
-- 邏輯運(yùn)算符：AND OR NOT
SELECT last_name, salary
FROM employees
WHERE salary >= 10000 AND salary <= 20000;
-- 范圍查詢 BETWEEN AND，或者使用 salary > 10000 AND salary < 20000
SELECT * 
FROM employees
WHERE salary BETWEEN 10000 AND 20000;

2.3.2 特殊條件查詢

-- IN 查詢，IN ()任意一個(gè)返回TRUE(1)
SELECT last_name, job_id
FROM employees
WHERE job_id IN ('AD_PRES', 'IT_PROG', 'PU_CLERK');
-- 是 NULL 值判斷
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct IS NULL;  -- IS NOT NULL
-- 安全等于 <=> (可判斷NULL和普通值)
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct <=> NULL;

2.3.3 模糊查詢 LIKE

使用% 匹配任意個(gè)字符, _ 匹配單個(gè)字符,使用 \ 轉(zhuǎn)義。

-- 基本通配符：% 匹配任意個(gè)字符, _ 匹配單個(gè)字符
SELECT last_name
FROM employees
WHERE last_name LIKE '_a_%' ESCAPE 'a';  -- 第二個(gè)字符為_(kāi)
-- 使用 \ 轉(zhuǎn)義
...同上
WHERE last_name LIKE '_\_%';  -- 使用 \ 轉(zhuǎn)義

2.4 GROUP BY分組

-- 統(tǒng)計(jì)每個(gè)部門的員工數(shù)
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
-- 分組后過(guò)濾（HAVING）
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING avg_price > 200;  -- 只返回平均價(jià)格>200的分組

2.5 ORDER BY排序

-- 基本排序
SELECT * FROM employees ORDER BY salary DESC;  --ASC 升序(默認(rèn))， DESC （drop降序）
-- 多字段排序
SELECT *
FROM employees
ORDER BY salary DESC, employee_id ASC;
-- 按表達(dá)式和函數(shù)結(jié)果多字段排序
SELECT 
    LENGTH(last_name)  字符長(zhǎng)度,  
    salary * 12 * (1 + IFNULL(commission_pct, 0))  年薪
FROM employees
ORDER BY 年薪 DESC, 字符長(zhǎng)度 DESC;  -- 按年薪和字符長(zhǎng)度排序

2.6 HAVING 分組后過(guò)濾

–> 分組后過(guò)濾組

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 6000;

2.7 LIMIT分頁(yè)

-- 提取第2頁(yè)，每頁(yè)10條           偏移量 每頁(yè)行數(shù)
 SELECT  *  FROM  products LIMIT 10  ,  10;  -- 等價(jià)于 OFFSET 10 LIMIT 10
-- 帶排序的分頁(yè)
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 20, 10;  -- 第3頁(yè)

03. 函數(shù)

3.1 聚合函數(shù)

函數(shù)	描述
`SUM(expr)`	求和
`AVG(expr)`	平均值
`MAX(expr)`	最大值
`MIN(expr)`	最小值
`COUNT(expr)`	計(jì)數(shù)

3.2 日期函數(shù)

SQL 標(biāo)準(zhǔn)函數(shù)

3.3 字符串函數(shù)

語(yǔ)法	功能描述
`CHARSET(str)`	返回字符串的字符集
`CONCAT(str1, str2, ...)`	連接多個(gè)字符串
`INSTR(str, substr)`	返回子串在字符串中的位置（從`1`開(kāi)始），未找到返回`0`
`UCASE(str)` 或 `UPPER(str)`	串轉(zhuǎn)換為大寫
`LCASE(str)` 或 `LOWER(str)`	串轉(zhuǎn)換為小寫
`LEFT(str, length)`	從字符串左側(cè)截取指定長(zhǎng)度的子串
`LENGTH(str)`	返回字符串的字節(jié)長(zhǎng)度（非字符數(shù)）
`REPLACE(str, from_str, to_str)`	替換字符串中的指定子串
`STRCMP(str1, str2)`	逐字符比較兩字符串大小（返回-`1`,`0`,`1`）
`SUBSTRING(str, pos, len)`	從指定位置截取子串（`pos`從`1`開(kāi)始）
`TRIM(str)` `LTRIM(str)` `RTRIM(str)`	去除字符串前或后面空格

3.4 數(shù)學(xué)函數(shù)

04.復(fù)合查詢

笛卡爾積： 將兩個(gè)表窮舉的結(jié)果

4.1 多表查詢

單行子查詢是指子查詢只返回單列，單行數(shù)據(jù);多行子查詢是指返回單列多行數(shù)據(jù)，都是針對(duì)單列而言的，而多列子查詢則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語(yǔ)句。

通過(guò)將兩個(gè)表直接笛卡爾積組成一個(gè)新表，但是引入了許多無(wú)關(guān)數(shù)據(jù)，這時(shí)對(duì)其進(jìn)行去除不正確數(shù)據(jù)后再篩選即可得到

顯示部門號(hào)為10的部門名，員工名和工資

select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno andDEPT.deptno = 10;

4.2 自連接

自連接是指在同一張表連接查詢

4.3 子查詢

子查詢是指嵌入在其他sql語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢。

4.3.1 單行子查詢

案例: 查詢和SMITH的部門和崗位完全相同的所有雇員。下面這個(gè)案例返回了多行，視情況而定

4.3.2 多行子查詢

IN/NOT IN 檢查值是否在于子查詢結(jié)果中
ANY 與子查詢返回的任一值比較
ALL 與子查詢返回的所有值比較
EXISTS 檢查存在性

示例：

SELECT *
FROM products
WHERE (category, price) IN (SELECT category, price FROM products WHERE category = 'Electronics');

4.3.3 FROM子句子查詢

-- from子查詢 (返回臨時(shí)表)
SELECT dep_ag.department_id, dep_ag.ag, j.grade_level
FROM (
    SELECT AVG(salary) ag, department_id
    FROM employees
    GROUP BY department_id
) AS dep_ag
INNER JOIN job_grades j ON dep_ag.ag BETWEEN j.lowest_sal AND j.highest_sal;

4.3.4 合并查詢

聯(lián)合查詢 :

UNION :并集<-- 該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。
UNION ALL :該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，不會(huì)去掉結(jié)果集中的重復(fù)行。

-- 基本聯(lián)合
SELECT employee_id FROM employees WHERE salary > 15000
UNION
SELECT employee_id FROM employees WHERE commission_pct > 0.2;
-- UNION ALL (保留重復(fù)記錄)
SELECT department_id FROM employees
UNION ALL
SELECT department_id FROM departments;

05. 內(nèi)外連接

5.1內(nèi)連接

內(nèi)連接實(shí)際上就是利用where子句對(duì)兩種表形成的笛卡兒積進(jìn)行篩選。

語(yǔ)法：

select 字段 from 表1 inner join 表2 on 連接條件 and 其他條件;

示例：

--用前面的寫法
select ename,dname from EMP, DEPT where EMp.deptno=DEPT.deptno andename='SMITH'
--用標(biāo)準(zhǔn)的內(nèi)連接寫法
select ename, dname from EMp inner join DEPT on EMP.deptno=DEPT.deptno andename='SMITH':