MySQL基本簡單操作02

標籤：欄位   0 rows   oracle   records   names   mes   簡單操作   一個   字元   

MySQL基本簡單操作

先進入Mysql容器。

[[email protected] ~]# docker exec -it mysql /bin/bash[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> 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 changedmysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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)

%用來表示不記得的部分，是萬用字元。
除了%之外還有 _表示任何單個字元，[ ]指定範圍或集合中的任何單個字元，[^]不屬於指定範圍或集合的任何單個字元。

MySQL基本簡單操作02