MySQL数据库实现云计算_【零基础学云计算】MySQL数据库管理

Mysql管理操作

数据库管理操作查看数据库结构

创建及删除库和表

管理表的记录

基本操作命令查看数据库列表信息SHOW DATABASES

[root@localhost ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.17 Source distribution

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

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)查看数据库中的数据表信息USE数据库名

SHOW TABLES

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 |

| engine_cost |

| event |

| ..... |

| user |

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

31 rows in set (0.00 sec)显示数据表的结构(字段)DESCRIBE [数据库名.]表名

mysql> describe db;

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

| Field | Type | Null | Key | Default | Extra |

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

| Host | char(60) | NO | PRI | | |

| Db | char(64) | NO | PRI | | |

| User | cha(32) | NO | PRI | | |

| Select_priv | enum('N','Y') | NO | | N | |

| Execute_priv | enum('N','Y') | NO | | N | |

| ... |

| Trigger_priv | enum('N','Y') | NO | | N | |

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

22 rows in set (0.00 sec)

SQL语句概述

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

是关系型数据库的标准语言

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

SQL分类DDL：数据定义语言

DML：数据操纵语言

DQL：数据查询语言

DCL：数据控制语言

DDL语句操作DDL语句用于创建数据库对象，如库、表、索引等

使用DDL语句新建库、表创建数据库: CREATE DATABASE 数据库名

创建数据表: CREATE TABLE 表名(字段定义...)

mysql> create database school;

Query OK, 1 row affected (0.00 sec)

mysql> use school;

Database changed

mysql> create table info (

-> id int(4) ,

-> name char(10) not null,

-> address varchar(50) default 'nanjing',

-> primary key (id));

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| school |

| sys |

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

5 rows in set (0.00 sec)

mysql> show tables;

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

| Tables_in_school |

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

| info |

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

1 row in set (0.00 sec)

DDL语句操作使用DDL语句删除库、表删除指定的数据表: DROP TABLE [数据库名.]表名

删除指定的数据库: DROP DATABASE 数据库名

mysql> drop table info;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

Empty set (0.00 sec)

mysql> drop database school;

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

DML语句操作DML语句用于对表中的数据进行管理

包括以下操作INSERT:插入新数据

UPDATE :更新原有数据

DELETE :删除不需要的数据

mysql> create database school;

Query OK, 1 row affected (0.01 sec)

mysql> use school;

Database changed

mysql> create table info (

-> id int(4) not null,

-> name char(10) not null,

-> address varchar(50) default 'nanjing',

-> primary key (id));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (id,name,address) values (1,'zhangsan','beijing');

Query OK, 1 row affected (0.01 sec)

mysql> select * from info; //查看表所有内容

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

| id | name | address |

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

| 1 | zhangsan | beijing |

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

1 row in set (0.00 sec)

mysql> update info set address='shanghai' where id=1; //将info表内id为1的address更改为shanghai

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from info; //查看表内容

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

| id | name | address |

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

| 1 | zhangsan | shanghai |

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

1 row in set (0.00 sec)

mysql> delete from info where id=1; //根据条件删除info表中id为1的数据，不带where条件时删除表内所有数据

Query OK, 1 row affected (0.00 sec)

mysql> select * from info;

Empty set (0.00 sec)

DQL语句操作DQL是数据查询语句，只有一条: SELECT

用于从数据表中查找符合条件的数据记录

查询时可不指定条件SELECT 字段名1,字段名2.... FROM表名

mysql> select * from info; //查看表所有内容

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

| id | name | address |

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

| 1 | zhangsan | beijing |

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

1 row in set (0.00 sec)

mysql> select name from info where id=1; //条件查看表内容

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

| name |

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

| zhangsan |

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

1 row in set (0.00 sec)

DCL语句操作设置用户权限(用户不存在时。则新建用户GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [ IDENTIFIED BY '密码' ]

查看用户的权限SHOW GRANT FOR 用户名@来源地址

撤销用户的权限REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址

写在最后：

本专栏所有文章均为南京课工场学员投稿，如有问题欢迎指出讨论，未经允许，禁止转载！