技術文章 » MySQL的簡單介紹

MySQL的簡單介紹

2020-08-13 21:06:30

SQL

SQL全稱是結構化查詢語言(Strutured Query Language)。

1. 數據定義語言(Data Definition Language--DDL)：如CTEATE,DROP,ALTER等語句。
2. 數據操縱語言(Data Manipulation Language--DML)：INSERT,UPDATE,DELETE語句。
3. 數據查詢語言(Data Query Language--DQL)：SELECT語句
4. 事務控制語言(Transaction Control Language--TCL)：如COMMIT,ROLLBACK等語句。

DDL

建立數據庫(CREATE DATABASE語句)
建立表(CREATE TABLE語句)
刪除表(DROP TABLE語句)
修改表結構(ALTER TABLE語句)
	alter table test add  column name varchar(10);--新增表列
	alter table test rename test;--修改表名
	alter table test drop column name;--刪除表列
	alter table test modify address char(10);--修改表列型別
	alter table test change address address char(40);--修改表列型別
	alter table test change cloumn address address1 varchar(30);--修改表列名

DML

新增數據(INSERT INTO...語句)
修改數據(UPDATE...SET語句)
刪除數據(DELETE FROM...語句)

DQL

-- 查詢表中的單個欄位
SELECT first_name FROM employees;
-- 查詢表中的多個欄位
SELECT first_name,last_name FROM employees;
-- 查詢表中的所有欄位
SELECT * FROM employees;
-- 查詢常數值
SELECT 123 AS 結果;
-- 查詢表達式
SELECT 10%3 AS 結果;  
-- 查詢函數
SELECT VERSION();
-- 別名
SELECT 50%3 結果;
-- 去重複
SELECT DISTINCT department_id FROM employees;
-- 查詢id爲100的員工資訊
SELECT * FROM employees WHERE employee_id = 100;
-- 查詢部門id爲空的員工資訊
SELECT * FROM employees WHERE employee_id IS NULL;
-- 查詢employee_id大於105的員工資訊
SELECT * FROM employees WHERE employee_id > 105;
-- 查詢job_id爲ST_MAN並且manger_id大於100的員工資訊
SELECT * FROM employees WHERE employee_id>100 AND job_id='ST_MAN';
-- 查詢姓名最後一個字母爲T的員工資訊
SELECT * FROM employees WHERE last_name LIKE '%T';
-- 查詢姓名中包含T的員工資訊
SELECT * FROM employees WHERE last_name LIKE '%T%';
-- 查詢姓名以T開頭的員工資訊
SELECT * FROM employees WHERE first_name LIKE 'T%';
-- 查詢所有員工資訊，並按工資降序排序(預設爲升序ASC)
SELECT * FROM employees ORDER BY salary DESC;
-- 查詢所有員工資訊，並按工資降序排序，當工資相同時按部門編號排序降序排序。
SELECT * FROM employees ORDER BY salary DESC,department_id DESC;
-- 按部門分組查各個部門的人數
SELECT department_id,COUNT(department_id) FROM employees GROUP BY department_id;
-- HAVING條件語句一個HAVING子句(條件查詢)必須位於GROUP BY子句之後，並位於ORDER BY子句之前
-- 按部門分組查詢部門id爲80的部門的人數
SELECT department_id,COUNT(department_id) FROM employees GROUP BY department_id HAVING department_id=80;
-- 查詢表的總記錄數
SELECT COUNT(*) 總數 FROM employees;
-- 查詢員工前三條記錄(從0開始)
SELECT * FROM employees LIMIT 0,3;

什麼是事務

事務（Transaction）是存取並更新數據庫中各種數據項的程式執行單元（unit）。事務通常由數據庫高階操縱語言或程式語言(如SQL,C++或Java)書寫的使用者程式的執行所引起的。
    
事務是恢復和併發控制的基本單位。
事務應該具有4個屬性：原子性、一致性、隔離性、持續性，這四個屬性通常稱爲ACID特性。
1.原子性(atomicity)
    一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。
2.一致性(consistency)
    事務必須是是數據庫從一個一致性狀態到另一個一致性狀態。一致性與原子性是密切相關的。
3.隔離性(isolation)
    一個事務的執行不能被其他事務幹擾，即一個事務內部的操作及使用的數據對併發的其他事務是隔離的，兵法執行的各個事務之間不能相互幹擾。
4.永續性(durability)
    永續性也稱爲永久性(permanece),指一個事務一旦提交，它對數據庫中的數據的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。

事務操作
設定預設的事務提交方式
set autocommit = false; 設定爲手動提交
set autocommit = true; 設定爲自動提交
事務是對數據庫的多步操作，要麼一起成功，要麼一起失敗
-- 建立學生表
CREATE TABLE `student` (
  `s_id` int(10) NOT NULL AUTO_INCREMENT,
  `s_name` varchar(11) NOT NULL,
  `s_age` int(10) NOT NULL,
  `s_gender` varchar(10) NOT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`s_id`));

-- 設定事務提交方式
SET autocommit = FALSE;
-- 新增學生
INSERT INTO student(s_name,s_age,s_gender,birthday) VALUES('小芳',18,'男','2018-02-02');
SELECT * FROM student;
-- 更新學生
UPDATE student SET s_age=29,s_gender='女' WHERE s_id = 10;
SELECT * FROM student;
COMMIT;

-- 實現回滾
SET autocommit = FALSE;
INSERT INTO student(s_name,s_age,s_gender,birthday) VALUES('小白白',17,'女','2020-02-11');
SELECT * FROM student;
-- 設定還原點
SAVEPOINT point1;
-- 更新學生
UPDATE student SET s_age=19,s_gender='男' WHERE s_id = 13;
SELECT * FROM student;
-- 設定還原點
SAVEPOINT point2;
-- 回滾
ROLLBACK TO point1;
SELECT * FROM student;

函數

GROUP BY函數

1. AVG([DISTINCT] expr)：返回express的平均值。DISTINC選項是可用於返回expr的不同值的平均值
-- 查詢各個部門的員工平均工資
SELECT department_id,AVG(salary) 平均工資 FROM employees GROUP BY department_id;

2. SUM([DISTINCT] expr)：返回expr的總數
-- 查詢學生的年齡總數
SELECT SUM(age) FROM student;

3.

控制流程函數

1. CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result...][ELSE result] END
如果沒有匹配的結果值，則返回結果爲ELSE後的結果，如果沒有ELSE部分，則返回值爲NULL。
SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;

2. IF(expr1,expr2,expr3):類似於三目運算子
如果expr1是TRUE,則IF()返回值是expr2;否則返回值爲expr3
SELECT IF(1<2,'yes ','no');

3. IFNULL(expr1,expr2)
假如expr1不爲unll，則IFNULL()的返回值是expr1；否則返回值爲expr2
SELECT IFNULL(1,0);

4. NULLIF(expr1,expr2)
如果expr1 = expr2 成立，那麼返回值爲NULL，否則返回值爲expr1.

字串函數

1. ASCII(str)
返回值爲字串str的最左字元的數值。假如str爲空字串，則返回0。假如str爲null，則返回值爲NULL。ASCII()用於帶有從0到255的數值的字元。
SELECT ASCII('ss');
2. BIN(N)
返回值爲N的二進制值的字串表示
SELECT BIN(9);
3. BIT_LENGTH(str)
返回值爲二進制的字串str長度
SELECT BIT_LENGTH('text');
4. CHAR_LENGTH(str)
返回值爲字串str的長度，長度的單位爲字元
SELECT CHAR_LENGTH('here');
5. FORMAT(X,D)
將數位X的格式寫爲'#,###,###.##'以四捨五入的方式保留小數點後D位，並將結果以字串的形式返回，若D爲0，則返回結果不帶小數點，或不含小數部分
SELECT FORMAT(12332.123456,4);
5. INSERT(str,pos,len.newstr)
返回字串str,其子字串其實於pos位置和長期被字串newstr取代的len字元。如果pos超過字串長度，則返回值爲原始字串。假如len的長度大於其他字串的長度，則從位置pos開始替換，若任何一個參數爲null,則返回值爲NULL。
SELECT INSERT('Quadratic', 3, 4, 'What'); SELECT INSERT('Quadratic', 3, 60, 'What');
6.INSTR(str,substr)
返回字串str中子字串的第一個出現位置
SELECT INSTR('foobarbar','bar');
7.LEFT(str,len)
返回從字串str開始的len最左字元
SELECT LEFT('foobarbar',5);
8.LENGTH(str)
返回值爲字串str的長度，單位爲位元組。
SELECT LENGTH('text');
9.LTRIM(str)
返回字串str，其引導空格字元被刪除。
SELECT LTRIM('  barbaaa');
10.TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
返回字串str,其中所有remstr字首和/或後綴都已經被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的，則假設爲BOTH。remstr爲可選項，在未指定情況下，可刪除空格。
11. STACMP(expr1.expr2)
若所欲的字串均相同，則返回0，若根據當前分類次序，第一個參數小於第二個，則返回-1，其他情況返回1.
SELECT STRCMP('text','text2');-- 返回1
SELECT STRCMP('text2','text');-- 返回1
SELECT STRCMP('text','text');-- 返回0
12. CONCAT(str1,str2,...)
返回結果爲連線參數產生的字串。如有任何一個參數爲NULL，則返回值爲NULL。或許有一個或多個參數。如果所欲參數均爲非二進制字串，則結果爲非二進制字串。如果自變數中含有任一二進制字串，則結果爲一個二進制字串。一個數字參數被轉化爲與之相等的二進制字串格式；若要避免這種情況，可使用顯示型別cast，例如
SELECT CONCAT(CAST(int_col AS CHAR), char_col) 
SELECT CONCAT('My', 'S', 'QL');

日期和時間函數

1. DAYOFWEEK(date)
    返回日期date的星期索引(1=星期天。。。)。
    select DAYOFWEEK('1999-03-13');
2. WEEKDAY(date)
    返回date的星期索引(0=星期一。。。)
    select WEEKDAY('1999-03-13');
3. DAYOFYEAR(date)
    返回date在一年中的日數，在1-366範圍內。
    select DAYOFYEAR('1999-03-13');
4. MONTH(date)
    返回date的月份，範圍1-12
    select MONTH('2019-03-13');
5.MONTHNAME(date)
    返回date的月份名字
    select MONTHNAME('1999-03-13');
6. QUARTER(date)
    返回date一年中的季度，範圍1-4
	select QUARTER('98-04-01');
7. WEEK(date)
    對於星期太難是一週的第一天的地方，有一個單個參數，返回date的週數，範圍在0-52.2個參數形式的WEEK()允許你指定星期是否開始於星期天或星期一。如果第二個參數是0，星期從星期天開始，如果第二個參數是1，從星期一開始。
    select WEEK('1998-02-20');
	select WEEK('1998-02-20',0);
	select WEEK('1998-02-20',1);
8.YEAR(date)
    返回date的年份，範圍在1000-9999。
    select YEAR('98-02-03');
9.HOUR(time)
    返回time的小時，範圍是0-23
    select HOUR('10:05:03');
10. MINUTE(time)
    返回time的分鐘，範圍是0到59
    select MINUTE('98-02-03 10:05:03');
11. SECOND(time)
    返回time的秒數，範圍是0-59
    select SECODE('10:05:03');
12. PERIOD_ADD(P,N)
    增加N個月到階段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意階段參數P不是日期值。
    select PERIOD_ADD(9801,2);
13. PERIOD_DIFF(P1,P2)
    返回在時期P1到P2之間的月數，P1和P2peye.com應該以格式YYMM或YYYYMM。注意，時期參數P1和P2不是日期值。
    select PERIOD DIFF(9802,199703);
14. ADDDATE(expr,days)
    expr是指定加到開始日期的間隔值是一個表達式，expr是一個字串；他可以以一個"-"開始表示負間隔。type是一個關鍵詞，指明表達式應該如何被解釋。(type關鍵詞用法請參考幫助文件)
    若days參數只是整數值，則將其作爲天數值新增至expr。
    select ADDDATE('1998-01-02',31);
	select DATE_ADD('1997-12-31 23:59:59',INTERVAL 1 SECODE);
15. SUBDATE(date,INTERVAL expr type)
    date是一個指定開始日期的DATETIME或DATE值，expr是指定從開始日期減去的間隔值減去的間隔值一個表達式，expr是一個字串；它可以以一個"-"開始表示負間隔。type是一個關鍵詞，指明表達式應該如何被解釋。(type關鍵詞用法請參考幫助文件)
    select DATE_SUB('1998-01-02',INTERVAL 31 DAY);
16.ADDTIME(expr,expr2)
    將expr2新增到expr然後返回結果。expr是一個時間或時間日期表達式，而expr2是一個時間表示式。
    select ADDTIME('01:00:00.999999', '02:00:00.999998');
17.DATE(expr)
    提取日期或時間日期表達式expr中的日期部分。
    select DATE('2003-12-31 01:02:03');
18.	TO_DAYS(date)
    給出一個日期date，返回一個天數(從0年到的天數)
    select TO_DAYS(950501);
	select TO_DAYS('1997-10-07');
19.	FORM_DAYS(N)
    給出一個天數N，返回一個DATE值
    select FROM_DAYS(729669);
20.	CURDATE()
    以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取決於函數在一個字串還是數位上下文就被使用。
    select CURDATE();
21. CURTIME()
	以'HH:MM:SS'或HHMMSS格式返回當前時間值，取決於函數是在一個字串還是在數位的上下文被使用。
	select CURTIME();
22. NOW()
	以'YYYY-MM-DD HH:MM:SS'或'YYYYMMDDHHMMSS'格式返回當前的日期和時間,取決於函數是在一個字串還是在數位的上下文被使用。
	select NOW();
23. SEC_TO_TIME(seconds)
	返回seconds參數，變換成小時、分鐘和秒，值以'HH:MM:SS'或HHMMSS格式化，取決於函數是在一個字串還是在數位上下文中被使用。
24. TIME_TO_SEC(time)
	返回time參數。轉換成秒。
	select TIME_TO_SEC('22:23:00');
	select TIME_TO_SEC('00:39:38');
25. DATE_FORMAT(date,format)
	根據format字串安排date值的格式
	SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); 		SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); 		SELECT DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j');