MySQL基本命令

1.输入密码登录 :mysql -u root -p

C:\Windows\system32>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.

 2.查看数据库：show databases;

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

3.查看数据库中的表 :show tables;

mysql> use sys
Database changed
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| innodb_lock_waits                             |
| io_by_thread_by_latency                       |
|...........................                    |
+-----------------------------------------------+
101 rows in set (0.20 sec)

4.show...from... 

mysql> show tables from mysql;

5.当前连接的数据库 :select database();

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

6.创建数据库：create database  库名

mysql> create database student;
Query OK, 1 row affected (0.15 sec)

mysql> use student;
Database changed
mysql> show tables;
Empty set (0.00 sec)

7.创建数据库中的表：create table 表名

mysql> create table stuinfo(
    -> id int,
    -> name varchar(20));
Query OK, 0 rows affected (1.12 sec)

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

8.查看表结构: desc stuinfo;

mysql> desc stuinfo;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.09 sec)

9.插入insert 

mysql> insert into stuinfo (id,name) values(1,'Like');
Query OK, 1 row affected (0.20 sec)

mysql> insert into stuinfo (id,name) values(2,'ZhuXu');
Query OK, 1 row affected (0.10 sec)

mysql> select * from stuinfo;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Like  |
|    2 | ZhuXu |
+------+-------+
2 rows in set (0.00 sec)

10.更新update 

mysql> update stuinfo set name='Chen' where id=2;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stuinfo;
+------+------+
| id   | name |
+------+------+
|    1 | Like |
|    2 | Chen |
+------+------+
2 rows in set (0.00 sec)

11.删除delete 

mysql> delete from stuinfo where id=2;
Query OK, 1 row affected (0.14 sec)

mysql> select * from stuinfo;
+------+------+
| id   | name |
+------+------+
|    1 | Like |
+------+------+

12.select * from 表名：从表中选取数据，星号（*）代表选取所有列。

13.查看MySQL服务端版本

（1）登录mysql服务端：

select version();

（2）未登录mysql服务端：

mysql --version

mysql -V

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye

C:\Windows\system32>mysql --version
mysql  Ver 8.0.15 for Win64 on x86_64 (MySQL Community Server - GPL)

C:\Windows\system32>mysql -V
mysql  Ver 8.0.15 for Win64 on x86_64 (MySQL Community Server - GPL)

C:\Windows\system32>

14.SQL SELECT 语句

15.SQL INSERT INTO 语句