mysql数据库管理_MySQL数据库的管理

一、数据库的创建和删除

· 安装MySQL后，在“开始” 菜单中依次选择“所有程序” →“MySQL”→“MySQL Server 5.7” →“MySQL 5.7 Command Line Client”，可以打开MySQL命令行工具。

· 首先要求用户输入管理员用户root的密码，输入完成后，按下回车键。

· MySQL>是MySQL命令行工具的提示符，可以在它的后面输入MySQL命令。

MySQL控制台

use+“数据库名” 控制接下来的SQL语句作用的数据库。

执行USE语句后，在执行其他SQL语句默认都作用于指定的数据库。

数据库选择

1.创建数据库

要创建数据库MySQLDB，可以使用下面的语句。

CREATE DATABASE IF NOT EXISTS MySQLDB;

创建数据库

如果使用IF NOT EXISTS关键字，则当指定的数据库名存在时，不创建数据库。

如果不使用IF NOT EXISTS关键字，当创建的数据库名存在时，将产生错误。

2.删除数据库

要删除数据库MySQLDB，可以使用下面的语句：

DROP DATABASE 数据库名；

删除数据库

二、建立表格

1.表格的设计

表格(table)是数据库中最基本的逻辑单元

主键(primary key)该列的数据具有唯一性，作为每一行数据的“特征”

约束(constraint)定义在表上的一种强制规则

约束类型：

1.NOT NULL:非空约束，不能为空值

2.UNIQUE:唯一性约束，指定列所有数据唯一

3.PRIMARY KEY:主键约束，指定列所有行数据必须唯一

4.FOREIGN KEY:外键约束，建立在列和引用列的一种强制依赖关系

5.CHECK:检查性约束，必须满足某种条件

MySQL的数据类型

MySQL的数据类型

数值类型

日期和时间类型

字符串类型

2.创建表格的语句

CREATE TABLE语句创建表，语法结构如下：

create table 表名(

字段名1 数据类型 [约束条件],

…

[其他约束条件],

[其他约束条件]

)其他选项(例如存储引擎、字符集等选项)

• PRIMARY KEY，指定字段为主键。

• AUTO_INCREMENT，指定字段为自动增加字段。

• INDEX，为字段创建索引。

• NOT NULL，字段值不允许为空。

• NULL，字段值可以为空。

• COMMENT，设置字段的注释信息。

• DEFAULT，设置字段的默认值。

例

使用CREATE TABLE语句创建表DEPT、EMP的代码如下：

CREATE TABLE DEPT

(

DEPTNO INT(2) AUTO_INCREMENT PRIMARY KEY,

DNAME VARCHAR(20),

LOC VARCHAR(30)

);

CREATE TABLE EMP

(

EMPNO int(4) AUTO_INCREMENT PRIMARY KEY,

ENAME VARCHAR(10),

JOB VARCHAR(10),

MGR INT(50),

HIREDATE DATE,

SAL DOUBLE(7,2),

COMM DOUBLE(7,2),

DEPTNO INT(2)

);

表“dept”

表“emp”

练习1

1.学校想做一个选课系统，其中涉及到课程表，学生表，请分别创建这两个表，自己思考表中应有的列及数据类型。

2.学校有一个选课系统，其中包括如下关系模式：

系(

系编号： 主键，

系名称: 唯一键，

系主任： 非空约束，

系所在校区：取值范围只能在南湖校区和浑南校区)

班级(

班级编号： 主键，

班级名称： 唯一键，

所属系： 外键)

3.创建学生表，包含如下属性:

– 学号 定长字符型 10位 主键

– 姓名 变长字符型 20位 非空

– 性别 定长字符型 2位 取值范围只能为男或女

– 出生日期 日期型

– 所在班级

CREATE TABLE xi(

XINumber VARCHAR(20) NOT NULL PRIMARY KEY,

XIName VARCHAR(20) NOT NULL,

XIZhuren VARCHAR(20) NOT NULL UNIQUE,

Xiaoqu enum('南湖校区','浑南校区')

);

CREATE TABLE class(

classid VARCHAR(20) NOT NULL PRIMARY KEY,

cname VARCHAR(20) NOT NULL,

xino VARCHAR(10) NOT NULL,

FOREIGN KEY(xino) REFERENCES xi(xino)

);

CREATE TABLE student(

xh CHAR(10) NOT NULL PRIMARY KEY,

xm VARCHAR(20) NOT NULL,

sex enum('男','女'),

birthday DATE NOT NULL,

sal VARCHAR(10) NOT NULL,

studentcid VARCHAR(2) NOT NULL,

FOREIGN KEY(studentcid) REFERENCES banji(classid)

);

3.复制表格

复制一个表结构的实现方法有两种。

• 方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下：

create table 新表名 like 源表

• 方法二、在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中：

create table 新表名 select * from 源表

练习2

1.通过子查询的方式创建一个表dept10,该表保存10号部门的员工数据。

CREATE TABLE dept10

AS SELECT * FROM emp WHERE deptno=10;

4.删除表格

使用DROP TABLE语句删除表，语法如下：

DROP TABLE 表名;

5.修改表格

可以使用ALTER TABLE语句修改表的结构，包括添加列、修改列属性和删除列等操作。

• ALTER TABLE 表名 ADD 列名 数据类型和长度 列属性

• ALTER TABLE 表名 MODIFY 列名 新数据类型和长度 新列属性

• ALTER TABLE 表名 DROP COLUMN 列名

练习3

1.在员工表中添加一个性别列，列名为gender，类型为char(2)，默认值为“男”

2.修改员工表中性别列的数据类型为char(4)

3.修改员工表中性别列的默认值为“女”

4.删除员工表中的性别列

ALTER TABLE emp

ADD gender CHAR(2) default '男';

ALTER TABLE emp

MODIFY gender CHAR(4);

ALTER TABLE emp

MODIFY gender CHAR(4) default '女';

ALTER TABLE emp

DROP (gender);

6.修改约束条件

添加约束条件

向表的某个字段添加约束条件的语法格式如下(其中约束类型可以是唯一性约束、主键约束及外键约束)。

alter table 表名 add constraint 约束名 约束类型 (字段名)

alter table person add constraint name_unique unique (name);

删除约束条件

删除表的主键约束条件语法格式比较简单，语法格式如下。

alter table 表名 drop primary key

删除表的外键约束时，需指定外键约束名称，语法格式如下(注意需指定外键约束名)。

alter table 表名 drop foreign key 约束名

删除表字段的唯一性约束，实际上只需删除该字段的唯一性索引即可，语法格式如下(注意需指定唯一性索引的索引名)。

alter table 表名 drop index 索引名;

例如：

alter table person drop index name_unique;

7.修改表中其他项

alter table 表名 engine=新的存储引擎类型

alter table 表名 default charset=新的字符集

alter table 表名 auto_increment=新的初始值

alter table 表名 pack_keys=新的压缩类型

例如：

alter table person engine=MyISAM;

alter table person default charset=gb2312;

alter table person auto_increment=8;

alter table person pack_keys=1;

三、课后作业

1.创建表date_test,包含列d，类型为date型。试向date_test表中插入两条记录，一条当前系统日期记录，一条记录为“1998-08-18”。

2.创建与dept表相同表结构的表dtest，将dept表中部门编号在40之前的信息插入该表。

3.创建与emp表结构相同的表empl，并将其部门编号为前30号的员工信息复制到empl表。

4.试为学生表student增加一列学生性别gender 默认值 “女”。

5.试修改学生姓名列数据类型为定长字符型10位。

6.简述5种约束的含义。

7.创建学生关系sc，包括属性名：

– 选课流水号 数值型 主键；

– 学生编号 非空 外键；

– 课程编号 非空 外键；

– 成绩 0-100之间；

8.创建copy_emp，要求格式同emp表完全一样，不包含数据。

9.创建copy_dept，要求格式同dept表完全一样，不包含数据。

10.设置copy_emp 表中外键deptno，参照copy_dept中deptno,语句能

否成功,为什么？

11.追加copy_dept表中主键deptno。

CREATE TABLE date_test(

d date);

INSERT date_test

VALUE (NOW()),

('1998-08-18');

CREATE TABLE dtest AS SELECT * FROM dept WHERE DEPTNO < 40;

CREATE TABLE empl AS SELECT * FROM emp WHERE DEPTNO <= 30;

ALTER TABLE student ADD gender enum('男','女') default '女';

ALTER TABLE student MODIFY xm CHAR(10);

1.NOT NULL:非空约束，不能为空值

2.UNIQUE:唯一性约束，指定列所有数据唯一

3.PRIMARY KEY:主键约束，指定列所有行数据必须唯一

4.FOREIGN KEY:外键约束，建立在列和引用列的一种强制依赖关系

5.CHECK:检查性约束，必须满足某种条件

create table sc (

scid number(10) constraint sc_scid_pk primary key,

studentno char(10) constraint sc_scstuno_fk references student(studentno),

cid number(4) constraint sc_sccid_fk references course(cid),

grade number(5,2) constraint sc_grade_ck check(grade between 0 and 100)

);

create table copy_emp as select * from emp where 1=0;

select * from copy_emp;

create table copy_dept as select * from dept where 1=0;

select * from copy_dept;

alter table copy_emp add constraint copy_emp_deptno_fk

foreign key(deptno) references copy_dept(deptno);

-- 不能，因为copy_dept表中的deptno不是主键或唯一值

alter table copy_dept add constraint copy_dept_deptno_pk primary key(deptno);