1、MySQL 基础命令
数据库语言
DQL 数据库查询语言
带 select 关键字的都是查询语句
DDL 数据库定义语言
create、drop、alter，与DML不同，主要对表结构进行操作
DML 数据库操作语言
insert、delete、update，对表中的数据进行操作
DCL 数据库控制语言
TCL 事务控制语言
事务提交 commit 和事务回滚 rollback
-- 单行注释 /* */多行注释
mysql -u root -p123456 --连接数据库
show databases; --查看所有的数据库
show tables; --查看数据库中所有的表
create database xxx; --创建数据库
2、操作数据库
mysql 不区分大小写
操作数据库 > 操作数据库中的表 > 操作数据库表中的数据
2.1 操作数据库
注释一定要加空格
–空格XXX
1、创建数据库
CREATE DATABASE school
2、删除数据库
DROP DATABASE school;
3、使用数据库
USE school;
2.2 创建和删除表
1、创建表
CREATE TABLE student(
id int(10),
name int VARCHAR(10)
)
2、删除表
-- 删除表的字段
ALTER TABLE student DROP age
-- 删除表
DROP TABLE IF EXISTS student
2.3 ALTER语句修改表
-- 修改表名
ALTER TABLE student RENAME AS student1;
-- 增加表的字段
ALTER TABLE student ADD age INT(11)
-- 修改表的字段
ALTER TABLE student MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE student CHANGE age age1 INT(1) --字段重命名
3、MySQL数据管理
3.1 外键
-- FK_cno 为约束名
ALTER TABLE student ADD CONSTRAINT FK_cno FOREIGN KEY (cno) REFERENCES course (cno);
3.2 DML语言
3.2.1 insert 语句
1、插入单个字段或多个字段
-- 插入语句
INSERT INTO student VALUES ('00001','小红','女');
-- 插入单个字段值
INSERT INTO student (sno) VALUES ('00002');
-- 插入多个字段值
INSERT INTO student ( sno ) VALUES ( '00003' );
3.2.2 update语句
1、修改单个语句
-- 修改语句
UPDATE student SET sname = '小杨' WHERE sno = '00002'
2、修改多个属性
-- 修改多个属性
UPDATE student SET sname = '小明',sex = '男' WHERE sno = '00003'
3.2.3 delete 和 truncate语句
- delete
1、删除指定数据
-- 删除指定数据
DELETE FROM student WHERE sno ='00004'
2、删除表
-- 删除表
DELETE FROM student
- truncate
作用：完全清空一个数据库表，表的结构和索引约束不会变！
-- 清空整张表
TRUNCATE student
- 区别
相同点： 都能删除数据，都不会删除表结构（表头）
不同点：truncate 重新设置自增列 计数器会归零，delete不会影响自增；truncate不会影响事物
3.3 DQL查询数据
3.3.1查询和去重
1、 查询
-- 查询
SELECT * FROM student;
SELECT sno FROM student;
2、别名
-- 别名
SELECT sno AS 学号 FROM student;
3、函数 concat(a,b)
-- 函数concat(a,b) 拼接
SELECT CONCAT('学号：',sno) AS id FROM student
4、DISTINCT 去除重复数据
-- 去除重复数据
SELECT DISTINCT sno FROM student;
5、and 优先级比 or 高
3.3.2 模糊查询
1、like 查询
-- 查询名字以小开头的
SELECT sname FROM student
WHERE sname LIKE '小%';
-- 查询名字中带红的
SELECT sname FROM student
WHERE sname LIKE '%红%';
-- 查询名字以小开头的两个字的
SELECT sname FROM student
WHERE sname LIKE '小_';
2、in 查询
in 里面可以一个或多个条件里查询
SELECT sno FROM student
WHERE sex IN ('女');
3、NULL 查询
SELECT sno FROM student
WHERE sname is NULL;
3.3.3 where 子句
显示连接和隐式连接
在FROM子句中使用JOIN，这种连接方式是进行显式连接。
FROM子句后面直接列出多个表名，这种连接方式即属于内连接，是隐式连接。
join 和 where 区别
1、内连接
INNER JOIN:只显示多表之间与关联条件相匹配的列.
2、外连接:
LEFT JOIN :
以左表为基础,显示左表中的所有列,不管是否与关联条件相匹配,而右表中的数据只显示与关联条件相匹配的列,不匹配的列以NULL字符填充.
RIGHT JOIN:
以右表为基础,显示右表中的所有列,不管是否与关联条件相匹配,而左表中的数据只显示与关联条件相匹配的列,不匹配的列以NULL字符填充.
FULL JOIN :显示多个表中的所有的列,不匹配关联条件的列以NULL字符填充.
3、WHERE子句中使用的连接语句，在数据库语言中，被称为隐性连接。INNER JOIN……ON子句产生的连接称为显性连接。（其他JOIN参数也是显性连接）WHERE 和INNER JOIN产生的连接关系，没有本质区别，结果也一样。但是！隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。
where和 on 区别
on是先对表进行筛选再生成关联表，where是先生成关联表再对关联表进行筛选，on执行的优先级高于left join，而where的优先级低于left join；
当我们使用on关键字时，会先根据on后面的条件进行筛选，条件为真时返回该行,由于on的优先级高于left join，所以left join关键字会把左表中没有匹配的所有行也都返回，然后生成临时表返回；where对与行的筛选是在left join之后的，也就是生成临时表之后才会对临时表进行筛选。
where 后面跟查询条件，join 后面跟表名 on 后面跟条件
3.3.4 联表查询
| 操作 | 描述 |
|---|---|
| inner join | 内连接 如果表中有一个匹配，就返回行（返回所有完美匹配的结果） |
| left join | 外连接会 从左表中返回所有的值，即使右表中没有匹配 |
| right join | 外连接 会从右表中返回所有的值，即使左表中没有匹配 |
3.3.5 分页和排序
group by—指定结果按照哪几个字段来分组
having—过滤分组的记录必须满足的次要条件
order by—指定查询记录按一个或多个条件排序
limit—指定查询记录从哪条到哪条
HAVING 要在GROUP BY后面
1、排序 order by
-- 升序ASC 降序DESC
SELECT sno from student
ORDER BY sno ASC
SELECT sno from student
ORDER BY sno DESC;
2、分页 limit
-- LIMIT 起始值 页面的大小
-- 第n页 (n-1)*pagesize
SELECT sno from student
ORDER BY sno ASC
LIMIT 0,5
3.3.6 子查询
SELECT cno
FROM grade
WHERE sno = (
SELECT sno
FROM student
WHERE sname = '小红'
)
4、MySQL函数
where子句中不能使用分组函数
4.1 函数
SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4); -- 向上取整
SELECT FLOOR(9.4); -- 向下取整
SELECT RAND(); -- 返回0~1之间的随机数
SELECT SIGN(10); -- 判断一个数的符号 负数-1 正数1
-- 时间和日期函数
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT NOW(); -- 年月日时分秒
SELECT LOCALTIME(); -- 获取本地时间
SELECT SYSDATE(); -- 获取系统时间
-- 系统
SELECT VERSION(); -- 系统版本
SELECT USER(); -- 获取用户
4.2 聚合函数
1、COUNT()
SELECT COUNT(列名) FROM student; -- 忽略null值
SELECT COUNT(*) FROM student; -- 忽略null值，本质计算行数
SELECT COUNT(1) FROM student; -- 忽略null值，本质计算行数
2、SUM()
HAVING 要在GROUP BY后面
SELECT sname,SUM(score),MAX(score),MIN(score)
FROM grade
INNER JOIN student
ON student.sno = grade.sno
GROUP BY sname -- sname 要在select语句后面出现否则会报错
HAVING MAX(score)>80
5、事务
将一组SQL放在一个批次中去执行
事务的四个特性（ACID）：
- 原子性：要么全部成功要么全部失败
- 一致性：事务操作前后数据完整性一致
- 隔离性：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，事务之间要相互隔离
- 持久性：表示事务结束后的数据不随着外界原因导致数据丢失；事务没有提交恢复到原状，事务已经提交，持久化到数据库。
6、索引
- 主键索引（PRIMARY KEY） 唯一标识，一个表中主键只能出现一次
- 唯一索引（UNIQUE KEY） 避免重复的列出现，一个表中可以有多个唯一索引
- 常规索引（KEY/INDEX）默认的，index，key关键字设置
- 全文索引（FULLTEXT） 在特定的数据库引擎下才有，MyLSAM；快速定位数据
7、用户
-- 创建用户
CREATE USER susn IDENTIFIED BY '123456'
-- 修改密码
SET PASSWORD FOR suns = PASSWORD('111111')
-- 重命名
RENAME USER susn TO zhiqi
8、范式
1、第一范式
第一范式(1NF)：
原子性：保证每一列不可再分；属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)
2、第二范式
第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)
3、第三范式
第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）
9、JDBC
jdbc（Java数据库连接）是一种用于执行SQL语句的Java API，由一组用Java语言编写的类和接口组成。
9.1 idea连接mysql
public class JdbcFirst {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2、用户信息和url
// 防止中文乱码useUnicode=true&characterEncoding=utf8&useSSL=true
String url = "jdbc:mysql://localhost:3306/jdbctest?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
//3、连接成功,数据库对象
Connection connection = DriverManager.getConnection(url,username,password);
//4、执行SQL的对象
Statement statement = connection.createStatement();
//5、执行SQL，查看返回的结果
String sql = "SELECT * FROM student; ";
ResultSet resultSet = statement.executeQuery(sql); //返回的结果集，全部查询结果
while (resultSet.next()){ //遍历
System.out.println("sno="+resultSet.getObject("sno"));
System.out.println("sname="+resultSet.getObject("sname"));
System.out.println("sex="+resultSet.getObject("sex"));
}
//6、释放连接
resultSet.close();
statement.close();
connection.close(); //很耗资源
}
}
用maven中的mysql驱动包：
pox.xml
<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>maventest</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>maventest</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<!--mysql驱动-->
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
</dependencies>
</project>
9.2 JDBC对象详解
1、DriverManager
Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动
Connection connection = DriverManager.getConnection(url,username,password);
2、URL
// jdbc:mysql//主机号: 端口号/数据库名？参数1&参数2&参数3
String url = "jdbc:mysql://localhost:3306/jdbctest?useUnicode=true&characterEncoding=utf8&useSSL=true";
3、Statement
//执行SQL的对象
Statement statement = connection.createStatement();
statement.executeQuery(); //查询操作返回ResultSet
statement.execute(); //执行任何sql
statement.executeUpdate(); //更新、插入、删除都用这个
4、ResultSet
查询的结果集，封装了所有查询结果
resultSet.getObject(); //在不知道指定类型时使用
9.3 Statement对象
statement.executeQuery(); //查询操作返回ResultSet
statement.execute(); //执行任何sql
statement.executeUpdate(); //更新、插入、删除都用这个
主程序：
package org.example.jdbc;
import org.example.jdbc.util.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "INSERT INTO student VALUES ('00005','小美','女');";
int i = st.executeUpdate(sql);
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
工具类：
package org.example.jdbc.util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try{
//获得类加载器获取资源文件名--返回一个输入流
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放连接
public static void release(Connection conn , Statement st , ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
if(st != null){
try {
st.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
}
9.4 SQL 注入
SQL会被拼接（OR）
"x’or’1=1"拼接字符串，结果始终为真，查出所有数据。
package org.example.jdbc;
import org.example.jdbc.util.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Login("x'or'1=1","x'or'1=1");
}
public static void Login( String id ,String name) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
// "SELECT * FROM student WHERE sno = '"+id+"' AND sname = '"+name+"';"
String sql = "SELECT * FROM student WHERE sno = '"+id+"' AND sname = '"+name+"';";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getString("sno"));
System.out.println(rs.getString("sname"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
9.5 PreparedStatement对象
继承Statement类，可以避免 sql 注入；
把传递进来的参数当做字符，如果传递进来的参数存在转义字符，直接忽略
package org.example.jdbc;
import org.example.jdbc.util.JdbcUtils;
import java.sql.*;
public class TestPreparedStatement {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
// 使用？站位
String sql = "INSERT INTO student (sno,sname,sex)VALUES (?,?,?);";
st = conn.prepareStatement(sql); //预编译sql,先写sql,然后不执行
//手动给参数赋值
st.setString(1,"00010");
st.setString(2,"小成");
st.setString(3,"男");
// 直接调用执行方法，不用传参
int i = st.executeUpdate();
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
