php70w mysql.x8664_PHP-MySQL基本操作

1 <?php2

3 //1.防止页面中文乱码

4 header("content-type:text/html;charset=utf-8");5

6 //链接数据库

7 @$conn = new mysqli('localhost', 'root', '');8 echo $conn == true ? '数据库连接成功' : '数据库连接失败';9

10 //2.连接到MySQL后设置字符集(防止存入中文乱码)

11 mysqli_set_charset($conn, 'utf8');12

13 //创建数据库

14 $createDatabaseSql = "create database ProsperLee01 charset utf8";15 //删除数据库

16 $dropDatabaseSql = "drop database ProsperLee01";17 //查询数据库

18 $showDatabaseSql = "show databases";19 //查询创建的数据库信息

20 $showCreateDatabaseSql = "show create database ProsperLee01";21 //修改数据库(不能修改数据库的名字，只能修改字符集和校对集)语法：alter database 数据库 character set=新的字符集 collate=新的校对集

22 $alterDatabaseSql1 = "alter database ProsperLee01 character set=gbk collate=gbk_Chinese_ci";23 $alterDatabaseSql2 = "alter database ProsperLee01 charset=utf8 collate=utf8_general_ci";24 //查询字符集

25 $showCharsetSql = "show charset";26 //查询校对集

27 $showCollationSql = "show collation";28 //使用数据库

29 $useDatabaseSql = "use ProsperLee01";30 //创建数据表 语法 CREATE TABLE 库名 表名 (字段名 字段类型 字段选项, 字段名 字段类型 字段选项···) 表选项; (表选项：表字符集(charset=utf8)和数据引擎(engine=引擎))

31 $createTableSql =<<

34 `sex` varchar(5) NULL COMMENT '性别',

35 `age` int(2) NULL COMMENT '年龄',

36 `index` int NOT NULL AUTO_INCREMENT COMMENT '序号',

37 `id` varchar(8) NOT NULL COMMENT 'id',

38 PRIMARY KEY (`index`,`id`)39 ) ENGINE=InnoDB DEFAULT CHARSET=utf840 sss;41 //查询数据表

42 $showTablesSql = "show tables";43 //查看数据表结构

44 $descTableSql = "desc lee01";45 //查询创建的数据表

46 $showCreateTableSql = "show create table Lee01";47 //修改表名 alter table 旧表名 rename [to] 新表名

48 $alterTableNameSql = "alter table lee01 rename to newlee01";49 //添加新字段

50 $alterTableAddFieldSql = "alter table lee01 add realname varchar(50) not null default '用户名' comment '真实姓名'";51 //删除字段 alter table 表名 drop column 字段名

52 $alterTableDropFieldSql = "alter table lee01 drop column phone";53 //修改表的字段类型 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型定义;

54 $alterTableModifyFieldSql = "ALTER TABLE lee01 MODIFY COLUMN id VARCHAR(32)";55 //修改表的字段名(修改后原始字段存的值不会变，新存入的值不填写变成默认值) alter table 表名 change 原字段名 新字段名 字段的类型

56 $alterTableChangeFieldSql = "ALTER TABLE lee01 change realname phone VARCHAR(11) default '01234567890' comment '电话号'";57 //插入数据 INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

58 $insertIntoDataSql1 = "INSERT INTO Lee01 (`sex`,`age`,`id`) VALUES ('女',20,'88888888')";59 $insertIntoDataSql2 = "INSERT INTO Lee01 (`name`,`sex`,`age`,`id`) VALUES ('Lee','女',20,'66666666')";60 $insertIntoDataSql = <<

64 ( '李四', '男', 18, '87654321' ),

65 ( '王五', '女', 20, '22222222' ),

66 ( '赵六', '女', 21, '11111111' ),

67 ( '八爷', '男', 26, '14725836' ),

68 ( '九少', '男', 16, '66666666')69 sss;70 //查询语句

71 $selectDataSql1 = "select * from lee01";72 $selectDataSql2 = "select * from lee01 where sex = '女' and age = '20'";73 $selectDataSql3 = "select * from lee01 where age = '20' or age = '16'";74 $selectDataSql4 = "select * from lee01 where not age = '20'";75 $selectDataSql5 = "select name,age from lee01 where age = '20' or age = '16'";76 //in 查询指定index (无则跳过)

77 $selectDataSql6 = "select name,age from lee01 where `index` in (1,2,4,6)";78 //between 语法：select 查询字段 from 表名 where 字段 between 最小值 and 最大值;

79 $selectDataSql7 = "select name from lee01 where `index` between 2 and 5";80 //查询数据量(启用别名)

81 $selectDataSql8 = "select count(`index`) as count from lee01";82 //like 模糊查询(注：直接写字符，严格匹配)%匹配任何字符

83 $selectDataSql9 = "select * from lee01 where name like '%三%'";84 //group by (只能查看分组后的数量，而不能查看分组后的信息)

85 $selectDataSql10 = "select count(sex),sex from lee01 group by sex";86 //查询性别数量大于2个的性别的数量

87 $selectDataSql11 = "select count(sex) as sexnum,sex from lee01 group by sex having sexnum>2";88 //查询正序(asc)倒序(desc)

89 $selectDataSql12 = "select * from lee01 order by id asc";90 //分页查询 (limit 下标,查询数量)

91 $selectDataSql13 = "select * from lee01 order by `index` asc limit 0,2";92 $selectDataSql14 = "select * from lee01 order by `index` asc limit 2,2";93 $selectDataSql15 = "select * from lee01 order by `index` asc limit 4,2";94 //删除数据表

95 $dropTableSql = "drop table lee01";96 //清空数据表(同时删除自增字段的定义)

97 $truncateTableSql1 = "truncate table lee01";98 //清空数据表(删除表中的所有行，一行一行的删)(delete 保留原有自增字段，再次创建不占用删除字段)

99 $deleteTableSql1 = "delete from lee01";100 //按条件删除 delete from 表名 [where条件] [order排序] [limit限定](删除范围数据可能会用到order、limit)

101 $deleteTableSql2 = "delete from lee01 where phone = '用户名'";102 //修改数据(无条件全改)

103 $updateSetSql = "update lee01 set name = 'ProsperLee',age=23 where id = '87654321'";104

105 //使用数据库

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

108 // 3.设置通信编码

109 mysqli_query($conn, "set names utf8");110

111 //执行sql语句

112 $result = mysqli_query($conn, $selectDataSql1);113 var_dump($result);114

115 /**116 * fetch_assoc() 每次拿1条数据117 * fetch_all() 拿出全部数据118 */

119 $showData = $result->fetch_all();120 var_dump($showData);121

122 //关闭数据库

123 mysqli_close($conn);