MySQL 增删改查数据表及其字段（SQL和PHP操作）

Hardworking666

已于 2022-03-29 18:55:57 修改

阅读量1.3k

点赞数 4

分类专栏： MySQL等数据库文章标签： mysql 数据库 php SQL 增删改查

于 2021-12-30 14:30:29 首次发布

本文链接：https://blog.csdn.net/Hardworking666/article/details/122234958

版权

MySQL等数据库专栏收录该内容

6 篇文章 1 订阅

订阅专栏

一、创建、删除数据表

CREATE、DROP

使用 SQL 语句 CREATE TABLE 来创建数据表：

CREATE TABLE table_name (column_name column_type);

使用 SQL 语句 DROP TABLE 来删除数据表：

DROP TABLE table_name;

delete删除的是表里的数据：

delete from table_name;

delete删除完还可以恢复。添加id将从以前删完的序号后添加。
注：本文中，数据表的 “列” = “字段” 。

创建数据表实例

SQL在 myDB 数据库中创建数据表myDB_tbl：

CREATE TABLE IF NOT EXISTS `myDB_tbl`(
   `myDB_id` INT UNSIGNED AUTO_INCREMENT, # 无符号自增整形，一般用于主键，数值会自动加1
   `myDB_title` VARCHAR(100) NOT NULL, # 可变长字符串
   `myDB_author` VARCHAR(40) NOT NULL, # 如果输入该字段的数据为NULL，会有报错
   'myDB_sex' enum('m','f') DEFAULT 'm', # 设置性别为枚举类型，默认为m（男）
   `myDB_date` DATE,
   PRIMARY KEY ( `myDB_id` ) # 定义列为 主键 。可使用多列来定义主键，列间以逗号分隔
   FOREIGN KEY(id) REFERENCES authorinfo(id) on DELETE CASCADE on UPDATE CASCADE, 
# 外键(id)引用authorinfo表的主键(id)，为了防止数据不一致可以使用删除级联和更新级联
# 如果authorinfo中信息修改了，myDB_tbl中的信息也要一并修改
)ENGINE=InnoDB DEFAULT CHARSET=utf8; # ENGINE:设置存储引擎为InnoDB（默认存储引擎）
# DEFAULT CHARSET:默认字符集
# 上面 2 和 7 也可以合并写成如下所示，但如果有多个主键就要单独写
`myDB_id` INT(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT

[root@host]# mysql -u root -p
Enter password:****
mysql> use myDB;
Database changed
mysql> CREATE TABLE myDB_tbl(
   -> myDB_id INT NOT NULL AUTO_INCREMENT,
   -> myDB_title VARCHAR(100) NOT NULL,
   -> myDB_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( myDB_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql>

注意：MySQL命令终止符为分号 “ ; ”。-> 是换行符标识，不要复制。最后一个字段后不需再加逗号。不写单引号也可以。

主键及外键

主关键字(primary key)是表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录。
在两个表的关系中，主关键字用来在一个表中引用来自于另一个表中的特定记录。
一个表的主键可以由多个关键字共同组成，并且主关键字的列不能包含空值。
主关键字是可选的，并且可在 CREATE TABLE 或 ALTER TABLE 语句中定义。

外键又称作外关键字。
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。
外键表示了两个关系之间的相关联系。
以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
注：不能直接删除主表，会报错：Cannot delete or update a parent row: a foreign key constraint fails （无法删除或更新本行:外键约束失败），要先删除外表。

PHP mysqli_query()

使用PHP 的 mysqli_query() 函数来创建、删除已存在数据库的数据表

mysqli_query(connection,query,resultmode);

<?php
$dbhost = 'localhost';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = 'root';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功<br />';
$sql = "CREATE TABLE myDB_tbl( ".
        "myDB_id INT NOT NULL AUTO_INCREMENT, ".
        "myDB_title VARCHAR(100) NOT NULL, ".
        "myDB_author VARCHAR(40) NOT NULL, ".
        'myDB_sex' enum('m','f') DEFAULT 'm'.
        "submission_date DATE, ".
        "PRIMARY KEY ( myDB_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
mysqli_select_db( $conn, 'myDB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('数据表创建失败: ' . mysqli_error($conn));
}
echo "数据表创建成功\n";
mysqli_close($conn);
?>

二、SQL ALTER 修改

ALTER 可以修改 数据表名 、 数据表字段、字段默认值、数据表索引，表示格式为：

ALTER TABLE <表名> [改变方式]

注：本文中数据表的 “列” = “字段” 。

RENAME、DROP、ADD、CHANGE、MODIFY

ALTER 修改基本表提供如下五种方式：

（1）RENAME：用于修改数据表的名称

如将数据表 myDB_tbl 重命名为 alter_tbl：

ALTER TABLE myDB_tbl RENAME TO alter_tbl;

（2）DROP：用于删除指定的完整性约束条件，或删指定的列，其语法格式为：

ALTER TABLE <表名> DROP [<完整性约束名>]
ALTER TABLE <表名> DROP COLUMN <列名>

如删除myDB_tbl表的 myDB_id 字段：

ALTER TABLE myDB_tbl DROP myDB_id;

如果数据表中只剩余一个字段则无法使用DROP来删除。

（3）ADD：用于增加新列和完整性约束，列的定义方式同CREARE TABLE语句中的列定义方式相同，其语法格式：

ALTER TABLE <表名> ADD <列定义>|<完整性约束>

由于使用此方式中增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束。

如在表 myDB_tbl 中添加 i 字段，并定义数据类型:

ALTER TABLE testalter_tbl ADD i INT;

执行以上命令后，i 字段会自动添加到数据表字段的末尾。如果需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)或 AFTER 字段名（设定位于某个字段之后）。

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，如果想重置数据表字段的位置需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

如果需要备注可以在后面增加comment语句：

ALTER TABLE testalter_tbl ADD i INT comment '备注';

（4）CHANGE：用于修改某些列名，其语法格式：

ALTER TABLE [表名] CHANGE <原列名> TO <新列名> <新列的数据类型>

ALTER TABLE myDB_tbl CHANGE j j INT;

（5）MODIFY：用于修改某些列的数据类型，其语法格式：

ALTER TABLE [表名] MODIFY [列名] [数据类型]

如把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令：

ALTER TABLE myDB_tbl MODIFY c CHAR(10);

查看表结构

desc 表名;

Null 值和默认值修改

当修改字段时，可以指定是否包含值或者是否设置默认值。如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。
以下实例，指定字段 j 为 NOT NULL 且默认值为100 。

mysql> ALTER TABLE myDB_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

使用 ALTER 来修改字段的默认值：

mysql> ALTER TABLE myDB_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM myDB_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

使用 ALTER 命令及 DROP子句来删除字段的默认值:

ALTER TABLE myDB_tbl ALTER i DROP DEFAULT;

MySQL 创建与修改索引（ALTER命令）

三、插入与更新数据

INSERT INTO

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

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

如果数据是字符型，必须使用单引号或者双引号，如：“value”。

root@host# mysql -u root -p password;
Enter password:****
mysql> use my_DB;
Database changed
mysql> INSERT INTO myDB_tbl 
    -> (myDB_title, myDB_author, submission_date)
    -> VALUES
    -> ("学习插入数据", "my", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql>

以上实例中，我们并没有提供 myDB_id 的数据，因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。
所以，该字段会自动递增而不需要去设置。
在MySQL中也是可以去设置从几自增以及自增几个：

insert into myDB_tbl values(1,'一'), (2,'二'),(3,'三'), (4,'四');

实例中 NOW() 是一个 MySQL 函数，该函数返回日期和时间。

我们可以通过以下语句查看数据表数据：

select * from myDB_tbl;

PHP插入数据

可以使用PHP 的 mysqli_query() 函数来执行 SQL INSERT INTO命令来插入数据

mysqli_query(connection,query,resultmode);

$sql = "INSERT INTO myDB_tbl".
        "(myDB_title, myDB_author, submission_date)".
        "VALUES".
        "('$myDB_title', '$myDB_author', '$submission_date')";
  
mysqli_select_db( $conn, 'myDB' );
$retval = mysqli_query( $conn, $sql );

对于含有中文的数据插入，需要添加语句：

mysqli_query($conn , "set names utf8");

UPDATE

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。

语法
以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

可以同时更新一个或多个字段。
可以在 WHERE 子句中指定任何条件。
可以在一个单独表中同时更新数据。
可以通过命令提示符更新数据。

当需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

例如把表里的zhangsan改成中文的张三：

update authorinfo set name='张三' where name='zhangsan';

四、查询数据

SELECT

MySQL 数据库使用SQL SELECT语句来查询数据。

语法
以下为在MySQL数据库中查询数据通用的 SELECT 语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

查询语句中可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
可以使用 WHERE 语句来包含任何条件。
可以使用 LIMIT 属性来设定返回的记录数。
可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

查询所有姓名为“张”开始的作者信息：

select * from authorinfo where name like'张%';

PHP mysqli_fetch_array()

使用 PHP 函数的 mysqli_query() 及 SQL SELECT 命令来获取数据。

该函数用于执行 SQL 命令，然后通过 PHP 函数 mysqli_fetch_array() 来使用或输出所有查询的数据。

mysqli_fetch_array() 函数从结果集中取得一行作为关联数组，或数字数组，或二者兼有，
返回根据从结果集取得的行生成的数组，如果没有更多行则返回 false。

如果需要在字符串中使用变量，请将变量置于花括号。

PHP mysqli_fetch_array() 函数第二个参数可以为 MYSQLI_ASSOC，设置该参数查询结果返回关联数组，可以使用字段名称来作为数组的索引。

PHP mysqli_fetch_assoc()

PHP 提供了另外一个函数 mysqli_fetch_assoc()，该函数从结果集中取得一行作为关联数组。返回根据从结果集取得的行生成的关联数组，如果没有更多行，则返回 false。

可以使用常量 MYSQLI_NUM 作为 PHP mysqli_fetch_array() 函数的第二个参数，返回数字数组。

PHP mysqli_free_result()

内存释放：
在执行完 SELECT 语句后，释放游标内存是一个很好的习惯。
可以通过 PHP 函数 mysqli_free_result() 来实现内存的释放。

mysqli_free_result($retval);

4.1 精确（条件）查询

select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]

4.1.1【查询所有数据行和列】

例：select * from a

说明：查询a表中所有行和列

4.1.2【查询部分行列–条件查询】

例：select i,j,k from a where f=5

说明：查询表a中f=5的所有行，并显示i,j,k３列

4.1.3【在查询中使用ＡＳ更改列名】

例：select name as 姓名 from a where y=‘男’

说明：查询a表中性别为男的所有行，显示name列，并将name列改名为（姓名）显示

4.1.4【查询空行】

例：select name from a where email is null

说明：查询表a中email为空的所有行，并显示name列；SQL语句中用is null或者is not null来判断是否为空行

4.1.5【在查询中使用常量】

例：select name, ‘唐山’ as 地址 from Student

说明：查询表a，显示name列，并添加地址列，其列值都为’唐山’

4.1.6【查询返回限制行数(关键字：top percent)】

例１：select top 6 name from a

说明：查询表a，显示列name的前６行，top为关键字

例２：select top 60 percent name from a

说明：查询表a，显示列name的60%，percent为关键字

4.1.7【查询排序（关键字：order by , asc , desc）】

例：select name

from a

where chengji>=60

order by desc

说明：查询a表中chengji大于等于60的所有行，并按降序显示name列；默认为ASCII升序

4.2 模糊查询

4.2.1【使用like进行模糊查询】

注意：like运算副只用于字符串，所以仅与char和varchar数据类型联合使用

例：select * from a where name like ‘赵%’

说明：查询显示表a中，name字段第一个字为赵的记录

4.2.2【使用between在某个范围内进行查询】

例：select * from a where nianling between 18 and 20

说明：查询显示表a中nianling在18到20之间的记录

4.2.3【使用in在列举值内进行查询】

例：select name from a where address in (‘北京’,‘上海’,‘唐山’)

说明：查询表a中address值为北京或者上海或者唐山的记录，显示name字段

4.3 分组查询

4.3.1【使用group by进行分组查询】

例：select studentID as 学员编号,AVG(score) as 平均成绩 (注释:这里的score是列名)

from score (注释:这里的score是表名)

group by studentID

说明：在表score中查询，按strdentID字段分组，显示strdentID字段和score字段的平均值；select语句中只允许被分组的列和为每个分组返回的一个值的表达式，例如用一个列名作为参数的聚合函数

4.3.2【使用having子句进行分组筛选】

例：select studentID as 学员编号,AVG(score) as 平均成绩 (注释:这里的score是列名)

from score (注释:这里的score是表名)

group by studentID

having count(score)>1

说明：接上面例子，显示分组后count(score)>1的行，由于where只能在没有分组时使用，分组后只能使用having来限制条件。

4.4 多表联接查询

4.4.1内联接

4.4.1.1【在where子句中指定联接条件】

例：select a.name,b.chengji

from a,b

where a.name=b.name

说明：查询表a和表b中name字段相等的记录，并显示表a中的name字段和表b中的chengji字段

4.4.1.2【在from子句中使用join…on】

例：select a.name,b.chengji

from a inner join b

on (a.name=b.name)

说明：同上

4.4.2外联接

4.4.2.1【左外联接查询】

例：select s.name,c.courseID,c.score

from strdents as s

left outer join score as c

on s.scode=c.strdentID

说明：在strdents表和score表中查询满足on条件的行，条件为score表的strdentID与strdents表中的sconde相同

4.4.2.2【右外联接查询】

例：select s.name,c.courseID,c.score

from strdents as s

right outer join score as c

on s.scode=c.strdentID

说明：在strdents表和score表中查询满足on条件的行，条件为strdents表中的sconde与score表的strdentID相同

五、MySQL有关权限的表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，
由mysql_install_db脚本初始化。

这些权限表分别user，db，table_priv，columns_priv和host。

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是 全局级 的。

db权限表：记录各个 帐号 在各个数据库上的操作权限。

table_priv权限表：记录数据 表 级的操作权限。

columns_priv权限表：记录数据 列 级的操作权限。

host权限表：配合db权限表对给定主机上数据 库 级操作权限作更细致的控制。

这个权限表不受GRANT和REVOKE语句的影响。

Hardworking666

关注

4
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
MySQL 增删改查数据表及其字段（SQL和PHP操作）

（1）使用 SQL 语句 CREATE TABLE 来创建数据表：CREATE TABLE table_name (column_name column_type);删除MySQL数据表的通用语法：DROP TABLE table_name ;在 myDB 数据库中创建数据表myDB_tbl：CREATE TABLE IF NOT EXISTS `myDB_tbl`( `myDB_id` INT UNSIGNED AUTO_INCREMENT, // 无符号自增整形，一般用于主键，数值会
复制链接

扫一扫