mysql关联表update

是夹心呀：我在b站学MySQL！！！——基础+查询zhuanlan.zhihu.com

是夹心呀：我在b站学MySQL！！！——函数+分组zhuanlan.zhihu.com

是夹心呀：我在b站学MySQL！！！——连接+子查询zhuanlan.zhihu.com

继上三篇之后，我们来学第四篇。

第十一章 分页查询

应用场景：当页面上的数据，一页显示不全，则需要分页显示

分页查询的sql命令请求数据库服务器——>服务器响应查询到的多条数据——>前台页面

语法：

select

from

join

on

where

group by

having

order by

limit

执行顺序：

1》from子句

2》join子句

3》on子句

4》where子句

5》group by子句

6》having子句

7》select子句

8》order by子句

9》limit子句

特点：

①起始条目索引如果不写，默认是0

②limit后面支持两个参数

参数1：显示的起始条目索引

参数2：条目数

公式：

假如要显示的页数是page，每页显示的条目数为size

select *

from employees

limit (page-1)*size,size;

page	size=10
1	limit 0,10
2	limit 10,10
3	limit 20,10
4	limit 30,10

#案例1：查询员工信息表的前5条
SELECT * FROM employees LIMIT 0,5;
#完全等价于
SELECT * FROM employees LIMIT 5;

#案例2：查询有奖金的，且工资较高的第11名到第20名
SELECT 
    * 
FROM
    employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC
LIMIT 10,10;

---

第十二章 联合查询

当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为union查询

语法：

select

from

where

union

select

from

where

特点：

1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致

2、union实现去重查询

union all 实现全部查询，包含重复项

#案例：查询所有国家的年龄>20岁的用户信息
SELECT * FROM usa WHERE uage >20 
UNION
SELECT * FROM chinese WHERE age >20 ;

#案例2：查询所有国家的用户姓名和年龄
SELECT uname,uage FROM usa
UNION
SELECT age,`name` FROM chinese;

#案例3：union自动去重/union all 可以支持重复项
SELECT 1,'范冰冰' 
UNION ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' ;

---

第十三章 DDL语言

说明：Data Define Language数据定义语言,用于对数据库和表的管理和操作

一、创建数据库

CREATE DATABASE stuDB;
CREATE DATABASE IF NOT EXISTS stuDB;

二、删除数据库

DROP DATABASE stuDB;
DROP DATABASE IF EXISTS stuDB;

三、创建表

语法：

CREATE TABLE [IF NOT EXISTS] 表名(

字段名 字段类型 【字段约束】,

字段名 字段类型 【字段约束】,

字段名 字段类型 【字段约束】,

字段名 字段类型 【字段约束】,

字段名 字段类型 【字段约束】

);

#案例1：没有添加约束
CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT ,
	stuname VARCHAR(20),
	stugender CHAR(1),
	email VARCHAR(20),
	borndate DATETIME
);

#案例2：添加约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT PRIMARY KEY,#添加了主键约束
	stuname VARCHAR(20) UNIQUE NOT NULL,#添加了唯一约束+非空
	stugender CHAR(1) DEFAULT '男',#添加了默认约束
	email VARCHAR(20) NOT NULL,
	age INT CHECK( age BETWEEN 0 AND 100),#添加了检查约束，mysql不支持
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)#添加了外键约束

);

（一）数据类型：

1、整数型

TINYINT SMALLINT INT BIGINT

2、浮点型

FLOAT(m,n)

DOUBLE(m,n)

DECIMAL(m,n)

m和n可选

3、字符型

CHAR(n):n可选

VARCHAR(n)：n必选

TEXT

n表示最多字符个数

4、日期型

DATE TIME DATETIME TIMESTAMP

5、二进制型

BLOB 存储图片数据

常用类型：

• int：整型
• double/float：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
• decimal：浮点型，在表示钱方面使用该类型，因为不会出现精度缺失问题；
• char：固定长度字符串类型；char(4) 范围是0-255
• varchar：可变长度字符串类型；
• text：字符串类型；表示存储较长文本
• blob：字节类型；//jpg mp3 avi
• date：日期类型，格式为：yyyy-MM-dd
• time：时间类型，格式为：hh:mm:ss
• timestamp/datetime：时间戳类型；日期+时间 yyyyMMdd hhmmss

（二）常见约束

说明：用于限制表中字段的数据的，从而进一步保证数据表的数据是一致的、准确的、可靠的！

NOT NULL 非空：用于限制该字段为必填项

DEFAULT 默认：用于限制该字段没有显式插入值，则直接显式默认值

PRIMARY KEY 主键：用于限制该字段值不能重复，设置为主键列的字段默认不能为空；一个表只能有一个主键，当然可以是组合主键

UNIQUE 唯一：用于限制该字段值不能重复

字段是否可以为空	一个表可以有几个
主键 ×	1个
唯一 √	n个

CHECK检查：用于限制该字段值必须满足指定条件

CHECK(age BETWEEN 1 AND 100)

FOREIGN KEY 外键:用于限制两个表的关系,要求外键列的值必须来自于主表的关联列

要求：

①主表的关联列和从表的关联列的类型必须一致，意思一样，名称无要求

②主表的关联列要求必须是主键

四、修改表

语法：ALTER TABLE 表名 ADD|MODIFY|CHANGE|DROP COLUMN 字段名 字段类型 【字段约束】;

1.修改表名

ALTER TABLE stuinfo RENAME TO students;

2.添加字段

ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL;
DESC students;

3.修改字段名

ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL;

4.修改字段类型

ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;

5.删除字段

ALTER TABLE students DROP COLUMN birthday;
DESC students;

五、删除表

DROP TABLE IF EXISTS students;

六、复制表

仅仅复制表的结构

CREATE TABLE newTable2 LIKE major;

复制表的结构+数据

CREATE TABLE newTable3 SELECT * FROM girls.`beauty`;

案例：复制employees表中的last_name,department_id,salary字段到新表 emp表，但不复制数据

CREATE TABLE emp 
SELECT last_name,department_id,salary 
FROM myemployees.`employees`
WHERE 1=2;

---

第十三章 DML语言

DML(Data Manipulation Language)数据操纵语言：insert update delete

对表中的数据的增删改

一、数据的插入

语法：

插入单行：

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

插入多行：

insert into 表名(字段名1,字段名2 ,...) values

(值1，值2,...),(值1，值2,...),(值1，值2,...);

特点：

①字段和值列表一一对应

包含类型、约束等必须匹配

②数值型的值，不用单引号

非数值型的值，必须使用单引号

③字段顺序无要求

#案例1：要求字段和值列表一一对应，且遵循类型和约束的限制

INSERT INTO stuinfo(stuid,stuname,stugender,email,age,majorid)
VALUES(1,'吴倩','男','[e-mail]',12,1); 

INSERT INTO stuinfo(stuid,stuname,stugender,email,age,majorid)
VALUES(6,'李宗盛2','女','[e-mail]',45,2); 

#案例2：可以为空字段如何插入

#方案1：字段名和值都不写
INSERT INTO stuinfo(stuid,stuname,email,majorid)
VALUES(5,'齐鱼','[e-mail]',2); 

#方案2：字段名写上，值使用null
INSERT INTO stuinfo(stuid,stuname,email,age,majorid)
VALUES(5,'齐鱼','[e-mail]',NULL,2); 


#案例3：默认字段如何插入

#方案1：字段名写上，值使用default
INSERT INTO stuinfo(stuid,stuname,email,stugender,majorid)
VALUES(7,'齐小鱼','[e-mail]',DEFAULT,2); 

#方案2：字段名和值都不写
INSERT INTO stuinfo(stuid,stuname,email,majorid)
VALUES(7,'齐小鱼','[e-mail]',2); 

#案例4：可以省略字段列表，默认所有字段

INSERT INTO stuinfo VALUES(8,'林忆莲','女','[e-mail]',12,3);
INSERT INTO stuinfo VALUES(NULL,'小黄','男','[e-mail]',12,3);

二、数据的修改

语法：

update 表名 set 字段名 = 新值,字段名=新值,...

where 筛选条件;

#案例1：修改年龄<20的专业编号为3号，且邮箱更改为 [e-mail]
UPDATE stuinfo SET majorid = 3,email='[e-mail]'
WHERE age<20;

三、数据的删除

方式1：delete语句

语法：delete from 表名 where 筛选条件;

方式2：truncate语句

语法：truncate table 表名;

#案例1：删除姓李所有信息
DELETE FROM stuinfo WHERE stuname LIKE '李%';

#案例2：删除表中所有数据
TRUNCATE TABLE stuinfo ;

【面试题】delete和truncate的区别

1.delete可以添加WHERE条件

TRUNCATE不能添加WHERE条件，一次性清除所有数据

2.truncate的效率较高

3.如果删除带自增长列的表，

使用DELETE删除后，重新插入数据，记录从断点处开始

使用TRUNCATE删除后，重新插入数据，记录从1开始

SELECT * FROM gradeinfo;
TRUNCATE TABLE gradeinfo;
INSERT INTO gradeinfo(gradename)VALUES('一年级'),('2年级'),('3年级');

4.delete删除数据，会返回受影响的行数

TRUNCATE删除数据，不返回受影响的行数

5.delete删除数据，可以支持事务回滚

TRUNCATE删除数据，不支持事务回滚

补充：设置自增长列

1、自增长列要求必须设置在一个键上，比如主键或唯一键

2、自增长列要求数据类型为数值型

3、一个表至多有一个自增长列

CREATE TABLE gradeinfo(
	gradeID INT PRIMARY KEY AUTO_INCREMENT,
	gradeName VARCHAR(20)
);

SELECT * FROM gradeinfo;
INSERT INTO gradeinfo VALUES(NULL,'一年级'),(NULL,'2年级'),(NULL,'3年级');
INSERT INTO gradeinfo(gradename)VALUES('一年级'),('2年级'),('3年级');