通用SQL基础

通用SQL基础

DML数据操作语言:insert delete update
DDL数据定义语言:create alter drop truncate
DCL数据控制语言:grant revoke --通常无需程序员 操作
事物控制语句:commit rollback savepoint

1. 五类完整性约束

NOT NULL:非空约束,指定某列不能为空。
UNIQUE:唯一约束，指定某列或者几列组合不能重复。
PRIMARY KEY:主键约束,指定该列的值可以唯一标识该条记录。
FOREIGN KEY:外键约束，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性。
CHECK:检查约束，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。(MYSQL对check约束不起任何作用)

2. 五种约束的建立和删除

2.1 非空约束

1.建表时指定非空约束:
create table user(id int not null, name varchar(255) not null, age int null);
2.建表后添加或者删除非空约束
–增加非空约束
alter table user modify age int not null;
–删除非空约束
alter table user modify age int null;
–取消非空并指定默认值
alter table user modify age int default 15 null;

2.2 unique约束

唯一性约束用于保证指定列或指定的列组合不允许出现重复值。同一张表内可建多个唯一约束，唯一约束可由多列组合而成。当为某列创建唯一约束时，mysql会为该列相应地创建唯一索引，如果不给唯一约束起名字，该唯一约束默认与列名相同。

–使用列级约束语法建立唯一约束–
create table User (
id int not null,
name varchar(255) unique
);
–为多列组合建立唯一约束----使用表级约束语法–
语法格式: constraint 约束名 约束定义;
create table User(
id int not null,
name varchar(255),
pass varchar(255),
unique(name),–使用列级约束
constraint User_uk unique(pass) --使用表级约束,此时可以起约束名
);
add关键字增加唯一约束
alter table User add unique(
name,pass
);

modify关键字增加唯一约束
alter table User modify name varchar(255) unique;

删除唯一约束:
alter table User drop constraint 约束名;

drop删除唯一约束(适用于mysql)
alter table User drop index User_uk; – User_uk为约束名

2.3 PRIMARY KEY 约束

主键约束相当于非空约束和唯一约束。
–使用列级语法创建主键约束
create table User (
id int primary key,
name varchar(255)
);

–使用表级语法创建主键约束
create table User (
id int not null,
name varchar(255),
pass varchar(255),
–指定主键约束名为User_pk，对大部分数据库有效，但对mysql无效。
–mysql中该主键约束名依然是primary
constraint User_pk primary key(id)
);

–多列建立组合主键
create table User (
name varchar(255),
pass varchar(255),
primary key(name,pass)
);
–删除指定表的主键约束
alter table User drop primary key;–alter table语句后使用drop primary key子句。
–add为指定表增加主键
alter table User add primary key(name ,pass);

–modify为单独的数据列增加主键
alter table User modify name varchar(255) primary key;

–mysql设置主键自动增长
create table User (
id int auto_increment primary key,
name varchar(255),
pass varchar(255)
–一旦指定了某列具有自动增长特性，插入记录时不能为该
–列指定值。
);

2.4 FOREIGN KEY约束

–外键约束主要用于保证一个或者两个数据表之间的参照完整性。
外键是构建于一个表的两个字段或者两个表的两个字段之间的
参照关系。

–从表外键参照的是主表主键或者唯一键列，这样保证从表记录
可以准确定位到被参照的主表记录。同一个表内可以拥有多个外键。
–对于一对多的关联关系，通常在多的一端来增加外键列。对于一对一
的关联关系，则可选择任意一方来增加外键列。
–只要将外键列增加唯一约束就可表示一对一关联了。
–对于多对多关联，则需要额外增加一个连接表来记录他们的关联关系。
==关键字references,references指定该列参照的那个主表，以及参照主表的哪一列。
eg:
–先建立主表
create table teacher(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);

–从表
create table student (
student_id int auto_increment primary key,
student_name varchar(255),
teacher_id int references teacher(teacher_id)
);
–虽然mysql支持列级约束的语法来建立外键约束，但这种列级约束语法
建立的外键约束不会生效。此时应该用表级外键约束语法
–先建立主表
create table teacher(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);
–从表–用表级语法建立
creata table student(
student_id int auto_increment primary key,
student_name varchar(255),
teacher_id int,
–不指定外键约束名时的写法(两者二选一)
foreign key(teacher_id) references teacher(teacher_id)
–指定外键约束名时的写法
constraint student_teacker_fk foreign key(teacher_id) references teacher(teacher_id)
);

–表级语法建立多列组合的外键约束
–先建立主表
create table teacher(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);

–从表–用表级语法建立
creata table student(
student_id int auto_increment primary key,
student_name varchar(255),
teacher_id int,
–不指定外键约束名时的写法(两者二选一)
foreign key(teacher_id,teacher_pass) references teacher(teacher_id,teacher_pass)
);
–删除外键约束
alter table student drop foreign key student_teacher_fk; --student_teacher_fk为约束名
–add增加外键约束
alter table student add foreign key(
teacher_name,teacher_pass
) references teacher(teacher_name,teacher_pass);
–自关联:例如使用一个表保存某个公司所有员工记录，而部门经理
和普通员工之间是1:N的关系，但是他们保存在同一张表中，这就是典型的
自关联。
create table dept(
id int auto_increment primary key,
name varchar(255),
refer_id int,
foreign key(id) references dept(id);
);
–删除主表记录时，从表记录也删除
–先建立主表
create table teacher(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);

–从表–用表级语法建立–需要在建立外键约束后添加on delete cascade 或
添加 on delete set null
creata table student(
student_id int auto_increment primary key,
student_name varchar(255),
teacher_id int,
–使用表级约束语法建立外键删除，第一级联删除
foreign key(teacher_id) references teacher(teacher_id) on delete cascade;

);

2.5 CHECK约束

–建表时增加check约束–需要在建表的列定义后增加check(逻辑表达式)
create table teacher(
teacher_id int auto_increment,
teacher_name varchar(255),
teacher_salary decimal,
primary key(teacher_id),
–建立check约束
check(teacher_salary >0)
);

常见数据库对象

表

数据字典:就是系统表，存放数据库相关信息的表。系统表里面的数据通常由数据库系统维护。程序员通常不用手动修改系统表及内部数据，只可查看系统表数据。

约束:constraint 是在表上强制执行数据校验的规则，用于保证了数据完整性的规则。

视图:view 一个或者多个数据表里数据的逻辑显示。视图并不存储数据。

索引:index 用于提高查询的性能 相当于书籍目录。

函数:function 用于完成一次特定的计算，具有一个返回值。

存储过程:procedure 用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境。

触发器: trigger 相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成响应的处理。

基础建表语句

create table [模式名.]表名(
//定义多个列
columnName datatype,
);
eg:
create table user(id int,username varchar(50), userpwd varchar(50),userinfo varchar(255));

子查询建表

使用子查询建表的同时可以插入数据。子查询建表:

create table [模式名.]表名 as subquery;

eg: //所建立的表User与table_userinfo表结构和表内容一样。
create table User as select * from table_userinfo; //此处的subquery为子查询语句:select * from table_userinfo

修改表结构

1. 添加列:alter … add

alter table 表名 add (
//多个列
columnName datatype,
);

eg:
alter table User add(
userinfo varchar(100) default ‘xxx’,
usertype int
); //增加列userinfo和usertype

2. 修改列属性:modify

只修改字段类型而不能修改字段名本身,修改字段本身需要用change–>change适用于mysql(见下文)
alter table 表名 modify columnName datatype;
eg:alter table User modify uage varchar(255); //将uage的类型修改为varchar(255)

3. 删除现有的列

删除现有的列:drop
alter table 表名 drop columnName;
eg:alter table User drop uname; //删除列uname

4. 删除表

drop table 表名;
eg:drop table User; //删除User表 --表结构被删除、表数据被删除、该表相关索引和约束一并删除。

5. 一次性删除表中所有的记录

truncate --truncate 比delete速度要快
truncate 表名;
eg: truncate User; //删除User所有记录，User表结构还在。

以上适用于所有数据库。

mysql 数据库

1.重新命名数据表

alter table 表名 rename (to) 新表名; //to可以省略

eg:
alter table User rename User1; //将表User重命名为User1;或者alter table User rename to User3;

2.修改表字段并修改该字段属性

alter table 表名 change old_columnName new_columnName datatype;

eg:alter table User3 change uage uage1 varchar(255);//将列uage重命名为uage1 字段类型设置为varchar(255)