15.1 MySQL数据库基础
MySQL是最流行的关系型数据库管理系统,在Web应用方面常用,且是免费的。关系型数据库中数据以表格的形式出现,每行为各种记录名称,每列为记录名称所对应的数据域。许多的行和列构成一张数据表,许多的表组成一个数据库。MySQL把数据存储在表格中,使用结构化查询语言SQL来访问数据库。MySQL具有以下特点:
(1)MySQL是开源的,所以你不需要支付额外的费用。
(2)MySQL支持大型的数据库,可以处理拥有上千万条记录的大型数据库。
(3)MySQL使用标准的SQL数据语言形式。
(4)MySQL可以允许于多个系统上,并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
(5)MySQL对PHP有很好的支持,PHP是目前最流行的Web开发语言。
(6)MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。
(7)MySQL是可以定制的,采用GPL协议,可以修改源码来开发自己的MySQL系统。
15.2 操作MySQL数据库
15.3 MySQL数据类型
在MySQL中定义数据字段的类型对的数据库的优化是非常重要的。MySQL支持多种类型,大致可以分为3类:数值、日期/时间和字符串(字符)类型。
15.3.1 数值类型
MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。MySQL数据库支持的数值类型说明如表15-1所示。
15.3.2 日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型都有一个有效值范围和一个“零”值,当指定不合法的MySQL不能表示的值时使用“零”值。MySQL中日期和时间数据类型如表15-2所示。
15.3.3 字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。字符串类型如表15-3所示。
CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串,而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值。
BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同,支持任何数据,如文本、声音和图像等。
有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的最大长度和存储需求,但是不能存储二进制文件。
15.4 操作MySQL数据表
操作MySQL数据表是指对表结构进行改变,比如创建一个表,更改表字段类型,设置主键索引等。这些在平常的使用中是很常见的。
15.4.1 创建数据表
在MySQL中创建数据表使用create table语句,语法如下:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
create table语句中的参数说明如表15-4所示。
关于create_definition参数的说明如表15-5所示。
下面演示创建一个名为test_table的表,该表包含id、title、author、content、submit_time、click字段。创建表的SQL语句如下:
CREATE TABLE `test_table`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(40) CHARACTER SET utf8 NOT NULL,
`author` CHAR(10) NULL,
`content` VARCHAR(45) NOT NULL COMMENT '文章内容',
`submit_time` VARCHAR(45) NOT NULL,
`click` INT(4) NULL DEFAULT 0,
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8
COMMENT='文章内容表';
在命令行模式下输入SQL语句的运行结果如下图所示
15.4.2 查看数据表结构
创建完一个表后,可以使用show columns或者describe语句查看指定数据表的结构。
show columns语法如下:
show columns显示在一个给定表中各列的信息
也可以使用describe语句查看表结构
也可以使用describe语句查看表结构,describe语法如下:
15.4.3 更改数据表结构
当我们需要修改数据表名或数据表字段名时,需要使用到alter命令。
alter语法如下:
alter table用于更改原有表的结构,可以增加或删减列、创建或取消索引、更改原有列的类型、重新命名列或表,还可以更改表的评注和表的类型。alter table运行时会对原表进行临时复制,在副本上进行更改,然后删除原表,再对新表进行重命名。在执行alter table时,其他用户可以阅读原表,但是对表的更新和修改的操作将被延迟,直到新表生成为止。新表生成后,这些更新和修改信息会自动转移到新表上。
下面介绍几个alter命令经常使用到的场景。
使用alter删除test_table中的click字段,语句如下:
ALTER TABLE test_table drop click
使用alter给表test_table添加字段click_times,语句如下:
ALTER TABLE test_table add click_times int(4)
使用alter修改表test_table中submit_time字段为datetime类型,语句如下:
ALTER TABLE test_table MODIFY submit_time datetime
使用alter修改表test_table表名为table_test,语句如下:
ALTER table test_table rename to table_test
15.4.4 删除数据表
删除数据表使用drop table语句,语法如下:
if exists检查表是否存在,因为在删除一个不存在的表的情况下会报错。例如,删除数据表table_test的语句如下:
DROP table table_test;
15.5 操作MySQL数据
操作MySQL数据是对表中数据进行增删改查,常用的是insert向表中增加数据,update更新数据,delete删除一些数据,select查询所需数据等。编程中用到的数据很多都存在MySQL数据库中,这一节就来介绍如何操作MySQL数据库中的数据。
15.5.1 插入数据
一般通过以下两种方式向数据表中插入数据。语法分别如下:
另一种常用语法如下:
下面通过使用第一种语法向表test_table中插入一行数据,语句如下:
INSERT INTO test_table (title,author,content,submit_time,click) values
('hello','chenxiaolong','hello everyone',now(),10);
接下来演示使用第二种语法向表中插入一行数据,语句如下:
INSERT INTO test_table set title='h1', author='chendalong',content='hello world', submit_time=now(),click=100;
15.5.2 更新数据
更新数据表使用update语句,语法如下:
set重新设定指定列的值,where子句指定记录中哪些行需要被更改,如果不设置where子句或where子句的值恒成立(如1=1),就将更新所有记录行的数据。下面演示更改表test_table中第一行记录的click值为200。语句如下:
UPDATE test_table SET click=200 where id=1;
15.5.3 删除数据
删除表中的数据使用delete语句,语法如下:
其中,where子句指定哪些行被删除,如果没有设置where子句或设置其恒成立(如where true)将会删除所有记录。下面演示如何删除表test_table中的第一行数据,语句如下:
DELETE FROM test_table where id=3;
MySQL中还提供一个可以清空数据表中所有数据的函数truncate
15.5.4 查询数据
最常用的数据库操作是增、删、改、查,其中查询数据可以说是数据库操作中最常使用到的。MySQL中提供的查询数据的语句非常强大,比如可限定查询数量和查询起始位置、对查询结果进行分组排序、使用函数和表达式查询数据等。
关于select语句参数的说明如表15-6所示。
通过应用程序向表test_table中插入一百行数据,表中的数据示例如图15-22所示。
CREATE DEFINER=`root`@`localhost` PROCEDURE `myproc4`()
begin
declare num int ;
set num = 0 ;
while num < 100 do
insert into test_table (title,author,content,submit_time,click) values
(concat("hello", num),'张小龙',concat('hello everyone',num),now(), concat('100',num));
set num = num + 1;
end
while;
end
1. distinct去掉查询重复行
使用distinct可去掉查询结果中的重复行。例如,查询test_table表并在结果中去掉字段author中的重复数据,SQL语句如下:
MariaDB [db_test]> SELECT DISTINCT author FROM test_table;
+--------+
| author |
+--------+
| 张小龙 |
| 陈小龙 |
| 张大龙 |
| 李小龙 |
+--------+
4 rows in set (0.001 sec)
2. into outfile将查询结果导出到文件
将从表test_table中查询到的数据导出到test_table.txt,语句如下:
SELECT * INTO OUTFILE 'test_table.txt' FROM test_table;
执行以上SQL语句后将会得到test_table.txt文件,其中存储着从test_table表中查询到的信息,如图15-24所示。
3. where查询子句
可以使用where子句限定查询条件,where子句功能非常强大,通过它可以实现一些复杂的查询,在where子句中常用到的运算符如表15-7所示。
select * from test_table where click>500 and author='张小龙';
4. order by对结果进行排序
MariaDB [db_test]> select * from test_table order by click desc limit 0,10;
+------+----------+--------+-------------------+---------------------+-------+
| id | title | author | content | submit_time | click |
+------+----------+--------+-------------------+---------------------+-------+
| 1000 | hello999 | 张小龙 | hello everyone999 | 2021-01-25 14:15:55 | 1000 |
| 999 | hello998 | 张小龙 | hello everyone998 | 2021-01-25 14:15:55 | 999 |
| 998 | hello997 | 张小龙 | hello everyone997 | 2021-01-25 14:15:55 | 998 |
| 997 | hello996 | 张小龙 | hello everyone996 | 2021-01-25 14:15:55 | 997 |
| 996 | hello995 | 张小龙 | hello everyone995 | 2021-01-25 14:15:55 | 996 |
| 995 | hello994 | 张小龙 | hello everyone994 | 2021-01-25 14:15:55 | 995 |
| 994 | hello993 | 张小龙 | hello everyone993 | 2021-01-25 14:15:55 | 994 |
| 993 | hello992 | 张小龙 | hello everyone992 | 2021-01-25 14:15:55 | 993 |
| 992 | hello991 | 张小龙 | hello everyone991 | 2021-01-25 14:15:55 | 992 |
| 991 | hello990 | 张小龙 | hello everyone990 | 2021-01-25 14:15:55 | 991 |
+------+----------+--------+-------------------+---------------------+-------+
10 rows in set (0.019 sec)
图15-26 使用order by对查询结果排序
使用like可实现模糊查询,它有两种通配符“%”和“_”。“%”可以匹配一个或多个字符,“—”只匹配一个字符。例如,查询test_table表中author=’陈小龙’和author=’陈大龙’的记录行,语句如下:
select * from test_table where author like ('张_龙') limit 0,10;
15.6 MySQL图形化管理工具
1. phpMyAdmin
2. MySQL Workbench
3. MySQL-Front
4. Navicat