MySQL
SQL語句分類
- DDL(Data Definition Language) -- 數(shù)據(jù)庫定義語言椅挣，增刪改查庫或表
- DML(Data Control Language) -- 數(shù)據(jù)庫操作語言 操作數(shù)據(jù) 對表中數(shù)據(jù)的增刪改查
- DCL(Data Manipulation Language) -- 數(shù)據(jù)庫控制語言 創(chuàng)建用戶头岔，管理用戶權(quán)限，事務(wù)控制
非標準分類：DQL(Data Query Language) -- 數(shù)據(jù)庫查詢鼠证，將數(shù)據(jù)庫的增刪改查中的“查”單獨提出來峡竣，因為最重要也最難稠集。
與服務(wù)器連接
先把MySQL配置到系統(tǒng)環(huán)境變量中由捎，以為5.7為例，Path里增加C:\Program Files\MySQL\MySQL Server 5.7\bin塑煎。cmd里輸入mysql -u root -p荠列，其中u代表user类浪，這里用root，p代表密碼肌似，不輸入密碼费就，回車后也會提示你輸入。填入自己的密碼就連接成功了川队。
數(shù)據(jù)庫的CURD
1力细、添加新數(shù)據(jù)庫
create database 庫名稱 [character set 碼表名稱 collate 字符校對集名稱];睬澡，括號里面是可選項，一般不加也可艳汽，舉個例子create database book;猴贰，注意每行語句結(jié)束，需要加;河狐。show databases;指令可以顯示全部數(shù)據(jù)庫米绕。
2、刪除數(shù)據(jù)庫
drop database 庫名稱;
3馋艺、修改數(shù)據(jù)庫（可修改碼表和字符校對集）
alter database 庫名稱 character set utf8 collate utf8_bin;
默認utf-8栅干，該語句不常用。
4捐祠、使用一個庫
use 庫名稱;成功會出現(xiàn)Database changed碱鳞。
select database();可以顯示當(dāng)前正在使用的數(shù)據(jù)庫。
5踱蛀、顯示創(chuàng)建數(shù)據(jù)庫語句
show create database 數(shù)據(jù)庫名稱;
數(shù)據(jù)庫中的數(shù)據(jù)類型
1窿给、數(shù)字型
整型
| 整型類型 | 大小 | 對比Java |
|---|---|---|
| TINYINT | 1字節(jié) | byte |
| SMALLINT | 2字節(jié) | short |
| MEDIUMINT | 3字節(jié) | ---- |
| （常用）INT | 4字節(jié) | int |
| BIGINT | 8字節(jié) | long |
浮點型
| 浮點類型 | 大小 | 對比Java |
|---|---|---|
| FLOAT | 單精度4字節(jié) | float |
| （常用）DOUBLE | 8字節(jié) | double |
| DECIMAL | -- | String保存的數(shù)字，沒有精度損失 |
DOUBLE 和 DECIMAL 區(qū)別率拒？
- DOUBLE類型在運算時會有精度的缺失崩泡。
- DECIMAL 就是解決精度缺失問題的。（底層使用字符串來保存數(shù)字）
單純想表示小數(shù)屬性時猬膨，使用double角撞。
需要頻繁參與運算的小數(shù)，使用decimal勃痴。
2谒所、字符串
注意: 字符串類型要使用單引號包裹.
短字符串類型
CHAR/VARCHAR (最大長度255字節(jié))
char和varchar有什么區(qū)別?
- char：定長字符串。
- varchar：變長字符串沛申。
同時指定長度為10劣领。當(dāng)存儲 abc時候
char =》 'abc ' 后面有7個空格
varchar => 'abc'
結(jié)論： 開發(fā)中varchar用的最多。 char只在表示固定長度的枚舉中使用污它。例如 ：性別（用01,02表示）
長字符串類型(流類型)
TEXT
保存文本(字符流) --> 當(dāng)要保存的內(nèi)容超過255字節(jié)時使用剖踊。java中的writer 字符。TEXT只能存儲字符數(shù)據(jù).
BLOB
保存字節(jié)(字節(jié)流) --> 開發(fā)中用不到衫贬。 java中的stream 字節(jié)德澈。
BLOB可以存儲字符和多媒體信息(圖片 聲音 圖像)
3、日期類型
| 類型 | 記錄的數(shù)據(jù) | 例子 |
|---|---|---|
| date | 只記錄日期 | 2017-03-11 |
| time | 只記錄時間 | 10:36:25 |
| year | 只記錄年 | 2017 |
| （常用）datatime | 記錄日期固惯、時間 | 2017-03-11 10:36:25 |
| （常用）timestamp | 記錄日期梆造、時間 | 2017-03-11 11:36:25 |
datatime 和 timestamp 區(qū)別?
這兩種類型記錄的數(shù)據(jù)是一模一樣。
-
區(qū)別在于插入的時候，如果插入datatime類型時镇辉，沒有傳值屡穗，那么該類型默認值就是
null；如果插入timestamp類型時忽肛，沒有傳值村砂，那么該類型默認值就是當(dāng)前時間。
表的CURD
1础废、創(chuàng)建表
create table t_user (
id int,
name varchar(20),
sal double(4,3),
birthday datetime,
hiredate timestamp
);
double(4, 3)中間是逗號，表示小數(shù)點前4位评腺，小數(shù)點后面3位。
2蒿讥、顯示所有表、查看某個表的結(jié)構(gòu)
顯示所有表：show tables;
查看某個表的結(jié)構(gòu)：desc 表名;
3芋绸、刪除表
drop table 表名
4、添加一列
alter table 表名 add 列名 類型;
alter table t_user add age int;
5担敌、修改列的類型
alter table 表名 modify 列名 類型;
alter table t_user modify age varchar(20);
6侥钳、修改列的名稱
alter table 表名 change 舊列名 新列名 數(shù)據(jù)類型；
alter table t_user change age location varchar(20);
7柄错、刪除某一列
alter table 表名 drop 列名;
alter table t_user drop location;
8、修改表名
rename table 舊表名 to 新名;
rename table t_user to t_root;
列的約束
1.非空約束(not null) 指定非空約束的列, 在插入記錄時 必須包含值.
2.唯一約束(unique) 該列的內(nèi)容在表中. 值是唯一的.
3.主鍵約束(primary key) 當(dāng)想要把某一列的值,作為該列的唯一標示符時,可以指定主鍵約束
(包含 非空約束和唯一約束). 一個表中只能指定一個主鍵約束列苦酱。主鍵約束 , 可以理解為非空
+唯一.
注意： 并且一張表中只能有一個主鍵約束.
主鍵自增
注意：
- 前提某個表的主鍵是數(shù)字. 我們可以將該主鍵設(shè)置為自增.
- 使用主鍵自增可能會造成主鍵的斷層售貌。（刪除后數(shù)值不減，比如刪除4疫萤、5行颂跨，下次新增行從6開始）
- mysql,sqlserver,sqllite這三個數(shù)據(jù)庫具有該功能.
- 主鍵自增只能給主鍵約束的列加。
自增就是 每次插入記錄時不需要指定值扯饶，該字段自己維護自己的值恒削。維護方式就是每次加1。
語法如id int primary key auto_increment,
表中數(shù)據(jù)的增刪改(DML)
1尾序、為表添加記錄
insert into 表名[(列名1,列名2...)] values (值1,值2...);
- 指定要插入哪些列
insert into t_user(name, email) values('tom','tom@163.com');
注意: 數(shù)據(jù)類型為字符串類型的钓丰，需要使用單引號包裹.
不指定插入哪些列，默認全部列每币，需要指定每一列的值携丁。
insert into t_user values(null,'jerry','jerry@itcast.cn');id自增所以填寫null。
``insert into t_user(name,email) values('湯姆','tom2@163.com');`
2兰怠、修改一條記錄
update 表名 set 列名1 = 值 , 列名2 = 值 ....[where 條件1,條件2...]
UPDATE t_user SET NAME='rose' WHERE id=7;表示將id為7的數(shù)據(jù)name改為rose梦鉴。
如果不指定id則是將所有列name都更新為rose李茫。
3、刪除某一條記錄
DELETE FROM 表名 [WHERE 條件];
DELETE FROM t_user WHERE NAME='God';把name是God的記錄刪除肥橙。
DELETE FROM t_user;刪除整張表中所有記錄魄宏。
使用truncate刪除表中記錄。
TRUNCATE TABLE employee;
DELETE 刪除 和 TRUNCATE刪除(了解) 兩者有什么區(qū)別?
首先,這兩種都是刪除表中的記錄存筏。
不同的是:
- delete 是逐行標記刪除. TRUNCATE 是將整張表包括表結(jié)構(gòu)都移除,然后將表重新創(chuàng)建.
- delete DML語句宠互。 TRUNCATE DDL語句。
- delete 刪除的記錄可以被恢復(fù)方篮，TRUNCATE 不能恢復(fù)名秀。
- delete 不釋放空間，TRUNCATE 釋放空間藕溅。
- TRUNCATE 會提交事務(wù)。
拿自增的id來說汁掠，delete后id不會歸為1考阱，而是延續(xù)被刪除的id繼續(xù)自增乞榨。truncate后id又像新表一樣從1開始吃既。
4跨细、查詢
語法：
SELECT selection_list -- 要查詢的列名稱
FROM table_list -- 要查詢的表名稱
WHERE condition -- 行條件
GROUP BY grouping_columns -- 對結(jié)果分組
HAVING condition -- 分組后的行條件
ORDER BY sorting_columns -- 對結(jié)果排序
LIMIT offset_start, row_count -- 結(jié)果限定
-
查詢所有行所有列
select * from stu;
*號 是通配符.通配所有列. 上面語句與下面是一模一樣的
select sid,sname,age,gender from stu;
誰的效率更高?下面的效率更高冀惭。*需要運算散休±；保可以忽略不計，用*簡便对人。
- 查詢某一列
select sname from stu;只會查詢sname這一行牺弄。
2.1 條件查詢介紹
條件查詢就是在查詢時給出WHERE子句势告，在WHERE子句中可以使用如下運算符及關(guān)鍵字：
=抚恒、!=俭驮、<>混萝、<、<=车要、>翼岁、>=登澜；
BETWEEN…AND；
IN(SET)/NOT IN(SET)
IS NULL/IS NOT NULL
---條件連接符
AND购撼； &&
OR迂求； ||
NOT揩局； !
查詢例子
-- 查詢性別為女，并且年齡小于50的記錄
select * from stu where gender='female' and age<50;
-- 查詢學(xué)號為S_1001烹玉，或者姓名為liSi的記錄
select * from stu where sid='S_1001' or sname='liSi';
-- 數(shù)據(jù)庫中,sql語句不區(qū)分大小寫 ,但是數(shù)據(jù)區(qū)分大小寫.
-- 查詢學(xué)號為S_1001二打，S_1002继效，S_1003的記錄
select * from stu where sid='S_1001' or sid='S_1002' or sid='S_1003';
select * from stu where sid in('S_1001','S_1002','S_1003');
-- 查詢學(xué)號不是S_1001瑞信，S_1002凡简，S_1003的記錄
select * from stu where not (sid='S_1001' or sid='S_1002' or sid='S_1003');
select * from stu where sid not in('S_1001','S_1002','S_1003');
-- 查詢年齡為null的記錄
select * from stu where age=null;
-- null的特性: null不等于null 所以判斷時應(yīng)如下寫法:
select * from stu where age is null;
-- 查詢年齡在20到40之間的學(xué)生記錄
select * from stu where age >= 20 and age <= 40;
select * from stu where age between 20 and 40;
-- 查詢性別非男的學(xué)生記錄
select * from stu where gender!= 'male';
select * from stu where not gender='male';
select * from stu where gender not in ('male');
-- 查詢姓名不為null的學(xué)生記錄
select * from stu where sname is not null;
select * from stu where not sname is null;
模糊查詢
where 字段 like '表達式';
% 通配任意個字符.
_通配單個字符潘鲫。
*通配任意多個字符溉仑。
-- 查詢姓名由5個字母構(gòu)成的學(xué)生記錄
select * from stu where sname like '_____';
-- 查詢姓名由5個字母構(gòu)成浊竟，并且第5個字母為“i”的學(xué)生記錄
select * from stu where sname like '____i';
-- 查詢姓名以“z”開頭的學(xué)生記錄
-- 說明: "%"該通配符匹配任意長度的字符.
select * from stu where sname like 'z%';
-- 查詢姓名中第2個字母為“i”的學(xué)生記錄
select * from stu where sname like '_i%';
-- 查詢姓名中包含“a”字母的學(xué)生記錄
select * from stu where sname like '%a%';
去除重復(fù)的記錄
-- 關(guān)鍵詞: distinct 去除重復(fù)查詢結(jié)果記錄.
select gender from stu; -- 出現(xiàn)大量重復(fù)的記錄
select distinct gender from stu; -- 去除重復(fù)的記錄
查看雇員的月薪與傭金之和
select sal*12+comm from emp;
null與任何數(shù)字計算結(jié)果都是null.上面的寫法是錯誤的.
使用IFNULL(參數(shù)1,參數(shù)2) 函數(shù)解決. 判斷參數(shù)1的值是否為null,如果為null返回參數(shù)2的值.
select sal*12 + IFNULL(comm,0) from emp;
這個函數(shù)在所有數(shù)據(jù)庫通用嗎振定？不通用后频。
給列名添加別名
SELECT ename '姓名' FROM emp; -- 把ename重命名為姓名
select sal*12 + IFNULL(comm,0) as '年收入' from emp;
select sal*12 + IFNULL(comm,0) '年收入' from emp;
select sal*12 + IFNULL(comm,0) 年收入 from emp;
按順序查詢
-- 查詢所有學(xué)生記錄暖途，按年齡升序排序
-- asc: 升序
-- desc:降序
select * from stu order by age asc;
-- 默認就是升序
select * from stu order by age;
-- 查詢所有學(xué)生記錄驻售，按年齡降序排序
select * from stu order by age desc;
-- 查詢所有雇員欺栗，按月薪降序排序征峦，如果月薪相同時栏笆，按編號升序排序竖伯。前面一個優(yōu)先因宇，后面一個僅僅是前面的相同時才會比較
select * from emp order by sal desc , empno asc;
聚合函數(shù)
聚合函數(shù)是用來做縱向運算的函數(shù)：
COUNT()：統(tǒng)計指定列不為NULL的記錄行數(shù)察滑；
MAX()：計算指定列的最大值贺辰，如果指定列是字符串類型，那么使用字符串排序運算莽鸭；
MIN()：計算指定列的最小值硫眨，如果指定列是字符串類型礁阁，那么使用字符串排序運算族奢；
SUM()：計算指定列的數(shù)值和，如果指定列類型不是數(shù)值類型越走，那么計算結(jié)果為0；
AVG()：計算指定列的平均值南片，如果指定列類型不是數(shù)值類型，那么計算結(jié)果為0薪缆；
-- COUNT
-- 當(dāng)需要縱向統(tǒng)計時可以使用COUNT()伞广。
-- 查詢emp表中記錄數(shù)：
select count(*) from emp;
-- 查詢emp表中有傭金的人數(shù)：
select count(*) from emp where comm is not null and comm >0;
-- 查詢emp表中月薪大于2500的人數(shù)：
select count(*) from emp where sal > 2500;
-- 統(tǒng)計月薪與傭金之和大于2500元的人數(shù)：
select count(*) from emp where sal+IFNULL(comm,0) > 2500;
-- 查詢有傭金的人數(shù)并且有領(lǐng)導(dǎo)的人數(shù)：
select count(*) from emp where comm > 0 and mgr is not null;
-- SUM(計算總和)和AVG(計算平均值)
-- 當(dāng)需要縱向求和時使用sum()函數(shù)。
-- 查詢所有雇員月薪和：
select sum(sal) from emp;
-- 查詢所有雇員月薪和区丑，以及所有雇員傭金和：
select sum(sal),sum(comm) from emp;
-- 查詢所有雇員月薪+傭金和：
select sum(sal+IFNULL(comm,0)) from emp;
-- 統(tǒng)計所有員工平均工資：
select avg(sal) from emp;
-- MAX和MIN
-- 查詢最高工資和最低工資：
select max(sal),min(sal) from emp;
分組查詢
當(dāng)需要分組查詢時需要使用GROUP BY子句沧侥，例如查詢每個部門的工資和魄鸦，這說明要使用部分來分組拾因。
-- 查詢每個部門的部門編號和每個部門的工資和：
select deptno,sum(sal) from emp group by deptno;
-- 查詢每個部門的部門編號以及每個部門的人數(shù)：
select deptno,count(ename) from emp group by deptno;
-- 查詢每個部門的部門編號以及每個部門工資大于1500的人數(shù)：
select deptno,count(ename) from emp where sal>1500 group by deptno ;
-- HAVING子句
-- 查詢工資總和大于9000的部門編號以及工資和：
select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;
-- 使用having在分組之后加條件.
-- where和having都可以加條件?
-- where在分組之前加條件.
-- having在分組之后加條件.
-- where的效率要遠遠高于having. 分組本身消耗資源非常大.
LIMIT
LIMIT用來限定查詢結(jié)果的起始行绢记，以及總行數(shù)。
第一個是起始行跪解，第二個是查幾條惠遏。
-- 查詢5行記錄骏啰，起始行從0開始
select * from emp limit 0,5;
-- 查詢10行記錄判耕，起始行從3開始
select * from emp limit 3,10;
如果一頁記錄為5條，希望查看第3頁記錄應(yīng)該怎么查呢帚豪？
-- 第一頁記錄起始行為0狸臣，一共查詢5行烛亦；
select * from emp limit 0,5;
-- 第二頁記錄起始行為5，一共查詢5行铐达；
select * from emp limit 5,5;
-- 第三頁記錄起始行為10瓮孙，一共查詢5行杭抠；
select * from emp limit 10,5;
by @sunhaiyu
2017.3.11
