GreatSQL社区

搜索

KAiTO

0基础学MySQL数据库—从小白到大牛(9)创建和管理表

KAiTO 已有 336 次阅读2022-10-9 16:40 |个人分类:零基础学习数据库|系统分类:其他

一、创建数据库

启动MySQL服务之后,输入以下命令连接到MySQL服务器:

[root@zhyno1 ~]# mysql -uroot -pmysql代表客户端命令、-u后面跟的是用户名、-p表示需要输入的密码
  • 如果登录成功,会返回 mysql>的提示符。和以下的欢迎界面 欢迎使用MySQL监视器。命令以结尾;或\g。你的MySQL连接id是32服务器版本:5.6.50-log Source distribution版权所有(c)2000、2020,Oracle和/或其附属公司。保留所有权利。Oracle是Oracle Corporation和/或其附属公司。其他名称可能是其各自的商标业主。键入“help;”或“\h”寻求帮助。键入“\c”以清除当前输入语句。 在mysql>提示符后的SQL语句都需要以;或者\g结尾

创建库命令:

创库命令,使用此方法创建,使用的是默认的字符集CREATE DATABASE dbname;创建数据库且带上字符集CREATE DATABASE dbname2 CHARACTER SET 'utf8';查看数据库支持的字符集SHOW VARIABLES LIKE 'character_%';+--------------------------+-----------------------------------+| Variable_name            | Value                             |+--------------------------+-----------------------------------+| character_set_client     | utf8                              || character_set_connection | utf8                              || character_set_database   | utf8mb4                           || character_set_filesystem | binary                            || character_set_results    | utf8                              || character_set_server     | utf8mb4                           || character_set_system     | utf8                              || character_sets_dir       | /www/server/mysql/share/charsets/ |+--------------------------+-----------------------------------+如果创建的数据库存在,则不会创建且不会报错,反之则创建CREATE DATABASE IF NOT EXISTS dbname;查看数据库的字符集show create database zhytest;+----------+------------------------------------------------------------------+| Database | Create Database                                                  |+----------+------------------------------------------------------------------+| zhytest  | CREATE DATABASE `zhytest` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+------------------------------------------------------------------+utf8便是数据库的字符集查看当前使用的数据库SELECT DATABASE() FROM DUAL;+------------+| DATABASE() |+------------+| zhytest    |+------------+查看当前数据库下保存的数据表SHOW TABLES ;查看别的数据下保存的数据表;SHOW TABLES FROM zhytest;修改数据库字符集ALTER DATABASE mytest2 CHARACTER SET 'utf8';

现在创建一个库叫test1;

mysql> CREATE DATABASE test1;Query OK, 1 row affected (0.00 sec)

创建成功后会返回Query OK, 1 row affected (0.00 sec),首先

  • Query OK 表示上面命令执行成功
  • 1 row affected 表示只影响了数据库中一行记录
  • 0.00 sec 记录操作时间

如果需要查看数据库中有那些库可以用

mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test1              || zhy                || zhytest            |+--------------------+6 rows in set (0.00 sec)
  • information_schema 主要存储系统中一些数据库对象信息,比如用户表信息,列信息,权限信息,字符集信息,分区信息等。每个用户都可以查看这个表,但是根据权限不同,查看的内容也不一样。
  • performance_schema MySQL引入的系统库,用于存储系统性能相关的动态参数表。
  • mysql 存储系统用户的权限信息。

在查看完已有数据库后,选择要操作的数据库

USE dbname;例如选择test1mysql> USE test1;Database changed再用以下命令来查看test1库中创建的表mysql> SHOW TABLES;Empty set (0.00 sec)因为这个库下面没有表,所以显示Empty set那我们查看一下mysql表USE mysql;mysql> SHOW TABLES;+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              || db                        || event                     || time_zone_transition      || time_zone_transition_type || user                      |+---------------------------+6 rows in set (0.00 sec)表太多这边做删减

二、删除库

方式1DROP DATABASE dbname;方式2-如果要删除的数据库存在,则删除成功,如果不存在,则默默结束,不会报错;DROP DATABASE IF EXISTS dbname;例如要删除test1mysql> DROP DATABASE IF EXISTS test1;Query OK, 0 rows affected (0.00 sec)
  • 0 rows affected这个提示含义是前一次MySQL操作所影响记录行数,通常只对增删改查操作生效,drop等DDL操作通常显示0 rows affected
  • 注意!删除库后,库下的所有表都会被全部删除,所以删除前一定要检查仔细

三、创建数据表

在数据库中创建数据表基本语法如下:

CREATE TABLE [IF NOT EXISTS]tablename( COLUMN_NAME_1 COLUMN_TYPE_1 CONSTRAINTS COLUMN_NAME_2 COLUMN_TYPE_2 CONSTRAINTS 字段3, 数据类型 [约束条件] [默认值],   ... COLUMN_NAME_N COLUMN_TYPE_N CONSTRAINTS)
  • COLUMN_NAME_1 为列名
  • COLUMN_TYPE_1 列类型
  • CONSTRAINTS 列约束

举例:

  • 创建一个emp的表
  • 表里包括ename(姓名)、hiredate(雇佣日期)、sal(薪水)、deptno(序号)
  • 字段分别为varchar(10)、date、int(2)、int(2)
mysql> CREATE TABLE IF NOT EXISTS emp( ename varchar(10),hiredate date ,sal int(2),deptno int(2));Query OK, 0 rows affected (0.02 sec)查看表结构mysql> desc emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| ename    | varchar(10) | YES  |     | NULL    |       || hiredate | date        | YES  |     | NULL    |       || sal      | int(2)      | YES  |     | NULL    |       || deptno   | int(2)      | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)为了得到更全面的建表信息使用mysql> show create table emp \G;*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE `emp` (  `ename` varchar(10) DEFAULT NULL,  `hiredate` date DEFAULT NULL,  `sal` int(2) DEFAULT NULL,  `deptno` int(2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specified可以看到存储引擎和字符集,\G是指可以按照竖向排列

创建一个表zhytest2实现对zhytest的复制,但是不包括表数据的复制;

CREATE TABLE zhytest2AS SELECT *FROM zhytestWHRER 1 = 2;通过WHERE 1=2 实现每一条表的数据过来,都返回0

四、删除&清空表

删表命令如下:

DROP TABLE IF EXISTS tablename;要删除emp表如下DROP TABLE IF EXISTS emp;

清空表命令如下:

  • 不删除表结构,只删除内容
TRUNCATE TABLE zhytest;

五、修改表

使用 ALTER TABLE 语句可以实现:

  • 向已有的表中添加列
  • 修改现有表中的列
  • 删除现有表中的列
  • 重命名现有表中的列
ALTER TABLE tablename MODIFY [COLUMN] COLUMN_DEFINITION [FIRST / AFTER COL_NAME]例如修改emp表中的ename字段定义,将varcher(10)改为varcher(20)mysql> desc emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| ename    | varchar(10) | YES  |     | NULL    |       || hiredate | date        | YES  |     | NULL    |       || sal      | int(2)      | YES  |     | NULL    |       || deptno   | int(2)      | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> ALTER TABLE emp MODIFY ename varchar(20);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| ename    | varchar(20) | YES  |     | NULL    |       || hiredate | date        | YES  |     | NULL    |       || sal      | int(2)      | YES  |     | NULL    |       || deptno   | int(2)      | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

那如果我们要添加字段要怎么操作呢:

ALTER TABLE tablename ADD [COLUMN] COLUMN_DEFINITION [FIRST / AFTER COL_NAME]例如在emp中新增字段age,类型为int(3)mysql> ALTER TABLE emp ADD COLUMN age int(3);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> DESC emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| ename    | varchar(20) | YES  |     | NULL    |       || hiredate | date        | YES  |     | NULL    |       || sal      | int(2)      | YES  |     | NULL    |       || deptno   | int(2)      | YES  |     | NULL    |       || age      | int(3)      | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)默认添加到最后一个字段如果想要添加到其他位置:放在第一个ALTER TABLE emp ADD COLUMN age int(3) FIRST;放在指定字段后面 salALTER TABLE emp ADD COLUMN age int(3) AFTER sal;

删除字段怎么做呢

ALTER TABLE tablename DROP [COLUMN] col_name;删除age字段ALTER TABLE emp DROP [COLUMN] age;

那如何改名字段呢

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST / AFTER COL_NAME];将age改为age1并且字段类型修改为int(4)ALTER TABLE emp CHANGE age age1 int(4);

六、重命名表

方式1RENAME TABLE zhytestTO zzhhyytest;方式2ALTER TABLE myemp2RENAME myemp12;

七、常见数据类型

==标黄==为实际开发过程中实际最长使用的数据类型


类型类型举例
整数类型TINYINT、SMALLINT、MEDIUMINT、==INT(或INTEGER)==、BIGINT
浮点类型FLOAT、DOUBLE
定点数类型==DECIMAL==
位类型BIT
日期时间类型YEAR、TIME、==DATE==、DATETIME、TIMESTAMP
文本字符串类型CHAR、==VARCHAR==、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型ENUM
集合类型SET
二进制字符串类型BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型JSON对象、JSON数组
空间数据类型单值:GEOMETRY、POINT、LINESTRING、POLYGON; 集合:MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

八、DCL中COMMIT和ROLLBACK

  • COMMIT提交数据 一旦执行COMMUT,则数据就被永久保留在了数据库中,意味着数据不可用回滚。
  • ROLLBACK回滚数据 一旦执行了ROLLBACK,则可以实现数据的回滚,回滚到最近一次的COMMIT之后。

九、对比TRUNCATE TABLE和DELETE FROM

相同点:都可以实现对表中所有数据的删除,同时保留表结构。
不同点:TRUNCATE TABLE:一旦执行此操作,表数据全部清除。同时,==数据是不可以回滚的==。
DELETE FROM:一旦执行此操作,表数据可以全部清除(不带WHERE)。同时数据是==可以实现回滚的==。

十、DDL和DML的说明

  • DDL(数据定义语言)一旦执行,==就不可以回滚==
  • DCL(数据库操作语言)==默认情况下一旦执行==,不可以回滚。但是在执行DML之前,执行SET autocommit = FALSE则DML就可以回滚
DELETE FROM回滚到最近的一次COMMIT操作:提交数据COMMIT;查询表所有数据SELECT *FROM zhytest;执行开启可回滚指令SET autocommit = FALSE;清空表数据DELETE FROM myemp3;再次查看是否有数据SELECT *FROM zhytest; 回滚ROLLBACK;发现表中数据回来SELECT *FROM zhytest; TRUNCATE TABLE不做示范,无法回滚。

二者如何选择?
阿里开发规范:

【参考】TRUNCATE TABLE 比 DELETE FROM速度快,且使用的系统和事务日志资源少,但 TRUNCATE 无事务且不触发 TRIGGER,有可能造成事故,故不建议在开发代码中使用此语句。

说明:TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

  • 拓展1:阿里巴巴《Java开发手册》之MySQL字段命名 【==强制==】表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。 正例:aliyun_admin,rdc_config,level3_name 反例:AliyunAdmin,rdcConfig,level_3_name 【==强制==】禁用保留字,如 desc、range、match、delayed 等,请参考 MySQL 官方保留字。 【==强制==】表必备三字段:id, gmt_create, gmt_modified。 说明:其中 id 必为主键,类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 DATETIME 类型,前者现在时表示主动式创建,后者过去分词表示被动式更新。 【==推荐==】表的命名最好是遵循 “业务名称_表的作用”。 正例:alipay_task 、 force_project、 trade_config 【==推荐==】库名与应用名称尽量一致。 【==参考==】合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。 正例:无符号值可以避免误存负数,且扩大了表示范围。
  • 拓展2 MySQL8.0的原子化DDL 原子的意思就是不可拆分,简单来说就是每写的一个语句,都是一个整体,你的语句操作,要么成功,要么回滚。 在MySQL5.7中,一个删除表语句 假设我们没有book2这个表,但是有book1这个表
如果我们删除这两个表DROP TABLE book1,book2;数据库会报错ERROR 1051 (42S02): Unknown table 'mytest.book2'但是还是把book1给删除了

如果我们用MySQL8.0,就不会把book1给删除,因为8.0具有原子化,即指令都是一个整体,不可拆分,如果一个有错误,即会马上回滚,而不会执行。
原子化指的就是 ‘增删改查’ 等组成的事务是一个整体,要么全部执行成功,一旦有一个执行失败则进行回滚,全部都不进行执行,这个整体性不可分割类似于原子。这个只在MySQL8.0中体现。


评论 (0 个评论)

facelist

您需要登录后才可以评论 登录 | 立即注册

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
社区小助手
QQ群
GMT+8, 2024-3-28 17:10 , Processed in 0.012834 second(s), 8 queries , Redis On.
返回顶部