Mysql必知必会 笔记(一)
一、数据库基本概念
数据库
数据库(database)保存有组织的数据的容器,通常是一个文件或一组文件。
数据库管理系统(DBMS)使用DBMS访问数据库,可理解为数据库软件。
表和模式
表(table) 某种特定类型数据的结构化清单。同一个数据库中表名唯一。
模式(schema) 关于数据库和表的布局及特性的信息。
列和数据类型
列(column) 表中的一个字段,所有表由一个或多个列组成。
数据类型(datatype) 数据库中每个列都有相应的数据类型,数值/字符/……。
行
行(row) 或称为数据库记录。
主键
主键(primary key) 一列或一组列,其值能够唯一区分表中每个行。任意两行都不具有相同的主键值,每个行都必须有一个主键值。
主键的好习惯:不更新主键列中的值;不重用主键列的值;不在主键列中使用可能会更改的值。
外键
SQL
Structured Query Language, 结构化查询语言。
二、Mysql
实用程序
- 命令输入在mysql>之后
- 命令用;或\g结束
- 输入help或\h获得帮助,help select获得使用SELECT语句的帮助
- 输入quit或exit推出命令行实用程序。
常用命令
USE crashcourse //选择数据库
SHOW DATABASES //返回可用数据库的一个列表
SHOW TABLES //返回当前选择的数据库内可用表的列表
SHOW COLUMNS FROM customers //返回指定表名中每个字段的行
DESCRIBE customers //SHOW COLUMNS FROM 语句的快捷方式
SHOW STATUS //用于显示广泛的服务器状态信息
SHOW CREATE DATABASE/TABLE //创建特定数据库或表
SHOW GRANTS //显示授予用户的安全权限
SHOW ERRORS/WARNINGS //显示服务器错误或警告消息
三、语句总结
1. 检索数据
FROM子句
SELECT [column_name] FROM [table_name] //检索单个列
SELECT [column_name1, column_name2, ...] FROM [table_name] //检索多个列
SELECT * FROM [table_name] //检索所有列
SELECT DISINCT [column_name] FROM [table_name] //唯一返回匹配的行,若有重复只返回一次
SELECT [column_name] FROM [table_name] LIMIT n //检索单个列,返回不多于n行
SELECT [column_name] FROM [table_name] LIMIT n,m //检索单个列,返回从n行开始的m行
SELECT [column_name] FROM [table_name] LIMIT m OFFSET n //从行3开始取4行
SELECT [table_name.column_name] FROM [database_name.table_name] //使用完全限定的表名
注意事项
- 多条SQL语句必须以分号分隔,单条不需要,但通常加上;
- SQL语句不区分大小写,有些版本的标识符可能不同;
- 处理SQL语句时空格忽略,分行便于阅读和调试;
- 不能部分使用DISTINCT,除非多列完全不同;
- 行数从行0开始计;
2. 排序检索数据
ORDER BY子句
SELECT [column_name] FROM [table_name] ORDER BY [column_name] //取一列或多列的名字,据此对输出排序
SELECT [column_name1, column_name2, ...] FROM [table_name] ORDER BY [column_name1, column_name3] //取一列或多列的名字,据此对输出排序
DESC关键字
SELECT [column_name1, column_name2, ...] FROM [table_name] ORDER BY [column_name2] DESC //降序排列
SELECT [column_name1, column_name2, ...] FROM [table_name] ORDER BY [column_name2] DESC, [column_name1] //降序排列,再按column_name1排列
SELECT [column_name1] FROM [table_name] ORDER BY [column_name1] DESC/ASC LIMIT 1 //找出按column_name1排序的最大或最小值
注意事项
- DESC关键字只应用到直接位于其前面的列名,若想多个列降序,必须对每个列指定DESC关键字。升序是ASC(ASCENDING);
- ORDER BY子句必须是SELECT语句中的最后一条子句。
3. 过滤数据
SELECT [column_name1, column_name2, ...] FROM [table_name] WHERE [搜索条件] //返回符合搜索条件的行
SELECT [column_name1, column_name2, ...] FROM [table_name] WHERE [column_name1] BETWEEN m AND n //AND关键字
SELECT [column_name1, column_name2, ...] FROM [table_name] WHERE [column_name1] IS NULL //IS NULL子句空值检查(不是指值为0)
WHERE子句操作符
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
<> | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定两个值之间 |
AND操作符
SELECT [prod_id, prod_price, prod_name]
FROM [products]
WHERE [vend_id = 1003] AND [prod_price <= 10];
OR操作符
SELECT [prod_price, prod_name]
FROM [products]
WHERE [vend_id = 1002] OR [vend_id = 1003];
//由供应商1003制造的任何价格为10美元(含)以上的产品,或者由供应商1002制造的任何产品
SELECT [prod_price, prod_name]
FROM [products]
WHERE [vend_id = 1002] OR [vend_id = 1003] AND [prod_price >= 10];
IN操作符
用来指定条件范围,范围中的每个条件都可以进行匹配,匹配条件在圆括号中逗号分隔,功能等同于OR。
SELECT [prod_price, prod_name]
FROM [products]
WHERE [vend_id] IN (1002, 1003)
order by [prod_name];
NOT操作符
NOT与IN联合使用便于找出与条件列表不匹配的行。
SELECT [prod_price, prod_name]
FROM [products]
WHERE [vend_id] NOT IN (1002, 1003)
order by [prod_name];
注意事项
- 同时使用ORDER BY和WHERE子句时,ORDER BY位于WHERE之后;
- MySQL匹配默认不区分大小写;
- 单引号用来限定字符串;
- 过滤选择出不具有特定值的行时,无法返回具有NULL值的行,因此要验证返回数据中确实给出了被过滤列具有NULL的行;
- SQL在处理OR操作符之前,优先处理AND操作符。
- IN操作符一般比OR操作符清单执行更快;
- MySQL支持NOT对IN/BETWEEEN/EXISTS子句取反,与其他DBMS允许使用NOT对各种条件取反不同。
4. 用通配符进行过滤
通配符(wildcard) 用来匹配值的一部分的特殊字符。
搜索模式(search pattern) 由字面值、通配符或两者组合构成的搜索条件。
LIKE操作符 本质是谓词,指示MySQL后面跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
百分号(%)通配符
%表示任何字符出现任意次数,可以位于模式的两端或中间
//检索任意jet起头的词,%告诉MySQL接受jet之后的任意字符
SELECT [prod_id, prod_name]
FROM [products]
WHERE [prod_name] LIKE 'jet%';
//匹配任何位置包含文本anvil的值
SELECT [prod_id, prod_name]
FROM [products]
WHERE [prod_name] LIKE '%anvil%';
下划线(_)通配符
下划线只匹配单个字符
SELECT [prod_id, prod_name]
FROM [products]
WHERE [prod_name] LIKE '_ ton anvil';
注意事项
- 根据MySQL的配置方式,搜索区分大小写;
- 尾空格可能会干扰通配符匹配,例如在保存词anvil时,如果后面有一个或多个空格,则子句WHERE prod_name LIKE '%anvil‘ 将不会匹配他们。解决方法是在搜索模式最后加一个%(%可以匹配0个字符),或使用函数去掉首尾空格。
- 通配符置于搜索模式的开始处,搜索起来是最慢的。除非有必要否则不要用在搜索模式的开始。通配符不要过度使用。
5. 用正则表达式进行搜索
关键字REGEXP
REGEXP后面的内容作为正则表达式处理
//匹配出JetPack 1000和JetPack 2000
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
OR匹配
为搜索两个串之一,使用 |
//匹配出JetPack 1000和JetPack 2000,多个OR条件可并入单个正则表达式
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
匹配几个字符之一
匹配特定字符,可指定一组[和]括起来的字符完成。
//匹配出1 ton anvil和2 ton anvil
//[]是另一种形式的OR语句
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
//检索‘1’或‘2’或’3 ton'
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name;
^表示否定一个字符集,如
[^123] 匹配除123外的任何东西
匹配范围
如[1-3]和 [0-9], [a-z]匹配任意字母字符。
匹配特殊字符
用\作为前导,如’\\-‘表示查找-,’\\.'表示查找.。
元字符 | 说明 |
---|---|
\\f | 换页 |
\\n | 换行 |
\\r | 回车 |
\\t | 制表 |
\\v | 纵向制表 |
\\\ | 反斜杠 |
匹配字符类
类 | 说明 |
---|---|
[:alnum:] | 任意字母和数字(同[a-zA-Z0-9]) |
[:alpha:] | 任意字符(同[a-zA-Z]) |
[:blank:] | 空格和制表(同[\\t]) |
[:cntrl:] | ASCII控制字符(ASCII 0-31和127) |
[:digit:] | 任意数字(同[0-9]) |
[:graph:] | 与[:print:]相同但不包括空格 |
[:lower:] | 任意小写字母(同[a-z]) |
[:upper:] | 任意大写字母(同[A-Z]) |
[:print:] | 任意可打印字符 |
[:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字符 |
[:space:] | 包括空格在内的任意空白字符(同[\\f \\n \\r \\t \\v]) |
[:xdigit:] | 任意十六进制数字(同[a-fA-F0-9]) |
匹配多个实例
元字符 | 说明 |
---|---|
* | 0个或多个匹配 |
+ | 1个或多个匹配,同{1,} |
? | 0个或1个匹配,同{0,1} |
{n} | 指定数目的匹配 |
{n,} | 不少于数目的匹配 |
{n,m} | 匹配数目的范围(m不超过225) |
\\([0-9]sticks?\\)表示:\\(匹配(,[0-9]匹配任意数字,sticks?匹配stick和sticks,\\)匹配)
[[:digit:]]{4}表示:匹配连在一起的4位数字
定位符
匹配特定位置的文本
元字符 | 说明 |
---|---|
^ | 文本的开始 |
$ | 文本的结尾 |
[[:<:]] | 词的开始 |
[[:>:]] | 词的结尾 |
'^[0-9\\.]'表示:在.或任意数字为串中第一个字符时才匹配
注意事项
- 匹配不区分大小写,为区分大小写可在匹配条件前使用BINARY关键字;
- 如果被匹配的文本仅在列值中出现,LIKE将不会找到它,相应的行也不会被返回除非使用通配符。而REGEXP在列值内进行匹配,如果被匹配的文本在列值中出现,REGEXP将会找到它,相应的行将被返回;LIKE匹配整个串而REGEXP匹配子串;
- MySQL要求两个反斜杠转义特殊字符;
- ^在集合中用来否定该集合,否则用来指串的开始处;
- 正则表达式测试:REGEXP检查返回0(不匹配)或1(匹配),如SELECT ‘hello’ REGEXP ’[0-9]’ 返回0,因为hello中没有数字。