MySQL浅学习——「MySQL」从零到删库

MySQL浅学习——「MySQL」从零到删库"/>

MySQL浅学习——「MySQL」从零到删库

0. 认识

0-1 软件版本

MySQL版本：5.7.37

MySQL Command Line Client --> Hyper（3.2.0）[推荐]

0-2 使用

启用

mysql -u root -por mysql -u root -p123456(直接加密码)

开/关服务

net start mysql57

net stop mysql57

显示编码集

show variables like 'character_set_%;'

如果出现编码问题，则将 client & results 改为gbk

set character_set_client=gbk;

1. 库的操作

Database

显示 show

show databases; -> 显示所有库；

创建 create

create database student; -> 创建库；

高级 create database if not exists teacher;

更高级 create database if not exists school charset=gbk -> 设置编码为GBK Windows使用，一般设置为utf8

删除 drop

drop database student -> 删除库

高级 drop database teacher if exists

修改 alter

alter database school charset=GBK; -> 修改编码集

show create database school -> 查看修改后的编码集

2. 表的操作

Table

查 show/desc/select

表

	show tables;show create table students;# 创建表的过程desc teacher; -> describe# 查看表内字段的类型等

数据

select * from teacher;

创 create

create table students(-> id int,-> name varchar(30),-> age int# 最后一个字段后无需加';'
);

高级

create table students(-> id int auto_increment primary key comment '主键id',# id 字段# auto_increment 自动增长# 自动增长的表必须为主键# 主键删除后无法再使用# primary key 主键# comment 注释-> name varchar(30) not null,# 不能为空-> phone varchar(20) comment '电话号码',  -> address varchar(100) default '暂时未知' comment '住址'#default 暂时未知-> )engine=innodb;
);

删 drop/delete

表

	drop table students->drop table if exists stu,tea,play;

数据

	delete from teacher where id=4;-> where 限定删除delete from student where age > 30;-> 全部删除delete from student; # 不建议，全部遍历，比较慢# ortruncate table student; # 清空所有表项

改 alter/update

修改字段

	-> 增加字段alter table student add phone varchar(20);-> 增加字段在制定字段之后alter table student add gender varchar(1) after name;-> 增加字段在首位（key word之后）alter table student add address varchar(100) first;-> 删除字段alter table student drop address;-> 修改字段名&类型alter table student drop address;-> 修改类型alter table student modify tel_phone varchar(13);-> 修改表名rename table student to students;# orrename table student to students;

修改数据

update teacher set name='Tom' where id=5;

增 insert

	insert into teacher(id,name,phone,address) values(1,'Bayyy','13866668888','Qingdao');-> 字段与数据对应即可ps1: insert into teacher (phone,name) values ('13688886666','bay');-> 乱序&可省略单元ps2: insert into teacher values(3,'Tom','13344445555','New York');-> 按顺序可不加字段名ps3: insert into teacher values(null,'Tom',null,default);-> null & defaultps4: insert into teacher values(null,'bay_1',null,default),(null,'bay_2',null,default);-> 直接插入多条数据

3. 数据类型

3-1 整数类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-27，27-1)	(0，28)	小整数值
SMALLINT	2 Bytes	(-215，215-1)	(0，216)	大整数值
MEDIUMINT	3 Bytes	(-8×106，8×106)	(0，1×107)	大整数值
INT或INTEGER	4 Bytes	(-2×109，2 ×109)	(0，4 ×109)	大整数值
BIGINT	8 Bytes			极大整数值
FLOAT	4 Bytes			单精度 浮点数值
DOUBLE	8 Bytes			双精度 浮点数值
DECIMAL	M+2 / D+2	依赖于M和D的值	依赖于M和D	小数值

create table emp(-> id smallint unsigned auto_increment primary key comment 'id',-> age tinyint unsigned default '0' comment '年龄', -> second int unsigned comment '活了几秒');create table tab1(-> num1 float(3,1),-> num2 double(5,2));# 需要标注浮点位数# '5':总位数 '2':小数位create table bank(-> money decimal(20,20));# 定点小数

3-2 小数类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串(*)
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

3-3 布尔类型

	create table t_4(-> buer boolean);insert into t_4 values(true);

3-4 枚举类型

	create table t_5(-> gender enum('man','women','?','it'));insert into t_5 values('man');insert into t_5 values(1);# 可用整数代替（从1开始）

3-5 set 类型

	create table t_6(-> hobby set('play','read','study','live'));insert into t_6 values('read,play'); 

3-6 日期和时间类型

<表内一般都要设置日期和时间>

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

mysql> create table t_7(-> createdTime datetime);mysql>insert into t_7 values('2021-03-02 11:00:00');

4. 属性约束

NOT NULL # 不能为空
DEFAULT 'default' # 默认
PRIMARY KEY # 主键# 删除后该主键不能使用# 唯一性、非空# 快速搜索
auto_increment # 自动增长# 自增属性必须为主键

4-1 主键

primary key 主键

• 删除主键 alter table t_8 drop primary key;
• 组合键 alter table t_8 add primary key(id, name);

unique 唯一键

• 一张表可以有多个 -> 保证唯一
• 查看 show create table 't_10'
• 组合唯一键

删除 alter table t_9 drop index name

外键

创建

	# 创建时添加
mysql>create table eat(-> id int primary key,-> money decimal(10,4),-> stuID int(4),-> foreign key (stuID) references stu(stuID));# 后期添加
mysql>alter table eat add foreign key (stuID) references stu(stuID);

删除外键

需要首先查看外键的constraint值，删除此项

	# 查看表的属性
mysql>show creaete table eatery;

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

	# 删除外键
mysql>alter table eatery drop foreign key eatery_ibfk_1;

外键的三种操作

置空：置为Null（一般删除选择）

级联：绑定外键数据全部更新/删除（一般更新选择）

mysql> create table eat(-> id int(20) primary key,-> money decimal(10,4),-> stuID int(4),-> foreign key(stuID) references stu(stuID) on delete set null on update cascade);# mysql 对于varchar类型大小写不敏感？update时会重复！！

5. 查询

5-1 单表查询

1.as
mysql> select 'name_1' as 'name_2';	# 字段重命名2.from	# 笛卡尔积
mysql> select * from t1; 
+------+------+
| id   | name |
+------+------+
|    1 | bay  |
|    2 | tom  |
+------+------+
2 rows in set (0.00 sec)mysql> select * from t2; 
+--------+--------+
| score1 | score2 |
+--------+--------+
|    150 |     90 |
|     80 |    140 |
+--------+--------+
2 rows in set (0.00 sec)mysql> select * from t1,t2;
+------+------+--------+--------+
| id   | name | score1 | score2 |
+------+------+--------+--------+
|    1 | bay  |    150 |     90 |
|    2 | tom  |    150 |     90 |
|    1 | bay  |     80 |    140 |
|    2 | tom  |     80 |    140 |
+------+------+--------+--------+
4 rows in set (0.00 sec)3.where
3.1 in
mysql> select * from t4 where address='shanghai' or address='beijing';# or
mysql> select * from t4 where address in('beijing','shanghai');# or
mysql> select * from t4 where address not in('beijing','shanghai');3.2 between
mysql> select * from student where age between 18 and 20;3.3 is null/ is not null
mysql> select * from teacher where phone is null;3.4 聚合函数
sum()/avg()/min()/max()/count()/

*客户端的使用

软件：Navicat Premium

4 模糊查询 like
mysql> select * from student where name like '张%';# % 多个字符
mysql> select * from student where name like '张_';# _ 一个字符5 排序 order by asc/desc# 升序
mysql> select * from score order by chinese asc; # 降序
mysql> select * from score order by chinese desc; 6 分组查询 group by# 必须是聚合+多组查询
mysql> select avg(age) as '年龄',gender as '性别' from info group by gender;# 聚合分组
select group_concat(id),address from info group by address;7 筛选 having# 对结果进行筛选
mysql> select avg(age) as age, address as '地区' from info group by address having age>23; 8 limit
mysql> select * from info limit 0,2;  # 开始 ，跨度9 distinct / all
mysql> select distinct address from info;   # 默认是 all

5-2 多表查询

1 union
mysql> select name from student union select Chinese from score;2 inner join on	# 内连接
mysql> SELECT name, Chinese from student INNER JOIN score on student.id=score.stuID;3 left join on	# 左连接4 right join on	# 右连接5 cross	join on# 交叉6 natural join on # 自然连接 # 自动匹配字段

5-3 子查询

mysql> select * from student where id in (select stuID from score where Chinese >= 50);# exists 存在 (存在就输出整表)

6. 选修内容

6-1 视图

1 创建
mysql> CREATE VIEW vw_stu2 as SELECT name, age, Chinese from student inner join score on student.id=score.stuID;2 查看
mysql> desc vw_stu;
# or
mysql> show create view vw_stu;
# or
mysql> show table status where comment='view' \G;	# 查看所以引擎名2 修改
mysql> alter view vw_stu as select name from student;3 删除
mysql> drop view vw_stu;4 视图算法
mysql> create algorithm = temptable view vm_stu select * from student;  # undifined/temptable/merge

6-2 事务

6-2-1 事务四大特性

ACID （引擎必须在innodb状态下！）

A：atomicity 原子性

C：consistency 一致性

I：isolation 隔离性

D：durability 持久性

6-2-2 指令

1 基础指令
1.1 开启事务start transaction；1.2 回退rollback；1.3 提交commit；2 回滚点
2.1 设置回滚点savepoint four;2.2 回到指定回滚点rollback to four；

6-3 索引

# indexcreate index index_name on table(name);# unique index	# 唯一索引create unique index index_name on table(name);# draop/ alter

6-3 存储过程

# 修改程序语句结尾为//delimiter //
# 创建	
mysql> delimiter //
mysql> create procedure proc()-> begin-> update wallet set balance=balance+50;-> update t1 set name='dan';-> end//
mysql> delimiter ;
mysql> call proc();# 删除
mysql> drop procedure proc;# 显示
mysql> show create procedure proc;

6-4 小技巧

①number

1 随机数
mysql> select rand();2 抽奖
mysql> select * from student order by rand() limit 3;3 取整
ceil #上/ floor #下/ round # 四舍五入/ truncate # 截取数字
mysql> select truncate(3.1415926,2);4 随机排序
mysql> select * from student order by rand();5 大小写转换
mysql> select ucase('bay');
mysql> select lcase('BAY');6 截取字符串
mysql> select left('BAYYY!',2);
mysql> select right('BAYYY!',2);7 拼接字符串
mysql> select concat('123','bayyy');# 使用
mysql> select concat(name,'|',age) from student;8 时间
mysql> select now();
mysql> select unix_timestamp();
mysql> select year(now()) year, month(now()) month, day(now()) day;9 加密函数
mysql> select sha('bay');

7. 企业规范约束

7-1 库表字段规范约束

• 字段为”是/否“含义

  • name=is_name
  • type=unsigned tinyint
  • long=1

  非负必须设置为unsigned

• 字段名

  • 必须以小写字母开头
  • 不能出现数字
  • 不能出现大写字母
  • 不同单词以下划线 ‘_’ 分开

• 表名

  • 不能出现负数
  • 不能出现关键字

• 索引名

  • 主键 pk_xxx
  • 唯一键 uk_xxx
  • 普通索引名 idx_xxx

• 小数类型

  • decimal！

• 字符串类型

  • 长度很小则选用 char

• 表内必须定义三个字段

  • id、create_time、update_time
  • id type=bigint、主键、非空、自增
  • time type=datatime

7-2 索引规范

7-3 SQL开发约束

• 选用count（*） —> count（xxx、xxx……）（×）

• 不能用 = 判断 null —> is null（√）

• 高并发集群操作不能使用外键（级联）！

• 实际开发中不得使用存储过程！

• 删除更新操作前，先进行查询操作，确保数据正确性

• in 操作尽可能避免

• gbk（×） —> utf8（√）

7-4 其他约束

• 实际开发中不能使用*进行查询
• @Transaction框架尽可能避免