Noname's Blog
信息安全专业的小萌新,立志走上更大的舞台
MySQL学习笔记

MySQL学习笔记

实验工具:MySQL5.7+Navicat

数据库基本概念

定义:按照数据结构来组织、存储和管理数据的仓库
管理数据库的计算机系统 --数据管理系统DBMS

基本组成

库->组成:表、视图、函数、事件、查询、报表、备份
表->行(代表一条数据) 列(代表每一行数据的一个组成结构)

数据类型

字符串类型

CHAR(m) 定长字符串(会在后面补0) m范围 0-255
VARCHAR(m) 可变长字符串 m范围 0-65535字节数

    Latin1 一个字符占一个字节
    GBK 一个字符占两个字节
    UTF8 一个字符占三个字节

TEXT 长文本数据 范围0-65535字节
TINYTEXT 短文本字符串 范围0-255字节
MEDIUMTEXT 中等程度文本数据 0-16777215字节
LONGTEXT 极大文本数据 0-4294967295字节

数值类型

INT/INTEGER 4字节
TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3字节
BIGINT 8字节
FLOAT 4字节 单精度浮点数
DOUBLE 8字节 双精度浮点数
DECIMAL(P,D) 存储精确数值(货币) 存储D位小数的P位数

DECIMAL(10,3) 共有10位,有3位占小数

日期和时间类型

DATE YYYY-MM-DD 1000-01-01 ~ 9999012031
TIME hh:mm:ss
DATETIME YYYY-MM-DD hh:mm:ss 1000-01-01 0:0:0 ~ 9999-12-31 23:59:59 8字节
TIMESTAMP 时间戳 1970-01-01 0:0:0 ~ 2038-01-19 03:14:07 UTC 4字节

二进制类型

BIT(m) 设置段类型大约为(m+7)/8字节
BINARAY(m) 固定长度2进制字符串 m字节
VARBINA 可变长度二进制
TINYBLOB(m) 非常小的BLOB+1字节
BLOB(m)
MEDIUMLB(m)
LONGBLOB(m)

其他

enum 枚举类型
set 集合类型
boolean 实际上不存在这种类型,内部会自动转换成tinyint(1)
JSON

MySQL基本查询

库操作

新建库

CREATE DATABASE testdb;

新建设置字符集的库

CREATE DATABASE IF NOT EXISTS testdb1 CHARACTER set utf8; # 如果不存在则创建

删除库

DROP DATABASE testdb1

选择数据库

USE testdb1

navicat的操作:可以选中某一行单独运行
注释

# 我是注释
/*我是注释*/
-- 我是注释

表操作

建表

CREATE TABLE

列的定义
列名、数据类型、NOT NULL不允许为空、DEFAULT指定默认值、AUTO_INCREMENT 自增、comment 注释
设置主键

PRIMARY KEY 

设置数据库引擎

ENGINE

简单判断

IF NOT EXISTS

完整示例

USE testdb; # 选择数据库

CREATE TABLE IF NOT EXISTS products(
	id INTEGER NOT NULL AUTO_INCREMENT COMMENT '主键',
	# utf8一个字符占3个字节, 不允许为空
	name VARCHAR(100) NOT NULL COMMENT '商品名',
	description TEXT COMMENT '商品描述',
	count INTEGER NOT NULL DEFAULT 0 COMMENT '库存',
	price DECIMAL(20,2) NOT NULL COMMENT '单价',# 20位,小部分占2位
	type VARCHAR(100) COMMENT '商品类别', 
	creattime DATETIME DEFAULT NOW() COMMENT'上架时间', 
	channe VARCHAR(100) COMMENT '进货渠道',
	inprice DECIMAL(20, 2) COMMENT '进货价',

	PRIMARY KEY (id) # 设置主键
)ENGINE=INNODB; # 设置数据库引擎

注意:使得关键字变成普通字符 用反引号(列名、表名、数据库名都可)

索引

主键索引(主键约束)
不接受重复值和空值
一般不会更改
一个表有且仅有一个主键
最小化原则(表可以由多个列组成,复合主键不能包含不必要的和多余的列)

PRIMARY KEY

唯一索引:允许空值NULL

UNIQUE KEY

表内容操作

插入数据

查询数据

WHERE 查询条件
<,>,=,!=,<>,>=,<=;
and,or
is null, is not null
_like%
in / not in
between/not between

ORDER BY 排序
ASC默认 升序
DESC 降序

更新数据

UPDATE tablename SET;

删除数据

DELETE FROM table WHERE;

整个表删除

DROP table products;

更改表的结构

更改列的属性

ALTER TABLE tablename CHANGE COLUMN col_name 类型;

添加列

ALTER TABLE tablename ADD COLUMN col_name TINYINT;

删除列

ALTER TABLE tablename DROP COLUMN col_name;

重命名表名

RENAME TABLE tablename1 to tablename2;

复合主键

多个列的组合不能重复

CREATE TABLE IF NOT EXISTS category(
	`id` INTEGER not NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL COMMENT '类目名',
	`description` TEXT COMMENT '类目描述',
	`parent` INTEGER DEFAULT 0 COMMENT '上级目录',
	 PRIMARY key(`id`), -- 主键索引
	 UNIQUE KEY(`parent`, `name`) -- 唯一索引 parent 上级目录id, name当前目录id
	-- 上级目录和目录名的组合不可重复(同一父目录下同一级子目录名不可重复)
)ENGINE=INNODB;

INSERT category(name) VALUES('食品'),('家具'), ('玩具'),('服装'); # 按默认的升序插入
INSERT category(name, parent) VALUES('食品',1),('零食',1), ('生鲜',1),('其他',1); # 插入二级目录,子目录的id都是1
INSERT category(name, parent) VALUES('其他',2);

高级插入

多个列的组合不能重复

插入或更新

ON DUPLICATE KEY UPDATE

去重

DISTINCT加在需要去重的目录名前面

排序

SELECT * FROM products ORDER BY count ASC; # 默认升序
SELECT * FROM products ORDER BY count DESC; # 降序

多列排序(二次排序)

SELECT * FROM products ORDER BY count ASC, id ASC;# count相同的商品,id按照升序排序

中文排序(拼音排序)

-- utf-8数据库中按照拼音排序
SELECT * FROM persons ORDER BY name;# 这样是不可的

-- 通过更改要查找的参数的字符串的字符编码实现按照拼音排序
SELECT * FROM persons ORDER BY CONVERT(name using gbk);

分页

将数据分批传输至前端页面
LIMIT子句(offset, num) 从第offset+1条数据开始读取num条数据

SELECT * FROM products LIMIT 100; # 只返回前100条
-- 只返回最后五条(排序+分页)
SELECT * FROM products ORDER BY id DESC LIMIT 100;
-- 从第101条到第200条数据
SELECT * FROM products LIMIT 100, 100; # 从101开始(数据索引也从0开始), 取100给数据
-- 从第201条到第300条数据
SELECT * FROM products LIMIT 200, 100; 
-- 找最贵的商品
SELECT * FROM products ORDER BY price DESC LIMIT 1;
-- 找最便宜的商品
SELECT * FROM products ORDER BY price LIMIT 1;

分组与聚合

聚合函数

不允许出现嵌套
获取行数

-- 获取结果条数
SELECT count(*) FROM products WHERE price>50 # 在统计结果时不会忽略值为null的列,包括所有列
SELECT count(id) FROM products WHERE price>50 # 忽略列的值为null的数据
SELECT count(1) FROM products WHERE price>50 # 无条件的,包括所有列

平均值

-- 获取商品平均价格
SELECT AVG(price) as avePrice FROM products;

求和

-- 获取商品在库总数
SELECT SUM(count) as total FROM products;

最小值和最大值

-- 获取价格最低的商品和最高的商品的价格
SELECT MIN(price) as minprice, MAX(price) as maxprice FROM products;

分组子句

按照指定字段将数据进行分组,再在单独的某个组里使用聚合函数
GROUP BY
相关用法

SELECT 数据列
FROM 数据表
WHERE 筛选条件
GROUP BY 分组
HAVING 分组筛选
LIMIT n,m

-- 计算每个目录下各有多少种商品
SELECT COUNT(*), type FROM products GROUP BY type;

-- 计算每一天上架商品总价
SELECT SUM(price * count), DATA(createtime) FROM products GROUP BY DATE(createtime);

-- 多重分组
SELECT COUNT(*) FROM products GROUP BY type, channel;# 先以type分组,再以channel分组

-- 筛选分组 分组后进行过滤
SELECT COUNT(*), type, channel FROM products GROUP BY type, channel HAVING channel='渠道1' OR channel='渠道3';

-- 查询结果在分组前就进行过滤
SELECT COUNT(*), type, channel FROM products	WHERE channel='渠道1' OR channel='渠道3' GROUP BY type, channel ;

-- 对比一下WHERE和HAVING
SELECT name FROM products WHERE price>50;
# SELECT name FROM products HAVING price>50; -- 
SELECT name, price FROM products HAVING price>50;

-- 查询平均价格大于100的商品信息
# 先where进行过滤,但是此时agprice并没有出生,报错:Unknow column 'agprice' in where 'clause'
# SELECT id, AVG(price) as agprice FROM products WHERE agprice>100 GROUP BY id;

SELECT id, AVG(price) as agprice FROM products agprice>100 GROUP BY id HAVING agprice>100;

常用函数

字符串函数

# 字符个数,字节个数
SELECT CHAR_LENGTH(description), LENGTH(description) FROM products WHERE id=1;
# 字符串拼接
SELECT CONCAT("abc","123",333);#abc123333
SELECT CONCAT_WS("-","123", 321,"abc");# 123-321-abc 第一个参数是分隔符

SELECT LOWER("Apple"), UPPER("Apple"), TRIM(" AAA "), REPLACE("apple","app","ppa"), REVERSE("apple"); 

数字函数

-- 绝对值 向上取整 向下取整 随机数
SELECT ABS(-1), CEIL(3.14), FLOOR(3.14), ROUND(3.14), POW(2, 10), RAND();# 1, 4, 3, 3, 1024, ....(0,1之间随机)

-- 随机取出一件商品
SELECT * FROM products ORDER BY RAND() LIMIT 1;

日期函数

-- 日期
SELECT NOW(), CURDATE(), CURTIME();
SELECT ADDDATE(NOW(),INTERVAL 3 DAY), ADDTIME(NOW(),INTERVAL -3 SECOND);# 在第一个参数基础上,加上多少。。。

SELECT SUBDATE(NOW(),INTERVAL 3 DAY), ADDTIME(NOW(),INTERVAL -3 SECOND);

SELECT TIMEDIFF(NOW(),'2019-10-01 12:35:06');
-- 计算有多少天
SELECT DATEDIFF(NOW(),'2019-10-01 12:35:06');

-- 日期格式化
SELECT DATE_FORMAT(NOW(),'%Y'); # 四位数的年份
SELECT DATE_FORMAT(NOW(),'%Y年%m月'); # 四位数的年份,两位数的月份
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');# 四位数的年份,两位数的月份,两位数的天数
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %r'); # 24小时制

SELECT QUARTER(NOW());# 季度 

多表查询

函数

CASE
IF
CONVERT 类型转换

子查询

由另一个查询语句提供值列表,可以嵌套

联合查询

把多个SELECT结果组合到一个结果集合内
结果字段的名称来自于第一个SELECT语句的字段名
UNION会把结合结果内的重复数据删除,可加上ALL防止
UNION ALL效率比UNION高
只需要SELECT查询的字段数一样,数据类型不一致也可
5
6

外键

一个表里的字段被另一个表的字段引用
保持参照的完整性
父表(被引用的表),子表(引用的表)
FOREIGN KEY 外键名

基本选项

CREATE TABLE goods(
	`id` INTEGER NOT NULL AUTU_INCREMENT,
	`name` VARCHAR(200) NOT NULL,
	`count` INTEGER NOT NULL,
	`price` DECIMAL(20,2) NOT NULL,
	`type` INTEGER,
	PRIMARY KEY(id),
	FOREIGN KEY fk_type(type) -- 设置外键名以及子表关联列
	REFERENCES category1(id) -- 设置父表以及父表的关联列
	-- 级联动作
	-- 父表更新,子表进行操作
	ON UPDATE CASCADE -- 设置级联更新操作
	ON DELETE RESTRICT -- 设置级联删除操作
);

-- 不能为不存在的商品类别插入数据
INSERT into goods(name, count, price, type) VALUES('商品1', 100, 18, 10);# 插入的数据在父表里不存在时不允许插入的

# DELETE FROM category1 WHERE id = 5; # 父表goods里有,所以不能删

DELETE FROM category1 WHERE id = 50; # 
UPDATE category1 SET id = 40 WHERE id =4;#

RESTRICT
父表删除时,子表内有关联数据,则不允许父表删除
父表更新时,子表内有关联数据,则不允许更新父表数据

CASCADE
父表更新时,子表内有关联数据,则自动更新子表关联数据的值
父表删除时,会把子表关联数据一起删除

NO ACTION
类比RESTRICT

SET NULL
更新或者删除父表数据时,子表关联数据会变成NULL(前提:该列要允许设置成NULL)

表连接查询

LEFT JOIN 左连接
获取左表的所有记录,即使右表没有对应记录(NULL)

RIGHT JOIN 右连接
获取右表的所有记录,即使左表没有对应记录(NULL)

INNER JOIN 内连接
获取2个表中字段匹配关系的记录

CROSS JOIN 交叉连接
获取2个表中字段的笛卡尔积
不具有连接条件

存储过程


-- 创建一个存储过程
-- 整一个结束符号
DELIMITER $$
CREATE PROCEDURE init_db()$$
BEGIN
	# 定义变量
	DECLARE i INTEGER DEFAULT 1;
	-- 如何搞随机!
	DECIMAL type INTEGER;
	WHILE i<=1000 DO
		SET type = (SELECT id FROM category1 ORDER BY RAND() LIMIT 1);
		INSERT INTO goods(name, count, price, type) VALUES(CONCAT('商品名',i),100,20,type);
		
		SET i = i + 1;
	
	END WHILE;
END$$

-- 还原结束符合
DELIMITER ;

-- 调用
CALL init_db();

JDBC与数据库操作

基本概念:

连接Java应用程序和数据库之间的桥梁
Java语言访问数据库的规范,API
提供了一组标准的Java语言的接口和类
接口的实现类是由各个数据库厂商提供,也就是驱动
使用对应的厂商的驱动,就可以访问不同类型的数据库
编译一次,处处运行

基本使用方法:

下载驱动程序
添加至Java项目中
加载驱动程序
建立连接 jdbc:mysql://
创建Statement对象
执行SQL语句
处理结果集的数据
释放资源,关闭数据库

获取数据库连接方法1

public static void getConn()
{
    //连接字符串
    String url = 
"jdbc:mysql://localhost:3306/testdb?useSSL=false&Character=UTF-88";//jdbc:mysql://数据库连接的 ip地址和端口

    //传递账号密码
    String username = "root";
    String password = "root";
    //加载驱动包
    try {
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Success");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
   }
    //建立连接
    //主机无法连接或数据库名字错误可能导致错误
    try {
        //获取连接
        Connection <u>con</u> = DriverManager.getConnection(url, username, 
password);//返回值是数据库连接对象
          } catch (SQLException e) {
                    e.printStackTrace();
          }
}

获取数据库连接方法2

public static Connection getConn2()
{

    Connection conn = null;
    //连接字符串
    String url = 
"jdbc:mysql://localhost:3306/testdb?useSSL=false&Character=UTF-88";//jdbc:mysql://数据库连接的 ip地址和端口
    //传递账号密码
//           String <u>username</u> = "root";
//           String password = "root";
    //加载驱动包
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
         e.printStackTrace();
    }
    //建立连接
    //主机无法连接或数据库名字错误可能导致错误
     try {
         Properties p = new Properties();
         p.setProperty("user", "root");
         p.setProperty("password", "root");
         p.setProperty("useSSL", "false");
         p.setProperty("characterEncoding", "utf-8");
         conn = DriverManager.getConnection(url, p);
         System.out.println("Success");
         //CURD
        } catch (SQLException e) {
          e.printStackTrace();
        }finally {
        //关闭数据库连接
        //如果外部需要引用,不可以关闭,可以写在有关操作后
//                  if(<u>conn</u> != null)//若在连接的过程中,参数配置错误,值依旧为null

//                  {
//                         try {
//                               conn.close();
//                         } catch (SQLException e) {
//                               e.printStackTrace();
//                         }
//                  }
             }

             return conn;
       }


JDBC核心对象

DriverManager 管理数据库驱动程序列表
Connection 数据库连接对象
Statement 将SQL语句提交到数据库
ResultSet 执行SQL查询后返回的数据 && 迭代器
SQLException

public class StatementDemo {
       public static void main(String[] args) {
             //一定要注意加where!
             //增加数据
//           String <u>sql</u> = "INSERT INTO products(name,description, count ,price) values('apple','delicious',100,1)";
             //更新数据
             String name = "苹果";
             String sql = "update products set name='"+name+"' where id=13";
             //删除数据
//           String <u>sql</u> = "delete from products where id = 8";
             //建立数据库通用的操作类
             try(Connection conn = CreateDbcConnection.getConn2();
                           Statement st = conn.createStatement();
                    )//适用于所有要更改数据的方法,返回<u>int</u>类型
             {
                    int count = st.executeUpdate(sql);
                    System.out.println(count);
                    if(count > 0)//影响到了一条及一条以上
                    {
                           System.out.println("操作中");
                    }else {
                           System.out.println("操作失败");
                    }
             }catch(SQLException e) {
                    e.printStackTrace();
             }
       }
}

最后可以整理成DBUtil

SQL注入

emmmmm会另外写

预处理

PreparedStatement-->在创建时就把sql传入了,执行的时候不用再传递sql语句
优点:
提高可读性和可维护性
一定程度上防止SQL攻击
执行SQL效率高

SQL执行顺序

词法分析
语法分析
语义分析
执行计划优先
执行

批处理

String sql = "update products set state=? where name=?";//理解成一个模板,?是从1开始
             //删除数据
//           String sql = "delete from products where id = 8";

     //建立数据库通用的操作类
try(Connection conn = CreateDbcConnection.getConn2();
    PreparedStatement st = conn.prepareStatement(sql);
    )//适用于所有要更改数据的方法,返回<u>int</u>类型
{
     //关闭自动提交
    conn.setAutoCommit(false);
    //测试需要多少时间
    Date d1 = new Date();

    for(int i = 1; i <= 3000;i++) {
        st.clearParameters();
        st.setString(1, "新商品" + i);
        st.setInt(2, i);
        st.setDouble(3, Math.random()*100);
        //执行语句
        //st.executeUpdate//每次都会提交,导致时间慢
        //添加一个批处理任务
        st.addBatch();
     }
     //执行批处理任务
     st.executeBatch();//返回<u>int</u>类型的数组,对应的<u>int</u>数组的长度是批处理任务里子任务的个数,在这里是3000

     //提交数据
     conn.commit();
     Date d2 = new Date();
     System.out.println("共花费了"+(d2.getTime()-d1.getTime())/1000 + "秒");