MySQL基础-表数据插入-创建

最新推荐文章于 2024-07-25 12:56:57 发布

澜然

最新推荐文章于 2024-07-25 12:56:57 发布

阅读量2k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_48378125/article/details/124543988

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

DML语言

#一、插入语句
/**
语法：

INSERT INTO 表名(列名,.....) VALUES(值1,值2 .... );
① 可以为null值的列可以填充null
② 空值列可以去掉不写
③ 列的顺序是否可以调换
④ 列数和值的个数必须一致
⑤ 可以省略列名 默认所有列 而且列的顺序和表中列的顺序一致
*/
# 方式二
/**
语法：

INSERT into 表名 
set 列名1=值1,
	列名2=值2,...
*/

1,方式一支持插入多行 方式二 不支持 
2,方式一支持子查询，方式二不支持

子查询
INSERT INTO beauty 
SET id = 15,`name`='刘涛',phone='888888'

SELECT * FROM beauty
#1.插入值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id)
VALUES (14,'郭襄','女','2000-12-03','18862517892',null,null)


INSERT INTO beauty (id,name,phone)
SELECT 21,'宋茜','3333' 先有结果集 在插入

INSERT INTO beauty ( id, `name`, phone ) SELECT
id,boyName,'123456' FROM boys WHERE id > 3;

二、修改语句


/*
1.修改单表的记录

语法：执行过程  update  where  set 
UPDATE 表名 ①
set 列=新值,列=新值,.... ③
WHERE 筛选条件  ② 

2.修改多表的记录[补充]

语法：
sql92语法 
 UPDATE 表1 别名,表2 别名
 set 列 = 值,....
 WHERE 连接条件 [只支持内联]
 and 筛选条件 

sql99语法
 UPDATE 表1 别名
 INNER|LEFT|RIGHT JOIN 表2 别名
 on 连接条件 
 set 列 = 值,....
 WHERE 筛选条件
*/

# 2.修改多表的记录[补充]

#修改张无忌的女朋友的手机号为114
UPDATE beauty b 
LEFT JOIN boys bo 
on b.boyfriend_id = bo.id 
SET phone ='114'
WHERE bo.boyName='张无忌'

#修改没有男朋友的女神的男朋友编号都为2号
UPDATE beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id 
SET boyfriend_id = 2 
WHERE
	boyfriend_id NOT IN ( SELECT id FROM boys );

#另一种写法
UPDATE boys bo 
RIGHT JOIN beauty b on b.boyfriend_id = bo.id
SET boyfriend_id =2 
WHERE bo.id is null;

#1.修改单表的记录
# 修改姓唐的电话
UPDATE beauty 
SET phone ='455778'
WHERE `name` LIKE '%唐%'


#修改boys表中id为2名称张飞 魅力值10
UPDATE boys 
SET userCP = 10,
boyName = '张飞' 
WHERE
	id = 2

三、删除语句

#三、删除语句 
/**
方式一：DELETE 
语法:
#补充的删除 前面几n条
DELETE FROM beauty  LIMIT n; 
#一、单表的删除
DELETE FROM 表名 WHERE 筛选条件

2、多表的删除[补充]
sql92 
DELETE 别名
FROM 表1 别名,表2 别名 
WHERE 连接条件 AND 筛选条件

sql99
DELETE 别名
FROM 表1 别名
LEFT|RIGHT|INNER JOIN 表2 别名 
on 连接条件
WHERE  筛选条件


方式二  TRUNCATE  截断一张表 
语法 ：TRUNCATE table 表名 
*/

#方式一 ：DELETE 
#1.单表的删除
#案例1 删除手机号以9结尾的女神信息 以什么结尾 %放前面 
DELETE FROM beauty WHERE phone LIKE '%7'

#多表删除
#删除张无忌女朋友的信息
DELETE b,bo
FROM beauty b ,boys bo  
WHERE b.boyfriend_id=bo.id AND bo.boyName='张无忌'

#sql99模式
DELETE b
FROM beauty b INNER JOIN boys bo  on b.boyfriend_id=bo.id
WHERE  bo.boyName='张无忌'

#删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b ,boys bo  
WHERE b.boyfriend_id=bo.id 
AND bo.boyName='黄晓明'


#sql99
DELETE b,bo
FROM beauty b INNER JOIN boys bo  on b.boyfriend_id=bo.id
WHERE  bo.boyName='黄晓明'

#方式二 TRUNCATE语句
#清空表 清空数据 
TRUNCATE TABLE boys;

delete 和TRUNCATE 区别

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

5.TRUNCATE删除不能回滚   DELETE删除可以回滚 
*/
#查看表结构
DESC employees
SELECT * FROM boys;

TRUNCATE TABLE boys
DELETE FROM  boys  WHERE id =4;

INSERT INTO boys (boyName,userCP)
VALUE
('段誉',90)

DDL语言

一、库的管理
创建,修改，删除
二、 表的管理
创建,修改，删除
创建:CREATE
修改:ALTER
删除:DROP
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] 库名;

2.库的修改
#更改字符集
ALTER DATABASE book CHARACTER SET utf8

3.库的删除
DROP DATABASE  IF EXISTS book;
4.表的创建
CREATE TABLE IF NOT EXISTS  表名(
			列名 列的类型[(长度) 约束],
			列名 列的类型[(长度) 约束],
			列名 列的类型[(长度) 约束],
			.....
			列名 列的类型[(长度) 约束]
)
5.表的修改
#①修改列名 加上类型 
ALTER TABLE book CHANGE COLUMN publishDate pbDate DATETIME;

#②修改列的类型或者约束
ALTER TABLE AUTHORS MODIFY COLUMN annual VARCHAR(20);

#③添加新列
ALTER TABLE authors ADD COLUMN annual DOUBLE COMMENT '年刊';

#添加到指定的后面
#ALTER TABLE 表名 add COLUMN 列名 类型 [FIRST|AFTER 字段名]
ALTER TABLE beauty ADD COLUMN garden  int  AFTER sex;

#删除列
ALTER TABLE author DROP COLUMN annual;

#修改表名
ALTER TABLE author RENAME to AUTHORS
/*
ALTER TABLE 表名 add|CHANGE|MODIFY|DROP COLUMN 列名 [列类型 约束 ];
*/

7.表的删除
#三、表的删除 
DROP TABLE if EXISTS book;
SHOW TABLES;

#通用的写法
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();

8.表的复制
#仅仅复制表结构
CREATE TABLE copy LIKE AUTHORS;

#复制表结构+数据
CREATE TABLE copy2 
SELECT * FROM AUTHORS;

#只复制部分数据
CREATE TABLE copy3 
SELECT id ,au_name
FROM AUTHORS WHERE id =4;

#仅仅复制某些字段 # 0 false | 1 true;
CREATE TABLE copy4 
SELECT id ,au_name
FROM AUTHORS 
WHERE 0;

常见的数据类型

#常见数据类型的介绍
/*
数值型
			整型：
			小数：
						定点数：
						浮点数：
字符型：
				较短的文本:char,VARCHAR
				较长的文本：text,blob(较长的二进制数据)

日期型：

*/

#一、整型  从小到大 
/**					字节     			 范围
Tinyint  			1 				有符号：-128~127  无符号0~255 
SMALLINT 			2 				
					
MEDIUMINT 	  3


Int,INTEGER 	4 

BIGINT 				8 

特点：
① 如果不设置无符号还是有符号 默认有符号 设置无符号 添加UNSIGNED 
② 不设置长度 会有默认长度 
③ INT(7) ZEROFILL 零填充  INT默认 变成无符号
④ 长度代表了显示的最大宽度 如果不够会用0在左边填充 但必须搭配ZEROFILL使用 
*/

#1.如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
		t1 INT ZEROFILL ,#不写默认有符号 
		t2 INT(7) ZEROFILL  UNSIGNED #无符号 
);

DESC tab_int;
INSERT into tab_int VALUES (-1234567);
INSERT into tab_int VALUES (-1234567,-1234567);

INSERT into tab_int VALUES (123,123);

SELECT * FROM tab_int;

#二、小数
/*
分类:
1.浮点型
2.定点型

# 浮点数类型    		  字节   
	FLOAT(M,D) 			4
	DOUBLE(M,D)          8
	
定点数类型
	DEC(M,D)				M+2			最大值范围与double相同 给定decimal的有效取值范围由M和D决定
	DECIMAL(M,D)


特点:
①M和D 
M：整数部位+小数部位
D：小数部位 
超过范围则插入失败

② M和D可以省略
如果是decimal 则默认为10 D默认为0 
如果是float和double 会根据插入的数值的精度来决定精度

③ 定点型的精确度较高 如果要求插入数值的精度较高如货币运算规则等考虑使用
*/

#原则
/*
所选择的类型越简单越好 能保存数值的类型越小越好
*/

#三、字符型
/**
较短的文本
char(M):固定长度的字符 
M:代表字符数 a 中 都是一个字符数
VARCHAR ：可变长度的字符 

#  解释
char(10) VARCHAR(10)

中国 两个字符
char(10) 给十个字符
VARCHAR(10) 给两个字符 

特点：			写法 		M的意思						特点				空间的耗费			效率

CHAR 			CHAR(M)		最大字符数 可以省略 默认1 	固定长度的字符			比较耗费				高

VARCHAR 		VARCHAR(M) 	最大字符数	不可以省略   	固定长度的字符			比较节省				低


较长的文本
text
BLOB(较大的二进制)


BINARY和 VARBINARY用于保存较短的二进制
enum 用于保存枚举
set用于保存集合
*/

CREATE TABLE tab_char(

			c1 ENUM('a','b','c')
);

desc tab_char;
#数值是 枚举的定位 
INSERT INTO tab_char VALUES(3)

INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('M');#直接不插入 
SELECT * FROM tab_char;


DROP TABLE IF EXISTS tab_float;
CREATE TABLE tab_float(
			f1 FLOAT,
			f2 DOUBLE,
			f3 DECIMAL
);

DESC tab_float;

INSERT INTO tab_float VALUES (1233.4,1233.4,1234.4777777);

SELECT * FROM tab_float;

#set集合
DROP TABLE if EXISTS tab_set;
CREATE TABLE tab_set(
			s1 set('a','b','c','d')
);
desc tab_set;

#多个字符插入
INSERT into tab_set VALUES('b,a,c');
INSERT into tab_set VALUES('A')

SELECT * FROM tab_set;

日期型

#日期型
/*						字节				最小值
date        		4 					只可以放日期

time  只存放时间

year  年

TIMESTAMP 			4	 			日期+时间 

datetime 				8 				日期+时间

TIMESTAMP 和 datetime区别

TIMESTAMP取值范围小
datetime 取值范围大

TIMESTAMP和实际时区有关
datetime 反映出插入时的当地时区

TIMESTAMP受版本影响
*/


CREATE TABLE tab_date(
			t1 DATETIME,
			t2 TIMESTAMP #会受时区的影响 
);

DESC tab_date;

INSERT into tab_date  VALUES (NOW(),NOW());

SELECT * FROM tab_date;

##查看时区
SHOW VARIABLES LIKE 'time_zone'

SET time_zone='+08:00'

主键和外键

#常见约束
/*
含义：一种限制 用于限制表中的数据 为了保证表中的数据的一致性

分类:六大约束
					1.NOT NULL :非空 用于保证该字段的值不能为空 比如姓名 学号等
					2.DEFAULT: 默认 用于保证该字段有默认值 比如性别
					3.PRIMARY KEY：主键 用于保证该字段的值具有唯一性 并且非空
					比如 学号 员工编号等 
					4.UNIQUE:唯一性 保证该字段的值具有唯一性 可以为空 比如座位号
					5.CHECK：检查约束[mysql不支持] 比如 年龄 性别 
					6.FOREIGN KEY :外键 用于限制两个表的关系 用于保证字段的值必须来自于主表的关联的值
												在从表添加外键约束 用于引用主表中某列的值
												比如学生表的专业编号 员工表的部门编号 

添加约束时机：
					1.创建表时
					2.修改表时
数据添加之前 					
					
约束的添加分类
			列级约束：
			六大约束语法以上都支持 但外键约束没有效果
			
			表级约束：
			
					除了非空 默认 其他都支持
					
主键和唯一键 					
		保证唯一性	是否允许为空	一个表中可以有多少个	  是否允许组合
主键 			√		×			至多一个			允许 不推荐															 						

唯一			√		√			有多个			允许 不推荐																	
			
外键：
				1.要求从表设置外键关系 
				2.从表的外键列的类型和主表的关联列的类型要一致或兼容 名城无要求
				3.主表的关联列必须是一个key(主键，唯一)
				4.插入数据时 先插入主表 在插入从表 
					删除数据时 先删除从表 在删除主表
*/

CREATE TABLE 表名 (
			字段名  字段类型 列级约束 
			字段名  字段类型,
			表级约束	
);


# 一、创建表时约束 
# 1.添加列级约束
/*
语法：
	
	直接在字段名和类型后面 追加 约束类型即可
	
	只支持：默认 非空 主键 唯一 
*/
#表级约束	  唯一索引
ALTER TABLE actor ADD CONSTRAINT uniq_idx_firstname UNIQUE( first_name) ;
# 对last_name创建普通索引idx_lastname
ALTER TABLE actor ADD INDEX idx_lastname (last_name) USING BTREE;

#二、 修改表时添加约束
/*
1.添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束

2.添加表级约束
ALTER TABLE 表名 add [CONSTRAINT 约束名]  约束类型(字段名) [外键引用]
*/

# 三、修改表时删除约束

#删除主键
ALTER TABLE stuinfo DROP PRIMARY key;
#删除唯一
alter table 表名 drop index 索引名

CREATE DATABASE students;

use students;

DROP TABLE if  EXISTS  stuinfo;
CREATE TABLE stuinfo(
		id int PRIMARY key COMMENT '主键 ',
		stuName VARCHAR(20) NOT NULL COMMENT '非空',
		gender CHAR(1) CHECK(gender='男' or gender='女'),
		seat int UNIQUE COMMENT '唯一',
		age int DEFAULT 18 COMMENT '默认值',
		majorId int  REFERENCES major(id)#外键
);

desc stuinfo;


CREATE TABLE major(
		id int PRIMARY key,
		majorName VARCHAR(20)
);

desc major;

#查看索引 表中所有的索引 包括主键  外键 唯一 
show index FROM stuinfo


# 2.添加表级约束
/*

语法：
在各个字段的最下面 
[CONSTRAINT 约束名] 约束类型(字段名)
*/


DROP TABLE IF EXISTS stuinfo;

CREATE TABLE stuinfo(
			id int ,
			stuname VARCHAR(20),
			gender char(1),
			seat int ,
			age int ,
			majorid int,
			#CONSTRAINT pk PRIMARY KEY(id,stuname), 组合主键 
			CONSTRAINT pk PRIMARY KEY(id),
			CONSTRAINT uq UNIQUE(seat),
			CONSTRAINT ck CHECK(gender='男' or gender='女'),
			CONSTRAINT fk_stuinfo_major FOREIGN key (majorid) REFERENCES major(id)
);

标识列|自增长列

#标识列 
/*
又称为自增长列
含义：可以不用手动的插入值 系统提供默认的序列值

特点：
1.标识列必须和主键搭配吗？ 不一定 但必须是一个key 
2.一个表最多只有一个增长列
3.标识列的类型只能是数值型
4.标识列可以通过SET auto_increment_increment =3 设置步长  
可以通过手动插入值 设置起始值 

*/
# 二、修改表时设置 标识列
ALTER TABLE tab_indentity MODIFY COLUMN id int PRIMARY key AUTO_INCREMENT;


DROP TABLE IF EXISTS tab_indentity;
CREATE TABLE tab_indentity(
			id int PRIMARY key AUTO_INCREMENT,
			NAME VARCHAR(20)
);



#一、创建表时设置标识列
SHOW VARIABLES LIKE '%auto_increment%'
#截断表 
TRUNCATE TABLE tab_indentity
#设置起始值  起始值为10 
INSERT into tab_indentity VALUES (10,'john');
INSERT into tab_indentity VALUES (NULL,'john');

SELECT * FROM tab_indentity

#设置步长
SET auto_increment_increment =1

级联删除

SHOW INDEX FROM major;

SHOW INDEX FROM stuinfo;

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

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

SELECT * FROM major ;
SELECT * FROM stuinfo;

INSERT INTO major VALUES (3,'vue')

INSERT INTO stuinfo 
SELECT 1,'john1','女',NULL,NULL,1 UNION ALL 
SELECT 2,'john2','男',NULL,NULL,3 UNION ALL 
SELECT 3,'john3','女',NULL,NULL,3 UNION ALL 
SELECT 4,'john4','男',NULL,NULL,2 UNION ALL 
SELECT 5,'john5','女',NULL,NULL,1


#一、 级联删除 ON DELETE CASCADE  主表从表数据都删除 
ALTER TABLE stuinfo ADD   FOREIGN KEY fk_stuinfo_majors(majorid) REFERENCES major (id) ON DELETE CASCADE ;
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_majors  FOREIGN KEY (majorid) REFERENCES major (id) ON DELETE CASCADE ;

# 主表数据 先删从表 在删主表
DELETE FROM major WHERE id=3;

#二、级联置空 ON DELETE SET NULL 主表数据删除 从表置空 
ALTER TABLE stuinfo ADD   FOREIGN KEY fk_stuinfo_majors(majorid) REFERENCES major (id) ON DELETE SET NULL ;
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_majors  FOREIGN KEY (majorid) REFERENCES major (id) ON DELETE SET NULL ;

DELETE FROM major WHERE id=3;

澜然

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础-表数据插入-创建

DML语言#一、插入语句/**语法：INSERT INTO 表名(列名,.....) VALUES(值1,值2 .... );① 可以为null值的列可以填充null② 空值列可以去掉不写③ 列的顺序是否可以调换④ 列数和值的个数必须一致⑤ 可以省略列名默认所有列而且列的顺序和表中列的顺序一致*/# 方式二/**语法：INSERT into 表名 set 列名1=值1, 列名2=值2,...*/1,方式一支持插入多行方式二不支持 2,方式一支持子查询，方式
复制链接

扫一扫

专栏目录