MySQL语法学习笔记
学习之道，非尽心竭力者不能进也！我是唧唧又唧唧，欢迎查看我的笔记，有问题欢迎交流探讨。
SQL是一种结构查询语言，用于查询关系数据库的标准语言，包括若干关键字和一致的语法，便于数据库元件(表、索引、字段等)的建立和操纵。
1.MySQL建库
1.1建库语句
create database emp #建库 名为 emp
default character set utf-8 #设置该库的默认编码格式为 utf-8
collate utf8_general_ci; #设置数据库校对规则。不区分大小写
示例中 utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感。utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感。
1.2删除库
drop database emp;
2.建表
2.1建表模板
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) 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)
) ENGINE=InnoDB;
2.2 主键(PRIMARY KEY)
主键值必须唯一：表中的每个行必须具有唯一的主键值。
如果主键使用单个列，则它的值必须唯一。
迄今为止我们看到的CREATE TABLE例子都是用单个列作为主键。
PRIMARY KEY (cust_id)
如果使用多个列，则这些列的组合值必须唯一 。
创建由多个列组成的主键，应该以逗号分隔的列表给出各列名。
PRIMARY KEY (order_num,order_item)
主键中只能使用不允许NULL值的列，允许NULL值的列不能作为唯一标识。
2.3AUTO_INCREMENT
AUTO_INCREMENT定义列为自增的属性，一般用于主键，每次执行一个INSERT操作时，数值会自动加1
使用的最简单的编号是下一个编号，所谓下一个编号是大于当前最大编号的编号。例如，如果cust_id的最大编号为10005，则插入表中的下一个顾客，可以具有等于10006的 cust_id 。
2.4 默认值
如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值 用CREATE TABLE语句的列定义中的DEFAULT关键字指定
2.5 存储引擎(ENGINE=InnoDB)
与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。
在你使用CREATE TABLE语句时，该引擎具体创建表。
而在你使用SELECT语句 或进行其他数据库处理时，该引擎在内部处理你的请求。
多数时候，此引擎 都隐藏在DBMS内，不需要过多关注它。
以下是几个需要知道的引擎：
InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY在功能等同于MyISAM，但由于数据存储在内存(不是磁盘)中，速度很快(特别适合于临时表)；
MyISAM是一个性能极高的引擎，它支持全文本搜索但不支持事务处理 。
2.6 ALTER TABLE 语句
ALTER TABLE 语句用于在已有的表中添加、删除或修改列。
-- 表中添加列
ALTER TABLE customers
ADD cust_phone char(10)
-- 删除表中的列(请注意，某些数据库系统不允许这种在数据库表中删除列的方式)
ALTER TABLE customers
DROP COLUMN cust_phone
-- 改变表中列的数据类型
-- 修改数量字段的默认值为80
ALTER TABLE customers
MODIFY COLUMN quantity int(11) null default 80 after prod_id
2.7 删除表
DROP TABLE customers
3.SQL处理数据的基本方法
3.1 数据检索(select 语句)
Select 语句语法
Select 检索所有列
Select 检索单个列
Select 检索不同行
Select 的结果限定
# 描述表
DESC products;
# select 语句 检索所有列
SELECT * FROM products;
# select 语句 检索指定的多个列
SELECT prod_id,prod_name,prod_price FROM products;
# select 语句 检索指定的一个列
SELECT vend_id FROM products;
# 去重，DISTINCT 关键词用于返回唯一不同的值
SELECT distinct vend_id FROM products;
# 第1行开始，取前3行
SELECT * FROM products limit 3;
# 第4行开始，取3行
SELECT * FROM products limit 3,3;
3.2 数据排序
ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，可以使用 DESC 关键字。
# 先查看一下这次用的products表
SELECT * from products;
# 单个字段的排序 升序 降序
SELECT prod_id,prod_price from products
ORDER BY prod_price;
# 默认(ASC)是升序，desc 是降序,默认可以不写
SELECT prod_id,prod_price from products
ORDER BY prod_price desc;
# 默认排序a-z ,反序就是z-a
SELECT prod_id,prod_price from products
ORDER BY prod_name desc;
# 多字段排序
SELECT prod_name,prod_price from products
ORDER BY prod_price,prod_name;
# 找出最便宜的产品
SELECT prod_name,prod_price from products
ORDER BY prod_price LIMIT 1;
# 找出最贵的产品
SELECT * from products
ORDER BY prod_price DESC LIMIT 1;
3.3 数据过滤
数据库表一般包含大量的数据，很少需要检索表中所有行。
通常只会根据特定操作或报告的需要提取表数据的子集。
只检索所需数据需要指定搜索条件(search criteria)
搜索条件也称为过滤条件(filter condition)。
在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。
WHERE子句在表名 (FROM子句)之后给出。
3.3.1 使用Where子句
检查单个值
不匹配检查
# 筛选价格是2.5的产品
select * from products where prod_price = 2.5;
# 筛选价格是2.5的产品，并只显示特定的字段
select prod_name,prod_price from products
where prod_price = 2.5;
# WHERE 条件操作符：=、>、=、<=、<>/！=、between * and *(包含首尾)
select prod_name,prod_price from products
where prod_price >= 2.5 and prod_price <10
order by prod_price;
select prod_name,prod_price from products
where prod_price BETWEEN 2.5 and 10
order by prod_price;
练习：
找出价格低于10元的产品
找出价格不是供应商1003制造的产品
找出供应商1001,1003 制造的产品
select prod_name,prod_price from products
where prod_price < 10
order by prod_price;
select vend_id,prod_name,prod_price from products
where vend_id != 1003
order by prod_price;
select vend_id,prod_name,prod_price from products
where vend_id = 1001 or vend_id = 1003;
order by prod_price;
3.3.2 空值检查
select * from products
where prod_desc is null
3.3.3 And、Or、In、Not操作及其计算次序
AND & OR 运算符用于基于一个以上的条件对记录进行过滤。
如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
先and语句后执行or语句
# where 组合子句之 and 操作(交集)
select * from products where vend_id=1003 and prod_price <=10
ORDER BY prod_price;
# where 组合子句之 or 操作(并集)
select * from products where vend_id=1003 or vend_id =1002；
# where 组合子句之 AND 和 OR 的结合使用(使用圆括号来组成复杂的表达式)
SELECT * FROM products
WHERE prod_price <=10
AND (vend_id=1003 or vend_id =1002);
IN 操作符允许在 WHERE 子句中规定多个值
‘=’ 规定一个值
# where 组合子句之 in 操作
select * from products where
vend_id in (1001,1005,1002);
# where 组合子句之 in 与 = 的转换
select * from products where
vend_id = 1001 or vend_id = 1005 or vend_id = 1002;
# where 组合子句之 not 操作
select * from products where
vend_id not in (1002);
not操作
# where 组合子句之 not 操作
select * from products where
vend_id not in (1002);
3.3.4 通配符(模糊匹配)
通配符可用于替代字符串中的任何其他字符。
% 替代 0 个或多个字符
“_” 替代1个字符。
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式
# 找到以 jet开头的产品
select prod_id ,prod_name from products
where prod_name like 'jet%'
select prod_id ,prod_name from products
where prod_name like '%anvil%'
select prod_id ,prod_name from products
where prod_name like 's%e'
select prod_id ,prod_name from products
where prod_name like '_ ton anvil'
select prod_id ,prod_name from products
where prod_name like '__ ton anvil'
注意：
不要过度使用通配符，如果其他操作符能达到相同的目的，应优先使用其他操作符。
非必要情况下，不要把通配符用在搜索模式的开始处，因为这样搜索起来是最慢的。
仔细注意通配符的位置，如果放错地方可能不会返回想要的数据。
4.SQL处理数据高级方法
4.1正则表达式
正则表达式 用特殊的字符集合与一个文本串进行比较，过滤检索出想要的数据
正则表达式是用来匹配文本的特殊的串(字符集合)
如果你想从一个文本文件中提取电话号码，可以使用正则表达式。
如果你需要查找名字中间有数字的所有文件，可以使用一个 正则表达式。
如果你想在一个文本块中找到所有重复的单词，可以使用一 个正则表达式。
如果你想替换一个页面中的所有URL为这些URL的实际 HTML链接，也可以使用一个正则表达式(对于最后这个例子，或者是两个正则表达式)。
4.1.1基本字符串匹配
# 使用 like 关键字和通配符 %
select prod_name from products
where prod_name like '%1000';
# 使用正则表达式 REGEXP
select prod_name from products
where prod_name REGEXP '1000';
# 可以检索出prod_name 中所有含有‘1000’的行
4.1.2 特殊字符“ .” 的使用
“ .” 在正则表达式中表示匹配任意一个字符
# 使用 like 关键字和通配符 _
select prod_name from products
where prod_name like 'JetPack _000';
# 使用正则表达式 REGEXP 和特殊字符“.”
select prod_name from products
where prod_name REGEXP '.000';
在这里会不会有人觉得奇怪，“.” 表示的是匹配任意一个字符，但结果显示的 JetPack 1000、JetPack
2000，000的前面可不止一个字符，为什么能这样匹配呢？
好好理解这句话 “正则表达式是用来匹配文本的特殊的串”
你会发现利用正则表达式我们可以匹配出相应字段中所有含有需要匹配的文本的行。例子中我们需要匹配的是‘.000’，.
可以代表任何一个字符，所以我们把 JetPack 1000、JetPack 2000
匹配出来了，1000，2000就是我们想要匹配的文本。不管他们前后还有没有别的字符都会被查询的到。
对比 关键字 like 与通配符的联合使用就无法达到这种效果了。
4.1.3 使用 or 进行匹配(条件匹配)
此 or 不是真的 or ，而是使用竖线 “|” 表示搜索 两个匹配文本串的其中一个
# 检索prod_name 中所有含有‘1000’或者‘2000’的行
select prod_name from products
where prod_name REGEXP '1000|2000';
正则表达式中可使用多个 or 条件
select prod_name from products
where prod_name REGEXP '1000|2000|anvil';
4.1.4 []匹配
”[]“代表需要匹配[]中所包含的任意一个字符
4.1.4.1 匹配几个字符之一
select prod_name from products
where prod_name REGEXP '[12] ton';
这里使用正则表达式 [12] ton 。 [12] 定义了一组字符1，2；可以匹配 1 ton 或者 2 ton.
其实，[] 是另一种形式的 or 语句
4.1.4.2 匹配范围
[]匹配[]中所包含的任意一个字符
[0-9] 匹配0到9的任意数字字符
[1-3] 匹配1到3的任意数字字符
[6-9] 匹配6到9的任意数字字符
[a-z] 匹配a到z 的任意字母字符
select prod_name from products
where prod_name REGEXP '[1-5] ton';
# 匹配1到5任意一个数字，所以返回了1 ton、2 ton、5 ton
4.1.4.3 排他符[^]
[^] 匹配未包含在[]中的任意字符。即，将匹配除指定字符外的任何东西。
在集合开始处放置一个 ^
例如，[^12]，会匹配除1，2外的任何东西
select prod_name from products
where prod_name REGEXP '[^345] ton'
# 不会出现 3 ton、4 ton、5 ton
4.1.5 特殊字符的匹配(. [] | -)
匹配特殊字符(. [] | -)时，需要使用 转义符(两个反斜杠\\)
\\- 表示查找 -，\\.表示查找 .
select prod_name from products
where prod_name REGEXP '\\.';
#查找 .
\\也用来引用元字符(具有特殊含义的字符)
元字符
说明
\\f
换页
\\n
换行
\\r
回车
\\t
制表
\\v
纵向制表
4.1.6 匹配字符类
[:digit:] 任意数字 (同[0-9])
[:alnum:] 任意字母和数字 (同[a-zA-Z0-9])
[:alpha:] 任意字母 (同[a-zA-Z])
[:lower:] 任意小写字母 (同[a-z])
[:upper:] 任意大写字母 (同[A-Z])
select prod_name from products
where prod_name REGEXP '[[:digit:]]{4}'
4.1.7 匹配多个实例，重复元字符
元字符
说明
*
0个或多个匹配
+
1个或多个匹配 (同{1,})
?
0个或1个匹配(同{0,1})
{n}
指定数目的匹配
{n,}
不少于指定数目的匹配
{n,m}
匹配数目的范围(m不超过255)
#匹配连在一起的任意4位数字的行
select prod_name from products
here prod_name REGEXP '[[:digit:]]{4}';
select prod_name from products
where prod_name REGEXP '[0-9][0-9][0-9][0-9]';
select prod_name from products
where prod_name REGEXP '\\([0-9] sticks?\\)';
# \\( 、\\) 使用转义符，[0-9]匹配1到9任意数字
#s? 中 使用 ？表示 s 可以出现0次或1次，所以出现了结果stick、sticks
？ 匹配它前面的任何字符的0次或1次出现，换句话说，？前面的字符有(1个)或者没有(0个)都能被匹配
4.1.8 定位符，元字符
元字符
说明
^
文本的开始
$
文本的结尾
[[:<:>
词的开始
[[:>:]]
词的结尾
select prod_name from products
where prod_name REGEXP '^[\\.]'
在这里，我们可以回顾一下排他符[^ ]
总结一下^ 的用途：
1.在集合中 [^],表示不匹配集合所包含的字符
2.匹配输入字符串的开始位置。
4.2 计算字段
4.3 MySQL数据处理函数
4.4 数据汇聚
4.5 数据分组
4.6 子查询
4.7 联结表
4.8 高级联结表
5.SQL高级数据查询
5.1 组合查询
5.2 全文检索
5.2 插入数据
5.3 更新和删除数据
5.4 视图
5.5 存储过程
5.6 游标
5.7 触发器
2032
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
