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:mm:ss
- datatime：YYYY-MM-DD HH:mm: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 EXISTS `student` ( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名', PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 SHOW 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` FROM `student` s INNER JOIN `result` r ON s.`studentno` = r.`studentno` WHERE `studentresult` > 80 AND `subjectno` = ( SELECT `subjectno` FROM `subject` WHERE `SubjectName` = 'a') ORDER BY `studentresult` DESC; -
order by
-- 升序ASC(默认)，降序DESC SELECT * FROM `user` where 'id' BETWEEN 1 AND 5 ORDER BY 'id' DESC; -
limit
-- limit(查询的起始下标,pageSize) SELECT * FROM `user` 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> <groupId>org.example</groupId> <artifactId>JDBC_01</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.25</version> </dependency> </dependencies> <properties> <maven.compiler.source>16</maven.compiler.source> <maven.compiler.target>16</maven.compiler.target> </properties> </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; static { try { //1.新建属性集对象 Properties properties = new Properties(); //2.通过反射，新建字符输入流，读取db.properties文件 InputStream input = JDBCutil.class.getClassLoader().getResourceAsStream("db.properties"); //3.将输入流中读取到的属性，加载到properties属性集对象中 properties.load(input); //4.根据键，获取properties中对应的值 driver = properties.getProperty("driver"); url = properties.getProperty("url"); user = properties.getProperty("user"); password = properties.getProperty("password"); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() { try { //注册数据库的驱动 Class.forName(driver); //获取数据库连接 Connection connection = DriverManager.getConnection(url, user, password); //返回数据库连接 return connection; } catch (Exception e) { e.printStackTrace(); } return null; } //关闭连接，释放资源 public static void release(ResultSet rs, Statement st, Connection conn) { try { if (rs != null) { rs.close(); } } catch (SQLException throwables) { throwables.printStackTrace(); } if (st != null) { try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (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"); } public static void test01(String username, String pwd) { //获取数据库连接 Connection connection = null; //执行SQL的对象 SQL注入问题 下面会讲 PreparedStatement statement = null; //结果集对象 ResultSet resultSet = null; try { connection = JDBCutil.getConnection(); //拼接成SQL语句 String sql = "select * from mybatis.user where 'id'= ?"; statement = connection.prepareStatement(sql); statement.setInt(1, 1); //增删改 返回受影响行数 //int i = statement.executeUpdate(); //查询 返回结果集对象 //resultSet = statement.executeQuery(); //结果集对象的方法及使用 //resultSet.getString(); //resultSet.getObject(); //resultSet.getInt(); //resultSet.next();//下一个数据 遍历 //while (resultSet.next()) { // System.out.println(resultSet.getInt("id")); //} } catch (SQLException e) { e.printStackTrace(); } finally { //关闭jdbc连接 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();
//关闭数据库的自动提交事务，自动会开启事务
connection.setAutoCommit(false);
String sql1 = "UPDATE account SET money=money-500 WHERE NAME = 'A'";
statement = connection.prepareStatement(sql1);
statement.executeUpdate();
String sql2 = "UPDATE account SET money=money+500 WHERE NAME = 'B'";
statement = connection.prepareStatement(sql2);
statement.executeUpdate();
//业务完毕，提交事务
connection.commit();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
connection.rollback();
//失败，自动回滚事务
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(resultSet, statement, connection);
}
}
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
