Mysql Learning Notes 2 Mui-getting started
-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.
