MySQL数据库的常用命令

一、如何进入MySQL数据库

[root@server1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.50 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>

 

 

 二、查看数据库信息

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lixing             |
| lx                 |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

 

三、查看当前数据库有哪些表

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

 

四、查看表的结构

1 mysql> USE mysql;
2 mysql> DESCRIBE user;

 

五、SQL语句概述

SQL语言：

Structured Query Language的缩写，即结构化查询语言

关系型数据库的标准语言

用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能

SQL分类：

DDL（Data　Definition　Language）数据定义语言：用来建立数据库、数据库对象和定义字段，如CREATE、ALTER、DROP

DML（Data　Manipulation　Language）数据操纵语言：用来插入、删除和修改数据库中的数据，如INSERT、UPDATE、DELETE

DQL（Data　Query　Language）数据查询语言：用来查询数据中的数据，如SELECT

DCL（Data　Control　Language）数据控制语言：用来控制数据库组件的存取许可、存取权限等，如COMMIT、ROLLBACK、GRANT、REVOKE

 

 

创建新的数据库

ql> create database lx;
Query OK, 1 row affected (0.00 sec)

创建新的表

mysql> use lx
Database changed
mysql> show tables;
+--------------+
| Tables_in_lx |
+--------------+
| abcd         |
+--------------+
1 row in set (0.00 sec)

mysql>  create table users(username CHAR(26) NOT NULL,passwd CHAR(36) DEFAULT'123123',PRIMARY KEY(username));
Query OK, 0 rows affected (0.01 sec)

删除一个数据库（原有数据库名称：lx）

 mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| lx                 |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

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

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

 

删除一个表（原有表：aaa）

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

mysql> drop table aaa;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

 

修改库的默认字符类型

mysql> show create database lx;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| lx       | CREATE DATABASE `lx` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database lx character set utf8;
Query OK, 1 row affected (0.00 sec)

 

mysql> show create database lx;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| lx       | CREATE DATABASE `lx` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

 

查看已经创建好的属性列表

mysql> desc abc;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | int(32) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

 

增加属性列表

mysql> alter table aaa
    -> add column age int(32);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc aaa;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | int(32) | NO   |     | NULL    |       |
| age   | int(32) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

 

删除新增

mysql> alter table aaa
    -> drop column weight;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc aaa;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | int(32)  | NO   |     | NULL    |       |
| height | int(200) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

在表中插入数据

 

mysql> insert into aaa values
    -> (
    -> '李兴' , 3 , 300 , 300 , 3
    -> )
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from aaa;
+--------+------+--------+--------+------+
| name   | age  | weight | height | tel  |
+--------+------+--------+--------+------+
| 李兴   |    3 |    434 |    343 |    3 |
| 李兴   |    3 |    300 |    300 |    3 |
| 李兴   |    3 |    300 |    300 |    3 |
+--------+------+--------+--------+------+
3 rows in set (0.00 sec)

 

顺序排列

升序：mysql> select * from aaa order by name asc；

降序：mysql> select * from aaa order by age desc；