mysql基础知识

DDL:
-- 一、数据库操作之：

#1、创建库：
    create database [if not exists] 库名 [character set ‘编码集’]
    #if not exists ：加上该字段如果库存在则不报错（不存在则创建存在则不创建）
    简洁版:create database 库名;

#2、创建库的时候指定库的编码集:
    create database 库名 character set '编码集';

#3、修改库的编码集：
    alter database 库名 character set '编码集';

#4、删除库:
    drop database [if exists] 库名;
    #if exists : 加上该字段库不存在则不会报错（库存在则删除不存在则不删）
    简洁版:drop database 库名;

#5、选库:
    use 库名;

#6、查看库的信息：
    show create database 库名;

#7、查看所有的库:
    show databases;


-- 二、数据表操作之：

#1、查看所有的表:
    show tables;

#2、查看表结构:
    desc 表名;

#3、查看表的信息:
    show create table 表名;

#4、删除表:
    drop table [if exists] 表名
    #if exists : 加上该字段表不存在则不会报错(表存在则删除不存在则不删)
    简洁版:drop table 表名;

#5、创建表:
    #方式一
    create table [if not exists] 表名(
        #int类型，自增
        emp_id INT PRIMARY KEY AUTO_INCREMENT, -- primary key与auto_increment顺序随便
        #最多保存20个中英文字符
        emp_name CHAR (20),
        salary DOUBLE,
        #日期类型
        birthday DATE,
        #主键
        PRIMARY KEY (emp_id)
    )character set '编码集';  #如果不指定编码集默认的编码集和库的编码集相同

    #方式二 ：基于查询结果创建一张新表(有数据)
    create table 表名
    [as]
    查询语句;

    #方式三 :基于现有的表结构创建一张新表(只有表结构没有数据)
    create table 新表名 like 存在的表的表名;

    附加表的类型：
        INT:从-2^31到2^31-1的整型数据。存储大小为 4个字节
        CHAR(size):定长字符数据。若未指定，默认为1个字符，最大长度255
        VARCHAR(size):可变长字符数据，根据字符串实际长度保存，必须指定长度
        FLOAT(M,D):单精度，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
        DOUBLE(M,D):双精度。D<=M<=255,0<=D<=30，默认M+D<=15
        DATE:日期型数据，格式’YYYY-MM-DD’
        BLOB:二进制形式的长文本数据，最大可达4G
        TEXT:长文本数据，最大可达4G

        整型 ： tinyint/samllint/mediumint/int/bigint
        tinyint : 1字节    smallint:2字节  mediumint:3字节  int:4字节  bigint:8字节

        浮点型:  float(n,m)/double(n,m)/decimal(n,m)
        Float:4字节  double:8字节  decimal(m,n):如果m>n为m+2否则为n+2
        n:代表整数部位+小数部位的最大长度
        m：代表小数部位的最大长度
        例：FLOAT(5,3) : -99.999 ~ 99.999

        字符型：char(n)/varchar(n)/text
        text:用于保存较长的文本，比如备注文字，协议文字
        char(n) 能保存的字符的最大个数，可选，默认是1 ，固定长度
        varchar(n)能保存的字符的最大个数，必写，可变长度

        日期型：datetime/date/time/timestamep(时间戳)/year
        date:只有日期(3字节)  time:只有时间(3字节)  
        Year:只有年份值（1字节）范围1901-2155
        datetime :日期+时间 ，占用8个字节，1900-1-1 ~ 9999-12-31 
        timestamp:日期+时间，占用4个字节 ，1970-1-1 ~ 2038-12-31

#6、对表中的列进行增，删，改的操作:
    #①添加字段
    alter table 表名 add [column] 字段名 字段类型;

    #②删除字段
    alter table 表名 drop [column] 字段名;

    #③修改字段的名字
    alter table 表名 change [column] 原字段名 新字段名 字段类型：

    #④修改字段的类型
    alter table 表名 modify [column] 字段名 字段类型;

#7、修改表的名字:
    alter table 旧表名 rename to 新表名;

#8、清空表(删表再建表)
    truncate table 表名;
    注意：TRUNCATE TABLE不能事务回滚，delete from可以事务回滚



DML:
-- 三、数据表数据操作之：

#1、插入(增)
    CREATE TABLE person(
        id INT,
        NAME VARCHAR(20),
        age INT
    );
    #①向表中插入一条数据(插入数据的字段可选择):
        #insert into 表名(字段名1，字段名2，...) values(值1，值2，......)
        INSERT INTO person(id,NAME,age) VALUES(1,'ab',18);    -- 选择插入三个字段数据
        INSERT INTO person(id,NAME) VALUES(2,'cd');    -- 选择插入两个字段数据

    #②如果插入的是全字段那么表名后面的字段名可以省略不写:
        INSERT INTO person VALUES(3,'df',20);

    #③向表中插入多条数据:
        #insert into 表名(字段名1，字段名2，...) values(值1，值2，......),(值1，值2，......),........
        INSERT INTO person VALUES(4,'aaa',18),(5,'ccc',18),(6,'fff',18);

    #④将查询的结果插入到表中:
        insert into 表名(字段名1，字段名2,...)
        select xxxxx;
        注意：查询的结果的字段和被插入的字段的数量和类型必须保持一致。

        INSERT INTO person(id,NAME)
        SELECT employee_id,first_name
        FROM myemployees.`employees`;

#2、更新(改)
    update 表名 set 字段名1=值1，字段名2=值2,..... [where 过滤条件]
        UPDATE person SET NAME='zl' WHERE id=6;
        UPDATE person SET NAME='yy',age=20 WHERE id=4;
        UPDATE person SET NAME='bb';

#3、删除(删)
    delete from 表名 [where 过滤条件]
        DELETE FROM person WHERE id>90 OR id=0;
        DELETE FROM person;


-- 事务

-- 四、数据的事务操作之：

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。

需求：让AA给CC转账1000元
	1.让AA减去1000元 -- 操作数据库
	System.out.println(1/0);
	2.让CC加上1000元 -- 操作数据库

    遇到的问题：AA减钱成功CC加钱失败。一个成功一个失败
    如何解决：让所有的操作变成一个整体要么都成功要么都失败。	
    使用事务解决：
	    try{
		    开启事务
		    1.让AA减去1000元 -- 操作数据库
		    System.out.println(1/0);
		    2.让CC加上1000元 -- 操作数据库
		    事务提交
	    }catch(Exception e){
		    事务回滚（撤销）
	    }

    #开启事务：1.set autocommit=false;
    #	   start transaction;一旦rollback或者commit事务结束

    #事务提交(一旦提交将不能回滚)：commit
    #事务回滚（撤销）：rollback

    SET autocommit=FALSE;#开启事务

    DELETE FROM person;#删除数据

    ROLLBACK;#回滚

    COMMIT;#提交

    SET autocommit=TRUE;#允许自动提交---下面的操作不再受事务的控制



-- 约束

-- 五、数据的约束操作之：

#1、约束的分类 ：列级约束 vs 表级约束
    列级约束：同时只能约束一列
    表级约束：可以同时约束多列

#2、约束的分类：
        NOT NULL 非空约束，规定某个字段不能为空
        UNIQUE  唯一约束，规定某个字段在整个表中是唯一的(唯一约束，允许出现多个空值：NULL)
        PRIMARY KEY  主键(非空且唯一)
        FOREIGN KEY  外键
        CHECK  检查约束 （mysql不支持）
        DEFAULT  默认值
   
#3、说明：①not null和default只有列级约束; ②添加主键约束和唯一约束的时候自动创建索引

#4、添加约束：创建表时添加约束与创建表后添加约束
    ①#创建表时添加列级约束
        CREATE TABLE s1(
            id INT PRIMARY KEY,#主键
            NAME VARCHAR(20) NOT NULL,#非空约束
            sid INT UNIQUE,#唯一约束
            age INT DEFAULT 18 #默认值
        );

    ②#创建表时添加表级约束
        CREATE TABLE s2(
            id INT,
            sid INT,
            NAME VARCHAR(20),#最后一个字段要加","因为下面有内容
            #CONSTRAINT 索引名 PRIMARY KEY(字段名1，字段名2，.....)
            CONSTRAINT s2_id_sid PRIMARY KEY(id,sid)
        );

        CREATE TABLE s3(
            id INT,
            sid INT,
            NAME VARCHAR(20),#最后一个字段要加","因为下面有内容
            #CONSTRAINT 索引名 unique(字段名1，字段名2，.....)
            CONSTRAINT s3_id_sid UNIQUE(id,sid)
        );

    ③#创建表后添加约束
        CREATE TABLE s1(
            id INT,
            NAME VARCHAR(20),
            sid INT,
            age INT 
        );

        1>、primary key
            添加约束 : alter table 表名 add primary key (字段名)
            修改约束 : alter table 表名 modify  字段名 类型 primary key
            删除约束 : alter table 表名 drop primary key
                ALTER TABLE s1 ADD PRIMARY KEY(id);
                ALTER TABLE s1 DROP PRIMARY KEY;
                ALTER TABLE s1 MODIFY id INT PRIMARY KEY;

        2>、unique:
            添加约束 : alter table 表名  add unique(字段名)
            添加约束 : alter table 表名 add constraint  索引名 unique(字段名)
            修改约束 ：alter table 表名 modify 字段名 类型  unique
            删除约束 ：alter table 表名 drop index 索引名
                ALTER TABLE s1 ADD UNIQUE(sid);
                ALTER TABLE s1 DROP INDEX sid;#默认索引名和字段名相同
                ALTER TABLE s1 MODIFY age INT UNIQUE;
                ALTER TABLE s1 ADD CONSTRAINT s1_id_sid UNIQUE(id,sid);

        3>、not null
            添加约束 : alter table 表名 modify 字段名 字段类型 not null
            删除约束 : alter table 表名 modify 字段名 字段类型 null

        4>、default:
            设置默认约束 : alter table tb_name alter 字段名 set default value;
            删除默认约束 : alter table tb_name alter 字段名 drop default;

        5>、foreign key:
            添加约束 : ALTER TABLE 表名  ADD  [CONSTRAINT emp_dept_id_fk]   FOREIGN KEY(dept_id) REFERENCES dept(dept_id);
            删除约束 ：alter table 表名 drop foreign key  索引名

            #外键约束：
	            1>>.先创建主表还是从表？主表
	            2>>.插入数据先往主表还是从表? 主表
	            3>>.删除数据先删除主表还是从表？从表

            #演示
            #部门表 ：主表
            CREATE TABLE dept(
                dept_id INT AUTO_INCREMENT PRIMARY KEY,
                dept_name VARCHAR(20)
            );

            员工表 ：从表
            CREATE TABLE emp(
                emp_id INT AUTO_INCREMENT PRIMARY KEY,
                last_name VARCHAR(15),
                dept_id INT,
                #CONSTRAINT 索引名 FOREIGN KEY(员工表的字段名) REFERENCES 部门表(部门表的字段名)
                CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
            );

            INSERT INTO dept(dept_id,dept_name) VALUES(10,'HR')
            INSERT INTO emp(last_name,dept_id) VALUES('aaa',10);

            DELETE FROM emp WHERE dept_id=10;
            DELETE FROM dept WHERE dept_id=10;

            #级联删除
            CREATE TABLE dept(
                dept_id INT AUTO_INCREMENT PRIMARY KEY,
                dept_name VARCHAR(20)
            );

            CREATE TABLE emp(
                emp_id INT AUTO_INCREMENT PRIMARY KEY,
                last_name VARCHAR(15),
                dept_id INT,
                #ON DELETE CASCADE :级联删除（删除部门时部门的员工全部删除掉）
                CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id) ON DELETE CASCADE
            );

            DELETE FROM dept WHERE dept_id=10;

#5、分页
    limit 索引位置,数据的条数

    分页公式：
	    limit (当前页数-1)*10,10 
        SELECT * FROM employees LIMIT 0,10;

        SELECT * FROM employees LIMIT 10,10;


-- JDBC

-- 六、Java提供的一套用来操作数据库的接口

#1、创建数据库连接对象
    ①准备工作：
        1.保证数据库可以正常使用(如果不能运行查看服务)
        2.保证数据库账号密码正确
        3.保证驱动包的版本数据库的版本匹配(尤其是8.0的同学别选错了)
        4.在工程/module下创建目录lib将驱动包放到该目录中
                 在驱动包上右键 --> add as library
     注意数据库8.0：
         1.com.mysql.cj.jdbc.Driver (Driver类的全类名)
         2.url要添加参数jdbc:mysql://localhost:3306/myemployees?serverTimeZone=Asia/Shanghai
    
     特别声明：
        /*
            1.Properties是一个Map,Properties是Hashtable的子类
            2.Properties的key和value是String(new对象的时候不用写泛型)
            3.可以通过Properties读取配置文件中的内容
        */

    ②方式一:Driver
        //1.创建对象
        //在同一个类中使用到不同包中相同名字的类那么该类需要使用全类名
        Driver driver = new com.mysql.jdbc.Driver();
        //2.调用方法
        /*
            connect(String url, java.util.Properties info)
            url : mysql的连接地址
	        jdbc:mysql://localhost:3306/myemployees
	        jdbc:mysql: 协议
	        localhost ：数据库所在服务器的ip地址
	        3306 ：端口号
	        myemployees: 库名
            info : 用来设置mysql的账号和密码
        */
        //mysql的连接地址
        String url = "jdbc:mysql://localhost:3306/myemployees";
        //用来设置mysql的账号和密码
        Properties properties = new Properties();//Properties是一个Map
        properties.setProperty("user","root");//设置Mysql用户名
        properties.setProperty("password","root");//设置Mysql密码
        //通过调用connect方法得到Connection对象有了该对象才能操作数据库
        Connection connect = driver.connect(url, properties);

        System.out.println(connect);

     方式二:通过DriverManager获取Connection对象
        //1.创建Driver对象
        Driver driver = new com.mysql.jdbc.Driver();
        //2.将Driver注册到DriverManager让DriverManager对Driver进行管理
        DriverManager.registerDriver(driver);
        //3.获取Connection对角
        /*
            getConnection(String url,String user, String password)
            url :mysql连接地址        user:mysql的用户名     password：mysql密码
        */
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myemployees","root", "root");
        System.out.println(connection);

     方式三:对方式二进行优化
        /*
	        Driver类中的静态代码块
	        static {
		        try {
			        DriverManager.registerDriver(new Driver());
		        } catch (SQLException var1) {
			        throw new RuntimeException("Can't register driver!");
		        }
	        }
        */

        //1.让Driver类初始化---初始化就会调用静态代码块
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取Connection对角
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myemployees","root", "root");

        System.out.println(connection);
        
     方式四:最终版 -- 通过读取配置文件的方式, 文件名称可以随意
        //1.读取文件内容-Properties
        //1.1创建Properties对象
        Properties properties = new Properties();
        //1.2创建流
        FileInputStream fis = new FileInputStream("jdbc.properties");
        //1.3加载流
        properties.load(fis);
        //1.4读取数据
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String className = properties.getProperty("className");
        //1.5关闭资源
        fis.close();

        System.out.println(user + " " + password + " " + url + " " + className);

        //2.获取Connection对象
        //2.1让Driver类初始化---初始化就会调用静态代码块
        Class.forName(className);
        //2.2获取Connection对角
        Connection connection = DriverManager.getConnection(url,user,password);

        System.out.println(connection);
        
        /*
            有关于配置文件的位置：
                在工程下无论是main方法还是单元测试方法默认路径在当前工程下
                在module下main方法的默认路径是当前工程下。单元测试方法的默认路径是当前module下
        */

#2、JDBC自己封装的工具类
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.sql.*;
    import java.util.Properties;

    public class JDBCUtils {

        private static String user;
        private static String password;
        private static String url;
        private static String className;

        static {
            FileInputStream fis = null;
            try {
                //1.读取文件内容-Properties
                //1.1创建Properties对象
                Properties properties = new Properties();
                //1.2创建流
                fis = new FileInputStream("jdbc.properties");
                //1.3加载流
                properties.load(fis);
                //1.4读取数据
                user = properties.getProperty("user");
                password = properties.getProperty("password");
                url = properties.getProperty("url");
                className = properties.getProperty("className");

                System.out.println(user + " " + password + " " + url + " " + className);
            }catch (Exception e){
                //终止程序的运行  将编译时异常转成运行时异常
                throw new RuntimeException(e.getMessage());
            }finally {
                //1.5关闭资源
                if (fis != null) {
                    try {
                        fis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        /*
            获取Connection对象
        */
        public static Connection getConnection() {
            try {
                //2.获取Connection对象
                //2.1让Driver类初始化---初始化就会调用静态代码块
                Class.forName(className);
                //2.2获取Connection对角
                Connection connection = DriverManager.getConnection(url, user, password);
                return connection;
            }catch (Exception e){
                //终止程序的运行  将编译时异常转成运行时异常
                throw new RuntimeException(e.getMessage());
            }
        }

        /*
            关闭资源
         */
        public static void close(PreparedStatement ps, Connection connection) {
            if (ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        public static void close(PreparedStatement ps, Connection connection, ResultSet rs) {
            close(ps,connection);
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }