mysql安装使用详细教程（上）

一、数据库介绍

1、什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库， 
每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。 
RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

1. 数据以表格的形式出现
2. 每行为各种记录名称
3. 每列为记录名称所对应的数据域
4. 许多的行和列组成一张表单
5. 若干的表单组成database

关系型数据库：

• oracle
• mysql
• serserver
• DB2
• postgresql
• Sqlite
• access

2、RDBMS常用术语

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。数据库中的表像一个简单的excel。
• 列: 一列(数据元素) 包含了相同的数据
• 行：一行（=元组，或记录）是一组相关的数据
• 冗余：：存储两倍数据，冗余可以使系统速度更快。(表的规范化程度越高，表与表之间的关系就越多；查询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低查询速度。例如，可以在student表中增加一个冗余字段dept_name，该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
• 主键：：主键是唯一的。一个数据表中只能包含一个主键。
• 外键：外键用于关联两个表，关联它表的主键。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 复合键：：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

3、Mysql数据库

MySQL是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• Mysql是开源的，所以你不需要支付额外的费用。
• Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL使用标准的SQL数据语言形式。
• Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
• Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
• MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
• Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

二、MYSQ数据库的安装使用

1、CentOS7 安装mariadb-server

yum -y install mariadb-server  # 安装systemctl start  mariadb.service  # 启动systemctl enable mariadb.service  # 开机启动
1
2
3
1
2
3

2、基本使用

mysqladmin --version  # 查看版本mysqladmin -uroot -p password  # 设密码show grants for root  # 查看用户权限GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '123456' WITH GRANT OPTION;  # 授权flush privileges;mysql -u root -p  # 连接show databases;  # 查看所有库use mysql       # 进入mysql库show tables;     # 查看库里的所有表desc user;       # 查看表结构；同SHOW COLUMNS FROM user;select * from user;  # 查看所有数据select * from user\G # 格式化显示show create database test;  # 查看创建create database testdb charset "utf8";  # 创建支持中文testdb库SHOW INDEX FROM 数据表 # 显示表的详细索引信息，包括PRIMARY KEY（主键）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

三、MySQL 数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。 
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

1、数值类型

类型大小范围（有符号）范围（无符号）用途TINYINT1 字节(-128，127)(0，255)小整数值SMALLINT2 字节(-32 768，32 767)(0，65 535)大整数值MEDIUMINT3 字节(-8 388 608，8 388 607)(0，16 777 215)大整数值INT或INTEGER4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值BIGINT8 字节(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值FLOAT4 字节(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度浮点数值DOUBLE8 字节(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度浮点数值DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值

2、日期和时间类型

类型大小(字节)范围格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3‘-838:59:59’/’838:59:59’HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:00/2037 年某时YYYYMMDD HHMMSS混合日期和时间值，时间戳

3、字符串类型

类型大小用途CHAR0-255字节定长字符串VARCHAR0-65535 字节变长字符串TINYBLOB0-255字节不超过 255 个字符的二进制字符串TINYTEXT0-255字节短文本字符串BLOB0-65 535字节二进制形式的长文本数据TEXT0-65 535字节长文本数据MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215字节中等长度文本数据LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据LONGTEXT0-4 294 967 295字节极大文本数据

四、mysql 常用命令

MySQL 创建数据表

语法

CREATE TABLE table_name (column_name column_type);
1
1

创建一个student表

create table student(   stu_id INT NOT NULL AUTO_INCREMENT,   name CHAR(32) NOT NULL,   age  INT NOT NULL,   register_date DATE,   PRIMARY KEY ( stu_id ));
1
2
3
4
5
6
7
1
2
3
4
5
6
7

实例解析：

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

mysql 插入数据

语法

INSERT INTO table_name ( field1, field2,...fieldN )                       VALUES                       ( value1, value2,...valueN );
1
2
3
1
2
3

插入数据

mysql> insert into student (name,age,register_date) values ("fgf",22,"2016-03-4");
1
1

MySQL 查询数据

语法

SELECT column_name,column_nameFROM table_name[WHERE Clause][OFFSET M ][LIMIT N]
1
2
3
4
1
2
3
4

MySQL where 子句

语法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...[WHERE condition1 [AND [OR]] condition2.....
1
2
1
2

以下为操作符列表，可用于 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。

使用主键来作为 WHERE 子句的条件查询是非常快速的。

MySQL UPDATE 查询

语法

UPDATE table_name SET field1=new-value1, field2=new-value2[WHERE Clause]update student set age=22 ,name="fgf" where stu_id>3;
1
2
3
4
1
2
3
4

MySQL DELETE 语句

语法

DELETE FROM table_name [WHERE Clause]<br><br>delete from student where stu_id=5; 
1
1

MySQL LIKE 子句

语法

SELECT field1, field2,...fieldN table_name1, table_name2...WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'select *from student where name binary like "%Li";select *from student where name binary like  binary "%Li"; #只匹配大写
1
2
3
4
5
1
2
3
4
5

* 排序*

SELECT field1, field2,...fieldN table_name1, table_name2...ORDER BY field1, [field2...] [ASC [DESC]]使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。select *from student where name like binary "%Li" order by stu_id desc;
1
2
3
4
1
2
3
4

MySQL GROUP BY 语句

SELECT column_name, function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name;
1
2
3
4
1
2
3
4

MySQL ALTER命令

删除，添加或修改表字段

alter table student drop register_date; #从student表删除register_date字段alter table student add phone int(11) not null; #添加phone字段
1
2
1
2

修改字段类型及名称

ALTER TABLE testalter_tbl MODIFY c CHAR(10);ALTER TABLE testalter_tbl CHANGE i j BIGINT;
1
2
1
2

修改表名

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
1
1

关于外键

外键，一个特殊的索引，用于关键2个表，只能是指定内容　

mysql> create table class(    -> id  int not null primary key,    -> name char(16));Query OK, 0 rows affected (0.02 sec)CREATE TABLE `student2` (  `id` int(11) NOT NULL,  `name` char(16) NOT NULL,  `class_id` int(11) NOT NULL,  PRIMARY KEY (`id`),  KEY `fk_class_key` (`class_id`),  CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))# 此时如果class表中不存在id 1,student表也插入不了，这就叫外键约束#如果有student表中跟这个class表有关联的数据，你是不能删除class表中与其关联的纪录的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

MySQL NULL 值处理

我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。 
为了处理这种情况，MySQL提供了三大运算符:

• IS NULL: 当列的值是NULL,此运算符返回true。
• IS NOT NULL: 当列的值不为NULL, 运算符返回true。
• <=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。 
在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。 
MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

多表连接查询

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

五、事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
• 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
• 事务用来管理insert,update,delete语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

• 1、事务的原子性：一组事务，要么成功；要么撤回。
• 2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
• 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
• 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

在Mysql控制台使用事务来操作

mysql> begin； ＃开始一个事务mysql> insert into a (a) values(555);mysql>rollback; 回滚 ， 这样数据是不会写入的
1
2
3
4
5
1
2
3
4
5

当然如果上面的数据没问题，就输入commit提交命令就行；

六、索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

普通索引 
创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length));  
1
1

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))
1
1

创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL,  username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 
1
2
3
4
5
1
2
3
4
5

删除索引的语法

DROP INDEX [indexName] ON mytable;
1
1

唯一索引 
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。以下实例为在表中添加索引。mysql> ALTER TABLE testalter_tbl ADD INDEX (c);你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:mysql> ALTER TABLE testalter_tbl DROP INDEX (c);
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);你也可以使用 ALTER 命令删除主键：mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
1
2
3
4
5
6
7
1
2
3
4
5
6
7

显示索引信息

mysql> SHOW INDEX FROM table_name\G
1
1

七、python 操作mysql

Python-mysqldb : 
pymysql : 
pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。

下载安装

pip3 install pymysql
1
1

使用操作

1、执行SQL

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql# 创建连接conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')# 创建游标cursor = conn.cursor()# 执行SQL，并返回收影响行数effect_row = cursor.execute("update hosts set host = '1.1.1.2'")# 执行SQL，并返回受影响行数#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))# 执行SQL，并返回受影响行数#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])# 提交，不然无法保存新建或者修改的数据conn.commit()# 关闭游标cursor.close()# 关闭连接conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2、获取新创建数据自增ID

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor()cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])conn.commit()cursor.close()conn.close()# 获取最新自增IDnew_id = cursor.lastrowid
1
2
3
4
5
6
7
8
9
10
11
12
13
1
2
3
4
5
6
7
8
9
10
11
12
13

3、获取查询数据

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor()cursor.execute("select * from hosts")# 获取第一行数据row_1 = cursor.fetchone()# 获取前n行数据# row_2 = cursor.fetchmany(3)# 获取所有数据# row_3 = cursor.fetchall()conn.commit()cursor.close()conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

• cursor.scroll(1,mode=’relative’) # 相对当前位置移动
• cursor.scroll(2,mode=’absolute’) # 相对绝对位置移动

4、fetch数据类型

　　关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')# 游标设置为字典类型cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)r = cursor.execute("call p1()")result = cursor.fetchone()conn.commit()cursor.close()conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

---

转载请务必保留此出处：http://blog.csdn.net/fgf00/article/details/52976250