Mysql数据库和数据表的创建和信息更改的常用指令

数据库和数据表的创建和信息更改

后续小实验做准备

1. 创建数据库

   语句：

    create database experiment;
   

通过:show databases;查看创建结果

2. 创建基本表

   创建基本表时，为不同的列选择合适的数据类型，正确创建表级和列级完整性约束，如列值是否允许为空、主码和外码等。

   创建表的代码如下：

   • student表

     create table student (sno char(9) primary key, sname char(20) unique, ssex char(2), sage smallint, sdept char(20));
     

   • course表

     create table course (sno char(4) primary key, cname char(40) not null, cpno char(4), ccredit smallint, foreign key(cpno) references course(cno));
     

   • SC表

     create table SC (Sno char(9), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno));
     

   查看创建表的就结果

   

   上面创建SC的时候属性是大写开头，二student表和cource表中的全是小写，创建成功，说明win10下mysql不区分大小写

3. 向基本表中加入数据

   student表数据随便输，course表中有坑，因为course表的参照表是自身，所以要先输入需要的先行课程，CS表依赖student表和course表。最后输入。

   • student

     

   • course表

     向输入cpno为null的元组，再选择cpno为已经输入元组cno值相同的元组输入。不然会出现参照不完整的报错。

     

   • CS表

     这个表的数据是查看cource表和student表中的数据

     

一. 关于数据库和数据表的其它操作

1）数据库

①创建数据库

create database 数据库名；

②显示目前所有的数据库

show databases

③数据库重命名

rename database olddbname to newdbname

MySQL 5.1.23 之前的旧版本中，我们可以使用 RENAME DATABASE来重命名数据库，但此后版本，因为安全考虑，删掉了这一条命令。

大于这个版本修改数据库名字基本上是不行的，采用新建数据库，然后进行数据迁移。

2.1 先创建新库：

create database new_db;

2.2 使用RENAME TABLE 命令修改表名，将表移动到新的库里：

rename table old_db.tb to new_db.tb;

2.3 完成后删除旧库：

drop database old_db;

上面的方法是通过重命名表的方式实现数据迁移。

也可以使用dump工具导出数据库的sql文件，更改数据库sql文件中创建数据库的名字。再用source指令引入sql文件实现数据库的数据迁移。

④更改当前使用的数据库

use 数据库名字

⑤更改当前使用的数据库

drop database 数据库名

2）数据表

①数据表的创建

create table <表名> (<列名><数据类型>[列级约束条件],<列名><数据类型>[列级约束条件], [表级约束条件])

列级约束：只能应用于一列上。

表级约束 ：可以应用于一列上，也可以应用在一个表中的多个列上。

字段数据类型

字段设置

主键: 被设置为主键的列，值不能为空，且列中不能出现重复的值

外键: 被设置成外码的列，值只能是参考列值中的数据，可以为空(在没有设置该列只非空的情况下)

这里不说那些上面约束条件，太抽象，只说怎么设置列的属性。

• 列值不能为空

  <列名><属性值> not null

  

• 设置列为主键

  主键不能为空，被设置为主键的列值不能重复

  • 只有一个主键是可以直接

    <列名><属性值> primary key标识主键
    
    course中的sno为主键

  • 多个主键时需要在创建表的末尾添加主键信息

    

    这里SC表中Sno和Cno为主键

  • 创建表后追加主键

    alter TABLE <表名> MODIFY <列名> <列的数据类型> PRIMARY KEY
    

• 设置为外键

  外键必须是参照表的某个key，可以说主键(primary key)也可以是唯一键(unique key)

  有外键时创建数据表，必须要先创建出被参考的表，不然会报错

  • 在创建时设置

    FOREIGN KEY (<外键>) REFERENCES <被参考的数据表>(<被参考的列>)

    

    这里外键是cpno，参考的列的course表中的cno列，cpno列的值只能是cno列中出现过的值，不然会报错

    多个外键 :

    

  • 在创建表后追加

    -- 已存在表增加外键
    ALTER TABLE Products ADD FOREIGN KEY products_vendors_fk_1 (vend_id) REFERENCES Vendors (vend_id);
    

  • 外键的约束模式

    • RESTRICT

      严格模式(默认的)，**父表不能删除或者更新一个已经被子表引用的记录数据(**外键对应的父表被引用的字段，其他字段可以更改)。即，当父表字段数据已经被子表引用时，不能再删除或者更新父表被引用的字段数据。

    • Cascade

      级联模式；针对父表的操作，对应子表关联的数据也跟着被操作。

      • 更新父表被引用的字段，如果该数据被子表外键使用，则子表外键随之更新；
      • 删除父表被引用的字段记录，如果该数据被子表外键使用，子表对应外键所属记录随之删除。

    • Set null

      置空模式，父表字段操作之后，如果该数据被子表外键使用，子表对应的外键字段被置空。

      外键置空的前提是对应字段允许为空，否则外键创建不成功。

    删除的时候子表置空，更新的时候子表级联：

    

• 自动增长
  自增列只能有一个，并且必须设置为key

  如果没有指定就取当前id最大值+1，如果指定了就直接插入或者更新数据子

  自增值在数据库操作失败后也会发生改变，从而影响后续自增列的值

  <列名><属性值> auto_increment

  

  • 如果有多个自增列会报错

  Incorrect table definition; there can be only one auto column and it must be defined as a key
  表定义不正确；只能有一个自动列，必须将其定义为键
  

  在IDEA可视化工具中创建多个自增列出现报错

  

  • 自增键自增详解

    自增值修改机制

    假设数据表当前的自增值是 Y，在插入一行数据的时候，数据库的执行情况如下：

    • 如果插入数据时自增字段指定了具体的值 X，就直接使用值 X 填到该自增字段，该表的自增值是否修改的判断如下：
      • 若 X<Y，则该表的自增值不变；
      • 若 X≥Y，则需要修改该表的自增值为 X + auto_increment_increment（步长）。
    • 如果插入数据时自增字段未指定值或指定为 0、null，则把该表当前的自增值（即 Y）填到自增字段，并修改该表的自增值为 Y + auto_increment_increment（步长）。

    实际上，MySQL 维护着两个系统参数：auto_increment_offset 和 auto_increment_increment，分别用来表示自增的初始值和步长，默认值都为 1。MySQL 修改数据表自增值是从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。

    自增值的修改时机

    **数据表的自增值修改实际上是在真正插入数据的操作之前。如果没有插入成功，MySQL 也不会将自增值再改回去。也就是说，自增主键有可能会出现不连续的情况。**比如在插入数据时出错（如违反唯一键约束），或者事务回滚的情况下，都会导致自增主键不连续。

  • 设置自增键的初始值

    

  • 自增列的自增步长是可以改变的, 不常用所以不赘述了。

• 列中的值唯一

  一张表往往有很多字段需要唯一性，数据不能重复；但是一张表只能有一个字段为主键，那么唯一键(unique key)，就可以解决表中有多个字段需要唯一性约束的问题。

  使用unique key标识

  

  

• 插入列数据前进行条件检查
  check(列值需要符合的条件)，符和条件时插入数据。当插入的数据符合列的条件时插入数据
  
  

②数据表信息修改

a. 修改表名

​ alter table 表名 rename 新表名

b. 增加或删除或修改列

• 增加

  ALTER TABLE table
  ADD [COLUMN] column_name_1 column_1_definition [FIRST|AFTER existing_column],
  ADD [COLUMN] column_name_2 column_2_definition [FIRST|AFTER existing_column],
  ···;
  

• 删除

  ALTER TABLE <表名>
  DROP COLUMN column_1,
  DROP COLUMN column_2,
  

• 修改列

  • 修改列名

  ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件
  

  
  • 还可以设置列的默认值，是否是主键，唯一键，外码等

d. 获取表的基本信息

show columns from <表名>

③数据表信息修改

drop table 表名