mysql基础篇

Mysql

net start mysql80

net stop mysql80

启动或者停止mysql服务,mysql开机默认自动启动

客户端连接

方式一,直接启动mysql的命令行客户端

方式二, 系统自带的命令行 mysql [-h 127.0.0.1] [-P 3306] -u root -p

配置完环境变量后只需要在cmd里面输入mysql -u root -p

关系型数据库:

建立在关系模型的基础上,由多张相互连接的二维表组成的数据库

通过表形式来建立的数据库叫做关系型数据库

SQL

SQL语句可以单行或者多行书写,以分行结尾.

可以通过空格和缩进来增加语句的可读性.mysql的SQL语句不分大小写,关键字建议大写.

注释:单行注释 --或者 # (mysql特有)

多行注释 /* 内容 */

分类:

DDL Data Definition language 数据定义语言,用来定义数据库对象

DML Data Manipulation Language 数据操作语言,用来对数据库的数据进行增删改

DQL Data Query Language 数据查询语言,用来查询数据库中表的记录

DCL Data Control Language 数据控制语言,用来创建数据库用户,控制数据库的访问权限

DDL

查询

查询所有数据库 SHOW DATABASES;

查询当前数据库 SELECT DATEBASE();

创建 CREAT DATABASE [IF NOT EXITS] 数据库名 [DEFAUL CHARSET 字符集] [COLLATE 排序规则];

[IF NOT EXITS]如果这个数据库不存在就创建,如果存在就不执行任何操作

[]里面的内容都是可选的

删除 DROP DATABASE [IF EXITS] 数据库名;

使用 USE 数据库名;

DDL - 表操作 - 查询

​ 查询当前数据库所有表: show tables;

​ 查询表结果:desc 表名;

​ 查询指定表的建表语句:show create table 表名;

DDL - 表操作 - 创建

​ create table 表名(

​ 字段1 字段1类型[comment 字段注释1],

​ 字段2 字段2类型[comment 字段注释2]

)[comment 表注释];

[]中为可选选项,最后一个字段后面没有","

字符串verchar()括号里面控制字符串长度

数值类型

tinyint,smallint,mediumint,int,bigint,float,double,decimal(得指定精度和标度,精度就是它的总长度,标度就是它的小数位)

double(4,1) 4表示总体长度,1表示小数位数

字符串类型

char,verchar,tinyblob,tinytext,blob,text,longblob,longtext.

char是定长字符串,verchar可变字符串,char(10)字符串长度一定是10,不够长补空格到10.

verchar(10)字符串会进行判断来决定它真正的长度.

时间日期类型

time,date,year,datetime

DDL - 表操作- 修改

​ 添加字段

​ ALTER TABLE 表名 ADD 字段名 类型(长度) [comment 注释] [约束];

​ 修改数据类型

​ alter table 表名 modify 字段名 新数据类型(长度);

​ 修改字段名和字段类型

​ alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

​ 删除字段

​ alter table 表名 drop 字段名

​ 修改表名

​ alter table 表名 rename to 新表名

​ 删除表

​ drop table [if exits] 表名;

​ 删除指定表,并重新创建该表

​ truncate table 表名;

Mysq图形化界面

DML

增加insert 修改update 删除delete

给指定字段添加数据

insert into 表名 (字段1,字段2) values(值1,值2);

给全部字段添加数据

insert into 表名 values(值1,值2…);

批量添加数据

insert into 表名(字段1,字段2…) values(值1,值2…)(值1,值2…)

修改数据

update 表名 set 字段名1=值1,字段名2=值2,…[where 条件];

删除数据

delete from 表名 [where 条件];

DQL

select

​ 字段列表

from

​ 表名列表

where

​ 条件列表

group by

​ 分组字段列表

having

​ 分组后字段 列表

order by

​ 排序字段列表

limit

​ 分页参数

查询多个字段

select 字段1,字段2… from 表名

select * from 表名

设置别名

select 字段1[as 别名 1],字段2[as 别名2]…from 表名;as可以省略

去除重复记录

select distinct 字段列表 from 表名;

DQL- 条件查询

select 字段列表 from 表名 where 条件列表

条件>,>=,<,<=,=,!=,between…and,in(…)like 占位符,is null,and &&,or ||,not !.

like 占位符(_匹配单个字符,%匹配任意个字符) 比如说两个字的

like ‘__’;

DQL-聚合函数

count,max,min,avg,sum;作用于某一列数据

select 聚合函数(字段列表) from 表名;

null值不参与聚合函数的运算

DQL-分组查询

select 字段列表 from 表名[where 条件] group by 分组字段名 [having 分组后过滤条件]

where在分组前进行操作,having在分组后进行操作且having可以使用聚合函数

执行顺序where > 聚合函数 >having

DQL-排序查询

select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;

asc:默认的升序排序

dese:降序排序

DQL-分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数;

起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数

数据库中分页查询可能不同

从第一页开始查询起始索引可以省略

DQL-执行顺序

先执行form再执行where在执行group by 和having 接着才是select 再是group by 最后limit

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-acH5fhJr-1690688689754)(C:\Users\念天\AppData\Roaming\Typora\typora-user-images\image-20230726180543263.png)]

DCL

控制用户访问和用户权限

查询用户

use mysql

select * from user;

用户都是存储再mysql里面的user里面

创建用户

create user ‘用户名’@‘主机名’ identified by 密码;

主机名用’%'表示可以在任意主机上访问

修改密码

alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’;

删除用户

drop user’用户名’@‘主机名’

再mysql里面只有通过用户名和主机名一起才能精准定位

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1n1PuKXn-1690688689755)(C:\Users\念天\AppData\Roaming\Typora\typora-user-images\image-20230726185046797.png)]

查询权限

show grants for ‘用户名’@‘主机名’

授予权限

grant 权限列表 on 数据苦名.表名 to ‘用户名’@‘主机名’

撤销权限

revoke 权限列表 on 数据苦名.表名 from ‘用户名’@‘主机名’;

多个权限间用,间隔

函数

一段可以直接被另一段程序调用的程序或代码

字符串函数

concat(s1,s2…sn) 字符串拼接,把s1,s2…sn拼接成一个字符串

lower(str) 把字符串str全部转成小写

upper(str) 把字符串str全部转为大写

lpad(str,n,pad) 左填充,用字符串pad对str的左边进行填充,使得str达到n个字符串长度

rpad(str,n,pad)右填充,用字符串pad对str的右边进行填充,使得str达到n个字符串长度

trim(str) 去掉字符串头部和尾部的空格

substring(str,start,len) 返回从字符串str从start位置起len长度的字符串

select 函数(参数);

数值函数

ceil(x) 向上取整

floor(x) 向下取整

mod(x,y) 返回x%y的值

rand() 得到0~1之间的随机数

round(x,y) 得到对x进行四舍五入,保留y个小数

日期函数

curdate() 返回当前日期

curtime() 返回当前时间

now()返回当前日期和时间

year(date) 获取指定date的年份

month(date) 获取指定date的月份

day(date) 获取指定date的日期

date_add(date,interval expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值

interval是固定的 expr是时间值,type是时间的单位

datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数,时间用字符串格式输入.

流程函数

用来条件筛选,提高语句的效率

if(vale,t,f) 如果value 为true 则返回t,否则返回f

ifnull(value1,value2) 如果value1不为空,则返回value1,否则返回value2

case when [val1] then [res1] …else [ default] end

val1为true 返回res1…否则返回default默认值,结尾end别忘记了

case [expr] when [val1] then [res1] …else [default] end

如果expr的值等于val1,返回res1…否则返回default默认值

select name,(case workadress when ‘北京’ then ‘一线城市’ when ‘上海’ then ‘一线城市’ else ‘二线城市’ end ) as ‘工作地址’

表头显示name 和工作地址,as可以省略

约束

约束是作用于表中字段上的规则,用于限制存储在表中的数据

目的是为保证数据库中数据的正确,有效性和完整性

非空约束 限制该字段的数据不为null not null

唯一约束 保证该字段的所有数据都是唯一的 unique

主键约束 主键是一行数据的标识,要求非空且唯一 primary key

默认约束 保存数据时,如果未指定该字段的值,则采用默认值 default

检测约束 保证字段值满足某一个或者多个条件 check

外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 foreign key

约束是作用于表中字段上的,可以在建表/修改表的时候添加约束

自动增长 auto_increment

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J0qBLhLg-1690688689755)(C:\Users\念天\AppData\Roaming\Typora\typora-user-images\image-20230727160958596.png)]

create table user(

​ id int primary key auto_increment comment ‘主键’,

​ name verchar(50) not null unique comment’姓名’;

​ age int check(age>0 && age<= 120) comment’年龄’;

​ status char(1) default ‘1’ comment ‘状态’;

​ gender char(1) comment’性别’;

)comment ‘用户表’;

如果插入到数据没有进入表,但是又auto_increment 的话,自增部分依旧会自动增加,数据依旧能申请到对应得到自增数据;

外键约束

通过外键让两张表之间的数据进行拼接,具有外键的表成为子表,从表;外键关联的叫做父表,主表;

两张表在数据库层面没有任何关系

在表结构创建时

[constraint] [外键名] foreign key (外键字段) referebces 主表(主表列表)

在表创建完后

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名);

alter table 表名 drop foreign key 外键名称;

外键约束删除/更新行为

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z2QzNOWU-1690688689756)(C:\Users\念天\AppData\Roaming\Typora\typora-user-images\image-20230729143657040.png)]

not action 和restrict是默认的

要加入那些操作在语句后面加上 on update on delete
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名) on update cascade on delete cascade;

多表查询

一对一 在任意一方加入外键,关联另一方的主键,并设置外键为unique

多对多 创建一张中间表来存储两张表的关系

多表查询会出现多余的笛卡尔积,ab集合相乘得到所有的情况,所有多表查询得消除多余的无效笛卡尔积

连接查询

内连接

查询ab表交集部分

隐式内连接

select 字段列表 from 表1,表2 where 条件…;

显式内连接

select 字段列表 from 表1 [inner] join 表2 on 条件…;

如果给表起了别名,就不能再用表名去操作

外连接

左外连接查询,查询左表包括交集部分

select 字段列表 from 表1 left [outer] join 表2 on 条件…;

右外连接查询,查询右表包括交际部分

select 字段列表 from 表1 right [outer] join 表2 on 条件…;

自连接

当前表与自己的连接查询,自连接必须用表别名

select 字段列表 from 表a 别名a join 表a 别名b on 条件…;

select a.name b.name from emp a ,empb where a.managerid = b.id;

自连接一定要给自己起别名,可以用内连接也可以用外连接

联合查询

对于union 查询,就是把多次查询的结构合并起来,形成一个新的查询结果集;

select 字段列表 from a…

union [all]

select 字段列表 from b…;

联合查询加上all会重复两张表的重复部分,取消all就只会出现一次

也就是union all会重复,union会去重

对于多表查询的多张表的列数必须保持一直,字段类型也需要保持一致

子查询

sql语句中嵌套select语句,成为嵌套查询,又称子查询

select * from t1 where column1 = (select column 1 from t2);

子查询外部语句可以说insert/update/delete/select的任何一个

分为

标量子查询 子查询结果为单个值

常用操作符: = <> > >= < <=

列子查询 子查询结果为一列

常用操作符: in ,not in ,any ,some ,all

行子查询 子查询结果我一行

= <>(不等于) in ,not in 😭) =()

表子查询 子查询结果为多行多列

() in () ;

把查询结果当作一张表来起别名进行查询

根据子查询位置 分为where后,from后,select后

可以用reformat code格式化代码使其更美观

事务

是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,既这些操作要么同时成功,要么同时失败.

默认mysql是自动提交的.

可以设置事务提交方式为手动

select @@autocommit;

set @@autocommit=0;

执行完后不会直接提交事务

执行commit ; 操作才会把事务操作上传

如果事务执行错误,就可以通过

rollback;来回滚事务

开启事务 或者修改事务自动提交

start tansaction 或 begin

事务的四大特性:

原子性 : 事务是不可分割的最小操作单元,要么全部成功,要么全部失败

一致性 :事务完成时,必须使所有的数据保持一致状态

隔离性 :数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行

持久性 :事务一旦提交或者回滚,它对数据库中的数据的修改是持久的,数据存储在磁盘中

并发事务的问题

脏读 一个事务读取到另外一个事务还没提交的数据

不可重复读 一个事务先后读取同一条记录,两次读取的数据不同

幻读 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,右发现这行数据已经存在,好像出现了一个"幻影"

我查询的时候没有,但是我插入的时候是有的

事务的隔离级别(解决并发事务的问题)

隔离级别 脏读 不可重复读 幻读

read uncommit √ √ √

read committed x √ √

repeatable read(m默认) x x √

serializable x x x

√是会出现,x是解决了

select @@transaction_isolation;

set [session(会话级别) \ golbal] transaction ioslation level [四种级别]