devbox
weixin_40725706
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL的概念与常用的SQL语句_mysql 内模式 外模式

作者：weixin_40725706 | 2024-06-12 21:15:54

赞

踩

mysql 内模式 外模式

/*殊彦shuyan*/

                                          第一章 基础概念

1. 数据库管理系统

  • 数据：对客观事物的符号表示，如图形符号、数字、字母等，数据是数据库中存储的基本对象。在数据库中，所有的数据都被保存在数据表中，数据表通过行来表示一条完整的记录，通过列来表示每一条记录的组成

  • 数据库DB：是按照数据结构来组织、存储和管理数据的容器，具有较小的冗余度、较高的数据独立性和易扩展性。基本特点：永久存储、有组织、可共享

  • 数据库管理系统DBMS：数据库软件，是一种操纵和管理数据库的大型软件，是用于建立、使用和维护数据库

  • 数据库系统DBS：是由数据库及其管理软件组成的系统

  • 数据库管理员DBA：负责全面管理和控制数据库系

                                                   

  • 表：一种结构化的文件，用来存储某种特定类型的数据，存储在表中的数据是同一种类型的数据或清单。使表名成为唯一的，实际上是数据库和表名的组合。在相同数据库中不能两次使用相同的表名，但是在同一个数据库中完全可以使用相同的表名

  • 数据类型：定义了列可以存储的所允许的数据类型。每个表列都有相应的数据类型，它限制（或允许）该列中存储的数据。数据类型及其名称是SQL不兼容的一个主要原因

  • 主键：一列（或者一组列），其值能够唯一标识表中每一行，就好像一个人的身份证一样，唯一性地代表那样东西。主键具有唯一性，且自动生成主键索引，数据库中通过主键进行查询是最快的，主键还有一个最大的用处是用作其他表的外键，在进行关联查询时提高查询速度。数据库的每张表只能有一个主键，不可能有多个主键。所谓的一张表多个主键，我们称之为联合主键，就是用多个字段一起作为一张表的主键。

2. 数据模型

数据库结构的基础是数据模型。数据模型是一个描述数据、数据联系、数据语义以及一致性约束的概念工具的集合。数据模 型提供了一种描述物理层、逻辑层和视图层数据库设计的方式。

  • 概念数据模型：面向用户的，按照用户的观点进行建模，典型代表：E-R图
  • 结构数据模型：面向计算机系统的，用于DBMS的实现，典型代表有：层次模型，网状模型、关系模型，面向对象模型

3. 什么是事务？

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

一系列相关的操作，要么全部成功，要么全部不执行。

4. 数据库的四大特性ACID

  • 原子性Atomicity：事务内包含的所有操作要么全部成功，要么全部失败回滚
  • 一致性Consistency：数据库应该不管多少并发事务执行，都应该保持一致性
  • 隔离性Isolation：每个之间互不影响，即使相同操作，也如同串行一样，顺序执行，同一时间一份数据执行
  • 持久性Durability：指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的

5. MySQL的四种隔离级别

  • 读未提交READ UNCOMMITED：在该隔离级别，所有事务都可以读取其它事务未提交的数据。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。
  • 读已提交READ COMMITED：这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：在事务未提交之前所做的修改其它事务是不可见的。
  • 可重复读REPEATABLE READ：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
  • 可串行化SERIALIZABLE：可串行化就是保证读取的范围内没有新的数据插入，比如事务第一次查询得到某个范围的数据，第二次查询也同样得到了相同范围的数据，中间没有新的数据插入到该范围中。

                                              

6. 事务的并发问题

  • 脏读：A，B两事务，A事务会读取到B事务未提交的数据，然后B因为某些原因回滚数据，所以A就读取了B没有提交的数据，也称脏数据
  • 不可重复读：在A事务中对同一数据两次查询不一致，可能原因是在A事务提交之前B事务对该数据进行了操作
  • 幻读：类似于不可重复读，都是在一个事务周期内读的数据不一致，区别在于幻读是侧重于插入操作带来的影响，而不可重复读是编辑或者删除带来的影响

7. 关系型数据库和非关系型数据库

关系型数据库

                                                                         

定义：是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

优点：

  1. 易于维护：都是使用表结构，格式一致
  2. 使用方便：SQL语言通用，可用于复杂查询
  3. 支持复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询

缺点：

  1. 读写性能比较差，尤其是海量数据的高效率读写
  2. 固定的表结构，灵活度稍欠

非关系型数据库

                                                                          

定义：非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。非关系型数据库以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构，可以减少一些时间和空间的开销。

优点：

  1. 格式灵活：存储数据的格式可以是key, value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型
  2. 速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘
  3. 高扩展性
  4. 成本低：nosql数据库部署简单，基本都是开源软件

缺点：

  1. 不提供SQL支持，学习和使用成本较高
  2. 无事务处理
  3. 数据结构相对复杂，复杂查询方面稍欠

8. 数据库语言

  • DML数据操纵语言：用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括insert、delete、update和select等。(增删改查）
  • DCL数据控制语言：用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke、commit和rollback分别代表了授权、收回、提交和回滚。
  • DDL数据定义语言：语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括create、drop、alter等。

9. 数据库系统三级模式

  • 概念模式：用以描述整个数据库中数据库的逻辑结构，描述现实世界中的实体及其性质与联系，定义记录、数据项、数据的完整性约束条件及记录之间的联系，是数据项值的框架。
  • 外模式：用以描述用户看到或使用的那部分数据的逻辑结构，用户根据外模式用数据操作语句或应用程序去操作数据库中的数据。外模式主要描述组成用户视图的各个记录的组成、相互关系、数据项的特征、数据的安全性和完整性束条件。
  • 内模式：是整个数据库的最低层表示，不同于物理层，它假设外存是一个无限的线性地址空间。内模式定义的是存储记录的类型、存储域的表示以及存储记录的物理顺序，指引元、索引和存储路径等数据的存储组织。

内模式、模式和外模式之间的关系：

  1. 模式是数据库的中心与关键
  2. 内模式依赖于模式，独立于外模式和存储设备
  3. 外模式面向具体的应用，独立于内模式和存储设备
  4. 应用程序依赖于外模式，独立于模式和内模式

10. 数据库系统三级抽象

  • 用户级数据库：对应于外模式，是最接近用户的一级数据库，是用户可以看到和使用的数据库，又称用户视图
  • 概念级数据库：对应于概念模式，介于用户级和物理级之间，是所有用户视图的最小并集，是数据库管理员可看到和使用的数据库
  • 物理级数据库：对应于内模式，是数据库的低层表示，它描述数据的实际存储组织，是最接近于物理存储的级

11. 数据库系统两级独立性

  • 物理独立性：是指用户的应用程序与存储在磁盘上的数据库中的数据是相互独立的。
  • 逻辑独立性：是指用户的应用程序与数据库中的逻辑结构是相互独立的。

                                        第二章 常见的SQL语句

1. DML数据操纵语言

select命令

1）简单查询

  1. 单列查询：   select 列名 from 数据表名;
  2. 多列查询：   select 列名1,列名2,列名3 from 数据表名;
  3. 查询所有列： select * from 数据表名;
  4. 查询不同值： select distinct 列名 from 数据表名;
  5.             --在select列表中只能使用一次distinct关键字，而且distinct关键字必须放在第一位
  6. 随机查询：   select * from 数据表名 order by rand() limit m;
  7. 限制结果：   select 列名 from 数据表名 limit m;    --返回不多于m行
  8.             select 列名 from 数据表名 limit m,n;  --返回从行m开始的n行，注意第一行称为行0
  9.             select 列名 from 数据表名 limit y offset x;  --返回从行x开始的y行，注意第一行称为行0
  10. 限定查询:    select 数据表名.列名 from 数据库名.数据表名;
  11. 计算列查询： select 列名1,列名2,列名1*列名2 as 列名3 from 数据表名;  --(MySQL算数操作符：+-*/)
  12. 添加单位：    select 列名1,列名2,concat (列名3,'个') from 数据表名;
  13. 添加新列：    select 列名1,列名2,1+1，'汉字1'+'汉字2' as 列名3 from 数据表名;
  14. 创建别名：    select 列名 "新列名" from 数据表名;
  15.               select 列名 '新列名' from 数据表名;
  16.               select 列名  新列名  from 数据表名;
  17.               select 列名 as "新列名" from 数据表名;

2）条件查询

  1. where子句：	select 列名1,列名2,列名3 from 数据表名 where 列名='';
  2. 			--比较运算符：等于=  不等于！=，<>  小于<  小于等于<=  大于>  大于等于>= 
  3. 空值检查： 	select 列名1,列名2,列名3 from 数据表名 where 列名 is null;
  4.                 select 列名1,列名2,isnull(列名3) as 新列名 from 数据表名;
  5.                 --判断列名3中的数据是否为null，如果为null则返回1，否则返回0
  6.                 select 列名1,列名2,nullif(列名3，'abc') as 新列名 from 数据表名;
  7.                 --将列名3中值为abc的有效值转化为空值
  8. and操作符： 	select 列名1,列名2,列名3 from 数据表名 where 列名2='' and 列名3<'';
  9. or操作符：  	select 列名1,列名2,列名3 from 数据表名 where 列名2='' or 列名3<'';     
  10. in操作符：  	select 列名1,列名2,列名3 from 数据表名 where 列名3 in (a，b);  
  11.                 select 列名1,列名2,列名3 from 数据表名 where m in (列名1，列名2);        
  12. not操作符： 	select 列名1,列名2,列名3 from 数据表名 where 列名3 not in (a，b);  
  13.                 select 列名1,列名2,列名3 from 数据表名 where not 列名3='';

注意点：

  1. SQL在处理OR操作符之前，优先处理AND操作符（优先级：小括号>NOT>AND>OR）
  2. 要逻辑通顺最好用括号括起来，圆括号具有比AND和OR操作符更高的优先级
  3. in操作符用来指定条件范围，in操作符完成和or操作符相同的功能。in (0,10)表示检索0,10的字段
  4. not操作符有且只有一个功能，就是否定它之后所跟的任何条件
  5. 在指定的两个值之间 between；between A and B 等价于 >=A and <=B

AND运算符真值表：

                         

OR运算符真值表：

                         

3）范围查询

  1. 查询两个值之间的数据：  	select 列名1,列名2,列名3 from 数据表名 where 列名3 between m and n;
  2. 查询不在两个值之间的数据：select 列名1,列名2,列名3 from 数据表名 where 列名3 not between m and n;
  3. 查询两个日期之间的数据：	select 列名1,列名2,列名3 from 数据表名 where 列名3 between '2019-10-01' and '2019-10-07';
  4. 指定日期查询数据：       select 列名1,列名2,列名3 from 数据表名 where day(列名3)=20;  
  5.                        --查询日期为20号的数据
  6.                        select 列名1,列名2,列名3 from 数据表名 where month(列名3)=11;  
  7.                        --查询11月份的数据
  8.                        select 列名1,列名2,列名3 from 数据表名 where year(列名3)=2019;  
  9.                        --查询2019年的数据

4）模糊查询

  1. 百分号%通配符： 	select 列名1,列名2,列名3 from 数据表名 where 列名2 like '**%' 
  2.                 --检索以**开头的字符，%表示任意字符出现零次或任意多次
  3.                 select 列名1,列名2,列名3 from 数据表名 where 列名2 like '%**%' 
  4.                 --检索任何位置包含**的字符
  5.                 select 列名1,列名2,列名3 from 数据表名 where 列名2 like '*%*' 
  6.                 --检索以*开头，以*结尾的字符       
  7. 下划线_通配符：   select 列名1,列名2,列名3 from 数据表名 where 列名2 like '_**'  
  8.                 -- 下划线_的作用和%的作用类似，但是下划线只表示任意单个字符
  9. 定义转义字符：    select 列名1,列名2,列名3 from 数据表名 where 列名2 like '%/_%' escape '/';
  10.                 --查询列名2中含有字符_的数据
  11.                 --第一个%为通配符，/是定义的转义字符，/后面的_被解释为普通字符，最后的%为通配符
  12.                 select 列名1,列名2,列名3 from 数据表名 where 列名2 like '%10#%' escape '#';
  13.                 --查询列名2中含有字符10%的数据
  14.                 --第一个%为通配符，#是定义的转义字符，#后面的%被解释为普通字符

5）REGEXP模糊查询

  1. 基本字符匹配：  	select 列名1,列名2,列名3 from 数据表名 where 列名2 REGEXP '***' 
  2. 				--检索列名2中包含***的所有行，REGEXP后所跟的东西作为正则表达式处理
  3.                 select 列名1,列名2,列名3 from 数据表名 where 列名2 REGEXP '.**'  
  4.                 --.是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符
  5. 进行OR匹配：    	select 列名1,列名2,列名3 from 数据表名 where 列名2 REGEXP 'a|b'   
  6. 				--|是正则表达式的OR操作         
  7. 匹配字符之一： 	select 列名1,列名2,列名3 from 数据表名 where 列名2 REGEXP '[abc]'   
  8. 				--匹配a或者b或者c
  9.                 select 列名1,列名2,列名3 from 数据表名 where 列名2 REGEXP '[a|b|c]'
  10. 匹配范围：      	select 列名1,列名2,列名3 from 数据表名 where 列名2 REGEXP '[a-b]***'   
  11. 				--a-b只匹配一个字符
  12. 匹配特殊字符： 	select 列名1,列名2,列名3 from 数据表名 where 列名2 REGEXP '\\*'  
  13. 				--为了匹配特殊字符，必须用\\为前导，用于查找特殊字符*

6）数据排序

  1.  
  2. 排序数据： 		select 列名 from 数据表名 order by 列名;
  3.                         select 列名 from 数据表名 order by 'a b';
  4. 				--如果列名中由空格，就需要加上单引号或者双引号
  5. 按照多个列排序： 	    select 列名1,列名2,列名3 from 数据表名 order by 列名2,列名3;   
  6. 				--优先按照列名2排序，在列名2相同的行内部会按照列名3排序
  7. 升序排序： 		select 列名 from 数据表名 order by 列名 asc;   
  8. 				--默认升序
  9. 降序排序： 		select 列名 from 数据表名 order by 列名 desc;
  10. 				--在字典排序中，A和a被视为相同
  11. 汉字拼音排序：	        select 列名 from 数据表名 order by convert (列名 using gbk) desc;

7）数据统计分析

  1. 返回某列的平均值： 	select avg(列名) from 数据表名;
  2. 返回某列的行数：     	select count(列名) from 数据表名;     
  3.                         select count(*) from 数据表名;
  4. 					--count(*)用于处理结果集中的行，它是最准确返回表中行的总计函数
  5. 					--count(列名)则是对表达式的返回值进行处理，返回的值可描述为传递次函数的列中非零值的计数
  6. 返回某列的最大值：   	select max(列名) from 数据表名;					
  7. 返回某列的最小值：   	select min(列名) from 数据表名;
  8. 					--max和min函数忽略值为null的值
  9. 返回某列值之和：     	select sum(列名) from 数据表名;
  10. 					--sum函数忽略值为null的值
  11. 聚集不同的值：		select avg(distinct 列名) as 新列名 from 数据表名;
  12. 					--MySQL中聚合函数不可以直接嵌套使用，但是可以嵌套子查询使用

8）分组统计

  1. 分组group by语句：   select sclass,count(*) as member from student group by sclass;
  2. 过滤having语句：     select sclass,count(*) as member from student group by sclass having count(*) >2;  
  3.                     select sclass,count(*) as member from student 
  4. 							where sclass=3 and 4
  5. 							group by sclass having count(*) >2
  6. 							order by sclass;
  7. rollup关键字：      select sclass,count(*) as member from student group by sclass，sno with rollup;
  8. 					--在分组统计数据的基础上在进行统计汇总
  9. cube关键字：        select sclass,count(*) as member from student group by sclass，sno with cube;

Select子句的顺序：

                                   

9）子查询

  1. 简单子查询：    select 列名1,列名2 from 数据表名 where 列名3 in（select 列名4 from 数据表名 where 列名5='');
  2.                select 列名1,列名2（select 列名4 from 数据表名 where 列名5='') from 数据表名;
  3. exists子查询：  select 列名1,列名2 from 数据表名 where exists (select * from from 数据表名 where 列名5='')
  4.                 select 列名1,列名2 from 数据表名 where not exists (select * from from 数据表名 where 列名5='')
  5. 量词子查询：    select 列名1,列名2 from 数据表名 where 列名3 < all（select 列名4 from 数据表名 where 列名5='');
  6.                 --<all表示小于最小的；>all表示大于最大的；=all则没有返回值
  7.                 select 列名1,列名2 from 数据表名 where 列名3 < any（select 列名4 from 数据表名 where 列名5='');
  8.                 --<any表示小于最大的；>any表示大于最小的；=any等同于in
  9.                 select 列名1,列名2 from 数据表名 where 列名3 < some（select 列名4 from 数据表名 where 列名5='');
  10.                 --量词some和any是同义的

10）多表连接

  1. 等值连接：      select cno,sname,degree from score,student where score.sno=student.sno;
  2.                 select student.cno,score.sname,score.degree from score inner join student on score.sno=student.sno;
  3. 不等值连接：	select cno,sname,degree from score,student where score.sno<>student.sno;
  4. 自然连接：      SELECT a.user_id,b.address,CONVERT(VARCHAR(10),last_login,120) AS last_login FROM users a,user_address b WHERE a.user_id=b.user_id;
  5.                 --自然连接是等值连接的一种特殊形式。即按照两个表中的相同属性进行等值连接，且目标中去除重复的列，保留所有不重复的列
  6. 连接多个表：   	select sname,cname,degree from student,score,class where score.sno=student.sno and score.cno=class.cno;
  7. 左连接：    	SELECT goods_id,goods_name,name FROM goods LEFT JOIN goods_type ON goods.goods_type=goods_type.id ORDER BY goods_id DESC;
  8. 右连接：		SELECT goods_id,goods_name,name FROM goods RIGHT JOIN goods_type ON goods.goods_type=goods_type.id;
  9. 全连接：		SELECT goods_id,goods_name,name FROM goods FULL JOIN goods_type ON goods.goods_type=goods_type.id ORDER BY goods_id;
  10. 				--注意mysql不支持全连接
  11. 自然连接：	SELECT b1.id,b1.name,b1.cat_name FROM brand b1,brand b2 WHERE b1.cat_name=b2.cat_name;
  12. 				--自连接是指一个表自身进行连接
  13. 交叉连接：	SELECT a.name,a.cat_name,b.goods_name FROM brand a CROSS JOIN goods b;
  14. 				--两个表的笛卡尔乘积，会将第一个表的每一行和第二个表的每一行相匹配       
  15.  
  16. 组合查询：       select 列名1,列名2 from 数据表名1 union select 列名1,列名2 from 数据表名2;  
  17. 				--union默认取消重复的列； 使用union all可以包含所有的列，不取消重复的列
  18. 组合查询排序：  	select 列名1,列名2 from 数据表名1 union select 列名1,列名2 from 数据表名2 order by 列名1;
  19. 				--只能使用一个order by子句
  20. 				--该子局必须放在最后一个select语句之后，所使用的排列名必须是第一个select语句中的列名

11）其他语句

  1. 拼接字段：    	select concat (列名1，'(',列名2,')') from 数据表名;
  2. 使用别名：    	select concat (列名1，'(',列名2,')') as 列名3 from 数据表名;  
  3. 全文本搜索：	select 列名 from 数据表名 where match(列名) against（'***');      
  4. 				--match指定被搜索的列，against指定要使用的搜索表达式
  5.  
  6. 文本处理函数： 	select 列名1，upper(列名2) as 列名3 from 数据表名；  
  7. 常用的文本处理函数： 	upper   将文本转换为大写     lower   将文本转换为小写                                 
  8.                    	left    返回串左边的字符     right   返回串右边的字符
  9.                  	length  返回串对的长度      
  10.                    	locate  找出串的一个子串
  11.                		ltrim   去掉串左边的空格     rtrim   去掉串右边的空格
  12.                   	soundex 返回串的soundex值  
  13.                   	substring 返回串的子串
  14. 常用数值处理函数：  	abs   返回一个数的绝对值    cos   返回一个角度的余弦
  15.                  	exp   返回一个数的指数值    mod   返回除操作的余数
  16.                         pi    返回圆周率           rand  返回一个随机数
  17.                         sin   返回一个角度的正弦    sqrt  返回一个数的平方根
  18.                         tan   返回一个角度的正切

insert命令

  1. 插入完整的行：      	insert into 数据表名 values('***','***','***');
  2.                    	insert into 数据表名(列名1,列名2,列名3) values('***','***','***');
  3. 插入多个行：         	insert into 数据表名(列名1,列名2,列名3) values('***','***','***')，('+++','+++','+++');
  4. 插入检索出的数据：   	insert into 数据表名(列名1,列名2,列名3) select 列名1,列名2,列名3 from 数据表名2;
  5. 复制表中的数据：		create table 数据表名 as select * from 数据表名2 where 列名1=***;

update命令

  1. 更新表中单个列：     	update 数据表名 set 列名1='***' where 列名2='+++';
  2. 更新表中多个列：     	update 数据表名 set 列名1='***',列名2='+++' where 列名3='---';
  3. 更新表中所有列：     	update 数据表名 set 列名1='***';
  4. 依据外表值更新数据：	update 数据表名 set 列名1=列名1+100 where 列名2='+++';

delete命令

  1. 删除某个列的值：     	update 数据表名 set 列名1='NULL' where 列名2='+++';
  2. 删除单个行的值：     	delete from 数据表名 where 列名1='***';
  3. 删除多个行的值：		delete from 数据表名 where 列名1 in ('***','+++','---');
  4. 删除所有行：             delete from 数据表名;
  5.                         truncate table 数据表名;

2. DDL数据定义语言

1）创建和操纵数据库/数据表

  1. 默认值创建数据库：     create database 数据库密码;
  2. 自定义选项创建数据库：  create database 数据库名 on (   );
  3. 向数据库中添加文件：    alter database 数据库名 add file (  );
  4. 向数据库中添加文件组：  alter database 数据库名 addfilegroup 文件组名;
  5. 删除文件或文件组：     alter database 数据库名 remove file 文件名;
  6. 修改数据库文件：      alter database 数据库名 modify file (  );
  7. 删除数据库：	     drop database 数据库名;
  8.  
  9. 创建表：          	create table 数据表名 (列名1 int,列名2 varchar(20),列名3 char(20));
  10. 使用NULL：          	create table 数据表名 (列名1 int not null,列名2 varchar(20),列名3 char(20));
  11. 指定主键：              create table 数据表名 (列名1 int primary key,列名2 varchar(20),列名3 char(20));
  12.                         create table 数据表名 (列名1 int,列名2 varchar(20),列名3 char(20),primary key (列名1));
  13. 自动增量：          	create table 数据表名 (列名1 int primary key auto_increment,列名2 varchar(20),列名3 char(20));
  14. 指定默认值：         	create table 数据表名 (列名1 int default 1,列名2 varchar(20),列名3 char(20));
  15. 更新表：            	alter table 数据表名  add 列名 varchar(20);
  16. 增加主键：			    alter table 数据表名  add primary key (列名);
  17. 删除添加的列：       	alter table 数据表名  drop column 列名;
  18. 删除表:             	drop table 数据表名;
  19. 重命名数据表：          rename table 原数据表名 to 现数据表名;

2）创建视图、存储过程、游标、触发器

  1. 创建视图：          	create view 视图名 as select cno,sname,degree from score,student where score.sno=student.sno;   
  2. 使用视图过滤数据：   	create view 视图名 as select * from student where sbirth is not null;
  3.  
  4.  
  5. 创建存储过程：       	create procedure 存储体名() begin select * from 数据表名; end;
  6. 删除存储体:              drop procedure 存储体名;
  7.  
  8. 创建游标：          	create procedure 存储体名() begin declare 游标名 for select 列名 from 数据表名; edn;
  9. 打开游标：          	open 游标名;
  10. 关闭游标：          	close 游标名;
  11.  
  12. 创建触发器：        	create trigger 触发器名 + insert / delete / update...;
  13. 删除触发器：        	drop trigger 触发器名;

3. DCL数据控制语言

  1. roolback语句：     	start transaction; ******; roolback;   (使用roolback语句可以回退 start transaction之后的所有语句)
  2. commit语句：       	start transaction; ******; commit;      (commit语句进行明确的提交)
  3. 使用保留点：        	savepoint delete1;  roolback to delete1;
  4. 管理用户：               use mysql; select user from user;
  5. 创建用户账户：       	create user 用户名 identified by '***';
  6. 重命名用户账号：     	rename user 原用户名 to 现用户名;
  7. 删除用户账号：       	drop user 用户名;
  8. 查看用户账号的权限： 	show grants for 用户名;
  9. 赋予用户账号的权限： 	grant 用户权限 on 数据库名.数据表名 to 用户名;
  10. 取消用户账号的权限： 	revoke 用户权限 on 数据库名.数据表名 from 用户名;
  11. 更改口令：          	set password for 用户名 = pasword ('***');

4. 其他SQL语句

  1. 显示所有的数据库： 	    show databases;
  2. 选择一个数据库： 		use 数据库名;
  3. 显示一个数据库内的所有表： 	show tables;
  4. 显示一个表的所有信息： 		show columns from 数据表名;  /  describe 数据表名;  /  desc 数据表名;

5. 注释方法

  1. 行内注释：-- 或者 #
  2. 任何内容注释：/* */

6. MySQL中格式化日期（date_format函数）

SELECT user_id,email,DATE_FORMAT(reg_time,'%Y/%m/%d') AS reg_time FROM shop.users LIMIT 6;

                           

                           

                          

                             第三章 数据库表连接

1. 内连接

                                               

                                                内连接：取两表的交集，只返回两张表匹配的记录 

select * from a inner join b on a.book_id=b.book_id

2. 外连接

1)左连接

                                                         

                         左连接：以左表为准，查询出左表的所有数据，右表中有对应的则显示出来，没有对应的则显示为null 

select * from a left join b on a.book_id=b.book_id

 2)右连接

                                                        

                        右连接：以右表为准，查询出右表的所有数据，左表中有对应的则显示出来，没有对应的则显示为null

select * from a right join b on a.book_id=b.book_id

 3)全连接

                                                           

                                     全连接：是left和right的集合，某表中某一行在另一表中无匹配行，则相应列的内容为null

select * from a full join b on a.book_id=b.book_id

3. 交叉连接

交叉连接：不带where条件子句，它将返回被连接的两个表的笛卡尔积，返回的行等于两个表行数的乘积

                                   第四章 数据库面试常见问题

1. 列举几种表连接方式,有什么区别？

内连接、自然连接、外连接（左、右、全）、交叉连接

  • 内连接：只有两个元素表相匹配的才能在结果集中显示。
  • 自然连接：自然连接是等值连接的一种特殊形式。即按照两个表中的相同属性进行等值连接，且目标中去除重复的列，保留所有不重复的列
  • 左外连接：左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
  • 右外连接：右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
  • 全外连接：连接的表中不匹配的数据全部会显示出来。
  • 交叉连接：笛卡尔效应，显示的结果是链接表数的乘积。

 2. 主键和外键的区别？

  • 主键是表格里的(一个或多个)字段，只用来定义表格里的行；主键里的值总是唯一的。
  • 外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。
  • 主键在本表中是唯一的、不可为空的，外键可以重复可以唯空；外键和另一张表的主键关联，不能创建对应表中不存在的外键。

 3. 数据库三大范式

简单的说，范式是为了消除重复数据减少冗余数据，从而让数据库内的数据更好的组织，让磁盘空间得到更有效利用的一种标准，满足高等级的范式的先决条件是满足低等级范式。

  • 第一范式：对于一张二维表，最基本的要求就是数据库表的每一列都是不可分割的原子数据项。也就是说同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。满足了这个条件的关系表就属于第一范式。

举例说明：

在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

  •  第二范式：满足1NF后，需要确保数据库表中的每一列都和主键相关，而不能有任何一列与主键没有关系。2NF是在1NF的基础上消除了非主属性对键的部分函数依赖。

举例说明：

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，这样就不满足第二范式的要求，调整如下，需分成两个表：

  •  第三范式：必须先满足第二范式（2NF），要求表中的每一列只与主键直接相关而不是间接相关

举例说明：

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，而不是主键“学号”，所以需做如下调整：

  1. 主键和外键的区别？
  1. 主键是表格里的(一个或多个)字段，只用来定义表格里的行；主键里的值总是唯一的。
  2. 外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。
  3. 主键在本表中是唯一的、不可为空的，外键可以重复可以唯空；外键和另一张表的主键关联，不能创建对应表中不存在的外键。
  1. 数据库三大范式

简单的说，范式是为了消除重复数据减少冗余数据，从而让数据库内的数据更好的组织，让磁盘空间得到更有效利用的一种标准，满足高等级的范式的先决条件是满足低等级范式。

  1. 第一范式：对于一张二维表，最基本的要求就是数据库表的每一列都是不可分割的原子数据项。也就是说同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。满足了这个条件的关系表就属于第一范式。

举例说明：

在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

  1. 第二范式：满足1NF后，需要确保数据库表中的每一列都和主键相关，而不能有任何一列与主键没有关系。2NF是在1NF的基础上消除了非主属性对键的部分函数依赖。

举例说明：

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，这样就不满足第二范式的要求，调整如下，需分成两个表：

  1. 第三范式：必须先满足第二范式（2NF），要求表中的每一列只与主键直接相关而不是间接相关

举例说明：

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，而不是主键“学号”，所以需做如下调整：

 4. 详解数据库三种删除方法：delete, drop, truncate

  • Delete：删除数据表中的行（可以删除某一行，也可以在不删除数据表的情况下删除所有行）。

删除某一行：Delete from 数据表名称 where 列名称=值；

删除所有行：Delete * from 数据表名称

  • Drop ：删除数据表或数据库，或删除数据表字段。

删除数据库：drop database 数据库名称

删除数据表：（表的结构、属性、索引也会被删除）

                               drop table 数据表1名称，数据表2名称

删除数据表字段（列）：

                               alter table 数据表名称

                               drop column 字段名（列名称）

  • Truncate：删除数据表中的数据（仅数据表中的数据，不删除表）。

truncate table 数据表名称

“ TRUNCATE TABLE语句是一种快速、无日志记录的方法。TRUNCATE TABLE语句与不含有WHERE子句的DELETE语句在功能上相同。但是，TRUNCATE TABLE语句速度更快，并且使用更少的系统资源和事务日志资源。” 

  • 删除数据的速度，一般来说: drop> truncate > delete

 5. where子句和having子句的区别

  • where不能放在group by后面，而having子句可以
  • having是与group by连在一起使用的，放在group by后面，此时的作用相当于where子句
  • where后面的条件中不能有聚合函数，而having子句可以
  • where子句在分组前进行过滤，having子句在分组后进行过滤

 6. 子查询与其他select语句之间的区别

          

 7. MySQL中ROLLUP和CUBE的区别

  • with rollup通常和group by 语句一起使用，是根据维度在分组的结果集中进行聚合操作，对group by的分组进行汇总。
  • rollup是cube的一种特殊情况，和rollup一样，cube也是一种对数据的聚合操作。但是rollup只在层次上对数据进行聚合，而cube对所有的维度进行聚合。具有N个维度的列，cube需要2的N次方次分组操作，而rollup只需要N次分组操作。
  • mysql聚合函数rollup和cube_利剑 -~的博客-CSDN博客

 8. MySQL中exists和in的区别及使用场景

  • 对B查询涉及id，使用索引，故B表效率高，可用大表 -->外小内大。select * from A where exists (select * from B where A.id=B.id);对A查询涉及id，使用索引，故A表效率高，可用大表 -->外大内小。select * from A where A.id in (select id from B);
  • exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；
  • in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。
  • 如果用not in，则是内外表都全表扫描，无索引，效率低，可考虑使用not exists，也可使用A left join B on A.id=B.id where B.id is null进行优化。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/709667
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号