mysql ndb 命令_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 SETlatin1 */ |

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

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；