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