MySQL数据表基本操作实例详解

本文实例讲述了MySQL数据表基本操作。分享给大家供大家参考,具体如下:

数据表的基本操作

1.主键约束要求主键列的数据唯一,并且不允许为空。主键能够唯一地识别表中的一条记录,可以结合外键来定义不同数据表之间的关系,并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系。

2.字表的外键必须关联父表的主键,且关联字段的数据类型必须匹配。如果类型不一样。创建子表时,就会出现错误:ERROR 1005(HY000):can't create table 'databases.tablesname'(error:150)

3.非空约束指字段的值不能为空。

4.唯一性约束:要求该列唯一,允许为空,但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

5.unique和primary key 的区别:一个表中可以有多个字段声明为unique,但只能有一个primary key声明:声明为关键字的列不允许为空值,但是声明为unique的字段允许空值null的存在。

【例.1】创建员工表tb_emp1

选择创建表的数据库:

USE test_db;

创建tb_emp1表:

CREATE TABLE tb_emp1
(
id int(11),
name varchar(25),
deptId int(11),
salary float
);

【例.2】定义数据表tb_emp 2,其主键为id:

CREATE TABLE tb_emp2
(
id int(11) PRIMARY KEY,
name varchar(25),
deptId int(11),
salary float
);

【例.3】定义完所有列之后指定主键

定义数据表tb_emp 3,其主键为id:

CREATE TABLE tb_emp3
(
id int(11),
name varchar(25),
deptId int(11),
salary float,
PRIMARY KEY(id)
);

【例.4】定义数据表tb_emp4,创建多字段联合主键SQL语句为:

CREATE TABLE tb_emp4
(
name varchar(25),
deptId int(11),
salary float,
PRIMARY KEY(name,deptId)
);

【例.5】定义数据表tb_emp5,并在tb_emp5表上创建外键约束

创建一个部门表tb_dept1:

CREATE TABLE tb_dept1
(
id int(11) PRIMARY KEY,
name varchar(22) NOT NULL,
location varchar(50)
);

定义数据表tb_emp5,让它的键deptId作为外键关联到tb_dept1的主键id:

CREATE TABLE tb_emp5
(
id int(11) PRIMARY KEY,
name varchar(25),
deptId int(11),
salary float,
CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

【例.6】定义数据表tb_emp6,指定员工的名称不能为空,SQL语句为:

CREATE TABLE tb_emp6
(
id int(11) PRIMARY KEY,
name varchar(25) NOT NULL,
deptId int(11),
salary float,
CONSTRAINT fk_emp_dept2 FOREIGN KEY (deptId) REFERENCES tb_dept(id)
);

【例.7】定义数据表tb_dept2,指定部门的名称唯一,SQL语句为:

CREATE TABLE tb_dept2
(
id int(11) PRIMARY KEY,
name varchar(22) UNIQUE,
location varchar(50)
);

【例.8】定义完所有列之后唯一约束,

定义数据表tb_dept3,指定部门的名称唯一,SQL语句为:

CREATE TABLE tb_dept3
(
id int(11) PRIMARY KEY,
name varchar(22),
location varchar(50),
CONSTRAINT STH UNIQUE(name)
);

【例.9】定义数据表tb_emp7,指定员工的部门编号默认为1111,SQL语句为:

CREATE TABLE tb_emp7
(
id int(11) PRIMARY KEY,
name varchar(25) NOT NULL,
deptId int(11) DEFAULT 1111,
salary float,
CONSTRAINT fk_emp_dept3 FOREIGN KEY (deptId) REFERENCES tb_dept(id)
);

【例.10】定义数据表tb_emp8,指定员工的编号自动递增,SQL语句为:

CREATE TABLE tb_emp8
(
id int(11) PRIMARY KEY AUTO_INCREMENT,
name varchar(25) NOT NULL,
deptId int(11),
salary float,
CONSTRAINT fk_emp_dept5 FOREIGN KEY (deptId) REFERENCES tb_dept(id)
);
INSERT INTO tb_emp8 (name,salary) VALUES('Lucy',1000), ('Lura',1200),('Kevin',1500);
select * from tb_emp8;

【例.11】分别使用DESCRIBEDESC查看表tb_dept和表tb_emp1的表结构,操作过程如下:

DESCRIBE tb_dept;
DESC tb_emp1;

【例.12】使用SHOW CREATE TABLE查看表tb_emp1的详细信息,SQL语句及相应的执行结果如下示:

SHOW CREATE TABLE tb_emp1;
SHOW CREATE TABLE tb_emp1\G;

查看数据表的结构

1.查看表基本结构语句describe或者desc +表名。

2.查看表详细结构语句 show create table。 如果不加‘\G'参数,显示的结果可能非常混乱,加上参数‘\G'之后,可使显示结果更加直观,易于查看。

修改数据表

1.修改表名

【例.13】将数据表tb_dept3改名为tb_deptment3

ALTER TABLE tb_dept3 RENAME tb_deptment3;

2.修改字段的数据类型

【例.14】将数据表tb_dept1中name字段的数据类型由VARCHAR(22)的修改成VARCHAR(30)

ALTER TABLE tb_dept MODIFY name VARCHAR(30);

3.修改该字段名

【例.15】将数据表tb_dept1中的location字段名称改为loc,数据类型保持不变。

ALTER TABLE tb_dept1 CHANGE location loc varchar(50);

【例.16】将数据表tb_dept1中的loc字段名称改为location,同时将数据类型保持变为varchar(60)。

ALTER TABLE tb_dept1 CHANGE loc location varchar(60);

4.添加字段

【例.17】在数据表tb_dept中添加一个没有完整性约束的int类型的字段managerId(部门经理编号),输入SQL语句如下:

ALTER TABLE tb_dept ADD managerId int(10);

【例.18】在数据表tb_dept1中添加一个不能为空的varchar(12)类型的字段column1。输入如下SQL语句,并执行:

ALTER TABLE tb_dept1 ADD column1 varchar(12) not null;

【例.19】在数据表tb_dept中添加一个int类型的字段column2。输入如下SQL语句,并执行:

ALTER TABLE tb_dept ADD column2 int(11) FIRST;

【例.20】在数据表tb_dept1中name列后添加一个int类型的字段column3。输入如下SQL语句,并执行:

ALTER TABLE tb_dept1 ADD column3 int(11) AFTER name;

5.删除字段

【例.21】删除数据表tb_dept1表中的column2字段

ALTER TABLE tb_dept1 DROP column2;

6.修改字段的排列位置

【例.22】将数据表tb_dept1中的column1字段修改为表的第一个字段

ALTER TABLE tb_dept1 MODIFY column1 varchar(12) FIRST;

【例.23】将数据表tb_dept1中的column1字段插入到location字段后面

ALTER TABLE tb_dept1 MODIFY column1 varchar(12) AFTER location;

7.删除数据的外键约束

【例.25】删除数据表tb_emp9中的外键约束

CREATE TABLE tb_emp9
(
id int(11) PRIMARY KEY,
name varchar(25),
deptId int(11),
salary float,
CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);
ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;

删除数据表

【例.26】删除数据表tb_dept2,输入如下SQL语句并执行:

DROP TABLE IF EXISTS tb_dept2;

【例.27】删除被数据表tb_emp关联的数据表tb_dept2

CREATE TABLE tb_dept2
(
id int(11) PRIMARY KEY,
name varchar(22),
location varchar(50)
);
CREATE TABLE tb_emp
(
id int(11) PRIMARY KEY,
name varchar(25),
deptId int(11),
salary float,
CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept2(id)
);

直接删除父表tb_dept2

DROP TABLE tb_dept2;

解除关联子表tb_emp的外键约束

ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

(0)

相关推荐

  • mysql修复数据表的命令方法

    所有数据库 复制代码 代码如下: mysqlcheck --auto-repair -A -o -uroot -pyigeorg 单一数据库 复制代码 代码如下: mysqlcheck --auto-repair --databases yige -o -uroot -pyigeorg 这样就搞定了.

  • MySQL中一些常用的数据表操作语句笔记

    0.创建表 CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件], 属性名 数据类型 [完整性约束条件], 属性名 数据类型 [完整性约束条件]) "完整性约束条件"是指指定某些字段的某些特殊约束条件. 在使用CREATE TABLE创建表时首先要使用USE语句选择数据库.比如有个example数据库,用USE EXAMPLE选择这个数据库. 表名不能用SQL的关键字,如create,update等,字母不区分大小写. 下面是一个创建表的例子: create tabl

  • mysql 数据表中查找重复记录

    复制代码 代码如下: select user_name,count(*) as count from user_table group by user_name having count>1; 这个我在很早有发过一个asp下的ACCESS 的

  • mysql 导入导出数据库、数据表的方法

    Linux下均在控制台下操作.导入数据库:前提:数据库和数据表要存在(已经被创建) (1)将数据表 test_user.sql 导入到test 数据库的test_user 表中[root@test ~]# mysql -uroot -p test < /www/web/test/test_user.sql (2) 将数据库 test.sql 导入到 test 数据库test 中[root@test ~]# mysql -uroot -p test < /www/web/test/test.sq

  • MySQL之终端Terminal(dos界面)管理数据库、数据表、数据的基本操作

    MySQL有很多的可视化管理工具,比如"mysql-workbench"和"sequel-pro-". 现在我写MySQL的终端命令操作的文章,是想强化一下自己对于MySQL的理解,总会比使用图形化的理解透彻,因为我本来就比较喜欢写代码.同时写出来这些文章,是想要给大家当个参考,希望也能对大家有所帮助,有所提升,这就是我为什么要写终端操作MySQL的文章了. 注意:MySQL数据库命令不区分大小写.但在MAC的终端,如果你想使用tab自动补全命令,那么你就必须使用大

  • Mysql数据表分区技术PARTITION浅析

    在这一章节里, 我们来了解下 Mysql 中的分区技术 (RANGE, LIST, HASH)   Mysql 的分区技术与水平分表有点类似, 但是它是在逻辑层进行的水平分表, 对于应用而言它还是一张表, 换句话说: 分区不是实际真正的对一张表进行拆分,分区之后表还是一个表,它是把存储文件进行拆分. 在 Mysql 5.1(后) 有了几种分区类型:   RANGE分区: 基于属于一个给定连续区间的列值, 把多行分配给分区 LIST分区: 类似于按 RANGE 分区, 区别在于 LIST 分区是基

  • MySQL数据库查看数据表占用空间大小和记录数的方法

    如果想知道MySQL数据库中每个表占用的空间.表记录的行数的话,可以打开MySQL的 information_schema 数据库.在该库中有一个 TABLES 表,这个表主要字段分别是: TABLE_SCHEMA : 数据库名 TABLE_NAME:表名 ENGINE:所使用的存储引擎 TABLES_ROWS:记录数 DATA_LENGTH:数据大小 INDEX_LENGTH:索引大小 其他字段请参考MySQL的手册,这几个字段对我们来说最有用. 一个表占用空间的大小,相当于是 数据大小 +

  • MySQL中复制数据表中的数据到新表中的操作教程

    MySQL是不支持SELECT - INTO语法的,使用INSERT INTO - SELECT替代相同用法,下面我们我们这里简答分一下新表存在和不存在两种情况,具体使用不同的语句. 1.新表不存在 复制表结构即数据到新表 create table new_table select * from old_talbe; 这种方法会将old_table中所有的内容都拷贝过来,用这种方法需要注意,new_table中没有了old_table中的primary key,Extra,auto_increm

  • MySQL快速复制数据库数据表的方法

    某些时候,例如为了搭建一个测试环境,或者克隆一个网站,需要复制一个已存在的mysql数据库.使用以下方法,可以非常简单地实现. 假设已经存在的数据库名字叫db1,想要复制一份,命名为newdb.步骤如下: 1. 首先创建新的数据库newdb #mysql -u root -ppassword mysql>CREATE DATABASE `newdb` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI; 2. 使用mysqldump及mysql的

  • mysql中数据库与数据表编码格式的查看、创建及修改

    一.查看数据库编码格式 mysql> show variables like 'character_set_database'; 二.查看数据表的编码格式 mysql> show create table <表名>; 三.创建数据库时指定数据库的字符集 mysql>create database <数据库名> character set utf8; 四.创建数据表时指定数据表的编码格式 create table tb_books ( name varchar(45

  • MySQL数据表字段内容的批量修改、清空、复制等更新命令

    最近遇到一点麻烦事,新安装的PHPwind6.0正式版社区在导入之前的会员帐号资料时,发现很多会员的mail地址貌似胡乱填写的,之前的PHPwind5.5版本没有开启mail地址验证功能,所以估计很多用户胡乱填写了email地址,所以我就想要求所有正式会员重新验证邮件地址来重新激活会员帐号,结果发现社区根本没有这项功能,挣扎了N久,数据库的会员资料数据表被反复安装=删除了好几遍,总算找到了一个批量修改的方法. 不过这样操作会连社区创建者的账号都改成未激活,所以要是不清楚还真的不敢动手. 在PHP

随机推荐