dml和mysql的关联_我在b站学MySQL！！！——分页查询+联合查询+DDL+DML

淡淡的烟草味﹌ 2023-01-12 04:23 130阅读 0赞

是夹心呀：我在b站学MySQL！！！——基础+查询zhuanlan.zhihu.com![a1ed4b36ad880225291c59ee758fc6ec.png][]是夹心呀：我在b站学MySQL！！！——函数+分组zhuanlan.zhihu.com![c6f6a8fc39f0c79aec0e4c46181df930.png][]是夹心呀：我在b站学MySQL！！！——连接+子查询zhuanlan.zhihu.com![19d82b4e7e7340c87892e1a7fd71bc18.png][]

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

第十一章 分页查询

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

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

语法：

select 查询列表

from 表1 别名

join 表2 别名

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;pagesize=10

1limit 0,10

2limit 10,10

3limit 20,10

4limit 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 表1

where 筛选条件

union

select 查询列表

from 表2

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,'吴倩','男','wuqian@qq.com',12,1);

INSERT INTO stuinfo(stuid,stuname,stugender,email,age,majorid)

VALUES(6,'李宗盛2','女','wuqian@qq.com',45,2);

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

\#方案1：字段名和值都不写INSERT INTO stuinfo(stuid,stuname,email,majorid)

VALUES(5,'齐鱼','qiqin@qq.com',2);

\#方案2：字段名写上，值使用nullINSERT INTO stuinfo(stuid,stuname,email,age,majorid)

VALUES(5,'齐鱼','qiqin@qq.com',NULL,2);

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

\#方案1：字段名写上，值使用defaultINSERT INTO stuinfo(stuid,stuname,email,stugender,majorid)

VALUES(7,'齐小鱼','qiqin@qq.com',DEFAULT,2);

\#方案2：字段名和值都不写INSERT INTO stuinfo(stuid,stuname,email,majorid)

VALUES(7,'齐小鱼','qiqin@qq.com',2);

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

INSERT INTO stuinfo VALUES(8,'林忆莲','女','lin@126.com',12,3);

INSERT INTO stuinfo VALUES(NULL,'小黄','男','dd@12.com',12,3);

二、数据的修改

语法：

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

where 筛选条件;

\#案例1：修改年龄<20的专业编号为3号，且邮箱更改为 xx@qq.comUPDATE stuinfo SET majorid = 3,email='xx@qq.com'

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年级');

[a1ed4b36ad880225291c59ee758fc6ec.png]: https://img-blog.csdnimg.cn/img_convert/a1ed4b36ad880225291c59ee758fc6ec.png
[c6f6a8fc39f0c79aec0e4c46181df930.png]: https://img-blog.csdnimg.cn/img_convert/c6f6a8fc39f0c79aec0e4c46181df930.png
[19d82b4e7e7340c87892e1a7fd71bc18.png]: /images/20221119/42c09ff8093a4600901f65b0e4144598.png