MySQL：(一)

数据库概述

什么是数据库

数据库是一个文件系统。通过标准SQL语言操作文件系统中数据——用来存放软件系统的数据！
SQL：Structured Query Language 结构查询语言

常用数据库简介

Oracle：甲骨文公司，专门数据库厂商，收购SUN、MySQL ------- 收费，大型数据库，用于任何系统任何平台神谕代神说话的人；
MySQL：早期开源免费数据库产品，建议用5.0---5.5
LAMP组合 Linux + Apache + MySQL + PHP 完全开源免费，自从mysql被oracle收购后，从6.0开始出现收费版本
DB2：IBM数据库产品，大型收费数据库与websphere服务器一起使用；
SYBASE：中等规模数据库，收费 PowerDesigner 数据库建模工具；
SQL Server：微软公司数据库产品，收费中等规模数据库，操作系统要求是windows 结合.net 一起使用；

Java开发者主要使用 MySQL、Oracle、DB2 三种数据库

MySQL安装配置和卸载

安装配置

安装mysql数据库
自定义安装目录：
MySQL Server 默认位置 C:\program files\mysql目录
Server Data File 数据文件 C:\Documents and Settings\All Users\Application Data\MySQL目录
⚠️⚠️⚠️当你需要卸载的时候需要将此目录删除。否则会导致重新安装数据库失败！因为windoes默认卸载不会删除此目录的。
安装过程中配置
1. 配置mysql默认字符集
  默认latin1等价于ISO-8859-1改为utf8(mysql中没有utf-8)
2. 将mysql/bin目录配置环境变量path
  Include Bin Directory in Window Path——☑️（勾选），自动配置好mysql环境变量，可以帮助我们快速进入mysql数据库的主界面。
3. 输入超级管理员root／root，系统默认用户名、管理员：root！验证安装成功
  测试mysql是否安装成功，启动cmd窗口：
  输入 mysql -u root -p 回车输入密码 root ==== 出现 mysql> 安装成功！
重置root密码(一般不会重置密码，了解即可)
1. 停止mysql服务器运行输入services.msc 停止mysql服务；
2. 在cmd下输入 mysqld --skip-grant-tables 启动服务器光标不动（不要关闭该窗口）；
3. 新打开cmd 输入mysql -u root -p 不需要密码
```
use mysql;
update user set password=password('abc') WHERE User='root';
```
4. 关闭两个cmd窗口在任务管理器结束mysqld 进程；
5. 在服务管理页面重启mysql 服务，密码修改完成。

卸载

在控制面板中卸载MySQL；
删除mysql 安装目录；
删除MySQL数据文件目录(⚠️数据存放目录一定要删除)，此目录是隐藏的，需要通过my.ini文件查找，用编辑器打开此文件查找datadir 的路径位置！

MySQL数据库存储结构

数据存储方式

mysql数据库服务器可以创建多个数据库：database ，每个database 都是独立的，开发中一般是一个工程对应一个数据库；
每一个数据库可以创建多张数据表 table，表是数据库的基本单元，每张数据表用来保存数据记录（表对应java中的对象）。
SQL语句（简称：结构化查询语句）
特点：非过程性一条SQL语句一个执行结果
为了使用SQL 编写复杂程序，各个数据库厂商对SQL 进行增强，SQL Server TSQL 、Oracle PLSQL 。

SQL语句分类

　　按功能分类：定义(DDL)、管理(DML)、控制(DCL)、查询(DQL)

⚠️所有表结构操作语句都是DDL语句

数据库操作语句

创建数据库

基本语法：create database 数据库名称;(采用默认字符集)
```
create database mydb1;
```

复杂语法：create database 数据库名称 character set 字符集；
```
create database mydb2 character set utf8;
```
⚠️每次创建一个数据库，在数据存放目录中生成一个文件夹，每个文件夹中存在db.opt 存放默认字符集和校对规则！

查询数据库

-- 查看所有数据库
show DATABASES;
-- 查看数据库定义信息
show create DATABASE db_name;
-- 查看当前使用的数据库
select DATABASE();
-- 切换当前数据库
use db_name;

删除数据库

-- 删除数据库
drop DATABASE db_name;

修改数据库编码集

-- 修改数据库db_name编码集
alter DATABASE db_name CHARACTER SET gbk;

数据库表结构操作语句

创建表

建表语法：create table 表名(列名类型(长度),列名类型(长度)... );
一个数据表可以存在很多列，每列具有类型和长度

- ⚠️只有字符串类型必须指定长度，而其它类型都有默认长度；
- ⚠️创建表时没有指定字符集，将采用数据库默认字符集；
- ⚠️创建表之前记得切库，必须使用use db 语法指定操作数据库！

MySQL常用数据类型

mysql中字符串型VACHAR、CHAR<--->java中String、char
CHAR是固定长度、VARCHAR是可变长度；例如：char(8) 保存 lisi，因为lisi 只有四个字符，所有会补充四个空格，成为8个字符存入 char(8)中，
如果有 varchar(8) 自动根据存放内容改变长度
mysql数值类型TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE<--->java中byte、short、int、long、float、double
mysql逻辑性BIT存放一位数值0/1<--->java中boolean
mysql日期型DATE(只有日期)、TIME(只有时间)、DATETIME(日期时间都有)、TIMESTAMP(日期时间都有)<--->java中Date
DATETIME和TIMESTAMP 表现形式上完全相同，区别就在于TIMESTAMP 在数据库可以自动更新（当前时间）
mysql大数据类型BLOB(存储大二进制数据)、TEXT(存储大文本文件)<--->java中blob(二进制文件)、text(文本文件)
- tinyblob tinytext 255字节
- blob text 64KB
- mediumblob mediumtext 16MB
- longblob longtext 4GB

表约束操作

　　约束用来保证数据有效性和完整性。

主键约束－primary key
- 主键：区分记录中不同记录的唯一字段，该字段赋予主键，称之主键约束（唯一、非空）；
- 结论：⚠️表必须含有主键，否则表非法！
唯一约束－unique
- 该字段的值不允许重复，比如email 邮箱是不可以重复的
  - 一张表中可以有很多个唯一约束，可以设置多个字段联合作为主键--联合主键；
  - null值不做比较，即使设置了唯一约束的字段，也可以存在多个null值！
非空约束－not null
该字段不能为空，必须有值！
自增长－auto_increment
一般用于主键字段，插入数据时，其值交给数据库去维护！

建表

CREATE TABLE `operate_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `manage_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '管理表id',
  `product_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '产品编号',
  `product_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '产品类型',
  `trans_id` varchar(50) NOT NULL DEFAULT '0' COMMENT '交易编号',
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户编号',
  `STATUS` tinyint(4) DEFAULT '0' COMMENT '操作：1:额度退出 2:额度恢复 3:额度调整',
  `amount` decimal(18,6) DEFAULT '0.000000' COMMENT '操作金额',
  `is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除:0.不删除 1.删除',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `creator` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建人',
  `updator` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新人',
  `VERSION` int(11) NOT NULL DEFAULT '1' COMMENT '版本',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_operate_record` (`trans_id`,`STATUS`,`is_delete`),
  KEY `idx_manage_id` (`manage_id`),
  KEY `idx_trans_id` (`trans_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_product_type` (`product_type`),
  KEY `idx_STATUS` (`STATUS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='操作记录表';

表结构操作

表结构修改

-- 增加列 语法：alter table 表名 add 列(可以省略列column) 列名 类型(长度) 约束;
ALTER TABLE `table_name` ADD COLUMN `lock_amount` DECIMAL(18,6) NOT NULL DEFAULT '0.00' COMMENT '锁定金额' AFTER `amount`;
-- 修改现有列类型、长度和约束 语法：alter table 表名 modify 列名 类型(长度) 约束;
ALTER TABLE `table_name` MODIFY COLUMN `product_type` TINYINT(10) NOT NULL COMMENT '产品类型...';
-- 修改现有列(名称、约束等) 语法:alter table 表名 change 旧列名 新列名 类型(长度) 约束;
ALTER TABLE `table_name` CHANGE old_column_name new_column_name TINYINT(10) NOT NULL COMMENT '产品类型...';
-- 删除现有列 语法：alter table 表名 drop 列名;
ALTER TABLE `table_name` DROP column_name;
-- 修改表名 语法：rename table 旧表名 to 新表名;
RENAME TABLE old_table_name to new_table_name;
-- 修改表字符集
RENAME TABLE table_name character set utf8;

数据表删除

-- 数据表删除
DROP TABLE table_name;

查看表结构

-- 查看表结构
DESC table_name;
-- 查看当前库所有表
SHOW TABLES;
-- 查看建表语句和字符集
SHOW CREATE TABLE table_name;

数据库表记录操作语句

Insert语句

INSERT INTO table_name (`manage_id`, `product_id`, `product_type`, `trans_id`, `user_id`, `STATUS`, `amount`)
VALUES
    (18, 1008927, 26, '100000006', 1008927, 1, 8000.00);

⚠️补充小知识：

通过cmd窗口插入一条中文记录可能会出现下面的现象：
```
insert into employee(id,name,job,salary) values(4,'小明','清洁员',1500);
```
出错了：
```
ERROR 1366 (HY000): Incorrect string value: '\xC3\xF7' for column 'name' at row 1 ;
```
错误原因：cmd窗口，接受的字符只能是gbk，所以mysql client采用默认字符集编码gbk
查看系统所有字符集：
```
show variables like 'character%';
```
MYSQL中共6处使用了字符集

client、connetion、result 和客户端相关，database、server、system 和服务器端相关
解决办法：修改客户端字符集位gbk
- 第一种方案：当前窗口临时修改，只对当前窗口有效，关闭后就会失效
```
-- 仅对cmd 窗口使用
set names gbk;
/* 或 */set character_set_results=gbk;
```
- 第二中方案：配置mysql/my.ini 文件(永久改变字符。一般习惯是utf8 所以不建议修改)
  ⚠️my.ini文件中，出现[mysql] 表示客户端配置，［mysqld] 表示服务器端配置
  
  修改客户端字符集 [mysql] 后字符集 default-character-set=gbk，当修改my.ini配置文件之后，需要重启Mysql 数据库！

Update语句

update table_name set status=1 where id in (。。。);

⚠️如果没有where条件语句，默认修改所有行数据，危险操作，三思！

Delete语句

语法：delete from 表名 where条件语句；如果没有where语句，将删除表中所有记录
- ⚠️使用 truncate table 表名;等价于delete from 表名;删除表所有数据；
- delete语句不能删除某一列的值（可使用update）针对记录的删除操作；
- 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table 表名;语句。
```
delete from employee where name='zhangsan';
```

面试题：delete删除表所有数据和truncate删除表记录，有什么区别？
- 分类：truncate 属于DDL ，delete 属于DML；
- truncate 删除数据，过程先将整个表删除，再重新创建；delete 删除数据，逐行删除记录 ,表结构存在！
- truncate效率要高于delete；
- 事务管理只能对DML有效，被事务管理SQL语句可以回滚到SQL执行前状态；Truncate数据删除不能回滚，数据无法恢复，相当于delete+commit；
```
start transaction;-- 开启事务
-- truncate table employee;-- truncate删除数据操作，事务不能回滚，数据无法恢复，相当于delete+commit,数据永久删除
delete from employee;-- 删除表中记录，delete删除的数据可以通过回滚操作来恢复数据！
select * from test1;-- 查询表，记录不存在
rollback;-- 事务回滚，表中数据恢复！
```

Select语句

基本select查询

-- 语法一：基本select语句
-- 查询表中所有列信息
select * from table_name;
-- 查询表中指定列的信息
select 列名,列名... from table_name;
-- distinct 用于过滤重复数据
select [distinct] * 列名,列名... from table_name;

-- 语法二：使用表达式对查询对列进行运算
select 表达式(列名执行运算) from table_name;
select name,chinese+math+english as 总分 from exam;
-- 使用as定义列别名，as可以省略
select 列名 as 别名 from table_name; 

-- 语法三：使用where子句，进行条件过滤查询
select * from table_name where name='关羽';

查询之运算符

between ... and ... 闭区间（between 70 and 80 等价于 >=70 and <=80）;

select * from exam where english>=90 and english <= 100;
select * from exam where english between 90 and 100;
select * from exam where math in(65,75,85);
select * from exam where name like '赵%';
select * from exam where chinese is null;
select * from exam where chinese is not null;

查询之排序

-- 语法:select * from 表名 order by 列名 asc|desc ;  ---- asc升序 desc降序
-- 默认asc升序
select * from exam order by math;
-- 按照英语进行降序排序，英语相同学员按照数学降序
select * from exam order by english desc,math desc;

查询之函数
聚集函数：指SQL语句中内置函数；
分组函数：用于统计

count：统计查询结果记录条数

-- 统计总分大于220的人数 
select count(*) from exam where chinese+math+english > 220;

sum：统计某列数据的和

-- 统计一个班级语文、英语、数学的成绩总和（使用ifnull函数处理 null情况）
select sum(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;

avg：统计某列平均值

-- 求一个班级总分平均分（使用ifnull函数处理 null情况）
select avg(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;

max：统计某列最大值，min：统计某列最小值

-- 求班级最高分和最低分（数值范围在统计中特别有用）
select max(chinese+math+english) ,min(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;

语法：select 分组函数 from exam group by 列名;

-- 按照某列进行分组统计（分组操作，就是具有相同数据记录分到一组中，便于统计）
-- 查询购买了几类商品，并且每类总价大于100的商品
select product,sum(price) from orders group by product having sum(price) > 100;

面试题：where 和 having 条件语句的区别？
- where 是在分组前进行条件过滤，having 是在分组后进行条件过滤；
- 使用where地方都可以用 having替换；
- having可以使用分组函数，而where后不可以用分组函数

SELETE语法小结

-- select 语句写法 ： 
-- S-F-W-G-H-O 组合 select ... from ... where ... group by... having... order by ... ; 
-- 数据库解析的顺序：不能改变 
-- 解析顺序 ： from  - where -  group by - having - select - order by

MySQL数据库备份和恢复

数据库备份
备份命令 mysql/bin/mysqldump 将数据库SQL语句导出
语法：mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径
⚠️备份时，脚本没有建库语句！
恢复数据库
- mysql命令～cmd下
  - 先登录mysql必须要手动添加数据库db_name--->exit
  - mysql/bin/mysql 将sql文件导入到数据库

案例练习

　　链接: https://pan.baidu.com/s/1i4JU1NJ 密码: yfqn

posted @ 2017-11-19 23:39 灰太郎^_^ 阅读(479) 评论(0) 编辑收藏举报

刷新页面返回顶部

灰太郎^_^