Mysql notes - universe42

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';