MySQL学习3

数据类型(常用数据类型)

整数类型

tinyint

默认长度是4，共占1个字节，无符号的范围是：0~255(即：2^8-1），有符号的范围是：-128（即：-（2^7-1+1））~127（即：2^7-1）的整型数据。

smallint

默认长度是6，共占2个字节，无符号的范围是：0~65535（即：2^16-1），有符号的范围是：-32768（即：-（2^15-1+1））~32767（即：2^15-1）的整型数据。

mediumint

默认长度是9，共占3个字节，无符号的范围是：0~16777215（即：2^24-1），有符号的范围是：-8388608（即：-（2^23-1+1））~8388607（即：2^23-1）的整型数据。

int

默认长度是11，共占4个字节，无符号的范围是：0~4294967295（即：2^32-1），有符号的范围是：-2147483648（即：-（2^31-1+1））~2147483647（即：2^31-1）的整型数据。

bigint

默认长度是20，共占8个字节，无符号的范围是：0~18446744073709551615，有符号的范围是：-9223372036854775808（即：-（2^63-1+1））~9223372036854775807（即：2^63-1）的整型数据。

 浮点类型

单精度浮点数 float(M,D)

使用4个字节; 这里，M称为精度，D称为标度。(M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。

双精度浮点数 double(M,D)

使用8个字节; 这里，M称为精度，D称为标度。(M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。

精准数据类型 decimail(M,D)

使用M+2个字节; M是数字的最大数（精度）。其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254），M 的默认值是10。D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。

字符串

定长字符串char

最大长度n是255（即：2^8-1），mysql5.0.3之前n表示字节数，mysql5.0.3之后n表示字符数，实际占的字节数跟字符编码有关；

变长字符串varchar

最大长度n与最大行宽65535有关，表中所有非大字段的字节总数不能超过65535-1-2=65532。mysql5.0.3之前n表示字节数，mysql5.0.3之后n表示字符数。占的字节数跟字符编码有关；

-- varchar能够存储的字节数是65535
  -- 1. 结构问题varchar类型的第一个字节不存储数据
  -- 2. varchar前两个字节存储数据长度
  -- 3. 有效位就剩下65532, 编码格式决定能存储多少个字符
  -- 4. 行中列的总字节长度不能超过65535
  -- 如果要存储长文本  使用text类型

char和varcher的区别

  -- 1. char是定长的,varchar是变长的
  -- char(20)  存了  abc  占20个字符位
  -- varchar(20)  变长的  abc  占3个字符位
  -- 2. char的性能更好一些  varchar稍逊, 因为要计算字符数
  -- 3. 场景: 身份证号  学号  手机号  订单号  用char
    -- 介绍信息  点名  姓名  地址  用varchar 

text  长文本  不需要设置长度

日期

time

        表示时分秒，显示和检索格式是HH:mm:ss，可表示 00:00:00~23:59:59 之间的时间，占3个字节。

date

        表示年月日，显示和检索格式是YYYY-MM-DD，占3个字节。

year

        表示年，显示和检索格式是YYYY。

datetime

        表示年月日时分秒，显示和检索格式是YYYY-MM-DD hh:mm:ss，可表示 1000-01-01 00:00:00~9999-12-31 23:59:59之间的时间，共占用8个字节。

视图

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

创建视图

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 
BEGIN
	update log set val=(select count(*) from student) where `key`='studentcount';
end;

 在student表中添加数据后log表中: 

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;

在student表中改变数据后log表中: 

 尽量不要使用触发器, 会影响正常业务逻辑; 通常使用Java逻辑代码完成触发器工作

函数

自定义函数

定义: 

set global log_bin_trust_function_creators=TRUE;

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(score) FROM sc;

结果: 

MySQL字符串常用函数

1. 连接字符串：‌

   CONCAT() 函数用于连接两个或多个字符串。‌它接受一个或多个字符串参数，‌并将它们连接在一起。‌如果只提供一个参数，‌它将返回该参数的值。‌CONCAT_WS() 函数是 CONCAT() 的特殊形式，‌它允许你指定一个分隔符来连接字符串。‌

2. 提取子字符串：‌

   SUBSTRING() 或 SUBSTR() 函数用于从字符串中提取子字符串。‌它接受三个参数：‌原始字符串、‌开始位置和结束位置。‌

3. 替换字符串：‌

   REPLACE() 函数用于替换字符串中的某个子字符串。‌它接受三个参数：‌原始字符串、‌要查找的子字符串和要替换为的新字符串。‌

4. 转换字符串大小写：‌

   UPPER(str) 和 LOWER(str) 函数分别用于将字符串转换为大写和小写。‌

5. 查找子字符串位置：‌

   LOCATE() 和 INSTR() 函数用于查找子字符串在原始字符串中的位置。‌如果找到子字符串，‌它将返回子字符串的第一个字符在原始字符串中的位置；‌否则，‌返回0。‌

6. 获取字符串长度：‌

   LENGTH() 函数返回字符串的长度。‌

7. 删除字符串两端的空格：‌

   TRIM() 函数用于去除字符串两端的空格。‌

8. 其他有用的函数：‌

   • LEFT() 和 RIGHT() 函数分别用于提取字符串左侧或右侧的指定数量的字符。‌
   • CHAR_LENGTH() 函数返回字符串的长度（‌以字符为单位）‌。‌
   • BIT_LENGTH() 函数返回二进制字符串的长度。‌
   • ASCII() 函数返回字符串最左字符的ASCII码值。‌
   • BIN() 函数将数字转换为二进制字符串表示形式。‌

 MySQL数值常用函数

1. ABS(X): 返回X的绝对值。‌
2. FLOOR(X): 返回不大于X的最大整数。‌
3. CEIL(X) / CEILING(X): 返回不小于X的最小整数。‌
4. TRUNCATE(X, D): 返回数值X保留到小数点后D位的值，‌截断时不进行四舍五入。‌
5. ROUND(X): 返回离X最近的整数，‌截断时要进行四舍五入。‌
6. ROUND(X, D): 保留X小数点后D位的值，‌截断时要进行四舍五入。‌
7. RAND(): 返回0~1的随机数。‌
8. SIGN(X): 返回X的符号（‌负数、‌零或正数）‌对应-1、‌0或1。‌
9. PI(): 返回圆周率的值，‌默认显示小数位数为7位。‌
10. POW(x, y) / POWER(x, y): 返回x的y次乘方的值。‌
11. SQRT(x): 返回非负数的x的二次方根。‌
12. EXP(x): 返回e的x乘方后的值。‌
13. MOD(N, M): 返回N除以M以后的余数。‌
14. LOG(x): 返回x的自然对数。‌
15. LOG10(x): 返回x的基数为10的对数。‌
16. RADIANS(x): 返回x由角度转化为弧度的值。‌
17. DEGREES(x): 返回x由弧度转化为角度的值。‌
18. SIN(x) / ASIN(x): 前者返回x的正弦，‌后者返回x的反正弦值。‌
19. COS(x) / ACOS(x): 前者返回x的余弦，‌后者返回x的反余弦值。‌

 MySQL日期类型函数

1. 获取当前日期和时间：‌

   • NOW()：‌返回当前的日期和时间，‌包含年、‌月、‌日、‌时、‌分、‌秒。‌
   • CURDATE()：‌返回当前日期，‌只包含年、‌月、‌日部分。‌
   • CURTIME()：‌返回当前时间，‌只包含时、‌分、‌秒部分。‌

2. 日期与时间戳的转化：‌

   • UNIX_TIMESTAMP(date)：‌将日期转换为UNIX时间戳。‌
   • FROM_UNIXTIME(timestamp)：‌将UNIX时间戳转化为日期时间，‌与UNIX_TIMESTAMP(date)函数互为反函数。‌

3. 提取日期的特定部分：‌

   • YEAR(date)、‌MONTH(date)、‌DAY(date)等：‌分别用于返回日期的年、‌月、‌日等特定部分。‌
   • EXTRACT(type FROM date)：‌返回指定日期中特定的部分，‌其中type可以是指定返回的值，‌如YEAR、‌MONTH等。‌

4. 日期计算：‌

   • DATEDIFF(date1, date2)：‌计算两个日期之间相差的天数。‌
   • LAST_DAY(date)：‌返回日期所在月份的最后一天。‌
   • 使用INTERVAL进行日期的增加或减少，‌如date_add(now(), INTERVAL 1 DAY)表示在当前时间上增加一天。‌

5. 日期格式化：‌

   • DATE_FORMAT(date, format)：‌按指定格式格式化日期。‌
   • STR_TO_DATE(string, format)：‌将字符串转换为日期，‌需要指定字符串的格式。‌

可使用的格式有: 

  存储过程

        存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库sql语言层面的代码封装与重用。 

存储过程语法: 

DELIMITER //  

CREATE PROCEDURE CalculateSquare(IN num INT, OUT result INT)  
BEGIN  
    SET result = num * num;  
END //  

DELIMITER ;

         DELIMITER 用于更改命令结束符，以便在存储过程中使用 BEGIN … END 语句。通常，我们使用 // 作为新的结束符，并在存储过程定义结束后将其改回。

调用存储过程需要使用call语句: 

SET @input = 5;  
SET @output = 0;  
  
CALL CalculateSquare(@input, @output);  
  
SELECT @output;  -- 输出应该是 25

-- 存储过程 PROCEDURE
create PROCEDURE pro_insert_student_log(in num int)
BEGIN
-- 循环语句 loop 死循环 leave;
  declare i int default 0;
  declare stuname varchar(20);
  declare randomS int;
  aa: LOOP
	-- 获取学生的名字，生成随机成绩 插入log表
	  select sname into stuname from student limit i,1;
	  set randomS = FLOOR((RAND()*100));
	  insert into log value(stuname,randomS);
	  set i = i + 1; 
	  IF i >= num THEN
		  LEAVE aa; 
	  END IF; 
  END LOOP aa;
END;

三大范式

        MySQL中的三大范式包括‌第一范式（1NF）、‌第二范式（2NF）和‌第三范式（3NF），它们是数据库设计的基本原则，用于确保数据库的结构具有良好的数据完整性和避免数据冗余。具体介绍如下：‌

        第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。这意味着列中存储的数据应该是最小的、不可再分的单位，不能是集合、数组或其他复杂的数据结构。
        第二范式（2NF）：在满足第一范式的基础上，要求非主键列完全依赖于主键。这意味着表中的所有列都必须完全通过主键来定义，不能有仅依赖于主键一部分的列。
        第三范式（3NF）：在第二范式的基础上，进一步要求非主键列不依赖于其他非主键列。这意味着表中的每一列都应该是直接依赖于主键的，不应该存在数据冗余和不必要的数据依赖。
        这些范式是逐步递进的，第二范式在第一范式的基础上建立，第三范式在第二范式的基础上建立。遵循这些范式可以帮助设计出结构清晰、易于维护的数据库结构。‌

        数据库三大范式只是数据库表设计时的理论依据，最终的目的是为了满足客户的需求，有时候会选择用数据库表的冗余来换取执行速度，因为在查询过程中，多张表的连接会降低查询的效率。

事务

事务: 一组不可分割的数据库操作, 要么全执行, 要么全不执行; 例如: 银行卡转帐 

ACID四大特性

        ACID四大特性是指数据库事务的四个关键属性，包括‌原子性（Atomicity）、‌一致性（Consistency）、‌隔离性（Isolation）和‌持久性（Durability）。‌

        原子性（Atomicity）：原子性指事务是一个不可分割的工作单位，事务中的操作要么全部执行，要么全部不执行。这确保了事务的完整性，防止了部分操作被执行而导致的数据不一致情况。

        一致性（Consistency）：一致性指事务必须使数据库从一个一致性状态变换到另一个一致性状态。也就是说，一个事务执行前后，数据库都必须保持一致性的状态。这保证了数据的正确性和可靠性。

        隔离性（Isolation）：隔离性指多个事务并发执行时，一个事务的执行不应影响其他事务的执行，各个事务之间互不干扰，相互独立。这防止了多个事务并发执行时产生的数据冲突和不一致问题。

        持久性（Durability）：持久性指一旦事务提交，则其结果就是永久性的，即使发生系统崩溃或宕机等故障，数据库也能将数据恢复到提交后的状态，从而保证了数据的稳定性和安全性。

事务并发访问可能造成的数据混乱

脏读

        脏读是指在一个事务处理过程里读取了另一个未提交事务中的数据。

        例如:数据表中一条记录值为v1, 事务A将值改为v2, 但并没有提交, 此时事务B读取, 如果读取到的记录值为v2, 则为脏读

不可重复读

        不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

        例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

        不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

        在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……

幻读(虚读)

        事务非独立执行时发生的一种现象。

        幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

        幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

事务隔离级别

隔离级别	脏读	不可重复读	幻读
读未提交(Read uncommitted)	是	是	是
读已提交(Read committed)	否	是	是
可重复读(Repeatable read)	否	否	是
串行化(Serializable)	否	否	否

MySQL默认的事务隔离级别是可重复读

JDBC连接数据库

        Java数据库连接, （Java Database Connectivity, 简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口, 提供了诸如查询和更新数据库中数据的方法。

 JDBC连接数据库的六个步骤

1.  加载驱动类
2.  创建连接
3.  获取执行对象
4.  执行sql语句
5.  处理结果集
6.  关闭连接

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) {
        //JDBC连接数据库的六个步骤
        //url: jdbc:mysql://地址:端口号/数据库名
        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) {
            throw new RuntimeException(e);
        }
        Connection con=null;
        Statement sta=null;
        try {
            //2>创建连接
            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) {
            throw new RuntimeException(e);
        }finally{
            //6>关闭连接
            if(sta!=null){
                try {
                    sta.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            //使用try-with-resource可省略此步
            /*
             * if(statement != null) { statement.close(); }
             * if(connection != null) { connection.close(); }
             */
        }
    }
}