SQL库表级操作

SQL库表级操作

数据库操作

创建数据库

CREATE

CREATE关键词被用来作为创建数据库和数据表的关键词。DATABASE顾名思义就是数据库的意思。

数据库名称可以用以下符号包裹 ：

CREATE DATABASE test;
CREATE DATABASE 'test';
CREATE DATABASE `test`;

数据库是不允许同名的，我们可能会出现名字不小心输一样了的情况,通过下面方式避免错误。

 CREATE DATABASE IF NOT EXISTS test;

CHARACTER

大多数时候创建数据库需要指定编码格式,同时我们可能也需要定义规则:

--UTF-8
CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
--GBK
CREATE DATABASE `test` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci

COLLATE

看完上述例子，COLLATE可能令你有所疑惑。我个人感觉COLLATE就类似于一种规则。它是与编码格式相辅相成的，每一个编码格式都有它自己的规则比如排序的规则，比较的规则，当然这些仅仅正对于字符串类型，所以在对字符串字段创建索引也会被这种规则限制。

下面的代码可以查看所有的规则以及其适用的编码格式。

show collation;

我们国人常用的或者说是默认的是UTF8编码下的utf8mb4_general_ci。同时我们可以注意加了ci作为后缀 的规则往往指的是大小写无关性。

该关键字的应用范围很广，可以用在示例级别、库级别、表级别、列级别、以及在SQL中指定。

最后总结一下：使用最多的MySQL数据库默认规则如下：

--5.7及以下
默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。
--5.8及以上
默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。

使用数据库

USE test;

查询数据库

查询该服务器下所有数据库

SHOW DATABASES;

模糊查询数据库

一个_相当于一个字符，一个%相当于无数个字符。

--寻找名称为test的数据库
SHOW DATABASES LIKE 'test';
--寻找带有est的五位数的数据库
SHOW DATABASES LIKE '_est_';
--寻找带有es的数据库
SHOW DATABASES LIKE '%es%';

查询数据库属性

可以查看当前数据库的名称，编码

SHOW CREATE DATABASE test;

修改数据库

注意编码格式需要加SET关键词，矫正规则不需要。

ALTER DATABASE test
DEFAULT CHARACTER SET gbk 
DEFAULT COLLATE gbk_chinese_ci;

删除数据库

DROP DATABASE IF EXISTS `test`;

当然也可以不做判断直接删除数据库。

DROP DATABASE test;

导出数据库

root改为自己的账户名称,注意把test改为自己的数据库名称，test.sql改为导出的文件名称。

windows/Linux下都可以使用。

导出数据库的结构

--add-drop-table命令会在每一个成绩表的前边加入删除表的操作。

mysqldump -uroot -p -d --add-drop-table test >d:/test.sql

导出数据库的结构和数据

mysqldump -uroot -p test > test.sql

导入数据库

命令行界面，创建并选择数据库之后，source跟sql文件路径即可：

source d:/test.sql

或者创建并选择数据库之后：

mysqldump -uroot -p test< d:/test.sql

到这里数据库操作就算是结束了，那么在介绍表操作之前我们先看一些常用的数据类型。

数据表操作

创建数据表

表属性

ENGINE=INNODB: 该表的数据驱动

CHARSET: 该数据表的字符编码格式

DEFAULT: 默认值

COLLATE: 矫正规则

列属性

primary key: 主键,主键具有唯一性，且不能为空。

NOT NULL: 该字段是否允许空值

AUTO_INCREMENT: 该行的值自增(通常用于主键自增)

defalut: 默认值，默认值会在没有赋值的时候使用。

UNIQUE: 值唯一不能重复。

CHARSET SET utf8mb4为某一列进行编码设置。

CREATE TABLE `products_test` (
  `product_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `quantity_in_stock` INT(11) CHARSET SET utf8mb4 NOT NULL,
  `unit_price` DECIMAL(4,2) NOT NULL DEFAULT 0,--默认为0
  PRIMARY KEY (`product_id`)
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查询数据表

查询该数据库下所有数据表

SHOW TABLES;

查询表结构

DESC p_test;

查询表引擎

SHOW CREATE TABLE p_test;

修改数据表

修改数据表名称

ALTER TABLE preson_test RENAME TO p_test;

修改字符集

ALTER TABLE preson_test
	CHARACTER SET UTF8;

修改存储引擎

ALTER TABLE p_test ENGINE=InnoDB

添加/删除字段

--添加新字段 默认放到最后
ALTER TABLE tb_student ADD phone INT;
--添加新字段到第一个
ALTER TABLE p_test ADD test_id INT FIRST;
--添加新字段到某个字段后边
ALTER TABLE p_test ADD sex VARCHAR(3) AFTER old_name;
--删除字段
ALTER TABLE tb_student DROP password;
--同时进行多个操作
ALTER TABLE p_test ADD sex VARCHAR(3) AFTER old_name,
DROP password;;

修改数据表字段

--修改字段名，p_test是表名 old_name是老名字 new_name是修改后的名字 最后是数据类型
ALTER TABLE p_test CHANGE old_name new_name VARCHAR(25);
--修改数据类型 p_test是表名 new_name是需要修改的字段名 最后是修改后的数据类型
ALTER TABLE p_test MODIFY new_name CHAR(20)；
--修改字段的排列位置 p_test是表名 new_name是需要修改的字段名 然后是修改后的数据类型 最后是想要的位置
alter table p_test MODIFY new_name CHAR(20) first;
--将字段放在什么字段之后 
alter table p_test MODIFY new_name CHAR(20) after age;

删除数据表

DROP TABLE 'products';
--若是数据库存在用此种方法比较合适
DROP TABLE IF EXISTS `products`;

数据表复制

--只有数据没有表结构
CREATE TABLE p1 AS SELECT * FROM p_test;
--有表结构但是没有数据
CREATE TABLE p2 LIKE p_test;

表之间的关系

举个很简单的例子:

我们有一个顾客表,那么理论上我们的订单表应该是与顾客表相关联的。也就是说,订单表是依赖于顾客表实现的。

那么我们会发现一些问题。如果我们对一个顾客的信息做了更改，那么对应的订单表应该也需要更改。如果我们删除了一个顾客的信息，那么他的订单信息我们该怎么办。

CREATE TABLE orders(
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY fk_orders_customers(customer_id)
   		REFERENCES customers (customer_id)--注意此处是主键
    	ON UPDATE CASCADE
    	ON DELETE NO ACTION
);

这样以后,我们就可以发现一个巨大的情况，当我们对客户表做删除的时候，我们无法进行。因为我们必须先把依赖于它的订单表删除才行。

添加/删除主键约束

alter table persons 
	DROP PRIMARY KEY,
	ADD PRIMARY KEY (customer_id);

添加/删除外键约束

alter table persons 
	DROP FOREIGN KEY fk_orders_customers,
	ADD FOREIGN KEY fk_orders_customers (customer_id);

字符集和排序规则

查看所有的字符集。

SHOW CHARSET;

MySQL5以上基本默认UTF-8;

数据引擎

MyISAM 5.5以前很流行不支持事务。

InnoDB 支持事务,支持外键。

重构存储引擎大概率要重构数据表。