SQL

SQL：
insert into person(name,city,age) values('J','nihaoshang',30)
truncate table newTable  //清空表
delete from newTable //清空表
delete from person where city='y angyuan' //删除表 person中字段 city为 y angyuan的记录
drop table newTable //删除表
create table newTable([city]char(20),[name]char(20),[age]int,[sex]char(10))
insert into newTable select * from person //复制就表的数据到新表 (两个结构完全相同的表 )
insert into newTable(city,name) select  city,name from person //复制部分相同字段的数据
select * from person where city like '%[a-z]%'  //查找 city中含字母的记录
select * from person where city not like '%[a-z]%'  //查找 city中不含字母的记录
select * from person where city like '%[0-9]%'  //查找 city中含数字的记录
select * from person where city not like '%[0-9]%'  //查找 city中不含数字的记录
select * from person where city like '%shang%' //查找 city中包含 shang的记录
select * from person where city like 'shang%' //查询 city中以 shang开头的记录
select * from person where city like '%shang' //查询 city中以 shang结尾的记录
select * from person where city like '_a_b_' //city中为 5个字符且第二四位分别是 a和 b的记录
select * from person order by age desc //按字段 age降序排列
select count(*) from person //得到记录的条数
select count(*) from person where age=30 //得到 age为 30的记录
select count(sex) from newTable group by sex having sex='man' //得到 sex字段为 man的记录数
select count(sex) as man from newTable group by sex having sex='man'
select count(*) as city_num from newTable group by city
select count(city) as city_num from newTable group by city
select city,sum(age) from newTable group by city//按 city字段进行分组 ,求每个 city的 ag合计
//查找和 zhao在同一个城市的人的名字 (包括 zhao本人 )
select  name from newTable where city in (select city from newTable where name='zhao') 或
select  name from newTable where city = (select city from newTable where name='zhao')
select student, avg(score) from SC group by student //得到表 SC中每个学生的名字和平均分数
//得到表 SC中有两门不及格的学生的名字和平均分数
select student, avg(score) from SC where student in (select student from SC where score<60 group by student having count(*)>=2) group by student
select course from SC group by course //得到表 SC中的全部 course
//得到 city为 beijing的记录的 city和 name，并把“ beijing”改为“北京”
select city='北京 ',name from newTable where city='beijing'
select distinct * from newTable //得到所有记录，所有相同的记录只显示一条
select distinct(city) from newTable where city='beijing'//得到 city为 beijing的记录，只显示一条
select * from newTable where age between 21 and 22 //得到 age在 21到 22之间的记录
select * from newTable where age>=21 and age<=22//同上
select * from newTable where age not between 21 and 22
select * from newTable where city is not NULL
select * from newTable where city != NULL //同上
select * from newTable order by age asc,name desc //按 age升序 ,age相同的再按 name降序排列
select name from person where age in (select max(age) from person) //得到 age最大的人的名字
update newTable set city='beijing' where name='zhao' //将 name为 zhao的记录的 city改为 beijing
select p.name,p.age,h.huo from person as p,order as h where p.name='B' and h.name ='B'
select p.name as '姓名 ',p.age as newage,h.huo as newhuo from person as p,order as h where p.name=h.name and h.huo='anpel'
//group 和 where 的位置不能颠倒
select student,sum(score)from SC where (student='zhang'or student='wang') and score>50 group by student
select name from newTable union select name from person
//得到成绩大于平均成绩的记录
select student,score as betterscore from SC where score>(select avg(score) from SC)
select count(student) as number from SC where student='zhang' //得到 zhang出现的次数
select count(*) from SC //得到表的记录数
select count(distinct student) from SC //得到不重复的 student的记录数
select first(student) from SC //得到字段 student的第一个记录值
select last(student) from SC //得到字段 student的最后一个记录值
select max(score) from SC //得到 score的最大值
select min(score) from SC //得到 score的最小值
select sum(score) from SC
//列出每位同学的总分数，并以降序排列
select student,sum(score) as allscore from SC group by student order by allscore desc
//列出 zhang的总分数
select student,sum(score) as allscore from SC group by student having student='zhang'
select ucase(student) from SC //将 student字段的值全部改为大写
select lcase(student) from SC //将 student字段的值全部改为小写
select mid(student,1,3) from SC //返回 student字段中每条记录的前三位值
select mid(student,2) from SC //返回 student字段中每条记录从第二位开始值
select len(student) from SC //返回 student每个值得长度
select round(score,1) from SC //获得 score，并保留一位小数
select student,score,now()as time from SC//用函数 now得到当前时间以 time列出