Loading... # [PHP —-MySQL 数据库](https://blog.p2hp.com/archives/657) ## MySQL数据库操作 ### 1.创建数据库. 在MySQL中应用 **create database** 语句创建数据库.格式如下: ```mysql create database db_name; ``` db_name 为数据库名,必须为合法名称.规定如下: a.不能与其他数据库重名. b.名字可以是任意的字母,阿拉伯数字,下划线或者"$"组成.可以使用以上任意的字符开头,但不能是使用单独的数字,哪样会造成与数字混淆. c.名字最长可由64个字符组成(包括表,列和索引),而别名最多可长达256个字符. d.不能使用MySQL关键字作为数据库名称和表名. > ps: 执行过程:在创建数据库时,首先连接MySQL服务器,用户名是root,密码也是root,然后编写"create database db_name;" sql语句,数据库创建成功. ### 2.选择数据库. **use** 语句用于选择一个数据库,使其成为当前默认数据库.格式如下: ```mysql use db_name; ``` ### 3.删除数据库. 删除数据库使用 **drop database** 语句.格式如下: ```mysql drop database db_name ``` > ps.对于删除数据库的操作应该谨慎使用.一旦删除数据库中的所有结构和数据都将会被删除,没有恢复的可能,除非数据库中有备份. ## MySQL 数据库表 上面将完数据库的操作,下面讲一下数据库中,对表的操作.MySQL 数据库表的操作包括 创建,查看,修改,重命名和删除. ### 1.创建表. 创建表使用 **create table** 语句.格式如下: ```mysql create [temporary] table [if not exists] 数据表名 [(create_definition,...)][table_options][select_statement] ``` a. temporary ,如果使用该关键字,表示创建一个临时表. b. if not exists ,该关键字用于避免创建表时,表存在时 MySQL报告的错误. c. create_definition,这是表的列表属性部分.MySQL要求在创建表时,表至少要包含一列. **create_deifnition** 格式如下: ```mysql col_name type [not null] [default default_value] [auto_increment] [primary key] [reference_definition] ``` > col_name:字段名. > type:字段类型. > not null|null:指出该列是否允许空值. > not null 表示不允许空值. > default default_value:表示默认值. > auto_increament:表示默认值. > primary key:表示是否为主键,一个表中只能有一个primary key.如果没有primary key ,而某些应用要求primary key,服务器将返回第一个没有null 列的unique 键作为primary key. d. table_option, 表的一些特性参数. e. select_statement,select语句描述部分,用它可以快速地创建表. ### 2.查看表. 对于创建成功的表,使用 **show columns** 语句或 **describe** 语句查看指定数据表的表结构.格式如下: show columns 语句: ```mysql show [full] columns from 数据表名 [from 数据库名]; 或者 show [full] columns from 数据表名.数据库名; ``` describe 语句,describe 可以简写成DESC. ```mysql describe 数据表名; 或者 describe 数据表名 列名; ``` ### 3.修改表. 修改表结构使用 **alter table** 语句.其中,修改表结构的操作又包括:增加或者删除字段,修改字段名称或者字段类型,设置/取消主键或者外键,设置/取消索引以及修改表的注释等.格式如下: ```mysql alter [ignore] TABLE 数据表名 alter_spec[,alter_spec].... ``` 如果指定参数 ignore,当出现重复行时,则只执行一行,其他重复行将被删除. 其中alter_spec 子句定义要修改的内容,格式如下: ```mysql alter_specification: add <div class="flex-column"></div> create_definition [FIRST | AFTER column_name],//添加新字段 add index [index_name] (index_col_name,....),//添加索引名称 add primary key (index_col_name,...),//添加主键名称 add unique [index_name] (index_col_name,...),//添加唯一的索引 alter <div class="flex-column"></div> col_name {SET DEFAULT literal | DROP DEFAULT},//修改字段名称 change <div class="flex-column"></div> old_col_name create_definition //修改字段类型 modify <div class="flex-column"></div> create_definition,//修改字句定义的字段 drop <div class="flex-column"></div> col_name ,//删除字段名称 drop primary key,//删除主键名称 drop indEX index_name, //删除索引名称 rename [AS] new_tbl_name,//更改表名 table_options ``` alter table 语句允许制定多个动作,其动作使用逗号分隔,每个动作表示对表的一个修改.通过alter 修改表列的前提是必须将表中的数据全部删除. ### 4. 重命名表. 重命名表使用rename table 语句.格式如下: ```mysql rename table 数据表名1 to 数据表名2;//rename table 语句可以同时对多个数据表进行重命名,多个表之间以逗号分隔. ``` ### 5. 删除表 删除数据表使用语句 drop table .格式如下: ```mysql drop table 数据表名; 或者 drop table if exists 数据表名; //防止删除不存在的表,导致的报错. ``` 删除数据表,将同样导致没有备份的数据无法恢复. > ps:在执行create table,alter table和drop table中的任何操作时,首先必须选择数据库,否则将无法对数据表进行操作. ## MySQL 数据表中的数据 下面总结数据表中的数据.如何更好的操作和使用这些数据才是使用MySQL数据库的根本. ### 1.添加(插入)数据. 创建完数据库和数据表后,要向表中添加数据.添加数据主要有三种语法方式: a.列出所有新添加数据的所有的值. ```mysql insert into table_name values(value1,value2,.....);//语句缺点 当列过多的时候,不易明确知道匹配值. ``` b.给出要赋值的列,然后再给出值. ```mysql insert into table_name (column_name1,column_name2,...) values ( value1,value2,....);//缺点同上 ``` c.用col_name =value 的形式给出列和值. ```mysql insert into table_name set column_name1 = value1 ,column_name2 = value2 , ....;//弥补以上缺点,但导致语句过长. ``` **批量添加数据begin** 数据的批量添加使用 **load data 和MySQLimport** 语句实现. load data 通过大量读取本地文件系统上的文件,可以将大量数据添加到数据库中.格式如下: ```mysql load data local infile "filename.txt" into table table_name; ``` MySQLimport语句实现程序直接从文件读取批量数据.它相当于load data 语句的一个接口.格式如下: ```mysql %MySQLimport -local table_name filename.txt; ``` MySQLimport可以自动生成一个load data 语句,该语句把filename.txt 文件中的数据装入table_name 表.MySQLimport 将文件名中第一个圆点前的的字符作为新的表名,并且将文件中的数据导入到新表中.如 文件名 com.youfilename.txt. 那么将会将数据导入到表com 中. ### 2.修改数据. 修改数据使用update语句.格式如下: ```mysql update table_name set column_name1=new_Value1,column_name2 =new_value2,... where condition; ``` condition 为条件语句,如user_name='张三'. 必须保证condition条件的准确性,否则将会导致破坏表中的数据. ### 3.删除数据. 删除数据使用 delete 语句.格式如下: ```mysql delete from table_name where condition; ``` > ps > a.删除某条数据时,一般选择该数据的id 作为条件,以避免产生不必要的错误. > b.当到删除整个表的数据,因效率问题,不推荐使用delete操作.可以使用truncate 语句,它可以很快的删除表中的所有内容. ### 4.查询数据. 从数据库表中查询数据用与操作以及显示,是对数据进行操作比较重要的一环.下面将会作详细的解释. 首先,对MySql数据库表进行数据查询用到select 语句.格式如下: ```mysql select selection_list //要查询的内容,选择查询的列. from table_list //从何表中查询,从何处选择行. where primaryz_constraint //查询时,需要满足的条件. group by grouping_columns //如何对查询结果进行分组. order by sorting_columns //如何对结果进行排序 having secondary_constraint //查询时满足的第二条件. limit count //限定输出的结果. ``` a. select_list 表示要查询的内容.如果要查询表中所有的列,可以用" * "表示.如果查询多列,可以直接输入列名,并使用" , "分隔. b. table_list 从指定的表中查询.既可以从一个表中查询;也可以从多个表中查询,多表查询使用 " ," 分隔,并且在where 字句中使用连接运算符号来确定表与表之间的关系.当使用多表查询的使用,如果表中有相同的字段,为了告诉服务器要显示的那个表中的字段信息,需要在字段前加上表名.格式如下: table_name.columns_name; //表名.字段名. 使用 " = "符号将表连接起来,叫做等同连接.比如.tb_student.name = tb_gradeOne.name ;如果不使用等号连接,那么产生的结果将是两个表的笛卡尔积,叫做全连接. c.where 条件语句用于通过相应条件获取对应信息.格式为 columns_name 比较运算符 value. d. group by 实现对查询得到的数据进行划分并加以分组,从而实现分组查询.在查询时,所查询的列必须包含在分组的列中,目的是使查询到的数据没有矛盾.在与avg() 或则sum()函数一起使用时,group by 语句能够发挥最大的作用. e.使用distinct 在结果中去除重复行. f.order by.使用order by 可以对结果进行升序和降序(DESC),在默认情况下,order by 按照升序输出结果.如果要降序可以使用DESC 来实现.当对含有null值的列进行排序时,升序,null值排在最前,降序,null 排在最后. g.like 模糊查询. like 属于较常用的运算符,通过它可以实现模糊查询,他有两种通配符,即"%"和下划线"_"."%"可以实现匹配一个或多个字符,而"_"只匹配一个字符. h. 使用concat 联合多列.使用concat 函数可以联合多个字段,从而构成一个总的字符串.例如把书名与价格合并,并使用as 为字段起一个别名. select id ,concat(bookname,":",price) as bookInfo form tb_mkbook; i.使用limit 限定结果函数.使用limit字句可以对结果的记录条数进行限定,从而控制它输出的行数. 例如:limit 3 --表示显示3条数据.limit 5,10---表示从编号为5的记录开始,往下读10条数据用于显示. j.函数表达式.常用的统计函数有: avg(columns_name);//获取指定列的平均值. count(columns_name);//统计出制定列的非空记录的条数. 加distinct限定关键字,则统计不同值的条数,相同值的被认为为一条记录.count(*),则统计包含空记录的数目. max(columns_name) 或者 min(columns_name);//获取指定字段的最大/最小值. std(columns_name) 或者 stdtev(columns_name);//指定字段的标准背离值. sum(columns_name);//指定字段所有记录的值的和. > ps.常见的数据类型有 数字类型,字符串类型,时间和日期类型.详细情况,请参考php相关知料与文档. ## MySQL 数据库的操作步骤 以上对mysql数据库结构层次总结如下,下面介绍对数据库的操作步骤. ### 1.连接到mysql服务器. 使用mysql_connect()函数创建与MySQL服务器连接.方法格式如下: ```php <?php $conn = mysql_connect('hostname' , 'username' , ' password ') or die( "数据库服务器连接失败".mysql_error()); //hostname: mysql服务器的主机名或者ip.如果省略端口号,则默认为3306; //username:登陆mysql数据库服务器的名称 //password: mysql服务器的用户密码. //改函数的返回值表示对这个数据库的连接,如果成功,则返回一个资源. if($conn){ //服务器连接成功 } ?> ``` 从上可以知道,可以指定非本机的机器名作为数据库服务器,这样就为数据库的异地存储和数据库的的安全隔离提供了保障.外界用户往往通过www服务器的直接访问权限,如果数据库直接放在www服务器上,就会给MySQL数据库带来安全隐患;如果为数据库系统安装防火墙,那么php可以直接通过局域网访问数据库,而局域网的计算机对外部不可见,这样保证了s数据库不受外来攻击. 为了方便查询数据库连接错误,可以加上die()函数进行屏蔽的错误处理机制.mysql_error()用于提取错误文本信息,如果没有出错则返回空字符;如果出错,连兰奇上将会显示错误信息. ps.对于用户而言,建议在mysql_connect()前面添加@符号,用于屏蔽错误信息,这样做是为了让用户看到一堆莫名其妙的错误信息.但是对于开发者而言,在调试的过程中,不使用@能让我们快速定位错误信息. ### 2.选择MySQL数据库. 使用mysql_select_db()函数选择MySQL数据库服务器上的数据库,并与数据库创建连接.格式如下: ```php mysql_select_db(string 数据库名 [,resource link_identifier]); //string 数据库名:要选择的MySQL数据库名称 //resource link_identifier: MySQL服务器的连接标识. ``` ```php //例子,同上一步. <?php if($conn){ $selected = sql_select_db("db_webBookStore",$conn); if($selected){ //数据库连接成功; } } ?> ``` ### 3.执行sql语句. 使用mysql_query()函数执行sql语句.sql语句操作在前面已经讲过,此处不在阐述.主要介绍mysql_query()函数的用法,格式如下: ```php $result = mysql_query(string sql [,resource link_identifier]); //如果sql语句为查询语句,成功则返回结果集合,否则返回false //如果sql语句为插入,删除或更新,成功则返回true,否则返回false. //ps.改函数也可以用于选择数据库,和设置数据库编码格式. //如:mysql_query('user db_database13',$conn); // mysql_query(' set names utf8'); ``` 提高: **mysql_unbuffered_query()** ,顾名思义改函数为不缓存结果查询.它仅向服务器发送一条sql查询语句,但不获取和缓存结果的行.它不像mysql_query哪样自动获取并缓存结果集.优点在于,当处理很大的结果集时,会节省客观的内存;另一方面,可以在获取第一行数据后立即对结果集进行操作,而不用等到整个SQL语句都执行完毕. mysql_fetch_array()函数,用于将结果集合返回到数组中.格式为: ```php $array = mysql_fetch_array(resource result [,int result_type]); //resource result:资源类型的参数,要传入的是由mysql_query()返回的资源数据指针. //int result_type:可选项,要传入的整型参数,可以是:mysql_assoc(关联索引),mysql_num(数字索引),mysql_both(同时包含前两者).默认为mysql_both. //注意,本函数返回的字段名区分大小写. mysql_fetch_row()函数,从结果集中获取一行来作为枚举数组.格式为: $array = mysql_fetch_row(resource result); //根据所获取的行数据,生成数组.如果没有更多的行,则返回false. //数组偏移量下标从0开始. //本函数返回的字段名区分大小写. 本函数只能使用数字索引,而mysql_fetch_array()两者都可使用.如:数字索引:$array[0],关联索引:$array[type]; mysql_num_rows()函数,用于获取查询结果集中的记录数.格式为: int mysql_num_rows(resource result); //返回结果集中行的数目.此命令只对select语句有效.要获取其他sql语句的操作所影响的数据集行数目,需要使用mysql_affected_rows() ``` ### 4.数据执行完后,需要关闭结果集,以释放资源.语法如下: ```php mysql_free_result(resource result); ``` 如果在网页中要频繁的对数据库进行访问,可以通过创建与服务器数据库持续连接来提高效率.这样就避免了,每次连接服务器请求多带来的长时间请求和较大的资源开销.持续连接数据库使用mysql_pconnect()函数来替换mysql_connect()函数.创建的持续连接,在程序结束前将不会调用mysql_close()来关闭数据库请求.当再次调用mysql_pconnect()去连接数据库的时,服务器将返回已经创建的持续连接的ID号,而不去重新创建数据库连接. ### 5.关闭MySQL服务器. 每次使用mysql_connect()和mysql_query(),都会消耗系统资源.在少量用户访问web网站时候问题还不大,但当大量用户连接超过一定的数量,就会造成系统性能的下降,甚至死机.为了避免这种情况,在完成数据库操作后,应调用改函数来关闭与MySQL服务器的连接,以节省系统的资源.格式如下: ```php mysql_close($conn); ``` > ps.php中与数据库的连接是非持久连接,系统会自动回收,一般不用设置关闭.但如果一次性返回的结果集比较大,或者网站访问量比较多,则最好使用改函数手动释放. ### 提高: 数据库乱码问题,使用mysql_query()函数设置数据库编码. **编码格式建议使用utf-8** .如果使用gbk2312,如果用户没有安装中文编码(如一些美洲,欧洲用户的机器查看中文网站),则在输出时将会导致乱码.utf-8 的使用返回更广,可移植性更高,也更被国际化支持. 最后修改：2023 年 08 月 10 日 © 允许规范转载 赞 如果觉得我的文章对你有用，请随意赞赏