mysql 使用基础 2

[code]
SELECT rank, AVG(salary) FROM people GROUP BY rank;

// 按照 rank 分组，并计算每个rank 薪水(salary)的平均值

SELECT rank, AVG(salary) FROM people GROUP BY rank
HAVING AVG(salary) > 1000;

// group by 分组后 使用 having 进行条件限制

SELECT rank, AVG(salary) FROM people
WHERE rank <> 'Private'
GROUP BY rank HAVING AVG(salary) > 10000;

逻辑运算符 AND, OR, NOT

SELECT user_name
FROM user
WHERE (AGE > 18) AND (STATUS='RESIDENT');

SELECT user_name
FROM user
WHERE (AGE >10 AND STATUS='RESIDENT')
OR (AGE > 10 AND STATUS='APPLICANT')

SELECT user_name
FROM user
WHERE NOT (AGE > 18 AND STATUS='RES'

SELECT title
FROM book
WHERE author IS null;

SELECT title
FROM book
WHERE author IN ('Stephen King', 'Richard');

//检查某个值是否在范围之内 用 in 运算符

SELECT title FROM book
WHERE book_id BETWEEN 1 AND 100;

SELECT name FROM people
WHERE name LIKE 'stac%';


[/code]