SQL学习(MySQL)

创建和使用数据库

创建MySQL数据库

步骤	描述	行为
1	使用root命令登陆MySQL	mysql -u root -p;
2	创建数据库	create database test;
3	创建用户zenshin，并赋予test数据库的权限	grant all privileges on test.* to ‘zenshin’@’.’ identified by ‘test’;
4	退出mysql工具包	quit;
5	使用zenshin登陆MySQL	mysql -u zenshin -p;
6	关联test数据库	use test;

MySQL中的字符集

show character set;
能查看到服务器支持哪些字符集，其中Maxlen列如果大于1，那么该字符集为多字符集

varchar(20) character set utf8;
这是设置一个数据列的字符集

create database test character set utf8;创建数据库的时候设置为utf8字符集

表的创建

创建表的语句

use test;//首先使用数据库
create table person 
(
    person_id smallint unsigned,
    fname varchar(20),
    lname varchar(20),
    gender enum('M','F'),--Mysql提供枚举类型
    birth_date date,
    street varchar(30),
    city varchar(20),
    state varchar(20),
    country varchar(20),
    postal_code varchar(20),
    constraint pk_person primary key (person_id)--将person_id设置为主键
) character set utf8;//设置utf8字符集

如果想看一下person表是否被创建,可以使用describe命令

desc person

如何创建外键和主键

create table favorite_foot 
(
    person_id smallint unsigned,
    food varchar(20),
    constraint pk_favorite primary key (person_id,food),--设置person_id和food为双重主键
    constraint fk_fav_food_person_id foreign key (person_id) references person (person_id)--设置person里面的person_id为外键
) character set utf8;

设置为外键意味这foot表中的person_id列只能来自person表中

操作和修改表

这里浅尝辄止,后面会对增删改查进行详细的描述
在上文我们创建了一个数据库和两个数据表,那我们就可以开始增删改查了(insert,update,delete,select);
为了保持主键的唯一性,我们利用sql语句自动生成主键

alter table person modify person_id smallint unsigned auto_increment;

alter table语句是用来修改已存在的表定义

插入数据

insert into person--insert into 是插入语句,后面跟表名
(person_id,fname,lname,gender,birth_date)--这里是要更新的字段
values (null,'zhu','longhao','M','1996-7-4')--这里是要插入的值

日期类型的字符串即可,只要是格式符合,就能转成日期类型

没有要赋值的,允许为空的直接赋值null

写了几个字段就要付几个字段的值,不能上下不一致

查询语句

select p.fname as 姓名 --select后面主要是跟查询的列,as是起别名
from person as p --form的意思是查询哪个表
where p.person_id = 1 --where则是限定条件,哪些匹配的我查询出来
order by p.fname -- order by命令是排序命令

更新数据

update person -- update关键字后面跟表名
set fname = 'haha' --SET后面跟要更新的数据
where person_id = 1 --where后面跟的是要更更新的是那条数据

删除数据

delete  --删除关键字
from person --要删除的表名
where person_id = 1 --要删除的条件,找到要删除哪条

可能出现的错误

主键不唯一

当在表中定义了主键约束,那么mysql就会保证重复主键不会被插入到数据表中,如果插入的主键在库里已经存在的话,那么就会报错.

不存在的外键

当塞入的外键是一个不存在的时候,就会报这么错误,如果想要不报错,那么就去外键所在的表添加一条信息,然后再塞入外键
外键约束只能在使用innoDB存储引擎创建表的时候才起作用.

列表不合法

插入的列表内容不符合,比如像性别这样的枚举值,你传入了一个不存在的枚举值那么就会出现报错

无效的日期转换

如果构建一个用于生产日期列的字符串,那么该字符串必须符合要求的格式,否则会接受一个错误
MYSQL默认的日期类型字符串是YYYY-MM-DD:年-月-日
我们可以用str_to_date(‘DEC-21-1980’,’%b-%d-%Y’)

update person
set birth_date = str_to_date('DEC-21-1980','%b-%d-%Y')
where person_id = 1;

mysql中将字符串转换为datetime类型时可能用到的格式:

%a : 星期几的简写,比如Sun,Mon…

%b : 月名称的简写,比如Jan,Feb…

%c : 月份的数字形式(0..12)

%d : 日在月中的次序(00..31)

%f : 毫秒数(000000…999999)

%H : 24时格式中的小时

%h : 12小时格式中的小时

%i : 小时中的分钟

%j : 一年中天的次序(001…3666)

%M : 完成的月名称(January..December)

%m : 月份的数字表示

%P : AM或者PM

%s : 秒(00..59)

%W : 完整的星期名(Sunday..Saturday)

%w : 天在星期中的次序(0=周天..6=周六)

%Y : 4位数字的年份

SQL语句的执行顺序

sql语句执行顺序
sql语句的每个操作都会产生一个虚拟表，作为下一个操作的输入，依次执行。
查询语句中执行顺序是这样的：from > where > group by > select > order by

查询入门

查询语句

select语句由几个组件或者说几个字句够成,在Mysql中必不可少的字句就是select

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

select子句

虽然select字句是select语句中第一个组成部分,但是实际上在数据库服务中,它是最后被评估的,因为在确定最后包含哪些列之前,必须先要知道结果集所有可能包含的列.

select *
from department;--这句话的意思就是显示department表下的所有列

通过星号来指代所有列,还可以指定列名

SELECT字句的作用概括如下
select字句用于在所有可能的列中,选择查询结果集要包含哪些列

select字句右面可以跟的东西有以下这么几个:

字符,比如数据或字符串

表达式,比如transaction.amount*-1

调用内建函数,比如ROUND(transaction.amount,2)

用户自定义的函数调用

select emp_id, --字段名
'ACTIVE',  -- 字符串
emp_id * 3.14,  --简单的运算表达式
upper(lname) -- 内建的函数
from employee

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

select version(), --查看数据库版本
user(), -- 查看有哪些用户
database() --查看是哪个数据库

列的别名

select查询出来的都是表里面的字段名称,如果我们想在输出的时候设置一下别名,那么我们就需要as关键字来实现,其实不使用as,直接一个空格加别名也是可以的,但是为了方便阅读我建议加上,不然真的很难认清楚

select emp_id,
'ACTIVE' as status, 
emp_id * 3.14 as empid_x_pi,  --使用as进行设置别名
upper(lname)  last_name_upper  -- 也可以通过直接空格后设置别名
from employee

去除重复的行

在某些情况下,查询可能会返回重复的行数据,这时需要在select关键字之后加上distinct就可以了

select distinct cust_id  --distinct关键字来表示去重
from account;

注意一点:产生无重复的结果首先需要给数据进行排序,对于大的结果集来说是很耗时的操作,因此我们不能随意舒勇distinct关键字.

from子句

from子句定义了查询中所使用的表,以及连接这些表的方式.

表的概念

这里的表是更宽泛的一个概念,以下是表的三种类型

永久表(使用create table语创建的表)

临时表(子查询返回的表)

虚拟表(使用create view子句创建的视图)
这三种类型的表都可以在查询的from子句中使用,

子查询产生的表

select e.emp_id,e.fname,e.lname
from 
(
    select emp_id,fname,lname,start_date,title from employee
) e;--这里的表e就是通过此查询查出来的表,也就是临时表

视图

首先我们创建视图

create view employee_vw as  
select emp_id,fname,lname,year(start_date) as start_year from employee

我们创建好一个视图以后,我们就可以像查询永久表那样使用视图了

select emp_id
from employee_vw  --- 这里就是我们创建的视图,用法与table是一样的.

定义表的别名

当编写复杂的sql语句时，可能会设置多个表，甚至是一张表多次利用，所以我们这里需要对表进行起别名，其实非常简单了，还是使用as关键字或者一个空格加别名

select e.emp_id
from employee_vw e --或者是from employee_vw  as e

where子句

where子句的作用就是筛选不需要的行，也就是给查询条件设置筛选项，要哪些不要哪些

where子句用于在结果集中过滤掉不需要的行

select e.emp_id,e.fname,e.lname,e.start_date,e.title
from employee as e
where e.title = 'Head Teller' --通过where子句将不是标题为Head Teller的行剔除掉

Where子句可以包含更复杂的条件，它们之间使用操作符and、or、或者not分割

select e.emp_id,e.fname,e.lname,e.start_date,e.title
from employee as e
where e.title = 'Head Teller'
and start_date > '2009-01-01'

如果需要在where子句中同时使用and和or操作符时需要用圆括号进行条件分组

select e.emp_id,e.fname,e.lname,e.start_date,e.title
from employee as e
where (e.title = 'Head Teller' and start_date >'2006-01-01')
or (e.start_date > '2007-01-01' and e.title= 'Teller');

这里就是筛选出2006年1月1日入职head teller公司的员工和20071月1日入职teller公司的员工

group by和having子句

前面的查询都是仅仅提取数据而未对其进行很任何加工，不过有时候也需要数据库服务器在返回数据之前对数据进行一下提炼

group by 用于根据列值对数据进行分组

having 与group by子句进行搭配使用对分组数据进行过滤

select d.name,count(e.emp_id) as num_employees
from department d Inner join employee e on d.dept_id = e.dept_id --连接中的内连接，后续会有详细的讲解
group by d.name  -- 对name进行分组
having count(e.emp_id) >2   -- 找出人数大于两人的数据

order by子句

通常情况下，查询的结果集返回的行并不以特定的顺序排列，如果需要对他们进行排列那么就需要order by子句了。

order by子句用于对结果集中的袁术列数据或是根据列数据计算的表达式结果进行排序

select open_emp_id,product_cd
from account
order by open_emp_id--对open_emp_id排序

如果order by子句后面跟了好几个列名，那么order子句中各列出现的顺序决定了对各列进行排序的次序

升序或者降序排序

在排序时，可以通过关键字asc和desc指定升序还是降序，默认情况下是升序，所以降序只需要加上desc的关键字就可以了

根据表达式排序

order by可以跟表达式，根据表中没有的列进行排序
举个例子：对customer表，我们根据社保卡的最后三位数字进行排序

select cust_id,cust_type_cd,city,state,fed_id
from customer
order by right(fed_id,3) -- right函数提取最后三位数字

根据数字占位符进行排序

如果需要根据select子句中的列进行排列，那么可以选择使用该列位于select子句的位置号来代替列名

select e.emp_id,e.fname,e.lname,e.start_date,e.title
from employee as e
order by 2,5

这种不建议使用，容易出现问题。因为order by写死列号，如果出现不对应就有问题。

过滤

一般情况下，我们查询的sql语句都是带着过滤条件的，因为我们不需要所有得行信息，更多的是所有行的子集。
本章讨论的是在select，delect，update语句中where所能使用的各种类型的过滤条件。

条件评估

where子句可能包含一个或者多个条件，每个条件之间用操作符and和or分隔，与编程中的|和&类似。

使用圆括号

如果条件过多，建议使用圆括号进行分割
圆括号的作用就是将条件包裹起来形成一个整体参与运算。

使用not操作符

not操作符就是编程语言中的！,如果是true则not一下就变成false，非，也就是取反。
not操作符与!=等价

构建条件

条件通常是1个或者包含一个或者多个操作符的表达式，表达式可以是下main任意一个；

数字

表或者视图的列

字符串

内建函数

子查询

表达式列表

比较操作符：=，!=,<,>,<>,like,in,between;

算术运算符:+,-,*,/等

条件类型

相等条件

相等条件就是=，!=这里不做太多的说明

范围条件

使用between操作符

当需要同时限制范围的上限和下限的时候，就可以选择使用between操作符构建一个查询条件

select emp_id,fname,lname,start_date
from employee
where start_date between '2005-01-01' and '2007-01-01'

在使用between的时候，下限在between后面需要首先指定，然后再指定上限在end以后。
between是一个闭区间，两个端点也包含在里面。

字符串范围

使用字符串范围的时候，需要知道所使用的字符集中各字符的次序(在某个字符集内各字符的次序被称为排序顺序)。

成员条件

如果我们要查找的是一个有限值集合，可以枚举出来的，那么我们该怎么操作，不能一个一个用等于和or操作符去限制，这样太费劲，
所以针对这种情况，使用操作符in.

select account_id
from account
where product_cd in ('A','B','C','D'); -- 这句话的意思就是product_cd = 'A' or product_cd = 'B' or...

使用子查询

除了编写自定义的集合之外，还可以子查询产生中间集合，那么对上面account表中的进行变化

select account_id
from account
where product_cd in 
(
	select product_cd from account where product_type_cd = 'account'   --代替集合，使用子查询
);

使用not in

如果我们要查询不在某个集合的条件时，我们需要使用not in操作符。

匹配条件

使用通配符

当根据部分字符串匹配进行搜索时，我们需要进行模糊匹配，查看是不是包含我要的信息

通配符	匹配
_	正好一个字符
%	任意数目的字符

下划线表示单个字符的占位符，百分号表示多个字符，当使用搜索表达式构建条件的时候，可以使用like操作符进行匹配

select emp_id,fname,lname
from employee
where lname like '_a%e%' --匹配第二个是a，后面包含e的名字的行

使用正则表达式

当我们的通配符满足不了我们的时候，我们就需要拿出我们的老大了，正则无敌
正则在每个数据库支持的方式不同，mysql使用regexp关键字支持后面带一个正则表达式，sql server使用的是like关键字，Oracle使用的是regexp_like函数实现

select emp_id,fname,lname
from employee
where lname regexp '^[BP]'  --找出姓名开头是B或者P的行

null关键字

当使用null的时候需要记住：

表达式可以为null，但是不能等于null；

两个null彼此不能判断为相等
如果我们想找出没有赋值的语句，不能直接使用= null来判断，应该使用is null来进行判断

select emp_id,fname,lname,superior_emp_id
from employee
where superior_emp_id is null --如果换成等于那么就查不出数据，但是不报错，所以这就很蛋疼

我们可以使用 is not null,可以判断列中数据是否被附上值。

常见的错误

我们在使用不等于的时候可能会带出空值，例如

select emp_id,fname,lname,superior_emp_id
from employee
where superior_emp_id != 6    -- 这里返回的结果里面是带有空值的

所以我们需要加上空值不允许

多表查询

本章主要讨论内连接(inner join)

什么是连接

通过两个表都有的元素进行关联，然后查询出想要的数据，结果集中包含两个表的列，这个操作叫连接

笛卡尔积

最简单的连接方式就是直接在from子句中加入employee表和department表，在from子句中包含两个表并使用join关键字进行分隔

select e.fname,e.lname,d.name
from employee e join department d

这样就产生了一个笛卡尔积的结果
MYSQL之笛卡尔积
简单的说笛卡尔积就是两个表相乘，A表中的元素都乘以B表中的元素产生的结果，AB!=BA

内连接

要把笛卡尔积进行只包含自己想要的数据，那么就需要使用两个表中相连的列，那么基于上面的sql语句我们应该加一个限制，来限制住条件

select e.fname,e.lname,d.name
from employee e join department d
  on e.dept_id = d.dept_id -- 这句话就是把两个表给限制住了，只找出两个表中dept_id相等的行数据

如果在e表里面有但是在d表里面没有找到对应的值，那么这行数据不显示，例如e表中部门为88，但是d表中不存在88的部门，那么e表中部门为88的不显示，如果想要显示出来使用外连接。
这里写join就是表示内连接，正确显示的写法为inner join，如果不写也默认内连接

如果用于关联两个表的列名是一样的，那么可以使用using子句进行代替on子句

select e.fname,e.lname,d.name
from employee e join department d
using(dept_id)

连接三个或者更多的表

对于三个表的连接，在from子句中包含3个表和两种连接类型，以及两个on子句
先给出两个表连接的例子：

select a.account_id,c.fed_id
from account as a inner join customer c
  on a.cust_id = c.cust_id
  where c.cust_type_cd = 'B'

这样看起来比较简洁，这样就是查询出所有账户类型为B的账户id和税务号码，如果我们需要再增加employee表来查询此账户的姓名则采取

select a.account_id,c.fed_id,e.fname,e.lname
from account as a inner join customer c
  on a.cust_id = c.cust_id
  inner join employee e -- 有多少个就join几个
  on a.open_emp_id = e.emp_id
  where c.cust_type_cd = 'B'

这里查询表的顺序无关紧要，sql不是一个过程化的语言，怎么执行时数据库引擎干的事，如果想要固定顺序，每种数据库关键字不同
mysql的关键字是straight_join。

select straight_join a.account_id,c.fed_id,e.fname,e.lname -- 这样就会以account作为驱动表，然后连接customer和employee
from account as a inner join customer c
  on a.cust_id = c.cust_id
  inner join employee e
  on a.open_emp_id = e.emp_id
  where c.cust_type_cd = 'B'

将子查询的结果作为查询表

我们来看一下下面这句sql

select a.account_id,a.cust_id,a.open_date,a.product_cd
from account a 
inner join 
(
	select emp_id,assigned_branch_id
      from employee
      where start_date < '2007-01-01'
        and (title = 'Teller' or title = 'Head Teller') 
) e -- 表e就是子查询的结果作为了一个表
on a.open_emp_id = e.emp_id
inner join  
(
	select branch_id
      from branch
	 where name = 'Woburn Branch'
) b  -- 这个也是，其中表的列为select查询出的数据
on e.assigned_branch_id = b.branch_id

这里的理解就是先将子查询执行出来，拿着子查询的表带入进行join，join后就是我们要的结果。

连续两次使用同一张表

当我们在使用一张表的时候有多个外键，那么就会有多张表访问到他，但是不能使用一次来解决所有的问题，所以就需要定义多张表然后命名为不一样的别名

select a.account_id,e.emp_id,b_a.name open_branch,b_e.name emp_branch
from account a inner join branch b_a -- b_a是branch表join的
  on b_a.branch_id = a.open_branch_id
  inner join employee e
  on  a.open_emp_id = e.emp_id
  inner join branch b_e  --b_e也是branch表join产生的
  on b_e.branch_id = e.assigned_branch_id
where a.product_cd = 'CHK'

自连接

自连接，顾名思义就是自己连接自己，这样一听感觉非常的不可以思并且非常的不解，举个例子，employee表中有一个指向自己的外键，那么就可以通过这个外键找到自己的上级

select e.fname,e.lname,e_mgr.fname as mgr_fname, e_mgr.lname as mgr_lname
  from employee e inner join employee e_mgr  -- 这里就是完全一样的表来做的自联结，找其本身
    on e_mgr.emp_id = e.superior_emp_id

这种情况查不出没有上级的员工，如果想要带没有上级的员工，应该使用外连接

相等连接和不等连接

到目前为止节接触的都是相等连接，我们也可以使用不等的表达式进行连接，
在两个表没有外键连接的情况下，我们可以使用不等关系进行连接，例如：

select e.emp_id, e.fname,e.lname,e.start_date
  from employee e inner join product p
    on e.start_date >= p.date_offered
   and e.start_date <= p.date_retired   -- 给员工的开始日期做限制
   where p.name = 'no-fee checking'

这样我们就可以设定返回或者别的条件表达式去做一些筛选。

on和where的区别

SQL中ON和WHERE的区别
执行顺序不同：on > where > having

使用集合

集合理论基础

A union B: 意思就是A的所有和B的所有，但是重复的只会出现一次

A intersect B:意思就是A和B的交集

A except B:意思是A除去与B相同的部分剩下的部分

(A union B) except (A instersect B) ：意思是A交B的补集
当两个数据集进行执行集合操作的时候必须满足以下两种规范

两个数据集必须具有相同数目的列

两个数据集对应列的数据类型必须完全一样（或者数据库服务器能够将其中一种转换成另外一种）

集合操作符

union操作符

union和union all操作符可以连接多个数据集，他们的区别在于union对连接后的集合排序并去除重复项，而union all则保留重复项

select 'IND' as type_cd,cust_id,lname as name
  from individual   -- 这是表A
union all
select 'BUS' as type_cd,cust_id,name
from business  -- 这是表B  ，对A，B进行并集

union all不删除重复项，union会将重复的数值删掉

intersect操作符

mysql6.0版本没有实现intersect操作符，可以在SQL server和Oracle中使用，mysql6.0以后可以使用，这里的操作请在合适的版本进行操作

select 'IND' as type_cd,cust_id,lname as name
  from individual   
intersect
select 'BUS' as type_cd,cust_id,name
from business  --这里A和B的交集是空集

intersect也存在intersect all,但是实现这个操作的数据库不是很多，大家可以去尝试以下。

except 操作符

执行集合的合差操作，但是mysql6.0依然没有实现该关键字，我们可以通过其他方式实现这里先不做介绍
此操作与以上的两种操作类似，使用方法也是相同的
差集如果在Oracle数据库中的时候，可以使用minus操作符进行操作。

集合操作规则

对复合查询结果排序

如果需要对复合查询的结果进行排序，那么可以在最后一个查询后面增加order by子句，当在order子句中指定要排序的列时，需要复合查询的一个查询中选择列名。
一般来说，复合查询中两个查询对应的名字时相同的，但是并不强制

select emp_id,assigned_branch_id
from employee
where title='Teller'
union 
select open_emp_id,open_branch_id 
from account
where product_cd = 'SAV'
order by emp_id desc  -- 如果指定第二个open_emp_id 会报错

集合操作符优先级

复合查询包含3个或者3个以上的查询语句的时候，他们是按照自顶向下的顺序被解析和执行的

根据ANSI SQL标准，在调用集合操作的时候，intersect操作符比其他操作符具有更高的优先级；

可以使用圆括号对多个查询进行封装，以明确指定他们的执行顺序
在mysql中，没有实现intersect关键字并且不允许在复合查询中使用括号，但是也需要注意次序，获得想要的结果。

数据生成、转换、操作

使用字符串数据

当使用字符串数据时，可以使用下面的字符数据类型

CHAR 固定长度、不足部分使用空格填充的字符串。Mysql的CHAR长度为255，Oracle的数据库的长度允2000个字符，而SQL Server允许的最大长度为8000.
varchar 变长字符串。Mysql允许varchar列最多可以包含65536个字符，Oracle数据(varchar2类型)允许包含4000个字符，而Sql Server允许包含varchar的最大长度为8000
text/CLOB，容纳大长度的变长字符串(通常在上下文中代之文档)，Mysql中具有多种text类型(TINYTEXT\TEXT\MEDIUMTEXT\LONGTEXT),最多可以保存4GB的文档数据。SQL Server中只具有一个最大长度为2TB的text类型，而Oracle数据库中包含的CLOB可以保存128TB的文档。
生成字符串
生成字符串数据的最简单方式是使用一对单引号将字符串包含起来，例如：
insert into table1(filed1,filed2)
values ('字符串1','字符串2')
这里需要注意，在向表中加入字符串的时候，需要保证字符串的最大长度不大于用于设置的最大长度(用户设置或者是数据库上限),否则数据库服务器会报异常。报异常是多数的数据库采取的办法，但是我们可以通过设置修改方式，将其修改为直接截断字符串后插入，并且不抛异常。
在mysql6.0中，默认行为是strict模式，即在发生问题时，抛出异常，而在早先的服务器版本中，默认时截断字符串并发出一个警告，如果希望数据库引擎采取后一种方式，可以将其修改为ANSI模式。
1、我们首先查看一下现在数据库是什么模式
select @@session.sql_mode; --返回结果为：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
2、然后用set命令改变模式
set sql_mode='ansi';
select @@session.sql_mode; -- 返回结果为：REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI
那么等待下次更新的数据字符串大于设定长度，那么就不会报错而是截断字符串并且发出一个警告。但是对于这种情况最好的办法就是设置的上限足够大，因为服务器在存储字符串的时候按需分配空间，不会因为varchar设置的上限太高浪费资源。

包含单引号

因为在mysql中字符串是使用单引号来判断字符串的，如果我们需要加入单引号作为字符串的一部分，我们需要在单引号前面加一个转义符进行转义

update table1 set filed1 = 'This String did'' t work'

Oracle和Mysql可以选择反斜杠作为单引号的转义符。
如果我们需要带着转义符但是不想让他转义，我们就可以使用mysql的内建函数quote()，它用单引号将整个字符串包裹起来，并且为字符串本身的单引号/撇号增加转义，这样就可以将转义的字符也显示出来了

select quote(filde1)
from table1

当取出用于导出的数据时，可以使用quote()函数处理所有非系统生成的字符列

包含特殊字符

当我们需要国际化的时候，很多语言的字符集都不同，我们需要存入特定的字母，这个时候就需要使用mysql和Sql Server中的内建函数char()可以从ASCII字符集中255个字符中任意构建字符串(Oracle使用的是chr()) ,使用样例

select 'filed1' ,CHAR(97,98,99,100,101,102,103);

这里需要注意，使用函数的返回值为BLOB类型，BLOB类型在workbench中的查看方式为，选中BLOB内容右键open view in viewer查看text文本
这里的显示与选择的字符集有关系，如果字符集不同，那么显示的文本可能会有所不同。
我们还可以使用concat()函数来连接若干字符串，其中比较复杂的字符可以使用char()来输入

select concat('danke sch',char(148),'n') -- danke sch  特殊字符 n

在Oracle中可以使用||来代替concat()函数，在Sql server可以使用连接符+来操作字符串的连接。如果我们不知道某个特殊字符的值，可以使用ascii()输入，就可以查出ASCII字符集中的数值了

操作字符串

本小节主要介绍一下字符串的操作，mysql通过内建函数来操作字符串，有两种类型的字符串操作，一种是返回数字的，一种是返回字符串的

返回数字的字符串函数

1、最常用的返回数字的字符串函数为length(),它返回字符串的字符数，使用方法

select length(filed1) filed1
from table1

varchar和text列的长度与真实存在的长度相同，char类型也是与存储的真实值相同，Mysql在获取服务器的char类型后，会将char类型数据的尾端空格删除。

2、position()可以查询某个特定字符串在整个字符串中的位置

select position('abc',filed1) from table1

返回0，说明没有找到。

3、如果希望字符串中任意位置开始搜索，而不是仅限于从第一个字符开始使用函数locate()

select locate('is',filed1,6)--从第六个字符开始查is

4、strcmp()接受两个字符串，返回值是，-1：第一个字符串的排序位于第二个字符串之前；0：这两个字符串相等；1：第一个字符串的排序位于第二个字符串之后

SELECT strcmp(123, 123);        -- 0
SELECT strcmp(123, 122);        -- 1
SELECT strcmp(123, 124);        -- -1

SELECT strcmp('abc', 'abc');    -- 0
SELECT strcmp('abc', 'abb');    -- 1
SELECT strcmp('abc', 'abd');    -- -1

这个排序是按照当前的排序顺序来的，如果你在之前order by了，那么就是按照那个顺序进行输出。同时这个函数对大小写不敏感，大写的ABC与小写的abc相等
4、除了像strcmp()这样的比较函数以外，mysql中还可以like和regexp操作符进行比较字符串，这些比较操作输出的结果一般为1(true)和0(false)

-- like的例子
select name,name like '%ns' end_in_ns
from department   
-- 这就是判断 name中最后带有ns的返回1，没有的返回0

-- 正则的例子
select fed_id,fed_id regexp '.{3}-.{2}-.{4}' is_ss_no_format from table1
-- 只要符合xxx-xx-xxxx格式的返回true其余的返回false

返回字符串的字符串函数

在某些情况下，需要修改已有的字符串，Mysql中也有很多内建的函数来提供支持
1、concat()函数有拼接字符串的作用，我们可以使用他来在已有的字符串后面做拼接

update table1
set textfiled = concat(textfiled,',这是加在后面的')

这样只要运行就能在原有基础上进行字符串的拼接，不仅可以修改原有数据库的值，还能在查询出的结果进行拼接

select concat(fname,' ',lname,' has been a ',title,' since',start_date) emp
from employee

这样就是对所有查出的数据进行拼接，然后返回。
2、在字符串中间替换或者增加部分字符insert(),其中有四个参数：原始字符、字符串操作的开始位置、需要替换的字符数、替换的字符串.根据第三个参数，如果是0就是插入，如果不是0就进行替换

select insert('goodbye world',9,0,'cruel') string -- 这样就根据前面的在第九个位置进行插入

select insert('goodbye world',1,7,'hello') string -- 返回的是hello world

在oracle中可以使用replace()方法，
3、提取子字符串，substring()，就是从原有的字符串中截断，取出一部分来

select substring('hello world',9,5) -- 从第九个截取五个字符

使用数值数据

数值类型的比较简单，所有的数值类型都可以进行算数运算符的运算(+,-,*,/)，还可以使用括号改变优先级

select (12+3)*(5/5) -- 返回值就是15

如果数值型数据的精度大于所在列的指定精度。那么在其被存储的时候就会进行取整操作，例如 9.96在被存放到定义为float(3,1)的列时将会被整取为10.

执行算数运算符

所有的数据库引擎都会支持一些数值计算函数，这里就不做太多的说明了，简单的介绍几个比较常用的
1、计算余数mod(),第一个参数是除数，第二个参数是被除数

select mod(10,4) --返回值为2

2、求一个函数的指数pow()，第一个参数为底数，第二个参数为指数

select pow(2,8) --返回256

控制数字的精度

当我们处理浮点型数据的时候，根据业务需要进行处理，mysql有四种函数对其进行操作ceil(),floor(),round(),truncate()

ceil()和floor()函数主要用于向上或者向下截取整形数字
select ceil(72.445),floor(72.445) -- 返回值为 73 72

使用round()进行四舍五入取整

select round(72.445) -- 72
select round(72.445,2) --72.45

truncate()函数是简单的将超出去的截断，不做任何判断
select truncate(72.445,2) -- 72.44
这里要说一句就是，round和truncate都可以通过有一个负号参数，是对小数点前面做四舍五入和截断

处理有符号数

首先所用数字列允许存储负数，也就是unsigned类型，

sign() 函数用于当有数的时候返回1，当数值为0的时候返回0 数值为负数的时候返回-1
abs()返回数值的绝对值

使用时间数据

处理时间是最为复杂的，因为时间类型丰富多样，不仅格式有问题，而且时区也是有查别

处理时区

Mysql提供了两个不同的时区设置：全局时区和会话时区，后者可能对于每个登陆的用户都是不同的。可以通过下面的查询查看这两种设置

select @@global.time_zone,@@session.time_zone;

set time_zone = '' --可以修改session的时区

生成时间数据

可以使用下面任意一种方式产生时间数据：

从已有的daye、datetime或time列重复制数据
执行返回date、datetime或者time型数据的内建函数
构建可以被服务器识别的代表日期的字符串

为了使用最后一种方法，必须首先理解格式化日期的各种组件

表示日期数据的字符串

组件	定义	范围
YYYY	年份，包括世纪	1000~9999
MM	月分	01(January)~12(December)
DD	日	01~31
HH	小时	00~23
HHH	小时(过去得)	-838~838
MI	分钟	00~59
SS	秒	00~59

因此为了向datetime列添加一条2008年9月17日下午15：00点的时间数据，需要使用下面的字符串：2008-09-17 15：30：00,如果服务器接受这样的数据，数据库会自动进行转换

产生日期的函数

如果我们需要根据字符串产生时间数据，但是日期格式并不是cast()函数所熟知的格式，那么而可以使用内建函数将字符串格式化为日期字符串，比如MySQL包含的str_to_date()函数

select str_to_date('September 17,2008','%M %d,%Y')
--这样就能输出日期的标准格式了

常见的日期部件有：
|格式部件|描述|
|——–|—-|
|%M|月名称(1月-12月)|
|%m|月序号(01-12)|
|%d|日序号(01-31)|
|%j|日在一年中的序号(001-366)|
|%W|星期名称(星期日-星期六)|
|%Y|4位数字表示的年份|
|%y|两位数字表示的年份|
|%H|小时(00-23)|
|%h|小时(01-12)|
|%i|分钟(00-59)|
|%s|秒钟(00-59)|
|%f|微妙(000000-999999)|
|%p|A.M.或者P.M.|

str_to_date()函数将根据格式字符串的内容返回datetime、date或time类型值。

操作时间数据

返回日期的时间函数

许多内建的时间函数接受一个日期类型的值作为一个参数，然后返回另一个日期，例如，MySQL的date_add()函数可以位指定日期增加任意一段时间间隔(如天、月、年)并产生另一个日期。

select date_add(current_date(), interval 5 day)

这句话得意思就是在当前时间再加五天，date_add()函数的第二个参数包含了三个元素：interval关键字、所需要增加的数量、时间间隔的类型。
这个表中有几个常用的时间间隔的类型：
|间隔名称|描述|
|——-|—-|
|Secound|秒数|
|Minute|分钟数|
|Hour|小时数|
|Day|天数|
|Month|月份|
|Year|年份|
|Minute_second|分钟数和秒数，中间用”:”隔开|
|Hour_second|小时数，分钟数和秒数中间用”:”隔开|
|Year_month|年份和月份，中间用”-“隔开|

转换函数

cast()函数就是转换函数，使用该函数的时候，必须提供一个作为关键字的值或表达式，以及所需要转换的类型

select cast('123456' as signed integer)

cast()函数在将字符串转换为数字的时候，首先会从左向右试着对整个字符串进行转换，如果期间在字符串遇到非数字的字符时，那么转换将终止并且不返回错误。

select cast('123AB321' as unsigned integer)

这样取出的数据就是123，而且不会报错。

分组与聚合

分组的概念

举个例子，当我们对原始数据进行查询的时候，比如查询银行中每个柜员创建了多少个账户

select open_emp_id
from account;

这样就把所有的数据显示出来了，但是我并不知道到底一个人创建了多少个账户，我难道要一个一个去数嘛，显然是不可能的，所以有group by自居对账户信息进行分组

select open_emp_id
from account
group by open_emp_id

这样就分成了四组，但是我们需要的是分组后每个组的个数，需要使用到聚合函数来显示出到底每个组有多少人

select open_emp_id,count(*) how_manay
from account
group by open_emp_id

聚合函数count()计算每个分组的行数，星号表示对分组的所有列进行计数，通过使用group by和count()子句的配合，可以精确的满足业务需求
当对数据进行分组的时候，或许还需要在结果中过滤掉不想要的数据，并且过滤条件是针对分组数据而不是原始数据，由于group by子在where子句被评估之后运行，因此无法对where子句添加过滤条件。
所以我们需要使用having子句使用分组过滤条件

select open_emp_id,count(*) how_manay
from account
group by open_emp_id
having count(*) > 4

这样查出的结果就只有大于4的了。

聚合函数

聚合函数对某个分组的所有行执行特定的操作。尽管每种数据库服务器都具有独特的聚合函数，但是一些通用的聚合函数在所有主流服务器上都得到了实现。

Max() 返回集合中的最大值
Min() 返回集合的最小值
Avg() 返回集合中的平均值
Sum() 返回集合中值的和
Count() 返回集合中的个数

下面的查询使用各种聚合函数来分析所有核算账户的可用余额

select max(avail_balance) max_balance,
min(avail_balance) min_balance,
avg(avail_balance) avg_balance,
sum(avail_balance) sum_balance,
count(*) num_balance
from account
where product_cd   = 'CHK'

我们首先对聚合函数有一个大致的印象，下面来具体阐述是怎么使用的

隐式或显式分组

在上一个例子中，查询返回的每一个值都是由聚合函数产生的，这些聚合函数作用于使用过滤条件where product_cd = 'CHK'指定的分组，这里没有使用group by子句进行分组，因此这是一个隐式分组(即包含查询返回的所有行)。
不过在大多数情况下，除了聚合函数所产生的列外，还需要获取额外的列，比如说，假如你需要扩展前一个查询，使之为每一种产品类型执行相同的5种聚合函数，而不只针对核算账户，那么对这个查询来说需要在列举5个聚合函数结果的同时提取product_cd,这里需要注意的是我们需要对这几种产品进行分组才能够查询到，所以我们不能通过where子句隐式分组查询，需要使用group by子句来显示的为产品分组

select product_cd,
max(avail_balance) max_balance,
min(avail_balance) min_balance,
avg(avail_balance) avg_balance,
sum(avail_balance) sum_balance,
count(*) num_balance
from account
group by product_cd

这样就可以将所有的信息查出来了。

对独立值计数

当使用count()函数确定每个分组成员数目时，可以选择时对分组中所有成员计数还是只计数某个列的不同值。

select count(open_emp_id)
from account

这样计算就是根据这一列来的了，如果是*，那么就显示的数据与上面的sql结果一样显示的是行数，如果是想要去重并计算的话，需要加上distinct参数

select count(distinct open_emp_id)
from account

这样显示就是去重以后剩下的个数，而非列数。通过distinct关键字，count()函数检查分组每个成员的特定列的值，并去除发生重复的行，而不是简单的对分组中所有的行进行计数。当然如果我们使用from虚拟表进行查询效率虽然低但是同样可以实现

select count(e.count)
from (
select count(open_emp_id) count 
from account group by 
open_emp_id) e  ; -- 查出分组后的列，然后对分组后的列再进行count就实现了上述的效果，但是效率低

使用表达式

除了使用列作为聚合函数的参数外，还可以创建表达式作为参数，例如、想要找到所有账户中prnding、deposit值(即pending——balence减去available——balance)的最大值，所有可以使用下面的查询：

select max(pending_balance-avail_balance) max_uncleared
from account

这个例子比较简单，但是实际上用聚合函数的参数表达式可以根据需要任意增加复杂度，只需要保证最后返回一个数字、字符串或者日期即可。

如何处理null值

当执行聚合函数或者其他数值计算的时候，应当首先考虑null值是否可能影响计算结果。
首先我们创建一个数值型的表然后加点数据

create table number_tb1 (val smallint);
insert into number_tb1 values(1);
insert into number_tb1 values(3);
insert into number_tb1 values(5);
--执行聚合函数
select count(*) num_rows,
count(val) num_vals,
sum(val) total,
max(val) max_vals,
avg(val) avg_vals
from number_tb1
--显示的值为 3，3，9，5，3.0000

如果现在我们加一个null会出现什么呢

insert into number_tb1 values(null);
select count(*) num_rows,
count(val) num_vals,
sum(val) total,
max(val) max_vals,
avg(val) avg_vals
from number_tb1
--结果变成了 4，3，9，5，3.0000

这里有两点要注意的，第一个变成了4是因为添加了一行，不管是不是null都是加了一行。但是后面的都没有改变，也就是说这些函数遇到null的时候都自动忽略了，不进行计算。
count(*) num_rows与count(val) num_vals的区别就是一个计算的是行数，一个计算的是某一列的总数，所以会忽略null

产生分组

通常人们很少对原始数据感兴趣，更希望对原始数据进行加工以便适应数据分析的需求。那么我就就需要对数据库进行分组，将数据进行分组处理，然后返回想要的值

对单列的分组

对于单列数据分组时最简单同时也是最常用的。例如，想要找到每种产品的余额总计，可以根据account.product_cd列来进行分组：

select product_cd,sum(avail_balance) prod_balance
from account
group by profuct_cd;

这就是最常用的单列的分组，也是之前一直进行演示的。

对多列进行分组

在某些情况才下，需要根据队列产生分组。下面扩展上一个例子，架实需要查找的不是每周产品的余额总计，而是同时根据产品和开户支行进行统计。下面的例子显示了可如何完成此任务：

select product_cd,open_branch_id,
sum(avail_balance) tot_balance
from account
group by product_cd,open_branch_id;

这样的查询就是product_cd与open_branch_id进行组合，所有的搭配。

利用表达式分组

处理根据列分组以外，还可以根据表达式进行分组。考虑下面的查询，根据职员入职年份对职员分组：

select extract(year from start_date) year, count(*) how_many
from employee
group by extract(year from start_date)

改查询使用的表达式十分简单，只是使用extract()函数获取并返回日期中的年份，然后根据此对employee表的数据进行分组。

产生合计数

接着上面的例子，现在假设需要在为每种产品/支行组合计算合计余额的同时，还需要为每种产品单独计算合计数，我们需要使用with rollup来请求数据库服务器完成这些事。

select product_cd,open_branch_id,sum(avail_balance) tot_balance
from account
group by product_cd,open_branch_id with rollup

现在集合里面多出来了额外的行，分别对应独立产品以及总合计数，这些产品的合计行中，open_branch_id是null，因为这些合计数对所有支行进行计算。
因为open_branch_id被with rollup修饰，所以在分组的时候，效果就是product_cd会单独进行计算并且还会计算所有的总值。
那么我现在想得到反向的结果也就是说open_branch_id分组所有的值，我们可以倒过来再进行一遍，但是如果两个我都想要，只能使用with cube

select product_cd,open_branch_id,sum(avail_balance) tot_balance
from account
group by product_cd,open_branch_id with cube

这样的结果就是两边都会分组进行计算，而另外一组为null。但是这个语法在mysql中没有，在sql server和oracle中存在。

分组过滤条件

当分组的时候，也可以根据产分组后对数据应用过滤条件，having子句就是放置这一类过滤条件的地方

select product_cd,sum(avail_balance) prod_balance
from account
where status = 'ACTIVE'
group by product_cd
having sum(avail_balance) >= 10000

这个查询里面包含了两个过滤条件：一个在where子句中，它用于过滤掉不活动的账号，另一个是在having中，它过滤掉的是可用余额合计小于10000的产品。因此，第一个条件在分组之前执行，第二个条件则是在分组产生以后作用在数据。如果将两个过滤条件都放在where子句中的话，就会报错，因为在where子句执行的时候并没有分组。
我们还可以在having子句中编写聚合函数来进行筛选。

子查询

子查询是一种可以用于总共4种SQL语句的强大工具

什么是子查询

子查询是指包含在另一个SQL语句(下文称包含语句)内部的查询。子查询总是由括号包裹。并且通常在包含语句之前执行，像其他查询一样，子查询也会返回一个如下类型的结果集：

单列单行
单列多行
多列多行

只查询返回的结果集类型觉醒了它可能如何被使用以及语句可能使用哪些运算符来处理子查询返回的数据。任何一个子查询返回的数据在包含语句执行完成后都会被丢弃，这使子查询像一个具有作用域的临时表，服务器在执行完sql语句后将清空子查询结果所占的内存。

文章作者: zenshin

文章链接: https://zlh.giserhub.com/2020/03/19/cl35o0mrz0064p4tgesbccuqi/

数据库 SQL

打赏

微信
支付宝

创建和使用数据库

创建MySQL数据库

MySQL中的字符集

表的创建

创建表的语句

操作和修改表

插入数据

查询语句

更新数据

删除数据

可能出现的错误

主键不唯一

不存在的外键

列表不合法

无效的日期转换

SQL语句的执行顺序

查询入门

查询语句

select子句

列的别名

去除重复的行

from子句

表的概念

子查询产生的表

视图

定义表的别名

where子句

group by和having子句

order by子句

升序或者降序排序

根据表达式排序

根据数字占位符进行排序

过滤

条件评估

使用圆括号

使用not操作符

构建条件

条件类型

相等条件

范围条件

使用between操作符

字符串范围

成员条件

使用子查询

使用not in

匹配条件

使用通配符

使用正则表达式

null关键字

常见的错误

多表查询

什么是连接

笛卡尔积

内连接

连接三个或者更多的表

将子查询的结果作为查询表

连续两次使用同一张表

自连接

相等连接和不等连接

on和where的区别

使用集合

集合理论基础

集合操作符

union操作符

intersect操作符

except 操作符

集合操作规则

对复合查询结果排序

集合操作符优先级

数据生成、转换、操作

使用字符串数据

生成字符串

包含单引号

包含特殊字符

操作字符串

返回数字的字符串函数

返回字符串的字符串函数

使用数值数据

执行算数运算符

控制数字的精度