MySQL面试

于 2023-05-11 16:47:22 发布
阅读量135 收藏
点赞数
文章标签： 面试 mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_53323742/article/details/125474551
版权

1.MySQL自连接及联表查询联系

1.1自连接

自连接特点：1.把一张表当做两张表使用   

                       2.需要给表取别名  表名  表别名 

                      3.列名不明确，要指定列的别名， 列名as列的别名

1.2 联表查询联系

笛卡尔积简单点理解：有两个集合A和B，笛卡尔积表示A集合中的元素和B集合中的元素任意相互关联产生的所有可能的结果。

假如A中有m个元素，B中有n个元素，A、B笛卡尔积产生的结果有m*n个结果，相当于循环遍历两个集合中的元素，任意组合。

一、内连接

语法：

select 字段 from 表1 inner join 表2 on 连接条件;
或
select 字段 from 表1 join 表2 on 连接条件;
或
select 字段 from 表1, 表2 [where 关联条件];

有连接条件（表1的id=表2的id）、无连接条件 （无联系）、组合条件进行查询（多个条件相等）

方式1：on中使用了组合条件。(select 字段 from 表1 inner join 表2 on 连接条件 and 条件)
方式2：在连接的结果之后再进行过滤，相当于先获取连接的结果，然后使用where中的条件再对连接结果进行过滤。(select 字段 from 表1 inner join 表2 on 连接条件 where 关联条件)
方式3：直接在where后面进行过滤。(select 字段 from 表1,表2 where 关联条件 and 条件)

二、外连接
外连接涉及到2个表，分为：主表和从表，要查询的信息主要来自于哪个表，谁就是主表。

外连接查询结果为主表中所有记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。

最终：外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录。

外连接分为2种：

左外链接：使用left join关键字，left join左边的是主表。
右外连接：使用right join关键字，right join右边的是主表。
2.分页和排序

2.1排序

  使用 ORDER BY 子句排序        ASC（ ascend : 升序）  DESC（ descend : 降序 ）
  ORDER BY 子句在 SELECT 语句的结尾

 2.2分页

select * from book_p where pid = 1 limit 0,2;

limit a,b;    # a表示从第几条数据开始获取，b表示获取几条数据

分页公式

limit（n - 1）* pageSize, pageSize ;

pageSize 页面大小

( n - 1) * pageSize 起始值

n 当前页

总页数 = 数据总数 / 页面大小

3. 子查询和嵌套查询

3.1子查询（select中套select）

某些情况下，当进行一个查询时，需要的条件或数据要用另外一个 select 语句的结果，这个时候，就要用到子查询。（select中套select）

一般根据子查询的嵌入位置分为，where型子查询,from型子查询，exists型子查询。

3.2嵌套查询（where套where）

SELECT cust_name, cust_contact FROM Customers
WHERE cust_id IN (SELECT cust_id FROM Order WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = ‘RGAN01’));
where中在where

4.MySQL常用函数

1.字符串连接CONCAT

SELECT CONCAT('my','sql')

2.字符串替换函数insert

SELECT INSERT('hi,mysql',4,5,'sql server')

3.字符串转小写lower

SELECT LOWER('MySql')

4.字符串转大写upper

SELECT UPPER('layui')

5.字符串截取函数substring

SELECT SUBSTRING('vue developer',1,3)

5.聚合函数及分组过滤 

5.1聚合函数(count{不统计null}、sum\ max /min/avg)

5.2分组过滤

（having 在 group by 的后面.WHERE 不能和 GROUP BY 连用，使用 HAVING 代替）

6.拓展直数据库级别的MD5加密

  • 一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。
  • 主要增强算法复杂度和不可逆性；
  • MD5 不可逆，具体的值的MD5是一样的；
  • MD5破解网站的原理：背后有一个字典 —— MD5加密后的值，加密前的值

7.Select小结(ddl定义创建、dml操作记录、dcl控制权限)

SQL语言在功能上主要分为如下3大类：

①DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
    主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
②DML（Data Manipulation Language、数据操作语言），用于 添加、 删除、 更新和 查询数据库记录，并检查数据完整性。
    主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等
③ DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
    主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

1.3 SQL的使用规则
①SQL大小写规范
数据库名 、表名 、表别名 、字段名 、字段别名 等都小写
SQL 关键字 、函数名 、绑定变量 等都大写

8.事务A原子C一致I独立D持久原则

事务的ACID原则
原子性（atomicity）：事务中包括的操作要么都做，要么都不做，

一致性（consistency）：针对一个事务操作前与操作后的状态一致。一致性和原子性密切相关（因为一个事务不管做或者没做，数据库都处于一致状态，但如果事务中一个操作做了，但另一个操作没做，则逻辑上就会发生错误，这是数据库就不处于一致状态了）

隔离性（isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。针对对个用户同时操作，主要是排除其他事物对本次事务的影响

隔离所导致的问题：

脏读：指一个事务读取了另外一个事务未提交的数据。
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同（数据内容不一致）。
持久性（durability）：指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，不可逆的。接下来的其他操作或故障不应该对其有任何影响。

9. 测试事务实现转账（autocommit=0关闭        1开启）

-- 转账
CREATE DATABASE shop CHARSET utf8 COLLATE utf8_general_ci -- 创建数据库
USE shop-- 使用数据库

-- 创建表
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET utf8

-- 插入数据
INSERT INTO `account`(`name`,`money`) VALUES("A",2000.00),("B",10000.00)

-- 模拟转账:事务
SET autocommit=0 -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE `account` SET `money`=`money`-500 WHERE `name`="A" -- A 减500
UPDATE `account` SET `money`=`money`+500 WHERE `name`="B" -- B 加500

COMMIT -- 提交事务
SET autocommit=1

10. 索引介绍及索引的分类

mysql索引的四种类型：主键索引、唯一索引、普通索引和全文索引。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

主键索引:

主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。alert table tablename add primary key(`字段名`)

唯一索引:

索引列的所有值都只能出现一次，即必须唯一，值可以为空。alter table table_name add unique index(`字段名`);

#alter table table_name drop index `字段名` , add unique index(`字段名`);#删除旧索引再添加

普通索引 :

基本的索引类型，值可以为空，没有唯一性的限制。alter table table_name add index(`字段名`);

全文索引:

全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。MyISAM支持全文索引

11. SQL编程创建1000万条数据测试

.创建索引测试表

1

2

3

4

5

6

7

DROP TABLE IF EXISTS big_data;

CREATE TABLE big_data(

id int PRIMARY KEY NOT NULL AUTO_INCREMENT,

name VARCHAR(16) DEFAULT NULL,

age int(11),

email varchar(64) default null

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

 注意:MYISAM存储引擎不产生引擎事务，数据插入速度极快，为方便快速插入千万条测试数据，等我们插完数据，再把存储类型修改为InnoDB

　　 2.创建存储过程，插入数据

1

2

3

4

5

6

7

8

CREATE PROCEDURE `insert_data_p`(IN num INT)

BEGIN

DECLARE n INT DEFAULT 1;

WHILE n <= num DO

INSERT INTO big_data(name,age,email)values(concat('alex',n),rand()*50,concat('alex',n,'@qq.com'));

set n=n+1;

end while;

ENd;　

　　 3.调用存储过程,插入1000万条数据(总耗时:382.614 sec)

1

CALL insert_data_p(10000000);

12.数据库用户管理 

12.1创建用户

a. 使用明文创建用户

CREATE USER 'xixi'@'localhost' IDENTIFIED BY '123456';

b.使用密文创建用户

select password('123456');
create user 'yong'@'localhost' identified by password 'XXXXXXXX';
 
 
查看用户信息
创建后的用户保存在mysql 数据库的user表里
use mysql
select user,authentication_string,host from user;

12.2查看用户

创建后的用户保存在 mysql 数据库的 user 表里

use mysql;
select User,authentication_string,Host from user;

12.3重命名用户

rename user 'liy'@'localhost' to 'yyli'@'localhost'; #前为原名后为修改
 
select user,authentication_string,host from user;

 12.4 删除用户

drop user 'yyli'@'localhost';

12.5修改当前登录用户密码

set password = password('123456');`在这里插入代码片`

12.6修改其他用户密码

set password for 'yong'@'localhost' = password('654321');

12.7忘记 root 密码的解决办法

遗忘mysql密码，如下图所示：

修改配置文件，添加配置，使登录mysql不使用授权表

vim /etc/my.cnf
#添加此行
skip-grant-tables
 
systemctl restart mysqld.service 
mysql

使用update修改root密码（不可无密码），刷新数据库

update mysql.user set authentication_string = password('001122') where user='root';
 
 
flush privileges;

13.MySQL备份 

备份全部数据库的数据和结构

1

mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql

备份全部数据库的结构（加 -d 参数）

1

mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql

备份全部数据库的数据(加 -t 参数)

1

mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql

备份单个数据库的数据和结构(,数据库名mydb)

1

mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql

备份单个数据库的结构

1

mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql

 备份单个数据库的数据

1

mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql

 备份多个表的数据和结构（数据，结构的单独备份方法与上同）

1

mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql

一次备份多个数据库

1

mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

第一种是在 MySQL 命令行中在系统命令行中，输入如下实现还原：

1

mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

 14.如何设计一个项目的数据库（数据库的三大范式）

三大范式
第一范式 数据库表 的每一列都是不可再分的
第二范式 满足第一范式的前提 数据表中的每一列都要和主键相关，不能和主键的一部分相关（每张表只描述一件事情）
第三范式满足第二范式的前提 每一列都要和主键直接相关不能间接相关

规范数据库的设置
规范性和性能的问题
关联查询的表不得超过三张

考虑商业化的需求和目标（成本，用户体验） 数据库的性能更加重要
在规范性能的问题的时候，需要适当考虑一下规范性
故意给某些表加入一些冗余的字段（从多表查询变成单表查询）
故意增加一些列（从大数据降低为小数据量的查询：索引）

 15.数据库驱动和JDBC

16.第一个JDBC程序

代码模板
sql


CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60),
 birthday DATE
);

 INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

SELECT VERSION(); -- 查询数据库版本

java

package com.ama.jdbc;

import java.sql.*;

//我的第一个JDBC程序
public class JDBCfirst {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String  passward = "123456";
        //3.连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url,username,passward);

        //4.执行SQL的对象
        Statement statement = connection.createStatement();

        //5.执行SQL的对象 去 执行sql语句，可能存在结果，查看返回的结果
        String sql = "select * from user";
        ResultSet resultSet = statement.executeQuery(sql);

        while (resultSet.next()){
            System.out.println("id" + resultSet.getObject("id"));
            System.out.println("name" + resultSet.getObject("name"));
            System.out.println("pwd" + resultSet.getObject("passward"));
            System.out.println("email" + resultSet.getObject("email"));
            System.out.println("birth" + resultSet.getObject("birth"));

        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

17.数据库的概念与重要性,MySQL安装与使用、基本的命令行操作、操作数据库语句

  • 数据库： 表的集合，是存储数据的仓库
    以一定的组织方式存储的相互有关的数据集合

雪山麦芽
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值