数据库和表操作

创建数据库

1
create database 数据库名称;

注意事项:

  1. information_schema是MySQL自带的数据库,存储MySQL服务器管理的信息;
  2. mysql是MySQL的系统数据库;
  3. test是用户学习和测试练习使用的数据库;
  4. MySQL中的数据库名称不能超过64字符,不能由纯数字组成,不能包含:“/”、“:”、“*”、“?”、“>”、“<”等符号;
  5. 如果要创建的数据库名称已经存在,也会发生错误。(可以用”if exists 数据库名称 ”来判断是否存在,以避免发生错误)。Eg:
1
2
3
4
5
-- 判断student数据库存在就删除
drop database if exists student;
-- 重新创建student数据库
create database student;
-- 创建数据库后,如果需要使用数据库,使用:”use 数据库名;”如:use student;

删除数据库

1
drop database 数据库名称;
1
2
Eg: drop database user; -- 删除了名叫“user”的数据库;
drop database if exists user; -- 也可以用if exists做判断,避免发生错误;

创建表

1
2
3
4
5
create table 表名(
字段名称1 数据类型 [ null / not null ] [ DEFAULT 默认值 ] [ AUTO_INCREMENT ] [ PRIMARY_KEY ] [ COMMENT ‘注释内容’ ] ,
......
字段名称N 数据类型 [ null / not null ] [ DEFAULT 默认值 ] [ AUTO_INCREMENT ] [ PRIMARY_KEY ] [ COMMENT ‘注释内容’ ]
);
1
2
3
4
5
6
7
8
Eg:
drop table if exists teacher ; -- 首先判断是否存在teacher表,如果存在就删除
create table teacher(
id int not null auto_increment primary key ,
name varchar(20) not null
);
-- 创建了teacher表,里面有int、 name两个字段, 其中id是主键,非空,自增
-- 创建数据库后,如果需要使用数据库,使用:”use 数据库名;”如:use teacher;

删除表

1
drop table 表名;
1
2
Eg: drop table teacher;
或者删除多个表格: drop table teacher, student;

复制表

1
2
3
4
完全复制的格式:
create table02 select * from01;
Eg: create table teacher02 select * from teacher;
-- 复制一张和teacher表完全一样的teacher02表;
1
2
3
4
复制指定列的格式:
create table02 select 字段a,字段b,...... from01;
Eg: create table teacher02 select id, name from teacher;
-- 复制一张teacher02表,里面包含teacher表里的id,name;
1
2
3
4
完全复制表结构(包括键key),但是不复制里面的内容,格式是:
create table02 like01;
Eg: create table teacher02 like teacher;
-- 复制一张teacher02表,其结构和teacher表相同,但没有teacher里的内容;

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1. 增加字段:add
Eg: alter table teacher add age int null; -- 添加age列,int类型,可以为null;

2. 删除字段:drop
Eg: alter table teacher drop age; -- 删除age列;

3.修改字段:change
Eg: alter table teacher change name teacher_name varchar(30) not null;
-- 修改name列为teacher_name,varchar类型最大30个字符,不能为null;

4. 增加主键:add primary key()
Eg: alter table teacher add primary key(id); -- 设置id为主键(id是已有字段);

5. 修改表名:rename to
Eg: alter table teacher rename to user; -- 把teacher表改名为user;

数据完整性

数据完整性的概念:确保数据正确性和一致性的机制。

  1. 域完整性:即列的完整性。如:年龄不能超过150岁,id不能为null。
  2. 实体完整性:即行的完整性。如:实体“张三”具有区别于实体“李四”的特征。
  3. 参照完整性:主关系表(被参照表)和从关系表(参照表)中数据的一致性。
  4. 用户定义完整性:用户根据具体需求来定义约束条件。

使用SQL语句为表创建和删除约束

如果要修改已创建的约束,最简单实用的方式就是先删除已有的约束,再建立同名的约束。

添加约束

1
格式:alter table 表名 add [constraint] [约束名称] 约束类型(约束内容[可多个字段]);

主键约束

1
2
3
4
5
6
7
主键约束: 
alter table user add constraint PK_user_id primary key(id);
-- 把user表的id添加主键约束,约束名称是PK_user_id。(PK是主键的简写)

设置联合主键的方式:
alter table user add primary key(idname);
-- 设置了联合主键id和name; 补充: 在创建表的时候也可以直接设置主键约束。

唯一约束

1
唯一约束:alter table 表名 add [constraint] unique(id);

默认约束

1
2
默认约束:alter table 表名 alter column列名 set DEFAULT 默认值;
-- 默认值可以为常量或者为空(null);

检查约束

1
2
3
4
检查约束:(与SQL Server中的检查约束有语法区别)可以使用**枚举**。
Eg:create table student ( ...... sex **enum**(‘男’, ‘女’) not
null -- 学员性别 );
-- 默认值可以为常量或者为空(null);

外键约束

1
2
3
4
5
6
7
8
9
10
11
外键约束: 格式:alter table 表名 add constraint 约束名称 约束类型;
(外键约束简称为“FK”)
Eg:alter table student add constraint FK_student_class_classid foreign key (classid) references class(classid);
-- 为student表所在的班级字段classid设置了外键约束。

创建时设置外键:
create table student (
......
classid int not null, foreign key(classid) references class(classid)
-- 设置名称为classid的外键,指向class表的classid;
);

设置级联操作

1
2
3
4
5
6
7
8
9
10
11
设置级联操作:
restrict和no action表示在子表有关联记录的情况下父表不能更新;
cascade 表示父表在更新或者删除时,同时更新或删除子表对应的记录;
set null 表示父表在更新或者删除时,子表对应的字段设置为null

Eg:
(1).alter table student add constraint FK_student_class_classid foreign key (classid) references class(classid) on delete set null;
-- 如:删除class表中classid=1对应的字段时,student表中的classid字段的值设置为null

(2).alter table student add constraint FK_student_class_classid foreign key (classid) references class(classid) on delete set cascade;
-- 如:删除class表中classid=1对应的字段时,同时删除student表中所有classid=1的该行数据。

删除约束

1
2
3
4
5
6
7
8
语法:alter table 表名 drop [constraint] 约束名称;
-- constraint关键字可省略 -- 如果将表删除了,那么相关的约束也就删除了。

Eg01: alter table student drop primary key;
-- 存在一个主键约束,可用此操作;

Eg02: alter table student drop foreign key FK_class_student_id;
-- 删除外键

数据操作

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
--(最常用)
-- 插入值的顺序必须与表中字段的顺序完全一致。
1. "insert...values"
语法:insert into 表名 values(值);

--插入一行数据
Eg01: insert into student values (1, ‘zhangsan’ );

--插入多行数据,用逗号隔开;
Eg02: insert into student values (2,‘jack’),(3, ‘rose’);

补充:当插入的记录会引发主键或唯一约束冲突时,可以用 **on duplicate key update** 条件。
Eg: insert into student values (1, ‘mike’ ) **on duplicate key update id =10**;
-- 由于id是主键,id=1与上面zhangsan行冲突,所以自动把zhangsan的id设置为10,然后再把id=1的mike数据插入进去;

-- 按照每一列对应的值插入数据
2. ”insert...set”
语法:insert into 表名 set 列名1=值1, 列名2=值2,......;

--插入的数据是:id为5,姓名’abc’ 3)”insert...select”
-- 把从其他表查询到的数据插入到该表中,类似于like属性 Eg01: insert into user
Eg01: insert into student set id =5, name=’abc’;

--把从student中查询到的数据插入到user中;
select * from student;

更新数据

1
2
3
格式:update 表名 set 列名1=值1 [,列名2=值2,......] [where...] [order by...] [limit 行数]
--把id=3的列的name改为wangwu;
eg: update student set name = 'wangwu' where id = 003;

删除数据

1
2
3
格式:delete from 表名 where 列名=值;
--删除id=3的列的数据。
eg:delete from student where id=3