三、数据库基本操作

1、DDL

数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。

1.对数据库创建、删除、修改操作

2.对表结构创建、删除、修改操作

创建数据库（database）

n多张不同的表结构

mayiktuser1

mayiktuser2

…….

2、数据库操作

查询

查询所有数据库的名称:
show databases;
查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;
查询当前正在使用的数据库名称
select database();
使用数据库
use 数据库名称;

创建

创建数据库：
create database 数据库名称;
创建数据库，判断不存在，再创建：
create database if not exists 数据库名称;
创建数据库，并指定字符集
create database 数据库名称 character set 字符集名;

删除

语法： 删除数据库
drop database 数据库名称;
判断数据库存在，存在再删除
drop database if exists 数据库名称;

修改

注意：一般不对数据库进行修改操作，因为修改的安全性低，容易导致数据的缺失。
基本操作：修改数据库的字符集
alter database 数据库名称 character set 字符集名称;

3、表操作

创建表

 create table if not exists 表名(
                列名1 数据类型1[长度] 【字段约束】,
                列名2 数据类型2[长度] 【字段约束】,
                ....
                列名n 数据类型n[长度] 【字段约束】
            );
    * 注意：最后一列，不需要加逗号（,）

use mayiktmeite;
CREATE TABLE if not exists mayikt_user(
	id INT,
	name VARCHAR(20),
    age int,
    create_time date,
    address VARCHAR(100)
);

修改

修改表名称：

语法：alter table 表名 rename to 新的表名;

ALTER TABLE mayikt_user RENAME TO meite_user ;

删除表

drop table 表名;

drop table if exists 表名;

4、数据类型

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

字符串类型

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符

5、DML

数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）

DML与DDL区别？

DDL 对数据库 表结构 增加、修改 删除操作

DML 表结构中的数据 增加（insert）、修改（update） 删除（delete） 查询（select ）

insert

INSERT INTO 语句用于向表格中插入新的行。

语法格式：

INSERT INTO 表名称 (列名1，列名2 ...) VALUES (值1, 值2,....) // 插入数据列与值的顺序需要一一对应。

INSERT INTO 表名称 VALUES (值1, 值2,....) // 向表中插入所有列

例子：

INSERT INTO `mayikt_user` (`id`, `name`, `age`, `addres`, `create_time`) VALUES (1, 'mm', 1, '1', '2022-01-20 03:51:54');

INSERT INTO `mayikt_user` VALUES (1, 'mm', 1, '1', '2022-01-20 03:51:54');

update

语法格式：

update 表名称 set 字段=值，字段=值，...； ---直接修改整张表的所有行数据

update 表名称 set 字段=值，字段=值，... where 条件 ---- 根据条件查找到对应行数据 修改

例子:

UPDATE mayikt_user ` SET `name` = 'mm', `age` = 25 WHERE `id` = 1;

UPDATE mayikt_user ` SET `name` = 'mm', `age` = 25 ---修改所有

delete

语法格式：

delete from 表名称 where 条件 根据条件删除表中的数据

TRUNCATE mayikt_user --清空表中所有的数据

例子：

delete from mayikt_user where where id=1; 根据条件id=1 删除id=1 行数据

delete from mayikt_user ---清空表中所有的数据

TRUNCATE mayikt_user --清空表中所有的数据

TRUNCATE 与delete 不同？

truncate:会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；

delete:逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；

简单练习题：

dml 操作练习题

1.创建一个员工表employees 字段如下：

CREATE TABLE IF NOT EXISTS `mayikt_employees` (

id int ,

name varchar(20) ,

sex tinyint ,

salary double

);

2.插入一条数据

INSERT INTO `mayikt_employees` (`id`, `name`, `sex`, `salary`) VALUES (1, 'mayikt', 1, 3600);

3.修改 id=1 薪水 为8000

update mayikt_employees set salary='8000' where id=1;

4.将表中的所有数据 名称为mayikt 改为meite

update mayikt_employees set name='meite';

5.修改 id=1 当前薪水+ 3000

update mayikt_employees set salary=salary+3000 where id=1;

6.删除id=1 行数据

delete from mayikt_employees where id=1;

四、约束

1、什么是约束

用于限制表中的数据，为了保证表中数据的准确性和可靠性,不符合约束的数据，插入时就会失败。

约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件

例如 用户信息表中 手机号码不允许为空，身份证号码不允许重复。

2、约束作用

用于限制表中的数据，为了保证表中数据的准确性和可靠性,不符合约束的数据，插入时就会失败。

3、约束分类

1.NOT NULL ：非空，用于保证该字段的值不能为空。例如学生表的学生姓名及学号等等。

2.DEFAULT：默认值，用于保证该字段有默认值。例如学生表的学生性别

3.PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。例如学生表的学生学号等。

4.UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。例如注册用户的手机号，身份证号等。

5.CHECK：检查约束（MySql不支持），检查字段的值是否为指定的值。

6.FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。例如学生表的专业编号

4、主键约束

1.MySQL 主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行，这样的一列或多列称为表的主键，通过它可以强制表的实体完整性，同时可以方便根据主键查询该行数据。

2.选取设置主键约束的字段 主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符,主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键,主键应该遵守下面的规则

3.每个表只能定义一个主键，主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值，这是唯一性原则

4.在创建表时设置主键约束 在 CREATE TABLE 语句中，主键是通过 PRIMARY KEY 关键字来指定的

5.当创建主键的约束时，系统会默认所在的列和列组合 建立对应的索引，方便提高查询效率。

如果使用表中的 一列 主键——-单列主键

使用表中的 多个列（id，手机号码、身份证号码）多列-多列主键（复合主键）

条件：一张表中只能够允许有一个主键、主键值 不允许是为空 主键保证 每行数据完整唯一性

不允许重复的。

mysql 查询高级知识 索引— 索引 方便提高查询效率

1.添加单列主键

2.添加多列联合主键

使用主键约束 PRIMARY KEY。

5、单列主键

1.定义字段时，指定单列主键

语法格式：

CREATE TABLE 表的名称 (

<字段名> <数据类型> PRIMARY KEY

}

CREATE TABLE `mayikt_users` (
  `id` int    PRIMARY KEY ,
  `name` varchar(255) ,
  `age` int 
) ;

2.定义完字段之后，指定主键列（复合主键）

语法格式：

CREATE TABLE 表的名称 (

....

CONSTRAINT 主键名称 PRIMARY key (主键列);

}

CREATE TABLE `mayikt_users` (
  `id` int ,
  `name` varchar(255) ,
  `age` int,
  CONSTRAINT pk1 PRIMARY key (id,phone)
) ;

6、联合主键

联合主键（复合主键），由多个列（字段组成）。

注意事项：

1.当主键是有一个列组成时，不能够直接在字段名称后面声明主键约束；

2.一张表只能够允许一个主键

CREATE TABLE `mayikt_users` (
  `id` int,
  `name` varchar(255),
  `age` int ,
  `phone` varchar(11) ,
  CONSTRAINT pk1 PRIMARY KEY (id,phone)
);

7、自定增长约束

设置自动增长

1.在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值，每增加一条记录，主键自动增加。

2.通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长

3.语法格式如下：

字段名 数据类型 AUTO_INCREMENT

4.默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。

一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复(即为主键或主键的一部分)。

AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。

AUTO_INCREMENT 约束的字段只能是整数类型(TINYINT、SMALLINT、INT、BIGINT 等)。

AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

注意：

1.一张表中只能有一个自动增长的字段
2.配合主键一起使用 并且只适用于整数类型
3.自动增长默认的初始值1，每增加一条记录，该字段的值会增加1

drop table mayikt_users;
CREATE TABLE `mayikt_users` (
  `id` int PRIMARY KEY AUTO_INCREMENT  ,
  `name` varchar(255),
  `age` int ,
  `phone` varchar(11) 
);

自动增长设置初始值

默认自动增长初始值是从1开始

mysql 指定自动增长字段初始值 1000 2000

1.创建表的时候指定

CREATE TABLE `mayikt_users` (
  `id` int PRIMARY KEY AUTO_INCREMENT  ,
  `name` varchar(255),
  `age` int ,
  `phone` varchar(11)
)AUTO_INCREMENT=1000;

2.创建表之后修改自定增长开始值

alter table mayikt_users AUTO_INCREMENT 2000;

delete与truncate 删除数据区别

delete 删除数据之后，自动增长还是从最后一个删除数据的id基础上做自增;

truncate 清空数据之后 自动增长是从初始值1开始

delete from mayikt_users —清空表数据

truncate mayikt_users —清空表数据

TRUNCATE 与delete 不同？

truncate:会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；

自动设定初始值 失效

delete:逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；

8、非空约束

1.MySql — not null 非空约束用于确保当前列的值不为空；在创建表时，如果指定 not null 该字段在插入数据时

不允许为空;

2.语法格式；

2.1 创建表的时候 <字段名><数据类型>NOT NULL;

CREATE TABLE `mayikt_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int NOT NULL,
  `phone` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
);

2.2 alter table 【数据库名.】表名称 modify 字段名 数据类型 not null;

9、唯一约束

MySQL 唯一约束（Unique Key）是指所有记录中该字段的值不能重复出现。例如为 phone 字段加上唯一性约束后，每条记录的 phone 值都是唯一的，不能出现重复的情况。如果其中一条记录的 phone 值为‘1880663389’，那么该表中就不能出现另一条记录的 phone 值也为‘1880663389’，唯一约束值可以允许null。、

主键约束 满足唯一性且值不允许为null 而我们的唯一约束 值允许为null

一张表结构中只允许有一个主键约束但是可以有多个唯一约束。

例如手机号码是唯一约束，每条记录的手机号码是不允许重复且保证唯一。

1.在创建表时设置唯一约束

语法规则:

UNIQUE KEY 约束名称 (约束字段)

CREATE TABLE `mayikt_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  `age` int DEFAULT NULL,
  `phone` varchar(11) ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_phone` (`phone`)  -- 指定约束名称和约束字段
);

2.在修改表时添加唯一约束

语法规则如下：

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE (<列名>)；

ALTER TABLE mayikt_users ADD CONSTRAINT unique_phone UNIQUE(phone);

[SQL]ALTER TABLE mayikt_users ADD CONSTRAINT unique_phone UNIQUE(phone);

[Err] 1062 – Duplicate entry ‘1880663389’ for key ‘mayikt_users.unique_phone’

该表中手机号码数据已经重复呢，

3.删除约束

ALTER TABLE mayikt_users DROP INDEX unique_phone;

10、默认约束

默认值（Default）的完整称呼是“默认值约束（Default Constraint）”，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。其中，“默认值”为该字段设置的默认值，如果是字符类型的，要用单引号括起来。

1.在创建表时设置默认值约束

<字段名> <数据类型> DEFAULT <默认值>;

CREATE TABLE `mayikt_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT 'mayikt',
  `age` int DEFAULT 1,
  `phone` varchar(11) ,
  PRIMARY KEY (`id`)
);

2.在修改表时添加默认值约束

ALTER TABLE <数据表名>

CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

ALTER TABLE mayikt_users

CHANGE COLUMN phone phone varchar(11) DEFAULT '1111';

11、零填充约束

定义了数据类型的长度，如果实际位数小于定义的长度，显示时会在左边用0填充

语法:

create table 表名称
(
    字段名称 int zerofill,
    ...
);

id int(10)

id=1

0000000001

id=1000

0000001000