数据库杂记2

DML

插入
方式一insert into xxx(colume1,column2…) values (,…);
方式二insert into xxx set 列名=值，列名=值…
方式一可插多行，可子查询，方式二均不行
修改
update xxx set 列=值，列=值 where …;
update修改多表的记录

删除
delete from xxx where…
truncate table xxx清空表
删除多表：


比较：1.若要删除的表中有自增长列，如果用delete删除，在插入数据，自增长从断点开始，而truncate从1开始
2.truncate没有返回值，delete有
3.truncate不能回滚，delete可以
4.truncate效率略高
5.一个能加where,一个不能

DDL

库
create database [if not exists] xxx;
drop database [if exists]xxx
更改库的字符集
alter database books character set gbk;
表
create table xxx(
列名 类型(长度) 约束，
列名 类型(长度) 约束，。。
)
表修改
1修改列名alter table book change column olddate newdate DATETIME
2修改列的类型或约束alter table book MODIFY COLUMN newdate TIMESTAMP;
3.添加新列alter table author ADD COLUMN annual DOUBLE
4.删除列alter table author DROP COLUMN anual;
5.修改表名alter table author RENAME TO author2
表删除 drop table [if exists] xxx
表的复制
1.仅表结构create table copy like oldtable;
2.结构加数据(可以只复制部分)
create table copy2 select * from oldtable;
3.仅仅复制某些字段(无数据)
create table copy select id,name from xxx where 1=2;
数据类型
一.整形

如何设置无符号和有符号？
create table test(
t1 int,
t2 int unsigned
);
二.小数

1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)
M:整数部位+小数部位 D:小数位，如果超过范围，则插入临界值。
MD都可以省略，如果是decimal，则默认M是10，D是0；
如果是float和double，则会根据插入的值的精度来确定。
定点型的精确度较高，货币运算等字段可以使用。

三。字符型

其他：binary和varhinary用于保存较短的二进制，enum保存枚举，set保存集合
长文本：text/blob
四。日期型

约束
六大约束：NOT NULL / DEFAULT / PRIMARY KEY / UNIQUE / CHECK(mysql不支持) / FOREIGN KEY
约束分为列级约束(六个都支持，但外键约束没效果)，表级约束(除了非空、默认其他的都支持)

表级约束：
【constranit 约束名】可以省略
唯一约束只允许一个是null
主键至多有一个，唯一可有多个，均支持组合(primary key(id,name))
外键主表关联列一般是主键或唯一

修改表时添加约束
alter table stuinfo modify column stuname varchar(12) not null

TCL

存储引擎:MySQL中的数据用各种不同的技术存储在文件（或者内存）中。
查看MySQL支持的存储引擎：show engines
MyISAM： 拥有较高的插入，查询速度，但不支持事务
InnoDB ：5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定
BDB： 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性
Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
insert/update/delete会开启隐式事务
显式事务：有明显的开始和结束标记，需要禁用自动提交功能


设置回滚点：

视图：
MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。
应用场景：
多个地方用到同样的查询结果
该查询结果使用的sql较复杂

视图的修改：
create or replace view 视图名 as 查询语句
或 alter view 视图名 as 查询语句
删除视图：
drop view 视图名,视图名…
查看视图：
desc xxx
show create view xxx
视图的增删改可更新至原始表

系统变量：全局变量，会话变量
自定义变量：用户变量，局部变量
系统变量：
1.查看所有的系统变量/会话变量
show global | [session] variables;
2.查看满足条件的部分系统变量
show global | [session] variables like ‘%char%’;
3.查看指定的系统变量的值
select @@变量名 默认为session
select @@global|session .系统变量名
4.赋值
set global | [session] 系统变量名 = 值；或
set @@global | [sesion].系统变量名 = 值；
服务器每次启动将为所有的全局变量赋初始值，针对所有的会话有效，但不能跨重启
自定义变量：
1.用户变量：针对当前会话(连接)有效
声明并初始化：

赋值/更新值同【声明并初始化】
赋值/更新值还可以select 字段 into @变量名 from 表，查询结果只能是一个值
使用：select @变量名
可应用在任何地方，begin end里外均可
2.局部变量(仅仅在定义他的begin end中有效，且必须是第一句话)

存储过程是一组预先编译好的sql的集合，一次编译后永久有效，理解成批处理语句；减少编译和数据库连接次数，提高效率
语法：
create procedure 存储过程名(参数列表)
begin
一组合法的sql语句
end
参数列表包括三部分
参数模式 参数名 参数类型
如：IN stuname VARCHAR(20)
参数模式：IN OUT INOUT(该参数既需要传入值，又可以返回值)
注意：如果存储过程只有一句话，则begin end可以省略
存储过程体中的每条sql语句的结尾必须加分号
存储过程的结尾可以使用delimiter重新设置
delimiter 结束标记(比如$)
调用方法：
call 存储过程名(实参列表)
空参存储过程：

调用：call myp1() $

创建带out的存储过程

删除存储过程：drop procedure 名
一次最多只能删除一个
查看存储过程：show create procedure 名
函数

调用语法：
select 函数名(参)

函数的查看：
show create function xxx
删除函数：
drop function xxx

if结构

循环结构：只能用在begin end中
while、loop、repeat
循环控制：
iterate结束本次循环，继续下一次
leave跳出循环
1.while
【标签:】while 条件 do
循环体；
end while [ 标签]
2.loop
[标签：]loop
循环体；
end loop [ 标签];
3.repeat
[标签：]repeat
循环体；
until 结束条件
end repeat [ 标签];