《MySQL》MySQL教程
文章目錄
- 01 數(shù)據(jù)庫(kù)操作, 數(shù)據(jù)表操作
- 1. MySQL 服務(wù)管理
- 1.1 查看服務(wù)是否開(kāi)啟
- 1.2 MySQL 服務(wù)管理命令
- 2. 連接數(shù)據(jù)庫(kù)
- 2.1 使用命令行客戶端連接
- 使用 mysql 命令連接數(shù)據(jù)庫(kù)
- 2.2 使用 Navicat 連接
- 3. 數(shù)據(jù)庫(kù)操作介紹
- ４. 數(shù)據(jù)庫(kù)操作
- 4.1 創(chuàng)建數(shù)據(jù)庫(kù)
- 4.2 修改數(shù)據(jù)庫(kù)編碼
- 4.3 顯示所有數(shù)據(jù)庫(kù)
- 4.4 切換、使用數(shù)據(jù)庫(kù)
- 4.5 顯示當(dāng)前數(shù)據(jù)庫(kù)
- 4.6 刪除數(shù)據(jù)庫(kù)
- 5. 數(shù)據(jù)表操作
- 02 數(shù)據(jù)查詢(xún)
- 1. 導(dǎo)入數(shù)據(jù)
- 1.1 創(chuàng)建庫(kù)
- 1.2 選用庫(kù)
- 1.3 導(dǎo)入數(shù)據(jù)表
- 2. 單表查詢(xún)數(shù)據(jù)
- 2.1 查詢(xún)表中所有數(shù)據(jù)
- 2.2 查詢(xún)指定字段的顯示
- 2.3 as別名
- 2.4 消除重復(fù)數(shù)據(jù) (group by可以結(jié)合聚合函數(shù), 其功能更強(qiáng)大)
- 2.5 帶條件的查詢(xún)
- 2.5.1 比較運(yùn)算符
- 2.5.2 邏輯運(yùn)算符
- 2.5.3 模糊查詢(xún)
- 2.5.4 范圍查詢(xún)
- 2.5.5 空判斷
- 2.6 查詢(xún)結(jié)果排序
- 2.6.1 單字段排序
- 2.6.2 多字段排序
- 2.7 分頁(yè)查詢(xún)
- 2.8 聚合函數(shù)
- 2.9 分組
- 3. 多表查詢(xún)數(shù)據(jù)
- 3.1 普通多表查詢(xún)
- 3.2 多表查詢(xún)
- 3.3 表別名
- 3.4 內(nèi)鏈接查詢(xún)
- 3.5 左連接查詢(xún)
- 3.6 右連接查詢(xún)
- 3.7 標(biāo)量子查詢(xún)
- 3.8 列級(jí)子查詢(xún)
- 3.9 行級(jí)子查詢(xún)
- MySQL常用函數(shù)
- 時(shí)間和日期函數(shù)
- 數(shù)學(xué)函數(shù)
- 系統(tǒng)信息函數(shù)
- 其他函數(shù)
- sql時(shí)間
- MySQL日期數(shù)據(jù)類(lèi)型、時(shí)間類(lèi)型使用總結(jié)
- 一、MySQL 獲得當(dāng)前日期時(shí)間 函數(shù)
- 二、MySQL 日期時(shí)間 Extract（選取） 函數(shù)。
- 三、MySQL 日期時(shí)間計(jì)算函數(shù)
- 四、MySQL 日期轉(zhuǎn)換函數(shù)、時(shí)間轉(zhuǎn)換函數(shù)
- 五、MySQL 時(shí)間戳（Timestamp）函數(shù)
- 六、MySQL 時(shí)區(qū)（timezone）轉(zhuǎn)換函數(shù)
01 數(shù)據(jù)庫(kù)操作, 數(shù)據(jù)表操作
1. MySQL 服務(wù)管理
1.1 查看服務(wù)是否開(kāi)啟
ps aux | grep mysql1.2 MySQL 服務(wù)管理命令
啟動(dòng)服務(wù)
sudo service mysql start停止服務(wù)
sudo service mysql stop重新啟動(dòng)服務(wù)
sudo service mysql restart查看服務(wù)狀態(tài)
sudo service mysql status2. 連接數(shù)據(jù)庫(kù)
2.1 使用命令行客戶端連接
使用命令行客戶端連接, 連接數(shù)據(jù)庫(kù)需要知道主機(jī)，端口號(hào)，用戶名，密碼。。。
mysql -hlocalhost -p3306 -uroot -pmysql使用 mysql 命令連接數(shù)據(jù)庫(kù)
使用 mysql 命令連接數(shù)據(jù)庫(kù)，可以只指定用戶名和密碼，其它 使用默認(rèn)
mysql -u root -p2.2 使用 Navicat 連接
cd xxx/navicat112_mysql_cs_x64/ ./start_navicat3. 數(shù)據(jù)庫(kù)操作介紹
在數(shù)據(jù)庫(kù)操作中，基本操作都是圍繞增刪改查來(lái)操作。簡(jiǎn)稱(chēng)CRUD
| C | Create | 創(chuàng)建 |
| R | Read/Retrieve | 查詢(xún) |
| U | Update | 修改 |
| D | Delete | 刪除 |
４. 數(shù)據(jù)庫(kù)操作
4.1 創(chuàng)建數(shù)據(jù)庫(kù)
create database testdb; create database testdb2 character set utf8; show create database testdb;4.2 修改數(shù)據(jù)庫(kù)編碼
alter database testdb charset=utf8;4.3 顯示所有數(shù)據(jù)庫(kù)
show databases;4.4 切換、使用數(shù)據(jù)庫(kù)
use 數(shù)據(jù)庫(kù)名
use testdb;4.5 顯示當(dāng)前數(shù)據(jù)庫(kù)
select database();4.6 刪除數(shù)據(jù)庫(kù)
drop database 數(shù)據(jù)庫(kù)名
drop database testdb2;5. 數(shù)據(jù)表操作
查看表
show tables;創(chuàng)建表
create table stu(sid int,sname char(20),sage int);顯示創(chuàng)建表信息
show create table stu;增加字段
alter table stu add gender char(4);刪除字段
alter table stu drop gender;修改字段的數(shù)據(jù)類(lèi)型
alter table stu modify sname varchar(20);修改列的數(shù)據(jù)類(lèi)型并且改名
alter table stu change sid snumber smallint;2.4.3 MySQL 常用字段類(lèi)型
整數(shù)類(lèi)型
小數(shù)類(lèi)型
字符串類(lèi)型
枚舉類(lèi)型 enum(枚舉值1,枚舉值2,…)
enum('男','女')2.4.4 查詢(xún)數(shù)據(jù)
select * from stu;
2.4.5 插入數(shù)據(jù)
插入所有字段數(shù)據(jù)
insert into tStudent values(1,‘tom’,20)
插入指定字段
insert into stu(sage,sname) values(20,‘jack’);
插入多條數(shù)據(jù)
insert into stu values(2,‘rose’,20),(3,‘tony’,22);
更新所有的數(shù)據(jù)
update stu set sAge=25;
更新滿足條件的數(shù)據(jù)
update stu set sname=‘a(chǎn)lice’ where name=‘tony’;
2.4.7 刪除數(shù)據(jù)
刪除全部數(shù)據(jù) truncate
truncate stu;不需要加條件，也不能加條件，刪除全部數(shù)據(jù)，重置自動(dòng)編號(hào)到默認(rèn)值，沒(méi)有事務(wù)，速度快。
delete from stu;相當(dāng)于 truncate stu,但是delete操作有事務(wù)操作，所以速度慢，而且不會(huì)重置自動(dòng)編號(hào)
刪除滿足條件的數(shù)據(jù)
delete from stu where snumber = 1;
2.4.8 添加約束
添加約束的作用
約束是一種限制，它通過(guò)對(duì)表的行或列的數(shù)據(jù)做出限制，來(lái)確保表的數(shù)據(jù)的準(zhǔn)確性，
完整性、唯一性，可靠性、聯(lián)動(dòng)性
沒(méi)有對(duì)字段設(shè)置約束時(shí)的問(wèn)題
數(shù)據(jù)冗余
失去數(shù)據(jù)完整性
數(shù)據(jù)缺少唯一標(biāo)識(shí)
失去實(shí)體完整性
失去域完整性
失去引用完整性
數(shù)據(jù)庫(kù)常用約束
主鍵約束
create table tpk(id int primary key , name char(10));
自動(dòng)增長(zhǎng)
create table tai(id int auto_increment primary key,name varchar(10));
唯一性約束
create table tuni(id int unique,name char(10)）；
非空約束
create table tnn(id int,name char(10) not null);
默認(rèn)約束
create table tdt(id int,name char(10) default ‘NoName’);
外鍵約束
– 表1
create table fClass(id int primary key,name char(10));
– 表2
create table fStudent(id int primary key auto_increment,
name char(10), cid int, foreign key(cid) references fClass(id));
設(shè)置外鍵約束字段所關(guān)聯(lián)的字段，必須是主鍵約束字段。想要?jiǎng)h除有設(shè)置外鍵的表，必須先刪除外鍵所關(guān)聯(lián)的表。檢查約束 (在MySQL中無(wú)效，不起作用)
create table tchk(id int,age int check(age > 0 and age < 150),gender char(10) check(‘boy’ or ‘girl’));
2.4.9 增加刪除約束
添加主鍵約束
alter table tpk add constraint PK_id primary key(id);
刪除主鍵約束
alter table tpk drop primary key;
添加外鍵約束
alter table tfk add constraint FK_id foreign key (id) references tpk(id);
刪除外鍵約束
alter table tfk drop foreign key FK_id;
2.4.10 數(shù)據(jù)庫(kù)導(dǎo)入導(dǎo)出
作用：使用數(shù)據(jù)庫(kù)和導(dǎo)入導(dǎo)出功能，可以對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份，遷移等操作。
導(dǎo)出數(shù)據(jù)庫(kù)
導(dǎo)出整個(gè)數(shù)據(jù)庫(kù)
mysqldump -uroot -p School > school_bak.sql
導(dǎo)出庫(kù)當(dāng)中的指定表
mysqldump -uroot -p School tpk > tpk.sql
mysqldump -uroot -p School tpk tfk > tpktfk.sql
導(dǎo)入數(shù)據(jù)庫(kù)
導(dǎo)入數(shù)據(jù)庫(kù)前需要先創(chuàng)建一個(gè)空數(shù)據(jù)庫(kù), 如sch
mysql -uroot -p sch < school_bak.sql
2.4.11 數(shù)據(jù)表設(shè)計(jì)思想
需求分析：根據(jù)用戶的需求，分析出需要記錄的數(shù)據(jù)
需求設(shè)計(jì)：根據(jù)分析出的數(shù)據(jù)，設(shè)計(jì)E-R模型圖 (Entity-Relationship,實(shí)體關(guān)系圖)
詳細(xì)設(shè)計(jì)：將E-R模型圖轉(zhuǎn)換成數(shù)據(jù)表
三大范式：使用數(shù)據(jù)庫(kù)三大范式的設(shè)計(jì)思想對(duì)數(shù)據(jù)表進(jìn)行審核
第一范式 : 每一列都具有原子性，也就是不能再分割
第二范式 : 每個(gè)表只描述一件事情
第三范式 : 表中不能存在冗余字段
2.4.12 數(shù)據(jù)表練習(xí)
<創(chuàng)建數(shù)據(jù)庫(kù) School>
create database School;
<創(chuàng)建學(xué)生表 tStudent>
tStudent學(xué)生表結(jié)構(gòu)：
– sId – 學(xué)生編號(hào) – sName – 姓名 – sAge – 年齡 – sGender – 性別 – sAddress-- 地址 –
sPhone – 電話 – sBirthday – 生日 – sCardId – 身份證號(hào) – sClassId – 班級(jí)Id
答案:
create table tStudent(sId int,sName char(20),sAge tinyint,sGender enum(‘boy’,‘girl’),
sAddress varchar(50),sPhone char(11),sBirthday year,sCardId char(18),sClassid int);
<創(chuàng)建成績(jī)表 tScore>
TblScore成績(jī)表結(jié)構(gòu)：
– sId（成績(jī)id,主鍵,自動(dòng)編號(hào)） – sStuId（學(xué)生編號(hào)） – sEnglish（英語(yǔ)成績(jī)） – sMath（數(shù)學(xué)成績(jī)）
答案:
create table tScore(sid int unsigned auto_increment primary key, sStuId int, sEnglish float(5,2), smath float(5,2) );
<創(chuàng)建班級(jí)表 tClass>
tClass班級(jí)表結(jié)構(gòu)：
– cId –班級(jí)ID – cName –班級(jí)名稱(chēng) – cDescription –班級(jí)描述 答案：
答案:
create table tClass(cId int,cName char(10),cDescription varchar(100));
<創(chuàng)建老師表tTeacher>
tTeacher老師表：
– tTId – tTName – tTGender – tTAge – tTSalary – tTBirthday
答案：
create table tTeacher(tId int, tName char(20),tAge tinyint,tGender enum(‘boy’,‘girl’),tSalary float(10,2),tBirthday year);
02 數(shù)據(jù)查詢(xún)
1. 導(dǎo)入數(shù)據(jù)
安裝并開(kāi)啟MySQL數(shù)據(jù)庫(kù)服務(wù)
連接到數(shù)據(jù)庫(kù)
1.1 創(chuàng)建庫(kù)
create database db charset utf8;1.2 選用庫(kù)
use db;1.3 導(dǎo)入數(shù)據(jù)表
source /home/python/Desktop/school.sql2. 單表查詢(xún)數(shù)據(jù)
2.1 查詢(xún)表中所有數(shù)據(jù)
select * from t_students;2.2 查詢(xún)指定字段的顯示
select c_id,c_name,c_adress from t_student;2.3 as別名
select c_id as 學(xué)號(hào),c_name as 姓名,c_adress 住址 from t_student;2.4 消除重復(fù)數(shù)據(jù) (group by可以結(jié)合聚合函數(shù), 其功能更強(qiáng)大)
select distinct c_adress from t_student;select distinct c_name,c_adress from t_student;2.5 帶條件的查詢(xún)
select * from t_student where c_gender="男";2.5.1 比較運(yùn)算符
select * from t_student where c_age<20;2.5.2 邏輯運(yùn)算符
select * from t_student where c_age<20 and c_gender="女";2.5.3 模糊查詢(xún)
select * from t_student where c_name like "孫";select * from t_student where c_name like "孫%";select * from t_student where c_name like "孫_";2.5.4 范圍查詢(xún)
select * from t_student where id in(1,3,8);select * from t_student where id between 2 and 5;2.5.5 空判斷
select * from t_student where c_age is null;select * fron t_student where c_age is not null;2.6 查詢(xún)結(jié)果排序
2.6.1 單字段排序
select * from t_student order by c_age;select * from t_student order by c_age asc;2.6.2 多字段排序
select * from t_student order by c_age desc,c_id asc,c_gender;2.7 分頁(yè)查詢(xún)
select * from t_student limit 3;select * from t_student limit 2,3;2.8 聚合函數(shù)
select sum(c_age) from t_student;select avg(c_age) from t_student;select max(c_age) from t_student where c_gender="男";select min(c_age) from t_student where c_gender="女";select count(*) from t_student;select dount(*) from t_student where c_gender="女";2.9 分組
-
單字段分組
select c_gender from t_student group by c_gender; -
多字段分組
select c_gender,c_adress from t_student group by c_gender,c_adress; -
group_concat()
select c_gender,group_concat(c_name) from t_student group by c_gender; -
分組和內(nèi)聚函數(shù)使用
select c_gender,max(c_age),nin(c_age),sum(c_age),avg(c_age),count(c_age) from t_student group by c_gender;delect c_gender,max(c_age),min(c_age),sum(c_age),avg(c_age),count(c_age) from t_student group by c_gender; -
having條件子句
having作用和where類(lèi)似, 用來(lái)對(duì)分組數(shù)據(jù)進(jìn)行篩選
select c_gender,group_concat(c_name) from t_student where c_age>50 group by c_gender having c_gender="女";select c_gender,group_concat(c_name) from t_student where c_age>50 group by c_gender having c_gender="女";
where是對(duì) “from 表” 中數(shù)據(jù)進(jìn)行篩選
heving是對(duì) “group by” 分組后的數(shù)據(jù)包進(jìn)行篩選
因?yàn)樵趫?zhí)行順序上, 在執(zhí)行where時(shí), 分組還沒(méi)有執(zhí)行
得先根據(jù)where的條件取出數(shù)據(jù), 才能對(duì)取出的數(shù)據(jù)進(jìn)行分組. -
分組匯總(了解即可)
作用: 會(huì)在分組下方, 加一行, 顯示匯總
select c_gender from t_student group by c_gender with rollup;select c_gender,count(*) from t_student group by c_gender with rollup;
語(yǔ)法: with rollup
3. 多表查詢(xún)數(shù)據(jù)
3.1 普通多表查詢(xún)
select * from t_student,t_class; (無(wú)意義)3.2 多表查詢(xún)
select * from t_student,t_class where t_student.c_class_id = t_class.c_id;select t_student.name,t_student.c_name from t_student.c_3.3 表別名
select ts.c_name as "姓名", tc.c_name "班級(jí)名" from t_student as ts,t_class tc where ts.c_class_id = tc.c_id;3.4 內(nèi)鏈接查詢(xún)
select ts.c_name,tc.c_name from t_student as ts inner join t_class tc on ts.c_class_id = tc.c_id;3.5 左連接查詢(xún)
select ts.c_name,tc.c_name from t_student as ts left join t_class tc on ts.c_class_id = tc.c_id;3.6 右連接查詢(xún)
select ts.c_name,tc.c_name from t_student as ts right jion t_class tc on ts.c_class_id = tc.c_id;3.7 標(biāo)量子查詢(xún)
select * from t_srudent where c_age > (select avg(c_age) from t_stuent);3.8 列級(jí)子查詢(xún)
select * from t_student where c_id in (select c_class_id from t_student);3.9 行級(jí)子查詢(xún)
select * from t_student where(c_age,c_calss_id) = (select max(c_age),min(c_class_id) from t_student);MySQL常用函數(shù)
時(shí)間和日期函數(shù)
CURDATE 和 CURRENT_DATE：返回當(dāng)前系統(tǒng)的日期值 CURTIME 和 CURRENT_TIME：返回當(dāng)前系統(tǒng)的時(shí)間值 NOW 和 SYSDATE：返回當(dāng)前系統(tǒng)的日期和時(shí)間值 UNIX_TIMESTAMP：獲取UNIX時(shí)間戳函數(shù) FROM_UNIXTIME：將UNIX時(shí)間戳轉(zhuǎn)換為時(shí)間格式 MONTH：獲取指定日期中的月份 MONTHNAME：獲取指定日期中的月份英文名稱(chēng) DAYNAME：獲取指定日期對(duì)應(yīng)的星期幾的英文名稱(chēng) DAYOFWEEK：獲取指定日期對(duì)應(yīng)的一周的索引位置值 WEEK：獲取指定日期是一年中的第幾周 DAYOFYEAR：獲取指定日期是一年中的第幾天 DAYOFMONTH：獲取指定日期是一個(gè)月中的第幾天 YEAR：獲取年份，返回值范圍是1970~2069 TIME_TO_SEC：將時(shí)間參數(shù)轉(zhuǎn)換為秒數(shù) DATE_ADD 和 ADDDATE：都是向日期添加指定的時(shí)間間隔 DATE_SUB 和 SUBDATE：都是向日期減去指定的時(shí)間間隔 ADDTIME：時(shí)間加法運(yùn)算，在原始時(shí)間上添加指定的時(shí)間 SUBTIME：時(shí)間減法運(yùn)算，在原始時(shí)間上減去指定的時(shí)間 DATEDIFF：獲取連個(gè)日期之間間隔 WEEKDAY：獲取指定日期在一周內(nèi)的對(duì)應(yīng)的工作日索引數(shù)學(xué)函數(shù)
ABS(x)：返回x的絕對(duì)值 SIGN(x)：返回x的符號(hào) PI()：返回圓周率π，默認(rèn)顯示6位小數(shù) SQRT(x)：返回非負(fù)數(shù)的x的二次方根 MOD(x,y)：返回x被y除后的余數(shù) CEIL(x)、CEILING(x)：返回不小于x的最小整數(shù) FLOOR(x)：返回不大于x的最大整數(shù) EXP(x)：返回e的x乘方后的值 LOG(x)：返回x的自然對(duì)數(shù)，x相對(duì)于基數(shù)e的的對(duì)數(shù) LOG10(x)：返回x的基數(shù)為10的對(duì)數(shù) RADIANS(x)：返回x由角度轉(zhuǎn)化為弧度的值 CHAR_LENGTH(str)：計(jì)算字符串字符個(gè)數(shù) CONCAT(s1,s2,...)：返回連接參數(shù)產(chǎn)生的字符串 CONCAT_WS(x,s1,s2,...)：返回多個(gè)字符串拼接之后的字符串 LEFT(s,n)：返回字符串s從最左邊開(kāi)始的n個(gè)字符 RIGHT(s,n)：返回字符串s從最右邊開(kāi)始的n個(gè)字符 TRIM(s)：返回字符串s刪除了兩邊空格之后的字符串 TRIM(s1 FROM s)：刪除字符串s兩端所有子字符串s1 REPEAT(s,n)：返回一個(gè)由重復(fù)字符串s組成的字符串 SPACE(n)：返回一個(gè)由n個(gè)空格組成的字符串 REPLACE(s,s1,s2)：返回一個(gè)字符串，用字符串s2代替字符串s中的所有字符串s1 SUBSTRING(s,n,len)、MID(s,n,len)：從字符串s中返回一個(gè)，第n個(gè)字符串開(kāi)始、長(zhǎng)度為len的字符串 REVERSE(s)：將字符串s反轉(zhuǎn) ELT(n,str1,str2,str3,str4,...)：返回第n個(gè)字符串系統(tǒng)信息函數(shù)
VERSION()：查看MySQL版本號(hào) CONNECTION_ID()：查看當(dāng)前用戶的連接數(shù) USER()\CURRENT_USER()、SYSTEM_USER()、SESSION_USER()：查看當(dāng)前被MySQL服務(wù)器驗(yàn)證的用戶名和主機(jī)的組合 CHARSET(str)：查看字符串str使用的字符集 COLLATION()：查看字符串排列方式其他函數(shù)
FORMAT(x,n)：將數(shù)字x格式化 CONV(n,from_base,to_base)：不同進(jìn)制數(shù)之間的轉(zhuǎn)換 INET_ATON(expr)：給出一個(gè)作為字符串的網(wǎng)絡(luò)地址的點(diǎn)地址 BENCHMARK(count,expr)：重復(fù)執(zhí)行count次表達(dá)式，expr用它在MySQL客戶端內(nèi)部報(bào)告語(yǔ)句執(zhí)行的時(shí)間 CONVERT(str USING charset)：是哦那個(gè)字符集charset表示字符串strsql時(shí)間
MySQL日期數(shù)據(jù)類(lèi)型、時(shí)間類(lèi)型使用總結(jié)
MySQL 日期類(lèi)型：日期格式、所占存儲(chǔ)空間、日期范圍 比較。
日期類(lèi)型 存儲(chǔ)空間 日期格式 日期范圍 ------------ --------- --------------------- ----------------------------------------- datetime 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 timestamp 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038 date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31 year 1 bytes YYYY 1901 ~ 2155在 MySQL 中創(chuàng)建表時(shí)，對(duì)照上面的表格，很容易就能選擇到合適自己的數(shù)據(jù)類(lèi)型。不過(guò)到底是選擇 datetime 還是 timestamp，可能會(huì)有點(diǎn)犯難。這兩個(gè)日期時(shí)間類(lèi)型各有優(yōu)點(diǎn)：datetime 的日期范圍比較大；timestamp 所占存儲(chǔ)空間比較小，只是 datetime 的一半。
另外，timestamp 類(lèi)型的列還有個(gè)特性：默認(rèn)情況下，在 insert, update 數(shù)據(jù)時(shí)，timestamp 列會(huì)自動(dòng)以當(dāng)前時(shí)間（CURRENT_TIMESTAMP）填充/更新。“自動(dòng)”的意思就是，你不去管它，MySQL 會(huì)替你去處理。
建表的代碼為:
create table t8 (`id1` timestamp NOT NULL default CURRENT_TIMESTAMP,`id2` datetime default NULL );一般情況下，我傾向于使用 datetime 日期類(lèi)型。
兩者之間的比較:
2.timestamp比較受時(shí)區(qū)timezone的影響以及MYSQL版本和服務(wù)器的SQL MODE的影響.
MySQL 時(shí)間類(lèi)型：時(shí)間格式、所占存儲(chǔ)空間、時(shí)間范圍。
時(shí)間類(lèi)型 存儲(chǔ)空間 時(shí)間格式 時(shí)間范圍 ------------ --------- --------------------- ----------------------------------------- time 3 bytes HH:MM:SS -838:59:59 ~ 838:59:59time 時(shí)間范圍居然有這么大的范圍，特別是 time 可以取負(fù)值，有點(diǎn)奇怪。后來(lái)，看了 MySQL 手冊(cè)才知道這是為了滿足兩個(gè)日期時(shí)間相減才這樣設(shè)計(jì)的。
select timediff('2000:01:31 23:59:59', '2000:01:01 00:00:00'); -- 743:59:59 select timediff('2000:01:01 00:00:00', '2000:01:31 23:59:59'); -- -743:59:59 select timediff('23:59:59', '12:00:00'); -- 11:59:59注意，timediff 的兩個(gè)參數(shù)只能是 datetime/timestamp, time 類(lèi)型的，并且這兩個(gè)參數(shù)類(lèi)型要相同。即：datetime/timestamp 和 datetime/timestamp 比較；time 和 time 相比較。
雖然 MySQL 中的日期時(shí)間類(lèi)型比較豐富，但遺憾的是，目前（2008-08-08）這些日期時(shí)間類(lèi)型只能支持到秒級(jí)別，不支持毫秒、微秒。也沒(méi)有產(chǎn)生毫秒的函數(shù)。
《MySQL：MySQL日期數(shù)據(jù)類(lèi)型、MySQL時(shí)間類(lèi)型使用總結(jié)》適用于 MySQL 5.X 及以上版本。
一、MySQL 獲得當(dāng)前日期時(shí)間 函數(shù)
1.1 獲得當(dāng)前日期+時(shí)間（date + time）函數(shù)：now()
mysql> select now();+---------------------+ | now() | +---------------------+ | 2008-08-08 22:20:46 | +---------------------+除了 now() 函數(shù)能獲得當(dāng)前的日期時(shí)間外，MySQL 中還有下面的函數(shù)：
current_timestamp() ,current_timestamp ,localtime() ,localtime ,localtimestamp -- (v4.0.6) ,localtimestamp() -- (v4.0.6)這些日期時(shí)間函數(shù)，都等同于 now()。鑒于 now() 函數(shù)簡(jiǎn)短易記，建議總是使用 now() 來(lái)替代上面列出的函數(shù)。
1.2 獲得當(dāng)前日期+時(shí)間（date + time）函數(shù)：sysdate()
sysdate() 日期時(shí)間函數(shù)跟 now() 類(lèi)似，不同之處在于：now() 在執(zhí)行開(kāi)始時(shí)值就得到了， sysdate() 在函數(shù)執(zhí)行時(shí)動(dòng)態(tài)得到值。看下面的例子就明白了：
mysql> select now(), sleep(3), now();+---------------------+----------+---------------------+ | now() | sleep(3) | now() | +---------------------+----------+---------------------+ | 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 | +---------------------+----------+---------------------+mysql> select sysdate(), sleep(3), sysdate();+---------------------+----------+---------------------+ | sysdate() | sleep(3) | sysdate() | +---------------------+----------+---------------------+ | 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 | +---------------------+----------+---------------------+可以看到，雖然中途 sleep 3 秒，但 now() 函數(shù)兩次的時(shí)間值是相同的； sysdate() 函數(shù)兩次得到的時(shí)間值相差 3 秒。MySQL Manual 中是這樣描述 sysdate() 的：Return the time at which the function executes。
sysdate() 日期時(shí)間函數(shù)，一般情況下很少用到。
其中，下面的兩個(gè)日期函數(shù)等同于 curdate()：
current_date() ,current_date其中，下面的兩個(gè)時(shí)間函數(shù)等同于 curtime()：
current_time() ,current_time因?yàn)槲覈?guó)位于東八時(shí)區(qū)，所以本地時(shí)間 = UTC 時(shí)間 + 8 小時(shí)。UTC 時(shí)間在業(yè)務(wù)涉及多個(gè)國(guó)家和地區(qū)的時(shí)候，非常有用。
二、MySQL 日期時(shí)間 Extract（選取） 函數(shù)。
MySQL Extract() 函數(shù)除了沒(méi)有date(),time() 的功能外，其他功能一應(yīng)具全。并且還具有選取‘day_microsecond’ 等功能。注意這里不是只選取 day 和 microsecond，而是從日期的 day 部分一直選取到 microsecond 部分。夠強(qiáng)悍的吧！
MySQL Extract() 函數(shù)唯一不好的地方在于：你需要多敲幾次鍵盤(pán)。
分別返回日期參數(shù)，在一周、一月、一年中的位置。
set @dt = '2008-08-08';select dayofweek(@dt); -- 6 select dayofmonth(@dt); -- 8 select dayofyear(@dt); -- 221日期 ‘2008-08-08’ 是一周中的第 6 天（1 = Sunday, 2 = Monday, …, 7 = Saturday）；一月中的第 8 天；一年中的第 221 天。
MySQL week() 函數(shù)，可以有兩個(gè)參數(shù)，具體可看手冊(cè)。 weekofyear() 和 week() 一樣，都是計(jì)算“某天”是位于一年中的第幾周。 weekofyear(@dt) 等價(jià)于 week(@dt,3)。
MySQL weekday() 函數(shù)和 dayofweek() 類(lèi)似，都是返回“某天”在一周中的位置。不同點(diǎn)在于參考的標(biāo)準(zhǔn)， weekday：(0 = Monday, 1 = Tuesday, …, 6 = Sunday)； dayofweek：（1 = Sunday, 2 = Monday, …, 7 = Saturday）
MySQL yearweek() 函數(shù)，返回 year(2008) + week 位置(31)。
思考，如何返回中文的名稱(chēng)呢？
MySQL last_day() 函數(shù)非常有用，比如我想得到當(dāng)前月份中有多少天，可以這樣來(lái)計(jì)算：
mysql> select now(), day(last_day(now())) as days;+---------------------+------+ | now() | days | +---------------------+------+ | 2008-08-09 11:45:45 | 31 | +---------------------+------+三、MySQL 日期時(shí)間計(jì)算函數(shù)
MySQL adddate(), addtime()函數(shù)，可以用 date_add() 來(lái)替代。下面是 date_add() 實(shí)現(xiàn) addtime() 功能示例：
mysql> set @dt = '2008-08-09 12:12:33';mysql> mysql> select date_add(@dt, interval '01:15:30' hour_second);+------------------------------------------------+ | date_add(@dt, interval '01:15:30' hour_second) | +------------------------------------------------+ | 2008-08-09 13:28:03 | +------------------------------------------------+mysql> select date_add(@dt, interval '1 01:15:30' day_second);+-------------------------------------------------+ | date_add(@dt, interval '1 01:15:30' day_second) | +-------------------------------------------------+ | 2008-08-10 13:28:03 | +-------------------------------------------------+date_add() 函數(shù)，分別為 @dt 增加了“1小時(shí) 15分 30秒” 和 “1天 1小時(shí) 15分 30秒”。建議：總是使用 date_add() 日期時(shí)間函數(shù)來(lái)替代 adddate(), addtime()。
MySQL 為日期減去一個(gè)時(shí)間間隔：date_sub()
mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);+----------------------------------------------------------------+ | date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) | +----------------------------------------------------------------+ | 1997-12-30 22:58:59 | +----------------------------------------------------------------+MySQL date_sub() 日期時(shí)間函數(shù) 和 date_add() 用法一致，不再贅述。另外，MySQL 中還有兩個(gè)函數(shù) subdate(), subtime()，建議，用 date_sub() 來(lái)替代。
函數(shù)參數(shù)“P” 的格式為“YYYYMM” 或者 “YYMM”，第二個(gè)參數(shù)“N” 表示增加或減去 N month（月）。
MySQL period_add(P,N)：日期加/減去N月。
mysql> select period_add(200808,2), period_add(20080808,-2)+----------------------+-------------------------+ | period_add(200808,2) | period_add(20080808,-2) | +----------------------+-------------------------+ | 200810 | 20080806 | +----------------------+-------------------------+MySQL period_diff(P1,P2)：日期 P1-P2，返回 N 個(gè)月。
mysql> select period_diff(200808, 200801);+-----------------------------+ | period_diff(200808, 200801) | +-----------------------------+ | 7 | +-----------------------------+在 MySQL 中，這兩個(gè)日期函數(shù)，一般情況下很少用到。
MySQL 日期、時(shí)間相減函數(shù)：datediff(date1,date2), timediff(time1,time2)
MySQL datediff(date1,date2)：兩個(gè)日期相減 date1 - date2，返回天數(shù)。
MySQL timediff(time1,time2)：兩個(gè)日期相減 time1 - time2，返回 time 差值。
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08 select timediff('08:08:08', '00:00:00'); -- 08:08:08注意：timediff(time1,time2) 函數(shù)的兩個(gè)參數(shù)類(lèi)型必須相同。
四、MySQL 日期轉(zhuǎn)換函數(shù)、時(shí)間轉(zhuǎn)換函數(shù)
可以看到，str_to_date(str,format) 轉(zhuǎn)換函數(shù)，可以把一些雜亂無(wú)章的字符串轉(zhuǎn)換為日期格式。另外，它也可以轉(zhuǎn)換為時(shí)間。“format” 可以參看 MySQL 手冊(cè)。
MySQL 日期、時(shí)間轉(zhuǎn)換函數(shù)：date_format(date,format), time_format(time,format) 能夠把一個(gè)日期/時(shí)間轉(zhuǎn)換成各種各樣的字符串格式。它是 str_to_date(str,format) 函數(shù)的 一個(gè)逆轉(zhuǎn)換。
MySQL get_format() 語(yǔ)法：
get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'MySQL get_format() 用法的全部示例：
select get_format(date,'usa') ; -- '%m.%d.%Y' select get_format(date,'jis') ; -- '%Y-%m-%d' select get_format(date,'iso') ; -- '%Y-%m-%d' select get_format(date,'eur') ; -- '%d.%m.%Y' select get_format(date,'internal') ; -- '%Y%m%d' select get_format(datetime,'usa') ; -- '%Y-%m-%d %H.%i.%s' select get_format(datetime,'jis') ; -- '%Y-%m-%d %H:%i:%s' select get_format(datetime,'iso') ; -- '%Y-%m-%d %H:%i:%s' select get_format(datetime,'eur') ; -- '%Y-%m-%d %H.%i.%s' select get_format(datetime,'internal') ; -- '%Y%m%d%H%i%s' select get_format(time,'usa') ; -- '%h:%i:%s %p' select get_format(time,'jis') ; -- '%H:%i:%s' select get_format(time,'iso') ; -- '%H:%i:%s' select get_format(time,'eur') ; -- '%H.%i.%s' select get_format(time,'internal') ; -- '%H%i%s'MySQL get_format() 函數(shù)在實(shí)際中用到機(jī)會(huì)的比較少。
五、MySQL 時(shí)間戳（Timestamp）函數(shù)
下面是示例：
select unix_timestamp(); -- 1218290027 ===得到當(dāng)前時(shí)間的UNIX時(shí)間值將具體時(shí)間來(lái)轉(zhuǎn)換成timestamp
select unix_timestamp('2008-08-08'); -- 1218124800 select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800將timestamp來(lái)轉(zhuǎn)換成具體時(shí)間
select from_unixtime(1218290027); -- '2008-08-09 21:53:47' select from_unixtime(1218124800); -- '2008-08-08 00:00:00' select from_unixtime(1218169800); -- '2008-08-08 12:30:00'select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'請(qǐng)看示例部分：
select timestamp('2008-08-08'); -- 2008-08-08 00:00:00 select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01 select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00 select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00MySQL timestampadd() 函數(shù)類(lèi)似于 date_add()。
select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1 select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485 select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7MySQL timestampdiff() 函數(shù)就比 datediff() 功能強(qiáng)多了，datediff() 只能計(jì)算兩個(gè)日期（date）之間相差的天數(shù)。
六、MySQL 時(shí)區(qū)（timezone）轉(zhuǎn)換函數(shù)
convert_tz(dt,from_tz,to_tz)select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00時(shí)區(qū)轉(zhuǎn)換也可以通過(guò) date_add, date_sub, timestampadd 來(lái)實(shí)現(xiàn)。
select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00 select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00 select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00總結(jié)
以上是生活随笔為你收集整理的《MySQL》MySQL教程的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 地理信息系统基础知识
- 下一篇： decode解码 geohash_Geo