MySQL基本简单操作
程序员文章站
2022-06-11 11:49:50
MySQL基本简单操作 学会了安装 ，那么就将它利用起来。（/滑稽脸） 之前想学习 （Windows下配置真麻烦），学会了 就方便了，直接使用 创建一个 服务岂不美滋滋。创建容器的步骤可以看一下 "分享04" 的 的创建过程。 首先检查一下本地镜像。 [root@promote ~] docker ......
MySQL基本简单操作
学会了安装Docker，那么就将它利用起来。（/滑稽脸）
之前想学习Mysql（Windows下配置真麻烦），学会了Docker就方便了，直接使用Docker创建一个Mysql服务岂不美滋滋。创建容器的步骤可以看一下的Nginx的创建过程。
首先检查一下本地镜像。
[root@promote ~]# docker images REPOSITORY TAG IMAGE ID CREATED SIZE
没有Mysql的镜像，那么我先来拉取一个Mysql镜像。
先搜索以下Mysql的镜像。
[root@promote ~]# docker search mysql INDEX NAME DESCRIPTION STARS OFFICIAL AUTOMATED docker.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 89 docker.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 ... 32 docker.io docker.io/centos/mysql-57-centos7 MySQL 5.7 SQL database server 31 docker.io docker.io/mysql/mysql-cluster Experimental MySQL Cluster Docker images. ... 30 docker.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... 14 docker.io docker.io/centos/mysql-56-centos7 MySQL 5.6 SQL database server 8 docker.io docker.io/openshift/mysql-55-centos7 DEPRECATED: A Centos7 based MySQL v5.5 ima... 6 docker.io docker.io/circleci/mysql MySQL is a widely used, open-source relati... 5 docker.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 ... 2 docker.io docker.io/openzipkin/zipkin-mysql Mirror of https://quay.io/repository/openz... 1 docker.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 0 docker.io docker.io/cloudposse/mysql Improved `mysql` service with support for ... 0 [OK]
接着拉取镜像到本地，当然是优先官方镜像。
[root@promote ~]# docker pull docker.io/mysql Using default tag: latest Trying to pull repository docker.io/library/mysql ... latest: Pulling from docker.io/library/mysql 683abbb4ea60: Pull complete 0550d17aeefa: Pull complete 7e26605ddd77: Pull complete 9882737bd15f: Pull complete 999c06ab75f6: Pull complete c71d695f9937: Pull complete c38f847c1491: Pull complete 5e0cb05a8fc3: Pull complete c89e3e373fca: Pull complete fa39a2c9922d: Pull complete b293d9c897c4: Pull complete 3dc061869740: Pull complete Digest: sha256:43ed4f8c9d1695e97a39cdfe9475af9096e3723cfb79d820d8da00d61a277a85 Status: Downloaded newer image for docker.io/mysql:latest
拉取成功，现在开始创建Mysql容器。
[root@promote ~]# docker run -itd --name=mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=000000 docker.io/mysql 30d60b852cf57c5f4e7df36846b10149387bb2b736cecb11f12a2d64a3bdbf43
进入容器。
[root@promote ~]# docker exec -it mysql /bin/bash root@30d60b852cf5:/#
连接数据库。
root@30d60b852cf5:/# mysql -uroot -p000000 mysql: [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 8 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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 | | 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)