Mysql之DDL、DML语句

根据B站老杜带你学Mysql视频的学习笔记
链接：老杜带你学

Mysql之DDL语句

数据定义语言（凡是带有create、drop、alter的都是DDL）
DDL主要操作的是表的结构，不是表中的数据

表的创建（建表）

建表属于DDL语句，DDL包括：create、drop、alter

1. 建表的语法格式：
   create table 表名 (字段名1 数据类型，字段名2 数据类型…);
   直观写法：
   create table 表名 (
   字段名1 数据类型，
   字段名2 数据类型,
   …
   )；
   表名建议以t_或者tb_开始，可读性强，见名知意
   字段名：见名知意
   表名和字段名都属于标识符
2. 关于Mysql中的数据类型
   Mysql中有许多数据类型，我们只需要掌握一些常见的数据类型即可，如
   1. varchar(num)最长255——可变长度字符串,num——字符串长度
      比较智能，会根据实际的数据长度动态分配空间
      优点：节省空间
      缺点：因为要动态分配空间，速度慢
   2. char(num)最长255 ——定长字符串，num——字符串长度
      不管实际的数据长度是多少，根据分配的固定长度的空间去存储数据
      优点：速度快（不需要动态分配）
      缺点：使用不恰当的时候可能会导致空间的浪费
      varchar和char的使用选择
      如性别字段是固定长度的字符串，所以选择char
      而姓名字段是可变长度的字符串，所以选择varchar
   3. int最长11 —— 整数型
   4. bigint —— 长整型等同于long
   5. float ——单精度浮点型
   6. duble ——单精度浮点型
   7. data ——短日期
   8. datatime ——长日期
   9. clob ——字符大对象（Character Large Object:CLOB）
      最多可以存储4个G的字符串，如：
      存储一篇文章，一个说明。
      超过255个字符的都要采用CLOB字符大对象来存储
   10. blob ——二进制大对象（Binary Large Object:BLOB）
       专门用来存储图片、声音、视频等流媒体数据

实例：建表：t_movie 电影表（专门存储电影信息的）
字段 编号no（bigint） 名字name（varchar） 简介description（clob） 上映时间 playtime（date） 导演director（varchar）演员actor（varchar） 时长time（double） 海报image（blob） 类型type（char）

creat table t_movie(
	no bigint,
	name varchar(32),
	description clob,
	playtime date,
	director varchar(10),
	actor varchar(32),
	time double,
	image blob,
	type char(1));

删除表drop

第一种方式：
drop table 表名；
第二种方式
drop table if exists 表名;
如果存在则删除，健壮性更好
新建数据库——create database 数据库名;
删除数据——drop database 数据库名;

对表结构的增删改
1.在实际开发中，需求一旦确定之后，表一旦设计好，很少进行表结构的修改。因为开发进行中，修改表结构成本比较高（大量Java代码需要修改）
2.要修改表结构可以使用可视化工具，如Navicat、SQLyog等。
修改表结构的操作是不需要写到Java程序中的，实际上也不是Java程序员的范畴

基础要好，工具不要用

---

Mysql之DML语句

数据操作语言（凡是对表当中的数据进行增删改的都是DML）
insert、delete、update

插入数据insert

单行处理函数日期函数补充

1. 语法格式：
   insert into 表名(字段名1,字段名2,字段名3,…) values (值1,值2,值3,…);
   注意：字段名和值要一一对应，即数量要对应，数据类型要对应
   insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@qq.com');
   注意：insert 语句只要是执行成功了，那么必然会新增一条记录，没有给其他字段赋值的话，默认值为null。
   可以在建表的时候使用**default**给字段指定默认值，如：
   create table t_student(sex char(1) default 'm');
   insert语句中的字段名可以省略
   insert into table() values();
   前面的字段名都省略的话，等于按照表结构的字段顺序写上了，所以值也要都写上。
2. 插入日期
   mysql的日期格式：
   %Y 年(必须大写)
   %m 月
   %d 日
   %h 时
   %i 分
   %s 秒

1. 数字格式化format：format(数字,‘格式’)不常用
   select ename,sal format(sal,'$999、999) from emp;//加入千分位
2. str_to_date:将字符串varchar类型转换为date类型
   str_to_date函数可以把字符串varchar转换成日期date类型数据，通常使用在插入数据insert时，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换成date。
   语法格式：str_to_date(‘字符串日期’,‘日期格式’)

create table t_user(id int,name varchar(255),birthday date);
insert into t_user(id,name,birthday) values(1,'zhangsan','01-10-1999');//类型不匹配，数据库会报错
//可以使用str_to_date函数进行类型转换
insert into t_user(id,name,birthday) values(2,'zdz',str_to_date('01-10-1999','%d-%m-%Y'));

如果你提供的日期字符串是这个格式：==‘%Y-%m-%d’==就不需要使用str_to_date函数了
3.date_format:将date类型转换为具有一定格式的varchar类型
date_format函数可以将日期类型转换成特定格式的字符串
语法格式date_format(日期类型数据,‘日期格式’)这个函数通常使用在查询日期方面，设置展示的日期格式。
select id,name,date_format(birthday,'%m/%d/%Y') as birthday from t_user;
±-----±----------±-----------+
| id | name | birthday |
±-----±----------±-----------+
| 1 | zhangsan | 10/01/1990 |
| 2 | zdz | 10/01/1999 |
| 3 | zdz | 10/10/1999 |
±-----±---------±-----------+
select id,name,birthday from t_user;
以上的sql语句实际上是进行了默认的日期格式转化，自动将数据库中的date类型转换成varchar格式，并且采用的格式是mysql默认的日期格式：‘%Y-%m-%d %H:%i:%s’
java中的日期格式：yy-MM-dd HH:mm:ss SSS

3. date和datetime两个类型的区别：
   date是短日期：只包括年月日信息
   datetime是长日期 ：包括年月日时分秒信息
   insert into t_user(create_time) vaues('2022-11-18 13:07:50);
4. 在mysql当中怎么获取系统的当前时间——now()函数
   获取的时间信息带有时分秒，是datetime类型的
   insert into t_user(create_time) vaues(now());`
   数据库命名规范：所有的标识符都是全部小写，单词和单词之间使用下划线进行衔接

修改数据update

语法格式：
update 表名 set 字段名1 = 值1，字段名2 = 值2，字段名3 = 值3,…where 条件；
注意：没有条件限制会导致所有数据全部更新
update t_user set name = 'Jack',sex = 'm' where id = 2;
更新所有：
update t_user set name = 'abc';即没有条件

删除数据

语法格式：
delete from 表名 where 条件；
注意：没有条件会将整张表的数据都删除
delete from t_user where id = 1;
删除所有：
delete from t_user;即没有条件

插入多条数据

insert可以一次插入多条记录

inset into t_user(id,name,sex) values
(1,'Maddox','w'),   //第一条记录
(2,'Merry','m'),  //第二条记录
(3,'Jack','w'),   //第三条记录
...
;

快速创建表

create table t_teacher as select * from t_student;
这句SQL语句的意思是将as后面的查询结果当做一张表创建出来
原理：将一个查询结果当做一张表新建，这个可以完成表的快速复制。表创建以后，同时表中的数据也复制过去了
create table mytable as select id,name,sex from t_student where sex = 'm';
将查询结果当做一张表新建

将查询结果当作一张表新建,很少用

create table dept_bak as select * from dept;
insert into dept_bak select * from dept;
查询的结果要符合表的结构才能插入

快速删除表中的数据truncate（DDL）

delete（DML）删除数据的方式比较慢
delete删除数据的原理：
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放。
这种删除的缺点：删除效率比较低
优点：支持回滚，后悔了可以恢复数据
truncate语句删除数据的原理：
这种删除效率比较高，表被一次截断，物理删除
这种删除缺点：不支持回滚（后悔）
可以选用truncate删除表中的数据，只需要1秒的时间就可以删除大量的数据，效率很高，但是使用之前，必须仔细询问是否真的需要删除，并警告删除之后不可恢复。

xxx.sql这种文件被称为SQL脚本文件，SQL脚本文件中编写了大量的sql语句，执行SQL脚本文件的时候，该文件中所有的SQL语句会全部执行。
要批量的执行SQL语句，可以使用SQL脚本文件，在mysql当中执行SQL脚本：
mysql-> source 绝对路径
在实际工作中，第一天到公司，项目经理会给你一个xx.sql文件，你执行以后，电脑上的数据库数据就有了。

约束（重点）constraint，非常重要

1.什么是约束：在创建表的时候，我们可以给表中字段加入约束来保证这个表中的数据的完整性、有效性。
2.约束的作用：就是为了保证表中的数据有效
3. 约束包括（重点学习下面四个）:
非空约束:not null
唯一性约束：unique
主键约束：primary key（简称：PK）
外键约束：foreign key（简称：FK）
检查约束：check 最新的 MySQL 8.0.16开始支持该约束
约束直接添加到列后面的，叫做列级约束。约束没有添加到列的后面，这种约束称为表级约束
not null只允许列级约束

非空约束（not null）

有非空约束的字段不能为null
在创建表的时候，在字段后面加上not null就行

create table t_tab(
	id int,
	name varchar(32) not null   //列级约束
);

唯一性约束（unique）

唯一性约束unique约束的字段不能重复，但是可以为null（可以有多个null）

create table t_tab(
	id int,
	name varchar(32) unique
);

两个字段（多个字段）联合唯一：

create table t_tab(
	id int,
	name varchar(32),
	unique(id,name)     //表级约束
);

unique与not null联合使用

create table t_tab(
	id int,
	name varchar(32) not null unique
);

在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（oracle中不一样）

主键约束（primary key，简称PK，pri）非常重要

1. 主键约束相关术语：
   主键约束：一种约束
   主键字段：该字段上添加了主键约束
   主键值：主键字段中的每一个值都叫做主键值。
2. 主键的作用：主键值是每一行记录的唯一标识
   注意：任何一张表都应该有主键,没有主键，表无效
   主键的特征：not null + unique（不能为空，不能重复）
   一个字段作主键，叫作单一主键

create table t_tab(
	id int primary key, // 列级约束
	name varchar(32)
	//primary key(id),表级约束也可以
);

表级约束主要是给多个字段联合起来添加约束
3. 多个字段联合起来作主键，叫作复合主键

create table t_tab(
	id int ,
	name varchar(32),
	primary key(id,name)
);

在实际开发中不建议使用复合主键，复合主键比较复杂，单一主键就足以达到主键所代表的意义
4. 主键数量
一张表，主键约束只能添加一个
5. 主键值建议使用int、bigint、char等类型，主键值一般是数字，一般是定长的
6. 主键除了单一主键和复合主键外还可以分为自然主键和业务主键
自然主键：主键值是一个自然数，和业务没关系
业务主键：主键值和业务紧密关联，如拿银行卡账号做主键值
在实际开发中，自然主键使用的比较多，因为主键只要做到不重复就行，不需要有意义，而业务主键不好，因为业务主键一旦和业务挂钩，那么当业务发生变动的时候，会影响到主键值，所以业务主键不建议使用，尽量使用自然主键。
7. 在mysql当中，有一种机制，可以帮助我们自动维护一个主键值

create table t_tab(
	id int primary key auto_increment,
	name varchar(32)
);

auto_increment 表示自增，从1开始，以1递增

外键约束（foreign key，简称FK）非常重要

1. 外键约束的相关术语：
   外键约束：一种约束
   外键字段：该字段上添加了外键约束
   外键值：外键字段当中的每一个值
   注意：
   被外键应用的表为父表，接受应用（有外键）的表为子表
   删除表的顺序：先删子，再删父
   创建表的顺序：先创建父，再创建子
   删除数据的顺序：先删子，再删父
   插入数据的顺序：先插入父，再插入子

create table t_class(
	classno int primary key,
	classname varchar(32)
);
create table t_student(
	id int primary key auto_increment,
	name varchar(32),
	cno int,
	foreign key(cno) references t_class(classno)
);

references——引用
注意：外键值可以为null
子表中的外键引用的父表中的某个字段，被引用的这个字段不一定是主键，但必须满足唯一性，即要有unique约束条件