MySQL对数据表整体的操作***

最新推荐文章于 2022-04-14 15:50:56 发布

thinker_1120

最新推荐文章于 2022-04-14 15:50:56 发布

阅读量360

点赞数

分类专栏：数据处理与分析

本文链接：https://blog.csdn.net/cymy001/article/details/79283479

版权

数据处理与分析专栏收录该内容

14 篇文章 3 订阅

订阅专栏

本文是个人学习笔记，内容主要包含数据表的存储方式简介，数据的导入与导出，数据表的创建、重命名与删除，数据表的结构更改，数据表的关联。学习文档来自易百教程：http://www.yiibai.com/mysql/

表的存储方式简介

表与存储引擎

MySQL为其表提供了各种存储引擎，每个存储引擎都有自己的优点和缺点，为表选择最合适的功能可以最大限度地提高数据库的性能。它们分别如下所示：
MyISAM：MyISAM表针对压缩和速度进行了优化。 MyISAM表也可以在平台和操作系统之间移植。MyISAM表的大小可达256TB，这个数据里是非常巨大的。此外，MyISAM表可以压缩为只读表以节省空间。在启动时，MySQL会检查MyISAM表是否有损坏，甚至在出现错误的情况下修复它们。

InnoDB：InnoDB表支持外键，提交，回滚，前滚操作。InnoDB表的大小最多可达64TB。像MyISAM一样，InnoDB表可以在不同的平台和操作系统之间移植。如果需要，MySQL还会在启动时检查和修复InnoDB表。

MERGE：MERGE表是将具有相似结构的多个MyISAM表组合到一个表中的虚拟表。MERGE存储引擎也被称为MRG_MyISAM引擎。 MERGE表没有自己的索引; 它会使用组件表的索。使用MERGE表，可以在连接多个表时加快性能。MySQL只允许您对MERGE表执行SELECT，DELETE，UPDATE和INSERT操作。如果在MERGE表上使用DROP TABLE语句，则仅删除MERGE规范。基础表不会受到影响。

MEMORY(HEAP)：内存表存储在内存中，并使用散列索引，使其比MyISAM表格快。内存表数据的生命周期取决于数据库服务器的正常运行时间。内存存储引擎以前称为HEAP。

ARCHIVE：归档存储引擎允许将大量用于归档目的的记录存储为压缩格式以节省磁盘空间。归档存储引擎在插入时压缩记录，并在读取时使用zlib库对其进行解压缩。归档表只允许INSERT和SELECT语句。 ARCHIVE表不支持索引，因此需要完整的表扫描来读取行。

CSV：CSV存储引擎以逗号分隔值(CSV)文件格式存储数据。 CSV表格提供了将数据迁移到非SQL应用程序(如电子表格软件)中的便捷方式。CSV表不支持NULL数据类型。此外，读操作需要全表扫描。

FEDERATED：FEDERATED存储引擎允许从远程MySQL服务器管理数据，而无需使用集群或复制技术。本地联合表不存储任何数据。从本地联合表查询数据时，数据将从远程联合表自动拉出。

数据的导入与导出

向数据库内导入数据

（1）向数据库里导入sql数据文件
在MySQL localhost创建yiibaidb数据库
这里写图片描述
导入yiibaidb.sql数据库

用select语句测试yiibaidb.sql数据库导入结果

（2）向数据库里导入csv数据文件
先在数据库mytestdb里创建要导入的数据表discounts（创建表时定义各列信息）：

use testdb;
CREATE TABLE discounts (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    expired_date DATE NOT NULL,
    amount DECIMAL(10 , 2 ) NULL,
    PRIMARY KEY (id)
);

这里写图片描述

从数据库导出数据

select * from discounts into outfile 'C:/mysql_root/discount.csv' fields enclosed by "" terminated by ';' escaped by "" lines terminated by '\r\n';

这里写图片描述

mysql -h ${HOSTSITE} -u ${USERNAME} -p${PASSWORD} -e "${select_sql}">localfile.format
wc -l localfile.format
sz localfile.format
df

表的创建、重命名与删除

CREATE TABLE创建表

create table [if not exist] table_name(column_list) engine=table_type;

（1）在CREATE TABLE子句之后指定创建的表的名称，表名在数据库中必须是唯一的。 IF NOT EXISTS是语句的可选部分，允许您检查正在创建的表是否已存在于数据库中。如果是这种情况，MySQL将忽略整个语句，不会创建任何新的表。强烈建议在每个CREATE TABLE语句中使用IF NOT EXISTS来防止创建已存在的新表而产生错误。
（2）在column_list部分指定表的列表。字段的列用逗号(，)分隔，下面会对column_list详细展开。
（3）为engine子句中的表指定存储引擎。可以使用任何存储引擎，如：InnoDB，MyISAM，CSV等。如果不明确声明存储引擎，MySQL将默认使用InnoDB。

#column_list的详细展开写法

column_name data_type[size] [not null|null] [default value] [auto_increment]

（2.1）column_name指定列的名称。每列具有特定数据类型和大小，例如：VARCHAR(255)。
（2.2）NOT NULL或NULL表示该列是否接受NULL值。
（2.3）DEFAULT值用于指定列的默认值。
（2.4）AUTO_INCREMENT指示每当将新行插入到表中时，列的值会自动增加。每个表都有一个且只有一个AUTO_INCREMENT列。

#设置表的主键特征列

primary key(col1,col2,...)

示例语句

CREATE TABLE IF NOT EXISTS tasks (
  task_id INT(11) NOT NULL AUTO_INCREMENT,
  subject VARCHAR(45) DEFAULT NULL,
  start_date DATE DEFAULT NULL,
  end_date DATE DEFAULT NULL,
  description VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (task_id)
) ENGINE=InnoDB;

DESC获得数据表列的信息描述

desc employees;

数据表的重命名

RENAME TABLE语句和ALTER TABLE语句都可以重命名表。不能使用RENAME TABLE语句来重命名临时表，但可以使用ALTER TABLE语句重命名临时表。

RENAME TABLE old_table_name_1 TO new_table_name_2,
             old_table_name_2 TO new_table_name_2;

ALTER TABLE tasks
RENAME TO work_items;
#Query OK, 0 rows affected (0.12 sec)

show tables;
#+----------------+
#| Tables_in_test |
#+----------------+
#| work_items     |
#+----------------+
#1 row in set (0.00 sec)

数据表的删除

drop table if exists table_name;

表的结构更改

show databases;
drop table if exists tasks;
CREATE TABLE tasks (
    task_id INT NOT NULL,
    subject VARCHAR(45) NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    description VARCHAR(200) NULL,
    PRIMARY KEY (task_id),
    UNIQUE INDEX task_id_unique (task_id ASC)
);
#Query OK, 0 rows affected (0.39 sec)

show tables;
#+----------------+
#| Tables_in_test |
#+----------------+
#| tasks          |
#+----------------+
#1 row in set (0.00 sec)

desc tasks;
#+-------------+--------------+------+-----+---------+-------+
#| Field       | Type         | Null | Key | Default | Extra |
#+-------------+--------------+------+-----+---------+-------+
#| task_id     | int(11)      | NO   | PRI | NULL    |       |
#| subject     | varchar(45)  | YES  |     | NULL    |       |
#| start_date  | date         | YES  |     | NULL    |       |
#| end_date    | date         | YES  |     | NULL    |       |
#| description | varchar(200) | YES  |     | NULL    |       |
#+-------------+--------------+------+-----+---------+-------+
#5 rows in set (0.01 sec)

ALTER TABLE tablename CHANGE COLUMN columnname columnname INT(11) NOT NULL AUTO_INCREMENT;——在表中插入新行，列的值自动增加1

ALTER TABLE tasks
CHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;
#Query OK, 0 rows affected (0.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

INSERT INTO tasks(subject,start_date,end_date,description)
VALUES('Learn MySQL ALTER TABLE',Now(),Now(),
       'Practicing MySQL ALTER TABLE statement');
INSERT INTO tasks(subject,start_date,end_date,description)
VALUES('Learn MySQL CREATE TABLE',Now(),Now(),
       'Practicing MySQL CREATE TABLE statement');

select * from tasks;
#+---------+---------------+------------+------------+----------------+
#| task_id | subject       | start_date | end_date   | description    |
#+---------+---------------+------------+------------+----------------+
#|       1 |  Learn Mysql  | 2018-02-08 | 2018-02-08 | Practice Mysql |
#|       2 |  Create table | 2018-02-08 | 2018-02-08 | Practice table |
#+---------+---------------+------------+------------+----------------+
#2 rows in set (0.00 sec)

ALTER TABLE tablename ADD COLUMN newcolumn DECIMAL(2,1) NULL AFTER existcolumn;——在表里添加一个新列

ALTER TABLE tasks 
ADD COLUMN complete DECIMAL(2,1) NULL
AFTER description;
#Query OK, 0 rows affected (0.51 sec)
#Records: 0  Duplicates: 0  Warnings: 0

desc tasks;
#+-------------+--------------+------+-----+---------+----------------+
#| Field       | Type         | Null | Key | Default | Extra          |
#+-------------+--------------+------+-----+---------+----------------+
#| task_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
#| subject     | varchar(45)  | YES  |     | NULL    |                |
#| start_date  | date         | YES  |     | NULL    |                |
#| end_date    | date         | YES  |     | NULL    |                |
#| description | varchar(200) | YES  |     | NULL    |                |
#| complete    | decimal(2,1) | YES  |     | NULL    |                |
#+-------------+--------------+------+-----+---------+----------------+
#6 rows in set (0.00 sec)

ALTER TABLE tablename DROP COLUMN columnname;——删除表的某一列

ALTER TABLE tasks
DROP COLUMN description;
#Query OK, 0 rows affected (0.60 sec)
#Records: 0  Duplicates: 0  Warnings: 0

desc tasks;
#+------------+--------------+------+-----+---------+----------------+
#| Field      | Type         | Null | Key | Default | Extra          |
#+------------+--------------+------+-----+---------+----------------+
#| task_id    | int(11)      | NO   | PRI | NULL    | auto_increment |
#| subject    | varchar(45)  | YES  |     | NULL    |                |
#| start_date | date         | YES  |     | NULL    |                |
#| end_date   | date         | YES  |     | NULL    |                |
#| complete   | decimal(2,1) | YES  |     | NULL    |                |
#+------------+--------------+------+-----+---------+----------------+
#5 rows in set (0.01 sec)

表关联

INNER JOIN表

select productcode,productname,textdescription from products t1 
inner join productlines t2 on t1.productline=t2.productline;

productcode,productname,textdescription列名可以取自表products或productlines
products是主表,inner join后接被连接表productlines,连接条件是将主表中的行与其他表中的行进行匹配
t1是products表别名
.是表限定符,后面接的是表t1里的列productline on子句后接连接条件
在上述on子句之后还可以加where条件

LEFT JOIN表

select c.customernumber,c.customername,ordernumber,o.status from customers c 
left join orders o on c.customernumber=o.customernumber;

select c.customernumber,c.customername,ordernumber,o.status from customers c 
left join orders o USING (customernumber) where ordernumber is null;   
#查找left join后,'右表orders'里ordernumber为null的用户,该结果表明左表里有的customernumber在右表里原本没有

left join子句将’左表customers’加入’右表orders’
on c.customernumber=o.customernumber等价于USING (customernumber)

c.customernumber,c.customername取自’customers表’
ordernumber,o.status取自’orders表’
根据customernumber列连接’左表customers’和’右表orders’:
(1)’左表customers’里customernumber数是122,’右表orders’里customernumber数是326;
(2)’左表customers’里有,但’右表orders’里customernumber显示为null的是24;
(3)left join后的表customernumber数是350

#select on where
select o.ordernumber,customernumber,productcode from orders o 
left join orderdetails d using (ordernumber) where ordernumber=10123;

#select on and
select o.ordernumber,customernumber,productcode from orders o 
left join orderdetails d on o.ordernumber=d.ordernumber and o.ordernumber=10123;

在on子句后的and和or与on同时对前面的集合起作用
而where先对集合进行on条件的抽取,再根据where的条件进行抽取

CROSS JOIN

结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。当连接的表之间没有关系时，会使用这种情况。

select * from t1 cross join t2;

自连接

在同一张表上自己连接自己，要执行自联接操作必须使用表别名来帮助MySQL在单个查询中区分左表与同一张表的右表。

select ifnull(concat(m.lastname,' ',m.firstname),'Top manager') as 'manager',
       concat(e.lastname,' ',m.firstname) as 'direct report' from employees e 
left join employees m on m.employeenumber=e.reportsto 
order by manager desc;

thinker_1120

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL对数据表整体的操作***

本文是个人学习笔记，内容主要包含数据表的存储方式简介，数据的导入与导出，数据表的创建、重命名与删除，数据表的结构更改，数据表的关联。学习文档来自易百教程：http://www.yiibai.com/mysql/表的存储方式简介表与存储引擎MySQL为其表提供了各种存储引擎，每个存储引擎都有自己的优点和缺点，为表选择最合适的功能可以最大限度地提高数据库的性能。它们分别如下所示： ...
复制链接

扫一扫