MySQL3-阿里云开发者社区

MySQL2:https://developer.aliyun.com/article/1580071

9 数据表操作

9.1数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串（字符）类型。对于我们约束数据的类型有很大的帮助

9.1.1 数值类型

类型	大小	范围（有符号）	范围《无符号）	用途
INT	4字节	(-2 147 483 648, 2 147 483 647)	(0, 4 294 967 295)	大整数值

DOUBLE

8字节

(-1.797E+308,-2.22E-308)

(0,2.22E-308,1.797 E+308)

双精度浮点数值

DOUBLE(M,D)

8个字节，M表示长度，D表示小数位数

同上，受M和D的约束 DOUBLE(5,2)-999.99-999.99

同上，受M和D的约束

双精度浮点数值

DECIMAL(M,D)

DECIMAL(M,D)

依赖于M和D的值，M最大值为65

小数值

9.1.2 日期类型

类型	大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值

TIME	3	‘-838:59:597’838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值

DATETIME

1000-01-01 00:00:00/9999-12-31 23:59:59

YYYY-MM-DDHH:MM:SS

混合日期和时间值

TIMESTAMP

1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒北京时间 2038-1-19 11:14:07,格林尼治时间2038年1月19日凌晨03:14:07

YYYYMMDDHHMMSS

混合日期和时间值，时间戳

9.1.3 字符串类型

CHAR	0-255字符	定长字符串char(10)丄0个字符
VARCHAR	0-65535 字节	变长字符串varchar(lO) 10个字符
BLOB (binary large object)	0-65535字节	二进制形式的长文本数据

TEXT

0-65535字节

长文本数据

• CHAR和VARCHAR类型类似，但它们保存和検索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB. BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

9.2 数据表的创建(CREATE)

CREATE TABLE 表名(

列名数据类型［约束］,

…

列名数据类型［约束］//最后一列的末尾不加逗号

)［charset=utf8］ //可根据需要指定表的字符编码集

9.2.1创建表

列名	数据类型	说明
subjectld	INT	课程编号
subjectName	VARCHAR(20)	课程名称
subjectHours	INT	课程时长

#依据上述表格创建数据表，并向表中插入3条测试语句
CREATE TABLE 
  subject( subjectld INT, 
  subjectName VARCHAR(20), 
  subjectHours INT
)charset=utf8;

INSERT INTO subject(subjectld,subjectName,subjectHours) VALUES(1,'Java',40);
INSERT INTO subject(subjectld,subjectName,subjectHours) VALUES(2,'MYSQL',20);
INSERT INTO subject(subjectld,subjectName,subjectHours) VALUES(3,'JavaScript',30);

9.3 数据表的修改(ALTER)

ALTER TABLE表名操作；

9.3.1 向现有表中添加列

#在课程表基础上添加gradeld列
ALTER TABLE subject ADD gradeld int;

9.3.2 修改表中的列

#修改课程表中课程名称长度为1。个字符
ALTER TABLE subject MODIFY subjectName VARCHAR(10);

注意：修改表中的某列时，也要写全列的名字，数据类型，约束

9.3.3 删除表中的列

#删除课程表中gradeld列
ALTER TABLE subject DROP gradeld;

注意：删除列时，每次只能删除一列

9.3.4 修改列名

#修改课程表中subjectHours列为classHours
ALTER TABLE subject CHANGE subjectHours classHours int ;

注意：修改列名时，在给定列新名称时，要指定列的类型和约束

9.3.5 修改表名

##修改课程表的subject为sub
ALTER TABLE subject rename sub;

9.4 数据表的删除(DROP)

DROP TABLE 表名

9.4.1 删除学生表

#刪除学生表
DROP TABLE subject；

10 约束

•问题：在往已创建表中新增数据时，可不可以新增两行相同列值得数据？

•如果可行，会有什么弊端？

10.1实体完整性约束

表中的一行数据代表一个实体(entity),实体完整性的作用即是标识每一行数据不重复、实体唯一。

10.1.1主键约束

PRIMARY KEY唯一，标识表中的一行数据，此列的值不可重复，且不能为NULL

#为表中适用主键的列添加主键约束
CREATE TABLE subject(
  subjectld INT PRIMARY KEY,#课程编号标识每一个课程的编号唯一，且不能为NULL
  subjectName VARCHAR(20),
  subjectHours INT
)charset=utf8;
INSERT INTO subject(subjectld,subjectName,subjectHours) VALUES(1,'Java* ,40);
INSERT INTO sub ject( sub j ectld, subjectName, subjectHours) VALUES (1, 'Java* ,49) ;#error 主铤 1 已存在

10.1.2睢一约束

UNIQUE睢一，标识表中的一行数据，不可重复，可以为NULL

#为表中列值不允许重复的列添加唯一约束
CREATE TABLE subject(
  subjectld INT PRIMARY KEY,
  subjectName VARCHAR(20) UNIQUE,#课程名称唯一！
  subjectHours INT
)charset=utf8;
INSERT INTO subject(subjectld,subjectName,subjectHours) VALUES(1,'Java',40);
INSERT INTO subject (subjectld, subjectName, subj ectHours) VALUES(2, 'Java* ,40) ;#error 课程名称已存在

10.1.3自动增长列

AUTO_INCREMENT自动增长，给主键数值列添加自动增长。从1开始，每次加1。不能单独使用，和主键配合。

#为表中主键列添加自动增长，避免忘记主键ID序号
CREATE TABLE subject(
 subjectld INT PRIMARY KEY AUTO.INCREMENT, ■#课程编号主键且自动增长，会从1幵始根据添加数据的顺序依次加1
 subjectName VARCHAR(20) UNIQUE,
 subjectHours INT
)charset=utf8;
INSERT INTO subject (subj ectName, subjectHours) VALUES( 'Java1,40) ;# 课程编号自动从 1 增长
INSERT INTO subject(subjectName, subjectHours) VALUES(' JavaScript1 , 30) ;#第二条编号为 2

10.2 域完整性约束

限制列的单元格的数据正确性。

10.2.1非空约束

NOT NULL非空，此列必须有值。

#课程名称虽然添加了唯一约束，但是有NULL值存在的可能，要避免课程名称为NULL
CREATE TABLE subject(
 subjectld INT PRIMARY KEY AUTO.INCREMENT,
 subjectName VARCHAR(20) UNIQUE NOT NULL, 
 subjectHours INT
)charset=utf8;
INSERT INTO subject(subjectName, subjectHours) VALUES(NULL,40) ;#error,课程名称约束了非空

10.2.2默认值约束

DEFAULT值为列赋予默认值，当新增数据不指定值时，书写DEFAULT,以指定的默认值进行填充。

#当存储课程信息时，若课程时长没有指定值，则以默认课时20填充
CREATE TABLE subject(
 subjectld INT PRIMARY KEY AUTO.INCREMENT,
 subjectName VARCHAR(20) UNIQUE NOT NULL, 
 subjectHours INT DEFAULT 20
)charset=utf8;

INSERT INTO subject(subjectName, subjectHours) VALUES(1 Java1 , DEFAULT) ;#课程时长以默认值 2。填充

10.2.3引用完整性约束

•语法：CONSTRAINT引用名FOREIGN KEY (列名)REFERENCES被引用表名例名)

•详解：FOREIGN KEY引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值。

#创建专业表
CREATE TABLE Speciality(
 id INT PRIMARY KEY AUTO_INCREMENT,
 SpecialName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;

#创建课程表(课程表的Specialld引用专业表的id)
CREATE TABLE subject(
 subjectld INT PRIMARY KEY AUTO_INCREMENT,
 subjectName VARCHAR(20) UNIQUE NOT NULL,
 subjectHours INT DEFAULT 20,
 specialld INT NOT NULL,
  CONSTRAINT fk_subject_specialld FOREIGN KEY(specialld) REFERENCES Speciality(id) #引用专业表里的 id 作为外键，新增课程 信息时，约束课程所属的专业。

)charset=utf8;
#专业表新增数据
INSERT INTO Speciality(SpecialName) VALUES('Java');
INSERT INTO Speciality(SpecialName) VALUES('C#');
#课程信息表添加数据
INSERT INTO subject(subjectName, subjectHours) VALUES(' Java1,30,1) ;#专业 id 为 1,引用的是专业表的 Java
INSERT INTO subject(subjectName, subjectHours) VALUES(1 C#MVC ,10,2);#专业 id 为 2,引用的是专业表的 C#

注意：当两张表存在引用关系，要执行删除操作，一定要先删除从表(引用表)，再删除主表(被引用表)

10.3 约束创建整合

创建带有约束的表

10.3.1 创建表

列名	数据类型	约束	说明
Gradeld	INT	主键、自动增长	班级编号
GradeName	VARCHAR(20)	唯一、非空	班级名称

CREATE TABLE Grade(
 Gradeld INT PRIMARY KEY AUTO.INCREMENT, 
 GradeName VARCHAR(20) unique NOT NULL )CHARSET=UTF8；

列名	数据类型	约束	说明
studentjd	VARCHAR(50)	主键	学号
Student_name	VARCHAR(50)	非空	姓名

sex	CHAR ⑵	默认填充’男’	性别
borndate	DATE	非空	生日
phone	VARCHAR(ll)	无	电话
Gradeld	INT	非空，外键约束:引用班级表的gradeido	班级编号

CREATE TABLE student(
 student_id varchar(50) PRIMARY KEY,
 student_name varchar(50) NOT NULL,
 sex CHAR(2) DEFAULT，男，
 borndate date NOT NULL,
 phone varchar(11),
 gradeld int not null,
 CONSTRAINT fk_student_gradeld FOREIGN KEY(gradeld) REFERENCES Grade(Gradeld) #引用Grade表的Gradeld列的值作为外键，插入 时约束学生的班级编号必须存在。
)；

注意：创建关系表时，一定要先创建主表，再创建从表

删除关系表时，先删除从表，再删除主表。

11 事务【重点】

11.1模拟转账

生活当中转账是转账方账户扣钱，收账方账户加钱。我们用数据库操作来模拟现实转账。

11.1.1数据库模拟转账

#A账户转账给B账户1000元。
#A账户减1。。。元
UPDATE account SET MONEY = MONEY-1000 WHERE id=1；
#B账户加1000元
UPDATE account SET MONEY = MONEY+1000 WHERE id=2;

上述代码完成了两个账户之间转账的操作。

11.1.2模拟转账错误

#A账户转账给B账户1000元。
#A账户减100。元
UPDATE account SET MONEY = MONEY-1000 WHERE id=1:
#断电、异常、出错...
#B账户加1000元
UPDATE account SET MONEY = MONEY+1000 WHERE id=2;

•上述代码在减操作后过程中出现了异常或加钱语句出错，会发现，减钱仍旧是成功的，而加钱失败了!

•注意：每条SQL语句都是一个独立的操作，一个操作执行完对数据库是永久性的影响。

11.2事务的概念

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

11.3事务的边界

•幵始：连接到数据库，执行一条DML语句。上一个事务结束后，又输入了一条DMLi吾句，即事务的幵始

•结束:

1）提交：

a. 显示提交：commit;

b. 隐式提交：一条创建、删除的语句，正常退出（客户端退出连接）；

2）回滚：

a. 显示回滚：rollback;

b. 隐式回滚：非正常退出（断电、宕机），执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。

11.4事务的原理

数据库会为每一个客户端都维护一个空间独立的缓存区（回滚段），一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束（commit）,才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（rollback）。

11.5事务的特性

• Atomicity（原子性）

表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败

• Consistency（—致性）

表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态

• Isolation（隔离性）

事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

• Durability］持久性）

持久性事务完成之后，它对于系统的影响是永久性的。

11.6事务应用

应用环境：基于增删改语句的操作结果（均返回操作后受影响的行数），可通过程序逻辑手动控制事务提交或回滚

11.6.1事务完成转账

#A账户给B账户转账。
#1 .幵启事务
START TRANSACTION; | setAutoCommit=0;#禁止自动提交 setAutoCommit=1 ;#开启自动提交
#2.事务内数据操作语句
UPDATE ACCOUNT SET MONEY = MONEY-1000 WHERE ID = 1；
UPDATE ACCOUNT SET MONEY = MONEY+1000 WHERE ID = 2；
#3.事务内语句都成功了，执行COMMIT；
COMMIT;
#4 .事务内如果出现错误，执行ROLLBACK ；
ROLLBACK；

注意：幵启事务后，执行的语句均属于当前事务，成功再执行COMIIT,失败要进行ROLLBACK

12 权限管理

12.1创建用户

CREATE USER 用户名 IDENTIFIED BY 密码

12.1.1创建一个用户

#创建一个zhangsan用户
CREATE USER 'zhangsan' IDENTIFIED BY '123* ;

12.2授权

GRANT ALL ON数据库.表TO用户名；

12.2.1用户授权

#将companyDB下的所有表的权限都赋给zhangsan 
GRANT ALL ON companyDB.* TO 'zhangsan *;

12.3撤销权限

REVOKE ALL ON数据库.表名FROM用户名

注意：撤销权限后，账户要重新连接客户端才会生效

1231撤销用户权限

#将zhangsan的companyDB的权限撤销
REVOKE ALL ON companyDB.* FROM 'zhangsan';

12.4删除用户

DROP USER用户名

12.4.1删除用户

#删除用户zhangsan
DROP USER 'zhangsan';

13 视图

13.1概念

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用 SELECT语句查询数据，也可以使用INSERT, UPDATE, DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

13.2视图特点

13 视图

13.1概念

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用 SELECT语句查询数据，也可以使用INSERT, UPDATE, DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

13.2视图特点

优点

简单化，数据所见即所得。
安全性，用户只能查询或修改他们所能见到得到的数据。
逻辑独立性，可以屏蔽真实表结构变化带来的影响。

缺点

性能相对较差，简单的查询也会变得稍显复杂。
修改不方便，特变是复杂的聚合视图基本无法修改。

13.3视图的创建

语法：CREATE VIEW 视图名 AS查询数据源表语句;

13.3.1创建视图

.3.1创建视图
#创建t_emplnfo的视图，其视图从t_employees表中查询到员工编号、员工姓名、员工邮箱、工资
CREATE VIEW t.emplnfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME.EMAIL,SALARY from t_employees;

13.3.2使用视图

#查询t^emplnfo视图中编号为的员工信息
SELECT * FROM t_emplnfo where employee_id = 1101';

13.4视图的修改

•方式一：CREATE OR REPLACE VIEW视图名AS查询语句

•方式二：ALTER VIEW视图名AS查询语句

13.4.1修改视图

 #方式1：如果视图存在则逬行修改，反之，进行创建
CREATE OR REPLACE VIEW t_emplnfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY,DEPARTMENT.ID from t_employees；

#方式2：直接对已存在的视图进行修改
ALTER VIEW t.emplnfo
AS
SELECT EMPLOYEE_ID,FIRST.NAME,LAST.NAME,EMAIL,SALARY from t_employees;

13.5视图的删除

DROP VIEW视图名

13.5.1删除视图

# 删除 t.empInfolJJ!图
DROP VIEW t_emplnfo;

注意：删除视图不会影响原表

13.6视图的注意事项

注意：

视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
如果视图包含以下结构中的一种，则视图不可更新

■ 聚合函数的结果

■ DISTINCT去重后的结果

■ GROUP BY分组后的结果

■ HAVING筛选过滤后的结果

■ UNION. UNION ALL联合后的结果

14 SQL语言分类

14.1 SQL语言分类

数据查询语言DQL (Data QueryLanguage) : select、where、order by、group by、having。

数据定义语言DDL(Data Definition Language):create、alter、drop。

数据操作语言DML (Data Manipulation Language) : insert、update、delete。

事务处理语言TPL (Transaction Process Language) : commit、rollback。

数据控制语言DCL(Data ControlLanguage) : grant、revoke。

15 综合练习

15.1 数据库表

#创建用户表
create table user(
 userId int primary key auto_increment
  username varchar(20) not null,
  password varchar(18) not null.
  address varchar(100)
  phone varchar(11)
  
#创建分类表
create table category(
 cid varchar(32) PRIMARY KEY,
 cname varchar(100) not null  #分类名称
);

#商品表
CREATE TABLE products
 'pid'varchar(32) PRIMARY KEY
 'name' VARCHAR(40)
 'price’DOUBLE(7,2),
 category_id varchar(32)，
 constraint fk_products_category-id foreign key(category-id) references category(cid)
 );
 
 #订单表
create table orders *(
 'oid' varchar(32) PRIMARY KEY ,
 'totalprice' double(12,2), #总计
 'userid' int,
constraint fk_orders_userld foreign key(userid) references user(userld) #外键
)；


#订单项表
create table orderitem(
 oid varchar(32), #订单id
 pid varchar(32), #商品id
 num int ,  #购买商品数量
 primary key(oid, pid), #主键
 constraint fk_orderitem_oid foreign key(oid) references orders(oid),
 constraint fk_orderitem_pid foreign key(pid) references products(pid)
)；

#------------------------------
#初始化数据
#用户表添加数据
#用户表添加数据
INSERT INTO USER(username,PASSWORD,address,phone) VALUES(张三，123'，北京昌平沙河'，13812345678");
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('王五'，5678 ，北京海淀'，13812345141');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES("赵六,123,北京朝阳,"13812340987');
INSERT INTO USER(username, PASSWORD,address,phone) VALUES('田七'123'北京大兴'，13812345687');

#给商品表初始化数据
insert into products(pid,name,price,category_id) values('p801',联想',5000,'c001');
insert into products(pid,name,price,category_id) values('p002insert雷神',508，c81');
insert into products(pid,name,price,category_id) values('p005',"真维斯',200,'c002');
insert into products(pid,name,price,category_id) values('p006，花花公子',440,'c002');
insert into products(pid,name,price,category_id) values('p007',"劲霸',2000,'c002');
insert into products(pid,name,price,category_id) values('p008'，香奈儿',800,'c003');
insert into products(pid,name,price,category_id) values('p009'，相宜本草',200,'c003');
insert into products(pid,name,price,category_id) values('p010',梅明子200,null);

#给分类表初始化数据
insert into category values('c001'，电器');
insert into category values('c002，服饰');
insert into category values( 'c003'，化妆品');
insert into category values('c004'，书籍');

#添加订单
insert into orders values('o6109'1800.50,1):
into orders values( o6101',7200.35,1);insert
insert into orders values('o6102',600.00,2);
insert into orders values('o6103 ,1300.26,4);



 #添加订单
 insert into orders values('o6100',18000.50,1);
 insert into orders values('o6101',7200.35,1);
 insert into orders values('o6102',600.00,2);
 insert into orders values('o6103',1300.26,4): 
 
 #订单详情表 
 insert into orderitem values('06100','p00V,1),(*o6100','p002',1),('o61011,1p003*,1);

15.2 综合练习1-【多表查询】

15.2.1查询所有用户的订单

SELECT O.oid,o.totalprice, u.userid,u.username,u.phone
FROM orders o INNER JOIN USER u ON o.userld=u.userid;

15.2.2查询用户id为1的所有订单详情

SELECT o.oid,o.totalprice, u.userid,u.username,u.phone ,oi.pid
FROM orders o INNER JOIN USER u ON o.userld=u.userid
INNER JOIN orderitem oi ON o.oid=oi.oid
where u .userid=1;

15.3 综合练习2•【子查询】

15.3.1查看用户为张三的订单

SELECT * FROM orders WHERE userId=(SELECT userid FROM USER WHERE username=,张三')；

15.4综合练习3-【分页查询】

15.4.1查询所有订单信息，每页显示5条数据

#查询第一页
SELECT * FROM orders LIMIT 0,5;

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL3

9 数据表操作

9.1数据类型

9.1.1 数值类型

9.1.2 日期类型

9.1.3 字符串类型

9.2 数据表的创建(CREATE)

9.2.1创建表

9.3 数据表的修改(ALTER)

9.3.1 向现有表中添加列

9.3.2 修改表中的列

9.3.3 删除表中的列

9.3.4 修改列名

9.3.5 修改表名

9.4 数据表的删除(DROP)

9.4.1 删除学生表

10 约束

10.1实体完整性约束

10.1.1主键约束

10.1.2睢一约束

10.1.3自动增长列

10.2 域完整性约束

10.2.1非空约束

10.2.2默认值约束

10.2.3引用完整性约束

10.3 约束创建整合

10.3.1 创建表

11 事务【重点】

11.1模拟转账

11.1.1数据库模拟转账

11.1.2模拟转账错误

11.2事务的概念

11.3事务的边界

12 权限管理

12.1创建用户

12.1.1创建一个用户

12.2授权

12.2.1用户授权

12.3撤销权限

1231撤销用户权限

12.4删除用户

12.4.1删除用户

13 视图

13.1概念

13.2视图特点

13.3视图的创建

13.3.1创建视图

13.3.2使用视图

13.4视图的修改

13.4.1修改视图

13.5视图的删除

13.5.1删除视图

13.6视图的注意事项

14 SQL语言分类

14.1 SQL语言分类

15 综合练习

15.1 数据库表

15.2 综合练习1-【多表查询】

15.2.1查询所有用户的订单

15.2.2查询用户id为1的所有订单详情

15.3 综合练习2•【子查询】

15.3.1查看用户为张三的订单

15.4综合练习3-【分页查询】

15.4.1查询所有订单信息，每页显示5条数据

热门文章

最新文章