MySQL学习-使用MySQL(2)

使用MySQL


数据库相关操作SQL

创建数据库

SQL:CREATE DATABASE databse_name;

1
2
mysql> CREATE DATABASE study;
Query OK, 1 row affected (0.01 sec)

也可以在创建数据库的时候指定字符集

SQL:CREATE DATABASE databse_name DEFAULT CHARACTER SET UTF8;

1
2
mysql> CREATE DATABASE study DEFAULT CHARACTER SET UTF8;
Query OK, 1 row affected (0.00 sec)

显示数据库

SQL:SHOW DATABASES;

1
2
3
4
5
6
7
8
9
10
11
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db |
| mysql |
| performance_schema |
| study |
+--------------------+
5 rows in set (0.00 sec)

删除数据库

SQL:DROP DATABASE database_name;

1
2
mysql> DROP DATABASE study;
Query OK, 2 rows affected (0.02 sec)

选择数据库

在连接到MySQL时,没有任何数据库供我们使用。在想操作数据库前,需要选择一个数据库。

SQL:USE database_name;

1
2
mysql> USE study;
Database changed

表相关操作SQL

创建表

SQL:CREATE TABLE table_name (column_name column_type);

1
2
3
4
5
mysql> CREATE TABLE tb (
-> id INT AUTO_INCREMENT,
-> name VARCHAR(10),
-> PRIMARY KEY(id));
Query OK, 0 rows affected (0.01 sec)

创建表也可以指定数据库引擎和字符集

SQL:CREATE TABLE table_name (column_name column_type)ENGINE = InnoDB DEFAULT CHARSET = UTF8 ;

SQL解析:

ENGINE设置数据库引擎。

DEFAULT CHARSET设置字符集。

1
2
3
4
5
6
mysql> CREATE TABLE tb (
->id INT AUTO_INCREMENT,
->name VARCHAR(40) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
Query OK, 0 rows affected (0.01 sec)

显示表

SQL:SHOW TABLES;

1
2
3
4
5
6
7
mysql> SHOW TABLES;
+-----------------+
| Tables_in_study |
+-----------------+
| tb |
+-----------------+
1 row in set (0.00 sec)

删除表

SQL:DROP TABLE table_name;

1
2
mysql> DROP TABLE tb;
Query OK, 0 rows affected (0.01 sec)

查看表结构

SQL:DESC table_name;

1
2
3
4
5
6
7
8
mysql> DESC tb;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(40) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
坚持分享,您的支持将鼓励我继续努力!