mysql的14天 | 小册训练营

经历掘金小册活动后,做一个关于mysql的总结,也是学习着发布的第一篇总结性文章。

简单介绍了解的几种字符编码,在日常中经常会碰到存入数据库字符串数据乱码的问题,设置好字符集很重要。

  1. 固定长度的两种字符集编码,ASCII,ASCII是使用一个字节长度来进行编码一个字符,ucs2是使用两个字节进行编码。
  2. 非固定长度的有utf8和gb2312,utf8收录了非常多的编码格式,现在大多都使用这个进行字符集的设置,数据库默认的utf8格式占用1~3个字节进行字符编码,而默认的utf8的真正名字叫做 utf8mb3
    ,还有个 utf8mb4
    ,如下图,mb4可以用来存入表情符。

老生常谈,介绍一些基本语法,包括创建,修改,查询,删除之类的。

  • 使用数据库之前第一步操作,创建数据库。

CREATE DATABASE IF NOT EXISTS 数据库名;`

  • 查看数据库也很简单,查看所有数据库语法

SHOW DATABASES

  • 在用到数据库的时候,需要先切换到当前数据库

USE 数据库名

  • 删除数据库

DROP DATABASE 数据库名;

善用 IF EXISTS和IF NOT EXISTS可以避免报错

如创建数据库的时候,如果存在就不创建使用if not exists就可以,删除时加入没有要删除的数据库存在,则会报错,
这时使用drop database if exists 数据库名就可以避免报错
复制代码
复制代码

创建好数据库后接下来创建表,基本语法为

create table 表名{
列名 数据类型  列属性,
列名 数据类型  列属性,
列名 数据类型  列属性
}
复制代码
复制代码

接下来会正常的创建两张表,如创建表user1和表user2

下面是主键自增,枚举类型的性别,number唯一索引,user_id为主键
create table user1(
user_id int(11) AUTO_INCREMENT,
name varchar(20) not null,
age int(3) not null,
sex ENUM('男','女'),
number VARCHAR(20) unique,
primary key(user_id)
)
user2_id作为主键并且自增,身份字段默认值为学生,外键是user_id对应为user1表的user_id字段
create table user2(
user2_id int(11) AUTO_INCREMENT,
class varchar(10) not null,
identity varchar(10) not null DEFAULT '学生',
user_id int(11),
primary key (user2_id),
constraint foreign key(user_id) references user1(user_id)
)
复制代码
复制代码

批量插入数据进user1

number字段有唯一索引,插入的数据不能重复;
insert into user1(name,age,sex,number)
values('张三',18,'男','350489199507180422'),
('李四',18,'男','350489199507183599'),
('小红',18,'女','350489199507185986')
insert into user2(class,identity,user_id)
values('2001','学生',1),
('2001','学生',2),
('2001','老师',3)
复制代码
复制代码

查询操作{下面分别为表user1和user2}

查询去重,关键字distinct 列名,查询多列可用英文逗号接在后面。
select  distinct sex from user1
between条件语句
select * from user1 where age between 18 and 30
匹配列表中的元素查询,因为有时候匹配值并不是单个的,而是一个列表,操作符为in
如:查询user1表中年龄符合18和20岁的所有数据
select * from user1 where age in(18,20)
in是匹配列表中的元素,not in则为不匹配列表中的元素,用法如下
如:查询user1表中年龄不符合18和20岁的所有数据select * from user1 where age not in(18,20)
聚合函数中的最大,最小,平均,求和等函数,MAX(列),MIN(列),AVG(列),SUM(列)
select max(age) 最大年龄,MIN(age) 最小年龄,AVG(age) 平均年龄,SUM(age) 总年龄 from user1
聚合函数中使用去重关键字distinct,以下查询总年龄本为76,但是去重后只剩下一个18,一个22
select sum(distinct age) 总年龄 from user1
关联查询
select * from user1 aa,user2 bb where aa.user_id=bb.user_id
分组查询
select aa.name,bb.class,count(*) from user1 aa,user2 bb where aa.user_id=bb.user_id  GROUP BY aa.name, bb.class
加上一条新数据王五的左连接查询
select * from user1 aa left join user2 bb on aa.user_id=bb.user_id
右连接查询以user2为主查询,只查询出基于user2表符合条件的数据,王五的数据不会被查出来
select * from user1 aa right join user2 bb on aa.user_id=bb.user_id
内连接查询,查询出的结果是以on后的条件成立为准的。
select * from user1 aa inner join user2 bb on aa.user_id=bb.user_id
随意列举一个子查询,查询性别为男并且平均年龄大于18的数据
select *  from user1 aa where aa.sex='男' and age>(select AVG(age) from user1)
组合查询关键字“UNION”,建议查询列的类型是相同的,不同也会进行自动转换,下面会进行正常的查询和列类型不匹配的查询。
select *  from user1 where name='张三' UNION select * from user1 where sex='女'
select name,age,sex  from user1 where name='张三' UNION select name,sex,age from user1 where sex='女';
当然也可以使用union连接两张表联合查询,如:
select name,age  from user1 where name='张三' UNION select class,identity from user2 where identity='学生'
使用UNION关键字的组合查询,会将查询出的结果去重。而想将所有结果展示出来就需要使用UNION ALL关键字了。
使用UNION查询出来的结果,是以第一个查询中给定的列名来展示的,所以如果你想给查询出的结果进行排序并且限制查询出的条数,那么应该注意列名(别名也可)
(select name,age  from user1 where name='张三') UNION (select class,identity from user2 where identity='学生') ORDER BY name LIMIT 1
复制代码
复制代码

插入,删除,更新要注意的点

插入;
关系型数据库中,数据一般是以行为单位插入到数据库中的,所以在插入的时候,需要制定列名,一个个插入。完整语法为:
insert into table(列名1,列名2) values(值1,值2);#值需要和列名的属性类型对应上,字符串的列对应的值需加上英文的双引号"值";
关键字INSERT IGNORE,加上了IGNORE后,比如遇到设置了唯一索引的字段,插入的时候值重复插入了,就不会提示异常,会忽略这条重复数据的插入。
删除;
删除语法:
delete from table where 条件;#不加where将删除全部数据,慎用!!!!
删除时也可以排序然后指定限制多少数据,如:
DELETE FROM first_table ORDER BY first_column DESC LIMIT 1;
更新;
更新语法:
update table set 列1=值1,列2=值2 where 条件;#不加where更新表所有的数据,慎用!!!!
同理更新也可以排序限制数据量;
复制代码
复制代码

总结一些视图,存储例程(存储过程,存储函数),游标,触发器和事件等

变量和语句分隔符

自定义变量和语句分隔符;
mysql自定义变量需要使用@符,如定义变量a,其语法为SET @a=1;同一个变量可被赋值多次,SET @a='呵呵';a就从1变为了呵呵;
同样可以将变量a赋值给另一个变量,如SET @b=@a;
将查询到的值赋值给变量:SET @a=select name from user limit 1;
同时赋值多个变量:select n1,m1 from user limit 1 into @a,@b;
语句分割符默认";",自定义分隔符的语法为delimiter
如:delimiter @后,则在执行完语句最后需要用上@符才算结束。
复制代码

视图

创建视图语法;create view 视图名 as 查询语句删除视图;drop view 视图名1:创建视图并不会吧其存在磁盘或者内存中,在对视图进行查询时,mysql会把对视图的查询传换为对其底层表的查询。2:创建视图的列要求和查询的列保持属性一致,在查询时,查询是视图的列而不是表的列。如创建一个视图,create view user_view(a,b,c) as select name,age,sex from user;查询时应查询a,b,c;还有一个要注意的点,创建的视图名不能和已经存在的表名冲突,否则会创建失败。3:创建视图时依赖单表创建,在对视图进行更新,删除等操作,其作用的是相对应的表。创建视图依赖多张表,则无法对其进行更新操作。
复制代码

存储例程(存储函数)

存储函数;存储函数更注重执行完语句返回结果结果值创建函数语法:CREATE FUNCTION 存储函数名称([参数列表])RETURNS 返回值类型BEGIN函数体内容END如:create function avg_age(a VARCHAR(100))returns intbeginreturn (select AVG(age) from user1 where sex=a);END;创建存储函数avg_age,参数值为varchar类型的a,返回值为int类型,begin语句后的查询语句表名返回值就是根据这个查询语句得来的。使用函数:select avg_age('男');给存储函数中定义变量赋值返回,语法如下:create function avg_age2(a VARCHAR(100))returns intbegindeclare cc int;SET cc= (select AVG(age) from user1 where sex=a);return cc;END;声明了一个变量cc为int类型,将查询到的结果赋值给cc并且return;存储函数中使用if语句,语法如下:CREATE FUNCTION condition_demo(i INT)RETURNS VARCHAR(10)BEGINDECLARE result VARCHAR(10);IF i = 1 THENSET result = '结果是1';ELSEIF i = 2 THENSET result = '结果是2';ELSEIF i = 3 THENSET result = '结果是3';ELSESET result = '非法参数';END IF;RETURN result;END;存储函数中使用循环,介绍一个loop语法:CREATE FUNCTION sum_all(n INT UNSIGNED)RETURNS INTBEGINDECLARE result INT DEFAULT 0;DECLARE i INT DEFAULT 1;LOOPIF i > n THENRETURN result;END IF;SET result = result + i;SET i = i + 1;END LOOP;END如果不想直接返回函数值,仅仅只是退出循环,可以使用leave语句,不过需要loop前放一个标记,如:CREATE FUNCTION sum_all(n INT UNSIGNED)RETURNS INTBEGINDECLARE result INT DEFAULT 0;DECLARE i INT DEFAULT 1;flag:LOOPIF i > n THENLEAVE flag;END IF;SET result = result + i;SET i = i + 1;END LOOP flag;RETURN result;END查看定义了多少存储函数:如:SHOW FUNCTION STATUS LIKE 'avg_age'查看函数如何定义的语法:如:SHOW CREATE FUNCTION 函数名删除存储函数:如:DROP FUNCTION 函数名
复制代码

存储例程(存储过程)

存储过程;存储过程更注重单纯的执行语句创建存储过程语法:CREATE PROCEDURE 存储过程名称([参数列表])BEGIN需要执行的语句END如:CREATE PROCEDURE t1_operation(m1_value VARCHAR(100),n1_value int)BEGINSELECT * FROM user1;INSERT INTO user1(name, age) VALUES(m1_value, n1_value);SELECT * FROM user1;END ;调用则需要使用call显示调用:CALL t1_operation('麻子',20);查看当前数据库中创建的存储过程都有哪些的语句:如:SHOW PROCEDURE STATUS LIKE 't1_operation'查看某个存储过程怎么定义的:SHOW CREATE PROCEDURE 存储过程名称删除存储过程:drop PROCEDURE t1_operation
复制代码

游标

游标:简单来说游标是用来存储查询出来的多个值的容器。创建游标语法:DECLARE 游标名称 CURSOR FOR 查询语句;如下,在存储过程中使用:CREATE PROCEDURE cursor_demo()BEGINDECLARE t1_record_cursor CURSOR FOR SELECT name, age FROM user1;END使用游标赋值,语法为:FETCH 游标名 INTO 变量1, 变量2, ... 变量n如下在存储过程中使用游标赋值:CREATE PROCEDURE cursor_demo()BEGINDECLARE m_value VARCHAR(100);DECLARE n_value int;DECLARE t1_record_cursor CURSOR FOR SELECT name, age FROM user1;OPEN t1_record_cursor;FETCH t1_record_cursor INTO m_value, n_value;SELECT m_value, n_value;CLOSE t1_record_cursor;END ;直接调用只会查询出一条数据,如果想要全部显示出来,则需要将fetch放入到循环中,如下使用while do循环CREATE PROCEDURE cursor_demo()BEGINDECLARE m_value VARCHAR(100);DECLARE n_value int;DECLARE record_count INT;DECLARE i INT DEFAULT 0;DECLARE t1_record_cursor CURSOR FOR SELECT name, age FROM user1;SELECT COUNT(*) FROM user1 INTO record_count;OPEN t1_record_cursor;WHILE i < record_count DOFETCH t1_record_cursor INTO m_value, n_value;SELECT m_value, n_value;SET i = i + 1;END WHILE;CLOSE t1_record_cursor;END ;多出了两个参数,record_count 和i,record_count 记录所有数据,i记录当前所处位置,每调用一次 FETCH 语句,游标就移动到下一条记录的位置。
复制代码

触发器和事件:触发器和事件是在mysql特定情况下自动调用的,而非像存储过程需要手动调用。

** 触发器:

**

创建触发器语法:CREATE TRIGGER 触发器名{BEFORE|AFTER}{INSERT|DELETE|UPDATE}ON 表名FOR EACH ROWBEGIN触发器内容END语法的第二行before和after分别代表具体语句执行前和后开始执行触发器的内容第五行FOR EACH ROW代表对受影响的每一条语句都执行触发器内容注意:mysql当前只能对insert,delete,update这三种类型语句设置触发器mysql针对每条受语句影响的语句,有两种访问方式,分别是new 和 old1:对于INSERT语句设置的触发器来说,NEW代表准备插入的记录,OLD无效。2:对于DELETE语句设置的触发器来说,OLD代表删除前的记录,NEW无效。3:对于UPDATE语句设置的触发器来说,NEW代表修改后的记录,OLD代表修改前的记录。4:触发器内容中NEW代表记录的列的值可以被更改,OLD代表记录的列的值无法更改。创建一个触发器:CREATE TRIGGER bi_t1BEFORE INSERT ON user1FOR EACH ROWBEGINIF NEW.age < 1 THENSET NEW.age = 1;ELSEIF NEW.age > 10 THENSET NEW.age = 10;END IF;END ;创建触发器bi_t1,针对user1表,当待插入数据age小于1,赋值为1,大于10则赋值为10,在对user1表进行插入时,会执行触发器内容;查看数据库所有定义触发器语句SHOW TRIGGERS;查看具体触发器的定义SHOW CREATE TRIGGER 触发器名;删除触发器:DROP TRIGGER 触发器名;
复制代码

事件:

事件:类似于定时任务,当想让mysql在指定事件或者每隔一段时间执行一些语句,则可以创建一个事件;创建事件语法:CREATE EVENT 事件名ON SCHEDULE{AT 某个确定的时间点|EVERY 期望的时间间隔 [STARTS datetime][END datetime]}DOBEGIN具体的语句END如:在某个时间点执行CREATE EVENT insert_t1_eventON SCHEDULEAT '2020-12-25 16:48:50'DOBEGININSERT INTO user1(name, age) VALUES('测试事件', 80);END如:在两天后的当前时间执行CREATE EVENT insert_t1ON SCHEDULEAT DATE_ADD(NOW(), INTERVAL 2 DAY)DOBEGININSERT INTO user1(name, age) VALUES('测试事件', 80);END如:每隔一小时执行一次CREATE EVENT insert_t1ON SCHEDULEEVERY 1 HOURDOBEGININSERT INTO user1(name, age) VALUES('测试事件', 80);END如:在指定事件内一小时执行一次CREATE EVENT insert_t1ON SCHEDULEEVERY 1 HOUR STARTS '2019-09-04 15:48:54' ENDS '2019-09-16 15:48:54'DOBEGININSERT INTO user1(name, age) VALUES('测试事件', 80);END查看所有定义的事件语句SHOW EVENTS;查看具体事件语句SHOW CREATE EVENT 事件名;删除事件DROP EVENT 事件名;
复制代码

注意:默认情况下,mysql并不会帮我们执行事件,需手动开启,语法为:

SET GLOBAL event_scheduler = ON;

陆续花了一星期时间,这是在掘金发表的第一篇文章,后续把从根上理解mysql看完有时间也会进行总结然后发表文章,全程是采用较为通俗的语言进行的总结,适合想复习以及初学mysql的小伙伴阅读,有兴趣的小伙伴看完这篇文章,希望能给你带来一些帮助,夯实mysql基础知识。

参考:小孩子4919 的 MySQL 是怎样使用的:从零蛋开始学习 MySQL

稀土掘金
我还没有学会写个人说明!
上一篇

2020年哪些App攻坚海外市场:图书、社交、娱乐赛道的出海情况解读

下一篇

今年圣诞最出彩的节日特饮,来自这款绿色的茶

你也可能喜欢

评论已经被关闭。

插入图片