MySQL基本简单操作02
2024-10-15 07:35:01
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)
%用来表示不记得的部分，是通配符。
除了%之外还有 _表示任何单个字符，[ ]指定范围或集合中的任何单个字符，[^]不属于指定范围或集合的任何单个字符。
最新文章
- python安装完毕后，提示找不到ssl模块的解决步骤
- [Hadoop大数据]——Hive初识
- 启动Oracle
- golang笔记——数据类型
- C#代码规范 .NET程序员需要提升的修养
- 网络编码 GB2312、GBK与UTF-8的区别
- python用法笔记（数组（list、touple、dict）、字符串）
- Ubuntu下配置 keepalived+nginx+tomcat 负载均衡
- 驱动程序环境搭配（VS2010 + WDK + VirtualDDK + VMware）good
- hdfs 常用命令
- AngularJS的指令(Directive) compile和link的区别及使用示例
- jQuery 获取和设置type为hidden的input的值
- cs231n spring 2017 lecture10 Recurrent Neural Networks 听课笔记
- js常用代码
- i春秋官网4.0上线啦 文末有福利
- Swoole 心跳检测
- mysql常用命令及语法规范
- C# StreamReader.ReadLine统计行数的问题
- win10下btcd的安装和简单配置
- mysql之Query Cache