MySQL及事务基础了解

MySQL基础

数据库相关概念

数据库：
-- 存储和管理数据的仓库 , 数据是有组织的进行存储 --
-- 英文名 DataBase , 简称 DB

数据库管理系统:
-- 管理数据库的大型软件
-- 英文名 DataBase Management System , 简称 DBMS

常见数据库管理系统:
* Oracle ：收费的大型数据库，Oracle 公司的产品
* MySQL ： 开源免费的中小型数据库。后来 Sun公司收购了 MySQL,而 Sun 公司又被 Oracle 收购
* SQL Server ：MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用
* PostgreSQL ：开源免费中小型的数据库
* DB2 ：IBM 公司的大型收费数据库产品
* SQLite ：嵌入式的微型数据库。如：作为 Android 内置数据库
* MariaDB ：开源免费中小型的数据库

SQL
-- 英文名 Structured Query Language , 简称 SQL , 结构化查询语言
操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准，可以使用SQL操作所有的关系型数据库管理系统，以后工作中如果使用到了其他的数据库管理系统，也同样的使用SQL来操作。

MySQL数据模型

关系型数据库
-- 建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的 二维表 组成的数据库

优点
1.都是使用表结构，格式一致，易于维护。
2.使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
	2.1关系型数据库都可以通过SQL进行操作，所以使用方便。
	2.2复杂查询。现在需要查询001号订单数据，我们可以看到该订单是1号客户的订单，而1号订单是李聪这个客户。以后也可以在一张表中进行统计分析等操作。
3.数据存储在磁盘中，安全。

小结
1.MySQL中可以创建多个数据库，每个数据库对应到磁盘上的一个文件夹
2.在每个数据库中可以创建多个表，每张都对应到磁盘上一个 frm 文件
3.每张表可以存储多条数据，数据会被存储到磁盘中  MYD 文件中

SQL概述

简介
* 英文：Structured Query Language，简称 SQL
* 结构化查询语言，一门操作关系型数据库的编程语言
* 定义操作所有关系型数据库的统一标准
* 对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”

注释
单行注释: -- 注释内容 或 #注释内容(MySQL特有)
多行注释: /* 注释内容 */

SQL分类

1.DDL

数据定义语言,用来定义数据库对象: 数据库,表,列等

1.1操作库

查询所有数据库: SHOW DATABASES;

创建数据库: CREATE DATABASE 数据库名称;

创建数据库(判断,如果不存在则创建): CREATE DATABASE IF NOT EXISTS 数据库名称;

删除数据库: DROP DATABASE 数据库名称;

删除数据库(判断,如果存在则删除): DROP DATABASE IF EXISTS 数据库名称;

使用数据库: USE 数据库名称;

查看当前使用的数据库: SELECT DATABASE();

1.2操作表

查询当前数据库下所有表名称: SHOW TABLES;

查询表结构: DESC 表名称;

创建表: 
	CREATE TABLE 表名(
   		字段名1  数据类型1,
    	字段名2  数据类型2,
    	……
		字段名n  数据类型n
    );
案例:
-- 创建包含 id , username , password 结构的表,数据类型分别为 int , 20长度的 String , 32长度的 String
create table tb_user (
	id int,
    username varchar(20),
    password varchar(32)
);

删除表: DROP TABLE 表名;

删除表是判断表是否存在: DROP TABLE IF EXISTS 表名;

修改表名: ALTER TABLE 表名 RENAME TO 新的表名;

添加一列: ALTER TABLE 表名 ADD 列名 数据类型;

修改数据类型: ALTER TABLE 表名 MODIFY 列名 新数据类型;

修改列名和数据类型: ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;

删除列: ALTER TABLE 表名 DROP 列名;

1.3数据类型

数值:
tinyint : 小整数型，占一个字节
int	： 大整数类型，占四个字节
	eg ： age int
double ： 浮点类型
	使用格式： 字段名 double(总长度,小数点后保留的位数)
	eg ： score double(5,2) 

日期:
date ： 日期值。只包含年月日
	eg ：birthday date ： 
datetime ： 混合日期和时间值。包含年月日时分秒

字符串:
char ： 定长字符串。
	优点：存储性能高
	缺点：浪费空间
	eg ： name char(10)  如果存储的数据字符个数不足10个，也会占10个的空间
varchar ： 变长字符串。
	优点：节约空间
	缺点：存储性能底
	eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间

1.4案例测试

需求:

设计一张学生表，请注重数据类型、长度的合理性
	1. 编号
	2. 姓名，姓名最长不超过10个汉字
	3. 性别，因为取值只有两种可能，因此最多一个汉字
	4. 生日，取值为年月日
	5. 入学成绩，小数点后保留两位
	6. 邮件地址，最大长度不超过 64
	7. 家庭联系电话，不一定是手机号码，可能会出现 - 等字符
	8. 学生状态（用数字表示，正常、休学、毕业...）

语句设计:

create table student (
	id int,
	name varchar(10),
	gender char(1),
	birthday date,
	score double(5,2),
	email varchar(15),
	tel varchar(15),
	status tinyint
);

表结构改动

-- 将表名student修改为stu
alter table student rename to stu;

-- 给stu表添加一列address，该字段类型是varchar(50)
alter table stu add address varchar(50);

-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);

-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table stu change address addr varchar(50);

-- 将stu表中的addr字段 删除
alter table stu drop addr;

2.DML

对数据进行增删改操作

2.1添加数据

给指定列添加数据: 
	INSERT INTO 表名(列名1,列名2,……) VALUES(值1,值2,……);

给全部列添加数据:
	INSERT INTO 表名 VALUES(值1,值2,……);

批量添加数据:
	INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
	INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;

案例演示:
-- 给指定列添加数据
INSERT INTO stu (id, NAME) VALUES (1, '张三');
-- 给所有列添加数据，列名的列表可以省略的
INSERT INTO stu (id,NAME,sex,birthday,score,email,tel,STATUS) VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);

INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);

-- 批量添加数据
INSERT INTO stu VALUES 
	(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
	(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
	(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);

2.2修改数据

修改表数据:
	UPDATE 表名 SET 列名1=值1,列名2=值2,……[WHERE 条件];
	
案例演示:
-- 将张三的性别改为女
update stu set sex = '女' where name = '张三';

-- 将张三的生日改为 1999-12-12 分数改为99.99
update stu set birthday = '1999-12-12', score = 99.99 where name = '张三';

/*
如果update语句没有加where条件，则会将表中所有数据全部修改！

update stu set sex = '女';

会将sex列所有数据更改为'女'
*/

2.3删除数据

删除数据:
	DELETE FROM 表名 [WHERE 条件];

案例演示:
-- 删除张三记录
delete from stu where name = '张三';

-- 删除stu表中所有的数据
delete from stu;

3.DQL

数据库进行查询的操作,根据不同使用场景进行选择不同查询方式

-- 完整语法
SELECT 
    字段列表
FROM 
    表名列表 
WHERE 
    条件列表
GROUP BY
    分组字段
HAVING
    分组后条件
ORDER BY
    排序字段
LIMIT
    分页限定

-- 数据准备
-- 删除stu表
drop table if exists stu;


-- 创建stu表
CREATE TABLE stu (
 id int, -- 编号
 name varchar(20), -- 姓名
 age int, -- 年龄
 sex varchar(5), -- 性别
 address varchar(100), -- 地址
 math double(5,2), -- 数学成绩
 english double(5,2), -- 英语成绩
 hire_date date -- 入学时间
);

-- 添加数据
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date) 
VALUES 
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

3.1基础查询

查询多个字段:
	SELECT 字段列表 FROM 表名;
	SELECT * FROM 表名; -- 查询所有数据

去除重复记录:
	SELECT DISTINCT 字段列表 FROM 表名;

起别名:
	AS: AS 也可以省略

案例练习:
-- 查询name,age两列
select name,age