查询表class
SELECT * FROM class
查询表class里studentid为1的学生
SELECT * FROM class WHERE studentid = 1
查询表class里studentid正序排列
SELECT * FROM class ORDER BY studentid ASC
查询表class里studentid倒序排列
SELECT * FROM class ORDER BY studentid DESC
查询表class studentid升序 grade降序
SELECT * FROM class ORDER BY studentid DESC,grade ASC
往class里插入这一行
INSERT INTO class VALUES(90,"周",85,"武当派",6)
更改class里no=90的这一行
UPDATE class SET NAME="令狐冲",grade=89 WHERE no=90
删除class里no=38的这一行
DELETE FROM class WHERE NO = 38
取class表里studentid正序排列前三名
sql SERVER 语法:
SELECT top 3* FROM class ORDER BY studentid
oracle 语法:
SELECT * FROM (SELECT c.*,c.rownum num FROM class as c ORDER BY c.studentid) as b
WHERE b.num<=3
mysql 语法:
SELECT * FROM class ORDER BY studentid LIMIT 3,3
取设计组,站岗组,轮滑组数据 按照grade排序 取第三名
mysql 语法:
SELECT * FROM class WHERE job="设计组" OR job="站岗组" OR job="轮滑组" ORDER BY grade LIMIT 2,1
SELECT * FROM class ORDER BY grade LIMIT 6,3
sql SERVER 语法:
SELECT * FROM (SELECT top 3* FROM class WHERE job="设计组" OR job="站岗组" OR job="轮滑组" ORDER BY grade DESC)b
WHERE b.grade NOT IN (SELECT top 2 grade FROM class WHERE job="设计组" OR job="站岗组" OR job="轮滑组" ORDER BY grade DESC)
oracle 语法:
SELECT * FROM (SELECT class.*,class.rownum num FROM class WHERE job="设计组" OR job="站岗组" OR job="轮滑组" ORDER BYgrade DESC) as b WHERE b.num=3
模糊查询LIKE
SELECT * FROM class WHERE name LIKE "赵%"
SELECT * FROM class WHERE name LIKE "赵__"
select * FROM class WHERE name NOT LIKE "赵"
SELECT * FROM class WHERE studentid IN(1,2)
等价于
SELECT * FROM class WHERE studentid =1 OR studentid =2
SELECT * FROM class WHERE studentid NOT IN(1,2)
SELECT * FROM class WHERE studentid BETWEEN 1 AND 2
select * FROM class WHERE studentid NOT BETWEEN 1 AND 2
1、求:有活动地点和活动人数的兴趣组
2、求:所有的人员信息及对应的活动表信息
SELECT DISTINCT club.`groupname` FROM class INNER JOIN club ON
class.`job` = club.`groupname` WHERE club.address LIKE "_%"
AND class.name LIKE "_%"
SELECT DISTINCT club.groupname FROM club INNER JOIN class ON
club.groupname=class.job
WHERE club.address !=" " AND class.`name`!=" "
SELECT * FROM class LEFT JOIN club ON class.job=club.groupname
求 名字叫令狐冲的几个人,叫司徒的几个人
求,人员表中,同一个名字大约1人的名字列表
SELECT COUNT(`name`) FROM class WHERE `name` IN
("令狐冲","司徒") GROUP BY `name`
SELECT `name` "名字",COUNT(`name`) "人数" FROM class
WHERE `name` in ("令狐冲","司徒") GROUP BY `name`
SELECT `name` FROM class GROUP BY `name` HAVING COUNT(`name`)>1
DISTINCT(去重)
只能在select中使用,具体的语法如下:
select distinct expression[,expression...] from tables [where conditions];
1.1 只对一列操作
这种操作是最常见和简单的,如下:
select distinct country from person
1.2 对多列进行操作
select distinct country, province from person
1.3 针对NULL
的处理
从1.1和1.2中都可以看出,distinct
对NULL
是不进行过滤的,即返回的结果中是包含NULL
值的。
1.4 与ALL
不能同时使用
默认情况下,查询时返回所有的结果,此时使用的就是all
语句,这是与distinct
相对应的,如下:
select all country, province from person
1.5 与distinctrow
同义
select distinctrow expression[,expression...] from tables [where conditions];
- 1
这个语句与distinct的作用是相同的。
1.6 对*
的处理
*
代表整列,使用distinct对*
操作
sql
select DISTINCT * from person
相当于
select DISTINCT id, `name`, country, province, city from person;