MySQL知识梳理（二）

最新推荐文章于 2023-11-14 22:10:00 发布

May--J--Oldhu

最新推荐文章于 2023-11-14 22:10:00 发布

阅读量783

点赞数

分类专栏：总结性文章 MySQL 文章标签：触发器数据库 mysql sql

本文链接：https://blog.csdn.net/May_J_Oldhu/article/details/108174004

版权

总结性文章同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

MySQL

7 篇文章 0 订阅

订阅专栏

MySQL知识梳理（二）

四. SELECT 语法
五.事务
六.备份与恢复
七.触发器

四. SELECT 语法

SELECT   [ALL | DISTINCT] 
         {  * |  table.* | [ table.field1 [ AS  alias1] [, table.field2 [AS  alias2]][, …]] }
         FROM  table_name  [ AS  table_ alias  ]
        [ LEFT|OUT|INNER  JOIN  table_name2 ]    #联合查询
	    [ WHERE  … ]   	                         #指定结果需满足的条件
	    [ GROUP BY …]	                         #指定结果按照哪几个字段来分组
	    [ HAVING …]	                         #过滤分组的记录必须满足的次要条件
	    [ ORDER BY… ]	                         #指定查询记录按一个或者多个条件排序
	    [ LIMIT  {   [ OFFSET,] ROW_COUNT    |   ROW_COUNT OFFSET OFFSET   }] ;  #指定查询的记录从哪条至哪条

1.查询指定字段

  SELECT * FROM  student;
  SELECT studentno,studentNAME FROM student;#查询指定列（学号，姓名）

2. AS 子句

1)作用：

         给数据列取一个新别名
         给表取一个新别名
         把经计算或总结的结果用另外一个新名称来代替

2)用法：

SELECT studentno AS 学号,studentname AS 姓名 student;
SELECT studentno  学号,studentname  姓名 student AS s;
SELECT CONCAT('姓名：'+studentname) AS 新姓名 FROM student;

3. DISTINCT 关键字

   1)作用：
         去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同），只返回一条
   2)用法：

 SELECT DISTINCT 字段名1, 字段名2... FROM 表名;
eg:
  SELECT  DISTINCT studentno FROM student;

   3)备注:ALL 关键字是默认的，返回所有的记录

4.select查询中可以使用表达式

 SELECT @@ auto_increment_increment
 SELECT VERSION();
 SELECT 100*3-1 AS 计算结果;

5. WHERE 指定结果需满足的条件

   搜索条件组成：
      逻辑操作符:AND 或 &&,OR 或||,NOT 或 ！
      比较操作符:ISNULL,IS NOT NULL,BETWEEN AND,LIKE,IN

1)LIKE 结合使用的通配符：%（0到任何字符）, _一个字符

eg:

  #姓李的同学，但名字只有一个字
          SELECT studentno,studentname 
          FROM student
          WHERE studentname LIKE '李%';
          
          SELECT studentno,studentname 
          FROM student
          WHERE studentname LIKE '李_';
          #姓名中含有‘文’字
          SELECT studentno,studentname 
          FROM student
          WHERE studentname LIKE '%文%';
          #查询学员姓名中有‘%’这个字的同学的学号和姓名
          SELECT studentno,studentname 
          FROM student
          WHERE studentname LIKE '%\%%';
          #转义符\能不能使用自己的转义符？（：）:用escape作为转义符
          SELECT studentno,studentname FROM student
          WHERE studentname LIKE'%:%%' ESCAPE':'

2) IN (集合)

 SELECT studentno,studentname FROM student
 WHERE  studentno IN (1000,1001,1002);

3) NULL 空

 #查询出生日期没有填写的同学 =null 是错误的 和null比较必须写is null
          SELECT studentno,studentname FROM student
          WHERE borndate IS NULL
          # 查询出生日期没有填写的同学
          SELECT studentno,studentname FROM student
          WHERE borndate IS NOT NULL

4)BETWEEN 值1 AND 值2：在两个值之间

 SELECT studentno, studentresult FROM result
 WHERE studentresult BETWEEN 95 AND 100

6.连接查询

(1)内连接 INNER JOIN(同等值连接)

 SELECT student.studentno，studentname，result.studentno，studentresult
         FROM student AS s
         INNER JOIN result AS r
         ON r.studentno=s.studentno

(2)外连接 OUTER JOIN

    1)左外连接 LEFT [OUTER] JOIN 以左表为基准，以左表来一一匹配，匹配不上，返回左表纪录，右表以null补充

         SELECT student.studentno，studentname，result.studentno，studentresult
         FROM student AS s
         LEFT JOIN result AS r
         ON r.studentno=s.studentno

    2)右外连接 RIGHT [OUTER] JOIN 以右表为基准，以右表来一一匹配，匹配不上，返回右表纪录，左表以null补充

          SELECT student.studentno，studentname，result.studentno，studentresult
          FROM student AS s
          RIGHT JOIN result AS r
          ON r.studentno=s.studentno

    3)三种连接的关系
    
     INNER JOIN 内连接：返回两个表的交集
     LEFT JOIN 左连接：返回左表所有行
     RIGHT JOIN 右连接：返回右表所有行

(3)等值连接(同内连接)

        SELECT student.studentno，studentname，result.studentno，studentresult
        FROM student AS s,result AS r
        WHERE r.studentno=s.studentno

(4)非等值连接（返回的行数，两个表的行数相乘）

        SELECT student.studentno，studentname，result.studentno，studentresult
        FROM student AS s,result AS r

(5)自连接：自己关联自己

        CREATE TABLE IF NOT EXISTS category(
              categoryId INT(10)  AUTO_INCREMENT  PRIMARY KEY,
              categoryName   VARCHAR(32) NOT NULL ,
              pid  INT(10)
         );
         
        INSERT INTO category(categoryName,pid) VALUES('软件开发',0),('美术设计',0)
        ,('数据库基础',1),('photoshop基础',2),('色彩搭配学',2),('php基础',1),('一起学python',1);
        
        SELECT a.categoryName AS'父栏目名称',b.categoryName AS '子栏目名称' 
        FROM category a JOIN category b 
        ON b.pid=a.categoryId WHERE a.pid=0;

(6)全连接

   左外连接
    UNION
   右外连接
  UNION :联合并去重
  UNION all：联合不去重

7.使用子查询:由里到外

      SELECT SELECT stu_id r.sub_no,r.score
      FROM result r WHERE stu_id=(SELECT SubjectNo FROM `subject` WHERE  SubjectName='高等数学-2')
      AND score>80

8. ORDER BY 排序查询

  对SELECT语句查询得到的结果，按某些字段进行排序
  与DESC或ASC搭配使用，默认为ASC
  ORDER BY score DESC,studentno#降序

9. LIMIT[m,]n 或 LIMIT n OFFSET m

     #limit (pageno-1)*pagesize,pagesize  ( 当前页码-1)*页容量 页容量
      SELECT * FROM `result` LIMIT 5       #返回前5条记录
      SELECT * FROM `result` LIMIT 5,10    #返回6-15条记录

10. GROUP BY 指定结果按照哪几个字段来分组

   与having结合使用

       SELECT SubjectName AS 课程编号,AVG(score)AS 平均分
       FROM result r
       INNER JOIN `subject` s
       ON s.SubjectNo=r.sub_no
       JOIN grade g
       ON  g.gradeid=s.`GradeID`
       GROUP BY sub_no
       ORDER BY 平均分 DESC

11. HAVING 过滤分组的记录必须满足的次要条件

      SELECT s.sid,AVG(scores) AS 平均分
      FROM tb_student s
      JOIN tb_score
      ON s.sid=stu_id
      GROUP BY stu_id
      HAVING 平均分>60

12.MySQL函数

 (1)数学函数
 (2)字符串函数  
 (3)日期和时间函数
 (4)系统信息函数
注：具体函数信息见：https://blog.csdn.net/May_J_Oldhu/article/details/108121545

13.其他：

CASE 字符 WHEN 值 THEN 操作 [ELSE 操作] END

五.事务

1.含义：事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行

 注：MySQL事务处理只支持InnoDB和BDB数据表类型

2.事务的特性：ACID特性

 1)原子性（Atomic)：事务是一个完整的操作
 2)一致性（Consist）：当事务完成时，数据必须处于一致状态
 3)隔离性（Isolated）：事务是独立的
 4)持久性（Durable）：事务处理的结果是永久的

3.事务的实现方法

 SHOW VARIABLES LIKE 'event Scheuler';
     SET GLOBAL Scheduler=ON;
     #1.关闭mysql的自动提交，默认自动提交
     SET autocommit=0;#关闭
     #2.开始事务，
     START TRANSACTION;
     #3.事务

     #4.提交事务
     COMMIT;
     #5.回滚，数据回到本次事务的初始状态
     ROLLBACK;
     #6.还原mysql数据库的自动提交
     SET autocommit=1;#开启

示例：

       #1.建表
       CREATE TABLE account(
             id INT(4) PRIMARY KEY AUTO_INCREMENT,
             NAME VARCHAR (32) NOT NULL,
             cash DECIMAL(9,2) NOT NULL
        );

       #2.填数据
       INSERT account(`name`,cash)
       VALUES('A',2000),('B',10000.00);
       #3.事务
       SET autocommit=0;#关闭mysql的自动提交
       START TRANSACTION;#开始事务
       UPDATE account SET cash=cash-500 WHERE NAME='A';
       UPDATE account SET cash=cash+500 WHERE NAME='B';
       #commit;#提交
       ROLLBACK;#回滚
       SET autocommit=1;

六.备份与恢复

1.mysqldump备份：

      mysqldump  -h 主机名 –u 用户名 –p   [OPTIONS]   数据库名 
      [ table1 table2 table3 ]   > path/filename.sql
      mysqldump -uroot -pok --skip-ADD-DROP-TABLE        
   source 命令恢复：
              mysql>USE test;
              mysql>source 脚本路径
   mysql命令恢复：  
           mysql –u 用户名 –p <脚本路径

2.使用sql yog工具

3.使用sql命令方式来备份与恢复

     USE MySchool;
      #导出
      SELECT studnetno,studentname 
      INTO OUTFILE '路径'
      FROM student
     #导入
      USE test
      CREATE TABLE stutab(
           id INT(4),
           sname  VARCHAR(20)
      )
      LOAD DATA INFILE '路径' INTO TABLE stutab(id,sname)
      --skip-ADD-DROP-TABLE #跳过建表

    注意：备份的文件不能提前存在

七.触发器

1.定义：触发器用来在某些操作时，“自动”执行一些操作。

2.语法：

       CREATE TRIGGER 触发器名
       BEFORE|AFTER 事件 
       ON 表名 
       FOR EACH ROW 
       触发器语句;

 (1)触发器名建议为trigger_xxx，这样便于区分，触发器名不能重复
 (2)BEFORE|AFTER 代表触发器语句执行时间，如果是 BEFORE,就是在 INSERT|DELETE|UPDATE 操作之前执行触发器语句;AFTER 就是之后
 (3)事件:就是 INSERT DELETE UPDATE 操作中的一个。
 (4)FOR EACH ROW 是代表任何记录执行对应操作都会触发器。
 (5)触发器语句就是触发器触发时要执行的语句。
 (6)多个触发语句用begin end包含：BEGIN触发语句1;触发语句2;...触发语句n;END;
 注：MySQL中;默认是语句结束符，在命令行使用时有问题
     命令行使用多个触发语句时应当先修改语句结束符($$)

         DELIMITER @@
         语句
         @@

   #定义一个触发器，当修改account后相应在account_history插入一条相应语句
         CREATE TRIGGER  trig_account_his 
         AFTER UPDATE 
         ON account
         FOR EACH ROW
         INSERT INTO account_history  (account_name,changed_cash)VALUES('A',10000);

         UPDATE account SET cash =cash+10000 WHERE NAME='A';
         INSERT:new.
         DELETE:old.

      1)insert：新增记录，没有旧纪录;

           CREATE TRIGGER trigger_a_insert 
           AFTER INSERT 
           ON account 
           FOR EACH ROW
           INSERT INTO account_history(account_name,changed_cash)VALUES(new.name,new.cash);
 
           INSERT INTO account(NAME,cash) VALUES('abc',100);

      2) delete：删除旧纪录，没有新纪录;

           CREATE TRIGGER trigger_a_delete 
           AFTER DELETE 
           ON account 
           FOR EACH ROW
           INSERT INTO account_history(account_name,changed_cash)VALUES(old.name,old.cash*-1);

           DELETE FROM account WHERE id=8

      3)update：有变更后的新纪录，也有被变更的旧纪录;

3. 查看触发器

     (1)查看所有的触发器

        SHOW TRIGGERS;

     (2)在information_schema中查看triggers表

        SELECT * FROM information_schema.`TRIGGERS`

     (3)查看触发器创建语句

        SHOW CREATE TRIGGER 触发器名

4.删除触发器

            DROP TRIGGER 触发器名

May--J--Oldhu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL知识梳理（二）

MySQL知识梳理（二）四. SELECT 语法1.查询指定字段2. AS 子句1)作用：2)用法：3. DISTINCT 关键字4.select查询中可以使用表达式5. WHERE 指定结果需满足的条件1)LIKE 结合使用的通配符：%（0到任何字符）, _一个字符2) IN (集合)3) NULL 空4)BETWEEN 值1 AND 值2：在两个值之间6.连接查询(1)内连接 INNER JOIN(同等值连接)(2)外连接 OUTER JOIN(3)等值连接(同内连接)(4)非等值连接（返回的行数，两
复制链接

扫一扫