MySQL-6 DML数据操作语言 插入insert修改update删除delete DDL数据定义语言 库和表的管理

DML数据操作语言(重点)

• 插入:insert
• 修改:update
• 删除:delete

插入语句

方式一

• 语法:(表名,列名,新值)

insert into 表名(列名,....) values(值1,值2,...);

• 要求:
  • 插入的值的类型要与列的类型一致或兼容
  • 不可以为null的列必须插入值,可以为null的列如何插入值:(方式一:null;方式二:去掉字段)
  • 列的顺序可以调换,但要与值的顺序保持对应
  • 列数和值的个数必须一致,且一一对应
  • 可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致

# 插入的值的类型要与列的类型一致或兼容
insert into 表名(列名1,2,3,4,5) values(13,'球球','女','1990-3-24','18933451133',null,2);
# 可以为null的列如何插入值
# 方式一:null
insert into 表名(列名1,2,3,4,5) values(13,'球球','女','1990-3-24','18933451133',null,2);
# 方式二:去掉字段
 insert into 表名(列名1,2,3,5) values(13,'球球','女','1990-3-24','18933451133',2);
# 列的顺序可以调换,但要与值的顺序保持对应
insert into 表名(列1,列3,列2) values(值1,值3,值2);
# 列数和值的个数必须一致,且一一对应
insert into 表名(列1,列3,列2) values(值1,值3,值2);
# 可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致
insert into 表名 values(值1,值2,值3,...);

方式二

• 语法:

insert into 表名 set 列名=值,列名=值,...;

insert into 表名 set id=19,name='球球',phone='999';

两种方式对比

• 方式一支持插入多行,批量插入,方式二不支持

insert into 表名 values(值1,值2,......),(值1,值2,....),(值1,值2,...);

• 方式一支持子查询,方式二不支持

insert into 表名(字段123) select 值1,'值2','值3' from 表名 where 筛选条件;

修改语句

• 语法:

# 修改单表的记录
update 表名 set 列=新值,列=新值,... where 筛选条件;
# 执行顺序:update>where>set

# 修改多表的记录(级联更新)
# sql92语法
update 表1 别名,表2 别名 set 列=值,... where 连接条件 and 筛选条件;
# sql99语法
update 表1 别名 连接类型(inner\left\right) join 表2 别名 on 连接条件 set 列=值,... where 筛选条件;

# 修改表中姓唐的女生的电话为123
update 表 set 电话=123 where name like '唐%';
# 修改表中id为2的,修改名称为张飞,魅力值10
update 表名 set 姓名='张飞',魅力值=10 where id=2;

# 修改多表的记录
# 修改男1的女朋友的手机号为123
update 表1 b inner join 表2 bo on b.id=bo.id 
set b.电话='123' where bo.name='男1';
# 修改没有男朋友的女生的男朋友编号都为2号
update 男表 bo right join 女表 b on b.id=bo.id
set b.男友编号=2 where b.id is null;

删除语句

方式一:delete

• 语法:

# 单表的删除
delete from 表名 where 筛选条件 

# 多表的删除
# sql92语法
delete 删除表的别名 from 表1 别名,表2 别名 where 连接条件 and 筛选条件;
# sql99语法(记这个)
delete 删除表的别名,删除表的表名 from 表1 别名 inner/left/right join 表2 别名 on 连接条件 where 筛选条件;

# 删除手机号以9结尾的女生信息
delete from 女表 where 电话 like '%9';
# 删除张的女朋友的信息
delete b from 男表 bo inner join 女表 b on b.id=bo.id where bo.男友='张';
# 删除黄的信息以及他女朋友的信息
delete b,bo from 女表 b inner join 男表 bo on b.id=bo.id where bo.name='黄';

方式二:truncate

• 语法:不能加where条件

truncate table 表名;

# 将魅力值大于100的男生删除
# truncate 清空数据,不能加where条件
truncate table 男表;

delete 和 truncate的区别(重点)

1. delete可以加where,truncate不能加where条件
2. truncate删除,效率高一点点
3. 假如要删除的表中有自增长列,如果用delete删除后,再插入数据,自增长列的值从断点开始,而truncate删除后,再插入数据,自增长列的值从1开始.
4. truncate删除没有返回值(0行受影响),delete删除有返回值(n行受影响)
5. truncate删除不能回滚,delete删除可以回滚

增删改练习

练习题

DDL数据定义语言

• 库和表的管理操作

库的管理

• 创建:create
• 修改:alter
• 删除:drop

库的创建

• 语法:

create database if not exists 库名;

# 创建库Books(不区分大小写)
create database if not exists books;

库的修改

• 通常不修改,修改会导致数据出问题
• 可以更改库的字符集,建库会有默认字符集

alter database 库名 character set 字符集(gbk/utf-8);

库的删除

drop database if exists 表名;

表的管理

• 创建:create
• 修改:alter
• 删除:drop

表的创建

• 语法:

create table 表名(
列名 列的类型(长度) 约束,
列名 列的类型(长度) 约束,
列名 列的类型(长度) 约束,
...
);

# 创建表Book
create table book(
id int,#编号
bName varchar(20),#书名
price double,#价格
authorId int,#作者编号
publishDate datetime #出版日期
);
desc book;
# 创建表author
create table author(
id int,
au_name varchar(20),
nation varchar(20)
);
desc author;

表的修改

• 语法:

alter table 表名 add/drop/modify/change column 列名 列类型 约束;

1. 修改列名
2. 修改列的类型或约束
3. 添加新列
4. 删除列
5. 修改表名

# 修改表名
alter table 表名 change column 旧列名 新列名 新类型; 
# 修改列的类型
alter table 表名 modify column 类名 新类型;
# 添加新列
alter table 表名 add column 列名 类型;
# 删除列
alter table 表名 drop column 列名;
# 修改表名
alter table 表名 rename to 新表名;

表的删除

drop table if exists 表名;
show tables;

表的复制

insert into author values
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');

# 仅仅复制表的结构
create table copy like author;
# 复制表的结构和数据
create table copy2 select * from author;
# 只复制部分数据
create table copy3 select id,au_name from author where nation='中国';
# 仅复制部分结构
create table copy4 select id,au_name from author where 0;

通用的写法

drop database if exists 旧库名;
create database 新库名();

drop table if exists 旧表名;
create table 表名();

库和表的管理练习

库和表的管理练习

数据类型

• 数值型
  • 整型
  • 小数:定点数,浮点数
• 字符型:
  • 较短的文本:char,varchar
  • 较长的文本:text,blob(较长的二进制数据)
• 日期型

数值型

• 整形
  • tinyint,smallint,mediumint,int/integer,bigint
  • 字节:1 , 2 , 3, 4, 8
• 特点:
  1. 如果不设置无符号,默认是有符号.如果想设置无符号,需要添加unsigned关键字
  2. 如果插入的数值超出了整型的范围,会报out of range异常,并且插入临界值.
  3. 如果不设置长度,会有默认的长度.
  4. 长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用.

# 如何设置无符号和有符号
drop table if exists tab_int;
create table tab_int(
t1 int,
t2 int unsigned,
t3 int(6) zerofill 
);
desc tab_int;
insert into tab_int values(-14245);#默认有符号
insert into tab_int values(-14245,-3896836);# 报错

• 小数
  
• 浮点型
  • float(M,D)
  • double(M,D)
• 定点型
  • dec(M,D)
  • decimal(M,D)
• 特点:
  1. M:整数部位+小数部位
  2. D:小数部位
  3. 如果超过范围,则插入临界值
  4. M和D可以省略.
  5. 如果是decimal,则M默认为10.D默认为0
  6. 如果是float和double,则会根据插入的数值的精度来决定精度.
  7. 定点型的精确度较高,如果要求插入数值的精度较高,如货币运算等则考虑使用.

# 测试M和D
create table tab_float(
f1 float(5,2),
f2 double(5,2),
f3 decimal(5,2)
);

insert into tab_float values(123.45,123.45,123.45);
values(123.456,123.456,123.456);# 报错
values(1223.456,1223.456,1223.456);# 报错
select * from tab_float;

原则

• 所选择的类型越简单越好,能保存数值的类型越小越好

字符型

字符型视频

• 较短的文本:
  • char(M),m可以省略,默认为1
  • varchar(M),m不可以省略
  • 二者区别:char固定长度字符,varchar可变长度字符,M代表最大的字符数,char比较耗费空间,varchar比较节省空间,char的效率比varchar高一点,
• 其他:
  • binary和varbinary用于保存较短的二进制
  • enum用于保存枚举
  • set用于保存集合
• 较长的文本:
  • text
  • blob(较大的二进制数据)

日期型

• 分类:
  • date只保存日期
  • time只保存时间
  • year只保存年
  • datetime保存日期+时间
  • timestamp保存日期+时间
• 特点:

  •   				字节					范围					时区等的影响
    

  •    datetime		8				1000-9999				不受
    

  •   timestamp		4				1970-2038				受
    

• 日期型必须用单引号’ ’ 引起来
• datetime和timestamp的区别
  

create table tab_date(
t1 datetime,
t2 timestamp
);

insert into tab_date values(now(),now());
select * from tab_date;
show variables like 'time_zone';
set time_zone='+9:00';

重点复习

重点复习内容