mysql基本操作示例_MySQL基本操作示例

MySQL基本操作示例

C:\Users\Administrator>mysql -u root -p(进入mysql)

Enter password: ******(输入密码)

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.6.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;(查看mysql中所有数据库)

+--------------------+

| Database           |

+--------------------+

| information_schema |

| database_mydb      |

| db_student         |

| mydb               |

| mysql              |

| performance_schema |

| test               |

+--------------------+

7 rows in set (0.00 sec)

mysql> create database mydb1;(创建名为mydb1的数据库)

Query OK, 1 row affected (0.01 sec)

mysql> use mydb1;(使用mydb1这个数据库)

Database changed

mysql> show tables;(查看库中所有表)

Empty set (0.00 sec)(新建的库，所以没有表)

mysql> create table student(

-> id bigint(10) auto_increment not null primary key,

-> name varchar(8) not null,

-> sex tinyint(1) not null,

-> age int(3)

-> );(创建名为student的表，表中有id，name，sex，age这几列)

Query OK, 0 rows affected (0.32 sec)

mysql> desc student;(查看student这张表中记录)

+-------+------------+------+-----+---------+----------------+

| Field | Type       | Null | Key | Default | Extra          |

+-------+------------+------+-----+---------+----------------+

| id    | bigint(10) | NO   | PRI | NULL    | auto_increment |

| name  | varchar(8) | NO   |     | NULL    |                |

| sex   | tinyint(1) | NO   |     | NULL    |                |

| age   | int(3)     | YES  |     | NULL    |                |

+-------+------------+------+-----+---------+----------------+

4 rows in set (0.02 sec)

mysql> alter table student add column major varchar(20) null;(增加一列名为major的记录)

Query OK, 0 rows affected (0.52 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;(再次查看表中记录看major是否被添加进去)

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | bigint(10)  | NO   | PRI | NULL    | auto_increment |

| name  | varchar(8)  | NO   |     | NULL    |                |

| sex   | tinyint(1)  | NO   |     | NULL    |                |

| age   | int(3)      | YES  |     | NULL    |                |

| major | varchar(20) | YES  |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

5 rows in set (0.02 sec)

mysql> alter table student change major profession char(10) not null;(将名为major的记录改名为profession其后的类型必须有not null可以省略)

Query OK, 0 rows affected (0.76 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;(查看改名后表)

+------------+------------+------+-----+---------+----------------+

| Field      | Type       | Null | Key | Default | Extra          |

+------------+------------+------+-----+---------+----------------+

| id         | bigint(10) | NO   | PRI | NULL    | auto_increment |

| name       | varchar(8) | NO   |     | NULL    |                |

| sex        | tinyint(1) | NO   |     | NULL    |                |

| age        | int(3)     | YES  |     | NULL    |                |

| profession | char(10)   | NO   |     | NULL    |                |

+------------+------------+------+-----+---------+----------------+

5 rows in set (0.02 sec)

mysql> alter table student drop id;(删除student的表中名为id的这列记录)

Query OK, 0 rows affected (1.75 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;(产看删除后的表)

+------------+------------+------+-----+---------+-------+

| Field      | Type       | Null | Key | Default | Extra |

+------------+------------+------+-----+---------+-------+

| name       | varchar(8) | NO   |     | NULL    |       |

| sex        | tinyint(1) | NO   |     | NULL    |       |

| age        | int(3)     | YES  |     | NULL    |       |

| profession | char(10)   | NO   |     | NULL    |       |

+------------+------------+------+-----+---------+-------+

4 rows in set (0.02 sec)

mysql> alter table student add id bigint(12) null(在student的表中天机名为id的这列据记录);

Query OK, 0 rows affected (0.48 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;(查看添加后的表)

+------------+------------+------+-----+---------+-------+

| Field      | Type       | Null | Key | Default | Extra |

+------------+------------+------+-----+---------+-------+

| name       | varchar(8) | NO   |     | NULL    |       |

| sex        | tinyint(1) | NO   |     | NULL    |       |

| age        | int(3)     | YES  |     | NULL    |       |

| profession | char(10)   | NO   |     | NULL    |       |

| id         | bigint(12) | YES  |     | NULL    |       |

+------------+------------+------+-----+---------+-------+

5 rows in set (0.02 sec)

mysql> alter table student drop id;(删除id这列记录)

Query OK, 0 rows affected (0.50 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student add id varchar(12) null first;(将id这列记录添加在表的第一列)

Query OK, 0 rows affected (0.51 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;(查看添加后的表)

+------------+-------------+------+-----+---------+-------+

| Field      | Type        | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| id         | varchar(12) | YES  |     | NULL    |       |

| name       | varchar(8)  | NO   |     | NULL    |       |

| sex        | tinyint(1)  | NO   |     | NULL    |       |

| age        | int(3)      | YES  |     | NULL    |       |

| profession | char(10)    | NO   |     | NULL    |       |

+------------+-------------+------+-----+---------+-------+

5 rows in set (0.02 sec)

mysql> alter table student add primary key(id);(将id这列记录设置为主键)

Query OK, 0 rows affected (0.80 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;(查看设置后的表)

+------------+-------------+------+-----+---------+-------+

| Field      | Type        | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| id         | varchar(12) | NO   | PRI |         |       |

| name       | varchar(8)  | NO   |     | NULL    |       |

| sex        | tinyint(1)  | NO   |     | NULL    |       |

| age        | int(3)      | YES  |     | NULL    |       |

| profession | char(10)    | NO   |     | NULL    |       |

+------------+-------------+------+-----+---------+-------+

5 rows in set (0.02 sec)

mysql> alter table student drop primary key;(删除主键)

Query OK, 0 rows affected (1.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> exit;(退出mysql)

Bye