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