Select语句的基本操作

Select语句的基本操作

    掌握了基本操作之后，相信你就能熟练运用Select语句了！Let's go！

操作环境：

mysql-workbench-community-6.1.7-win32
mysql-installer-community-5.6.20.0
mysql-connector-odbc-5.3.4-win32

#创建数据库
create database bank;
grant all privileges on bank.* to 'feng'@'localhost' identified by '******';
#grant usage on bank.* to 'feng'@'localhost' identified by '******';
/*创建用户feng,密码为******，赋予bank数据库权限*/

使用feng用户登录MySQL：
1，进入MANAGEMENT下Users and Privileges,选择feng用户，点击Administrative Roles下Revoke ALL Privileges
2，进入Home，点击MySQL Connections右边的加号，填写Connection Name(随意)和Username(feng)，再点击Test Connection，输入密码，登录成功！

use bank; #以下命令均在feng用户下操作！

使用Learning SQL书中的LearningSQLExample.sql文件生成数据：

在workbench中直接打开LearningSQLExample文件并执行.

出错!workbench不能执行多个update语句

解决方案：进入Workbench下Edit>-Preferences>-SQL Queries选项，去掉"Safe Updates"之前的勾,再次重启并执行该文件。

注意：在workbench下不能使用source命令，参见http://blog.mclaughlinsoftware.com/2012/09/01/mysql-workbench-limit/

select语句：
/*select 需要选择的列名        #可用字符、表达式、内建函数、自定义函数
    from 表名        #永久表(create table创建的表),临时表(子查询生成的表),虚拟表(create view创建的视图),表连接
       where 选择特定的行  #可用一个条件(=,!=,<>,>,<,like),两个条件(and,between,or,not),多个条件(in,圆括号分组)
          group by 根据列值分组
             having 选择特定的组
order by 选择的列名排序;    #升序，降序，表达式
*/
MySQL还有一些特有的子句，如limit,into outfile等

1.1 select from

SELECT * FROM department;
SELECT version(),user(),database(); #内建函数
SELECT emp_id,'ACTIVE', emp_id*3.14159,UPPER(lname) FROM employee; #upper、lower为大写和小写的内建函数
SELECT emp_id,'ACTIVE' status, emp_id*3.14159 empid_x_pi,UPPER(lname) upperlname
      FROM employee; #select 列名的重命名
SELECT emp_id,'ACTIVE' AS status, emp_id*3.14159 AS empid_x_pi,UPPER(lname) AS upperlname
      FROM employee; #完整命令

SELECT cust_id FROM account;
select distinct cust_id from account; #去除重复的行。由于DISTINCT涉及数据排序，
#在数据集较大时，这是相当耗时的，因此，应先了解数据是否含有重复行，以减少DISTINCT的使用.

select e.emp_id,e.fname,e.lname 
	   from (select emp_id,fname,lname,start_date,title from employee) e; #from 定义表别名

create view employee_vw AS
       select emp_id,fname,lname,year(start_date) start_year from employee; #定义视图
select emp_id,start_year from employee_vw;

select e.emp_id,e.fname,e.lname,d.name dept_name
      from employee as e inner join department as d #from 定义表别名
		   on e.dept_id=d.dept_id; #on表示连接条件

1.2 select from where

select emp_id,fname,lname
     from employee
	    where left(lname,1)='T';

select emp_id,fname,lname
     from employee
	    where lname like '_a%e%'; #'_'表示单个字符;'%'表示多个字符

select cust_id,fed_id
     from customer
        where fed_id like '___-__-____';

select emp_id,fname,lname
     from employee
        where lname like 'F%' or lname like 'G%';

select emp_id,fname,lname
     from employee
        where lname regexp '^[FG]'; #使用正则表达式，等价于上一句

select emp_id,fname,lname,start_date,title 
     from employee
	    where start_date > '2002-01-01' and start_date < '2004-01-01';

select emp_id,fname,lname,start_date,title 
     from employee
	    where start_date between '2002-01-01' and '2004-01-01'; #范围的上下限是闭合的

select account_id,product_cd,cust_id,avail_balance
     from account
	    where product_cd = 'CHK' or  product_cd = 'SAV' or product_cd = 'CD' or product_cd = 'MM';

select account_id,product_cd,cust_id,avail_balance
     from account
	    where product_cd in ('CHK','SAV','CD','MM'); #等价于上一句

select account_id,product_cd,cust_id,avail_balance
     from account
	    where product_cd in (select product_cd from product where product_type_cd='account'); #等价于上一句

select account_id,product_cd,cust_id,avail_balance
     from account
	    where product_cd not in ('CHK','SAV','CD','MM'); 

select emp_id,fname,lname,superior_emp_id
     from employee
        where superior_emp_id is null; #表达式不能等于null，即superior_emp_id=null是错误的！

select emp_id,fname,lname,superior_emp_id
     from employee
        where superior_emp_id is not null;

select emp_id,fname,lname,superior_emp_id
     from employee
        where superior_emp_id !=6 or superior_emp_id is not null; #注意：取值为null的数据不能忽略！

select emp_id,fname,lname,start_date,title 
     from employee
        where end_date is NULL and (title = 'Teller' or start_date > '2003-01-01');

select emp_id,fname,lname,start_date,title 
     from employee
        where end_date is NULL and not (title = 'Teller' or start_date > '2003-01-01');

select emp_id,fname,lname,start_date,title 
     from employee
        where end_date is NULL and (title != 'Teller' or start_date <= '2003-01-01'); #等价于上一句

select emp_id,fname,lname,start_date,title 
     from employee
        where (title = 'Head Teller' or start_date > '2002-01-01') or (title = 'Teller' or start_date > '2003-01-01');

1.3 select from group by having

select d.name,count(e.emp_id) num_employees
     from department as d inner join employee as e
           on d.dept_id=e.dept_id
	 group by d.NAME
     having count(e.emp_id)>2;

1.4 select from order by

select open_emp_id,product_cd
     from account
         order by open_emp_id,product_cd; #根据雇员id和账户类型cd排序

select account_id,product_cd,open_date,avail_balance
	 from account
         order by avail_balance desc #desc表示descend降序，asc表示ascend升序
            limit 5; #显示前5个数据

select cust_id,cust_type_cd,city,state,fed_id
	 from customer
         order by right(fed_id,3) #fed_id的最后三位数排序			 
            limit 5;

Reference:
Learning SQL chapter 3,chapter 4