名奢网 › 名表› 最新资讯 › 查看内容

mysql必知必会

2023-3-27 13:42| 发布者: 挖安琥| 查看: 138| 评论: 0

放大缩小

简介：Mysql必知必会笔记查询Sql语句事例都是mysql8.0系统库检索数据Select 语句Select xx from xx基本要求:想选择什么，从哪里选Sql 虽说系统默认不区分大小写，但是最好按照大小写的惯例，且始终保持一致搜索去重Select ...

Mysql必知必会笔记

查询Sql语句事例都是mysql8.0系统库

检索数据

Select 语句

Select xx from xx

基本要求:想选择什么，从哪里选

Sql 虽说系统默认不区分大小写，但是最好按照大小写的惯例，且始终保持一致

搜索去重

Select DISTINCT xx

不能部分使用DISTINCT DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price，除非指定的两个列都不同，否则所有行都将被检索出来

限制匹配行数

Select xx from xx limit n;

Limit n指返回默认搜索结果的前面n行

Select xx from xx limit n,m;

指从第n行开始往后m行都打印，但是不打印第n行

当结果不足m行时，MySQL将只返回它能返回的那么多行

上面的sql等同于

Select xx from xx limit m offset n;

使用完全限定的表名（列名）

Select tablename.xx from databasename.tablename;

排序检索数据

排序数据

其实，检索出的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是，如果数据后来进行过更新或删除，则此顺序将会受到MySQL重用回收存储空间的影响。因此，如果不明确控制的话，不能（也不应该）依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。

为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。 ORDER BY子句取一个或多个列的名字，据此对输出进行排序。

Select xx from xx order by xx;

通常，ORDER BY子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

按多列排序

Select a,b,c from xx order a,b;

多列排序时，仅在多行具有相同的a行时才会根据b行排序，如果a的值唯一，则不会按照b排序。

指定排序方向

Select a,b,c from xx order by a desc;

Mysql默认的检索结果是升序排序(asc)，也可以使用DESC倒序排序。

当使用多列排序时，DESC只会对它前面的第一个生效。

Select a,b,c from xx order by a desc,b;

上例是先按照a进行降序排序，然后在a相同的行对b进行正序排序。

想在多列上降序排序，需要在每个排序的列名后面加上DESC

Select a,b,c from xx order by a desc,b desc,c desc;

区分大小写和排序顺序

在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL （和大多数数据库管理系统）的默认行为。但是，许多数据库管理员能够在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。（后续collate说明）

Order by 和 limit组合使用

Select xx form xx order by xx limit n;

在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY 之后。使用子句的次序不对将产生错误消息。

过滤数据

Where 语句

Select xx from xx where a=n;

这样就只会匹配a行=n的行

在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。

Where子句操作符

操作符说明

= 等于

<> 不等于

!= 不等于

< 小于

<= 小于等于

> 大于

>= 大于等于

BETWEEN 在指定的两个值之间

引号问题：何时使用引号 如果仔细观察上述WHERE子句中使用的条件， 会看到有的值括在单引号内（如前面使用的'fuses'），而有 的值未括起来。单引号用来限定字符串。如果将值与串类型的 列进行比较，则需要限定引号。用来与数值列进行比较的值不 用引号。

Between问题：在使用BETWEEN时，必须指定两个值 ——所需范围的低端值和高端值。这两个值必须用AND关键字 分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值

空值检查

Select xx from xx where xx is null;

Null 和不匹配是两个概念

Where子句and /or/in/not操作符

And 和 or

Select xx from xx where a=n and/or b=m;

And 操作符的优先级比or 高

Select xx from xx where a=n or b>m and a=u;

这句匹配是b>m且a=u的行或则a=n的所有行，为了避免优先级的问题，可以通过（）来解决。

Select xx from xx where (a=n or a=u) and b>m;

In

In操作符和or的功能相同，但是in有下面几个优势

在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。

在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。

IN操作符一般比OR操作符清单执行更快。

IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

not

NOT WHERE子句中用来否定后跟条件的关键字。

通配符过滤

Like 操作符

1操作符何时不是操作符？答案是在它作为谓词（predicate）时。从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的，

Like子句通配符

% 通配符

Select xx from xx where a like ‘b%’;

上例表示a行中以b开头，后面接任意字符。

重要的是要注意到，除了一个或多个字符外，%还能匹配0个字符。% 代表搜索模式中给定位置的0个、1个或多个字符。

注意尾空格 尾空格可能会干扰通配符匹配。例如，在保存词 anvil 时，如果它后面有一个或多个空格，则子句WHERE prod_name LIKE '%anvil'将不会匹配它们，因为在最后的l 后有多余的字符。解决这个问题的一个简单的办法是在搜索模 式最后附加一个%

注意NULL 虽然似乎%通配符可以匹配任何东西，但有一个例 外，即NULL。即使是WHERE prod_name LIKE '%'也不能匹配 用值NULL作为产品名的行。

下划线（_）通配符

与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少。

使用通配符注意事项

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

在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

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

Mysql正则表达式

基本字符匹配

select * from tables where table_name regexp 'lqm'；

这里不是绝对匹配，只要table_name中包含了lqm字符就行。

LIKE匹配整个列。如果被匹配的文本在列值 中出现，LIKE将不会找到它，相应的行也不被返回（除非使用 通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在 列值中出现，REGEXP将会找到它，相应的行将被返回。这是一 个非常重要的差别。

REGEXP能不能用来匹配整个列值（从而起与LIKE相同的作用）？答案是肯定的，使用^和$定位符（anchor）即可。

select * from tables where table_name regexp '.qm'；

这里的点（.）代表匹配任意一个字符。

regexp区分大小写

MySQL中的正则表达式匹配（自版本 3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

Or匹配

select * from tables where table_name regexp 'lqm|8'

用|来表示或

两个以上的OR条件 可以给出两个以上的OR条件。例如， '1000 | 2000 | 3000'将匹配1000或2000或3000。

匹配几个字符之一

Select xx from xx where a REGEXP ‘[123] Ton’;

这里，使用了正则表达式[123] Ton。[123]定义一组字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回（没有3 ton）。

Select xx from xx where a REGEXP ‘1|2|3 Ton’;

这样用or匹配的是1或2或3 ton，想同[]匹配结果一样，需要将1|2|3用（）括起来。

Select xx from xx where a REGEXP ‘[^123] Ton’;

中括号里面加^是取反。

匹配范围

Select xx from xx where a REGEXP ‘[1-5] Ton’;

上例等同于[12345] Ton. 范围不限于完整的集合，[1-3]和[6-9]也是合法的范围。此外，范围不一定只是数值的，[a-z]匹配任意字母字符。

匹配特殊字符

为了匹配特殊字符，必须用\\为前导。\\-表示查找-，\\.表示查找.。这种处理就是所谓的转义（escaping），正则表达式内具有特殊意义的所有字符都必须以这种方式转义。这包括.、|、[]以及迄今为止使用过的其他特殊字符。

元字符说明

\\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]）

[:print:] 任意可打印字符

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

[:space:] 包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]）

[:upper:] 任意大写字母（同[A-Z]）

[:xdigit:] 任意十六进制数字（同[a-fA-F0-9]）

匹配多个字符

元字符说明

* 0个或多个匹配

+ 1个或多个匹配（等于{1,}）

? 0个或1个匹配（等于{0,1}）

{n} 指定数目的匹配

{n,} 不少于指定数目的匹配

{n,m} 匹配数目的范围（m不超过255）

Select xx from xx where a REGEXP ‘\$[0-9] sticks?\$’;

正则表达式\$[0-9] sticks?\$需要解说一下。\$匹配)， [0-9]匹配任意数字（这个例子中为1和5），sticks?匹配stick 和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现），\$匹配)。没有?，匹配stick和sticks会非常困难。

Select xx from xx where REGEXP ‘[[:digit:]]{4}’;

[:digit:]匹配任意数字，因而它为数字的一个集合。{4}确切地要求它前面的字符（任意数字）出现4次，所以 [[:digit:]]{4}匹配连在一起的任意4位数字。

定位符

元字符说明

^ 文本的开始

$ 文本的结尾

[[:<:]] 词的开始

[[:>:]] 词的结尾

Select xx from xx where a REGEXP ‘^[0-9\\.]’;

^匹配串的开始。因此，^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们。

^的双重用途 ^有两种用法。在集合中（用[和]定义），用它 来否定该集合，否则，用来指串的开始处。

使REGEXP起类似LIKE的作用 本章前面说过，LIKE和REGEXP 的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位 符，通过用^开始每个表达式，用$结束每个表达式，可以使 REGEXP的作用与LIKE一样。

简单的正则表达式测试 可以在不使用数据库表的情况下用 SELECT来测试正则表达式。REGEXP检查总是返回0（没有匹配） 或1（匹配）。可以用带文字串的REGEXP来测试表达式，并试 验它们。相应的语法如下： 这个例子显然将返回0（因为文本hello中没有数字）。

计算字段

如果想在一个字段中既显示公司名，又显示公司的地址，但这两个信息一般包含在不同的表列中。

城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序却需要把它们作为一个恰当格式的字段检索出来。

列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。

物品订单表存储物品的价格和数量，但不需要存储每个物品的总价格（用价格乘以数量即可）。为打印发票，需要物品的总价格。

需要根据表数据进行总数、平均数计算或其他计算。

拼接字段

select concat(table_name,'(',engine,')') from tables where engine IS NOT NULL;

| concat(table_name,'(',engine,')') |

+--------------------------------------------------------------------------+

| columns_priv(InnoDB) |

| component(InnoDB) |

| db(InnoDB) |

| default_roles(InnoDB) |

Concat()拼接串，即把多个串连接起来形成一个较长的串。 Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

select concat(RTrim(table_name),'(',RTrim(engine),')') from tables where engine IS NOT NULL;

Trim函数 MySQL除了支持RTrim()（正如刚才所见，它去掉 串右边的空格），还支持LTrim()（去掉串左边的空格）以及 Trim()（去掉串左右两边的空格）。

使用别名

SQL支持列别名。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。

执行算术计算

操作符说明

+ 加

- 减

* 乘

/ 除

SELECT提供了测试和试验函数与计算的一个 很好的办法。虽然SELECT通常用来从表中检索数据，但可以 省略FROM子句以便简单地访问和处理表达式。例如，SELECT 3*2;将返回6，SELECT Trim('abc');将返回abc，而SELECT Now()利用Now()函数返回当前日期和时间。通过这些例子， 可以明白如何根据需要使用SELECT进行试验。

使用数据处理函数

文本处理函数

Upper函数

select table_name,upper(table_name) as table_nameupper from tables;

其他函数说明

函数说明

Left() 返回串左边的字符

Length() 返回串的长度

Locate() 找出串的一个子串

Lower() 将串转换为小写

LTrim() 去掉串左边的空格

Right() 返回串右边的字符

RTrim() 去掉串右边的空格

Soundex() 返回串的SOUNDEX值

SubString() 返回子串的字符

Upper() 将串转换为大写

Right/left用法

select table_name,Right(table_name,5) as table_nameupper from tables limit 5;

上例是打印table_name列最右边的5个字符

Soundex说明

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然 SOUNDEX不是SQL概念，但MySQL（就像多数DBMS一样）都提供对 SOUNDEX的支持。

Select xx from xx where soundex(cust_contact)=soundex(‘y lie’);

因为Y.Lee和 Y.Lie发音相似，所以它们的SOUNDEX值匹配.

日期和时间处理函数

函数说明

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

AddTime() 增加一个时间（时、分等）

CurDate() 返回当前日期

CurTime() 返回当前时间

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

DateDiff() 计算两个日期之差

Date_Add() 高度灵活的日期运算函数

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

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

DayOfWeek() 对于一个日期，返回对应的星期几

Hour() 返回一个时间的小时部分

Minute() 返回一个时间的分钟部分

Month() 返回一个日期的月份部分

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

Second() 返回一个时间的秒部分

Time() 返回一个日期时间的时间部分

Year() 返回一个日期的年份部分

数值处理函数

函数说明

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

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

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

Mod() 返回除操作的余数

Pi() 返回圆周率

Rand() 返回一个随机数

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

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

Tan() 返回一个角度的正切

汇总数据

聚集函数

函数说明

AVG() 返回某列的平均值

COUNT() 返回某列的行数

MAX() 返回某列的最大值

MIN() 返回某列的最小值

SUM() 返回某列值之和

分组数据

创建分组

select engine,count(*) from tables group by engine;

使用group by 进行分组统计

在具体使用GROUP BY子句前，需要知道一些重要的规定。

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。

GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。

除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

使用ROLLUP 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值

select engine,count(*) from tables group by engine with rollup;

过滤分组

select engine,count(*) from tables group by engine having count(*) >33;

在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。那么，不使用WHERE使用什么呢？MySQL为此目的提供了另外的子句，那就是HAVING子句。HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是 WHERE过滤行，而HAVING过滤分组。

select engine,count(*) from tables where table_name not like 'lqm_8' group by engine having count(*) >33;

where 先过滤行，在having过滤组。

Select 子句顺序

子句说明是否必须使用

SELECT 要返回的列或表达式是

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

WHERE 行级过滤否

GROUP BY 分组说明仅在按组计算聚集时使用

HAVING 组级过滤否

ORDER BY 输出排序顺序否

LIMIT 要检索的行数否

使用子查询

利用子查询进行过滤

Select xx from xx where a in (

Select xx from xx where b=xx);

在WHERE子句中使用子查询能够编写出功能很强并且很灵活的 SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

作为计算字段使用子查询

Select cust_name,cust_state,(select count(*) from orders where orders.cust_id = customers.cust_id) as order from customers order by cust_name;

select count(*) from orders where orders.cust_id = customers.cust_id 查询计算的结果作为父查询语句的一个列返回

联结表

内部联结（inner join）或等值联结（where 加判断条件）

创建高级联结

使用表别名

select concat(RTrim(file_name),'(',RTrim(tablespace_name),')') as tn from files;

+--------------------------------------+

| tn |

+--------------------------------------+

| ./ibdata1(innodb_system) |

| ./ibtmp1(innodb_temporary) |

| ./undo_001(innodb_undo_001) |

| ./undo_002(innodb_undo_002) |

| ./mysql.ibd(mysql) |

| ./sys/sys_config.ibd(sys/sys_config) |

| ./test/lqm_8.ibd(test/lqm_8) |

+--------------------------------------+

7 rows in set (0.00 sec)

自联结

Select prod_id,prod_name from products where vend_id=(select vend_id from products where prod_id=’’’xx’);

这句子查询改为自联结查询

Select p1.prod_id,p1.prod_name from products as p1,products as p2 where p1.vend_id=p2.vend_id and p2.prod_id=’xx’;

自联结通常作为外部语句用来替代 从相同表中检索数据时使用的子查询语句。虽然最终的结果是 相同的，但有时候处理联结远比处理子查询快得多。应该试一 下两种方法，以确定哪一种的性能更好。

组合查询

组合查询（复合查询）

Union 来连接多个查询语句，会和where一样，自动去除重复行，要想保留重复行，需要使用union all

Union使用规则

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个 UNION关键字）。

UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。

列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

全文本搜索

全文搜索相关概念

并非所有引擎都支持全文本搜索正如第21章所述，MySQL 支持几种基本的数据库引擎。并非所有的引擎都支持本书所描述的全文本搜索。两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。这就是为什么虽然本书中创建的多数样例表使用 InnoDB ，而有一个样例表（productnotes表）却使用MyISAM的原因。如果你的应用中需要全文本搜索功能，应该记住这一点。

5.7以后版本innodb支持全文搜索（全文索引）

前文有使用like和regexp进行正则匹配，有以下几个限制

性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。

明确控制——使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。

智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。

使用全文本搜索

创建全文索引

create table full_text(

-> note_id int not null auto_increment,

-> prod_id char(10) not null,

-> note_date datetime not null,

-> note_text text null,

-> primary key(note_id),

-> fulltext(note_text)

-> )engine = innodb;

表数据

mysql> select * from full_text;

+---------+---------+---------------------+----------------------+

+---------+---------+---------------------+----------------------+

| 1 | news | 2020-03-25 16:25:54 | python change world! |

| 2 | news | 2020-03-25 16:26:41 | Python change world! |

| 3 | news | 2020-03-25 16:26:50 | java change world! |

| 4 | news | 2020-03-25 16:26:55 | php change world! |

+---------+---------+---------------------+----------------------+

搜索默认是不区分大小的

select * from full_text where match(note_text) against("python");

+---------+---------+---------------------+----------------------+

+---------+---------+---------------------+----------------------+

| 1 | news | 2020-03-25 16:25:54 | python change world! |

| 2 | news | 2020-03-25 16:26:41 | Python change world! |

+---------+---------+---------------------+----------------------+

想区分可以加binary

测试结果显示，mysql8.0 全文检索against不支持binary，like支持

搜索结果排行问题

全文检索中如果检索的关键字个数一样，则关键字出现的越靠前，则显示的结果越靠前。

使用查询扩展

select note_text from full_text where match(note_text) against("python" with query expansion);

+----------------------+

| note_text |

+----------------------+

| python change world! |

| Python change world! |

| PYTHON change world! |

| java change world! |

| php change world! |

+----------------------+

使用with query expansion可以将没有匹配关键词的行也匹配出来，并按照一定顺序排序

布尔文本搜索

select note_text from full_text where match(note_text) against("python" in boolean mode);

+---------------------------+

| note_text |

+---------------------------+

| python change world! |

| Python change world! |

| PYTHON change world! |

| java,python change world! |

+---------------------------+

4 rows in set (0.00 sec)

mysql> select note_text from full_text where match(note_text) against("python -java*" in boolean mode);

+----------------------+

| note_text |

+----------------------+

| python change world! |

| Python change world! |

| PYTHON change world! |

+----------------------+

上面的-Java*代表不匹配任何以Java开头的行。

全文本布尔操作符

布尔操作符说明

+ 包含，词必须存在

- 排除，词必须不出现

> 包含，而且增加等级值

< 包含，且减少等级值

() 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）

~ 取消一个词的排序值

* 词尾的通配符

"" 定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

插入数据

数据插入

顾名思义，INSERT是用来插入（或添加）行到数据库表的。插入可以用几种方式使用：

插入完整的行；

插入行的一部分；

插入多行；

插入某些查询的结果。

insert into full_text(note_id,prod_id,note_date,note_text) values(null,'news',now(),'i love python!');

插入语句最好提供列名，方便后续表结构有变动，sql语句还能够运行。

省略列如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。

该列定义为允许NULL值（无值或空值）。

在表定义中给出默认值。这表示如果不给出值，将使用默认值

可以使用low_priority关键字来降低select的执行优先级，而且同样使用于update,delete

insert low_priority into full_text(note_id,prod_id,note_date,note_text) values(null,'news',now(),'i love java!');

插入多行数据

一条语句插入多行

insert low_priority into full_text(note_id,prod_id,note_date,note_text) values(null,'news',now(),'i love java!')

-> ,

-> (null,'news',now(),'i love php!');

Query OK, 2 rows affected (0.01 sec)

这样一条语句插入多行比多条插入语句的性能要好。

插入检索出的数据

insert low_priority into full_text(prod_id,note_date,note_text) select prod_id,note_date,note_text from full_text;

Query OK, 11 rows affected (0.01 sec)

更新和删除数据

更新数据

update full_text set prod_id='words',note_date='2020-01-01' where note_id=1;

可同时更新多列数据，用逗号（,）隔开

子查询更新

update full_text set prod_id='words' where note_id in (select * from (select note_id from full_text where prod_id not like 'words') tmp);

update full_text set note_text=(select * from (select note_text from full_text where note_id=22)tmp) where note_id =1;

格式：主句(select * from (从句 temp)

IGNORE关键字 如果用UPDATE语句更新多行，并且在更新这些 行中的一行或多行时出一个现错误，则整个UPDATE操作被取消 （错误发生前更新的所有行被恢复到它们原来的值）。为即使是发 生错误，也继续进行更新，可使用IGNORE关键字，如下所示： UPDATE IGNORE customers…

删除指定列的值

update full_text set note_text=null where note_id=22;

删除数据

delete from full_text where note_id=22;

delete是删除行数据，而update set =null是删除列数据

更快的删除 如果想从表中删除所有行，不要使用DELETE。 可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更 快（TRUNCATE实际是删除原来的表并重新创建一个表，而不 是逐行删除表中的数据）。

下面是许多SQL程序员使用UPDATE或DELETE时所遵循的习惯。

除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE 子句的UPDATE或DELETE语句。

保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。

在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。

使用强制实施引用完整性的数据库（关于这个内容，请参阅第15 章），这样MySQL将不允许删除具有与其他表相关联的数据的行。

创建和操纵表

创建表

mysql> create table full_text(

-> note_id int not null auto_increment,

-> prod_id char(10) not null,

-> note_date datetime not null,

-> note_text text null,

-> primary key(note_id),

-> fulltext(note_text)

-> )engine = innodb;

如果主键设置为多列，则多列的组合必须是唯一的。

auto_increment

auto_increment默认是从1自动增长的，如果某次插入自己定义了数字，后续的增长，将以新定义的数字作为开始自动增长。

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

| 34 |

使用last_insert_id()可以获取最新的增长值

default

mysql可以用default来指定默认值

不允许函数 与大多数DBMS不一样，MySQL不允许使用函 数作为默认值，它只支持常量。

使用默认值而不是NULL值许多数据库开发人员使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。

更新表

添加列

alter table full_text add author char(250) not null default 'qiuming.li';

删除列

alter table full_text drop column author;

删除表

Drop table xx;

重命名表

rename table author to author_list;

添加外键

alter table full_text add constraint fk_auhtor foreign key(author_id) references author(author_id);

视图

什么是视图

我们已经看到了视图应用的一个例子。下面是视图的一些常见应用。

重用SQL语句。

简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。

使用表的组成部分而不是整个表。

保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的规则和限制

与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。对于可以创建的视图数目没有限制。

为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。

视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。 ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。

视图不能索引，也不能有关联的触发器或默认值。

视图可以和表一起使用。例如，编写一条联结表和视图的SELECT 语句。

使用视图

在理解什么是视图（以及管理它们的规则及约束）后，我们来看一下视图的创建。

视图用CREATE VIEW语句来创建。

使用SHOW CREATE VIEW viewname；来查看创建视图的语句。

用DROP删除视图，其语法为DROP VIEW viewname;。

更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

create view book_msg as select a.note_date,a.note_text,b.author_name from full_text as a,author as b where a.author_id=b.author_id;

查询视图结果

select * from book_msg where author_name='dwj';;

+---------------------+-------------+-------------+

| note_date | note_text | author_name |

+---------------------+-------------+-------------+

| 2020-03-26 15:31:27 | i love php! | dwj |

使用函数或则计算查询建立的视图

create view book_face as select concat(RTrim(a.note_text),'(',RTrim(b.author_name),')') as book_f from full_text as a,author as b where a.author_id = b.author_id;

WHERE子句与WHERE子句 如果从视图检索数据时使用了一条 WHERE子句，则两组子句（一组在视图中，另一组是传递给视 图的）将自动组合。

更新视图

如果视图定义中有以下操作，则不能进行视图的更新：

分组（使用GROUP BY和HAVING）；

联结；

子查询；

并；

聚集函数（Min()、Count()、Sum()等）；

DISTINCT；

导出（计算）列。

存储过程

创建存储过程

delimiter ;

mysql> delimiter //

mysql> create procedure dwj() BEGIN select * from full_text; END//

Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

调用存储过程

call dwj();

删除存储过程

mysql> drop procedure dwj;

Query OK, 0 rows affected (0.01 sec)

带参数的存储过程及调用

drop procedure if exists test.agelist;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter //

mysql> create procedure agelist( out al decimal(8,2), out am decimal(8,2), out aa decimal(8,2)) begin select min(age) into al from author; select max(age) into am from author; select avg(age) into aa from authoor; end//

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> call agelist(@minage,@maxage,@avgage);

Query OK, 1 row affected (0.00 sec)

mysql> select @minage,@maxage,@avgage;

+---------+---------+---------+

| @minage | @maxage | @avgage |

+---------+---------+---------+

| 16.00 | 18.00 | 17.00 |

+---------+---------+---------+

1 row in set (0.01 sec)

高阶存储过程

mysql> delimiter //

mysql> create procedure magicage(

in name char(25),

in changeage int,

out oage int)

comment 'girl is younger,boy is older'

begin

declare mag_age int;

select age from author where author_name=name into mag_age;

if name='dwj' then

select mag_age-3 into mag_age;

elseif name='lqm' then

select mag_age-2 into mag_age;

else select mag_age+2 into mag_age;

end if;

select mag_age into oage;

end//

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> call magicage('lqm',1,@outage);

Query OK, 1 row affected (0.00 sec)

mysql> select @outage;

+---------+

| @outage |

+---------+

| 16 |

+---------+

1 row in set (0.00 sec)

mysql> call magicage('dwj',1,@outage);

Query OK, 1 row affected (0.00 sec)

mysql> select @outage;

+---------+

| @outage |

+---------+

| 14 |

+---------+

1 row in set (0.00 sec)

mysql> show procedure status like 'magicage';

+------+----------+-----------+----------------+---------------------+---------------------+---------------+------------------------------+----------------------+----------------------+--------------------+

1 row in set (0.01 sec)

mysql>