MySQL数据库_table
1.创建数据表:create table语句
语法格式1:create table <表名>
MariaDB [db_work]> create [temporary] table [if not exists] <表名>
-> (表定义选项,...)
-> [表选项]
-> [分区选项];
-
CREATE [temporary] TABLE:用于(临时)创建给定名称的数据表,必须拥有数据表CREATE的权限。
-
[IF NOT EXISTS]:表被创建到当前的数据库中,若表已存在、没有当前数据库或者数据库不存在,则会出现错误。此选项可以用来避免数据表已经存在而重复创建的错误。
-
<表名>:指定要创建数据表的名称,在CREATE TABLE之后给出,必须符合标识符命名规则。表名被指定为db_name.tbl_name,以便在特定的数据库中创建表。无论是否有当前数据库,都可以通过这种方式创建。在当前数据库中创建表时,可以省略db-name。如果使用加引号的识别名(也可以不加引号),则应对数据库和表名称分别加引号。例如,'mydb'.'mytbl'是合法的,但 'mydb.mytbl' 不合法。
-
<表定义选项>:表创建定义,由列名(column_name)、列的定义(column_definition)以及可能的空值说明、完整性约束或表索引组成。
语法格式2:
MariaDB [db_work]> create [temporary] table [if not exists] <表名>
-> [(
-> 字段1 数据类型1,
-> 字段2 数据类型2,
-> ……
-> 字段n 数据类型n,
-> )]
-> [表选项]
-> [分区选项]
-> 选择语句;
语法格式3:
MariaDB [db_work]> create [temporary] table [if not exists] <表名>
->{like <旧表名1> | (like <旧表名2>)};
MariaDB [(none)]> use db_work; #在创建数据表前需要先指定数据库
Database changed
MariaDB [db_work]> create table tab_work
-> (
-> stu_id int(5),
-> stu_name varchar(10),
-> stu_gender char(2),
-> stu_age int(3),
-> stu_tel int(15)
-> );
Query OK, 0 rows affected (0.08 sec)
MariaDB [db_work]> show tables; #查看数据表是否创建成功
+-------------------+
| Tables_in_db_work |
+-------------------+
| tab_work |
+-------------------+
1 row in set (0.00 sec)
2.删除数据表:drop table语句
语法格式:
MariaDB [db_work]> drop [temporary] table [if not exists] <表名1> [,表名2,表名3,……] [restrict | cascade];
MariaDB [(none)]> use db_work; #在删除数据表前需要先指定数据库
Database changed
MariaDB [db_work]> show tables; #查看当前数据库里有哪些数据表
+-------------------+
| Tables_in_db_work |
+-------------------+
| tab_life |
| tab_work |
+-------------------+
2 rows in set (0.01 sec)
MariaDB [db_work]> drop table tab_life; #删除指定数据表
Query OK, 0 rows affected (0.01 sec)
MariaDB [db_work]> show tables; #查看删除之后数据库里有哪些表,删除成功
+-------------------+
| Tables_in_db_work |
+-------------------+
| tab_work |
+-------------------+
1 row in set (0.00 sec)
3.查询数据表:show语句
(1)查看数据库中的所有表
语法格式:MariaDB [(mysql)]> show tables;
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> show tables; #查看mysql数据库里的所有表,查表之前必须先选中指定数据库,否则会报错
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
(2)查看某个表结构:show create table(以SQL语句形式显示)或者describe/desc语句(以表格形式显示)
语法格式1:MariaDB [(none)]> describe <表名>; #describe可缩写成desc
语法格式2:MariaDB [(none)]> show create table <表名>;
MariaDB [(none)]> use db_work; #在创建数据表前需要先指定数据库
Database changed
MariaDB [db_work]> show create table tab_work; #以SQL语句形式显示tab_work数据表
+----------+---------------------------+
| Table | Create Table |
+----------+---------------------------+
| tab_work | CREATE TABLE `tab_work` (
`stu_id` int(5) DEFAULT NULL,
`stu_name` varchar(10) DEFAULT NULL,
`stu_gender` char(2) DEFAULT NULL,
`stu_age` int(3) DEFAULT NULL,
`stu_tel` int(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+----------+---------------------------+
1 row in set (0.00 sec)
MariaDB [db_work]> desc tab_work; #以表格形式显示tab_work数据表
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| stu_id | int(5) | YES | | NULL | |
| stu_name | varchar(10) | YES | | NULL | |
| stu_gender | char(2) | YES | | NULL | |
| stu_age | int(3) | YES | | NULL | |
| stu_tel | int(15) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.06 sec)
4.修改数据表:alter table语句
语法格式:
MariaDB [db_work]> alter table <表名>
-> { add <字段> <类型>
-> | change <旧字段> <新字段> <新数据类型>
-> | alter <字段> {set default <默认值> | drop default}
-> | modify <字段> <数据类型>
-> | drop <字段>
-> | rename [to] <新表名>
-> | [DEFAULT] character set <字符集名>
-> | [DEFAULT] collate <校对规则名> };
-
修改表名:MariaDB [db_work]> alter table <旧表名> rename [to] <新表名>;
MariaDB [db_work]> show tables; #查看数据表的名称
+-------------------+
| Tables_in_db_work |
+-------------------+
| tab_work |
+-------------------+
1 row in set (0.00 sec)
MariaDB [db_work]> alter table tab_work rename tab_students; #修改数据表名称
Query OK, 0 rows affected (0.01 sec)
MariaDB [db_work]> show tables; #修改成功
+-------------------+
| Tables_in_db_work |
+-------------------+
| tab_students |
+-------------------+
1 row in set (0.01 sec)
-
修改表字符集:MariaDB [db_work]> alter table <表名> [default] character set <字符集名> [default] collate <校对规则名>;
MariaDB [db_work]> alter table tab_students #修改表字符集及校对规则
-> default charset gb2312
-> default collate gb2312_chinese_ci;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db_work]> show create table tab_students;
+--------------+--------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------+
| tab_students | CREATE TABLE `tab_students` (
`stu_id` int(5) DEFAULT NULL,
`stu_name` varchar(10) DEFAULT NULL,
`stu_gender` char(2) DEFAULT NULL,
`stu_age` int(3) DEFAULT NULL,
`stu_tel` int(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb312 |
+--------------+--------------------------------------------+
1 row in set (0.01 sec)
-
在开头添加字段:MariaDB [db_work]> alter table <表名> add <新字段名> <数据类型> [约束条件] first;
MariaDB [db_work]> alter table tab_students add id int(5) first; #在字段开头添加id字段
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db_work]> show create table tab_students;
+--------------+--------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------+
| tab_students | CREATE TABLE `tab_students` (
`id` int(5) DEFAULT NULL,
`stu_id` int(5) DEFAULT NULL,
`stu_name` varchar(10) DEFAULT NULL,
`stu_gender` char(2) DEFAULT NULL,
`stu_age` int(3) DEFAULT NULL,
`stu_tel` int(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb312 |
+--------------+--------------------------------------------+
1 row in set (0.01 sec)
-
在中间添加字段:MariaDB [db_work]> alter table <表名> add <新字段名> <数据类型> [约束条件] after <已存在的某字段>;
MariaDB [db_work]> alter table tab_students add stu_qq int(15) after stu_age; #在stu_age字段后添加stu_qq字段
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db_work]> show create table tab_students;
+--------------+--------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------+
| tab_students | CREATE TABLE `tab_students` (
`id` int(5) DEFAULT NULL,
`stu_id` int(5) DEFAULT NULL,
`stu_name` varchar(10) DEFAULT NULL,
`stu_gender` char(2) DEFAULT NULL,
`stu_age` int(3) DEFAULT NULL,
`stu_qq` int(15) DEFAULT NULL,
`stu_tel` int(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb312 |
+--------------+--------------------------------------------+
1 row in set (0.01 sec)
-
在末尾添加字段:MariaDB [db_work]> alter table <表名> add <新字段名> <数据类型> [约束条件];
MariaDB [db_work]> alter table tab_students add stu_address char(20); #在字段末尾添加stu_address字段
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db_work]> show create table tab_students;
+--------------+--------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------+
| tab_students | CREATE TABLE `tab_students` (
`id` int(5) DEFAULT NULL,
`stu_id` int(5) DEFAULT NULL,
`stu_name` varchar(10) DEFAULT NULL,
`stu_gender` char(2) DEFAULT NULL,
`stu_age` int(3) DEFAULT NULL,
`stu_qq` int(15) DEFAULT NULL,
`stu_tel` int(15) DEFAULT NULL,
`stu_address` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb312 |
+--------------+--------------------------------------------+
1 row in set (0.01 sec)
-
修改字段名称:MariaDB [db_work]> alter table <表名> change <旧字段名> <新字段名> <新数据类型>;
MariaDB [db_work]> alter table tab_students change stu_address stu_addr char(20); #将stu_address字段修改为stu_addr字段
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db_work]> show create table tab_students;
+--------------+--------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------+
| tab_students | CREATE TABLE `tab_students` (
`id` int(5) DEFAULT NULL,
`stu_id` int(5) DEFAULT NULL,
`stu_name` varchar(10) DEFAULT NULL,
`stu_gender` char(2) DEFAULT NULL,
`stu_age` int(3) DEFAULT NULL,
`stu_qq` int(15) DEFAULT NULL,
`stu_tel` int(15) DEFAULT NULL,
`stu_addr` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb312 |
+--------------+--------------------------------------------+
1 row in set (0.01 sec)
-
修改字段数据类型:MariaDB [db_work]> alter table <表名> modify <字段名> <数据类型>;
MariaDB [db_work]> alter table tab_students modify stu_addr varchar(20); #将stu_addr字段的数据类型修改为varchar(20)
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db_work]> show create table tab_students;
+--------------+--------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------+
| tab_students | CREATE TABLE `tab_students` (
`id` int(5) DEFAULT NULL,
`stu_id` int(5) DEFAULT NULL,
`stu_name` varchar(10) DEFAULT NULL,
`stu_gender` char(2) DEFAULT NULL,
`stu_age` int(3) DEFAULT NULL,
`stu_qq` int(15) DEFAULT NULL,
`stu_tel` int(15) DEFAULT NULL,
`stu_addr` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb312 |
+--------------+--------------------------------------------+
1 row in set (0.01 sec)
-
修改字段位置:MariaDB [db_work]> alter table <表名> modify <字段名1> <数据类型> after <字段名2>;
MariaDB [db_work]> alter table tab_students modify stu_qq int(15) after stu_tel; #将stu_qq字段换到stu_tel字段后面
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db_work]> show create table tab_students;
+--------------+--------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------+
| tab_students | CREATE TABLE `tab_students` (
`id` int(5) DEFAULT NULL,
`stu_id` int(5) DEFAULT NULL,
`stu_name` varchar(10) DEFAULT NULL,
`stu_gender` char(2) DEFAULT NULL,
`stu_age` int(3) DEFAULT NULL,
`stu_tel` int(15) DEFAULT NULL,
`stu_qq` int(15) DEFAULT NULL,
`stu_addr` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb312 |
+--------------+--------------------------------------------+
1 row in set (0.01 sec)
-
删除字段:MariaDB [db_work]> alter table <表名> drop <字段名>;
MariaDB [db_work]> alter table tab_students drop id; #删除id字段
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db_work]> show create table tab_students;
+--------------+--------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------+
| tab_students | CREATE TABLE `tab_students` (
`stu_id` int(5) DEFAULT NULL,
`stu_name` varchar(10) DEFAULT NULL,
`stu_gender` char(2) DEFAULT NULL,
`stu_age` int(3) DEFAULT NULL,
`stu_tel` int(15) DEFAULT NULL,
`stu_qq` int(15) DEFAULT NULL,
`stu_addr` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb312 |
+--------------+--------------------------------------------+
1 row in set (0.01 sec)