mysql(6000字详细攻略）

最新推荐文章于 2024-09-15 19:42:28 发布
最新推荐文章于 2024-09-15 19:42:28 发布
阅读量1.2k 收藏 29
点赞数 36
文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_65993561/article/details/141280167
版权

一、mysql的基本操作

SQL分类

1.DDL数据定义语言 定义数据库对象：数据库，表，列等

2.DML数据操作语言 用来对数据库中表的记录进行更新

3.DQL数据库查询语言 用来查询数据库中表的

4.DCL数据库控制语言 定义数据库的访问权限和安全级别 创建用户

(一)DDL

操作数据库

-- 创建数据库并设置字符集 
create database db1 character set utf8; 
-- 查看正在使用的数据库 
select database(); 
-- 切换数据库 
use db1; 
-- 查看所有的数据库 
show databases ; 
-- 查看指定数据库的定义信息 
show create database db1;
 -- 修改指定数据库的字符集 
alter database db1 character set utf8;
 -- 删除指定数据库 
drop database db1;

操作数据表

-- 创建表 
create table t1( id int , name varchar(20), age int ); 
-- 复制表结构 
create table t1_ like t1;
 -- 查看数据库所有表 
show tables ; 
-- 查看表结构 
desc t1;
 -- 删除表 
drop table t1; 
-- 存在删除 不存在不执行
 drop table if exists t1; 
-- 修改表名 
rename table t1 to t1_; 
-- 修改表的字符集
 alter table t1 character set utf8; 
-- 表中添加字段（列）
 alter table t1 add f int; 
-- 修改表中列的数据类型或长度 
alter table t1 modify f int;
 -- 修改列名称 
alter table t1 change f f_ int; 
-- 删除列 
alter table t1 drop f;

(二)DML

-- 插入全部字段数据 
insert into t1 (id,name,age) values (1,'z',18); 
insert into t1 values (1,'z',18); 
-- 插入指定字段
 insert into t1(name)values ('x'); 
-- 修改 
update t1 set name = 'a'where id=1; 
-- 全部删除，一条一条删除 
delete from t1; 
-- 删除整个表，在创建一张相同的表
 truncate table t1; 
-- 指定删除
 delete from t1 where id =1;

(三)DQL

-- 查询表中所 有数据
 select * from t1;
 -- 查询指定字段数据 
select id from t1; 
-- 别名查询 AS（可省略）
 select id as '学号' from t1; 
-- 去重 select distinct * from t1; 
-- 运算符 
-- between 1 and 10 显示1-10区间内的值 
-- in（集合） 表示多个值用逗号分隔 
-- like '%z%' 模糊查询 %表示多个字符 _表示一个字符
 -- is null 查询某一列为null的值 
-- and && 多个条件同时成立 
-- or || 多个条件任一成立 -- not 不成立取反

(四)DQL单表操作

-- 排序 ASC升序 DESC降序 
select 字段名 from [where 字段=值] order by 字段名[ASC/DESC] 
select 字段名 from [where 字段=值] order by 字段名1[ASC/DESC],字段名2[ASC/DESC]; 
-- 聚合函数 
select 聚合函数（字段名）from 表名； 
count(字段) 统计指定列不为null的记录行数
 sum(字段) 计算指定列的数值和 
min(字段) 计算指定列的最小值
 max(字段) 计算指定列的最大值 
avg(字段) 计算指定列的平均值
 -- 分组 
select 分组字段/聚合函数 from 表名 group by 分组字段[having 条件] 
where 分组前过滤 不能写聚合函数 
having 分组后过滤 可以写聚合函数 
-- limit关键字 （mysql独有，常用来分页） s
elect 字段 from 表名 limit 起始行数，返回行数；
 分页公式 起始索引=（当前页-1）*每页条数

(五)sql约束

-- sql约束
1.主键 - primary key 
2.唯一 - unique
3.非空 - not null
4.外键 - foreign key 

-- 主键约束 （不可重复、唯一、非空）
1.创建表时设置
create table 表名（
    字段名1 数据类型，
    字段名2 数据类型 primary key，
    字段名3 数据类型
）;
create table 表名（
    字段名1 数据类型，
    字段名2 数据类型 ，
    字段名3 数据类型,
    primary key(字段名2)
）;
2.修改表结构添加主键
alter table 表名 add primary key(字段名)
3.删除主键约束
alter table 表名 drop primary key;
4.设置主键自增 （必须是整数类型）
create table 表名（
    字段名1 数据类型，
    字段名2 数据类型 primary key auto_increment，
    字段名3 数据类型
）auto_increment=100;//设置主键的自增初始值
自增只能在创建表时设置
delete 只删除数据对自增无影响
truncate 将整个表删除 自增重新开始

-- 非空约束 （某一列数据不能为空）
1.创建表时设置
create table 表名（
    字段名1 数据类型，
    字段名2 数据类型 primary key，
    字段名3 数据类型 not null
）;
2.修改表结构
alter table 表名 add not null(字段名);
alter table 表名 modify 表名 类型名 not null;

-- 唯一约束 （某一列不能重复）
只能在创建表时设置
create table 表名（
    字段名1 数据类型 unique，
    字段名2 数据类型 primary key，
    字段名3 数据类型 not null
）;

-- 外键约束
1.创建表时添加外键
[ constraint ] [外键约束名称] foreign key(外键字段名) references 主表名(主键字段名)
create table 表名（
    字段名1 数据类型，
    字段名2 数据类型 primary key auto_increment，
    字段名3 数据类型
    constraint 外键约束名 foreign key(外键字段名)references 主表名（主键字段名）；
）；
2.添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES主表(主 键字段名);
3.删除外键
alter table 从表 drop foreign key 外键约束名称
4.级联删除
删除主表数据的同时删除从表数据
create table 表名（
    字段名1 数据类型，
    字段名2 数据类型 primary key auto_increment，
    字段名3 数据类型
    constraint 外键约束名 foreign key(外键字段名)references 主表名（主键字段名）；
     ON DELETE CASCADE
）；

-- 默认值
create table 表名（
    字段名1 数据类型，
    字段名2 数据类型 ，
    字段名3 数据类型 default 数据
   
）;

(六)事务

什么是事务

事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败

什么是回滚

即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成 的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

MYSQL 中可以有两种方式进行事务的操作：

  1. 查看提交事务的状态： SHOW VARIABLES LIKE 'autocommit'
  2. 手动提交事务 set @@autocommit = on （默认）/off
  3. 自动提交事务

事务的基本操作

  1. start transaction / begin ——开始一个事务
  2. rollback ——回滚全部事务
  3. commit——提交事务，所有的操作生效，无法在回滚

        begin ;

        sql语句；

        commit/rollback;

事务的四大特性

  1. 原子性： 事务是一个不可拆分的整体，要么全部成功要么全部失败
  2. 一致性： 数据的状态，在执行前后保持一致
  3. 隔离性：事务与事务之间互不影响，执行保持隔离状态
  4. 持久性：执行成功，对数据库的修改是永久性的

事务的隔离级别

  • 一个数据库被多个客户端并发访问，相同的数据被多个事务同时访问，若没有隔离措施，会导致各种问题

产生的问题

  • 脏读： 一个事务读取到另一个事务尚未提交的数据
  • 不可重复读：同一个事务多次读取同一条数据时，数据不一致
  • 幻读： 一个事务内，多次查询同一数据时，数据量不一致

  • 查看隔离级别 select @@tx_isolation;
  • 修改隔离级别：set global transaction isolation level 级别名称
  • 修改完成后需要重新启动数据库

(七)sql操作多表

多表设计

  • 一对一
  • 一对多

在从表（多方）创建一个字段，字段作为外键指向主表的（一方）的主键

  • 多对多

创建一个中间表，中间表至少两个字段，分别作为外键指向各自一方的主键

多表查询

正常情况下查询两个表会发生笛卡尔积现象

分类

  • 内连接（隐式连接、显示连接）
  • 外连接（左连接、右连接）
  • 子查询
-- 内连接
通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
比如通过: 从表的外键 = 主表的主键 方式去匹配
1.隐式连接
SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
2.显示连接
select 字段名 from 左表[ INNER ] JOIN 右表 ON 连接条件 

-- 外连接
1.左连接
以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
如果匹配不到, 左表中的数据正常展示, 右边的展示为null.
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件 
2.右连接
右外连接的特点以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
如果匹配不到，右表中的数据正常展示, 左边展示为null
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

总结

子查询

  1. 一条select 查询语句的结果, 作为另一条 select 语句的一部分
  2. 子查询的特点
    1. 子查询必须放在小括号中
    2. 子查询一般作为父查询的查询条件使用
  3. 子查询分类

where型 子查询 将子查询的结果作为父查询的比较条件

from 型 子查询 将子查询的结果 作为一张表提供给父层查询使用

exists 型 子查询 子查询的结果是单列多行类似一个数组，父层查询使用in函数 包含子查询结果

(八)DCL

创建用户 create uesr '用户名'@'主机名' identified by '密码'
用户授权 grant 权限1，权限2... on 数据库名.表名 to '用户名'@'主机名'
查看权限 SHOW GRANTS FOR '用户名'@'主机名';
删除用户 DROP USER '用户名'@'主机名';
命令行备份 mysqldump -u 用户名 -p 密码 数据库 > 文件路径

二、数据库设计

（一）数据库三范式

第一范式 1NF

原子性，做到列不可拆分

第一范式是最基本的范式。数据库表里面字段都是单一属性，不可再分，数据表中每个字段都是不可再分的最小数据单元

第二范式 2NF

在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。

第三范式 3NF

消除传递依赖

（二）反三范式

  1. 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
  2. 浪费存储空间,节省查询时间 (以空间换时间）

三、mysql索引

  1. 主键索引(primary key)
    1. 主键是一种唯一性索引，每个表只能有一个主键，用于标识数据表中的每一条记录
  2. 唯一索引(unique)
    1. 唯一索引是指 索引列的所有值只能出现一次
  3. 普通索引(index) key
    1. 常见的索引，单纯加快数据的访问速度
  4. MySql将一个表的索引都保存在同一个索引文件中, 如果对中数据进行增删改操作,MySql都会自动的更新索引.

主键索引 （primary key）

一个表可以没有主键，但最多只能有一个主键，且主键值不能包含null

语法格式：即主键的格式

唯一索引（unique）

唯一索引可以保证数据记录的唯一性。

语法格式：即唯一的格式

create unique index 索引名 on 表名 （列名（长度））

普通索引（index）

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBY column）中的数据列创建索引。

  • 在已有的表上创建索引 create index 索引名 on 表名(列名[长度])
  • 修改表结构添加索引 ALTER TABLE 表名 ADD INDEX 索引名 (列名)

索引的优缺点和总结

  1. 添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
  2. 索引的优点
    1. 大大的提高查询速度
    2. 可以显著的减少查询中分组和排序的时间。
  3. 索引的缺点
    1. 创建索引和维护索引需要时间，而且数据量越大时间越长
    2. 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

四、mysql视图

  1. 视图是一种虚拟表
  2. 视图建立在已有表的基础上，视图赖以建立这些表称为基表
  3. 向视图提供数据内容的语句为select语句，可以将视图理解为存储起来的select语句
  4. 视图向用户提供基表数据的另一种形式
  • 视图其实是一个虚拟表，方便我们查询操作！视图主要就是为了简化多表的查询

创建视图

格式 create view 视图名 [column_list] as select语句;

视图的查询

格式 select * from 视图名

五、jdbc连接idea实现增删改、查

为项目添加连接的架包

打开idea --->在项目的根目录的平级建立lib文件夹--->将架包复制到lib文件夹--->并添加到库

jdbc连接mysql 的步骤

加载驱动 ->创建链接->创建处理sql语句的平台->处理占位符->执行sql语句

注册驱动

class JDBCUtil {
   public static Connection getConnection(){
       //加载驱动
       Connection connection = null;
       try {
           Class.forName("com.mysql.cj.jdbc.Driver");
           //创建连接
           String url = "jdbc:mysql://localhost:3306/db1?serverTimezone=GMT%2B8&characterEncoding=utf8&useSSL=true";
           String user = "root";
           String pwd = "123456";
           connection = DriverManager.getConnection(url, user, pwd);
       } catch (Exception e){
           e.printStackTrace();
       }
       return connection;
   }
}

增删改

String sql = "sql语句（？为占位符）";
PreparedStatement ps = JDBCUtil.getConnection().prepareStatement(sql);
ps.setString(占位符的次序,应加载的数据);
ps.execute(); //执行sql语句

查

String sql = "sql语句";
PreparedStatement ps = JDBCUtil.getConnection().prepareStatement(sql);
 ResultSet  resultSet = ps.executeQuery();
 //执行sql语句将结果集返回保存到resultSet

程序员张三疯
关注
php 旅游网毕业论文,旅游网站毕业设计论文（优质范文6篇）
weixin_31874577的博客
03-10 5996
随着社会经济的发展,人民生活水平的大幅提高,旅游也变得越来越大众化。旅游网站可以实现消费者预先对旅游质量和特点进行详细的了解和感知。下面是旅游网站毕业设计论文6篇，供大家阅读指正。旅游网站毕业设计论文第一篇：JSP+MySQL技术下旅游网站系统的设计随着我国人民生活水平的不断提高，旅游已成为人们生活的重要活动，通过旅游网站进行旅游景点查询、旅游线路预订等，可以为游客出行提供极大的方便。设计开发方便...
[转]高负载并发网站架构分析
热门推荐
bluehire的专栏
12-23 2万+
由于自己正在做一个高性能大用户量的论坛程序，对高性能高并发服务器架构比较感兴趣，于是在网上收集了不少这方面的资料和大家分享。希望能和大家交流 msn: defender_ios@hotmail.com ——————————————————————————————————————— ? 初创网站与开源软件 6 ? 谈谈大型高负载网站服务器的优化心得! 8 ? Lighttpd+Squid+Apach
MySQL操作命令大全，让你秒变DB大神
程序员云帆哥的博客
11-19 4046
文章目录1、获取当前日期时间2、日期转换字符串3、字符串转换日期4、时间转换5、拼凑日期6、时间戳转换7、日期时间计算 1、获取当前日期时间 select now(); select current_timestamp(); 2、日期转换字符串 select date_format('2020-11-19 20:06:00','%Y%m%d%H%i%s'); --20201119200600 3、字符串转换日期 select str_to_date('11/19/2020', '%m/%d/%Y');
Mysql 全部教程
10-17
数据库中是通过数据库表来存储数据的，数据库表是二维结构，每行对应一条记录，每一列对应具体的内容。 数据库表的设计时，每一列都有具体的列类型，列名 Java是面向对象的编程语言，类中有对象的特征（属性），对于对象的存储，可以直接存储到数据库表中的一行记录上。要求类的属性和表的字段列一一对应的。
MySQl篇（基本介绍）（持续更新迭代）
最新发布
wlcass zhengge blog
09-15 4299
数据库就是用于存储和管理数据的仓库，英文：DataBase（简称：DB），而在数据库中的数据也是有组织的进行存储！关系型数据库指的是多张相互连接的二维表组成的数据库，所谓二维表指的就是由行和列组成的表，如下图（就类似于Excel表格数据，有表头、有列、有行， 还可以通过一列关联另外一个表格中的某一列数据）。还有MySQL、Oracle、DB2、SQLServer这些都属于关系型数据库，里面都是基于二维表存储数据的。
MySql教程
It's my life . . .
07-17 295
MySQL简介 什么是MySQL？ MySQL是一个多用户、多线程的SQL数据库，是一个客户机/服务器结构的应用，它由一个服务器守护程序mysqld和很多不同的客户程序和库组成。SQL (Structur...
mysql详细教程
菜鸟学识的博客
05-04 4952
数据库：DB（DataBase）概念：数据仓库，软件，安装在操作系统之上作用：存储数据，管理数据。
MySQL详细教程
weixin_44585751的博客
09-23 1199
MySQL常用指令 1.1、查看当前所有数据库 show databases; 1.2、打开指定库名 use 库名 1.3、查看当前库的所以表 show tables; 1.4、查看其它库的所有表 show tables from 表名 1.5、创建表 create table 表名( 列名 列类型, 列名 列类型, ………… ); 1.6、查看表结构 desc 表名; 1.7、开启数据库 以管理员身份打开命令提示符，输入net start mysql 1.8、关闭数据库 以管理员身份打开命令提示符，输
六千字带你快速上手操作MySQL
doomwatcher的博客
11-11 2119
快速上手mysql
攻击防范六（整理）
民兵的家园
06-20 2万+
八种扫描器说明八种扫描器说明⑴NSS（网络安全扫描器） NSS由Perl语言编成，它最根本的价值在于它的速度，它运行速度非常快，它可以执行下列常规检查： ■Sendmail ■匿名FTP ■NFS出口 ■TFTP ■Hosts.equiv ■Xhost 注：除非你拥有最高特权，否则NSS不允许你执行Hosts.equiv。 利用NSS，用户可以增加更强大的功能，其中包括： ■AppleTalk扫描
[原创]我的读研两年半(更新6/6/2006）
ivsking的专栏
09-24 1万+
注意：本帖内的每个网址（URL）均可直接点饥可以进入。另外，由于本人读研所学细分的专业属于工程应用类型，所以需要项目实践加深学习，请一些研究理论学问，家庭宽裕的同学（例如材料科学，或算法理论等等）切勿走这种打工读研路线，希望搞基础学问的同学们都能专心搞出厉害的论文和学问！：）   可能文章太长，大家喜欢看哪段就用ctrl+F搜索对应的部分关键字看吧，呵呵。提纲：   0.前序（打工考研的日子）  
mysql详细教程（完整版）
11-10
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。 MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
mysql完整教程
11-03
mysql教程
mysql最详细的基础入门教程
03-12
最全最完整的mysql基础入门学习资料，由基础到精通，内容详尽，讲解通俗易懂
Mysql安装配置和Mysql使用六千字详解！！
qq_62958114的博客
12-10 2237
数据库基本操作详解，你想学的增删改查都在里面！！！，约束和联合查询详细讲解
6000字带你入门MySQL
https://github.com/QAQDFAFD
01-13 893
mysqlSQL语句函数约束多表查询事务。
MySQL 教程
Sunshine
06-12 246
https://www.runoob.com/mysql/mysql-tutorial.html
mysql教程
浪漫韫秋
06-30 1567
mysql 是最流行的关系型数据库管理系统，在web应用方面mysql是最好的RDBMS(relational database management system) 应用软件之一。 RDBMS=realational database management system 关系数据库关系系统。 在本教程当中，会让大家快速掌握mysql的基本知识，并且轻松使用mysql数据库。 什么...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值