Java学习笔记(十四):MySQL数据库

1 数据库的基本概念

1.1 什么是数据库

数据库(DataBase,简称:DB),是一个用于存储和管理数据的仓库

1.2 数据库的特定

  • 持久化存储数据。数据库其实是一个文件系统
  • 方便存储和管理数据。
  • 使用统一的方式操作数据库——SQL。

1.3 常用的数据库

可以通过网址:https://db-engines.com/en/ranking , 查看数据库的排行榜。

2 SQL

2.1 SQL的概念

SQL(Structured Query Language): 结构化查询语义,就是定义了操作所有关系型数据库的规则。

2.2 SQL的通用语法

  1. SQL 语句可以单行或者多行书写,以分号 “;”结尾。

  2. 可以使用空格和缩进来增强语句的可以读性。

  3. MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。

  4. SQL语句的注释:

    • 单行注释:

      -- 注释内容
      
      # 注释内容(MySQL特有)
    • 多行注释

      /* 
      注释
      注释
      */

2.3 SQL的分类

  1. DDL(Data Definition Language),数据定义语义:

    用来定义数据库对象:数据库、表、列等。关键字:createdropalert等。

  2. DML(Data Manipulation Language),数据操作语义:
    用来对数据库中的表进行增删改。关键字:insertdeleteupdate

  3. DQL(Data Query Language),数据查询语言:

    用来查询数据库中表的数据。关键字:selectwhere等。

  4. DCL(Data Control Language),数据控制语言:

    用来定义数据库的访问权限和安全级别,以及创建用户。关键字:GRANT,REVOKE等。

2.4 CRUD

  • C(Create):创建
  • R(Retrieve):查询
  • U(Update):修改
  • D(Delete):删除

4 操作数据库

4.1 创建数据库

# 创建数据库
create database db1;

# 创建数据库,判断不存在,再创建
create database if not exists db2;

# 创建数据库,并指定字符集
create database db3 character set gbk;

# 创建数据库,判断是否存在,并指定字符集
create database if not exists db4 character set gbk;

4.2 查询数据库

查询所有的数据库名称

# 查询所有的数据库名称
show databases;

示例:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| education_course   |
| education_system   |
| education_user     |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.01 sec)

查询某个数据库的字符集

查询某个数据库的创建语句

# 查询某个数据库的字符集/查询某个数据库的创建语句
show create database 数据库名称

示例:

mysql> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

4.3 修改数据库

修改数据库的字符集

# 修改字符集
alter database 数据库名称 character set 字符集名称;

示例:

mysql> alter database db3 character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database db3;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db3      | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

4.4 删除数据库

####

#删除数据库
drop dababase 数据库名称;

# 先判断是否存在,再删除
drop database if exists 数据库名称;

4.5 使用数据库

查询当前正在使用的数据库

# 查看当前使用的数据库
select database();

使用数据库

# 使用数据库
use 数据库名称;

5 操作表

5.1 创建表

创建语法

create tabele 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    ......
    列名n 数据类型n
)

示例:

create table student(
    id int,
    name varchar(32),
    age int,
    score double(4,1), #最多4位,取1位小数(如:100.0)
    birthday date,
    insert_time timestamp
);

复制表

# 复制表
create table 表名 like 被复制的表名;

5.2 查询表

查询所有表

# 查询某个数据库的所有表的名称
show tables;

查询表结构

# 查询表结构
desc 表名;

查询表字符集

查询表创建语句

# 查询表字符集/查询表创建语句
show create table 表名;

5.3 修改表

修改表名

# 修改表名
alter table 表名 rename to 新的表名;

修改表的字符集

# 修改字符集为utf-8
alter table 表名 character set utf8;

添加一列

# 添加列
alter table 表名 add 列名 数据类型;

修改某一列

# 修改列名和数据类型
alter table 表名 change 列名 新列名 新数据类型;

# 修改数据类型
alter table 表名 modify 列名 新数据类型;

删除某一列

# 删除列
alter table 表名 drop 列名;

5.4 删除表

# 删除表
drop table 表名;

# 判断是否存在,再删除
drop table if exists 表名;

6 操作数据

6.1 添加数据

语法:

# 插入数据
insert into 表名(列名1,列名2,...,列名n) values(1,2,...,值n);

注意列名和表名要一一对应,如果不定义列名,则给所有的列添加值:

如:

# 插入数据
insert into 表名 values(1,2,...,值n);

示例:

# 插入数据
insert into student(id,name)values(1,"小明");

6.2 删除数据

# 删除数据
delete from 表名 [where 条件]

示例:

# 删除id=1的数据
delete from student where id=1;

# 删除全部数据(不建议使用)
# 有多少条记录,就会进行多少次删除操作,效率比较低
delete from student;

注意如果不加条件会删除所有记录,但如果要删除所有的数据也不建议使用该命令,因为它会一条一条的删除数据,效率比较低下,建议使用TRUNCATE ,它会先删除表,再创建一个一模一样的表。

# 删除全部数据
# 删除表,再创建一个相同的表
truncate table 表名; 

6.3 修改数据

语法:

# 修改数据
update 表名 set 列名1 =1,列名2 =2,...[where 条件];

注意:如果不加任何条件,会将表中所有的记录全部修改。

6.4 查询数据

语法

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组之后的条件
order by
    排序
limit
    分页限定

测试表:

create table student(
    id int,
    name varchar(32),
    age int,
    english double(4,1),
    math double(4,1)
)

基础查询

  1. 多个字段查询

    select 字段名1,字段名2,... from 表名;
    
    select * from 表名; -- 查询所有字段
  2. 去除重复: 使用关键字 distinct

  3. 计算列

    • 一般可以使用四则运算计算一些列的值(一般只会对数值型的数据进行计算)。

    • 如果有字段为null参与运算,结果都会null,这时可以使用ifnull关键字,具体格式如下

      ifnull(表达式1,表达式2)
      -- 表达式1:需要判断是否为null的字段
      -- 表达式2:如果该字段为空,所替换的值
  4. 取别名

    使用as关键字,as也可以省略。

select * from student;

select age from student;

--去除重复结果
select distinct age from student;

--技术math和english分数之和
--如果有NULL参与,结果都为NULL
select name,math,english,math+english from student;

--技术math和english分数之和
--使用IFNULL
select name,math,english,math+IFNULL(english,0) from student;

--字段取别名
select name,math,enlish,math+IFNULL(endlish,0) as 总分 from student;
select name,math,enlish,math+IFNULL(endlish,0)  总分 from student; --可以不用as,直接空格也可以

条件查询

使用 where字句

  1. 使用运算符 > , < , <= , >= , = ,!= , <>
--查询年龄大于(等于)20岁
select * from student where age > 20;
select * from student where age >= 20;

--查询年龄等于20岁
select * from student where age = 20; --是一个等号,不是==

--查询年龄不等于20岁  != 和 <>效果是一样的
select * from student where age != 20;
select * from student where age <> 20;

  1. 使用 && , and ,between ... and
--查询年龄大于等于20 小于等于30
select * from student where age >=20 && age <= 30; --推荐使用 and
select * from student where age >=20 and age <= 30;
select * from student where age between 20 and 30;
  1. 使用in (集合)
--查询年龄22岁,19岁,25岁的信息
select * from student where age = 22 or age = 19 or age = 25;
select * from student where age in (22,19,25);
  1. 使用 is null ,is not null
--查询英语成绩为null
select * from student where english = null; -- 这是不对的写法,null不能使用 = 和!=判断
select * from student where english is null; -- 使用is关键字

--查询英语成绩不为null
select * from  student where english is not null;
  1. 使用like

占位符:

  • _ : 单个任意字符
  • % : 多个任意字符
--查询name中姓马的有哪些?  使用like
select * from student where name like '马%';  --单引号 双引号都行

--查询name中第二个字是“华”的有哪些 ?
select * from student where name like "_华%";

--查询姓名是三个字的人
select * from student where name like '___';

--查询姓名中包含“马”的人
select * from student where name like '%马%';

排序查询

语法:

-- order by 字句
order by 排序字段1 ,排序方式1, 排序字段2,排序方式2 ....   

排序方式:

  • ASC :升序,默认为升序。
  • DESC : 降序。

排序规则: 如果有多个排序条件,则当前面的条件值一样是,才会判断后面的条件值。

-- 按数学成绩排序
select * from student order by math asc; --升序
select * from student order by math desc; --降序

-- 先按数学成绩排序,如果数学成绩一样,再按英语成绩排序
select * from student order by math asc, english asc;

聚合函数

将一列数据作为一个整体,进行纵向的计算。

注意:聚合函数会排除null的值

解决方案:

(1)选择不包含null的列进行计算

(2)使用ifnull函数

  1. count :计算个数
    • 一般选择非null的列,如主键 。
    • 可以使用count(*)
select count(name) from student;
select count(ifnull(english ,0)) from student;
select count(id) from student;
select count(*) from student;
  1. max :计算最大值
  2. min : 计算最小值
  3. sum :计算和
  4. avg : 计算平均值
select max(math) from student;
select min(math) from student;
select sun(math) from student;
select avg(math) from student;

分组查询

语法

-- group by
group by 分组字段;

注意:

  1. 分组之后查询的字段只能是分组字段和聚合函数
  2. wherehaving的区别?
    • where在分组前进行限定,不满足条件不参与分组;having在分组后进行限定,如果不满足条件则不会被查出来。
    • where后不可以跟聚合函数,having后面可以进行聚合函数判断。
-- 按照性别分组。分别查询男、女同学的数学平均分
select sex ,avg(math) from student group by sex;

-- 按照性别分组。分别查询男、女同学的数学平均分,人数
select sex ,avg(math),count(id) from student group by sex;

-- 按照性别分组。分别查询男、女同学的数学平均分,人数。要求分数小于70的人不参与分组
select sex ,avg(math),count(id) from student where math > 70 group by sex;

-- 按照性别分组。分别查询男、女同学的数学平均分,人数。要求分数小于70的人不参与分组,且分组后组的人数要大于2
select sex ,avg(math),count(id) from student where math > 70 group by sex having count(id);

分页查询

语法:

-- limit
limit 开始的索引 ,每页查询的条数;

开始的索引 = (当前的页码 - 1)* 每页显示的条数

-- 每页显示3条记录
select * from student limit 0,3;  --第1页
select * from student limit 3,3;  --第2页
select * from student limit 6,3;  --第3页

limit关键字是在MySQL中特有,OracleSqlServer没有

7 约束

概念: 对表中的数据进行限定,保证数据的正确性、有效性和完整性

分类:

  1. 主键约束:primary key
  2. 非空约束:not null
  3. 唯一约束:unique
  4. 外键约束:foreign key

MySQL 数据类型

来源:https://www.runoob.com/mysql/mysql-data-types.html

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。


数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型大小范围(有符号)范围(无符号)用途
TINYINT1 字节(-128,127)(0,255)小整数值
SMALLINT2 字节(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 字节(-8 388 608,8 388 607)(0,16 777 215)大整数值
INT或INTEGER4 字节(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值
BIGINT8 字节(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值
FLOAT4 字节(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度 浮点数值
DOUBLE8 字节(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度 浮点数值
DECIMAL对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性,将在后面描述。

类型大小 (字节)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值,时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535 字节变长字符串
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
TINYTEXT0-255字节短文本字符串
BLOB0-65 535字节二进制形式的长文本数据
TEXT0-65 535字节长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215字节中等长度文本数据
LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295字节极大文本数据

CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。


本文作者:foochane
本文链接:https://foochane.cn/article/2020011301.html
版权声明:本文采用 知识共享署名 4.0 国际许可协议 ,转载请注明出处!
  目录