数据库sql实战(61题)自我整理

一、关于查找

基本SQL: 
select [name1,n2...*] from 表名 where 条件；

###查找出现次数###
--查找字符串'10,A,B' 中逗号','出现的次数cnt
--select (length('10,A,B')-length(replace('10,A,B',',','')))as cnt;
######

###查询按照字段最后两个字母排序###
--获取tablename1中的name1，查询按照name1最后两个字母，按照升序进行排列
--select name1 from tablename1 order by substr(name1,length(name1)-1);
######

###eg:选择查找###
select e.emp_no,e.first_name,e.last_name,b.btype,s.salary,
(case b.btype
when 1 then s.salary*0.1
when 2 then s.salary*0.2
else s.salary*0.3 end) as bonus
from employees e inner join emp_bonus b on e.emp_no=b.emp_no inner join salaries s on
s.emp_no=e.emp_no and s.to_date = '9999-01-01';
######

二、关于函数

取最大：max    --max(name1)
                        --max(date1)--取最晚日期

取最小：min     --min(id)

计数：count     --count(name1)
                        --count(name1) as t

累加：sum        --sum(t1.salary1)

###group by###
group by  --group by [salary1(以其为基准分组，每组显示算一个数据)] order by salary desc;--相同数据只显示一次，降序排序
######
###having--存在###
having      --group by [name1] having t>15;--每组中存在t>15的情况；
                 --group by category_id having count(film_id) >= 5) as cc,
######

去重复：distinct   --count(distinct name1) as t;

取平均数：avg     --avg(salary1) as avg1;

###时间函数###
在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
strftime:  --from tablename1 t1,tablename1 t2
                  where strftime('%Y',s2.to_date) - strftime('%Y',s1.to_date) = 1
               --每年
######

查看执行计划
explain     --explain select * from tablename1;


###替换###
replace
--replace into tablename1 values('x', 'xxxxx', 'xxxxxxxxx', 'xxxx-xx-xx', 'xxxx-xx-xx');
--将name_id(主键)=x以及name2=xxxxx的行数据替换成name_id=x以及name2=yyyyy,其他数据保持不变，使用replace实现。
######


汇总：group_concat
--select group_cancat(name1) from tablename1 group by name1;

三、关于排序

降序：desc  --order by name1 desc;
                   --order by name1 desc limit 2,1;

###limit###
limit m,n    --表示从第m+1条开始，取n条数据；
linit  n        --表示从第0条开始，取n条数据，是limit(0,n)的缩写
######

升序：asc  --order by name1 asc;
                 --order by t1.salary1 desc,t1.name1 asc;

四、关于代替

as:     --tablename as t   ==   tablename t
         --t1.name1 as n1

五、关于连接

内连接：以两表共同的列名为基准，建立基准中相同数据的数据集
inner join   --tablename1 t1 inner join tablename2 t2 on t1.name=t2.name;

左连接：以两表共同的列名为基准，建立基准中相同数据的数据集，但包括左表全部数据
left join      --tablename1 t1 left join tablename2 t2 on t1.name=t2.name;

六、关于存在

is null(为空)   --where n1.name1 is null;

not in(不存在)  --where salary1 not in (select max(salary1) from tablename1);

not exists(不存在) --where not exists (...);

<>/!=(不等于)    --where t1.name <>/!= t2.name

like-模糊查询      --where t1.name1 like '%xxxx%';

七、关于分组

group by  --group by name1;

八、关于符号

%    --where name1 %2 = 1;
''     --where name1 != 'xxxx';
-     --select max(salary1)-min(salary1) as growth
<= --where t1.salary1 <= t2.salary2

拼接：
||""||     --select name1||" "||name2 from tablename1;--拼接name1和name2中间以一个空格区分

九、关于创建

创建表：
基本语句：
create table tablename
(    
      id1 smallint(5) not null,
      name1 varchar(45) not null,
      name2  varchar(45) not null,
      name3 int not null,
      name4 datetime not null,
      time1 timestamp not null default(datetime('now','localtime')),
      --最后更新时间，默认是系统的当前时间
      primary key(id1)，
      --创建外键约束
      foreign key(name1) references tablename2(name0)
);

###增加一列###
--增加一列名字为name1, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'
alter table tablename1 add column name1 datetime not null default '0000-00-00 00:00:00';
######

###更改表名###
alter table tablename1 rename to tablename_new;
######


创建索引：
基本语句：
create unigue index index_name1 on tablename1(name1);
--对name1创建唯一索引index_name1

create index index_name2 on tablename2(name2);
--对name2创建普通索引index_name2

###通过索引查询###
--针对tablename1表name1字段创建索引index_name1，查询name1为xxxxx, 使用强制索引

create index index_name1 tablename1(name1);

select * from tablename1 indexed by index_name1 where name1 = 'xxxxx';
######



创建视图
基本语句：
create view name_view (name1_v,name2_v) as
select name1,name2 from tablename;
--针对tablename表创建name_view,只包含name1以及name2两列，
   并对这两列重命名，分别为name1_v,name2_v；

创建触发器
基本语句：
create trigger name1_log after insert on tablename1
begin
        insert into tablename2 values(new.id,new.name);
end;

--构造一个触发器name_log，在向tablename1表中插入一条数据的时候，触发插入相关的数据到tablename2中。


###sql生成###
--针对库中的所有表生成select count(*)对应的SQL语句
--SELECT "select count(*) from " || name || ";" AS cnts
   FROM sqlite_master WHERE type = 'table'
######

十、关于插入

基本语句：
insert into tablename
values(x,'xxxxxxxx','xxxxxxx','xxxx-xx-xx xx:xx:xx'),
(y,'yyyy','yyyyyyyy','yyyy-yy-yy yy:yy:yy');

insert or ignore into tablename values(xxx);
--如果数据已经存在，请忽略，不使用replace操作

insert into tablename1 select name1,name2 from tablename2;
--将tablename2表中的name1,name2所有数据导入tablename1表中；

十一、关于删除

基本语句：

delete from tablename1 where ......;

###删除表###
drop table tablename;
######

十二、关于更新

基本语句：
update tablename set name1 = null,name2 = 'xxxx',salary1 = salary1*1.1 where name1 = 'xxxx';
#SQL Server#