mysql数据库的高级查询_多表查询

蓝海5666 贡献于2014-12-27

作者 china  创建于2011-03-04 09:58:00   修改者china  修改于2011-03-04 10:00:00字数8040

文档摘要:Mysql数据库的高级查询多表查询,联表查询查询emp表中,emp_name为啸天的全部信息
关键词:

Mysql数据库的高级查询 多表查询,联表查询 查询emp表中,emp_name为啸天的全部信息 mysql> select * from emp where emp_name='啸天';   查询结果显示如下: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100005 | 啸天 | 27 | 4000 | 1979-07-10 | male | +--------+----------+---------+---------+------------+---------+ 1 row in set (0.00 sec)   查询emp表中,emp_sal,工资在5000以上的全部信息 mysql> select * from emp where emp_sal>5000;   查询结果显示如下: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100001 | 红枫 | 29 | 9000 | 1977-01-01 | male | | 100002 | 丽鹃 | 27 | 8000 | 1979-12-31 | fmale | +--------+----------+---------+---------+------------+---------+ 2 rows in set (0.00 sec)   查询emp表中在1978年1月1日之后出生的 mysql> select * from emp where emp_bir>'1978-01-01';   查询结果显示如下: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100005 | 啸天 | 27 | 4000 | 1979-07-10 | male | | 100002 | 丽鹃 | 27 | 8000 | 1979-12-31 | fmale | +--------+----------+---------+---------+------------+---------+ 2 rows in set (0.00 sec)   查询emp表中在1979年12月1日之前出生,工资在5000以上的 mysql> select * from emp where emp_bir<'1979-12-01' and emp_sal>5000;   查询结果显示如下: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100001 | 红枫 | 29 | 9000 | 1977-01-01 | male | +--------+----------+---------+---------+------------+---------+ 1 row in set (0.00 sec)   2.6.2 字段查询   CEO查看员工工资情况 mysql> select emp_name,emp_sal from emp;   查询结果显示如下: +----------+---------+ | emp_name | emp_sal | +----------+---------+ | 啸天 | 4000 | | 红枫 | 9000 | | 丽鹃 | 8000 | +----------+---------+ 3 rows in set (0.00 sec)   查看1978年后出生的人的姓名、工资和性别 mysql> select emp_name,emp_sal,emp_sex from emp where emp_bir>"1977-12-31";   查询结果显示如下: +----------+---------+---------+ | emp_name | emp_sal | emp_sex | +----------+---------+---------+ | 啸天 | 4000 | male | | 丽鹃 | 8000 | fmale | +----------+---------+---------+ 2 rows in set (0.00 sec)   2.6.3 查询结果排序   用ORDER BY语句对emp表中所有员工工资高低顺序查询结果(默认是从低到高——升序) mysql> select * from emp order by emp_sal;   查询结果显示如下: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100005 | 啸天 | 27 | 4000 | 1979-07-10 | male | | 100002 | 丽鹃 | 27 | 8000 | 1979-12-31 | fmale | | 100001 | 红枫 | 29 | 9000 | 1977-01-01 | male | +--------+----------+---------+---------+------------+---------+ 3 rows in set (0.00 sec)   用DESC关键字来进行从高到低排序——降序 mysql> select * from emp order by emp_sal desc;   查询结果显示如下: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100001 | 红枫 | 29 | 9000 | 1977-01-01 | male | | 100002 | 丽鹃 | 27 | 8000 | 1979-12-31 | fmale | | 100005 | 啸天 | 27 | 4000 | 1979-07-10 | male | +--------+----------+---------+---------+------------+---------+ 3 rows in set (0.00 sec)   2.6.4 查询结果数量的限制   用LIMIT查看emp表中工资收入排名前两个员工的资料: mysql> select * from emp order by emp_sal desc limit 2;   查询结果显示如下: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100001 | 红枫 | 29 | 9000 | 1977-01-01 | male | | 100002 | 丽鹃 | 27 | 8000 | 1979-12-31 | fmale | +--------+----------+---------+---------+------------+---------+ 2 rows in set (0.00 sec)   查看工资排名第2到第3的员工资料: mysql> select * from emp order by emp_sal desc limit 1,2;   查询结果显示如下: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100002 | 丽鹃 | 27 | 8000 | 1979-12-31 | fmale | | 100005 | 啸天 | 27 | 4000 | 1979-07-10 | male | +--------+----------+---------+---------+------------+---------+ 2 rows in set (0.01 sec)   使用rand()抽样调查,随机抽取2个员工,查看其资料 mysql> select * from emp order by rand() limit 2;   如如下结果:(随机的)   +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100005 | 啸天 | 27 | 4000 | 1979-07-10 | male | | 100001 | 红枫 | 29 | 9000 | 1977-01-01 | male | +--------+----------+---------+---------+------------+---------+ 2 rows in set (0.01 sec)   2.6.5 查询结果的字段联合和重新命名   mysql> select concat(emp_id," ",emp_name) from emp;   查询结果: +------------------------------+ | concat(emp_id," ",emp_name) | +------------------------------+ | 100005 啸天 | | 100001 红枫 | | 100002 丽鹃 | +------------------------------+ 3 rows in set (0.00 sec)   用AS关键字重新给输出结果命名标题 mysql> select concat(emp_id," ",emp_name) as info from emp;   查询结果如下显示: +----------------+ | info | +----------------+ | 100005 啸天 | | 100001 红枫 | | 100002 丽鹃 | +----------------+ 3 rows in set (0.00 sec)   2.6.6 日期查询的相关运算   可以通过YEAR()、MONTH()、DAYOFMONTH()函数来提取日期的组成元素   查询7月份出生的员工资料: mysql> select * from emp where month(emp_bir)=7;   查询结果显示如下: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100005 | 啸天 | 27 | 4000 | 1979-07-10 | male | +--------+----------+---------+---------+------------+---------+ 1 row in set (0.00 sec)   可以利用英文月份来查询: mysql> select * from emp where monthname(emp_bir)="January";   查询结果显示如下: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100001 | 红枫 | 29 | 9000 | 1977-01-01 | male | +--------+----------+---------+---------+------------+---------+ 1 row in set (0.00 sec)   利用TO_DAYS()函数可以查询出职工们从出生到现在所经理的时间,单位是天数 mysql> select to_days(current_date) - to_days(emp_bir) as livingdays from emp;   查询后结果如下: +------------+ | livingdays | +------------+ | 9425 | | 10345 | | 9251 | +------------+ 3 rows in set (0.00 sec)   计算从现在开始经历100天后的日期 mysql> select date_add(now(),interval 100 day);   查询结果如下: +----------------------------------+ | date_add(now(),interval 100 day) | +----------------------------------+ | 2005-08-07 13:56:58 | +----------------------------------+ 1 row in set (0.00 sec)   计算从现在开始经历100天后的日期 mysql> select date_sub(now(),interval 100 day);   查询结果如下: +----------------------------------+ | date_sub(now(),interval 100 day) | +----------------------------------+ | 2005-01-19 14:00:20 | +----------------------------------+ 1 row in set (0.00 sec)   2.6.7 数据统计   使用COUNT()函数计算表中的数据数目(比如emp表中的员工数目) mysql> select count(*) from emp;   查询结果如下: +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.01 sec)   统计工资上5000的数目 mysql> select count(*) from emp where emp_sal>5000;   查询结果如下: +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)   统计男女职工数目:(GROUP BY语句分类) mysql> select emp_sex,count(*) from emp group by emp_sex;   查询结果如下: +---------+----------+ | emp_sex | count(*) | +---------+----------+ | fmale | 1 | | male | 2 | +---------+----------+ 2 rows in set (0.01 sec)   使用数据统计函数(MIN(),MAX(),SUM(),AVG()) mysql> select -> min(emp_sal) as min_salary, -> max(emp_sal) as max_salary, -> sum(emp_sal) as sum_salary, -> avg(emp_sal) as avg_salary, -> count(*) as employee_num -> from emp;   查询结果如下: +------------+------------+------------+------------+--------------+ | min_salary | max_salary | sum_salary | avg_salary | employee_num | +------------+------------+------------+------------+--------------+ | 4000 | 9000 | 21000 | 7000.0000 | 3 | +------------+------------+------------+------------+--------------+ 1 row in set (0.00 sec)   2.6.8 从多个数据表中检索信息   根据前面的方法,分别进行如下操作: 1). 在数据库asb中建立一个新表dept,表中有两项元素: dept_id --> varchar(6) dept_name --> varchar(10) 2). 在表emp中插入如下一行新记录: +--------+----------+---------+---------+------------+---------+ | emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex | +--------+----------+---------+---------+------------+---------+ | 100003 | 小红 | 30 | 8000 | 1976-11-11 | fmale | +--------+----------+---------+---------+------------+---------+ 3). 在新表dept中,输入如下记录 +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 100005 | MTD | | 100001 | MTD | | 100002 | MTD | | 100003 | HR | +---------+-----------+   查询emp和dept这两个表中,员工的姓名和部门信息 mysql> select emp.emp_name,dept.dept_name from emp,dept -> where emp.emp_id=dept.dept_id;   查询结果如下: +----------+-----------+ | emp_name | dept_name | +----------+-----------+ | 啸天 | MTD | | 红枫 | MTD | | 丽鹃 | MTD | | 小红 | HR | +----------+-----------+ 4 rows in set (0.00 sec)   多表查询时注意: 1). FROM子句必须给出所查询的表的全部名称 2). 选择字段时候注明其所属表的名称(如emp表中的emp_id要表示为emp.emp_id) 3). 在Where子句中必须指明查询的条件(如,emp.emp_id和dept.dept_id是相同意义的元素)

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 6 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档