标签：java String -- 数据库笔记 sql import getconnect

数据库

1.操作数据库

CREATE DATABASE AAA--创建
DROP DATABASE AAA--删除
USE school--使用

2.创建表

CREATE TABLE if NOT EXISTS `tb_usear`(  
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '序号',  
  `age` INT(2) NOT NULL COMMENT '年龄',  
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',  
  `databirth` DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '数据',  
  PRIMARY KEY (`id`)  
)

SHOW CREATE TABLE qq --查询表的创造语句
DESC qq --查询表的结构

3.引擎区别

4.修改和删除表

USE school  
ALTER TABLE student RENAME AS student1
ALTER TABLE student1 CHANGE age age1 VARCHAR(10)
ALTER TABLE student1 ADD age INT(4)
ALTER TABLE student1 MODIFY age1 int(4)

5.外键

ALTER TABLE student1 
ADD CONSTRAINT `key_id` FOREIGN KEY (`id`) REFERENCES `tb_user` (`id`)

操作表

1.增加内容

INSERT INTO `tb_user` (age,sex) VALUES (2,'女'),(3,'女'),(4,'女')
--公式 insert into 表名 （属性值，属性值）values （对应的值），（对应的值），（对应的值）...........

2.修改内容

UPDATE `tb_user` SET sex='女' ,`data`='2002-03-04' WHERE id=1;--！=，>,<,<=,>=
--特殊的 WHERE id between 2 and 5;
--特殊的 WHERE id =2 and sex=’男‘;
--特殊的 WHERE id =2 or sex=’男‘;

3.删除内容

delete FROM qq WHERE `id`=6 --删除那一条语句
delete FROM  qq  -- 清空表
TRUNCATE TABLE qq  -- 清空表

清空类型	delete	truncate
相同点	都是清空表的值，约束和字段不会被清除	都是清空表的值，约束和字段不会被清除
不同点	delete，清空后，自增不会被清空	truncate ，清空后，自增从0开始

delete在不同引擎的区别，在innerdob，引擎中，断电会自增会从0开始，因为他保存在内存中，会直接消失。在上一个默认引擎中不会丢失自增，因为他的数据保存在文件里。

DQL查询语句

SELECT CONCAT('姓名：',studentname) AS '名字' FROM `student`  -- 字符串拼接
SELECT studentno AS '学号',studentname AS '姓名' FROM `student`  --查询多个属性值

select  studentno FROM `student`
WHERE studentno>1000 --查询用大于等于

模糊查询

select  * FROM `student`
WHERE studentname = '吕文年'
--查询知道了名字的
select  * FROM `student`
WHERE studentname LIKE '张_' 
--查询知道姓名的， --张__,几个杠后面几个字
select  *  FROM `student`
WHERE studentname LIKE '张%
--查询已张开头的名字
select  * FROM `student`
WHERE studentname LIKE '%文%'
--查询知道中间的字的
select  * FROM `student`
WHERE studentname LIKE '%年'
--查询只知道最后一个字的

------------------------------in-----------------------------
select  * FROM `student`
WHERE studentno in (1001,1000,1002,1003)  --查询在多个值
--------------------------null---------------------
select  * FROM `student`
WHERE sex IS not NULL  -- 查询是不是空
----------------------------between----------------------
select  * FROM `student`
WHERE studentno BETWEEN 1000 AND 1010 --查询区间

去重

select  DISTINCT studentno FROM `result`
--去重

筛选

select  *  FROM `result`
WHERE studentno = 1000 AND subjectno='1'
-- 查询学号1000，考试的第一科成绩

多表查询

查询没有考试的人

SELECT studentname,s.studentno,subjectno,studentresult
FROM student AS s
LEFT JOIN  result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

查询	解释
中间查询	连接两个都有一个属性的
左查询	返回左面有值的
右查询	返回右面有值的

三表查询

SELECT studentname,s.studentno,subjectname,studentresult
FROM student AS s
RIGHT JOIN  result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS su
ON r.subjectno=su.subjectno
-----------
select ＋所需要的属性值名字
from 左表 as 新名字
inner(left)(right) join 右表 as 新名字
on 左表.属性=右表.属性

SELECT studentno,studentname,`subjectname`,studentresult
FROM `student` s
INNER JOIN `subject` sub
WHERE s.gradeid=sub.gradeid
INNER JOIN result
WHERE result.subjectno=s.subjectno

自连接查询

一张表拆为两张一样的表

SELECT a.categoryName AS '父亲',b.categoryName AS '儿子'
FROM `category` AS a,`category` AS b
WHERE a.categoryid = b.pid

自联结查询练习

SELECT r.studentno,studentname,`subjectname`,studentresult
FROM `result` r
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
INNER JOIN student
ON student.studentno=r.studentno
WHERE subjectname='高等数学-1'

这是查询参加高等数学考试的学生的，姓名学号，考试科目，考试成绩

排序和分页

order by 属性值 asc --升序
order by 属性值 desc --降序

limit 0,5   --0表示起始页，5表示页面大小

子查询

嵌套查询

SELECT s.studentno,studentname,studentresult
FROM result r
INNER JOIN student s 
WHERE r.studentno=s.studentno AND subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname='C语言-1'
)

java连接数据库

package JDBCDemo;

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

public class Demo1 {
    public static void main(String[] args) throws Exception {
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF-8&useSSL=true";
        String username = "root";
        String password = "123456";
        //连接数据库，connect代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //获取数据库执行对象
        Statement statement = connection.createStatement();
        String sql = "select * from users";
        //执行sql语句
        ResultSet resultSet = statement.executeQuery(sql);
        //获得返回值
        while (resultSet.next()) {
            System.out.print("id=" + resultSet.getObject("id")+" ");
            System.out.print("NAME=" + resultSet.getObject("NAME")+" ");
            System.out.print("PASSWORD=" + resultSet.getObject("PASSWORD")+" ");
            System.out.print("email=" + resultSet.getObject("email")+" ");
            System.out.print("birthday=" + resultSet.getObject("birthday")+" ");
            System.out.println();
        }
        //关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

增删改

包装jdbc

增删改

package JDBCDemo;

import JDBCDemo.Utis.Jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class AddDeleteUpdate {
    public static void main(String[] args) throws Exception {
        Connection getconnect = null;
        Statement statement = null;


        try {
            getconnect = Jdbc.getconnect();
            statement = getconnect.createStatement();
            String sql = "INSERT INTO users(id, NAME, PASSWORD, email, birthday)  \n" +
                    "VALUES  \n" +
                    "('zhansan3', '123456', '[email protected]', '1980-12-04'),  \n" +
                    "('lisi3', '123456', '[email protected]', '1981-12-04'),  \n" +
                    "('wangwu3', '123456', '[email protected]', '1979-12-04');";//添加
            String sql2 = "delete FROM users WHERE `id`>10 "; //删除
            String sql3 = "UPDATE `users` SET password='123789' WHERE id=1; "; //修改
            int i = statement.executeUpdate(sql3);
            if (i > 0) {
                System.out.println("修改成功");
            }
        } finally {
            Jdbc.relase(getconnect, statement, null);
        }

    }
}

查询

package JDBCDemo;

import JDBCDemo.Utis.Jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class AddDeleteUpdate {
    public static void main(String[] args) throws Exception {
        Connection getconnect = null;
        Statement statement = null;


        try {
            getconnect = Jdbc.getconnect();
            statement = getconnect.createStatement();
            String sql = "INSERT INTO users(id, NAME, PASSWORD, email, birthday)  \n" +
                    "VALUES  \n" +
                    "('zhansan3', '123456', '[email protected]', '1980-12-04'),  \n" +
                    "('lisi3', '123456', '[email protected]', '1981-12-04'),  \n" +
                    "('wangwu3', '123456', '[email protected]', '1979-12-04');";//添加
            String sql2 = "delete FROM users WHERE `id`>10 "; //删除
            String sql3 = "UPDATE `users` SET password='123789' WHERE id=1; "; //修改
            int i = statement.executeUpdate(sql3);
            if (i > 0) {
                System.out.println("修改成功");
            }
        } finally {
            Jdbc.relase(getconnect, statement, null);
        }

    }
}

业务登陆界面

package JDBCDemo.Utis;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class Login {

        public static void main(String[] args) throws Exception {
            Connection getconnect = null;
            Statement statement = null;
            ResultSet resultSet = null;
            String name = "zhansan";
            String password = "123789";
            try {
                getconnect = Jdbc.getconnect();
                statement = getconnect.createStatement();

                String sql = "select * from users where `NAME`='" + name + "' AND `PASSWORD`='" + password + "'";
                resultSet = statement.executeQuery(sql);
                while (resultSet.next()) {
                    System.out.println("name=" + resultSet.getObject("NAME"));
                    System.out.println("password=" + resultSet.getObject("PASSWORD"));
                }
            } finally {
                Jdbc.relase(getconnect, statement, resultSet);
            }


        }
    }

preparestatement

增加

package JDBCDemo.preparestatement;

import JDBCDemo.Utis.Jdbc;

import javax.xml.transform.Result;
import java.sql.*;

public class add {
    public static void main(String[] args) throws Exception {
        Connection getconnect = null;
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            getconnect = Jdbc.getconnect();
            String sql = " INSERT INTO users(`NAME`, PASSWORD, email, birthday) VALUES (?, ?, ?, ?)";

             statement = getconnect.prepareStatement(sql);
            statement.setString(1, "吕文年003");
            statement.setString(2, "123456");
            statement.setString(3, "[email protected]");
            statement.setDate(4, Date.valueOf("1981-12-04"));
            int i = statement.executeUpdate();
            if (i>0){
                System.out.println("添加成功");
            }
        } finally {
            Jdbc.relase(getconnect, statement, result);
        }

    }
}

删除

package JDBCDemo.preparestatement;

import JDBCDemo.Utis.Jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class delete {
    public static void main(String[] args) throws Exception {
        Connection getconnect = null;
        PreparedStatement preparedStatement = null;

        try {
            getconnect = Jdbc.getconnect();
            String sql = "delete FROM users WHERE `id`>?";
            preparedStatement = getconnect.prepareStatement(sql);
            preparedStatement.setInt(1, 10);
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("删除成功");
            }
        } finally {
            Jdbc.relase(getconnect, preparedStatement, null);
        }
    }
}

更新

package JDBCDemo.preparestatement;

import JDBCDemo.Utis.Jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class update {
    public static void main(String[] args) throws Exception {
        Connection getconnect = null;
        PreparedStatement preparedStatement = null;
        try {
            getconnect = Jdbc.getconnect();
            String sql = "UPDATE `users` SET `NAME`=? WHERE id=?;";
            preparedStatement = getconnect.prepareStatement(sql);
            preparedStatement.setString(1, "哈哈哈");
            preparedStatement.setInt(2, 1);
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("更新成功");
            }
        } finally {
            Jdbc.relase(getconnect, preparedStatement, null);
        }

    }
}

查询

package JDBCDemo.preparestatement;

import JDBCDemo.Utis.Jdbc;

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

public class select {
    public static void main(String[] args) throws Exception {
        Connection getconnect = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            getconnect = Jdbc.getconnect();
            String sql = "select * from users where id<?";
            preparedStatement = getconnect.prepareStatement(sql);
            preparedStatement.setInt(1, 10);
            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("=======================================");
            }
        } finally {
            Jdbc.relase(getconnect, preparedStatement, resultSet);
        }
    }
}

业务登录防漏

package JDBCDemo.preparestatement;

import JDBCDemo.Utis.Jdbc;

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

public class login {
    public static void main(String[] args) throws Exception{
        String username = "哈哈哈";
        String password ="123789";
        Connection getconnect =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        try {
             getconnect = Jdbc.getconnect();
            String sql ="select * from users where `NAME`=? AND PASSWORD=? ";
             preparedStatement = getconnect.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
             resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {

                System.out.println("name=" + resultSet.getObject("NAME"));
                System.out.println("password=" + resultSet.getObject("PASSWORD"));

            }
        } finally {
            Jdbc.relase(getconnect,preparedStatement,resultSet);
        }


    }
}

java实现简单的连接数据库，实现事务

package JDBCDemo.preparestatement;

import JDBCDemo.Utis.Jdbc;

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

public class Ban {
    public static void main(String[] args) throws Exception{

        Connection getconnect =null;
        Statement statement =null;

        try {
             getconnect = Jdbc.getconnect();
            getconnect.setAutoCommit(false);//关闭自动提交
            String sql ="update bank set money = money - 100 where `name` = 'A' ";//转账人
             statement = getconnect.createStatement();
            statement.executeUpdate(sql);
            String sql2 ="update bank set money = money + 100 where `name` = 'B'";//收款人
             statement = getconnect.createStatement();
            statement.executeUpdate(sql2);
        } catch (SQLException e) {

            try {
                getconnect.rollback();//捕获到异常，就回滚
            } catch (RuntimeException ex) {
                throw new RuntimeException(ex);
            }
        } finally {
            getconnect.commit();//提交事务
            System.out.println("成功");
        }

    }
}

标签：java,String,--,数据库,笔记,sql,import,getconnect
From： https://www.cnblogs.com/inian/p/18121297

数据库笔记

数据库