Mysql一些维护命令
---------------------------------------------------------
-------------------使用mysql客户端程序-------------------
---------------------------------------------------------
-------------------建立MySQL用户帐户-------------------
--登录mysql
mysql -h hostname -u username -p password;
mysql --host=localhost --user=root --password
--创建账户并授予数据库访问权限
grant all on dbname.* to 'username'@'hostname' identified by 'password';
--查看MySQL帮助
mysql --help
-------------------创建数据库表和样表-------------------
--创建数据库
create database dbname;
--切换当前使用数据库
use dbname;
--创建表
create table limbs (thing varchar(20), legs int, arms int);
--插入数据
insert into limbs(thing, legs, arms) values('human', 2, 2);
insert into limbs(thing, legs, arms) values('insect', 6, 0);
insert into limbs(thing, legs, arms) values('squid', 0, 10);
insert into limbs(thing, legs, arms) values('octopus', 0, 8);
insert into limbs(thing, legs, arms) values('fish', 0, 0);
insert into limbs(thing, legs, arms) values('centipede', 100, 0);
insert into limbs(thing, legs, arms) values('table', 4, 0);
insert into limbs(thing, legs, arms) values('armchair', 4, 2);
insert into limbs(thing, legs, arms) values('phonograph', 0, 1);
insert into limbs(thing, legs, arms) values('tripod', 3, 0);
insert into limbs(thing, legs, arms) values('Peg Leg Pete', 1, 2);
insert into limbs(thing, legs, arms) values('space alien', null, null);
--查询数据
select * from limbs;
-------------------启动和停止MySQL-------------------
--备份数据库
mysqldump -h localhost -u username -p daname > dbname.sql
mysqldump -h localhost -u cbuser -p cookbook > cookbook.sql
mysqldump -h srchost -uusername -ppassword srcdbname | mysql -hdsthost -uusername -p dstdbname
mysqldump -h192.168.62.68 -uroot -p** text | mysql -h127.0.0.1 -uroot -p test
mysqldump -h192.168.71.137 -uroot -p** love | mysql -h127.0.0.1 -uroot -p love
--shutdown数据库服务器
mysqladmin -p -u root shutdown
--退出MySQL
quit;
exit;
--操作系统服务方式启动停止mysql(vista\win7管理员方式启动cmd)
net start mysql
net stop mysql
--操作系统服务方式启动停止mysql(linux管理员方式启动cmd)
service mysqld start
service mysqld stop
-------------------使用可选项文件来指定连接参数-------------------
--通用客户端程序连接选项
[client]
host=localhost
user=cbuser
password=cbpass
--mysql特定选项
[mysql]
skip-auto-rehash
--查看从选项文件读取哪些选项
mysql --print-defaults
--检查从选项文件读取了什么值
my_print_defaults client mysql
-------------------保护选项文件以阻止其他用户读取-------------------
chmod 60 .my.cnf
chmod go-rwx .my.cnf
-------------------发起sql语句-------------------
--分号；结束
select * from tables;
--\g结束
select * from table\g
-------------------取消一条部分输入的语句-------------------
--\c取消
select *
from tables
where fun \c
--/* */注释
select * /* this is comments*/
from tables
where fun \c
-------------------重复和编辑sql语句-------------------
up 向上翻动语句的历史
down 向下翻动语句的历史
left 行内左移
right 行内右移
backspace 删除前一个字符
-------------------自动完成数据库和表名-------------------
-------------------让mysql从文件中读取语句-----------------
--cmd从sql文件中读取
mysql dbname < filename
mysql cookbook < limbs.sql
--mysql会话内从sql文件读取
source filename
\. filename
source testscore.sql
\. testscore.sql
--测试.sql文件中调用脚本文件可嵌套层数
create table counter (depth int);
insert into counter set depth = 0;
--loop.sql文件内容
update counter set depth = depth + 1;
select depth from counter;
source loop.sql;
--cmd调用loop.sql文件
mysql cookbook < loop.sql
--mysql会话调用loop.sql文件
source loop.sql
\. loop.sql
--cmd查看系统错误号描述
perror 24
-------------------让mysql从其他程序读取语句-----------------
--cmd从pipe（管道）读取语句
type filename | mysql dbname /*cmd*/
cat filename | mysql dbname /*shell*/
type limbs.sql | mysql cookbook
cat limbs.sql | mysql cookbook
--结合mysqldump和mysql拷贝一个数据库到另外一个mysql服务器
mysqldump dbname | mysql -h some.other.host.com other_dbname
mysqldump cookbook | mysql -h other.host cookbook
--使用程序生成sql语句发送到mysql
generate-test-data | mysql cookbook
-------------------一行输入所有sql-----------------
--cmd一行输入sql
mysql -e "statement1" dbname -u username -p password
mysql -e "select count(*) from limbs" cookbook -uroot -p password
--cmd一行输入多个sql
mysql -e "statement1; statement2" dbname -u username -p password
mysql -e "select count(*) from limbs;,select now()" cookbook -uroot -p password
-------------------预防查询输出超出屏幕范围-----------------
--为mysql设置分页显示程序
mysql --pager=/usr/bin/less
mysql --pager /*已经设置环境变量pager*/
--mysql命令行开启关闭分页功能
\p /*开启分页功能*/
\p /usr/bin/less
\n /*关闭分页功能*/
-------------------发送查询输出到文件或程序-----------------
--交互模式下表格显示结果集数据
select * from limbs
--非交互模式下制表符显示结果集数据
echo select * from limbs | mysql cookbook
--将mysql输出保存到文件
mysql dbname > outputfile
statement1;
statement2;
exit
mysql cookbook > d:\result.txt
select * from limbs;
select * from testscore;
exit
--从指定文件读入sql，将结果保存到指定文件
mysql dbname < inputfile > outfile
mysql cookbook < test.txt > d:\result.txt
--从指定文件读入sql，将结果发送到另一程序
mysql dbname < inputfile | mail paul
mysql dbname < test.txt | more
mysql cookbook < test.txt | find "alien" /*cmd*/
mysql cookbook < test.txt | grep "alien" /*linux*/
-------------------选择表格或制表符定界的查询输出格式-----------------
mysql -t dbname < inputfile
mysql -t cookbook < test.txt
mysql -t dbname < inputfile | program
mysql -t cookbook < test.txt | find "a"
-------------------指定任意的输出列分隔符-----------------
mysql cookbook < test.sql | sed -e "s/TAB/:/g" > result.txt
mysql cookbook < test.sql | tr " TAB" ":" > result.txt
mysql cookbook < test.sql | tr "\011" ":" > result.txt
-------------------生成xml或html-----------------
mysql -H -e "statement" dbname > outfile
mysql -X -e "statement" dbname > outfile
mysql -H -e "select * from limbs" cookbook > demohtml.txt
mysql -X -e "select * from limbs" cookbook > demoxml.txt
-------------------使长输出行更具有可读性-----------------
--mysql命令行垂直显示结果集
show full columns from tables;
show full columns from tables\g
show full columns from tables\G /*局部控制垂直显示方式*/
--cmd命令行垂直显示结果集
mysql -E /*全局控制垂直显示方式*/
-------------------控制mysql的繁冗级别-----------------
echo select now() | mysql /*cmd*/
echo select now() | mysql -v /*cmd*/
echo select now() | mysql -vv /*cmd*/
echo select now() | mysql -vvv /*cmd*/
echo "select now()" | mysql /*linux*/
echo "select now()" | mysql -v /*linux*/
echo "select now()" | mysql -vv /*linux*/
echo "select now()" | mysql -vvv /*linux*/
-------------------记录交互式的mysql会话-----------------
--cmd命令行记录交互日志到指定文件
mysql -tee=outfile dbname
mysql -tee=tmp.out cookbook
--\T开启交互日志功能、\t关闭日志交互功能
-------------------以之前执行的语句创建mysql脚本-----------------
--使用mysql执行语句历史文件.mysql_history(位于用户home目录下)
cd; cat .mysql_history
-------------------在sql语句中使用用户自定义的变量-----------------
--将select列值赋予变量
select @class_id := id from t_classes where id = 2;
delete from t_student where classesid = @class_id;
delete from t_classes where id = @class_id;
--将表达式至赋予变量
select @max_limbs := max(arms+legs) from limbs;
--将含有auto_increment列的表插入新行之后的last_insert_id()的结果赋给变量
select @last_id := last_insert_id();
--如果查询返回值含有多行，最后一行的值赋予变量
select @name := thing from limbs where legs = 0;
select @name;
--如果语句没有返回任何行，变量保持先前值，如果先前没有赋值，则为null
select @name2 := thing from limbs where legs < 0;
--使用set显式为某个变量指定特定值
set @sum = 4 + 7;
select @sum;
set @sum2 := 3 + 2;
select @sum2;
--使用set将一个select结果赋给变量
set @max_limbs = (select max(arms+legs) from limbs);
--变量名不区分大小写
set @x = 1, @X = 2;
select @x, @X;
-------------------为查询输出行计数-----------------
--linux命令行--skip-column-names结合cat -n
mysql --skip-column-names -e "select thing, arms from limbs" cookbook | cat -n
--mysql命令行使用自定义变量
set @n = 0;
select @n := @n + 1 as rownum, thing, arms, legs from limbs;
-------------------将mysql用作计算器-----------------
select (17 + 24) / sqrt(64);
select "ABC" = "abc";
select "ABC" = "abcd";
set @daily_room_charge = 100.00;
set @num_of_nights = 3;
set @tax_percent = 8;
set @total_room_charge = @daily_room_charge * @num_of_nights;
set @tax = (@total_room_charge * @tax_percent) / 100;
set @total = @total_room_charge + @tax;
select @total;
-------------------在unix下写shell脚本-----------------
--shell脚本查看mysql服务器正常运行时间
#!/bin/sh
# mysql_uptime.sh - report server uptime in seconds
mysql --skip-column-names -B -e "SHOW /*!50002 GLOBAL */ STATUS LIKE 'Uptime'"
#!/bin/sh
# mysql_uptime2.sh - report server uptime
mysql -e STATUS | grep "^Uptime"
#!/bin/sh
# mysql_uptime3.sh - report server uptime
echo STATUS | mysql | grep "^Uptime"
--mysql命令行查看mysql服务器正常运行时间
status
--win环境下安装类unix命令行环境
cygnus
uwin
---------------------------------------------------------
---------------------从表中查询数据----------------------
---------------------------------------------------------
--查询数据库版本、当前会话默认数据库名
select version(), database();
--创建表
CREATE TABLE mail
(
t DATETIME, # when message was sent
srcuser CHAR(8), # sender (source user and host)
srchost CHAR(20),
dstuser CHAR(8), # recipient (destination user and host)
dsthost CHAR(20),
size BIGINT, # message size in bytes
INDEX (t)
);
--插入数据
INSERT INTO mail (t,srchost,srcuser,dsthost,dstuser,size)
VALUES
('2006-05-11 10:15:08','saturn','barb','mars','tricia',58274),
('2006-05-12 12:48:13','mars','tricia','venus','gene',194925),
('2006-05-12 15:02:49','mars','phil','saturn','phil',1048),
('2006-05-13 13:59:18','saturn','barb','venus','tricia',271),
('2006-05-14 09:31:37','venus','gene','mars','barb',2291),
('2006-05-14 11:52:17','mars','phil','saturn','tricia',5781),
('2006-05-14 14:42:21','venus','barb','venus','barb',98151),
('2006-05-14 17:03:01','saturn','tricia','venus','phil',2394482),
('2006-05-15 07:17:48','mars','gene','saturn','gene',3824),
('2006-05-15 08:50:57','venus','phil','venus','phil',978),
('2006-05-15 10:25:52','mars','gene','saturn','tricia',998532),
('2006-05-15 17:35:31','saturn','gene','mars','gene',3856),
('2006-05-16 09:00:28','venus','gene','mars','barb',613),
('2006-05-16 23:04:19','venus','phil','venus','barb',10294),
('2006-05-17 12:49:23','mars','phil','saturn','tricia',873),
('2006-05-19 22:21:51','saturn','gene','venus','gene',23992)
;
----------------指定查询列/从指定列中查询-----------------
select * from mail;
select t, srchost, srcuser, dsthost, dstuser, size from mail;
select t, srcuser, srchost, size from mail;
------------------------指定查询行-------------------------
select t, srcuser, srchost from mail where srchost = "venus";
select t, srcuser, srchost from mail where srchost like 's%';
select * from mail where srcuser = 'barb' and dstuser = 'tricia';
---------------------格式化显示查询结果----------------------
select t, srcuser, size from mail;
select concat(monthname(t), ' ', dayofmonth(t), ', ', year(t)), srcuser, size from mail;
select date_format(t, '%M %e, %Y'), srcuser, size from mail;
select date_format(t, '%M %e, %Y') as 'Date of message',
srcuser as 'Message sendr',
size as 'Number of bytes'
from mail;
select '1+1+1' as 'The epression', 1+1+1 as 'The result';
select 1 as 'integer';
---------------------合并多列来构建复合值----------------------
select date_format(t, '%M %e, %Y') as date_sent,
concat(srcuser, '@', srchost) as sender,
concat(dstuser, '@', dsthost) as recipient,
size
from mail;
---------------------where表达式中的列别名----------------------
select t, srcuser, dstuser, size/1024 as kilobytes
from mail where kilobytes > 500;
select t, srcuser, dstuser, size/1024 as kilobytes
from mail where size/1024 > 500;
---------------------调试比较表达式----------------------
select * from mail where srcuser < 'c' and size > 500;
select srcuser, srcuser < 'c', size, size > 500 from mail;
select srcuser, srcuser < 'c', size, size > 500 from mail where srcuser < 'c' and size > 500;
---------------------使查询结果唯一化---------------------
select srcuser from mail;
select distinct srcuser from mail;
select distinct year(t), month(t), dayofmonth(t) from mail;
select count(distinct srcuser) from mail;
----------------------如何处理null值-----------------------
--创建表
CREATE TABLE taxpayer
(
name CHAR(20),
id CHAR(20)
);
--插入数据
INSERT INTO taxpayer (name,id) VALUES ('bernina','198-48');
INSERT INTO taxpayer (name,id) VALUES ('bertha',NULL);
INSERT INTO taxpayer (name,id) VALUES ('ben',NULL);
INSERT INTO taxpayer (name,id) VALUES ('bill','475-83');
select * from taxpayer;
select * from taxpayer where id = null;
select * from taxpayer where id != null;
select * from taxpayer where id is null;
select * from taxpayer where id is not null;
select null = null, null <=> null;
select if(id is null, "unknown", id) as 'id' from taxpayer;
select name, ifnull(id, 'unknown') as 'id' from taxpayer;
----------------------结果集排序-----------------------
select * from mail where size > 1000000 order by size;
select * from mail where dstuser = 'tricia' order by srchost, srcuser;
select * from mail where size > 50000 order by size desc;
----------------------使用视图简化查询-----------------------
select date_format(t, '') as date_sent,
concat(srcuser, '@', srchost) as sender,
concat(dstuser, '@', dsthost) as recipient,
size
from mail;
create view mail_view as
select date_format(t, '%M %e, %Y') as date_sent,
concat(srcuser, '@', srchost) as sender,
concat(dstuser, '@', dsthost) as recipient,
size
from mail;
select date_sent, sender, size from mail_view
where size > 100000
order by size;
----------------------多表查询-----------------------
--创建表
CREATE TABLE profile_contact
(
profile_id INT UNSIGNED NOT NULL, # ID from profile table
service CHAR(20) NOT NULL, # messaging service name
contact_name CHAR(25) NOT NULL, # name to use for contacting person
INDEX (profile_id)
);
--插入数据
INSERT INTO profile_contact
VALUES
(1, 'AIM', 'user1-aimid'),
(1, 'MSN', 'user1-msnid'),
(2, 'AIM', 'user2-aimid'),
(2, 'MSN', 'user2-msnid'),
(2, 'Yahoo', 'user2-yahooid'),
(4, 'Yahoo', 'user4-yahooid')
;
select * from profile_contact order by profile_id, service;
select id, name, service, contact_name
from profile inner join profile_contact on id = profile_id;
select * from profile_contact
where profile_id = (select id from profile where name = 'Mort');
----------------------从查询结果集头或尾取出部分行-----------------------
select * from profile;
select * from profile limit 1;
select * from profile limit 5;
select * from profile order by birth limit 1;
select * from profile order by birth desc limit 1;
select name, date_format(birth, '%m-%d') as birthday
from profile
order by birthday
limit 1;
----------------------在结果集头中间选取部分行-----------------------
select * from profile order by birth limit 2, 1;
select * from profile order by birth desc limit 2, 1;
select count(*) from profile;
select * from profile order by name desc limit 0, 4;
select * from profile order by name desc limit 4, 4;
select * from profile order by name desc limit 8, 4;
select sql_calc_found_rows * from profile order by id limit 4;
select found_rows();
----------------------选择合适的limit参数-----------------------
--创建表
CREATE TABLE al_winner
(
name CHAR(30),
wins INT
);
--初始化数据 al_winner-2001.txt
Mulder, Mark 21
Clemens, Roger 20
Moyer, Jamie 20
Garcia, Freddy 18
Hudson, Tim 18
Abbott, Paul 17
Mays, Joe 17
Mussina, Mike 17
Sabathia, C.C. 17
Zito, Barry 17
Buehrle, Mark 16
Milton, Eric 15
Pettitte, Andy 15
Radke, Brad 15
Sele, Aaron 15
--导入数据数据
load data local infile 'al_winner-2001.txt' into table al_winner;
set @n = 0;
set @val = 0;
select @n:=if(@val=wins, @n, @n+1) as rank,name, @val:=wins as wins
from al_winner order by wins desc, name;
selectname, wins from al_winner
order by wins desc, name;
select name, wins from al_winner
order by wins desc, name
limit 4;
select name, wins from al_winner
order by wins desc, name
limit 3, 1;
select name, wins from al_winner
where wins >= 18
order by wins desc, name;
select name, wins from al_winner
where wins >=
(
select wins from al_winner
order by wins desc, name
limit 3, 1
)
order by wins desc, name;
select distinct wins from al_winner
order by wins desc, name
limit 3, 1;
select name, wins from al_winner
where wins >= 17
order by wins desc, name;
select name, wins from al_winner
where wins >=
(
select distinct wins from al_winner
order by wins desc, name
limit 3, 1
)
order by wins desc, name;
----------------------当limit需要“错误”的排列顺序时做什么-----------------------
select name, birth from profile order by birth desc limit 4;
select count(*) from profle;
select name, birth from profile order by birth limit 6, 4;
select name, birth
from
(
select name, birth from profile order by birth desc limit 4
) as t
order by birth;
----------------------从表达式中计算limit值-----------------------
--limit只允许数字作为其参数，不能使用表达式
select * from profile limit 5+5;
select * from profile limit @skip_count, @show_count;
---------------------------------------------------------
--------------------------表关联-------------------------
---------------------------------------------------------
----------------------克隆表-----------------------------
--仅仅克隆表结构(不克隆源表外键定义)
create table new_table like original_table;
create table mail2 like mail;
select * from mail2;
--转移表数据
insert into new_table select * from original_table;
insert into mail2 select * from mail where srcuser = 'barb';
select * from mail2;
----------------------将查询结果保存到表中-----------------------------
--如果目的表已经存在
insert into dsttbl(i, s) select val, name from src_tal;
insert into dst_tbl select * from src_tbl; /*目的表与源表结构相同，拷贝全部数据*/
insert into dst_tbl select * from src_tbl where val > 1000 and name like 'A%'; /*目的表与源表结构相同，拷贝部分数据*/
insert into dst_tbl(i, s) select count(*), name from src_tbl group by name; /*目的表与基于源表进行统计的结果结构相同，将源表统计结果插入目的表*/
--如果目的表不存在
create table dst_tbl select * from src_tbl;
create table dst_tbl select * from src_tbl where 0; /*仅克隆表结构，不克隆约束、键等，不包含数据*/
create table dst_tbl select b, c from src_tbl; /*依照源表部分列建表*/
create table dst_tbl select c, b, a from src_tbl; /*改变源表列定义顺序（假设源表为a、b、c）*/
create table dst_tbl (id int not null auto_increment), primary key(id)) select a, b, c from src_tbl; /*在create table子句中插入列定义，此列不存在于源表中*/
/*
create table dst_tbl
(
id int not null auto_increment),
primary key(id)
)
select a, b, c from src_tbl;
*/
create table hoho
(
id int not null auto_increment,
primary key (id)
)
select * from limbs;
create table dst_tbl select inv_no, sum(unit_cost * quantity) as total_cost from src_tbl group by inv_no; /*在create table子句中为表达式赋予别名，此列不存在于源表中*/
/*
create table dst_tbl
select inv_no, sum(unit_cost * quantity) as total_cost
from src_tbl
group by inv_no;
*/
create table dst_tbl (primary key(id), index(state, city)) select * from src_tbl; /*根据源表定义目的表主键、索引，假设源表主键为id，state、city列上有索引*/
/*间接复制列属性与默认值*/
create table dst_tbl (primary key(id)) select * from src_tbl;
alter table dst_tbl modify id int unsigned not null auto_increment;
----------------------使用临时表-----------------------------
--普通建表
create temporary table tbl_name(...)
--克隆表
create temporary table new_table like original_table;
--根据查询结果建表
create temporary table tbl_name select ...;
--创建与已存在的表同名的临时表，暂时屏蔽非临时表
create temporary table mail select * from mail;
select count(*) from mail;
delete from mail;
select count(*) from mail;
drop table mail;
select count(*) from mail;
drop temporary table if exists tbl_name;
----------------------检查或改变某个表的存储引擎-----------------------------
--InnoDB BDB MyISAM
--检测information_schema
select engine from information_schema.tables where table_schema = 'cookbook' and table_name = 'mail';
show table status\G
show table status like 'mail'\G
show create table mail\G
--改变表引擎
alter table mail engine = InnoDB;
alter table mail engine = BDB;
alter table mail engine = MyISAM;
--生成唯一的表名
drop table if exists tbl_name;
select connection_id();
---------------------------------------------------------
----------------------与字符串共舞-----------------------
---------------------------------------------------------
----------------------字符串属性-------------------------
--查看系统支持字符集
show character set;
--查看字节长度、字符长度
set @s = convert('abc' using ucs2);
select length(@s), char_length(@s);
set @s = convert('abc' using utf8);
select length(@s), char_length(@s);
--查看collation
show collation;
--查看特定字符集collation
show collation like 'latin1%';
--collation排序
create table t (c char(3) character set latin1);
insert into t (c) values('AAA'), ('bbb'), ('aaa'), ('BBB');
select c from t;
--大小写不敏感
select c from t order by c collate latin1_swedish_ci;
--大小写敏感
select c from t order by c collate latin1_general_cs;
--二进制数值排序
select c from t order by c collate latin1_bin;
create table t(c char(2) character set utf8);
insert into t(c) values('cg'), ('ch'), ('ci'), ('lk'), ('ll'), ('lm');
select c from t order by c collate utf8_general_ci;
select c from t order by c collate utf8_spanish2_ci;
----------------------选择字符串数据类型-------------------------
-----------------------------------------------------
二进制数据类型 非二进制数据类型 最大长度
binary char 255
varbinary varchar 65535
tinyblob tinytext 255
blob text 65535
mediumblob mediumtext 16777215
longblog longtext 4294967295
-----------------------------------------------------
--char与varchar处理尾部空格的差异之处
create table t (c1 char(10), c2 varchar(10));
insert into t (c1, c2) values('abc ', 'abc ');
select c1, c2, char_length(c1), char_length(c2) from t;
--指定列character set与collation
create table mytbl
(
utf8data varchar(100) character set utf8 collate utf8_danish_ci,
sjisdata varchar(100) character set sjis collate sjis_japanese_ci
);
--character-set-server
--collation-server
----------------------正确设置客户端连接的字符集-------------------------
--配置文件
[mysql]
default-character-set=utf-8
--mysql命令行
set names 'utf8';
set names 'utf8' collate 'utf8-general-ci';
--编程接口:MySQL connectot/J, 应用程序连接URL
jdbc:mysql://localhost/cookbook?characterEncoding=UTF-8
----------------------串字母-------------------------
--定义字符串
'my string'
"my string" /*ansi_quotes 模式启用时双引号内的字符解释为表名或列名*/
--使用十六进制数标记abcd
0x6162364
x'6162364'
X'6162364'
insert into t set binary_col = 0xdeadbeef; /*sql中使用十六进制字符串指代二进制数值*/
--为字符串指定一个字符集解释器
_latin1 'abcd' /*包含四个单字节字符的字符串*/
_ucs2 'abcd' /*包含两个双字节字符的字符串*/
--包含单引号的字符串
select "I'm asleep";
select 'I''m asleep';
select 'I\'m wide awake';
--包含双引号的字符串
select 'He said, "Boo!"';
select "He said, ""Bob!""";
select "And I said \"Yikes!\"";
--包含\反斜线的字符串(特殊字符\b,\n,\r,\t,\0,\\)
select 'Install MySQL in c:\\mysql on Windows';
--使用十六进制数表示字符串
select 0x49276D2061736C656570;
----------------------检查一个字符串的字符集或字符排序-------------------------
--返回特定字符集和字符排序
select user(), charset(user()), collation(user());
--改变当前字符集影响普通字符串字符集和字符排序
set names 'latin1';
select charset('abc'), collation('abc');
set names latin7 collate 'latin7_bin';
select charset('abc'), collation('abc');
--返回二机制字符串
select charset(md5('a')), collation(md5('a'));
select charset(password('a')), collation(password('a'));
--转换二机制字符串结果大小写将失败
select upper(md5('a')), lower(md5('a'));
----------------------改变字符串的字符集或字符排序-------------------------
--改变字符集
set @s1 = "my string";
set @s2 = convert(@s1 using utf8);
select charset(@s1), charset(@s2);
--改变collation
set @s1 = 'my string';
set @s2 = @s1 collate latin1_spanish_ci;
select collation(@s1), collation(@s2);
select _latin1 'abc' collate utf8_bin; /*出错，Collation对于字符串的字符集必须是合法的*/
set @s1 = "my string";
set @s2 = convert(@s1 using utf8) collate utf8_spanish_ci; /*正确，先转换字符集，再转换排序*/
select charset(@s1), collation(@s1), charset(@s2), collation(@s2);
--转换二进制字符串和非二进制字符串
set @s1 = 'my string';
set @s2 = convert(@s1 using binary);
set @s3 = convert(@s2 using utf8);
select charset(@s1), charset(@s2), charset(@s3);
--binary操作符产生二进制字符串
set @s1 = 'my string';
set @s2 = binary @s2; /*与convert(str using binary)等效*/
select charset(@s1), charset(@s2);
----------------------更改字符串字母的大小写-------------------------
--整体替换大小写
select thing, upper(thing), lower(thing) from limbs;
--局部修改大小写
select thing, concat(upper(left(thing, 1)), mid(thing, 2)) from limbs;
--自定义函数局部修改大小写
create function initial_cap(s varchar(255))
returns varchar(255) deterministic
return concat(upper(left(s, 1)), mid(s, 2));
select thing, initial_cap(thing) from limbs;
----------------------更改字符串字母的大小写失败处理情况-------------------------
--改变非二进制字符串大小写
set @s = 'aBcD';
select upper(@s), lower(@s);
--改变二进制字符串大小写
drop table if exists t;
create table t(b blob) select 'aBcD' as b;
select b, upper(b), lower(b) from t; /*upper、lower改变二进制字符串大小写不生效*/
set @s = binary 'aBcD';
select @s, lower(@s), upper(@s); /*MySQL4.1之前生效*/
select b, upper(convert(b using latin1)) as upper, lower(convert(b using latin1)) as lower from t; /*先转换二进制字符串到非二机制字符串，再进行大小写转换*/
select charset(version()), charset(md5('some string'));
select upper(version());
select md5('some thing'), upper(convert(md5('some thing') using latin1));
----------------------控制字符串比较中的大小写敏感-------------------------
select 'cat' = 'cat', 'cat' = 'dpg';
select 'cat' != 'cat', 'cat' != 'dpg';
select 'cat' < 'awk', 'cat' < 'dpg';
select 'cat' between 'awk' and 'egret';
set @s1 = binary 'cat', @s2 = binary 'CAT';
select @s1 = @s2; /*二进制字符串比较大小写敏感，结果不等*/
set @s1 = convert(@s1 using latin1) collate latin1_swedish_ci;
set @s2 = convert(@s2 using latin1) collate latin1_swedish_ci;
select @s1 = @s2; /*转换成非二进制字符串，并指定字符集collation大小写不敏感，结果相等*/
/*latin1默认collation为latin1_swedish_ci*/
set @s1 = convert(@s1 using latin1);
set @s2 = convert(@s2 using latin1);
select @s1 = @s2;
set @s1 = _latin1 'cat', @s2 = _latin1 'CAT';
select @s1 = @s2;
select @s1 collate latin1_general_cs = @s2 collate latin1_general_cs as '@s1 = @s2';
select _latin1 'cat' = binary 'CAT';
set @s1 = _latin1 'cat', @s2 = _latin1 'CAT';
select @s1 = @s2, binary @s1 = @s2, @s1 = binary @s2;
--修改表定义，改变列大小写敏感模式
create table new
(
id int unsigned not null auto_increment,
article blob, /*大小写敏感*/
primary key(id)
);
alter table news
modify article text charcter set utf8 collate utf_general_ci; /*大小写不敏感*/
----------------------使用sql模式进行模式匹配-------------------------
--消除可能存在的表冲突
drop table if exists metal;
--创建表
create table metal (name varchar(20));
--初始化表数据
insert into metal (name) values('copper'), ('gold'), ('iron'), ('lead'), ('mercury'), ('platinum'), ('silver'), ('tin');
--查询初始化数据
select * from metal;
--查询以特定字符开头的字符串
select name from metal where name like 'co%';
--查询以特定字符结尾的字符串
select name from metal where name like '%er';
--查询含有特定字符的字符串
select name from metal where name like '%er%';
--查询特定位置出现特定字符
select name from metal where name like '__pp%';
--查询不含有特定字符的字符串
select name from metal where name not like '%i%';
--Null值
select null like '%', null not like '%';
----------------------使用正则表达式进行模式匹配-------------------------
--模式字符与定义
模式字符 定义
^ 匹配字符串的开始部分
$ 匹配字符串的结束部分
. 匹配任何字符（包括回车和新行）
[...] 括号内任意一个字符
[^...] 除了括号内所列字符之外的任意一个字符
p1 | p2 | p3 p1、p2或p3中任意一个模式串
* 匹配0或多个*之前的任何序列
+ 匹配1或多个+之前的任何序列
{n} n个{n}之前的任何序列
{m, n} 最少m个，最多n个，{m, n}之前的任何序列
--查询以特定字符开头的字符串
select name from metal where name regexp '^co';
--查询以特定字符结尾的字符串
select name from metal where name regexp 'er$';
--查询含有特定字符的字符串
select name from metal where name regexp 'er';
--查询特定位置出现特定字符
select name from metal where name regexp '^..pp%';
--查询不含有特定字符的字符串
select name from metal where name not like '%i%';
--posix字符集定义正则表达式
posix类 匹配定义
[:alnum:] 字符和数字
[:alpha:] 字母
[:blank:] 空格或制表符(tab)
[:cntrl:] 控制符
[:digit:] 数字
[:graph:] 图形符号(不包括空格)
[:lower:] 小写字母
[:print:] 图形符号(包括空格)
[:punct:] 标点符号
[:space:] 空格、制表符、换行、回车换行
[:upper:] 大写字母
[:xdigit:] 十六进制符(0-9, a-f, A-F)
--检查是否含有十六进制字符
select name, name regexp '[[:xdigit:]]' from metal;
--选择性匹配
select name from metal where name regexp '^[aeiou]|er$';
--对正则表达式进行分组
select '0m' regexp '^[[:digit:]]+|[[:alpha:]]+$'; /*以数字开头或字母结尾*/
select '0m' regexp '^([[:digit:]]+|[[:alpha:]]+)$'; /*完全由数字或者完全由字母组成*/
--Null值
select null regexp '.*', null not regexp '.*';
----------------------模式匹配大小写问题-------------------------
select 'a' like 'A', 'a' regexp 'A';
select 'a' regexp '[[:lower:]]', 'a' regexp '[[:upper:]]';
set names latin1;
set @s = 'a' collate latin1_general_cs;
select @s like 'A', @s regexp 'A';
set @s = 'a', @s_cs = 'a' collate latin1_general_cs;
select @s regexp '[[:upper:]]', @s_cs regexp '[[:upper:]];'
----------------------分割或者串联字符串-------------------------
--取得左侧、中间、右侧字串
select name, left(name, 2), mid(name, 3, 1), right(name, 3) from metal;
--取得字串substring()
select name, substring(name, 4), mid(name, 4) from metal;
--取得字串substring_index()
select name, substring_index(name, 'r', 1), substring_index(name, 'i', -1); /*正数从左到右，负数从右到左*/
select name from metal where left(name, 1) >= 'n';
--拼接字符串concat()
select concat('Hello', user(), ', welcome to MySQL!') as greeting;
select concat(name, ' ends in "d": ', if(right(name, 1)='d', 'yes', 'no')) as 'ends in "d"?' from metal;
update metal set name = concat(name, 'ide');
select name from metal;
update tbl_name set set_col = if(set_col is null, val, concat(set_col, ', ', val));
update metal set name = left(name, char_length(name) - 3);
select name from metal;
----------------------查询字串-------------------------
select name, locate('in', name), locate('in', name, 3) from metal;
----------------------使用fulltext查询-------------------------
--创建表
create table kjv
(
bsect enum('0', 'N') not null,
bname varchar(20) not null,
bnum tinyint unsigned not null,
cnum tinyint unsigned not null,
vnum tinyint unsigned not null,
vtext text not null
) engine = MyISAM;
--导入初始化数据
load data local infile 'kjv.txt' into table kjv;
--添加全文索引
alter table kjv add fulltext(vtext);
--查询'Mizraim'一共出现了多少次
select count(*) from kjv where match(vtext) against('Mizraim');
--查询'Mizraim'具体出现在什么地方
select bname, cnum, vnum, vtext from kjv where match(vtext) against('Mizraim')\G
select bname, cnum, vnum, vtext from kjv where match(vtext) against('search string') order by bnum, cnum, vnum\G
select count(*) from kjv where match(vtext) against('Abraham') and bsect = 'N';
select count(*) from kjv where match(vtext) against('Abraham') and bname = 'Hebrews';
select count(*) from kjv where match(vtext) against('Abraham') and bname = 'Hebrews' and cnum = 11;
alter table kjv add index(bnum), add index(cnum), add index(vnum);
select count(*) from kjv where match(vtext) against('Abraham');
select count(*) from kjv where match(vtext) against('Abraham Sarah');
select count(*) from kjv where match(vtext) against('Abraham Sarah Ishmael Isaac');
alter table tbl_name add fulltext(col1, col2, col3);
select ... from tbl_name where match(col1, col2, col3) against('search string');
----------------------用短语来进行fulltext查询-------------------------
select count(*) from kjv where match(vtext) against('God');
select count(*) from kjv where match(vtext) against('sin');
select count(*) as 'total verses',
count(if(vtext like '%God%', 1, null)) as 'Verses containing "God"',
count(if(vtext like '%sin%', 1, null)) as 'Verses containing "sin"'
from kjv;
--修改配置文件/etc/my.cnf 或者mysqlhome/my.ini
[mysqld]
ft_min_world_len=3;
--重启服务器启用新的设置
repair table kjv quick;
select count(*) from kjv where match(vtext) against('God');
select count(*) from kjv where match(vtext) against('sin');
----------------------要求或静止fulltext搜索单词-------------------------
select count(*) from kjv where match(vtext) against('David Goliath');
select count(*) from kjv where match(vtext) against('+David +Goliath' in boolean mode);
select count(*) from kjv where match(vtext) against('+David -Goliath' in boolean mode);
select count(*) from kjv where match(vtext) against('-David +Goliath' in boolean mode);
select count(*) from kjv where match(vtext) against('whirl*' in boolean mode);
----------------------用fulltext索引来执行词组查询-------------------------
select count(*) from kjv where match(vtext) against('still small voice');
select count(*) from kjv where match(vtext) against('"still small voice"' in boolean mode);
---------------------------------------------------------
----------------------使用日期或时间---------------------
---------------------------------------------------------
----------------------选择合适的提起或者时间变量类型-------------------------
--创建表、初始化数据
DROP TABLE IF EXISTS date_val;
CREATE TABLE date_val
(
d DATE
);
INSERT INTO date_val (d) VALUES('1864-02-28');
INSERT INTO date_val (d) VALUES('1900-01-15');
INSERT INTO date_val (d) VALUES('1987-03-05');
INSERT INTO date_val (d) VALUES('1999-12-31');
INSERT INTO date_val (d) VALUES('2000-06-04');
DROP TABLE IF EXISTS datetime_val;
CREATE TABLE datetime_val
(
dt DATETIME
);
INSERT INTO datetime_val (dt) VALUES('1970-01-01 00:00:00');
INSERT INTO datetime_val (dt) VALUES('1987-03-05 12:30:15');
INSERT INTO datetime_val (dt) VALUES('1999-12-31 09:00:00');
INSERT INTO datetime_val (dt) VALUES('2000-06-04 15:45:30');
DROP TABLE IF EXISTS time_val;
CREATE TABLE time_val
(
t1 TIME,
t2 TIME
);
INSERT INTO time_val (t1,t2) VALUES('15:00:00','15:00:00');
INSERT INTO time_val (t1,t2) VALUES('05:01:30','02:30:20');
INSERT INTO time_val (t1,t2) VALUES('12:30:20','17:30:45');
DROP TABLE IF EXISTS time_val;
CREATE TABLE time_val
(
t1 TIME,
t2 TIME
);
INSERT INTO time_val (t1,t2) VALUES('15:00:00','15:00:00');
INSERT INTO time_val (t1,t2) VALUES('05:01:30','02:30:20');
INSERT INTO time_val (t1,t2) VALUES('12:30:20','17:30:45');
DROP TABLE IF EXISTS timestamp_val;
CREATE TABLE timestamp_val
(
ts TIMESTAMP
);
INSERT INTO timestamp_val (ts) VALUES('1970-01-01 09:00:00');
INSERT INTO timestamp_val (ts) VALUES('1987-03-05 12:30:15');
INSERT INTO timestamp_val (ts) VALUES('1999-12-31 09:00:00');
INSERT INTO timestamp_val (ts) VALUES('2000-06-04 15:45:30');
--查看数据
select * from date_val;
select * from datetime_val;
select * from time_val;
select * from timestamp_val;
----------------------修改MySQL日期格式-------------------------
select str_to_date('May 13, 2007', '%M %d, %Y');
insert into date_val (d) values(str_to_date('May 13, 2007', '%M %d, %Y'));
select * from date_val;
select d, date_format(d, '%M %d, %Y') from date_val;
select d, date_format(d, '%M %d, %Y') as date from date_val;
--格式化串定义
格式化字符 含义
%Y 年份，数字形式，4位数
%y 年份，数字形式，2位数
%M 完整度月份名称（Hanuary-December）
%b 月份名称的前三个字母（Jan-Dec）
%m 月份，数字形式（01..12）
%c 月份，数字形式(1..12)
%d 该月日期，数字形式（01..31）
%e 该月日期，数字形式（1..31）
%W 工作日名称（Sunday..Saturday）
%r 时间，12小时制，以AM或PM结尾
%T 时间，24小时制
%H 小时，数字形式，2位数（00..23）
%i 分钟，数字形式，2位数（00..59）
%s 秒，数字形式，2位数（00..59）
%% '%'文字字符
select dt, date_format(dt, '%c/%e/%y %r') as format1, date_format(dt, '%M %e, %Y %T') as format2 from datetime_val;
select dt, time_format(dt, '%r') as '12-hour time', time_format(dt, '%T') as '24-hour time' from datetime_val;
delimiter $$
create function time_ampm(t time)
returns varchar(13) # mm:dd:ss (a.m.|p.m.)格式
begin
declare ampm char(4);
if time_to_sec(t) < 12*60*60 then
set ampm = 'a.m.';
else
set ampm = 'p.m.';
end if;
return concat(left(time_format(4, '%r'), 9), ampm);
end;
$$
delimiter ;
select t1, time_ampm(t1) from time_val;
----------------------设置客户端时区-------------------------
--客户端位于不同时区需要注意，如果位于同一时区则不需要关心
drop table if exists t;
create table t (ts timestamp);
insert into t (ts) values('2006-06-01 12:30:00');
select ts from t;
select @@global.time_zone, @@session.time_zone;
select ts from t;
set session time_zone = '+04:00';
select @@global.time_zone, @@session.time_zone;
select ts from t;
----------------------获取当前日期或时间-------------------------
select curdate(), curtime(), now();
select current_date(), current_time(), current_timestamp();
select utc_date(), utc_time(), utc_timestamp();
----------------------使用timestamp跟踪行修改时间-------------------------
--timestamp初始化为当前日期，并随行值改变而改变
drop table if exists t;
create table t(ts timestamp);
show create table t\G
drop table if exists tsdemo1;
create table tsdemo1(ts timestamp, val int);
insert into tsdemo1(val) values(5);
insert into tsdemo1(ts, val) values(null, 10);
select * from tsdemo1;
update tsdemo1 set val = 6 where val = 5;
select * from tsdemp1;
update tsdemo1 set val = val + 1;
--timestamp初始化为当前日期，行值改变不改变
drop table if exists tsdemo2;
create table tsdemo2(t_create timestamp default current_timestamp, val int);
select * from tsdemo2;
insert into tsdemo2(val) values(5);
insert into tsdemo2(t_create, val) values(null, 10);
select * from tsdemo2;
update tsdemo2 set val = val + 1;
select * from tsdemo2;
----------------------从日期或者时间值中分解出各部分值-------------------------
--使用成分分解函数来分解日期和时间值
select dt, date(dt), time(dt) from datetime_val;
--日期相关函数
函数 返回值
year() 月份数值
month() 月份名称（1..12）
monthname() 月份中的天数值（January..December）
dayofmonth() 一周中的天数（1..31）
dayname() 一周中的天数（Sunday..Saturday）
dayofweek() 一周中的天数（1..7对应Sunday..Saturday）
weekday() 一周中的天数（0..6对应Monday..Sunday）
dayofyear() 一年中的天数值（1.366）
hour() 时间中的小数值（0..23）
minute() 时间中的分数值（0..59）
second() 时间中的秒数（0..59）
select dt, year(dt), dayofmonth(dt), hour(dt), second(dt) from datetime_val;
select d, dayofyear(d) from date_val;
select d, dayname(d), left(dayname(d), 3) from date_val;
select d, dayname(d), dayofweek(d), weekday(d) from date_val; /*dayofweek 1~7星期天到星期六 weekday0~6星期一到星期天*/
select dt, extract(day from dt), extract(hour from dt) from datetime_val; /*year,month,day,hour,minute,second*/
select curdate(), year(curdate()) as year, month(curdate()) as month, monthname(curdate()) as monthname, dayofmonth(curdate()) as day, dayname(curdate()) as dayname;
select now(), hour(now()) as hour, minute(now()) as minute, second(now()) as second;
--使用格式化分解日期或者时间值
select dt, date_format(dt, '%Y') as year, date_format(dt, '%d') as day, time_format(dt, '%H') as hour, time_format(dt, '%s') as second from datetime_val;
select dt, date_format(dt, '%Y-%m-%d') as 'date part', time_format(dt, '%T') as 'time part' from datetime_val;
select ts, date_format(ts, '%M %e, %Y') as 'descriptive date', time_format(ts, '%H:%i') as 'hours/minutes' from timestamp_val;
--使用字符串函数分解时间或者日期值
select dt, left(dt, 4) as year, mid(dt, 9, 2) as day, right(dt, 2) as second from datetime_val;
select dt, left(dt, 10) as date, right(dt, 8) as time from datetime_val;
select ts, left(ts, 10) as date, right(ts, 8) as time from timestamp_val;
----------------------合成日期或者时间-------------------------
select maketime(10,30,58), maketime(-5,0,11);
select makedate(2007,60);
select d, date_format(d, '%Y-%m-01') from date_val;
select t1, time_format(t1, '%H:%i:00') from time_val;
select d, concat(year(d), '-', month(d), '-01') from date_val;
select d, concat(year(d), '-', lpad(month(d), 2, '0'), '-01') from date_val;
select d, concat(year(d), '-', lpad(month(d), 4, '0'), '-01') from date_val;
select t1, concat(lpad(hour(t1), 2, '0'), ':', lpad(minute(t1), 2, '0'), ':00') as recombined from time_val;
set @d = '2006-02-28';
set @t = '13:10:05';
select @d, @t, concat(@d, ' ', @t);
----------------------在时间数据类型和基本单位间进行转换-------------------------
--在时间和秒之间进行转换
select t1, time_to_sec(t1) as 'TIME to seconds', sec_to_time(time_to_sec(t1)) as 'TIME to seconds to TIME' from time_val;
select t1, time_to_sec(t1) as 'seconds', time_to_sec(t1)/60 as 'minutes', time_to_sec(t1)/(60*60) as hours, time_to_sec(t1)/(24*60*60) as 'days' from time_val;
select t1, time_to_sec(t1) as 'seconds',
floor(time_to_sec(t1)/60) as 'minutes',
floor(time_to_sec(t1)/(60*60)) as hours,
floor(time_to_sec(t1)/(24*60*60)) as 'days'
from time_val;
select dt, time_to_sec(dt) as 'time part in seconds',
sec_to_time(time_to_sec(dt)) as 'time part as TIME'
from datetime_val;
select ts, time_to_sec(ts) as 'time part in seconds',
sec_to_time(time_to_sec(ts)) as 'time part as TIME'
from timestamp_val;
--在日期值和天数之间进行转换
select d, to_days(d) as 'date to days', from_days(to_days(d)) as 'DATE to days to DATE' from date_val;
select dt, to_days(dt) as 'date part in days', from_days(to_days(dt)) as 'date part as DATE' from datetime_val;
select ts, to_days(ts) as 'date part in days', from_days(to_days(ts)) as 'date part as DATE' from timestamp_val;
--在datetime或者timestamp类型值和秒数之间进行转换
select dt, unix_timestamp(dt) as seconds, from_unixtime(unix_timestamp(dt)) as timestamp from datetime_val;
select curdate(), unix_timestamp(curdate()), from_unixtime(unix_timestamp(curdate()))\G
----------------------计算两个日期和时间之间的间隔-------------------------
--使用时间差函数
set @d1 = '2010-01-01', @d2 = '2009-12-01';
select datediff(@d1, @d2) as 'd1 - d2', datediff(@d2, @d1) as 'd2 - d1';
set @t1 = '12:00:00', @t2 = '16:30:00';
select timediff(@t1, @t2) as 't1 - t2', timediff(@t2, @t1) as 't2 - t1';
select t1, t2, timediff(t2, t1) as 't2 - t1 as TIME',
if(timediff(t2, t1) >= 0, '+', '-') as sign,
hour(timediff(t2, t1)) as hour,
minute(timediff(t2, t1)) as minute,
second(timediff(t2, t1)) as second
from time_val;
set @dt1 = '1900-01-01 00:00:00', @dt2 = '1910-01-01 00:00:00';
select timestampdiff(minute, @dt1, @dt2) as minutes,
timestampdiff(hour, @dt1, @dt2) as hours,
timestampdiff(day, @dt1, @dt2) as days,
timestampdiff(week, @dt1, @dt2) as weeks,
timestampdiff(year, @dt1, @dt2) as years;
--利用基本时间单位计算时间间隔
select t1, t2, time_to_sec(t2) - time_to_sec(t1) as 't2 - t1 (in seconds)',
sec_to_time(time_to_sec(t2) - time_to_sec(t1)) as 't2 - t1 (as TIME)'
from time_val;
--使用基本单位计算两个Date类型值，或者两个date-and-time类型值的时间间隔
select to_days('1884-01-01') - to_days('1883-06-05') as days;
select (to_days('1884-01-01') - to_days('1883-06-05')) / 7 as weeks;
set @dt1 = '1984-01-01 09:00:00';
set @dt2 = @dt1 + interval 14 day;
select unix_timestamp(@dt2) - unix_timestamp(@dt1) as seconds;
set @interval = unix_timestamp(@dt2) - unix_timestamp(@dt1);
select @interval as seconds,
@interval / 60 as minutes,
@interval / (60 * 60) as hours,
@interval / (24 * 60 * 60) as days,
@interval / (7 * 24 * 60 * 60) as weeks;
set @dt1 = '1800-02-14 07:30:00';
set @dt2 = @dt1 + interval 7 day;
set @interval = ((to_days(@dt2) - to_days(@dt1)) * 24 * 60 * 60)
+ time_to_sec(@dt2) - time_to_sec(@dt1);
select @interval as seconds, sec_to_time(@interval) as time;
----------------------增加日期或时间值-------------------------
--使用时间加法函数或者操作符进行时间值求和运算
set @t1 = '12:00:00', @t2 = '15:30:00';
select addtime(@t1, @t2);
set @dt = '1984-03-01 12:00:00', @t = '12:00:00';
select addtime(@dt, @t);
set @d = '1984-03-01', @t = '15:30:00';
select timestamp(@d, @t);
set @dt = '1984-03-01 12:00:00', @t = '12:00:00';
select timestamp(@dt, @t);
--求出即日第三天的日期值
select curdate(), date_add(curdate(), interval 3 day);
--一个星期前的日期值
select curdate(), date_sub(curdate(), interval 7 day);
--60小时之后是什么时间（同时需要日期和时间值）
select now(), date_add(now(), interval 60 hour);
select now(), date_add(now(), interval '14:30' hour_minute);
select now(), date_add(now(), interval '3 4' day_hour);
select curdate(), curdate() + interval 1 year;
select now(), now() - interval '1 12' day_hour;
--使用基本时间单位进行时间加法运算
select t1, sec_to_time(time_to_sec(t1) + 7200) as 't1 plus 2 hours' from time_val;
select t1, t2, time_to_sec(t1) + time_to_sec(t2) as 't1 + t2 (in seconds)',
sec_to_time(time_to_sec(t1) + time_to_sec(t2)) as 't1 + t2 (as TIME)'
from time_val;
select t1, t2, mod(time_to_sec(t1) + time_to_sec(t2), 86400) as 't1 + t2 (in seconds)',
sec_to_time(mod(time_to_sec(t1) + time_to_sec(t2), 86400)) as 't1 + t2 (as TIME)'
from time_val;
set @d = '2006-01-01';
select @d as date, from_days(to_days(@d) + 7) as 'date + 1 week',
from_days(to_days(@d) - 7) as 'date - 1 week';
set @dt = '2006-01-01 12:30:45';
select @dt as datetime,
from_days(to_days(@dt) + 7) as 'datetime + 1 week',
from_days(to_days(@dt) - 7) as 'datetime - 1 week';
set @dt = '2006-01-01 09:00:00';
select @dt as datetime,
from_unixtime(unix_timestamp(@dt) + 3600) as 'datetime + 1 hour',
from_unixtime(unix_timestamp(@dt) - 3600) as 'datetime - 1 hour';
----------------------计算年龄-------------------------
--创建表、初始化数据
drop table if exists sibling;
create table sibling
(
name char(20),
birth date
);
insert into sibling (name,birth) values('Gretchen','1942-04-14');
insert into sibling (name,birth) values('Wilbur','1946-11-28');
insert into sibling (name,birth) values('Franz','1953-03-05');
select * from sibling;
select name, birth, curdate() as today, timestampdiff(year, birth, curdate()) as 'age in years' from sibling;
select name, birth, '1953-03-05' as 'Franz'' birth', timestampdiff(year, birth, '1953-03-05') as 'age in years' from sibling where name != 'Franz';
select name, birth, curdate() as today, timestampdiff(month, birth, curdate()) as 'age in months' from sibling;
select dayofyear('1995-03-01'), dayofyear('1996-02-29');
select right('1995-03-01', 5), right('1996-02-29', 5);
select if('02-29' < '03-01', '02-29', '03-01') as earliest;
set @birth = '1965-03-01';
set @target = '1975-01-01';
select @birth, @target, year(@target)- year(@birth) as 'difference',
if(right(@target, 5) < right(@birth, 5), 1, 0) as 'adjustment',
year(@target) - year(@birth) - if(right(@target, 5) < right(@birth, 5), 1, 0) as 'age';
select name, birth, curdate() as today, year(curdate()) - year(birth) - if(right(curdate(), 5) < right(birth, 5), 1, 0) as 'age in years' from sibling;
select name, birth, '1953-03-05' as 'Franz'' birthday', year('1953-03-05') - year(birth) - if(right('1953-03-05', 5) < right(birth, 5), 1, 0) as 'age in years' from sibling where name != 'Franz';
select name, birth, curdate() as today, (year(curdate()) - year(birth)) * 12 + (month(curdate()) - month(birth)) - if(dayofmonth(curdate()) < dayofmonth(birth), 1, 0) as 'age in months' from sibling;
--将一个日期和时间值切换到另一个时区
set @dt = '2006-11-23 09:00:00';
select @dt as Chicago, convert_tz(@dt, 'US/Central', 'Europe/Berlin') as Berlin,
convert_tz(@dt, 'US/Central', 'Europe/London') as London,
convert_tz(@dt, 'US/Central', 'America/Edmonton') as Edmonton,
convert_tz(@dt, 'US/Central', 'Australia/Brisbane') as Brisbane\G
select @dt as Chicago, convert_tz(@dt, '-06:00', '+01:00') as Berlin,
convert_tz(@dt, '-06:00', '+00:00') as London,
convert_tz(@dt, '-06:00', '-07:00') as Edmonton,
convert_tz(@dt, '-06:00', '+10:00') as Brisbane\G
----------------------找出每月的第一天和最后一天-------------------------
select d, date_sub(d, interval dayofmonth(d)-1 day) as '1st of month' from date_val;
select d, date_add(date_sub(d, interval dayofmonth(d)-1 day), interval -1 month) as '1st of previous month',
date_add(date_sub(d, interval dayofmonth(d)-1 day), interval 1 month) as '1st of following month'
from date_val;
select d, last_day(d) as 'last of month' from date_val;
select d, last_day(date_add(d, interval -1 month)) as 'last of previous month',
last_day(date_add(d, interval 1 month)) as 'last of following month'
from date_val;
select d, dayofmonth(last_day(d)) as 'days in month' from date_val;
----------------------通过字串替换来计算日期-------------------------
select d, date_format(d, '%Y-%m-01') as method1,
concat(year(d), '-', lpad(month(d), 2, '0'), '-01') as method2
from date_val;
select d, date_format(d, '%Y-01-01') as method1,
concat(year(d), '-01-01') as method2
from date_val;
select d, date_format(d, '%Y-12-15') as method1,
concat(year(d), '-12-15') as method2
from date_val;
select curdate(), date_add(date_format(curdate(), '%Y-12-25'), interval 2 year) as method1,
date_format(date_add(curdate(), interval 2 year), '%Y-12-25') as method2;
----------------------计算某个日期为星期几-------------------------
select curdate(), dayname(curdate());
--一个月的第一天是星期几
set @d = curdate();
set @first = date_sub(@d, interval dayofmonth(@d)-1 day);
select @d as 'starting date',
@first as '1st of month date',
dayname(@first) as '1st of month day';
----------------------查出给定某周到某天的日期-------------------------
select d, dayname(d) as day,
date_add(d, interval 1-dayofweek(d) day) as sunday,
date_add(d, interval 7-dayofweek(d) day) as saturday
from date_val;
--两个星期前星期三的日期
set @target = date_sub(date_add(curdate(), interval 4-dayofweek(curdate()) day), interval 14 day);
select curdate(), @target, dayname(@target);
set @target = date_add(date_sub(curdate(), interval 14 day), interval 4-dayofweek(curdate()) day);
select curdate(), @target, dayname(@target);
----------------------执行闰年计算-------------------------
select d, year(d) % 4 = 0 as 'rule-of-thumb test',
(year(d) % 4 = 0) and ((year(d) % 100 != 0) or (year(d) % 400 = 0))
as 'complete test'
from date_val;
set @d = '2006-04-13';
select dayofyear(date_format(@d, '%Y-12-31'));
set @d = '2008-04-13';
select dayofyear(date_format(@d, '%Y-12-31'));
----------------------接近但不是iso格式的日期格式-------------------------
select d, concat(year(d), '-', month(d), '-01') from date_val;
select d, concat(year(d), '-', lpad(month(d), 2, '0'), '-01') from date_val;
select concat(year(d), '-', month(d), '-01') as 'non-iso',
date_add(concat(year(d), '-', month(d), '-01'), interval 0 day) as 'iso 1',
concat(year(d), '-', month(d), '-01') + interval 0 day as 'iso2',
from_days(to_days(concat(year(d), '-', month(d), '-01'))) as 'iso 3',
str_to_date(concat(year(d), '-', month(d), '-01'), '%Y-%m-%d') as 'iso 4'
from date_val;
----------------------将日期或时间当成数值-------------------------
select t1, t1+0 as 't1 as number',
floor(t1) as 't1 as number',
floor(t1/10000) as 'hour part'
from time_val;
select d, d+0 from date_val;
select dt, dt+0, floor(dt+0) from datetime_val;
select '1999-01-01' + 0, '1999-01-01 12:30:45' + 0, '12:30:45'+0;
show warnings;
----------------------强制MySQL将字符串当作时间值-------------------------
select '12:30:45'+0, sec_to_time(time_to_sec('12:30:45'))+0;
select '1999-01-01'+0, from_days(to_days('1999-01-01'))+0;
select date_add('1999-01-01 12:30:45', interval 0 day)+0 as 'numberic datetime';
---------------------------------------------------------
-----------------------排序查询结果----------------------
---------------------------------------------------------
--建表
drop table if exists driver_log;
create table driver_log
(
rec_id int unsigned not null auto_incerment,
name varchar(20) not null,
trav_date date not null,
miles int not null,
primary key (rec_id)
);
--初始化数据
insert into driver_log (name,trav_date,miles)
values
('Ben','2006-08-30',152),
('Suzi','2006-08-29',391),
('Henry','2006-08-29',300),
('Henry','2006-08-27',96),
('Ben','2006-08-29',131),
('Henry','2006-08-26',115),
('Suzi','2006-09-02',502),
('Henry','2006-09-01',197),
('Ben','2006-09-02',79),
('Henry','2006-08-30',203)
;
--查看数据
select * from driver_log;
select * from mail;
----------------------使用order by命令排序查询结果-------------------------
select * from driver_log order by name;
select * from driver_log order by name asc;
select * from driver_log order by name desc;
select * from driver_log order by name, trav_date;
select * from driver_log order by name desc, trav_date desc;
select * from driver_log order by name desc, trav_date;
select name, trav_date, miles as distance from driver_log order by distance;
select name, trav_date, miles as distance from driver_log order by distance desc;
----------------------使用表达式排序-------------------------
select * from mail;
select t, srcuser, floor((size+1023)/1024)
from mail
where size > 50000
order by floor((size+1023)/1024);
select t, srcuser, floor((size+1023)/1024) as kilobytes
from mail
where size > 50000
order by kilobytes;
----------------------显示一组按照其他属性排序的值-------------------------
select t, srcuser, concat(floor((size+1023)/1024), 'K') as size_in_k
from mail where size > 50000
order by size_in_k;
select t, srcuser, concat(floor((size+1023)/1024), 'K') as size_in_k
from mail where size > 50000
order by size;
--建表
drop table if exists roster;
create table roster
(
name CHAR(30), # player name
jersey_num CHAR(3) # jersey number
);
--初始化表
insert into roster (name, jersey_num)
values
('Lynne','29'),
('Ella','0'),
('Elizabeth','100'),
('Nancy','00'),
('Jean','8'),
('Sherry','47')
;
select name, jersey_num from roster;
select name, jersey_num from roster order by jersey_num;
select name, jersey_num from roster order by jersey_num+0;
select t, concat(srcuser, '@', srchost) as sender, size
from mail where size > 50000
order by srchost, srcuser;
--建表
drop table if exists name;
create table name
(
last_name char(20),
first_name char(20)
);
--初始化数据
insert into name (first_name,last_name) VALUES('Kevin','Brown');
insert into name (first_name,last_name) VALUES('Vida','Blue');
insert into name (first_name,last_name) VALUES('Pete','Gray');
insert into name (first_name,last_name) VALUES('Devon','White');
insert into name (first_name,last_name) VALUES('Rondell','White');
select last_name, first_name from name order by last_name, first_name;
select concat(first_name, ' ', last_name) as full_name from name order by last_name, first_name;
----------------------字符串排序的大小写区分控制-------------------------
--建表
drop table if exists str_val;
create table str_val
(
ci_str char(3) character set latin1 collate latin1_swedish_ci,
cs_str char(3) character set latin1 collate latin1_general_cs,
bin_str binary(3)
);
--初始化数据
insert into str_val (ci_str,cs_str,bin_str) VALUES
('AAA','AAA','AAA'),
('aaa','aaa','aaa'),
('bbb','bbb','bbb'),
('BBB','BBB','BBB');
select * from str_val;
select * from str_val order by ci_str;
select * from str_val order by cs_str;
select * from str_val order by bin_str;
select ci_str from str_val order by ci_str;
select cs_str from str_val order by cs_str;
select bin_str from str_val order by bin_str;
select ci_str from str_val order by ci_str collate latin1_general_cs;
select cs_str from str_val order by cs_str collate latin1_swedish_ci;
select cs_str from str_val order by upper(cs_str);
select bin_str from str_val order by convert(bin_str using latin1) collate latin1_swedish_ci;
----------------------基于日期的排序-------------------------
--建表
drop table if exists temporal_val;
create table temporal_val
(
d date,
dt datetime,
t time,
ts timestamp
);
--初始化数据
insert into temporal_val (d, dt, t, ts)
values
('1970-01-01','1884-01-01 12:00:00','13:00:00','1980-01-01 02:00:00'),
('1999-01-01','1860-01-01 12:00:00','19:00:00','2021-01-01 03:00:00'),
('1981-01-01','1871-01-01 12:00:00','03:00:00','1975-01-01 04:00:00'),
('1964-01-01','1899-01-01 12:00:00','01:00:00','1985-01-01 05:00:00')
;
select * from temporal_val；
select * from temporal_val order by d;
select * from temporal_val order by dt;
select * from temporal_val order by t;
select * from temporal_val order by ts;
----------------------按日历排序-------------------------
--建表
drop table if exists event;
create table event
(
date date,
description varchar(255)
)
;
--初始化表
insert into event (date,description)
values
('1789-07-04','US Independence Day'),
('1776-07-14','Bastille Day'),
('1957-10-04','Sputnik launch date'),
('1958-01-31','Explorer 1 launch date'),
('1919-06-28','Signing of the Treaty of Versailles'),
('1732-02-22','George Washington\'s birthday'),
('1989-11-09','Opening of the Berlin Wall'),
('1944-06-06','D-Day at Normandy Beaches'),
('1215-06-15','Signing of the Magna Carta'),
('1809-02-12','Abraham Lincoln\'s birthday')
;
select date, description from event;
select date, description from event order by date;
select date, description from event order by month(date), dayofmonth(date);
select date, description from event order by dayofyear(date);
select dayofyear('1996-02-29'), dayofyear('1997-03-01');
----------------------按周历排序-------------------------
select dayname(date) as day, date, description
from event
order by dayofweek(date);
select dayname(date), date, description
from event
order by mod(dayofweek(date)+5, 7);
----------------------按时钟排序-------------------------
select * from mail order by hour(t), minute(t), second(t);
select *, time_to_sec(t) from mail order by time_to_sec(t);
----------------------按数据列的字串排序-------------------------
--创建表、初始化数据
drop table if exists housewares;
create table housewares
(
id VARCHAR(20),
description VARCHAR(255)
);
insert into housewares (id,description)
values
('DIN40672US', 'dining table'),
('KIT00372UK', 'garbage disposal'),
('KIT01729JP', 'microwave oven'),
('BED00038SG', 'bedside lamp'),
('BTH00485US', 'shower stall'),
('BTH00415JP', 'lavatory')
;
select * from housewares;
drop table if exists housewares2;
create table housewares2
(
id varchar(20),
description varchar(255)
);
insert into housewares2 (id,description)
values
('DIN40672US', 'dining table'),
('KIT372UK', 'garbage disposal'),
('KIT1729JP', 'microwave oven'),
('BED38SG', 'bedside lamp'),
('BTH485US', 'shower stall'),
('BTH415JP', 'lavatory')
;
select * from housewares2;
drop table if exists housewares3;
create table housewares3
(
id VARCHAR(20),
description VARCHAR(255)
);
insert into housewares3 (id,description)
VALUES
('13-478-92-2', 'dining table'),
('873-48-649-63', 'garbage disposal'),
('8-4-2-1', 'microwave oven'),
('97-681-37-66', 'bedside lamp'),
('27-48-534-2', 'shower stall'),
('5764-56-89-72', 'lavatory')
;
select * from housewares3;
drop table if exists hw_category;
create table hw_category
(
abbrev VARCHAR(3),
name VARCHAR(20)
);
insert into hw_category (abbrev,name)
values
('DIN', 'dining'),
('KIT', 'kitchen'),
('BTH', 'bathroom'),
('BED', 'bedroom')
;
select * from hw_category;
--按固定长度的字串排序
select * from housewares;
select * from housewares order by id;
select id, left(id, 3) as category, mid(id, 4, 5) as serial, right(id, 2) as country from housewares;
select * from housewares order by left(id, 3);
select * from housewares order by mid(id, 4, 5);
select * from housewares order by right(id, 2);
select * from housewares order by right(id, 2), mid(id, 4, 5);
--按可变长度的字串排序
select * from housewares2;
select id, left(substring(id, 4), char_length(substring(id, 4))-2) from housewares2;
select id, substring(id, 4), substring(id, 4, char_length(id)-5) from housewares2;
select * from housewares2 order by substring(id, 4, char_length(id)-5);
select * from housewares2 order by substring(id, 4, char_length(id)-5);
select * from housewares2 order by substring(id, 4, char_length(id)-5)+0;
select *, substring(id, 4, char_length(id)-5)+0 from housewares2 order by substring(id, 4, char_length(id)-5)+0;
select * from housewares2 order by substring(id, 4)+0;
select * from housewares3;
select id, substring_index(substring_index(id, '-', 2), '-', -1) as segment2,
substring_index(substring_index(id, '-', 4), '-', -1) as segment4
from housewares3;
select * from housewares3 order by substring_index(substring_index(id, '-', 2), '-', -1);
select * from housewares3 order by substring_index(substring_index(id, '-', 2), '-', -1)+0;
----------------------按域名顺序排列主机名-------------------------
--建表
drop table if exists hostname;
create table hostname
(
name varchar(64)
);
--初始化数据
insert into hostname (name)
values
('cvs.php.net'),
('dbi.perl.org'),
('lists.mysql.com'),
('mysql.com'),
('jakarta.apache.org'),
('')
;
select * from hostname;
select name from hostname order by name;
select name, substring_index(substring_index(name, '.', -3), '.', 1) as leftmost,
substring_index(substring_index(name, '.', -2), '.', 1) as middle,
substring_index(name, '.', -1) as rightmost
from hostname;
select name, substring_index(substring_index(concat('..', name), '.', -3), '.', 1) as leftmost,
substring_index(substring_index(concat('.', name), '.', -2), '.', 1) as middle,
substring_index(name, '.', -1) as rightmost
from hostname;
select name, substring_index(substring_index(concat('....', name), '.', -3), '.', 1) as leftmost,
substring_index(substring_index(concat('....', name), '.', -2), '.', 1) as middle,
substring_index(name, '.', -1) as rightmost
from hostname;
select name from hostname
order by
substring_index(name, '.', -1),
substring_index(substring_index(concat('.', name), '.', -2), '.', 1),
substring_index(substring_index(concat('..', name), '.', -3), '.', 1);
----------------------按照数字顺序排序点分式主IP地址-------------------------
--建表
drop table if exists hostip;
create table hostip
(
ip varchar(64)
);
--初始化数据
insert into hostip (ip)
values
('127.0.0.1'),
('192.168.0.2'),
('192.168.0.10'),
('192.168.1.2'),
('192.168.1.10'),
('255.255.255.255'),
('21.0.0.1')
;
select * from hostip ORDER BY ip;
select ip from hostip
order by
substring_index(ip, '.', 1)+0,
substring_index(substring_index(ip, '.', -3), '.', 1)+0,
substring_index(substring_index(ip, '.', -2), '.', 1)+0,
substring_index(ip, '.', -1)+0;
select ip from hostip order by inet_aton(ip);
select ip, ip+0 from hostip;
----------------------将数值移动到排序结果的头部或尾部-------------------------
select null = null;
--建表
drop table if exists t;
create table t (
val varchar(64)
);
--初始化数据
insert into t (val)
values
(3),
(100),
(null),
(null),
(9)
;
select val from t;
select val from t order by val;
select val from t order by if(val is null, 1, 0), val;
select t, srcuser, dstuser, size
from mail
order by if(srcuser='phil', 0, 1), srcuser, dstuser;
select t, srcuser, dstuser, size
from mail
order by if(srcuser=dstuser, 0, 1), srcuser, dstuser;
select t, srcuser, dstuser, size
from mail
order by if(srcuser=dstuser, null, srcuser), dstuser;
----------------------按照用户定义排序-------------------------
select *, field(name, 'Henry', 'Suzi', 'Ben') from driver_log
order by field(name, 'Henry', 'Suzi', 'Ben');
select id, description, field(right(id, 2), 'US', 'UK', 'JP', 'SG') from housewares
order by field(right(id, 2), 'US', 'UK', 'JP', 'SG');
----------------------排序枚举数值-------------------------
--创建表
drop table id exists weekday;
create table weekday
(
day enum('Sunday','Monday','Tuesday','Wednesday',
'Thursday','Friday','Saturday')
);
--初始化数据
insert into weekday (day) values('Monday'),('Friday'),
('Tuesday'),('Sunday'),('Thursday'),('Saturday'),('Wednesday');
select * from weekday;
select day, day+0 from weekday;
select day, day+0 from weekday order by day;
select day, day+0 from weekday order by cast(day as char);
--创建表
drop table if exists color;
create table color
(
name cahr(10)
);
insert into color (name)
values('blue'),('green'),('indigo'),('orange'),('red'),('violet'),('yellow');
select name from color;
select name from color order by name;
select name from color order by field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
select name, field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet')
from color
order by field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
alter table color
modify name
enum('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
select name from color;
select name from color order by name;
---------------------------------------------------------
-------------------------生成摘要------------------------
---------------------------------------------------------
select * from states order by name;
---------------------使用count函数生成摘要--------------------
select count(*) from driver_log;
select count(*) from states;
select table_rows
from information_schema.tables
where table_schema = 'cookbook'
and table_name = 'states';
show tables status;
select count(*) from driver_log where miles > 200;
select count(*) from driver_log where name = 'Suzi';
select count(*) from states where statehood < '1900-01-01';
select count(*) from states where statehood between '1800-01-01' and '1899-12-31';
select count(if(dayofweek(trav_date)=7, 1, null)) as 'Staturday trips',
count(if(dayofweek(trav_date)=1, 1, null)) as 'Sunday trips'
from driver_log;
select count(if(dayofweek(trav_date) in (1, 7), 1, null)) as 'weekend trips',
count(if(dayofweek(trav_date) in (1, 7), null, 1)) as 'weekday trips'
from driver_log;
create view trip_summary_view as
select count(if(dayofweek(trav_date) in (1, 7), 1, null)) as 'weekend trips',
count(if(dayofweek(trav_date) in (1, 7), null, 1)) as 'weekday trips'
from driver_log;
select * from trip_summary_view;
