MySQL基本簡單操作

標籤：test   odi   extra   link   routing   web   current   exp   val   

MySQL基本簡單操作

學會了安裝Docker，那麼就將它利用起來。（/滑稽臉）
之前想學習Mysql（Windows下配置真麻煩），學會了Docker就方便了，直接使用Docker建立一個Mysql服務豈不美滋滋。建立容器的步驟可以看一下分享04的Nginx的建立過程。
首先檢查一下本地鏡像。

[[email protected] ~]# docker imagesREPOSITORY          TAG                 IMAGE ID            CREATED             SIZE

沒有Mysql的鏡像，那麼我先來拉取一個Mysql鏡像。
先搜尋以下Mysql的鏡像。

[[email protected] ~]# docker search mysqlINDEX       NAME                                                             DESCRIPTION                                     STARS     OFFICIAL   AUTOMATEDdocker.io   docker.io/mysql                                                  MySQL is a widely used, open-source relati...   6527      [OK]docker.io   docker.io/mariadb                                                MariaDB is a community-developed fork of M...   2061      [OK]docker.io   docker.io/mysql/mysql-server                                     Optimized MySQL Server Docker images. Crea...   479                  [OK]docker.io   docker.io/percona                                                Percona Server is a fork of the MySQL rela...   344       [OK]docker.io   docker.io/zabbix/zabbix-server-mysql                             Zabbix Server with MySQL database support       106                  [OK]docker.io   docker.io/hypriot/rpi-mysql                                      RPi-compatible Docker Image with Mysql          89docker.io   docker.io/centurylink/mysql                                      Image containing mysql. Optimized to be li...   60                   [OK]docker.io   docker.io/zabbix/zabbix-web-nginx-mysql                          Zabbix frontend based on Nginx web-server ...   58                   [OK]docker.io   docker.io/1and1internet/ubuntu-16-nginx-php-phpmyadmin-mysql-5   ubuntu-16-nginx-php-phpmyadmin-mysql-5          36                   [OK]docker.io   docker.io/tutum/mysql                                            Base docker image to run a MySQL database ...   32docker.io   docker.io/centos/mysql-57-centos7                                MySQL 5.7 SQL database server                   31docker.io   docker.io/mysql/mysql-cluster                                    Experimental MySQL Cluster Docker images. ...   30docker.io   docker.io/schickling/mysql-backup-s3                             Backup MySQL to S3 (supports periodic back...   20                   [OK]docker.io   docker.io/bitnami/mysql                                          Bitnami MySQL Docker Image                      15                   [OK]docker.io   docker.io/zabbix/zabbix-proxy-mysql                              Zabbix proxy with MySQL database support        15                   [OK]docker.io   docker.io/linuxserver/mysql                                      A Mysql container, brought to you by Linux...   14docker.io   docker.io/centos/mysql-56-centos7                                MySQL 5.6 SQL database server                   8docker.io   docker.io/openshift/mysql-55-centos7                             DEPRECATED: A Centos7 based MySQL v5.5 ima...   6docker.io   docker.io/circleci/mysql                                         MySQL is a widely used, open-source relati...   5docker.io   docker.io/dsteinkopf/backup-all-mysql                            backup all DBs in a mysql server                4                    [OK]docker.io   docker.io/mysql/mysql-router                                     MySQL Router provides transparent routing ...   2docker.io   docker.io/openzipkin/zipkin-mysql                                Mirror of https://quay.io/repository/openz...   1docker.io   docker.io/ansibleplaybookbundle/mysql-apb                        An APB which deploys RHSCL MySQL                0                    [OK]docker.io   docker.io/cloudfoundry/cf-mysql-ci                               Image used in CI of cf-mysql-release            0docker.io   docker.io/cloudposse/mysql                                       Improved `mysql` service with support for ...   0                    [OK]

接著拉取鏡像到本地，當然是優先官方鏡像。

[[email protected] ~]# docker pull docker.io/mysqlUsing default tag: latestTrying to pull repository docker.io/library/mysql ...latest: Pulling from docker.io/library/mysql683abbb4ea60: Pull complete0550d17aeefa: Pull complete7e26605ddd77: Pull complete9882737bd15f: Pull complete999c06ab75f6: Pull completec71d695f9937: Pull completec38f847c1491: Pull complete5e0cb05a8fc3: Pull completec89e3e373fca: Pull completefa39a2c9922d: Pull completeb293d9c897c4: Pull complete3dc061869740: Pull completeDigest: sha256:43ed4f8c9d1695e97a39cdfe9475af9096e3723cfb79d820d8da00d61a277a85Status: Downloaded newer image for docker.io/mysql:latest

拉取成功，現在開始建立Mysql容器。

[[email protected] ~]# docker run -itd --name=mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=000000 docker.io/mysql30d60b852cf57c5f4e7df36846b10149387bb2b736cecb11f12a2d64a3bdbf43

進入容器。

[[email protected] ~]# docker exec -it mysql /bin/bash[email protected]:/#

串連資料庫。

[email protected]:/# mysql -uroot -p000000mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.11 MySQL Community Server - GPLCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

串連資料庫成功！
先學習查看資料庫。

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

然後是建立資料庫。

mysql> create database gubeiqing;Query OK, 1 row affected (0.02 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| gubeiqing          || information_schema || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)

接著使用我剛剛建立好的gubeiqing資料庫。

mysql> use gubeiqing;Database changed

進入這個資料庫之後，來看一下有哪些表。

mysql> show tables;Empty set (0.00 sec)

這個時候的資料表是空的，接著來建立資料表。

mysql> create table gubeiqing1(name varchar(20) not null , age varchar(20) not null);Query OK, 0 rows affected (0.08 sec)

建表的通用語句文法是：CREATE TABLE table_name (column_name column_type);.
現在來查看一下資料表是什麼樣的。

mysql> desc gubeiqing1;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name  | varchar(20) | NO   |     | NULL    |       || age   | varchar(20) | NO   |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

可以看到現在gubeiqing1這個資料表已經有了兩列。但是還沒有資料，所以現在向這個資料表裡添加資料。

mysql> insert into gubeiqing1 (name,age) values ('gbq',21);Query OK, 1 row affected (0.04 sec)

如果添加的資料是字元型，那麼必須使用單引號或者雙引號。
現在查詢一下這個表裡所有的內容。

可以看到資料已經被我們添加進去了。

mysql> select * from gubeiqing1;+------+-----+| name | age |+------+-----+| gbq  | 21  |+------+-----+1 row in set (0.00 sec)

簡單的查詢語句文法是:SELECT column_name,column_name FROM table_name。

再來插入幾條（插入時可以不指定列名，但是自己要知道插入資料的順序）。

mysql> insert into gubeiqing1 values ('zhangsan',20);Query OK, 1 row affected (0.05 sec)mysql> insert into gubeiqing1 values ('lisi',19);Query OK, 1 row affected (0.03 sec)

查看一下。

mysql> select * from gubeiqing1;+----------+-----+| name     | age |+----------+-----+| gbq      | 21  || zhangsan | 20  || lisi     | 19  |+----------+-----+3 rows in set (0.00 sec)

然後學習改資料。
現在我將lisi的age欄位，由19改為22。

mysql> update gubeiqing1 set age=22 where name='lisi';Query OK, 1 row affected (0.04 sec)Rows matched: 1  Changed: 1  Warnings: 0

改資料的基本簡單文法是：UPDATE table_name SET column_name1=values1，column_name2=values2 [WHERE 條件運算式]。

再查看一下資料表。

mysql> select * from gubeiqing1;+----------+-----+| name     | age |+----------+-----+| gbq      | 21  || zhangsan | 20  || lisi     | 22  |+----------+-----+3 rows in set (0.00 sec)

可以看到lisi的age欄位已經被改了。
資料庫基本的增刪改查，已經看了三個，接著來看刪。
將zhangsan這條資料從資料表中刪除。

mysql> delete from gubeiqing1 where name='zhangsan';Query OK, 1 row affected (0.39 sec)mysql> select * from gubeiqing1;+------+-----+| name | age |+------+-----+| gbq  | 21  || lisi | 22  |+------+-----+2 rows in set (0.00 sec)

再來刪除這個表，最後刪除庫。

mysql> drop table gubeiqing1;Query OK, 0 rows affected (0.13 sec)mysql> show tables;Empty set (0.00 sec)mysql> drop database gubeiqing;Query OK, 0 rows affected (0.09 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)

MySQL基本簡單操作