MySQL学习笔记---基础概念和一些基础SQL语句（一）

作者：zhangjunqiang

MySQL学习笔记，以防遗忘

数据库基本概念

数据库：保存有组织的数据的容器（通常是一个文件或一组文件）。

表：某种特定类型数据的结构化清单，数据库中的表都有一个唯一的名字用来标识自己。

模式：关于数据库和表的布局及特性的信息。

列：表中的一个字段。所有表都是由一个或多个列组成，每个列都有对应的数据类型。

行：表中的一个记录。

主键：一列（或一组列），其值能够唯一区分表中每个行（每条记录）。

任意两行都不具有相同的主键值，每个行都必须具有一个主键值（主键值列不允许NULL值）。

SQL：一种专门用来与数据库通信的语言，SQL不依赖DBMS的存在而存在。

MySQL命令

mysql -user(u) username -password(p) password or none  MySQL登录

USE databasename; 使用数据库

SHOW

SHOW DATABASES;(;可用\g代替)：显示当前安装的数据库

SHOW TABLES; 查看当前数据库中的可用表的列表

SHOW COLUMNS FROM tablename; 查看某个表中的内容，它对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息

DESCRIBE tablename; 和 SHOW COLUMNS FROM tablename; 具有相同的功能，前者是后者的缩写版。

SHOW STATUS; 用来显示广泛的服务器状态信息。

SHOW CREATE DATABASE databasename; 用来显示创建特定数据库的MySQL语句。

SHOW CREATE TABLE tablename; 用来显示创建特定表的MySQL语句。

SHOW GRANTS; 用来显示授权用户（所有用户或特定用户）的安全权限。

SHOW ERRORS; 用来显示服务器错误

SHOW WARNINGS; 用来显示服务器的警告消息

SELECT

SELECT columnname FROM tablename; 用来检索数据表中的coulumnname列中的内容

SELECT row1,row2... FROM tablename; 用来检索数据表中的row1,row2...行的内容

SELECT * FROM tablename; *为通配符，用来检索表中的所有列

SELECT DISTINCT key FROM tablename; 用来检索表中不同值key的行

SELECT key FROM tablename LIMIT num; 返回key的前num行数据

SELECT key FROM tablename LIMIT num1,num2; 返回表中key的以num1为起点后num2行

SELECT tablename.colunname FROM tablename; 用来检索数据表中的coulumnname列中的内容，但是同时列出了表明和列名(完全限定)

SELECT coulumname FROM tablename ORDER BY key; 以key为导向排序(升序ASC,可省略)

SELECT coulumname FROM tablename ORDER BY key DESC; 以key为导向排序(降序，注：当有多个key时DESC只作用于直接位于其前面的key)

SELECT coulumname FROM tablename WHERE condition; 按照条件condition从表中检索指定列中的数据(WHERE子句在FROM子句后给出)

SELECT coulumname FROM tablename WHERE key BETWEEN c1 AND c2; 从表中检索指定列中在条件c1和c2之间的数据(c1 <= c2)

SELECT coulumname FROM tablename WHERE key IS NULL; 从表中检索key中为NULL的数据

操作符

用来联结或改变WHERE子句中的子句的关键字。

AND操作符：

用在WHERE子句中的关键字，用来指示检索满足 所有 给定条件的行。

OR操作符：

用在WHERE子句中的关键字，用来表示检索匹配 任一 给定条件的行。

IN操作符：

用在WHERE子句中的关键字，用来指定要匹配值的清单的关键字(取合法值的由逗号分割开的清单，全在圆括号中)，功能与OR相当。

NOT操作符：

用在WHERE子句中的关键字，用来否定它之后所跟的任何条件。

BETWEEN操作符：

用在WHERE子句中的关键字，用来检查某个范围的值。

通配符

用来匹配值的一部分的特殊字符。为在搜索子句中使用通配符，必须使用LIKE谓词。LIKE指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

%通配符：

表示任何字符出现的任一次数。

_通配符：

下划线通配符 _ 用途与%一样，但下划线只匹配单个字符而不是多个字符。

通配符使用技巧

1、不要过度使用通配符。如果其他操作能达到相同的目的，应该使用其他操作符。

2、在确实需要使用通配符时除非绝对有必要，否则不要把它们用在搜索模式的最开始处，因为这样是最慢的。

3、仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

计算字段

1、Concat拼接字段：

把多个串链接起来形成一个较长的串，需要一个或多个指定的串，各个串之间用逗号隔开。

例如：SELECT Concat(vend_name,'(',vend_country,')') FROM vendors ORDER BY vend_name;

2、去掉返回的值中的空格：

(1)、RTrim()去掉右侧多余的空格来整理数据。

(2)、LTrim()去掉左侧多余的空格来整理数据。

(3)、Trim()去掉左右两边多余的空格来整理数据。

3、执行算数运算(MySQL支持 加减乘除运算和圆括号改变运算优先级)：

计算字段另一个用途就是对检索出的数据进行算术计算。

例如：SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM orderitems WHERE order_num = 20005;

事件处理函数

函数一般是在数据上执行的，它给数据的转换和处理提供了方便。大多数SQL实现支持用于处理文本串的函数、用于在数值数据上进行算术运算操作的函数、用于处理日期和时间并提取特定成分的日期和时间函数、返回DBMS正使用的特殊信息的函数

1、常见的文本处理函数：

(1)、Upper(str)：将文本str转换为大写。

例：SELECT Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

(2)、Left(str, len)：返回串str左边的len个字符。

例：SELECT Left(vend_name, 3) AS vend_name_upcase FROM vendors ORDER BY vend_name;

(3)、Length(str)：返回串str的长度。

例：SELECT Length(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

(4)、Locate(substr, str)：返回串str的一个substr子串的第一个字符出现的位置,如果substr不再str中则返回0。该函数的另一个版本是Locate(substr,str,pos),表示从pos位置开始在str中寻找substr。

例：SELECT Locate('ACM', vend_name) FROM vendors ORDER BY vend_name;

注意：

1、ORDER BY子句必须位于FROM子句之后。

2、当ORDER BY 子句和LIMIT子句同时使用时LIMIT子句必须位于ORDER BY子句之后。

3、同时使用WHERE子句和ORDER BY子句时应将ORDER BY子句置于WHERE子句之后。

4、WHERE子句支持 =、>、<、!=、<>(不等于)、>=、<=、BETWEEN(指定的两个值之间)过滤操作。

5、AND和OR操作符混合使用时要注意AND操作符的计算次序比OR操作符号的计算次序要高(操作符优先级高)，所以一般有多个操作符时要加上圆括号消除歧义(即使不这样做也是正确的)

6、MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反(其他DBMS允许使用NOT对各种条件取反)。

7、尾空格可能会干扰通配符匹配，例如：'%anvil'如果anvil后有一个空格则不会被匹配出来。可使用'%anvil%'或者使用函数解决此问题。

8、%通配符不会匹配到NULL。

9、SELECT语句的拼接完成的列是没有名字的，它只是一个值，这种是无法在客户机中直接使用的，所以可以用AS关键字赋予一个别名(也称为导出列)。

例如：SELECT Concat(vend_name,'(',vend_country,')') AS vend_title FROM vendors ORDER BY vend_name;

聚集函数：

运行在行组上，计算和返回单个值的函数（需要汇总数据而不需要检索出来）

ACG(column)：返回某列的平均值。

COUNT(column)：返回某列的行数。COUNT(*)：返回所有列的和。

MIN(column)：返回某列的最小值。

MAX(column)：返回某列的最大值。

SUM(column)：返回某列的和。

DISTINCT：只包含不同值

分组：

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

创建分组：GROUP BY子句，GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

WITH ROLLUP：可以得到每个分组汇总级别的值（和ORDER BY子句互斥）。

HAVING：HAVING过滤分组，WHERE过滤列，HAVING位于GROUP BY子句之前，HAVING位于GROUP BY子句之后。（WHERE在分组前进行过滤，HAVING在分组后进行过滤）

GROUP BY和ORDER BY子句的区别：

1、OEDER BY是排序后的输出而GROUP BY是分组的顺序（不一定是期望输出的顺序）

2、ORDER BY任意列都可以使用，而GROUP BY只可能使用选择列或表达式列，而且必须使用每个选择列表达式。

3、ORDER BY不是必须出现在SQL语句中，但是如果与聚集函数一起使用列（或表达式）GROUP BY子句必须存在于SQL语句中。

SELECT 子句顺序：

子查询：

嵌套在其他查询中的查询。如：

SELECT cust_name

FROM customers

WHERE cust_id IN

(

SELECT cust_id

FROM orders

WHERE order_num IN

(

SELECT order_num

FROM orderitems

WHERE prod_id = 'TNT2'

)

);

一些概念

完全限定：表名.列名

相关子查询：涉及外部查询的子查询

关系表：将不同数据分别存储在不同数据表中，用相应的关系进行连接。

主键：每个表都有一个唯一的标识。

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