MariaDB Structured Query Language（Domain types、View、DDL、DML）

最新推荐文章于 2022-03-03 12:55:34 发布

ROBIN.FANG

最新推荐文章于 2022-03-03 12:55:34 发布

阅读量202

点赞数

本文介绍了SQL中的数据类型，如字符类型、数值类型等，并详细解释了如何使用DDL语句创建表格，定义主键和外键。此外还涉及了视图的概念及DML语句的应用，包括插入、更新、删除和查询数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Domain types in SQL

char(n), varchar(n)
int, smallint
numberic(p,d) : Fixed point number,with user-specified precision of p digits,with n digits to the right of decimal point.
real, double precision : Floating point and double-precision floating point numbers, with machine-dependent precision.
float(n) : Floating point number,with user-specified precision of at least n digits
date : '2005-7-27'
time : '09:00:30'
timestamp : '2005-7-28 09:00:30.75'
interval : period time

DDL

create table instructor(ID        char(5),
                        name      varchar(20) not null,
                        dept_name varchar(20),
                        salary    numeric(8,2),
                        primary key(ID),
                        foreign key(dept_name) references department);

View

A viewprovides a mechanism to hide certain data from the view of certain users.

Any relation that is not of the conceptual 概念上的 model but is made visible to a user as a “virtual relation” is called a view.

Alter Table

alter table classroom add 601 int;
alter table classroom drop 302;

DML

insert into instructor(ID,name,dept_name,salary) 
values('22222','Robin','Physics',95000);

update instructor set salary=10000,dept_name="music" where ID='2222';

delete from instructor where ID='2222';

select A1,A2...An from r1,r2...rm where ..

select name from instructor 
where dept_name='Comp.Sci.' and salary>8000;

select name,course_id from instructor,teaches 
where instructor.ID=teaches.ID;

select name,course_id from instructor,teaches
where instructor.ID=teaches.ID and year=2010;