MySQL详细基础教程_mysql数据库

create database IF NOT EXISTS school; -- 创建一个school，（IF NOT EXISTS）如果不存在school库就创建，存在就不创建。
IF NOT EXISTS  --解决已经有school数据库存在的异常，因为在有school数据库的情况下，再次创建就会出现异常。

drop database IF EXISTS hello;  -- 移除hello数据库，该数据不存在会报错。

use ` school `;  -- 如果你的表名或字段名带有特殊符号，就要加  ` ` 这个符号
select  --查询信息

show databases -- 查看所有数据库

select *
from student;
create database IF NOT EXISTS school;
drop database IF EXISTS hello;
create database test;
drop database test;
use school;
show databases;

-- 注意点：使用英文 () ，表的名称尽量用`` 括号起来，因为你也不知道什么时候它会变成一个关键字。
-- NOT NULL ID必须不为空
-- AUTO_INCREMENT 能自增的单词
-- COMMENT 注释
-- 字符串使用单引号 '' 括起来
-- 所有语句后面加, (英文的逗号) 最后一个字段不用加
-- PRIMARY KEY 主键，一般一个表只有一个，唯一的
create table if not exists `student`(
    `id` INT(4) NOT NULL  AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '张三' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `SEX` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET =UTF8

SHOW CREATE DATABASE school; -- 查看创建数据库的语句
SHOW CREATE TABLE STUDENT; -- 查看student创建表的定义语句
DESC student -- 查看表的结构

-- 修改表的名称 RENAME 重命名 AS 表名
ALTER TABLE STUDENT RENAME AS STUDENT1;
-- 增加表的字段 ADD 字段名 + 列属性
ALTER TABLE STUDENT1 ADD age int(11);
-- 修改表的字段（重命名 ，MODIFY修改约束！）
ALTER TABLE STUDENT1 MODIFY age1 varchar(11); -- 修改约束
-- CHANGE更改 age(旧名字) AGE1（新名字）列属性
ALTER TABLE STUDENT1 CHANGE age AGE1 INT(1); -- 字段重命名
-- 删除表的字段  表名  DROP 字段名
ALTER TABLE STUDENT1 DROP AGE1

create table `grade`(
    `gradeid` int(10) not null auto_increment comment '学生的年级', -- 年级表字段
    `gradename` varchar(10) not null comment '表单名字',
     primary key (`gradeid`)
)engine =innodb default charset =utf8;
 
create table if not exists `student`(
    `id` INT(4) NOT NULL  AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '张三' COMMENT '姓名',
    `gradeid` int(10) not null comment '学生的年级', -- 学生表字段
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `SEX` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (`id`),
    KEY `FK_gradeid`(`gradeid`),  -- 方式1
    CONSTRAINT `FK_gradeid` foreign key (`gradeid`)references `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET =utf8;

-- 创建表的时候没有外键关系
alter table `student`  -- 方式2  修改这个表，外键  添加约束，约束名，通过外部的键去参考哪个表（哪个字段）
add constraint `FK_gradeid` foreign key (`gradeid`) references `grade`(`gradeid`);
-- alter table 表 add constraint 约束名 foreign key （作为外键的列）references 哪个表（哪个字段）

insert into 表名(字段名) values (字段值)  -- insert into     ,  values
 
insert into `stdent`(`id`)values (1231),(47865);  -- 可以同时插入多个值

update  修改谁  （条件）  set原来的值=新值 
-- 修改学院的名字，带了简介
update `table_name` set `name`='李四' where id=1;-- id等于1，就更改name的值
-- 不指定条件的情况下，会改变所有的表，
update `table_name` set `name`='李四'
-- UPDATE 表名 set colnum_name=value   where 条件
and && 语句 如果name相等and（和） id也相等，那么就为true 执行命令
-- 李四是要修改的新值
update `test` set `name`='李四' ,`pwd`='123456' where `name`='张三' and id=2;-- 通过多个条件定位数据

-- 删除数据（避免这么写，因为会删掉该表的所有字段）
delete from `test`;
-- 删除指定数据
delete from `test` where id=2

truncate `test`  -- 清空一个表

delete from `text`where coll>=1; -- 不会影响自增
truncate table `text` -- 自增会归零

-- 查询所有表的结构  select 字段 from 表
select * from text;
-- 查询指定字段
select `id`,`coll` from text;
-- 别名，给结果取一个名字 AS  可以给字段取别名，也可以给表取别名
select `id`AS 学号,`coll` AS 电话 from text AS S;
-- 函数 拼接字符串Concat(a,b)
select concat('电话：',coll) AS 新名字 from text

select *  from text;-- 查询学生id和电话 （查询所有字段）
select `coll` from text;-- 查询哪些同学有电话
select distinct `coll` from text;-- 发现重复数据，去重（distinct）

select VERSION();-- 查看系统版本（相当于函数）
select 100*3-123 AS 计算结果;-- 用来计算（相当于表达式）
select @@auto_increment_increment; -- 查询自增的步长（相当于变量）
-- 所有人的电话加个数
select `id`,`coll`+1 AS '电话加1后' from text;

select distinct * from `text`  -- distinct（去重）
[left join | right join |inner join `name`] -- 联合查询 
[on] -- 等值判断
[where ...] -- 指定字段满足的条件
[group by ...] -- 指定结果按照哪个字段来分组
[having] -- 过滤分组的记录，必须满足的次要条件。条件和where是一样的，只是位置不同
[order by ...] -- 指定查询记录一个或多个条件排序（升序或降序）
[limit] startindex,pagesize  -- 指定查询记录从哪条至哪条 
-- limit 开始下标 ， 页面大小

select `id`,`coll` from text;
-- 查询id在2~4之间的电话   and = &&
select `id`,`coll` from text
where `id` >=2 and `id` <=4;
-- 模糊查询（区间范围） between..and..
select `id`,`coll` from text;
select `id`,`coll` from text
where `id` between 2 and 4;
-- 查询id不等于1的coll
select `id`,`coll` from text  -- ！=
where `id` !=2;
select `id`,`coll` from text -- not
where not `id` =1;

-- ===============         where      ============
-- 查询王开头的字
-- like 结合 %（代表0到任意个字符） _ （一个字符）
select * from text;
select id, coll,  age,app,name from text
where name like '刘%';
-- 查询王开头的字 名字后面只有一个字的
select id, coll,  age,app,name from text
where name like '刘_';
-- 查询王开头的字 名字后面只有两个个字的
select id, coll,  age,app,name from text
where name like '刘__';
-- 查询名字中间有七的同学
select id, coll,  age,app,name from text
where name like '%七%';
 
-- ===============         in(具体的一个或者多个值)      ============
-- 查询2，3，4学员的信息
select id,name from text
where id  in (2,4,3);
-- 查询在打王者荣耀的学生
select id,name from text
where app in ('王者荣耀');
 
-- ===============         null not null      ============
-- 查询名字为空的同学 null（空是指空字符串）
select id,app from text
where name =' '   or name is null;
-- 查询名字不为空的同学
select id,app from text
where name is not null;
-- 查询没有名字的同学
select id,app from text
where name is  null;

--  ==========  自连接 ==========
create table `category`(
`categoryid` int(10) unsigned not null auto_increment comment '主体id',
`pid` int(10) not null comment '父id',
`categoryname` varchar(50) not null comment '主体名字',
primary key (`categoryid`)
)engine = innodb auto_increment=9 default charset =utf8;
 
-- 查询父子信息:把一张表看为两个一模一样的表
select a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
from `category` as a , `category` as b
where a.`categoryid`=b.`pid`    
-- 1是最高级，所以1变成了categoryid的最高级，所以与pid相对应，由大到小依次对应（没有的数字默认为空）

-- 查询结果，根据年龄降序排序
select * from text
order by age DESC; -- 通过降序给年龄排序
select * from text
order by age ASC -- 通过升序给年龄排序

-- 分页，每页值显示3条数据
select *from text
limit 6,3

-- =========  常用函数  ===========
-- 数学运算
select ABS (-8); -- 绝对值
select ceiling(9.4);  -- 向上取整
select floor(9.4);  -- 向下取整
select rand();  -- 返回0到1之间的随机数
select sign(0);  -- 判断一个数的符号  负数返回-1，整数返回1，0返回零
-- 字符串
select char_length('一二三四五');  -- 返回字符串长度
select concat('天天','学习'); -- 拼接字符串
select insert('二十四月',1,2,'超级热爱');  -- 插入，从某个位置开始替换某个长度
select lower('ZHANGSAN'); -- 全部转化为小写
select upper('zhangsan'); -- 全部转换为大写
select instr('zhangsan','g'); -- 返回第一次出现字串的索引
select replace('坚持就能成功','坚持','奋斗'); -- 替换出现的指定字符串
select substring('床前明月光',2,3); -- 返回指定的字符串 (原字符串，截取的位置，截取的长度)
select reverse('床前明月光'); -- 反转
 
-- 查询性刘的同学，将名字换成李
select replace(name,'刘','李') from text
where name like '刘%';
 
-- ======    时间和日期函数   =====
select current_date();  -- 获取当前日期
select now(); -- 获取当前时间
select localtime(); -- 获取本地时间
select sysdate(); -- 获取系统时间
select year(now());-- 获取本年（需要在里面设置一个现在）
-- 系统
select system_user(); -- 系统
select user();
select VERSION(); -- 当前版本

-- ========  聚合函数  ==========
-- 都能统计表中的数据（想查询这个表中有多少条记录，就可以用count() ）
select count(name) from text; -- count(指定列)，会忽略所有的null值
select count(*) from text; -- count(*)，不会忽略所有的null值
select count(1) from text; -- count(*)，不会忽略所有的null值
 
select sum(`age`) AS 总和 from text;
select avg(`age`) AS 平均分 from text;
select max(`age`) AS 最高分 from text;
select min(`age`) AS 最低分 from text;
 
-- 查询课程的平均分，最高分，最低分,平均分大于80的
-- 核心：根据不同的课程分组
select a.id,avg(b.`sub`) as 平均分,max(a.`sub`) as 最高分,min(a.`sub1`)  as 最低分 from text a
inner join test b
on a.id = b.id
group by a.id
having 平均分>80

create table `testmd5`(
    `id` int(4) not null,
    `name` varchar(10) not null ,
    `pwd` varchar(10) not null,
     primary key(`id`)
)engine = innodb  default charset =utf8;
 
-- 明文密码
insert into testmd5 values(1,'张三','123456 '),(2,'李四','654321'),(3,'刘大','123789 ');
 
-- 加密
UPDATE testmd5 SET PWD =MD5(PWD) WHERE id=1;
UPDATE testmd5 SET PWD =MD5(PWD);-- 加密全部的密码
-- 插入的时候加密
insert into testmd5 values (4,'zhangsan',md5('123456'));
-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `NAME`='ZHANGSAN' AND PWD = MD5('123456') -- 只能查询到MD5加密过一次的密码

-- ==============   事务   ================
-- mysql 是默认开启事务自动提交的
set autocommit =0;  /*关闭*/
set autocommit =1;  /*开启(默认的)*/
 
-- 手动处理事务
set autocommit =0;-- 关闭自动提交
-- 事务开启
start transaction; -- 标记一个事务的开始，从这个开始之后的sql都在用一个事务内
INSERT XXX -- 事务操作
INSERT XXX -- 事务操作
 
-- 提交：持久化  （成功就提交）
commit;
-- 回滚：回到原来的样子  （失败就回滚）
rollback;
 
-- 事务结束
set autocommit =1; -- 事务结束后开启自动提交
 
SAVEPOINT 保存点名; -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点

-- 转账
create database shop;
use shop;
create table `account`(
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL ,
    `money` DECIMAL(9,2) NOT NULL,
     PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET =UTF8;
 
INSERT INTO account(`NAME`,`MONEY`) VALUES ('A',2000.00),('B',10000.00);
-- 模拟转账：事务
SET AUTOCOMMIT =0;-- 关闭自动提交
start transaction ; -- 开启一个事务
update account set money=money-500 where `id`='1'; -- id=1减500
update account set money=money+500 where `id`='2' ;-- id=2加500
commit;-- 提交事务  就会被持久化
rollback; -- 回滚
SET AUTOCOMMIT =1; -- 恢复默认值

-- 显示所有的索引信息
show index from student
-- 增加一个全文索引（索引名）列名
alter table school.student add fulltext index `studentName`(`studentName`)
-- exolain 分析sql执行的情况
explain select * from student; -- 非全文索引
explain select * from student where match(studentName) against('刘');

-- 创建用户   create user '用户名' IDENTIFIED BY '密码'
create user 'modong' IDENTIFIED BY '123456';
 
-- 修改密码（修改当前用户）  提示：练习后要记得更改回原来的密码
set password=password ('123456');
-- 修改密码（修改指定用户）   提示：练习后要记得更改回原来的密码
set password for modong =password ('123456');
 
-- 重命名   RENAME USER 原用户名 TO 新用户名
RENAME USER modong TO modong2;
 
-- 用户授权 ALL PRIVILEGES,全部的权限，库，表
-- ALL PRIVILEGES 除了给其他用户授权，其他都可以做
GRANT ALL PRIVILEGES ON *.* TO modong2;
 
-- 查询权限
SHOW GRANTS FOR modong2;  -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost;
-- root用户的权限： GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM modong2;
 
-- 删除用户
DROP USER modong2;

CREATE DATABASE jdbcStudy;
use jdbcStudy;
create table users(
    `id` int primary key ,
    `name` varchar(40),
    `PASSWORD` varchar(40),
    `email` varchar(60),
    `birthday` DATE
);
 
insert into users (id,name,password,email,birthday)
values (1,'zhangsan','123456','zs@sina.com','2020-12-20'),
       (2,'zhaoyiyi','123456','zy@sina.com','2020-9-20'),
       (3,'lisi','123456','ls@sina.com','2020-8-20');

package com.lei.lesson01;
 
import java.sql.*;
 
// 我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1，加载驱动
        Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动
 
        // 2，用户信息和url              
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useSSL=false&serverTimezone=Hongkong&characterEncoding=utf-8&autoReconnect=true";
        String username = "root";
        String password = "123456";
        // 3，连接成功，返回数据库对象   Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
 
        // 4，执行sql的对象   Statement执行sql的对象
        Statement statement = connection.createStatement();
 
        // 5，执行sql对象 去 执行sql，可能存在结果，我们要查看连接结果
        String sql = "SELECT * FROM users;";
 
        ResultSet resultSet = statement.executeQuery(sql); // 返回的结果集，结果集中封装了我们全部的查询出来的结果
 
        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("=======================");
        }
 
        // 6,释放连接
        resultSet.close();
        statement.close();
        connection.close();
 
    }
 
}

Statement st = conn.createStatement();
String sql = "insert into user(...)values(...)";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功");
}

Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功");
}

Statement st = conn.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功");
}

Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeUpdate(sql);
while(rs.next()){
// 根据获取列的数据类型，分别调用rs的相应方法映射到Java对象中
}

private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
 
static {
 
    try {
        InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dp.properties");
        Properties properties = new Properties();
        properties.load(in);
 
        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
 
        // 驱动只用加载一次
        Class.forName(driver);
 
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
 
}
 
// 获取连接
public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(url, username, password);
 
}
 
// 释放连接
public static void release(Connection conn, Statement st, ResultSet rs) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (st != null) {
        try {
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

public static void main(String[] args) {
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    try {
        conn = JdbcUtils.getConnection(); // 获取数据库连接
        st = conn.createStatement(); // 获取sql的执行对象
        // 只能加载一次，加载成功后，再次加载就会出现异常（解决方法，把加载的数据删除，重新加载）
        String sql = "delete from users where id =4";
        int i = st.executeUpdate(sql);
        if (i>0){
            System.out.println("插入成功！");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JdbcUtils.release(conn,st,rs);
    }
}

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;
 
    try {
        conn = JdbcUtils.getConnection();
 
 
        String sql = "insert into users(id,NAME,PASSWORD,email,birthday) values(?,?,?,?,?)";
        st = conn.prepareStatement(sql);  // 预编译SQL，先写SQL，然后不执行
 
        // 手动给参数赋值
        st.setInt(1, 4); // 给id赋值
        st.setString(2, "zhangsan");
        st.setString(3, "123456");
        st.setString(4, "123456@qq.com");
        st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
        // 执行
        int i = st.executeUpdate();
        if (i > 0) {
            System.out.println("插入成功");
        }
 
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        JdbcUtils.release(conn,st,null);
    }
}

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;
 
    try {
        conn = JdbcUtils.getConnection();
 
        // 区别
        // 使用？占位符代替参数
        String sql = "delete from users where id=?";
        st = conn.prepareStatement(sql);  // 预编译SQL，先写SQL，然后不执行
 
        // 手动给参数赋值
        st.setInt(1,4);
 
        // 执行
        int i = st.executeUpdate();
        if (i > 0) {
            System.out.println("删除成功");
        }
 
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        JdbcUtils.release(conn,st,null);
    }
}

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;
 
    try {
        conn = JdbcUtils.getConnection();
 
        // 区别
        // 使用？占位符代替参数
        String sql = "update users set `NAME` = ? where id =?;";
        st = conn.prepareStatement(sql);  // 预编译SQL，先写SQL，然后不执行
 
        // 手动给参数赋值
        st.setString(1, "张三");
        st.setInt(2, 1);
 
        // 执行
        int i = st.executeUpdate();
        if (i > 0) {
            System.out.println("更新成功");
        }
 
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JdbcUtils.release(conn, st, null);
    }
}

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    try {
        conn = JdbcUtils.getConnection();
        String sql = "select * from users where id =? "; // 编写SQL
        st = conn.prepareStatement(sql);// 预编译
 
        st.setInt(1,2); // 传递参数
 
        rs = st.executeQuery(); // 执行
 
        if (rs.next()){
            System.out.println(rs.getString("NAME"));
        }
 
    } catch (SQLException e) {
        e.printStackTrace();
    }
    finally {
        JdbcUtils.release(conn,st,rs);
    }
}