MYSQL Getting started: basic operations on databases and tables _ MySQL

Getting started with MYSQL: basic database and table operations bitsCN.com

Step 8: basic database and table operations

Related links:

MYSQL: Basic operations

Http: // database/201212/173868 .html

MYSQL 2: use regular expressions to search

Http: // database/201212/173869 .html

MYSQL 3: full text search

Http: // database/201212/173873 .html

MYSQL entry 4: MYSQL data types

Http: // database/201212/175536 .html

MYSQL entry 5: MYSQL character set

Http: // database/201212/175541 .html

MYSQL getting started 6: MYSQL operators

Http: // database/201212/175862 .html

MYSQL entry 7: MYSQL common functions

Http: // database/201212/175864 .html

I. database operations

1. view the database

Show databases [like ''];

Example:

Mysql> show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Luomian |

| Mydb |

| Mysql |

| Net80576314 |

| Phpcms_uat |

| Phpcmsv9 |

| Phpcmsv9_new |

| Rutiao |

| Szexplorers |

| Test |

| V9test1 |

+ -------------------- +

Mysql> show databases like 'php % ';

+ ----------------- +

| Database (php %) |

+ ----------------- +

| Phpcms_uat |

| Phpcmsv9 |

| Phpcmsv9_new |

+ ----------------- +

2. create a database

Create database [if not exists] dbname;

Example:

Mysql> create database if not exists mydb;

3. select the required Database

Use dbname

Example:

Mysql> use mydb;

Database changed

4. delete a database

Drop database [if exists] dbname;

Example:

Mysql> drop database if exists mydb;

II. operation table

1. display table

Show tables;

Example:

Mysql> show tables;

+ ----------------------- +

| Tables_in_test |

+ ----------------------- +

| Newname |

| Productnotes |

| Test_char |

| Test_inn |

| Test_inn2 |

| Test_priority |

| Test_trans |

| Test_view |

+ ----------------------- +

2. create a table

Example:

Mysql> create table user (

-> Id int (10) not null auto_increment primary key,

-> Name varchar (50) default 'n'/a' not null,

-> Sex char (1) null

->) Engine = InnDB;

3. copy a table

Example:

Mysql> create table student select * from user;

Mysql> create table teacher like user;

4. rename a table

Mysql> rename table teacher to senior_teacher;

Mysql> alter table student rename to senior_student;

5. delete a table

Mysql> drop table if exists senior_teacher;

6. view the table creation statement

Mysql> show create table student;

+ --------- + -------------------------------------

| Table | Create Table

+ --------- + -------------------------------------

| Student | create table 'student '(

'Id' int (10) not null default '0 ',

'Name' varchar (50) not null default 'n'/',

'Sex 'char (1) DEFAULT NULL

) ENGINE = MyISAM default charset = latin1 |

+ --------- + -------------------------------------

7. view the table structure

Mysql> desc student;

+ ------- + ------------- + ------ + ----- + --------- + ------- +

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

+ ------- + ------------- + ------ + ----- + --------- + ------- +

| Id | int (10) | NO | 0 |

| Name | varchar (50) | NO | N/A |

| Sex | char (1) | YES | NULL |

+ ------- + ------------- + ------ + ----- + --------- + ------- +

8. modify the table structure

Mysql> alter table student add bithday date null;

Mysql> alter table student modify bithday datetime;

Mysql> alter table student change bithday birt datetime;

Mysql> alter table student drop column bithday;

9. operate the data in the table

Mysql> select * from student;

+ ---- + ------ + --------------------- +

| Id | name | sex | birt |

+ ---- + ------ + --------------------- +

| 0 | jack | 1 | 00:00:00 |

+ ---- + ------ + --------------------- +

Mysql> insert into senior_student select * from student;

Mysql> insert into student (name, sex, birt) values ('Jack', '1', current_date ());

Mysql> update student set sex = 0 where name = 'Jack ';

Mysql> delete from student where name = 'Jack ';

10. create and view indexes

Mysql> create index idx_student_name on student (name );

Mysql> show index from student;

+ --------- + ------------ + ------------------ + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+ --------- + ------------ + ------------------ + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +

| Student | 1 | idx_student_name | 1 | name | A | NULL | BTREE |

+ --------- + ------------ + ------------------ + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +

BitsCN.com