常用SQL语句
1. 查询 select
-- 查询 select
select * from tablename;
select t.name from tablename t where t.id='123' and t.state='1';
-- 指定别名
select name as username from tablename;
-- 统计数量
select count(1) from tablename;
select count(*) from tablename;
-- 查询一行
select * from tablename where rownum<=1;
-- 正序 asc | 逆序 desc
select t.name,t.state from tablename t order by t.name desc;
-- 去重 distinc
select distinc state from tablename;
-- 分组计数
select count(1),t.state from tablename t group by t.state;
-- 条件查询
-- in
select * from tablename where name in ('test1','test2');
-- like
-- 1. '%':表示0个或多个字符
select * from tablename where name like '%test%';
-- 2. '_':表示任意单个字符
select * from tablename where name like '_test_';
-- 3. '[]':类似正则表达式,指定字符、字符串或范围
select * from tablename where name like 'test[0-9]';
-- 4. '[^]':匹配对象为指定字符以外的任一个字符
select * from tablename where name like '[^test]';
2. 修改数据 insert update delete
-- 插入
insert into tablename (id, name, state) values (10,test,1);
commit;
-- 更新
update tablename set state='0' where id='10';
commit;
-- 删除
delete from tablename where state='2';
commit;
3. 修改表结构
# 新增列
alter table tablename add column updatetime VARCHAR(14) not null;
# 删除列
alter table tablename drop column updatetime;