18 . Go之操作Mysql

men發表於2020-08-03

安裝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查詢

相關文章