前言

在后端开发中，对数据库的了解是必不可少的。这里记录一下我所掌握的一些常用命令。

常用命令

• mysql_secure_installation 初始化数据库
• mysql -u root -p 连接数据库
• alter user root@localhost identified by 'root'; 更改数据库密码
• show databases 查看数据库
• create database database_name 创建数据库
• create database database_name default character set charset_name 创建数据库，设置字符集
• alter database database_name default character set charset_name 修改数据库字符集
• drop database my_db 删除数据库
• use my_db 选择数据库
• show tables 查看表
• create table table_name(column_name column_type) 创建表
• drop table table_name 删除表
• insert into table_name values(column_value_1, column_value_2) 插入数据
• insert into table_name(column_name_1, column_name_2) values(column_value_1, column_value_2) 插入数据

表

创建表

-- 创建用户表
create table user
(
userid varchar(100) primary key,
nickname varchar(20),
email varchar(20),
phone int(11),
`password` varchar(20),
createdate datetime,
avatar varchar(255),
sex int,
birthday date,
address varchar(255)
)
-- 创建博文表
create table blog
(
blogid varchar(100) primary key,
title varchar(255) not null,
content longtext
)
-- 创建发表博文表
create table blog_add
(
userid varchar(100),
blogid varchar(100),
createdate datetime
) 
-- 创建博文浏览表
create table blog_look
(
userid varchar(100),
blogid varchar(100),
foreign key(userid) references `user`(userid),
foreign key(blogid) references blog(blogid)
)
-- 创建博文点赞表
create table blog_agree
(
userid varchar(100) unique,
blogid varchar(100) unique,
foreign key(userid) references `user`(userid),
foreign key(blogid) references blog(blogid)
)

修改表结构

ALTER TABLE <表名> 
{ ADD COLUMN <列名> <类型>        #增加列
| CHANGE COLUMN <旧列名> <新列名> <新列类型>        #修改列名&数据类型
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }        #修改某列的默认值
| MODIFY COLUMN <列名> <类型>        #修改数据类型
| DROP COLUMN <列名>        #删除列
| RENAME TO <新表名>        #重命名表 （TO可省略不写，不影响）
| CHARACTER SET <字符集名>        #修改字符集（表的编码格式等）
| COLLATE <校对规则名> }        字符集内用于字符比较和排序的一套规则

联合唯一约束

create table blog_agree
(
userid varchar(100) not null,
blogid varchar(100) not null,
foreign key(userid) references `user`(userid),
foreign key(blogid) references blog(blogid),
unique(userid,blogid)
)

触发器

查看所有触发器

show triggers;

删除触发器

drop trigger user_insert_before;

创建触发器

-- 创建触发器user_insert_before，用户信息必须符合以下条件，否则插入失败。条件如下：
-- 1. 性别只能为：null、0、1。
-- 2. 生日不能大于当天。
-- 3. 注册时间不能大于当前。
create trigger user_insert_before before insert on `user` for each row
begin
	if new.sex !=0 
	or new.sex!=1 
	or new.sex is not null
	or new.birthday > now() 
	or new.createdate > now() then
		insert into errortable values(0);
	end if;
end;

insert into user(username,nickname,email,phone,`password`,createdate,avatar,sex,birthday,address) values
(null,'小贩','1778211090@qq.com',13454133320,'admin','2022-6-1','http://heiza.work/avatar.png',1,'2000-2-5','山东省滨州市惠民县')

-- 创建触发器blog_insert_before，录入博客信息时，浏览量不能为负数，否则插入失败。
create trigger blog_insert_before before insert on blog for each row
begin
	if new.title is null or new.look < 0 then
		insert into errortable values(0);
	end if;
end;

show triggers;
drop trigger blog_insert_before;
insert into blog(title,content,look) values
('《望庐山瀑布》 [唐] 李白','日照香炉生紫烟，遥看瀑布挂前川。飞流直下三千尺，疑是银河落九天。',-1)

函数

-- 创建一个函数get_user_age，传入用户id，返回用户当前年龄。
create function get_user_age(uid varchar(100)) returns int
reads sql data
begin
	declare age int;
	select year(now())-year(birthday) into age from user where userid = uid;
	return age;
end;
select get_user_age('db5c22fee48311eca808b025aa2e1a1b');

-- 创建一个函数get_blog_agree_count，传入博文id，返回博文的点赞数。
create function get_blog_agree_count(bid varchar(100)) returns bigint
reads sql data
begin
	declare agree_count bigint;
	select count(*) into agree_count from blog_agree where blogid = bid;
	return agree_count;
end;
select get_blog_agree_count('2f07108ee48511eca808b025aa2e1a1b');

-- 创建一个函数get_comment_agree_count，传入评论id，返回评论的点赞数。
create function get_comment_agree_count(cid varchar(100)) returns bigint
reads sql data
begin
	declare agree_count bigint;
	select count(*) into agree_count from comment_agree where commentid = cid;
	return agree_count;
end;
select get_comment_agree_count('021a265de48b11eca808b025aa2e1a1b');

-- 创建一个函数get_comment_comment_count，传入评论id，返回评论的回复数。
create function get_comment_comment_count(cid varchar(100)) returns bigint
reads sql data
begin
	declare comment_count bigint;
	select count(*) into comment_count from comment_comment where parentid = cid;
	return comment_count;
end;
select get_comment_comment_count('0219e77be48b11eca808b025aa2e1a1b');

-- 创建一个函数get_blog_comment_count，传入博文id，返回博文的评论数。
create function get_blog_comment_count(bid varchar(100)) returns bigint
reads sql data
begin
	declare blog_comment_count bigint;
	declare comment_comment_count bigint;
	select count(*) into blog_comment_count from blog_comment where blogid = bid;
	select count(*) into comment_comment_count from blog_comment join comment_comment on blog_comment.commentid = comment_comment.parentid;
	return blog_comment_count+comment_comment_count;
end;
select get_blog_comment_count('2f06ec42e48511eca808b025aa2e1a1b');

存储过程

-- 创建一个存储过程agree_blog_list，传入用户id，返回点赞的博文列表。
create procedure agree_blog_list(uid varchar(100))
reads sql data
begin
	select * from blog join blog_agree on blog.blogid = blog_agree.blogid where blog_agree.userid=uid;
end;
call agree_blog_list('db5c22fee48311eca808b025aa2e1a1b');
-- 创建一个存储过程user_blog_list，传入用户id，返回该用户发表的博文列表。
create procedure user_blog_list(uid varchar(100))
reads sql data
begin
	select * from blog join blog_add on blog.blogid = blog_add.blogid where blog_add.userid=uid;
end;
call user_blog_list('db5c22fee48311eca808b025aa2e1a1b');

视图

-- 创建视图view_user_info，查看用户信息，包含当前年龄，不包含密码信息。
create view view_user_info as select userid,username,nickname,email,phone,avatar,sex,birthday,address,createdate,get_user_age(userid) as age from user;
select * from view_user_info;

-- 创建视图view_blog_info，查看博文信息，包含点赞量和评论数。
create view view_blog_info as select blogid,title,content,look,get_blog_agree_count(blogid) as agree_count,get_blog_comment_count(blogid) as comment_count,createdate from blog;
select * from view_blog_info;
-- 创建视图view_blog_hot_list，查看点赞量大于100的文章列表。
create view view_blog_hot_list as select * from blog where get_blog_agree_count(blogid)>100;
select * from view_blog_hot_list;

-- 创建视图view_blog_hotlook_list，查看浏览量大于100的文章列表。
create view view_blog_hotlook_list as select * from blog where look>100;
select * from view_blog_hotlook_list;