首页 > 代码库 > 11. mysqli 学习

11. mysqli 学习

2024-08-12 07:23:32   210人阅读

内容参考 mysqli的官网，和w3c英文版

1. mysqli 连接

<?php$servername = "localhost";$username = "username";$password = "password";// Create connection$conn = new mysqli($servername, $username, $password);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);} echo "Connected successfully";?>

1.a

mysqli:

 

 

面向对象风格

mysqli::__construct ([ string $host = ini_get("mysqli.default_host") [, string $username = ini_get("mysqli.default_user") [, string $passwd = ini_get("mysqli.default_pw") [, string $dbname = "" [, int $port = ini_get("mysqli.default_port") [, string $socket = ini_get("mysqli.default_socket") ]]]]]] )
l         host：连接的服务器地址。
l         username：连接数据库的用户名，默认值是服务器进程所有者的用户名。
l         passwd：连接数据库的密码，默认值为空。
l         dbname：连接的数据库名称。
l         port：TCP端口号。
l         socket：UNIX域socket。
 
2 create database
<?php$servername = "localhost";$username = "username";$password = "password";// Create connection$conn = new mysqli($servername, $username, $password);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);} // Create database$sql = "CREATE DATABASE myDB";if ($conn->query($sql) === TRUE) {    echo "Database created successfully";} else {    echo "Error creating database: " . $conn->error;}$conn->close();?>

3. create table

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// Create connection$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);} // sql to create table$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 "Error creating table: " . $conn->error;}$conn->close();?>

  • NOT NULL - Each row must contain a value for that column, null values are not allowed
  • DEFAULT value - Set a default value that is added when no other value is passed
  • UNSIGNED - Used for number types, limits the stored data to positive numbers and zero
  • AUTO INCREMENT - MySQL automatically increases the value of the field by 1 each time a new record is added
  • PRIMARY KEY - Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT

 

4. insert data

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// Create connection$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);} $sql = "INSERT INTO MyGuests (firstname, lastname, email)VALUES (‘John‘, ‘Doe‘, ‘john@example.com‘)";if ($conn->query($sql) === TRUE) {    echo "New record created successfully";} else {    echo "Error: " . $sql . "<br>" . $conn->error;}$conn->close();?>

5. prepared statements

可以把一些常用的表达式存起来省的每次都query那么多

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// Create connection$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// prepare and bind$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");$stmt->bind_param("sss", $firstname, $lastname, $email);// set parameters and execute$firstname = "John";$lastname = "Doe";$email = "john@example.com";$stmt->execute();$firstname = "Mary";$lastname = "Moe";$email = "mary@example.com";$stmt->execute();$firstname = "Julie";$lastname = "Dooley";$email = "julie@example.com";$stmt->execute();echo "New records created successfully";$stmt->close();$conn->close();?>

5.a 

bind_param : 

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param(‘sssd‘, $code, $language, $official, $percent);

其中：

i corresponding variable has type integer
d corresponding variable has type double
s corresponding variable has type string
b corresponding variable is a blob and will be sent in packets

6. select data

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// Create connection$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);} $sql = "SELECT id, firstname, lastname FROM MyGuests";$result = $conn->query($sql);if ($result->num_rows > 0) {    // output data of each row    while($row = $result->fetch_assoc()) {        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";    }} else {    echo "0 results";}$conn->close();?>

6.a 

where：

example ：$sql = "SELECT * FROM MyGuests WHERE id = 2";

SELECT column FROM table
WHERE column operator value

运算符 说明
= 等于
!= 不等于
> 大于
< 小于
>= 大于或等于
<= 小于或等于
BETWEEN 介于一个包含范围内
LIKE 搜索匹配的模式

6.b

ordered by

SELECT column_name(s)
FROM table_name
ORDER BY column_name

如果降序：

SELECT column_name(s)
FROM table_name
ORDER BY column_name DESC

如果多列（第一列相同，比第二列）：

SELECT column_name(s)
FROM table_name
ORDER BY column_name1, column_name2

6.c 

update:

UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value

 

6.d

delete：

DELETE FROM table_name
WHERE column_name = some_value

 

 

 

 
 
11. mysqli 学习






























        

            
                
                  
                
                

                    联系

                    我们