mysql教程-我自己的学习过程

1.数据库学习之路：

1.安装mysql 只需执行

sudo apt-get install mysql-server

即可

2.安装后，一般的默认密码是root。但是用普通用户登录时，会出现如下报错：

~$ mysql -u root -p

Enter password:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

 

需要切换到sudo su，即root权限才可以用

mysql -u root -proot

这个密码登录上。

登录上之后，我们需要查看一下user表，错误的起因就是在这里， root的plugin被修改成了auth_socket，用密码登陆的plugin应该是mysql_native_password。

 

我们想以普通用户正常登录的话，需要把这里改了。执行：

mysql> update mysql.user set authentication_string=PASSWORD('newPwd'), plugin='mysql_native_password' where user='root';

 

然后重启数据库，问题就解决了。

~$ sudo service mysql stop

...

 * MySQL Community Server 5.7.10 is stopped

~$ sudo service mysql start

..

 * MySQL Community Server 5.7.10 is started

~$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.10 MySQL Community Server (GPL)

3.一些常用命令：

create database test_db;

show databases;

use databases;

create table tb_emql

(

id INT,

name VARCHAR(25),

salary FLOAT

);

 

show tables;

desc tb_emql;

alter table tb_emql modify id float;//修改表的id字段的数据类型。

insert into tb_emql (id,name,salary) values (1,’zhangsan’,10.5),

(2,’lisi’,15);

精简版写法：

insert into tb_emql values (3,’liubei’,23);

update tb_emql set name=’张三’,salary=15 where id=1;

delete from tb_emql where id>1;

select * from tb_emql;

select * from tb_emql where id>1;

select id,name from tb_emql;

 

2.

 

数据库中文乱码问题解决：

在使用mysql时，我们有时候会遇到这样一个报错：

Mysql：ERROR 1366 (HY000): Incorrect string value

这个是中文乱码的问题，就是说你输入了中文，但是目前mysql的编码方式并不认识中文。

解决方案：

1.查看表字符编码

Show create table tb_lock;

 

我们可以看到表的默认字符集是utf8;

所以我们在创建表的时候就需要指定表的字符集:

 create table user(name varchar(11)) default charset=utf8;

这样在Linux里面可以访问并且可以插入与访问这个表了。

 

2.数据库与操作系统编码

虽然在服务器端可以显示中文正常，但是在客户端可能会显示乱码。因为我们的服务器是UTF8。

 

而且数据库的编码也存在问题。

 

这里我们可以看character_sert_database与character_set_server的字符集都是latin1.那么在mysql数据库中，server,database,table的字符集都默认是latin1.下面我们就来看看如何解决mysql乱码情况。

3.mysql设置变量的范围

查看数据库编码：

show variables like '%char%';

• 
   

 

修改字符编码：

set character_set_server=utf8;

set character_set_database=utf8;

show variables like '%char%';

 

我们可以看到字符集已经修改成都是utf8了。但是这里有一个问题，那就是我们重新打开一个命令窗口时，它们就会恢复原状。

所以终极大招还是需要改mysql配置文件：

怎么修改MySQL的配置呢？网上的很多版本都在讲找/etc/my.cnf，或者/etc/mysql/my.cnf进行配置。ls /etc/my.cnf没有结果，/etc/mysql/my.cnf倒是有，通过sudo vim /etc/mysql/my.cnf打开一瞧，啥都木有啊。

 

去这2个目录下找找呗，恩，上天垂爱，原来真正的配置文件在/etc/mysql/mysql.conf.d的目录下，就是mysqld.cnf文件，执行命令sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

 

[mysqld]

character-set-server=utf8

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

 

把这3行加进去，注意位置哦。

 

而且我们创建表的时候不需要指定字符编码,它默认就是utf8;

 

3.

mysql修改字段类型： 

--能修改字段类型、类型长度、默认值、注释

--对某字段进行修改

ALTER  TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 新类型长度  新默认值  新注释;

 -- COLUMN关键字可以省略不写

 

alter  table table1 modify  column column1  decimal(10,1) DEFAULT NULL COMMENT '注释';

-- 正常，能修改字段类型、类型长度、默认值、注释

 

alter  table table1 modify column1  decimal(10,2) DEFAULT NULL COMMENT '注释';

-- 正常，能修改字段类型、类型长度、默认值、注释

 

mysql修改字段名：

ALTER  TABLE 表名 CHANGE [column] 旧字段名 新字段名 新数据类型;      

alter  table table1 change column1 column1 varchar(100) DEFAULT 1.2 COMMENT '注释';

-- 正常，此时字段名称没有改变，能修改字段类型、类型长度、默认值、注释

alter  table table1 change column1 column2 decimal(10,1) DEFAULT NULL COMMENT '注释'

-- 正常，能修改字段名、字段类型、类型长度、默认值、注释

alter  table table1 change column2 column1 decimal(10,1) DEFAULT NULL COMMENT '注释'

-- 正常，能修改字段名、字段类型、类型长度、默认值、注释

alter  table table1 change column1 column2;

-- 报错

 

mysql> alter table white_user change column name nick_name  varchar(50) null comment '昵称';

-- 正确

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

解决MySql 报错：ERROR 1366 (HY000): Incorrect string value....

1.向表testtb表中插入中文时，报错：ERROR 1366 (HY000): Incorrect string value....；

2.之前修改字符集的编码为utf8。

(show varialbles like '%char%'里面查出来的一些字段设置为utf8);

3.把表的字符集也改下，改成与你一样的字符集.命令:alter table testtb convert to charset utf8;

 

5.数据库之ORM

为了提高效率，一般是不会直接用sql语句去操作数据库的，这时候就出现了一个框架叫做ORM，使用ORM可以方便的操作数据库，提高开发效率。

Go语言开发常用的ORM框架有xorm,gorm等。

Gorm常用命令：

1.创建

插入记录

// 相当于insert into users(name,age,brithday) values("BGBiao",18,time.Now())

user := User{Name: "BGBiao", Age: 18, Birthday: time.Now()}

// 主键为空返回`true`

db.NewRecord(user)

db.Create(&user)

// 创建`user`后返回`false`

db.NewRecord(user)

2.查询

基本查询

// 根据主键查询第一条记录

// SELECT * FROM users ORDER BY id LIMIT 1;

db.First(&user)

 

// 随机获取一条记录

// SELECT * FROM users LIMIT 1;

db.Take(&user)

 

// 根据主键查询最后一条记录

// SELECT * FROM users ORDER BY id DESC LIMIT 1;

db.Last(&user)

 

// 查询所有的记录

// SELECT * FROM users;

db.Find(&users)

 

// 查询指定的某条记录(仅当主键为整型时可用)

// SELECT * FROM users WHERE id = 10;

db.First(&user, 10)

 

结构体方式查询

// 结构体方式

// select * from users where name = 'bgbiao.top'

db.Where(&User{Name: "bgbiao.top", Age: 20}).First(&user)

 

// Map方式

// select * from users where name = 'bgbiao.top' and age = 20;

db.Where(map[string]interface{}{"name": "bgbiao.top", "age": 20}).Find(&users)

 

// 主键的切片

// select * from users where id in (20,21,22);

db.Where([]int64{20, 21, 22}).Find(&users)

 

where条件查询

ps：使用了where方法，基本就是sql语法了。

// 使用条件获取一条记录 First()方法

db.Where("name = ?", "bgbiao.top").First(&user)

 

// 获取全部记录 Find()方法

db.Where("name = ?", "jinzhu").Find(&users)

 

// 不等于

db.Where("name <> ?", "jinzhu").Find(&users)

 

// IN

db.Where("name IN (?)", []string{"jinzhu", "bgbiao.top"}).Find(&users)

 

// LIKE

db.Where("name LIKE ?", "%jin%").Find(&users)

 

// AND

db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)

 

// Time

// select * from users where updated_at > '2020-03-06 00:00:00'

db.Where("updated_at > ?", lastWeek).Find(&users)

 

// BETWEEN

// select * from users where created_at between '2020-03-06 00:00:00' and '2020-03-14 00:00:00'

db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

 

3.更新

更新所有字段

Ps：save会更新所有字段，即使你没有赋值

db.First(&user)

 

user.Name = "bgbiao.top"

user.Age = 100

// update users set name = 'bgbiao.top',age=100 where id = user.id

db.Save(&user)

 

更新修改字段

使用update和updates方法

// 更新单个属性，如果它有变化

// update users set name = 'hello' where id = user.id

db.Model(&user).Update("name", "hello")

 

// 根据给定的条件更新单个属性

// update users set name = 'hello' where active = true

db.Model(&user).Where("active = ?", true).Update("name", "hello")

 

// 使用 map 更新多个属性，只会更新其中有变化的属性

// update users set name = 'hello',age=18,actived=false where id = user.id

db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})

 

// 使用 struct 更新多个属性，只会更新其中有变化且为非零值的字段

db.Model(&user).Updates(User{Name: "hello", Age: 18})

 

// 警告：当使用 struct 更新时，GORM只会更新那些非零值的字段

// 对于下面的操作，不会发生任何更新，"", 0, false 都是其类型的零值

db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

 

6.mysql导出为excel

在实际的使用中，经常需要将mysql数据导出为excel表格数据。需要执行下面的指令：

 

mysql>select * from xi_table into outfile ’/tmp/test.xls’;

 

但是一般来说，当你第一次运行的时候，会出现这样一个报错：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。

提示你的secure_file_priv参数设置的不对。可以查看一下具体的值是多少：

 

查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。

 

secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。

secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。

secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

 

查看 secure_file_priv 的值，默认为NULL，表示限制不能导入导出。

 

mysql> show global variables like '%secure_file_priv%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| secure_file_priv | NULL  |

+------------------+-------+

1 row in set (0.00 sec)

我们决定把它设置为没有值。而因为 secure_file_priv 参数是只读参数，不能使用set global命令修改，所以只能改动配置文件my.cnf了。

加一行secure_file_priv=''就可以啦。

加上之后重启mysql就能生效。这个时候你就可以把你自己的mysql结果导出到各个excel表里了。

 

 

注意：这里有一个关于中文的点。

在mysql导出为表格后，用excel打开时，其中中文的部分会成为乱码。想要解决这个问题，我目前的方案是，用notepad++打开，然后转为ansi编码。这样的话再用excel打开时，就会是正常的中文了。

 

导出表格时带表头：

 select * from (select 'name','age' union select name,age from test) b;

ps:最后这个b可以随便起名。test是表的名字。

7.将已存在的字段设置为主键并自增长

1.    alter table tb_lock add primary key(id);

2.    alter table tb_lock modify id int auto_increment;

3.    alter table tb_cabinet auto_increment = 1;//将自增主键设置为一个特定的值

 

8.在已有的数据表中增加两个字段，

一个是createtime，一个是updatetime.

alter table tb_cabinet add createtime datetime DEFAULT CURRENT_TIMESTAMP;

alter table tb_cabinet add updatetime datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

 

9.修改mysql时区

 

10．统计数据表中每小时的平均值、最大值

正确的：

select date_format(updatetime,'%Y-%m-%d %H:00:00') as time,avg(unlock_delay),max(unlock_delay),count(*) as num from tb_cabinet where lock_status = 2 and door_status =1 group by time order by time;

 

查询一张表的多个count时，即聚合查询时，需要这样写

select date_format(updatetime,'%Y-%m-%d %H:00:00') as time,avg(unlock_delay),max(unlock_delay),count(*) as only_unlock_num ,(select count(*) from tb_cabinet where lock_status=2 and door_status=2) as open_door_num from tb_cabinet where lock_status = 2 and door_status =1 group by time;

 

导出文件：

select date_format(updatetime,'%Y-%m-%d %H:00:00') as time,avg(unlock_delay),max(unlock_delay),count(*) as only_unlock_num ,(select count(*)from tb_cabinet where lock_status=2 and door_status=2) as open_door_num from tb_cabinet where lock_status = 2 and door_status =1 group by time into outfile '/tmp/cabinet.xls';

 

 

 

11.将查询结果导出到新表

 

12.整合一下操作指令：

删除旧表：

drop table tmp_db;

 

将查询结果存为新表：

create table tmp_db as select date_format(updatetime,'%Y-%m-%d %H:00:00') as time,avg(unlock_delay),max(unlock_delay),count(*) as only_unlock_num ,(select count(*) from tb_cabinet where lock_status=2 and door_status=2) as open_door_num from tb_cabinet where lock_status = 2 and door_status =1 group by time;

 

导出新表带表头：

select * from (select 'time','avg(unlock_delay)','max(unlock_delay)','only_unlock_num','open_door_num' union select `time`,`avg(unlock_delay)`,`max(unlock_delay)`,only_unlock_num,open_door_num from tmp_db) b into outfile '/tmp/cabinet.xls';

 

sudo mv /tmp/cabinet.xls ~/lixing/sharefolder/mysqlstudy/