SQL语句

数据库


1.删 DELETE  
DELETE FROM 表 WHERE 条件

2.增 INSERT
INSERT INTO (字段列表)  VALUES(值列表)

3.改 UPDATE      
UPDATE 表 SET 字段=值，字段=值，... WHERE 条件

4.查 SELECT
SELECT * FROM 表 WHERE 条件

-------------------------------------------------

SQL标准写法：

1.关键字大写
2.库、表、字段需要加上``

-------------------------------------------------

子句：

WHERE  条件

WHERE name='hao'
WHERE age>18
WHERE age<=18
WHERE age>=18 AND score<60
WHERE cach>100 OR score>1000


ORDER  排序

ORDER BY age ASC/DESC
    ASC-升序(从小到大)
    DESC-降序(从大到小)
ORDER BY price ASC

----------------------

*价格(price)升序排序，如果价格相同，再按销量(sales)降序排序
ORDER BY price ASC, sales DESC

----------------------

GROUP 聚类-合并相同

    COUNT、MIN、MAX、AVG、SUM


SELECT COUNT(*) FROM student_table


SELECT * FROM studen_table GROUP BY class;


SELECT class,COUNT(class) FROM student_table GROUP BY class; 


*统计每个班的平均分

//SELECT *　FROM　student_table GROUP BY class;

SELECT class,AVG(score) FROM student_table GROUP BY class;


*统计每个班的最高和最低分

SELECT class,MIN(score),MAX(score), FROM student_table GROUP BY class;


SELECT name,SUM(price) FROM sales_table GROUP BY name OREDER BY SUM(price) ASC;

----------------------


LIMIT   限制输出、分页

LIMIT 10; 前10条
LIMIT 5,8; 从5开始要8个


LIMIT (n-1)*x,x  分页
----------------------

子句之间是有顺序的

WHERE / GROUP / ORDER / LIMIT