1、写在前面的话


这本书是一本MySQL的经典入门书籍,小小的一本,也受到众多网友推荐。之前自己学习的时候是啃的清华大学出版社的计算机系列教材《数据库系统概论》,基础也算是半罐水,糊里糊涂,知识点欠缺梳理。于是,也算是借此机会,从这本书对数据库和SQL部分的知识点进行梳理,记录一下基础的关键的东西,也便于以后翻阅查询,好了,就不叨叨了。






2、MySQL基本操作


  • 命令输入在 mysql> 之后;
  • 命令用;或 \g 结束,仅按Enter不执行命令;
  • 输入 help 获得帮助;
  • 输入 quit 或 exit 退出命令行实用程序



{ }花括号中的内容表示实际内容,而非此处的固定字符。


操作

命令

进入MySQL    

安装目录的bin下打开命令行,输入mysql -u {yourAccountName} -p

显示现有数据库    

show databases;

选择数据库    

use {databaseName};

显示已选数据库的表

show tables;

显示列(字段)

show columns from {tableName}; 或 describe {tableName};

显示授权用户的安全权限

show grants;






3、基本查询 SELECT


操作    

语法

查询单列    

SELECT {columnName} FROM {tableName};

查询多列    

SELECT {columnName1, columnName2} FROM {tableName};

查询所有列    

SELECT * FROM {tableName};



其他关键字:


  • AS
  • 放在字段名后或表名后,表示别名
  • e.g. SELECT {newTableName}.{columnName} AS {newColumnName} FROM {tableName} as {newTableName};
  • e.g. SELECT b.author as 'zuozhe' FROM books as b;


  • DISTINCT
  • 放在SELECT之后,字段名之前,表示互异,使SQL对任何元组只生成一份副本,且应用于所有列
  • e.g. SELECT DISTINCT {columnName} FROM {tableName};


  • ORDER BY
  • 排序,BY后面跟字段名,默认为升序(从小到大)排列,多个排序规则用逗号隔开依次满足条件
  • e.g. SELECT * FROM fruits ORDER BY price;


  • DESC
  • 配合ORDER BY,表示降序排列,放在字段名后,限制单列
  • e.g. SELECT * FROM fruits ORDER BY price DESC;



  • LIMIT beginColumnNum, ColumnAmount
  • 限制结果,指定输出从beginColumnNum开始的(索引从0开始而不是1)的ColumnAmount行记录(常用于分页)
  • e.g. SELECT * FROM {tableName} LIMIT 5, 10  (输出表中包括第6行开始的10条数据)



另外:


  • SQL语句不分大小写,为了便于阅读可以将所有关键字大写,列和表名采用小写;
  • SQL语句可以分一行给出,也可以分成多行;
  • 不需要查询出明确的列时尽量采用通配符 * 可以提高性能。






4、条件查询 WHERE


为了获取特定的结果而指定条件,SQL中根据WHERE子句中指定的搜索条件进行过滤,WHERE跟在表名(FROM子句)之后。


基本操作符(1)

说明

=

等于    

<>

不等于    

!=

不等于    

<

小于    

<=

小于等于    

>

大于    

>=

大于等于    

BETWEEN

指定两值之间(含开始和结束),配合AND(BETWEEN valA AND valB)

IS NULL    

空值检查


注意:


  • 表示要查询某字段为空值,不能使用"=NULL"或"<>NULL",而是"IS NULL"或"IS NOT NULL"



基本操作符(2)

说明

AND    

“与”,同时过滤多个条件子句

OR    

“或”,配合检索匹配任一条件的记录

NOT  

“非”,否定它之后所跟着的条件(常配合IN、BETWEEN、EXISTS等)

IN    

指定条件范围,多值之间逗号隔开(功能类似OR)


注意:


  • NOT优先级高于AND,AND优先级高于OR






5、模糊查询 LIKE


用于查询包含某个关键字的记录。


操作符

说明

LIKE     

表示搜索模式利用通配符匹配


通配符

说明

%

不限长度的字符序列匹配

_

单个字符的序列匹配


转义符

说明

\

表示某些特殊符号,如‘\%’表示匹配百分号字符,而不是通配符






6、分组查询 GROUP BY 


关键字

说明

GROUP BY

根据字段进行分组,必须在WHERE子句之后,ORDER之前

HAVING

过滤分组,用法类似于WHERE,但分组只能用HAVING不能用WHERE


注意:


  • GROUP BY 可以包含多列,即分组嵌套,且嵌套后数据在最后规定的分组上进行汇总
  • 分组列如果有NULL,则NULL会作为一个分组返回(多行NULL将分为一组)
  • GROUP BY 如果用于输出,实际只会输出每组首行,而不是全部






7、聚合函数


函数

说明

COUNT()

求行数,在使用 * 通配符时才不会忽略NULL值的行

AVG()

求平均值,自动忽略NULL值的行

MAX()

求最大值,自动忽略NULL值的行

MIN()    

求最小值,自动忽略NULL值的行

SUM()    

求和,自动忽略NULL值的行


注意:


  • 以上的算值函数,可以利用算术符作用多列,其含义是类似 “SELECT MAX(price*amount) FROM fruits” 如此用法


  • 以上5种聚合函数,在MySQL 5.0 版本之后,其括号中的表达式可以使用前缀关键字DISTINCT
  • 聚合函数多配合分组GROUP BY使用








8、子查询


子查询,即嵌套在其他查询中的查询。在SELECT语句中,子查询总是从内向外分步进行处理。


8.1 子查询过滤


SELECT  
  cust_id
FROM
  orders
WHERE
  order_num IN 
(
SELECT
  order_num
FROM
  orderitems
WHERE
  prod_id = 'TNT2'
)


14


 


1


SELECT


2


cust_id


3


FROM


4


orders


5


WHERE


6


order_num IN


7


(


8


SELECT


9


order_num


10


FROM


11


orderitems


12


WHERE


13


prod_id = 'TNT2'


14


)


  • 子查询应与WHERE匹配相同数量的列,通常是单个列匹配,结合IN等关键字使用



8.2 子查询作为计算字段


SELECT
  cust_name,
  cust_state,
  (
  SELECT
    COUNT(*)
  FROM
    orders
  WHERE
    orders.cust_id = customers.cust_id
  ) AS orders
FROM
  customers
ORDER BY cust_name


14


 


1


SELECT


2


cust_name,


3


cust_state,


4


(


5


SELECT


6


COUNT(*)


7


FROM


8


  orders


9


WHERE


10


orders.cust_id = customers.cust_id


11


) AS orders


12


FROM


13


customers


14


ORDER BY cust_name


  • 涉及外部查询的子查询叫做相关子查询,每次取外部查询的值和子查询所有行分别匹配,再取外部查询下一行和子查询匹配,循环至结束
  • 子查询可以逐步建立用来进行测试和调试,这是很有技巧性的






9、创建计算字段


直接从数据库中检索出转换、计算或格式化过后的数据,而不是检索出数据然后再到客户机程序中重新格式化。



9.1 拼接字段


输出指定格式,如vendors供应商表包含name和location,希望name按照name(location)格式输出,使用Concat函数:


多数DBMS使用 + 或 || 实现拼接,而MySQL使用Concat()实现)


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


5


 


1


SELECT


2


Concat(vend_name, '(', vend_country, ')')


3


FROM


4


vendors


5


ORDER BY vend_name;



Concat()拼接串,把多个串连接起来形成长串,各串之间用逗号隔开。



Trim()删除左侧多余的空格,Trim()删除两端的空格:


SELECT
  Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
FROM
  vendors
ORDER BY vend_name;


5


 


1


SELECT


2


Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title


3


FROM


4


vendors


5


ORDER BY vend_name;



9.2 执行算术计算


可以针对检索出来的数据直接进行基本的算术计算(加减乘除),如:



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


9


 


1


SELECT


2


prod_id,


3


quantity,


4


item_price,


5


quantity*item_price AS expanded_price


6


FROM


7


orderitems


8


WHERE


9


order_num = 20005;






10、处理数据的函数


写在前面的话,SQL语句的通用性很强,可以说是可移植的,虽然部分数据库之间存在差异,可是通常并不是那么难以处理。但是函数的可移植性就比较差了,几乎每种主要的DBMS的实现都支持其他实现不支持的函数,有时候差异还很大。



如果你决定在SQL中使用函数,应该确保做好代码注释,以便将来你或者他人能确切地知道SQL代码的含义。



10.1 文本处理函数


Left()    

返回串左边的字符

Length()    

返回串的长度    

Locate()    

找出串的一个子串

Lower()    

将串转换为小写    

LTrim()    

去掉串左边的空格    

Right()    

返回串右边的字符    

RTrim()    

去掉串右边的空格    

Soundex()    

返回串的soundex值(将任何文本串转换为描述其语音表示的字母数字模式,你可以理解为,变成读音)p70

SubString()    

返回子川的字符    

Upper()

将串转换为大写



10.2 日期和时间处理函数


写在前面的话,MySQL使用的日期格式,无论什么时候都必须为 yyyy-mm-dd,虽然其他日期格式可能也行,但是这个是首选日期格式。


AddDate()    

增加一个日期(天、周等    )

AddTime()    

增加一个时间(时、分等    )

CurDate()    

返回当前日期    

CurTime()    

返回当前时间

Date()    

返回日期时间的日期那部分

DateDiff()    

计算两个日期之差        

Date_Add()    

高度灵活的日期运算函数    

Date_Format()    

返回一个格式化的日期或时间串    

Day()    

返回一个日期的天数部分    

DayOfWeek()    

对于一个日期,返回对应的星期几    

Hour()    

返回一个时间的小时部分    

Minute()    

返回一个时间的分钟部分    

Month()    

返回一个日期的月份部分    

Now()    

返回当前日期和时间    

Second()    

返回一个时间的秒部分    

Time()    

返回一个日期时间的时间部分    

Year()    

返回一个日期的年份部分    



WHERE order_date = '2005-09-01' 可靠吗?假如时间是 2005-09-01 11:30:05,则该匹配会失败;如果我们使用Date()提取日期部分,则 WHERE Date(order_date) = '2005-09-01' 则更可靠了。



检索某年2月的所有数据,WHERE Date(order_date) BETWEEN '2005-02-01' AND '2005-02-28',这种方式可行,但是需要你自己去计算是闰年还是平年,以确定这个月份到底有多少天,比较麻烦,实际上你可以用更简单的方式:WHERE Year(order_date) = 2005 AND Month(order_date) = 9;



10.3 数值处理


数值处理一般用于代数、三角或几何运算,使用并不频繁:


Abs()    

返回一个数的绝对值    

Cos()    

返回一个角度的余弦    

Exp()    

返回一个数的指数值    

Mod()    

返回除操作的余数    

Pi()    

返回圆周率π    

Rand()    

返回一个随机数    

Sin()    

返回一个角度的正弦    

Sqrt()    

返回一个数的平方根    

Tan()    

返回一个角度的正切






11、用正则表达式进行搜索


正则表达式的作用是匹配文本,其概念和基本使用,可以参考在JS篇中的一篇博客《 03标准对象-02-RegExp 正则表达式》,这跟在JS还是在SQL里都没有关系,因为几乎所有种类的程序设计语言、文本编辑器和操作系统等,都支持正则表达式。



REGEXP关键字(Regular Expression,正则表达式),用法类似LIKE:


SELECT
  prod_name
FROM
  products
WHERE
  prod_name REGEXP '.000'
ORDER BY prod_name;


7


 


1


SELECT


2


prod_name


3


FROM


4


products


5


WHERE


6


prod_name REGEXP '.000'


7


ORDER BY prod_name;



上条SQL你可以得到价格尾数带000的记录,如1000或2000都会得到匹配返回。



除了在延伸阅读的博客里的内容,还有一些需要提到的不同的:


(1)匹配特殊字符


用\\为前导,如希望查找 '-',要使用\\- (正则一般只用一个\做转义,但MySQL要求两个,因为它自己要解释一个,正则要解释一个)



(2)匹配字符类


[:alnum:]    

任意字母和数字,同[a-zA-Z0-9]

[:alpha:]    

任意字符,同[a-zA-Z]

[:blank:]    

空格和制表

[:cntrl:]

ASCII控制字符

[:digit:]

任意数字

[:graph:]

与print相同但不包括空格

[:lower:]

任意小写字母

[:print:]

任意可打印字符

[:punct:]

既不在[:alnum:]也不在[:cntrl:]中的任意字符

[:space:]

包括空格在内的任意空白字符

[:upper:]

任意大写字母

[:xdigit:]

任意十六进制数字,同[a-fA-F0-9]



一个例子:


SELECT
  prod_name
FROM
  products
WHERE
  prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;


 


1


SELECT


2


prod_name


3


FROM


4


products


5


WHERE


6


prod_name REGEXP '\\([0-9] sticks?\\)'


7


ORDER BY prod_name;



输出结果:


TNT (1 stick)


TNT (5 sticks)






12、其他

12.1 SELECT子句的顺序


  • SELECT
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY
  • LIMIT