mysql开发和 与实践 第4章 数据库和表的操作.pdf

本章要求:第4章数据库和表的操作 掌握MySQL数据库操作 掌握MySQL数据表操作 掌握MySQL语句操作 掌握创建、删除、修改数据表 掌握插入、查询语句的方法 掌握查询数据库记录的使用方法1.毛:F:主要内容第4章数据库和表的操作1.数据库操作2.数据表操作3语句操作4.综合实例查询名称中包含“P H P”的 图书信息.4.1数据库操作4.1.14.1.24.1.34.1.4创建数据库 查看数据库 选择数据库 删除数据库仁心4.1.1创建数据库二一 一 使用CREATE DATAB ASE语句可以轻松创建MySQL数据库。语法如下：CREATE DATABASE 数据库名；在创建数据库时，数据库命名有以下几项规则：公不能与其他数据库重名，否则将发生错误。名称可以由任意字母、阿拉伯数字、下划线（_）和组成，可以使用上述的任意字符开头.，但不能使用单独的数字，否则会造成它与数值相混淆。名称最长可为64个字符，而别名最多可长达256个字符。.不能使用MySQL关键字作为数据库名、表名。在默认情况下，Windows下数据库名、表名的大小写是不敏感的，而在Linux下数据库名、表名 的大小写是敏感的。为了便于数据库在平台间进行移植，建议读者采用小写来定义数据库名和表仁二【例4-1】通过CREATE DATAB ASE语句创建一个名称为db_admin的数据库，如图4-1所示。ii?sql create database db_adnin；OK.|1 row affected iysql|仓隹tSrin娄蜘奉 泊费建药情绮各图4-1创建MySQL数据库4.1.2查看数据库成功创建数据库后，可以使用SH O W命令查看MySQL服务器中的所有数据库信息。语法如下：SHOW DATABASES;【例4-2 在4.1.1节中创建了数据库db_admin,下面使用SH O W DATAB ASES语句查看MySQL服务器中的所有数据库名称，如图4-2所示。nysql show databases；.-Database杳看所有数据库infornation_schema db_admin II mysql TI test!数据库列表4 rows in set mysql图4-2 查看数据库从图4-2运行的结果可以看出，通过SH O W命吾查看MySQL服务器中的所有数据库，结果显示 MySQL服务器中有4个数据库。4.1.3选择数据库在上面的讲解中，虽然成功创建了数据库，但并不表示当前就在操作数据库db_admin。可以使用USE 语句选择一个数据库，使其成为当前默认数据库。语法如下：USE数据库名；【例4-3】选择名称为db_admin的数据库，设置其为当前默认的数据库，如图4-3所示。图4-3选择数据库仁4.1.4删除数据库.删除数据库的操作可以使用DRO P DATAB ASE语句。语法如下：DROP DATABASE 数据库名；,注意：.删除数据库的操作应该谨慎使用，一旦执行该操作，数据库的所有结构和数据都会被删除，没“有恢复的可能，除非数据库有备份。【例4-4】通过DRO P DATAB ASE语句删除名称为db_admin的数据库，如图4-4所示。图4-4删除数据库仁口4.2数据表操作4.2.14.2.24.2.34.2.44.2.5创建数据表 查看表结构 修改表结构 重命名表 删除表4.2.1创建数据表创建数据表使用CREATE TAB LE语句。语法如下：CREATE TEMPORARY TABLE IF NOT EXISTS数据表名(create_definition,.)table_options select_statementCREATE TAB LE语句的蓼数说明如袤4-1所示。表4-1 CREATE TAB LE语句的参数说明关键字说 明TEMPORARY如果使用该关键字，表示创建一个临时表IF NOTEXISTS该关键字用于避免表存在时MySQL报告的错误create_defin ition这是表的列属性部分。MySQL要求在创建表时，表要至少包 含一列table_options表的一些特性参数select_state mentSELECT语句描述部分，用它可以快速地创建表仁口下面介绍列属性create_definition部分，每一列定义的具体格式如下：col_name type NOT NULL|NULL DEFAULT default_value AUTOJNCREMENTPRIMARY KEY reference_definition属性create def ini t ion的参数说明如表4-2所示。表4-2 属性createdef inition的参数说明参 数说 明col name字段名type字段类型NOT NULL|NULL指出该列是否允许是空值，系统一般默认允许为空值，所以当 不允许为空值时，必须使用NOT NULLDEFAULT default value表示默认值AUTO_INCREMEN T表示是否是自动编号，每个表只能有一个AUTOJNCREMENT列，并且必须被索引PRIMARY KEY表示是否为主键。一个表只能有一个PRIMARY KEYo如表中没有 一个PRIMARY KEY,而某些应用程序需要PRIMARY KEY,MySQL将返回 第一个没有任何NULL列的UNIQUE键，作为PRIMARY KEYreference_def inition为字段添加注释以上是创建一个数据表的一些基础知识，它看起来十分复杂，但在实际的应用中使用最基本的 格式创建数据表即可，具体格式如下：create table table_name（列名1属性，列名2属性）；仁心【例4-5】使用CREATE TAB LE语句在MySQL数据库db_admin中创建一个名为tb_admin的数据表，该 表包括id、user、password和createtime等字段，如图4-5所示。use db_admin e changed 一 create table tb_?id int autoincrement prinary keV user uarchar not null,password varchar not null,createtime datetime；OK,0 rows affected 创建数据表数据表创建成功图4-5创建MySQL数据库仁口4.2.2查看表结构对于一个创建成功的数据表，可以使用SH O W CO LUMNS语句或DESCRIB E语句查看指定数据表的表 结构。下面分别对这两个语句进行介绍。1.SH O W CO LUMNS语句SH O W CO LUMNS语句的语法：SHOW FULL COLUMNS FROM 数据表名FROM 数据库名;或写成SHOW FULL COLUMNS FROM数据表名.数据库名；【例4-6】使用SH O W CO LUMNS语句查看数据表tb_admin表结构，如图4-6所示。nysql shou columns from tb_admin from db_adnin；i Field!Type i Null 11 Key 11 Defaulti id!int i NO!user!uarchar!NO!password!uarchar4 rows in set 1nysql.图4-6查看表结构2.DESCRIB E语句DESCRIB E语句的语法：DESCRIBE数据表名；其中，DESCRIB E可以简写成DESC。在查看表结构时，也可以只列出某一列的信息。其语法格式 如下:，DESCRIBE数据表名列名；【例4-7】使用DESCRIB E语句的简写形式查看数据表tb_admin中的某一列信息，如图4-7所示。nysql desc tb_adnin user；+-+-+-!Field!Type+-+-!Null!Key!Default!Extra ti user i uarchar!NO+-+-+-1 row in set 图4-7查看表的某一列信息仁口m u m 一一二4.2.3修改表结构修改表结构使用ALTER TAB LE语句。修改表结构指增加或者删除字段、修改字段名称或者字段类型、设置取消主键外键、设置取消索引以及修改表的注释等。语法如下：AlterIGNORE TABLE 数据表名 alter_spec,alter_spec.注意：当指定IGNO RE时，如果出现重复关键的行，则只执行一行，其他重复的行被删除.其中，altejspec子句定义要修改的内容，其语法如下：alter_specification:.ADD COLUMN create_definition FIRST|AFTER column_name|ADD INDEX index_name(index_col_name,.)|ADD PRIMARY KEY(index_coLname,.|ADD UNIQUE index_name(index_col_name,.)|ALTER COLUMN col_name SET DEFAULT literal|DROP DEFAULT0|CHANGE COLUMN old_col_name create_definition|MODIFY COLUMN create_definition.修改子句定反字段 I DROP COLUMN col_name-删除字段名称命|DROP PRIMARY KEY-删除主键名称-添加新字段-添加索引名称-添加主键名称-添加唯一索引一修改字段名称-修改字段类型|DROP INDEX index_name|RENAME AS new_tbl_name-删除索引名称-更改表名|table_options ALTER TAB LE语句允许指定多个动作，其纨作间使用逗号分隔，每个动作表示对表的一个修改。仁心;0,lLm-【例4-8】添加一个新的字段email,类型为varchar(50),not null,varchar(30)改为varchar(40),代码如下：alter table tb_admin add email varchar(50)not null,modify user varchar(40);在命令模式下的运行情况如图4-8所示。alter table tb_adnln add enail notodiFy user uarchar；ei*y OK，0 rows affected desc tb_admin user；查看修改的字段信息Field i Typei Null i Key i Default i Extra-user-：uarchar-：YES-+-row in set ysql _+,!NULLh将字段user的类型由图4-8修改表结构图4-8中只给出了修改user字段类型的结果，读者可以通过语句mysqlshow tb_admin;查看整 个表的结构，以确认email字段是否添加成功。说明：通过alter修改表列，其前提是必须将表中数据全部删除，然后才可以修改表列。4.2.4重命名表重命名数据表使用RENAME TAB LE语句，语法如下：RENAME TABLE数据表名1 To数据表名2说明：该语句可以同时对多个数据表进行重命名，多个表之间以逗号分隔。【例4-9】对数据表tb_admin进行重命名，更名后的数据表为tb_user,如图4-9所示。.二一二mysql rename table tb_admin to tb_user；Query OK,0 rows affected 修改数据表名mysqxz aesc tu_usei9+-+-+-+-!Field!Type!Null!查看更名后的数据表信息11卜：id!i NO：user I uarchar!YES!password!uarchar30)!NO!createtime!datetime YES!email!uarchar!NOPRI!NULL!NULL 1!NULL 1 1auto_increment5 rows in set mysql一图4-9对数据表进行更名仁口:mW4.2.5删除表 删除数据表的操作很简单，同删除数据库的操作类似，使用DRO P TAB LE语句即可实现。语法如下:DROP TABLE数据表名；#【例4-10删除数据表tb_user,如图4T0所示。mysql drop table tb_user；Query OK.0 rows affectedhysql图4-10删除数据表 注意：.删除数据表的操作应该谨慎使用。一旦删除了数据表，那么表中的数据将会全部清除，没有备份 则无法恢复。.在删除数据表的过程中，删除一个不存在的表将会产生错误，如果在删除语句中加入IF EXISTS 关键字就不会出错了。格式如下：drop table if exists 数据表名；仁4.3语句操作4.3.1插入记录4.3.2查询数据库记录4.3.3修改记录4.3.4删除记录仁口4.3.1插入记录在建立一个空的数据库和数据表时，首先需要考虑的是如何向数据表中添加数据，该操作可以使.用INSERT语句来完成。语法如下：insert into 数据表名(column_name,column_name2,)values(value 1,value2,.).在MySQL中，一次可以同时用入多行记录，各行记录的值清单在VALUES关键字后以逗号“，”分隔，而标准的SQL语句一次只能插入一行。【例4-11】向管理员信息表tb_admin中插入一条数据信息，如图4-11所示。向数据表tb_admin中插入一条数据nysql insert into tb_adminuser,password,enail,createtime)一)ualues tsot/111/ 09；Query 1 row affected _图4-11插入记录仁口mm y 二4.3.2查询数据库记录-要查询的内容，选-查询时需要满足的-如何对结果进行分-如何对结果进行排-查询时满足的第二条件仁口要从数据库中把数据查询出来，就要用到数据查询语句SELECT。SELECT语句是最常用的查询语.句，它的使用方式有些复杂，但功能也是很强大的。其语法如下:select selection_list择哪些列.from数据表名丁指定数据表where primary_constraint条件，行必须满足的条件group by grouping_columns,组_ order by sorting_cloumns.序叼having secondary_constraint limit count-限定输出的查询结果,这就是select查询语句的语法，下面对它的参数进行详细的讲解。1.selection_list,设置查询内容。如果要查询表中所有列，可以将其设置为“*”；如果要查询表中某一列或多歹U,则直接输入列名，并以“，”为分隔符。仁口【例4-12】查询tbmrbook数据表中所有列和查询user和pass列的代码如下：select*from tb_mrbook;-查询数据表中所有数据select user,pass from tb_mrbook;-查询数据表中user和pass列的数据.2.table list（数据表名）.指定善询的数据表。即可以从一个数据表中查询，也可以从多个数据表中进行查询，多个数据.表之间用“，”进行分隔，并且通过WH ERE子句使用连接运算来确定表之间的联系。【例4T3】从tb mrbook和tb bookinfo数据表中查询bookname=MySQL入门与实践，的作者和.价格，其代码如下；select tb_mrbook.id,tb_mrbook.bookname,author,price from tb_mrbook,tb_bookinfowhere tb_mrbook.bookname=tb_bookinfo.bookname and.tb bookinfo.bookname=MySQL入门与实践；在上面而SQL语句中，因为2个表都有id字段和bookname字段，为了告诉服务器要显示的是哪个表中0 的字段信息，要加上前缀。语法如下：表名.字段名.tb mrbook.bookname=tb bookinfo.bookname将表tb_mrbook和tb bookinfo连接起来，叫做 等同连接；如果不使用tb mrbook.bookname=tb bookinfo.bookname,那么产生的结果将是两个表的笛卡尔积，叫做全连接。3.where条件语句在使用查询语句时，如要从很多的记录中查询出想要的记录，就需要一个查询的条件。只有设 定了查询的条件，查询才有实际的意义。设定查询条件应用的是WH ERE子句。where子句的功能非常强大，通过它可以实现很多复杂的条件查询。在使用WH ERE子句时，需要使用 一些比较运算符，常用比较运算符如表4-3所示。表4-3 常用的WH ERE子句比较运算符运算 符名 称示例运算符名称示例等于id=5Is not nulln/aId is not null大于id5Betweenn/aId betweenl and 15小于id大于 等于id=5Not inn/aName not in(shi,li)小于 等于id=5Like模式匹配Name like(shi%)!=或不等id!=5Not like模式匹配Name not like(shi%)Is nulln/aid isnullRegexp常规表达 式Name正则表达式表4-3中列举的是WH ERE子句常用的比较运算符，示例中的id是记录的编号，name是表中的用户名。【例4T4】应用where子句，查询tb_mrbook表，条件是type(类别)为P H P的所有图书，代码如下select*from tb_mrbook where type=php;4.GRO UP B Y对结果分组通过GRO UP B Y子句可以将数据划分到不同的组中，实现对记录进行分组查询。在查询时，所查 询的列必须包含在分组的列中，目的是使查询到的数据没有矛盾。在与AVG()或SUMO函数一起使用 时，GRO UP B Y子句能发挥最大作用。【例4-15】查询tbjnrbook表，按照type进行分组，求每类图书的平均价格，代码如下：select bookname,avg(price),type from tb_mrbook group by type;5.DISTINCT在结果中去除重曼行使用DISTINCT关键字，可以去除结果中重复的行。【例4T6】查询tb_mrbook表，并在结果中去掉类型字段type中的重复数据，代码如下：select distinct type from tb_mrbook;6.O RDER B Y对结果排序.使用O RDER B Y可以对查询的结果进行升序和降序(DESC)排列，在默认情况下，O RDER B Y按升序输出结果。如果要按降序排列可以使用DESC来实现。.如果对含有NULL值的列进行排序时，如果是按升序排列，NULL值将出现在最前面，如果是按降序排列，NULL值将出现在最后。【例4-17】查询tbjnrbook表中的所有信息，按照“id”进行降序排列，并且只显示3条记录。其代码如下：.select*from tb_mrbook order by id desc limit 3;7.LIK E模确查询.LIK E属于较常用的比较运算符，通过它可以实现模糊查询。它有两种通配符：“%”和下划线“，”可以匹配一个或多个字符，而只匹配一个字符。【例4-18】查找所有第二个字母是“h”的图书，代码如下：select*from tb_mrbook where bookname like(_h%);说明：p”和“入”都算做一个字符，这点上英文字母和中文没有什么区别。8.CO NCAT联合多列使用CO NCAT函数可以联合多个字段，构成一个总的字符串。【例4-19】把tb mrbook表中的书名（bookname）和价格（price）合并到一起，构成一个新的字符串。代码如下：select id,concat（bookname,:,price）as info,type from tb_mrbook;其中合并后的字段名为CO NCAT函数形成的表达式“concat（bookname,：,price）”，看上去十 分复杂，通过AS关键字给合并字段取一个别名，这样看上去就很清晰。9.LIMI邛艮定结果行数LIMIT子句可以对查询结果的记录条数进行限定，控制它输出的行数。【例4-20 查询tb_mrbook表，按照图书价格降序排列，显示3条记录，代码如下：select*from tb_mrbook order by price desc limit 3;使用LlIlT还可以从查询结果的中间部分取值。首先要定义两个参数，参数1是开始读取的第一 条记录的编号（在查询结果中，第一个结果的记录编号是0,而不是1）；参数2是要查询记录的个数【例4-21】查询tbjnrbook表，从编号1开始（即从第2条记录），查询4个记录，代码如下：select*from tb_mrbook where id limit 1,4；10.使用函数和表达式在MySQL中，还可以使用表达式来计算各列的值，作为输出结果。表达式还可以包含一些函数。【例4-22】计算tbjnrbook表中各类图书的总价格，代码如下：select sum（price）as to tai,type from tb_mrbook group by type;在对MySQL数据库进行操作时，有时需要对数据库中的记录进行统计，例如求平均值、最小值、最大值等，这时可以使用MySQL中的统计函数，常用统计函数如表4-4所示。表4-4 常用统计函数名 称说 明Avg（字段名）获取指定列的平均值Count（字段名）如指定了一个字段，则会统计出该字段中的非空记录。如在 前面增加DISTINCT,则会统计不同值的记录，相同的值当作一条 记录。如使用COUNT（*）则统计包含空值的所有记录数Min（字段名）获取指定字段的最小值Max（字段名）获取指定字段的最大值Std（字段名）指定字段的标准背离值Stdtev（字段名）与STD相同Sum（字段名）指定字段所有记录的总合除了使用函数之外，还可以使用算术运算符、字符串运算符，以及逻辑运算符来构成表达式。【例4-23】可以计算图书打八折之后的价格，代码如下：select*,（price*0.8）as 80%from tb_mrbook;Umm-4.3.3修改记录要执行修改的操作可以使用UP DATE语句，语法如下：.update 数据表名set column_name=new_value 1,column_name2=new_value2,.where condition其中，set子句指出要修改的列和它们给定的值，where子句是可选的，如果给出它将指定记录中哪行.应该被更新，否则，所有的记录行都将被更新。【例4-24 下面将管理员信息表tb_admin中用户名为tsoft的管理员密码111修改为896552,如图4-.12所示。mysqlupdate tb_admin set passwoi*d=,896552*where usei*=,tsof tJ；Query OK,1 row affected select*from tb_adnin where user=,tsoft!id!user!password I email!createtine1!tsoft!896552 i tmsoftOtnsoft.con i 2008-03-24 09：14：59 i1 row in set 的修改后的IBW言息、nysql图4-12修改指定条件的记录.注意：更新时一定要保证where子句的正确性，一旦where子句出错，将会破坏所有改变的数据。口 仁4.3.4删除记录在数据库中，有些数据已经失去意义或者错误时就需要将它们删除，此时可以使用DELETE语句,语法如下：delete from 数据表名 where condition注意：该语句在执行过程中，如果没有指定where条件，将删除所有的记录；如果指定了where条件，将按照指定的条件进行删除。【例4-25】删除管理员数据表tb_admin中用户名为“小欣”的记录信息，如图4-13所示。de lete from tb_admin where usei*=小欣；iQuery OK,1 row affected 二二二nysql删除数据表tb admin中的指定条件的圮录图4-13删除数据表中指定的记录注意：在实际的应用中，执行删除操作时，执行删除的条件一般应该为数据的id,而不是具体某个字 段值，这样可以避免一些不必要的错误发生。仁l一4.5综合实例查询名称中包含“P H P”的图书信息本实用将在图书信息表tb_mrbook中查找bookname字段中包含“P H P”的图书信息，运行结果如 图4T4所示。ysql select*from tb_ni*book where bookname like 9 zPHPz,；id-userf-!pass-+-+-i bookname!type-+-+price i2!LIU ill!PHP开农实战宝典1!JAUA!793：ZHAO!123!PHP编程词典!PHP!298rows in set iysql.图4-14查询名称中包含“P H P”的图书信息要实现查询名称中包含“P H P”的图书信息，可以通过LIK E关键字和通配符%来实现，关键代码 参考如下：mysql select*from tb_mrbook where bookname like%PHP%,;口 仁知销点梃落(1)使用CREATE DATAB ASE语句可以轻松创建MySQL数据库。(2)在对MySQL数据表进行操作之前，必须首先使用USE语句选择数据库，才可在指定的数据库中对 数据表进行操作。(3)要从数据库中把数据查询出来，就要用到数据查询语句SELECT。SELECT语句是最常用的查询语 句，它的使用方式有些复杂，但功能也是很强大的。(4)对于一个创建成功的数据表，可以使用SH O W CO LUMNS语句或DESCRIB E语句查看指定数据表的表 结构。(5)修改表结构使用ALTER TAB LE语句。(6)在建立一个空的数据库和数据表时，首先需要考虑的是如何向数据表中添加数据，该操作可以 使用INSERT语句来完成。(7)在数据库中，有些数据已经失去意义或者错误时就需要将它们删除，此时可以使用DELETE语句仁口灯 f9 3.44.MySQL中查看数据库的SQL语句是什么。MySQL中创建数据库的SQL语句是什么。MySQL中如何把原来的数据表重命名。MySQL中在查询结果中去除重复行使用哪个关键字。二富二仁口