技术复盘(2)--mysql

技术复盘(2)–mysql

mysql命令复习

资料地址

mysql基本命令中文网：https://www.mysqlzh.com/doc/124.html

总结

总结放前面，因为myslq中总结比敲代码更重要

1. 只查询需要的数据，避免使用select *查询
2. 多表查询能用join就不要用where，因为where查询逻辑是先生成表的笛卡尔积，再查找符合要求的数据
3. 去重避免使用SELECT DISTINCT，使用SELECT DISTINCT会导致MySQL进行排序和去重操作，这个过程非常耗时。如果需要去重，可以使用GROUP BY 进行替代。
4. 每张表最好都有一个自增id，也就是索引index，因为mysql底层采用的b+树，可以将索引index作为值进行存储，便于查询，当然，如果不设置索引index，默认也会有个rowid作为索引，前提是你的表结构中不能有rowid字段，否则会严重影响性能。
5. 尽可能的使用与存储数据匹配的字段类型，比如男女用01表示，数据类型可以选择tinyint而不是int，tinyint占1个字节，而int占4个字节。这算个小细节吧，现在内存够多了，访问量不大用啥也不太影响。

…欢迎补充

复习代码

【数据库为mysql8.x，如果使用mysql5.x部分命令会失败】
【涉及到的sql语句并没有写得太规范，只是为了时不时看一看，怕忘了】

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学生名字',
  `number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学号',
  `birthday` date NULL DEFAULT NULL COMMENT '生日',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `grade_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '成绩id',
  `grade` int(11) NULL DEFAULT NULL COMMENT '成绩',
  `student_id` int(11) NULL DEFAULT NULL COMMENT '学生id',
  PRIMARY KEY (`grade_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

# 查询数据库版本
select version();
# 插入数据 id自增可不设置
insert into student (name,number,address) values('张三三','92021458490','北京');
#加多个值
insert into student (name,number,address) values('李四','92021458491','重庆'),('王麻子','92021458492','成都');
#不指定条件修改所有
update student set name = '张三';
# 修改多个值
update student set name = '张三' , number = '920213333333',birthday = CURRENT_TIME where id = 1;
# 删库跑路--1 
delete from student;
# 删库跑路--2 自增归零
truncate  student;
# 函数---concat  在查询出来的数据前加上“姓名:”
select concat("姓名:",name) as name,number from student;
# 去重---distinct
select distinct name from student;
# 取反not
select * from student where not id = 5;
# 模糊查询--like 通配符
select * from student where name like "张_";
select * from student where name like "张%";
# 查询在北京和成都的学生  in
select * from student where address in ('北京','成都');
# 左连接 成绩查询
select grade.grade ,student.name
from grade
left join student
on grade.student_id = student.id;
# 分组 group by 以唯一标识符分组可以视作去重
select * from student
group by name;
# 排序 order by 默认升序 desc为降序
select * from student 
order by id desc;
# 分页 limit 从第几条到第几条 一页十条就是0 10第一页，10 20 第二页
select * from student  
limit 1,5;
# 和where类似的having 有点点区别
select * from student 
having id = 1 and name = "张三三"
limit 0,5;
# 聚合函数 count求个数 sum求和 avg求平均
select count(id) from student;
select sum(grade) from grade;
select avg(grade) from grade;
select min(grade) from grade;
select max(grade) from grade;

-- 批量插入数据 测试用
drop PROCEDURE if exists test_insert;
delimiter //
CREATE PROCEDURE test_insert(n int)  
    begin  
        declare v int default 0;
        SET AUTOCOMMIT=0;       
        while v < n 
        do  
            insert into student(name,number,address,birthday)
            values (concat("张三",v),v,"重庆",CURRENT_TIME());  
            set v = v + 1;  
        end while;
        SET AUTOCOMMIT=1;       
    end //
call test_insert(1000000)