数据语句

-- 创建数据库

create table *(id integer primary keyautoincrement,

＊text,

*  real,

*  integer)

 

-- 基本查询

select * from pet

 

-- 列出指定的列

select name, owner form pet

 

-- 直接进行算术运算，对字段起别名

select sin(1+2) as sin

 

--where 条件

select * from pet where (birth>'1980' and species='dog')or species='bird'

 

-- 对null 的条件

select * from pet where sex is not null

 

-- 所有名字第四位是n 的宠物信息是

select * from pet where owner like '___n%'

 

 

-- 所有主人名叫gwen 或benny 的宠物

select * from pet where owner in ('gwen' , 'benny')

 

-- 查询出生日期在90 年代是宠物，相当与 >= and   <=

select * from pet where birth between '1990' and'1999'

 

-- 按主人姓名排序，相同的按宠物姓名倒序排列

select * from pet order by owner, name desc

 

-- 查询性别为公的宠物，按生日倒序排列

select * from pet where sex='m' order by birth desc

 

--char_lenngth() 返回的字符的长度，length() 返回字节长度

SELECT owner,length(owner),char_length(owner) FROMpet p;

 

 

 

-- 列出养有宠物狗的人名

select distinct owner from pet where species='dog'

 

-- 用两种方法查询出所有狗和猫的名字、出生年份、出生月份

select name, left(birth,4) as year, mid(birth, 6,2) as month from pet

where species='dog' or species='cat'

 

select name, year(birth) as year, month(birth) asmonth from pet

where species in('dog','cat')

 

-- 查询所有名字中存在字母'e' 的人，将他们养的宠物按类别、年龄排序

select name, species, birth

from pet

where owner like '%e%'

order by species,birth desc

 

-- 数字函数

select round(2.345,2), truncate(2.345,2),mod(323,5)

 

-- 日期函数

select now(), curdate(), curtime()

 

select adddate('2007-02-02', interval 31 day)

 

-- 求出所有宠物的年龄

select name,birth,

truncate(datediff(now(),birth)/365,0) as age1,

year(now())-year(birth) - (dayofyear(birth)>dayofyear(now()))as age2

from pet

 

-- 分组函数

selectmin(birth),max(birth),avg(birth),count(*),count(sex),

sum(birth)

from pet

 

-- 每种宠物各有几只

select species,count(*)

from pet

group by species

 

-- 查询年龄最大的宠物的信息

select * from pet where birth =

   (selectmax(birth) from pet)

 

-- 每年各出生了几只宠物

select year(birth), count(*) from pet group byyear(birth)

 

-- 鸟和猫的性别比例

select species, sex, count(*)

from pet

where species in ('cat','bird')

group by species, sex

 

-- 各种宠物年龄的和

select species, sum(truncate(datediff(now(),birth)/365,0))as SumAge

from pet

group by species

 

-- 数量大于1 的宠物种类

select species, count(*) as c

from pet

group by species

having c>=2

 

-- 基本双表关联

select a.name,a.species, a.sex,b.date, b.type,b.remark

from pet a,event b

where a.name = b.name

 

-- 查询宠物产仔时的年龄

select a.name, a.species,

truncate(datediff(b.date,a.birth)/365,0) as age

from pet a,event b

where a.name = b.name and b.type='litter'

 

--90 年代出生的狗的事件列表

select a.name,birth,species,sex,date,type,remark

from pet a,event b

where a.name=b.name and birth between '1990' and'1999'

and species='dog'

 

-- 活着的宠物按发生的事件类型分组，看各种事件发生的次数

select type, count(*)

from pet a, event b

where a.name=b.name and a.death is null

group by type

 

-- 记录的事件数量超过1 条的宠物信息

select a.name,species,sex,count(*)

from pet a, event b

where a.name = b.name

group by b.name

having count(*)>=2

 

-- 列出发生了两件事情的宠物的事件记录信息

selecta.name,type,date,remark,b.species,b.sex,b.owner

from event a, pet b

where a.name=b.name and

   b.name in

   (

select name

from event

group by name

having count(*)=2

   )

 

 

-- 插入语句

insert into pet (name,species,birth)

values ('KKK','snake','2007-01-01');

 

insert into pet

values ('KK','Diane','cat','f',null,null);

 

insert into pet set name='k',owner='Benny'

 

 

-- 更新语句

update pet set species='snake',sex='f',birth=now()

where name='k'

 

-- 将事件表中生日的日期，更新到pet 表中相应宠物的birth 字段

update pet a

set birth = (

            select date

            from event b

            where a.name=b.name and b.type='birthday'

         )

where a.name in (

              select name

              from event

              where type='birthday'

            )

 

 

-- 删除语句

delete from pet where name like 'k%'