18 . Go之操作Mysql
2024-09-06 12:47:33
安装mysql
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
yum -y localinstall mysql57-community-release-el7-8.noarch.rpm
yum install mysql-community-server -y
systemctl start mysqld
grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'ZHOUjian.22';
grant all privileges on *.* to admin@"%" identified by 'ZHOUjian.21' withth grant option;
# 授权test用户有testdb数据库某一部分权限
grant select,update on testDB.* to test@'%' identified by 'ZHOUjian.22';
# 授权test用户拥有所有数据库所有权限
grant all on *.* to 'test'@'%' identified by 'ZHOUjian.22';
flush privileges;
Mysql常用库操作
# 创建数据库
mysql> create database myblog1 charset=utf8;
# 查询数据库
mysql> show databases;
# 查看数据库
mysql> show create database myblog;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| myblog | CREATE DATABASE `myblog` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
# 删除数据库
mysql> drop database myblog1;
Mysql常用表操作
# 进入Myblog数据库
mysql> use myblog;
# 创建表
mysql> create table name (
-> colname coltype 修饰,
-> ) engine=inndb default charset=utf8mb4;
colname: 列名, 大小写英文字母,数字,_组成
coltype: 数值类型(int bigint float double decimal(m,n))
字符串类型(varchar(n), char(n))
时间类型(data,datetime,time)
文本类型(text 64k,mediumtext 16M,longtext 4G)
二进制类型(blob,longblob)
json/array()
# 修饰:
# 主键: primary key
# 唯一: unique
# 自动增长: auto_increment
# 默认值: default 0,default ''
# 是否允许为Null, 不允许为NULL ,NOT NULL
# 注释: COMMENT ''
# 索引
index
创建表Example1
create table task(
id bigint primary key auto_increment,
name varchar(64) not null default '' comment '任务名称',
status int not null default 0 comment '状态,0: 新建, 1: 正在执行, 2: 停止, 3: 完成',
start_time datetime comment '开始时间',
complete_time datetime comment '完成时间',
deadline_time datetime not null comment '截止时间',
content text comment '任务详情',
index idx_name (name),
index deadline_time(deadline_time)
)engine=innodb default charset utf8mb4 auto_increment 1000;
# 查看表结构
mysql> desc task;
# 删除表
mysql> drop table name;
# 修改表
mysql> alter table name 动作;
# 只允许添加列
alter table name add column colname coltype 修饰;
# 删除列
alter table name drop column colname;
# 修改列
alter table name modify column colname coltype 修饰;
# 索引
create index name on table (column,column2,...);
drop index name on table (column,column2);
create unique index name on table (column,column2,...);
表数据操作
# 增 insert into table(c1,c2,c3,c4) values(v1,v2,v3,v4);
mysql> insert into task(name,content,deadline_time) values('写作业','完成todulist web', '2020-06-27 15:20:00');
# 删
# 改
# 查
mysql> select * from task;
+------+-----------+--------+------------+---------------+---------------------+--------------------+
| id | name | status | start_time | complete_time | deadline_time | content |
+------+-----------+--------+------------+---------------+---------------------+--------------------+
| 1000 | 写作业 | 0 | NULL | NULL | 2020-06-27 15:20:00 | 完成todulist web |
+------+-----------+--------+------------+---------------+---------------------+--------------------+
# 指定列查询
mysql> select name,status,start_time from task;
+-----------+--------+------------+
| name | status | start_time |
+-----------+--------+------------+
| 写作业 | 0 | NULL |
+-----------+--------+------------+
# 条件查询
mysql> select name,status,start_time from task where name='写作业';
+-----------+--------+------------+
| name | status | start_time |
+-----------+--------+------------+
| 写作业 | 0 | NULL |
| 写作业 | 0 | NULL |
+-----------+--------+------------+
# 逻辑关系
与 and
或 or colname=v1 or colname = v2
非 not
c1 = v1 and (c2 = v2 or c3 = v3)
删 delete from table;
delete from table where 条件;
# 改
update table
set colname = v1, col2 = v2 ,col3 = v3;
mysql> update task set status=1;
内置函数
# 获取当前时间
select now();
# 获取md5('1');
select md5('1')
# date_format(time,layout)
查询
insert into task(name,content,deadline_time) values('1','1','2020-05-20'),
('1','1','2020-05-21'),
('2','1','2020-05-22'),
('3','1','2020-05-23'),
('4','1','2020-05-24'),
('5','1','2020-05-25'),
('6','1','2020-05-26');
# 数量
count(*)
count(id)
mysql> select count(*) from task where status=1;
# as 别名
#
# 排序
order by colname [asc | desc],col2 [asc|desc]
mysql> select * from task order by deadline_time;
# 分页
# 展示多少条数据 每页的数据量
# 展示第几页 页面
# limit 限制查询数量
# offset 设置便宜
# 每页展示5条 limit 5
# 第一页offset 0
# 第二页offset 5
mysql> select * from task order by deadline_time limit 5 offset 0;
# 分组
# IP time url status_code
# ip 出现的次数
# status_code出现的次数
# url,status_code出现的次数
# ip url status_code出现次数
# group by
select [] from table group by colname,colname2 [having 过滤条件;]
# select 元素必须是指定分组的列名或聚合类
mysql> select status,count(*) from task group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
| 0 | 7 |
| 1 | 2 |
+--------+----------+
create table accesslog(
id bigint primary key auto_increment,
logtime datetime not null comment '访问时间',
ip varchar(128) not null default '' comment '访问来源',
url varchar(4096) not null default '' comment '访问地址',
status int not null default 0 comment '状态码'
) engine=innodb default charset utf8mb4;
insert into accesslog(logtime,ip,url,status) values
('2020-06-05 05:20:00','1.1.1.1','/index',200),
('2020-06-06 05:20:00','1.1.1.2','/home',302),
('2020-06-07 05:20:00','1.1.1.3','/test.txt',404),
('2020-06-08 05:20:00','1.1.1.4','/backup.sh',404),
('2020-06-09 05:20:00','1.1.1.5','/login',200),
('2020-06-10 05:20:00','1.1.1.6','/register',200),
('2020-06-15 05:20:00','1.1.1.7','/test',200),
('2020-06-13 05:20:00','1.1.1.8','/upload.php',200),
('2020-06-15 05:20:00','1.1.1.9','/index',200);
# ip出现次数
mysql> select ip,count(*) from accesslog group by ip;
+---------+----------+
| ip | count(*) |
+---------+----------+
| 1.1.1.1 | 1 |
| 1.1.1.2 | 1 |
| 1.1.1.3 | 1 |
| 1.1.1.4 | 1 |
| 1.1.1.5 | 1 |
| 1.1.1.6 | 1 |
| 1.1.1.7 | 1 |
| 1.1.1.8 | 1 |
| 1.1.1.9 | 1 |
+---------+----------+
# status_code出现次数
mysql> select status,count(*) from accesslog group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
| 200 | 6 |
| 302 | 1 |
| 404 | 2 |
+--------+----------+
# ip url status_code出现次数
mysql> select url,status,count(*) from accesslog group by url,status;
+-------------+--------+----------+
| url | status | count(*) |
+-------------+--------+----------+
| /backup.sh | 404 | 1 |
| /home | 302 | 1 |
| /index | 200 | 2 |
| /login | 200 | 1 |
| /register | 200 | 1 |
| /test | 200 | 1 |
| /test.txt | 404 | 1 |
| /upload.php | 200 | 1 |
+-------------+--------+----------+
# ip访问次数大于等于1
mysql> select ip,count(*) from accesslog group by ip having count(*)>=1;
+---------+----------+
| ip | count(*) |
+---------+----------+
| 1.1.1.1 | 1 |
| 1.1.1.2 | 1 |
| 1.1.1.3 | 1 |
| 1.1.1.4 | 1 |
| 1.1.1.5 | 1 |
| 1.1.1.6 | 1 |
| 1.1.1.7 | 1 |
| 1.1.1.8 | 1 |
| 1.1.1.9 | 1 |
+---------+----------+
# 每一天的访问量
mysql> select date_format(logtime,'%Y-%m-%d') as log_day,count(*) as cnt from accesslog group by log_day;
+------------+-----+
| log_day | cnt |
+------------+-----+
| 2020-06-05 | 18 |
+------------+-----+
mysql> select date_format(logtime,'%Y-%m-%d') as log_day,count(*) as cnt from accesslog group by log_day order by cnt desc;
create table score(
id bigint primary key auto_increment,
day date not null comment '日期',
name varchar(32) not null default '' comment '姓名',
score float not null default 0 comment '分数'
)engine=innodb default charset utf8mb4;
insert into score(day,name,score) values
('2020-05-20','youmen',2),
('2020-05-21','xiaobai',3),
('2020-05-22','haba',4),
('2020-05-23','wunai',5),
('2020-05-24','Amaris',6),
('2020-05-25','Cassiel',6),
('2020-05-26','Gina',6),
('2020-05-27','Deirdre',6);
# 求和
mysql> select name,sum(score) from score group by name;
+---------+------------+
| name | sum(score) |
+---------+------------+
| Amaris | 6 |
| Cassiel | 6 |
| Deirdre | 6 |
| Gina | 6 |
| haba | 4 |
| wunai | 5 |
| xiaobai | 3 |
| youmen | 2 |
+---------+------------+
# 最小值,最大值,平均值
mysql> select name,sum(score),max(score),min(score),avg(score) from score group by name;
+---------+------------+------------+------------+------------+
| name | sum(score) | max(score) | min(score) | avg(score) |
+---------+------------+------------+------------+------------+
| Amaris | 6 | 6 | 6 | 6 |
| Cassiel | 6 | 6 | 6 | 6 |
| Deirdre | 6 | 6 | 6 | 6 |
| Gina | 6 | 6 | 6 | 6 |
| haba | 4 | 4 | 4 | 4 |
| wunai | 5 | 5 | 5 | 5 |
| xiaobai | 3 | 3 | 3 | 3 |
| youmen | 2 | 2 | 2 | 2 |
+---------+------------+------------+------------+------------+
# 每一天的总分
# 联查
# 多张表进行查询数据
join
left join on
inner join on
right join on
create table user(
id bigint primary key auto_increment,
name varchar(32) not null default '',
status int not null default 0 comment '0:在职,1:离职'
)engine=innodb default charset utf8mb4;
alter table task add column user bigint;
insert into user(name,status) values
('youmen',0),
('幽梦',1),
('wunai',0);
insert into task(name,content,deadline_time,user) values
('完成web任务','',now(),1),
('打游戏','',now(),0),
('吃饭','',now(),1),
('睡觉','',now(),2);
# 每个人(名字)未完成的任务
配置数据库驱动
数据库编程属于beego中的Models层,也称为ORM模块
在beego中,目前支持三种数据库驱动,分别是:
// MySQL： github.com/go-sql-driver/mysql
// PostgreSQL：github.com/lib/pq
// Sqlite3：github.com/mattn/go-sqlite3
go get github.com/go-sql-driver/mysql
beego中的ORM所具备的几个特性
// 1. 支持G语言的所有类型存储
// 2. CRUD操作简单
// 3. 自动Join关联表
// 4. 允许直接使用SQL查询
最新文章
- 测试框架Mocha与断言expect
- HttpCookie加匿名类实现多语言
- 【原】iOS学习38网络之数据解析
- C#实现XML文件数据库存储
- [改善Java代码]适当设置阻塞队列长度
- SQLite入门与分析(三)---内核概述(1)
- ios专题 － APP设计流程
- 【stm32】实现STM32的串口数据发送和printf函数重定向
- Unhandled Exxception “Unhandled exception type IOException”?
- c++，operator=
- hdu 1712 (分组背包入门)
- Some 3D Graphics (rgl) for Classification with Splines and Logistic Regression (from The Elements of Statistical Learning)（转）
- 快速搭建一个Fabric 1.0的环境
- activemq修改端口
- 【版本更新】开发工具DevExtreme发布v18.2.7|附下载
- LeetCode算法题-Find All Anagrams in a String（Java实现）
- Step2：SQL Server 复制事务发布
- 简单介绍RPM包制作方法
- FileSystemResource在Srping FrameWork 5中的变化
- Activity的setResult方法