《MySQL必知必会》学习笔记

《MySQL必知必会》学习笔记

第21章 创建和操纵表

创建表

Create Table customers   #创建表customers
(   
    #列名         数据类型
    cust_id      int     not null auto_increment,#not null非空，auto_Increment 该列值自增
    cust_name    char(50) not null,
    cust_address char(50)  null,
    cust_city    char(50)  null DEFAULT 'shanghai',#DEFAULT默认值，没设值的时候使用默认值
    cust_state   char(50)  null,
    cust_zip     char(10)  null,
    cust_country char(50)  null,
    cust_contact char(50)  null,
    cust_email   char(255)  null,
    primary key (cust_id)#定义cust_id为主键
)Engine=InnoDB;#引擎使用InnoDB

更改表

#给vendors表增加一个名为vend_phone的列
Alter table vendors
ADD vend_phone char(20);
#删除列vend_phone
Alter Table vendors
Drop Cloumn vend_phone;
#定义外键:定义与order_num表关联的外键order_num
Alter Table orderitems
ADD constraint fk_orderitems_orders
Foreign key (order_num) References orders (order_num);

删除表

#删除表customers
Drop Table customers2;

重命名表

rename Table backup_customers To customers;

第19章 插入数据

#可以同时插入多行，没列出来的字段名，默认为null。给出了列名，次序可以和表的不同。
#Insert操作很耗时，添加Low_Priority关键字，可以降低Insert语句的优先级。
Insert Low_Priority into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
Values('Pep','100 Main Street','Los Angeles','CA','90046',NULL)
Values(……);

#插入检索出的数据
#将custnew检索出的数据插入到custmers表中。不会列出检索出的数据。
Insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
select cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country
from custnew;

第20章 更新和删除数据

更新数据

#如果不加where子句，将会更新所有行。
#ignore关键字：及时发生错误，也继续更新。一般不加
update ignore customers
set cust_name='The Fudds',
    cust_email='elmer@fudd.com'  #更新值
where cust_id=10005;

删除数据

#如果不加where子句，将会删除所有客户
#Delete删除整行而不是删除列。为了删除指定的列，要使用Update语句。
Delete From customers
Where cust_id=10006;

#更快的删除，1比2更快，Truncate是直接删除表，然后重建表。而Delete是逐行删除表中的数据。
#删除customers表的所有行
Truncate table customers;#1
Delete From customers;#2

第3章 使用MySQL

#使用数据库
USE 数据库名;
#显示数据库
SHOW DATABASES;
#显示表格
SHOW Tables;
#显示customers表中所有的列
SHOW Columns from customers;   ==   Describe customers;
#显示服务器错误或警告消息
SHOW Errors;
SHOW Warnings;

第4章 检索数据

#检索单个列
Select 列名 From 表名;
Select prod_name From products;
#检索多个列
Select prod_id,prod_name,prod_price 
From products;
#检索所有列
#(*) 通配符
Select * From products;
#检索不同的行
#Distinct关键字：只返回不同的值
Select DisTinct vend_id 
From products;
#限制返回结果数量
#Limit 5指示MySql返回不多于5行
Select prod_name
From products
Limit 5;
#Limit 0,5指示MySql返回从行0开始的5行
#行0，检索出来的第一行为行0而不是行1
Select prod_name
From products
Limit 0,5;

第5章 排序检索数据

#排序数据
#Order By子句
#对prod_name列以字母顺序排序数据
Select prod_name
From products
Order By prod_name;
#按多个列排序
#先按价格，然后再按名称排序
Select prod_id,prod_price,prod_name
From products
Order By prod_price,prod_name;
#指定排序方向
#Desc 降序  Asc 升序（默认） 这两个关键字只应用到直接位于其前面的列名
#先按价格降序排序，然后再按名称升序排序
Select prod_id,prod_price,prod_name
From products
Order By prod_price Desc,prod_name;
#Order by和limit的组合，能够找出一个列中最高或最低的值
select prod_price
from products
Order by prod_price Desc
limit 1;

第6、7章 过滤数据

#where子句
Select prod_name，prod_price
From products
Where prod_price=2.5;
#where子句操作符
= <> != < <= > >= Between and 空值检查is null  非空值检查is not null
#AND、OR操作符、计算次序：（）> AND > OR
Select prod_name,prod_price
From products
Where vend_id=1002 OR vend_id=1003 AND prod_price >=10;
#IN、NOT操作符
#IN(A,B)  查找A,B之间的值，  NOT：否定它之后所跟的条件
#列出除1002~1005之外的所有供应商制造的产品
Select prod_name,prod_price
From products
Where vend_id NOT IN (1002,1005)
Order by prod_name;

第8章 用通配符进行过滤

#Like操作符
#通配符：用来匹配值的一部分的特殊字符，要使用通配符，必须使用Like操作符
# % 通配符 区分大小写
# % 告诉mysql接受jet之后的任意字符，不管长度
Select prod_id,prod_name
From products
Where prod_name Like 'jet%';
# _ 下划线通配符  匹配单个字符
Select prod_id,prod_name
From products
where prod_name Like '_ ton anvil';
#注意：通配符搜索很费时间，尽量不要把通配符置于搜索模式的开始处。

第9章 用正则表达式进行搜索

正则表达式是用来匹配文本的特殊的串（字符集合）

#Regexp:代表用正则表达式搜索
#‘.’是正则表达式中特殊字符，表示匹配任意一个字符。
#检索列prod_name中包含JetPack .000的所有行
#Binary：表示区分大小写，一般不加
Select prod_name
From products
Where prod_name Regexp Binary 'JetPack .000'
Order by prod_name;

#进行Or匹配
#搜索1000或2000
Select prod_name
From products
Where prod_name Regexp '1000|2000'
Order by prod_name;

#匹配1 ton或2 ton或3 ton
Where prod_name Regexp '[123] Ton'
#匹配非 1 ton或2 ton或3 ton
Where prod_name Regexp '[^123] Ton'
#匹配1 或 2 或3 Ton
Where prod_name Regexp '1|2|3 Ton'
#范围匹配：匹配1-5 Ton
Where prod_name Regexp '[1-5] Ton'
#为匹配特殊字符，必须用\\转义，\\-表示查找-，\\.表示查找.

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1，}）
？	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过25）

第10章 创建计算字段

拼接字段：将值联结到一起构成单个值

#ConCat():拼接函数
Select ConCat(vend_name, ' (',vend_country,')')
From vendors
Order by vend_name;
#搜索结果
#ConCat(vend_name, ' (',vend_country,')')
#         ACME       (      USA       )
#Trim()（去掉串左右两边的空格）
#RTrim()（去掉串右边的空格）
#LTrim()（去掉串左边的空格）
Select Concat(RTrim(vend_name),' (',RTrim(vend_country),')')

#AS可以给表、字段、拼接字段等取别名
Select Concat(RTrim(vend_name),' (',RTrim(vend_country),')') AS vend_title

第11章 使用数据处理函数

#文本处理函数
Upper()函数：将文本转换为大写

#日期和时间处理函数
#日期必须为yyyy-mm-dd格式
#Data(order_data):仅提取列的日期部分
Select cust_id,order_num
From orders
Where Data(order_data) Between '2005-09-01' and '2005-09-30';

#数值处理函数
#abs()：返回一个数的绝对值

第12章 汇总数据

SQL聚集函数

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

#返回products表中所有产品的平均价格
Select AVG(prod_price) AS avg_price
#Select AVG(Distinct prod_price) AS avg_price    Distinct:只包含不同的值。
From products;
#count()函数：count(*)对表中行的数目进行计数，count(column)对column列中具有值的行进行计数，忽略null值。
Select Count(*) AS num_cust
From customers;

第13章 分组数据

#按vend_id分组，并统计每个组的行数，统计每个供应商vend_id的产品数。
Select vend_id,count(*) As num_pords
From products
group by vend_id;
#搜索结果
vend_id   num_prods
1001        3
1002        2
1003        7
1005        2
#注意：Group by子句必须出现在Where子句之后，Order By子句之前。
#Rollup:可以得到每个分组以及每个分组汇总级别
group by vend_id With Rollup;

#过滤分组
#having子句类似于where,后接过滤条件，区别是where在数据分组前进行过滤，having在数据分组后进行过滤。
#
Select cust_id,count(*) AS orders
From orders
Group by cust_id
having count(*) >=2 Order by orders;#注意count(*) >=2只能使用count(*)，不能用别名orders.

Select子句顺序

Select > From > Where > Group by > Having > Order by > Limit

第14章 使用子查询

子查询：嵌套在其他查询中的查询，可以多层嵌套，子查询从内向外处理。

#（）中的即为子查询，子查询查询出来的结果作为IN()的值。
Select cust_id
From orders
Where order_num IN (Select order_num From orderitems Where prod_id = 'TNT2');
#作为计算字段使用子查询
#只使用cust_id具有二义性，所有使用了完全限定名orders.cust_id
Select cust_name,cust_state,
(Select count(*)
From orders
Where orders.cust_id=customers.cust_id) AS orders  
From customers
Order by cust_name;

第15章 联结表

联结：数据存储在多个表中，需要用单条Select语句检索出数据。

关系表：多张之间有相同字段的表。

主键：每一行的唯一标识。通过主键值能唯一确定一行。

外键：某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

#列出每个供应商的名字，生产的产品名和产品价格
Select vend_name,prod_name,prod_price
From vendors,products
Where vendors.vend_id=products.vend_id
Order by vend_name,prod_name;

内部联结

#和上一条select语句作用相同
Select vend_name,prod_name,prod_price
From vendors inner join products
on vendors.vend_id=products.vend_id;

第16章 创建高级联结

自联结：自联结比子查询快

Select p1.prod_id,p1.prod_name
From products AS p1,products AS p2
Where p2.prod_id = 'DTNTR'   #过滤出p2表中prod_id = 'DTNTR'这一行
and  p1.vend_id=p2.vend_id   #搜索出p1表中所有与prod_id = 'DTNTR'这行vend_id相同的行。
#与下面语句搜索结果相同
Select prod_id,prod_name
From products
Where vend_id = (Select vend_id From products Where prod_id='DTNRT');

外部联结：和内部联结相比，会检索出orders表中不包含而customers表中包含的cust_id。

#Left outer join 左边的表取全集，右边的表取交集
#Right outer join 右边的表取全集，左边的表取交集
Select customers.cust_id,orders.order_num
From customers Left Outer Join orders
on customers.cust_id=orders.cust_id;

第17章 组合查询

union：将多条Select语句查询的结果合成单个查询结果集输出。Select的字段要相同。重复的行被自动取消。

Select vend_id,prod_id,prod_price
From products
Where prod_price <=5
union
Select vend_id,prod_id,prod_price
From products
Where vend_id IN(1001,1002);

union all：不取消重复的行。

对组合查询的结果进行排序，只能使用一条Order by，放在最后。