MySQL单表查询 SELECT
分类:MYSQL 热度:

MySQL单表查询 SELECT
========================================================
    简单查询
    通过条件查询
    查询排序
    限制查询记录数
    使用集合函数查询
    分组查询
    使用正则表达式查询
 
表company.employee
雇员编号 emp_id int
雇员姓名 emp_name    varchar(30)
雇员性别 sex enum
雇用时期 hire_date date
职位 post varchar(50)
职位描述 job_description    varchar(100)
薪水 salary double(15,2)
办公室 office                    int
部门编号 dep_id int
 
    CREATE TABLE employee(emp_id int unsigned primary key AUTO_INCREMENT,emp_name varchar(30),
    sex enum('male','female'),hire_date date,post varchar(50),job_description varchar(100),
    salary double(15,2),office int, dep_id int);
 
mysql> desc employee;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| emp_id          | int(11)               | NO   | PRI | NULL    | auto_increment |
| emp_name        | varchar(30)           | YES  |     | NULL    |                |
| sex             | enum('male','female') | YES  |     | NULL    |                |
| hire_date       | date                  | YES  |     | NULL    |                |
| post            | varchar(50)           | YES  |     | NULL    |                |
| job_description | varchar(100)          | YES  |     | NULL    |                |
| salary          | double(15,2)          | YES  |     | NULL    |                |
| office          | int(11)               | YES  |     | NULL    |                |
| dep_id          | int(11)               | YES  |     | NULL    |                |
+-----------------+-----------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
 
mysql> insert into employee values 
(1,'jack','male','20130202','instructor','teach',5000,501,100),
(2,'tom','male','20130203','instructor','teach',5500,501,100),
(3,'robin','male','20130202','instructor','teach',8000,501,100),
(4,'alice','female','20130202','instructor','teach',7200,501,100),
(5,'tianyun','male','20130202','hr','hrcc',600,502,101),
(6,'harry','male','20120202','hr','hrcc',6000,502,101),
(7,'emma','female','20120206','sale','salecc',20000,503,102),
(8,'christine','female','20120205','sale','salecc',2200,503,102);
 
 
一、简单查询
简单查询
SELECT emp_id, emp_name,sex, hire_date, post, job_description, salary, office, dep_id  FROM employee;
SELECT * FROM employee;
SELECT emp_name, salary, dep_id FROM employee;
select sql_no_cache * from employee; (不使用cache直接取数据)
或reset query cache; (清空query cache)
 
避免重复DISTINCT
SELECT DISTINCT post  FROM employee;
 
通过四则运算查询
    SELECT emp_name, salary*12 FROM employee;
    SELECT emp_name, salary*12 AS Annual_salary FROM employee;
    SELECT emp_name, salary*12 Annual_salary FROM employee;
 
定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT(emp_name, ' annual salary: ', salary*12)  AS Annual_salary FROM employee;
 
二、单条件查询
单条件查询
SELECT emp_name FROM employee WHERE post='hr';
多条件查询
SELECT emp_name,salary FROM employee  WHERE post='hr' AND salary>10000;
关键字BETWEEN AND
SELECT emp_name,salary FROM employee WHERE salary BETWEEN 5000 AND 15000;
SELECT emp_name,salary FROM employee WHERE salary NOT BETWEEN 5000 AND 15000;
关键字IS NULL
SELECT emp_name,job_description FROM employee WHERE job_description IS NULL;
SELECT emp_name,job_description FROM employee WHERE job_description IS NOT NULL;
SELECT emp_name,job_description FROM employee WHERE job_description='';
 
关键字IN集合查询
SELECT emp_name, salary FROM employee WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;
SELECT emp_name, salary FROM employee WHERE salary IN (4000,5000,6000,9000) ;
SELECT emp_name, salary FROM employee WHERE salary NOT IN (4000,5000,6000,9000) ;
 
关键字LIKE模糊查询
通配符’%’ SELECT * FROM employee WHERE emp_name LIKE 'al%';
 
通配符’_’   SELECT * FROM employee WHERE emp_name LIKE 'al___';
 
三、查询排序
按单列排序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC;
 
按多列排序
SELECT * FROM employee ORDER BY hire_date DESC, salary ASC;
 
四、限制查询的记录数
示例:
SELECT * FROM employee ORDER BY salary DESC LIMIT 5; //默认初始位置为0 
SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5;
SELECT * FROM employee ORDER BY salary DESC LIMIT 3,5; //从第4条开始,共显示5条
 
五、使用集合函数查询
示例:
SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE dep_id=101;
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE dep_id=101;
 
六、分组查询
单独使用GROUP BY关键字分组
SELECT * FROM employee GROUP BY dep_id;
//默认只显示每个分组的第一条记录,意义不大
 
GROUP BY关键字和GROUP_CONCAT()函数一起使用
SELECT dep_id,GROUP_CONCAT(emp_name) FROM employee GROUP BY dep_id;
SELECT dep_id,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY dep_id;
 
GROUP BY与集合函数一起使用
SELECT dep_id,COUNT(dep_id) FROM employee GROUP BY dep_id;
 
七、使用正则表达式查询
    SELECT * FROM employee WHERE emp_name REGEXP '^ali';
    SELECT * FROM employee WHERE emp_name REGEXP 'yun$';
    SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
 
小结:对字符串匹配的方式
    WHERE emp_name = 'tom';
    WHERE emp_name LIKE 'to%';
    WHERE emp_name REGEXP 'yun$';
========================================================
 
上一篇:MySQL数据操作: DML 下一篇:MySQL索引
猜你喜欢
各种观点
热门排行
精彩图文