本文主要是介绍Day33,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
Day33
SQL-续
数据类型
tinyint、int、unsigned、float、double、decimal、char、varchar、BLOB、LONGBLOB、TEXT、LONGTEXT、date、time、datetime、timestamp、year
# 数据类型# 整数类型 ------------------------------------------------------------- # tinyint - 1字节 # int - 4字节 # 经验:存储状态码可以选择tinyint,没有特殊要求存储整数可以选择int# INT(3):取值范围是-2147483648~2147483647,数据不满3位就用0填充 # unsigned - 无符号(不满存负数) # zerofill - 0填充 CREATE TABLE user (id int(3) PRIMARY KEY auto_increment,name varchar(3) ,age int(3) unsigned zerofill )# 浮点类型 ------------------------------------------------------------- # FLOAT # DOUBLE # DECIMAL:以字符串形式存储的小数 # 经验:因为FLOAT和DOUBLE可能会失去精度,所以小数一般使用DECIMAL# 字符串类型 -------------------------------------------------------------# CHAR(32):定长字符串,比如:存储"abc"会开辟32个字符的空间存储数据 # VARCHAR(32):变长字符串,比如:存储"abc"会开辟3个字符的空间存储数据 # 32:MySQL5.0之前为字节单位,之后为字符单位# BLOB:存储二进制数据 # LONGBLOB:存储极长的二进制数据 # 经验:存储二进制数据的技术叫做BLOB,就说明MySQL可以存储二进制文件(音频、视频、图片...)# TEXT:存储纯文本数据 # LONGTEXT:存储极长的纯文本数据 # 经验:存储纯文本数据的技术叫做CLOB,就说明MySQL可以存储纯文本文件# 字符串类型 -------------------------------------------------------------# date : 2024-06-05 # time : 09:46:30 # datetime :2024-06-05 09:46:30 # timestamp:时间戳 # year : 2024# UPDATE CURRENT_TIMESTAMP - 数据行修改数据后,自定更新时间戳 CREATE TABLE user (id int(3) PRIMARY KEY AUTO_INCREMENT,name varchar(3) COLLATE utf8mb4_general_ci DEFAULT NULL,age int(3) unsigned zerofill DEFAULT NULL,xxx timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, )
约束
理解:对于数据的限制
# 约束 # 理解:对于数据的限制# 主键约束 ---------------------------------------------------------------------------------- # 理解:唯一约束+非空约束,该列上的数据不能重复,且不能为null# 添加主键约束 - 方式一 CREATE TABLE user(username VARCHAR(32) PRIMARY KEY,password VARCHAR(32),name VARCHAR(32),sex VARCHAR(32),age INT(3),phone VARCHAR(32) )# 添加主键约束 - 方式二 CREATE TABLE user(username VARCHAR(32),password VARCHAR(32),name VARCHAR(32),sex VARCHAR(32),age INT(3),PRIMARY KEY(username,password) )# 删除主键约束 ALTER TABLE user DROP PRIMARY KEY;# 唯一约束 ---------------------------------------------------------------------------------- # 理解:该列上的数据不能重复,但可以为null# 添加唯一约束 ALTER TABLE user ADD UNIQUE(phone);# 删除唯一约束 ALTER TABLE user DROP INDEX phone;# 非空约束 ---------------------------------------------------------------------------------- # 理解:该列上的数据不能为null,但可以重复# 添加非空约束 ALTER TABLE user MODIFY name VARCHAR(32) NOT NULL;# 删除非空约束 ALTER TABLE user MODIFY name VARCHAR(32) NULL;# 添加默认值 ALTER TABLE user MODIFY password VARCHAR(32) DEFAULT("000000");# 外键约束 ---------------------------------------------------------------------------------- # 理解:约束两个字段的参照完整性# 添加外键约束 ALTER TABLE user ADD FOREIGN KEY(t_id) REFERENCES type(id); # 删除外键约束(外键名使用show create table user查询) ALTER TABLE USER DROP FOREIGN KEY user_ibfk_1; # 经验:在项目中不使用外键约束,因为效率低
索引
主键索引、唯一索引、普通索引、全文索引、面试题
# 索引 # 理解:索引类似于一本书的目录,让查询更快# 主键索引 ---------------------------------------------------------------------------------- # 理解:主键作为约束,让字段上的数据不能重复且不能为空;主键作为索引,字段作为查询条件会更快# 添加主键索引 - 方式一 CREATE TABLE user(username VARCHAR(32) PRIMARY KEY,password VARCHAR(32),name VARCHAR(32),sex VARCHAR(32),age INT(3),phone VARCHAR(32) )# 添加主键索引 - 方式二 CREATE TABLE user(username VARCHAR(32),password VARCHAR(32),name VARCHAR(32),sex VARCHAR(32),age INT(3),PRIMARY KEY(username,password) )# 删除主键索引 alter table user drop primary key;# 唯一索引 ---------------------------------------------------------------------------------- # 理解:唯一作为约束,让字段上的数据不能重复;唯一作为索引,字段作为查询条件会更快# 添加唯一索引 alter table user add unique(phone);# 删除唯一索引 alter table user drop index phone;# 普通索引 ---------------------------------------------------------------------------------- # 理解:普通索引对于数据没有约束,作为索引,字段作为查询条件会更快# 添加普通索引 ALTER TABLE user ADD INDEX(name);# 删除普通索引 drop index name on user;# 全文索引 ----------------------------------------------------------------------------------# 添加全文索引 # ngram - 解析器(中文、日文、函数) ALTER TABLE news ADD FULLTEXT(info) WITH PARSER ngram;# 查询数据 SELECT * FROM news WHERE MATCH(info) against('胡歌很帅');# 删除全文索引 drop index info on news;# ----------------------------------------------------------------------------------# 索引的数据结构? # B+Tree# 索引的优缺点? # 优点:索引字段作为查询条件会更快 # 缺点:添加、删除数据时,索引结构会发生改变 # 解决方案:少量的添加和删除就不管,批量添加和删除之前把索引删除掉,等操作完成之后再加上索引# 索引的分类 # 聚簇索引/聚集索引:主键索引 # 非聚簇索引/非聚集索引:唯一索引、普通索引、全文索引# 聚簇索引 和 非聚簇索引的区别 # 聚簇索引:叶子节点存储的是数据行(效率高) # 非聚簇索引:叶子节点存储的是数据行的地址# 索引失效的失效的情况 # 1.最短路径算法 # 2.最左匹配原则 # 3.OR # 4.模糊查询 # 5.函数 # 6. ...
视图
# 视图 # 理解:虚拟表# 创建虚拟表 # or replace - 视图名重复就替换 # with check option - 修改视图数据如果违反条件,就拒绝 create or replace view view01 as select phone,province,city,money from user where money>1000 with check option;# 查询视图数据 SELECT * FROM view01;# 修改视图数据 # 注意:修改视图数据,原表也会发生改变 UPDATE view01 SET money=2000 WHERE phone='15879090131'; UPDATE view01 SET money= 500 WHERE phone='15879090131';# 删除视图 DROP view view01;
触发器
# 触发器# 创建场景 --------------------------------------------------------------------------# 创建学科表,并添加数据 CREATE TABLE course(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32),sum INT(3) DEFAULT 0 ) INSERT INTO course(name) VALUES('JavaEE'); INSERT INTO course(name) VALUES('Python'); INSERT INTO course(name) VALUES('HTML');# 创建班级表,并添加数据 CREATE TABLE class(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32),sum INT(3) DEFAULT 0 ) INSERT INTO class(name) VALUES('2402JavaEE'); INSERT INTO class(name) VALUES('2403JavaEE'); INSERT INTO class(name) VALUES('2401Python'); INSERT INTO class(name) VALUES('2402Python'); INSERT INTO class(name) VALUES('2401HTML'); INSERT INTO class(name) VALUES('2402HTML');# 添加学生表 CREATE TABLE student(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32),course_id INT(3),class_id INT(3) )# 需求1 -------------------------------------------------------------------------- # 需求:添加学生,并更新对应的学科和班级的总人数# 创建触发器:当学生表添加数据之后触发,更新对应的学科和班级的总人数 # AFTER - 后置触发 # delimiter xx - 设置sql命令的结束符 # new - 获取新增数据delimiter xx CREATE TRIGGER tri01 AFTER INSERT ON student FOR EACH ROWBEGINUPDATE course SET sum=sum+1 WHERE id=new.course_id;UPDATE class SET sum=sum+1 WHERE id=new.class_id;END xx delimiter ;INSERT INTO student(name,course_id,class_id) VALUES('张三',1,1); INSERT INTO student(name,course_id,class_id) VALUES('李四',1,1); INSERT INTO student(name,course_id,class_id) VALUES('王五',1,2); INSERT INTO student(name,course_id,class_id) VALUES('赵六',2,3); INSERT INTO student(name,course_id,class_id) VALUES('小明',2,4); INSERT INTO student(name,course_id,class_id) VALUES('李华',3,5);# 需求2 -------------------------------------------------------------------------- # 需求:删除学生,并更新对应的学科和班级的总人数 # BEFORE - 前置触发 # old - 获取原有数据 delimiter xx CREATE TRIGGER tri02 BEFORE DELETE ON student FOR EACH ROWBEGINUPDATE course SET sum=sum-1 WHERE id=old.course_id;UPDATE class SET sum=sum-1 WHERE id=old.class_id;END xx delimiter ;DELETE FROM student WHERE id=1;# 删除触发器 -------------------------------------------------------------------------- DROP TRIGGER tri01; DROP TRIGGER tri02;
存储过程
理解:存储过程类似于Java里的方法
区别:
Java里的方法有参数和返回值,参数-将方法外界的数据传入到方法内部,返回值-将方法里的数据返回给外界
存储过程没有返回值,但是有传入、传出、传入传出参数,也就是说参数有3个方向
存储过程的参数分类:
in - 传入
out - 传出
inout - 传入传出
1.无传入传出:
# 最简单的存储过程 delimiter xx CREATE PROCEDURE pro01()BEGINSELECT * FROM student;SELECT * FROM class;SELECT * FROM course;END xx delimiter ;# 调用存储过程 CALL pro01();
2.有传入
# 需求:传入学生的id,查询出学生的姓名 delimiter xx CREATE PROCEDURE pro02(IN s_id INT(3))BEGINSELECT name FROM student WHERE id=s_id;END xx delimiter ;# 调用存储过程 CALL pro02(2);
3.有传出
# 需求:传入学生的id,返回出学生的姓名 delimiter xx CREATE PROCEDURE pro03(IN s_id INT(3),OUT s_name VARCHAR(32))BEGINSELECT name INTO s_name FROM student WHERE id=s_id;END xx delimiter ;# 调用存储过程 CALL pro03(2,@s_name); SELECT @s_name;
4.传入传出(当传入和传出的数据类型一致时)
# 需求:传入学生的id,返回出学生的年龄 delimiter xx CREATE PROCEDURE pro04(INOUT param INT(3))BEGINSELECT age INTO param FROM student WHERE id=param;END xx delimiter ;# 调用存储过程 SET @param=2; CALL pro04(@param); SELECT @param;
删除存储过程
DROP PROCEDURE pro01; DROP PROCEDURE pro02; DROP PROCEDURE pro03; DROP PROCEDURE pro04;
函数
预定义函数
# 预定义函数# 数学类函数 ---------------------------------------------------------------- SELECT ABS(-10);# 求绝对值 SELECT pow(2,3);# 求次方 SELECT SQRT(9);# 求平方根 SELECT MOD(10,3);# 求余数 SELECT RAND();# 求随机数# 字符串类函数 ---------------------------------------------------------------- SELECT CONCAT("用良心做教育","做真实的自己");# 拼接 SELECT SUBSTRING("用良心做教育",2,2);# 截取(目标字符串,第几个字符,截取长度) SELECT LENGTH("用良心做教育");# 获取长度(字节)# 日期时间类函数 ---------------------------------------------------------------- SELECT NOW(); SELECT YEAR(now()); SELECT MONTH(now()); SELECT DAYOFMONTH(now()); SELECT HOUR(now()); SELECT MINUTE(now()); SELECT SECOND(now()); SELECT DATE_FORMAT(now(),"%Y年%m月%d日 %H时%i分%s秒");
自定义函数
# 自定义函数 # 注意:MySQL的函数必须有返回值# 需求:创建函数,传入学生id,返回学生的姓名 delimiter xx CREATE FUNCTION fun(s_id INT(3)) RETURNS VARCHAR(32) DETERMINISTICBEGINDECLARE s_name VARCHAR(32);SELECT name INTO s_name FROM student WHERE id=s_id;RETURN s_name;END xx delimiter ;# 调用函数 SELECT fun(2);# 删除函数 DROP FUNCTION fun;
初识JDBC
JDBC:java database connectivity
SUN公司提供的一套操作数据库的标准规范。
JDBC与数据库驱动的关系:接口与实现的关系。
JDBC的四大金刚
DriverManager:用于注册驱动
Connection: 表示与数据库创建的连接
Statement: 操作数据库sql语句的对象
4.ResultSet: sql语句执行后返回的结果集
JDBC操作步骤
加载驱动
创建连接对象
创建Statement对象
通过Statement对象执行sql语句
获取执行结果
关闭资源
package com.qf.jdbc;import org.junit.Test;import java.sql.*;/*** 知识点:JDBC*/ public class Test01 {//增@Testpublic void test01() throws ClassNotFoundException, SQLException {//导入驱动包Class.forName("com.mysql.cj.jdbc.Driver");//获取连接对象String url="jdbc:mysql://localhost:3306/2042javaee?characterEncoding=utf8&serverTimezone=UTC";String username="root";String password="123456";Connection connection = DriverManager.getConnection(url, username, password);//获取发送sql指令对象Statement statement = connection.createStatement();//发送sql指令String sql="insert into student(name,sex,salary,age,course) values('小红','女',9000,22,'Java');";int num = statement.executeUpdate(sql);System.out.println("对"+num+"行产生了影响");//关闭资源statement.close();connection.close();}//删@Testpublic void test02() throws ClassNotFoundException, SQLException {//导入驱动包Class.forName("com.mysql.cj.jdbc.Driver");//获取连接对象String url="jdbc:mysql://localhost:3306/2042javaee?characterEncoding=utf8&serverTimezone=UTC";String username="root";String password="123456";Connection connection = DriverManager.getConnection(url,username,password);//获取发送sql指令对象Statement statement = connection.createStatement();//发送sql指令String sql="delete from student where id>8;";int num = statement.executeUpdate(sql);System.out.println("对"+num+"行产生了影响");//关闭资源statement.close();connection.close();}//改@Testpublic void test03() throws ClassNotFoundException, SQLException {//导入驱动包Class.forName("com.mysql.cj.jdbc.Driver");//获取连接对象String url="jdbc:mysql://localhost:3306/2042javaee?characterEncoding=utf8&serverTimezone=UTC";String username="root";String password="123456";Connection connection = DriverManager.getConnection(url, username, password);//获取发送sql指令对象Statement statement = connection.createStatement();//发送sql指令String sql="update student set salary=20000 where id=1;";int num = statement.executeUpdate(sql);System.out.println("对"+num+"行产生了影响");//关闭资源statement.close();connection.close();}//查@Testpublic void test04() throws ClassNotFoundException, SQLException {//导入驱动包Class.forName("com.mysql.cj.jdbc.Driver");//获取连接对象String url="jdbc:mysql://localhost:3306/2042javaee?characterEncoding=utf8&serverTimezone=UTC";String username="root";String password="123456";Connection connection = DriverManager.getConnection(url, username, password);//获取发送sql指令对象Statement statement = connection.createStatement();//发送sql指令String sql="select * from student;";ResultSet resultSet = statement.executeQuery(sql);//遍历结果集while(resultSet.next()){int id = resultSet.getInt("id");String name = resultSet.getString("name");String sex = resultSet.getString("sex");double salary = resultSet.getDouble("salary");String course = resultSet.getString("course");System.out.println(id+"--"+name+"--"+sex+"--"+salary+"--"+course);}//关闭资源statement.close();connection.close();} }
这篇关于Day33的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!