MySQL基本简单操作02
程序员文章站
2024-02-03 11:42:28
MySQL基本简单操作 先进入 容器。 [root@promote ~] docker exec it mysql /bin/bash root@30d60b852cf5:/ mysql uroot p000000 mysql: [Warning] Using a password on the c ......
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)
%
用来表示不记得的部分,是通配符。
除了%
之外还有 _
表示任何单个字符,[ ]
指定范围或集合中的任何单个字符,[^]
不属于指定范围或集合的任何单个字符。