SELECT * FROM employee ORDER BY salary desc, id DESC;
//asc 升序 默认 desc 降序
SELECT COUNT(*) from employee;
SELECT count(*) from employee WHERE salary>2500;
SELECT count(*) from employee WHERE
IFNULL(salary,0)+IFNULL(performance,0) >5000;
SELECT count(performance),count(manage) from employee;
SELECT sum(salary),SUM(manage) from employee;///求和
SELECT sum(salary+IFNULL(performance,0)) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT MAX(salary),MIN(salary) FROM employee;
SELECT * FROM students WHERE gender='男' AND age=18;
SELECT * FROM students WHERE id=7 or name='ls';
SELECT * FROM students WHERE id =2 or id=7;
SELECT * FROM students WHERE id in(2,7);
SELECT * FROM students WHERE id is not null;
SELECT * FROM students WHERE id is null;
SELECT * FROM students WHERE gender!='男';
SELECT * FROM students WHERE age>=18 and age<40;
SELECT * FROM students WHERE age BETWEEN 18 and 20;
SELECT * FROM students WHERE name like '_____';查询五个字母的
SELECT * FROM students WHERE name like '__';
SELECT * FROM students WHERE name like '___y';
SELECT * FROM students WHERE name like 't%';
SELECT * FROM students WHERE name like '_u%';
SELECT * FROM students WHERE name like '%李%';
SELECT DISTINCT name from students;去重
//分组查询
SELECT * FROM employee GROUP BY gender;
SELECT department FROM employee GROUP BY department;
SELECT department,GROUP_CONCAT(name) FROM employee GROUP BY department;
SELECT gender,GROUP_CONCAT(name) FROM employee GROUP BY gender;
SELECT name,gender FROM employee GROUP BY gender,name;
SELECT department, GROUP_CONCAT(salary),SUM(salary), AVG(salary),MAX(salary),min(salary) FROM employee GROUP BY department;
SELECT department,GROUP_CONCAT(salary),SUM(salary) from employee GROUP BY department;
SELECT department,GROUP_CONCAT(name),count(*) from employee GROUP BY department ;
SELECT department,GROUP_CONCAT(salary),COUNT(*) from employee WHERE salary>1500 GROUP BY department;
//having 和limit
SELECT department,GROUP_CONCAT(salary), SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>=9000;
SELECT department,GROUP_CONCAT(salary),SUM(salary)
FROM employee WHERE salary >2000 GROUP BY department HAVING SUM(salary)>6000;
SELECT * FROM employee LIMIT 0,3;//分页
另附Navicat Premium 12 破解文件:http://www.chasen.shop/video/Navicat_Premium_12_破解补丁.zip
下载来之后找到C:\Program Files\PremiumSoft\Navicat Premium 12替换掉相应文件。