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

 

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;