MySQL学习

MySQL基础学习

1、基本命令

mysql -uroot -p123456	--连接数据库
alter user 'root'@'localhost' identified by '123456';	--更改密码
flush privileges;		--刷新权限
exit;					--退出
source xxx.sql			--执行sql脚本
ctrl + c				--强行终止sql语句
==========================================================
--查看
show databases;		--查看数据库
use school;			--切换数据库
show tables;		--查看表
desc student;		--查看表结构
==========================================================
--创建和删除
create database xxx;
drop database xxx;
create table xxx;

2、数据类型

数值

tinyint		极小的整数	    1byte
smallint	小			   2byte
mediumint	中等			  3byte
int			标准的整数		4byte	【常用】
bigint		大整数			 8byte
float		浮点数			 4byte
double		浮点数			 8byte
decimal		字符串型的浮点数  金融计算方面使用

日期事件

date		3byte
time		3byte
year		3byte
datetime	8byte	【常用】
timestamp	时间戳	  【较常用】

字符串

char		定长字符串	0-255
varchar		可变字符串	0-65535	【常用】
tinytext	短文本		 0-255
text		长文本		 0-65535

3、字段属性

unsigned：无符号的整数，声明该列不能为负数

zerofill：零填充，不足的位数使用0填充【int类型长度3： 5 — 005】

auto_increment：自增+1

not null：非空

NULL

primary key：主键

foreign key：外键

default：默认，如果没有赋值会显示默认的值

comment：别名

4、数据库操作

4.1 创建表

create table if not exists 'student'(
    'id' int(4) not null auto_increment comment '学号',
    'name' varchar(10) not null default '默认' comment '姓名',
    'birthday' datetime default null comment '出生日期',
    primary key ('id')
)engine=innodb default charset=utf8

数据库引擎engine

INNODB：默认使用：安全型高，支持事务的处理，因为外键可以多表多用户操作

MYISAM：早些年默认使用：节约空间，速度较快

	MYISAM	INNODB
事务	不支持	支持
外键约束	不支持	支持
数据行锁定	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大【约为2倍】

表的字符集编码charset

charset=utf8
--不设置，使用的就是mysql默认的字符集编码Latin1，不支持中文
--也可以在my.ini中设置默认编码：character-set-server=utf8

4.2 修改表结构

--修改表名
alter table 'student' rename as '';
--添加字段
alter table 'student' add age int(4);
--修改字段【重命名，修改约束】
alter table 'student' change age age1;
alter table 'sthdent' modify age int(3);
--删除字段
alter table 'student' drop age;

4.3 删除表

drop table if exists 'teacher';

4.4 物理外键

【数据库级别的，了解即可】

--添加外键两种方法
--建表语句内
key '外键值'('表内字段'),
constraint '外键值' foreign key('表内字段') references '外表'('外表字段')
--建表语句外
alter table '主表' add constraint '外键值' foreign key('表内字段') references '外表'('外表字段')

5、数据操作

DDL数据库定义语言
DQL数据库查询
DML数据库操作语言
DCL数据库控制语言
TCL事务控制语言

5.1 DML操作语言

添加：insert

insert into '表名'
	('id','name','age')
values
	('1','张三','22'),
	('2','张si','23');

修改：update

update '表名'
	set 'name' = '李四','age' = '25'
	where id = 1;

删除：delete

delete from '表名'
	where id = 2；

truncate：完全清空一个数据库表，表的索引和结构不会变

truncate 'student';
--清除时，会重新设置自增序列归零
--不会影响事务

5.2 DQL查询语言

--查询版本号
select version();


SELECT[ALL|DISTINCT]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]		按照某些字段分组
[HAVING…]		对分组之后数据再过滤
[ORDER BY…]		按照指定条件排序
[limit(开始位置,显示条数)]	分页显示

5.2.1 简单查询

select和别名

select * from '表名'；
select 'name','age' from '表名';

--concat函数，在查询的结果前拼接“姓名为”
select concat('姓名为','name') from '表名';

--别名
select 'name' as 姓名,'age' as 年龄 from '表名';


select * from '表名' where id = 1;

5.2.2 查询结果去重

distinct

select distinct 'age' from '表名';

5.2.3 where条件子句

逻辑运算符

&&	and				||	or				!	not

where id>90 and id<100;
where id>90 && id<100;
where id between 90 and 100;

where id<70 or id>90;
where id<70 ||id>90;

where id !=90;
where not id=90;

模糊查询：比较运算符

is null
is not null
between xxx  and  xxx
like	%表示零到任意个字符，_表示一个字符
in		in(a,b,c)	精确匹配in中间的值

--查询姓王的同学
select 'sname' from 'student' where 'sname' like '王%';

联表查询：join…on…【连接查询】

• 内连接

  • 内连接inner join
  • 左连接left join
  • 右连接right join

• 外连接

  • 左外连接
  • 右外连接
  • 全外连接

• 交叉连接

自连接：自己一张表看成两张表连接查询

5.2.4 分页和排序

分页：limit

排序：order by 升序：ASC 降序：DESC

order by id ASC
--从零开始，显示10个
limit 0,10;

5.2.5 子查询和嵌套查询

查询中嵌套查询

5.3 常用函数

--日期2021-08-11
select current_date();
--2021-08-11 17:19:19
select now();
select localtime();
select sysdate();
select year(now());


select system_user();
select user();
select version();

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

--学生有多少个
select count('stuname') from 'student';		--count(字段)，计算字段的个数，会忽略所有的null值
select count(*) from 'student';				--不会忽略所有的null值，计算行数
select count(1) from 'student';				--不会忽略所有的null值

6、MD5加密

MD5不可逆，MD5破解网站并不是对加密数据解密，而是有一个字典，存储的是MD5加密后的值和加密前的值

md5('123456')

7、事务

要么都成功，要么都失败

ACID原则：原子性、一致性、隔离性、持久性

原子性

事务内操作，要么都成功，要么都失败

一致性

事务前后数据完整性要保持一致

隔离性

多个用户并发访问数据库时，数据库为每个用户开启事务，不能被其他事务的操作数据干扰，事务之间相互隔离

持久性

事务一旦提交不可逆，被持久化到数据库中

隔离可能产生的问题

脏读

一个事务读取到了另一个事务未提交的数据

不可重复读

在一个事务内读取到某行数据，多次读取结果不一样

虚读（幻读）

一个事务内读取到了别的事务插入进来的数据，导致前后读取不一致

=================================================================

mysql是默认开启事务自动提交的

set autocommit = 0;		--关闭自动提交
set autocommit = 1;		--开启自动提交

=================================================================

手动处理事务

set autocommit = 0;		--关闭自动提交
start transaction		--事务开始
--开始执行事务
insert into xxx
insert into xxxx
--如果执行成功，提交
commit
--如果执行失败
rollback
--事务结束

--如果事务特别多，可以设置保存点
savepoint xxx	--设置保存点名
rollback to xxx	--回滚到保存点xxx
release savepoint xxx	--撤销保存点xxx

8、索引

是帮助mysql高效获取数据的数据结构

8.1 索引分类

• 主键索引【primary key】
  • 唯一表示，不可重复，一个表只能有一个
• 唯一索引【unique key】
  • 避免重复的列出现，可以重复，多个列都可以表示为唯一索引
• 常规索引【key】
• 全文索引【FullText】
  • 快速定位数据

8.2 索引使用

--显示表studnet的索引信息
show index from student;

--增加索引
--1、建表的时候添加索引
--2、alter添加
alter table teacher add key index 'index_name'(name);	--给teacher表的name字段添加一个index_name常规索引

--explain分析sql执行的状况
explain select * from teacher;

InnoDB的默认数据结构：BTree

9、用户管理

用户授权

grant all privileges on *.* to xxx用户;	--授全部的权限，除了grant权限
grant all privileges on *.* to xxx用户 WITH GRANT OPTION;

--查看指定用户的授权
show grants for xxx;

--查看管理员权限
show grants for root@localhost;

撤销权限

revoke all privileges on *.* from xxx用户;

10、数据库备份

• 保证重要的数据不丢失
• 数据转移

怎么备份：

• 直接拷贝物理文件
• 使用命令行导出【mysqldump】

#命令行导出：teacher表到d盘a.sql，-h是主机，school是数据库，teacher是表
mysqldump -hlocalhost -uroot -p123456 school teacher>D:/a.sql
#导出多个表
mysqldump -hlocalhost -uroot -p123456 school teacher student>D:/b.sql
#导出数据库
mysqldump -hlocalhost -uroot -p123456 school>D:/c.sql
#导入表
mysql -uroot -p123456 库名<xxxx.sql

#登录MySQL之后，导入表或库
source xxxx.sql

11、数据库设计和三范式

第一范式（1NF）

保证每一列不可再分

第二范式（2NF）

在第一范式基础上，每张表只描述一件事情

第三范式（3NF）

在第二范式基础上，