SQL的分類
| 名稱 | 描述 | 舉例 |
|---|---|---|
| DDL | 數(shù)據(jù)定義語(yǔ)言 | create（創(chuàng)建）、alter（修改）、drop（刪除）、rename（重命名）、truncate（清空） |
| DML | 數(shù)據(jù)操作語(yǔ)言 | insert（添加）、delete（刪除）、update（修改）、select（查詢） |
| DCL | 數(shù)據(jù)控制語(yǔ)言 | commit（提交）、rollback（撤銷）、grant（賦予權(quán)限）、revoke（回收權(quán)限） |
一、數(shù)據(jù)庫(kù)的數(shù)據(jù)類型
數(shù)值
①整數(shù)
| 名稱 | 含義 | 占用字節(jié) |
|---|---|---|
| tinyint | 十分小的數(shù)據(jù) | 1 |
| smallint | 較小的數(shù)據(jù) | 2 |
| mediumint | 中等大小的數(shù)據(jù) | 3 |
int |
標(biāo)準(zhǔn)的整數(shù)（常用） | 4 |
| bigint | 較大的數(shù)據(jù) | 8 |
②小數(shù)
| 名稱 | 含義 | 占用字節(jié) |
|---|---|---|
| float | 單精度浮點(diǎn)數(shù) | 4 |
double |
雙精度浮點(diǎn)數(shù) | 8 |
| decimal | 字符串形式的浮點(diǎn)數(shù)（金融計(jì)算時(shí)使用,精度更高） |
字符串
| 名稱 | 含義 | 占用字節(jié) |
|---|---|---|
char |
固定字符串 | 0~255 |
varchar |
可變字符串（常用） | 0~65535 |
| tinytext | 微型文本 | 0~255 |
| text | 文本串（常用） | 0~65535 |
char和tinyint的區(qū)別
char：在定義時(shí)必須填寫(xiě)大小，例:char[20]，創(chuàng)建一個(gè)20個(gè)字節(jié)的char類型數(shù)據(jù)
tinytext:在定義時(shí)無(wú)須填寫(xiě)大小，固定為255字節(jié)
時(shí)間日期
| 名稱 | 含義 |
|---|---|
| date | 日期格式：YYYY-DD-MM |
| time | 時(shí)間格式：HH:mm:ss |
datetime |
YYYY-DD-MM HH:mm:ss (常用) |
| timestamp | 時(shí)間戳，1970.1.1到現(xiàn)在的毫秒數(shù)（較為常用） |
| year | 年份表示 |
null
可以理解為空值
不要使用null進(jìn)行運(yùn)算，結(jié)果為null
二、運(yùn)算符
2.1、算術(shù)運(yùn)算符
| 運(yùn)算符 | 含義 |
|---|---|
| + | 加法 |
| - | 減法 |
| * | 乘法 |
| / 或 div | 除法 |
| % 或mod | 取模、取余 |
模[余數(shù)]的正負(fù)，只與被模數(shù)[被除數(shù)的]正負(fù)有關(guān)，例:
-12 % 5 = -2 -12 % -5 = -2
2.2、邏輯運(yùn)算符
| 運(yùn)算符 | 語(yǔ)法 | 描述 |
|---|---|---|
| and && | a and b 或 a&&b | 邏輯與（兩個(gè)都為真，結(jié)果為真） |
| or || | a or b 或 a||b | 邏輯或（其中一個(gè)為真，結(jié)果為真） |
| not ! | not a 或 ！a | 邏輯非（取反） |
2.3、比較運(yùn)算符
| 運(yùn)算符 | 名稱 | 作用 | 示例 |
|---|---|---|---|
| is null | 為空運(yùn)算符 | 判斷值、字符串或表達(dá)式是否為空 | 如果a為null，結(jié)果為真 |
| is not null | 不為空運(yùn)算符 | 判斷值、字符串或表達(dá)式是否不為空 | 如果a不為null，結(jié)果為真 |
| between...and... | 兩值之間的運(yùn)算符 | 判斷一個(gè)值是否在兩個(gè)值之間 | 若a在b和c之間，結(jié)果為真 |
三、操作數(shù)據(jù)庫(kù)
-- 1、創(chuàng)建數(shù)據(jù)庫(kù)
create database [if not exists] 數(shù)據(jù)庫(kù)名；
-- 1、創(chuàng)建數(shù)據(jù)庫(kù)并指明字符集
create database [if not exists] 數(shù)據(jù)庫(kù)名 character set 'utf8'；
-- 2、查看當(dāng)前連接中的所有數(shù)據(jù)庫(kù)
show databases;
-- 3、查看當(dāng)前使用的數(shù)據(jù)庫(kù)
select database()
-- 4、切換數(shù)據(jù)庫(kù)
use 數(shù)據(jù)庫(kù)名；
-- 5、刪除數(shù)據(jù)庫(kù)
drop database [if exists] 數(shù)據(jù)庫(kù)名；
-- 6、更改數(shù)據(jù)庫(kù)字符集
alter database 數(shù)據(jù)庫(kù)名 character set 'utf8'；
四、操作表
4,.1、創(chuàng)建表
-- 刪除表
drop table [if exists] 表名；
-- 創(chuàng)建表
create table 表名(
字段名 類型 約束（主鍵，非空，唯一，默認(rèn)值），
字段名 類型 約束（主鍵，非空，唯一，默認(rèn)值），
...
字段名 類型 約束（主鍵，非空，唯一，默認(rèn)值）
)編碼，存儲(chǔ)引擎；
CREATE TABLE student
(
sid INT not null,
sname varchar(32),
sage INT,
ssex varchar(8),
primary key (sid)
);
在SQL中，有如下約束：
- not null -指示某列不能存儲(chǔ)null值。
- unique -保證某列的每行必須有唯一的值。
- primary key -主鍵（非空，唯一，not null和unique的結(jié)合）。
- foreign key -外鍵，保證一個(gè)表中的數(shù)據(jù)匹配另一個(gè)表中的值的參照完整性。
- check -保證列中的值符合指定的條件。
- default -規(guī)定沒(méi)有給列賦值時(shí)的默認(rèn)值。
-- 例
CREATE TABLE student
(
sid INT,
sname nvarchar(32),
sage INT,
ssex nvarchar(8)
)engine=InnoDB default charset=utf8;
常用命令
show create database 數(shù)據(jù)庫(kù)名； -- 查看創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)句
show create table 表名；--查看創(chuàng)建該表的語(yǔ)句
desc 表名； --查看表的結(jié)構(gòu)
5.2、修改表
5.2.1、對(duì)表名進(jìn)行修改
1、重命名表
-- 把表A的名字改為B
#方式一：
rename table A to B；
#方式二：
alter table A rename [to] B；
2、清空表
-- 清空表A內(nèi)的數(shù)據(jù)
truncate table A;
5.2.2、對(duì)表內(nèi)字段進(jìn)行修改
alter：修改
1、添加列
-- 添加一個(gè)字段（默認(rèn)添加到最后一列）
alter table 表名 add 字段名 [類型]；
-- 添加到第一列
alter table 表名 add 字段名 [類型] first；
-- 添加到指定列的后面（放到A列后面）
alter table 表名 add 字段名 [類型] after A；
2、修改列
-- 修改字段的長(zhǎng)度(把A的字符串長(zhǎng)度改為10)
alter table 表名 modify A varchar(10);
--修改字段的長(zhǎng)度(把A的字符串長(zhǎng)度改為10，并添加默認(rèn)值為a)
alter table 表名 modify A varchar(10) default 'a';
3、重命名列
-- 重命名字段（列名a改成b）
alter table 表名 change a b；
-- 重命名的同時(shí)，修改字符串的長(zhǎng)度
alter table 表名 change a b varchar(20)；
4、刪除列
-- 刪除一個(gè)字段（列）
alter table 表名 drop column a；
5.3、刪除表
-- 刪除表A
drop table if exists A；
五、操作數(shù)據(jù)
5.1、插入數(shù)據(jù)
-- 插入3條具體數(shù)據(jù)
insert into student(sid,sname,sage,ssex) values(1,'劉一',18,'男'),
(2,'錢(qián)二',19,'女'),
(3,'張三',17,'男')
-- 插入一個(gè)結(jié)果集（字段與字段類型要對(duì)應(yīng)）
-- 查詢語(yǔ)句的返回結(jié)果為：（3,2,72），插入到sc表中
insert into sc
select sid,2,(SELECT AVG(score) FROM sc WHERE cid =2)
from student
where sid not in(select sid from sc where cid = 3)
5.2、更新數(shù)據(jù)（修改數(shù)據(jù)）
-- 格式：update 表名 set 數(shù)據(jù) where 過(guò)濾條件
-- 例：把第3條數(shù)據(jù)的'葉平'改為'王艷'
UPDATE teacher set tname = '王艷' where tid=3;
-- 多表連接更新
UPDATE sc
JOIN (select cid,avg(score) AS avg
from sc
WHERE cid IN(SELECT cou.cid
FROM teacher tea,course cou
WHERE tea.tid = cou.tid AND tea.tname = '葉平')
GROUP BY cid) AS SS
ON sc.cid =SS.cid
SET sc.score = SS.avg
修改數(shù)據(jù)時(shí)，由于約束的影響，可能會(huì)造成更新失敗的情況
5.3、刪除數(shù)據(jù)
-- 格式：delete from 表名 where 過(guò)濾條件
-- 把葉平刪除
delete from teacher where tname = '葉平';
刪除數(shù)據(jù)時(shí)，由于約束的影響，可能會(huì)造成更新失敗的情況
5.4、查詢數(shù)據(jù)
查詢語(yǔ)句書(shū)寫(xiě)的結(jié)構(gòu)
查詢語(yǔ)句執(zhí)行的順序
AS（alias） 別名
作用：有的時(shí)候，列名字不是那么見(jiàn)名知意，可以起一個(gè)別名
格式：字段名 AS 別名
-- 別名 給查詢的字段起一個(gè)名字 AS
select sname as 學(xué)生姓名,sage as 學(xué)生年齡 from student；
distinct 去重
作用：去除select查詢出來(lái)的結(jié)果中重復(fù)的數(shù)據(jù)，重復(fù)的數(shù)據(jù)只顯示一條
格式：distinct 字段名
-- 查詢有哪些學(xué)生參加了考試；
SELECT sid FROM sc;
-- 發(fā)現(xiàn)重復(fù)數(shù)據(jù)，去重
SELECT DISTINCT sid from sc;
where 條件子句
作用：檢索數(shù)據(jù)中符合條件的值
--查詢sid=1的學(xué)生的所有課程的成績(jī)
SELECT sid,score FROM sc WHERE sid = 1;
in/not in
作用：在某個(gè)范圍內(nèi)
like
作用：模糊查詢
_:表示單個(gè)字符
%：表示0或無(wú)數(shù)個(gè)字符
-- 查詢姓李的學(xué)生
select sname from student where like '李%';
-- 查詢名字中帶國(guó)的學(xué)生
select sname from student where like '%國(guó)%';
order by
作用：子句排序，默認(rèn)升序
- ASC 升序
- DESC 降序
-- 查詢學(xué)生的所有成績(jī)，并降序排列
select * FROM sc ORDER BY score DESC;
-- 一級(jí)排序、二級(jí)排序
-- 查詢學(xué)生的所有成績(jī)，并降序排列，當(dāng)成績(jī)相同時(shí)，按學(xué)生id升序排列
select * FROM sc ORDER BY score DESC,sid ASC;
limit
作用：分頁(yè)
用法：limit n,m
n表示指針的偏移量
m表示每頁(yè)顯示幾條數(shù)據(jù)
分頁(yè)顯示公式：n=（n-1)*m
例：第2頁(yè)，每頁(yè)顯示5行
limit （2-1）*5,5
limit 5,5
-- 查詢學(xué)生的所有成績(jī)，以每頁(yè)5行進(jìn)行分頁(yè)，并只展示第二頁(yè)
SELECT * from sc LIMIT 5,5;
group by
作用：分組
having
作用：過(guò)濾數(shù)據(jù)
必須與group by連用
多表查詢
等值連接、非等值連接
-- 等值連接 錢(qián)二的語(yǔ)文成績(jī)是多少 79
SELECT student.sid,student.sname,course.cname,sc.score
FROM sc,student,course
WHERE sc.sid = student.sid AND sc.cid = course.cid AND student.sname='錢(qián)二' AND course.cname='語(yǔ)文';
-- 非等值連接 查詢錢(qián)二的成績(jī)?cè)?0~90之間的分?jǐn)?shù)及學(xué)科
select student.sid,student.sname,course.cname,sc.score
FROM sc,student,course
WHERE sc.sid = student.sid AND sc.cid = course.cid AND student.sname='錢(qián)二' AND sc.score>60 AND sc.score<90;
自連接、非自連接
自連接：其核心在于把一個(gè)表復(fù)制成兩個(gè)表，找到對(duì)應(yīng)字段后，進(jìn)行查詢
內(nèi)連接、外連接
六、函數(shù)
5.1、基本函數(shù)
下面列舉其中一些基本函數(shù)
5.1.1、字符串函數(shù)
| 函數(shù) | 用法 |
|---|---|
| concat(s1,s2,...sn) | 字符串拼接，將s1,s2,...sn拼接成一個(gè)字符串 |
| lower(str) | 將字符串str全部轉(zhuǎn)為小寫(xiě) |
| upper(str) | 將字符串str全部轉(zhuǎn)為大寫(xiě) |
| lpad(str,n,pad) | 左填充，用字符串pad對(duì)str左邊進(jìn)行填充，達(dá)到n個(gè)字符串長(zhǎng)度 |
| rpad(str,n,pad) | 右填充，用字符串pad對(duì)str右邊進(jìn)行填充，達(dá)到n個(gè)字符串長(zhǎng)度 |
| trim(str) | 去掉字符串頭部和尾部的空格 |
| substring(str,strat,len) | 返回字符串str從start位置起的len個(gè)長(zhǎng)度的字符串 |
5.1.2、數(shù)值函數(shù)
| 函數(shù) | 用法 |
|---|---|
| ABS(x) | 返回x的絕對(duì)值 |
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x除以y的余數(shù) |
| RAND() | 返回0~1的隨機(jī)值 |
| RAND(x) | 返回0~1的隨機(jī)值，相同的x值會(huì)產(chǎn)生相同的隨機(jī)數(shù) |
| ROUND(x) | 返回x四舍五入后的整數(shù) |
| ROUND(x,y) | 返回x四舍五入后的小數(shù)，小數(shù)點(diǎn)保留y位，y可為負(fù)數(shù)
|
-- RAND(x) 返回0~1的隨機(jī)值，相同的x值會(huì)產(chǎn)生相同的隨機(jī)數(shù)
select rand(1),rand(1),rand(2);
-- 生成6位驗(yàn)證碼
lpad(round(rand()*1000000,0),6,'0')
5.1.3、日期函數(shù)
| 函數(shù) | 用法 |
|---|---|
| curdate() | 返回當(dāng)前日期（年月日） |
| curtime() | 返回當(dāng)前時(shí)間（時(shí)分秒） |
| now() | 返回當(dāng)前日期和時(shí)間 |
| year('date') | 獲取指定date的年份（date格式：2023-6-19）
|
| month('date') | 獲取指定date的月份 |
| day('date') | 獲取指定date的日期 |
| date_add('date',interval expr type) | 返回一個(gè)日期/時(shí)間加上一個(gè)時(shí)間間隔expr后的時(shí)間值 |
| datediff('day1','dat2') | 返回起始時(shí)間date1和結(jié)束時(shí)間date2之間的天數(shù) |
-- year(date) 獲取當(dāng)前時(shí)間的年份
select year(now())
-- month(date) 獲取當(dāng)前時(shí)間的月份
select month(now())
-- day(date) 獲取當(dāng)前時(shí)間的日期（日）
select day(now())
-- date_add(date,interval expr type)
-- 當(dāng)前時(shí)間加上70天
select date_add(now(),interval 70 day)
-- datediff(day1,day2)
-- 前者減去后者
select datediff('2023-6-19','1996-8-7')
5.1.1、流程控制函數(shù)
| 函數(shù) | 用法 |
|---|---|
| if（values,a,b） | 如果values為真，返回a，否則返回b |
| ifnull（values1,values2） | 如果values為空(null)，返回values1，否則返回values2 |
| case when [val1] then [res1]...else [default] end | 如果val1為真，返回res1，...否則返回default默認(rèn)值 |
| case [erp1] when [val1] then [res1]...else [default] end | 如果erp1=val1，返回res1，...否則返回default默認(rèn)值 |
-- ifnull（values1,values2）
-- 如果一個(gè)值為空(null)時(shí),把它當(dāng)成0來(lái)計(jì)算
select ifnull(values,0);
-- case when [val1] then [res1]...else [default] end
-- 求某一個(gè)課程的及格率
SELECT sum(CASE WHEN cid =1 and score>60 THEN 1 ELSE 0 END)/sum(CASE WHEN cid =1 THEN 1 ELSE 0 END)
FROM sc
由于avg()、sum()、count()在計(jì)算時(shí)都不計(jì)算空(null)值,所以可以使用ifnull(values,0)轉(zhuǎn)為0進(jìn)行計(jì)算文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-697453.html
5.2、聚合函數(shù)
常用的幾個(gè)聚合函數(shù)文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-697453.html
| 名稱 | 作用 | |
|---|---|---|
| AVG（） | 求平均值 | 只適用于數(shù)值類型的字段/變量（不計(jì)算null值） |
| SUN（） | 求和 | 只適用于數(shù)值類型的字段/變量（不計(jì)算null值） |
| MAX（） | 求最大值 | 數(shù)值類型、字符串類型、日期類型 |
| MIN（） | 求最小值 | 數(shù)值類型、字符串類型、日期類型 |
| COUNT（） | 計(jì)算指定字段在查詢結(jié)果中的個(gè)數(shù) | （不計(jì)算null值） |
到了這里，關(guān)于數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
