本篇内容重点
- 修改表结构
- 删除表
- 表的约束
- 表达式及运算符
- 增加数据
- 删除数据
- 修改数据
- 查询数据
具体内容
数据定义语言(DDL)(data definition language)
数据库中的元数据来自于数据定义语言,例如表结构的定义,视图的定义。
alter create drop
修改表结构
添加列
desc是查看表结构
需求:添加一列性别sex tinyint
alter table tbl_student add column sex tinyint(1);
修改列
alter table tbl_student modify sex tinyint(2);
删除列
alter table tbl_student drop sex;
删除表
drop table tbl_student;
(慎用,无法恢复)
drop database
数据库名 删库跑路
表的约束
约束用于限制加入表的数据的类型。可以在创建表时规定约束(通过 CREATE TABLE 语句),或者在表创建之后也可以(通过 ALTER TABLE 语句)。
constraints
非空约束
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新纪录或者更新记录。
创建时添加非空约束
create table tbl_class (id int not null );
创建后时添加非空约束
alter table tbl_student modify age int(3) not null;
下面的错误就是非空字段,没有赋值
唯一约束(unique)
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
创建时添加唯一约束
create table tbl_student (id int not null,name varchar(20) not null ,age int(3) not null,unique(name))
如果需要命名 UNIQUE 约束,以及为多个列定义 UNIQUE 约束,请使用下面的 SQL 语法:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)
创建后添加唯一约束
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD UNIQUE (P_Id)
如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
alter table tbl_student add constraint uq_name unique(name);
上面加粗的部分是可变的,tbl_student表名,uq_name是约束名,name是列名。
删除唯一约束
alter table tbl_student drop index name;
删除之后,可以添加重复数据。
主键约束(primary key)
创建时添加主键约束
CREATE TABLE tbl_student (
id INT NOT NULL PRIMARY KEY,
NAME VARCHAR ( 20 ) NOT NULL,
age INT ( 3 ) NOT NULL,
UNIQUE ( NAME )
);
创建后添加主键约束
alter table tbl_student add constraint pk_id primary key (id);
主键自增(auto_increment)
主键约束自带唯一约束,不能插入跟已经存在的id重复的数据,并且自己插入的id数值比自增大的时候,主键自增以最大的为基准自增。
外键约束
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY或者唯一约束的列。
CREATE TABLE tbl_student (
id INT NOT NULL PRIMARY KEY auto_increment,
NAME VARCHAR ( 20 ) NOT NULL,
age INT ( 3 ) NOT NULL,
class_id INT ( 11 ) NOT NULL,
UNIQUE ( NAME ),
/*fk_class_id外键名称,class_id子表中的列名,tbl_class 父表名,id是父表中的主键,*/
CONSTRAINT fk_class_id FOREIGN KEY ( class_id ) REFERENCES tbl_class ( id )
);
SQL FOREIGN KEY Constraint on CREATE TABLE
下面的 SQL 在 “Orders” 表创建时为 “Id_P” 列创建 FOREIGN KEY:
MySQL:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
如果需要命名 FOREIGN KEY 约束,以及为多个列定义 FOREIGN KEY 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)
SQL FOREIGN KEY Constraint on ALTER TABLE
如果在 “Orders” 表已存在的情况下为 “Id_P” 列创建 FOREIGN KEY 约束,请使用下面的 SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
如果需要命名 FOREIGN KEY 约束,以及为多个列定义 FOREIGN KEY 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
撤销 FOREIGN KEY 约束
如需撤销 FOREIGN KEY 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
补充内容:
创建外键的时候,有级联关系
CASCADE(强制删除)
在父表上update/delete记录时,同步update/delete掉子表的匹配记录
SET NULL
在父表上update/delete记录时,将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)
NO ACTION
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
RESTRICT(默认)
同no action, 都是立即检查外键约束
check约束
CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
如果对一个表定义 CHECK 约束,那么此约束会在特定的列中对值进行限制。
注意:
mysql所有的存储引擎均不支持check约束,MySQL会对check子句进行分析,但是在插入数据时会忽略,因此check并不起作用,因此实现对数据约束有两种方法:
1.在mysql种约束,如使用enum类型或者触发器等。
2.在应用程序里面对数据进行检查再插入。
default约束
默认值
创建表的时候
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
创建表之后
alter table tbl_student alter name set default ‘xxx’;
表达式及运算符
表达式是指使用运算符将同类型的数据(如常量、变量、函数等)按一定的规则连接起来的、具有特定意义的语句。
MySQL中表达式包括
条件表达式
逻辑表达式
这两种表达式结果只能为TRUE或FALSE
比较运算符
赋值和比较都用 =
不等于:!= <>
与:and
或者:or
非:not
特殊的判空:
age is null
age is not null
赋值:
age=null
逻辑运算符
not 运算符:非 !
and 运算符:并且 &&
or 运算符:或者 ||
is null
is not null
数据操纵语言DML(Data Manipulation Language)
增加数据
#写列名的时候,可以自定义值的个数,个数跟列名个数一致就行
insert into emp (empno, ename, job, mgr, hiredate, sal, depton) VALUES (8888,‘张三’,‘CLERK’,7935,now(),3000,30);
#如果不写列名,必须写所有的列对应的值
insert into emp values (9999,‘张三’,‘CLERK’,7935,now(),3000,2000,30);
删除数据
#删除数据
delete from emp where empno=9999;
select * from tbl_student;
#效率低,可以恢复
delete from tbl_student;
#效率高,无法恢复
truncate table tbl_student;
修改数据
update table set name=‘’
查询数据
条件
where
select * from emp where (depton=10 and job='MANAGER') or (depton=20 and job='CLERK');
多个条件是并且的关系 使用and 相当于java中的&&
多个条件是或者的关系,使用or 相当于java中的||
排序
order by
#按照工资从小到大的顺序
select * from emp order by sal asc;
#按照工资从大到小的顺序
select * from emp order by sal desc;
去重复
distinct
#查询出公司所有的工作岗位
select job from emp ;
select distinct job from emp ;
显示部分行
limit
#查询前5名员工
select * from emp limit 5;
#查询从第三个之后的3个员工,第一个3是开始位置,不包含开始位置,后面的3是显示的个数
select * from emp limit 3,3;