SQL(三)DDL、DML、DCL总结

SQL(三)DDL、DML、DCL总结

表的创建

建表语法

create table 表名(
    字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型
);

指定默认值

创建student表并指定性别默认为男：

create table student(
    id int(8),
    name varchar(32),
    sex char(1) default '男'
);

常见数据类型

• varchar，可变长度字符串（最长255）

  优点：节省空间，根据实际数据长度动态分配空间

  缺点：速度慢

• char,定长字符串（最长255）

  优点：速度快

  缺点：使用不当会造成空间浪费

• int，等同于java中的int（最长11）

• bigint，等同于java中的long

• float，单精度浮点型数据

• double，双精度浮点型数据

• date，短日期类型，年月日

• datetime，长日期类型，年月日时分秒

• clob，字符大对象，最多可存储4G的字符串，超过255个字符时使用，比如存储一篇文章

• blob，二进制大对象，存储图片、声音、视频等流媒体数据（需使用IO流）

表的删除

删表语法

drop table 表名;
//如果表不存在上面语句会报错，下面不报错
drop table 表名 if exists;

插入数据

插入语法

插入一条：

insert into 表名(字段名1,字段名2,字段名3,字段名4) values(值1，值2，值3，值4);
举例:
insert into 表名(id,name,sex,age) values(101,'xpx','男',20);

插入多条：

insert into 表名(id,name,age) values(102,'xpx',20),(103,'cy',19),(104,'xxp',21);

注意：

每次insert都会增加一条记录，没有指定的字段为null

如果前面字段名都省略，相当于所有字段名都写了，那么后面values里的值要写全(不建议采用)

插入日期

如果插入时恰好写的是日期的标准格式，则可以直接插入：

insert into 表名(id,name,birthday) values(101,'xpx','2001-07-06');

mysql日期格式

%Y	年
%m	月
%d	日
%h	时
%i	分
%s	秒

format数字格式化

查询薪资转为千分位的格式：

select name,format(money,'$999,999') as money from 表名;

str_to_date字符串转日期date

如果插入时写的不是日期的标准格式，则需要使用该函数：

insert into 表名(id,name,birthday) values(101,'xpx',str_to_date('06-07-2001','%d-%m-%Y'));

date_format日期date转字符串

查询时会将date类型自动转换成varchar类型，想以特定的格式进行呈现需用该函数：

select id,name,date_format(birthday,'%m/%d/%Y') as birthday from 表名;

date和datetime

• date（短日期）

  mysql短日期默认格式：%Y-%m%d

• datetime（长日期）

  mysql长日期默认格式：%Y-%m%d %h:%i:%s

now()函数

插入时使用now()函数可以获得插入时间，now()函数可以获取当前datetime类型的时间

insert into 表名(id,name,birthday) values(101,'xpx',now());

修改数据

修改语法

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3 where 条件;
举例：
update 表名 set name='xpx',age=20,birthday='2001-07-06' where id=101;

注意：

若不加where条件则会修改所有数据

删除数据

delete删除语法(DML)

delete from 表名 where 条件;
举例：
delete from 表名 where id=101;

注意：

若不加where条件则会删除所有数据

优点：支持回滚，物理存储没有被删除，可恢复数据

缺点：删除效率低

回滚

//撤销刚才的操作，回到上一次的提交点，前提是开启事务start transaction;
rollback;

truncate删除语法(DDL)

//删除表中所有数据
truncate from 表;

优点：效率高，表被一次截断，物理删除

缺点：不能恢复数据

快速复制表

将查询结果当作一张表新建

create table 表2 as select * from 表1;

将查询结果插入一张表

insert into 表2 select * from 表1;

修改表

改表名

alter table 表名 rename to 新表名;

改表的字符集(编码)

alter table 表名 character set 字符集名称;

添加一个字段

alter table 表名 add 列名 数据类型;

修改字段名称、类型

alter table 表名 change 字段名 新字段名 新数据类型;
//只改类型
alter table 表名 modify 字段名 新数据类型;

删除字段

alter table 表名 drop 字段名;

约束

• 非空约束：not null
• 唯一性约束：unique
• 主键约束：primary key(简称PK)
• 外键约束：foreign key(简称FK)
• 检查约束：check(MySQL不支持，Oracle支持)

同时被not null和unique约束时自动变成主键约束(Oracle不是这样)

语法

建表时直接将约束置于数据类型后

create table 表名(
    字段名1 数据类型 约束,
    字段名2 数据类型 约束 约束 ...,
    字段名3 数据类型 约束
);

两字段联合起来具有唯一性

//此时id和name都相同才违反唯一性
create table student(
    id int(7),
    name varchar(255),
    email varchar(255),
    unique(name,email)
);

约束添加到字段后叫列级约束，上面是表级约束

not null只有列级约束

两字段联合起来做主键

//复合主键(不建议使用，一般用单一主键)
create table student(
    id int(7),
    name varchar(255),
    email varchar(255),
    primary key(name,email)
);

自增

//自然主键
create table student(
    id int primary key auto_increment,
    name varchar(255)
);

外键

创建表时先建父表再建子表

删除表时先删子表再删父表

插入数据时先保证父表中有对应数据

删除数据时先保证子表中该数据删除

语法

//t_student表的cla_no引用t_class表的class_no字段
create table t_class(
    class_no int primary key,
    class_name varchar(255)
);
create table t_student(
    S_no int primary key auto_increment,
    name varchar(255),
    cla_no int,
    foreign key(cla_no) references t_class(class_no)
);

被引用的字段要么有主键约束，要么有唯一性约束

指定存储引擎及字符编码方式

create table t_class(
    class_no int primary key,
    class_name varchar(255)
)engine=InnoDB default charset=gbk;

查看mysql支持的存储引擎

show engines \G

事务

事务：批量DML语句同时成功和同时失败

如：A给B转钱，A账户钱减少和B账户钱增加都成功才算成功

InnoDB存储引擎提供一组用来记录事务性活动的日志文件

事务开始：
语句1
语句2
语句3
...
事务结束

提交事务(commit)：清空事务性活动的日志文件，将数据全部彻底持久化到数据库中。提交事务标志事务的结束。是一种全部成功的结束。

回滚事务(rollback)：将之前所有的DML操作撤销，并清空事务性活动的日志文件。回滚也标志着事务的结束。是一种全部失败的结束。

注意：

MySQL默认清空下每执行一条语句自动提交，回滚只能回到上一个提交点，此时rollback不起作用。

可以通过start transaction开启事务，也就是关闭自动提交。

start transaction;
语句1;
语句2;
...
rollback;//回滚
语句3;
语句4;
commit;//提交

事务的四个特性

• 原子性：事务是最小的工作单元，不可再分。
• 一致性：同一事务中，所有操作要同时成功或同时失败，保证数据的一致性。
• 隔离性：各事务间相互独立，如：A事务在操作一张表时，B事务不能同时操作这张表。
• 持久性：事务结束的一个保障。事务提交或回滚后数据库会持久化保存数据。

隔离级别

• 读未提交：read uncommitted(最低级别隔离)

事务A能读取事务B未提交的数据(脏读，一般不用)

• 读已提交：read committed

事务A能读取事务B提交后的数据(不可重复读取数据，比如B正在执行，A不同时间读取到B的结果可能不同,但此时读取到的数据绝对真实，Oracle中默认的事务隔离级别)

• 可重复读：repeatable read

相当于备份数据，永远读取的都是刚开启事务时的数据(MySql中默认的事务隔离级别)

• 序列化/串行化：serializable(最高级别隔离)

效率最低，相当于事务排队，B事务执行完A才能执行

索引

数据库优化的重要手段

• 单一索引
• 复合索引
• 主键索引
• 唯一性索引

创建索引

给student表的name字段创建索引起名name_index

create index name_index on student(name);

主键约束和唯一性约束的情况下默认添加索引

唯一性较弱的字段添加索引用处不大

删除索引

删除student表的索引name_index

drop index name_index on student;

查看SQL语句是否使用索引

//只需在语句前加explain
explain select * from student where name='xpx';

索引失效

• 避免使用模糊查询，%开头没法索引
• 使用or时or两边都有索引才能索引
• 复合索引不使用左侧字段查找时失效
• 在where中索引字段参与运算时失效
• 在where中索引列使用函数时失效

视图

对视图对象进行增删改查时原表也会跟着变，语法与对表操作相同

作用:多表联查时把查询结果作为一个新表进行操作往往更加方便，比如一条update语句可以对多张表的数据进行修改

创建视图

create view student_view as select * from student;

删除视图

drop view student_view;

DCL命令

创建用户

create user '用户名'@'主机名' identified by '密码';
如下：
create user 'xpx'@'localhost' identified by 123;

若想在任意主机访问，将主机名改为通配符'%'

删除用户

drop user '用户名'@'主机名';

修改用户密码

可以直接用update语法

//法一
update user set password=新密码 where user='用户名';
//法二
set password for '用户名'@'主机名'=password('新密码');

忘记密码怎么办

1.管理员权限运行cmd,net stop mysql停止MySQL服务
2.无体验方式启动MySQL,mysql --skip-grant-tables
3.打开新的cmd窗口直接mysql回车，找到存放用户的表进行修改
4.打开任务管理器，手动结束mysql进程
5.管理员权限运行cmd,net start mysql开启MySQL服务

权限管理

查询权限

show grants for '用户名'@'主机名';

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
//给xpx查询和删除权限
grant select,delete on db1.student to 'xpx'@'%';

给xpx授予所有数据库所有表所有权限：

grant all on *.* to 'xpx'@'%';

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

数据导入导出

数据库导出

首先退出mysql,直接在windows的dos命令窗口

mysqldump 数据库名>保存地址(.sql文件) -u用户名 -p密码
//导出study数据库
mysqldump study>D:\study.sql -uroot -p123
//导出study数据库下的student表
mysqldump study student>D:\study.sql -uroot -p123

数据库导入

1.create数据库
2.use数据库
3.source .sql文件路径;