社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
一、安装MySQL
1.下载MySQL:点击链接下载https://dev.mysql.com/downloads/file/?id=484900,然后点击no,thanks,获得ZIP Archive版是免安装的。只要解压就行了。和安装版的没什么不同,但就是不需要安装。找到下载好的mysql-x.x.xx-winx64.zip右键选择(解压到...),找到你想安装的地方。
2.设置配置文件:在mysql-x.x.xx-winx64目录下,新建个my.ini。复制下面的代码保存就ok了。my.ini会替换掉下面的my-default.ini文件。.ini文件是window里面的配置文件。保存里面各种默认的数据。安装版是在安装的时候让你自己选然后系统给你保存进来,zip archive是自己写,都一样。
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:mysqlmysql-5.6.17-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:mysqlmysql-5.6.17-winx64data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
3.安装与启动数据库服务:管理员身份打开cmd窗口后,将目录切换到你解压文件的bin目录,再输入mysqld install回车运行就行了,注意是是mysqld,不是mysql出现安装成功就ok。接着就是在输入 net start mysql 启动服务。或者不嫌麻烦就手动,打开服务手动启动了mysql服务。
4.启动数据库:将目录切换到F:mysqlmysql-x.x.xx-winx64bin(x.xx.xxx是什么根据你自己的版本来吧)输入mysql -uroot -p ,默认是没有密码的。回车进入,当然我的已经设置密码了,所以要输入密码才能进。有密码的话,可以mysql -uroot -p密码,也是可以是mysql -uroot -p 回车输入密码,推荐第二种,原因,你动手之后就知道了。设置密码的方法:mysqladmin -u root -p password 密码。退出exit 就行了。记住直接关闭cmd窗口是没有退出的,要输入exit才会退出啊。
5.设置环境变量:右键我的电脑->属性->高级系统设置->环境变量->path->编辑,将你的mysql软件下的bin目录的全路径放里面,最后在那个目录的路径后面加个英文的分号(;)保存就行了。如F:mysqlmysql-x.x.xx-winx64bin;
6.测试:cmd->mysql -uroot -p ->回车输入密码->show databases;查找数据库->use 数据库名;切换数据库目录->show tables;查找表->sql查询工作select * from 表名->exit退出,检查当前MySQL版本:mysqladmin --version;关闭已经运行的 MySQL 服务器:mysqladmin -u root -p shutdown。
7.可选:mysql瘦身,下载mysql压缩包的时候只有200M多一点,可解压只有竟然达到竟然的1G多,mysql是开源的数据库,里面的源文件和debug等文件都没有删掉,这些文件其实没有什么用的,都删掉就是了。删掉dubug文件和.pdf文件。当然不止这一个目录里面有,删了之后是不是发现小了特别多!
二、MySQL管理
1.启动MySQL服务;net start mysql;连接MySQL:mysql -u root -p;进入MySQL数据库:use mysql;创建一个新用户:
方法:
grant usage on *.* to 'username'@'hostname' identified by 'passwd' with grant option; //添加用户
grant all privileges on *.* to 'username'@'hostname' identified by 'passwd'; //添加权限
flush privileges; //更新权限
示例:GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON TUTORIALS.* TO 'liu'@'localhost' IDENTIFIED BY '12345678';刷新用户权限:FLUSH PRIVILEGES;对密码进行加密:SELECT host, user,authentication_string FROM user WHERE user = 'liu';展示当前用户权限:show grants for 'liu'@'localhost';
注:在 MySQL5.7 中 user 表的 password 已换成了authentication_string。password() 加密函数已经在 8.0.11 中移除了,可以使用 MD5() 函数代替。在注意需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表,否则需要重启MySQL服务。
2.Linux文件下:/etc/my.cnf 文件配置,一般情况下,你不需要修改该配置文件,该文件默认配置如下:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
在配置文件中,你可以指定不同的错误日志文件存放的目录,一般你不需要改动这些配置。
3.管理MySQL的命令:
USE 数据库名 :选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库。
SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表。
SHOW TABLES:显示指定数据库的所有表,使用该命令前需要使用 use 命令来选择要操作的数据库。
SHOW COLUMNS FROM 数据表:显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。
SHOW INDEX FROM 数据表:显示数据表的详细索引信息,包括PRIMARY KEY(主键)。
SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] G: 该命令将输出Mysql数据库管理系统的性能及统计信息。
mysql> SHOW TABLE STATUS FROM RUNOOB; # 显示数据库 RUNOOB 中所有表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'; # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'G # 加上 G,查询结果按列打印
三、数据库常见命令
1. RUNOOB数据库创建:create DATABASE RUNOOB,使用root用户登录,root用户拥有最高权限,可以使用 mysql mysqladmin 命令来创建数据库。以下命令简单的演示了创建数据库的过程,数据库名为 RUNOOB: mysqladmin -u root -p create RUNOOB。
2.删除数据库RUNOOB:删除名为 RUNOOB 的数据库: drop database RUNOOB;也可以使用 mysql mysqladmin 命令在终端来执行删除命令。以下实例删除数据库 RUNOOB:mysqladmin -u root -p drop RUNOOB;
3.选择数据库RUNOOB:use RUNOOB;所有的数据库名,表名,表字段都是区分大小写的。所以你在使用SQL命令时需要输入正确的名称。
四、MySQL 数据类型
1、整型
MySQL数据类型 | 含义(有符号) |
tinyint(m) | 1个字节 范围(-128~127) |
smallint(m) | 2个字节 范围(-32768~32767) |
mediumint(m) | 3个字节 范围(-8388608~8388607) |
int(m) | 4个字节 范围(-2147483648~2147483647) |
bigint(m) | 8个字节 范围(+-9.22*10的18次方) |
取值范围如果加了 unsigned,则最大值翻倍,如 tinyint unsigned 的取值范围为(0~255)。int(m) 里的 m 是表示 SELECT 查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个 m 有什么用。
2、浮点型(float 和 double)
MySQL数据类型 | 含义 |
float(m,d) | 单精度浮点型 8位精度(4字节) m总个数,d小数位 |
double(m,d) | 双精度浮点型 16位精度(8字节) m总个数,d小数位 |
设一个字段定义为 float(5,3),如果插入一个数 123.45678,实际数据库里存的是 123.457,但总个数还以实际为准,即 6 位。
3、定点数
浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数 m<65 是总个数,d<30 且 d<m 是小数位。
4、字符串(char,varchar,_text)
MySQL数据类型 | 含义 |
char(n) | 固定长度,最多255个字符 |
varchar(n) | 可变长度,最多65535个字符 |
tinytext | 可变长度,最多255个字符 |
text | 可变长度,最多65535个字符 |
mediumtext | 可变长度,最多2的24次方-1个字符 |
longtext | 可变长度,最多2的32次方-1个字符 |
char 和 varchar:
varchar 和 text:
5.二进制数据(_Blob)
6.日期时间类型
MySQL数据类型 | 含义 |
date | 日期 '2008-12-2' |
time | 时间 '12:25:36' |
datetime | 日期时间 '2008-12-2 22:06:44' |
timestamp | 自动存储记录修改时间 |
若定义一个字段为timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。
7.数据类型的属性
MySQL关键字 | 含义 |
NULL | 数据列可包含NULL值 |
NOT NULL | 数据列不允许包含NULL值 |
DEFAULT | 默认值 |
PRIMARY KEY | 主键 |
AUTO_INCREMENT | 自动递增,适用于整数类型 |
UNSIGNED | 无符号 |
CHARACTER SET name | 指定一个字符集 |
注:一个汉字占多少长度与编码有关:UTF-8:一个汉字=3个字节、GBK:一个汉字=2个字节;
varchar(n) 表示 n 个字符,无论汉字和英文,Mysql 都能存入 n 个字符,仅是实际字节长度有所区别
MySQL 检查长度,可用 SQL 语言来查看:select LENGTH(fieldname) from tablename
五、数据表管理
1.MySQL 创建数据表,创建MySQL数据表需要以下信息:表名、表字段名、定义每个表字段。以下为创建MySQL数据表的SQL通用语法:CREATE TABLE table_name (column_name column_type);在 RUNOOB 数据库中创建数据表runoob_tbl:
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.删除数据表:MySQL中删除数据表是非常容易操作的, 但是你再进行删除表操作时要非常小心,因为执行删除命令后所有数据都会消失。语法:以下为删除MySQL数据表的通用语法:DROP TABLE table_name ;命令提示窗口中删除数据表:mysql> DROP TABLE :以下实例删除了数据表runoob_tbl:DROP TABLE runoob_tbl;
3.MySQL 表中使用 INSERT INTO SQL语句来插入数据。以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
如果数据是字符型,必须使用单引号或者双引号,如:"value"。将向 runoob_tbl 表插入三条数据:
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 PHP", "菜鸟教程", NOW());
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 MySQL", "菜鸟教程", NOW());
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
我们并没有提供 runoob_id 的数据,因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以,该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数,该函数返回日期和时间。接下来我们可以通过以下语句查看数据表数据:select * from runoob_tbl;INSERT 插入多条数据:INSERT INTO table_name (field1, field2,...fieldN) VALUES (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;
4.MySQL 数据库使用SQL SELECT语句来查询数据。
在MySQL数据库中查询数据通用的 SELECT 语法:
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
返回数据表 runoob_tbl 的所有记录:select * from runoob_tbl;
5.有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句中。SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法:
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
以下为操作符列表,可用于 WHERE 子句中。下表中实例假定 A 为 10, B 为 20
操作符 | 描述 | 实例 |
---|---|---|
= | 等号,检测两个值是否相等,如果相等返回true | (A = B) 返回false。 |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true | (A != B) 返回 true。 |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
<= | 小于等于号,检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
如果我们想在 MySQL 数据表中读取指定的数据,WHERE 子句是非常有用的。使用主键来作为 WHERE 子句的条件查询是非常快速的。如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据。将读取 runoob_tbl 表中 runoob_author 字段值为 Sanjay 的所有记录:SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的:
6.修改或更新 MySQL 中的数据,我们可以使用 SQL UPDATE 命令来操作。 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法:
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。将更新数据表中 runoob_id 为 3 的 runoob_title 字段值:UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;当我们需要将字段中的特定字符串批量修改为其他字符串时,可已使用以下操作:
UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string')
[WHERE Clause]
以下实例将更新 runoob_id 为 3 的runoob_title 字段值的 "C++" 替换为 "Python":UPDATE runoob_tbl SET runoob_title = REPLACE(runoob_title, 'C++', 'Python') where runoob_id = 3;
7.SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法:DELETE FROM table_name [WHERE Clause];
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。将删除 runoob_tbl 表中 runoob_id 为3 的记录:DELETE FROM runoob_tbl WHERE runoob_id=3;
注:delete,drop,truncate 都有删除表的作用,区别在于:
8.WHERE 子句中可以使用等号 = 来设定获取数据的条件,如 "runoob_author = 'RUNOOB.COM'"。但是有时候我们需要获取 runoob_author 字段含有 "COM" 字符的所有记录,这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。SQL LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法:
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
我们将 runoob_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录: SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM';like 匹配/模糊匹配,会与 % 和 _ 结合使用。
'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
'_a_' //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的
9.MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。MySQL UNION 操作符语法格式:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选, 检索条件。
DISTINCT: 可选,删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选,返回所有结果集,包含重复数据。
我们将使用 RUNOOB 样本数据库。创建两个新的数据表,
DROP TABLE runoob_tbl;
CREATE TABLE IF NOT EXISTS `Websites`( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, `url` VARCHAR(100) NOT NULL, `alexa` int(100), `Country`char(20) , PRIMARY KEY ( `id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;
show tables;
INSERT INTO websites (name, url,alexa,Country) VALUES ("Google ", " https://www.google.cm/",1,"USA" );
select * from websites;
INSERT INTO websites (name, url,alexa,Country) VALUES ("淘宝", " https://www.taobao.com/ ",13,"CN" );
INSERT INTO websites (name, url,alexa,Country) VALUES ("菜鸟教程", "http://www.runoob.com/ ",4689,"CN" );
INSERT INTO websites (name, url,alexa,Country) VALUES ("微博", "http://weibo.com/ ",123,"CN" );
INSERT INTO websites (name, url,alexa,Country) VALUES ("FACEBOOK", "https://www.facebook.com/",3,"USA" );
CREATE TABLE IF NOT EXISTS `apps`( `id` INT UNSIGNED AUTO_INCREMENT, `app_name` VARCHAR(100) NOT NULL, `url` VARCHAR(100) NOT NULL, `Country`char(20) , PRIMARY KEY ( `id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO apps (app_name, url,Country) VALUES ("QQ_APP", "http://im.qq.com/","CN" );
INSERT INTO apps (app_name, url,Country) VALUES ("淘宝 APP", "https://www.taobao.com/","CN" );
INSERT INTO apps (app_name, url,Country) VALUES ("微博 APP", "https://www.weibo.com/","CN" );
SELECT * FROM Websites;
SELECT * FROM apps;
SQL 语句从 "Websites" 和 "apps" 表中选取所有不同的country(只有不同的值):SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家,每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值!SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country; SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据(也有重复的值):SELECT country, name FROM Websites WHERE country='CN' UNION ALL SELECT country, app_name FROM apps WHERE country='CN' ORDER BY country;
10.对读取的数据进行排序,我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果。以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
在 SQL SELECT 语句中使用 ORDER BY 子句来读取MySQL 数据表Websites中的数据,结果将按升序及降序排列:SELECT * from Websites ORDER BY alexa ASC;SELECT * from Websites ORDER BY alexa DESC;
注:MySQL 拼音排序,如果字符集采用的是 gbk(汉字编码字符集),直接在查询语句后边添加 ORDER BY:
SELECT *
FROM runoob_tbl
ORDER BY runoob_title;
如果字符集采用的是 utf8(万国码),需要先对字段进行转码然后排序:
SELECT *
FROM runoob_tbl
ORDER BY CONVERT(runoob_title using gbk);
11.GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。GROUP BY 语法:
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
使用前我们可以先将以下数据导入数据库中。
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL DEFAULT '',
`date` datetime NOT NULL,
`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
导入成功后,执行以下 SQL 语句:
mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name | date | singin |
+----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)
接下来我们使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
3 rows in set (0.01 sec)
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:
mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | singin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+--------+--------------+
4 rows in set (0.00 sec)
其中记录 NULL 表示所有人的登录次数。我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:
select coalesce(a,b,c);
参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。以下实例中如果名字为空我们使用总数代替:
mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)
六、go操作MySQL数据库
1.我们有了基础的数据库知识,可以对数据库进行增删改查。
2.选择合适的MySQL数据库驱动:https://github.com/Go-SQL-Driver/MySQL 支持database/sql,全部采用go写,优势:
执行命令:下载:go get github.com/Go-SQL-Driver/MySQL、安装:go install github.com/Go-SQL-Driver/MySQL
3.建立数据结构:采用同一个数据库表结构:数据库test,用户表userinfo,关联用户信息表userdetail。
mysql -u root -p
create database mytest;
use mytest;
CREATE TABLE `userinfo` (
`uid` INT(10) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(64) NULL DEFAULT NULL,
`department` VARCHAR(64) NULL DEFAULT NULL,
`created` DATE NULL DEFAULT NULL,
PRIMARY KEY (`uid`)
);
CREATE TABLE `userdetail` (
`uid` INT(10) NOT NULL DEFAULT '0',
`intro` TEXT NULL,
`profile` TEXT NULL,
PRIMARY KEY (`uid`)
);
查看数据库IP: select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;查看当前数据库端口,show variables like 'port';查看当前用户:select user();查看当前数据库: select database();
4.导入数据库包:database/sql,是golang的标准库之一,它提供了一系列接口方法,用于访问关系数据库。它并不会提供数据库特有的方法,那些特有的方法交给数据库驱动去实现。我们正在加载的驱动是匿名的,将其限定符别名为_,因此我们的代码中没有一个到处的名称可见。当导入了一个数据库驱动后, 此驱动会自行初始化并注册自己到Golang的database/sql上下文中, 因此我们就可以通过 database/sql 包提供的方法访问数据库了。
import (
"database/sql"
_"github.com/Go-SQL-Driver/MySQL"
)
5.使用Open函数:func Open(driverName, dataSourceName string) (*DB, error),其中的两个参数:drvierName,这个名字其实就是数据库驱动注册到 database/sql 时所使用的名字 "mysql",dataSourceName,数据库连接信息,这个连接包含了数据库的用户名, 密码, 数据库主机以及需要连接的数据库名等信息.,用户名:密码@协议(地址:端口)/数据库?参数=参数值,db, err := sql.Open("mysql", "用户名:密码@tcp(IP:端口)/数据库?charset=utf8")。例如:db, err := sql.Open("mysql", "root:111111@tcp(127.0.0.1:3306)/test?charset=utf8")。
sql.Open并不会立即建立一个数据库的网络连接, 也不会对数据库链接参数的合法性做检验, 它仅仅是初始化一个sql.DB对象. 当真正进行第一次数据库查询操作时, 此时才会真正建立网络连接;
sql.DB表示操作数据库的抽象接口的对象,但不是所谓的数据库连接对象,sql.DB对象只有当需要使用时才会创建连接,如果想立即验证连接,需要用Ping()方法;
sql.Open返回的sql.DB对象是协程并发安全的.
sql.DB的设计就是用来作为长连接使用的。不要频繁Open, Close。比较好的做法是,为每个不同的datastore建一个DB对象,保持这些对象Open。如果需要短连接,那么把DB作为参数传入function,而不要在function中Open, Close。
6.增加数据有两种方法:直接使用Exec函数添加
result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)","lily","销售","2016-06-21")
首先使用Prepare获得stmt,然后调用Exec添加,预编译语句(PreparedStatement)提供了诸多好处, 因此我们在开发中尽量使用它. 下面列出了使用预编译语句所提供的功能:PreparedStatement 可以实现自定义参数的查询、PreparedStatement 通常来说, 比手动拼接字符串 SQL 语句高效、PreparedStatement 可以防止SQL注入攻击。
stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
res, err := stmt.Exec("zhja", "研发", "2016-06-17")
另一个经常用到的功能,获得刚刚添加数据的自增ID
id, err := res.LastInsertId()
获取影响数据库的行数,可以根据该数值判断是否插入或删除或修改成功。
count, err := result.RowsAffected()
删除和上面的增加语法一样,只是把其中的INSERT语句改为DELETE语句,修改和上面的增加语法一样,只是把其中的INSERT语句改为UPDATE语句。
7.查询单条数据,扫描并复制当前行中每一列的值,但是要求行必须与行中的列数相同:func (rs *Rows) Scan(dest ...interface{}) error
rows.Scan 参数的顺序很重要, 需要和查询的结果的column对应. 例如 “SELECT * From user where age >=20 AND age < 30” 查询的行的 column 顺序是 “id, name, age” 和插入操作顺序相同, 因此 rows.Scan 也需要按照此顺序 rows.Scan(&id, &name, &age), 不然会造成数据读取的错位。因为golang是强类型语言,所以查询数据时先定义数据类型,但是查询数据库中的数据存在三种可能:存在值,存在零值,未赋值NULL 三种状态, 因为可以将待查询的数据类型定义为sql.Nullxxx类型,可以通过判断Valid值来判断查询到的值是否为赋值状态还是未赋值NULL状态.
QueryRow 函数:
var username, departname, created string
err := db.QueryRow("SELECT username,departname,created FROM userinfo WHERE uid=?", 3).Scan(&username, &departname, &created)
查询多条数据,并遍历:Query 获取数据,for xxx.Next() 遍历数据。首先使用Query()方法进行查询,如果查询无误,返回Rows,就是所有行的信息,类似结果集。func (db *DB) Query(query string, args ...interface{}) (*Rows, error);我们可以通过Next()方法判断是否存在下一条数据,如果有,可以使用之前的Scan()方法读取一行,然后继续判断,继续获取。这个过程的本质就是迭代,所以通常要配合循环使用。func (rs *Rows) Next() bool;每次db.Query操作后,都建议调用rows.Close()。
8.事务:由于事务是一个一直连接的状态,所以Tx对象必须绑定和控制单个连接。一个Tx会在整个生命周期中保存一个连接,然后在调用commit()或Rollback()的时候释放掉。在调用这几个函数的时候必须十分小心,否则连接会一直被占用直到被垃圾回收。在操作数据库之前执行,db.Begin(),例:tx, err := db.Begin();保存到数据库:err := tx.Commit();回滚:err := tx.Rollback(),注意设置事务以后操作数据库就不是db了,而是tx。
9.示例代码:
package main
import (
//"fmt"
"database/sql"
_"github.com/Go-SQL-Driver/MySQL"
)
type userinfo struct {
username string
departname string
created string
}
func main(){
db, err := sql.Open("mysql", "root:111111@tcp(127.0.0.1:3306)/test?charset=utf8")
checkErr(err)
//insert
//stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
//checkErr(err)
//res, err := stmt.Exec("zhja", "研发", "2016-06-17")
//checkErr(err)
//id, err := res.LastInsertId()
//checkErr(err)
//fmt.Println(id)
//result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)","lily","销售","2016-06-21")
//checkErr(err)
//ids, err := result.LastInsertId()
//fmt.Println(ids)
//db.Exec("DELETE FROM userinfo WHERE uid=?", 1)
//checkErr(err)
//stmt, err := db.Prepare("DELETE FROM userinfo WHERE uid=?")
//stmt.Exec(2)
//var username, departname, created string
//err = db.QueryRow("SELECT username,departname,created FROM userinfo WHERE uid=?", 3).Scan(&username, &departname, &created)
//fmt.Println(username)
//fmt.Println(departname)
//fmt.Println(created)
rows, err := db.Query("SELECT username,departname,created FROM userinfo WHERE username=?", "zhja")
checkErr(err)
for rows.Next() {
var username, departname, created string
if err := rows.Scan(&username, &departname, &created); err == nil {
fmt.Println(err)
}
fmt.Println(username)
fmt.Println(departname)
fmt.Println(created)
}
tx, err := db.Begin()
checkErr(err)
stmt, err1 := tx.Prepare("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)")
checkErr(err1)
_, err2 := stmt.Exec("test", "测试", "2016-06-20")
checkErr(err2)
//err3 := tx.Commit()
err3 := tx.Rollback()
checkErr(err3)
}
func checkErr(err error){
if err != nil {
panic(err)
}
}
七、sqlx扩展包[扩展内容]
sqlx包是作为database/sql包的一个额外扩展包,在原有的database/sql加了很多扩展,如直接将查询的数据转为结构体,大大简化了代码书写,当然database/sql包中的方法同样起作用。安装:go get "github.com/jmoiron/sqlx";连接数据库:
var Db *sqlx.DB
db, err := sqlx.Open("mysql","username:password@tcp(ip:port)/database?charset=utf8")
Db = db
处理类型(Handle Types):sqlx设计和database/sql使用方法是一样的。包含有4中主要的handle types:
sqlx.DB - 和sql.DB相似,表示数据库。
sqlx.Tx - 和sql.Tx相似,表示事物。
sqlx.Stmt - 和sql.Stmt相似,表示prepared statement。
sqlx.NamedStmt - 表示prepared statement(支持named parameters)
所有的handler types都提供了对database/sql的兼容,意味着当你调用sqlx.DB.Query时,可以直接替换为sql.DB.Query.这就使得sqlx可以很容易的加入到已有的数据库项目中。
此外,sqlx还有两个cursor类型:sqlx.Rows - 和sql.Rows类似,Queryx返回、sqlx.Row - 和sql.Row类似,QueryRowx返回。
相比database/sql方法还多了新语法,也就是实现将获取的数据直接转换结构体实现。Get(dest interface{}, …) error、Select(dest interface{}, …) error;Get和Select是一个非常省时的扩展,可直接将结果赋值给结构体,其内部封装了StructScan进行转化。Get用于获取单个结果然后Scan,Select用来获取结果切片。示例代码:
package main
import (
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"fmt"
)
var Db *sqlx.DB
type User struct {
Uid int
Username string
Departname string
Created string
}
func main() {
db, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/mytest?charset=utf8")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = db
var users []User
err = Db.Select(&users, "SELECT uid,username,departname,created FROM userinfo")
if err != nil {
fmt.Println("Select error", err)
}
fmt.Printf("this is Select res:%vn", users)
var user User
err1 := Db.Get(&user, "SELECT uid,username,departname,created FROM userinfo where uid = ?", 1)
if err1 != nil {
fmt.Println("GET error :", err1)
} else {
fmt.Printf("this is GET res:%v", user)
}
}
//this is Select res:[{1 测试人员 技术部 2018-11-21} {2 文员 行政部 2018-11-11}]
//this is GET res:{1 测试人员 技术部 2018-11-21}
八、数据库操作技巧
1.创建特定编码的数据库,mysql 例子utf-8
-- 创建数据库时,设置数据库的编码方式
-- CHARACTER SET:指定数据库采用的字符集,utf8不能写成utf-8
-- COLLATE:指定数据库字符集的排序规则,utf8的默认排序规则为utf8_general_ci(通过show character set查看)
drop database if EXISTS dbtest;
create database dbtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 修改数据库编码
alter database dbtest CHARACTER SET GBK COLLATE gbk_chinese_ci;
alter database dbtest CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 创建表时,设置表、字段编码
use dbtest;
drop table if exists tbtest;
create table tbtest(
id int(10) auto_increment,
user_name varchar(60) CHARACTER SET GBK COLLATE gbk_chinese_ci,
email varchar(60),
PRIMARY key(id)
)CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 修改表编码
alter table tbtest character set utf8 COLLATE utf8_general_ci;
-- 修改字段编码
ALTER TABLE tbtest MODIFY email VARCHAR(60) CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 查看创建数据库的指令并查看数据库使用的编码
show create database dbtest;
-- 查看所有的字符编码
SHOW CHARACTER SET;
-- 查看某字段使用的编码:
SELECT CHARSET(email) FROM tbtest;
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!