MySQL从零开始

mysql 学习第一天

已经有过mysql的N个第一天了，但是咱还是不能放弃，要好好整一下这玩意

前期准备

掘金小册《MySQL 是怎样使用的：从零蛋开始学习 MySQL》
掘金小册《MySQL是怎样运行的：从根儿上理解MySQL》
书籍《mysql高级》

从零蛋开始学习 MySQL

1. mysql 初体验

1.1 目录详解

bin 放置的mysql的可执行文件
data 默认数据存储文件夹
mysql.ini 配置文件

1.2 连接方式

#
mysql -hlocalhost -uroot -proot
#
mysql --host=localhost --user=root --password=root

2. 数据类型

2.1 整数类型

1个字节等于8个比特(1 btype = 8bit)，有符号的情况下，内存的最高位代表符号好，0 正数，1 负数
计算器的最大补码表示为：0111 1111 即 $2^6+2^5+......+2^0=\frac{1-2^7}{1-2}=127$
二进制表示的最小负数: 1000 0000 即为 10000000（求反码）——>11111111（求补码）——>1 00000000（化为十进制）——>-128

类型	占用存储空间（字节）	无符号取值范围	有符号取值范围	含义
tinyInt	1	0~255	-128~127	非常小的整数
smallInt	2
mediumInt	3
int	4
bigInt	8

3. 数据库的基本操作

3.1 数据库示例图

3.2 基础操作

展示数据库
```
SHOW DATABASES;
```
选定数据库
```
USE DATABASE_NAME;
```

创建数据库

CREATE DATABASE dq;				# dq 是我创建的数据库名字

# 加上IF条件创建数据库，防止出现error错误(当数据库不存在的时候就创建该数据库)
 CREATE DATABASE IF NOT EXISTS dq

删除数据库

DROP DATABASE dq;

# 加上IF条件判断，当库存在的时候才删除该数据库
DROP DATABASE IF EXISTS dq;

直连到指定数据库

mysql -uroot -hlocalhost -proot dq	# 直接连到指定数据库

4. 表的基础操作

4.1 基础定义

创建表需要先描述清楚这个表长什么样，列要存储的数据类型是什么，这些对表的描述成为表的结构或者定义

表中的一行叫做记录，一列叫做一个字段

4.2 基础的操作

展示表
```
SHOW TABLES;
```

创建表

给表起个名字
给表定义一些列，并且给这些列都起个名
每一列都需要定义一种数据类型
如果有需要的可以给列定义一些列的属性，比如不允许存储NULL

CREATE TABLE 表名 (
	列名1		数据类型	[列的属性]		[注释]
    列名2		数据类型	[列的属性]		[注释]
    ......
    列名N		数据类型	[列的属性]		[注释]
);

CREATE TABLE IF NOT EXISTS first_table (
	first_column int,
    second_column varchar(100)
);

查看表结构

DESC 表名;
DESCRIBE 表名;
EXPLAIN 表名;
SHOW COLUMNS FROM 表名;
SHOW FIELDS FROM 表名;

查看建表语句
```
SHOW CREATE TABLE first_table;
```

修改表

修改表名（ALTER TABLE 旧表名 RENAME TO 新表名;） , 同时都可以附带移动到别的数据库下

# 创建测试表
create table cs_name (id int,name varchar(100));
# 修改表名
alter table cs_name rename to cs_name_1;

批量修改表名(RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2, ... 旧表名n TO 新表名n;)
```
rename table cs_name_1 to cs_name;
```

增加列（ALTER TABLE 表名 ADD COLUMN 列名列属性列的位置）

alter table first_table add column thrid_column int;

# 插入新列到第一列
alter table first_table  add column fourth_column varchar(10) first;
# 插入一列到指定的列的后面
alter table first_table add column fivth_column varchar(5) after first_column;

删除列（ALTER TABLE 表名 DROP COLUMN 列名）
```
alter table first_table drop column fivth_column;
```
修改列（ALTER TABLE 表名 MODIFY 列名属性名）
```
alter table first_table modify fourth_column char(10);
```

重设列（ALTER TABLE 表名 CHANGE 列名新列名新属性）

alter table first_table change fourth_column new_fourth_column varchar(20);

修改列的排序（ALTER TABLE 表名 MODIFY 列名属性名排序）
```
alter table first_table modify thrid_column char(10) first;
```

5. 列的属性

5.1 基础的操作

主键与UNIQUE的区别
1. 主键与 unique 都能约束都能保证某个列或者列组合的唯一性
2. 一张表中只能定义一个主键，却可以定义多个UNIQUE约束！
3. 规定：主键列不允许存放NULL，而声明了UNIQUE属性的列可以存放NULL，而且NULL可以重复地出现在多条记录中！
外键
1. 父表中被子表依赖的列或者列组合必须建立索引，如果该列或者列组合已经是主键或者UNIQUE属性，那他们也被默认建立了索引。
AUTO_INCREMENT 属性
1. AUTO_INCREMENT翻译为中文可以理解为自动增长，简称自增
2. 列的数据类型是整数型或者浮点数类型，那么这个列可以设置为AUTO_INCREMENT属性
3. 设置AUTO_INCREMENT属性之后，不允许再通过指定DEFAULT属性来设置默认值
ZEROFILL 左边补充 0
1. 该字段必须是整数列
2. 该列必须要有UNSIGNED ZEROFILL的属性
3. 该列的实际值位数要小于显示宽度

创建主键表的操作

create table dq_c (id int auto_increment,name varchar(100),age smallInt,primary key(id));

6. 视图

6.1 视图是什么

我们可以将视图理解为一个查询语句的别名，创建一个视图的语句如下：

CREATE VIEW 视图名 AS 查询语句

6.2 视图的使用建议

视图只在查询的时候使用，增、删、改的时候不要使用，虽然在某一些情况，针对视图也可以做一些更新操作。

在下面这些情况下不能使用更新:

聚合函数
group by
distinct
UNION 或者 UNION ALL
某些子查询
某些连接查询

6.3 删除视图

DROP VIEW 视图名

7. 自定义变量和语句结束分隔符

7.1 存储程序

概念：可以分成存储例程、触发器、事件
存储例程：可以分为存储函数和存储过程
图示

7.2 自定义变量

创建变量

# SET @变量名 = 变量值
SET @a = 'hello';

变量赋值
```
SET @b = @a;
```
查看变量
```
SELECT @a,@b;
```

使用查询的结果赋值

# 赋值单个
set @a = (select a from dq_a limit 1);
select a from dq_a limit 1 into @a;

# 多个赋值
select a,b from dq_a limit 1 into @a,@b;

7.3 语句结束分隔符

默认的结束符为 ;

重定义语句分隔符

delimiter EOF  # 不需要加分好，不然的话结束分隔符也要加上分号
# 想要改回默认的就使用
delimiter ;

8. 存储函数和存储过程

8.1 创建存储函数

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
    函数体内容
END

8.2 编写存储函数

create function show_name(id int)
returns double
begin
	return (select `id` from dq_c where id=id limit 1);
end

8.3 变量的使用

局部变量

declare 变量名 类型 DEFAULT 默认值

# 在函数中使用(1)
create function show_id()
returns double
begin
	declare c int default 1;
	return c;
end
# 在函数中使用(2)
create function show_id()
returns double
begin
	declare c int;
	set c = 1;
	return c;
end

全局变量

调用过函数之后，全局变量后面也可以单独调用

set @变量名 变量值
# 在函数中使用(3)
create function show_id()
returns double
begin
	set @c = 10;
	return @c;
end

# 调用
select show_id();

# 调用全局变量
select @c;

8.4 判断语句

IF 表达式 THEN
    处理语句列表
[ELSEIF 表达式 THEN
    处理语句列表]
... # 这里可以有多个ELSEIF语句
[ELSE
    处理语句列表]
END IF;

create function condition_demo(i int)
returns varchar(10)
BEGIN
	declare result varchar(10);
	if i = 1 then
		set result = '结果是1';
	elseif i = 10 then
    	set result = '结果是10';
    elseif i = 20 then
    	set result = '结果是20';
    else
    	set result = '非法参数';
   	END IF;
   	return result;
END

8.5 循环语句

while ... do 循环

WHILE 表达式 DO
    处理语句列表
END WHILE;

create function sum_all(n int unsigned)
returns int
begin
	declare result int default 0;
	declare i int default 1;
	while i < n do
		set result = result + i;
		set i = i + 1;
	end while;
	return result;
end

REPEAT循环

REPEAT
    处理语句列表
UNTIL 表达式 END REPEAT;

create function sum_all_repeat(n int unsigned)
returns int
begin
	declare result int default 0;
	declare i int default 1;
	repeat
		set result = result + i;
		set i = i + 1;
	until i >= n end repeat;
	return result;
end

loop循环

LOOP
    处理语句列表
END LOOP;

直接跳出执行

CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    LOOP
        IF i > n THEN
            RETURN result;
        END IF;
        SET result = result + i;
        SET i = i + 1;
    END LOOP;
END

只跳出循环

CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    flag:LOOP
        IF i > n THEN
            leave flag;
        END IF;
        SET result = result + i;
        SET i = i + 1;
    END LOOP flag;
    RETURN result;
END

其实也可以在BEGIN ... END、REPEAT和WHILE这些语句上打标记，标记主要是为了在这些语句发生嵌套时可以跳到指定的语句中使用的。

8.6 存储过程创建

定义：存储过程更侧重于单纯的去执行这些语句，而且不需要定义返回类型

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

create procedure first_op(id int, name varchar(100), age int)
begin
	select * from `dq_c`;
	insert into `dq_c` values(id,name,age);
	select * from `dq_c`;
end

调用

CALL 存储过程名([存储过程参数])

查看有哪些存储过程

show procedure status like 需要匹配的存储过程名

显示存储过程详情
```
show create procedure 存储过程名
```
删除存储过程
```
drop procedure 存储过程名
```

8.7 存储过程参数前缀

参数类型 [IN | OUT | INOUT] 参数名 数据类型
# in 为可读不可写
# out 为可写不可读
# inout 为可读可写

9. 游标

9.1 概念

返回的结果集存在多条的时候，变量赋值就没法使用，所以引入了游标的概念

9.2 游标的创建

创建游标

declare 游标名称 cursor for 查询语句

create procedure cursor_test()
begin
	declare first_cursor cursor for select * from `dq_c`;
end

打开游标
```
open 游标名称
```

通过游标访问记录

FETCH 游标名 INTO 变量1, 变量2, ... 变量n

关闭游标
```
close 游标名称
```

获取不到更多记录

DECLARE CONTINUE HANDLER FOR NOT FOUND 处理语句;

完整示例

CREATE PROCEDURE cursor_test () BEGIN
	DECLARE id INT;
	DECLARE NAME VARCHAR ( 100 );
	DECLARE age INT;
	DECLARE first_cursor CURSOR FOR SELECT * FROM `dq_c`;
	DECLARE CONTINUE HANDLER FOR NOT FOUND set not_done=0;	
	OPEN first_cursor;
	flag:Loop
	FETCH first_cursor INTO id,NAME,age;
	IF not_done = 0 THEN
       LEAVE flag;
    END IF;
	SELECT id,NAME,age;
	end loop flag;
	CLOSE first_cursor;
END

10. 触发器和事件

10.1 触发器

示例

CREATE TRIGGER 触发器名
{BEFORE|AFTER}				# before 表示在执行之前   after表示在执行语句之后
{INSERT|DELETE|UPDATE}		# 现只针对 新增，删除，更新
ON 表名
FOR EACH ROW
BEGIN
    触发器内容
END

对于INSERT语句设置的触发器来说，NEW代表准备插入的记录，OLD无效。
对于DELETE语句设置的触发器来说，OLD代表删除前的记录，NEW无效。
对于UPDATE语句设置的触发器来说，NEW代表修改后的记录，OLD代表修改前的记录。

实例：

create trigger bi_dqc
before insert on `dq_c`
for each row
begin
	if new.age < 1 then
		set new.age = 16;
	elseif new.age > 20 then
		set new.age = 18;
	end if;
end

查看触发器
```
SHOW TRIGGERS;
```
查看触发器的定义
```
SHOW CREATE TRIGGER 触发器名;
```
删除触发器
```
drop trigger 触发器名
```

10.2 事件

概念 :

让mysql服务器在个时间点或者每隔一段时间，自动执行一些语句，这时候就需要去创建一个事件

语法：

CREATE EVENT 事件名
ON SCHEDULE
{
    AT 某个确定的时间点| 
    EVERY 期望的时间间隔 [STARTS datetime][END datetime]
}
DO
BEGIN
    具体的语句
END

在某个时间点执行

CREATE EVENT insert_dqc_event
ON SCHEDULE
AT '2022-05-04 16:43:30'
DO
BEGIN
    INSERT INTO dq_c(name, age) VALUES('lisss', '20');
END

每隔一段时间执行一次

CREATE EVENT insert_dqc
ON SCHEDULE
EVERY 10 SECOND
DO
BEGIN
    INSERT INTO dq_c(name, age) VALUES('lkd', '99');
END

查看事件
```
show events;
```
查看指定事件的定义
```
show create event insert_dqc_event;
```
删除事件
```
DROP EVENT 事件名;
```
开启事件自动执行
```
SET GLOBAL event_scheduler = ON;
```

扩展性知识

1. 计算字节存储范围的方法

1.1 符号位

在C语言中将内存的最高位作为符号位，且用0表示整数，1表示负数

2.在计算机中，负数以其正值的补码形式表示

2.1 原码

一个整数按照绝对值的方式转换成为二进制数，就称为原码

00000000  00000000  00000000 00000110	# 就是十进制数6的原码

2.2 反码

将二进制的数按位取反，所得到的新二进制数称为原二进制数的反码

11111111  11111111  11111111  11111001  # 十进制数6的反码

2.3 补码

反码加1称为补码，也就是说，要得到一个数的补码，先得到反码，然后反码加1，所得的数就称为补码所以-6在计算机的表示形式为：

# -6 的绝对值 6 的二进制：
00000000  00000000  00000000  00000110
# 取反得到反码
11111111  11111111  11111111  11111001
# +1 得到补码，即为 -6 在计算机表示形式
11111111  11111111  11111111  11111010

posted @ 2022-05-04 16:57 Dan_eil 阅读(54) 评论(0) 编辑收藏举报

刷新页面返回顶部

Zzz_

我说我喜欢八十年代的歌，你却问我喜欢八十年代的哪首歌