MySQL常用命令

1.创建数据库

   命令 create database 数据库名 charset=utf8;

mysql> create database python charset=utf8;
Query OK, 1 row affected (0.00 sec)

2.显示数据库

   命令 show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| python             |
+--------------------+
4 rows in set (0.00 sec)

3.指定数据库 

   命令 use 数据库名;

mysql> use python;
Database changed

4.查看当前使用数据库

   命令 select database();

mysql> select database();
+------------+
| database() |
+------------+
| python     |
+------------+
1 row in set (0.00 sec)

5.删除数据库

   命令 drop database 数据库名;

mysql> drop database python;
Query OK, 0 rows affected (0.00 sec)

6.创建表

   命令 create table 表名(列及类型);

mysql> create table students(
    -> id int auto_increment primary key not null,
    -> name varchar(10) not null,
    -> brithday date not null,
    -> hobby varchar(20) default 'MySQL');
Query OK, 0 rows affected (0.19 sec)

　　auto_increment表示自动增长

7.显示表

   命令 show tables;

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

8.显示字段

   命令 desc 表名;

mysql> desc students;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | NO   |     | NULL    |                |
| brithday | date        | NO   |     | NULL    |                |
| hobby    | varchar(20) | YES  |     | MySQL   |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

9.新增字段

   命令 alter table 表名 add 字段;

mysql> alter table students add gender bit default 0;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | NO   |     | NULL    |                |
| brithday | date        | NO   |     | NULL    |                |
| hobby    | varchar(20) | YES  |     | MySQL   |                |
| gender   | bit(1)      | YES  |     | b'0'    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

10.添加数据

　　a.全列插入

        命令 insert into 表名 values();

mysql> insert into students values(0,'隔壁老王','1997-4-13','code',1);
Query OK, 1 row affected (0.00 sec)

　　b.缺省插入

        命令 insert into 表名(列1,列2，...) values(值1,值2,...);

mysql> insert into students(name,brithday) values('张三','1996-4-13');
Query OK, 1 row affected (0.01 sec)

11.查看内容

     命令 select * from 表名;

mysql> select * from students;
+----+--------------+------------+-------+--------+
| id | name         | brithday   | hobby | gender |
+----+--------------+------------+-------+--------+
|  1 | 隔壁老王     | 1997-04-13 | code  |       |
|  2 | 张三         | 1996-04-13 | MySQL |        |
+----+--------------+------------+-------+--------+
2 rows in set (0.00 sec)

12.修改表

     命令 update 表名 set 列1=值1 where 条件;

mysql> update students set hobby='打飞机' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;
+----+--------------+------------+-----------+--------+
| id | name         | brithday   | hobby     | gender |
+----+--------------+------------+-----------+--------+
|  1 | 隔壁老王     | 1997-04-13 | 打飞机    |       |
|  2 | 张三         | 1996-04-13 | MySQL     |        |
+----+--------------+------------+-----------+--------+
2 rows in set (0.00 sec)