mysql表操作
- 模糊查旬
like
between and
in
is null | is not null
like
案例: 查詢員工名中包含了"a"字符的所有員工的信息
select * from employees where last_name like '%a%'; // %:通配符,表示任意多個(gè)字符,也可表示0個(gè)字符, _:任意一個(gè)字符;
案例: 查詢第三個(gè)字符為n或第五個(gè)字符為l的員工信息
select * from employees where last_name like '__n_l%';
案例: 查詢員工信息表中員工名第二個(gè)字符是"_"的員工信息
select * form employees where last_name like '_\_%'; //支持java的轉(zhuǎn)義字符
select * from employees where last_name like '_&_' escape '&'; // escape '&':說明&這個(gè)符號(hào)是轉(zhuǎn)義字符,mysql推薦這樣寫;
注意:一般情況下,like都是通配符連在一起使用的.
between and
案例:查詢員工工資中10000到20000之間到員工信息
select * from employees where salary>=10000 and salary<=20000;
select * from employees where salary between 10000 and 20000;
in:
案例:查詢員工的工種編號(hào)是 it_prog,ad_vp,ad_pres中任意一個(gè)的員工信息
select * from employee where job_id='it_prog' or job_id='ad_vp' or job_id='ad_pres';
select * from employees where job_id in('it_prog','ad_vp','ad_pres')
in:判斷某個(gè)字段的值是否屬于in列表中的某一項(xiàng)
1.使用in比使用or后sql語句更簡(jiǎn)單
2.in的值必須是同一種數(shù)據(jù)類型或者兼容
is null | is no null:
案例:查詢沒有獎(jiǎng)金率的員工信息
select * from employees commission_pct is null; 注意:commission_pct=null這個(gè)寫法不能判斷null值
查詢獎(jiǎng)金率的就取反:
select * from employees commission_pct is not null; 注意:當(dāng)然這里is not也不能換成<>不等號(hào).
補(bǔ)充一個(gè)東西:
安全等于號(hào):<=> , 這個(gè)符號(hào)可以判斷null值,也可以判斷普通數(shù)值;
is null:只能判斷null值,不能判斷普通數(shù)值
=:只能判斷普通數(shù)值,不能判斷null值
事務(wù)機(jī)制
TCL:Transaction Control Language,事務(wù)控制語言
事務(wù):在MySQL數(shù)據(jù)庫中表示一條或多條Sql語句組合在一起的一個(gè)執(zhí)行單元.這個(gè)執(zhí)行單元要么全部執(zhí)行,要么全部不執(zhí)行,否則就會(huì)出現(xiàn)邏輯錯(cuò)誤!
比如銀行里的轉(zhuǎn)賬這個(gè)事情:
A賬號(hào)余額:1000
B賬號(hào)余額:1000
現(xiàn)在A轉(zhuǎn)500元給B,那么要完成這個(gè)轉(zhuǎn)賬的事務(wù),數(shù)據(jù)中的SQL應(yīng)該是這樣的執(zhí)行過程:
①A賬號(hào)上要減少500元
update 儲(chǔ)蓄表 set A.余額=A.余額-500 where 賬號(hào)名='A';
②B賬號(hào)上要增加500元
update 儲(chǔ)蓄表 set B.余額=B.余額+500 where 賬號(hào)名='B';
如果沒有事務(wù)處理這個(gè)功能,上面的情況下,很可能會(huì)發(fā)生這樣的情況:
①執(zhí)行成功 A的余額變?yōu)?500
剛開始執(zhí)行②的時(shí)候,突然出現(xiàn)某系統(tǒng)系統(tǒng)錯(cuò)誤,導(dǎo)致②執(zhí)行失敗!
①√②×:A的錢減少了,B的錢沒增加!
所以在類似的場(chǎng)景需求中我們需要事務(wù)處理:實(shí)現(xiàn)將①和②的SQL語句綁定在一起,要么都執(zhí)行成功,沒的事! 要么不管是①執(zhí)行出錯(cuò)還是②執(zhí)行出錯(cuò),數(shù)據(jù)庫里的數(shù)據(jù)狀態(tài)會(huì)回滾到?jīng)]有執(zhí)行任何①或②里的SQL語句之前!
①什么是存儲(chǔ)引擎:
在mysql中的數(shù)據(jù)是用各種不同的技術(shù)來存儲(chǔ)在磁盤文件(或內(nèi)存)當(dāng)中的,這種具體的存儲(chǔ)技術(shù)就是我們說的存儲(chǔ)引擎.
②我們可以通過show engines;命令來查看mysql支持的存儲(chǔ)引擎.
③在mysql可以選擇的這些存儲(chǔ)引擎中,
innodb,myisam,memory這個(gè)三個(gè)是最常用的,但是其中只有innodb支持事務(wù)處理,而其他是不支持事務(wù)處理的.
事務(wù)的ACID特點(diǎn):
①原子性(Atomicity):組成事務(wù)的SQL語句不可在分,要么都執(zhí)行,要么都不執(zhí)行.
②一致性(Consistency):事務(wù)必須讓數(shù)據(jù)的數(shù)據(jù)狀態(tài)變化到另一個(gè)一致性的狀態(tài),比如:剛剛的例子中A和B的余額總和是2000,轉(zhuǎn)賬后,A和B的余額總和不能變.前后具有一致性.
③隔離性(Isolation):一個(gè)事務(wù)的執(zhí)行,不受其他事務(wù)的干擾,相互應(yīng)該是隔離的,但是實(shí)際上是很難做到的,要通過隔離級(jí)別做選擇!
④持久性(Durability):一個(gè)事務(wù)被提交,并成功執(zhí)行,那么它對(duì)數(shù)據(jù)的修改就是永久性的.接下來的其他操作或出現(xiàn)的故障,不能影響到它執(zhí)行的結(jié)果!
MySQL的事務(wù)的創(chuàng)建:
①隱視事務(wù):
事務(wù)沒有明顯的開始和結(jié)束的標(biāo)記.這時(shí)候像insert語句,update語句和delete語句,每一條SQL語句就默認(rèn)是一個(gè)事務(wù).顯然,隱視事務(wù)在類似轉(zhuǎn)賬的邏輯業(yè)務(wù)需求的時(shí)候,就無法處理了!
②顯示事務(wù):
說白了,這個(gè)事務(wù)模式,就要我們中程序中手動(dòng)的用命令來開啟事務(wù),和結(jié)束事務(wù),并讓事務(wù)里的多條SQL語句去執(zhí)行.
注意:默認(rèn)MySQL是開啟自動(dòng)提交事務(wù)的,用show variables like 'autocommit';命令可以查看到. 所以開啟顯示事務(wù)前,要關(guān)掉它,用set autocommit=0;只對(duì)本身會(huì)話有效，如重新登陸mysql就是打開了自動(dòng)提交.
例子：銀行轉(zhuǎn)賬：
開啟事務(wù)：執(zhí)行語句：結(jié)束事務(wù)
set autocommit=0;
start transaction;
編寫事務(wù)中的SQL語句(主要是:select update delete insert等語句)
語句1;語句2;........
commit; #提交事務(wù)去真正執(zhí)行(結(jié)束了）
ROLLBACK; #回滾事務(wù),恢復(fù)數(shù)據(jù)庫執(zhí)行前的狀態(tài)!（回到執(zhí)行前，也是一個(gè)事務(wù)結(jié)束了。）
1.建表，插入兩條數(shù)據(jù)，A,B都有1000元：
DROP TABLE IF EXISTS account;
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO account(username,balance) VALUES('A',1000),('B',1000);
2.事務(wù)，A向B轉(zhuǎn)賬500，兩條記錄同時(shí)更改，但要同步。
SET autocommit = 0;
START TRANSACTION; #開啟事務(wù)
UPDATE account SET balance=balance-500 WHERE username='A';
UPDATE account SET balance=balance+500 WHERE username='B';
commit; #提交事務(wù)
-----當(dāng)有多個(gè)事務(wù)同時(shí)訪問數(shù)據(jù)庫中的同一個(gè)數(shù)據(jù)時(shí),如果沒有采取必要的隔離機(jī)制,就會(huì)導(dǎo)致各種并發(fā)錯(cuò)誤發(fā)生，不是數(shù)據(jù)丟失:
兩個(gè)事務(wù)t1,t2:
①臟讀: 當(dāng)t2正在更新某個(gè)字段但還沒有提交數(shù)據(jù)庫執(zhí)行時(shí),t1在這個(gè)時(shí)刻正好讀取這個(gè)字段的數(shù)據(jù),然后t2（java中）在發(fā)生錯(cuò)誤,然后回滾數(shù)據(jù),導(dǎo)致t1讀取到數(shù)據(jù)就是t2更新時(shí)的臨時(shí)數(shù)據(jù),而且最終沒有更新成功的無效數(shù)據(jù)!
②不可重復(fù)讀:t1讀取一個(gè)字段是數(shù)值,然后t2更新了這個(gè)字段,之后t1在讀取同一個(gè)字段,值發(fā)生了變化!因?yàn)楹芏虝r(shí)間內(nèi)t1只想讀取初始值，但由于t2的原因，導(dǎo)致一不致性。
③幻讀:t1讀取一個(gè)字段是數(shù)值,然后t2對(duì)這個(gè)字段插入新數(shù)值,t1在讀突然就多了幾行數(shù)據(jù).
數(shù)據(jù)庫事務(wù)的隔離性:
數(shù)據(jù)庫必須具有隔離這種并發(fā)運(yùn)行的事務(wù)的能力,避免這些個(gè)錯(cuò)誤現(xiàn)象!
一個(gè)事務(wù)與其他事務(wù)隔離的程度稱為隔離級(jí)別:數(shù)據(jù)庫規(guī)定了事務(wù)隔離級(jí)別,不同隔離級(jí)別對(duì)應(yīng)不同的干擾程度,級(jí)別越高,數(shù)據(jù)一致性越好,但并發(fā)性越弱!MySQL數(shù)據(jù)庫支持四個(gè)不同隔離級(jí)別，這四個(gè)由底到高的級(jí)別是:
read uncommitted; 讀未提交數(shù)據(jù)
read commited; 讀已提交數(shù)據(jù)
repeatable read; 可重復(fù)讀（默認(rèn)）
serialable; 串行化
演示隔離級(jí)別:
(在cmd命令窗口演示，要模擬并發(fā)情況，需要不同客戶端，linux中直接可以打開多個(gè)窗口)
1.停目，重新啟動(dòng)mysql服務(wù)。
Windos：
>net stop mysql
>net start mysql
Linux:
#systemctl stop/start mysqld
2.查看當(dāng)前隔離級(jí)別：默認(rèn)：repeatable read;
登陸mysql后：
select @@tx_isolation; #mysql8.x已棄用
select @@transaction_isolation;
3.設(shè)置隔離級(jí)別：有會(huì)話，全局級(jí)別。
全局級(jí)的：設(shè)置全局的事務(wù)隔離級(jí)別，該設(shè)置不會(huì)影響當(dāng)前已經(jīng)連接的會(huì)話，設(shè)置完畢后，新打開的會(huì)話，將使用新設(shè)置的事務(wù)隔離級(jí)別。
會(huì)話級(jí)的：設(shè)置本次會(huì)話的事務(wù)隔離級(jí)別，只在本會(huì)話有效，不會(huì)影響到其它會(huì)話。
本例t1,t2：會(huì)話級(jí)的測(cè)試，不同會(huì)話之間不影響彼此。
首先t1事務(wù)：打開一個(gè)命令窗口，登入mysql服務(wù)。
select @@transaction_isolation; #查看隔離級(jí)別
set session transaction isolation level read uncommitted; #設(shè)置最低級(jí)別的，讀未提交的數(shù)據(jù)，出臟讀現(xiàn)象。
set autocommit =0; #因?yàn)槊看瓮顺鰉ysql后，session重新建立，又是默認(rèn)打開了提交事務(wù)，所以要關(guān)
START TRANSACTION;
use test;
update account set username='C' where id=1; #修改表第一條記錄username='C;
注意：此處不寫提交語句（commit;),命令窗口不關(guān)閉
查看一下表，可以看到表數(shù)據(jù)修改了。馬上進(jìn)入t2打開另一個(gè)命令窗口，進(jìn)入mysql
其次t2：
use test;
select *from account;
可以發(fā)現(xiàn)該表的數(shù)據(jù)沒有修改，仍是'A',出現(xiàn)了臟讀。
設(shè)置與t1同一隔離級(jí)別：
set session transaction isolation level read uncommitted;
再查詢：
select *from account; #數(shù)據(jù)修改了
再次t1回滾：
rollback;
又到t2中
select *from account;
其它級(jí)別自行測(cè)試：
第二級(jí)別: 臟讀沒有了,但不可重復(fù)讀和幻讀仍然有!
第三級(jí)別:沒有臟讀,沒有不可重復(fù)讀,仍然有幻讀!
第四個(gè)級(jí)別:可以解決所有問題! 會(huì)使性能十分低下!
事務(wù)回滾點(diǎn)。
主要是關(guān)鍵字:savepoint
SET autocommit = 0;#開啟事務(wù)
START TRANSACTION;
編寫sql語句
DELETE FROM account WHERE id=1;
SAVEPOINT a; #設(shè)置保存點(diǎn)
DELETE FROM account WHERE id=2;
結(jié)束事務(wù)
ROLLBACK TO a; #回滾到保存點(diǎn)
java開啟事務(wù)，提交事務(wù)，回滾事務(wù)。
上面是mysql中的硬編碼測(cè)試，這里我們用到j(luò)ava代碼開啟事務(wù)。
首先有一個(gè)數(shù)據(jù)庫連接: conn
其次:conn.setAutoCommit(false);相當(dāng)于：
mysql>set autocommit =0;
mysql>start transcation ;
再次，提交,回滾結(jié)束事務(wù)：當(dāng)然了要寫異常處理。
conn.commit();
conn.rollback();
例 junit 測(cè)試：
/*
* 支持事務(wù)的查詢測(cè)試。
*/
@Test
void testGetIntTranscation() throws SQLException {
Connection conn = JdbcUtils.getConnection();
User user = null;
conn.setAutoCommit(false); //開啟事務(wù)
try {
user = userDao.get(conn, 5); //可以有很多語句，這里只一條
conn.commit();//提交事務(wù)
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback(); //有異常，回滾
}catch(SQLException e1) {
e1.printStackTrace();
}
} finally {
JdbcUtils.closeConn(conn);
}
System.out.println(user);
}
問題
-
java.sql.SQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
報(bào)錯(cuò)很明顯，數(shù)據(jù)庫太多鏈接，應(yīng)該是關(guān)閉項(xiàng)目后資源沒有及時(shí)釋放。
image.png
解決方法如下：
首先，重啟MySQL服務(wù)。
進(jìn)入MySQL，輸入命令：
show variables like 'max_connections'; 查看當(dāng)前最大連接數(shù)。
最大連接數(shù)修改大一點(diǎn)：
set global max_connections=1000;
注意事項(xiàng)：使用上面的可以進(jìn)行臨時(shí)解決問題，想要一直開啟就需要去MySQL下面的my.ini文件里修改max_connections即可。
MySQL安裝路徑查詢命令：
進(jìn)入mysql命令行輸入：show variables like "%char%"; Access denied for user 'root'@'localhost' (using password: Yes)
到安裝的MySQL的目錄下，找my.ini文件；
在[mysqld]后添加skip-grant-tables（使用 set password for設(shè)置密碼無效，且此后登錄無需鍵入密碼）
skip-grant-tables #在my.ini，[mysqld]下添加一行，使其登錄時(shí)跳過權(quán)限檢查
-
windows 配置mysql允許遠(yuǎn)程登陸。
1.----連接Mysql (連接方式:mysql -u 你設(shè)置的用戶名 -p你設(shè)置的密碼 )
2.----查看數(shù)據(jù)庫:show databases;我們會(huì)看到有一個(gè)叫做"mysql"的數(shù)據(jù)庫,這里我們輸入: use mysql 進(jìn)入'mysql'數(shù)據(jù)庫中
3.----執(zhí)行 GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY '你設(shè)置的密碼' WITH GRANT OPTION;
若出現(xiàn)ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
解決方案:
執(zhí)行一次：mysql> flush privileges;
然后再執(zhí)行：GRANT ALL Privileges....語句。
重啟一下mysql服務(wù)
注意：如果是云主機(jī)，遠(yuǎn)程不能連接，那么可能是，防火墻規(guī)則沒有放行3306端口。我們要添加允許3306端口放行數(shù)據(jù)。
image.png -
IDEA中mysql8.x注意時(shí)區(qū)設(shè)置問題，不設(shè)置容易出錯(cuò)。
11 查看時(shí)區(qū)
show VARIABLES like '%time_zone%'
image.png
time_zone: 表示 MySQL 采用的是系統(tǒng)的時(shí)區(qū)。也就是說，如果在連接時(shí)沒有設(shè)置時(shí)區(qū)信息，就會(huì)采用這個(gè)時(shí)區(qū)配置。
12僅修改當(dāng)前會(huì)話的時(shí)區(qū)，停止會(huì)話失效(CET)
set time_zone = '+8:00';
13 修改全局的時(shí)區(qū)配置
set globaltime_zone = '+8:00';
flush privileges;
大多數(shù)時(shí)候用到13.
- com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias.別名
－－－做EduSystem項(xiàng)目時(shí)，在ScoreDao.java中遇到的問題，它在連接oralce 12c不會(huì)出錯(cuò)，這里是mysql出的問題。
String sql = "select courseno,coursename,case when score between 0 and 60 then '0-60' "+
" when score between 60 and 70 then '60-70'"+
" when score between 70 and 80 then '70-80'"+
" when score between 80 and 90 then '80-90'"+
" else '90-100' end as 分?jǐn)?shù)段,count(*)人數(shù) "+
" from (select A.courseno courseno,B.coursename coursename,A.score score from" +
" t_score A join t_course B on A.courseno=B.courseno where A.courseno='"+courseno+"')"+
" group by courseno,coursename,case when score between 0 and 60 then '0-60'"+
" when score between 60 and 70 then '60-70'"+
" when score between 70 and 80 then '70-80'"+
" when score between 80 and 90 then '80-90'"+
" else '90-100' end order by count(*)";
這句話的意思是說每個(gè)派生出來的表都必須有一個(gè)自己的別名。
一般在多表查詢時(shí)，會(huì)出現(xiàn)此錯(cuò)誤。
因?yàn)?，進(jìn)行嵌套查詢的時(shí)候子查詢出來的的結(jié)果是作為一個(gè)派生表來進(jìn)行上一級(jí)的查詢的，所以子查詢的結(jié)果必須要有一個(gè)別名
把MySQL語句改成：select * from (select * from ……) as 別名;
改：注意，看下面代碼了最未尾的as xiong。
String sql = "select courseno,coursename,case when score between 0 and 60 then '0-60' "+
" when score between 60 and 70 then '60-70'"+
" when score between 70 and 80 then '70-80'"+
" when score between 80 and 90 then '80-90'"+
" else '90-100' end as 分?jǐn)?shù)段,count(*)人數(shù) "+
" from (select A.courseno courseno,B.coursename coursename,A.score score from" +
" t_score A join t_course B on A.courseno=B.courseno where A.courseno='"+courseno+"') as xiong".....
- windows的cmd編碼問題,cmd默認(rèn)為gpk編碼，所以在cmd登入mysql后，插入中文數(shù)據(jù)，不允許插入，查詢的中文也是亂碼。處理：登入數(shù)據(jù)庫后，寫下如下代碼，臨時(shí)有用。
>set names gpk;
幾個(gè)sql語句舉例。
----首選見以下幾個(gè)表
《1》t_student
《2》t_teahcer
《3》t_course
《4》t_score
- 查詢001號(hào)課程的每個(gè)分?jǐn)?shù)段有幾個(gè)人。
SELECT courseno,coursename,CASE WHEN score BETWEEN 0 AND 60 THEN '0-60'
WHEN score BETWEEN 60 AND 70 THEN '60-70'
WHEN score BETWEEN 70 AND 80 THEN '70-80'
WHEN score BETWEEN 80 AND 90 THEN '80-90'
ELSE '90-100' END AS 分?jǐn)?shù)段,COUNT(*) 人數(shù)
FROM (SELECT A.courseno courseno,B.coursename coursename,A.score score FROM
t_score A JOIN t_course B ON A.courseno=B.courseno WHERE A.courseno=001) AS xiong
GROUP BY courseno,coursename,cas`t_student`e WHEN score BETWEEN 0 AND 60 THEN '0-60'
WHEN score BETWEEN 60 AND 70 THEN '60-70'
WHEN score BETWEEN 70 AND 80 THEN '70-80'
WHEN score BETWEEN 80 AND 90 THEN '80-90'
ELSE '90-100' END ORDER BY COUNT(*)
結(jié)果：
- 查詢0001號(hào)學(xué)生所選的課程號(hào)課程名，及所授課老師，課程成現(xiàn)。涉及三個(gè)表。
SELECT * FROM t_score A JOIN t_student B ON A.stuno=B.stuno JOIN t_course C ON
A.courseno=C.`courseno` WHERE A.`stuno`=0001;
結(jié)果：
- 查詢所有課程名，結(jié)果集中不含重復(fù)的值。
SELECT DISTINCT(coursename) FROM t_course;
結(jié)果：
mysql數(shù)據(jù)的安裝配置
windows環(huán)境下
- mysql 8.x
https://dev.mysql.com/downloads/mysql/#downloads
[mysql安裝與遠(yuǎn)程登陸配置]（http://www.itdecent.cn/p/a6801ba4b4d6）
- mysql 5.x
數(shù)據(jù)庫與表操作
本示例是遠(yuǎn)程登陸數(shù)據(jù),首先本地要安裝了mysql，并配置了環(huán)境變量。
mysql -h 124.222.48.147 -u root -p //回車
xiong //密碼
- 創(chuàng)建/刪除數(shù)據(jù)庫
//創(chuàng)建數(shù)據(jù)庫
create database test_db;
SHOW create database test_db\G; //查看數(shù)據(jù)庫定義
show databases; //查看已有的數(shù)據(jù)庫
//刪除數(shù)據(jù)庫
DROP DATABASE database_name;
//顯示數(shù)據(jù)庫引擎類型
show engines \G //Support列的值表示某種引擎是否能使用，YES表示可以，NO表示不能，DEFAULT表示默認(rèn)為該引擎
show variables like 'storage_engine';
－－－－InnoDB是事務(wù)型數(shù)據(jù)庫的首選引擎，支持事務(wù)的安全表（ACID）,支持行鎖定和外鍵，MYSQL 5.5.5之后，InnoDB作為默認(rèn)存儲(chǔ)引擎 。如果數(shù)據(jù)表主要用來插入和查詢記錄，則 MyISAM引擎更好點(diǎn)。如果只是存放臨時(shí)數(shù)據(jù)，數(shù)據(jù)量不大，并且不要求較高的安全性可用選用保存在內(nèi)存中的Memory引擎，只是存放一些中間結(jié)果。
- 創(chuàng)建數(shù)據(jù)表與相關(guān)配置
創(chuàng)建表
create table <table_name>
(字段名1 數(shù)據(jù)類型 [列級(jí)別約束條件] [默認(rèn)值]，
字段名2 數(shù)據(jù)類型 [列級(jí)別約束條件] [默認(rèn)值]，
……
[表級(jí)別約束條件]
）;
//創(chuàng)建表tb_emp1
mysql> create table tb_emp1
-> (
-> id INT(11),
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT
-> );
//創(chuàng)建表 tb_emp2 設(shè)了id為主鍵
create table tb_emp2
(
id INT(11) PRIMARY KEY,
name varchar(25),
deptId INT(11),
salary FLOAT
);
//或(也可用逗號(hào)隔開多字段聯(lián)合主鍵）
create table tb_emp2
(
id INT(11) PRIMARY KEY,
name varchar(25),
deptId INT(11),
salary FLOAT，
primary key(id)
);
//外鍵約束,主要作用是保持?jǐn)?shù)據(jù)的一致性，完整性。
//外鍵約束用來在兩個(gè)表之間建立鏈接，它可以是一列或者多列。一個(gè)表可以有一個(gè)
//或多個(gè)外鍵，一個(gè)表的外鍵值可以為空，若不為空，則每一個(gè)外鍵值必須等于另一個(gè)表中的主鍵值。
//語法規(guī)則如下：
[constraint <外鍵名>] foreign key 字段名1[，字段名2，……] references <主表名> 主鍵列1[，主鍵列2.……]
//例：創(chuàng)建表tb_dept1關(guān)聯(lián)到tb_emp3,主鍵id關(guān)聯(lián)到外鍵dpetId.
create table tb_emp3
(
id INT(11) PRIMARY KEY,
name varchar(22),
deptId INT(11),
salary FLOAT,
constraint fk_emp3_dept1 foreign key(deptId) references tb_dept1(id)
);
create table tb_dept1
(
id INT(11) primary key,
name varchar(22) not null,
location varchar(50)
);
//非空約束
name varchar(25) not null,
//唯一性約束
[constraint <約束名>] UNIQUE(<字段名>)
........
location varchar(50),
CONSTRAINT sth UNIQUE(name)
)
//默認(rèn)性約束，如某一表中性別‘男’較多時(shí)，可設(shè)默認(rèn)值為‘男’
sex varchar(2) DEFAULT '男',
//設(shè)置表的字段值自動(dòng)增加
id INT(11) PRIMARY KEY AUTO_INCREMENT,
查看表結(jié)構(gòu)
describe 表名; 或 desc 表名;
show create table <表名\G>
修改表
一，表刪除操作須謹(jǐn)慎，因?yàn)閯h除表時(shí)沒有任提示信息，應(yīng)先備份再做刪除操作
二，并不是每個(gè)表都要一個(gè)主鍵，只有關(guān)聯(lián)時(shí)用到，有時(shí)因不須要有主鍵
三，并不是每個(gè)表都可以任意的選擇存儲(chǔ)引擎，如外鍵約束不可跨引擎。
//修改表名
ALTER TABLE <舊表名> RENAME [TO] <新表名>;
//修改數(shù)據(jù)類型
ALTER TABLE <表名> MODIFY <字段名> <數(shù)據(jù)類型>
例 ：alter table tb_dept1 modify name varchar(30);
//添加字段
ALTER TABLE ,<表名> ADD <新字段名> <數(shù)據(jù)類型> [約束條件] [FIRST | AFTER 已存在字段名]; //默認(rèn)插入到最后列
//刪除字段
ALTER TABLE <tablename> DROP <字段名>;
//修改字段的排列位置
ALTER TABLE <表名> MODIFY <字段1> <數(shù)據(jù)類型> FIRST|AFTER <字段名2>;
//修改字段名
ALTER TABLE <表名> change <舊字段名> <新字段名> <數(shù)據(jù)類型>
//更改表的存儲(chǔ)引擎 ，可以為每張表設(shè)定不同的引擎
//常用的引擎名：MyISAM,InnoDB,MEMORY(HEAP),BDB,CSV,ARCHIVE
ALTER TABLE <表名> ENGINE=<更改后的存儲(chǔ)引擎名>;
//刪除表的外鍵約束
ALTER TABLE <表名> DROP FOREIGN KEY <外鍵約束名>;
//刪除表
//1,刪除沒有被關(guān)聯(lián)的表
DROP TABLE [IF EXISTS] 表1，表2，表3.....n;
//2,刪除關(guān)聯(lián)表，首先，刪除父表，再刪除子表。而刪除父表之前，要冊(cè)除子表的外鍵約束。
//刪除表中所有的數(shù)據(jù)
delete from tablename;
數(shù)據(jù)類型
-
整數(shù)類型
image.png
分別點(diǎn)用1，2，3，4，8個(gè)字節(jié)存儲(chǔ)
- 浮點(diǎn)數(shù)類型
MySQL中使用浮點(diǎn)數(shù)和定點(diǎn)數(shù)來表示小數(shù)。
浮點(diǎn)類型：FLOAT、DOUBLE。
定點(diǎn)類型：DECIMAL。占用m+2個(gè)字節(jié)
例：create table temp(x FLOAT(5,1),y DOUBLE(5,1),z DECIMAL(5,1));
都是保留一位小數(shù)。 - 字符串類型
字符串類型用來存儲(chǔ)字符串?dāng)?shù)據(jù)，除了可以存儲(chǔ)字符串?dāng)?shù)據(jù)之外，還可以存儲(chǔ)其它數(shù)據(jù)，比如圖片和聲音的二進(jìn)制數(shù)據(jù)。
CHAR、VARCHAR、BINARY、VARBINARY BLOB、TEXT、ENUM和SET。
31。char與varchar固定長度，非固定長都為非二進(jìn)制字符串
char（M）為固定長度字符串同，在定義時(shí)指定字符串列長。當(dāng)保存時(shí)在右側(cè)填充空格以達(dá)到指定的度.M表示長度，M的范圍是0－255個(gè)字符，其檢索時(shí)尾部空格被刪除，varchar(M),實(shí)際長度0~65535，實(shí)現(xiàn)存儲(chǔ)為M+1長度，一個(gè)結(jié)束符。
insert into tmp4 values ('ab ','ab ');
mysql> select concat('(',ch,')'),concat('(',vch,')') from tmp4;
+--------------------+---------------------+
| concat('(',ch,')') | concat('(',vch,')') |
+--------------------+---------------------+
| (ab) | (ab ) |
+--------------------+---------------------+
insert into tmp4 values ('ab ','ab ');
select concat('(',ch,')'),concat('(',vch,')') from tmp4;
+--------------------+---------------------+
| concat('(',ch,')') | concat('(',vch,')') |
+--------------------+---------------------+
| (ab) | (ab ) |
+--------------------+---------------------+
32。ENUM是一個(gè)字符串對(duì)象，其值為表創(chuàng)建時(shí)在列規(guī)定中枚舉的一列值。
create table tmp5(enm ENUM('first','second','third');
mysql> insert into tmp5 values('first'),('second'),('df');
ERROR 1265 (01000): Data truncated for column 'enm' at row 3 //沒有‘df’這個(gè)枚舉值
insert into tmp5 values('first'),('second')；
+--------+
| enm |
+--------+
| first |
| second |
+--------+
- 二進(jìn)制
前面講解了存儲(chǔ)文本的字符串類型，這一節(jié)將講解MySQL中存儲(chǔ)二進(jìn)制數(shù)據(jù)的數(shù)據(jù)類型
BIT、BINARY、VARBINARY、TINYBLOB、
BLOB、MEDIUMBLOB、LONGBLOB，
41。BIT（M）類型
BIT類型是位字段類型，M表示每個(gè)值的位數(shù)，范圍為1~64.如果M被省略，默認(rèn)為1，如果為BIT(M)列分配的值的長度小于M位，在值的左邊用0填充。例如，為BIT(6）列分配一個(gè)值b'101',其效果與分配b‘000101‘相同。大于二進(jìn)制1111的數(shù)據(jù)是不能插入到BIT(4)中
insert into tmp6 values(2),(6),(9);
mysql> select BIN(b+0) from tmp6;
+----------+
| BIN(b+0) |
+----------+
| 10 |
| 110 |
| 1001 |
| 1101 |
+----------+
- 日期與時(shí)間類型
51。YEAR
日期格式---YYYY,日期范圍：1901~2155 存儲(chǔ)需求：1字節(jié)，（1）以4位字符串或數(shù)字格式表示YEAR年，例：輸入 ‘2020‘或2020，插入到數(shù)據(jù)庫的值均為2020.（2）以2位數(shù)字符串或數(shù)字格式表示的YEAR,范圍為‘00‘到‘99‘?！?0‘~‘69‘和‘70‘~’99’，范圍的值分別被轉(zhuǎn)換為2000~2069和1970~1999，數(shù)字1~69和70~99范圍的值分別被轉(zhuǎn)換為2000~2069和1970~1999，
例：
create table tmp3(y YEAR);
INSERT INTO tmp3 values(2010),('2010');
+------+
| y |
+------+
| 2010 |
| 2010 |
+------+
insert into tmp3 values('0'),('00'),('77'),('10');
+------+
| y |
+------+
| 2000 |
| 2000 |
| 1977 |
| 2010 |
+------+
insert into tmp3 values(77),(10);
| 1977 |
| 2010 |
52。TIME
時(shí)間類類里，在存儲(chǔ)時(shí)需要3個(gè)字節(jié)。格式為‘HH:MM:SS’.TIME類型的取值范圍為－838：59：59~838：59：59。（1）‘D HH:MM:SS'格式的字符串，可以使用下面任何一種語法，‘HH:MM:SS','D HH:MM','HH:MM','D HH'或‘SS’,為里D表示日，可以取值0~34之間的值，在插入數(shù)據(jù)庫時(shí)，D被轉(zhuǎn)成小時(shí)保存，格式為“D*24+HH" (2)?！瓾HMMSS'格式的，沒有間隔符的字符串或HHMMSS數(shù)字格式的值，例如‘101112‘被理解為‘10：11：12’，但‘109712’是不合法的，因?yàn)榉譀]有大于59的。（3）CURRENT_TIME,NOW(),會(huì)自動(dòng)識(shí)別當(dāng)前時(shí)間TIME.
例：
insert into tmp4 values('10:05:05'),('23:23'),('2 10:10'),('3 02'),('10');
+----------+
| t |
+----------+
| 10:05:05 |
| 23:23:00 |
| 58:10:00 |
| 74:00:00 |
| 00:00:10 |
+----------+
insert into tmp4 values('101112'),('111213'),('0');
+----------+
| t |
+----------+
| 10:11:12 |
| 11:12:13 |
| 00:00:00 |
+----------+
insert into tmp4 values(CURRENT_TIME),(now());
53。DATE
該類型用在僅需要日期值時(shí)，沒有時(shí)間部分,存儲(chǔ)需要3個(gè)字節(jié)（1），以‘YYYY-MM-DD’或‘YYYYMMDD’字符串表示的日期，取值范圍'1000-01-01'~'9999-12-3'（2）'YY-MM-DD'或'YYMMDD',二位年份參考前面的YEAR類型講解（3），使用CURRENT_DATE或NOW(),插入當(dāng)前系統(tǒng)的日期
----Mysql允許“不嚴(yán)格”語法：任何標(biāo)點(diǎn)符號(hào)都可以用作日期部分之間的間隔符。例：‘99-11-31' '99.11.31','99/11/31','99@11@31"都是等價(jià)的。
insert into tmp1 values (990909),(000101),(111111);
+------------+
| d |
+------------+
| 1999-09-09 |
| 2000-01-01 |
| 2011-11-11 |
+------------+
54。DATETIME
DATATIME類型用在需要同時(shí)包含日期和時(shí)間信息的值，在存儲(chǔ)時(shí)需要8個(gè)字節(jié)。日期格式'YYYY-MM-DD HH:MM:SS'(1).''YYYY-MM-DD HH:MM:SS'或者‘YYYYMMDDHHMMSS’取值范圍是'1000-01-01 00:00:00'到‘9999－12－3 23L59：59‘（2）?！甕Y-MM-DD HH:MM:SS'或者‘YYMMDDHHMMSS'(3)。YYYYMMDDHHMMSS或者YYMMDDHHMMSS
insert into tmp2 values('1998-01-17 08:08:08'),('19980117080808');
+---------------------+
| d |
+---------------------+
| 1998-01-17 08:08:08 |
| 1998-01-17 08:08:08 |
insert into tmp2 values('99-09-09 09:09:09'),('990909090909');
| 1999-09-09 09:09:09 |
| 1999-09-09 09:09:09 |
+---------------------+
insert into tmp2 values (NOW()); //插入當(dāng)前日期
55。TIMESTAMP
顯示格式與DATETIME相同，顯示寬度固定在19個(gè)字符，存儲(chǔ)需要4個(gè)字節(jié)，但它的取值范圍為'1970-01-01 00:00:01' UTC~ '2038-01-19 03:14:07` UTC.UTC世間標(biāo)準(zhǔn)時(shí)間。TIMESTAMP值的存儲(chǔ)是以UTC格式保存的，存儲(chǔ)時(shí)對(duì)前時(shí)區(qū)進(jìn)行轉(zhuǎn)換，檢索時(shí)再轉(zhuǎn)換回回當(dāng)前時(shí)區(qū)。即查詢時(shí)，根據(jù)當(dāng)前時(shí)區(qū)的不同，顯示的時(shí)間值是不同的
insert into tmp3(NOW());
+---------------------+
| ti |
+---------------------+
| 2022-12-08 20:00:37 |
+---------------------+
//我們中國所在的時(shí)區(qū)為東8區(qū)，下面我們修改一下時(shí)區(qū)為東10區(qū)，再看看結(jié)果
set time_zone='+10:00';
+---------------------+
| 2022-12-08 22:00:37 |
+---------------------+
常見運(yùn)算符介紹
運(yùn)算符,下面是按高到低的優(yōu)先級(jí)寫的。
!
-負(fù)號(hào)，~位反轉(zhuǎn)
*,/,%(求余)
+,-
<<.>>
&
|
=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN,LEAST(返回最小值）
BETWEEN,CASE,WHEN,THEN,ELSE
NOT
&& ,AND
XOR
||,OR
=(賦值),:=,逗號(hào)
- 算術(shù)運(yùn)算符
例：創(chuàng)建表tmp7,定義數(shù)據(jù)類型為INT的字段num，插入值64，對(duì)num值進(jìn)行算術(shù)運(yùn)算。
create table tmp7(num int);
insert into tmp7 values(64);
select num,num+10,num-3+5,num+5-3,num+36.5 from tmp7;
+------+--------+---------+---------+----------+
| num | num+10 | num-3+5 | num+5-3 | num+36.5 |
+------+--------+---------+---------+----------+
| 64 | 74 | 66 | 66 | 100.5 |
+------+--------+---------+---------+----------+
