Mysql数据库学习笔记[已暂停更新，一个月后继续]

MariaDB学习笔记

• • 一、数据库
  • • 1.概述
    • 2.Mysql数据库
    • 3.数据的结构
    • • (1). 结构
      • (2).命名规则
  • 二、SQL语言
  • 1.定义
  • 2.分类
  • 3.语句
  • • 数据库操作
    • 数据表操作
    • 字段约束
    • • 主键约束
      • 非空约束 not null
      • 唯一约束 unique
      • 默认约束
      • 外建约束
      • 检查约束
    • 常见函数
    • 条件查询
    • 统计实例
    • 聚合函数aggregation
    • 语句的执行顺序
    • 序号
  • 事务(Transaction)
  • • • 四个特性
      • 隔离级别
      • 读未提交(Read uncommitted)
      • 读已提交(Read committed)
      • 可重复读(repeatable read)
      • 串行化(Serializable)
      • • Mysql手动开启、提交和滚事务

一、数据库 1.概述

• 数据库（database ,简称db）就是用来存储数据和管理数据的仓库
  • 分类：
    • 关系型数据库：指存放的数据之间是有紧密关系的
      • 常见的有：Oracle、MySQL、SQLServer、Access
    • 非关系型数据库：指存放的数据之间关系松散
      • 常见的有：MongoDB、Redis、Solr、ElasticSearch、Hive、Hbase

2.Mysql数据库

• 服务端：负责实际的数据的增删改查的操作的。
  • 默认端口号是3306
  • 如果需要存储中文建议指定utf8或gbk这样支持中文的字符集
• 客户端：连接服务器，对数据进行增删改查的交互的界面
  • 学习时可以使用MariaDB 百度介绍
  • DOS命令提示符（win：开始-支行-cmd）
    • mysql -u root -p 回车后，待Enter password:提示出现后输入密码即可
    • 或直接输入mysql -uroot -proot 此种方式是-u后面直接跟用户名，没有空格，同样的-p后面直接跟密码
  • 可视化工具
    • SQLyog
    • Navicat
    • MysqlFront

3.数据的结构 (1). 结构

数据库 -> 表 -> 记录
数据库：对库的操作
数据表：维护表的结构，如包含哪些列、叫什么名、什么类型、多大、各种结束等
记录：按表的结构来增删改查数据

(2).命名规则 二、SQL语言 1.定义

• 结构化查询语言（Structure Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据、管理数据库及表；同时也是数据库脚本的扩展名。
• SQL 是1986.10月由美国国家标准局ANSI通过的数据库语言美国标准，接着国际标准化组织ISO公布了SQL正式国际标准

2.分类

DML(Data Manipulation Language) 数据操纵语言，如insert update delete select
DDL(Data Definition Language) 数据库定义语言，如grant deny revoke等，只有管理员才有相应的权限的
DCL(Data Query Language) 数据库查询语言，如create table

3.语句 数据库操作

show databases; 查看数据库
create database testdb default character set utf8 创建数据库 database后面跟要创建的数据库的名字，后面四个单词是用来指定数据库默认编码为utf8，也可以指定其他编码
show variables like "%char%"; 查看整个数据库的所有编码相关信息
show create database testdb; 查看指定数据库的编码
ALTER DATAbase testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 修改指定数据库的编码
alter table [数据库名称.表名称] character set utf8; 修改指定数据表的编码
alter table [表名称] change [字段名称] [字段名称] [类型] character set utf8; 修改指定数据表中字段的编码
drop database testdb 删除数据库testdb

数据表操作

show tables; 查看当前数据库中的所有的表

desc testdb.ta 查看指定表的结构

drop table ta 删除指定表
创建数据表

 CREATE TABLE `users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(64) NULL DEFAULT NULL,
    `password` VARCHAR(64) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) default COLLATE='utf8_general_ci'

• 添加列

alter table testb add column money numeric(7,2);

• 修改表名
  rename table 原表名 to 新表名;

• 插入数据

insert into users values(1,"张三","123"); --按原表里面的各列的顺序和数量来插入时，可以使用此种简写
insert into users(name,password) values("李四","23");--插入的列的数量或是顺序与表格不一致时，可以手动指定要插入的列的名称与顺序
set names gbk;--如果插入数据时，报中文字段值太长如"ERROR(22001):Data too long for column 'loc' at row 1"这个报错，就可以临时使用这个命令来临时解决

• 删除数据

delete from users where id = 1;--删除users表中id=1的记录，如果不写where条件，则整个表中所有的数据全部被删掉

字段约束 主键约束

主键列用于区分每条记录，因此需要整表唯一且不能为空

CREATE TABLE `users` (
    `id` INT(11)  PRIVMARY KEY,
    `name` VARCHAR(64) NULL DEFAULT NULL 
) 

AUTO_INCREMENT用于修饰int型的字段的值自动递增

CREATE TABLE `users` (
    `id` INT(11) PRIVMARY KEY  AUTO_INCREMENT,
    `name` VARCHAR(64) NULL
)

非空约束 not null

该字段不能为null，其他任意值都可以

唯一约束 unique

该字段的值必须整个表内唯一，不可重复，比如身份证号、手机号，每个人的都不能一样

默认约束

• 设置该字段的默认值

create table e(id int primary key auto_increment,sex varchar(10) default '男')

外建约束

创建外键约束后，被指向的表格称为主表，子表在新增记录时，外键字段的值必须是主表里面对应的键的列中已经存在了的，否则将无法新增成功。同样的，主表在想要删除一条记录时，也必须保证子表中没有任何一条记录与此记录相关，才可以删除。

CREATE TABLE t_user(id INT PRIMARY KEY AUTO_INCREMENT,t_name VARCHAR(20));
CREATE TABLE t_orders(id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,detail_id INT,FOREIGN KEY (user_id) REFERENCES t_user(id));

检查约束

很少使用，了解即可，录入age超过200将报错

DROp TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键 
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
sex CHAR(2) DEFAULT '男', #默认值
phone CHAR(18),
age INT,
CHECK (age>0 AND age<=200),
createdTime DATE DEFAULT NOW()
);
DESC tb_user;

常见函数

• upper/lower 作用到列上，对某列使用此函数后，该列查询出来的结果将会全部转成大写/小写

select upper(dname) from dept;--查询所有的部门名称，并将部门名称里面的值转成大写
select lower(dname) from dept;--查询所有的部门名称，并将部门名称里面的值转成小写

• length 作用到列上，计算查询结果的长度，注意：返回值是字节长度，比如在utf-8编码下，一个汉字占3个字节，因此“Java开发部”这个的长度就是13

select length(dname) from dept;--查询所有的部门名称，但只显示其字节数

• substr(string,start,length) 截取字符串，从第start个位置开始，截取length个字符，这里是按字符，一个字母或一个中文都是一个字符，不够的就有多少显示多少

select substr(dname,2,3);--只显示部门名称从第2个位置开始，共显示3个字符

• concat(str1,str2,…) 拼接所有给定的字符串

select ename,concat(ename,123) from emp;--在每个ename的值的后面拼接上字符串123

• group_concat(column_name order by column_name desc separator “|”) 聚合字符串，参考原连接

SELECt * FROM `wms_sku`;

SELECt order_id,group_concat(sku_name ORDER BY sku_name DESC SEPARATOR "|") AS skus FROM wms_sku GROUP BY order_id;

结果如下：

• replace 替换文本

select dname,replace(dname,'j','Mr j') X from dept;--把j字符替换成Mr j

• ifnull(column_name,value) 如果第一个参数里面的值是null，将替换成value的值

select ifnull(comm,3.13) as com from emp;--把comm列值是null的替换成3.13后显示

• round() 四舍五入取整

select round(comm) from emp;--对薪水四舍五入处理

• ceil() 向上取整

select ceil(comm) from emp;--对薪水向上取整

• floor() 向下取整

select floor(comm) from emp;-- 对薪水向下取整

• now() 取当前yyyy-MM-dd HH:mm:ss的时间

select now(),year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now()); -- 分别获取当前时间、年、月、日、时、分秒

• 转义字符 反斜线

select "a'b";

• dinstinct 去除当前列的重复项

SELECT DISTINCT loc FROM dept;-- 查询所有的部门，总共有几个办公地点

条件查询

• where 条件子句，注意：不可以使用别名、聚合函数 参考连接

select * from emp

select * from emp where 1=1 --类似没条件

select * from emp where 1=0 --条件不成立

select * from emp where empno=100 --唯一条件

select * from emp where ename='tony' and deptno=2 --相当于两个条件的&关系

select * from emp where ename='tony' or deptno=1 --相当于两个条件的|关系

select name, sal from emp where sal=1400 or sal=1600 or sal=1800;

-- 或

select name, sal from emp where sal in(1400,1600,1800);

select name, sal from emp where sal not in(1400,1600,1800);

• like 模糊查询 通配符%代表0到n个字符，通配符下划线_代表1个字符

select * from emp where ename like 'l%' --以l开头的

select * from emp where ename like '%a' --以a结束的

select * from emp where ename like '%a%' --中间包含a的

select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置

• null 筛选记录的某个值是否为null。注意：此处不使用=来比较，而是使用is。比如要查奖金为null的需要使用select * from emp where mgr is null 而不是select * from emp where mgr = null

select * from emp where mgr is null --过滤字段值为空的

select * from emp where mgr is not null --过滤字段值不为空的

• between value1 and value2 值在value1(含)与value2(含)之间的

select * from emp where sal between 5000 and 10000;

• limit([start,]length) 限制返回结果的条数，从start+1条记录开始，展示length条记录

select * from emp limit 2;

• order by 指定排序依据的列，asc升序(默认), desc 降序 ，如果排序的是汉字，则会按汉字对应的在UTF-8字符集里面的顺序来排序

select * from emp order by sal asc,mgr desc;

统计实例

• 统计员工的年薪

select ename,sal,ifnull(sal,0)*13 as 年薪,comm,ifnull(comm,0)*13 as 年奖金 from emp;

聚合函数aggregation

把一列所有的值聚合起来，然后做数据分析

• count
  count(*) 与 count(1) 效果一致，后者更高效一些。count(sal)这种是统计不为null的数量
• max/min 求最大值 、最小值
• sum 求合
• avg 求平均数
• group by 当查询语句里面同时有聚合列和非聚合列时，需要使用group by 指定非聚合列是哪些。
  比如要统计各部门的人数这个问题，是在查询各部门人数的同时还要显示部门名称 ，这个时候就要group by 部门名称，这样每个部门名称都会有一行。注意：group by是可以使用列的虽名的。

SELECt YEAR(hiredate) AS ye ,COUNT(1) FROM emp GROUP BY YEAR(hiredate);

• having 对查询出来的结果做最后的筛选，注意：having也是可以使用别名的

SELECt deptno,MAX(sal) FROM emp GROUP BY deptno HAVINg MAX(sal)>10000; 

语句的执行顺序

from 
join 
on 
where 
group by(开始使用select中的别名，后面的语句中都可以使用)
 avg,sum.... 
having 
select 
distinct 
order by
limit 

序号

@r :=@r + 1 as alias放在select 里面，然后在from的表的最后面加,(select @r:=0) as alias

#每个商品对应了哪些订单
SELECT tc.skuoid,GROUP_CONCAT(tc.m,"(",tc.skuCount,")" ORDER BY tc.m SEPARATOR ";") FROM (
SELECt ta.m,ta.packageno,tb.skuoid,tb.skuCount FROM (
SELECt @i :=@i+1 AS m,t.*FROM damaiorder AS t,(
SELECt @i :=0) AS ida WHERe ownercode='hanpeng' ORDER BY date DESC LIMIT 10) AS ta LEFT JOIN damaiorderdetails AS tb ON ta.id=tb.phid) AS tc GROUP BY tc.skuoid ORDER BY skuoid;
SELECT (@i :=@i+1) AS m,*FROM em;

事务(Transaction)

是指作为单个逻辑工作单元的一系列操作，要么全部执行成功，要么全部失败，都不执行。

四个特性

可参考此文章来记忆

• 原子性：把多个操作看作一个原子，紧密联系不可分割
• 隔离性：数据库为了保证性能，也支持高并发，但是有安全隐患。保证多个操作之间是隔离的，相互之间不影响
• 持久性：对数据库的操作是持久生效的
• 一致性：

隔离级别 读未提交(Read uncommitted)

性能最好，但是安全性最差
个人理解，就是指一个事务在查询数据时，会受到其他尚未提交的事务已经“执行”的修改的影响。例如，事务A第一次查询后并未提交，但此时事务B开始了，然后执行了数据的插入，但是未提交，然后此时事务A又执行了一次查询，那么在当前级别下还没有被事务B提交的插入的数据，也会被查询出来。

读已提交(Read committed)

Oracle默认级别
个人理解，可以对比着读未提交来理解，就是只能查询到其他已经提交的数据，尚未提交的数据不会。

可重复读(repeatable read)

个人理解，事务开始后，查询到的数据会被直接生成一个快照（至于这个快照的粒度有多大就不清楚了，具说是MVCC），当前事务提交前再次执行同样的查询时，还是从这个快照上查询，因此也就实现了当前事务内，重复查询时数据一致。

串行化(Serializable)

个人理解：表级锁，一个表同一时间只能被一个事务所访问

Mysql手动开启、提交和滚事务

默认情况，mysql每一条mysql语句都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

start transaction; --开启事务
committed; --提交事务
rollback; --回滚事务