MySQL笔记（一）

MySQL学习（一）

1. 基本操作

2. 检索数据select的使用

   2.1 select语句
   2.2 检索的排序
   2.3 where 语句条件搜索
   2.4 where搜索条件用like通配符（区分大小写）
   2.5 where用正则表达式匹配
   2.6 创建字段
   2.7 使用函数处理
   2.8 聚集函数（计算返回单个值）
   2.9 分组数据
   2.10使用子查询(select 语句作为搜索条件)

使用MySQL基本操作

MySQL是一种DBMS（数据库软件），是一种基于客户机-服务器的数据库 。客户机-服务器型数据库系统由两部分组成，其中服务器软件负责数据处理，可以是MySQL DBMS；客户机软件负责提交用户请求，可以是MySQL工具。

常用MySQL工具。

• MySQL命令行实时使用程序，用命令行与数据库交流
• MySQL 管理器，图形交互客户机
• MySQL Query Browser

1.连接到MySQL：mysql -u ben

2.在MySQL中选择数据库

输入：USE crashcoure
输出：Database changed
这里的Database changed消息是MySQL选择数据库成功后显示的。

3.显示
数据库：SHOW databases;
一个库的列表:SHOW tables;
一个表的列：SHOW columns from customers;
服务器状态：SHOW status;
用户权限： SHOW grants;
警 告： SHOW errors;
错误信息：SHOW warnings;

检索数据select的使用

select语句

检索单个列：

select prod_name
FROM products;

检索多个列：

select prod_id,prod_name,prod_price
from products;

检索所有列：

select *
from products;

检索去重：

select distinct vend_id,prod_price
from products;

注意：distinct同时作用于vend_id 和prod_price两个列
limit限制检索行：

select prod_name
from products
limit 5,5;#只检索从第五行开始的五行，mysql第一行是第0行

select prod_name
from products
limit 5;#只检索从头开始的五行

完全限定名：

select products.prod_name
from crashcourse.products;

检索的排序

使用order by子句，该句必须位于select语句的最后，为检索结果的输出排序。
按一列排序：

```sql
在这里插入代码片

```sql
select prod_name
from products
order by prod_name;#默认按字母顺序排

按多列排序：

select prod_id,prod_price,prod_name
from products
order by prod_price,prod_name;

指定排序方向：

select prod_id,prod_price,prod_name
from products
order by prod_name,prod_price desc;#使用desc关键字，指定某一列的排序是降序

where 语句条件搜索

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between A and B	AB之间

检查单个值:

select prod_name
from products
where prod_name='foses';

select prod_name
from products
where prod_price<10;

select vend_id,prod_name
from products
where ven_id <>1003;

范围:

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

检查空值:

select prod_name
from products
where prod_price is null;

and or 与或:

select prod_name,prod_price
from products
where (vend_id=1002 or vend_id=1003) and prod_price>=10;

in :

select prod_name,prod_price
from products
where vend_id in(1002,1003);#等价于where vend_id=1002 or vend_id=1003,
#in更直观而且包含其他select子句，速度也更快，尽量用in

not :

select prod_name,prod_price
from products
where vend_id not in(1002,1003);

where搜索条件用like通配符（区分大小写）

%任意字符出现任意次数：

select prod_name
from products
where prod_name like '%avail';#注意%不能匹配null，即使是‘%’也不行，这个条件如
#avail后面有空格就会匹配失败，解决方法是用函数去掉右空格

_下划线匹配一个字符

select prod_id,prod_name
from products
where prod_name like '_ ton anvil';

通配符很有用，但是不可过度使用，他会花费过多的时间，而且使用时尽量不要在搜索模式起始处就用通配符，这是最慢的。

where用正则表达式匹配

正则表达式功能强大，可以提取电话号码，查找文件，替换URL等，SQL仅仅支持一部分的正则表达式。
正则表达式不区分大小写，如果需要区分大小写使用关键字’binary’,如：
where prod_name regexp binary 'JetPack .000';
基本匹配：

select prod_name
from products
where prod_name regexp '1000';#与Like的区别：like '1000'只能匹配1000，regexp 
#是只要列值中有1000，就会匹配成功，比如 11000就会被搜索到

两个串或 ‘|’:

select prod_name
from products
where prod_name regexp '1000|2000';

单个字符之一 [ ]:

select prod_name
from products
where prod_name regexp '[123] Ton'

范围 ‘-’：

select prod_name
from products
where prod_name regexp '[1-5] Ton';#等价于'[12345] Ton'

常用字符类：

类	说明
[: alpha:]	任意字母同 [a-zA-Z]
[: alnum:]	字母数字同 [a-zA-Z0-9]
[: blank:]	制表符
[: space:]	任意空白字符 t n v f r
[: lower:]	小写字母
[: upper:]	大写字母
[: digit:]	数字
[: cntrl:]	ASCII控制字符0-31
[: xdigit:]	任意十六进制数字
[: print:]	任意可打印字符

转义字符\：

select prod_name
from products
where prod_name regexp '\\.';

指定个数匹配：
有跟在指定字符后面的元字符指定数量

元字符	说明
*	0个或多个字符
？	0个或1个字符
+	1个或多个字符
{n}	n个字符
{n，}	大于等于n个字符
{n，m}	n到m个字符

select prod_name
from products
where prod_name regexp '[[:digit:]]{4}';

select prod_name
from products
where prod_name regexp '\\([0-9] sticks? \\)';#匹配出（ stick ）和( sticks )

定位符号：

元字符	说明
^	文本开始
$	文本结尾
[[:<:]]	词开始
[[:>:]]	词结尾

select prod_name
from products
where prod_name regexp '^[0-9]\\.]$';#效果类似于like

注意：^在集合[]中表示非

select prod_name
from products
where prod_name regexp '^[0-9]';#不以数字开头

创建字段

拼接字段:

select Concat(vend_id,'(',vend_country,')')
from vendors;

使用别名：
创建的新的字段是没有自己的名字的，它只是一个值，客户机无法直接引用它，需要用As创建别名。

select Concat(vend_id,'(',vend_country,')') as vend_tile
from vendors

算术字段：

select prod_id,quantity*item_price as totle_price
from orderitems

使用函数处理

文本处理函数：

函数	说明
Left()	返回串左边的字符
Right()	返回串右边的字符
Length()	返回串的长度
Lower()	将串转为小写
Upper()	将串转为大写
Locate()	找 子串
Ltrim()	去左边空格
Rtrim()	去右边空格
SubString()	返回子串
Soundex()	返回发音相近的值

select Upper(vend_name) as vend_name_uppper
from vendors;

select cust_name,cust_contact
from customers
where Soundex(cust_contact)=Soundex('Y Lie');

时间日期处理函数

函数	说明
Now()	返回当前日期和时间
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
Date_Format()	返回格式化日期时间
Year()	返回日期的年份
Month()	返回日期的月部分
Day()	返回日期的天数部分
Hour()	返回时间的小时部分
Minute()	返回时间的分钟部分
Second()	返回时间的分钟部分
Time()	返回时间日期的时间部分
Now()	返回当前日期和时间

注意，在mysql中，日期格式必须是yyyy-mm–dd

selecty cust_id,order_num
from olders
where Date(order_date) = '2005-9-01';

select cust_id,order_num
from orders
where Year(order_date)=2005 and Month(order_date) = 9;

数值处理函数

函数	说明
Abs()	返回绝对值
Mod()	返回取模
Rand()	返回随机数
Sqrt()	返回平方跟
Sin()	返回正弦
Cos()	返回余弦
Tan()	返回正切

聚集函数（计算返回单个值）

函数	说明
AVG（）	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列最大值
MIN()	返回某列的最小值
SUM()	返回某列之和

select AVG(prod_price) as avg_price
frpm products;

加限定条件

select AVG(prod_price) as avg_price
from products
where  vend_id = 1003;

select COUNT(*) as nun_cust
from costomers;#返回表的行数

select COUNT(prod_price) as price_num
from costomers;#返回prod_price的列的行数，不包括null

select MAX(prod_price) as max_price
from products;

select sum(quantity) as items_ordered
from orderitems
where order_num = 2005;

使用distinct关键字，只考虑不同值

select AVG(distinct prod_price) as avg_price
from products
where vend_id = 1003;

分组数据

select vend_id,COUNT(*) as num_prods
from products
group by vend_id;#按照vend_id分组，此时聚集函数聚集的是对每一个组聚集

group 语句必须写在where之后，where负责在分组之前过滤掉表中的语句，对分组的过滤使用having

select cust_id,COUNT(*) as orders
from orders
group by cust_id
having COUNT(*)>=2;#过滤出订单数大于等于2 的分组

select vend_id ,COUNT(*) as num_prods
from products
where prod_price>=10
group by vend_id
having COUNT(*)>=2;#在商品价格大于等于10的行中分组过滤出订单数大于等于2的分组

排序

select order_num, SUM(quantity*item_price) as ordertotal
from orderitems
group by order_num
having SUM(quantity*item)>=50
order by ordertotal;

使用子查询(select 作为搜索条件)

select cust_name,cust_contact#在客户表中找tnt2订单们的客户id们的名字和联系方式
from costomers
where cust_id in(select cust_id #在订单表中tnt2订单们的客户id们
                 from orders
                 where order_num in(select order_num#在商品表中找商品tnt2 的订单号们
                 					from orderitems
                 					where prod_id = 'tnt2'));

例题：在orders表中有订单号和顾客ID，在costomers表中有所有客户ID和姓名，要求显示cotomers表中每位客户的订单总数

select cust_name,(select COUNT(*)
				  from orders
				  where order.cust_id = costomers.cust_id) as orders
from customers#每次在custmers查询一行，都要执行一次上面的select子程序
order by cust_name;