MySQL简单整理

数据库执行语句之前的检查

1.用户是否有权限执行该语句
2.用户是否有权限访问该数据
3.语句的语法是否正确

mysql语句执行顺序

mysql查询需要处理三个方面的信息

• 确定表 通过from和join on确定连接的表，
• 确定行 where过滤无用数据；通过group by进行分组；通过having过滤不需要的组
• 确定列 通过select初步确定哪些列；distinct过滤重复的行；order by依据列进行排序

以下是sql查询字句执行顺序：
from 确定查询的表
on 确定连接条件
join 确定连接方式
where 过滤无用数据，用于过滤结果集的无用的行
group by 用于对具有相同列值的行进行分组
having 过滤不需要的组
select 确定结果集中应该包含哪些列
distinct 过滤重复的行
union 不做介绍
order by 按一个或多个列，对最后结果集进行排序

Quary语句了解 ：查询

query字句

数据库查询根据不同的使用目的，有六种常用的字句。

字句名称	使用目的
select	确定结果集中应该包含那些列
from	指明所要提取数据的表，以及这些表如何连接的
where	过滤不需要的数据
group by	用于对具有相同列值的行进行分组
having	过滤掉不需要的组
order by	按一个或多个列，对最后结果集中的行进行排序

1.select语句的使用

select emp_id , fname f, lname from employee where lname = 'Bkadf1';
select fname, lname from employee;

select

select 字句用于在所有可能的列中，选择查询结果集要包含那些列，
select 字句：

1. 字符，比如数字或字符串；
2. 表达式，比如transaction.amount*-1；
3. 调用内建函数，比如ROUND（transaction.amount，2）；
4. 用户自定义的函数调用。

select * from department; /*显示department表中多有的行和列*/
select emp_id, 'ACTIVE', emp_id*3.14159, UPPER(lname) from employee;

如果只是执行一个内建函数或对简单的表达式求值可以完全省略from字句

select VERSION(), USER(), DATABASE();

指定所查列的别名，只是指定显示时列的名字，不做任何意义。

select emp_id, 'ACTIVE' status,emp_id * 3.14159 empid_x_pi, UPPER(lname) last_name_upper 
from employee;
select emp_id, 'ACTIVE' as status,emp_id * 3.14159 as empid_x_pi, UPPER(lname) as last_name_upper 
from employee;

过滤掉重复的行
注意: distinct产生无重复结果集需要首先数据排序，对于大的结果集相当耗时。

select distinct cust_id from account;

2.from语句 ：定义了查询中所使用的表，以及连接这些表的方式

三种表：

• 永久表（使用create table语句创建的表）
• 临时表（子查询所返回的表）
• 虚拟表（使用create view 字句所创建的视图）

子查询

select e.emp_id, e.fname, e.lname 
from (select emp_id, fname, lname, start_date, title from employee) e;

视图查询

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 employee.emp_id, employee.fname, employee.lname, department.name dept_name 
from employee 
inner join department 
on employee.dept_id = department.dept_id;

定义表的别名

select e.emp_id, e.fname, e.lname, d.name dept_name 
from employee e inner join department d on e.dept_id = d.dept_id;

3.where语句 ：用于在结果集中过滤掉不需要的行

/*单个条件*/
select emp_id, fname, lname, start_date, title 
from employee 
where title = 'Head Teller';

多个条件 ：使用操作符 and、or或者 not 分隔

（1.and 用来连接两个条件

select emp_id, fname, lname, start_date, title 
from employee 
where title = 'Head Teller' and start_date > '2002-01-01';

（2.or

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

（3.not

select emp_id, fname, lname, start_date, title 
from employee 
where not title = 'Head Teller';

除了and，or等，还有用来标识范围性数据的between和in

（4.between and

select emp_id, fname, lname from employee 
where emp_id between 3 and 9;/*包含3和9*/

select emp_id, fname, lname from employee 
where emp_id not between 3 and 9;

（5.in

select emp_id, fname, lname from employee 
where emp_id in (3,4,5,9);
select emp_id, fname, lname from employee 
where emp_id not in (3,4,5,9);

4.group by 和 having 字句

group by用于对具有相同列值的行进行分组

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

having 是用来过滤不需要的组，那么having的条件是以分组的列来进行过滤
那么没有分好的组，就没有办法使用having

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

5.order by 字句

按一个或多个列，对最后结果集中的行进行排序
本次只讨论单个列

select open_emp_id, product_cd from account order by open_emp_id;

order by 可以使用两个排序条件，排序顺序按照书写顺序

select open_emp_id, product_cd from account 
order by open_emp_id, product_cd;
select open_emp_id, product_cd from account 
order by product_cd, open_emp_id;

升序 降序

select open_emp_id, product_cd from account order by open_emp_id desc;
select open_emp_id, product_cd from account order by open_emp_id asc;

表达式排序

select cust_id, cust_type_cd, city, state, fed_id from customer 
order by right(fed_id, 3);

根据数字占位符排序

select emp_id, title, start_date, fname, lname from employee 
order by 2, 5;

过滤 ：查询表中所有行的一个子集

1.条件评估

where title = 'Teller' and start_date < '2007-01-01';
where title = 'Teller' or start_date < '2007-01-01';

使用圆括号:当where字句中包含了3个或更多条件，且同时使用了and和or操作符时

where end_date is null and (title = 'Teller' or start_date < '2007-01-01');

使用not操作符： 使用not有时会很难理解

where end_date is null and 
not (title = 'Teller' or start_date < '2007-01-01');

重写上述条件

where end_date is null and title != 'Teller' and start_date >= '2007-01-01';

2.构建条件

条件通常由1个或多个包含1个到多个操作符的表达式构成
表达式：数字； 表或视图中的列； 字符串，比如‘Teller’； 内建函数； 子查询； 表达式列表;比较操作符； 算术操作符。
条件类型：相等条件，不等条件

on 后是连接条件，where后是过滤条件

select pt.name product_type, p.name product from product p 
inner join product_type pt 
on p.product_type_cd = pt.product_type_cd 
where pt.name = 'Customer Accounts';

不等条件 不等号<>，也写作！=，#

select pt.name product_type, p.name product from product p 
inner join product_type pt on p.product_type_cd = pt.product_type_cd 
where pt.name <> 'Customer Accounts';

范围条件

select emp_id, fname, lname, start_date from employee 
where start_date < '2007-01-01';
select emp_id, fname, lname, start_date from employee 
where start_date between '2005-01-01' and '2007-01-01';
等同于下式	因此between的条件顺序有要求；
select emp_id, fname, lname, start_date from employee 
where start_date >= '2005-01-01' and start_date <= '2007-01-01';

字符串范围 ： 查询社会安全号码“XXX-XX-XXXX”

select cust_id, fed_id from customer 
where cust_type_cd = 'I' and fed_id between '500-00-0000' and '999-99-9999';

成员条件

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 
not in (select product_cd from product where product_type_cd = 'ACCOUNT');
/*使用not in*/
select account_id, product_id, cust_id, avail_balance from account 
where product_cd 
not in ('CHK','SAV','CD','MM');

使用内建函数left(XXX,int)=’ ’

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

使用通配符：
以某个字符开始（或结束）的字符串；
包含某个子字符串的字符串；
在字符串的任意位置包含某个字符的字符串；
在字符串的任意位置包含某个子字符串的字符串；
具备特定格式而不关心单个字符的字符串。

select lname from employee where lname like '_a%e%';

/使用正则表达式/

通配符	匹配
_	正好一个字符
%	任意数目的字符（包括0）

搜索表达式示例

搜索表达式	解释
F%	以F打头的字符串
%t	以t结尾的字符串
%bas%	包含’bas‘子字符串的字符串
__ t _	包含4个字符且第3个字符为t的字符串
_ _ _ -_ _ - _ _ _ _	包含11个字符且第4个和第7个字符为破折号的字符串

null

使用场景：1.没有合适的值。 比如atm机上不需要employee Id列
         2.值未确定。     比如下单后，受理的外卖小哥