1 数据库操作
查看现有数据库,语法格式如下:
show datadases
创建数据库,语法格式如下:
create databases 库名
删除数据库,语法格式如下:
drop database 库名
切换/选择数据数据库格式如下:
use 数据库名
系统自带库不可以删除!
2 数据类型
2.1 数值类型
常用的数据类型
整数类型 int
小数类型 decimal
小数格式工decimal(总长度,小数位)
decimal(5,2) 总长度是5位,小数位是2位 例如:123.45
2.2 日期和时间类型
常用的日期时间类型
datetime YYY-MM-DD HH:MM:SS
date YYY-MM-DD
2.3 字符串类型
常用的字符串类型
char(字符的长度) 固定长度
varchar(字符串的长度) 可变长度
例如:
字段姓名,数据类型为char(10),填写内容为bass,剩余的6个字符空间不会释放,系统用空格填充满。
字段姓名,数据类型为varchar(10),填写内容为bass,剩余的6个字符空间会释放。
2 表操作
2.1 创建表
语法格式如下:
create table 表名(属性1 数据类型 [约束条件],属性2 数据类型 [约束条件],属性3 数据类型 [约束条件])ENGINE=INNODB DEFAULT CHARSET=utf8[]是可选内容引擎设置为INNODB,字符集设置为utf8
单行注释:-- 注释的内容
多行注释:/多行注释的内容/
切换数据库,语法格式如下:
use 数据库名
例题:
创建表
员工信息表(员工编号,姓名,工资,入职时间)
CREATE TABLE employeeinfo(eno VARCHAR(10),ename VARCHAR(10),wages DECIMAL(8,2),entrytime DATETIME)ENGINE=INNODB DEFAULT CHARSET=utf8
2.2 删除表
语法格式如下:
drop table 表名drop table 表名1,表名2,……
2.3 修改表
添加列(属性,字段),语法格式如下:
alter table 表名 add 属性名 数据类型
删除列(属性,字段),语法格式如下:
alter table 表名 drop 属性名
修改列(属性,字段)的数据类型,语法格式如下:
alter table 表名 modify 属性名 数据类型
修改列(属性,字段)名,语法格式如下:
alter table 表名 change 旧属性名 新属性名 数据类型
显示表结构,语法格式如下:
desc 表名
3 实体完整性约束
数据完整性是指存储在数据库中的数据应该保持准确性和可靠性。
实体完整性是在表中至少有一个唯一的标识,主属性不能为空,不能重复。
3.1 主键约束
关键词:primary key
特点:唯一 不重复 不能为空一个表中有且只有一个主键约束
创建表时添加主键
格式一,如下:
craete table 表名(列名1 数据类型 primary key,列名2 数据类型,……)
格式二,如下:
craete table 表名(列名1 数据类型,列名2 数据类型,……,constraint PK_列名 primary key(列名))
格式三,如下:
craete table 表名(列名1 数据类型,列名2 数据类型,……,primary key(列名))
创表之后添加主键
格式一,如下:
alter table 表名 modify 列名 数据类型 primary key
格式二,如下:
alter table 表名 add primary key(列名)
格式三,如下:
alter table 表名 add constraint 主键约束的名字 primary key(列名)
主键约束的名字格式:PK_列名
删除主键的格式,如下:
alter table 表名 drop primary key
例题:
创建student表,列名有学号(sno)和姓名(sname),其中学号为主键,在创建表时添加主键,如下:
-- 格式一CREATE TABLE student(sno VARCHAR(10) PRIMARY KEY,sname VARCHAR(10))ENGINE=INNODB DEFAULT CHARSET=utf8-- 格式二CREATE TABLE student(sno VARCHAR(10),sname VARCHAR(10),CONSTRAINT PK_sno PRIMARY KEY(sno))ENGINE=INNODB DEFAULT CHARSET=utf8-- 格式三CREATE TABLE student(sno VARCHAR(10),sname VARCHAR(10),PRIMARY KEY(sno))ENGINE=INNODB DEFAULT CHARSET=utf8
创建student表,列名有学号(sno)和姓名(sname),其中学号为主键,在创建表后添加主键,如下:
-- 格式一ALTER TABLE student MODIFY sno VARCHAR(10) PRIMARY KEY-- 格式二ALTER TABLE student ADD PRIMARY KEY(sno)-- 格式三ALTER TABLE student ADD CONSTRAINT PK_sno PRIMARY KEY(sno)
删除student表的主键,如下:
ALTER TABLE student DROP PRIMARY KEY
联合主键指的是把两个列看成是一个整体,这个整体是不为空,唯一,不重复。
创建表时添加主键
格式一,如下:
craete table 表名(列名1 数据类型,列名2 数据类型,……,constraint PK_列名 primary key(列名1,列名2))
格式二,如下:
craete table 表名(列名1 数据类型,列名2 数据类型,……,primary key(列名1,列名2))
创建表后添加主键
格式一,如下:
alter table 表名 add primary key(列名1,列名2)
格式二,如下:
alter table 表名 add constraint 主键约束的名字 primary key(列名1,列名2)
例题:
创建员工表(employeeinfo),有员工编号(eno),姓名(ename),部门编号(departno)属性,其中员工编号和部门编号做为联合主键,在创建表时建立主键,语句如下:
-- 格式一CREATE TABLE employeeinfo(eno VARCHAR(10),ename VARCHAR(10),departtno VARCHAR(10),CONSTRAINT PK_sno PRIMARY KEY(eno,departtno))ENGINE=INNODB DEFAULT CHARSET=utf8-- 格式二CREATE TABLE employeeinfo(eno VARCHAR(10),ename VARCHAR(10),departtno VARCHAR(10),PRIMARY KEY(eno,departtno))ENGINE=INNODB DEFAULT CHARSET=utf8
创建员工表(employeeinfo),有员工编号(eno),姓名(ename),部门编号(departno)属性,其中员工编号和部门编号做为联合主键,在创建表后建立主键,语句如下:
-- 格式一ALTER TABLE employeeinfo ADD PRIMARY KEY(eno,departno) -- 格式二ALTER TABLE employeeinfo ADD CONSTRAINT PK_eno_departno PRIMARY KEY(eno,departno)
3.2 唯一约束
关键词:unique
特点:不允许有重复的值,保证数据的唯一性;可以有空值;一张表可以有多个唯一约束;唯一约束的名字和列名保持一致;添加唯一约束的列,默认添加该列的索引。
索引等同于书本的目录,加快数据的查询速度。
创建表时创建唯一约束
格式一,如下:
craete table 表名(列名1 数据类型,列名2 数据类型,……,constraint UN_列名1 UN(列名1),constraint UN_列名2 UN(列名2))
格式二,如下:
craete table 表名(列名1 数据类型 unique,列名2 数据类型 unique,……)
例题
创建会员表,有会员编号、会员姓名和手机号,其中手机号是唯一约束,创建语句如下:
CREATE TABLE member(memberno VARCHAR(15),membername VARCHAR(15),memberphone VARCHAR(15),CONSTRAINT UN_menberphone UNIQUE(memberphone))ENGINE=INNODB DEFAULT CHARSET=utf8
查看唯一约束,可查看member表的索引,因为添加唯一约束的列,系统默认添加的索引,查看索引语句如下:
SHOW INDEX FROM member
创建表后创建唯一约束的格式,如下:
alter table 表名 add unique(列名1,列名2,……)
删除约束的格式,如下:
alter table 表名 drop index 索引名
题目
创建登记表(register),有登记号(registerno)、登记人(registerpeople)和登记时间(registertime)属性,在创建表之后,添加登记号的唯一约束,随后删除该唯一约束,语句如下:
-- 创建登记表CREATE TABLE register(registerno VARCHAR(15),registerpeople VARCHAR(15),registertime DATETIME)ENGINE=INNODB DEFAULT CHARSET=utf8-- 为register添加唯一约束ALTER TABLE register ADD INDEX(registerno)-- 查看register表的唯一约束,若没有指定唯一约束名,-- 则默认为该唯一约束名就是属性名SHOW INDEX FROM register-- 删除唯一约束ALTER TABLE register DROP INDEX registerno
4 域完整性约束
保证在表中不会输入无效的数据
4.1 默认约束
关键词:default
作用:当默认约束来修饰某个列的时候,修饰的列即使不写数据也会默认一个值的。
创建表时创建默认约束,格式如下:
create table 表名(列名1 数据类型 default ‘字符串或者日期’,列名2 数据类型 数值)
创建表后创建默认约束,格式如下:
alter table 表名 modify 属性名 数据类型 default ‘字符串’/数值
删除默认约束格式如下:
alter table 表名 modify 属性名 数据类型
题目
创建成绩表,有学号、性别和成绩字段,其中性别的默认值为男,成绩默认值为60在创建表之后定义,随后删除该默认值,语句如下:
-- 创建grade表CREATE TABLE grade(sno VARCHAR(15),ssex VARCHAR(2) DEFAULT '男',grade INT )ENGINE=INNODB DEFAULT CHARSET=utf8-- 添加成绩的默认值为60ALTER TABLE grade MODIFY grade INT DEFAULT 60-- 删除成绩的默认值ALTER TABLE grade MODIFY grade INT
4.2 非空约束
关键词:not null
当前列必须有值
创建表时创建非空约束,格式如下:
create table 表名(列名1 数据类型 not null,列名2 数据类型)
创建表后创建非空约束,格式如下:
alter table 表名 modify 属性名 数据类型 not null
删除非空约束,格式如下:
alter table 表名 modify 属性名 数据类型
题目
创建系别表(dept),有学号(sno)、姓名(sname)、性别(ssex)、系别号(deptno)属性,其中学号为主键,姓名不能为空,性别默认为男,在创建该表之后将系别号添加非空约束,随后删除该约束,语句如下:
-- 创建系别表CREATE TABLE dept(sno VARCHAR(15) PRIMARY KEY,sname VARCHAR(15) NOT NULL,ssex VARCHAR(2) DEFAULT '男',deptno VARCHAR(5))ENGINE=INNODB DEFAULT CHARSET=utf8-- 添加系别号的非空约束ALTER TABLE dept MODIFY deptno VARCHAR(5) NOT NULL-- 删除系别号的非空约束ALTER TABLE dept MODIFY deptno VARCHAR(5)
5 参照完整性约束
外键约束
foreign key
创建表时创建外键约束,格式如下:
-- 主表create table 表1(列名1 数据类型 primary key,列名2 数据类型)-- 从表(外键约束所在的表)create table 表2(列名1 数据类型,列名2 数据类型,constraint 外键约束的名字 foreign key(从表的列名1)references 主表的表名(列名1))
注意:
外键约束的名字格式:KF_列名1
被引用的列的要求:从表和主表的列名可以不一样,但这两个列的数据类型和内容必须一样。
主表有的数据,从表可以做操作。主表中没有的数据,从表不能做操作。
若想删除主表数据,必须先删除从表的数据,再来删主表。
从表参照主表的某个列,要求主表的列必须有主键约束或者唯一约束。
题目:
新建student表,有学号(sno)和姓名(sname)字段,学号(sno)为主键。
新建garde表,有学号(sno)和成绩(grade)字段,该表的学号参考student表的学号。
-- 新建student表,为主表CREATE TABLE student(sno VARCHAR(10) PRIMARY KEY,sname VARCHAR(10))ENGINE=INNODB DEFAULT CHARSET=utf8-- 建grade表,sno为外键,参考student表的sno,garde表为从表CREATE TABLE grade(sno VARCHAR(15),grade VARCHAR(3),CONSTRAINT FK_sno FOREIGN KEY(sno) REFERENCES student(sno))ENGINE=INNODB DEFAULT CHARSET=utf8-- 为student表插入数据INSERT INTO studentVALUES('101','张一'),('102','张二'),('103','张三')-- 从表grade插入数据,必须参考主表student的sno,-- 若插入主表不存在的数据,则会插入失败INSERT INTO gradeVALUES('104','张四')-- 插入主表存在的数据INSERT INTO gradeVALUES('101','张一'),('102','李四')SELECT * FROM studentSELECT * FROM grade-- 若想删除主表中一行数据,必须在从表删除该行数据,主表才能成功删除该行数据DELETE FROM grade WHERE sno='101'DELETE FROM student WHERE sno='101'
创建表后创建外键约束,格式如下:
alter table 表名 add constraint 外键名 foreign key(属性1) references 主表(属性1)-- 注:外键是建立在从表上的;从表和主表的属性1内容和数据类型必须一致,属性名可以不一样。
删除外键约束格式如下:
alter table 表名 dorp foregin key 外键名
题目:
删除garde表的外键,随后添加回来。
-- 删除garde表的外键ALTER TABLE grade DROP FOREIGN KEY FK_sno-- 为grade表添加外键,参考student表的sno字段ALTER TABLE grade ADD CONSTRAINT FK_sno FOREIGN KEY(sno) REFERENCES student(sno)
小结
数据库(database)操作有查看数据库(show)、创建数据库(create)、删除数据库(drop)和选择数据库(use)。
数据类型有数值类型、日期和时间类型和字符串类型。整数用int,小数用decimal,字符串用varchar。
表操作有创建表、删除表和修改表。
实体完整性约束有主键约束(primary key)和唯一约束(unique)。
域完整性约束有默认约束和非空约束。
参照完整性约束主要是外键约束(foreign key)。
软件测试 数据库 MySQL01 数据库操作 表操作 实体完整性约束 域完整性约束 参照完整性约束 外键约束