Mysql notes

1. 数据库操作 database management

create database sampleDatabase;  --创建数据库sampleDatabase
show databases;    --显示已有的数据库
use sampleDatabase;   --开始使用sampleDatabase
drop database sampleDatabase;   --删除

2. 表操作 table management

create table sampletable(
id int(10) not null auto_increment;
val varcahr(50) ;
primary key (id)
);   --创建表sampletable

--改变表结构操作alter
alter table sampletable add column start_time date after id;  --在id列后添加start_time列
alter table sampletable change column val val varchar(100) not null;  --更改var列的设置
alter table sampletable drop column start_time  --删除start_time列
alter table sampletable rename to sample； --表名改为sample

 3. 选择某列第n大的值

select * from sampleTable     --选择表sampleTable
order by targetColumn desc    --按照targetColum降序排列
limit n-1,1                   --选择第n行的数据（注意这里n-1要为计算后的常量，否则出错）

--function definition
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  declare m int;
  set m = n-1;
  RETURN (select distinct salary from Employee order by salary desc limit m,1);
END

4. 聚合函数

select avg(columnA), sum(columnA), count(columnA),  min(columnA), max(columnA)
from sampleTable;

 5. DATE操作

select datediff('2011-08-17','2011-08-08')  --前面日期-后面日期 = 9 days
select (now() - INTERVAL 1 DAY) 'NOW - 1 day',
        now() 'now ',
       (now() + INTERVAL 1 DAY) 'NOW + 1 day';

 

Reference: Mysql Tutorial