• MySQL的一些基本操作


    近期開始学习MySQL,主要是通过书籍。和看燕十八老师的视频,然后通过博客记录自己的学习过程。

    登入数据库

    zhiniaobu@telunsu-K55VD:~$ mysql -uroot -p
    Enter password: 

    查看当前全部数据库

    mysql> show databases;#查看当前全部数据库
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    3 rows in set (0.00 sec)

    创建数据库

    mysql> create database ceshi;#创建数据库ceshi
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show databases;#查看当前全部数据库。会看到如今多了一个ceshi
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | ceshi              |
    | mysql              |
    | performance_schema |
    +--------------------+
    4 rows in set (0.00 sec)

    查看数据库的定义

    mysql> show create database ceshi;#查看数据库ceshi的定义
    +----------+------------------------------------------------------------------+
    | Database | Create Database                                                  |
    +----------+------------------------------------------------------------------+
    | ceshi    | CREATE DATABASE `ceshi` /*!40100 DEFAULT CHARACTER SET latin1 */ |
    +----------+------------------------------------------------------------------+
    1 row in set (0.00 sec)

    删除数据库

    mysql> drop database ceshi;#删除数据库ceshi
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> show databases;#查看当前全部数据库,如今会发现ceshi已经不存在了。由于已经删除了
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    3 rows in set (0.00 sec
    

    使用数据库

    mysql> create database ceshi_ku;#先建立一个库。等下用来建表
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show databases;#ceshi_ku已经成功的建立了
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | ceshi_ku           |
    | mysql              |
    | performance_schema |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> use ceshi_ku;#建立表之前,首先要选择一个库
    Database changed

    查看当前库以下的全部表

    mysql> show tables;#查看当前库以下的全部表。眼下是什么表也没有
    Empty set (0.00 sec)
    

    创建表

    mysql> create table ceshi_biao
        -> (
        -> id int(11),
        -> salary float
        -> );#建立ceshi_biao表
    Query OK, 0 rows affected (0.10 sec)
    
    mysql> show tables;#查看当前库以下的全部表。ceshi_biao表已经成功建立
    +--------------------+
    | Tables_in_ceshi_ku |
    +--------------------+
    | ceshi_biao         |
    +--------------------+
    1 row in set (0.00 sec)

    查看表基本结构

    mysql> desc ceshi_biao;#查看ceshi_biao表基本结构
    +--------+---------+------+-----+---------+-------+
    | Field  | Type    | Null | Key | Default | Extra |
    +--------+---------+------+-----+---------+-------+
    | id     | int(11) | YES  |     | NULL    |       |
    | salary | float   | YES  |     | NULL    |       |
    +--------+---------+------+-----+---------+-------+
    2 rows in set (0.04 sec)

    查看表具体结构

    mysql> show create table ceshi_biao;#查看ceshi_biao表具体结构
    +------------+-------------------------------------------------------------------------------------------------------------------------------+
    | Table      | Create Table                                                                                                                  |
    +------------+-------------------------------------------------------------------------------------------------------------------------------+
    | ceshi_biao | CREATE TABLE `ceshi_biao` (
      `id` int(11) DEFAULT NULL,
      `salary` float DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +------------+-------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    改动表名

    mysql> alter table ceshi_biao rename to ceshi_new;#把ceshi_biao表的名字换成ceshi_new
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> show tables;#查看当前库以下的全部表。ceshi_biao表的名字已经成功改为ceshi_new
    +--------------------+
    | Tables_in_ceshi_ku |
    +--------------------+
    | ceshi_new          |
    +--------------------+
    1 row in set (0.00 sec)
    
    ###########2种方法################
    
    mysql> rename table ceshi_biao to ceshi_new;#把ceshi_biao表的名字换成ceshi_new
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show tables;#查看当前库以下的全部表。ceshi_biao表的名字已经成功改为ceshi_new
    +--------------------+
    | Tables_in_ceshi_ku |
    +--------------------+
    | ceshi_new          |
    +--------------------+
    1 row in set (0.00 sec)
    

    往表里面插入数据

    mysql> insert into  ceshi_new values 
        -> (11,400.56),
        -> (12,600.07),
        -> (13,800.45);
    Query OK, 3 rows affected (0.03 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from ceshi_new;#这个表就是员工号码以及员工的工资,刚才就仅仅插入了3条
    +------+--------+
    | id   | salary |
    +------+--------+
    |   11 | 400.56 |
    |   12 | 600.07 |
    |   13 | 800.45 |
    +------+--------+
    3 rows in set (0.00 sec
    

    清空表

    mysql> truncate ceshi_new;#清空表,不是删除表
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> select * from ceshi_new;#查看内容
    Empty set (0.00 sec)
    

    删除表

    mysql> drop table ceshi_new;#删除ceshi_new表
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> show tables;#查看当前库以下的全部表,ceshi_new表已经成功删除
    Empty set (0.01 sec)

    改动编码

    mysql> set names utf8;#改动编码为utf8
    Query OK, 0 rows affected (0.00 sec)

  • 相关阅读:
    CentOS7 安装Redis Cluster集群
    CentOS7 安装Redis 单机版
    CentOS7 配置免密登陆
    深入java虚拟机学习 -- 内存管理机制
    深入java虚拟机学习 -- 类的卸载
    深入java虚拟机学习 -- 类的加载机制(四)
    深入java虚拟机学习 -- 类的加载机制(三)
    深入java虚拟机学习 -- 类的加载机制(续)
    (原创)cocos lua 热更新从零开始(一)最简单demo
    lua 调用参数报错(a userdata value)
  • 原文地址:https://www.cnblogs.com/mqxnongmin/p/10776091.html
Copyright © 2020-2023  润新知