MySQL学习笔记

hbnlko

已于 2024-07-31 20:51:12 修改

阅读量349

点赞数 5

文章标签：学习笔记

于 2024-07-29 19:26:07 首次发布

本文链接：https://blog.csdn.net/qq_68366976/article/details/140778563

版权

2024/7/29

注释

mysql有两种注释方法

1.-- 加一个空格

2.#

创建数据库

create database 数据库名称

创建表

表是数据库中存储数据的主体

create table 表名() 表名一般用下划线_来分割单词括号内是表中字段

CREATE TABLE table_name (

column1 datatype constraints,

column2 datatype constraints, ... );

列名称数据类型约束条件，多个列之间用逗号隔开

id int auto_increment PRIMARY KEY,

auto_increment表示自动递增的

删除表

-- 删除表
drop table `table`;

约束条件

约束条件可以不写

约束条件 PRIMARY KEY 表示为主键主键是唯一标识，不能重复不能为空

约束条件 NOT NULL 表示不能为空

code varchar(20) not NULL, varchar(20)表示存储可变长度的字符串，最大为20个字符

重命名表

可以使用to和as

alter table staff rename to t_staff;

alter table staff rename as t_staff;

设置编码

alter table staff character set 'utf8mb3';

-- utf8mb3 是一种字符集，表示使用最多3个字节来存储一个字符的UTF-8编码。
-- 不同的字符占用的空间是不一样的
-- 数字，字母，欧洲的语言字符占一个字节
-- 中东，阿拉伯语言占两个字节
-- 中文，棒子文，日文，东南亚地区字符占3个字节

对表中列的操作(修改表)

-- 添加一个列 (添加sex列)

alter table staff add COLUMN sex VARCHAR(2);

-- 删除列(使用drop)
alter table staff DROP COLUMN age;

-- 重新定义列MODIFY(修改sex的数据类型和约束条件)

MODIFY 用于修改现有列的类型、属性或约束，而不改变列的名称。
alter TABLE staff MODIFY sex VARCHAR(20) not NULL;

-- 列重命名CHANGE(修改sex列为age，后面跟数据类型和约束条件)

CHANGE 用于修改现有列的名称和类型、属性或约束。
ALTER TABLE staff CHANGE sex age int not null;

MODIFY:
- 只能修改列的类型、属性或约束。
- 不能修改列的名称。
- 适用于只需要调整列定义而不改变列名的情况。
CHANGE:
- 可以修改列的名称、类型、属性或约束。
- 适用于需要同时更改列名和其他属性的情况。

-- 定义列的位置(将sex列放在name列的后面)

alter table staff modify sex VARCHAR(20) not null after name;

重定义

-- 在起名列名表名时经常会用到一些关键字用tab键上面的符号括起来表示不是关键字
create table `table`(
`varchar` varchar(20)
)

2024/7/30 DML数据管理语言增删改

在表中添加数据

insert into staff(属性列表) value（每个属性对应的值)，（每个属性对应的值）;

添加多个数据用括号括起来并用逗号隔开

eg：insert into staff(id,`code`,`name`,salart) VALUE
(2,'1002','李四',9000);

如果某个属性是自动增长的，则在添加时可以不指定

eg：insert into staff(`code`,`name`,salart) VALUES 由于ID是自增的，所以我们可以不指定
('1003','王五',10000),
('100','赵六',11000);

-- 自动递增不会回撤不会补齐从该列最大值递增

-- 可以不指定列，但是需要将全部列指定数据
insert into staff value(6,'1006','王维',3000);

在表中删除数据

-- 删除 delete
delete from staff where id=2;

清空表

-- 清空表
delete from staff; -- 删除掉每一行数据
TRUNCATE staff; -- 清空表性能更快

修改表中的数据 update

update staff set name='李清照' where id=3;
UPDATE staff set salart=salart+2000 WHERE name='李清照';
update staff set name='蒲松龄' ,salart=salart-2000 where id=5;

查询select

select 1;
select now(); -- 查看当前时间

-- 查询表格

-- 指定列明查询
select name ,salart from staff;
-- 使用*代替所有的列
select * from staff;
-- 使用as 指定别名列方法结果表视图
select name as 姓名 from staff;
-- 使用where来指定条件语句
select * from staff where id=5;
-- 不等于
select * from staff where id!=5;
select * from staff where id<>5;-- 既大于又小于就是不等于
-- 大于小于
select * from staff where id>=3;

-- 对null值的判断

select * from staff where salart =null; -- 不对的
select * from staff where salart is null; -- 正确的
select * from staff where salart is not null;
select * from staff where salart <=> null; -- 等于null

-- 多条件

使用 and or 和between and，in，not in

select * from staff where id<7 and salart>=10000;
select * from staff where salart<5000 or salart>=1000;
select * from staff where id=7 or id=3 or id= 4;
select * from staff where id in (1,2,3,4);
select * from staff where id not in (1,2,3,4);
-- between and
select * from staff where salart between 1000 and 10000;

模糊查询 like

%代表任意个任意字符 _代表有且只有一个任意字符
select * from staff where name LIKE '王%';
select * from staff where name LIKE '张%';

是否存在exists

类似于条件语句，当exists里面有数据（为true）才会执行前面的select

select * from staff where EXISTS(select * FROM staff WHERE 1=2);
select * from staff where EXISTS(select * FROM staff WHERE id=3);
select * from staff where not EXISTS(select * FROM staff WHERE 1=2);

any all

SELECT * FROM staff WHERE salart >any(SELECT 25);
SELECT * FROM staff WHERE salart >ALL(SELECT 25);

排序 order by

SELECT * FROM staff ORDER BY salary;
-- 正序 asc 倒序 DESC，默认正序
SELECT * FROM staff ORDER BY salary ASC;
SELECT * FROM staff ORDER BY salary DESC;

SELECT * FROM staff ORDER BY salary ASC,CODE DESC;
SELECT * FROM staff ORDER BY salary,CODE DESC;

拼接（合并）查询结果俩个列的数据的数量需要相同

SELECT NAME,salary FROM staff UNION
SELECT CODE,NAME FROM staff
-- UNION会将重复的结果去除
SELECT NAME,salary FROM staff UNION ALL
SELECT NAME,salary FROM staff
-- UNION ALL不会去除

去重DISTINCT 对整个查询结果去重

SELECT DISTINCT salary,NAME FROM staff;

-- 部分查询 limit

SELECT * FROM staff ORDER BY salary DESC LIMIT 3; 查询前三个

-- 查询第三名到第四名 limit start,count

SELECT * FROM staff ORDER BY salary DESC LIMIT 2,2;

-- case when then

-- 范围性判断
SELECT * ,CASE
WHEN salary<10000 THEN '薪资较低'
WHEN salary>=10000 AND salary<=15000 THEN '薪资正常'
ELSE '薪资较高'
END
FROM staff;
-- 数值匹配
SELECT *,CASE salary
WHEN 12000 THEN '还行'
WHEN 15000 THEN '不错'
WHEN 20000 THEN '挺高'
WHEN 110000 THEN 'good'
END as 'level' FROM staff;

分组，聚合函数将多个数据聚合成一个数据的函数

-- 最大值
SELECT MAX(salary) FROM staff
-- 最小值
SELECT MIN(salary) FROM staff
-- 平均数
SELECT AVG(salary) FROM staff
-- 求和
SELECT SUM(salary) FROM staff
-- 求个数
SELECT COUNT(salary) FROM staff

-- 分组查询
select department,
avg(salart)as 平均薪资,
sum(salart) as 薪资总和
from staff GROUP BY department;
-- 分组筛选
-- having 是对分组之后的数据在进行筛选
select department from staff group by department
having AVG(salart)<10000;

-- null
select department, avg(salart),count(salart) from staff GROUP BY department;
SELECT COUNT(*) ,count(1) FROM staff;

链接查询

外连接：

左外连接left join 右外连接right join

左链接以左表为主表，会显示所有的数据，右表为附表，只会显示和主表有对应关系的数据

内连接 inner join/join 只显示有对应关系的数据

-- 老师的姓名以及教授的课程
-- teacher Tname course Cname
-- teacher.tid=course.tid
select tname ,cname from teacher
left join course
on teacher.tid=course.tid;
-- as起别名
select a.tname,b.Cname from teacher as a left join course as b -- 左表为主表
on a.tid=b.tid

-- 子查询
select sname from student where sid in(select sid from sc where score<60);

-- 将子查询当做表进行查询
SELECT sname from (SELECT * from student where sid = 01) as g;

2024/7/31

mySql中的行列转换

select name,
sum(CASE SUBJECT
WHEN '语文' then fraction
else 0
END )as '语文',
sum( CASE SUBJECT
WHEN '数学' then fraction
else 0
END) as '数学',
sum( CASE SUBJECT
WHEN '英语' then fraction
else 0
END) as '英语'
from t_score GROUP BY name;

简化一下上面的代码即为select name,'数学','语文','英语' GROUP BY NAME;

再来解读以下每个sum，当中使用了case when then 当科目符合要求时就返回成绩，不符合要求返回0,3个科目按照name分组得到的sum即为该单科的成绩

mySql中的数据类型

整数类型 tinyint(1个字节)，smallint(2个字节) ，mediumint(3个字节), int(4个字节), bigint(8个字节)

浮点型 double(总长度,小数位数) ，float ，decimal

字符串 char varchar() text

char类型最大长度为255

varchar能够存储的字节数为65535

关于varchar：

1.结构问题，varchar类型的第一个字节不存储数据

2.varchar前两个字节要存储长度

3.由上，有效位就剩下65532，编码格式决定能存储多少个字符，比如utf8mb3为多大三个字节存储一个字符，多以varchar括号里的字符数就位65532/3 取整

4.行中列的总长度不能超过65535 即varchar和同列的其他的的和、

如果要存储长文本，使用text，因为他不会与其他的存在一起，没有65535的限制

char和varchar的区别

1.char是定长的，varchar是变长的

char(20)存入‘abc’，则‘abc’占20个字符位

varchar(20)存入‘abc’，则‘abc’只占3个字符位

2.char的性能更好，varchar稍逊，因为要计算字符数

3.身份证号等固定长度的使用char，介绍信息等可变的使用varvhar

text用来记录长文本，不需要指定长度

日期：

Date 年月日 time时分秒毫秒 Datetime年月时分秒

视图view

可以简化数据操作，提高数据安全

视图不存储数据，数据还是存储在表中

使用视图可以隐藏不想被知道的信息，保证数据安全

视图view 是一个已经编译好的sql语句

create view view_name as

select column1,column2...

from table_name

where 条件；

后续可以直接将视图当做表使用

select column1 from view_name;

触发器trigger

相当于一个事件，一旦表中发生了指定的事件，该触发器就会自动运行

触发器可以通过三种操作触发：增删改 insert delete update

触发时机 before after

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
trigger_body;

trigger_body：

BEGIN

INSERT INTO employee_audit (employee_id, old_salary, new_salary)

VALUES (OLD.id, OLD.salary, NEW.salary);

END;

old与new：old. 是修改前的数据二new.是更新后的数据

trigger_name: 触发器的名称，必须是唯一的。

{BEFORE | AFTER}: 指定触发器是在操作之前（BEFORE）还是之后（AFTER）执行。

{INSERT | UPDATE | DELETE}: 指定触发器响应的操作类型：

INSERT: 插入新记录时触发。
UPDATE: 更新现有记录时触发。
DELETE: 删除记录时触发。
table_name: 触发器关联的表名。触发器只能关联到一个表。

FOR EACH ROW: 表示触发器会为受影响的每一行执行一次。

trigger_body: 触发器的具体操作内容，可以是多条 SQL 语句。如果包含多条语句，需要使用 BEGIN ... END 块。

删除触发器：DROP TRIGGER trigger_name;

-- 尽量不用触发器会影响正常的业务逻辑由JAVA逻辑代码完成触发器的工作

自定义函数

create function 函数名(参数类型) returns 返回的类型

BEGIN

函数体；

逻辑操作（return 一个值）；

END;

注意：参数类型的格式先名称后类型如（sum int）

create function method(score int) RETURNS VARCHAR(20)
BEGIN
-- 判断score的数值 60分以上及格否则不及格
-- 定义一个局部变量记录返回结果
declare result varchar(20);
IF score>=60
then -- 对变量赋值两种语法
set result='及格';
ELSE
set result='不及格';
end if;
return result;
END;

SELECT method(80); 调用
SELECT * ,method(score) from sc;

drop FUNCTION method; 删除

其他一些函数

//想下取整
SELECT FLOOR(12.99);

//char_length（）返回字符数， length（）返回字节数

SELECT CHAR_LENGTH('你好'),LENGTH('你好');

//选择左边的三个字符

select left('123456',3);

//选择右边的三个字符

select right('123456',3);

//comcat 拼接
select concat(left('16652909373',3),'****',RIGHT('16652909373',4));

//trim 去掉首位的空格

SELECT TRIM(' 123 5 ');

//将12替换为a

SELECT REPLACE('123123123','12','a');

//截取从第二个开始的三个

SELECT SUBSTRING('123123'from 2 for 3 )

//截取第6个开始到结尾

SELECT SUBSTR('abcdefg'from 6)

//颠倒次序

SELECT REVERSE('abc')

//获取时间

SELECT now(),SYSDATE();
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d-%h');

存储过程procedure

存储过程（Stored Procedure）是一组预编译的 SQL 语句的集合，它封装了可以复用的业务逻辑。存储过程通常用于执行复杂的数据库操作，比如插入、更新或删除记录，以及执行其他管理任务。

CREATE PROCEDURE procedure_name ([parameter_name data_type ])
BEGIN
-- 存储过程体
DECLARE variable_name data_type;
-- 逻辑操作
END;

存储过程创建后，可以通过 CALL 语句来执行它：

CALL procedure_name(parameter_values);
DROP PROCEDURE procedure_name;

DECLARE

DECLARE 语句在 MySQL 中用于在存储过程、函数、触发器或事件中声明局部变量、条件处理程序（错误处理）、游标等。DECLARE 语句必须位于 BEGIN ... END 块的开头，并且只能在存储过程、函数、触发器或事件内部使用。

自定义函数与存储过程的区别

自定义函数主要用于执行某个计算或处理，并返回一个单一的结果值，通常在sql查询中使用

自定义函数必须且只能返回一个值，自定义函数可以在查询的任何敌方使用

存储过程用于执行一系列操作或复杂的业务逻辑，可以包含多个sql语句包括增删改查等

存储过程不必返回值，存储过程不能嵌套入SQL查询中，需要通过call调用

三范式

-- 1.每一列的数据是不可分割的
-- 2.每一列的数据要完全依赖主键不可以部分依赖
-- 3.不可以传递依赖

事务

事务的ACDI四大特性

原子性：事务内的操作是一个整体，要么执行成功，要么执行失败

一致性：事务执行前后，数据库状态保持一致

隔离性：多个事务并发时，事务之间不能互相影响

持久性：事务一旦执行成功，数据库会保证数据处理一定会持久化到数据库中

BEGIN;开始事务

COMMIT;提交事务，确认所有操作，并将更改永久保存到数据库中

ROLLBACK;回滚事务，撤销自事务开始以来的所有更改

并发访问数据混乱：

脏读：一个事务读取到另一个事务修改未提交的记录

幻读：当事务不是独立执行时出现的一种现象

一个事务在执行过程中多次查询数据时，由于其他事务的插入或删除操作，导致同样的查询在不同时间点返回不同的结果集。同一个事务的相同查询在不同时间点返回不同的结果集。

不可重复读：一个事务两次读取的数据不一致

当一个事务在执行过程中多次读取相同的数据项时，由于其他事务对该数据项进行了修改，导致每次读取的结果不同。

不可重复读 关注的是单个数据项的值在事务内的变化。
幻读关注的是查询结果集在事务内的变化。

事务的隔离级别

JDBC

//1.JDBC链接数据库的六个步骤

//1>加载驱动

//2>创建链接

//3>获取执行对象

//4>执行SQL语句

//5>处理结果集

//6>关闭链接

package com.easy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import java.sql.Statement;

public class JDBC {

   public static void main(String[] args) {
       //1.JDBC链接数据库的六个步骤
       String url="jdbc:mysql://localhost:3306/easydata";
       String username="root";
       String password="2003417";
       String driverClassName="com.mysql.cj.jdbc.Driver";
       //1>加载驱动
       try {
           Class.forName(driverClassName);
       } catch (ClassNotFoundException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
       Connection con=null;
       Statement sta=null;
       //2>创建链接
       try {
           con=DriverManager.getConnection(url,username,password);
       //3>获取执行对象
           sta= con.createStatement();
       //4>执行SQL语句
           int rowCount=sta.executeUpdate("delete from student where sid=10");
       //5>处理结果集
           if(rowCount>0) {
               System.out.println("删除成功");
           }else {
               System.out.println("删除失败");
           }
       } catch (SQLException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }finally {
           //6>关闭链接
           if(sta!=null) {
               try {
                   sta.close();
               } catch (SQLException e) {
                   // TODO Auto-generated catch block
                   e.printStackTrace();
               }
           }
           if(con!=null) {
               try {
                   con.close();
               } catch (SQLException e) {
                   // TODO Auto-generated catch block
                   e.printStackTrace();
               }
           }
       }
   }
}