MySQL浅学习——「MySQL」从零到删库"/>
MySQL浅学习——「MySQL」从零到删库
0. 认识
0-1 软件版本
MySQL版本:5.7.37
MySQL Command Line Client --> Hyper(3.2.0)[推荐]
0-2 使用
启用
mysql -u root -p
or 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框架尽可能避免
更多推荐
MySQL浅学习——「MySQL」从零到删库
发布评论