Mysql disciples (2): Mysql views, creates, and changes the database and table _ MySQL

Mysql disciples (2): Mysql views, creates, and changes databases and tables bitsCN.com

1. check whether

I want to see how many databases, how many tables, and what are in the table. Then you can:

Graphic interface:

Command:

Check the number of databases: Note that s is followed

# View show databases;

# View the table USE blog; show tables;

# View the column show columns from auth_user in the table;

DESCRIBE auth_user;

The describe table name is a shortcut for the show columns from table name.

II. modify the mysql database password

Method 1:

Use phpmyadmin to directly modify the user table of the Mysql database. You can also use Navicat for Mysql to directly modify the connection attributes.

Method 2: Use mysqladmin

# Cmd, run DOS, cd to the bin folder of mysql, and then execute the following D:/Mysql/bin> mysqladmin-u root-p password newPwd # Enter password :( Enter the original password here) # newPwd indicates the new password.
Open mysql and enter the new password.

Format: mysqladmin-u username-p old password new password.

For more information, see: http://wenku.baidu.com/view/43db0b62ddccda38376bafff.html

3. create databases and tables

SHOW statement to find out what database exists on the server:

Mysql is required because it describes the user's access permissions. The test database is often used as a workspace for users to test their skills.

Access the database and use the use statement

Note that USE, similar to QUIT, does not require a semicolon. (If you like it, you can use a semicolon to terminate such a statement ).

Create a database

Mysql> create database name;

Mysql> USE database name; mysql> create table name (field name: VARCHAR (20); field name: CHAR (1 ));

For example:

# Create a table use demo; create table pet (name varchar (20), # name owner varchar (20), # Master species varchar (20), # type sex char (1 ), # Gender birth date, # Birthdate death date # date of death)

To verify that your table is created as expected, use a DESCRIBE statement:

Delete database:

Mysql> drop database name;

Delete a data table:

Mysql> drop table name;

Clear Records in the table:

Mysql> delete from table name;

Create a table (complex form ):

# Create a customer table: create table customers (id int not null auto_increment, name char (20) not null, address char (50) null, city char (50) null, age int not null, love char (50) not null default 'no habbit ', primary key (id) engine = InnoDB;
# SELECT last_insert_id (); this function returns the last auto_increment value. # default value: default 'no habbit ', # engine type, mostly engine = InnoDB. if the engine = statement is omitted, the default engine (MyISAM) is used)

Change the table structure:

# Add an alter table pet add des char (100) null; # delete alter table pet drop column des;

Rename a table:

# Rename table pet to animals;

Add id field

You can perform the following operations:

# Add the id field alter table pet add id int not nullprimary key auto_increment first;

Add a foreign key:

Example: tech.ddvip.com/2007-05/118009486725743.html

Assume that a computer manufacturer stores the product information of the whole machine and accessories in its database. The table used to save the product information of the entire machine is called Pc; the table used to save the accessory supply information is called Parts. In the Pc table, there is a field used to describe the CPU model used by the computer. in the Parts table, there is a field describing the CPU model, we can think of it as a list of all CPU models. Obviously, the CPU used by the computer produced by this manufacturer must be the model existing in the supply Information Table (parts. At this time,There is a constraint between the two tables (constraint)-- The CPU model in the Pc table is subject to the model constraints in the Parts table.

Table definition:

# PartsCREATE TABLE parts (... field Definition ..., model VARCHAR (20) not null ,... field Definition ...); # pcCREATE TABLE pc (... field Definition ..., cpumodel VARCHAR (20) not null ,... field Definition ...};

Since the constraints exist, you need to set indexes.

Set indexes:

To set a foreign key, in the reference table (referencing table, that is, the Pc table) and the referenced table (referenced table, that is, the parts table, indexes must be set for both fields ).

For the Parts table:

ALTER TABLE parts ADD INDEX idx_model (model);

This statement adds an index to the parts table, which is created on the model Field and named idx_model.

Similar to Pc tables:

ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel);

In fact, these two indexes can be set during table creation. This is just to highlight its necessity.

Define foreign keys:

The following describes how to create a constraint between two tables ". Because the CPU model of the pc must refer to the corresponding model in the parts table, we set the cpumodel field of the Pc table to "foreign key" (foreign key ), that is, the reference value of this key comes from other tables.

ALTER TABLE pc ADD CONSTRAINT fk_cpu_modelFOREIGN KEY (cpumodel)REFERENCES parts(model);

The first line is to set a foreign key for the Pc table. name this foreign key fk_cpu_model;

The second row sets the cpumodel field of the table as a foreign key;

The third row indicates that the foreign key is restricted by the model field of the Parts table.

Cascade operations:

Technicians found that all the cpu (possibly many) models of a series entered in the parts table a month ago had a wrong letter, and now they need to correct it. We hope that when the Referenced columns in the parts table change, the Referencing Column in the corresponding table can also be automatically corrected.

When defining a foreign key, you can add such a keyword at the end:

ON UPDATE CASCADE;

That is to say, when the primary table is updated, the sub-table (s) generates a chain update action. it seems that some people like to call this "cascade" operation.

If the statement is completely written, it is:

ALTER TABLE pc ADD CONSTRAINT fk_cpu_modelFOREIGN KEY (cpumodel)REFERENCES parts(model)ON UPDATE CASCADE;

In addition to cascade, there are also restrict (restriction; constraint; limitation), which refer to operations such as (prohibiting the change of the primary table) and set null (the corresponding field of the sub-table is SET to NULL.

You can view the table structure on the graphic interface:

IV. alter database syntax

ALTER {DATABASE | SCHEMA} [db_name]    alter_specification [, alter_specification] ...alter_specification:    [DEFAULT] CHARACTER SET charset_name  | [DEFAULT] COLLATE collation_name

Alter database is used to change the global features of a DATABASE. These features are stored in the db. opt file in the Database Directory. To use alter database, you must obtain the ALTER permission for the DATABASE.

The character set clause is used to change the default database character set. The COLLATE clause is used to change the default database order. The database name can be ignored. in this case, the statement corresponds to the default database. You can also use alter schema.

V. alter table syntax

See Mysql 5.1 Manual 13.1.2. alter table syntax

Alter table is used to change the structure of the original TABLE. For example, you can add or delete columns, create or cancel indexes, change the type of original columns, or rename columns or tables. You can also modify the table comment and table type.

You can use FIRST or AFTER col_name to add columns at a specific position in a table row. By default, columns are added to the end. You can also use FIRST and AFTER in CHANGE or MODIFY statements.

BitsCN.com