MySQL完整版详解

mysql 文章 2022-07-20 10:06 0 全屏看文

一、数据库的操作

1.创建数据库

若在可视化软件上创建数据库,参考如下图

如果要创建的数据库不存在,则创建成功

create database if not exists westos;

2.删除数据库

drop database if exists westos;

3.使用数据库

use tmalldemodb;//tab键的上面,如果你的表名或者字段名是一个特殊字符,就需要带``

4.查看数据库

show databases;

5.清空当前命令行界面命令

clear;

6.创建表

  • auto_increment:自增

  • 字符串使用单引号括起来

  • 所有语句后面加逗号,英文的,最后一个不用加

  • primary key主键,一般一个表只有一个唯一的主键,且必须要有

  • engine=innodb:数据库表的引擎

mysql> create table if not exists student(    -> id int(4) not null auto_increment comment '学号',    -> name varchar(30) not null default '匿名' comment '姓名',    -> pwd varchar(20) not null default '123456' comment '密码',    -> sex varchar(2) not null default '女' comment '性别',    -> birthday datetime default null comment '出生日期',    -> address varchar(100) default null comment '家庭地址',    -> email varchar(50) default null  comment '邮箱',    -> primary key(id)    -> )engine=innodb default charset=utf8;

创键成功之后的图

7.常用命令

(1)查看创建数据库的语句

show create database 数据库名;

运行结果图

(2)查看表的创建语句

show create table 表名;

(3)显示表的结构

desc 表名;

8.数据表的类型

(1)数据库的引擎

innodb //默认使用myisam//早些年使用的
  • 事务:假设有两个SQL都执行,要么都成功,要么都失败,如果有一个成功一个失败,它就提交不上去

  • 数据行锁定:假设有两条SQL去查同一个表,他会把这个表先锁住,剩下的数据再查的时候就要排队等待

  • 外键约束:一张表连接到另一张表

  • 全文索引:比如在百度上根据你输入的关键词(在数据库叫字段)区搜索你想要的结果

myisam innodb
事务支持 不支持 支持
数据行锁定 不支持 (支持表锁) 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大,约为myisam的2倍

常规使用操作:

  • myisam 节约空间,速度较快

  • innodb安全性高,事务的处理,多表多用户操作

(2)在物理空间存在的位置

所有的数据库文件都存在data目录下 本质还是文件的存储!

9.修改和删除表的字段

(1)修改表名

alter table 旧表名 rename as 新表名;
alter table teacher rename as teacher1;

(2)增加表的字段

alter table 表名 add 字段名 列属性;
alter table teacher1 add age int(11);

(3)修改表的字段(重命名,修改约束)

①修改约束modify(不能重命名): alter table 表名 modify 字段名 新的列属性;
alter table teacher1 modify age varchar(11);


②字段重命名(既可以给字段重命名,又可以修改约束)
alter table 表名 change 旧字段名 新字段名 列属性;

alter table teacher1 change age age1 int(11);

(4)删除表的字段

alter table 表名 drop 字段名;
alter table teacher1 drop age1;

(5)删除表

如果要删除的表存在,则删除 drop table if exists 表名;
drop table if exists teacher1;


所有的创建和删除操作都尽量加上判断,以免报错

二、列的数据类型详解

1.数值

  • tinyint    十分小的数据  1个字节

  • smallint   较小的数据 2个字节

  • int           标准的整数 4个字节      常用

  • bigint      较大的数据  8个字节

  • float         浮点数        4个字节

  • double     浮点数         8个字节

  • decimal      字符串形式的浮点数

2.字符串

  • char       字符串固定的大小       0-255

  • varchar       可变字符串      0-65535      常用

  • tinytext        微型文本       2^8-1

  • text        保存大文本       2^16-1

3.时间和日期

  • date       日期格式:YYYY-MM-DD

  • time       时间格式:HH:mm:ss

  • datetime       日期格式:YYYY-MM-DD      HH:mm:ss      常用

  • timestamp      时间戳,从1970.1.1至现在的毫秒数     常用

  • year      年份表示

4.null

  • 没有值,未知

  • 注意,不要使用NULL进行运算,结果为NULL

三、数据库的字段属性(重点)

1.unsigned

  • 无符号的整数

  • 不能声明为负数

2.zerofill

  • 0填充的

  • 假设你现在要写一个长度为10的int类型,但是你只写了个1,则他会用自动给你在1前面填充9个零

3.自增

  • 通常理解为自增,自动在上一条记录的基础上+1(默认)

  • 通常用来设计唯一的主键~index,必须是整数类型

  • 可以自定义设置主键自增的起始值和步长

4.非空

  • 假设设置为not null ,如果不给它赋值,就会报错!

  • Null,如果不填写值,默认就是null!

5.默认

  • 设置默认的值

  • sex,默认值为男,如果不指定该列的值,则会有默认的值!

  • 设置默认的值

四、MySQL数据管理

1.外键(了解即可)


删除有外键关系的表的时候,必须要先删除字表,才能删除父表

2.DML语言(全部背住)

  • insert

  • update

  • delete

3.添加

insert into 表名(字段名1,字段名2,字段名3,....) value(值1,值2,值3,....) ==注意:一般写插入语句,我们一定要数据和字段一一对应!==

(1)插入一行数据

insert into grade(gradename) value('大三');

(2)插入多行数据

==注意:给一个字段添加多行值时,每个值都用括号括起来,且中间用逗号隔开。==
insert grade(gradename) value('大二'),('大一');

insert into student(name,pwd,sex)  values('张三','aaaa','男'),('李四','vvvv','女');

4.修改

(1)修改一条数据

格式:update 表名 set colnum_name = value where 条件 下面这行代码的意思是将student表中id=2的name值设置为TWQ
update student set name='TWQ' where id =2;

(2)修改多条数据

条件:where子句,运算符id等于某个值,大于某个值,或在某个区间内修改
操作符 含义 范围 结果
= 等于 5=6 false
<>或!= 不等于 5<>6 true
< 小于 5<6 true
> 大于 5>6 false
<= 小于等于 5<=6 true
>= 大于等于 5>=6 false
between a and b 在a到b这个闭包区间内 [a,b] true
and 我和你 && 5>1 and 1>2 false
or 我或你 5>1 or 1>2 true

5.删除

(1)delete命令

语法:delete from 表名 where 条件;
delete from student where id =1;

(2)truncate命令

作用:完全清空一个数据库表,表的结构和索引不会变
truncate table student;

(3)truncate和delete命令区别

  • 相同点:都能删除数据,都不会删除表结构

  • 不同:①truncate重新设置自增列,计数器会归零。②truncate不会影响事务
    使用delete删除表中所有数据,不会影响自增的值
    拓展(了解即可)关于delete删除的问题:删除后重启数据库

  • 若该表使用的引擎为innodb,重启数据库后,自增列会从1开始(存在内存当中断电即失)

  • 若采用的引擎是myisam 重启数据库后,会继续从上一个自增变量开始(存储在文件中,不会丢失)

delete from grade;


使用truncate删除表中所有数据,自增变量会归零

truncate table grade;

五、DQL查询数据(重点中的重点)

select语法

5.1指定查询字段

(1)查询某个表中全部的数据

如查询student表中所有的数据
select * from student;

(2)查询某个表中指定字段

①查询student表中name和pwd字段的值
select name,pwd from student;


②给字段或表名起别名(as)

select name as 姓名,pwd as 密码 from student;

(3)函数concat(a,b)

select concat('姓名:',name) as 新名字 from student;

(4)去重distinct

①查询有哪些同学参加了考试(可能有一个同学考了多个学科,造成的数据重复,需要去重)
select distinct Student_id from result;

(5)查询系统版本

select version();

(6)用来计算

select 100*3-1 as 计算结果;

(7)查看学生成绩+1分之后的结果

select Student_id,StudentResult+1 as 加一分后 from result;

5.2 where条件子句

作用:检索数据中符合条件的值 搜索的条件由一个或者多个表达式组成!结果为布尔值

(1)逻辑运算符

运算符 语法 描述
and a and b 逻辑与
or a or b 逻辑或
Not not a 逻辑非

(2)where的运用

①查询23(学科编号)这个学科成绩在95到100之间的学生
//方式一:select student_id  as 学生编号,studentresult as 分数 from resultwhere studentresult>=95 and studentresult<=100 and subject_id=23;//方式二:select student_id  as 学生编号,studentresult as 分数 from resultwhere studentresult between  95 and 100 and subject_id=23;

(3)模糊查询:比较运算符

运算符 语法 描述
is null a is null 如果操作符为null,结果为真
is not null a is not nul 如果操作符为不为null,结果为真
between a between b and c 若a在b和c之间,则结果为真
like a like b sql匹配,如果匹配b,则结果为真
in a in(a1,a2,a3,.....) 假设a在a1,或者a2....其中的一个值中,结果为真

①查询姓唐的同学
like 结合 %(代表0到任意个字符)    _(一个字符)

select name,birthday from student where name like '唐%';

②查询姓唐的同学,并且名字后面仅有一个字的

select name,birthday from student where name like '唐_';

③查询姓唐的同学,并且名字后面仅有个字的

select name,birthday from student where name like '唐__';

④查询名字中含有嘉字的同学 %嘉%

select name,birthday from student where name like '%嘉%';

⑤查询id为3,4,5号的学生

select name,birthday from student where id in (3,4,5);


⑥查询家住在宁波和上海的学生

select name,address from student where address in ('宁波','上海');


⑦查询邮箱为空的学生

select name,email from student where email is null;


⑧查询邮箱不为空的同学

select name,email from student where email is not null;

5.3联表查询

(1)join对比


①查询参加了考试的同学(学号,姓名,科目编号,分数)使用inner join来查询
思路:

  • 分析需求,分析查询的字段来自哪些表,(连接查询)

  • 确定使用哪种连接查询? 7种

  • 确定交叉点(这两个表中那个数据是相同的)判断条件:学生表中的student_id = 成绩表的student_id

--  这里给student表和result表起别名,就是因为这两张表中都有student_id这个属性,为了不扯皮,所以起别名并写明用哪个表的idselect s.student_id,name,subject_id,studentresultfrom student as sinner join result as rwhere s.student_id=r.student_id;


②使用right join来实现上述操作
join(连接的表) on(判断的条件) 连接查询
where 等值查询

select s.student_id,name,subject_id,studentresultfrom student  sright join result  ron s.student_id=r.student_id;


③使用left join操作上述问题

select s.student_id,name,subject_id,studentresultfrom student  sleft join result  ron s.student_id=r.student_id;

由结果可知left join会将没有参与考试的学生也查了出来

操作 描述
inner join 如果表中至少有一个匹配,就返回行(hang)
left join 会从左表中返回所有的值 ,即使右表中没有匹配
right join 会从右表中返回所有的值 ,即使左表中没有匹配
④查询缺考的同学
select s.student_id,name,subject_id,studentresult--  起别名的时候as也可以省略from student  sleft join result  ron s.student_id=r.student_idwhere studentresult is null;


⑤查询参加了考试的同学的信息:学号,姓名,科目名,分数
思路:

  • 分析需求,分析查询的字段来自哪些表,student,result,subject(连接查询)

  • 确定使用哪种连接查询? 7种

  • 确定交叉点(这两个表中那个数据是相同的)判断条件:学生表中的student_id = 成绩表的student_id ,成绩表中的subject_id =科目表中的subject_id;

select s.student_id,name,subject_name,studetresultfrom student sright join result ron s.student_id=r.student_idinner join subject subon r.subject_id=sub.subject_id;


⑥查询参加了高等数学考试的同学的信息:学号,姓名,科目名,分数

select s.student_id,name,subject_name,studentresultfrom student sright join result ron s.student_id=r.student_idinner join subject subon r.subject_id=sub.subject_idwhere subject_name ='高等数学';

总结:

  • 我要查询哪些数据 select.....

  • 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件

  • 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加

(2)自连接

自己的表和自己的表连接,核心:一张表拆为两张一样的表即可 对于下面这张表进行拆分

父类(找pid等于1的为一棵树的最顶端)

categoryid categoryname
2 信息技术
3 软件开发
5 美术技术

子类

pid categoryid categoryname
3 4 数据库
3 6 web
5 7 ps技术
2 8 办公信息

①操作:查询父类对应的子类关系

父栏目 子栏目
信息技术 办公信息
软件开发 数据库
软件开发 web
美术技术 ps技术

采用联表查询得到上表只需要让父类的categoryid等于子类的pid即可

//将一张表看成一模一样的表select a.categoryname as 父栏目,b.categoryname as 子栏目from category as a,category as bwhere a.categoryid = b.pid;

5.4分页(limit)和排序(order by)

(1)排序

①升序asc
查询参加了考试同学的信息(姓名,考试科目,成绩)并将成绩按从小到大排序
select  name,subject_name,studentresultfrom student sinner join result ron s.student_id=r.student_idinner join subject subon r.subject_id = sub.subject_idorder by studentresult asc;

②降序desc
只需在上述例子中将asc改为desc即可

(2)分页

语法:==limit(查询起始下标,pagesize)==

第一页: limit 0,5     起始查询下标:(1-1)5
第二页: limit 5,5     起始查询下标:(2-1)
5
第三页: limit 10,5     起始查询下标:(3-1)5
第N页: limit (n-1)
5,5     起始查询下标:(n-1)*pagesize

pagesize:页面大小
(n-1)*pagesize:起始查询下标
n:当前页
数据总数/页面大小 = 总页数

5.5子查询

①查询高等数学 的所有考试结果(学生id,科目编号,成绩)降序排列 方式一:使用连接查询
select student_id,r.subject_id,studentresultfrom result rinner join subject subon r.subject_id=sub.subject_idwhere subject_name='高等数学'order by studentresult desc;


方式二:使用子查询

select student_id,subject_id,studentresultfrom resultwhere subject_id =(  select subject_id   from subject  where subject_name='高等数学');

六、MySQL函数

1.常用函数

①数学运算

--  绝对值select abs(-8)-- 向上取整select ceiling(9.4)-- 向下取整select floor(9.4)--  返回一个0-1之间的随机数select rand()-- 判断一个数的符号 0-0 返回-1,正数返回1select sign(10)

②字符串函数

-- 字符串长度select char length('发来的法计算')-- 拼接字符串select concat('我','adf','asd')-- 查询,从某个位置开始替换某个长度select insert('我爱变成helloworld',1,2,'超级热爱')-- 小写字母select lower('KjfdsSJK')-- 大写字母select upper('KjfdsSJK')-- 返回第一次出现的子串的索引select instr('twq123','3')-- 替换出现的指定字符串select replace('坚持就是胜利','坚持','努力')-- 返回指定的字符串(源字符串,截取的位置,截取的长度)select substr('坚持就是胜利',3,2)-- 反转select reverse('清晨我上马')

运用
将姓唐的同学姓式改为姓汤

select replace(name,'唐','汤') from studentwhere name like '唐%';

③时间和日期函数

-- 获取当前的日期select current date()-- 获取当前日期select curdate()-- 获取当前的时间select now()-- 获取本地时间select localtime()-- 获取系统时间select system()select year(now())select month(now())select day(now())select hour(now())select minute(now())select second(now())--系统select system_usr()select user()select version()

2.聚合函数(常用)

函数名称 描述
count() 计数
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
....

查询student表中一共有多少人

-- 都能够统计,表中的数据(想查询一个表中有多少个记录,就是用这个count())select count(name) from student -- count(字段),会忽略所有的null值select count(*) from student; -- count(*) 不会忽略null值 ,本质在计算行数select count(1) from result; -- count(1) 不会忽略null值 ,本质在计算行数select sum(studentresult) as 总和 from resultselect avg(studentresult) as 平均分 from resultselect max(studentresult) as  最高分 from resultselect min(studentresult) as  最低分 from result-- 查询不同课程的平均分,最高分,最低分-- 核心:(根据不同的课程分组)select subject_name, avg(studentresult),max(studentresult),min(studentresult)from result rinner join subject subon r.subject_id =sub.subject_idgroup by r.subject_id -- 通过什么字段来分组having 平均分>80; -- 分组之后再添加条件必须使用having,详见select语法的图

注意:执行以上命令可能会如果没有修改MySQL的mode将会报以下错误

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.sub.subject_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方式:
在Navicat或者sqlyog的命令中执行以下命令即可

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

5.3数据库级别的MD5加密(扩展)

-- 明文密码insert into testmd5 values(1,'zhanshan','123456'),(2,'lisi','abcdefg'),(3,'wangwu','asdfgh');

-- 加密update testmd5 set pwd=MD5(pwd) where id =1; -- 对单个数据进行加密update testmd5 set pwd=MD5(pwd); -- 加密全部数据

-- 插入的数据的时候加密insert into testmd5 values(4,'xiaming',MD5('567890'));

七、事务

7.1什么是事务

要么都成功,要么都失败就是下面这个转账的事务,必须要子啊两条SQL执行完成之后该事务才算结束,体现事物的原子性

①、SQL执行     A给B转账    A最开始有1000   B最开始有200   A给B转200
②、SQL执行    B收到A的钱   此时A有800    B有400

7.2事务原则

ACID 原则:原子性,一致性,隔离性,持久性

(1)一致性

还是A给B转账这个事,事务完成后,符合逻辑运算也就是总钱数始终是1200

(2)隔离性

所谓的独立性是指并发(多个用户同时操作)的事务之间不会互相影响,如果一个事务要访问的数据正在被另外一个事务修改,只要另外一个事务未提交,它所访问的数据就不受未提交事务的影响 隔离所导致的一些问题:

①脏读:

指一个事务读取另外一个事务未提交的数据.

②不可重复读:

在一个事务内读取表中的某一行数据,多次读取结果不同.(这个不一定是错误,只是某些场合不对).

③虚读(幻读):

是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致.

(3)持久性

事务因提交不可逆转,被持久化到数据库中

(4)事务的实际操作

-- MySQL是默认开启事务自动提交的set autocommit =0  -- 关闭set autocommit =1; --开启(默认的)-- 手动处理事务set autocommit =0 -- 关闭自动提交-- 事务开启start transaction -- 标记一个事务的开始,从这个之后的sql都在同一个事务内-- 提交:持久化(成功)commit-- 回滚:回到原来的样子(失败)rollback-- 事务结束set autocommit=1 -- 开启自动提交

(5)模拟事务场景

-- 创建一个转账的数据库create database shop character set utf8 collate utf8_general_ci;-- 在此数据库中创建表create table account(    id int(3) not null auto_increment,    name varchar(20) not null,    money decimal(9,2) not null,    primary key(id)    )engine=innodb default charset=utf8;--往表里添加数据insert into account(name,money)values('A',2000.00),('B',10000);--模拟转账,事务set autocommit =0; -- 关闭自动提交start transaction; --开启一个事务update account set money=money-500 where name='A'; -- A减500update account set money=money+500 where name='B'; -- B加500commit; -- 提交事务rollback; -- 回滚set autocommit =1; -- 开启自动提交

八、索引

索引的定义:索引是帮助MySQL高效获取数据的数据结构,
提取句子主干,就可以得到索引的本质:索引就是数据结构

8.1索引的分类

在一个表中,主键索引只能有一个,唯一索引可以有多个
  • 主键索引(primary key)
    • 唯一的标识,主键不可重复,只能有一个列作为主键

*唯一索引 (unique key)

  • 避免重复列出现,唯一索引可以重复,多个列都可以标识唯一索引

  • 常规索引(key / index)

    • 默认的,index,key关键字来设置
  • 全文索引(fulltext)

    • 快速定位数据

(1)索引的使用

-- 索引的使用-- 1.在创建表的时候给字段增加索引-- 2.创建完毕后,增加索引show index from student; -- 显示所有的索引信息-- 增加一个全文索引(索引名) 列名alter table school.student add fulltext index name (name);-- explain 分析sql执行的状况explain select * from student;-- 非全文索引explain select * from student where match(name) against('唐');

给student表中的name添加字段之后结果图

8.2测试索引

~~~sql -- 插入100万条数据 -- 写函数之前必须要写,标志 delimiter $$ create function mock_data() returns int begin declare num int default 1000000; declare i int default 0;

while i <num do
insert into app_user(name,email,phone,gender,password,age)
values(concat('用户',i),'1430953131@qq.com',concat('18',floor(rand()((999999999-100000000)+100000000))),floor(rand()2),uuid(),floor(rand()*100));
set i =i+1;
end while;
return i;
end;

执行上述函数可能会报以下错误1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)解决方式:临时解决方法(重启MySQL后失效):在命令行界面执行以下命令~~~sqlset global log_bin_trust_function_creators=TRUE;

永久解决方法:
在配置文件/etc/my.cnf的[mysqld]配置log_bin_trust_function_creators=1

8.3索引的原则

  • 索引不是越多越好

  • 不要对进程变动数据加索引

  • 小数据量的表不需要加索引

  • 索引一般加在常用来查询的字段上

(1)索引的数据结构

Hash类型的索引 Btree:innodb的默认数据结构

九、数据库备份

十、权限管理

1.用户管理

十一、数据库的规约,三大范式

十二、JDBC

-EOF-