MySQL 基本简单操作
先进入 Mysql 容器.
- [root@promote ~]# docker exec -it mysql /bin/bash
- 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> 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.01 sec)
进入数据库, 新建数据表.
- mysql> use gubeiqing;
- Database changed
- mysql> show tables;
- Empty set (0.00 sec)
- mysql> create table gubeiqing_table(name varchar(20) not null , age varchar(20) not null);
- Query OK, 0 rows affected (0.11 sec)
- mysql> show tables;
- +---------------------+
- | Tables_in_gubeiqing |
- +---------------------+
- | gubeiqing_table |
- +---------------------+
- 1 row in set (0.01 sec)
接着给数据库新增列, 基本简单语法是:
ALTER TABLE 表名 add column 列名 列类型 是否为空;
- .
- mysql> desc gubeiqing_table;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | name | varchar(20) | NO | | NULL | |
- | age | varchar(20) | NO | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 2 rows in set (0.01 sec)
- mysql> alter table gubeiqing_table add column job varchar(20) not null;
- Query OK, 0 rows affected (0.26 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> desc gubeiqing_table;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | name | varchar(20) | NO | | NULL | |
- | age | varchar(20) | NO | | NULL | |
- | job | varchar(20) | NO | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 3 rows in set (0.00 sec)
修改列名, 基本简单语法是:
ALTER TABLE 表名 change column 原列名 修改后的列名 列类型 是否为空;
- .
- mysql> desc gubeiqing_table;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | name | varchar(20) | NO | | NULL | |
- | age | varchar(20) | NO | | NULL | |
- | job | varchar(20) | NO | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 3 rows in set (0.00 sec)
- mysql> alter table gubeiqing_table change column job gbq_job varchar(20) not null;
- Query OK, 0 rows affected (0.11 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> desc gubeiqing_table;
- +---------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+-------------+------+-----+---------+-------+
- | name | varchar(20) | NO | | NULL | |
- | age | varchar(20) | NO | | NULL | |
- | gbq_job | varchar(20) | NO | | NULL | |
- +---------+-------------+------+-----+---------+-------+
- 3 rows in set (0.00 sec)
删除刚刚新增的列, 基本简单语法是:
ALTER TABLE 表名 drop column 列名;
- .
- mysql> desc gubeiqing_table;
- +---------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+-------------+------+-----+---------+-------+
- | name | varchar(20) | NO | | NULL | |
- | age | varchar(20) | NO | | NULL | |
- | gbq_job | varchar(20) | NO | | NULL | |
- +---------+-------------+------+-----+---------+-------+
- 3 rows in set (0.00 sec)
- mysql> alter table gubeiqing_table drop column gbq_job;
- Query OK, 0 rows affected (0.11 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> desc gubeiqing_table;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | name | varchar(20) | NO | | NULL | |
- | age | varchar(20) | NO | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 2 rows in set (0.00 sec)
然后说一下模糊搜索, 就比如现在要在数据库里查找 zhangsan 的信息, 可是只记得 zhang 剩下的部分都不记得了, 那么就可以使用模糊搜索, 基本简单语法是:
SELECT * from 表名 WHERE 字段 LIKE '模糊字段';
- .
- mysql> select * from gubeiqing_table where name like 'zhang%';
- +----------+-----+
- | name | age |
- +----------+-----+
- | zhangsan | 20 |
- +----------+-----+
- 1 row in set (0.01 sec)
% 用来表示不记得的部分, 是通配符.
除了 % 之外还有 _表示任何单个字符,[ ] 指定范围或集合中的任何单个字符,[^] 不属于指定范围或集合的任何单个字符.
来源: http://www.bubuko.com/infodetail-2687111.html