基础的SQL操作

整理一些基础的SQL操作，留存备忘。

1.sql语句单行不用书写结束符号，但是多行需要在末尾添加一个 ；

1.基本查询

SELECT * FROM students

表示从 students这个表中查询整个表的所有资料，在现在主流的关系性数据库中，以一种二维的形式展示出来

2.条件查询

SELECT * FROM TABLENAME WHERE COLUMN = ‘条件’;
SELECT * FROM TABLENAME WHERE COLUMN <= ‘条件’;
SELECT * FROM TABLENAME WHERE COLUMN >= ‘条件’;
SELECT * FROM TABLENAME WHERE NOT COLUMN = ‘条件’;
SELECT * FROM TABLENAME WHERE COLUMN ！= ‘条件’;
SELECT * FROM TABLENAME WHERE COLUMN <> ‘条件’;
SELECT * FROM TABLENAME WHERE COLUMN LIKE  ‘条件%’;
SELECT * FROM TABLENAME WHERE COLUMN LIKE  ‘%条件%’;
SELECT * FROM TABLENAME WHERE COLUMN BETWEEN ‘条件1’ AND ‘条件2’;
SELECT * FROM TABLENAME WHERE COLUMN BETWEEN ‘条件1’ OR ‘条件2’;
SELECT * FROM TABLENAME WHERE COLUMN BETWEEN （‘条件1’ OR ‘条件2’） AND ‘条件3’;

3.数据库连接测试

SELECT 1

4.投影查询

SELECT COLUMN1，COLUMN2 FROM TABLENAME WHERE COLUMN = ‘条件’;

5.结果排序

SELECT COLUMN1，COLUMN2 FROM TABLENAME WHERE COLUMN = ‘条件’ ORDER BY COLUMN3;
SELECT COLUMN1，COLUMN2 FROM TABLENAME WHERE COLUMN = ‘条件’ ORDER BY COLUMN3 DESC;
SELECT COLUMN1，COLUMN2 FROM TABLENAME WHERE COLUMN = ‘条件’ ORDER BY COLUMN3 DESC LIMIT 100;

6多列结果聚合一列

SELECT CONCAT(COLUMN1,'-',COLUMN2) AS 别民 FROM TABLENAME ORDER BY COLUMN3 DESC LIMIT 1

7.分页查询，个人认为这个适用于table js库不是一次性加载查询结果时，而是服务器在执行下一页等操作时实时刷新可以使用

 SELECT CONCAT(COLUMN1,'-',COLUMN2) AS 别民 FROM TABLENAME ORDER BY COLUMN3 DESC LIMIT 1 OFFSET 100

8.聚合查询

SELECT COUNT(*) AS 别民 FROM TABLENAME ORDER BY COLUMN3 DESC;
SELECT SUM(*) AS 别民 FROM TABLENAME ORDER BY COLUMN3 DESC;
SELECT AVG(*) AS 别民 FROM TABLENAME ORDER BY COLUMN3 DESC;
SELECT MAX(*) AS 别民 FROM TABLENAME ORDER BY COLUMN3 DESC;
SELECT MIN(*) AS 别民 FROM TABLENAME ORDER BY COLUMN3 DESC;
SELECT COUNT(*) AS 别民 FROM TABLENAME GROUP BY COLUMN;

9.多表查询

SELECT TABLENAME1.COULUMN1,TABLENAME2.COULUMN2 FROM TABLENAME1,ABLENAME2

10.连接查询，重点！！！！

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

10.1内连接

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

https://www.liaoxuefeng.com/wiki/001508284671805d39d23243d884b8b99f440bfae87b0f4000/001509167103179399448cb200549bdab7651a5e9167597000