MySQL使用笔记2

连接表

• 内连接

SELECE vend_name, prod_name, prod_price FROM vendors, products ORDER BY vend_name, prod_name;   # 等值连接, 这种连接也称为内部连接
SELECE vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;  # 内部连接

• 多表连接

SELECT cust_name, cust_contact, FROM customer, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = order.order_num AND prod_id = "TNT2";

修改表名

ALTER TABLE tablename RENAME TO newtablename;
RENAME TABLE tablename TO newtablename;

为已经创建的表添加外键约束

ALTER TABLE orderitems ADD CONSTRAINT fk_order FOREIGN KEY (order_num) REFERENCES orders(order_num);

视图

• 视图就是一个数据库操作定义,每次使用视图时,都会执行定义的操作.
• 创建视图

CREATE VIEW viewname AS sql查询语句;
DROP VIEW viewname  # 删除视图

使用存储过程

• 执行存储过程

CALL productpricing(@pricelow, @pricehigh, @priceaverage);  # 执行名为productpricing的存储过程,计算并发挥产品的最低,最高和平均价格

• 创建存储过程

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

• 删除存储过程

DROP PROCEDURE productpricing;
DROP PROCEDURE IF EXIST productpricing;  # 如果存在存储过程,删除存储过程

• 使用参数

  CREATE PROCEDURE productpricing(
     OUT p1 DECIMAL(8,2),    # OUT指出相应的参数用来从存储过程传出一个值
     OUT ph DECIMAL(8,2),    # MySQL支持IN(传递给存储过程),OUT,INOUT(对存储过程传入和传出)
     OUT pa DECIMAL(8,2)
   )
   BEGIN
      SQLstatement.
   END;
   
  过程调用:  CALL productpricing(@pricelow, @priceheigh, @priceaverage);  # 所有的MySQL变量名都必须从@开头
  
  CREATE PROCEDURE ordertotal(
  IN number INT,
  OUT total DECIMAL(8,2)
  )
  BEGIN
     SELECT Sum(item_price*quantity)
     FROM orderitems
     WHERE order_num = number
     INTO total;  # 使用INTO关键字给输出变量赋值
  END;
  
  过程调用: CALL ordertotal(20005, @total);

• 可以在存储过程装使用IF,THEN, ELESIF, ELSE等变得更加灵活
• 显示存储过程的创建

SHOW CREATE PROCEDURE ordertotal;
SHOW PROCEDURE STATUS LIKE "ordertotal";  # 显示存储过程的详细信息(何时,由谁创建)

游标

• 游标就是被检索出来的结果集,应用程序可以根据需要滚动或浏览其中的数据
• 游标使用步骤

1. 定义游标,这个过程没有检索数据,只是定义要使用的SELECT语句
2. 打开游标,这个过程将会把数据实际检索出来
3. 根据需要,从游标中取出记录
4. 关闭游标,使用结束后必须要关闭游标

• 创建游标(DECLARE语句用来定义和命名游标)

1. DECLARE cursor_name CURSOR FOR select_statement 

2.CREATE PROCEDURE precessorders()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END;

• 打开和关闭游标

OPEN ordernumbers;  # 打开游标
CLOSE ordernumbers;  # 关闭游标

• 使用游标数据

OPEN cursorname;
FETCH cursorname INTO varname;  # 将游标中的一条记录赋值给变量
CLOSE cursorname;

触发器

• 创建触发器
  创建触发器时需要给出4条信息:
  1. 唯一的触发器名
  2. 触发器关联的表
  3. 触发器应该响应的活动(DELETE, INSERT或UPDATE)
  4. 触发器何时执行(处理之前或之后)

     CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT "product added";  # FOR EACH ROW指定每行改变都要执行SELECT "product added";
  

• 删除触发器

DROP TRIGGER triggername;

• 使用触发器
  • INSERT触发器

  CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;  # NEW为虚拟表,返回插入的记录的订单号
  

  • DELETE触发器

  CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW
  BEGIN
     INSERT INTO archive_orders(order_num, order_date, cust_id)
     VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);   # OLD为DELETE触发器的虚拟表
  END
  

  • UPDATE触发器

  CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
  

事务

• 事务术语
  • 事务(transaction): 指一组SQL语句
  • 回退(rollback): 指撤销指定SQL语句的过程
  • 提交(commit): 指将未存储的SQL语句结果写入数据库表中
  • 保留点(savepoint): 指事务处理中设置的临时占位符,你可以对它发布回退
• 事务操作
  • START TRANSACTION 标记事务的开始
  • 使用ROLLBACK进行回退,默认撤销整个事务
  • 使用COMMIT提交事务(如果不提交,事务中的操作将不会对数据库进行更改)
  • 使用SAVEPOINT pointname设置保留点(部分回退)
  • 使用ROLLBACK TO pointname来回退到某个保留点
• 更改默认的提交行为
  • SET autocommit = 0 (autocommit是针对每个连接的,而不是服务器的)

安全管理

• 创建一个用户

  CREATE USER ben IDENTIFIED BY "yourpassword";
  

• 用户重命名

RENAME USER ben TO bforta;

• 删除用户

DROP USER username;

• 设置访问权限
  新创建的用户账号没有访问权限,它们能登录MySQL,但不能看到数据,不能执行任何数据库操作.
  • 查看用户的访问权限

  SHOW GRANTS FOR username;
  

  -设置权限

  GRANT SELECT ON crashcourse.* TO username;  # 设置用户在crashcourse数据库上的所有表的权限
  

  • 撤销权限

  REVOKE SELECT ON crashcourse.*  FROM username;
  

  • 简化多次授权

  GRANT SELECT, INSERT ON crashcourse.*  TO username;
  

  • 更改口令

  SET PASSWORD FOR 	username = Password('password');  # Password()函数用于加密口令
  

  • 更改自己的口令

  SET PASSWORD = Password('password');
  

• 刷新权限

flush privileges;

• 将缓存写入到数据库中

flush tables;

数据库的备份和还原

• 使用mysqldump命令备份

mysqldump -u username -p dbname table1 table2 ...-> BackupName.sql  # 备份一个数据库
mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql  # 备份多个数据库
mysqldump -u username -p -all-databases > BackupName.sql  # 备份所有数据库

• 数据还原

mysql -u root -p [dbname] < backup.sq

数据备份还原参考博客(https://www.cnblogs.com/kissdodog/p/4174421.html)