MySQL basic operation of the database and table additions and deletions to change

Database management

Log in to MySQL

[[email protected] conf]# mysql -u root -pEnter password: Welcome to the MySQL monitor.  

View database server all databases

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || aming              || blog               || log                || logs               || mydata             || mysql              || mysql2             || performance_schema || temp               || test               || zabbix             || zrlog              |+--------------------+13 rows in set (0.00 sec)

Create a database

mysql> create database myss;Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || aming              || blog               || log                || logs               || mydata             || mysql              || mysql2             || myss               || performance_schema || temp               || test               || zabbix             || zrlog              |+--------------------+14 rows in set (0.01 sec)

Deleting a database

mysql> drop database mydata;Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || aming              || blog               || log                || logs               || mysql              || mysql2             || myss               || performance_schema || temp               || test               || zabbix             || zrlog              |+--------------------+13 rows in set (0.00 sec)

Creating a database with Set character sets

mysql> create database brlog default character set utf8;Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || aming              || blog               || brlog              || log                || logs               || mysql              || mysql2             || myss               || performance_schema || temp               || test               || zabbix             || zrlog              |+--------------------+14 rows in set (0.00 sec)

View the default character set for a library

mysql> show create database brlog;+----------+----------------------------------------------------------------+| Database | Create Database                                                |+----------+----------------------------------------------------------------+| brlog    | CREATE DATABASE `brlog` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+----------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create database blog;+----------+-----------------------------------------------------------------+| Database | Create Database                                                 |+----------+-----------------------------------------------------------------+| blog     | CREATE DATABASE `blog` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+-----------------------------------------------------------------+1 row in set (0.00 sec)

Data Sheet Management

Select Database

mysql> use brlog;Database changed

View the tables in the database

mysql> show tables;Empty set (0.00 sec)

Create a data table

mysql> create table yy (username varchar(16), passwd int, yy int );Query OK, 0 rows affected (0.01 sec)mysql> show tables;+-----------------+| Tables_in_brlog |+-----------------+| yy              |+-----------------+1 row in set (0.01 sec)

View table Structure

mysql> desc yy;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| username | varchar(16) | YES  |     | NULL    |       || passwd   | int(11)     | YES  |     | NULL    |       || yy       | int(11)     | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)

Delete a table

mysql> create table qq(username varchar(16), passwd int);Query OK, 0 rows affected (0.00 sec)mysql> drop table qq;Query OK, 0 rows affected (0.00 sec)

Modify table field type

mysql> desc yy;+----------+---------+------+-----+---------+-------+| Field    | Type    | Null | Key | Default | Extra |+----------+---------+------+-----+---------+-------+| username | int(11) | YES  |     | NULL    |       || passwd   | int(11) | YES  |     | NULL    |       || yy       | int(11) | YES  |     | NULL    |       |+----------+---------+------+-----+---------+-------+3 rows in set (0.00 sec)

Modify table field name

mysql> mysql> alter table yy change column pawd int;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc yy;+----------+---------+------+-----+---------+-------+| Field    | Type    | Null | Key | Default | Extra |+----------+---------+------+-----+---------+-------+| username | int(11) | YES  |     | NULL    |       || pawd     | int(11) | YES  |     | NULL    |       || yy       | int(11) | YES  |     | NULL    |       |+----------+---------+------+-----+---------+-------+3 rows in set (0.01 sec)

Modify Table Name

mysql> alter table yy rename to qq;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+-----------------+| Tables_in_brlog |+-----------------+| qq              |+-----------------+1 row in set (0.00 sec)mysql> desc qq;+----------+---------+------+-----+---------+-------+| Field    | Type    | Null | Key | Default | Extra |+----------+---------+------+-----+---------+-------+| username | int(11) | YES  |     | NULL    |       || pawd     | int(11) | YES  |     | NULL    |       || yy       | int(11) | YES  |     | NULL    |       |+----------+---------+------+-----+---------+-------+3 rows in set (0.00 sec)

MySQL basic operation of the database and table additions and deletions to change