MySQL基础学习笔记

一、基本概念

DB:database数据库,存储一系列有组织数据的容器。
DBMS:Database Management System数据库管理系统,负责管理和维护DB。
SQL:Structure Query Language结构化查询语言,程序员用于与DBMS沟通的语言。

二、MySQL优点
  1. 体积小、安装方便
  2. 重点开源,不要钱
  3. 性能高,稳定性好
  4. 兼容性好

MySQL基本语法

DDL

1.库和表的管理

库的管理:
创建库 create database 库名;
删除库 drop database 库名;

表的管理:
create table if not exists stuinfo(
​ stuid int,
​ stuName varchar(15),
​ gender char,
​ bornDate datetime
);
desc stuinfo;//查看表结构

修改表
语法:alter table 表名 add|modify|drop|change column 字段名 【字段类型】;
①修改字段名
alter table stuinfo change colunm name(原名称)gender(修改后名称)sex char;
②修改表名
alter table stuinfo rename to studentinfo;
③修改字段类型
alter table stuinfo modify column bornDate Date;
④添加字段
alter table stuinfo add colunm email varchar(20) ;
⑤删除字段
alter table stuinfo drop colunm email;
删除表
drop table if exists stuinfo;

2.常见数据类型介绍

主要包括以下五大类:
整数类型:BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型:FLOAT、DOUBLE、DECIMAL(更精准,一般用于金额)
字符串类型:CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型:Date、DateTime、TimeStamp、Time、Year
其他数据类型:BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

3.常见约束

①NOT NULL :非空,用于保证该字段的值不能为空。例如学生表的学生姓名及学号等等。
②DEFAULT:默认值,用于保证该字段有默认值。例如学生表的学生性别
③PRIMARY KEY:主键,用于保证该字段的值具有唯一性并且非空。例如学生表的学生学号等。
④UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空。例如注册用户的手机号,身份证号等。
⑤CHECK:检查约束(MySql不支持),检查字段的值是否为指定的值。
⑥FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值,在从表添加外键约束,用于引用主表中某些的值。例如学生表的专业编号

DML

1.插入数据

insert into 表名(字段名,…) values(值1,…);
特点:
①字段类型和值类型一致或兼容,而且一一对应
②可以为空的字段,可以不用插入值,或用null填充
③不可以为空的字段,必须插入值
④字段个数和值的个数必须一致
⑤字段可以省略;默认所有字段,并且顺序和表中的存储顺序一致

2.修改数据

修改单表:
update 表名 set 字段=新值,字段=新值 where 条件;
修改多表:
update 表1 别名1,表2 别名2 set 字段=新值,字段=新值 where 连接条件 and 筛选条件

3.删除数据

方式一:delete
单表删除:
delete from 表名 where 筛选条件
多表删除
delete 别名1,别名2 from 表1 别名1,表2 别名2 where 连接条件 and 筛选条件;

方式二:truncate
truncate table 表名;

delete和truncate的区别?
1.truncate不能加where条件,而delete可以加where条件
2.truncate的效率高一些
3.truncate 删除带自增长的列的表数据后,如果再插入数据,数据从1开始;
delete删除带自增长列的表后,如果再插入数据,数据从上一次的断点处开始
4.truncate删除不能回滚,delete删除可以回滚

##DQL

####1.基本查询

语法: select 查询列表 from 表名;
特点:
①查询结果集是一个虚拟表
②查询列表可以是单个字段、多个字段、常量、表达式、函数,也可以是以上的任意组合
引申1:起别名
select 字段名 as “别名” from 表名;
select 字段名 ”别名“ from 表名;
引申2:+的作用
作用:加法运算

  • 如果两个操作数都是数值型,则直接进行加法运算
  • 如果其中一个为非数值型,则将强制装换成数值型,如果转换失败,则当做0处理
    ’123‘+4===》127
    ‘abc’+4===》4
  • 如果其中一个为NULL,则结构直接为NULL
    引申3:去重
    select distinct department_id from employees;
    引申4:内置函数
    select version(); 查看数据库版本
    select databese();查询当前所使用的数据库
    select user();//查询当前挡路用户名
    select ifnull(字段名,表达式); //SELECT IFNULL(NULL,“11”); -> 11 SELECT IFNULL(“00”,“11”); -> 00
    select concat(字符1,字符2,字符3);//拼接多个列
    select length(字符、字段);获取字节长度
2.条件查询

语法:
select 查询列表 from 表名 where 筛选条件;
筛选条件分类:
①按条件表达式筛选:
关系运算符:>= < <= > <> =
②按逻辑表达式筛选
逻辑运算符:and or not
③模糊查询
like:一般和通配符搭配使用
_ 任意单个字符
​ % 任意多个字符
between and:一般用于判断某字段是否在指定的区间
​ a between 10 and 100
in: 一般用于判断某字段是否在指定的列表
​ a in(10,30,50)
is null:判断是否null值

3.排序查询

语法:
select 查询列表 from 表名 where 筛选条件 order by 排序列表;
特点:
①排序列表可以是 单个字段、多个字段、函数、表达式、别名、列的索引,以及以上的组合
②升序,通过asc;降序,通过desc

4.常见函数

SQL中的函数分为单行函数和分组函数
语法: select 函数名(实参列表);

  1. 字符函数
    concat(str1,str2,…);拼接字符
    substr(str,pos);截取从pos开始的所有字符,起始索引从1开始
    substr(str,pos,len);截取len个从pos开始的字符,起始索引从1开始
    length(str);获取字节个数
    char_length(str);获取字符个数
    upper(str);变大写
    lower(str);变小写
    trim(【substr from】str);去除指定字符,默认是空格
    left(str,len);从左边截取制定len个数的字符
    right(str,len);从右边截取制定len个数的字符
    lpad(str,substr,len);左填充
    rpad(str,substr,len);右填充
    strcmp(str1,str2);比较两个字符的大小
    instr(str,substr);获取substr在str中第一次出现的索引

2.数学函数
ceil(x);向上取整
floor(x);向下取整
round(x,d);四舍五入,四舍五入返回D位小数
mod(x,y);取模/取余
truncate(x,d);截断,保留小数点后d位
abs(x);求绝对值

3.日期函数
now();获取当前日期–时间
curtime();获取时间
curdate();获取日期
date_format(date,格式);格式日期为字符
str_to_date(str,格式);将字符转成日期
datediff(date1,date2);获取两个日期之间的天数差
year(date);
month(date);

4.流程控制函数
①if(条件,表达式1,表达式2):如果条件成立,返回表达式1,否则返回表达式2
②case 表达式
when 值1 then 结果1
when 值2 then 结果2

else 结果n
end

③case
when 条件1 then 结果1
when 条件2 then 结果2

else 结果n
end

5.分组函数

sum求和
avg平均
max最大
min最小
count个数

特点:

  1. 实参的字段的类型,sum和avg只支持数值型,其他三个可以支持任意类型
  2. 这五个函数都忽略null值
  3. count可以支持一下参数
    count(字段):查询该字段非空值的个数
    count(*):查询结果集的行数
    count(1):查询结果集的行数
  4. 分组函数可以和distinct搭配使用,实现去重的统计
    select count(distinct 字段) from 表名;
6.分组查询

语法:
select 分组函数,分组的字段 from 表名 where 分组前的筛选条件 group by 分组列表 having 分组后的筛选条件 order by 排序列表;
特点:
①分组列表可以是单个字段
②筛选条件分为两类:

筛选的基表使用的关键字位置
分组前筛选原始表wheregroup by之前
分组后筛选分组后的结果集havinggroup by之后
7.连接查询

当查询中设计到多个字段,则需要通过多表连接
笛卡尔乘积:
​ 出现原因:没有有效的链接条件
​ 解决办法:添加有效的链接条件

--------------------------SQL92语法------------------------
语法:
select 查询列表 ①
from 表1 别名,表2 别名,… ②
where 连接条件 ③
and 筛选条件 ④
group by 分组列表 ⑤
having 分组后筛选 ⑥
order by 排序列表;⑦
执行顺序:②③④⑤⑥①⑦

--------------------------SQL99语法------------------------

1.内连接

语法:

select 查询列表 ①

from 表1 别名 ②

【inner】join 表2 别名 on 连接条件 ③

【inner】join 表2 别名 on 连接条件

where 筛选条件④

goup by 分组列表⑤

having 分组后的筛选⑥

order by 排序列表⑦

执行顺序:②③④⑤⑥①⑦

8.子查询

一条查询语句中又嵌套了另一条完整的select语句,其中被嵌套的select语句,称为子查询或内查询;在外面的查询语句,称为主查询或外查询

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面,但一般放在条件的右侧
3、子查询优先于主查询执行,主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类:
① 单行子查询
	结果集只有一行
	一般搭配单行操作符使用:> < = <> >= <= 
	非法使用子查询的情况:
	a、子查询的结果为一组值
	b、子查询的结果为空
	
② 多行子查询
	结果集有多行
	一般搭配多行操作符使用:any、all、in、not in
	in: 属于子查询结果中的任意一个就行
	any和all往往可以用其他查询代替
9.分页查询

语法:

select 字段 from 表名 where 条件 group by 分组字段 having 条件 order by 排序字段 limit 【起始的条目索引,】条目数;

特点:

①起始条目索引从0开始

②limit子句放在查询语句之后

③select * from 表 limit (page-1)*sizePerPage,sizePerPage
假如:每页显示条目数sizePerPage,要显示的页数 page

10.union联合查询

语法:

select 字段|常量|表达式|函数 from 表 【where 条件】 union 【all】
select 字段|常量|表达式|函数 from 表 【where 条件】 union 【all】
select 字段|常量|表达式|函数 from 表 【where 条件】 union 【all】

select 字段|常量|表达式|函数 from 表 【where 条件】

特点:

①多条查询语句的查询的列数必须是一致的

②多条查询语句的查询的列的类型几乎相同

③union代表去重,union all代表不去重

数据库事务

**定义:**数据库事务是构成单一逻辑工作单元的操作集合。

特点:

①原子性:要么都执行,要么都回滚

②一致性:保证数据的状态操作前和操作后保持一致

③隔离性:多个事务同时操作相同数据库的同一个数据时,一个事务的执行不受另外一个事务的干扰

④持久性:一个事务一旦提交,则数据将持久化到本地,除非其他事务对其进行修改

相关步骤:

1、开启事务
2、编写事务的一组逻辑操作单元(多条sql语句)
3、提交事务或回滚事务
事务分类:

隐式事务,没有明显的开启和结束事务的标志,比如

insert、update、delete语句本身就是一个事务

显式事务,具有明显的开启和结束事务的标志

set autocommit=0;
start transaction;
commit;
rollback;
事务的隔离级别

隔离级别一共有四种:读未提交、读已提交、可重复读、串行化。这四种隔离级别分别可以解决的不同的问题,下面先描述一下不使用隔离级别可能会出现的几种问题。

​ (1)丢失修改

​ A和B两个事物同事修改同一个数据,A修改的提交在B提交之后,导致B好像没有修改,丢失修改。

​ (2)脏读

​ B事务修改了一个数据并未提交,A事物读取了这个数据,然后B事务回滚了,最后A又读取了一次,两次读取的数据不一致,称为脏读。

​ (3)不可重复读

​ A事务读取了一个数据后,B事务修改了这个数据,A事务又读取了这个数据,两次读取的数据也不一致,称为不可重复读。

​ (4)幻读

​ A事务更新了某个字段(范围是整个数据表的)(以id=1为条件的),B事务又插入了一条新的记录,导致A事务认为自己没有完全更新过来,就像出现幻觉一样。

​ 针对这几种错误分别设置不同的隔离级别来解决:

​ 第一种丢失修改一般使用加锁锁来解决,因此串行化可以解决,并且串行化可以解决上面出现的所有问题。

​ 第二种问题脏读是因为读取其他事物未提交的数据,因为设置读已提交隔离级别可以解决这个问题。但不可解决不可重复读和幻读的问题。

​ 第三种问题不可能重复读,是因为B事物的修改影响了A事务的读取数据,设置可重复读隔离级别,使得B事务修改数据和A事务读取数据互不影响,隔离开来,从而解决这个问题,同时解决 脏读问题。

​ 第四种幻读问题,是因为A事务更新完数据后,B事务又插入了新的数据,设置串行化隔离级别可解决,并且这种隔离级别解决上面所有的问题。除了串行化,多版本并发控制(MVCC,Multiversion Concurrency Control)机制也可以解决该问题。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值