Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to view, create and change databases and tables in Mysql". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Modify mysql database password

Method 1:

Using phpmyadmin, modify the user table of the Mysql library directly. Or you can use Navicat for Mysql to modify the connection properties directly.

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 means the new password and then open mysql and enter the new password directly

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

Create databases, tables

The SHOW statement finds out what databases currently exist on the server:

Mysql is required because it describes user access rights, and test databases often serve as workspaces for users to try out.

To access the database, use the use statement

Note that USE, similar to QUIT, does not require a semicolon. If you like, you can terminate such a statement with a semicolon; it doesn't matter.

Create a database

Mysql > CREATE DATABASE library name; mysql > USE library name; mysql > CREATE TABLE table name (field name VARCHAR (20), field name CHAR (1)); # create table use demo Create table pet (name varchar (20), # name owner varchar (20), # Master species varchar (20), # species sex char (1), # gender birth date, # date of birth death date # date of death)

To verify that your table is created the way you want it to, use a DESCRIBE statement:

Delete the database:

Mysql > DROP DATABASE library name

Delete the datasheet:

Mysql > DROP TABLE table name

Empty the records in the table:

Mysql > DELETE FROM table name

Create a table (complex form):

# create 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 a column of alter table pet add des char (100) null;# and delete alter table pet drop column des

Rename the table:

# rename table rename table pet to animals

Add id field

You can do the following:

# add id field alter table pet add id int not null primary key auto_increment first

Add foreign keys:

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

If a computer manufacturer keeps product information about the whole machine and accessories in its database. The table used to store the product information of the whole machine is called Pc;. The table used to store the supply information of accessories is called Parts. There is a field in the Pc table that describes the CPU model used on this computer; in the Parts table, there is a field describing the CPU model, which we can think of as a list of all CPU models. Obviously, the CPU used by the computer produced by this factory must be the model that exists in the supply information table (parts). At this point, there is a constraint relationship between the two tables (constraint)-the CPU model in the Pc table is constrained by the model in the Parts table.

Table definition:

# parts CREATE TABLE parts (... Field definition..., model VARCHAR (20) NOT NULL,... Field definition.); # pc CREATE TABLE pc (... Field definition..., cpumodel VARCHAR (20) NOT NULL,... Field definition.}

Now that it exists in the constraint relationship (constraint), you need to set the index.

Set up the index:

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

For the Parts table:

ALTER TABLE parts ADD INDEX idx_model (model)

This means to add an index to the parts table, which is based on the model field, and give the name to the idx_model.

The same is true for Pc tables:

ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel)

In fact, these two indexes can be set when the table is created. This is just to highlight its necessity.

Define foreign keys:

The following is to establish the kind of "constraint" described earlier between the two tables. Because the CPU model of pc must refer to the corresponding model in the parts table, we set the cpumodel field of the Pc table to "FOREIGN KEY", that is, the reference value of this key comes from other tables.

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model FOREIGN KEY (cpumodel) REFERENCES parts (model)

The first line says to set a foreign key for the Pc table, giving the foreign key a name of fk_cpu_model

The second line is to set the cpumodel field of this table to a foreign key.

The third line says that the foreign key is constrained by the model field of the Parts table.

Cascade operation:

The technician found that the models of a series of cpu (there may be many) entered into the parts table a month ago all typed a wrong letter and now need to be corrected. What we hope is that when those Referenced Column in the parts table change, the Referencing Column in the corresponding table will also be automatically corrected.

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

ON UPDATE CASCADE

That is, when the main table is updated, the child table (us) produces a chain update action, which some people like to call a "cascading" operation.

If you write this sentence in its entirety, it is:

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model FOREIGN KEY (cpumodel) REFERENCES parts (model) ON UPDATE CASCADE

In addition to cascade, there is also restrict (Chinese meaning: limit; constraint; qualification), which refers to operations such as (forbidding changes in the main table), SET NULL (the corresponding fields in the child table are set to empty), and so on.

Then we can view the structure of the table in the graphical interface:

IV. ALTER DATABASE grammar

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 properties of the database. These features are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need to obtain database ALTER permissions.

The CHARACTER SET clause is used to change the default database character set The COLLATE clause is used to change the default database ordering. The database name can be ignored, and at this point the statement corresponds to the default database. You can also use ALTER SCHEMA.

That's all for "how to view, create, and change databases and tables in Mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.