MySQL数据库

数据库

1.查询全部数据库
show databases;
2.创建一个数据库
create database school;
3.使用一个数据库
use school;
4.删除一个数据库
drop database school

表-字段-数据（增删改数据）

1.创建表
CREATE TABLE new_student (
id INT ( 4 ) NOT NULL auto_increment,
name VARCHAR ( 30 ) NOT NULL DEFAULT '匿名',
sex VARCHAR ( 30 ) NOT NULL DEFAULT '男',
address VARCHAR ( 30 ) DEFAULT NULL,
PRIMARY KEY ( id ) 
) ENGINE = INNODB DEFAULT charset = utf8

2.查询表结构
DESC new_student

3.查询全部表
SHOW TABLES

4.修改表名
ALTER TABLE new_student RENAME student

5.插入字段
ALTER TABLE student ADD new_email char ( 30 ) DEFAULT NULL

5.修改字段类型
alter table student MODIFY new_email varchar(30)

7.修改字段名
ALTER TABLE student CHANGE new_email email VARCHAR ( 30 )

8.删除字段
ALTER TABLE student DROP email

9.插入数据
INSERT INTO student ( name, sex, address ) VALUES ( 'xxx', '女', '璃月' )

10.修改数据
UPDATE student SET name = '刻晴' WHERE id =1

11.删除数据
DELETE FROM student WHERE id =1

12.清空表，不建议使用（delete from grade），建议使用TRUNCATE（不会影响事务，主键自增的计数器会重置为1）
TRUNCATE student 

13.删除表
DROP TABLE student

查询数据

-- 准备4张表并插入数据

创建学生表
create table `student`(
	`studentno` int(4) not null comment '学号',
    `loginpwd` varchar(20) default null,
    `studentname` varchar(20) default null comment '学生姓名',
    `sex` tinyint(1) default null comment '性别，0或1',
    `gradeid` int(11) default null comment '年级编号',
    `phone` varchar(50) not null comment '联系电话，允许为空',
    `address` varchar(255) not null comment '地址，允许为空',
    `borndate` datetime default null comment '出生时间',
    `email` varchar (50) not null comment '邮箱账号允许为空',
    `identitycard` varchar(18) default null comment '身份证号',
    primary key (`studentno`),
    unique key `identitycard`(`identitycard`),
    key `email` (`email`)
)engine=myisam default charset=utf8;

insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');


创建年级表
create table `grade`(
	`gradeid` int(11) not null auto_increment comment '年级编号',
  `gradename` varchar(50) not null comment '年级名称',
    primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;

insert into `grade` (`gradeid`,`gradename`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

创建科目表
create table `subject`(
	`subjectno`int(11) not null auto_increment comment '课程编号',
    `subjectname` varchar(50) default null comment '课程名称',
    `classhour` int(4) default null comment '学时',
    `gradeid` int(4) default null comment '年级编号',
    primary key (`subjectno`)
)engine = innodb auto_increment = 19 default charset = utf8;

insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

创建成绩表
create table `result`(
	`studentno` int(4) not null comment '学号',
    `subjectno` int(4) not null comment '课程编号',
    `examdate` datetime not null comment '考试日期',
    `studentresult` int (4) not null comment '考试成绩',
    key `subjectno` (`subjectno`)
)engine = innodb default charset = utf8;

insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

1.查询学生表全部数据
select * from student
查询成绩表全部数据
select * from result

2.查询指定字段，并起个别名
select studentname as 学生姓名 from student

3.concat函数：字符拼接结果列
select concat('姓名：',studentname) as 学生姓名 from student

4.distinct函数：去重复值
select distinct StudentNo from result

5.列表达式
select StudentResult+1 as 考试成绩+1 FROM result

6.运算符
and、or、not：与、或、非 
select studentresult from result where studentresult>58 and studentresult<98
select studentresult from result where studentresult>99999 or studentresult<98
select studentresult from result where not studentresult = 58 

7.操作符
is null：数据为null的
is not null：数据不为null的
between：介于筛选范围之间的数据
in：允许WHERE子句中规定多个值
like：模糊匹配数据

8.通配符 必须与 LIKE运算符一起使用
%：0或多个字符
_：1个字符

select subjectname from subject  where subjectname like '%数%'
select subjectname from subject  where subjectname like 'C#基_'

8.联表查询
	左关联：left join (查出两个表的并集，返回左表全部数据，右表匹配不到的列为null)
	SELECT
		s.studentno,
		s.studentname,
		r.subjectno,
		r.studentresult
	FROM
		student AS s
		left JOIN result AS r ON s.studentno = r.studentno
		
	内连接：INNER JOIN(查出两个表的并集)
	SELECT
		s.studentno,
		s.studentname,
		r.subjectno,
		r.studentresult
	FROM
		student AS s
		INNER JOIN result AS r ON s.studentno = r.studentno
		
	右关联：right join(查出两个表的并集，返回右表全部数据，左表匹配不到的列为null)
	SELECT
		s.studentno,
		s.studentname,
		r.subjectno,
		r.studentresult
	FROM
		student AS s
		right JOIN result AS r ON s.studentno = r.studentno
		
9.排序
ORDER BY(默认升序)
升序：asc
降序：desc

SELECT
 stu.studentname,
 sub.subjectname,
 res.studentresult 
FROM
 student stu
 JOIN result res ON stu.studentno = res.studentno
 JOIN SUBJECT sub ON res.subjectno = sub.subjectno 
ORDER BY
 res.studentresult DESC
 
10.分页
limit (起始页索引，,每页条数)
第一页 limit (1-1,10)
第N页 limit (N-1*10,10)

SELECT
 stu.studentname,
 sub.subjectname,
 res.studentresult 
FROM
 student stu
 JOIN result res ON stu.studentno = res.studentno
 JOIN SUBJECT sub ON res.subjectno = sub.subjectno 
ORDER BY
 res.studentresult 
 LIMIT 0,10

11. 子查询
where(select * from)

SELECT
 s.studentno,
 s.studentname,
 r.studentresult 
FROM
 student s JOIN result r ON s.studentno = r.studentno 
WHERE
 studentresult < 80 
 AND subjectno = ( SELECT subjectno FROM SUBJECT WHERE subjectname = '高等数学-2' )

12.聚合函数
count() 计数
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
GROUP BY 分组
HAVING 过滤聚合函数的条件

SELECT
 count( * ) 计数,
 subjectname,
 sum( studentresult ) 总和,
 avg( studentresult ) 平均值,
 max( studentresult ) 最大值,
 min( studentresult ) 最小值 
FROM
 result
 JOIN SUBJECT ON result.subjectno = SUBJECT.subjectno 
GROUP BY
 result.subjectno 
HAVING
 平均值 > 68	

事务

ACID原则
1.原子性(同一个事务保证一组数据库操作，要么都成功，要么都失败)
2.一致性(保证数据最终一致性，符合逻辑运算)
3.隔离性(多个事务，排除相互间的影响)
4.持久性(事务一旦提交不可逆)

事务隔离失败
1.脏读：一个事务读取到了，其它事务未提交的数据
2.不可重复读：一个事务内，多次相同的查询却返回了不同数据
3.幻读：一个事务读取到了，其它事务提交的数据，导致前后读取不一致

事务操作流程
1.关闭mysql事务自动提交
set autocommit = 0 
2.开启事务
start transaction
3.提交(提交成功，数据不可逆)
commit 
4.回滚(提交失败，可还原数据)
rollback
5.开启mysql事务自动提交
set autocommit = 1 

//准备一个账户表
create table account (
	id int(3) auto_increment,
	name varchar(30),
	money decimal(9,2),
	primary key (id)
)engine = innodb default charset = utf8

//插入两个账户数据
insert into account (name,money)values('a',1000.00),('b',1000.00)

//模拟转账事务
select * from account
//关闭mysql事务自动提交
set autocommit = 0
//开启事务
start transaction 
//a:1000-500
update account set money = money-500 where name = 'a'
//b:1000+500
update account set money = money+500 where name = 'b'
//提交
commit 
//回滚
rollback
//开启mysql事务自动提交
set autocommit = 1

索引

优点：查询效率高
缺点：占用磁盘空间，每次增删改数据，都会操作对应的索引文件
1.primary key  主键索引（列值唯一，不允许为空）
2.unique key 唯一索引（列值唯一，允许为空）
3.key或index 常规索引（列值不唯一，不允许为空）
4.fulltext index 全文索引（列值不唯一，允许为空）

-- 准备一个表
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT'0男1女',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT = 'app用户表'

-- 插入200w条数据
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 2000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(name,email,phone,gender,password,age)
   VALUES(CONCAT('用户', i), '1234567@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

-- 测试索引查询速度
select * from app_user where name = '用户100000' -- 无索引时 1108毫秒
CREATE INDEX idx_app_user_name ON app_user(name); -- 创建索引 4254毫秒
select * from app_user where name = '用户100000' -- 有索引时 18毫秒

Jdbc

-- 准备一张表

CREATE TABLE `users` (
id INT PRIMARY KEY auto_increment,
NAME VARCHAR ( 40 ),
PASSWORD VARCHAR ( 40 ),
email VARCHAR ( 60 ),
birthday DATE 
);

初步封装JDBC

package com.baijun.utils.utils;
import lombok.SneakyThrows;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

//JDBC工具类
public class JdbcConfig {

    private static String url ="jdbc:mysql://localhost:3306/jdbcStudy?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
    private static String user ="root";
    private static String password ="root";
    //1.加载数据库驱动只执行一次
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //2.获取数据库连接对象
    @SneakyThrows
    public static Connection getConnection(){
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    //6.释放连接
    @SneakyThrows
    public static void release(ResultSet resultSet,Statement statement,Connection connection){
        //关闭结果集对象
        if (resultSet!=null){
            resultSet.close();
        }
        //关闭执行SQL对象
        if (statement!=null){
            statement.close();
        }
        //关闭数据库连接对象
        if (connection!=null){
            connection.close();
        }
    }

}

一套CRUD

package com.baijun.utils.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcDemo {

    public static void main(String[] args) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //2.获取数据库连接对象
            connection = JdbcConfig.getConnection();
            //3.获取执行SQL对象
            statement = connection.createStatement();
            //4.执行添加
            int insertRes = statement.executeUpdate("insert into users(name,password,email,birthday)values('张三','123456','zs@sina.com','1980-12-04'),('李四','123456','lisi@sina.com','1981-12-04'),('王五','123456','wangwu@sina.com','1979-12-04')");
            if (insertRes > 0) {
                System.out.println("插入成功");
            }
            //执行修改
            int updateRes = statement.executeUpdate("update users set name = '法外狂徒' where name ='张三'");
            if (updateRes > 0) {
                System.out.println("修改成功");
            }
            //执行删除
            int deleteRes = statement.executeUpdate("delete from users where name in ('李四','王五')");
            if (deleteRes > 0) {
                System.out.println("删除成功");
            }
            //5.执行查询，获取结果集对象
            resultSet = statement.executeQuery("select * from users");
            while (resultSet.next()){
                System.out.println("id："+resultSet.getObject("id"));
                System.out.println("name："+resultSet.getObject("name"));
                System.out.println("password："+resultSet.getObject("password"));
                System.out.println("email："+resultSet.getObject("email"));
                System.out.println("birthday："+resultSet.getObject("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放连接
            JdbcConfig.release(resultSet, statement, connection);
        }

    }
}

SQL注入问题

攻击者实现SQL注入的方式有很多种，但本质上都是通过修改SQL语句，以此达到欺骗数据库服务器的目的

package com.baijun.utils.test;
import com.baijun.utils.utils.JdbcConfig;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//sql注入
public class SqlInsert {

    public static void main(String[] args) {
//  正常登录          login("1");
//  利用or运算符实现SQL注入      login("''or 1=1");
    }

    public static void  login (String id){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //2.获取数据库连接对象
            connection = JdbcConfig.getConnection();
            //3.获取执行SQL对象
            statement = connection.createStatement();
            //5.执行查询，获取结果集对象
            resultSet = statement.executeQuery("select * from users where id ="+id);
            while (resultSet.next()){
                System.out.println("id："+resultSet.getObject("id"));
                System.out.println("name："+resultSet.getObject("name"));
                System.out.println("password："+resultSet.getObject("password"));
                System.out.println("email："+resultSet.getObject("email"));
                System.out.println("birthday："+resultSet.getObject("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放连接
            JdbcConfig.release(resultSet, statement, connection);
        }
    }

}

PreparedStatement，它也是一个执行SQL的对象，与Statement用法大同小异，区别在于 PreparedStatement可防SQL注入，且执行效率更高，所以后面使用JDBC优先考虑用它。

package com.baijun.utils.test;
import com.baijun.utils.utils.JdbcConfig;
import org.omg.PortableInterceptor.SUCCESSFUL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;
        try {
            connection = JdbcConfig.getConnection();//1.获取数据库连接对象
            connection.setAutoCommit(false);//2.关闭mysql自动提交，并开启事务
            preparedStatement = connection.prepareStatement("insert into users(name,password,email,birthday)values('防注入','123456','fzr@sina.com','1980-12-04')");//3.执行SQL对象，PreparedStatement，预编译状态
            int i = preparedStatement.executeUpdate();//4.执行SQL
            if (i>0){
                System.out.println("插入数据成功！");
            }
            preparedStatement = connection.prepareStatement("update users set password = '456789' where name = '防注入'");
            int u = preparedStatement.executeUpdate();
            if (u>0){
                System.out.println("修改数据成功！");
            }
            preparedStatement = connection.prepareStatement("select * from users");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println("id"+resultSet.getObject("id"));
                System.out.println("name"+resultSet.getObject("name"));
                System.out.println("password"+resultSet.getObject("password"));
                System.out.println("email"+resultSet.getObject("email"));
                System.out.println("birthday"+resultSet.getObject("birthday"));
                System.out.println("查询成功！");
            }
            connection.commit();//5.提交事务
        } catch (SQLException e) {
            try {
                connection.rollback();//6.回滚，这段代码不写默认也会回滚
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcConfig.release(null,preparedStatement,connection);
        }
    }
}