drop database if exists 数据库名;

use 数据库名;

show databases;

CREATE TABLE IF NOT EXISTS teacher( 
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'tid',
    `name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'tname', 
    `psssword` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'tpwd',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT 'sex',
    `brithday` DATETIME DEFAULT NULL COMMENT 'bornday',
    `address` VARCHAR(100) DEFAULT NULL COMMENT 'address',
    `email` VARCHAR(50) DEFAULT NULL COMMENT 'mail',
    PRIMARY KEY (`id`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4,COLLATE=utf8mb4_general_ci;




//CREATE TABLE [IF NOT EXISTS] 表名(
`字段名` 列类型 [字段属性] [索引] [注释],
`字段名` 列类型 [字段属性] [索引] [注释],
`字段名` 列类型 [字段属性] [索引] [注释],
[ PRIMARY KEY (`字段名`) ]
)[表类型] [字符集设置] [注释]

show create database `库名`; --查看库的定义
show create table `表名`; ---查看该表的定义
desc `表名` --显示表结构

rename tb_txt to tb_txt_new；

alter tb_txt_new rename column txtid to tid；

alter table tb_txt_new modify（tid vachar2(20))；

alter table tb_txt new add col_test name varchar2(30);

drop table

insert into 表名 [（字段列表）] values（值列表）;

insert into tb_user values(1001，'test'，'test123' ，null，'女’，null，sysdate）;

insert into tb_user_copy values(select * from tb_user）；

insert into tb_user（username，userid，userpwd）values（tom,1002，tompwd）；

insert into tb_user_copy(username,userid,userpwd） values(select username userid，userpwd from tb_user)

insert into tb_txt(txtid,title,userid）（1001，title1，123（select userid from tb_user where username=随笔大师））;

update tb_user set userpwd=8888 where 1=1;

update tb_user set userpwd=good , age=29 where username='zzz' and pwd='123'

update tb_userset(username， userpwd）= （select  'good'，'block’ from dual） where userid=1；

delete from tb_user where userid<10;

delete from tb user;

select * |colname  [,...] from table [alias]

select * from 表名;

select 列1 , 列2 列n from 表名;

select 自定顺序列1 , 自定顺序列2 from 表名;

select distinct 列名 from 表名;

select 列名 改名 from 表名;
select 列名 as 改名 from 表名;
//多列在from前加，号再将列名 改名写上

select 列名1 , 列名2 from 表名 orer by 列名(要按此排序) desc(降序)/asc(升序);

select 列名1 , 列名2 from 表名 orer by 列名 desc(降序)/asc(升序),列名 desc(降序)/asc(升序);

select 查询内容 from 数据来源 where 行记录条件

select * from emp where deptno = (select deptno from dept where dname = 'SALES')

select * from emp where deptno in 'xx'
--查询所有值条件为deptno为xx的具体条目

select * from emp where ename like '%S%' ;

//查询emp表里ename列 含有S的属性
注意：内容区分大小写

--inner join

SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s
INNER JOIN result AS r
ON s.`studentno` =r.`studentno`;

--right join

SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.`studentno` =r.`studentno`;

--left join

SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.`studentno` =r.`studentno`;

SELECT f.`categoryname` AS father ,s.`categoryname` AS son
FROM category AS f,category AS s
WHERE f.categoryid = s.pid

select deptno,count(deptno) from emp group by deptno;

select 1 from emps
select ename,1 from emp；

select 条件+nvl(列，返回的数值) from 表;

select 列名/条件||'要拼接的字符串' form 表名；

set autocommit = 0	/*关闭自动提交*/
set autocommit = 1	/*开启自动提交*/



--事务的手动处理
set autocommit = 0	/*关闭自动提交*/

start transcation --标记一个事务的开始,之后sql都在同一个事务内
insert xxx
insert xxx

--提交:持久化(succes)
commit
--回滚:回到某个位置(fail)
rollback

--事务结束
set autocommit = 1	/*开启自动提交*/

--了解
savepoint 名称 --设置事务保存点
rollback to savepoint 名称  --回滚到保存点
release savepoint 名称  --撤销保存点

--索引的使用
--1.在创建表时给字段增加索引
--2.创建完毕后增加索引

--显示所有索引
show index from student
--show index from 表名


--增加一个全文索引(索引名) 列名
alter table school.student add fulltext index `studentname`(`studentname`)

--explain 分析sql执行情况
explain select * from student; --非全文索引

explain select * from student where match(studentname) against('刘');

--创建用户 
CREATE USER loh IDENTIFIED BY '123456'

--修改当前用户密码
SET PASSWORD = PASSWORD('12345678')
--修改指定用户密码
SET PASSWORD FOR loh@% = PASSWORD('12345678')

--重命名 rename user 旧名字 to 新名字
rename user loh to loh2

--用户授权all privileges全部权限，库，表
--all privileges权限，包含除授权权限外的全部权限
grant all privileges on *.* to loh2

--查询权限
show grants for loh2
show grants for root@localhost

--root用户权限：grant all privileges on *.* 'root'@'localhost' with grant option

--撤销权限, revoke哪些权限在哪个库撤销
revoke all privileges on *.* from loh2

--删除用户
drop user loh2

package com.loh.utils;

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

public class TestSqlInject {
    public static void main(String[] args) {
        login("' or' 1=1","123456");
    }
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try{

            conn = JdbcUtils.getConnection();
            st=conn.createStatement();

            //SELECT * FROM USER WHERE `name` = 'zhangsan' AND `password` = '123456';
            String sql = "SELECT * FROM USER where `name` ='"+username+"'and `password` = '"+password+"'";
            rs = st.executeQuery(sql);

            while (rs.next()){
                System.out.println("name："+rs.getObject("name"));
                System.out.println("pwd："+rs.getObject("password"));
                System.out.println("====================");
            }

        }catch (Exception e){}



    }
}

package com.loh.utils;

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


public class TestPreparedStatementSelect {
    public static void main(String[] args) {
        login();
    }
    public static void login () {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {

            conn = JdbcUtils.getConnection();
            //使用？作为占位符代替参数预编译
            String sql = "select * from user where id = ?";
            st = conn.prepareStatement(sql);
            //手动给参数赋值
            //set可以指定参数类型，(sql参数下标,设置值)
            st.setInt(1, 8);

            rs = st.executeQuery();
            while (rs.next()){
                System.out.println("id："+rs.getObject("id"));
                System.out.println("name："+rs.getObject("name"));
                System.out.println("pwd："+rs.getObject("password"));
                System.out.println("email："+rs.getObject("email"));
                System.out.println("birth："+rs.getObject("birthday"));
            }

        } catch (Exception e) {

        } finally {
            JdbcUtils.release(conn, st, null);
        }
    }
}

package com.loh.utils;

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

public class TestPreparedStatementInsert {
    public static void main(String[] args) {
        login();
    }
    public static void login(){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try{

            conn = JdbcUtils.getConnection();
            //使用？作为占位符代替参数预编译
            String sql = "insert into user(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
            st=conn.prepareStatement(sql);
            //手动给参数赋值
            //set可以指定参数类型，(sql参数下标,设置值)
            st.setInt(1,9);
            st.setString(2,"wangwu");
            st.setString(3,"654321");
            st.setString(4,"654321@da.com");
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
            int i = st.executeUpdate();

            if(i > 0 ){
                System.out.println("插入成功");
            }


        }catch (Exception e){

        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

package com.loh.utils;

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

public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try{

            conn = JdbcUtils.getConnection();

            //关闭数据库自动提交
            conn.setAutoCommit(false);
            
            String sql1 = "update user set `id` = '15' where id = '10'" ;
            st=conn.prepareStatement(sql1);
            String sql2 = "update user set `id` = '16' where id = '8'" ;
            st=conn.prepareStatement(sql2);

            int i=st.executeUpdate();

            //业务完成，提交事务
            conn.commit();
            
            if(i > 0 ){
                System.out.println("修改成功");
            }
            
        }catch (SQLException e){
            try {
            conn.rollback();
            }catch (SQLException e1){
                e1.printStackTrace();
            }
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }

}