PHP operation MySql (Basic introduction to MySql)

MySql basic use

Commonly used field data types in MySql

(1) INT regular integer, signed value range: -231~231-1, unsigned value range: 0 ~ 2 to the 32nd power -1
(2) VARCHAR is used to represent variable-length character strings
(3 )CHAR A fixed-length character string. If it does not reach the fixed length, it will automatically be filled with spaces.
(4) UNSIGNED is used to set the field data type to be unsigned.
(5) PRIMARY KEY is used to set the primary key. A record in the unique identifier can be used as a unique variable to distinguish each variable. The attribute or attribute set used to uniquely identify the original group (that is, there can be multiple attributes to determine a primary key) is called the primary key, and the primary key has its own NOT NULL attribute.
(6) AUTO_INCREMENT is used to indicate automatic growth, and each additional item Record, this field will be automatically incremented by 1.
(7) NOT NULL means that the field does not allow NULL (cannot be empty), and is used for account passwords, etc.
(8) TEXT is used to store relatively long character strings or binary data
(9) DATETIME is the data type for saving date/time, and the length cannot be specified
(10) BOOL Boolean data

MySql's management of data tables (use of MySql statements)

The four sentences can be added, deleted, modified, and checked for the created data table. SQL language, the structured query language.
(1) Select statement-query record
(2) Insert statement-add record
(3) Delete statement-delete record
(4) Update-update record

Select statement

The Select statement is used to query the database and query records (rows) or fields (columns) that meet specific conditions from related tables in the database.
The syntax is as follows: select from [where condition], [order field], [group by field], [limit start,number].
Syntax example:
basic usage

select * from student/*查询表中的所有字段*/
select sno,sname from student/*查询表中的指定元素*/
select * from student limit 5/*查询前五条数据*/
select * from student limit 0,5/*查询从5开始的后五条数据*/
select * from student limit 5,10/*查询从5开始的后十条数据*/
select * from student where sage>25/*从数据库中选择student这个表,然后查询这个表里面叫sage的,选择sage>25的字段*/
select * from student where name='Lumxi'/*从数据库中选择student这个表,然后查询这个表里面叫name的,选择name==Lumxi的字段*/
select sno,sname from student where sage between 18 and 20/*从数据库中选择student这个表,然后查询这个表里面叫sno和name的,选择其中关联的对应sage在18和20之间的字段*/
select sno,sname form student where sage in(19,20,21)/*从数据库中选择student这个表,然后查询这个表里面叫sno和name的,选择其中关联的对应sage满足19 20 21的字段*/

Fuzzy search and orderly display use

select * from student where sname like '%杰%'/*从数据库中选择student这个表,然后查询这个表中sname,可以找到里面所有含有杰的元素,其中%表示0个或者多个字符,可以匹配任意类型和长度的字符*/
select * from student where sname like '张%'/*找到所有姓张的人*/
select * from student where sanem like '唐_'/*匹配单个任意字符*/
select * from student order by sage asc/*表示按照sage,然后升序排序*/
select * from student order by sage dasc/*表示按照sage,然后降序排序*/
select count(*) from student/*查询该表中共有多少条记录*/
select avg(sage),min(sage),max(sage) from student /*从表中查询对应的数据*/

Use of as keyword: take alias

select count(*) as "学生人数" from student/*把记录总数命名为学生人数*/
select avg(sage) as "平均年龄",min(sage) as "最小年龄",max(sage) as "最大年龄" from student

The use of the Distinct keyword: remove duplicate elements in the table

select distinct ssex from student

Grouping query function: group by clause: aggregate functions can be used in conjunction with group by statements to realize how many records are in the query table for classification statistics.

select sdept,count(*) as "各系人数" from student group by sdept/*按照sdept分类统计人数*/
select ssex,count(*) as "人数" from student group by ssex/*按照性别分类统计人数*/

Select group-having: Specify the group or aggregation conditions. Only the groups that meet the conditions are selected. Having is usually used with the group by clause.

select sno, count(*)
from grade
group by sno
having count(*) > 3

Multi-table join query: The query field involves 2 or more tables, and the table can be joined in advance if there are common fields between the tables.

/*查询选修了高数课程的学生姓名及其成绩:*/
select student.sno, student.sname, sc.grade
from student, sc, course
where student.sno=sc.sno and sc.cno=course.cno and course.cname="高数"

Insert statement

The basic syntax means: insert into table (field 1, field 2, …) values ​​(the value of field 1, the value of field 2, …)

//实例:

insert into student values ("201720202701", "陈小乔", "女", 18,'计算机' )
insert into student (sname, ssex, sage) values ("吴小芬", "女", 19) 

/*个人比较习惯第二种的方法*/

Delete statement

Basic syntax: delete from table where condition

//实例:
delete from student where sno="201720202701"
delete from student where ssex="女" and sage>22

Update statement

Basic syntax: update table set field 1 = field value 1, field 2 = field value 2,… where conditions

//实例:
update student set sdept="生物工程" where sno="201720202701"

The above is just a simple understanding, this time the database development is still using PHPMyadmin

Focus: PHP operation MySql

In php, it is mainly done by operating mqsqli, and the default extension has been installed.

1. Establish and close the connection with the MySql server

(1) Connect to the specified MySql server

   //也可以提前定义好字符串,然后输入函数中。
   $link=mysqli_connect('localhost','root','','',3307);
   var_dump($link);

As the above code, the establishment of a database connection in PHP is the host name, user name, password, database name, port name first.
When the password is wrong, there is

   if(mysqli_connect_errno())
   {
     exit(mysqli_connect_errno());
   }

You can also create a database directly in PHP

// 创建数据库
$sql = "CREATE DATABASE myDB";//新数据库命命
if ($conn->query($sql) === TRUE) {//
    echo "数据库创建成功";
} else {
    echo "Error creating database: " . $conn->error;
}
$conn->close();
mysqli_query($conn, "set character set 'utf8'");//读库 
mysqli_query($conn,"set names 'utf8'");//写库

Create a database and write specific parameters in the form. You can also use PHPMyadmin to create a table and write a table

// 使用 sql 创建数据表
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
 
if ($conn->query($sql) === TRUE) {
    echo "Table MyGuests created successfully";
} else {
    echo "创建数据表错误: " . $conn->error;
}

Follow this format for error reporting.

   if(mysqli_connect_errno())
   {
     echo "Your DataBase Password is not corrected!";
     exit(mysqli_connect_errno());
   }

Set the encoding format of php

   mysqli_set_charset($link,utf8);

Set to utf-8 encoding format

  mysqli_select_db($link,'student');

Select database or switch database. When the database token is specified, you can switch the database through this statement.

mysqli_close($link);

Close the connection between MySql and the server

Second, execute SQL statements in PHP coding

(1) The basic operation template, in fact, is to write the operand first and then pass it to the interface.

   $query="select * from student ";/*设定好Mysql语句*/
   mysqli_query($link,$query);/*提供操作对象和操作数*/

Examples are as follows

   $query="insert into student (sno,sname,sdepet) values ('202025220227','张三','软件工程2班')";
   mysqli_query($link,$query);

There are quite a lot of functions in PHP for us to use, we can choose according to our needs during the development process, here we only learn how to call the results

Three, insert data into Mysql in PHP

There are four rules:
①SQL query statements in PHP must use single quotes ②String values
queried in SQL must be
quoted
③Numerical values ​​do not need quotation marks ④NULL values ​​do not need quotation marks

Statement 1: INSERT INTO statement is usually used to add a new record to the MySQL table (insert a single data)

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Among them, table_name is the name of a specific table in the database, followed by the name of each table item,
VALUES is followed by the value to be assigned

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";

For example, to insert a new student in the student table, the operation is as follows:

<?php
   $servername="localhost";
   $username="root";
   $password="";
   $dbname="students";
   $conn= new mysqli($servername,$username,$password,$dbname);
   if($conn->connect_errno)
   {
     die("连接失败!".$conn->connect_errno);
   }
   mysqli_set_charset($conn,'utf8');
   $sql="INSERT INTO student (sno,sname,sdepet)
   VALUES('202025220229','张夫','20级计算机2班')";
   if($conn->query($sql)==true)
   {
     echo "成功!";
   }
   else
   {
     echo "寄!";
   }
   $conn->close();
?>

Statement 2: mysqli_multi_query() function can be used to execute multiple SQL statements.

   $sql  ="INSERT INTO student (sno,sname,sdepet)
   VALUES('202025220230','孙夫','20级计算机2班');";
   $sql .="INSERT INTO student (sno,sname,sdepet)
   VALUES('202025220231','王夫','20级计算机2班');";
   $sql .="INSERT INTO student (sno,sname,sdepet)
   VALUES('202025220232','李夫','20级计算机2班');";
   if($conn->multi_query($sql)===true)
   {
     echo "成功!";
   }

Statement 3: MySql prepared statement

The principle of prepared statements is as follows: Create a SQL statement template and send it to the database. The reserved value is marked with the parameter "?", such as:

   $sql  ="INSERT INTO student (sno,sname,sdepet)
   VALUES(?,?,?)";

Database analysis, compilation, query optimization of SQL statement templates, and store the results without output.
Execution: Finally, the value bound by the application is passed to the parameter ("?" mark), and the database executes the statement. The application can execute the statement multiple times if the values ​​of the parameters are different.
Example of use:

   $Insert=$conn->prepare("INSERT INTO student (sno,sname,sdepet) VALUES(?,?,?)");
   $Insert->bind_param("sss",$sno,$sname,$sdepet);
   //设置参数并执行相关操作
   $sno="202025220234";
   $sname="王老五";
   $sdepet="20软件工程3班";
   $Insert->execute();

According to this, you can directly write related interfaces.
Among them, the use of bind_param function is
specified. The first parameter is used to specify the type of the variable, as follows:
i-integer (integer)
d-double (double-precision floating point)
s-string (string)
b-BLOB ( Boolean value)
then the sss here refers to three string-shaped variables

Four, read data to MySql in PHP

Key statement: SELECT (used to select relevant data that meets the requirements from the database) The
usage example is as follows:

   $sql="SELECT * FROM student";
   $result=mysqli_query($conn,$sql);
   if(mysqli_num_rows($result))
   {
     while($row = mysqli_fetch_assoc($result))
     {
       echo "学号:".$row["sno"]." "."姓名".$row["sname"]." "."专业班级:".$row["sdepet"]."<br>";
     }
   }

Note: The mysqli_fetch_assoc() function gets a row from the result set as an associative array. That is, take a line from the result to print.
mysqli_num_rows refers to the number of rows of data in the database. If there is data, it will be printed, otherwise it will not be entered.

Five, where clause

Brief description: The WHERE clause is used to extract records that meet the specified criteria.

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

Example of use

   $sql="SELECT * FROM student WHERE sno ='202025220227'";
   $result=mysqli_query($conn,$sql);
   while($row =mysqli_fetch_assoc($result))
   {
     echo $row['sno'] . " " . $row['sname'];
     echo "<br>";
   }

Note: mysqli_fetch_assoc can also be replaced by mysqli_fetch_array,
which means to take out a row of data from the result set and use it

Six, order by statement

The ORDER BY keyword is used to sort the data in the record set.
The ORDER BY keyword sorts the records in ascending order by default. When you need to sort in descending order, you need to use the DESC keyword

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

Examples of usage are as follows:

   $sql="SELECT * FROM student ORDER BY sno";
   $result=mysqli_query($conn,$sql);
   while($row =mysqli_fetch_assoc($result))
   {
     echo $row['sno'] . " " . $row['sname'];
     echo "<br>";
   }

When sorting in descending order

   $sql="SELECT * FROM student ORDER BY sno DESC";
   $result=mysqli_query($conn,$sql);
   while($row =mysqli_fetch_assoc($result))
   {
     echo $row['sno'] . " " . $row['sname'];
     echo "<br>";
   }

You can also sort by multiple key values, and only use the first key value when it cannot be sorted.

Six, Updata statement

The UPDATE statement is used to update the records that already exist in the database table.
The WHERE clause specifies which records need to be updated. If you omit the WHERE clause, all records will be updated!
In order for PHP to execute the above statement, we must use the mysqli_query() function. This function is used to send queries or commands to the MySQL connection.

Instance

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
   $sql="UPDATE student SET sname='张奇夫' WHERE sno='202025220227' ";
   mysqli_query($conn,$sql);

Seven, DELETE statement

The DELETE FROM statement is used to delete records from a database table.

DELETE FROM table_name
WHERE some_column = some_value
$sql="DELETE FROM student WHERE sname='张奇夫' ";