{"id":93,"date":"2023-05-24T19:14:21","date_gmt":"2023-05-24T11:14:21","guid":{"rendered":"https:\/\/jiachen.de.cool\/blog\/?p=93"},"modified":"2023-05-24T19:14:21","modified_gmt":"2023-05-24T11:14:21","slug":"%e6%95%b0%e6%8d%ae%e5%ae%9a%e4%b9%89%e8%af%ad%e8%a8%80ddl","status":"publish","type":"post","link":"https:\/\/jiachen.de.cool\/blog\/2023\/05\/24\/%e6%95%b0%e6%8d%ae%e5%ae%9a%e4%b9%89%e8%af%ad%e8%a8%80ddl\/","title":{"rendered":"\u6570\u636e\u5b9a\u4e49\u8bed\u8a00DDL"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Data Definition Language \u662fSQL\u8bed\u8a00\u96c6\u4e2d\u8d1f\u8d23\u6570\u636e\u7ed3\u6784\u5b9a\u4e49\u4e0e\u6570\u636e\u5e93\u5bf9\u8c61\u5b9a\u4e49\u7684\u8bed\u8a00\uff0c\u7531CREATE\u3001ALTER\u4e0eDROP\u4e09\u4e2a\u8bed\u6cd5\u6240\u7ec4\u6210\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> show databases;\n+--------------------+\n| Database           |\n+--------------------+\n| information_schema |\n| learning           |\n| learning3          |\n| mysql              |\n| performance_schema |\n| sys                |\n+--------------------+\n6 rows in set (0.02 sec)\n\nmysql> drop database learning3;\nQuery OK, 1 row affected (0.31 sec)\n\nmysql> create database learning3;\nQuery OK, 1 row affected (0.07 sec)\n\nmysql> use learning3;\nDatabase changed\nmysql> create table houses (name varchar(100) not null,\n    -> house_location varchar(100) not null,\n    -> purchasing_year varchar(100) not null);\nQuery OK, 0 rows affected (0.25 sec)\n\nmysql> show tables;\n+---------------------+\n| Tables_in_learning3 |\n+---------------------+\n| houses              |\n+---------------------+\n1 row in set (0.00 sec)\n\nmysql> desc houses;\n+-----------------+--------------+------+-----+---------+-------+\n| Field           | Type         | Null | Key | Default | Extra |\n+-----------------+--------------+------+-----+---------+-------+\n| name            | varchar(100) | NO   |     | NULL    |       |\n| house_location  | varchar(100) | NO   |     | NULL    |       |\n| purchasing_year | varchar(100) | NO   |     | NULL    |       |\n+-----------------+--------------+------+-----+---------+-------+\n3 rows in set (0.00 sec)\n\nmysql> alter table houses drop purchasing_year;\nQuery OK, 0 rows affected (0.51 sec)\nRecords: 0  Duplicates: 0  Warnings: 0\n\nmysql> desc houses;\n+----------------+--------------+------+-----+---------+-------+\n| Field          | Type         | Null | Key | Default | Extra |\n+----------------+--------------+------+-----+---------+-------+\n| name           | varchar(100) | NO   |     | NULL    |       |\n| house_location | varchar(100) | NO   |     | NULL    |       |\n+----------------+--------------+------+-----+---------+-------+\n2 rows in set (0.00 sec)\n\nmysql> alter table houses add purchasing_year varchar(100) after house_location;\nQuery OK, 0 rows affected (0.25 sec)\nRecords: 0  Duplicates: 0  Warnings: 0\n\nmysql> desc houses;\n+-----------------+--------------+------+-----+---------+-------+\n| Field           | Type         | Null | Key | Default | Extra |\n+-----------------+--------------+------+-----+---------+-------+\n| name            | varchar(100) | NO   |     | NULL    |       |\n| house_location  | varchar(100) | NO   |     | NULL    |       |\n| purchasing_year | varchar(100) | YES  |     | NULL    |       |\n+-----------------+--------------+------+-----+---------+-------+\n3 rows in set (0.00 sec)\n\nmysql> alter table houses modify purchasing_year char(100);\nQuery OK, 0 rows affected (0.49 sec)\nRecords: 0  Duplicates: 0  Warnings: 0\n\nmysql> desc houses;\n+-----------------+--------------+------+-----+---------+-------+\n| Field           | Type         | Null | Key | Default | Extra |\n+-----------------+--------------+------+-----+---------+-------+\n| name            | varchar(100) | NO   |     | NULL    |       |\n| house_location  | varchar(100) | NO   |     | NULL    |       |\n| purchasing_year | char(100)    | YES  |     | NULL    |       |\n+-----------------+--------------+------+-----+---------+-------+\n3 rows in set (0.00 sec)\n\nmysql> alter table houses change purchasing_year year  varchar(100);\nQuery OK, 0 rows affected (0.43 sec)\nRecords: 0  Duplicates: 0  Warnings: 0\n\nmysql> desc houses;\n+----------------+--------------+------+-----+---------+-------+\n| Field          | Type         | Null | Key | Default | Extra |\n+----------------+--------------+------+-----+---------+-------+\n| name           | varchar(100) | NO   |     | NULL    |       |\n| house_location | varchar(100) | NO   |     | NULL    |       |\n| year           | varchar(100) | YES  |     | NULL    |       |\n+----------------+--------------+------+-----+---------+-------+\n3 rows in set (0.00 sec)\n\nmysql> alter table houses rename to bighouses;\nQuery OK, 0 rows affected (0.24 sec)\n\nmysql> show tables;\n+---------------------+\n| Tables_in_learning3 |\n+---------------------+\n| bighouses           |\n+---------------------+\n1 row in set (0.00 sec)\n\nmysql> create table test(\n    -> id int(10) unsigned not null auto_increment,\n    -> name varchar(10) not null,\n    -> primary key (id)\n    -> );\nQuery OK, 0 rows affected (0.27 sec)\n\nmysql> desc test;\n+-------+------------------+------+-----+---------+----------------+\n| Field | Type             | Null | Key | Default | Extra          |\n+-------+------------------+------+-----+---------+----------------+\n| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |\n| name  | varchar(10)      | NO   |     | NULL    |                |\n+-------+------------------+------+-----+---------+----------------+\n2 rows in set (0.00 sec)\n\nmysql> insert into test (name) values ('abc');\nQuery OK, 1 row affected (0.06 sec)\n\nmysql> select * from test;\n+----+------+\n| id | name |\n+----+------+\n|  1 | abc  |\n+----+------+\n1 row in set (0.00 sec)\n\nmysql> truncate test;\nQuery OK, 0 rows affected (0.49 sec)\n\nmysql> select * from test;\nEmpty set (0.00 sec)\n\nmysql> desc test;\n+-------+------------------+------+-----+---------+----------------+\n| Field | Type             | Null | Key | Default | Extra          |\n+-------+------------------+------+-----+---------+----------------+\n| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |\n| name  | varchar(10)      | NO   |     | NULL    |                |\n+-------+------------------+------+-----+---------+----------------+\n2 rows in set (0.00 sec)\n\nmysql> show variables like 'autocommit';\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| autocommit    | ON    |\n+---------------+-------+\n1 row in set, 1 warning (0.11 sec)\n\nmysql> insert into test (name) values('abc');\nQuery OK, 1 row affected (0.05 sec)\n\nmysql> select * from test;\n+----+------+\n| id | name |\n+----+------+\n|  1 | abc  |\n+----+------+\n1 row in set (0.00 sec)\n\nmysql> delete from test;\nQuery OK, 1 row affected (0.04 sec)\n\nmysql> select * from test;\nEmpty set (0.00 sec)\n\nmysql> desc test;\n+-------+------------------+------+-----+---------+----------------+\n| Field | Type             | Null | Key | Default | Extra          |\n+-------+------------------+------+-----+---------+----------------+\n| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |\n| name  | varchar(10)      | NO   |     | NULL    |                |\n+-------+------------------+------+-----+---------+----------------+\n2 rows in set (0.00 sec)\n\nmysql> insert into test (name) values ('abc');\nQuery OK, 1 row affected (0.07 sec)\n\nmysql> select * from test;\n+----+------+\n| id | name |\n+----+------+\n|  2 | abc  |\n+----+------+\n1 row in set (0.00 sec)\n\nmysql> set global autocommit='off';\nQuery OK, 0 rows affected (0.03 sec)\n\nmysql> show global variables like 'autocommit';\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| autocommit    | OFF   |\n+---------------+-------+\n1 row in set, 1 warning (0.03 sec)\n\nmysql> delete from test;\nQuery OK, 1 row affected (0.07 sec)\n\nmysql> select * from test;\nEmpty set (0.00 sec)\n\nmysql> rollback;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> select * from test;\nEmpty set (0.00 sec)\n\nmysql> insert into test (name) values ('abc');\nQuery OK, 1 row affected (0.07 sec)\n\nmysql> select * from test;\n+----+------+\n| id | name |\n+----+------+\n|  3 | abc  |\n+----+------+\n1 row in set (0.00 sec)\n\nmysql> commit;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> delete from test;\nQuery OK, 1 row affected (0.06 sec)\n\nmysql> select * from test;\nEmpty set (0.00 sec)\n\nmysql> rollback;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> select * from test;\nEmpty set (0.00 sec)\n\nmysql> set session autocommit='off';\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> show variables like 'autocommit';\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| autocommit    | OFF   |\n+---------------+-------+\n1 row in set, 1 warning (0.00 sec)\n\nmysql> insert into test (name) values ('abc');\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> select * from test;\n+----+------+\n| id | name |\n+----+------+\n|  4 | abc  |\n+----+------+\n1 row in set (0.00 sec)\n\nmysql> commit;\nQuery OK, 0 rows affected (0.07 sec)\n\nmysql> delete from test;\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> select * from test;\nEmpty set (0.00 sec)\n\nmysql> rollback;\nQuery OK, 0 rows affected (0.02 sec)\n\nmysql> select * from test;\n+----+------+\n| id | name |\n+----+------+\n|  4 | abc  |\n+----+------+\n1 row in set (0.00 sec)\n\nmysql> truncate test;\nQuery OK, 0 rows affected (0.38 sec)\n\nmysql> select * from test;\nEmpty set (0.00 sec)\n\nmysql> rollback;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> select * from test;\nEmpty set (0.00 sec)\n\nmysql> drop table test;\nQuery OK, 0 rows affected (0.17 sec)\n\nmysql> show tables;\n+---------------------+\n| Tables_in_learning3 |\n+---------------------+\n| bighouses           |\n+---------------------+\n1 row in set (0.00 sec)\n\nmysql> drop table bighouses;\nQuery OK, 0 rows affected (0.18 sec)\n\nmysql> show tables;\nEmpty set (0.00 sec)\n\nmysql> show databases;\n+--------------------+\n| Database           |\n+--------------------+\n| information_schema |\n| learning           |\n| learning3          |\n| mysql              |\n| performance_schema |\n| sys                |\n+--------------------+\n6 rows in set (0.00 sec)\n\nmysql> drop database learning3;\nQuery OK, 0 rows affected (0.08 sec)\n\nmysql> show databases;\n+--------------------+\n| Database           |\n+--------------------+\n| information_schema |\n| learning           |\n| mysql              |\n| performance_schema |\n| sys                |\n+--------------------+\n5 rows in set (0.00 sec)<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Data Definition Language \u662fSQL\u8bed\u8a00\u96c6\u4e2d\u8d1f\u8d23\u6570\u636e\u7ed3\u6784\u5b9a\u4e49\u4e0e\u6570\u636e\u5e93\u5bf9\u8c61\u5b9a\u4e49\u7684\u8bed\u8a00\uff0c\u7531C&hellip; <\/p>\n<p><a class=\"moretag\" href=\"https:\/\/jiachen.de.cool\/blog\/2023\/05\/24\/%e6%95%b0%e6%8d%ae%e5%ae%9a%e4%b9%89%e8%af%ad%e8%a8%80ddl\/\">Read the full article<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[12,13],"class_list":["post-93","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-ddl","tag-13"],"_links":{"self":[{"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts\/93","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/comments?post=93"}],"version-history":[{"count":1,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts\/93\/revisions"}],"predecessor-version":[{"id":94,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/posts\/93\/revisions\/94"}],"wp:attachment":[{"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/media?parent=93"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/categories?post=93"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jiachen.de.cool\/blog\/wp-json\/wp\/v2\/tags?post=93"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}