SQL—MySQL基础教程（新手向）
<svg xmlns="http://www.w3.org/2000/svg" style="display: none">
<path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0)"></path>
</svg>
<p></p>
SQL—MySQL 基础教程（新手向）#
前言
文章主要讲述数据库相关概念，MySQL 的基本使用。我觉得这些都是 MySQL 中比较重要且精华的部分，有些没有提到的点都是基本不会用到的（当然了，也可能是我菜，根本没学）
一、数据库
1. 概念#
数据库：(DB,DataBase)：安装在操作系统之（windows，Linux，mac）上的数据仓库软件。可以存储大量的数据
2. 分类#
- 关系型数据库：(SQL)：
- MySQL, Oracle, SQL Server, DB2, SQLite
- 通过表和表之间，行和列之间的关系进行数据的存储 - 非关系型数据库：(NoSQL) Not Only SQL
- Redis, MongDB
- 非关系型数据库，对象存储，通过对象自身的属性来决定。
二、MySQL 基本概念
1. 概念#
MySQL 是一个关系型数据库管理系统
2. 安装#
这里给出 官方下载网址
（建议不建议下载压缩包，有点麻烦）
跟着百度下载教程走就好了
3. 启动关闭连接#
安装完成之后记得配置环境变量，然后 cmd
net start -- 查看 mysql80 是否开启
net start mysql80 -- 开启
net stop mysql80 -- 关闭
mysql -u 用户名 -p密码 -- 连接数据库
quit -- 退出数据库
4. 操作数据库#
数据库里的操作是大小写不敏感的
-- 创建数据库
CREATE DATABASE IF NOT EXISTS mysql01;
-- 删除数据库
DROP DATABASE IF EXISTS mysql01;
-- 使用数据库
USE mysql01;
-- 查看数据库
SHOW DATABASES;
5. 变量类型#
- 数值：
- int：标准的整数 4 字节
- float：浮点数 4 字节
- double：浮点数 8 字节 - 字符串：
- char：字符串长度大小固定 0~55
- varchar：可变字符串 0~65535
- text：文本串 2^16-1 - 时间：
- data：YYYY-MM-DD
- time：HH🇲🇲ss
- datatime：YYYY-MM-DD HH🇲🇲ss
- timestamp：时间戳 1970.1.1 到现在的毫秒
6. 字段属性#
- 每个表都必须存在五个字段：
- id 主键
- version 乐观锁
- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间） - unsigned：无符号整数
- zerofill：int(3) 1–>001
- auto_increment：自增，自动在上一条记录的基础上 +1。可以设置起始值和步长。通常用来设计主键
- NULL not null：非空
7. 表#
- 数据表引擎（ENGINE）：INNODB 和 MYISAM（了解）
- 创建表
-- 格式 CREATE TABLE [IF NOT EXISTS] `表名`( `字段名` 列类型 [属性] [索引] [注释], `字段名` 列类型 [属性] [索引] [注释], PRIMARY KEY(`id`) )[表类型][表字符集设置][注释]-- 举例
CREATE TABLE IF NOT EXISTSstudent(idINT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',nameVARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看 student 数据表的定义语句
DESC student -- 显示表的结构 - 修改表
-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列类型
ALTER TABLE teacher ADD age INT(11)-- 修改表的字段
-- ALTER TABLE 表名 MODIFY 字段名 列类型 []
ALTER TABLE teacher1 MODIFY age VARCHAR(10)-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列类型 []
ALTER TABLE teacher1 CHANGE age age1 INT(1)-- 修改表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1-- 删除表的字段：
三、MySQL 数据管理
1.CRUD#
-
insert
-- 插入语句（添加）-- -- insert into ` 表名 ` (` 字段 1`,` 字段 2`,` 字段 3`) values('值 1'),('值 2'),('值 3') INSERT INTO `user` (`id`) VALUES ('1'),('2');-- 匹配一个字段, 插入两个值 INSERT INTO `user` (`name`,`id`,`pwd`) VALUES('a','1','a');-- 匹配多个字段, 插入一个值 INSERT INTO `user` (`name`,`id`,`pwd`) VALUES('a','1','a'),('b','2','b');-- 匹配多个字段, 插入多个值 -
update
-- UPDATE ` 表名 ` SET ` 字段名 `='value',[` 字段名 `='value',...] where [条件] UPDATE `user` SET `pwd`='a' WHERE `name` = 'a'; -
delete
-- delete from 表名 [where 条件] DELETE FROM `user` WHERE id=1; -
select
-- 查询指定的字段 SELECT 字段,[字段 2],[字段 3] FROM 表 -- 数据库的列可以是表达式：文本值，列，null，函数，计算表达式，系统变量… SELECT `id`+ 1 AS 编号,`name` AS 姓名 FROM user; -- 顺序 SELECT * FROM user; [left | right | inner join table_name] -- 联合查询 [where ...] -- 指定结果满足的条件 [group by ...] -- 指定结果按照哪几个字段来分组 [having] -- 过滤分组的记录必须满足的次要条件 [order by ...] -- 指定查询记录按一个或多个条件排序 [limit {[offset,]row_count | row_countOFFSET offset}];
2. 其他语法#
-
操作符
操作符 含义 范围 结果 = 相等 5=6 false <> 或!= 不等于 5<>6 true > 大于 5>6 false < 小于 5<6 true <= 小于等于 5<=6 true >= 大于等于 5>=6 false BETWEEN … AND … 在某个范围内，闭合区间 [2,5] true AND 相当于 && 5>1 and1>2 false OR 相当于 || 5>1 or 1>2 true -
运算符
运算符 语法 描述 is null a is null 如果操作符 null，则为真 is not null a is not null 如果操作符为 not null ，则为真 between a between b and c 若 a 在 b 和 c 之间，则为真 like a like b %(代表 0 到任意个字符) ，_(代表一个字符) in a in(a2,a3,a4…) 假设 a 在 a2,a3…其中的某一个值中，则结果为真 -
where
SELECT * FROM `user` where 'id' BETWEEN 1 AND 3 AND 'name' != 'a'; SELECT * FROM `user` where 'name' like '%a_' AND 'id' in (1,2,3); SELECT * FROM `user` where 'name' IS NOT NULL OR 1 = 1; -
join
分为三种操作：
- left join
- right join
- inner join每种操作都可以用 where 限制查询条件
-- 嵌套查询 SELECT s.`studentno`, `studentname` FROM `student` s INNER JOIN `result` r ON s.`studentno` = r.`studentno` INNER JOIN `subject` sub ON r.`subjectno` = sub.`subjectno` WHERE `SubjectName` = 'a' AND `studentresult` > 80 ORDER BY `studentresult` DESC;-- 子查询
SELECT s.studentno,studentname
FROMstudents
INNER JOIN<span class="hljs-keyword">result</span>r
ON s.studentno= r.studentno
WHEREstudentresult> 80
ANDsubjectno= (
SELECTsubjectno
FROMsubject
WHERESubjectName= 'a')
ORDER BYstudentresultDESC; -
order by
-- 升序 ASC(默认)，降序 DESC
SELECT FROM<span class="hljs-keyword">user</span>where 'id' BETWEEN 1 AND 5 ORDER BY 'id' DESC; -
limit
-- limit(查询的起始下标,pageSize)
SELECTFROM<span class="hljs-keyword">user</span>where 'id' IS NOT NULL limit 0,2; -
group by
SELECT * from user GROUP BY 'name';
3. 函数#
- 常用函数
大概了解一下就可以，不常用-- 数学运算 SELECT ABS(-8) -- 绝对值 SELECT CEILING(6.6) -- 向上取整 SELECT FLOOR(6.6) -- 向下取整 SELECT RAND() -- 返回一个 0~1 的随机数 SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回 -1 正数返回 1 -- 字符串函数 SELECT CHAR_LENGTH('length') -- 字符串长度 SELECT CONCAT('con','cat') -- 拼接字符串 SELECT INSERT() -- 从某个位置开始替换某个长度 SELECT LOWER() -- 转小写 SELECT UPPER() -- 转大写 SELECT INSTR() -- 返回字符第一次出现的索引 SELECT REPLACE() -- 替换出现的指定字符 SELECT SUBSTR() -- 返回指定的字符串 (原字符串、截取的位置、截取的长度) SELECT REVERSE() -- 反转字符串 -- 时间和日期函数 (记住) SELECT CURRENT_DATE() -- 获取当前日期 SELECT CURDATE() -- 获取当前日期 SELECT NOW() -- 获取当前时间 SELECT LOCALTIME() -- 本地时间 SELECT SYSDATE() -- 系统时间 SELECT YEAR(NOW) -- 获取当前日期的年份 SELECT SYSTEM_USER() -- 获取当前用户 SELECT USER()SELECT VERSION() - 聚合函数
SELECT COUNT(studentname) FROM student; -- count(指定字段)，会忽略 null 值 SELECT COUNT(*) FROM student; -- 不会忽略 null 值 SELECT COUNT(1) FROM result; -- 不会忽略 null 值 SELECT SUM(`studentresult`) AS 总和 FROM `result` SELECT AVG(`studentresult`) AS 平均分 FROM `result` SELECT MAX(`studentresult`) AS 最高分 FROM `result` SELECT MIN(`studentresult`) AS 最低分 FROM `result`
四、事务
将一组 SQL 放在一个批次中去执行，这些语句要么都成功，要么都失败
原则：
1. 原子性（atomicity)：要么都成功，要么都失败
2. 一致性（consistency)：事物前后得数据完整性要保证一致
3. 持久性（durability）：事物一旦提交则不可逆，被持久化到数据库中
4. 隔离性（isolation）：事物得隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干预，事务之间要相互隔离
五、三大范式
- 第一范式（1NF）
原子性：保证每一列不可再分 - 第二范式（2NF）
前提：满足第一范式
确保数据库中的每一列都和所有主键相关，而不能只与某一部分主键相关 - 第三范式（3NF）
前提：满足第一范式和第二范式
确保数据表中的每一列数据都和主键之间相关，而不能间接相关
根据阿里规范：关联查询的表不得超过三张表
考虑商业化的需求和目标，数据库的性能更加重要，我们有时会故意在表中增加一些冗余的字段来提高性能
六、JDBC
1. 概念#
早期由于有多个数据库厂家，而每个厂家的语法不同，对程序员造成了很大困扰。SUN 公司为了简化开发人员的（对数据库的统一）操作，提供了一个（Java 操作数据库的）规范，俗称 JDBC。各大数据库厂家只需要实现这个规范就可以了，而程序员只需要学习 JDBC 语法即可实现对所有数据库的操作。
2. 第一个 JDBC#
- 创建一个 maven 项目
- pom.xml 导入相关配置，这里只给出 jdbc 驱动配置
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion><span class="hljs-tag"><<span class="hljs-name">groupId</span>></span>org.example<span class="hljs-tag"></<span class="hljs-name">groupId</span>></span> <span class="hljs-tag"><<span class="hljs-name">artifactId</span>></span>JDBC_01<span class="hljs-tag"></<span class="hljs-name">artifactId</span>></span> <span class="hljs-tag"><<span class="hljs-name">version</span>></span>1.0-SNAPSHOT<span class="hljs-tag"></<span class="hljs-name">version</span>></span> <span class="hljs-tag"><<span class="hljs-name">dependencies</span>></span> <span class="hljs-tag"><<span class="hljs-name">dependency</span>></span> <span class="hljs-tag"><<span class="hljs-name">groupId</span>></span>mysql<span class="hljs-tag"></<span class="hljs-name">groupId</span>></span> <span class="hljs-tag"><<span class="hljs-name">artifactId</span>></span>mysql-connector-java<span class="hljs-tag"></<span class="hljs-name">artifactId</span>></span> <span class="hljs-tag"><<span class="hljs-name">version</span>></span>8.0.25<span class="hljs-tag"></<span class="hljs-name">version</span>></span> <span class="hljs-tag"></<span class="hljs-name">dependency</span>></span> <span class="hljs-tag"></<span class="hljs-name">dependencies</span>></span> <span class="hljs-tag"><<span class="hljs-name">properties</span>></span> <span class="hljs-tag"><<span class="hljs-name">maven.compiler.source</span>></span>16<span class="hljs-tag"></<span class="hljs-name">maven.compiler.source</span>></span> <span class="hljs-tag"><<span class="hljs-name">maven.compiler.target</span>></span>16<span class="hljs-tag"></<span class="hljs-name">maven.compiler.target</span>></span> <span class="hljs-tag"></<span class="hljs-name">properties</span>></span></project> - 在 resources 下创建 db.properties，作为连接数据库的资源文件（便于后期修改）
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/?useSSL=false&useUnicode=true&characterEncoding=UTF-8
user=root
password=**** - 创建 JDBC 工具类，方便连接数据库
package Utils;import java.io.InputStream;
import java.sql.*;
import java.util.Properties;@SuppressWarnings("all")
public class JDBCutil {
private static String driver;
private static String url;
private static String user;
private static String password;<span class="hljs-keyword">static</span> { <span class="hljs-keyword">try</span> { <span class="hljs-comment">//1.新建属性集对象</span> <span class="hljs-type">Properties</span> <span class="hljs-variable">properties</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Properties</span>(); <span class="hljs-comment">//2.通过反射，新建字符输入流，读取db.properties文件</span> <span class="hljs-type">InputStream</span> <span class="hljs-variable">input</span> <span class="hljs-operator">=</span> JDBCutil.class.getClassLoader().getResourceAsStream(<span class="hljs-string">"db.properties"</span>); <span class="hljs-comment">//3.将输入流中读取到的属性，加载到properties属性集对象中</span> properties.load(input); <span class="hljs-comment">//4.根据键，获取properties中对应的值</span> driver = properties.getProperty(<span class="hljs-string">"driver"</span>); url = properties.getProperty(<span class="hljs-string">"url"</span>); user = properties.getProperty(<span class="hljs-string">"user"</span>); password = properties.getProperty(<span class="hljs-string">"password"</span>); } <span class="hljs-keyword">catch</span> (Exception e) { e.printStackTrace(); } } <span class="hljs-comment">//获取连接</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> Connection <span class="hljs-title function_">getConnection</span><span class="hljs-params">()</span> { <span class="hljs-keyword">try</span> { <span class="hljs-comment">//注册数据库的驱动</span> Class.forName(driver); <span class="hljs-comment">//获取数据库连接</span> <span class="hljs-type">Connection</span> <span class="hljs-variable">connection</span> <span class="hljs-operator">=</span> DriverManager.getConnection(url, user, password); <span class="hljs-comment">//返回数据库连接</span> <span class="hljs-keyword">return</span> connection; } <span class="hljs-keyword">catch</span> (Exception e) { e.printStackTrace(); } <span class="hljs-keyword">return</span> <span class="hljs-literal">null</span>; } <span class="hljs-comment">//关闭连接，释放资源</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">release</span><span class="hljs-params">(ResultSet rs, Statement st, Connection conn)</span> { <span class="hljs-keyword">try</span> { <span class="hljs-keyword">if</span> (rs != <span class="hljs-literal">null</span>) { rs.close(); } } <span class="hljs-keyword">catch</span> (SQLException throwables) { throwables.printStackTrace(); } <span class="hljs-keyword">if</span> (st != <span class="hljs-literal">null</span>) { <span class="hljs-keyword">try</span> { st.close(); } <span class="hljs-keyword">catch</span> (SQLException throwables) { throwables.printStackTrace(); } } <span class="hljs-keyword">if</span> (conn != <span class="hljs-literal">null</span>) { <span class="hljs-keyword">try</span> { conn.close(); } <span class="hljs-keyword">catch</span> (SQLException throwables) { throwables.printStackTrace(); } } }} - 创建测试类
package test;import Utils.JDBCutil;
import java.sql.*;
import java.util.stream.StreamSupport;public class jdbc01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
test01("1", "1");
}<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">test01</span><span class="hljs-params">(String username, String pwd)</span> { <span class="hljs-comment">//获取数据库连接</span> <span class="hljs-type">Connection</span> <span class="hljs-variable">connection</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>; <span class="hljs-comment">//执行SQL的对象 SQL注入问题 下面会讲</span> <span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">statement</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>; <span class="hljs-comment">//结果集对象</span> <span class="hljs-type">ResultSet</span> <span class="hljs-variable">resultSet</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>; <span class="hljs-keyword">try</span> { connection = JDBCutil.getConnection(); <span class="hljs-comment">//拼接成SQL语句</span> <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"select * from mybatis.user where 'id'= ?"</span>; statement = connection.prepareStatement(sql); statement.setInt(<span class="hljs-number">1</span>, <span class="hljs-number">1</span>); <span class="hljs-comment">//增删改 返回受影响行数</span> <span class="hljs-comment">//int i = statement.executeUpdate();</span> <span class="hljs-comment">//查询 返回结果集对象</span> <span class="hljs-comment">//resultSet = statement.executeQuery();</span> <span class="hljs-comment">//结果集对象的方法及使用</span> <span class="hljs-comment">//resultSet.getString();</span> <span class="hljs-comment">//resultSet.getObject();</span> <span class="hljs-comment">//resultSet.getInt();</span> <span class="hljs-comment">//resultSet.next();//下一个数据 遍历</span> <span class="hljs-comment">//while (resultSet.next()) {</span> <span class="hljs-comment">// System.out.println(resultSet.getInt("id"));</span> <span class="hljs-comment">//}</span> } <span class="hljs-keyword">catch</span> (SQLException e) { e.printStackTrace(); } <span class="hljs-keyword">finally</span> { <span class="hljs-comment">//关闭jdbc连接</span> JDBCutil.release(resultSet, statement, connection); } }} - 整个项目结构大概就是这样
3.SQL 注入#
SQL 注入攻击是指恶意拼接 SQL 作为参数，传递给 Web 服务器，进而传给数据库服务器以执行数据库命令，获取数据库的信息以及提权。
举例：
SELECT * FROM user WHERE name = '" + name +"' AND psw = '" + psw +"';
String name = "' or'1=1"
String psw = "' or'1=1"
拼接成
SELECT * FROM user WHERE `name` = '' or '1=1' AND `psw` = '' or '1=1'
此 SQL 语句相当于 SELECT * FROM user，即不需要 name 和 psw，通过 SQL 注入查出了所有用户
我们使用 PreparedStatement，也就是这个原因
我们先写出 SQL 语句，使用？占位符作为参数 ，PreparedStatement 会执行预编译，然后我们再去补参数，这样就可以有效的防止 SQL 注入问题
4. 处理事务#
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
<span class="hljs-comment">//关闭数据库的自动提交事务，自动会开启事务</span>
connection.setAutoCommit(<span class="hljs-literal">false</span>);
<span class="hljs-type">String</span> <span class="hljs-variable">sql1</span> <span class="hljs-operator">=</span> <span class="hljs-string">"UPDATE account SET money=money-500 WHERE NAME = 'A'"</span>;
statement = connection.prepareStatement(sql1);
statement.executeUpdate();
<span class="hljs-type">String</span> <span class="hljs-variable">sql2</span> <span class="hljs-operator">=</span> <span class="hljs-string">"UPDATE account SET money=money+500 WHERE NAME = 'B'"</span>;
statement = connection.prepareStatement(sql2);
statement.executeUpdate();
<span class="hljs-comment">//业务完毕，提交事务</span>
connection.commit();
} <span class="hljs-keyword">catch</span> (SQLException throwables) {
throwables.printStackTrace();
}
<span class="hljs-keyword">try</span> {
connection.rollback();
<span class="hljs-comment">//失败，自动回滚事务</span>
} <span class="hljs-keyword">catch</span> (SQLException throwables) {
throwables.printStackTrace();
} <span class="hljs-keyword">finally</span> {
JdbcUtils.release(resultSet, statement, connection);
}
}