數(shù)據(jù)庫(kù)操作
- cmd命令進(jìn)入數(shù)據(jù)庫(kù) MySQL-u root -p
- 創(chuàng)建數(shù)據(jù)庫(kù)
Create database [if not exists] database_name character set utf8|gbk - collate 表示校隊(duì)規(guī)則 utf8_bin 區(qū)分字母的大小寫(xiě) uft-8_general_ci 表示不區(qū)分字母的大小寫(xiě)
- 查看數(shù)據(jù)庫(kù)
SHOW DATABASES; - 查看數(shù)據(jù)庫(kù)的創(chuàng)建語(yǔ)句
SHOW CREATE DATABASE 數(shù)據(jù)庫(kù)名字 - 修改數(shù)據(jù)庫(kù)編碼：
ALTER DATABASE DB_NAME CHARACTER SET UTF8 - 刪除數(shù)據(jù)庫(kù):
DROP DATABASE [IF EXISTES] DB_NAME; - 選擇進(jìn)去特定數(shù)據(jù)庫(kù)：
USE DB_NAME; - 如何在cmd窗口進(jìn)行包含漢語(yǔ)的查詢(xún)和插入：
set character_set_cilent=gbk;set character_set_results=gbk;因?yàn)閏md窗口只識(shí)別gbk編碼的漢字馋贤，所以設(shè)置之后不影響數(shù)據(jù)庫(kù)的編碼斟或，只是方便查看而已梦碗。
數(shù)據(jù)表操作
創(chuàng)建表 ：
( ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(40) UNIQUE,
MATH DOUBLE,
ENGLISH DOUBLE,
CHINESE DOUBLE
)[CHCARACTER SET UTF8];
- 查看當(dāng)前數(shù)據(jù)庫(kù)的所有表 ：
SHOW TABLES; - 查看建表語(yǔ)句：
SHOW CREATE TABLE TABLE_NAME; - 查看表結(jié)構(gòu) ：
DESC TABLE_NAME;
修改表
- 增加一列：
ALTER TABLE TABLE_NAME ADD 列名 類(lèi)型 - 修改一列：
ALTER TABLE TABLE_NAME MODIFY 列名 新類(lèi)型 - 修改表的字符集：
ALTER TABLE TABLE_NAME CHARACTER SET UTF8 - 修改表名：
ALTER TABLE 舊的表名 TO 新的表名 - 修改列名：
ALTER TABLE TABLE_NAME CHANGE 舊的列名 新的列名 新的類(lèi)型 - 刪除表：
DROP TABLE TABLE_NAME;
操作表記錄
- INSERT
INSERT INTO TABLE_NAME [(column,[column,...])] VALUES (VALUE,[VALUE...]);
插入的數(shù)據(jù)應(yīng)與字段的數(shù)據(jù)類(lèi)型相同。
數(shù)據(jù)的大小應(yīng)在列的規(guī)定范圍內(nèi)，例如：不能將一個(gè)長(zhǎng)度為80的字符串加入到長(zhǎng)度為40的列中。
在values中列出的數(shù)據(jù)位置必須與被加入的列的排列位置相對(duì)應(yīng)。
字符和日期型數(shù)據(jù)應(yīng)包含在單引號(hào)中。
插入空值：不指定或insert into table value(null)
如果要插入所有字段可以省寫(xiě)列列表递鹉，直接按表中字段順序?qū)懼盗斜?/p>
- UPDATE
UPDATE語(yǔ)法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要給予哪些值藏斩。
WHERE子句指定應(yīng)更新哪些行躏结。如沒(méi)有WHERE子句，則更新所有的行
- DELETE
DELETE FROM TABLE_NAME [WHERE 子句]
如果不使用where子句狰域，將刪除表中所有數(shù)據(jù)媳拴。
Delete語(yǔ)句不能刪除某一列的值（可使用update）
使用delete語(yǔ)句僅刪除記錄，不刪除表本身兆览。如要?jiǎng)h除表禀挫，使用drop table語(yǔ)句。
同insert和update一樣拓颓，從一個(gè)表中刪除記錄將引起其它表的參照完整性問(wèn)題语婴，在修改數(shù)據(jù)庫(kù)數(shù)據(jù)時(shí)，頭腦中應(yīng)該始終不要忘記這個(gè)潛在的問(wèn)題驶睦。
外鍵約束
刪除表中數(shù)據(jù)也可使用TRUNCATE TABLE 語(yǔ)句砰左，它和delete有所不同，參看mysql文檔场航。
- QUERY
- 基本查詢(xún)：
SELECT [DISTINCT] FROM TABLE_NAME [WHERE 子句]
- 查詢(xún)學(xué)生表中的所有數(shù)據(jù) ：SELECT * FROM STUDENT;
- 查詢(xún)學(xué)生表中所有學(xué)生的name和math：SELECT NAME,MATH FROM STUDENT;
- 使用別名查詢(xún)：
SELECT NAME [AS] 姓名缠导，ENGLISH+MATH+CHINESE [AS] 總成績(jī) FROM STUDENG;
- 使用where子句過(guò)濾查詢(xún)(操作符+ - > < % _ between and or in )：
查詢(xún)姓名為lu的學(xué)生的語(yǔ)文成績(jī)：SELECT CHINESE FROM STUDENT WHERE NAME='lu';
查詢(xún)總分大于200分的學(xué)生姓名：SELECT NAME FROM STUDENT WHERE CHINESE+MATH+ENGLISH>200;
查詢(xún)姓張的學(xué)生的姓名：SELECT NAME FROM STUDENT WHERE NAME LIKE '張%'|'張_';
查詢(xún)數(shù)學(xué)成績(jī)?cè)?0~90的所有學(xué)生姓名：SELECT NAME FROM STUDENT WHERE MATH BETWEEN 70 AND 90;
查詢(xún)英語(yǔ)成績(jī)大于80，數(shù)學(xué)成績(jī)大于90的學(xué)生： SELECT * FROM STUDENT WHERE MATH>80 AND ENGLISH>90;
查詢(xún)學(xué)生語(yǔ)文成績(jī)?yōu)?0溉痢、70僻造、80的學(xué)生：SELECT * FROM STUDENG WHERE CHINESE IN (60，70孩饼，80);
- 使用ORDER BY 關(guān)鍵字對(duì)查詢(xún)結(jié)果排序（DESC 降序排列 ASC升序排列（默認(rèn)排列形式））
- 查詢(xún)學(xué)生的總成績(jī)按降序排列： SELECT NAME AS 名字髓削，MATH+ENGLISH+CHINESE AS 總成績(jī) FROM ATUDENG WHERE ORDER BY 總成績(jī) DESC;
- 對(duì)姓張的學(xué)生成績(jī)排序輸出: SELECT NAME AS 名字，MATH+ENGLISH+CHINESE AS 總成績(jī) FROM STUDENG WHERE NAME LIKE '張%' ORDER BY 總成績(jī) DESC;
- 聚合函數(shù)-用來(lái)將查詢(xún)結(jié)果包裹起來(lái)計(jì)算（SUM（求和）镀娶、COUNT（數(shù)據(jù)個(gè)數(shù)）立膛、 AVG（平均數(shù)）、MAX（最大值）、MIN（最小值））
- SUM 用來(lái)將查詢(xún)結(jié)果的列進(jìn)行求和計(jì)算：
統(tǒng)計(jì)一個(gè)班各科成績(jī)的總成績(jī)：SELECT SUM(MATH),SUM(CHINESE),SUM(ENGLISH) FROM STUDENT; - COUNT 用來(lái)統(tǒng)計(jì)查詢(xún)結(jié)果的個(gè)數(shù)：
統(tǒng)計(jì)一個(gè)班數(shù)學(xué)成績(jī)大于70的人數(shù)：SELECT COUNT(*) FROM STUDENT WHERE MATH>70; - AVG 用來(lái)計(jì)算查詢(xún)結(jié)果的列數(shù)據(jù)的平均值：
求出整個(gè)班數(shù)學(xué)成績(jī)平均值：SELECT AVG(MATH) FROM STUDENT; - MAX 用來(lái)計(jì)算查詢(xún)結(jié)果列數(shù)據(jù)中的最大值：
求出整個(gè)班數(shù)學(xué)成績(jī)的最大值：SELECT MAX(MATH) FROM STUDENT; - MIN 用來(lái)計(jì)算查詢(xún)結(jié)果列數(shù)據(jù)的最小值：
求出整個(gè)班數(shù)學(xué)成績(jī)的最小值：SELECT MIN(MATH) FROM STUDENT;
如果數(shù)據(jù)有null值宝泵，則會(huì)造成聚合失敗好啰，因?yàn)閚ull值無(wú)法進(jìn)行計(jì)算，所以在查詢(xún)時(shí)需要添加判空：SELECT AVG(IFNULL(MATH,0)) FROM STUDENG;切記IFNULL 之間沒(méi)有空格儿奶。
分組查詢(xún)(字段和數(shù)據(jù)表名字不能使用sql語(yǔ)句的關(guān)鍵字)
create table orders(
id int primary key auto_increment,
name varchar(40) unique,
product varchar(40),
price double);
GROUP BY(分組查詢(xún))
- 對(duì)訂單中的商品歸類(lèi)后框往，查詢(xún)每個(gè)商品類(lèi)的總價(jià)：SELECT PRODUCT,SUM(PRICE) FROM ORDERS GROUP BY PRODUCT;
- 查詢(xún)購(gòu)買(mǎi)了幾類(lèi)產(chǎn)品，每類(lèi)產(chǎn)品的總價(jià)不大于20的產(chǎn)品：SELECT PRODUCT FROM ORDERS GROUP BY PRODUCT HAVING SUM(PRICE)<20;
where子句和having子句的區(qū)別:
where子句在分組之前進(jìn)行過(guò)濾having子句在分組之后進(jìn)行過(guò)濾
having子句中可以使用聚合函數(shù),where子句中不能使用
很多情況下使用where子句的地方可以使用having子句進(jìn)行替代
- 查詢(xún)單價(jià)小于20而總價(jià)大于30的商品的名稱(chēng) :
SELECT PRODUCT FROM ORDERS WHERE PRICE<20 GROUP BY PRODUCT HAVING SUM(PRICE)>30
SQL語(yǔ)句書(shū)寫(xiě)順序：
SELECT FROM WHERE GROUP BY HAVING ORDER BY
SQL語(yǔ)句執(zhí)行順序
FROM WHERE SELECT GROUP BY HAVING ORDER BY
備份恢復(fù)數(shù)據(jù)庫(kù)
備份: 在cmd窗口下 mysqldump -u root -p dbName>c:/1.sql
恢復(fù): 方式1:在cmd窗口下 mysql -u root -p dbName<c:/1.sql
方式2:在mysql命令下, source c:/1.sql
要注意恢復(fù)數(shù)據(jù)只能恢復(fù)數(shù)據(jù)本身,數(shù)據(jù)庫(kù)沒(méi)法恢復(fù),需要先自己創(chuàng)建出數(shù)據(jù)后才能進(jìn)行恢復(fù).
多表設(shè)計(jì)多表查詢(xún)
- 外鍵約束:在進(jìn)行多表設(shè)計(jì)時(shí)闯捎，為了保證表與表之間的約束關(guān)系椰弊，引出了外鍵約束來(lái)進(jìn)行關(guān)聯(lián)：
- 創(chuàng)建兩個(gè)表，部門(mén)表和員工表隙券，員工表的dept_id作為關(guān)聯(lián)部門(mén)表的外鍵
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values(null,'財(cái)務(wù)部'),(null,'人事部'),(null,'銷(xiāo)售部'),(null,'行政部');
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
foreign key(dept_id) references dept(id)
);
insert into emp values(null,'奧巴馬',1),(null,'詹姆斯',2),(null,'本拉登',3),(null,'科比',3);
3.多表查詢(xún)
笛卡爾積查詢(xún):
將兩張表的記錄進(jìn)行一個(gè)相乘的操作查詢(xún)出來(lái)的結(jié)果就是笛卡爾積查詢(xún),如果左表有n條記錄,右表有m條記錄,笛卡爾積查詢(xún)出有n*m條記錄,其中往往包含了很多錯(cuò)誤的數(shù)據(jù),所以這種查詢(xún)方式并不常用
select * from dept,emp;
內(nèi)連接查詢(xún):查詢(xún)的是左邊表和右邊表都能找到對(duì)應(yīng)記錄的記錄
select * from dept,emp where dept.id = emp.dept_id;
select * from dept inner join emp on dept.id=emp.dept_id;
外連接查詢(xún):
左外連接查詢(xún):在內(nèi)連接的基礎(chǔ)上增加左邊表有而右邊表沒(méi)有的記錄
select * from dept left join emp on dept.id=emp.dept_id;
右外連接查詢(xún):在內(nèi)連接的基礎(chǔ)上增加右邊表有而左邊表沒(méi)有的記錄
select * from dept right join emp on dept.id=emp.dept_id;
全外連接查詢(xún):在內(nèi)連接的基礎(chǔ)上增加左邊表有而右邊表沒(méi)有的記錄和右邊表有而左表表沒(méi)有的記錄
select * from dept full join emp on dept.id=emp.dept_id; -- mysql不支持全外連接
可以使用union關(guān)鍵字模擬全外連接:
select * from dept left join emp on dept.id = emp.dept_id
union
select * from dept right join emp on dept.id = emp.dept_id;
