总结mysql 常用DDL, DML语言

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