What are the basic knowledge points of databases in PHP? Basic statement? basic concept?

The previous article introduced you to "What are the types of scopes in PHP? What area can be accessed by the scope? 》, this article continues to introduce to you what are the basic knowledge points of databases in PHP? Basic statement? basic concept? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

Basic concepts of database

Command terminator: "\g" or ";"

Mysql Supported data types:

• Numeric type

• Integer type

Tinyint, smallint, mediumint, int and bigint

Ddl statement (commands can be case-sensitive)

Create database

 Create database 数据库名

Select the database to be operated: use database; for We need to use use to select the operating database

View all data tables in the database show tables

Delete the database

 Drop database 数据库名称

Create a table (in a certain To create a table in a database, you need to first use use to select the database to be operated)

Create table table name (

Field 1 name Field 1 type Column constraints,

Field 2 name Field 2 type Column constraints,

After creating the table, you can view the form definition

Desc 表名；

View the SQL statement that created the table

 Show create table 表名\G（\G选项使得记录能按照字段竖向排列，一遍更好展示内容较长的记录，\G之后不需要加分号）

Delete table

Drop table 表名；

Modify table field type

 Alter table 表名 modify [colimn] 字段定义 [first|after字段名];

Add table field

Alter table 表名 add [colimn] 字段定义 [first|after字段名]；

Delete table fields

Alter table 表名 change [colimn] 旧的字段名 字段定义 [first|after字段名]；

Note: Both change and modify can modify the definition of the table. The difference is that change requires two column names after change, which is not particularly convenient. The advantage is changeYou can modify the field name

Modify the field arrangement and sorting

[first|after field name] This selection can be used to modify the position of the field in the table. New fields are loaded at the last position in the table by default, and change/modifywill not change the field position by default

Note: change/first|after field names these Keywords are extensions of mysql to standard sql and may not be applicable to other databases

Change table name

After table 表名 rename [to] 新的表名
Dml语句

Insert record

Insert into table name (field 1, field 2, ..., field n) values ​​(value 1, value 2, ..., value n);

You don’t need to specify fields name, but the order after values should be consistent with the field sorting

Insert multiple records at one time

Insert into table name (field 1, field 2, ...,field n)

Values

(value 1,value 2,... ,value n),

(value 1,value 2,.. . , value n)

;

Query records

select * from 表名 where 条件;（*代表你查询表里的所有字段，如果我们查询某一字段，只需要将*改成那一字段即可。）

Query unique records

Select distinct 字段1,字段2 from 表名;(只要字段1,字段2任何一个字段有不同就会被选择，一般用于distinct，只筛选一个字段)

Conditional query

Note: Conditional query comparison symbols: =,<,>,>=,<=,!= and other comparison operators, multiple You can use or and etc. between conditions.

Sort and limit

Sort:

• asc: from low to high, It is also the default value select * from table name order by field name asc;

• desc: from high to bottom select * from table name order by field name desc;

• Multiple fields sort select * from table name order by field name desc,id desc;

• The number 1 represents the record from which to start (starting from 0 ), the number 2 represents how many to take!

Aggregation

Users need to perform some summary operations, which requires sql aggregation operations.

①sum sum select sum(field name) from table name;

②count total number of recordsselect count(*|field name) from table name;

③maxmax select max(field name) from table name;

④min minimum valueselect min(field name) from table name;

⑤GROUP BY classification aggregation select department,sum(salary) from employee group by department ;

⑥WITH ROLLUP The results after classification and aggregation are summarized again select sum(salary) from employee group by department with rollup;

⑦HAVING

Note: ## The difference between #having and where is that having is conditional filtering of the aggregation results, while where is filtering the records before aggregation. You should filter the records first as much as possible!

select sum(salary) from employee group by department having sum(salary)>1000;
在一起使用：select sum(id),max(id),min(id),count(*) from a1;

Table connection (can be used when displaying fields in multiple tables)

Connection classification

Inner join:Select Records that match each other in the two tables (select table.field,.... from table 1 name, table 2 name,... where [matching conditions such as table 1. field = table 2. field];)

select statements can give aliases to fields! Just write them directly after the fields that need to be displayed in the query. You can also give aliases to the tables.

Outer joins: Not only select two matching fields Records, other unmatched records will also be queried

Left join

• 包含左边表中的所有记录(包括右表中没有和它匹配的记录)select * from 表1 left join 表2 on 表1.字段=表2.字段;

• 包含右边表中的所有记录(包括左表中没有和它匹配的记录)

• 左连接和右连接是可以相互转换的！

• 子查询（一个查询需要另外一个查询的结果参与的时候）

用于子查询的关键字:

in在..里面（注意点 in后面的子语句必须只返回一个字段，若查询结果唯一(只有一条)可以使用=代替in，not in与in相反）

语法：select * from 表名1 where 字段1 in(select 字段2 from 表2);

Exists（后面那个子语句有没有查询出记录来，如果查询出记录来返回true,否则就是false，并且查询出来的记录的具体的值是NULL也可以,也是返回true.）

语法：select语句 where exists(select 语句);

not exits（与exists相反）

记录联合（我们常常会碰到需要将两个表或者多个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示这是就需要用到记录联合）

多个select 语句用UNION或者UNION ALL隔开即可实现

区别： 前者会将多个查询结果合并后并且进行去除重复后返回，后者 则直接合并并不去除重复

联合的条件：查询的列个数要相等

更新记录

更新一个表

Update 表名 set 字段1=值1,字段2=值2,...,字段n=值n[where条件];

更新多个表中数据

Update 表1,表2,...表n  set 表1.字段1=表达式1,...,表n.字段n=表达式n[where条件];

注：多表更新更多是用在根据一个标的字段来动态更新另一表的字段

推荐学习：php视频教程

 

The above is the detailed content of What are the basic knowledge points of databases in PHP? Basic statement? basic concept?. For more information, please follow other related articles on the PHP Chinese website!