当前位置：首页 > news >正文

MySQL学习笔记第三天

news 来源：原创 2024/9/20 19:34:49

首先，我们用以下命令在数据库中建立四张所需表

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

连接查询

一、外连接：
1.左外连接left JOIN 以左表为主表会显示所以的数据，右表为副表，只会显示和主表有关系的数据，右连接相反
2.右外连接right JOIN
3.全外连接full JOIN mysql不支持
二、内连接
inner JOIN 也可以写成join 只显示有对应关系的数据

– 老师的姓名以及教授的课程

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 WHERE cname is null;SELECT a.Tname,b.Cname FROM teacher as a RIGHT JOIN course as b ON a.TId=b.TId;SELECT a.Tname,b.Cname FROM teacher as a LEFT JOIN course as b ON a.TId=b.TId;

– 交叉连接

SELECT * FROM teacher a,course b WHERE a.TId=b.TId;

内连接inner JOIN 也可以写成join 只显示有对应关系的数据

-- 子查询
SELECT sname FROM student WHERE sid in (SELECT sid FROM sc WHERE score<60)
-- 将子查询当作表进行查询
SELECT sname FROM (SELECT* FROM student WHERE sid=01) AS b;

– 行列转换

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

– if 函数三元

SELECT *,if(SUBJECT='语文',fraction,0)FROM t_score

数据类型

整数类型
– TINYINT(1) SMALLINT(2) MEDIUMINT(3) int(4) BIGINT(8)

浮点型
double(总长度，小数位数)后两个同理 float decimal

字符串
char(255) varchar text longtext

varchar(16383(计算得到,mb3)) 能够存储的字节数是65535
– 1.结构问题，varchar类型的第一个字节不存储数据
– 2.varchar第一个字节后的第二三个字节存储字符数据长度
– 3.有效位就剩下65532 由编码格式决定存储多少个字符
– 4.行中列的总字节长度不能超过65535
– 5.如果要存储长文本使用text类型

char和varchar的区别
– 1.char是定长的，varchar是变长的
– char(20) 无论存储的数据是多是少，只要能存下，则永远占20个字符位
– varchar(20) 根据存储的数据，改变占用的字符位数
– 2.char的性能更好 varchar次之，因为要计算字符数
– 3.使用场景：存储位数固定：char例如：身份证号，手机号，学号。场景不固定的用varchar

text 长文本不需要设置长度

日期
Date 年月日
time时分秒毫秒
datetime 年月日时分秒

视图 view

是一个已经编译好的SQL语句

– 创建视图 v_
create view v_student_score AS
select a.sname,c.cname,b.score from student as a
left join sc as b on a.sid=b.sid
left join course as c on b.cid=c.cid;
– 视图中不存储数据数据还是存储在表中
SELECT * from v_student_score;
– 编译执行

– 触发器 trigger
– 相当于一个事件 ,一旦表中发生了指定的事件,该触发器就会自动运行
– 触发器可以通过三种操作触发增删改
– 触发时机 before after

create trigger tgg_i_a_student after insert on student for each row BEGINupdate log set val=(select count(*) from student) where `key`='studentcount';
end;create trigger tgg_u_b_student before UPDATE
on student for each row  BEGIN

create trigger tgg_u_b_student before UPDATE
on student for each row  BEGIN
-- old.列名  原来数据
-- new.列名  新的数据
update log set val=
CONCAT(CONCAT('{',old.sid,',',old.sname,'}'),'->',CONCAT('{',new.sid,',',new.sname,'}')) WHERE`key`='lastupdate';
end;

如果触发器代码报错，请尝试先运行以下代码：

set global log_bin_trust_function_creators=TRUE;

– 删除触发器

drop trigger tgg_i_a_student;
drop trigger tgg_u_b_student

尽量不用触发器会影响正常业务逻辑，使用java逻辑代码完成触发器的工作

函数

now(),max等聚合函数,if()

判断score 的数值 60分以上及格否则不及格
定义一个局部变量记录返回结果

create function method(score int) returns varchar(20)
BEGINDECLARE result varchar(20);IF score>=60 THENSET result='及格';
ELSESET result='不及格';
END IF;return result;
END;SELECT *,method(score) FROM sc

mysql数值常用函数


ABS(x)	返回 x 的绝对值
CEILING(x) 或 CEIL(x)	返回不小于 x 的最小整数值
FLOOR(x)	返回不大于 x 的最大整数值
ROUND(x, d)	将 x 四舍五入到 d 位小数
MOD(x, y)	返回 x 除以 y 的余数
sqrt(x)	求平方根

mysql字符串常用函数


CONCAT(str1, str2, …)	将多个字符串连接成一个字符串
SUBSTRING(str, start, length)	返回字符串 str 从 start 开始长度为 length 的子串
UPPER(str)	将字符串转换为大写
LOWER(str)	将字符串转换为小写
LENGTH(str)	返回字符串 str 的长度
TRIM(str)	去除字符串首尾的空格
REPLACE(str, from_str, to_str)	在字符串 str 中将 from_str 替换为 to_str

mysql日期函数


NOW()	返回当前日期和时间
DATE_FORMAT(date, format)	返回日期的格式化表示
DATE_ADD(date, INTERVAL expr type)	在日期上添加一个时间间隔
DATEDIFF(date1, date2)	返回两个日期之间的天数差
DAYOFWEEK(date)	返回日期对应的星期几

向下取整

SELECT FLOOR(12.99)

计算字符串长度，前者计算字符长度，后者计算字节长度

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

截取字符串，left从前截取，right从后截取

SELECT LEFT('123456',3),RIGHT('123456',3)
-- 手机号显示形式
SELECT CONCAT(LEFT(13151666223,3),'****',RIGHT(13151666223,4))

清空空白字段

SELECT TRIM('    123   ')

将1替换成a

SELECT REPLACE('123123123','1','a')

从第2位字符往后截取3位

SELECT SUBSTR('abcdefg'FROM 2 FOR 3)
-- 从第2位字符截取到最后
SELECT SUBSTR('abcdefg'FROM 2)

倒序排列字符串

SELECT REVERSE('dsfasfas')

获取现在的时间

SELECT NOW(),SYSDATE()

时间格式化

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%S')

存储过程

PROCEDURE

CREATE PROCEDURE pro_insert_student_log(IN num INT)
BEGIN
DECLARE i int DEFAULT 0;
DECLARE stuname VARCHAR(20); 
DECLARE randomS int;
-- 循环语句  loop 死循环 用leave跳出死循环 aa: LOOP-- 获取学生的名字  生成随机成绩
SELECT Sname INTO stuname FROM student LIMIT i,1;
SET randomS=FLOOR(RAND()*100);
-- 插入log表
INSERT INTO log VALUE(stuname,randomS);
SET i=i+1;IF i>=num THENLEAVE aa; END IF; 
END LOOP aa;
END;CALL pro_insert_student_log(8)

三范式

1.每一列的数据不可分割的
– 2.第二范式，每一列的数据完全依赖主键（不可以部分依赖）
– 3.不可以传递依赖

– 事务范围内当作一件事情处理，要不都成功，要不都失败

BEGIN;
DELETE FROM student;
SELECT * FROM student;
-- 撤销之前的操作
ROLLBACK;

在MySQL中，select @@global.transaction_isolation;语句用于查询当前MySQL服务器的全局事务隔离级别设置。事务隔离级别决定了在多个事务并发执行时，一个事务能看到其他事务对数据库修改的程度。MySQL支持多种事务隔离级别，常见的包括：

READ UNCOMMITTED：最低的隔离级别，允许一个事务读取另一个事务未提交的数据变更。这可能导致脏读、不可重复读和幻读问题。

READ COMMITTED：允许一个事务读取另一个事务已经提交的数据变更。在这个级别下，避免了脏读问题，但可能会遇到不可重复读和幻读。

REPEATABLE READ：确保在同一事务中多次读取同样记录时，结果是一致的。可以避免脏读和不可重复读，但仍可能出现幻读。

SERIALIZABLE：最高的隔离级别，确保每个事务串行执行，避免了所有类型的并发问题，包括幻读。

select @@global.transaction_isolation;

脏读（Dirty Read）

脏读是指在数据库事务中，一个事务读取了另一个事务未提交的数据。也就是说，当你读取某个数据时，这个数据可能还没有被最终确定，它可能是临时或中间状态的数据。这种情况下，如果后续该事务回滚，那么读取到的数据就是无效的。脏读违反了事务的隔离性原则，可能会导致数据不一致。

幻读（Phantom Read）

幻读是指在事务执行过程中，某个事务读取了一组记录，而在读取过程中，另一个事务插入了一些新的记录或者删除了一些记录，导致原始事务读取到的记录集发生了变化。这种现象就像是在原始事务的读取过程中，出现了一些“幽灵”记录一样，因此称为幻读。幻读同样违反了事务的隔离性原则。

不可重复读（Non-Repeatable Read）

不可重复读是指在同一个事务中，多次读取同一数据时，得到的结果不一致。这种情况通常发生在以下两种情况下：

一个事务在读取某些记录后，另一个事务对这些记录进行了更新，导致原始事务再次读取时数据发生了变化。
一个事务在读取某些记录后，另一个事务对这些记录进行了删除操作，导致原始事务再次读取时数据不存在了

ACID特性

是数据库事务处理的基本属性，用来确保数据的完整性和一致性。这四个特性分别是：

原子性（Atomicity）

原子性确保事务（transaction）中的所有操作要么全部成功，要么全部失败。换句话说，一个事务中的所有操作作为一个整体执行，如果某个操作失败，整个事务都会回滚，确保数据库状态不会因为部分操作的成功而部分改变。例如，当你在银行账户中进行转账操作时，如果转账失败，那么账户的余额应该不会因为这个操作而改变，即转账的增加和减少应该被视为一个不可分割的单位。

一致性（Consistency）

一致性指的是事务执行前后，数据库的状态必须从一个合法状态转变到另一个合法状态。在事务开始前，数据库满足某些特定的约束，事务执行后，这些约束仍然得到满足。例如，如果一个数据库表中记录了所有书籍的库存数量，那么在执行任何更新库存数量的事务后，库存数量必须保持非负，并且总数不会减少。

隔离性（Isolation）

隔离性确保事务的执行不会被其他并发事务的影响。换句话说，事务在执行时，可以看到其他事务在执行前的数据状态。这可以通过不同的隔离级别（如读未提交、读已提交、可重复读、串行化）来实现，以减少并发操作带来的数据不一致性。例如，在“可重复读”隔离级别下，同一个事务在不同时间读取同一数据时，看到的数据应该是相同的。

持久性（Durability）

持久性确保一旦事务提交，其对数据库的影响将永久保存，即使在系统故障或重启后仍然有效。这意味着数据的更改在磁盘上的数据库文件中是持久的，不会因为系统崩溃而丢失。例如，当你在数据库中插入一条记录后，即使数据库服务突然关闭，这条记录仍然会存在于数据库中，直到被明确删除或被新的事务覆盖。

JDBC（Java Database Connectivity）

JDBC链接数据库的六个步骤
1>加载驱动
2>创建链接
3>获取执行对象
4>执行SQL语句
5>处理结果集
6>关闭链接

public class JDBC {public static void main(String[] args) {//1.JDBC链接数据库的六个步骤String url="jdbc:mysql://localhost:3306/easydata";String username="root";String password="123456";String driverClassName="com.mysql.cj.jdbc.Driver";//1>加载驱动try {Class.forName(driverClassName);} catch (ClassNotFoundException e) {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=14");//5>处理结果集if(rowCount>0) {System.out.println("删除成功");}else {System.out.println("删除失败");}} catch (SQLException e) {e.printStackTrace();}finally {//6>关闭链接if(sta!=null) {try {sta.close();} catch (SQLException e) {e.printStackTrace();}}if(con!=null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}}
}