面试之mysql专题

DB：数据库(database):存储数据的“仓库”。它保存了一系列有组织的数据。

DBMS：数据库管理系统(Database Management System)。数据库是通过DBMS创建和操作的容器

       1. 基于共享文件系统的DBMS (Access ) 

       2.基于客户机——服务器的DBMS (MySQL、Oracle、SqlServer)

SQL：结构化查询语言(Structure Query Language):专门用来与数据库通信的语言。

 

SQL:

1、DML(Data Manipulation Language):数据操纵语句，

              用于添加、删除、修改、查询数据库记录，并检查数据完整性

           INSERT:添加数据到数据库中 

              UPDATE:修改数据库中的数据 

              DELETE:删除数据库中的数据

           SELECT:选择(查询)数据

 SELECT是SQL语言的基础，最为重要。

 

2、DDL(Data Definition Language):数据定义语句，用于库和表的创建、修改、删除。

　　　　CREATE TABLE:创建数据库表

　　　　ALTER TABLE:更改表结构、添加、删除、修改列长度 

　　　　　DROP TABLE:删除表

 

　　　　CREATE INDEX:在表上建立索引

　　　　DROP INDEX:删除索引

3、DCL(Data Control Language):数据控制语句，用于定义用户的访问权限和安全级别。

 

　　　　GRANT:授予访问权限 

   　　　　REVOKE:撤销访问权限 

   　　　　COMMIT:提交事务处理 

   　　　　 ROLLBACK:事务处理回退 

　　　　   SAVEPOINT:设置保存点

 　　　　LOCK:对数据库的特定部分进行锁定

 

Mysql:

              启动:net start mysql服务名        停止:net stop mysql服务名

               MySQL服务端的登录和退出

               mysql –h 主机名 –u用户名 –p密码

            exit

语法规范：

          不区分大小写

          每句话用;或\g结尾

           各子句一般分行写 

              关键字不能缩写也不能分行

            用缩进提高语句的可读性

 类型一样 隐式转换

常用命令：

 

  

查看表的结构   desc tb_emp;

                    show tables；

　　　　　　删除表: droptable表名

 

               insert into 表名(列名列表) values(列对应的值的列表);

               注意:插入varchar或date型的数据要用单引号引起

               修改记录: update 表名 set 列1 = 列1的值, 列2 = 列2的值 where ...

                删除记录: delete from 表名 where ....

                去重：DISTINCT

数据处理之查询：

 

• 基本的SELECT语句

 

SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;

 

SELECT 标识选择哪些列。

FROM 标识从哪个表中选择。

 

列的别名:

• 重命名一个列。

• 便于计算   列的别名：

• 紧跟列名

• 过滤和排序数据

使用WHERE 子句，将不满足条件的行过滤掉

 

比较运算：

 

• 分组函数

 

 

 

 

 

 

 

 

使用 LIKE 运算选择类似的值

 选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。  _ 代表一个字符。

‘%’和‘-’可以同时使用

 

 

使用 IS (NOT) NULL 判断空值。

 

 

 

 

 

 

 

 

 

ORDER BY子句

使用 ORDER BY 子句排序

 ASC(ascend): 升序

DESC(descend): 降序

• ORDERBY子句在SELECT语句的结尾。

 

 

 

 

 

 分组查询

组函数类型

 AVG()      COUNT()      MAX()      MIN()       SUM()

 

 

 

可以对数值型数据使用AVG 和 SUM 函数。

 

 

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

 

 

 

 

COUNT(*) 返回表中记录总数,适用于任意数据类型。

 

 

 

分组数据: GROUP BY 子句语法

可以使用GROUP BY子句将表中的数据分成若干组

 

WHERE一定放在FROM后面

在SELECT 列表中所有未包含在组函数中的列都应该包含 在 GROUP BY 子句中。

 

 

在GROUP BY子句中包含多个列

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

 

在GROUP BY子句中包含多个列

 

 

非法使用组函数：

不能在 WHERE 子句中使用组函数。

可以在 HAVING 子句中使用组函数。

 

过滤分组：  过滤分组

过滤分组: HAVING 子句

使用 HAVING 过滤分组:

1. 行已经被分组。

2. 使用了组函数。

3. 满足HAVING 子句中条件的分组将被显示。

 

 

 

 

 

 

多表查询

笛卡尔集

 

笛卡尔集会在下面条件下产生:

– 省略连接条件

– 连接条件无效

– 所有表中的所有行互相连接

 为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。

 

Mysql 连接

使用连接在多个表中查询数据。

在 WHERE 子句中写入连接条件。

在表中有相同列时，在列名之前加上表名前缀

 

等值连接

 

区分重复的列名

使用表名前缀在多个表中区分相同的列。

在不同表中具有相同列名的列可以用表的别名加以区分。

如果使用了表别名，则在select语句中需要使用表别名代替表名

表别名最多支持32个字符长度，但建议越少越好

 

表的别名

使用别名可以简化查询。

使用表名前缀可以提高执行效率。

 

 

连接多个表

连接 n个表,至少需要 n-1个连接条件。 例如:连接 三个表，至少需要两个连接条件。

 

使用ON 子句创建连接

自然连接中是以具有相同名字的列为连接条件的。

可以使用 ON 子句指定额外的连接条件。

这个连接条件是与其它条件分开的。

ON 子句使语句具有更高的易读性。

 

Join连接

内连接 [inner] join on

自连接 根据自己表查到的数据再通过查到饿数据在自己表里再找

 

外连接：查询一个表有。一个表没有的场景

 

外连接

 

• 左外连接 left [outer] join on

 

• 右外连接 right [outer] join on

 

 

    加 Where条件：取一半 

 

不支持

 

条件表达式

• 在 SQL 语句中使用IF-THEN-ELSE 逻辑

• 使用方法:

– CASE 表达式

 

 

 

 

 

 

 

 

 

子查询

概念:出现在其他语句内部的select语句，称为子 查询或内查询

内部嵌套其他select语句的查询，称为外查询或主 查询

select first_name from employees where

department_id in(

select department_id from departments where location_id=1700

)

 

 

• 子查询要包含在括号内。

• 将子查询放在比较条件的右侧。

• 单行操作符对应单行子查询，多行操作符对应 多行子查询。

 

非法使用子查询：

 

 

 

 子查询中的空值问题：

 

 

 

多行子查询

返回多行。

使用多行比较操作符。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

创建和管理表

 

创建数据库

创建表

描述各种数据类型

修改表的定义

删除，重命名和清空表

 

 

 

 

 

 

 

 

 

使用子查询创建表

 

 

 

 

 

 

ALTER TABLE dept80

ADD job_id varchar(15);

 

ALTER TABLE dept80

MODIFY (last_name VARCHAR(30));

 

ALTER TABLE dept80

DROP COLUMN job_id;

 

重命名一个列

ALTER TABLE dept80

CHANGE department_name dept_name varchar(15);

 

删除表

数据和结构都被删除

所有正在运行的相关事务被提交

所有相关索引被删除

 DROP TABLE 语句不能回滚

 

清空表

TRUNCATE TABLE

语句:  删除表中所有的数据

         释放表的存储空间

TRUNCATE语句不能回滚

可以使用 DELETE 语句删除数据,可以回滚

 

执行RENAME语句改变表, 视图的名称

ALTER table dept RENAME TO detail_dept;

必须是对象的拥有者

 

约束和分页

 

描述约束

创建和维护约束

数据库分页

 

为了保证数据的一致性和完整性，SQL规范以约 束的方式对表数据进行额外的条件限制。

约束是表级的强制规定

可以在创建表时规定约束(通过 CREATE TABLE 语句)，或者在表创建之后也可以(通 过 ALTER TABLE 语句)

 

约束

 有以下六种约束:

– NOT NULL 非空约束，规定某个字段不能为空

– UNIQUE 唯一约束，规定某个字段在整个表中是唯一的 – PRIMARY KEY 主键(非空且唯一)

– FOREIGN KEY 外键

– CHECK 检查约束

– DEFAULT 默认值

注意: MySQL不支持check约束，但可以使用check约束，而没有任何效果; 具体细节可以参阅W3Cschool手册

 

根据约束数据列的限制，约束可分为: – 单列约束:每个约束只约束一列

– 多列约束:每个约束可约束多列数据

 根据约束的作用范围，约束可分为:

– 列级约束只能作用在一个列上，跟在列的定义后面

– 表级约束可以作用在多个列上，不与列一起，而是 单独定义

 

 

MySQL中使用limit实现分页

SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

limit子句必须放在整个查询语句的最后!

 

事务

事务的概念和特性

事务的隔离级别

 

事务由单独单元的一个或多个SQL语句组成，

在这 个单元中，每个MySQL语句是相互依赖的。

而整个单独单 元作为一个不可分割的整体，

如果单元中某条SQL语句一 旦执行失败或产生错误，

整个单元将会回滚。

所有受到影 响的数据将返回到事物开始以前的状态;如果单元中的所

有SQL语句均执行成功，则事物被顺利执行。

 

show engines;

在mysql中用的最多的存储引擎有:innodb， myisam ,memory 等。

其中innodb支持事务，而 myisam、memory等不支持事务

 

事务的ACID(acid)属性 

1. 原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位，事务中的操作要么 都发生，要么都不发生。

2. 一致性(Consistency) 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3. 隔离性(Isolation) 事务的隔离性是指一个事务的执行不能被其他事务干扰，

即一个 事务内部的操作及使用的数据对并发的其他事务是隔离的，并发 执行的各个事务之间不能互相干扰。

4. 持久性(Durability) 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，

接下来的其他操作和数据库故障不应该对其有任何影响

 

    1.以第一个 DML 语句的执行作为开始

 2.以下面的其中之一作为结束:  COMMIT 或 ROLLBACK 语句

 DDL 或 DCL 语句(自动提交)  用户会话正常结束

 系统异常终了

 

     对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时,

     如果没有采取必要的隔离机制, 就会导致各种并发问题:

 

 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新

              但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.

 

 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.

 

 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插 入了一些新的行. 之后,

           如果 T1 再次读取同一个表, 就会多出几行.

 

 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.



     一个事务与其他事务隔离的程度称为隔离级别.

 

 

    数据库规定了多种事务隔 离级别, 不同隔离级别对应不同的干扰程度,

     隔离级别越高, 数据一致性就 越好, 但并发性越弱.

 

 

    看当前的隔离级别:

      SELECT @@tx_isolation;

 设置当前 mySQL 连接的隔离级别:

 set transaction isolation level read committed;

 

 设置数据库系统的全局的隔离级别:

 set global transaction isolation level read committed;

 

视图

 

MySQL从5.0.1版本开始提供视图功能。

一种虚拟 存在的表，行和列的数据来自定义视图的查询中使用的表 ，

并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

 

 

应用场景:

– 多个地方用到同样的查询结果

– 该查询结果使用的sql语句较复杂

 

CREATE VIEW my_v1

AS

SELECT studentname,majorname FROM student s

INNER JOIN major m ON s.majorid=m.majorid WHERE s.majorid=1;

 

视图的好处:

 

重用sql语句

简化复杂的sql操作，不必知道它的查询细节

保护数据，提高安全性

 

 

 

 

存储过程和函数

什么是存储过程和函数

使用存储过程和函数的好处

创建存储过程和函数

修改存储过程和函数

调用存储过程和函数

查看存储过程和函数

 

存储过程和函数:

事先经过编译并存储在数据库中的一段sql语句的

集合。

使用好处:

1、简化应用开发人员的很多工作

2、减少数据在数据库和应用服务器之间的传输 减少了编译次数

3、提高了数据处理的效率

 

 

 

 

 

 

 

流程控制结构