mysql语句

mysql cookbook, 2ed edition, 一点笔记。
 
yum install mysql-server mysql-client mysql-devel
 
mysql不区分大小写
 
#service mysqld stop
#mysqld_safe --skip-grant-table
另起一个终端，mysql  -u root mysql
mysql> update user set password = password('q.,...') where user = 'root';
mysql> flush privileges;
mysql>quit
 
mysqladmin -u root -p password newpwd
 
grant all on dbname.* to username@'localhost' identified by 'password'
 
~/.my.cnf
[client]
host = localhost
user = username
password = pwd
[mysql]
pager = "/usr/bin/less -/E"
 
select version();
 
show databases;
use dbname;
 
show full columns from tbbkp;
desc tbbkp;
 
select distinct tn.city from tbname as tn where  
            colname <> .
            colname in ...
            colname between . and .
            not like
            order by oone desc, otwo asc,  
 
insert into tbname ( ... ) values ( ... )
 
top:
select top 50 [percent] ...
 
通配符：
%，一或多个
_，一个
别名，as可选
 
内连接
select * from tbphone ph inner join tbcus cus on ph.brand = cus.brand;相当于where，brand项列出
无相同项，同交叉连接
 
左连接，左外连接
select * from tbphone ph left outer join tbcus cus on ph.brand = cus.brand;
相当于where，brand项列出，右表没有的显示null
 
右连接，右外连接
select * from tbphone ph right outer join tbcus cus on ph.brand = cus.brand;
相当于where，brand项列出，左表没有的显示null
 
外连接
相当于左连接+右连接
 
交叉连接
笛卡尔积
 
自连接
select * from tbphone p, tbphone h where p.brand = h.brand;
 
union，查询的列数相同，数据类型相似
select brand from tbphone union select brand from tbcus;
union，显示重复的列
distinct 适用于所有查询列
 
select * from tbphone limit start, length 下标从0开始
 
select *，(price - 200) as curpri from tbphone order by curpri desc;
 
select * from tbphone where price between 1500 and 3000;包括1500和3000
 
insert into tbbkp select ph.brand, ph.price, cus.cname, cus.id from tbphone as ph, tbcus as cus where ph.brand = cus.brand;
 
create database dbname;
 
create table tbstu(
phonum int default 100,
 
id int primary key auto_increment,
primary key(id),
constraint multi_prikey primary key(phonum, id),
 
 
lover varchar(20) unique,
unique(lover),
constraint multi_unique unique(id, lover)，
 
city varchar(20),
foreign key(city) references tbcity(city),
constraint named_forkey foreign key(city) references tbcity(city),
 
score int,
check (score < 101 and score > 0),
constraint chk_score check (score < 101 and score > 0),
 
enschdate date default current_date,
 
color enum('yellow', 'blue', 'red'),
food set('bread', 'noddle', 'fruit'),
);
 
约束
    not null
    unique: not null
    primary key: unique, not null
    foreign key
    default
    check
unique:
alter table tbstu add unique(lover);
alter table tbstu add constraint multi_unique unique(id, lover);
alter table tbstu drop index multi_unique;
primary:
每个表只能一个主键
alter table tbstu add primary key(id)
alter table tbstu add constraint multi_prikey(phonum, id)
alter table tbstu drop primary key
foreign key:
alter table tbstu add foreign key(city) references tbcity(city)
alter table tbstu add constraint named_forkey foreign key(city) references tbcity(city)
alter table tbstu drop foreign key named_forkey
check:
alter table tbstu add check (score < 101 and score > 0)
alter table tbstu add constraint chk_score check (score < 101 and score > 0)
default:
alter table tbstu alter enschdate set default getdate()，错误，默认值必须为常量
alter table tbstu alter enschdate drop default
 
create [unique] index idx_name on tbstu(lover desc, city)
alter table tbstu drop index idx_name
 
drop table tbstu，删除表的结构、属性、索引
drop database dbstu
truncate table tbstu，删除表内数据，不删除表本身
 
alter table tbname add colname datatype after colage
alter table drop column colname
alter table tbname alter column colname datatype
 
alter table tbname auto_increment = 100，从100开始自增
 
alter table tbname rename tbnewname
 
view:
只存放定义，增加了一层抽象，降低底层变动对应用程序的影响，定制安全角色，简化逻辑
create/replace view view_name as select ...
drop view view_name

select ... from view_name

日期函数

…………………………………………………………

now()
curdate()
curtime()
date()
extract()
date_add（）
date_sub()
datediff()
date_format()
format：
date YYYY-MM-DD
datetiem YYYY-MM-DD HH:MM:SS
timestamp YYYY-MM-DD HH:MM:SS
time HH:MM:SS
year YYYY/YY
即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
year(dateval), month(dateval), day(dateval)获取年、月、日
 
null与0不等价
where address is not null
 
select name, count * (base + ifnull(benefit, 0)) from tbgoods
select name, count * (base + coalesce(benefit, 0)) from tbgoods
 
blob, binary large objects
 
基本函数  
aggregate，合计函数，操作一系列值，返回单一值
scalar函数操作面向输入的单一值，并返回期望的单一值
 
返回最后几个结果，order by, limit  
 
mysql cookbook 第二版 P182
avg(colname)，求平均数
count(distinct colname)，null不计数
first(vals)，vals中的第一个数
last(vals)，vals中的最后一个数
max()，求最大值
min()，求最小值，null不计
sum()，求和
group by
having，select name, sum(orders) from tborder group by name having sum(orders) > 100
ucase(word)，转为大写
lcase(word)，转为小写
mid(colname, start[,length])，在colname中从start提取length个字符
len(word)，获取字符串长度
round(colname, decimals)，四舍五入为指定的小数位数
now()，返回当前日期和时间
format(colname, "YYYY-MM-DD")，格式化字符串
 
select date_format(dt, "%Y-%M-%e") as birthday, concat(user, '@', machine) as email from tbuser
 
select null = null, null <=> null    结果，null和1
 
if(exprone, valone, valtwo):exprone? valone: valtwo等价于ifnull(valone, valtwo),if valone is null, return valtwo

show warnings;
... where indexed_field >=，这样效率高，假如用于一个表达式内，则每一行都会被执行，速度很慢呐，放任大好江山。
将 ... where year(vdate) >= 1987 and year(vdate) <= 1990
改为...where vdate >= '1987-01-01' and vdate <= '1990-12-31'

关注临界值
将 ... where month(vdate) = month(date_add(curdate(), interval 1 month))
改为...where month(vdate) = mod(month(curdate(), 12) + 1

不要要求mysql按要求的顺序存储数据，假如有多种顺序呢？定制查询结果

字符串排序大小写敏感
create table tbname(name char(3) character set . collate .
…………………………………………………………………………………………………………………………

只获取结果的前几行limit maxline，通常配合order by使用
select * from tbstu order by birthday limit 7

limit indexnum, cnt，第一个参数是从零开始的下标，第二个参数是个数

select sql_calc_found_rows * from tbphone limit 4;
select found_rows();

100 90 80 70 70
select @fourth := wins from al_winners order by wins desc limit 3, 1
select * from al_winners where wins >= @fourth order by wins desc

100 90 80 80 70 70
select @dis_fourth := distint wins from al_winners order by wins desc limit 3, 1
select name, wins from al_winners where wins >= @dis_fourth order by wins desc, name

limit返回排序后的个数，假如要求另外一种顺序
select * from (select name, birth from by tbstu order by birth desc limit 4) as tmptbname order by birth
from之后必须是表名

表管理
………………………………………………………………………………………………………………
create table newtbname like originaltbname
create dst_tb(id int auto_increment primary key, index(a, b)) select a, b, c as cc from src_tb where 0
insert into tbname[(field1, field2)] select ...
insert into dst_tb(i, s) select count(name), name from src_tb group by name
create temporary table tbtmp ...，与数据库连接结束后，自动删除临时表
drop temporary table if exists tbname

\G每个字段打印到行
select engine from information_schema.tables where table_schema = 'dbname' and table_name = 'tbname'
show table status like 'tbname'\G
show create table tbname\G
alter table tbname engine = InnoDB
select connection_id()

字符
………………………………………………………………………………………………………………
length()返回字节数
char_length()返回字符数
char, binary自动填补null(0)，存储含有null的数据时，使用varchar, varbinary
select 'my friend''s name is ding'
select user(), charset(user()), collation(user())
select user(), concat(upper(left(user(), 1)), mid(user(), 2))，首字母大写
create function first_char_upper(str varchar(255)) returns varchar(255) deterministic return concat(upper(left(str, 1)), mid(str, 2)); select first_char_upper(user())
select version()

like, not like: %零或多个,_一个
模式匹配速度更快
year(d) = 1976 -- d like '1976-%'
month(d) = 4   -- d like '%-04-%'
str like 'abc%'-- left(str, 3) = 'abc'
str like '%abc'-- right(str, 3) = 'abc'

正则匹配
p1 | p2 | p3
. 任意
* >=0
+ >=1
{m, n}
正则表达式只能用于单字节字符集

select * from tbname where address regexp '^..zhou$'
select * from tbname where address not regexp '[:space:]'
[:almum:]
[:alpha:]
[:digit:]
[:xdigit:]
[:graph:]
[:print:]
[:punct:]
[:upper:]
[:lower:]
[:space:]
[:cntrl:]
[:blank:]

substring_index(string, char, cnt)大小写敏感
locate(pattern, text, start_pos)

alter table tbname add fulltext(field1, field2 ... )
建立表后再建立索引，比创建带索引的表后再添加数据，速度更快
select count(*) from tbname where match(field1, field2 ...) against('ding', 'lang' ...)
against()中使用or进行查找，增加查找条件，返回的条目更多
/etc/my.cnf 添加[mysql] ft_min_word_len=3
select count(*) from tbname where match(ftfield) against('david') and against('knuth')
select count(*) from tbname where match(ftfield) against('+david* -knuth' in boolean mode)
select count(*) from tbname where match(ftfield) against('"still small voice"' in boolean mode)

日期
……………………………………………………………………………………………………………………
... where vdate between '1994-10-24' and curdate()

顺序
…………………………………………………………………………………………………………………………
order by可以使用别名
将字符串转为字符，忽略不能转换的非数字字符，返回最长有效字符转成的数字
substring_index(str, char, cnt)，返回之前cnt个char之前的字符串，cnt为负数则从右边匹配，返回右边的字符串