MySQL

select from where group by having order by limit

使用mysql

连接

  • 主机名----如果连接到本机则是 localhost
  • 端口号----默认是3306
  • 一个合法的用户名
  • 用户口令

选择数据库

  • 使用use 关键词----use database(数据库名称);

查询数据库

  • show database;

查询表

  • show tables;

查询列信息

  • show columns from 表名;(它对每个列返回一行信息)

服务器状态查询

  • show status; (显示服务器状态信息)

显示其他信息

  • show grants; (用来显示 授予 用户的安全权限)
  • show errors; show warnings; (显示服务器错误或警告信息)

检索数据

sql 不区分大小写 一般关键词大写 列和表名小写

查询单个列 多个列

  • select 列名(*) from 表名;(返回所有行)

查询不想要值的列( 去重 distinct )

  • select distinct 列名 from 表名;(告诉mysql 只返回唯一的 列)

    不能部分使用 distinct 如果后跟多个列则都去重

Limit 限制结果

  • select 列 from 表 limit 5; (返回不多于5条)
  • select 列 from 表 limit 5,5; (返回第二个5条 第一个5表示跳过5条)

使用完全限定名称查询

  • select 表名.列名 from 数据库名.表名;

排序检索数据

排序数据(order by 默认升序 使用 desc 降序排列)

  • select 列 from 表 order by 列;
  • select 列1,列2,列2 from 表 order by 列1 desc,列2;(列1 降序,列2升序)
  • 如果多个需要降序 在各个列后面添加desc
  • 如果使用order by 和 Limit 结合 要保证 order by 位于from 或 where后 limie位于order by 后

过滤数据 where

  • MySQL在执行匹配时是不区分大小写的 所以where name="aa"和where name="AA"是相同的
  • between 两者之间 (where id between 5 AND 10; 包括开始和结尾)
  • 任何时候where 后使用OR 或者AND 都应该使用括号
  • 一般使用IN操作符 原因它比OR操作符要快 直白

通配符 LIKE

  • 百分号% 表示任意字符出现任意次数
  • 下划线 _ 表示匹配单个字符
  • 缺点:搜索速度慢 除非必须使用

正则表达式

  • 使用 REGEXP 关键词替代 like 告诉MySQL 我后面所跟为正则表达式

    select name where id REGEXP ".000"; 其中.表示匹配任一字符

  • 匹配过程不区分大小写 如果需要可使用BINARY

    where name REGEXP BINARY "Jet"

  • where id REGEXP "1000|2000|3000" 搜索多个串之一

  • 可使用 [123] 它和 [1|2|3] 一样

  • 匹配范围 [1-5] [a-z]

  • 匹配特殊字符 使用双斜杠\ \-

创建计算字段

拼接字段

  • 将两值拼接构成单个值 可使用Concat() 函数将俩列拼接

  • Concat() 需要一个或多个指定的串 各个串之间用逗号隔开

  • 例子:select Concat(name, '(' , Rtrim(id), ')' ) from 表名;

    其中 Rtrim 表示删除数据右侧空格

    也可以使用别名关键词 AS

计算字段

  • select 3*2; 返回6 select NOW(); 返回当前时间
  • select 列1*列2 As a from 表名;

数据处理函数

文本处理函数

  • Upper(列名) 转换大写

  • 发音搜索 Soundex

    例子:where Soundex(name) = Soundex("Li hua");

日期处理函数

  • 不管是插入还是查询日期必须为 yyyy-mm-dd

  • DateDiff() 返回两个时间的日期之差
  • AddDate() 增加一个日期
  • Time() 返回一个时间的时间部分
  • Date() 返回一个时间的日期部分

汇总数据

聚集函数

  • AVG() 函数 返回某列的平均值

    select AVG( 列 ) AS 别名 from 表名

    也可以为select AVG( distinct 列 ) AS 别名 from 表名 where...(<u>去重</u>)

  • Count() 函数 有两种使用方式

    使用Count(*) 对表中行的数目进行计数 不管表列中包含的是null还是非null值

    select count(*) from 表; 返回所有行不管行中各列有什么值

    使用Count(列) 对特定列中的具有值的行进行计数 忽略null值

    select count(email) from 表; 对表中有email的客户进行计数

  • MAX() 函数返回指定列中最大值包括日期

  • MIN() 函数返回指定列中最小值包括日期

  • Sum() 返回指定列值的和

分组数据

创建分组

  • 使用group by 关键词 位于where之后 order by 之前
  • 一般在group by 之后跟having 或者跟order by 保证数据正确

过滤分组 having

  • having 和 where区别 having 过滤分组 where过滤行

    having 数据分组后过滤 where 分组前过滤

    select id,count(*) AS aa from 表 GROUP BY id;

联结表

外键

  • 为某个表中的一列,它包含另一张表的主键值

可伸缩性

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

创建联结

  • 规定要联结的所有表以及他们如何关联即可

  • 例子:select v.name,p.name,p.price from v,p where <u>v.id=p.id</u> order by v.name,p.name;

  • 以上查询两张表 from两张表 最后把他们的联结关系对应,然后进行排序

    联结条件的时候要使用完全限定名

笛卡尔积

  • 由没有联结条件的表关系返回的结果为笛卡尔积,检索出的行的数目由两张表的乘积

    所以不要忘记where子句

内部联结

  • 目前为止所用的是等值联结 也称为内部联结,它基于两张表之间的相等测试

  • 例子:select v.name, p.name, p.price from v INNER JOIN p ON v.id = p.id;

    传递给ON 的条件和 where 条件相同

  • <u>一般使用 内部联结较多 方便程序员不忘记联结条件</u>

联结多个表

  • 首先列出所有表,然后定义表之间的关系

  • 例子:select a.name, b.name, c.name from a, b, c where a.id = b.id AND b.id = c.id;

    性能:不要联结没有不必要的表

创建高级联结

使用表别名-----AS

  • 缩短SQL语句

  • 允许在单条select语句中多次使用相同的表

  • 例子:select student.name, teacher.name, person.name from student AS s,

    teacher AS t, person AS p where s.id = t.id AND t.id = p.id;

  • 例子二--应用检索语句<u><还可以应用 ORDER BY , 语句其他部分></u>

    select s.name, t.name, p.name from student AS s,

    teacher AS t, person AS p where s.id = t.id AND t.id = p.id;

自联结

  • 如果你发现某物品(其ID 为DTNTR)存在问题,因此想知道生产该物品的供应商生产的其他

    物品是否存在问题------首先要找到生产该物品的厂家,然后再找出其他物品

    <u>使用子联结 查询</u>

  • select prod_id , prod_name FROM products WHERE vend_id =

    (select vend_id FROM products WHERE prod_id = "DTNTR");

    <u>使用自联结 查询</u>

  • select p1.prod_id , p1.prod_name FROM products AS p1, products AS p2

    WHERE p1.prod_id = p2.prod_id AND p2.prod_id = "DTNTR";

外部链接

  • 例子:select c.id , o.num FROM customers AS c LEFT OUTER JOIN orders AS o

    ON c.id = o.id;

  • 在使用OUTER JOIN必须使用 LEFT 或者 RIGHT 指定包括所有行的表

使用带聚集函数的连接

SELECT c.id , c.name , COUNT(o.num)  AS num  
FROM customers AS c  
LEFT OUTER JOIN orders AS o
ON c.id = o.id  GROUP BY c.id;

小结

  • 一般使用内部联结
  • 保证正确的联结条件,否则返回不正确的数据
  • 应该总是提供联结条件 否则出现笛卡尔积

组合查询

组合查询

  • MySQL 也允许执行多个查询并将结果作为单个查询结果集返回,这些组合查询

    通常称为并或复合查询

  • 两种情况需要用到组合查询

    • 在单个查询中从不同的表返回类似结构的数据
    • 在对单个表执行多个查询,按单个查询返回数据

组建组合查询

  • 利用UNION 关键词,可给出多条select语句,将它们的结果作为结果集

使用UNION

  • 使用它很简单,只需要在多条select中间放上UNION

  • 例子:假设需要价格小于等于5的所有产品的一个列表,而且还想提供包括

    供应商1001和1002生产的所有产品

    select v_id, p_id, p_price FROM products WHERE p_price <=5

    UNION

    select v_id, p_id, p_price FROM products WHERE v_id IN(1001,1002);

UNION 规则

  • UNION 必须由两条或者多条select语句组成,语句之间用UNION分割
  • UNION 中的每个查询必须包含相同的列,表达式或聚集函数
  • 列数据类型必须兼容:类型不必完全相同,但必须DBMS可以隐含的转换的类型

包含或取消重复的行

  • 重复的行自动取消,如果需要可使用UNION ALL 而不是UNION

排序

  • 在使用UNION 组合查询时,只能使用一条ORDER BY 子句,他必须出现在

    最后一条 selcet 语句之后(实际上对整个结果集排序)

全文本搜索

概念

  • MySQL 中MyISAM引擎支持全文本搜索

  • 性能 — 通配符Like 和正则表达式匹配通常都会要求MySQL尝试匹配所有行,

    因此这样非常耗时(因为极少使用索引)

  • 因为全文本搜索 会使MySQL 创建指定列中各词的一个索引,搜索可以针对

    这些词进行,比较快。

使用全文本搜索

  • 为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断的

    重新索引。在对表列进行适当设计后,MySQL会自动进行所有的索引和重新

    索引。在索引之后 SELECT 可与 Match() 和 Against() 一起使用执行搜索。

启动全文本搜索支持

  • 创建表的时候启用全文本搜索,create table 接受 FULL TEXT ,它给出被索

    引列的一个逗号分割的列表。

CREATE TABLE productbotes
(
    note_id int NOT NULL AUTO_INCREMENT, //自增
    prod_id char(20) NOT NULL,
    note_date datetime NOT NULL,
    note_text text NULL,
    PRIMARY KEY(note_id),                 //主键
    FULLTEXT (note_text)                  //索引
)ENGINE = MyISAM;
  • 表中有个note_text 列,MySQL根据子句FULLTEXT(note_text) 对其索引

    这里 FULLTEXT 索引单个列,也可以索引多个列。定义索引之后MySQL会

    自动维护索引。在增加,删除,更新行时,索引也会随之更新。

  • 可以在创建表时指定索引,也可之后再指定但必须针对已有数据立即索引

  • 不要在导入数据时使用FULLTEXT,先导入数据再启用索引

  • 再索引之后,使用两个函数 Match() , Against() , 进行全文本搜索,其中

    Match() 指定被搜索的列,Against() 指定要使用搜索的表达式。

    SELECT note_text
    FROM productbotes
    WHERE Match(note_text) Against("rabbit");

    解析:此检索语句只查询 note_text 列Match(note_text) 针对指定的列搜索,

    Against("rabbit") 作为搜索文本 返回两行包括 rabbit 的内容。

  • 传递给 Match() 函数的参数必须与 FULLTEXT 内容相同,如果指定多个列

    则必须列出他们(而且次序相同)

  • 搜索不区分大小写,除非使用BINARY

  • 全文本搜索重要部分----对结果排序,具有较高级别的行先返回

    SELECT note_text
    Match(note_text) Against("rabbit") AS rank
    FROM productnotes;

    上面例子rank 列 会显示等级值 (根据包含 rabbit 个数 和 它所在的行数)

    根据等级制排序 越高越靠前显示

查询扩展

  • 首先全文本搜索要查询词的行

  • MySQL 匹配这些行并且选出有用的词

  • 再次进行全文本搜索 不仅使用原本的 还使用有用的词

    SELECT note_text 
    FROM productnotes
    WHERE Mctch(note_text) Against("anvils" WITH QUERY EXPANSION);//使用关键词扩展查询

布尔文本搜索

  • 要匹配的词

  • 要排斥的词

  • 排列提升

  • 表达式分组

  • 即使没有FULLTEXT 也可以使用

    //匹配包含 heavy 词 但不包含任意以 rope 开始的词的行
    SELECT note_text
    FROM productnotes
    WHERE Mctch(note_text) Against("heavy -rope*" IN BOOLEAN MODE);
    //  - 代表排除 * 代表截断

全文本搜索注意事项

  • 在索引全文本数据时,短词(3个或以下的字符)被忽略且从索引中排除
  • MySQL 带有一个内建的非用词,往往在索引全文本时总是被忽略
  • 一个词语超过50%的行中,视为一个非用词
  • 如果表中的行数少于3 行,则全文本搜索不返回结果
  • 忽略词的单引号

插入数据

使用方式

  • INSERT 用来插入(或添加)行到数据库中
  • 插入完整的行
  • 插入行的一部分
  • 插入多行
  • 插入某些查询的结果

插入完整的行

  • 这种方式不安全

    INSERT INTO Students
    VALUES(NULL,      //此处id会自动增量
        "LiHua",        //没有值的列应该使用NULL(前提该列允许NULL)     
        28,         
        "AnHui");
  • 安全但繁琐的插入

    优点:即使表的结构发生改变也能正常使用

    必须每一个列都要给默认值

    INSERT INTO students(id,
                      name,
                      age,
                      address)
                    VALUES(NULL,
                          "LiHua",
                          28,
                          "AnHui");
    //降低 INSERT 语句的优先级  目的:为了高性能的 SELECT
    INSERT LOW_PRIORITY INTO...

插入多个行

INSERT INTO students(id,
                    name,
                    age,
                    address)
                 VALUES(NULL,
                       "LiHua",
                       24,
                       "ShangHai"
                       ),(              //每组值用括号括起来 逗号分开
                           NULL,        //使用单条语句插入要比多条语句插入性能要快
                           "zhangsan",
                           26,
                           "ANHUI"
                       );

插入检索出的数据

/*例子:把 custnew 表中的数据导入到 customers
应该首先创建和填充custnew 表 不应该使用在 customers 中使用过的cust_id 值
防止后续插入错误*/

INSERT INTO customers(cust_id,
                     cust_contact,
                     cust_name)
                  SELECT cust_id,
                        cust_contact,
                        cust_name
                  FROM custnew;
 /* SELECT 中列出的每个列对应 customers 后所跟列 (也可以不用两张表列名
    相同只需对应各自位置即可,其中select 语句可跟WHERE 筛选)
    例子中导入了 cust_id 前提是确保 cust_id 不重复 也可以不导入此列*/

更新数据----Update

更新数据

  • 更新表中所有的行
  • 更新表中特定的行
  • 注意:不要忽略 WHERE 子句,稍不注意就会更新表中所有行
  • 安全:可以限制和控制 UPDATE 语句的使用

组成部分

  • 要更新的表
  • 列名,和它的新值
  • 确定要更新行的过滤条件
UPDATE personSET name = "刘德华"WHERE id = 111;/*更新多个列  中间用逗号隔开*/UPDATE personSET name = "刘德华",    age = 27 WHERE id = 111;
  • 在 UPDATE 语句中使用子查询 用以更新需要的数据

  • IGNORE 关键词

    如果用 UPDATE 语句更新多列时,在更新的过程中如果发生错误,

    则整个 UPDATE 操作被取消(错误发生去更新的数据 回复原样)

    <u>即使发生错误需要继续执行的时候使用 IGNORE 关键词</u>

    UPDATE IGNORE person.....

删除某列的值

UPDATE person SET name = null  /* 可以设置其为 NULL  */WHERE id = 111;

删除数据 DELETE

  • 从表中删除特定的行

  • 从表中删除所有的行

  • 不要忽略 WHERE 语句

    DELETE FROM personWHERE id = 111;  /*删除 id 为 111 的一行数据*/

删除所有行 TRUNCATE TABLE

  • 不要使用 DELETE 因为 DELETE 是逐条删除,而 TRUNCATE TABLE

    是删除表,再重新创建表,速度更快

注意事项

  • 除非确实需要删除或者更新每一行,否则不要忽略 WHERE

  • 保证每个表都有主键

  • 在对表进行更新和删除时,应该先检索下数据是否正确

  • 使用强制实施引用完整性的数据库,这样MySQL 将不允许删除

    具有与其他表相关联的数据的行

创建和操作表

创建表

  • 使用交互式的工具
  • 使用MySQL 语句

创建表的语句

  • 新表的名字,必须在 CREATE TABLE 之后

  • 表列的名字和定义,用逗号隔开

    CREATE TABLE person     /*如果想在表不存在创建表则只需要在表名后添加 IF NOT EXISTS */( per_id   int         NOT NULL  AUTO_INCREMENT, /*自增*/    per_name var(20)     NOT NULL,    per_age  int         NOT NULL,    PRIMARY KEY (per_id)   /*定义主键*/) ENGINE = InnoDB;

主键再介绍

  • PRIMARY KEY ( 列名 ) ;

  • 如果主键使用单个列,则它的值必须唯一。

  • PRIMARY KEY ( 列名,列名 ) ;

  • 如果使用多个列,则这些列的组合值必须唯一。

    解析:每个订单有很多物品,都要第一个物品,第二个物品

    所以 订单号和订单物品组合 作为主键

  • 主键只能使用不允许 NULL 值的列

使用 AUTO_INCREMENT

  • 每个表只允许一个自增列,而且他必须被索引

    如果插入使用新的值,则以后的数据按照此值自增

    SELECT last_insert_id() /*返回最后一个 AUTO_INCREMENT 值*/

指定默认值

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

    CREATE TABLE person(  id int      NOT NULL AUTO_INCREMENT,    age int      NOT NULL DEFAULT 25,/*默认25岁 */    name var(20) NOT NULL,    PRIMARY KEY(id))ENGINE = InnoDB;

引擎

  • InnoDB 是一个可靠的事务处理引擎,不支持全文本搜索。

  • MEMORY 在功能上等同于 MyISAM ,但由于数据存在内存,速度快(适合临时表)

  • MyISAM 是一个性能极高的引擎,支持全文本搜索,不支持事务处理

    <u>使用一个引擎的表不能引用具有使用不同引擎的表的外键</u>

更新表

  • 在 ALTER TABLE 之后给出要更改的表名

  • 所做更改的列表

    ALTER TABLE personADD height int NOT NULL;

复杂的表结构更改需要注意:

  • 用新的列布局创建一个新表
  • 使用 INSERT SELECT 语句,从旧表复制数据到新表
  • 检验包含所需数据的新表
  • 重命名旧表
  • 用旧表原来的名字重命名新表
  • <u>根据需要创建 触发器,存储过程,索引,外键</u>

删除表

DROP TABLE person;

重命名表

RENAME TABLE person TO persons;

视图

概念

  • 视图是虚拟的表,视图只包含使用时动态检索数据的查询。

使用视图的好处

  • 重用 SQL 语句
  • 简化复杂的 SQL 操作
  • 使用表的组成部分而不是整个表
  • 保护数据 ------- 可以给用户授予表的特定部分的访问权限而不是整个表
  • 更改数据格式和表示 ------- 视图可返回与底层表的表示和格式不同的数据

视图的规则和限制

  • 与表一样,视图名称必须唯一

  • 视图数量没有限制

  • 为了创建视图,必须有足够的访问权限

  • 视图可以嵌套

  • ORDER BY 可运用在视图中,但如果运用视图检索的语句中有ORDER BY

    则 视图的ORDER BY 会被覆盖

  • 视图不能索引,也不能有触发器或默认值

  • 视图可以和表一起使用 ------ 编写一个视图和表的联结查询

使用视图

  • 视图使用 CREATE VIEW 语句创建

  • 使用 SHOW CREATE VIEW 视图名 ; 来查看创建视图的语句

  • 用 DROP 删除视图, DROP VIEW 视图名;

  • 更新视图的时候,可以先用DROP 再用 CREATE ,也可以直接用 CREATE OR

    REPLACE VIEW ,如果更新的视图存在,则第二条更新语句会替换原有视图,

    如果更新的视图不存在,则第二条更新语句会创建一个视图

    SELECT cust_name,cust_contact               /*三表联查*/
    FROM customers, orders,orderitems
    WHERE customers.cust_id = orders.cust_id
    AND orderitems.order_num = orders.order_num
    AND prod_id = "TNT2";
    /*创建视图*/
    CREATE VIEW productcustomers AS         /*使用 CREATE VIEW 创建视图*/
    SELECT cust_name,cust_contact               
    FROM customers, orders,orderitems
    WHERE customers.cust_id = orders.cust_id
        AND orderitems.order_num = orders.order_num 
    /*进行查询*/
    SELECT cust_name,cust_contact 
    FROM productcustomers             /*作为视图,不包含表中数据,包含的是SQL查询*/
    WHERE prod_id = "TNT2";           /*如果原表更改 则视图返回更改之后的数据*/

用视图重新格式化检索出的数据

CREATE VIEW good AS
SELECT Contact(RTrim(vend_name),"(," RTrim(vend_country),")") 
        AS vendors
        ORDER BY vend_name;

使用视图过滤不想要的数据

CREATE VIEW well AS 
SELECT id,name      /*如果有两个WHERE语句 则会组合(一组在视图中 一组在查询中)*/
WHERE age>18;      

使用视图与计算字段

CREATE VIEW gg AS 
SELECT name ,
      age*height
FROM person;

更新视图

  • 视图是可更新的(可以对他们使用 INSERT UPDATE 和 DELETE)

  • 如果视图中包含以下操作不能更新视图

    一般不需要更新 因为视图用于查询比较多

    • 分组 (使用 GROUP BY 和 HAVING)
    • 联结
    • 子查询
    • 聚集函数( MIN() , Count() , Sum() 等 )
    • DISTINCT
    • 导出列

使用存储过程

存储过程

  • 把多条语句封装成一个集合

使用存储过程的好处

  • 简化复杂的操作
  • 假设都使用此存储过程,数据的完整性
  • 如需更改表名,只需要更改存储过程其代码
  • 使用存储过程要比使用单独的SQL 语句要快

创建存储过程

CREATE PROCEDURE productpricing()  /* 存储过程名为 productpricing 如果接受参数则需在括号定义*/
BEGIN 
    SELECT Avg( prod_price ) AS priceaverage
    FROM products;
END;                            /* BEGIN 和 END 定义存储体 */

/* 由于MySQL语句分隔符和 命令行实用程序都为' ; '  这可能出现错误 */
DELIMITER //   /*告诉命令行实用程序使用 // 作为新的语句分隔符*/

CREATE PROCEDURE productpricing()
BEGIN 
    SELECT Avg( prod_price ) AS priceaverage
    FROM products;
END//

DELIMITER ;  /* 恢复原来的语句分隔符 */

使用存储过程

CALL productpricing();          /*调用无参*/

删除存储过程

DROP PROCEDURE IF EXISTS productpricing; /*仅当存在时删除,后面没有括号*/

使用参数

  • 存储过程不显示结果,而是把结果返回给你指定的变量

    CREATE PROCEDURE productpricing ( OUT pl DECIMAL(8,2), /* OUT 从存储过程传出给调用者 MySQL 还支持 IN 传递给存储过程*/  OUT ph DECIMAL(8,2), /* INOUT 对存储过程传入和传出类型的参数 */    OUT pa DECIMAL(8,2))BEGIN                   /* 存储过程的代码位于 BEGIN 和 END */ SELECT Min(prod_price)  INTO pl             /*存储最低价格 通过 INTO 保存在指定的变量*/ FROM products;  SELECT Max(prod_price)  INTO ph             /*存储最高价格*/  FROM products;  SELECT Avg(prod_price)  INTO pa             /*存储平均价格*/  FROM products;END;/* 调用它 */CALL productpricing(@pricelow                    @pricehigh                    @priceaverage); /*调用有参 他们是保存结果的三个变量 所有MySQL变量都以@开头*//* 调用后 不显示任何结果 */SELECT @pricelow ;/* 或者 */SELECT @pricelow, @pricehigh, @priceaverage;

使用 IN OUT 传入传出参数

CREATE PROCEDURE ordertotal(    IN onumber INT,         /* IN 代表 onumber 被传入存储过程 */    OUT ototal DECIMAL(8,2)   /* OUT 代表 ototal 被传出存储过程 */)BEGIN  SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = number    INTO ototal;    /* INTO 使用ototal存储计算出来的合计 */END;/*调用*/CALL ordertotal(2005,@total); /*第一个参数为订单号,第二个参数为包含计算出来的合计的变量名*/SELECT @total;

智能存储过程

CREATE PROCEDURE ordertotal(    IN onumber INT,    IN taxable BOOLEAN,  /*添加一个boolean参数*/    OUT ototal DECIMAL(8,2))COMMENT "Obtain order total,optionally adding tax " /*comment 不是必须的 加上将可以在*/BEGIN                                          /*SHOW PROCEDURE STATUS 的结果中显示*/    /*定义两个局部变量*/    DECLARE total DECIMAL(8,2); DECLARE taxrate INT DEFAULT 6; /*默认6%*/     SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber   INTO total;  /*暂时保存局部变量*/       IF taxable THEN     SELECT total+(total/100*taxrate) INTO total;    END IF; SELECT total INTO ototal;END;   /*调用*/CALL ordertotal(2021,0,@ototal); /* 0 表示假 1表示真*/SELECT @ototal;

检查存储过程

SHOW CREATE PROCEDURE ordertotal; /*为了获取何时由谁创建等信息*/

游标

概念

  • 有时需要对检索出来的行中前进或者后退一行或多行
  • 存储在MySQL 服务器上的数据库查询
  • 主要用在交互式应用,其中用户需要滚动屏幕上的数据

使用游标

  • 声明游标,只是定义要使用的SELECT 语句
  • 打开游标,把之前定义的SELECT 语句检索出来
  • 对于填有数据的游标,根据需要取出各行
  • 关闭游标

创建游标

CREATE PROCEDURE processorders()BEGIN   DECLARE o INT;   /*定义变量*/       DECLARE ordernumbers CURSOR  /*定义和命名游标*/    FOR SELECT order_num FROM orders;   OPEN ordernumbers;    /*打开游标 会执行查询 */   FETCH ordernumbers INTO o;/*使用 FETCH 将检索的数据 自动从第一行开始到O这个局部变量*/  CLOSE ordernumbers;    /*关闭游标*/END;

触发器

概念

  • 某个表发生改动时,自动处理

  • 响应以下任意语句自动执行

    DELETEINSERTUPDATE/*其他语句不支持触发器*/

创建触发器

  • 唯一的触发器名

  • 触发器关联的表

  • 触发器应该响应的活动(增删改)

  • 触发器何时执行

  • 只有表才支持触发器

  • 保证每个数据库的触发器名唯一

  • 每个表每个事件每次只允许一个触发器,所以一张表只有6个触发器

    ( INSERT UPDATE DELETE 在他们之前之后发生 )

  • 单一触发器不能与多个事件或多个表关联
/*CREATE TRIGGER 创建在Insert products 后每一行只要插入 就会出现 "Product added" */
CREATE TRIGGER newproduct AFTER INSERT ON products  
FOR EACH ROW SELECT "Product added";

删除触发器

DROP TRIGGER newproduct;  /*触发器不能更新,只能先删除后创建*/

INSERT 触发器

  • 在 INSERT 触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行

  • 在 BEFORE INSERT 触发器中,NEW 中的值也可以被更新(允许更改被插入的值)。

  • 对于 AUTO_INCREMENT 列,NEW 在 INSERT 执行之前包含0,在 INSERT

    执行之后包含新的自动生成值。

    CREATE TRIGGER neworder AFTER INSERT ON orders /*创建neworder 触发器在AFTER INSERT 之后执行*/
    /*创建一个NEW 表,每次插入就会在order_num 插入值,触发器从NEW.order_num取得值并返回它*/
    FOR EACH ROW SELECT NEW.order_num;        

  通常 BEFORE 用于验证数据正确性,保证插入值正确

### DELETE 触发器

- 

## 事务

### 事务处理

- InnoDB 引擎支持事务处理
- 维护数据的完整性,保证成批的MySQL 要么完全执行,要么完全不执行

### 概念

- 事务处理:是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库

  不包含不完整的操作结果。利用事务处理,可以保证一组操作不会中途停止,它

  们或者作为整体执行,或者不完全执行,如果发生错误则进行回退,恢复数据库

  到某个已知且安全的状态,如果不发生错误整组语句提交到数据库

### 名词

- 事务:指一组SQL语句    transaction
- 回退:指撤销指定SQL语句的过程    rollback
- 提交:指将未存储的SQL语句写入数据库    commit
- 保留点:指事务处理中设置的临时占位符    savepoint

### 控制事务处理

~~~mysql
SELECT * FROM ordertotals;
START TRANSACTION;       /*标志事务的开始*/
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;               /*回退 START 之后的所有语句(用来撤销MySQL语句) 只能回退 增 删 改*/    
SELECT * FROM ordertotals;

使用COMMIT

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 2021; /*因设计两表的语句 使用事务*/
DELETE FROM orders WHERE order_num = 2021;
COMMIT;  /*仅在两条语句不出错的情况下提交*/

使用保留点

  • 为了回退部分事务,需要在合适的地方添加占位符 即 保留点。

    SAVEPOINT delete1; /*设置保留点 保证名称唯一*/
    ROLLBACK TO delete1;  /*回退到保留点*/

更改默认的提交行为

SET autocommit = 0; /*0表示关闭自动提交*/

客户权限

访问控制

  • 用户对他们的数据具有适当的访问权,不能多也不能少
  • 防止无意的错误
  • 不要使用 root

管理用户

USE mysql; /*MySQL 用户账户和信息存储在名为mysql 的数据库中*/
SELECT user FROM user; /*user 表存储所有用户账号,user列存储用户登录名*/

创建用户账号

CREATE USER ben IDENTIFIED BY 'P@$$WOrd';

重新命名用户账号

RENAME USER ben TO bforta;

删除用户账号

DROP USER bforta;

设置访问权限

  • 查看用户权限(一般新建的用户没有访问权限)
SHOW GRANTS FOR bforta;
  • 设置权限需要使用 GRANT ,并且给出以下信息
    • 要授予的权限
    • 被授予访问权限的数据库或表
    • 用户名
GRANT SELECT ON crashcourse.* TO bforta; /*允许bforta 在crashcourse数据库上所有表使用查询*/

撤销用户权限

REVOKE SELECT ON crashcourse.* FROM bforta;

注意事项

  • 当某个数据库或者表被删除时权限仍然存在,如果将来重新创建数据库或表权限仍然起作用

更改口令

SET PASSWORD FOR bforta = Password('root');  /*新口令必须到Password()函数加密*/
/*设置当前登录用户的口令*/
SET PASSWORD = Password('root');

数据库维护

诊断启动问题

  • --help 显示帮助
  • --safe-mode 装载减去某些最佳配置的服务器
  • --verbose 显示全文本消息
  • --version 显示版本消息然后退出

查看日志

可用 FLUSH LOGS 用来刷新或重新开始所有日志文件

  • 错误日志:包含启动和关闭问题以及任意关键错误的信息

    日志通常名为 hostname.err 位于data目录中,此日志名可

    用--log--error命令行选项更改

  • 查询日志

    它记录所有MySQL活动,在诊断问题时非常有用,通常命名

    hostname.log,位于data目录中

  • 二进制日志

    它记录更新过数据的所有语句 命名为 hostname-bin 位于data

  • <u>缓慢查询日志</u>

    记录查询缓慢的日志 后期优化很有用,命名 hostname-slow.log

    位于data目录

改善性能

  • SELECT 有多种方法,可试试联结,子查询找出最快的
  • 使用存储过程要比一条一条查询快
  • 使用正确的数据类型
  • 不要使用 SELECT *
  • 必须索引数据库表以改善数据检索的性能
  • like很慢 应该使用 FULLTEXT