mysql 学习笔记

这学期就开了数据库， 既然开了就好好学学。

以下是记录一些知识点。方便自己记忆。

1.命令输入到mysql>之后， 命令用；或者\g结束，按enter不执行命令。

source + 路径名 + 文件名；   导入数据库

2.连接mysql 需要信息：主机名（本地为localhost），端口（默认端口3306），合法的用户名， 应户口令。

3.先用USE命令打开数据库，才能读取其中的数据。  使用crashcourse数据库 ， USE crashcourse

4.SHOW DATABASES；    

返回可用的数据库列表。

SHOW TABLES;

显示数据库内的表的列表

show columns from + 表名， 它对每个字段返回一行 等价于 DESCRIBE 表名   （DESCRIBE 是个SHOW COLUMNS 快捷键）

5.自动增量：在每行添加到表中时， mysql可以自动的为每个行分配下一个可用编号，不用手动添加

6.SHOW STATUS 显示的是MYSQL服务器运行的一些统计信息

   SHOW CREATE DATABASE  + 数据库名, SHOW CREATE TABLE + 表名           分别用来显示创建特定数据库或表的mysql语句

   SHOW GRANTS 用来显示授予用户的安全权限；

   SHOW ERRORS , SHOW WARNINGS    显示服务器错误或警告消息

7.经常使用的就是SELECT语句， 从一个或多个表中检索信息。

7.1 SELECT 列名 FROM 表名

ps:sql 语句一般不区分大小写， sql语句结束时加上；号是个好习惯，使用空格便于阅读和调试。

7.2 检索多个列

select 列名，列名，.. + from+ 表名。

select  distinct（独特的）+   列名 +  from  +  表名。     去重后显示

select + 列名 + from + 表名 + limit + 行数（或者开始行数，行数）。      限定显示的行数（显示从开始行数的下一行开始）

select * from + 表名   检测所有列 （一般尽量别使用* 通配符， 除非不要， 可能会降低性能）

分清楚数据库， 表，和列， 分清命令后面加的到底是什么。

有一些列名需要完全限定，表名.列名。  

8.排序检索数据（）

8.1 SELECT + 列名  FROM + 表名 + ORDER BY 一个或多个列名字 （对此输出进行排序）

也可以按照多个列名来排序

8.2指定方向排序,默认为升序

SELECT + 列名 FROM + 表名 + ORDER BY 一个或多个列名字 DESC；

对多个列上进行排序， 必须对每个列指定desc关键字

添加上limit 关键字就可以找到最大或者最小

9.过滤数据 主要是select 的 where 子句指定搜索条件

9.1 有时候只需要提取表的特定数据， 只检索所需数据需要指定搜索条件， 搜索条件也称为过滤条件。

where + 条件

=              等于

<>  , !=    不等于

<              大于

<=            大于等于

>              小于

>=            小于等于

BETWEEN 指定的两个值之间   两个值之间加 AND

9.2 检测单个值

例如 where prod_name = ' fuses'  , prod_name 是表名， ‘ ’中的mysql 在执行时不区分大小写

9.3 AND操作符。

使用AND操作符可以给WHERE句子添加条件。

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

9.4 OR操作符

检索匹配任一条件的行。

AND 和 OR 操作符有优先级    AND的优先级比OR高

解决优先级问题通过使用括号（）

9.5 IN和NOT操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

IN （范围）。一般使用IN， 因为它更快

NOT 操作符， 否定跟在它后面的条件

10.用通配符进行过滤

10.1 LIKE操作符

百分号（%）通配符，搜索串中，%表示任何字符出现任意次数。

%jet 搜索jet开头的词。

%jet%搜索中间包含Jet的词， 前后无所谓

通配符不会匹配NULL

下划线（_）通配符， 搜索中， 匹配单个字符。

通配符使用技巧：

通配符很有用，但是功能是有代价的，通配符搜索的时间会比一般的时间更长。

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

2.不要把通配符放在搜索模式的开始处，这样搜索是最慢的。

3.注意通配符的位置， 不要放错了位置

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

正则表达式是用来匹配文本特殊的串

mysql 里面的正则表达式只是正则表达式里面的一个非常小的子集

关键字REGEXP ‘  ’           regular expression

"  . ”  是正则表达式语言中的一个特殊的字符，它表示匹配任意一个字符

like 是在列内寻找， regexp是在列值内寻找, like匹配整个串， 而regexp匹配子串

匹配一般不区分大小写， 可以使用关键字BINARY

OR 用 | 来代替 （注意带括号 1|2|3 ton）

[123]   意义：1或2或3

[^123]   意义：不包含1或2或3

[0-9]    意义：匹配范围

匹配上述关键字字符需要加上"  \\  "

\\f          换页

\\n         换行

\\r          回车

\\t           制表

\\v          纵向制表

\\\           \本身

匹配字符类

    [:alnum:]  任意字母和数字（通［a-zA-Z0-9］）
    [:alpha:]    任意字符(同[a-zA-Z])
    [:blank:]   空格和制表符(同[\\t])
    [:digit:]    任意数字(同[0-9])
    [:lower:]  任意小写字母
    [:upper:]   任意大写字母
    [:space:]   包括空格在内的任意空白字符

匹配多个实例，关于重复元字符
   
    *     0个或者多个匹配
    +    1个或者多个匹配（等于{1,}）
    ？    1个或者多个匹配（等于{0,1})
    {n}   指定数目的匹配
{n,}   不少于指定数目的匹配
{n,m}   匹配数目的范围（m不超过255）

定位符
 

^  文本的开始
&  文本的结尾
[[:<:]]  词的开始
[[:>:]]  词的结尾

可以简单的测试正则表达式
select 'hello'  regexp '[0-9]' ;
看返回值，返回1是匹配成功， 返回0是匹配失败。



12.创建计算字段

12.1 拼接字段
拼接，将两个值拼接成一个值
Concat( )函数来拼接两个列。
其他的dbms使用+ 或者 || 来实现拼接
RTrim( ) 函数删除右侧多余空格
LTrim( ) 函数删除左侧多余空格

12.2 使用别名

新计算的列需要使用一个别名， 别名用 AS 关键字来实现

12.3 执行算数计算

+ - * / d

例如：

select prod_id, prod_price, prod_num, prod_price*prod_num AS price from products where ...

可以直接测试计算

select + 需要测试的数据

13. 使用数据处理函数

和大多数其他语言一样， mysql也支持函数来处理数据。

sql函数可移植性并不强， 如果确定使用函数， 最好做好代码注释。

13.1 使用函数

RTrim( )右对齐（去掉空格）

LTrim( )左对齐

Upper( )将文本转换为大写

left( ) 返回串左边的字符

LengthI( ) 返回串的长度

Locate( ) 找出串的一个子串

Lower ( ) 将串转换为小写

Right ( ) 返回串右边的字符

Soundex ( ) 返回串的soundex( 探测法 )：将任何文本串转换为描述其语音表示的字母模式的算法， Soundex考虑了类似的发音字符和音节， 使得能对串进行发音比较而不是字母比较   也就是按照发音来搜索数据， 提供了更加方便的一种模式

substring ( ) 返回子串的字符

13.1

soundex( 列名) = soundex( 字符串 )；  显示的结果是只要和soundex(' 字符串 ') 的音节，发音相近的字符串都会显示

13.2 日期和时间处理函数

Adddata( )  增加一个日期（天， 周）

addtime( )  增加一个时间（时， 分）

curdata( )   返回当前日期

curtime( )   返回当前时间

data( )         返回日期时间的日期部分

datediff( )    计算两个日期的差值

date_add( )高度灵活的日期计算函数

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

day( )           返回一个日期的天数部分

dayofweek( ) 返回日期对应的星期几

hour( ), minute( ), month( ) 返回时间的小时，分钟， 月份部分

Now( )          返回当前日期和时间

Second( )   返回时间的秒部分

time( )          返回日期时间的时间部分

year( )           返回日期的年份部分

mysql> select order_num, cust_id from orders where date(order_date) = '2005-09-01';比mysql> select order_num, cust_id from orders where order_date = '2005-09-01';更加安全可靠， 因为2005-09-01只是日期的一部分, 可能会比较失败

所以如果是日期， 请使用data( ), 这是一个良好的习惯。

13.3 数值处理函数

abs( )   返回一个数的绝对值

cos( )   返回一个角度的余弦

exp( )    返回一个数的指数值

mod( )  返回除操作的余数

pi( )       返回圆周率

rang( )  返回一个数的随机数

sin( )     返回一个角度的正弦

sqrt( )    返回一个数的平方根

tan( )      返回一个数的角度的正切

14 汇总数据

有时我们可能并不需要实际数据， 而是需要一些数据的和，平均值等等

sql提供了5个聚集函数     聚集函数： 运行在行组上， 计算和返回单个值的函数

AVG( )    返回某列的平均值

COUNT( ) 返回某列的行数

MAX( )    返回某列的最大值

MIN( )     返回某列的最小值

SUM( )    返回某列之和

14.1 AVG（）

AVG( ) 通过对表中行数计数并计算特定列值之和， 求得平均值。

也可以返回特定列或行的平均值

AVG（）函数忽略NULL

14.2 COUNT（） 函数

COUNT（*）对表中行的数目进行计数， 不管表列中包含的是空值（NULL）还是非空值

COUNT（column）对特定列中具有值的行进行计数，忽略NULL

14.3 max( )函数

返回指定列的最大值， MAX（）要求指定列名

14.4 min( )函数

返回指定列的最小值， 要求指定列名

14.5 sum( ) 函数

sum用来返回指定列值的和

14.6 聚合不同值

添加distinct 关键字。

14.7 组合聚集函数

mysql> select count(*) As num_item,               min(prod_price) AS price_min,              max(prod_price) AS price_max,              avg(prod_price) AS avg from products;

15 分组数据

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

group by 指示mysql 进行分组排序

注意指示：

1.group by 子句可以包含任意数目的列

2.group by 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）

3.出聚集计算语句外，select 语句中的每个列都必须在group by子句中给出

4.如果分组中有NULL值， 那么NULL值作为一个分组给出。

5.group by 子句必须出现在where句子之后， order by子句之前。

mysql> select vend_id, count(*) AS num_prods from products group by vend_id with rollup;

with rollup 可以得到每个分组以及每个分组汇总级别的值。

           

15.2 过滤分组

where是过滤条件，但是它只能过滤行， 不能过滤分组。

mysql用了naving 来代替where实现过滤分组，而且where能实现的Having 都能实现

where在数据分组前进行过滤， 而Having 在数据分组后进行过滤。

15.3 分组和排序

group by 和 order by 的区别

order by 专门用来排序的， 任意列都可以使用

group by 分组行， 但是输出的可能不是分组的顺序。只可能使用选择列或表达式列，而且不许使用每个选择列表达式。如何和聚合函数一起使用列， 则必须使用

一般在给出group by 子句时， 应该给出order by 子句， 这是保证数据正确排序的唯一方法。 千万不要仅以来group by 排序数据

15.4 select 子句的顺序

select  要返回的列或表达式    必须使用

from    从中检索数据的表         仅从表中选择数据时使用

where  行级过滤                        不是必须使用

group by   分组说明                   尽在安组计算聚集是使用

having 组级过滤                         不是必须使用

order by   输出排序顺序            不是必须使用

limit   要检索的行数                   不是必须使用

16. 使用子查询

mysql是个关系型数据库， 表之间也是有关系的

如果多条查询之间有包含关系可以使用子查询

select cust_name, cust_contact from customerswhere cust_id in (select cust_id                               from orderitems                                where order_num IN ( select order_num                                                                        from orderitems                                                                                                    where prod_id = 'TNT2' ));

从上面可以看出使用非常灵活， 但是不能嵌套太多的子查询，

子查询一般于IN结合使用

后面会有更简便的方法。

17.联结表

sql最强大的功能之一是在数据检索查询的执行中联结

主键， 每个表列具有唯一的标示。

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

如果数据存储在多个表中， 怎样用单条select 语句检索出数据，答案是使用联结

mysql> select vend_name, prod_name, prod_price                from vendors, products                where vendors.vend_id = products.vend_id   //使用完全限定列名， 来确保使用的列是对应表中的列               order by vend_name, prod_name;

出现了两个表， 就是使用联结。

笛卡儿积：由没有联结条件的表关系返回的结果为笛卡儿积。

所以我们应该保证所有的联结都有where子句。

17.2 内部联结

mysql> select vend_name, prod_name, prod_price                            from vendors inner join products                            on vendors.vend_id = products.vend_id;

两个表的联结关系用inner join来指出， 传递给on 的实际条件和where相同

一般有限选用inner join 语法。

17.3 联结多个表

sql对一条select 语句可以联结的表的数目没有限制。

但是不要联结不必要的表，否则性能会下降的很厉害

18.创建高级联结

表也可以使用别名，用来缩短语句

18.1 上面所说的联结都是内部联结或者等值联结。

自然联结

外部联结

使用带聚集函数的联结

19.组合查询

19.1使用union

所要做的只是给出每条select 语句， 在各条语句之间放上关键字union

Union必须是两条或者两条以上的select 语句组成。

union中的每个查询必须包含相同的列， 表达式或聚集函数

union从查询结果集中自动去除了重复的行，如果需要改变，在union后面加上all。

union可以用where代替， 但是union all 可以完成一些where完成不了的工作。

19.2 对组合查询结果排序

用union组合查询的时， 只能使用一条order by 子句， 它必须出现在最后一条select 语句之后

使用union组合查询可以用于不同的表。

20.全文本搜索

20.1 数据库引擎是用于存储、处理和保护数据的核心服务

最长用的数据库引擎是MyISAM 和 InnoDB, 前者支持全文本搜索， 后者不支持

前面分别介绍了like和正则表达式来搜索文本， 但是这些要求sql匹配所有的行， 由于被搜索的行数不断增加， 这些搜索可能是非常耗时的。

而且like和正则表达式很难明确控制匹配什么和不匹配什么， 没有全文本搜索智能。

20.2 使用全文本搜索

1.启用全文本搜索支持

在创建表时， 在里面添加

FULLTEXT( )， 括号里面填指示对什么进行索引

一般在创建表时启用全文本搜索， create table 语句接受 fulltext语句， 它给出被索引列的一个逗号分隔的列表。

2.进行全文本搜索（会对结果进行排序）

使用函数match( ) 和 against( )执行全文本搜索

传递给match( ) 的值必须于fulltext( ) 定义中的相同， 如果指定多个列，则必须列出他们，搜索不区分大小写， 除非使用binary

select note_text from productnotes where match(note_next) Against('rabbit');

3.使用查询扩展

select note_text from productnotes where match(note_next) Against('anvils' with query expansion);

查找结果会显示一些和anvils相关的结果。

表中的行越多， 使用查询扩展返回的结果越好

4.布尔文本搜索（没有定义fulltext( ) 也可以使用）

布尔文本搜索可以提供更多的选择

against(  后面添加in boolen mode)；

要匹配的词

要排斥的词（如果某行包含这个词， 则不返回某行）

排列提示（指定什么等级高）

表达式分组

其他

mysql> select note_text               from productnotes                where match(note_text) against('heavy -rope*' in boolean mode);

上面有两个全文本搜索的布尔操作符* 和 - ，

- 是排除一个词， *是截断操作符（词尾的通配符）。

+   包含，词必须存在

-    排除， 词必须不能存在

>   包含，而且增加等级值

<   包含， 而且减少等级值

( )  把词组成子表达式

～ 取消一个词的排序值

*    词尾的通配符

" "  定义一个短语， 包含一个即可

注意：

如果一个词出现超过%50，它将会被忽略。

忽略词中的don't  改为dont

21. 数据插入

21.1 数据插入

insert 是用来插入（或添加）行到数据库表的， 插入可以选择以下几种方式

1.插入完整的行

2.插入行的一部分

3.插入多行

4.插入某些查询的结果

1.插入完整的行

INSERT INTO customers VALUES( NULL, 'pep ' ...);

插入时每个列必须提供一个值， 不出现的用NULL来代替

上面的语法很简单但是不安全

INSERT INTO customers( cust_name, cust_address, cust_city, cust_state,....)

                           VALUE ( 'pep', ' 100 main street' ....);

这条比较安全， 因为可以一一对应。

即使次序不同， 也会正常插入

INSERT LOW——PROORITY INTO 可以降低insert语句的优先级，因为INSERT语句可能会非常耗时， 可能会影响select语句的效率。

插入也可以同时插入多个行。圆括号括起来，然后用，号隔开即可

插入检索出来的数据，

INSERT SELECT 将一条select语句的结果插入到表中。

22.更新和删除数据UPDATE和DELETE

22.1 更新数据，使用UPDATE 语句

更新表中特定行

更新表中所有行

UPDATE语句由三部分构成， 要更新的表；列名和他们的新值；确定要更新行的过滤条件

例子 UPDATE customers SET cust_email = '[e-mail]'  （还可以添加）where cust_id = 10005;

STE命令用来将新值赋值给被更新的列。

！不要省略where语句， 否则会更新全部的内容， 这不是我们想要的。

UPDATE 后面添加关键字 IGNORE ，即使更新错误也不理睬， 从而继续更新。

删除某个列的值， 可把它设置为NULL

22.2 删除数据

从一个表中删除数据， 使用DELETE

从表中删除特定的行

从表中删除所有的行

！不要省略where语句， 因为稍微不注意就会错误的删除表中所有的行。

DELETE FROM customers WHERE cust_id = 10006;

更快的删除使用TRUNCATE， 删除原先的表并重新建立一个表

上面的DELETE和UPDATE 如果不添加where将更新或删除表中的所有内容。

经验：在对UPDATE和DELETE语句使用where子句之前， 应该先用select 进行测试， 保证它过滤的是正确的记录， 以防止编写错误的where 语句。

mysql 可没有撤销按钮！！