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