mysql 基本使用

最新推荐文章于 2023-11-04 12:26:54 发布

原创最新推荐文章于 2023-11-04 12:26:54 发布 · 332 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#杂碎 #mysql

开发专栏收录该内容

134 篇文章

订阅专栏

1. 建表

mysql> create database test;
Query OK, 1 row affected (0.02 sec)

mysql> use test;
Database changed

mysql> ALTER TABLE test_db COMMENT = 'test time table'
    -> ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_db        |
+----------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test_db MODIFY COLUMN update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_db;
Empty set (0.01 sec)

2. 描述表结构

mysql> DESCRIBE test_db;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(11)     | NO   | PRI | NULL              | auto_increment              |
| test_status | varchar(24) | YES  |     | NULL              |                             |
| create_time | datetime    | YES  |     | CURRENT_TIMESTAMP |                             |
| update_time | datetime    | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

3. 查看建表语句

mysql> SHOW CREATE TABLE test_db;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                         |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_db | CREATE TABLE `test_db` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_status` varchar(24) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='test time table' |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

4. 存储sql

# 1. 存储整个库
mysqldump -uroot -p test > /root/test/mysql-test/db/test.sql

# 2. 存储库的结构
mysqldump -uroot -p -d test > /root/test/mysql-test/db/test_structure.sql

# 3. 存储表的结构
 mysqldump -uroot -p -d test test_db > /root/test/mysql-test/db/test_tb_structure.sql

# 4. 存储表
mysqldump -uroot -p test test_db > /root/test/mysql-test/db/test_tb.sql

5. 使用sql生成表和数据

在新的地址

create database test;

use test;

show tables;

source /xxxx/xxx.sql

mysql> source test_db.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

6. 删除数据表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_db        |
| test_db1       |
| test_db11      |
+----------------+
3 rows in set (0.00 sec)

mysql> drop table test_db11;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_db        |
| test_db1       |
+----------------+
2 rows in set (0.00 sec)

mysql>