MySQL 增删改查 数据表及其字段(SQL和PHP操作)
文章目录
一、创建、删除数据表
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;
三、插入与更新数据
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,k3列
4.1.3【在查询中使用AS更改列名】
例: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)】
例1:select top 6 name from a
说明:查询表a,显示列name的前6行,top为关键字
例2: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相同
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· 上周热点回顾(2.24-3.2)