MySQL笔记

最新推荐文章于 2024-08-19 22:23:29 发布

猪鼻龟同学

最新推荐文章于 2024-08-19 22:23:29 发布

阅读量265

点赞数

分类专栏： Java基础文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45135434/article/details/118684693

版权

Java基础专栏收录该内容

9 篇文章 1 订阅

订阅专栏

启动命令 net start MySQL
停止命令 net stop MySQL
默认端口：3306

一、SQL

DDL:数据定义语句建表建库
DML:Data Manipulation Language 数据操纵语句 insert update delete
DQL:数据查询语句 select
DCL:数据控制语句 commit

1.1 表和库的语句

1.1.1 库

查看所有库

show databases;

建库

create database 库名；

删库

drop database 库名；

选择库

use 库名；

数据库的数据类型

字符串	varchar(50)	可变长度	nvarchar
	char(50)	固定长度	nchar
数值	int	float double	decimal
日期	date	年月日
	time	时分秒
	datetime	年月日时分秒

1.1.2 表

建表

   create table 表名(
       字段名  字段类型  约束,
       字段名  字段类型  约束,
       ......
      
      字段名  字段类型  约束 
   );

删表

drop table 表名;

复制表

步骤：

使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等
复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令将完全的复制数据表结构。
如果你想复制表的内容，你就可以使用 INSERT INTO … SELECT 语句来实现。

1: show create table 表名;
2: 修改步骤1获得的建表语句表名,创建新表;
3: insert into 新表名 select * from 表名

create table 新表名 select * from 旧表名;

约束

唯一 unique (别称：UK ) 值不能重复的，值可以为Null，可以多个值是null
非空 not null 值不能为空
主键 primary key (别称：PK) 非空且唯一
外键 foreign key (别称：FK)
默认值 default
检查约束 check【mysql不支持】

主键：功能是相当于唯一且非空
但是一个表只能设置一个主键的，但是这个主键不一定只有一列，可以由多列组成【复合主键】
主键还可以自增，自动增长
外键
1.外键引用的是另一个表的主键
2.外键列的数据类型必须和主表的主键的数据类型保持一致
3.外键建立在子表上的

添加外键：

  alter table 表名 add constraint 约束名  约束类型
  alter table 表名 add constraint 约束名 foreign key(外键) references 主表(主键);
  例：
  alter table student add constraint fk_f1 foreign key (studentid) references class(classid);

1.1.3 改表名

 alter table taboldname rename to newname;

1.1.4 更改表结构

1.改类型

  alter table tabname modify colname coltype;

2.改列

  alter table tabname change oldcolname newcolname coltype;

3.添加一列

  alter table tabname add  newcolname coltype first | after xxx;

4.删除一列

  alter table tabname drop colname;

5.修改列的位置

  alter table tabname modify colname coltype first|after xxx;

1.2 CURD

1.2.1 插入数据

语法：

insert into 表名 values|value(value1,value2,...valueN);
或者
insert into 表名(field1,field2,...fieldN) values|value(value1,value2,...valueN);

如果数据是字符型，必须使用单引号或者双引号，如：“value”。

value和values：
插入单条数据时，使用values较快；
插入多条数据时，使用value较快。

1.2.2 修改数据

语法：

UPDATE 表名 SET 需要修改的字段1=新值1, 需要修改的字段2=新值2
[WHERE 条件]

1.2.3 查询数据

SELECT 字段名,字段名
FROM 表名
[WHERE 条件]
[LIMIT N][ OFFSET M]

查询语句中可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
可以使用 WHERE 语句来包含任何条件。
可以使用 LIMIT 属性来设定返回的记录数。
可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

1.2.4 WHERE子句

SELECT * FROM 表名
[WHERE condition1 [AND [OR]] condition2.....

可以使用 AND 或者 OR 指定一个或多个条件。（and且，or或）
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令

1.2.5 LIKE子句模糊查询

占位符	描述
%	零个或多个字符
_	一个字符

like子句如果不使用%或_效果将与=一样。
语法：

SELECT *
FROM 表名
WHERE 字段名1 LIKE ‘%xxx%’ [AND [OR]] 字段名2= 'somevalue'

1.2.6 UNION操作符

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据(UNION ALL 不会删除重复数据)。
语法：

SELECT *
FROM 表1
UNION [ALL | DISTINCT]
SELECT *
FROM 表2

1.2.7 排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

ASC从小到大排序，默认值
DESC 从大到小排序

例：

SELECT * from student
ORDER BY name desc,sex asc;

1.2.8 分组

GROUP BY 语句根据一个或多个列对结果集进行分组。

聚合函数	功能
SUM()	求和
AVG()	平均值
COUNT()	计数
MAX()	最大值
MIN()	最小值

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

注意：使用group by 后，selcet后的字段只能是 group by后的字段。

HAVING子句
如果在分组后还要进行条件筛选那么就不能用where了，而是用having子句
例：
查询学生表中大于18岁的人数大于5的班级

select class from student where age > 18
group by class having count(*) > 5;

1.2.9 关联查询

where连接
与inner join差不多
语法

select * from 表1，表2 where 表1.id=表2.id

可以使用子查询代替

select sname,cname from student,class where student.cno=class.cno;

selcet sname,(select cname from class where class.cno=student.cno) as classname 
from student;

JOIN连接：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

1.3存储过程和函数

1.3.1 存储过程

存储过程就是具有名字的一段代码，用来完成一个特定的功能。
创建的存储过程保存在数据库的数据字典中。
创建和调用

关键语法
声明语句结束符，可以自定义:

DELIMITER $$
或
DELIMITER //

声明存储过程:

CREATE PROCEDURE 存储过程名(IN p_in int)

存储过程开始和结束符号:

BEGIN .... END

变量赋值:

DECLARE 变量名 int unsigned default 默认值;

执行存储过程

call 存储过程名[(传参)];

存储过程参数

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

1.3.2 存储函数

mysql 默认不允许创建function,使用该命令[1418]
SET GLOBAL log_bin_trust_function_creators=TRUE;

创建语法

CREATE FUNCTION func_name ([param_name type[,...]])
RETURNS type
BEGIN
	routine_body
END;

参数说明

func_name ：存储函数的名称。
param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
RETURNS type：指定返回值的类型。
routine_body：SQL代码内容。

1.3.3 触发器

语法格式

delimiter $$
create 
trigger 触发器名 after|before  insert|delete|update
	on 表名
	for each row begin --行级触发
	begin
		sql语句
	end$$
delimiter ;

触发器通过增删改语句触发；

1.3.4 if语句

delimiter $$
create procedure|function pro_name|funname([参数列表])
	begin
	if 条件 then
		操作语句；
	elseif 条件 then
		操作语句；
	else 
		操作语句；
	end if;
	end$$
delimiter ;

1.3.5 循环语句

1.while循环

delimiter $$
create
	procedure sum1(a int)
	begin
		while 循环条件 do
			循环体
		end while;
	end$$
delimiter ;

2.用loop循环

delimiter $$
create
	procedure sum2(a int)
	begin
		declare  p_sum int defualt 0;
		declare i int default 1;

	loop_name:loop
		if i>a then
			leave loop_name;
		end if;
		set p_sum = p_sum+i;
		set i=i+1;
	end loop;
	select p_sum;
		
	end$$
delimiter ;

3.用repeat循环

delimiter $$
create
	procedure sum3(a int)
	begin
		declare  p_sum int defualt 0;
		declare i int default 1;

	repeat
		set p_sum = p_sum+i;
		set i = i+1;
	until i > a end reapeat;
		select p_sum;
		
	end$$
delimiter ;

1.4 游标

1.4.1 什么是游标

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标充当指针的作用。

尽管游标能遍历结果中的所有行，但他一次只指向一行。

游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

1.4.2 用法

声明一个游标: declare 游标名称 CURSOR for table;(这里的table可以是你查询出来的任意集合)
打开定义的游标:open 游标名称;
获得下一行数据:FETCH 游标名称 into testrangeid,versionid;
需要执行的语句(增删改查):这里视具体情况而定
释放游标:CLOSE 游标名称;

注:mysql存储过程每一句后面必须用;结尾，使用的临时字段需要在定义游标之前进行声明。
在这里插入图片描述

fetch是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。
在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件，指定这个事件发生时修改done变量的值。

--定义变量  
DELIMITER &&
CREATE
	PROCEDURE cur_demo()
	BEFIN
	declare done int default 0;  
	declare p_name varchar(50);   
	declare p_id int;  
--创建游标，并存储数据  
	declare cur_emp CURSOR for select emp_id,empname from emp;  
--游标中的内容执行完后将done设置为1  
	 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;   
--打开游标  
	open cur_emp;  
--执行循环  
	curloop:LOOP
--判断是否结束循环  
    	IF done=1 THEN    
    	LEAVE curloop;  
    END IF;   
--取游标中的值  
    FETCH  cur_emp into p_id,p_name;  
--执行其他操作  
	insert into emp_bak01 values (p_id,p_name);
  END LOOP;  
--释放游标  
CLOSE cur_emp;  
  
END$$
DELIMITER ;

二、视图索引事务

2.1 视图

2.1.1 什么是视图

数据库中的视图是一个虚拟表。同真实的表一样，视图包含一系列带有名称的行和列数据。行和列数据来自由定义视图查询所引用的表，并且在引用视图时动态生成。

对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动反映到视图中。

2.1.2 视图的优点

1.简单化
看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化它们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2.安全性
通过视图用户只能查询和修改他们所能见到的数据。
3.逻辑数据独立性
视图可帮助用户屏蔽真实表结构变化带来的影响。

2.1.3 相关sql语句

创建视图的语法:

create view viewname  as select...

查看视图的语法:

DESCRIBE  viewname

查看数据库中所有视图的详细信息

SELECT * FROM information_schema.views;

使用SHOW TABLE STATUS命令查看视图信息

CREATE  OR REPLACE  VIEW emp_view AS SELECT * FROM dept
DESCRIBE emp_view;
SHOW TABLE STATUS LIKE 'emp_view'
SHOW CREATE VIEW emp_view;

2.2 索引

2.2.1 关于索引

什么是索引
索引用于快速找出在某个列中有一特定值的行。索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。

MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关；MyISAM和InnoDB存储引擎只支持BTREE索引MEMORY/HEAP存储引擎可以支持HASH和BTREE

索引的优点

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度，这也是创建索引的主要原因。
在实现数据的参考完整性方面，可以加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

添加索引的不利方面

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

2.2.2 MySql索引的分类

1．普通索引和唯一索引
普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重
复值和空值。唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，
则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

2．单列索引和组合索引
单列索引即一个索引只包含单个列，一个表可以有多个单列索引。组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

3．全文索引
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、 VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。

4．空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。 MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

2.2.3 设计索引需要考虑的准则

索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新。
避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。
数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

2.2.4 相关sql语句

1.建表时创建索引

创建唯一索引：
在这里插入图片描述

创建单列索引
单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。
在这里插入图片描述

创建组合索引组合索引是在多个字段上创建一个索引。
在这里插入图片描述
遵从“最左前缀”：利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如，这里由id、name和age 3个字段构成的索引，索引行中按id、name、age的顺序存放，索引可以搜索（id, name, age）、（id, name）或者id字段组合。如果列不构成索引最左面的前缀，那么MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用索引查询

2.如果表已经存在，创建索引的语法

Alter table tabname add [unique|fulltext|spatial] index indexname (colname[length])

在这里插入图片描述
3.删除索引的语法

2.3 事务

2.3.1 什么是事务

InnoDB存储引擎支持行级锁，支持事务处理。事务是由一组SQL语句
组成的逻辑处理单元

ACID特性

原子性（Atomicity）：事务具有原子不可分割的特性，要么一起执行，要么都不执行。
一致性（Consistency）：在事务开始和事务结束时，数据都保持一致状态。
隔离性（Isolation）：在事务开始和结束过程中，事务保持着一定的隔离特性，保证事务不受外部并发数据操作的影响。
持久性（Durability）：事务完成后，数据将会被持久化到数据库中。
InnoDB存储引擎并发事务处理能力大大增加了数据库资源的利用率，

2.3.2 事务隔离级别

并发事务同时也存在一些问题，主要包括更新丢失（Lost Update）、脏读（Dirty Reads）、不可重复读（Non- Repeatable Reads）、幻读（Phantom Reads）。

幻想读：事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻想。
不可重复读取：事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录，然后T1再次查询，发现与第一次读取的记录不同，这称为不可重复读。
脏读：事务T1更新了一行记录，还未提交所做的修改，这个T2读取了更新后的数据，然后T1执行回滚操作，取消刚才的修改，所以T2所读取的行就无效，也就是脏数据。

为了处理这些问题，SQL标准定义了以下几种事务隔离级别

隔离级别	解决的问题
READ UNCOMMITTED	幻想读、不可重复读和脏读都允许。
READ COMMITTED	允许幻想读、不可重复读，不允许脏读
REPEATABLE READ	允许幻想读，不允许不可重复读和脏读
SERIALIZABLE	幻想读、不可重复读和脏读都不允许

2.3.3 相关sql

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。或者 SET @@autocommit=0; 也可以将事务设置为手动
查看事务的隔离级别 SELECT @@transaction_isolation; 【 select @@tx_isolation;】
设置事务的隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

猪鼻龟同学

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL笔记

目录一、SQL1.1 基本SQL语句启动命令 net start MySQL停止命令 net stop MySQL默认端口：3306一、SQLDDL:数据定义语句建表建库DML:Data Manipulation Language 数据操纵语句 insert update deleteDQL:数据查询语句 selectDCL:数据控制语句 commit1.1 基本SQL语句建库create database 库名；数据库的数据类型字符串varchar(
复制链接

扫一扫

专栏目录