数据库
1.数据库概念(了解)
1.1 什么是数据库
数据库就是用来存储和管理数据的仓库
数据库存储数据的优点:
- 可存储大量数据
- 方便检索
- 保持数据的一致性、完整性
- 安全,可共享
- 通过组合分析,可产生新数据
1.2 数据库的发展历史
- 没有数据库,使用磁盘文件存储数据
- 层次结构模型数据库
- 网状结构模型数据库
- 关系结构模型数据库:使用二维表格来存储数据 - > 关系型
- 关系·对象模型数据库
MySQL就是关系型数据库
1.3 常见数据库
- Oracle(神喻):甲骨文(最高)
- DB2:IBM
- SQL Server:微软
- Sybase:塞尔斯
- MySQL:甲骨文
1.4 理解数据库
数据库泛指“关系型数据库管理系统”(RDBMS-Relational database management system),即“数据库服务器”
- RDBMS = 管理员(manager)+仓库(database)
- database = N个table
- table:
- 表结构:定义表的列数,列名和列类型
- 数据库是一个多行多列的表格,其中行数是没有上限的
- 向表格中添加数据是以行为单位的
- 表记录:一行一行的记录
- 表结构:定义表的列数,列名和列类型
1.5 Java应用程序与数据
应用程序使用数据库完成对数据的存储
- 应用程序向数据库请求数据,并显示结果
- 数据库服务器响应和提供数据
2.安装MySQL数据库
2.1 安装MySQL
校验MySQL:(命令行中)
- 登录MySQL:mysql -uroot -p123
- 退出MySQL:exit | quit
- 查看数据库:show database
2.2 删除MySQL
- 添加删除程序中卸载MySQL
- 到安装目录删除MySQL
- 删除目标文件
- 查看注册表:搜索mysql,找到一律删除
- 重启
2.3 MySQL目录结构
- bin\mysqld.exe:服务器端程序,服务器开启时有。服务器只有一个
- bin\mysql.exe:客户端程序,用来连接服务器。客户端可以开多个
- bin\my.ini:服务器配置文件
- 配置MySQL的端口:默认为3306,没有必要去修改它
- 配置字符编码:
- [client]下配置客户端编码:default-character-set=gbk
- [mysqld]下配置服务器编码:character-set-server=utf8
- 配置二进制数据大小上限:
- 在[mysqld]下配置:max_allowed_packet=8M
- \data:多个数据库,每个库中有多个表(.frm)
3.基本命令
3.1 启动和关闭ays(服务器操作)
- 开启服务器(必须保证mysql为Windows服务):net start mysql
- 查看进程表中是否存在:mysql.exe进程(存在)
- 关闭服务器(必须保证mysql为Windows服务):net stop mysql
- 查看进程表中是否存在:mysqld.exe进程(不存在)
3.2 客户端登录退出
- 登录服务器:mysql -uroot -p123 -hlocalhost
- -u:后面跟随用户名
- -p:后面跟随密码
- -h:后面跟随IP
- 退出服务器:exit或quit
SQL语句
1.SQL概述
1.1 什么是SQL
结构化查询语言(Structured Query Language)
- 作用:客户端使用SQL来操作服务器
- 启动mysql.exe,连接服务器后,就可以使用sql来操作服务器了
- 将来会使用Java程序连接服务器,然后使用sql来操作服务器
- SQL标准
- 例如SQL99,即1999年制定的标准
- 由国际标准化组织(ISO)制定的,对DBMS的统一操作方式(例如相同的语句可以操作:mysql,oracle等)
- SQL方言
- 某种DBMS不止会支持SQL标准,而且还会有一些自己独特的语法
- 例如limit语句只在MySQL中可以使用
1.2 语法要求
- SQL语句可以在单行或多行书写,以分号结尾
- 可以使用空格和缩进来增强语句的可读性
- MySQL不区别大小写,建议使用大写
- 在数据库中所有的字符串类型,必须使用单引,不能使用双引(日期类型也要使用单引)
2.分类
- DDL(Data Definition Language)
- 数据定义语言
- 用来定义数据库对象:库,表,列等
- 数据库或表的结构操作
- 创建,删除,修改:库,表结构
- DML(Data Manipulation Language)
- 数据操作语言
- 用来定义数据库记录(数据)
- 对表的记录进行更新(增、删、改)
- DCL(Data Control Language)
- 数据控制语言
- 用来定义访问权限和安全级别
- 对用户的创建,及授权
- DQL(Data Query Language)
- 数据查询语言
- 用来查询记录(数据)
3.DDL
3.1 操作数据库
- 查看所有数据库:SHOW DATABASES
- 切换(选择要操作的)数据库:USE 数据库名
- 创建数据库:CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARSET=utf8]
- 删除数据库:DROP DATABASE [IF EXISTS] 数据库名
- 修改数据库编码:ALIETR DATABASE 数据库名 CHARACTER SET utf8
3.2 数据类型(列类型)
- int:整型
- double:浮点型
- double(n,m)表示最多n个数字,其中必须有m位小数
- decimal:浮点型
- 十进制的,在表单钱方面使用该类型,因为不会出现精度缺失问题
- char:固定长度字符串类型
- char(n)(n最大为255)
- 数据的长度不足指定长度时,会自动补足到指定长度
- varchar:可变长度字符串类型
- 取实际输入字符串长度
- 会占1,2个字节来存储字符串长度
- text(clob):字符串类型(可变长度字符串)
- 很小:tinytext(2^8-1B,256B)
- 小:text(2^16-1B,64K)
- 中:mediumtext(2^24-1B,16M)
- 很大:longtext(2^32-1B,4G)
- blob:字节类型(可变长度二进制类型)
- 很小:tinyblob(2^8-1B,256B)
- 小:blob(2^16-1B,64K)
- 中:mediumblob(2^24-1B,16M)
- 很大:longblob(2^32-1B,4G)
- date:日期类型
- 格式为:yyyy-mm-dd
- time:时间类型
- 格式为:hh:mm:ss
- timestamp:时间戳类型
3.3 操作表
- 创建表:
CREATE TABLE [IF NOT EXISTS] 表名(
列名 列类型,
列名 列类型,
……
列名 列类型
);
-
查看当前数据库中所有表名称:SHOW TABLES;
-
查看指定表的创建语句:SHOW CREATE TABLE 表名;
-
查看表结构:DESC 表名;
-
删除表:DROP TABLE 表名;
-
修改表:前缀,ALTER TABLE 表名
- 修改之添加列:
ALTER TABLE 表名 ADD(
列名 列名称,
列名 列名称,
……
);
-
修改之修改列类型(如果被修改的列已经存在数据,那么新的类型可能影响到已存在的数据)
ALTER TABLE 表名 MODIFY 列名 列类型;
-
修改之修改列名:
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
-
修改之删除列:
ALTER TABLE 表名 DROP 列名;
-
修改表名称:
ALTER TABLE 原表名 RENAME TO 新表名;
4.DML
4.1 插入数据
INSERT INTO 表名(
列名1,列名2,……
)
VALUES(
列值1,列值2,……
);
-
在表名后给出要插入的列名,其他没有指定的列等同于插入NULL值
-
在VALUES后给出列值,值的顺序和个数必须与前面指定的列对应
-
插入记录总是插入一行,不可能是半行
-
INSERT INTO 表名(列值1,列值2,……)
- 没有给出要插入的列,那么表示插入所有列
- 值的个数必须是该表列的个数
- 值的顺序,必须与表创建时给出的列的顺序相同
4.2 修改数据
-
UPDATE 表名 SET 列名1=列值1,列名2,…… [WHERE 条件];
-
条件
- 条件是可选的,若是没有条件,则对该列所有值进行修改
- 条件必须是一个boolean类型的值或表达
- 运算符:=、!=、<>、>、<、>=、<=、BETWEEN…AND、IN< … >、IS NULL、NOT、OR、AND
- <>:大于小于(即不等于)
- BETWEEN A AND B:值在AB之间
- IN< A,B,…… >:值在A,B,……之中
- IS [NOT] NULL:列值=null的值永远为false
4.3 删除数据
DELETE FROM 表名 [WHERE 条件];
- 若不加条件会将整个表删除,故一般都需有备份
TRUNCATE TABLE 表名;
- YRUNCATE是DDL语句,它是先删除该表,再create该表,而且无法回滚
5.DCL
- 一个项目创建一个用户
- 一个项目对应的数据库只有一个
- 这个用户只能对这个数据库有权限,其他数据库就不能操作了
5.1 创建用户
CREATE USER 用户名@IP地址 IDENTIFIED BY '密码';
- 用户只能在指定的IP地址上登录
-CREATE USER 用户名@'%' IDENTIFIED BY '密码';
- 用户可以在任意IP地址上登录
- 用户只能在指定的IP地址上登录
5.2 给用户授权
GRANT 权限1,……,权限n ON 数据库.* TO 用户名@IP地址;
- 权限、用户、数据库
- 给用户分配在指定的数据库上的权限
- 权限:ALTER,DROP,INSERT,UPDATE,DELETE,SELECT…ALL
5.3 撤销授权
REMOVE 权限1,……权限n ON 数据库.* FROM 用户名@IP地址;
- 撤销指定用户在指定数据库上的指定权限
5.4 查看权限
SHOW GRANTS FOR 用户名@IP地址
- 查看指定用户的权限
5.5 删除用户
DROP USER 用户名@IP地址
6.DQL
6.1 基本查询
6.1.1 字段(列)控制
- 查询所有列:
SELECT * FROM 表名;
- 其中“*”表示查询所有列
- 查询指定列:
SELECT 列1,[列2,……] FROM 表名;
SELECT *,列1 FROM 表名;
- 可以在打印所有列后再加上列1
- 完全重复的记录只显示一次
- 当查询结果中的多行记录一模一样时,只显示一行,一般查询所有列时很少会有这种情况,但只查询一列(或几列)时,总可能很大
- SELECT DISTINCT *|列1,[列2,……] FROM 表名;
- 列运算
- 数量类型的列可以做加、减、乘、除运算
SELECT 列1*a FROM 表名;
-SELECT 列1+列2 FROM 表名;
- 字符串类型可以做连续运算
SELECT CONCAT('str',列1) FROM 表名;
- 转换NULL值
- NULL加任何值都为NULL
- 有时候需要把NULL转换成其他值,比如0
SELECT IFNULL(列1,0)+a FROM 表名;
- 给列起别名
SELECT 列1+列2 [AS] 别名 FROM 表名;
- 数量类型的列可以做加、减、乘、除运算
6.1.2 条件查询
-
条件查询
- 使用WHERE子句来控制记录
-SELECT 列1,[列2,……] FROM 表名 WHERE 条件;
- 使用WHERE子句来控制记录
-
模糊查询
当查询字符串中含有A字符的元素,可以采用模糊查询
SELECT * FROM 表名 WHERE 列名1 LIKE 'A_';
- 模糊查询需要使用运算符:
- _匹配任意一个字符
- eg:LIKE ‘___’:匹配三个字符
- %匹配0-n个字符
- eg:LIKE ‘%’:匹配除NULL外的所有元素
- _匹配任意一个字符
6.2 排序
- 升序:
SELECT * FROM 表名 ORDER BY 列名 ASC;
- ASC可以省略
- 降序:
SELECT * FROM 表名 ORDER BY 列名 DESC;
- DESC不能省略
- 使用多列作为排序条件:
SELECT * FROM 表名 ORDER BY 列名1 关键字1,列名2 关键字2,……;
6.3 聚合函数
聚合函数用来做某列的纵向运算
-
COUNT
-
SELECT COUNT(*/1/2) FROM 表名;
- 计算表中所有列都不为NULL的记录的行数
-
SELECT COUNT(列名) FROM 表名;
- 计算表中该列不为NULL的记录的行数
-
-
MAX:
SELECT MAX(列名) FROM 表名
-
MIN:
SELECT MIN(列名) FROM 表名
-
SUM:
SELECT SUM(列名) FROM 表名
-
AVG:
SELECT AVG(列名) FROM 表名
6.4 分组查询
分组查询是把记录使用某一列进行分组,然后查询组信息
SELECT 分组列,聚合函数(组信息) FROM 表名 GROUP BY 分组列;
- 设置条件,注意是在分组前还是分组后
SELECT 分组列,聚合函数(组信息) WHERE 条件 FROM 表名 GROUP BY 分组列;
SELECT 分组列,聚合函数(组信息) FROM 表名 GROUP BY 分组列 HAVING 条件;
6.5 limit子句(方言)
LIMIT用来限定查询结果的起始行,以及总行数
SELECT * FROM 表名 起始行,总行数;
- 可用于分页查询:LIMIT (当前页-1)*每页记录数,每页记录数