Detailed explanation of the basic operation code for calling MySQL database in PHP

This article mainly talks about the basic operation codes and explanations for calling MySQL database in PHP. It has certain reference value. Friends in need must take a good look!

##PHP-MySQL basic operation

 <?php

// 1.防止页面中文乱码
header("content-type:text/html;charset=utf-8");

// 链接数据库
@$conn = new mysqli(&#39;localhost&#39;, &#39;root&#39;, &#39;&#39;);
echo $conn == true ? &#39;数据库连接成功&#39; : &#39;数据库连接失败&#39;;

// 2.连接到MySQL后设置字符集(防止存入中文乱码)
mysqli_set_charset($conn, &#39;utf8&#39;);

// 创建数据库
$createDatabaseSql = "create database ProsperLee01 charset utf8";
// 删除数据库
$dropDatabaseSql = "drop database ProsperLee01";
// 查询数据库
$showDatabaseSql = "show databases";
// 查询创建的数据库信息
$showCreateDatabaseSql = "show create database ProsperLee01";
// 修改数据库（不能修改数据库的名字，只能修改字符集和校对集）语法：alter database 数据库 character set=新的字符集 collate=新的校对集
$alterDatabaseSql1 = "alter database ProsperLee01 character set=gbk collate=gbk_Chinese_ci";
$alterDatabaseSql2 = "alter database ProsperLee01 charset=utf8 collate=utf8_general_ci";
// 查询字符集
$showCharsetSql = "show charset";
// 查询校对集
$showCollationSql = "show collation";
// 使用数据库
$useDatabaseSql = "use ProsperLee01";
// 创建数据表 语法 CREATE TABLE 库名 表名 (字段名 字段类型 字段选项, 字段名 字段类型 字段选项···) 表选项; （表选项：表字符集(charset=utf8)和数据引擎(engine=引擎)）
$createTableSql =<<<sss
    CREATE TABLE `prosperlee01`.`Lee01` (
        `name` varchar(20) DEFAULT &#39;小明同学&#39; NOT NULL COMMENT &#39;姓名&#39;,
        `sex` varchar(5) NULL COMMENT &#39;性别&#39;,
        `age` int(2) NULL COMMENT &#39;年龄&#39;,
        `index` int NOT NULL AUTO_INCREMENT COMMENT &#39;序号&#39;,
        `id` varchar(8) NOT NULL COMMENT &#39;id&#39;,
        PRIMARY KEY (`index`, `id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
sss;
// 查询数据表
$showTablesSql = "show tables";
// 查看数据表结构
$descTableSql = "desc lee01";
// 查询创建的数据表
$showCreateTableSql = "show create table Lee01";
// 修改表名 alter table 旧表名 rename [to] 新表名
$alterTableNameSql = "alter table lee01 rename to newlee01";
// 添加新字段
$alterTableAddFieldSql = "alter table lee01 add realname varchar(50) not null default &#39;用户名&#39; comment &#39;真实姓名&#39;";
// 删除字段 alter table 表名 drop column 字段名
$alterTableDropFieldSql = "alter table lee01 drop column phone";
// 修改表的字段类型   ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型定义;
$alterTableModifyFieldSql = "ALTER TABLE lee01 MODIFY COLUMN id VARCHAR(32)";
// 修改表的字段名(修改后原始字段存的值不会变，新存入的值不填写变成默认值) alter table 表名 change 原字段名  新字段名  字段的类型
$alterTableChangeFieldSql = "ALTER TABLE lee01 change realname phone VARCHAR(11) default &#39;01234567890&#39; comment &#39;电话号&#39;";
// 插入数据 INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
$insertIntoDataSql1 = "INSERT INTO Lee01 (`sex`,`age`,`id`) VALUES (&#39;女&#39;,20,&#39;88888888&#39;)";
$insertIntoDataSql2 = "INSERT INTO Lee01 (`name`,`sex`,`age`,`id`) VALUES (&#39;Lee&#39;,&#39;女&#39;,20,&#39;66666666&#39;)";
$insertIntoDataSql = <<<sss
    INSERT INTO Lee01 ( `name`, `sex`, `age`, `id` )
    VALUES
        ( &#39;张三&#39;, &#39;女&#39;, 16, &#39;12345678&#39; ),
        ( &#39;李四&#39;, &#39;男&#39;, 18, &#39;87654321&#39; ),
        ( &#39;王五&#39;, &#39;女&#39;, 20, &#39;22222222&#39; ),
        ( &#39;赵六&#39;, &#39;女&#39;, 21, &#39;11111111&#39; ),
        ( &#39;八爷&#39;, &#39;男&#39;, 26, &#39;14725836&#39; ),
        ( &#39;九少&#39;, &#39;男&#39;, 16, &#39;66666666&#39; )
sss;
// 查询语句
$selectDataSql1 = "select * from lee01";
$selectDataSql2 = "select * from lee01 where sex = &#39;女&#39; and age = &#39;20&#39;";
$selectDataSql3 = "select * from lee01 where age = &#39;20&#39; or age = &#39;16&#39;";
$selectDataSql4 = "select * from lee01 where not age = &#39;20&#39;";
$selectDataSql5 = "select name,age from lee01 where age = &#39;20&#39; or age = &#39;16&#39;";
// in 查询指定index (无则跳过)
$selectDataSql6 = "select name,age from lee01 where `index` in (1,2,4,6)";
// between 语法：select 查询字段 from 表名 where 字段 between 最小值 and 最大值;
$selectDataSql7 = "select name from lee01 where `index` between 2 and 5";
// 查询数据量(启用别名)
$selectDataSql8 = "select count(`index`) as count from lee01";
// like 模糊查询（注：直接写字符，严格匹配）%匹配任何字符
$selectDataSql9 = "select * from lee01 where name like &#39;%三%&#39;";
// group by （只能查看分组后的数量，而不能查看分组后的信息）
$selectDataSql10 = "select count(sex),sex from lee01 group by sex";
// 查询性别数量大于2个的性别的数量
$selectDataSql11 = "select count(sex) as sexnum,sex from lee01 group by sex having sexnum>2";
// 查询正序(asc)倒序(desc)
$selectDataSql12 = "select * from lee01 order by id asc";
// 分页查询 （limit 下标,查询数量）
$selectDataSql13 = "select * from lee01 order by `index` asc limit 0,2";
$selectDataSql14 = "select * from lee01 order by `index` asc limit 2,2";
$selectDataSql15 = "select * from lee01 order by `index` asc limit 4,2";
// 删除数据表
$dropTableSql = "drop table lee01";
// 清空数据表(同时删除自增字段的定义)
$truncateTableSql1 = "truncate table lee01";
// 清空数据表（删除表中的所有行，一行一行的删）（delete 保留原有自增字段，再次创建不占用删除字段）
$deleteTableSql1 = "delete from lee01";
// 按条件删除 delete from 表名 [where条件] [order排序] [limit限定]（删除范围数据可能会用到order、limit）
$deleteTableSql2 = "delete from lee01 where phone = '用户名'";
// 修改数据(无条件全改)
$updateSetSql = "update lee01 set name = 'ProsperLee',age=23 where id = '87654321'";

// 使用数据库
mysqli_select_db($conn, 'ProsperLee01'); // 使用数据库

// 3.设置通信编码
mysqli_query($conn, "set names utf8");

// 执行sql语句
$result = mysqli_query($conn, $selectDataSql1);
var_dump($result);

/**
 * fetch_assoc() 每次拿1条数据
 * fetch_all() 拿出全部数据
 */
$showData = $result->fetch_all();
var_dump($showData);

// 关闭数据库
mysqli_close($conn);

If you want to learn PHP without taking any detours, just follow the PHP Chinese website for more

PHP related tutorials, mysql related tutorials are waiting for you to learn!

The above is the detailed content of Detailed explanation of the basic operation code for calling MySQL database in PHP. For more information, please follow other related articles on the PHP Chinese website!