学习记录：初识MySQL

目录

数据库：

MySQL：

常用字段类型 ：

3键4约束

常用sql命令：

        在cmd中操作数据库

        ddl语句：数据库定义语言

dml：数据库操作语言

dql：数据库查询语言

内置函数

聚合函数

常用关键字

编写顺序

执行顺序

多表复杂查询

事务

事务的四大特性

数据库的隔离级别及出现的现象

---

数据库：

        按照数据结构来组织存储，管理数据的仓库。

种类：网状数据库 ​ 层次结构数据库 ​ 关系型数据  非关系数据库

MySQL：

        关系型数据库的一种

常用字段类型 ：

        整型：int

        短整型：tinyint（-127-128）

        小数类型：float，double

        字符类型：char

        可变字符类型：varchar （根据输入内容自动调节长度）

        日期类型：date （年月日）

        日期时间类型：datatime（年月日时分秒）

        大文本类型：text

3键4约束

        主键：保证唯一，通常把id设为主键（主键只有一个）

        外键：A表中的某个字段依赖于B表中的主键（类型一致，被依赖的必须有 主键约束或唯一约束）

        联合主键 ：把两个字段连在一起，形成一个主键

        唯一索引：不允许重复

        非空约束：不允许为空

        自增约束：只能用于主键，int类型

        默认约束：设置默认值

常用sql命令：

        在cmd中操作数据库

        登录：mysql -uroot -p

        创建数据库：create database 数据库名;

        查看数据库：show databases;

        查看所有表：show tables;

        查看某张表：select * from * 表名;

        创建表：create  table  表名(字段名1  字段类型 comment  ‘字段名解释名，字段名2  字段类型 ，字段名3……’)；

        删除表：drop  table  表名；

        更新表：update   表名   set  字段名1=‘最新字段值1’，字段名2=‘最新字段值2’，字段名3=‘最新字段值3’，……；（说明针对所有字段数据进行更新）

        创建指定编码集：create database 数据库名 character set utf8;

        ddl语句：数据库定义语言

-- ddl语句：数据库定义语言
-- 建表;
CREATE TABLE tb_eml(
	id int (11) ,
	name VARCHAR(25),
	depId int(11),
	aslary FLOAT
);

-- 主键 PRIMARY KEY
create table tb_em2(
	id int (11) PRIMARY KEY
);

CREATE TABLE tb_em3(
	id int (11),
    PRIMARY KEY (id)
);

-- 联合主键
CREATE TABLE tb_em4(
	id int (11) ,
	name VARCHAR(25),
	PRIMARY KEY(id,name)
);

-- 外键
CREATE TABLE tb_em5(
	deptId int(11),
	CONSTRAINT fk_tb_em5 FOREIGN KEY(deptId) REFERENCES tb_em2(id)
  
);

-- 非空约束 NOT NULL
CREATE TABLE tb_em6(
	id int NOT NULL
);

-- 唯一索引 UNIQUE
CREATE TABLE tb_em7(
	name VARCHAR(25) UNIQUE
);

-- 默认约束 DEFAULT 30
CREATE TABLE tb_em8(
	deptId int DEFAULT 30
);

-- 自增约束 AUTO_INCREMENT
CREATE TABLE tb_em9(
	id int PRIMARY KEY AUTO_INCREMENT
);

-- 新建并复制表（不能复制数据）
CREATE TABLE 表名 LIKE 被复制的表名;

-- 修改表名
ALTER TABLE 表名 RENAME TO 修改后的表名;

-- 添加字段
ALTER TABLE 表名 add 字段名 数据类型;

-- 修改字段名和数据类型 
ALTER TABLE 表名 CHANGE 字段名 新字段名 新数据类型;

dml：数据库操作语言

-- dml：数据库操作语言
-- * 通配符 代表所有
SELECT * FROM student1

--  ` 漂号 标注，避免误认为关键字
SELECT * FROM  `name`

-- 增加语句
INSERT INTO student1 (id,name,score) VALUES(02,'清华',80);

-- DEFAULT ： 当字段 自增，有默认值，可以为null时，可以用DEFAULT占位
INSERT INTO student1 (id,name,score) VALUES(03,'北大',DEFAULT);

-- 省略字段名不写：自增，默认值，可以null时
INSERT INTO student1 (name) VALUES('哈工大');

-- 删除语句
DELETE FROM student1    -- 全部删除
DELETE FROM student1 WHERE id = 3    --删除id为3的数据
DELETE FROM student1 WHERE score > 70    --删除score大于70的数据
DELETE FROM student1 WHERE score is NOT NULL    --删除score不是空值的数据
DELETE FROM student1 WHERE id = 2 AND score = 60    --删除id为2 并且 score等于60的数据
DELETE FROM student1 WHERE id = 2 or score = 60    --删除id为2 或者 score等于60的数据

-- 修改
UPDATE student1 set name = '北大'    -- 全部修改
UPDATE student1 set name = '北大' where id = 3
UPDATE student1 set name = '北大' where name is NULL
UPDATE student1 set name = '北大' where id=3 and score=60
UPDATE student1 set name = '北大' where id=3 or name='清华' 

dql：数据库查询语言

-- 查询
SELECT name FROM student1

-- count(*) 条数
SELECT count(*) FROM student1

-- as 起别名 如果别名包含空格，则需要双引号或方括号
SELECT name AS '校名' FROM student1

-- 区间判断
SELECT * FROM `student1` WHERE id BETWEEN 2 AND 5     --（id >= 2 AND id <= 5）

-- 多个等值判断 
SELECT * FROM `student1` WHERE `name` in ('清华','北大') 
--（name = '清华' or name = '北大'）


-- 模糊查询  LIKE     %代表若干个字符（也可以为空）  _代表一个字符(不能为空)
SELECT * FROM `student1` WHERE `name` LIKE '%大%'

内置函数

--时间处理函数

-- 年
SELECT year(birthday) FROM tb_student

-- 月
SELECT month(birthday) FROM tb_student

-- 日
SELECT day(birthday) FROM tb_student


--数值处理函数

-- 返回绝对值 abs()
SELECT abs(score) FROM tb_student

-- 向上取整 ceil()  ceil(2.3) = 3   
SELECT ceil(score) FROM tb_student

-- 向下取整 floor()
SELECT floor(score) FROM tb_student

-- 四舍五入 round()
SELECT round(4.5)

-- 取模（取余）mod(5,3)  = 2
SELECT mod(5,3)

-- 随机数  rand（） 【0，1）
SELECT rand()


--字符串处理函数

-- 合并字符串  concat(str1,str2,...)

SELECT CONCAT(id,name) FROM tb_student
SELECT CONCAT('123','name')

-- 去除左右空格 trim()
SELECT trim('   12   ')

	-- 去除左空格
	SELECT ltrim('   12   ')

	-- 去除右空格
	SELECT rtrim('   12   ')

-- 截取字符串 substring('要截取的字符串'，从第几个字符开始，截取几个字符)
SELECT SUBSTRING ("I like java",2,3)

    -- 去除名字的最后一个字，名字有两个字的，有三个字的
SELECT SUBSTRING(	`name`,1,(LENGTH(name) - 3) / 3) FROM tb_student 

-- 返回字符串长度 LENGTH()
SELECT LENGTH ( name )
	

聚合函数

-- 返回条数 count（*）所有信息总条数 count（字段名）该字段信息条数
SELECT count(*) FROM tb_student

-- 计算平均值 avg（）
SELECT avg(age) FROM tb_student

-- 计算最大值 max()
SELECT max(age) FROM tb_student

-- 计算最小值 min()
SELECT min(age) FROM tb_student

-- 总和 sum()
SELECT sum(age) FROM tb_student

常用关键字

-- 去重 distinct()  只能单独使用
SELECT DISTINCT (nationality) 

-- 分组  group by + 字段名
select sum(qty),title_id from sales group by title_id

-- having 分组后查询
select sum(qty),title_id from sales group by title_id having `sum(qty)` > 80
-- 排序 多个字段排序，order by关键字只写一次，每个字段都要定义排序规则，不写默认为升序
-- 排序 order by (升序)
SELECT * FROM tb_student ORDER BY age 


-- 排序 order by  + 字段名 + desc (降序)
SELECT * FROM tb_student ORDER BY age  DESC

-- 排序 根据age降序，age相同 id 升序排序
SELECT * FROM tb_student ORDER BY age  DESC , id asc 

-- 排序 根据age降序，age相同 birthday 降序排序
SELECT * FROM tb_student ORDER BY age  DESC , birthday desc 

-- 限制显示数量
-- 只显示第一条
SELECT * FROM tb_student ORDER BY age  DESC limit 1

-- 从第二条开始显示，显示5条
SELECT * FROM tb_student ORDER BY age  DESC limit 1,5

编写顺序

-- 	编写顺序   
SELECT    
FROM      
WHERE     
GROUP BY  
HAVING   
ORDER BY  
LIMIT     

执行顺序

from      
on       
join      
where       
group by   
聚合函数
having    
select     
distinct   
order by   
limit	   

多表复杂查询

-- 内连接	inner join
-- 笛卡尔积现象 ：两表数据相乘   为避免，加上on
SELECT employees.emp_name ,department.com_name FROM employees 
INNER JOIN department 
ON employees.com_id = department.com_id

-- 外连接  
	-- 左外 返回两张表的交集数据以及左表的剩余数据
    SELECT * FROM student
    LEFT JOIN school
    on student.schID = school.id

	-- 右外 返回两张表的交集数据以及右表的剩余数据
    SELECT * FROM student
    RIGHT JOIN school
    on student.schID = school.id

    -- 全外连接 左外连接和右外连接查询字段相同
    SELECT * FROM student
    LEFT JOIN school
    on student.schID = school.id
    union
    SELECT * FROM student
    RIGHT JOIN school
    on student.schID = school.id

-- 多表连接
SELECT student.id,student.stuName,hhobby.hobby,school.schName
FROM student
INNER JOIN school
on student.schID = school.id
LEFT  JOIN hhobby
on hhobby.stu_id = student.id

-- 自连接  与嵌套连接相比速度更快
SELECT * FROM tb_student as a
INNER JOIN tb_student as b
on a.name = '郑新伟' and b.age > a.age

-- 嵌套连接
SELECT * FROM job WHERE position IN 
(SELECT position FROM job where name = '邱艳' )

-- 可以用虚拟表进行连接
SELECT * FROM student
INNER JOIN 
(SELECT * FROM school WHERE id > 2  ) AS b
ON student.schID = b.id

事务

• 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。例如转账操作

• 开启事务：start transaction;

• 回滚事务：rollback;

• 提交事务：commit;

事务的四大特性

• 原子性(atomicity)

  • 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响

• 一致性(consistency)

  • 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态

  • 拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性

• 隔离性(isolcation)

  • 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

• 持久性(durability)

  • 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

数据库的隔离级别及出现的现象

• read uncommitted(读未提交)

• read committed (读已提交)

• repeatable read (可重复读)

• serializable (串行化)

隔离级别	名称	出现脏读	出现不可重复读	出现幻读
read uncommitted	读未提交	是	是	是
read committed	读已提交	否	是	是	Oracle / SQLServer 默认级别
repeatable read	可重复读	否	否	是	MySQL 默认级别
serializable	串行化	否	否	否

问题	现象
脏读	是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读	是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读