常用SQL

1. CEILING 向上取整
2. FLOOR 向下取整
3. FORMAT
mysql> SELECT FORMAT(12332.123456, 4);
-> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
-> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
-> '12,332'

4. concat 连接函数
5. insert
current_date
CURDATE()
now()
timestampdiff
year()
month()
dayofmonth()

date_add
下个月过生日
month(birth) = month(date_add(curdate(), interval 1 month))
where month(birth)= mod(month(curdate()), 12) + 1

搜索以小写b开头的记录
select * from pet where name regexp binary '^b'
搜索以fy结尾的记录
select * from pet where name regexp 'fy$'
搜索包含w的记录
select * from pet where name regexp 'w'

SELECT * FROM pet WHERE name REGEXP '^.....$';
SELECT * FROM pet WHERE name REGEXP '^.{5}$';

SELECT DATABASE();
SHOW TABLES;
DESCRIBE pet

批量模式
source filename

 

use test1
show tables;

SHOW TABLES

create table pet
(
name varchar(20),
owner varchar(20),
species varchar(20),
sex char,
birth varchar(20),
death varchar(20)
)

load data local infile 'E:\\a.txt' into table pet
select * from pet;

CREATE TABLE event (name VARCHAR(20), date DATE,
type VARCHAR(15), remark VARCHAR(255));

load data local infile 'E:\\event.txt' into table event
select * from event;

select pet.name,
timestampdiff(year,birth,date) as age,
remark
from pet inner join event
on pet.name = event.name
where event.type = 'litter'

select * from pet;

SELECT DATABASE();
desc pet;
SHOW INDEX FROM pet
source 'E:/mysqlTestScript/mysql.sql'

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

 

SHOW VARIABLES LIKE '%iso%'         #查看隔离级别
SHOW VARIABLES LIKE 'AUTOCOMMIT';   #查看是否自动提交

SET AUTOCOMMIT=1;                   # 1 或者ON标识启用 0或者OFF表示禁用

SHOW TABLE STATUS LIKE '%关键字%'; #0显示表的相关信息

SELECT VERSION();  查询数据库版本

 

mysql 学习网站

http://dev.mysql.com/doc/

http://www.cnblogs.com/kerrycode/p/3866174.html