Java Web

SHOW DATABASES;

CREATE DATABASE 数据库名称;

CREATE DATABASE IF NOT EXISTS 数据库名称;

DROP DATABASE 数据库名称;

DROP DATABASE IF EXISTS 数据库名称;

USE 数据库名称;

SELECT DATABASE();

SHOW TABLES;

DESC 表名称;

CREATE TABLE 表名 (
  字段名1  数据类型1,
  字段名2  数据类型2,
  …
  字段名n  数据类型n
);

DROP TABLE 表名;

DROP TABLE IF EXISTS 表名;

ALTER TABLE 表名 RENAME TO 新的表名;

-- 将表名student修改为stu
alter table student rename to stu;

ALTER TABLE 表名 ADD 列名 数据类型;

-- 给stu表添加一列address，该字段类型是varchar(50)
alter table stu add address varchar(50);

ALTER TABLE 表名 MODIFY 列名 新数据类型;

-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);

ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;

-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table stu change address addr varchar(50);

ALTER TABLE 表名 DROP 列名;

-- 将stu表中的addr字段 删除
alter table stu drop addr;

INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);

INSERT INTO 表名 VALUES(值1,值2,…);

INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;

UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;

DELETE FROM 表名 [WHERE 条件] ;

-- 删除云龙记录
delete from stu where name = '云龙';

-- 删除stu表中所有的数据
delete from stu;

-- 删除stu表
drop table if exists stu;


-- 创建stu表
CREATE TABLE stu (
 id int, -- 编号
 name varchar(20), -- 姓名
 age int, -- 年龄
 sex varchar(5), -- 性别
 address varchar(100), -- 地址
 math double(5,2), -- 数学成绩
 english double(5,2), -- 英语成绩
 hire_date date -- 入学时间
);

-- 添加数据
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date)
VALUES
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据

SELECT DISTINCT 字段列表 FROM 表名;

AS: AS 也可以省略

SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

SELECT 聚合函数名(列名) FROM 表;

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

SELECT 字段列表 FROM 表名 LIMIT  起始索引 , 查询条目数;

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.29</version>
    </dependency>
</dependencies>

package com.liu.JDBC;

import java.sql.Connection;
import java.sql.DriverAction;
import java.sql.DriverManager;
import java.sql.Statement;

public class JDBCDemo01 {
    public static void main(String[] args) throws Exception {
        //注册驱动：通过反射把实现类加载到内存
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test";
        String name= "root";
        String password = "131411";
        Connection connection = DriverManager.getConnection(url, name, password);
        //定义执行SQL
        String sql = "update stu set sex='男' where id = 7";
        //通过连接对象获取执行sql的对象 Statement
        Statement statement = connection.createStatement();
        //通过执行sql对象执行sql语句
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        //关闭连接对象
        statement.close();
        connection.close();
    }
}

package com.liu.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCDemo02 {
    public static void main(String[] args) throws Exception {
        //获取连接
        String url = "jdbc:mysql:///test";
        String name= "root";
        String password = "131411";
        Connection connection = DriverManager.getConnection(url, name, password);
        //定义执行SQL
        String sql = "update stu set sex='女' where id = 7";
        //通过连接对象获取执行sql的对象 Statement
        Statement statement = connection.createStatement();
        //通过执行sql对象执行sql语句
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        //关闭连接对象
        statement.close();
        connection.close();
    }
}

package com.liu.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCDemo03 {
    public static void main(String[] args) throws Exception {
        //获取连接
        String url = "jdbc:mysql:///test";
        String name= "root";
        String password = "131411";
        Connection connection = DriverManager.getConnection(url, name, password);

        //定义执行SQL
        String sql1 = "update stu set age=23 where id = 7";
        String sql2 = "update stu set age=20 where id = 8";
        //通过连接对象获取执行sql的对象 Statement
        Statement statement = connection.createStatement();

        //开启事务
        connection.setAutoCommit(false);

        try {
            //通过执行sql对象执行sql语句
            int i = statement.executeUpdate(sql1);
            System.out.println(i);
            int i1 = statement.executeUpdate(sql2);
            System.out.println(i1);
            //提交事务
            connection.commit();
        } catch (Exception e) {
            //回滚事务
            connection.rollback();
            e.printStackTrace();
        }
        //关闭连接对象
        statement.close();
        connection.close();
    }
}

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.1</version>
    <scope>test</scope>
</dependency>

@Test
public void Test1() throws Exception {
    //获取连接
    String url = "jdbc:mysql:///test";
    String name= "root";
    String password = "131411";
    Connection connection = DriverManager.getConnection(url, name, password);
    //定义执行SQL:DML语句
    String sql = "update stu set sex='女' where id = 7";
    String sql2 = "insert into stu values (9,'刘畅',23,'男','河南',90,70,'1999-06-14')," +
        "(10,'成龙',22,'男','河南',100,80,'2000-06-14')";
    //通过连接对象获取执行sql的对象 Statement
    Statement statement = connection.createStatement();
    //通过执行sql对象执行sql语句
    int i = statement.executeUpdate(sql);
    System.out.println(i);//只更改了一条数据，返回值应该是1
    if (i>0){
        System.out.println("修改成功");
    }else {
        System.out.println("修改失败");
    }
    int i1 = statement.executeUpdate(sql2);
    System.out.println(i1); //修改两条数据，返回值应该是2
    if (i1>0){
        System.out.println("修改成功");
    }else {
        System.out.println("修改失败");
    }
    //关闭连接对象
    statement.close();
    connection.close();
}
/**
运行结果：
1
修改成功
2
修改成功
*/

@Test
public void Test2() throws Exception {
    //获取连接
    String url = "jdbc:mysql:///test";
    String name= "root";
    String password = "131411";
    Connection connection = DriverManager.getConnection(url, name, password);
    //定义执行SQL:DML语句
    String sql = "create database if not exists stu1";
    String sql2 = "drop database stu1";
    //通过连接对象获取执行sql的对象 Statement
    Statement statement = connection.createStatement();
    //通过执行sql对象执行sql语句
    int i = statement.executeUpdate(sql);
    System.out.println(i);//返回值为1
    int i1 = statement.executeUpdate(sql2);
    System.out.println(i1);//执行成功，但是返回值为0
    //关闭连接对象
    statement.close();
    connection.close();
}

package com.liu.pojo;

import java.util.Date;

public class Student {
    private int id;
    private String name;
    private int age;
    private String sex;
    private String address;
    private Double math;
    private Double english;
    private Date hire_date;

    public Student() {
    }

    public Student(int id, String name, int age, String sex, String address, Double math, Double english, Date hire_date) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.address = address;
        this.math = math;
        this.english = english;
        this.hire_date = hire_date;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Double getMath() {
        return math;
    }

    public void setMath(Double math) {
        this.math = math;
    }

    public Double getEnglish() {
        return english;
    }

    public void setEnglish(Double english) {
        this.english = english;
    }

    public Date getHire_date() {
        return hire_date;
    }

    public void setHire_date(Date hire_date) {
        this.hire_date = hire_date;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", math=" + math +
                ", english=" + english +
                ", hire_date=" + hire_date +
                '}';
    }
}

@Test
public void Test3() throws Exception{
    //获取连接
    String url = "jdbc:mysql:///test";
    String name= "root";
    String password = "131411";
    Connection connection = DriverManager.getConnection(url, name, password);
    //定义执行SQL:DQL语句
    String sql = "select * from stu";
    //通过连接对象获取执行sql的对象 Statement
    Statement statement = connection.createStatement();
    //通过执行sql对象执行sql语句
    ResultSet resultSet = statement.executeQuery(sql);
    List<Student> list = new ArrayList<>();
    while (resultSet.next()){
        Student student = new Student();
        student.setId(resultSet.getInt(1));
        student.setName(resultSet.getString(2));
        student.setAge(resultSet.getInt(3));
        student.setSex(resultSet.getString("sex"));
        student.setAddress(resultSet.getString("address"));
        student.setMath(resultSet.getDouble("math"));
        student.setEnglish(resultSet.getDouble(7));
        student.setHire_date(resultSet.getDate("hire_date"));
        list.add(student);
    }
    System.out.println(list);
    //关闭连接对象
    resultSet.close();
    statement.close();
    connection.close();
}

 @Test
public void Test4() throws Exception {
    //获取连接
    String url = "jdbc:mysql:///test";
    String name= "root";
    String password = "131411";
    Connection connection = DriverManager.getConnection(url, name, password);
    //定义执行SQL:DML语句
    String name1 = "马运";
    String age = "' or '1' = '1";
    String sql = "select *from stu where name ='"+name1+"' and age = '"+age+"'";
    //通过连接对象获取执行sql的对象 Statement
    Statement statement = connection.createStatement();
    //通过执行sql对象执行sql语句
    ResultSet resultSet = statement.executeQuery(sql);
    if (resultSet.next()){
        System.out.println("登录成功");
    }else {
        System.out.println("登录失败");
    }
    //关闭连接对象
    statement.close();
    connection.close();
}
/**
运行结果：
select *from stu where name ='马运' and age = '' or '1' = '1'
登录成功
*/

@Test
public void Test5() throws Exception{
    //获取连接
    String url = "jdbc:mysql:///test";
    String name= "root";
    String password = "131411";
    Connection connection = DriverManager.getConnection(url, name, password);
    //定义执行SQL:DML语句
    String name1 = "马运";
    int age = 55;
    String sql = "select * from stu where name = ? and age = ?";
    //通过连接对象获取执行sql的对象 Statement
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    // 设置？的值
    preparedStatement.setString(1,name1);
    preparedStatement.setInt(2,age);
    ResultSet resultSet = preparedStatement.executeQuery();
    if (resultSet.next()){
        System.out.println("登录成功");
    }else {
        System.out.println("登录失败");
    }
    //关闭连接对象
    resultSet.close();
    preparedStatement.close();
    connection.close();
}

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.24</version>
</dependency>

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///test?useSSL=false&useServerPrepStmts=true
username=root
password=131411
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

@Test
public void Test1() throws Exception {
    //加载配置文件
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/main/resources/properties.properties"));
    //获取数据库连接池对象
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    //获取连接
    Connection connection = dataSource.getConnection();
    System.out.println(connection);
}

-- 删除tb_brand表
drop table if exists tb_brand;
-- 创建tb_brand表
create table tb_brand (
    -- id 主键
    id int primary key auto_increment,
    -- 品牌名称
    brand_name varchar(20),
    -- 企业名称
    company_name varchar(20),
    -- 排序字段
    ordered int,
    -- 描述信息
    description varchar(100),
    -- 状态：0：禁用  1：启用
    status int
);
-- 添加数据
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),
       ('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1),
       ('小米', '小米科技有限公司', 50, 'are you ok', 1);

public class Brand {
    // id 主键
    private Integer id;
    // 品牌名称
    private String brandName;
    // 企业名称
    private String companyName;
    // 排序字段
    private Integer ordered;
    // 描述信息
    private String description;
    // 状态：0：禁用  1：启用
    private Integer status;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public Integer getOrdered() {
        return ordered;
    }

    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}

 @Test
public void Test2() throws Exception{
    //加载配置文件
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/main/resources/properties.properties"));
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    //编写SQL
    String sql = "select *from tb_brand";
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);
    ArrayList<Brand> arrayList = new ArrayList<>();
    while (resultSet.next()){
        Brand brand = new Brand();
        brand.setId(resultSet.getInt("id"));
        brand.setBrandName(resultSet.getString("brand_name"));
        brand.setCompanyName(resultSet.getString("company_name"));
        brand.setOrdered(resultSet.getInt("ordered"));
        brand.setDescription(resultSet.getString("description"));
        brand.setStatus(resultSet.getInt("status"));
        arrayList.add(brand);
    }
    System.out.println(arrayList);
    resultSet.close();
    statement.close();
    connection.close();
}

@Test
public void Test3() throws Exception{
    //加载配置文件
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/main/resources/properties.properties"));
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    String sql = "insert into tb_brand (brand_name, company_name, ordered, description, status) values(?,?,?,?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    String brandName = "香飘飘";
    String companyName = "香飘飘";
    int ordered = 1;
    String description = "绕地球一圈";
    int status = 1;
    preparedStatement.setString(1,brandName);
    preparedStatement.setString(2,companyName);
    preparedStatement.setInt(3,ordered);
    preparedStatement.setString(4,description);
    preparedStatement.setInt(5,status);
    int i = preparedStatement.executeUpdate();
    if (i>0){
        System.out.println("插入成功");
    }else {
        System.out.println("插入失败");
    }

    preparedStatement.close();
    connection.close();
}

@Test
public void Test4() throws Exception{
    //加载配置文件
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/main/resources/properties.properties"));
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    String sql = "update tb_brand set description = ? where id = ?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    String description = "绕地球三圈";
    int id = 4;
    preparedStatement.setString(1,description);
    preparedStatement.setInt(2,id);
    int i = preparedStatement.executeUpdate();
    if (i>0){
        System.out.println("更新成功");
    }else {
        System.out.println("更新失败");
    }
    preparedStatement.close();
    connection.close();
}

@Test
public void Test5() throws Exception{
    //加载配置文件
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/main/resources/properties.properties"));
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    String sql = "delete from tb_brand where id = ?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    int id = 4;
    preparedStatement.setInt(1,id);
    int i = preparedStatement.executeUpdate();
    if (i>0){
        System.out.println("删除成功");
    }else {
        System.out.println("删除失败");
    }
    preparedStatement.close();
    connection.close();
}

create table tb_user(
  id int primary key auto_increment,
  username varchar(20),
  password varchar(20),
  gender char(1),
  addr varchar(30)
);

INSERT INTO tb_user VALUES (1, 'zhangsan', '123', '男', '北京');
INSERT INTO tb_user VALUES (2, '李四', '234', '女', '天津');
INSERT INTO tb_user VALUES (3, '王五', '11', '男', '西安');

<dependencies>
    <!--mybatis 依赖-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.9</version>
    </dependency>
    <!--mysql 驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.29</version>
    </dependency>
    <!-- 添加slf4j日志api -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.36</version>
    </dependency>
    <!-- 添加logback-classic依赖 -->
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-classic</artifactId>
        <version>1.2.3</version>
    </dependency>
    <!-- 添加logback-core依赖 -->
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-core</artifactId>
        <version>1.2.3</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.1</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.24</version>
    </dependency>
</dependencies>

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <!--
        CONSOLE ：表示当前的日志信息是可以输出到控制台的。
    -->
    <appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>[%level] %blue(%d{HH:mm:ss.SSS}) %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>
        </encoder>
    </appender>

    <logger name="com.itheima" level="DEBUG" additivity="false">
        <appender-ref ref="Console"/>
    </logger>

    <!--

      level:用来设置打印级别，大小写无关：TRACE, DEBUG, INFO, WARN, ERROR, ALL 和 OFF
     ， 默认debug
      <root>可以包含零个或多个<appender-ref>元素，标识这个输出位置将会被本日志级别控制。
      -->
    <root level="DEBUG">
        <appender-ref ref="Console"/>
    </root>
</configuration>