mysql创建师徒_mysql基础整理

(一)几个数据库相关的概念

1.数据库

数据库: 保存有组织数据的容器。

数据的所有存储、检索、管理和处理实际上是有数据库软件DBMS完成的。

我们通过数据库软件DBMS来创建和操纵容器。

2.表

某种特定类型数据的结构化清单。表名是唯一的，用来标识自己。

表具有一些特性，定义了数据在表中如何的存储，存储什么样的数据，数据如何分解，各部分信息如何命名等。描述这组信息叫做模式(schema)，它是关于数据库和表的布局及特性信息。

3.列和数据类型

列：表中的一个字段

数据类型：每个列都有相应的数据类型

4.行

表中的一条记录

5.主键

一列或者一组列，能够唯一区分表中的每个行。

习惯上：不更新主键列中的值，不重用主键列的值，不在主键列中使用可能更改的值。

6.外键

外键为某个表的一列，它包含另一个表的主键值。

(二)mysql使用方法

1.mysql是什么？

mysql是一种DBMS，即是一种数据库软件。它是一种基于客户机-服务器的数据库。

2.mysql的优点：

(1)因为开源，成本低

(2)执行速度快，性能好

(3)可信赖

(4)易于安装和使用

3.连接好数据库后，就可以访问数据库并做操作，其中use是用来选择数据库的，show是用来查看mysql数据库、表、每部信息的。

(1)use + database name;

表示切换使用哪个数据库。

用use打开数据库，才能读取其中的数据。

(2)show database;

返回数据库的列表

(3)show tables;

获取一个数据库内的表的列表

(4)show columns from customers;

显示每列的信息，后面用的比较多的是 desc customers；

(三)用select检索数据

1.select：

用途：从表中检索一个或者多个数据列。

select语句中需要体现两种信息：选什么，从什么地方选。

2.检索单列

例如：select prod_name from products;

解释：从products表中检索出来prod_name列

注意：返回结果是未排序的。

3.检索多列

例如：select prod_id,prod_name,prod_price from products;

4.检索所有列

select * from product；

优点：不明确列名的时候使用。

5.distinct关键字

作用：指示mysql只返回不同的值的行

例如：select distinct vend_id from products;

使用：它必须放在列名前面

6.limit

作用：返回结果的前几行

例如：select prod_name from products limit 5;

如果是 select prod_name from products limit 5，5;则表示从行5开始，检索5行.

注意：检索出来第一行是行0。如果行数不够，能返回多少就返回多少。

7.完全限定的表名

select products.prod_name from product;

(四)用where进行数据过滤

1、where子句

作用：指定搜索条件，因为一般数据库表都包含大量的数据，很少我们需要所有的行，通常会根据特定需要来提取数据的子集。where语句就是来指定搜索条件(过滤条件)

位置：放在from之后，order by之前

例子：select prod_name,prod_price from products where prod_price = 2.50

解释：这里采用了相等测试，只返回prod_price为2.5的行，还可以有等于、不等于、小于、小于等、大于、大于等、between操作符。

扩展：根据这些操作符，可以做单个值匹配(=)、不匹配检查(！=或者<>)、范围值检查(between)、空值检查(is null)

例子1：between用法，它需要两个值。

select prod_name,prod_price from products where prod_price between 5 and 10；

例子2：空值检查

select cust_id from customers where cust_email is null;

2.组合where子句

目的：为了进行更强的过滤控制，mysql允许给出多个where自居，以逻辑操作符and或者or的方式使用。

and例子：

select prod_id,prod_price,prod_name

from products

where vend_id =1003 and prod_price <=10;

解释：必须同时满足两个条件

or例子：

select prod_id,prod_price,prod_name

from products

where vend_id =1003 or vend_id =1002;

解释：满足任意一个条件即可

注意：计算次序用圆括号界定，要不容易混淆。

3、in操作

作用：指定条件范围

例子：select prod_name,prod_price

from products

where vend_id in (1002,1003);

解释：检索供应商1002和1003制造的所有产品。in操作符后面跟着的是合法值得清单。

另一种写法：

select prod_name,prod_price

from products

where vend_id = 1002 or vend_id = 1003;

那么为什么使用in操作符呢，优点是什么呢？

(1)清楚只管

(2)计算次序容易理解

(3)in执行比or执行的快

(4)在in中可以包含其他的select语句

4、not操作符：

作用：where子句中用来否定后跟条件的关键字。

例如：select prod_name,prod_price

from products

where vend_id not in (1002,1003);

解释：检索除了1002和1003之外的所有。

(五)通配符过滤

1、应用场景

之前说的数据过滤都是对已知值进行过滤的，比如说匹配一个值或者多个值，大于某个值或者是小于某个值，或者是检查某个范围的值。

但是如果我要搜索产品名中包含anvil的所有产品呢，这时候通配符就可以大显身手了，我们可以利用通配符搜索模式，找出产品名中任何位置出现anvil的产品。

2、什么是通配符

概念：用来匹配值得一部分的特殊字符

如何使用：为了在搜索子句中使用通配符，必须使用like操作符。

3、有哪些通配符以及如何使用呢？

(一)百分号通配符%

表示：任何字符出现任意次数,也可以是0次

例子：

(1)找到以jet开头的产品，接受jet后面为任意多个字符

select prod_id,prod_name

from products

where prod_name like 'jet%';

(2)匹配任何位置包含anvil，不论在之前还是之后出现什么字符。

select prod_id,prod_name

from products

where prod_name like '%anvil%';

(3)找到以s起头、以e结尾的所有产品：

select prod_name

from products

where prod_name like 's%e';

注意：

(1)可以用‘**%’的形式进行尾空格处理，也可以用trim函数进行处理

(2)%通配符不能匹配null

(二)下划线通配符_

表示：下划线只匹配单个字符而不是多个字符

这也是与%的区别，这里就不举例赘述了。

4、小结：

通配符是一种非常有用的搜索工具，但是不能过度使用，否则搜索时间会很长。

(六)正则表达式匹配

1、啥是正则表达式

正则表达式的作用是匹配文本，将一个正则表达式与一个文本串进行比较，mysql用where子句对正则表达式提供了初步的支持，允许指定正则表达式，过滤select检索出的数据。

2、like与regexp的区别

举个例子来看两者的差别：

(一)like统配符

select prod_name

from products

where prod_name like '1000'

order by prod_name;

结果：不返回数据

(二)正则表达式

select prod_name

from products

where prod_name regexp '1000'

order by prod_name;

结果：返回一行

原因：like匹配的是整个列，只有使用通配符的时候才会返回。而regexp是在列值中匹配，如果被匹配的文本在列值中出现regexp将会找到他，相应的行将被返回。

3、有哪几种匹配呢？

(一)基本字符匹配

例1：检索列prod_name包含文本1000的所有行

select prod_name

from products

where prod_name regexp '1000'

order by  prod_name;

注意：regexp后所跟的东西作为正则表达式处理。

例2：检索列prod_name包含000的所有行

select prod_name

from products

where prod_name regexp '.000'

order by  prod_name;

注意：.是正则表达式语言中的一个特殊的字符，它表示的匹配任意一个字符，所以1000和2000都符合条件。

(二)or匹配

使用：当我想搜索两个串之一时，使用|

例子：匹配prod_name为1000或者2000的情况

select prod_name

from products

where prod_name regexp '1000|2000'

order by prod_name;

(三)匹配几个字符之一

表示：匹配任何一个单一字符，当想匹配特定字符的时候，可通过制定一组用[]括起来的字符来完成。

例1：

select prod_name

from products

where prod_name regexp '[123] Ton'

order by prod_name;

解释：正则表达式是[123] Ton，[123]定义了一组字符，即匹配1或者2或者3，这么看，其实[ ]是另一种形式or语句，也可以看做是[1|2|3]的缩写。

例2：

select prod_name

from products

where prod_name regexp '[^123] Ton'

order by prod_name;

解释：匹配的是除这些字符意外的任何东西。

(三)匹配范围

集合可以用来定义要匹配的一个或者多个字符，如果想匹配0到9，可以用[0123456789],也可以用[0-9],并且范围不一定是数值，也可以匹配字符，[a-z]匹配任意的字母字符。

例1：

select prod_name

from products

where prod_name regexp '[1-5] Ton'

order by prod_name;

解释：这个表达式的意思是匹配1到5，例如.5 Ton也会返回。

(四)匹配特殊字符

为了匹配特殊字符，必须使用\\为前导

例如：

(1) \\-匹配-，\\.匹配.

(2) 匹配\用\\\

(五)匹配字符类

(1)[:alnum:]  --  任意字符和数字

(2)[:alpha:]  --  任意字符

(3)[:blank:]  --  空格和制表

(4)[:cntrl:]  --  ascii控制字符

(5)[:digit:]  --  任意数字

(6)[:graph:]  --  与[:print:]相同，但是不包含空格

(7)[:print:]  --  任意可打印字符

(8)[:lower:]  --  任意小写字母

(9)[:punct:]  --  既不在[:alpha:]也不在[:cntrl:]中的任意字符

(10)[:space:]  --  包括空格在内的任意空白字符

(11)[:upper:]  --  任意大写字母

(12)[:xdigit:]  --  任意十六进制数字

(六)匹配多个实例

意义：之前的正则表达式师徒匹配单词出现。但是有的时候需要对匹配书目进行更强的控制

例1：

select prod_name

from products

where prod_name regexp '\\([0-9] sticks?\\)'

order by prod_name;

解释：其中\\是用来匹配括号的，[0-9]用来匹配任意数字，sticks？匹配stick和sticks，因为?匹配他前面任何字符的0次或者1次出现。

例2：

select prod_name

from products

where prod_name regexp '[[:digit:]]{4}'

order by prod_name;

解释：[:digit:]匹配任意数字，{4}确切的要求它前面的数字出现4次，所有正则表达式匹配连在一起的任意4位数字。

同理，可以写成：

select prod_name

from products

where prod_name regexp '[0-9][0-9][0-9][0-9]’

order by prod_name;

扩展：

*表示0个或者过个匹配

+表示1个或者多个匹配

？表示0个或者1个匹配

{n}表示指定书目的匹配

{n,}表示不少于指定数目的匹配

{n,m}表示匹配数目的范围

(七)定位符

目的：为了匹配特定位置的文本。

例子：找出以一个数或者小数点开始的所有产品，这里需要定位符^，表示文本的开始。

select prod_name

from products

where prod_name regexp '^[0-9\\.]'

order by prod_name;

注意：在集合^[123]中表示否定该集合，在此处表示的是文本的开始。

扩展：

(1)^ 文本的开始

(2)$ 文本的结尾

(七)计算字段

--为什么需要计算字段？

因为存储在数据库表中的数据一般不是应用程序所需要的格式，有的时候我们需要对原始数据做一些变换等需求。这就是计算字段发挥作用的时候了。

注意：

(1)我们需要直接从数据库中检索出转换、计算或者格式化过的数据，而不是检索出数据，然后再在客户机应用程序或者报告程序中重新格式化。

(2)计算字段并不实际存在于数据库表中，计算字段是运行时在select语句内创建的。

本文主要介绍两个知识点，一个是拼接字段，一个是执行算数计算。

1、拼接字段

拼接：将值联结到一起构成一个单个值。在select语句中，使用concat()函数来拼接两个列,待拼接的各个串用逗号分隔。

例子：

select concat(vend_name,' (',vend_country,') ')

from vendors

order by vend_name;

2、执行算数计算

例子：

select prod_id,

quantity,

item_price,

quantity*item_price as expanded_price

from orderitems

where order_name = 20005;

解释：其中expanded_price是一个计算字段，计算为quantity*item_price。在执行算数计算的时候，加减乘除都可以甲酸，并且用圆括号来确定计算顺序。

扩展：删除右侧的所有空格，可以用rtrim()函数，如果是删除左侧的所有空格，可用ltrim()，去掉两边的空格是trim(),在拼接字段有时候会用到。例如：

select concat(rtrim(vend_name),' (',rtrim(vend_country),')') as

vend_title

from vendors

order by vend_name;

(八)数据处理函数

本文主要介绍mysql支持什么样的函数，以及如何使用这些函数。

1、使用函数，那么主要有哪些种类的函数呢？

(1)用于处理文本串的文本函数。比如说：删除或者填充值，转化大小写

(2)用于在数值数据上进行算数计算，比如说：返回绝对值以及代数运算

(3)用于处理日期和时间值，并且从这些值中提取特定的成分的日期和时间函数，比如说返回这两个值的日期之差，检查日期的有效性。

(4)返回DBMS正使用的特殊信息的系统函数。比如返回用户登录信息、检查版本细节。

2、文本处理函数

(1)rtrim()：去除列值右边的空格

(2)upper()：将文本转换大写

(3)left()：返回串左边的字符

(4)length()：返回串的长度

(5)locate()：找出串的一个子串

(6)lower()：转换为小写

(7)soundex()：返回串的soundex值，发音相似

(8)substring()：返回子串的字符

例子：

select cust_name,cust_contact

from customers

where soundex(cust_contact) = soundex('Y Lie');

3、日期和时间处理函数

(1)adddate() ：增加一个日期(天、周)

(2)addtime() ：增加一个时间(时、分)

(3)curdate()：返回当前日期

(4)date()：返回日期时间的日期部分

(5)datediff()：计算两个日期之差

(6)day()：返回一个日期的天数部分

(7)dayofweek()：对应一个日期返回对应星期几

(8)hour()：返回一个日期的小时部分

(9)minute()：返回一个时间的分钟部分

(10)month()：返回一个时间的月份部分

(11)now()：返回当前日期的时间

(12)second()：返回一个时间的秒部分

(13)time()：返回一个日期时间的时间部分

(14)year()：返回一个日期的年份部分

注意：不管是插入表还是更新表还是用where子句进行过滤，日期必须为yyyy-mm-dd的格式，虽然其他的日期格式可能可行，但是这是首选的日期格式，它排除了多意义性。

例如：

select cust_id,order_num

from orders

where order_date = '2015-09-01'；

如果日期的形式是‘2015-09-01 00:00:00’，那么就检索不出来，所以更加可靠的形式为：

select cust_id,order_num

from orders

where date(order_date) = '2015-09-01'；

如果我想检索出2005年9月下的所有订单：

方法一：

select cust_id,order_num

from orders

where date(order_date) between '2005-09-01' and '2015-09-30'；

方法二：

select cust_id,order_num

from orders

where year(order_date) =2005 and month(order_date)=9;

4、数值处理函数：

它一般用于代数、三角或几何运算。

abs、cos、exp、mod、pi、rand、sin、sqrt、tan

(九)汇总数据

这部分主要介绍聚集函数以及如何利用它们来汇总表的数据。

1、聚集函数的概念：运行在行组上，计算和返回单个值的函数。

2、mysql主要的聚集函数有哪些？

(1)avg：返回某个列的平均值

(2)count：返回某列的行数

(3)max：返回某列的最大值

(4)min：返回某列的最小值

(5)sum：返回某列值之和

3、avg

概念：

(1)通过对表中的行数计数病计算特定列值之和。求得该列的平均值。

(2)avg可以用来返回所有列的平均值，也可以用来范数特定列或行的平均值。

例子：

selelct avg(prod_price) as avg_price

from product;

解释：返回products表中所有产品的平均价格

select avg(prod_price) as avg_price

from products

where vend_id = 1003;

解释：指定特定行或者特定列求平均值，它返回的是特定供应商1003所提供产品的平均价格。

注意：

(1)avg只能确定特定数值列的平均值，而且列名必须作为函数的参数给出。如果说我们想获得更多列的平均值，要使用多个avg()函数。

(2)avg()函数忽略列值为NULL的行

4、count

概念：

(1)count()主要是用来计数

(2)利用count()确定表中行的数目，以及符合特定条件的行的数目

注意：

(1)使用count(*)对表中行的数目进行统计，不管是表列中包含的是null和非null

(2)使用count(column)对特定的具有值的行进行计数，忽略null

例子：

select count(*) as num_cust

from customers;

解释：返回customers表中客户的总数，这里利用count(*)对所有的行进行计数，不管各行中有什么值，计数值将在num_cust中返回。

select count(cust_emial) as num_cust

from customers;

解释：只对有点子邮件的客户进行计数。

5、max()

概念：max()返回指定列的最大值，但是max()需要制定列名

例子：

select max(prod_price) as max_price

from products;

解释：返回products表中最贵的物品。

注意：

(1)在用于文本数据的时候，如果数据是按相应的列排序，则max()返回的是最后一行。

(2)max()函数忽略列值为null的行

6、min()

概念：max()返回指定列的最小值，但是max()需要制定列名，使用方法与max同理

7、sum

概念：用来返回指定列值的和(总计)

例子：

select sum(quantity) as items_ordered

from orderitems

where order_num = 20005;

解释：sum(quantity)用来返回订单中所有物品的数量之和，where子句值统计某个物品订单中的物品。

同理，sum也可以用来合计计算值。例如下面的例子，可以得到总的订单金额,where子句同样保证只统计某个物品订单中的物品。

select sum(item_price*quantity) as total_price

from orderitems

where order_num = 20005;

注意：sum()会忽略列值为null的行。

8、在聚合函数中用distinct

例子：加了distinct参数之后，平均值只考虑各个不同的价格

select avg(distinct prod_price) as avg_price

from products

where vend_id = 1003;

注意：

(1)distinct只能用于count()

(2)不能用count(distinct)

(3)不能用distinct count(*)

9、组合聚集函数，看到这个例子就会秒懂~

例如：

select count(*) as num_items

min(prod_price) as price_min,

max(prod_price) as price_max,

avg(prod_price) as price_avg

from products;

(十)排序检索数据

目的：为了明确的排序用select语句检索出来的数据，可以用order by子句，order by往往取一个或者多个列的名字，根据此对输出进行排序。

1、如何用order by

按照单个列排序的例子：

select prod_name

from products

order by prod_name;

如果按照多个列进行排序的话，只要列出列名，列名之间用逗号分隔。例如下面的例子，首先按照价格排序，再按照商品名字排序。

select prod_id,prod_price,prod_name

from products

order by prod_price,prod_name;

注意：对于上述例子，仅仅在多个行具有相同的prod_price值时才对prod_name进行排序。如果prod_price列中所有的值都是唯一的，那么就不会按照prod_name排序。

2、指定排序方向

升序：asc，默认的

降序：desc

例子：按照价格降序，最贵的在前面。

select prod_id,prod_price,prod_name

from products

order by prod_price desc;

如果按照多个列进行排序呢？例如：

select prod_id,prod_price,prod_name

from products

order by prod_price desc,prod_name;

解释：以降序排序产品，然后再对产品名排序。

注意：这里只对prod_price降序了，如果想在多个列上进行姜旭排序，必须对每个列指定desc关键字。

扩展：使用order by和limit的组合，能够找出一个列的最高和最低的值。

例如：如何找出最昂贵物品的值

select prod_price

from products

order by prod_price desc

limit 1;

(十一)分组数据

1、什么是分组？

分组就是汇总表内容的子集，分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

2、涉及的子句

group by子句和having子句

3、下面从两个部分介绍分组：

(1)创建分组 --group by

(2)过滤分组 --having

(一)创建分组

举例：

select vend_id, count(※) as num_prods

from products

group by vend_id;

解释：

对vend_id分组，并且统计个数。

group by指定要被分组的目标(vend_id)，并做统计。

注意：

(1)group by子句可以包含任意数目的列

(2)如果在group by子句中嵌套了分组，数据将会在最后规定的分组上进行汇总。

(3)在group by中列出的列不能是聚集函数

(4)如果在select中使用表达式，则必须在group by子句中指定相同的表达式，不能使用别名。

(5)除了聚集计算语句之外，select语句中的没个列都必须在group by子句中给出。

(6)如果分组列中具有null，则它们将会作为一个分组返回。

(7)group by必须在where语句之后，order语句之前。

(二)过滤分组

意义：规定了包括哪些分组，排除哪些分组

比如：我想列出至少有两个订单的所有顾客，此时，必须基于完整的分组进行过滤，而不是根据个别的行进行过滤。

注意：where过滤指定的是行而不是分组。where没有分组的概念。

引入：having进行过滤分组，事实上，所有类型的where子句都可以用having来代替。

where和having的区别：where过滤行，having过滤分组

举例1：

select cust_id, count(※)  as orders

from orders

group by cust_id

having count(※)>2;

解释：订单数大于2的用户

举例2：

select vend_id, count(※) as num_prods

from products

where prod_price >10

group by vend_id

having count(※)>2;

解释：同时用了where和having，表示具有2个以上，价格10以上的产品的供应商。

最后注意：

一般在使用group by子句的时候，应该给出order by子句，这是保证数据正确排序的唯一方法。千万不要依赖group by的排序数据。

总结：

select子句的顺序:

select

from

where

group by

having

order by

limit

(十二)子查询

子查询经常出现的场景：

(1)where子句的in操作符中

(2)用来填充计算列

1、在这里举一个例子，就知道子查询是什么，以及子查询如何使用了。

举例：列出订购物品TNT2的所有客户。

参考：mysql必知必会样例表

涉及的表：orderitems、orders、customers

检索的步骤：

(1)检索包含物品TNT2的所有订单号

select order_num

from orderitems

where prod_id = 'TNT2';

得到的结果是：order_num:20005和20007

(2)查询具有订单20005和20007的客户

select cust_id

from orders

where order_num in (20005,20007);

得到的结果是：cust_id:10001和10004

(3)检索客户id为10001和10004的客户信息

select cust_name,cust_contact

from customers

where cust_id in (10001,10004);

即得到了最后的结果

将这三个查询合并为一个：

select cust_name,cust_contact

from customers

where cust_id in (select cust_id

from orders

where order_num in (select order_num

from orderitems

where prod_id = 'TNT2'));

2、再举一个例子：现在需要显示customers表中每个客户的订单总数。

涉及的表：customers，orders(存储订单与相应的客户id)

(1)先过滤某个特定用户的订单数量，再推广到每个用户。

select count(※) as orders

from orders

where cust_id = 10001;

(2)对每个用户进行count计算

select cust_name,

cust_state,

(select count(※)

from orders

where orders.cust_id = customers.cust_id) as orders

from customers

order by cust_name;

备注：该子查询对检索出的每个客户执行一次

子查询的优缺点：

优点：

在where子句中使用子查询能够编写出功能很强并且很灵活的SQL语句

缺点：

(1)包含子查询的select语句难以阅读和调试。

(2)虽然对嵌套的子查询的数目没有限制，不过在实际使用中由于性能的限制，不能嵌套太多的子查询。