SQL语句

检索数据 ：

SELECT id,age,name FROM Author

SELECT DISTINCT age FROM Autho 

SELECT DISTINCT user, name FROM one

是去除 one 表中 user 和 name 组合的重复数据。也就是说，如果 one 表中有多个行，它们的 user 和 name 值都相同，那么在执行上述 SQL 查询后，只会返回一条结果。

需要注意的是，SELECT DISTINCT 去重是基于指定的列组合进行的，如果只想去除 user 列中的重复数据，可以使用 SELECT DISTINCT user FROM one，这样只会返回不同的 user 值。

SELECT id,name,age FROM Author
LIMIT 2;

SELECT id,name,age FROM Author
LIMIT 4 OFFSET 2; 

#从第三行开始返回四条数据

补充：

补充

SELECT user,name FROM one LIMIT 0,3

#从第一行开始返回三条数据

SELECT id,name,age FROM Author  # 注释
LIMIT 1 OFFSET 0;       --注释
使用 # 进行注释时，需要确保注释符号前后都有空格，以免引起语法错误

/* 这是一个块注释
可以跨行
*/

排序检索数据

SELECT id,name,age FROM Author  
ORDER BY age;（放最后） 

SELECT id,name,age FROM Author  
ORDER BY age DESC;

过滤数据

SELECT id,name,age FROM Author  
WHERE age=34;

SELECT id,name,age FROM Author  
WHERE age BETWEEN 30 and 60;

高级数据过滤 

SELECT id,name,age FROM Author  
WHERE age BETWEEN 条件1 and 条件2;

SELECT id,name,age FROM Author  
WHERE age BETWEEN 条件1 or 条件2; 

SELECT id,name,age FROM Author  
WHERE age BETWEEN （条件1 or 条件2）AND 条件3;

SELECT id,name,age FROM Author  
WHERE age IN  ('34');

SELECT id,name,age FROM Author  
WHERE age NOT IN ('54')
ORDER BY age DESC;

同配符过滤

SELECT id,name_s,age FROM Author  
WHERE name_s LIKE '冰%';

SELECT id,name_s,age FROM Author  
WHERE name_s LIKE '%树%';

SELECT id,name_s,age FROM Author  
WHERE name_s LIKE '_iao bai on_';    #匹配一个字符

创建计算字段

SELECT id,name_s,age,CONCAT(name_s,'(',age,')') FROM Author;

SELECT id,name_s,age,CONCAT(name_s,'(',age,')') 
AS info
FROM Author;

SELECT id,name_s,age,id*age
AS info
FROM Author;

使用数据处理函数

SELECT id,UPPER(name_s),age,id*age
AS info
FROM Author;

SELECT id,UPPER(name_s),age,LENGTH(name_s)
AS len
FROM Author;

汇总数据：（聚合函数）

SELECT AVG(age) AS avv
FROM Author;

SELECT COUNT(age) AS tang
FROM Author
WHERE age=34;

SELECT MAX(age)AS tang
FROM Author

SELECT MIN(age) AS tang
FROM Author

SELECT SUM(age) AS tang
FROM Author

SELECT SUM(age*12) AS tang
FROM Author

聚集不同值 ：

SELECT SUM(age) AS tang
FROM Author;

SELECT SUM(DISTINCT age) AS tang
FROM Author;

SELECT AVG(age) AS tang
FROM Author;

SELECT AVG(DISTINCT age) AS tang
FROM Author;

组合聚集函数：

SELECT 
    COUNT(*) AS hanshu,
    AVG(age) AS tang,
    MAX(age) AS max_1,
    MIN(age) AS min_1    
FROM Author;