关于sql数据库的命令操作

SELECT * FROM class

SELECT DISTINCT studentid FROM class

SELECT * FROM class WHERE studentid=1

SELECT * FROM class WHERE studentid=1 OR studentid=2

SELECT * FROM class WHERE studentid=2 AND job="站岗组"

//排序写法  ASC正序   ORDER BY 是排序
SELECT * FROM class ORDER BY studentid ASC

/排序写法  desc倒序   ORDER BY 是排序
SELECT * FROM class ORDER BY studentid DESC

SELECT * FROM class ORDER BY grade DESC

//以studentid倒叙如果与grade有相同的就倒叙
SELECT * FROM class ORDER BY studentid DESC,grade

INSERT INTO class VALUES(35,"赵哥",92,"设计组",11)

UPDATE class SET NO=99,NAME="聚聚",grade=99,job="射击组",studentid=100 WHERE NO=99

DELETE FROM class WHERE NO=22

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 0,6

SELECT * FROM class WHERE job="设计组" OR job="站岗组" OR job="轮滑组" ORDER BY grade DESC LIMIT 2,1
取设计组，站岗组，轮滑组数据，按照grade倒序，取第3名
mysql
SELECT * FROM class WHERE job ="设计组" OR job ="站岗组"OR
job ="轮滑组" ORDER BY grade DESC LIMIT 2,1

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 BY grade
 DESC) AS b WHERE b.num=3

模糊查询
SELECT * FROM class WHERE NAME LIKE "赵%"

模糊查询查姓赵的值只有一个字`class``class``class`
SELECT * FROM class WHERE NAME LIKE "赵_"
模糊查询查不出不行找的
SELECT * FROM class WHERE NAME NOT LIKE "赵"

SELECT * FROM class WHERE NAME LIKE "孙"
 
SELECT * FROM Persons  class WHERE NOT LIKE "%zhao%"

SELECT * FROM class WHERE studentid IN(1,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
别名
SELECT  NAME AS "名字",grade "id" FROM class

SELECT * FROM class

SELECT * FROM club

SELECT * FROM club WHERE groupid OR address

SELECT DISTINCT club.groupname FROM club INNER JOIN class ON club.`groupname`=class.`job` WHERE club.`address` LIKE "_%" AND class.`name` LIKE "_%"

SELECT * FROM club RIGHT JOIN class ON club.`groupname`=class.`job`

SELECT AVG(grade) FROM class WHERE NAME IN("令狐冲","赵哥哥")
SELECT SUM(grade) FROM class

SELECT * FROM class WHERE NAME LIKE "令狐冲%" OR WHERE NAME LIKE "司徒%"

SELECT NAME "名字" COUNT(`name`) FROM class WHERE `name` IN ("令狐冲","司徒") GROUP BY `name`

SELECT NAME FROM class GROUP BY NAME HAVING COUNT(NAME)>1

SELECT projectid,AVG(componentid),COUNT(supplyid) AS gyscount FROM SPI GROUP BY projectid HAVING gyscount>=3 ORDER BY projectid DESC

SELECT * FROM purchase

SELECT * FROM Sell

SELECT * FROM purchase RIGHT JOIN purchase purchase.A,purchase.B=Sell.`A`,Sell.`B`