启动数据库服务

以管理员身份运行Windows PowerShell

输入开启服务指令

1
net start MySQL5.7(数据库服务器名称)

关闭服务指令

1
net stop MySQL5.7(数据库服务器名称)

使用数据库服务

登录数据库
1
mysql -uroot -p

然后键入密码

创建一个数据库
1
DATABASE CREATE t1;

如数据库已存在，会直接报ERROR

创建一个数据库（带检查）
1
DATABASE CREATE IF NOT EXISTS t1;

如数据库已存在，会报WARNING

查看WARRINGS
1
SHOW WARNINGS;
显示可用的数据库列表
1
SHOW TABLES
选择数据库
1
USE XXX（数据库名）
返回当前选择的数据库内可用表的列表
1
SHOW COLUMNS FROM XXX（表名）;

一种MySQL可以使用的快捷方式

1
DESCRIBE XXX（表名）;
显示广泛的服务器状态信息
1
SHOW STATUS;
分别用来显示创建特定数据库或表的MySQL语句
1
SHOW CREATE DATABASE; & SHOW CREATE TABLE;
用来显示服务器错误或警告信息
1
SHOW ERRORS; & SHOW WARNINGS;
显示允许的SHOW语句
1
HELP SHOW;

检索数据

检索单个列
1
SELECT prod_name FROM products;
检索多个列
1
SELECT prod_id, prod_name, prod_price FROM products;
检索所有列（使用通配符 *）
1
SELECT * FROM products；
检索不同的行（使用关键字DISTINCT）只返回不同（唯一）vend_id行
1
SELECT DISTINCT vend_id FROM products;

==不能部分使用DISTINCT，DISTINCT关键字应用于所有列而不仅是前置他的列==

限制结果（使用LIMIT子句）
1
SELECT prod_name FROM products LIMIT 5;

（LIMIT 5 指示MySQL返回不多于5行）

为得出下一个5行，可以指定要检索的开始和行数

1
SELECT prod_name FROM PRODUCTS LIMIT 5,5;

（LIMIT 5,5 指示MySQL返回从行5开始的5行，第一个数为开始位置，第二个数为要检索的行数）

==检索出来的第一行是行0而不是行1，因此LIMIT 1,1 将检索出第二行而不是第一行==

限制结果（另一种替代语法）
1
SELECT prod_name FROM PRODUCTS LIMIT 4 OFFSET 3;

（从行3开始取4行，正如LIMIT 3,4）

使用完全限定的表名（同时使用表名和列名）
1
SELECT products.prod_name FROM products;

（功能与检索单个列相同，但是制定了一个完全限定的列名）

1
SELECT products.prod_name FROM course.products;

（表名也可以是完全限定的）

排序检索数据

什么是子句？

SQL语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成，如（SELECT FROM）中的FROM子句

ORDER BY子句

为了明确地排序用SELECT语句检索出的数据，可以使用ORDER BY子句

ORDER BY子句取一个或多个列的名字，据此对输出进行排序

1
SELECT prod_name FROM products ORDER BY prod_name;

（指示MYSQL对prod_name列以字母顺序排序数据）

按多个列排序

为了按多个列排序，只要指定列名，列名之间用逗号分开即可

下面的代码检索3个列，并按其中两个列对结果进行排序——首先按价格，然后再按名称排序

1
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;

注：在按多个列排序是，排序完全按照所规定的顺序进行，在上述例子中的输出，仅在多个行具有相同的prod_price值时在对产品按prod_name进行排序，如果prod_price列中所有的值都是惟一的，则不会按prod_name排序

指定排序方向

数据排序不仅限于升序排序（从A到Z），还可以按降序（从Z到A）排序

为了进行降序排序，必须指定DESC关键字

1
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC;

关于用多个列排序

1
2
SELECT prod_id,prod_price,prod_name FROM products
ORDER BY prod_price DESC,prod_name;

DESC关键字只应用到直接位于其前面的列名，在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此prod_price列以降序排序，而prod_name列（在每个价格内）仍能按照标准的升序排序

==如果想在多个列上进行降序排序，必须对每个列指定DESC关键字==

与DESC相反的关键字是ASC（ASCENDING），即默认的升序排序

（大小写和排序顺序取决于数据库的设置）

综合案例

使用ORDER BY与LIMIT的结合，找出一个列中的最值

1
2
3
4
SELECT prod_price 
FROM products
ORDER BY prod_price DESC
LIMIT 1;

过滤数据

使用Where子句

Where子句操作符
操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定的两个值之间
检查单个值
1
SELECT prod_name,prod_price FROM products WHERE prod_name = 'fuses';
1
SELECT prod_name,prod_price FROM products WHERE prod_price < 10;
1
SELECT prod_name,prod_price FROM products WHERE prod_price <= 10;
不匹配检查
1
SELECT vend_id,prod_name FROM products WHERE vend_id <> 1003;
1
SELECT vend_id,prod_name FROM products WHERE vend_id != 1003;
范围值检查
1
2
3
SELECT prod_name,prod_price 
FROM products 
WHERE prod_price BETWEEN 5 AND 10;

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

空值检查

==NULL 无值（no value），它与字段包含0、空字符串或仅仅包含空格不同==

select语句就有一个特殊的WHERE子句，可用来检查具有NULL值得列

这个WHERE子句就是IS NULL子句

1
2
3
SELECT prod_namme
FROM products
WHERE prod_price IS NULL;

这条语句返回没有价格（空prod_price字段，不是价格为0）的所有产品

NULL与不匹配

在通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值德的行。但是，不行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回他们。

因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行

数据过滤

AND操作符

为了通过不止一个列进行过滤，可使用AND操作符给WHERE子句添加条件

当想要添加多个过滤条件时，每添加一条就要使用一个AND

1
2
3
SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
OR操作符

与AND操作符不同，OR操作符指示MySQL检索匹配任一指定条件的行

1
2
3
SELECT prod_name,prod_price
FROM products
WHERE vend_id = 1002 or vend_id = 1003;
计算次序

产生错误的写法：

1
2
3
SELECT prod_name,prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

计算机会认为是 id=1002 OR (vend_id = 1003 AND prod_price >= 10);

其实想表达的是：

1
2
3
SELECT prod_name,prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

WHERE可包含任意数目的AND和OR操作符，允许两者结合以进行复杂和高级的过滤

SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符，如不添加括号，容易导致错误的组合，产生不理想的结果。

==因此，任何时候都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的的东西也是如此，使用圆括号可以消除歧义，只有好处没有坏处==

IN操作符

圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由括号分隔的清单，全都括在圆括号中

1
2
3
4
SELECT prod_name,prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

既然IN = OR,为什么还要使用IN？

原因如下：

  • 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观
  • 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）
  • IN操作符一般比OR操作符清单执行更快
  • IN的最大优点是可以包含其他SELECT语句，是的能够更动态地建立WHERE子句
NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件

1
2
3
4
SELECT prod_name,prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;

(MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别)

用通配符进行过滤

利用通配符可创建比较特定的数据搜索模式

通配符：用来匹配值得一部分的特殊字符

搜索模式：由字面值、通配符或两者组合构成的搜索条件

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

LIKE操作符
百分号（%）操作符

最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数

例如，为了找出所有以词jet起头的产品，可使用一下SELECT语句

1
2
3
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'jet%';

此例子使用了搜索模式’jet%’。%告诉MySQL接受jet之后的任意字符，不管它有多少字符

（根据MySQL的配置方式，搜索可以是区分大小写的。如果区分大小写，’jet%’将与’JetPack 1000’不匹配

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。

1
2
3
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '%anvil';

搜索模式’%anvil%’表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符

通配符也可以出现在搜索模式的中间，虽然这样做不太有用

1
2
3
SELECT prod_name
FROM products
WHERE prod_name LIKE 's%e';

==值得注意的是，除了一个或多个字符外，%还能匹配0个字符==

==%代表搜索模式中给定位置的0个、1个或者多个字符==

下划线（_）通配符

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

1
2
3
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE'_ ton anvil';

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

MySQL的通配符很有用，但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长

使用通配符的技巧
  • 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符
  • 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的
  • 仔细注意通配符的位置。如果放错位置，可能不会返回想要的数据

用正则表达式进行搜索

创建计算字段

使用数据处理函数

汇总数据

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。这种了类型的检索例子有以下几种

  • 确定表中行数（或者满足某个条件或包含某个特定值的行数）
  • 获得表中行组的和
  • 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值
聚集函数

运行在行组上，计算和返回单个值的函数

函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
AVG()函数
1
2
SELECT AVG(prod_price) AS avg_price
FROM products;

此SELECT语句返回值avg_price，它包含products表中所有产品的平均价格

AVG()也可以用来确定特定列或行的平均值

1
2
3
SELECT AVG(prod_price) as avg_price
FROM products
WHERE vend_id = 1003;

WHERE子句过滤出vend_id为1003的产品，因此avg_price中返回的值只是该供应商的商品的平均值

COUNT()函数

COUNT()函数进行计数，可利用COUNT()确定表中行的数目或符合特定条件的行的数目

COUNT()函数有两种使用方式

  • 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值
  • 使用COUNT(column)对特定列中具有值得行进行计数，忽略NULL值
1
2
SELECT COUNT(*) AS num_cust
FROM customers;

利用COUNT(*)对所有行计数，不关行中各列有什么值。计数值在num_cust中返回

1
2
SELECT COUNT(cust_email) AS num_cust
FROM customers;

使用COUNT(cust_email)对cust_email列中有值的行进行计数

MAX()函数

MAX()返回指定列中的最大值，MAX()要求指定列名

1
2
SELECT MAX(prod_price) AS max_price
FROM products;

使用MAX()返回products表中最贵的物品的价格

MIN()函数

MIN()返回指定列的最小值，和MAX()一样，MIN()要求指定列名

1
2
SELECT MIN(prod_price) AS min_price
FROM products;

使用MIN()返回products表中最便宜物品的价格

SUM()函数

SUM()用来返回指定列值得和（总计）

1
2
3
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;

使用SUM(quantity)返回订单中所有物品数量之和,WHERE子句保证只统计某个物品订单中的物品在SUM()也可以用来合计计算值，在下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额

1
2
3
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;

使用SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句同样保证只统计某个物品订单中的值

聚集不同值

以上五个聚集函数都可以如下使用

  • 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）
  • 只包含不同的值，指定DISTINCT参数

下面例子使用AVG()函数返回特定供应商提供的商品的平均价格，与上面的欲哭不同，它使用了DISTINCT参数，因此平均值只考虑各个不同的价格

1
2
3
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

==注意：如果指定列名，则DISTINCT只能用于COUNT（列名）。==

​ ==DISTINCT不能用于COUNT(*)==

​ ==因此不允许使用COUNT(DISTANCT*）==

组合聚集函数

实际中，SELECT语句可根据需要包含多个聚集函数

1
2
3
4
5
SELECT COUNT(*) AS items,
	   MIN(prod_price) AS price_min,
	   MAX(prod_price) AS price_max,
	   AVG(prod_price) AS price_avg
FROM products;

分组数据

使用子查询

联结表

联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分

关系表

关系表的设计就是要保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值（即关系设计中的关系（relational））互相关联

外键（foreign key）：外键为某个表中的一列，它包含另一个表的主键值，定义两个表之间的关系

可伸缩性：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）

为什么要是用联结？

正如所述，分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。但这些好处是有代价的

如果数据存储在多个表中，怎样用单挑SELECT语句检索出数据？

答案是是用联结！简单地说，联结是一种机制，用来在一条SELECT语句中关键表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行

创建联结
1
2
3
4
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

可以看到，所指定的两个列（prod_name，prod_id）在一个表中，而另一个列（vend_id）在另一个表中。FROM子句，与以前的SELECT语句不一样，这条语句的FROM子句列出了两个表，分别是vendors和products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配VENDORS表种的vend_id和products表种的vend_id

==在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。如果引用一个没有用表名限制的具有二义性的列名，MySQL将返回错误==

WHERE子句的重要性

在联结两个表时，实际上做得是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行匹配，而不管它们逻辑上是否可以配在一起

==笛卡尔积==（cartesian product）：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

内部联结

等值联结，也称为内部联结，基于两个表之间的相等测试。对于这种联结可以使用稍微不同的语法来明确指定联结的类型

1
2
3
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同

联结多个表

SQL对一条SELECTY语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系

1
2
3
4
5
SELECT prod_name,vend_name,prod_price,quantity
FROM orderitems,products,vendors
WHERE products.vend_id = vendors.vend_id
  AND orderitems.prod_id = products.prod_id
  AND order_num = 20005;

此例子显示编号为20005的订单中的物品。订单物品存储在orderitems表中，每个产品按其产品ID存储，它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商，供应商ID存储在每个产品的记录中。这里的FROM子句列出了3个表，而WHERE子句定义了这两个联结条件，而第三个联结条件用来过滤出订单20005中的物品

==（MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害）==

创建高级联结

组合查询

全文本搜索

插入数据

更新和删除数据

创建和操纵表

创建表

NULL值

NULL值就是没有值或者缺值，允许NULL值得列也允许在插入行时不给出该列的值，不允许NULL值得列不接受该列没有值的行

1
2
3
4
5
6
CREATE TABLE vendors
(
	vend_id int NOT NULL AUTO_INCREMENT,
    vend_name char(50) NOT NULL,
    vend_address char(50) NULL
) ENGINE=InnoDB;

NULL为默认设置，如果不指示NOT NULL，则认为指定的是NULL

主键

表中的每个行必须具有为一个主键值。如果主键使用单个列，则它的值必须唯一，如果主键使用多个列，则这些列的组合值必须唯一。

主键定义语句：

1
PRIMARY KEY(XXX);

多主键示例

1
2
3
4
5
6
7
8
9
CREATE TABLE orderitmes
(
	order_num int NUL NULL,
    order_item int NUT NULL,
    prod_id char(10) NUT NULL,
    quantity int NUT NULL,
    item_price decimal(8,2) NUT NULL,
    PRIMARY KEY (order_num,order_item)
) ENGINE=InnoDB;

==主键只能使用不允许NULL值得列，允许NULL值的列不能作为唯一标识==

AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量，每次执行一个INSERT操作时,MySQL自动对该列增量，给该列赋予下一个可用的值，这样可以给每个行分配一个唯一的cust_id，从而可以用作主键值

1
cust_id int NOT NULL AUTO_INCREMENT

==每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）==

指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值

默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定

1
2
3
4
5
6
7
8
9
CREATE TABLE orderitems
(
	order_num int NOT NULL,
    order_item int NOT NULL,
    prod_id char(10) NOT NULL,
    quantity int NOT NULL DEFAULT 1,
    item_price decimal(8,2) NOT NULL,
    PRIMARY KEY(order_num,order_item)
) ENGINE=InnoDB;

在列的描述添加文本DEFAULT 1指示MySQL，在未给出数量的情况下使用数量1

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

==使用默认值而不是NULL值！==

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

引擎类型

不同于其他DBMS，MySQL具有多种引擎，它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令

为什么要研发多种引擎呢？

因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性

==如果省略ENGINE=语句，则使用默认引擎（很可能是MyISAM），多数SQL语句都会默认使用它，但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要的原因==

几个需要知道的引擎：

  • InnoDB：一个可靠的事务处理引擎，它不支持全文本搜索
  • MEMORY：在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）
  • MyISAM：一个性能极高的引擎，它支持全文本搜索，但不支持事务处理

（引擎可以混用，但有一个大缺陷，就是==外键（用于强制实施引用完整性）不能跨引擎==，即使用一个引擎的表不能引用具有使用不同引擎的表的外键）

更新表

为更新表定义，可以使用ALTER TABLE语句，但是，理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动

为了使用ALTER TABLE更改表结构，必须给出下面的信息

  • 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）
  • 所做更改的列表

例：给表增加一个列

1
2
ALTER TABLE vendors
ADD vend_phone CHAR(20);

这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型

例：删除列

1
2
ALTER TABLE vendors
DROP COLUMN vend_phont;

不必使用多条ALTER TABLE语句，可以使用单挑ALTER TABLE语句，每个更改用逗号分割

复杂的表结构修改一般需要手动删除过程，它涉及以下步骤

  • 用新的列布局创建一个新表
  • 使用 INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段
  • 检验包含所需数据的新表
  • 重命名旧表（如果确定，可以删除它）
  • 用旧表原来的名字重命名新表
  • 根据需要，重新创建触发器，存储过程，索引和外键
删除表
1
DROP TABLE customers2;

这条语句删除customers2表（假设它存在）

==删除表没有确认，也不能撤销，执行这条语句将永远删除该表==

重命名表

使用RENAME TABLE语句可以重命名一个表

1
RENAME TABLE customers2 TO customers;

RENAME TABLE所做的仅是重命名一个表，可以使用下面的语句对多个表重命名

1
2
3
RENAME TABLE backup_customers TO customers,
			 backup_vendors TO vendors,
			 backup_products TO products;

使用视图

使用存储过程

使用游标

使用触发器

管理事务处理

全球化和本地化

安全管理

数据库维护

改善性能