SQL语句基础练习

select * from students; select: 查询
select distinct name from students; distinct: 去重复值
select name from students where name = '小王'； where: 判断条件
select * from students where score = 88 and gender = 'F' ; and: 判断多个条件同时成立
select * from students where score = 88 or gender = 'M' ; OR: 判断某一个条件成立
select * from students where (score = 81 or score = 90) and gender = 'M'; and/or: 可以同时使用
select name,score from students order by score; order by:以...排序，默认升序展示，desc倒序
select name,score from students order by score desc, name asc; order by:以...排序，默认升序展示，desc倒序；asc升序
insert into students value("beijing","nanjing","xiamen","shanghai","hangzhou"); insert into 插入数据
insert into students (列1，列2) value ("beijing",90)； 对指定的列进行插入数据
update students set gender = 'F' where name = '小明' 更新修改表中的值，
delete from students where name = '小明' 删除表中某一行的数据
select top 50 percent * from students 查询出表中50%的数据
select top 2 * from students 查询出表中前两行的数据
select * from students where score like '8%' 查询分数列下8开头的行
select *from students where score like '%3' 查询分数列下以3结尾的行
select * from students where score like '%zhou%' 查询分数列下包含zhou的行
select * from students where score not like '%zhou%' 查询分数列下不含喊zhou的行
select * from students where name like '_明' 查询姓名列下第一个字符后是明的行
select * from students where score like '[fsl]%' 查询分数列下以f，s，l，等开头的行
select * from students where score like '[!fl]%' 查询分数列下不以f,l开头的行
select * from students where name in ('小明','小红') 查询姓名列下有小明和小红的行
select * from students where score between 90 and 88 查询分数列下在90和88之间的行（包前不包后）
select * from students where score not between 90 and 88 查询分数列下不在90和88之间的行
select name as M from students where name = '小明' as后面跟别名
select students.name,students.score classes.name from students inner join classes on students.id = classes.id order by students.name 内连接 inner join 和join 是一样的
select students.class_id,students.score,classes.name from students left join classes on students.id=classes.id order by student.class_id 左连接 返回左表的行
select students.class_id,students.score,classes.name from students right join classes on students.id=classes.id order by students.score 右连接 返回右表的行
select students.score,classes.name from students full outer join classes on students.id=classes.id order by students.score 全连接 返回左右两个表的行，

select avg(score) as scoreavg from students 求 分数列下的平均值
select score from students where score > (select avg(score) from students) AVG（）指定列下的平均值 求分数列下大于分数的平均值的行
select count(score) as scorecount from students where score = 88 count() 指定列下的总行数 求分数列下为88的总行数
select count(*) as scorecount from students count() 指定列下的总行数 求表中的总函数
select count(distinct score) as scorecount from students count() 指定列下的总行数 求分数列下不同的分数数目
select first(score) as firstscore from students first() 指定列下第一个值 求分数列下第一个值
select last(score) as lastscore from students last() 指定列下最后一个值 求分数列下最后一个值
select max(score) as maxscore from students max() 指定列下最大的值 求分数列下最大的值
select min(score) as minscore from students min() 指定列下最小的值 求分数列下最小的值
select sum(score) as sumscore from students sum() 指定列下的总数 求分数列下值的总和
select name,sum(score) as sumscore from students group by name; group by 针对合计函数进行分组 求不同学生的分数总和
select name,sum(score) as sumscore from students where name='小红' group by name having sum(score)> 90 having 针对合计函数进行使用 求小红的总分数，大于90分以上
select name,score,Now() as TODAY from students now() 当前时间