mysql表操作與查詢功能詳解
01.表的操作
1.1表操作概覽
1.2創(chuàng)建表
語法: 語句以;
結(jié)尾。
# []表示里面內(nèi)容可選,使用時不帶[]本身 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)
- 日期時間:DATE、TIME、DATETIME、TIMESTAMP
- 其他類型:
- BOOLEAN
- ENUM (’
opt1
’,’opt2
’)只能存儲定義時指定的值之一,單選 - SET (’
opt1
’,’opt2
’,’opt3
’),多選
列約束:
- NOT NULL`:該字段不能為空
UNIQUE
:該字段值必須唯一DEFAULT
:設(shè)置默認值AUTO_INCREMENT
:自增主鍵(通常用于 ID 字段)
表約束: 主外鍵
PRIMARY KEY (列1, ...), FOREIGN KEY (字段名) REFERENCES 主表(列),
創(chuàng)建主表: 主鍵時表的身份證,唯一,可由多個字段組成。
create table myclass( id int primary key, name varchar(30)not null comment'班級名' );
創(chuàng)建從表: 外鍵是與其他表的關(guān)系紐帶。
create table stu( id int primary key, name varchar(30) not nu1l comment'學生名', class_id int , foreign key(class_id) references myclass(id) ); #插入數(shù)據(jù)過程省略
創(chuàng)建表代碼示例:
-- 1:創(chuàng)建數(shù)據(jù)庫 CREATE DATABASE sql_stu; -- 2:使用數(shù)據(jù)庫 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;-- 或簡寫為:DESC table_name;
1.3修改表
語法:
ALTER TABLE 表名 操作1, ...;
修改表的操作:
ALTER TABLE employees #添加列 ADD COLUMN 列名 數(shù)據(jù)類型 AFTER 某個列; #修改列數(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ù)來源 [WHERE 條件] --對原始數(shù)據(jù)進行篩選 [GROUP BY 分組列] --對篩選后的數(shù)據(jù)分組 [HAVING 分組條件] --對分組后的數(shù)據(jù)進行篩選 [ORDER BY 排序列 [ASC|DESC]] --對結(jié)果進行排序 [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 獎金, commission_pct FROM employees;
2.3 WHERE條件過濾
–> 在分組前過濾行
2.3.1 基本條件表達式
/* 語法結(jié)構(gòu): SELECT 查詢列表 FROM 表名 WHERE 篩選條件 */ -- 比較運算符:> < = != <> >= <= SELECT * FROM employees WHERE salary > 12000; -- 邏輯運算符: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 ()任意一個返回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
使用%
匹配任意個字符, _
匹配單個字符,使用 \
轉(zhuǎn)義。
-- 基本通配符:% 匹配任意個字符, _ 匹配單個字符 SELECT last_name FROM employees WHERE last_name LIKE '_a_%' ESCAPE 'a'; -- 第二個字符為_ -- 使用 \ 轉(zhuǎn)義 ...同上 WHERE last_name LIKE '_\_%'; -- 使用 \ 轉(zhuǎn)義
2.4 GROUP BY分組
-- 統(tǒng)計每個部門的員工數(shù) SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department; -- 分組后過濾(HAVING) SELECT category, AVG(price) AS avg_price FROM products GROUP BY category HAVING avg_price > 200; -- 只返回平均價格>200的分組
2.5 ORDER BY排序
-- 基本排序 SELECT * FROM employees ORDER BY salary DESC; --ASC 升序(默認), DESC (drop降序) -- 多字段排序 SELECT * FROM employees ORDER BY salary DESC, employee_id ASC; -- 按表達式和函數(shù)結(jié)果多字段排序 SELECT LENGTH(last_name) 字符長度, salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪 FROM employees ORDER BY 年薪 DESC, 字符長度 DESC; -- 按年薪和字符長度排序
2.6 HAVING 分組后過濾
–> 分組后過濾組
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 6000;
2.7 LIMIT分頁
-- 提取第2頁,每頁10條 偏移量 每頁行數(shù) SELECT * FROM products LIMIT 10 , 10; -- 等價于 OFFSET 10 LIMIT 10 -- 帶排序的分頁 SELECT * FROM employees ORDER BY salary DESC LIMIT 20, 10; -- 第3頁
03. 函數(shù)
3.1 聚合函數(shù)
函數(shù) | 描述 |
---|---|
SUM(expr) | 求和 |
AVG(expr) | 平均值 |
MAX(expr) | 最大值 |
MIN(expr) | 最小值 |
COUNT(expr) | 計數(shù) |
3.2 日期函數(shù)
SQL
標準函數(shù)
3.3 字符串函數(shù)
語法 | 功能描述 |
---|---|
CHARSET(str) | 返回字符串的字符集 |
CONCAT(str1, str2, ...) | 連接多個字符串 |
INSTR(str, substr) | 返回子串在字符串中的位置(從1 開始),未找到返回0 |
UCASE(str) 或 UPPER(str) | 串轉(zhuǎn)換為大寫 |
LCASE(str) 或 LOWER(str) | 串轉(zhuǎn)換為小寫 |
LEFT(str, length) | 從字符串左側(cè)截取指定長度的子串 |
LENGTH(str) | 返回字符串的字節(jié)長度(非字符數(shù)) |
REPLACE(str, from_str, to_str) | 替換字符串中的指定子串 |
STRCMP(str1, str2) | 逐字符比較兩字符串大?。ǚ祷?1 ,0 ,1 ) |
SUBSTRING(str, pos, len) | 從指定位置截取子串(pos 從1 開始) |
TRIM(str) LTRIM(str) RTRIM(str) | 去除字符串前或后面空格 |
3.4 數(shù)學函數(shù)
04.復(fù)合查詢
笛卡爾積: 將兩個表窮舉的結(jié)果
4.1 多表查詢
單行子查詢是指子查詢只返回單列,單行數(shù)據(jù);多行子查詢是指返回單列多行數(shù)據(jù),都是針對單列而言的,而多列子查詢則是指查詢返回多個列數(shù)據(jù)的子查詢語句。
通過將兩個表直接笛卡爾積組成一個新表,但是引入了許多無關(guān)數(shù)據(jù),這時對其進行去除不正確數(shù)據(jù)后再篩選即可得到
顯示部門號為10的部門名,員工名和工資
select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno andDEPT.deptno = 10;
4.2 自連接
自連接是指在同一張表連接查詢
4.3 子查詢
子查詢是指嵌入在其他sql
語句中的select
語句,也叫嵌套查詢。
4.3.1 單行子查詢
案例: 查詢和SMITH的部門和崗位完全相同的所有雇員。下面這個案例返回了多行,視情況而定
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子查詢 (返回臨時表) 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 :并集<-- 該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時,會自動去掉結(jié)果集中的重復(fù)行。
- UNION ALL :該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時,不會去掉結(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)連接實際上就是利用where子句對兩種表形成的笛卡兒積進行篩選。
語法:
select 字段 from 表1 inner join 表2 on 連接條件 and 其他條件;
示例:
--用前面的寫法 select ename,dname from EMP, DEPT where EMp.deptno=DEPT.deptno andename='SMITH' --用標準的內(nèi)連接寫法 select ename, dname from EMp inner join DEPT on EMP.deptno=DEPT.deptno andename='SMITH':
5.2 外連接
保留左側(cè)表或者右側(cè)表數(shù)據(jù)
多表內(nèi)連接耶可以
到此這篇關(guān)于mysql表操作與查詢的文章就介紹到這了,更多相關(guān)mysql表操作與查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL 如何查找并刪除重復(fù)記錄的實現(xiàn)
這篇文章主要介紹了MySQL 如何查找并刪除重復(fù)記錄的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2020-08-08