PHP入门 – SQL语句设计/数据表增删改查

在我们创建好数据库和数据表后,我们就需要往数据表里插入数据了。这也是我们经常听到的增删改查。

操作数据表中的数据记录

1.使用INSERT语句向数据表中添加数据

格式:

INSERT INTO 表名 [(字段名1,…字段名n)] VALUES(‘值1’,…,’值n’);

INSERT INTO 表名 [(字段名1,…字段名n)] VALUES(‘值1’,…,‘值n’),(‘值1’,…,’值n’);

2.使用UPDATE语句更新数据表中已存在的数据

SQL语句可以使用UPDATE语句对表中的一列或多列数据进行修改,必须指定需要修改的字段,并且需要赋予的新值。还要给出必要的WHERE子句指定要更新的数据行。

格式:

UPDATE 表名 SET 字段名=表达式 [,…][WHERE 条件]

示例

UPDATE `users` SET `username`='xiaoming', `tel`='123456789' WHERE `id`=3;

3.使用DELETE语句删除数据表中不需要的数据记录

DELETE语句用来删除数据表中的一条或多条数据记录。

格式:

DELETE FROM 表名 [WHERE 条件]

小提示:删除数据是风险较高的操作,通常在编程中不会使用DELETE删除数据,而是使用更新某列数据的状态。

查询一张表中的数据

1.最简单的查询语句是使用SELECT语句检索记录的特定字段,多个字段可以用逗号分隔,例如:

SELECT username,password FROM user;

也可以使用*从表中检索出所有字段,使用“SELECT *”主要是针对用户的书写方便而言的。如果一张表当中的数据多大几百万,就意味着资源的浪费和漫长的查询等待,所以实际应用时要尽量避免使用它,而把查询的列名准确地列出来,也可以按自己指定的列顺序输出。

 

如果想为返回的列取一个新的标题,以及经过对字段的计算或总结之后,产生了一个新的值,希望把它放到一个新的列里显示,则用AS保留。例如:在上例的输出结果中使用中文字段名,可以在MySQL控制台中输入的命令如下所示:

SELECT username as ‘用户名’ FROM user;

定义别名时一定要使用单引号引起来,其中AS关键字是可选的,在原字段名和别名之间使用一个空格即可。

SELECT username ‘用户名’ FROM user;

2. 如果在使用SELECT语句返回的记录结果中包含重复的记录,可以使用DISTINCT关键字取消重复的数据,只返回一个。另外,要注意DISTINCT关键字的作用是整个查询的列表,而不是单独的一列。DISTINCT会消耗一定的服务器资源,如果不指定,会默认使用ALL关键字作为检索模式。

SELECT DISTINCT gid FROM user_group;

3. 在SELECT语句中,可以使用WHERE子句指定搜索条件,实现从数据表中检索出符合条件的记录。其中,搜索条件可以由一个或多个逻辑表达式组成,这些表达式指定关于某一记录是真或假的条件。在WHERE子句中,可以通过逻辑操作符和比较操作符指定基本的表达式条件。

逻辑操作符

图片1

比较运算符

图片2 图片3

4.空值只能定义在允许NULL字段中出现,NULL值是特殊的值,代表“无值”,与零值(0)和空字符串(”)都不相同。当在不支持默认值的字段中未输入值,或在字段中显式的设置为空,就会出现空值,但不能用处理已知值的方式来处理NULL。为了进行NULL值的搜索,必须采用特殊的语法。如果要检索NULL值,必须使用IS NULL和IS NOT NULL关键字。

5.使用SELECT语句获取数据表中的数据时,返回的记录一般是无规则排列的,有可能每次获取的查询记录截然不同。为了使用检索的结果方便阅读,可以在SELECT语句中使用ORDER BY子句,对检索的结果进行排序。

ORDER BY后面可以接一列或多列用于排序的字段,并且使用DESC或ASC关键字设计字段排序的方式。默认情况下按照升序排列,即使用ASC关键字。否则要按照降序排列,必须使用DESC关键字。ORDER BY子句可以和SELECT语句中的其他子句一起使用,但在子查询中不能有ORDER BY子句,因为ORDER BY子句只能对最终查询结果排序。

SELECT * FROM users ORDER BY id DESC;

以上可以实现对数据表内容按照字段 id 的降序排列

6. 如果在数据表中的记录数非常多,一次从表中返回大量的记录不仅检索的速度慢,用户阅读也很不方便。所以在通过SELECT语句检索时,使用LIMIT子句一次取少量的记录,而用分页的方式继续阅读后面的数据。

LIMIT子句也可以和其他的SELECT子句一起使用,它可以指定两个参数,分别用以设置返回记录的起始位置,和返回记录的数量。

LIMIT子句也可以只使用一个参数,表示从开头位置,即偏移量为0的位置返回指定数量的记录,在上例中使用的“LIMIT 0, 5”等价于“LIMIT 5”。

SELECT * FROM users ORDER BY id DESC LIMIT 2,2;

以上可以实现对数据表内容按照 id 的倒序排列并只读取从第 3条数据的两条数据。

7. 在数据库系统中提供了一系列的内置统计函数,在SQL查询中使用这些统计函数可以更有效地处理数据。这些统计函数把存储在数据库中的数据,描述为一个整体而不是一行行孤立的记录。

图片4

这些函数通常用在SELECT子句中,作为结果数据集的字段返回的结果。在SELECT语句的SELECT子句中使用函数的语法如下:

SELECT 函数名(列名1 或*),…函数名(列名n) FROM 表名; #使用统计函数

SELECT COUNT(*) AS count FROM users;

以上可以统计数据表中有多少列数据。

SELECT COUNT(DISTINCT username) as count from users;

以上可以实现统计数据表中 字段 username 值不重复的列数。

SELECT sex,COUNT(*) AS count FROM users GROUP BY sex;

以上可以实现统计数据表中以字段 sex 分类的各类的列数

8.前面使用统计函数返回的是所有记录的统计结果,如果要对数据进行分组统计,就需要使用GROUP BY子句。这将可以允许用户在对数据进行分类的基础上,进行再查询。GROUP BY子句将表按列值分组,列的值相同的分为一组。如果GROUP BY后面有多个列名,则先按第一个列名分组,再在每组中按第二个列名分组。

需要注意的是,在GROUP BY子句中不支持对字段分配别名,也不支持任何使用了统计函数的集合列。

在完成数据结果的分组查询和统计后,还可以使用HAVING子句来对查询的结果,进行进一步的筛选。

在SELECT语句的子句中:WHERE子句选择所需要的行;GROUP BY子句进行了必要的分组整理;而HAVING子句对最后的分组结果进行了重新筛选。

查询多张表中的数据

1.多表查询(连接查询)

前提条件:假设有两个表,一个是学生表,一个是学生成绩表。

内连接 inner jion :

最常见的连接查询可能是这样,查出学生的名字和成绩:

select s.name,m.mark from student s,mark m where s.id=m.studentid

上面就是我们最常见的inner join,即内连接,把符合student.id=mark.studentid 条件的元组才选出来,也可以写成:

select s.name,m.score from student s inner join mark m on s.id=m.studentid

左连接-left join:

左连接是把左边的表的数据全部选出来:

select s.name,m.score from student s left join mark m on s.id=m.studentid

上面语句就是把左边的表,即student表中的元组全部选出,尽管有些分数表是没数据的,也选了出来

右连接-right join:

右连接就是把右边表的数据全部取出,不管左边的表是否有匹配的数据:

select s.name,m.score from student s right join mark m on s.id=m.studentid

上面的语句就是把mark分数表的数据全部取出来,不管student表中是否有数据匹配

全连接-full join(貌似mysql不支持)

把左右两个表的数据都取出来,不管是否匹配:

select s.name,m.score from student s full join mark m on s.id=m.studentid

2.嵌套查询(子查询)

where型子查询(把内层查询结果当作外层查询的比较条件)

取出每个栏目下最新的产品(goods_id唯一)

select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

from型子查询 (把内层的查询结果供外层再次查询)

用子查询查出挂科两门及以上的同学的平均成绩

思路:

先查出哪些同学挂科两门以上

select name,count(*) as gk from stu where score < 60 having gk >=2;

以上查询结果,我们只要名字就可以了,所以再取一次名字

select name from (select name,count(*) as gk from stu having gk >=2) as t;

找出这些同学了,那么再计算他们的平均分

select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

exists型子查询(把外层查询结果拿到内层,看内层的查询是否成立)

查询哪些栏目下有商品,栏目表category,商品表goods

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

 

PHP入门 – MySQL数据库/数据表设计

动态网站都是对数据进行操作,所谓的动态网站就是基于数据库开发的系统,最重要的就是数据管理,或者说我们在开发时都是在围绕数据库在写程序。

在我们使用PHP开发中小型网站时,一般首选使用MySQL数据库。LAMP或LNMP都是开源免费的,是我们经常搭建的服务器环境。

MySQL数据库常用操作

mysql –h 服务器主机地址 –u 用户名 –p 用户密码

只有客户机所在的主机被授予权限才能去连接MySQL服务器。

注意:

每个SQL命令都需要使用分号来完成

可以将一行命令拆成多行

可以通过\c来取消本行命令

可以通过\q、exit、ctrl+c或者quit来退出当前客户端

MySQL数据库创建/删除/选择

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名称;

删除数据库:

DROP DATABASE [IF EXISTS] 数据库名称;

显示当前数据库服务器下的所有数据库列表:

SHOW DATABASES;

选择数据库:

USE 数据库名称;

注意:

MySQL数据库中命令不区分大小写。

每创建一个数据库,就会在data目录下创建一个以此数据库名称命名的文件夹。

在Windows下,数据库名称也是不区分大小写的,但在Linux下,数据库名称严格区分大小写。

数据表的设计

1.数据列四大数据类型

数值类数据列类型 字符串类数据列类型 日期和时间类数据列类型 NULL值

1.1数值类数据列类型

图片1 图片2

整型注意事项:

INT(3)、SMALLINT(3)等整型后面的数字不会影响数值的存储范围,只会影响显示,整型后面的数字只有配合零填充的时候才有实际意义。整型后面的数字可以省略

浮点型注意事项:

浮点型后面的数字会将存入的数字四舍五入,例如:把一个1.234存入FLOAT(6,1)数据列中,结果是1.2,6代表显示长度,1代表小数位长度,会四舍五入.

1.2字符串数据列类型

图片3

字符串类型注意事项:

(1)CHAR和VARCHAR类型

CHAR和VARCHAR类型的长度范围都在0~255之间

在使用CHAR和VARCHAR类型时,当我们传入的实际的值的长度大于指定的长度,字符串会被截取至指定长度

在使用CHAR类型时,如果我们传入的值的长度小于指定长度,实际长度会使用空格补至指定长度

在使用VARCHAR类型时,如果我们传入的值的长度小于指定长度,实际长度即为传入字符串的长度,不会使用空格填补

CHAR要比VARCHAR效率更高,但占用空间较大

(2)BLOB和TEXT类型

BLOB和TEXT类型是可以存放任意大数据的数据类型

BLOB区分大小写,TEXT不区分大小写

(3)ENUM和SET类型

ENUM和SET类型是特殊的的串类型,其列值必须从固定的串集中选择

ENUM只能选择其中一个值,SET可以选择多个值

1.3时间和日期类据列类型

图片4

日期类型注意事项: 存储日期时,我们可以使用整型来进行存储时间戳,这样做便于我们进行日期的计算

1.4 NULL值

NULL值注意事项:NULL意味着“没有值”或“未知值,可以测试某个值是否为NULL,不能对NULL值进行算术计算,对NULL值进行算术运算,其结果还是NULL,0或NULL都意味着假,其余值都意味着真。

2.数据字段属性

UNSIGNED 只能用于设置数值类型,不允许出现负数,最大存储长度会增加一倍

ZEROFILL 只能用于设置数值类型,在数值之前会自动用0补齐不足的位数

AUTO_INCREMENT 用于设置字段的自动增长属性,每增加一条记录,该字段的值会自动加1

NULL和NOT NULL 默认为NULL,即插入值时没有在此字段插入值,默认为NULL值,如果指定了NOT NULL,则必须在插入值时在此字段填入值

DEFAULT 可以通过此属性来指定一个默认值,如果没有在此列添加值,那么默认添加此值

3.索引

索引主要包含 主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、常规索引(INDEX)、全文索引(FULLTEXT)。

3.1 主键索引

主键索引是关系数据库中最常见的索引类型,主要作用是确定数据表里一条特定的数据记录的位置。我们可以在字段后添加PRIMARY KEY来对字段设置为主键索引。

注意:

最好为每张表指定一个主键,但不是必须指定。

一个表只能指定一个主键,而且主键的值不能为空

主键可以有多个候选索引(例如NOT NULL,AUTO_INCREMENT)

3.2 唯一索引

唯一索引与主键索引一样,都可以防止创建重复的值。但是,不同之处在于,每个数据表中只能有一个主键索引,但可以有多个唯一索引。我们使用关键字UNIQUE对字段定义为唯一索引

3.3 常规索引

常规索引技术是关系数据查询中最重要的技术,如果要提升数据库的性能,索引优化是首先应该考虑的,因为它能使我们的数据库得到最大性能方面的提升。常规索引也存在缺点:

多占用磁盘空间

会减慢插入,删除和修改操作

需要按照索引列上排序格式执行

创建索引我们可以使用INDEX和KEY关键字随表一同创建。

4.数据表操作

4.1 创建表

创建数据表之前,我们应该注意:创建数据库(如已存在则不需要创建),选择数据库,在该数据库当中创建数据表

创建数据表需要注意:指定数据表的名称(数据表不能重名),指定该表的字段名称、字段数据类型、字段索引,指定表类型和表默认字符集(可省略)

图片1

图片2

注意事项:

表的字段之间要使用逗号隔开。

建表的最后一句一定不能有逗号。

表名称和字段名称尽量不要使用MySQL系统的关键字

如果一定要使用关键字,我们可以使用反引号将表名称和字段名称包含起来来进行过滤屏蔽。

使用反引号会使建表效率增高。

数据表名称和字段名称不能重名

AUTO_INCREMENT属性必须依附于主键索引或唯一索引

查看表结构命令: DESC 表名;

4.2 修改表

修改表的语法:ALTER TABLE 表名 ACTION;

我们可以对表进行修改字段,添加字段,删除字段,添加索引,删除索引,更改表名称,更改字段名称,更改auto_increment属性的初始值等。

4.2.1 修改字段

我们使用change或者是modify关键字

ALTER TABLE `users` CHANGE `username` `uname` VARCHAR(32) NOT NULL;

ALTER TABLE `users` MODIFY `username` VARCHAR(32) NOT NULL;

由上例可以发现:

change可以改变字段名称,而modify不可以

4.2.2 添加字段

我们使用add关键字

ALTER TABLE `uses` ADD `tname` VARCHAR(32) NOT NULL;

这样我们就会新增一个tname字段。

如果添加在某个字段后面添加一个字段,可以按照下面的方法写:

ALTER TABLE `users` ADD `email` VARCHAR(20) NOT NULL DEFAULT ”  AFTER `tel`;

这样添加的 email 字段就会紧挨这字段 tel 后面

4.2.3 删除字段

我们使用drop关键字

ALTER TABLE `users` DROP `tname`; 这样我们会删除tname字段

4.2.4 更改表名称:

我们使用rename关键字

ALTER TABLE 旧表名 RENAME AS 新表名; 将旧表名更改为新表名

更改AUTO_INCREMENT初始值:

ALTER TABLE 表名称 AUTO_INCREMENT=1;  将AUTO_INCREMENT的初始值设置为1

4.2.5 添加索引:

我们使用add关键字

ALTER TABLE `uses` ADD INDEX/UNIQUE/PRIMARY KEY/索引名 (字段名称)

这样会在该字段上建立索引(普通索引,唯一索引,主键索引)。

ALTER TABLE user ADD UNIQUE username_unqiue (username);
ALTER TABLE user ADD INDEX username_index (username);

注意:在创建唯一索引时,有时会出现提示 ERROR 1062 (23000): Duplicate entry ‘***’ for key ‘username_unique’

这个问题的原因是该字段中已经存在重复的值,所以不能直接创建唯一索引。解决办法是删除重复的值,或清空表数据。

查看表里的全部索引

SHOW INDEX FROM 表名称;

或者 SHOW KEYS FROM 表名称;

查询的结果分别是:

· Table
表的名称。
· Non_unique
如果索引不能包括重复词,则为0。如果可以,则为1。
· Key_name
索引的名称。
· Seq_in_index
索引中的列序列号,从1开始。
· Column_name
列名称。
· Collation
列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。
· Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机 会就越大。
· Sub_part
如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
· Packed
指示关键字如何被压缩。如果没有被压缩,则为NULL。
· Null
如果列含有NULL,则含有YES。如果没有,则该列含有NO。
· Index_type
用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。
· Comment

4.2.6 删除索引:

删除索引研究了半天,终于知道怎么删除了。

(1)删除UNIQUE唯一索引和INDEX常规索引的方法:

ALTER TABLE `users` DROP INDEX 索引名称;

注意:INDEX可以删除唯一索引

(2)删除PRIMARY KEY 主键索引,去掉一个表格的主键需要分2中情况:

A、该列(column)不光设置为主键(primary key),还有自增长(auto_increment);

句式:alter table +表名+ modify id int, drop primary key;

B、如果没有设置为自增长(auto_increment),那么可以直接删除主键(primary key );

句式:alter table +表名+drop primary key;

这样我们会删除这个索引,我们可以使用show indexes from 表名查看当前表索引。

4.3 删除表

DROP TABLE [IF EXISTS] 表名称;