欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL数据库(四)数据库和表操作

程序员文章站 2022-03-12 10:49:06
4.1 连接 在最初安装MySQL,可能会要求你输入一个管理登录(通常为root)和一个口令(密码)。 连接MySQL需要以下信息: 主机名(计算机名)——如果连接到本地MySQL服务器,为localhost; 端口(如果使用默认端口3306之外的端口); 一个合法的用户名(默认为root) 用户口 ......

4.1 连接

在最初安装mysql,可能会要求你输入一个管理登录(通常为root)和一个口令(密码)。

连接mysql需要以下信息:

  • 主机名(计算机名)——如果连接到本地mysql服务器,为localhost;
  • 端口(如果使用默认端口3306之外的端口);
  • 一个合法的用户名(默认为root)
  • 用户口令(密码,如果需要的话)。

cmd登录前一篇有说。

会简单介绍navicat premium 12,但是后面都是执行命令,所以在navicat premium 12的命令行窗口做或者在cmd做都可以。

现在说下:navicat premium 12,打开它。

MySQL数据库(四)数据库和表操作

MySQL数据库(四)数据库和表操作

3.2 创建数据库

《mysql必知必会》前面一开始就开讲selete查询等,而像新手可能就会不知道怎么创建数据库,创建表;然后利用这些表去测试所学的selete查询等。

下面提供两种方式创建数据库:

  1. 图形界面创建数据库

MySQL数据库(四)数据库和表操作

MySQL数据库(四)数据库和表操作

那么数据库就创建完成。

  1. 命令行创建数据库

MySQL数据库(四)数据库和表操作

MySQL数据库(四)数据库和表操作

然后输入:

create database ts;

更加规范的写法(推荐):
格式:

create database if not exists <数据库名> default character set='utf8';

比如:

create database if not exists ts default character set='utf8';

解释:

  • create database ts:表示创建一个名为ts的数据库;
  • if not exists:翻译过来就是如果不存在,防止当数据库存在时会报错;
  • create database if not exists ts:表示如果不存在一个名为ts的数据库,则创建该数据库;
  • character set='utf8':这个跟我们上面图形界面设置的一样,就是设置数据库的字符集。
  • ; :分号表示语句结束,所以每一条语句都需要分号结尾。

注意:写的时候注意中英文符号,比如 ''。

4.3 删除数据库

格式:

drop database <数据库名>;

4.4 选择数据库

可以看到我们刚刚是在my连接这里开启命令界面,我们操作的使用并不是对所有数据库进行操作,而是对单个数据库进行操作,那么就需要输入选择数据库的语句。

格式:

use <数据库名>;

选择刚刚建立的ts数据库:

use ts;

命令行界面会返回:

database changed # 表示成功

4.5 了解数据库和表

数据库、表、用户、列、权限等信息被存储在数据库和表中(mysql用mysql来存储这些信息)。不过,内部的表一般不直接访问。可用mysql的show命令来显示这些信息(mysql从内部表中提取这些信息)。

  1. 查询已经存在的数据库(包括mysql系统数据库:mysql和information_schema):
show databases;

输出:
MySQL数据库(四)数据库和表操作

  1. 获取一个数据库中的表的列表,前提是已经选择数据库,不然也不知道你要获取哪个数据库,假设获取mysql数据库的表。这里只是演示,一般别去动mysql数据库,毕竟是mysql的系统数据库。
use mysql;
show tables; # 显示数据库的全部表

输出:
MySQL数据库(四)数据库和表操作

  1. 显示一个表中的列
use mysql;
show columns from 表名称;

输出:
MySQL数据库(四)数据库和表操作

mysql支持用 describe 作为 show columns from 的一种快捷方式。

4.其他的show语句:

  • show status:用于显示广泛的服务器状态信息;
  • show create database和show create table:分别用来显示创建特定数据库或表的mysql语句;
  • show grants:用来显示授予用户(所有用户或特定用户)的安全权限;
  • show errors和show warnings:用来显示服务器错误或警告消息。

4.6 mysql数据类型

mysql中定义数据字段的类型对你数据库的优化是非常重要的。

mysql支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。

4.6.1 数值类型

mysql支持所有标准sql数值数据类型。这些类型包括严格数值数据类型(integer、smallint、decimal和numeric),以及近似数值数据类型(float、real和double precision)。

关键字int是integer的同义词,关键字dec是decimal的同义词。

bit数据类型保存位字段值,并且支持myisam、memory、innodb和bdb表。

作为sql标准的扩展,mysql也支持整数类型tinyint、mediumint和bigint。下面的表显示了需要的每个整数类型的存储和范围。
MySQL数据库(四)数据库和表操作

4.6.2 日期和时间类型

表示时间值的日期和时间类型为datetime、date、timestamp、time和year。

每个时间类型有一个有效值范围和一个"零"值,当指定不合法的mysql不能表示的值时使用"零"值。

timestamp类型有专有的自动更新特性,将在后面描述。
MySQL数据库(四)数据库和表操作

4.6.3 字符串类型

字符串类型指char、varchar、binary、varbinary、blob、text、enum和set。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
MySQL数据库(四)数据库和表操作
char 和 varchar 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

binary 和 varbinary 类似于 char 和 varchar,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。

blob 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 blob 类型:tinyblob、blob、mediumblob 和 longblob。它们区别在于可容纳存储范围不同。

有 4 种 text 类型:tinytext、text、mediumtext 和 longtext。对应的这 4 种 blob 类型,可存储的最大长度不同,可根据实际情况选择。

关于 char、varchar 与 text 平时没有太在意,一般来说,可能现在大家都是用 varchar。但是当要存储的内容比较大时,究竟是选择 varchar 还是 text 呢?

这三种类型比较:

  1. char: char 不用多说了,它是定长格式的,==但是长度范围是 0~255。 当你想要储存一个长度不足 255 的字符时,mysql 会用空格来填充剩下的字符。因此在读取数据时,char 类型的数据要进行处理,把后面的空格去除。== 这就是一个坑。
  2. varchar: 关于 varchar,有的说最大长度是 255,也有的说是 65535,查阅很多资料后发现是这样的:++varchar 类型在 5.0.3 以下的版本中的最大长度限制为 255++,而++在 5.0.3 及以上的版本中,varchar 数据类型的长度支持到了 65535++,也就是说可以存放 65532 个字节(==注意是字节而不是字符!!!==)的数据(起始位和结束位占去了3个字节),也就是说,在 5.0.3 以下版本中需要使用固定的 text 或 blob 格式存放的数据可以在高版本中使用可变长的 varchar 来存放,这样就能有效的减少数据库文件的大小。
  3. text: 与 char 和 varchar 不同的是,text 不可以有默认值,其最大长度是 2 的 16 次方-1

总结起来,有几点:

  • 经常变化的字段用 varchar
  • 知道固定长度的用 char(比如性别)
  • 超过 255 字符的只能用 varchar 或者 text
  • 能用 varchar 的地方不用 text
  • 理论上在表中的列全都可以使用varchar来定义列的数据类型,但是千万不要这样,最好根据列的实际情况选择对应的数据类型,并且可以优化数据库,比如:性别-》char,学生学号位一般用整型来存储。


数据类型的选择特别重要:

  1. 数据类型会影响存储空间的开销。
  2. 数据类型会影响数据库查询性能。

所以当一个数据类型可以有多种选择多种类型的时候,应该优先考虑数字类型,其次是日期或二进制类型,最后应该是字符类型。对于相同级别的数据类型,应该优先选择占用空间小的数据类型。
原理:在对数据进行比较(查询条件,join条件及排序)操作时:同样的数据,字符处理往往比数字处理慢,而且在数据库中,数据的处理是以页为单位,列的长度越小,数据类型占用的空间越小,利于性能的提升。

更多的优化等我看《高性能mysql》再总结。

4.7 创建数据库表

创建表有两种方式:

  • 使用具有交互式创建和管理表的工具(navicat premium 12 图形界面);
  • 表也可以直接用mysql语句操纵。

利用create table创建表需要以下信息:

  • 新表的名称,再关键字create table之后给出;
  • 表列的名字和定义,用逗号隔开

创建表的语法:

create table 表名(
    列名1 列的定义比如数据类型,是否为空,是否为主键等,
    列名2 列的定义,
    ...
);

简单创建单表的例子:

create table student(
    stu_id int auto_increment,
    stu_name varchar(10) not null,
    stu_sex char(1) null,
    primary key(stu_id)
    )engine=innodb default charset=utf8;;

解释:

  • 如果你不想字段为 null 可以设置字段的属性为 not null, 在操作数据库时如果输入该字段的数据为null ,就会报错。
  • auto_increment定义列为自增的属性,一般用于主键,数值会自动加1。
  • primary key关键字用于定义列为主键。表中的每个行必须具有唯一的主键值,也就是一个表中至少有一个主键。 可以使用多列来定义主键,列间以逗号分隔。该关键字也可以直接写在stu_id的定义中。
  • engine 设置存储引擎(这个先别管),charset 设置编码。

额外:

null和not null:null值就是没有值或缺值。允许null值的列也允许在插入行时不给出该列的值。不允许null值的列不接受该列没有值的行。

理解null:不要把null值与空串相混淆。null值是没有值,它不是空串。如果指定''(两个单引号,其间没有字符),这在not null列中是允许的。空串是一个有效的值,它不是无值。null值用关键字null而不是空串指定。

主键中只能使用不允许null值得列。允许null值得列不能作为唯一标识。

auto_increment:被该关键字指定的列,表示每次插入数据时会对该列自动增长,通常使用它去定义主键。比如在增加一个新记录时,需要一个id值,这id可以任意,只要它是唯一的即可,那么最简单的就是使用下一个id值,即当前表中最后一条记录的id为1,那么新插入数据时,新插入的数据的id就为2。所以就可以使用auto_increment自动增加id值,我们在插入的时候也不用去理会id是多少。
如何在表中获得auto_increment的值,输入:
select last_insert_id();
就会返回最后一个auto_increment值。
也可以自己修改auto_increment的值,只要是唯一的。

default:指定列的默认值,注意默认值不允许为函数,《mysql必知必会》推荐使用默认值而不是使用null值。否则使用not null也可以。

MySQL数据库(四)数据库和表操作

4.8 引擎类型

上面创建表中最后有一个 engine=innodb。下面来解释一下:

mysql与其他dbms一样有一个具体管理和处理数据的内部引擎。比如当你使用create table语句时,该引擎具体创建表,而在你使用selece查询语句时或进行其他数据库处理时,该引擎在内部处理你的请求。所以它是对我们透明化的。

mysql与其他dbms不一样的是,mysql具有多种引擎。这些引擎都隐藏在mysql服务器内,全都能执行create table和selece等命令。

为什么要这么多引擎,因为它们具有各自不同的功能和特效,为不同的任务选择正确的引擎能够获得良好的功能和灵活性。

但是在create table时也可以忽略它,会自动使用默认引擎(很可能是myisam),多数sql语句都会默认使用它。

以下是几个必须知道的引擎:

  • innobd是一个可靠的事务处理引擎,它步支持全文本搜索;
  • memory在功能等同于myisam,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表)。
  • myisam是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。

引擎类型可以混用。但是外键不能跨引擎。混合引擎有一个很大缺陷。外键(用于强制实施引用完整性)不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键

4.9 删除表

格式:

drop table <表名> ;

4.10 更新表

语法:

alter table <表名>
操作

比如:
给表添加一个列:

alter table student
 add stu_phone char(11);

给表删除一个列:

alter table student
 drop stu_phone;

外键可以在创建表定义,也可以使用alter table来定义,下面是定义外键的一种方式:

再创建一张班主任表

create table headmaster(
    ma_id int primary key auto_increment,
    ma_name varchar(10) not null
    )engine=innodb default charset='utf8';

先给学生表添加一个 班主任的列:

alter table student add ma_id int;

定义外键:与学生表相关联。

alter table student
 add constraint fk_student_master
 foreign key (ma_id) references headmaster(ma_id);

外键语法:在创建表直接加上下面这句,修改表得再加个add

constraint <外键名,自定义>
 foreign key (<当前表的列,比如学生表>) references <要关联的表,比如班主任表>(<要关联的列>);

复杂得表结构更改一般需要手动删除过程,它涉及一下步骤:

  • 用新的列布局创建一个新表;
  • 使用insert select语句(待了解)从旧表复杂数据到新表。如果有必要,可使用转换函数和计算字段;
  • 校验包含所需数据的新表;
  • 重命名旧表(如果确定,可以删除它);
  • 用旧表原来的名字重命名新表;
  • 根据需要,重新创建触发器,存储过程,索引和外键(除外键其他待了解)。

小心使用alter table :应该在改动前做一个完整的备份(模式和数据的备份)。数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们。类似地,如果删除了不应该删除的列,可能会丢失该列中的所有数据。

4.11 重命名表

语法:

rename table <原表名> to <新表名>;