SQL*

create database test;
use test;

create table mytable (
    # int 不可空，自增
    id int not null auto_increment,
    # int 不可控，默认为 1
    col1 int not null default 1,
    # 变长字符串，最长 45 字符，可为空
    col2 varchar(45) null,
    # 日期类型，可空
    col3 date null,
    primary key (`id`));

# 添加列
alter table mytable
add col char(20);
# 删除列
alter table mytable
drop column col;
# 删除表
drop table mytable;

# 普通插入
insert into mytable(col1, col2)
values(val1, val2);
# 插入检索出来的数据
insert into mytable1(col1, col2)
select col1, col2
from mytable2;
# 将一个表的内容插入到一个新表
create table newtable as
select * from mytable;

update mytable
set col = val
where id = 1;

delete from mytable
where id = 1;
# 清空表
truncate table mytable;

select distinct col1, col2
from mytable;

# 返回前 5 行
select *
from mytable
limit 0, 5; -- 0 可省略
# 返回 3 ~ 5 行
select *
from mytable
limit 2, 3;

select *
from mytable
order by col1 DESC, col2 ASC;

select *
from mytable
where col like '[^AB]%';

select col1 * col2 as alias
from mytable;

select concat(trim(col1), '(', trim(col2), ')') as concat_col
from mytable;

# order by 可根据汇总字段排序
select col, count(*) as num
from mytable
group by col
order by num;
# where 过滤行，having 过滤分组，行过滤应优先
select col, count(*) as num
from mytable
where col > 2
group by col
having num >= 2

select *
from mytable1
where col1 in (select col2
              from mytable2);
# 检索客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行依次：
select cust_name, (select count(*)
                   from Orders
                   where Orders.cust_id = Customers.cust_id)
                   as orders_num
from Customers
order by cust_name;

select A.value, B.value
from tablea as A inner join tableb as B
on A.key = B.key;

select A.value, B.value
from tablea as A, tableb as B
where A.key = B.key;

# 子查询
select name
from employee
where department = (
    select department
    from employee
    where name = "Jim");
# 自连接
select e1.name
from employee as e1 inner join employee as e2
on e1.department = e2.department
    and e2.name = "Jim";

# 检索所有顾客的订单信息，包括没有订单的顾客
select Customers.cust_id, Orders.order_num
from Customers left outer join Orders
on Customers.cust_id = Orders.cust_id;

select col
from mytable
where col = 1
union
select col 
from mytable
where col = 2;

create view myview as
select concat(col1, col2) as concat_col, col3 * col4 as compute_col
from mytable
where col5 = val;

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y * y into ret;
    end //
delimiter ;

call myprocedure(@ret);
select @ret;

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;
        # 声明游标，此时没有实际检索数据
        declare mycursor cursor for
        select col1 from mytable;
        # 定义一个 continue handler，当 sqlstate ‘02000‘ 条件出现时，执行 set done
        declare continue handler for sqlstate '02000' set done = 1;
        # 打开游标
        open mycursor;
        # 取数据
        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;
        # 关闭游标
        close mycursor;
    end //
delimiter ;

create trigger mytrigger after insert on mytable
for each row select NEW.col into @result;

start transaction
...
savepoint delete1
...
rollback to delete1
...
commit

create table mytable (
    # 还可以给列指定字符集和校对
    col varchar(10) character set latin collate latin1_general_ci)
default character set hebrew collate hebrew_general_ci;
# 可以在排序、分组时指定校对
select *
from mytable
order by col collate latin1_general_ci;

# 创建账户，新账户没有任何权限
create user myuser identified by 'mypassword';
# 修改账户名
rename user myuser to newuser;
# 删除账户
drop user myuser;
# 查看权限
show grants for myuser;
# 授予权限，账户以 username@host 形式定义
grant select, insert on mydatabase.* to myuser;
# 回收权限
revoke select, insert on mydatabase.* to myuser;
# 更改密码
set password for myuser = Password('new_password');