zl程序教程

您现在的位置是:首页 >  数据库

当前栏目

细说MySQL表操作

mysql 操作 细说
2023-09-27 14:19:39 时间

要想在某一个数据库里创建表,首先要使用(进入)这个数据库,使用命令 use 数据名

如果是通过Windows控制台创建表,则一定注意,将控制台的编码设置为gbk(set names gbk),因为控制台就只有一种编码,那就是gbk。如果不设置会出错。虽然这里设置的编码是gbk,这里设置的是控制台的编码,不是数据库的编码也不是表的编码。数据库管理系统会自动将表的格式转换为创建表时设置的格式。

如果是通过MacOS的终端创建表,则需要设置编码的编码和数据库的字符编码保持一致。

语法

CREATE TABLE 表名 (
字段名1  数据类型  字段选项 ,
字段名2  数据类型  字段选项 ,
字段名3  数据类型   字段选项,
字段名4  数据类型   字段选项,
字段名n  数据类型 (这里千万不能再有逗号,否则会报错)
)CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 存储引擎方式;

(字段选项、字符集、校对规则、存储引擎等都可以不写)

字段名: 自定义的名称,来告诉表数据表头是什么

字段类型: mysql数据类型分三大类,数值型,字符串型,日期时间型

字段选项: 每个字段有不同的功能,可以添加不同选项来特殊化字段

主键: primary key,作用:加上主键后数据不能重复,查询时主要用这个字段

ENGINE: 数据库表的存储引擎(PHP程序员需要掌握MyISAM、InnoDB、Memory),目前我们使用最多的是MyISMAM和InnoDB,默认是InnoDB.该值在my.ini中设置

说明:

(1) 表也可以指定字符集

(2) 如果表指定了字符集和校对规则,则以表指定的为准,如果表没有指定,则以表所在的数据库的字符集和校对规则。

(3) engin 就是存储引擎,比如常用的(MyISAM、InnoDB, Memory), 如果创建表的时指定了这个存储引擎,则以这个准,如果没有指定以默认的. 在my.ini中设置的有

(4) AUTO_INCREMENT,加上这个数据就会自动增长

示例:

CREATE TABLE `user`
(
	`id` int unsigned AUTO_INCREMENT PRIMARY KEY COMMENT "主键ID",
	`name` varchar(64) NOT NULL COMMENT "姓名",
	`nickname` varchar(64) NOT NULL DEFAULT "" COMMENT "昵称",
	`password` char(32) NOT NULL COMMENT "密码",
	`birthday` date COMMENT "出生日期"
)CHARSET=UTF8 COLLATE=utf8_general_ci ENGINE=InnoDB;

查看表结构

desc 表名;
describe 表名;

查看所有的表

show tables;

删除表

drop table 表名;

该命令一旦执行,那么这个表连同表中的数据都会被删掉,虽然有恢复数据的方法,但是很困难,除非你想删库跑路,否则,在遇到在删除数据或者数据库的时候一定要谨慎操作,除非真的有必要。

查看创建表的语句

show create table 表名;

修改表的字符集和校验规则

ALTER  TABLE  表名 CONVERT TO CHARACTER SET 字符集 COLLATE 校对规则;

修改表名

rename table 旧表名 to 新表名;

在表中添加新字段

alter table 表名 add  新字段名 字段类型 [字段属性列表];

原本abc表中,只有两个字段,新增一个hobby字段

在指定位置后面添加新的字段

alter  table  表名  add  新字段  字段类型[字段属性列表]   after  指定位置的字段名;

在name后面添加一个age字段

在表的最前面添加新的字段

alter  table  表名  add  新字段  字段类型[字段属性列表]  first;

注意:使用first只能是在表的最前面添加字段,first后面不要再接字段名;

修改表的字段名

alter table 表名 change  旧字段名 新字段名 新字段类型 [新字段属性列表];

注意: 不能修改主键的名字, 修改字段名的时候一定要给新字段名添加属性设置,并且设置的字段类型的存储范围要大于等于存储的数据,否则保护机制会提醒,更改后原有的数据无法存下,比如原有的字段名类型为varchar(4),存储了一个数据为'1234',如果改为char(2),就无法存下'1234'了,这样是不允许的。

而且修改字段名的时候就必须声明字段类型,即使是字段类型和原来的字段类型一样也要重新声明。

下图中把hobby字段改成了love

修改表中字段的属性

alter table 表名 modify  字段名 新字段类型 [新字段属性列表];

这个属性是指字段的数据类型int ,decimal等

删除表中的某个字段

alter table 表名 drop 字段名;

设置某个字段为主键

alter table 表名 add primary key(要设置为主键的字段名);

把某个字段的主键属性删掉

alter table 表名 drop primary key;

注意:如果把某个字段设置为了主键,并且该字段是auto_increment,那么我们将无法把他的主键属性删除掉,如果我们想删除这个带auto_increment的属性的主键属性,那么就必须直接把这个字段删除掉(用alter table 表名 drop 字段名;来删除),如果我们还需要这个字段,只是不需要它成为主键了,我们可以再通过增加字段的方法再添上该字段,由于一个表中只能有一个主键(这个主键也可能是由多个字段组合而成的主键),所以当一个表中已经存在一个主键时,我们不能再同时添加另一个主键,要想在添加其它主键就必须删除原有的主键。

只有主键和unique才能使用auto_increment,如果一个字段不是主键或unique,那么他也不能使用auto_increment。

移动字段

如果把某个字段移动到另一个字段的后面(比如把B字段移动到A字段后面)

alter table 表名  modify 要被移动的字段名(B) 要被移动的字段的定义(和原来的定义一样)  after A字段名(也就是所说的另一个字段A);

示例:将name字段移到nickname字段后面

注意,要被移动的字段一定要带上它本身的属性,不然会报错。比如如果使用这样的语句就会报错。

错误用法:alter table user modify name after nickname;

清空表数据

# 这个只是清空了表的数据,表和表结构都还在,等同于一张从没插入过数据的新表,如果插入数据主键ID重新开始计数
truncate  tableName;

从图中数据的主键ID就可以看到,通过truncate 表名清空的数据表就相当于一张新表,好像以前就没插入过数据一样。

删除表数据

# 这条命令删除整个表的数据,表结构还在,插入数据时,主键ID会按照原来以后的主键ID基数基础上继续计数
delete from 表名;

从图中可以看出,delete 命令只是删除数据,被删除的数据占用的空间依然存在,所以插入的数据时候主键ID会在原来主键ID基数的基础上开始增加。

复制一张表

如果我们需要创建的表和原来的表的结构类似,我们就可以依据原来的表复制一张新表,只是复制了结构,并不复制数据,然后可以通过命令把不需要的字段删除掉。这样我们就可以快速创建一张类似的数据表。

create table 新表名 like  被复制的表名;