方便的函数
* length -- 字符长度
* concat -- 字符连接
* alias -- 别名
* substring -- 切割子字符串
* random -- 随机数
例子
> select player, length(player) from users;
> select player, concat(player, '/', team) from users;
> select player, concat(player, '/', team) as "球员信息" from users;
更改了列名
> select substring(team, 1, 1) as "球队首文字" from users;
> select concat('我', substring(team, 1, 1)) as "球队首文字" from users;
> select random();
> select * from users order by random();
随机打乱排序
> select * from users order by random() limit 1;
抽奖
变更表结构
alter table [tablename] ...
选择修改的表
alter column_name ...
选择修改表的那一部分
create index ...
添加索引
drop index ...
删除索引
例子
alter table users add fullname varchar(255);
alter table users drop fullname;
alter table users rename player to nba_player;
alter table users alter nba_player type varchar(100);
create index nba_player_index on users(nba_player);
drop index nba_player_index;
多表查询，联合
where
select users.player, twitters.content from users, twitters where users.id = twitters.user_id;
select u.player, t.content from users as u, twitters as t where u.id = t.user_id;
select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
注意：having 一般与 group by 合用
join ... on
连接两张表变成一张新表（通过 on 生成一个 组合表）
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
union
将两个查询结果合并成一个结果（union 两个 select 语句）
SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country;
会显示不同的结果
SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;
只显示一个结果
视图（定义为一个select语句）
select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
create view curry_twitters as select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
\dv
\d curry_twitters
select * from curry_twitters;
drop view curry_twitters;
\dv
事务
> select * from users;
> begin;
> update users set score = 50 where player = '库里';
> update users set score = 60 where player = '哈登';
> commit;
> select * from users;
> begin;
> update users set score = 0 where player = '库里';
> update users set score = 0 where player = '哈登';
> rollback;
> select * from users;
