Shulou(Shulou.com)06/01 Report--

-create database kakaxi

Mysql > create database kakaxi

Query OK, 1 row affected (0.02 sec)

-to know which databases are in the system, you can query them using the following command

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | company |

| | kakaxi |

| | mysql |

| | performance_schema |

| | sys |

+-+

6 rows in set (0.00 sec)

Information_schema-- mainly stores some database object information in the system, such as user table information, column information, permission information, character set information, partition information.

Mysql-- stores user rights information for the system.

Performance_schema-A feature provided by mysql to monitor the performance of mysql servers at the underlying level of the system, and the performance_schema storage engine monitors events for mysql services.

Sys-mysql5.7 adds the sys system database, through which you can quickly understand the system metadata information.

-query the currently used database

Mysql > select database ()

+-+

| | database () |

+-+

| | NULL |

+-+

1 row in set (0.00 sec)

Mysql > use kakaxi

Database changed

Mysql > select database ()

+-+

| | database () |

+-+

| | kakaxi |

+-+

1 row in set (0.00 sec)

-Delete the database

Mysql > drop database kakaxi

Query OK, 0 rows affected (.56 sec)

-use use dbname to select the database to connect to

Mysql > use mysql

Database changed

-View the tables that exist in the database

Mysql > show tables

+-+

| | Tables_in_mysql |

+-+

| | columns_priv |

| | db |

| | engine_cost |

| | event |

| | func |

| | general_log |

| | gtid_executed |

| | help_category |

| | help_keyword |

| | help_relation |

| | help_topic |

| | innodb_index_stats |

| | innodb_table_stats |

| | ndb_binlog_index |

| | plugin |

| | proc |

| | procs_priv |

| | proxies_priv |

| | server_cost |

| | servers |

| | slave_master_info |

| | slave_relay_log_info |

| | slave_worker_info |

| | slow_log |

| | tables_priv |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

31 rows in set (0.04 sec)

-create a table. Since the table name of mysql is stored on disk in the form of a directory, the table name must match the characters allowed by any directory.

Mysql > create table delp (deptno int (4), deptname varchar (50))

Query OK, 0 rows affected (0.74 sec)

-View the table structure

Mysql > desc delp

->

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | deptno | int (4) | YES | | NULL |

| | deptname | varchar (50) | YES | | NULL |

+-+ +

2 rows in set (0.11 sec)

Mysql > create table employee (eid int (4), ename varchar (50), birthday date,deptno I

Nt (4))

Query OK, 0 rows affected (0.38 sec)

-View the ddl statement of the construction table

Mysql > show create table employee\ G

* * 1. Row *

Table: employee

Create Table: CREATE TABLE `employee` (

`eid` int (4) DEFAULT NULL

`ename` varchar (50) DEFAULT NULL

`dayday` date DEFAULT NULL

`deptno` int (4) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.03 sec)

ERROR: (there's an ERROR here, but I don't know what it means yet)

No query specified

-how to use alter table

Mysql > alter table employee rename emp

Query OK, 0 rows affected (0.22 sec)

Mysql > desc emp

->

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | eid | int (4) | YES | | NULL |

| | ename | varchar (50) | YES | | NULL |

| | birthday | date | YES | | NULL |

| | deptno | int (4) | YES | | NULL |

+-+ +

4 rows in set (0.07 sec)

-modify column names in the table

Alter table tabname change column old_col_name new_col_name col_definition

-change birthday to birthdate

Mysql > alter table emp change birthday birdate date

Query OK, 0 rows affected (0.20 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc emp

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | eid | int (4) | YES | | NULL |

| | ename | varchar (50) | YES | | NULL |

| | birdate | date | YES | | NULL |

| | deptno | int (4) | YES | | NULL |

+-+ +

-modify the type of fields in the table

Mysql > alter table emp modify eid int (5)

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

Alter table tabname modify [column] column_definition [FIRST | AFTER col_name]

Mysql > desc emp

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | eid | int (5) | YES | | NULL |

| | ename | varchar (50) | YES | | NULL |

| | birdate | date | YES | | NULL |

| | deptno | int (4) | YES | | NULL |

+-+ +

4 rows in set (0.00 sec)

Compare the difference between change and modify

1. Both change and modify can modify the definition of the table, except that the column name needs to be written twice after change, which is inconvenient.

2. The advantage of change is that you can modify the column names in the table, while modify cannot.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.