0511 mysql基础 与 pdo作业

1. 总结mysql 常用DDL, DML语言

• 数据类型

  • 数值型 整形

整形	占用字节数	范围
tinyint	1	-128~127
smallint	2	-32768~32767
mediumint	3	-8388608~8388607
int	4	-2147483648~2147483647
bigint	8	-9223372036854775808~9223372036854775807

选择的范围尽可能小，范围越小占用资源越少
可选参数有
无符号整形（unsigned） 无符号整形就是没有负数，无符号整数是整数的两倍
整形支持显示宽度，显示宽带是最小的显示位数，如int(11)表示整形最少用11位表示，如果不够位数用0填充。显示宽度默认不起作用，必须结合zerofill才起作用。
num int(5) zerofill # 添加前导0,int(5)显示宽带是5
1、范围要尽可能小，范围越小，占用空间越少
2、无符号整数是整数的两倍
3、整形支持显示宽度，显示宽带是最小的显示位数，必须结合zerofill才起作用

• 浮点型
  浮点型 占用字节数 范围
  float（单精度型） 4 -3.4E+38~3.4E+38
  double（双精度型） 8 -1.8E+308~1.8E+308

浮点型的声明：float(M,D) double(M,D)
M：总位数 D：小数位数
1、浮点数有单精度和双精度
2、浮点数支持科学计数法
3、浮点数精度会丢失

• 小数（定点数
  原理：将整数部分和小数部分分开存储
  语法：
  decimal(M,D)
  1、decimal是变长的，大致是每9个数字用4个字节存储，整数和小数分开计算。M最大是65,D最大是30，默认是（10,2）。
  2、定点和浮点都支持无符号、显示宽度0填充。

• 字符型
  在数据库中没有字符串概念，只有字符，所以数据库中只能用单引号

数据类型	描述
char	定长字符，最大可以到255
varchar	可变长度字符，最大可以到65535
tinytext	2**8–1 =255
text	2**16–1 =65535
mediumtext	2**24–1
longtext	2**32–1

1、char是定长，varchar是变长
2、char最大值是255，varchar最大值是65535，具体要看字符编码
3、text系列在表中存储的是地址
4、一条记录的总长度不能超过65535

• 日期时间型

数据类型	描述
datetime	日期时间，占用8个字节
date	日期 占用3个字节
time	时间 占用3个字节
year	年份，占用1个字节
timestamp	时间戳，占用4个字节

1、datetime和date
datetime格式：年-月-日 小时:分钟:秒。支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。
2、timestamp（时间戳）
datetime类型和timestamp类型表现上是一样的，他们的区别在于：
datetime从1000到9999，而timestamp从1970年~2038年（原因在于timestamp占用4个字节，和整形的范围一样，2038年01月19日11:14:07以后的秒数就超过了4个字节的长度）
3、year
只能表示1901~2155之间的年份，因为只占用1个字节，只能表示255个数
4、time
可以表示时间，也可以表示时间间隔。范围是：-838:59:59~838:59:59

• 枚举(enum)
  从集合中选择一个值作为数据（单选）
  枚举值是通过整形数字来管理的，第一个值是1，第二个值是2，以此类推，枚举值在数据库存储的是整形数字。
  枚举优点：
  （1）、限制值
  （2）、节省空间
  （3）、运行速度快（整形比字符串运行速度快）
  枚举占用两个字节，2字节=16位，216=65536，范围是（0-65535），由于枚举从1开始，所以枚举值最多有65535个

• 集合(set)
  从集合中选择一些值作为数据（多选）
  集合和枚举一样，也为每个集合元素分配一个固定值，分配方式是从前往后按2的0、1、2、…次方，转换成二进制后只有一位是1，其他都是0。
  集合类型占8个字节,集合中最多有64个选项.

• 表的操作

  • 创建表
    语法：
    create table [if not exists] 表名(
    字段名 数据类型 [null|not null] [default] [auto_increment] [primary key] [comment],
    `字段名 数据类型 …
    )[engine=存储引擎] [charset=字符编码]

null not null	是否为空
default:	默认值
auto_increment	自动增长,默认从1开始，每次递增1
primary key	主键，主键的值不能重复，不能为空,每个表必须只能有一个主键
comment:	备注
engine	引擎决定了数据的存储和查找 myisam、innodb

表名和字段名如果用了关键字，要用反引号引起来。

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '帐号',
  `sex` enum('先生','女士') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '先生' COMMENT '性别',
  `age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• 修改表
  1、添加字段：alter table 表名add [column] 字段名 数据类型 [位置]
  位置:first 最前面 after[列] 在*列之后 默认是在表最后
  2、删除字段：alter table 表 drop [column] 字段名
  3、修改字段(改名)：alter table 表 change [column] 原字段名 新字段名 数据类型
  4、修改字段（不改名）:alter table 表 modify 字段名 字段属性…
  5、修改引擎：alter table 表名 engine=引擎名
  6、修改表名:alter table 表名 rename to 新表名
  7、将表移动到其他数据库
  alter table 表名 rename to 新库.新表名

  alter table student rename to php74.stu;

• 删除表
  drop table [if exists] 表1，表2,…
• 显示所有表
  show tables;
• 显示创建表的语句
  show create table; — 结果横着排列
  show create table \G — 将结果竖着排列
• 查看表结构
  desc[ribe] 表名
• 复制表
  语法一：create table 新表 select 字段 from 旧表
  特点：不能复制父表的键，能够复制父表的数据
  语法二：create table 新表 like 旧表
  特点：只能复制表结构，不能复制表数据
• 删除表
  drop table [if exists] 表1，表2,…

• 数据操作 (增 删 改 查)

  • 插入数据
    语法：insert into 表名 (字段名, 字段名,…) values (值1, 值1,…),(值2, 值2,…)
    1、插入字段名的顺序和数据表中字段名的顺序可以不一致
    2、插入值的个数、顺序必须和插入字段名的个数、顺序要一致。
    3、如果插入的值的顺序和个数与表字段的顺序个数一致，插入字段可以省略。
    default关键字用来插入默认值，null用来插入空值.

  • 更新数据
    语法：update 表名 set 字段=值 [where 条件]

  • 删除数据
    语法：delete from 表名 [where 条件]
    delete from 表和truncate table 表区别？
    1、delete from 表：遍历表记录，一条一条的删除
    2、truncate table：将原表销毁，再创建一个同结构的新表。就清空表而言，这种方法效率高。

  • 查询数据
    语法：select [选项] 列名 [from 表名] [where 条件] [group by 分组] [order by 排序][having 条件] [limit 限制]
    1 字段表达式
    通过as给字段取别名 as可以省略
    2 from子句
    from：来自，from后面跟的是数据源。数据源可以有多个。返回笛卡尔积。
    3 dual表
    dual表是一个伪表。在有些特定情况下，没有具体的表的参与，但是为了保证select语句的完整又必须要一个表名，这时候就使用伪表。
    4 where子句
    where后面跟的是条件，在数据源中进行筛选。返回条件为真记录
    MySQL支持的运算符
    — 比较运算符
    > 大于
    < 小于
    >= 大于等于
    <= 小于等于
    = 等于
    != 不等于
    — 逻辑运算符
    and 与
    or 或
    not 非
    — 其他
    in | not in 字段的值在枚举范围内
    between…and|not between…and 字段的值在数字范围内
    is null | is not null 字段的值不为空
    5 group by 【分组查询】
    将查询的结果分组，分组查询目的在于统计数据。
    查询字段是普通字段，只取第一个值
    通过group_concat()函数将同一组的值连接起来显示
    1、 如果是分组查询，查询字段是分组字段和聚合函数。
    2、 查询字段是普通字段，只取第一个值
    3、 group_concat()将同一组的数据连接起来
    6 order by排序
    asc：升序【默认】
    desc：降序
    7 having条件
    having：是在结果集上进行条件筛选
    小结：having和where的区别：
    where是对原始数据进行筛选，having是对记录集进行筛选。
    8 limit
    语法：limit [起始位置]，显示长度
    起始位置可以省略，默认是从0开始
    9 查询语句中的选项
    查询语句中的选项有两个：
    1、 all：显示所有数据 【默认】
    2、 distinct：去除结果集中重复的数据

2. pdo操作数据库的对象方法;

• 实例化PDO对象
  DSN：data source name，数据源名称，包含的是连接数据库的信息，格式如下：
  $dsn=’数据库类型:host=主机地址;port=端口号;dbname=数据库名称;charset=字符集’;
  $db = new PDO($dsn,’用户名’,’密码’);
  数据库类型：
  MySQL数据库 => mysql:
  oracle数据库 => oci:
  SQL Server =>sqlsrv:
  具体驱动类型参见手册“PDO驱动”
  注意事项
  1、如果连接的是本地数据库，host可以省略
  2、如果使用的是3306端口，port可以省略
  3、charset也省略，如果省略，使用的是默认字符编码
  4、dbname也可以省略，如果省略就没有选择数据库
  5、host、port、dbname、charset不区分大小写，没有先后顺序
  6、驱动名称不能省略，冒号不能省略（因为冒号是驱动名组成部分），数据库驱动只能小写

$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=user';
$username = 'root';
$password = 'root123';
$db = new PDO($dsn, $username, $password);
var_dump($db);  //输出 object(PDO)#1 (0) { }

• PDO执行数据查询语句query($sql)
  方法：$pdo->query($sql)
  返回的是PDOStatement对象
  1 执行查询语句

// 1. 执行查询语句
$stmt = $db->query('select * from users');
//var_dump($stmt);  //输出 object(PDOStatement)#2 (1) { ["queryString"]=> string(19) "select * from users" }

2 获取数据
2.1 获取二维数组fetchAll()

// 获取二维数组
$rs = $stmt->fetchAll();  //默认返回关联和索引数组
$rs=$stmt->fetchAll(PDO::FETCH_BOTH);     //返回关联和索引数组
$rs = $stmt->fetchAll(PDO::FETCH_NUM);  //返回索引数组
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);  //返回关联数组
$rs = $stmt->fetchAll(PDO::FETCH_OBJ);  //返回对象数组

2.2 获得一维数组fetch()

// 获得一维数组
$rs = $stmt->fetch();  // 匹配完成后指针下移一条 数据1
var_dump($rs);
$rs = $stmt->fetch(PDO::FETCH_NUM);  // 数据2
var_dump($rs);
//通过while循环获取所有数据
$rs = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $rs[] = $row;
}
echo '<pre>';
var_dump($rs);

3 匹配列：fetchColumn()匹配当前行的第n列，列的编号从0开始，匹配完毕后指针下移一条

//获取当前数据行的第0列值
echo $stmt->fetchColumn();  //输出 1
//获取当前数据行的第1列值
echo $stmt->fetchColumn(1);  //输出 user2   下移了

4 总行数，总列数rowCount() columnCount()

echo '总行数：'.$stmt->rowCount(),'<br>';  // 总行数：100
echo '总列数：'.$stmt->columnCount();  // 总列数：6

• PDO执行数据操作语句>exec($sql(insert update delete)
  方法: $pdo->exec($sql)
  执行数据增、删、改语句，执行成功返回受影响的记录数，如果SQL语句错误返回false。

  $res = $db->exec("insert into `users` values (null,'users101','先生',20,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)");
  echo $res;  //输出 1

  $res = $db->exec("update `users` set username='王五' where id=101");
  echo $res;  //输出 1

  $res = $db->exec("DELETE FROM `users` WHERE id=101;");
  echo $res; //输出 1