Mysql的基本使用总结！( 数据类型 - 约束 - 范式 - 陌生命令 - 联合查询 - 事务)

目录

1："数据类型"：   

2："约束"：

3："三大范式"： 

4："基础命令"：

5："连接查询"：

6："事务"：

---

 

"数据类型"：   

日期按照格式选择，数值和字符串按照范围大小选择。 下方几张图片来自 菜鸟教程

            

            

            

 

 

"约束"：

        约束可以在建表的时候添加： alter table 表名 add 约束(字段);

                        如： alter table student add primary key(sno);        

            也可以创建联合约束。特点：设置多个主键，他们的值只要不完全重复就行(至少有一个值不重复),且主键的值都不能为null

                            alter table student add primary key(sno,sname);

        约束可以在修改字段时添加： alter table 表名 modify 字段 数据类型 约束;

                        如： alter table student modify sno varchar(20) primary key;

        

        删除主键约束： alter table 表名 drop 约束;

                        如： alter table student drop primary key;  --删除表中的全部的主键约束

 

        删除唯一约束：

                    查看约束的名字：show indexes from 表名\G;

                    alter table 表名 drop index 约束名;

        删除外键约束：

                    查看外建名： show create table 表名; 找外建名

                    alter table 表名 drop froeign key 外键名;

 

 

 

• 主键： primary key

                联合主键：     特点：设置多个主键，他们的值只要不完全重复就行(至少有一个值不重复),且主键的值都不能为null

                    

            

                后期添加主键： alter table 表名 add primary key(字段);

                后期删除主键： alter table 表名 drop primary key;

                通过修改字段修改主键： alter table 表名 modify sno INT primary key;

            

• 自增： auto_increment，设置自增的主键是删除不了的

                设置/取消自增： alter table 表名 modify 字段 数据类型 [其他约束 | auto_increment];

 

• 唯一 ： unique  

               联合唯一：特点和联合主键相同                         也可以在字段后指定：

                                              

                alter table 表名 add unique(字段);

    

• 非空： not null

               

• 默认： default ，如果不分配值就使用默认值，传值就不使用默认。

    

• 外键： foreign key

            外键的特点： 

                1. 父表中没有的数据值，在子表中是不能使用的

                2. 父表中的记录被子表引用的数据是不能被删除的。

 

 

            外键的作用： 通过外键关联的两个表，子表要以父表为依据来插入数据，不能插入父表中不存在的列。

                

            其中的studnet表就是子表，classes是父表，他们之间通过下图中的外键绑定，所以student中的最后一列只能是插入到class中的主键列中的值。

            如果在子表中插入不存在的值，就会报错：

                    

            创建表的时候创建为外键：

                foreign key(当前表的字段) references 要关联的表(主键)

                    

                    

 

 

"三大范式"： 

 

• 第一范式(1NF)： 拆字段。

    数据表中的所有字段都是不可再分的原子值。

            就是表的字段只能是最细化的，例如地址这种字段，如果只写一个address，他的值可以是"中国河北省保定市xx县xxx"，但是address这个字段可以继续细分为：country、province、city、county、等，直到这个字段不能再拆分为止。

 

        

    范式： 一般来说是越详细越好，对于某些的项目来说可能会好，但是对于一些项目来说详细了并不是好事。所以这些还要根据实际情况来设计表。

            如：我就想要一个家庭地址，如果设计的非常详细了就要将上面例子中 细化的所有字段拼接起来才行。但是这就会带来很大麻烦！

    

• 第二范式(2NF)：  拆表。           

            必须满足第一范式的前提下，第二范式要求，该表中除了主键外的每一列都必须完全依赖于主键，

            如果出现不完全依赖，只可能发生在联合主键的情况下。

        例如：设计一个订单表order。这个表中的xx_name只依赖于其对应的xx_id字段。很明显这不满足除了主键外的所有列都完全依赖主键

            所以联合主键并不满足第二范式。

                

 

 

        所以要拆表： 将各个依赖于主键的字段单独抽取出来独立组成一个表，然后外键关联主键表

                

    

• 第三范式(3NF)： 拆依赖。

            必须满足第二范式的前提下，出主键以外的其他列之间不能有传递依赖。

            例如： 还是在上面案例的基础上有一个customer_phone的字段。该字段可以通过customer_id找到，而customer_id可以通过order_id找到，这就会有传递依赖，所以customer_phone这个字段不能放在这个表里面。应该放在右边的表里面。

    

"基础命令"：

• " describe 表名;"  或者使用 " desc 表名;"：  查看创建好的数据表的结构

                

 

• "COMMENT 备注" : 在创建表的字段的时候添加备注的

 

• "distinct"： 去重
  • select distinct class from student; 

    

• "between xx and xx"： 查询区间
  • select * from student where sno between 3 and 5;
  • select * from student where sno>3 and sno<5;
  • select * from score where grade between low and upp; 

                                -- 查询成绩在最高和最低之间的所有信息，能直接用low和upp

            

• "in"： 表示某个范围内，否定使用 "not in"。范围内只能是相同类型的数据，即同一个字段的值
  • select * from student where sno in(1,2,4);

    

• "or"： 表示或者的关系，可以表示不同字段的值
  • select * from student where sno=1 or sname='张三';

    

• "order by"： 排序
  • 升序： select * from student order by sno acs;  默认升序
  • 降序： select * from student order by sno desc;

 

• • 对score表的grade升序，sno降序：  不会出现矛盾会先按照grade升序，分数相同的会降序(没有相同的就相当于没有score排序字段)

                    select * from score order by grade asc,score desc;      -- 用逗号隔开

 

• "count()"： 统计
  • select count(*) from score where grade=100;      -- 统计分数为100的人的个数

 

• 常用函数：

            max(字段)：select max(grade) 最高分 from score;

            min(字段)：。。。

            avg(字段)：。。。

            year(字段)：获取年份：select year(birthday) from student;

            now(字段)：获取当前时间： select year(now());  --获取当前时间的年份 

    

• "查询嵌套"： 查询最大小值，
  • 查询score中的最高分的学生学号和课程号

                select sno cno from score where grade=(select max(degree) from score);

            

• • 或者是： select sno cno from score order by grade desc limit 0,1;   

                -- 降序后，从0开始查询1条数据，但是最高分有多个就是会有bug。

 

• • in 子查询：select * from course where tno in (select tno from teacher where tsex='男');

 

• "group by xx"： 分组
  • select sno from score group by cno;      -- 将相同的课程号的学生分组出来

 

• "group by xx having xxx"： 过滤分组的数据
  • select * from score group by cno having count(sno)>=2;     -- 给sno个数大于2的人分组

    

• "like"：模糊查询，如果否定就使用 not like 'xx'

             %：   0或多个字符。

 

             _：   任意一个字符。通常用来限制字符串的长度

                     select * from user where realname like '%林_%';    -- 名字至少是两个字

 

            [ ]： 表示括号内所有字符中的一个。 如 [] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”

                    select * from user where realname like '[张王]杰';    -- 查询出"张杰"，"王杰"（而不是"张蔡王杰"）

                    select * from user where realname like '林[1-9]';    -- 将会查询出“林1”“林2”......“林9”

 

            [^]： 表示不匹配在括号内的字符。

                     select * from user where realname like '[^张王]杰';    -- 查询出不姓"张"、"王"的 “林杰”，“赵杰”等

 

• "as 或 不使用"： 别名，可以不用引号，但是当别名中有特殊字符的时候必须要是使用引号引起来
  • select sname as 姓名 ,sage 年龄 from student;    
  • 或者省略as：select sname 姓名,sage 年龄 from student;    

    

            别名的另一种使用方式：

                select s.sno,s.sname,sc.cno,sc.grade from student s,score sc where s.sno = sc.sno

 

• "多表查询"：
  • 双表查询： select cno sno from student,score where studnt.sno=score.sno;
  • 三表查询： select sname,cname,grade,student.sno,course.cno from student,course,score where student.sno=score.sno and course.cno=score.cno

    

• • 如果是查询数字之类的，建议使用 in，而不是 = 

                select * from course where tno in (select tno from teacher where department="计算机");

    

• "union"：联合查询。  将多个select查询语句结合起来显示。

            如果使用union又取了别名，显示的结果会根据union前面的select设置别名。

• • select sno from student where sno=1 union select cno from course where cno = "1001";
  • 别名版：select sno 编号 from student where sno=1 union select cno from course where cno = "1001";

                    +------+

                    | 编号 |

                    +------+

                    | 0001 |        -- 上面的两个对应的是学生编号，只是该列的名字是第一个select生孩子的别名

                    | 0002 |

                    | 1001 |        -- 其中的这个对应的是课程编号

                    +------+

 

• "any(范围)"： 至少满足范围内的一个条件。 大于最小！
  • select * from score where cno> any(select cno from course where cno ="1003");  -- 大于最小的cno就行
  • 等价于：  select ...from ... where cno > result1 or cno > result2 or cno > result3

                

 

• "all(范围)"： 满足所有条件。和any类似，只是将上面的or改为and。 大于最大！

 

            

        总结: ANY 和 ALL

            ANY:表示任何一个就行了,如;数组A中的值比数组B中任何一个都要大,那么只要A和B中最小的比较就行了 。

            ALL:表示所有都要比较,如:数组A中的值比数组B中所有的数都要大,那么A要和B中最大的值比较才行。

  

• "some(范围)"： 和any的功能相同

    

"连接查询"：

根据案例理解

    有两个单独的表：card和person，两个表之间没有任何关系，只是编写sql语句的时候在逻辑上有关系(下面的举例)。

                     

 

• "内连接"：  "inner join" 或 "join"
  • 基本语法： 左表  [inner] join  右表  on  左表 . 字段  =  右表 . 字段；

                        

          定义： 从左表中取出每一条记录，去右表中与所有的记录进行匹配，匹配必须是某个条件在左表中与右表中相同，最终才会保留结果，否则不保留。 (on 后面表示条件)就相当于where条件查询。

            select * from person inner join card on person.cardId = card.id;

        <=> select * from person,card where person.cardId = card.id;

            

 

 

    ON和WHERE的区别：

        ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。

        WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。

        从这里可以看出，将WHERE条件移入ON后面是不恰当的。推荐的做法是：

        ON只进行连接操作，WHERE只过滤中间表的记录。

   

• "外连接"： 基本语法：  左表  left/right join  右表  on  左表 . 字段  =  右表 . 字段；

 

• • 左外连接： "left join" 或 "left outer join"

                    

          定义： 会把左边表里面的所有数据取出来，而右表中的数据如果有和条件相等的就显示出来，如果没有相等的就显示为null。

 

             select * from person left join card on person.cardId=card.id;

                 

    

    

• • 右外连接： "right join" 或 "right outer join"

                    

          定义： 和左外连接相反，会把右表中的所有数据取出来，而左表中的数据如果有和条件相等的就显示出来，如果没有相等的就显示为null。

            select * from person right join card on person.cardId = card.id;

            

 

• "全外连接"： "full join" 或 "full outer join"

                     

    MYSQL不支持全外连接(可以使用左连接union右连接)，但是oracle等支持。

        定义： 会把左外连接和右外连接的结果合并到一起。

 

    union语句注意事项：

         1.通过union连接的SQL它们分别单独取出的列数必须相同；

         2.不要求合并的表列名称相同时，以第一个sql 表列名为准；

         3.使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；

         4.被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；

                (select id,name from A order by id) union all (select id,name from B order by id); //没有排序效果 

                (select id,name from A ) union all (select id,name from B ) order by id; //有排序效果

         5.union会自动将完全重复的数据去除掉;union all会保留那些重复的数据；

 

         select * from person left join on card where person.cardId = card.id

         union

         select * from person right join on card where person.cardId = card.id;

          

 

• "交叉连接"： 交叉连接会形成笛卡尔积

        select * from 表1 , 表2;

    

• " 联合连接(union join)"： 这是一种很少见的连接方式。Oracle、MySQL均不支持，其作用是：找出全外连接和内连接之间差异的所有行。这在数据分析中排错中比较常用。也可以利用数据库的集合操作来实现此功能  点击链接

    

 

"事务"：

事务是一个最小的不可分割的工作单元，事务能保证一个业务的完整性。要么全成功要么全失败  

 

mysql是默认开启自动提交事务的。 自动提交的作用： 当执行一个sql语句的时候，效果会立刻显示出来，且不能回滚。

    select @@autocommit; --可以查看是否开启事务的字段提交，1是开启，0是关闭。

        比如：执行mysql的插入操作的时候：insert into student values(1,"zs");   执行后查询数据就会立刻显示出来，因为自动提交了，然后执行rollback数据也不会被删除(回滚)，就是证明了mysql是自动提交数据的。

            但是如果设置了自动提交为false，执行了上面的insert操作也会执行成功，而且执行后查询也会立刻显示出来，但是可以执行rollback操作了，执行了rollback后会回滚，然后在查询刚才插入的数据就被删除了。 

            因为关闭了自动提交会将CRUD操作的表重新设置一个相同的新的虚拟的表，当数据操作时就是对这个虚拟表进行的操作。只有执行了commit操作才能将数据保存到真正的表，即使再执行rollback也不会回滚。

 

设置自动提交为false： set autocommit=0;    事务为我们提供了一个返回操作的机会。

                     begin;

                     start transaction;

    

• "事务的四大特性"：

            参考： https://blog.csdn.net/w_linux/article/details/79666086

 

        事务的四大特性ACID：

            原子性(A)： 事务是最小单位，不可再分

            一致性(C)： 事务要求所有的DML(insert、update、delete)语句操作的时候，必须保证同时成功或者同时失败

            隔离性(I)： 事务A和事务B之间具有隔离性

            持久性(D)： 事务一旦结束(commit/rollback)，就不可以再返回。是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)。

 

        - 提交(commit)：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步。

            在MySQL中，默认情况下，事务是自动提交的，也就是说，只要执行一条DML语句就开启了事物，并且提交了事务

        - 回滚(rollback)：撤销sql语句的执行，失败的结束，将所有的DML语句操作历史记录全部清空

 

 

• 事务操作：
  • 手动开启事务: 暂时关闭事务。即使自动提交也开启着，begin后逻辑上就相当于关闭了自动提交，但是实际上并没有关闭。

                begin;  或者  start tansaction;  或者 

                全局设置： set autocommit=1;

 

                结束事务: end transaction; 或者 rollback; 或者 commit;

 

• • 提交成功：

三步：

    - start transaction;    或者  begin;

    - DML语句

    - commit;

mysql> start transaction;#手动开启事务 

mysql> insert into t_user(name) values('pp'); 

mysql> commit;#commit之后即可改变底层数据库数据 

mysql> select * from t_user;

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

 | id | name | 

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

 | 1 | jay | 

 | 2 | man |

 | 3 | pp  | 

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

 3 rows in set (0.00 sec)

 

• • 提交失败：回滚操作

三步：

    - start transaction;

    - DML语句

    - rollback;

mysql> start transaction;

mysql> insert into t_user(name) values('yy');

mysql> rollback;

mysql> select * from t_user;

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

| id | name |

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

|  1 | jay  |

|  2 | man  |

|  3 | pp   |

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

3 rows in set (0.00 sec)

    

• 事务 ——  " 隔离性":  四个隔离级别：
  •   READ-UNCOMMITTED  <  READ-COMMITTED  <  REPEATABLE-READ  < SERIALIZABLE   隔离级别越高性能越差

查看数据库的隔离级别： 

    系统级别： 一般查看系统的级别

        mysql 8.x： select @@global.transaction_isolation;

        mysql 5.x： select @@global.tx_isolation;

 

    会话级别：

        mysql 8.x： select @@transaction_isolation;

        mysql 5.x： select @@tx_isolation; 

 

修改数据库的隔离级别：

    设置系统级别： 一般设置系统的级别

        mysql 8.x： set global transaction isolation level 下面四种级别;

        mysql 5.x： 。。。

 

    设置会话级别：

        mysql 8.x： set transaction isolation level 下面四种级别;

        mysql 5.x： 。。。

 

• • 1. "read uncommited": 读取未提交的。   使用之前先修改为当前的隔离级别。

                - 有两个事务，事物A和事物B，事物A未提交的数据，事物B可以读取到

                - 这里读取到的数据叫做“脏数据”。 

                - 脏读： 一个事务读取到了另外一个事务没有提交的数据叫脏读。

                - 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

 

 

            例如： 转账，早上张三开启事务，而且已经给李四店铺(双方都update)update了钱，但是没有commit，然后让李四店铺去查看一下是否到账，然后李四查看到确实到账，并且李四立刻就用了账户中的数据进行了消费。但是张三依然没有commit，到了中午，张三突然记起来这笔钱有问题需要rollback，并且回滚成功。然后这个钱就变回了双方默认update之前的状态。  此时的情况就是： 张三钱恢复称为了默认状态(自己钱恢复了，店铺钱变少了)，李四店铺没有发现钱变少了并且已经消费了账户中的数据(但事实上就是钱变少了)。 这种情况就出现了"脏读"。

 

 

 

• • 2. "read committed"： 读取已提交的。   使用之前先修改为当前的隔离级别。

                - 事物A和事物B，事物A提交的数据，事物B才能读取到

                - 这种隔离级别高于读未提交

                - 换句话说，对方事物提交之后的数据，我当前事物才能读取到

                - 这种级别可以避免“脏数据”

                - 这种隔离级别会导致“不可重复读取”

                - Oracle默认隔离级别

    

 

            例如： 张三开启事务查询了一遍当前表中的数据并算出平均数之后就去上了一个厕所，但是这时候李四在其他地方开启了一个事务，又对这个表进行了修改(比如添加了一个数据)，当张三回来后再查询数据算平均数发现数据算出的不一样了，也就是两次算出的平均数的值不一致。这种情况就是"不可重复读"。之前的数据只能读取一次。

    

• • 3. "repeatable read"： 可以重复读，mysql默认级别，性能较低

                - 事务A和事务B，事务A提交之后的数据，事务B读取不到

                - 事务B是可重复读取数据

                - 这种隔离级别高于读已提交

                - 换句话说，对方提交之后的数据，我还是读取不到

                - 这种隔离级别可以避免“不可重复读取”，达到可重复读取

                - 比如1点和2点读到数据是同一个

                - MySQL默认级别

                - 虽然可以达到可重复读取，但是会导致“幻读”

 

 

            例如：张三和李四对同一个表操作，并在操作前都开启了事务，该表有主键约束。张三先插入了一条数据，并且commit了，然后查询数据后能看到数据插入到数据表中，但是李四因为开启了事务的原因(因为当前隔离级别的缘故)查询到的数据还是张三插入数据之前的数据，所以当李四也要插入和张三同一个主键id的数据的时候就会报错，这就是"幻读"，这时双方操作数据就会出现一些问题。除非李四退出事务，再重新插入。 即：即使张三提交了事务，因为李四开启了事务，李四这边也读取不到。

 

 

• • 4. "sesrializable"： 串行化。    使用前先修改为当前的隔离级别

                - 事务A和事务B，事务A在操作数据库时，事务B只能排队等待

                - 这种隔离级别很少使用，吞吐量太低，用户体验差，性能特差

                - 这种级别可以避免“幻读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

 

 

            例如：张三和李四同时对一个表操作，他们都同时开启事务，张三先插入数据并提交(提交代表事务的结束)，随后李四这时也查询了数据，能看到张三插入的数据，现在是李四在操作这个表(李四的事务还没有结束)。但是张三又需要操作这个表，所以又开启了一个事务，要插入数据，写了insert语句后执行，发现张三卡在了执行步骤，什么也不能操作。这时只能等待(排队状态)李四结束事务张三的操作就会马上被执行。如果张三等待时间过长就会自动结束等待