MySql-DML-DDl语言

DML

插入

一、方式一
语法：

insert into 表名(字段名,...) values(值,...);

INSERT INTO admin(`username`,`password`) VALUES('1','2')

特点：
1、要求值的类型和字段的类型要一致或兼容
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
3、假如表中有可以为null的字段，注意可以通过以下两种方式插入null值
①字段和值都省略
②字段写上，值使用null
4、字段和值的个数必须一致
5、字段名可以省略，默认所有列

二、方式二
语法：

insert into 表名 set 字段=值,字段=值,...;

INSERT INTO admin SET `username` = 1,`password`=2

两种方式 的区别：
1.方式一支持一次插入多行，语法如下：

insert into 表名【(字段名,..)】 values(值，..),(值，...),...;

INSERT INTO admin(`username`,`password`) VALUES('1','2'),('2','3'),('5','3');

2.方式一支持子查询，语法如下：

INSERT INTO admin(`username`,`password`) SELECT `username` , `password` FROM `admin`;

修改

一、修改单表的记录 ★
语法：

update 表名 set 字段=值,字段=值 【where 筛选条件】;

UPDATE admin SET username = 'lang' WHERE id > 3

二、修改多表的记录【补充】
语法：

update 表1 别名 
left|right|inner join 表2 别名 
on 连接条件  
set 字段=值,字段=值 
【where 筛选条件】;

UPDATE boys bo
INNER JOIN beauty b ON bo.`id` = b.`boyfriend_id`
SET b.`phone` = '114'
WHERE bo.`boyName` = '张无忌'

删除

方式一：使用delete
一、删除单表的记录★

语法：delete from 表名 【where 筛选条件】【limit 条目数】

二、级联删除[补充]
语法：

delete 别名1,别名2 from 表1 别名 
inner|left|right join 表2 别名 
on 连接条件
 【where 筛选条件】

方式二：使用truncate
语法：

truncate table 表名

两种方式的区别【面试题】★

1.truncate删除后，如果再插入，标识列从1开始
delete删除后，如果再插入，标识列从断点开始
2.delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值
delete可以返回受影响的行数
5.truncate不可以回滚
delete可以回滚

DDL

库的管理

一、创建库

create database 【if not exists】 库名【 character set 字符集名】;

CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8

二、修改库的字符集

alter database 库名 character set 字符集名;

ALTER DATABASE books CHARACTER SET utf8;

三、删除库

drop database 【if exists】 库名;

表的管理

一、创建表 ★

create table 【if not exists】 表名(
	字段名 字段类型 【长度 ，约束】,
	字段名 字段类型 【长度 ，约束】,
	。。。
	字段名 字段类型 【长度 ，约束】 

)

二、修改表

1.添加列

alter table 表名 add column 列名 类型 【first|after 字段名】;

2.修改列的类型或约束

alter table 表名 modify column 列名 新类型 【新约束】;

3.修改列名

alter table 表名 change column 旧列名 新列名 类型;

4 .删除列

alter table 表名 drop column 列名;

5.修改表名

alter table 表名 rename 【to】 新表名;

三、删除表

drop table【if exists】 表名;

四、复制表
1、复制表的结构

create table 表名 like 旧表;

2、复制表的结构+数据

create table 表名 
select 查询列表 from 旧表【where 筛选】;

练习：

# 创建表 dept1
CREATE TABLE IF NOT EXISTS dept1(
	t_id INT(7),
	t_name VARCHAR(20)
)

# 将表departments中的数据插入新表dept2中
CREATE TABLE dept2
SELECT `department_id`,`department_name` FROM `myemployees`.`departments`;


# 创建一个emp表 后 将last_name的长度增加到50
CREATE TABLE IF NOT EXISTS emp (
	id INT(7),
	first_name VARCHAR(20),
	last_name VARCHAR(20),
	dept_id INT(7)
)

ALTER TABLE emp MODIFY COLUMN last_name VARCHAR(50);

# 根据表employees创建employees2 只需要结构
CREATE TABLE employees2 LIKE myemployees.`employees`;

# 删除表emp
DROP TABLE IF EXISTS emp;

# 将表employees2重命名为emp
ALTER TABLE employees2 RENAME TO emp

# 在表dept1和emp中添加新列test_column 
ALTER TABLE dept1 ADD COLUMN text_column INT;
ALTER TABLE emp ADD COLUMN text_column VARCHAR(10);

# 直接删除表emp中的列dept_id
ALTER TABLE emp DROP COLUMN IF EXISTS dept_id

# 修改emp中的text_column列名为 user_id
ALTER TABLE emp CHANGE COLUMN  text_column user_id BIGINT;

数据类型

一、数值型
1、整型

tinyint、smallint、mediumint、int/integer、bigint  默认都为有符号
   1       2         3          4            8

特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
②如果超出了范围，会报out or range异常，插入临界值
③长度可以不指定，默认会有一个长度
长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型

CREATE TABLE t_int(
	t1 INT,
	t2 INT UNSIGNED # 设置为无符号 ,如果为负数 则为0
);

2、浮点型
定点数：decimal(M,D)
浮点数:
float(M,D) 4
double(M,D) 8

特点：
①M代表整数部位+小数部位的总长度，D代表小数点后显示的位数 超出会四舍五入
②如果超出范围，则报out or range异常，并且插入临界值
③M和D都可以省略，但对于定点数，M默认为10，D默认为0
④如果精度要求较高，则优先考虑使用定点数

mysql无符号和有符号的区别
无符号unsigned 表示设置的的数据为0或者正数；
有符号则可以是负数 -；

CREATE TABLE t_float(
	t1 FLOAT(5,2), # float(M,D) M 代表一共的位数 D代表 小数的位数 这里小数是两位,所以小数点前就只剩三位了
	t2 DOUBLE(5,2),
	t3 DECIMAL(5,2)
);

二、字符型

char、varchar、binary、varbinary、enum、set、text、blob

char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1
varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略

CREATE TABLE t_enum(
	t1 ENUM('a','b','c')
)

INSERT INTO t_enum VALUES('a')

CREATE TABLE t_set(
	t1 SET('a','b','c','d')
);

INSERT INTO t_set VALUES('a,b')

三、日期型
year年
date日期
time时间
datetime 日期+时间 8
timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间

约束

含义 ： 一种限制，用于限制表中的数据。为了保证表中的数据的可靠性。

分类： 六大约束：

• not null 非空 用于保证该字段的值不能为空。
• default 默认值，用于给该字段设置默认值。
• primary key 主键 用于保证该字段的值具有唯一性 并且具有非空性
• unique 唯一 用于保证该字段的值具有唯一性 可以为空
• foreign key 外键 用于限制两个表的关系的

分类：约束类型：

• 列级约束：

其中 外键不支持列级约束

CREATE TABLE stuinfo(
	id INT PRIMARY KEY ,#主键
	stuName VARCHAR(10) NOT NULL , #非空
	gender CHAR(1) NOT NULL,
	seat INT UNIQUE,#唯一元素
	age INT DEFAULT 18 #默认
);

• 表级约束

其中 default not null 不支持表级约束

CREATE TABLE stuinfo(
	id INT,
	stuName VARCHAR(10) NOT NULL , #非空
	gender CHAR(1) NOT NULL,
	seat INT,#唯一元素
	age INT,#默认
	majorId INT ,
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)#外键
	#【CONSTRAINT name】可省略
);

通用写法：

# 通用写法
CREATE TABLE stuinfo(
	id INT PRIMARY KEY, #主键
	stuName VARCHAR(10) NOT NULL , #非空
	enrollmentDate DATETIME DEFAULT NOW(),#默认
	seatNo INT UNIQUE, #唯一
	majorNameId INT,	
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorNameId) REFERENCES major(id) #外键
);

联合主键/联合唯一：

CREATE TABLE stuinfo(
	id INT,
	stuName VARCHAR(10) NOT NULL ,
	CONSTRAINT pk PRIMARY KEY(id,stuName)#联合主键
);

约束修改

CREATE TABLE stuinfo(
	id INT,
	stuName VARCHAR(10)
);

# 添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;

# 删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20);

# 添加外键
ALTER TABLE stuinfo ADD COLUMN majorid INT ;
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

# 外键 级联删除
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id) on delete cascade;

# 外键 级联置空
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id) on delete set null;

# 删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY ;

# 删除唯一
ALTER TABLE stuinfo DROP INDEX name

# 删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY majorid;

标识列

又叫自增长列 ，可以 不用手动的插入值，系统提供默认的序列值.
每张表只能存在一个，而且要与key搭配使用

CREATE TABLE stuinfo(
	id INT PRIMARY KEY AUTO_INCREMENT,
	stuName VARCHAR(10)
);

• 查看自动增长

SHOW VARIABLES LIKE '%auto_increment%';

给已存在的列添加自增

alter table tablename modify id int(11)  auto_increment;

mysql中不支持设置起始值，但是可以设置步长

SET auto_increment_increment = 3;

• 修改标识列

#设置标识列
ALTER TABLE table_ MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;