mysql 操作练习

查询表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;