mysql表操作

创建表

create table student(
  id  int(11),
  name  varchar(20),
  age int(11)
);

查看库中所有的表

mysql> show tables;
+-----------------+
| Tables_in_learn |
+-----------------+
| edu_user        |
| student         |
+-----------------+

查看建表语句

mysql> show create table student;
+---------+----------------------------------+
| Table   | Create Table                                                                                                                                               |
+---------+----------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------+

查看表结构

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

修改表名

mysql> alter table student rename to h_student;
mysql> show tables;
+-----------------+
| Tables_in_learn |
+-----------------+
| edu_user        |
| h_student       |
+-----------------+

修改基线名

alter table h_student change name stu_name varchar(20);
mysql> desc h_student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| stu_name | varchar(20) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

修改前端的数据类型

alter table h_student modify id int(20);
mysql> desc h_student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(20)     | YES  |     | NULL    |       |
| stu_name | varchar(20) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

添加和删​​除预设

添加
alter table h_student add grade float;

删除
alter table h_student drop grade;

修改基线的位置

mysql> desc h_student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(20)     | YES  |     | NULL    |       |
| stu_name | varchar(20) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

alter table h_student modify stu_name varchar(20) first;
alter table h_student modify id int(11) after age;

mysql> desc h_student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_name | varchar(20) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
| id       | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

备份数据表

select * into base_job_bak_2070106 (当天日期) from base_job

删除数据表

drop table h_student;

删除所有表

mysql> SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='sonar';
+---------------------------------------+
| CONCAT('drop table ',table_name,';')  |
+---------------------------------------+
| drop table active_rule_parameters;    |
| drop table active_rules;              |
| drop table alm_app_installs;          |
| drop table analysis_properties;       |
| drop table ce_activity;               |
| drop table ce_queue;                  |
| drop table ce_scanner_context;        |
| drop table ce_task_characteristics;   |
| drop table ce_task_input;             |
| drop table ce_task_message;           |
| drop table default_qprofiles;         |
| drop table deprecated_rule_keys;      |
| drop table duplications_index;        |
| drop table es_queue;                  |
| drop table event_component_changes;   |
| drop table events;                    |
| drop table file_sources;              |
| drop table group_roles;               |
| drop table groups;                    |
| drop table groups_users;              |
| drop table internal_component_props;  |
| drop table internal_properties;       |
| drop table issue_changes;             |
| drop table issues;                    |
| drop table live_measures;             |
| drop table manual_measures;           |
| drop table metrics;                   |
| drop table notifications;             |
| drop table org_qprofiles;             |
| drop table org_quality_gates;         |
| drop table organization_alm_bindings; |
| drop table organization_members;      |
| drop table organizations;             |
| drop table perm_templates_groups;     |
| drop table perm_templates_users;      |
| drop table perm_tpl_characteristics;  |
| drop table permission_templates;      |
| drop table plugins;                   |
| drop table project_alm_bindings;      |
| drop table project_branches;          |
| drop table project_links;             |
| drop table project_mappings;          |
| drop table project_measures;          |
| drop table project_qprofiles;         |
| drop table projects;                  |
| drop table properties;                |
| drop table qprofile_changes;          |
| drop table qprofile_edit_groups;      |
| drop table qprofile_edit_users;       |
| drop table quality_gate_conditions;   |
| drop table quality_gates;             |
| drop table rule_repositories;         |
| drop table rules;                     |
| drop table rules_metadata;            |
| drop table rules_parameters;          |
| drop table rules_profiles;            |
| drop table schema_migrations;         |
| drop table snapshots;                 |
| drop table user_properties;           |
| drop table user_roles;                |
| drop table user_tokens;               |
| drop table users;                     |
| drop table webhook_deliveries;        |
| drop table webhooks;                  |
+------------------------------