MySQL基础学习4

标签（空格分隔）： MySQL

DML语言

数据操作语言：
插入：insert
修改：update
删除：delete

一、插入语言

插入方式1、

语法：
insert into 表名（列名，...）
values（值1，...）

插入的值的类型要与列的类型一致或兼容

INSERT INTO
beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','18988888888',NULL,2) ;
SELECT * FROM beauty ;

2.不可以为null的列必须插入值，可以为null的列如何插入值？

方式一：列名保留，填充null

INSERT INTO beauty(`id`,`name`,`sex`,`borndate`,`phone`,`boyfriend_id`)
VALUES(13,'唐艺昕','女','1990-4-23','18988888888',2) ;
SELECT * FROM `beauty` ;

方式二：不保留列名，不填充

INSERT INTO beauty(`id`,`name`,`sex`,`borndate`,`phone`,`boyfriend_id`)
    VALUES(14,'金星','女','1960-4-23','18966688888',2) ;
    SELECT * FROM `beauty` ;
或
INSERT INTO beauty(`id`,`name`,`sex`,`phone`)
VALUES(15,'古力娜扎','女','18966688888') ;没有填充的，如果有默认值将自动的将默认值填充进去，没有默认值则填充null

列的顺序是否可以调换（可以）

INSERT INTO beauty(name,sex,id,phone)
VALUES('蒋欣','女',16,'123979657575') ;
列数和值的个数必须一致
可以忽略列名，默认所有列，而且列的顺序和表中的顺序一致

INSERT INTObeauty
VALUES(18,'张飞','男',NULL,'1192322424245',NULL,NULL) ;

插入方式2、

语法：
insert into 表名
set 列名 = 值，列名 = 值，....

INSERT INTO `beauty`
 SET id=19,NAME='刘涛',sex = '女', phone = '909999' ;

两种方式大PK

1.方式一支持插入多行，方式二不支持

    INSERT INTO beauty(`id`,`name`,`sex`,`borndate`,`phone`,`boyfriend_id`)
    VALUES(20,'艺昕','女','1990-4-23','18988888888',2) ,
    (23,'西昕','女','1990-4-23','18988888888',2), 
    (33,'唐昕','女','1990-4-23','18988888888',2) ;

2.方式一支持子查询，方式二不支持（子查询相当于插入）

INSERT INTO `beauty` (`id`,`name`,`phone`)
SELECT 26,'宋茜','118087678665' ;

二、修改语句

1.修改单表的记录（*）
语法：
update 表名
set 列 = 新值，列 = 新值，.....
where 筛选条件；

2.修改多表的记录
语法：
SQL92语法：
update 表1 别名，表2 别名
set 列 = 新值，.....
where 连接条件
and 筛选条件

SQL99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列 = 新值，.....
筛选条件

1、修改单表的记录
案例1、：修改beauty表中姓唐的女神的电话为13809009671

UPDATE `beauty` SET `phone`= '13809009671'
WHERE `name` LIKE '唐%' ;

案例2、修改boys表中id号为2的名称为张飞，魅力值为10

UPDATE boys SET `boyName` = '张飞',`userCP` = 10
WHERE `id` = 2 ;

1、修改多表的记录
案例1：修改张无忌的女朋友的手机号为144

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

案例2：将没有男朋友的女神的男朋友编号都改成2

UPDATE boys AS bo
RIGHT JOIN  beauty AS b
ON bo.`id` = b.`boyfriend_id`
SET b.`boyfriend_id` = 2
WHERE bo.`id` IS NULL ;

三、删除语句

方式1：delete
语法：
1.单表的删除（*）
delete from 表名 where 筛选条件

2.多表的删除
SQL92：
delete 表1的（别名），表2的（别名）
from 表1 as 别名，表2 as 别名
where 连接条件
and 筛选条件

SQL99语法：
delete 表1的别名，表2的别名
from 表1 as 别名
inner|left|right join 表2 as 别名
on 连接条件
where 筛选条件；

方法2：truncate
语法：truncate table 表名

delete PK truncate

truncate 删除，效率高一点点
假如要删除的表有自增列
如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长的值从1开始
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚

方式一：delete语句

单表的删除
案例：删除手机号以9结尾的女神信息

DELETE FROM beauty
WHERE `phone` LIKE '%9' ;

多表的删除
案例：删除张无忌的女朋友的信息

DELETE b
FROM `beauty` AS b
INNER JOIN `boys` AS bo
ON bo.`id` = b.`boyfriend_id`
WHERE bo.`boyName` = '张无忌' ;

案例：删除黄晓明的信息以及他的女朋友的信息

DELETE b, bo
FROM `beauty` AS b
INNER JOIN boys AS bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName` = '黄晓明' ;

方式二：truncate语句

案例：将魅力值>100的男神信息删除

TRUNCATE TABLE boys ;

DDL（数据定义语言）

库和表的管理

一、库的管理
创建、修改、删除
二、表的管理
创建、修改、管理

创建： create
修改： alter
删除： drop

一、库的管理

1、库的创建
语法：
create database 库名；

案例：创建库Books

  CREATE DATABASE Books ;

加大容错率：添加IF NOT EXISTS

CREATE DATABASE IF NOT EXISTS Books ;

2、库的修改
更改库的字符集

 ALTER DATABASE books CHARACTER SET gbk ;

3、库的删除

  DROP DATABASE IF EXISTS books ;

二、表的管理

1、表的创建（*）

create table 表名（
列名列的类型【（长度0）约束】
列名列的类型【（长度0）约束】
列名列的类型【（长度0）约束】
列名列的类型【（长度0）约束】
。。。
列名列的类型【（长度）约束】
）；

案例：创建表book

CREATE TABLE book(
id INT,#编号
bName VARCHAR(20),#图书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期
) ;

案例：创建author

CREATE TABLE auther(
id INT,
au_nume VARCHAR(20),
nation VARCHAR(10)
) ;

2、表的修改
语法：
ALTER TABLE 表名 add增加|drop删除|modify修改|change改列名 COLUMN 列名【列类型约束】

1.修改列名

 ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME ;

2.修改列名的类型或约束

ALTER TABLE book MODIFY COLUMN `pubDate` TIMESTAMP【（更改的数值）】 ;

3.添加新列

ALTER TABLE auther ADD COLUMN annual DOUBLE ;

4.删除列

ALTER TABLE auther DROP COLUMN annual ;

5.修改表名

ALTER TABLE auther RENAME TO book_author ;

三、表的删除

关于表：IF EXISTS只在表的创建与删除中有

 DROP TABLE IF EXISTS book_author ;

库与表的删除与创建#通用写法：
库的：
DEOP DATABASE IF EXUSTS 久库名 ;
CREATE DATABASE 新库名 ;

表的：
DEOP table IF EXISTS 久表名 ;
CREATE TABLE 新表名 ;

四、表的复制

1、仅仅只复制表的结构

CREATE TABLE copy LIKE auther ;

2、复制表的结构+数据

CREATE TABLE copy LIKE auther ;
CREATE TABLE copy2
SELECT * FROM `auther` ;

3、只复制部分数据

CREATE TABLE copy3
SELECT * FROM `auther` 
WHERE nation = '中国' ;

4、仅仅复制某些字段

CREATE TABLE copy4
SELECT `id`,`au_nume` 
FROM `auther` 
WHERE 0 ;

数据类型

原则：
所选择的类型越简单越好，能保存数值越小越好

常见的数据类型：
数值型：
1、整型
2、小数：定点数，浮点数
字符型：
1、较短的文本：char、varcher
2、较长的文本：text、blob（较长的二进制数据）
日期型

一、整数

• 整型

整数类型	字节	范围
Tinyint	1	有符号：-128~127 无符号：0~255
Smallint	2	有符号：-32768~32767 无符号：0~65535
Mediumint	3	有符号：-8388608_{8388607无符号：0}1677215(好吧，反正很大，不用记住)
Int、integer	4	有符号：- 2147483648~2147483647 无符号：0~4294967295(好吧，反正很大，不用记住)
Bigint	8	有符号：-9223372036854775808~9223372036854775807 无符号：0~9223372036854775807*2+1 (好吧，反正很大，不用记住）

特点：
（1）如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要加unsigned关键字
（2）如果插入的数值超出范围，会报out of range异常，并且插入临界值
（3）如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

如何设置无符号和有符号

CREATE TABLE tab_int(
t1 INT(7) zerofill,
t2 INT(7) zerofill UNSIGNED
) ;#创建表
INSERT INTO tab_int VALUES(-123133,-123133) ;#给表赋值
SELECT * FROM tab_int ;查看表

二、小数

• 小数

|浮点数类型| 字节 |范围|
|---|---|---|---|
|float| 4| ±1.75494351E-38~±3.402823466E+38(好吧，反正很大，不用记住)|
|double| 8 |±2.2250738585072014E-308~±1.7976931348623157E+308(好吧，反正很大，不用记住)|
|定点数类型| 字节| 范围|
|DEC(M,D) DECIMAL(M,D)|M+2| 最大取值范围与double相同，给定decimal的有效取值范围由M和D决定|

1.浮点型：
float(M,D)
doube(M,D)
2.定点型
dec(M,D)
decimal(M,D)

特点：
（1）M和D
M:整数部位+小数部位
D:小数部位
（2）
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
（3）定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

CREATE TABLE tab_float(
f1 FLOAT(5,2),
f2 DOUBLE(5,2),
f3 DECIMAL(5,2)
) ;#创表
INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523) ;#赋值
INSERT INTO tab_float VALUES(123.456,123.456,123.456) ;
INSERT INTO tab_float VALUES(123.4,123.4,123.4) ;

三、字符型

较短文本：
char
varchar

较长的文本：
text
blob（较大的二进制）

特点：

无	写法	M的意思	特点	空间耗费	效率
char	char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar	varchar(M)	最大的字符，不可以忽略	可变长度的字符	比较节省	低

enum型（枚举）

只能存储枚举过的，其他的将不会存储进去，大小写会转换，不区分大小写

CREATE TABLE tab_char(
c1 ENUM ('a','b','c')
) ;
 INSERT INTO tab_char VALUES('a') ;#a
 INSERT INTO tab_char VALUES('b') ;#b
 INSERT INTO tab_char VALUES('c') ;#c
 INSERT INTO tab_char VALUES('m') ;#无
 INSERT INTO tab_char VALUES('A') ;#a

四、日期型

分类：
date只保存日期
time只保存时间

datetime保存日期+时间
timestamp保存日期+时间
特点：

无	字节	范围	时区等的影响
datetime	8	1000-9999	不受
timestamp	4	1970-2038	受

CREATE TABLE tab_date(
 t1 DATETIME,
 t2 TIMESTAMP
 ) ;
 INSERT INTO tab_date VALUES(NOW(),NOW()) ;#存入现在的时间

常见约束

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

分类：六大约束
NOT NULL；非空，用于保证该字段的值不能为空
DEFAULT:默认，用于保证该字段有默认值
PRIMARY：主键，用于保证该字段的值具有唯一性，并且非空
UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空
CHECK：检查约束【MySQL中不支持】
FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须由来自于主表的关系列的值。在从表添加约束，用于引用主表中的某列的值

添加约束的时机：
1.创建表时
2.修改表时

约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束不支持
表级约束：
除了非空，默认，其他都支持

CREATE TABLE 表名（
字段名 字段类型 列级约束
字段名 字段类型，
表级约束
）

一、创建表时添加约束

1.添加列级约束
语法：
直接在字段名和类型名后面追加约束类型即可
只支持：默认、非空、主键、唯一

 USE students ;
 DROP TABLE stuinfo ;
 CREATE TABLE stuinfo(
 id INT PRIMARY KEY,#主键
 stuName VARCHAR(20) NOT NULL,#非空
 gender CHAR(1) CHECK (gender ='男' OR gender = ' 女'),#检查
 seat INT UNIQUE,#唯一
 age INT DEFAULT 18,#默认约束
 majorId INT REFERENCES major(id)#外键
 ) ;
 
 CREATE TABLE major(
 id INT PRIMARY KEY,
 majorName VARCHAR(20)
 ) ;

查看stuinfo表中的所有的索引，包括主键、外键、唯一

 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),#主键
 CONSTRAINT uq UNIQUE(seat),#唯一键
 CONSTRAINT ck CHECK(gender = '男' OR gender = '女'),#检查
 CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
 ) ;

查看stuinfo表中的所有的索引，包括主键、外键、唯一

 SHOW INDEX FROM stuinfo ;#查看索引

通用写法：

 CREATE TABLE IF NOT EXISTS stuinfo(
 id  INT PRIMARY KEY,
 stuname VARCHAR(20) NOT NULL,
 sxe CHAR(1),
 age INT DEFAULT 18,
 seat INT UNIQUE,
 majorid INT,
 CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
 ) ;

主键和唯一的大对比：
|无|保证唯一性|是否允许为空|一个表中可以有多少个|是否允许组合|
|---|---|---|---|
|主键|√|×|至多有一个|√，但不推荐|
|唯一|√|√|可以多个|√，但不推荐|

外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求保持一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一键）
4、插入数据时，先插入主表，再插入从表。删除数据时，先删除从表，再删除主表

二、修改表时添加约束

（1 添加列级约束
alter table 表名 modify column 字段名字段类型新约束;
（2 添加表级约束
alter table 表名 add 【constraint 约束名】约束条件（字段名）【外键的引用】 ;

CREATE TABLE IF NOT EXISTS stuinfo(
 id  INT PRIMARY KEY,
 stuname VARCHAR(20) NOT NULL,
 sxe CHAR(1),
 age INT DEFAULT 18,
 seat INT UNIQUE,
 majorid INT
 ) ;

1、添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL ;

2、添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18 ;

3、添加主键

1.列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY ;
2.表级约束
ALTER TABLE stuinfo ADD 【CONSTRAINT 名字】 PRIMARY KEY(id) ;

4、添加唯一键

  列级约束
  ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE ;
  表级约束
  ALTER TABLE stuinfo ADD UNIQUE(seat) ;

5、添加外键

ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id) ;

三、修改表时删除约束

1、删除非空约束

  ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL ;

2、删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

3、删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY ;

4、删除唯一

ALTER TABLE stuinfo DROP INDEX seat ;
SHOW INDEX FROM stuinfo ;#查询唯一索引，确定索引名

5、删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_majorid ;

标识列

又称自增长列
含义：可以不用手动的插入值，系统提供默认的序列值

特点：
1.标识列必须和主键搭配？不一定，但要求是一个key
2.一个表可以有几个标识列？至多一个
3.标识列的类型只能是数值型
4.标识列可以通过 SET auto_increment_increment = 3; 设置步长

1、创建表时设置标识列（AUTO_INCREMENT关键字）

CREATE TABLE tab_identity(
  id INT PRIMARY KEY AUTO_INCREMENT ,
  NAME VARCHAR(20)
  ) ;#创建表
  TRUNCATE TABLE tab_identity ;#删表
  INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john') ;#给值，可多次重复，进行 
  INSERT INTO tab_identity(NAME) VALUES('luck') ;
  SELECT * FROM tab_identity ;#查表

更改步长

SET ato_increment_increment = 3;

初始序列号的方法：

INSERT INTO tab_identity(id,NAME) VALUES(初始序列号,'john')#初始
INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john')#后续

2、修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT ;

3、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT ;

posted @ 2024-08-08 11:24 我的十四行诗在哪里阅读(7) 评论(0) 编辑收藏举报

刷新页面返回顶部

fengmian13wl