MySQL Basic simple Operation 02

MySQL Basic simple operation

Enter the Mysql container first.

[[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>

Create a new database.

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)

Enter the database and create a new data table.

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)

Then add a new column to the database, the basic simple syntax is: 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 (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)  

The basic simple syntax for the

Modify column name is: ALTER TABLE name, change column name, or not; .

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 (a) 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) 

Delete the newly added column, the basic simple syntax is: ALTER table name drop column name; .

  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)  

Then say the fuzzy search, such as now to find in the database zhangsan information, but only remember the zhang rest of the memory, then you can use fuzzy Search, the basic simple syntax is: SELECT * from 表名 WHERE 字段 LIKE ‘模糊字段‘; .

mysql> select * from gubeiqing_table where name like 'zhang%';+----------+-----+| name     | age |+----------+-----+| zhangsan | 20  |+----------+-----+1 row in set (0.01 sec)

%The part that is used to indicate that you do not remember is a wildcard character.
In addition to any single character that represents any single character, the specified range, or any single character % _ [ ] in the collection, [^] does not belong to the specified range or collection.

MySQL Basic simple Operation 02