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

This article mainly introduces the basic operation of the MySQL database example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

I. installation of the database

This will not be explained too much here, because there are too many tutorials for installing mysql on the Internet. With the mysql I gave you, according to this installation tutorial (MySQL installation tutorial), you will be able to install well.

After installing mysql, if you need to use the windows command window (that is, cmd) to operate mysql, then you need to configure environment variables, find bin under the installed mysql, and put its directory into the environment variable path, on the line. The way to detect success is to type mysql in the cmd command window, and there will be a large section of English, indicating success, otherwise failure, if not, then go to Baidu search tutorials.

2. Basic operation of database

1. Enable mysql service command

Net start mysql

2. Two ways to enter mysql

Plaintext entry: mysql-uroot-proot format: mysql-u account-p password

Ciphertext entry: mysql-uroot-p Press enter will prompt you for a password (Enter pssword:), and the password you write will be displayed as *.

3. Check all the databases in mysql (usually use uppercase in fixed word commands. Get used to this. If you read more and type more, you will know it.)

The first four databases are included in mysql, that is, necessary.

SHOW DATABASES

4. Create a database named test_1

Format: CREATE DATABASE database name

CREATE DATABASE test_1

5. Delete the database named test_1

Format: DROP DATABASE database name

DROP DATABASE test_1

Summary: learned three operations on the database: 1, view all databases 2, create databases 3, delete databases

Third, the basic operation of the data table

The relationship between data table and database and Mysql

Many databases are saved in mysql, and many tables can be saved in one database.

Add to the data table (create table) delete (delete table) change (modify table field) look up (query table structure). Note: the operation object here is the table, and the operation on the table is the structure of the table, and the operation of the fields in the table (fields and records should be clearly separated).

Premise: the table is under the database, so make sure which database to use first.

USE test_1

1. Create a data table

Format: CREATE TABLE data Table name (

Field name 1 data type [column level constraints]

Field name 2 data type [column level constraints]

Field name 3 data type [column level constraints]

);

Note: the format does not have to be written in this way, it can all be written on one line. But the observability of writing like that is very poor. I only write this so that I can see it more clearly.

Explanation:

1. The contents in parentheses in [] indicate that you can have or not.

2. Column level this "column" must be clear about what is being said. In a table, there are rows with columns, columns for vertical, and rows for horizontal.

3. The constraints will be discussed later.

1.1.Create an unconstrained student table

CREATE TABLE student (id INT (11), name VARCHAR (12), age INT (11))

Note: SHOW TABLES queries all tables under the database.

1.2.Create a constrained student table

Six constraints: primary key constraint, foreign key constraint, non-empty constraint, unique constraint, default constraint, auto-increment

1.2.1: primary key constraint

PRIMARY KEY (primary key): unique (unique) and cannot be empty (non-empty). Generally speaking, when adding records to a table, the data under this field cannot be repeated and cannot be empty. For example, take the table created above as an example, add two records to the table, if the id field uses a primary key constraint. Then the id cannot be the same and cannot be empty. Generally, each table has a field in the middle as the primary key, which uniquely identifies the record. Later, if you need to find the record, you can also confirm the record with this primary key, because the primary key is unique and non-empty, and the primary key of each record in a table is different, so the corresponding record can be found according to the primary key. Instead of multiple duplicate records. If there is no primary key, there will be many duplicate records in the table, which wastes storage space and consumes more resources when querying.

The degree of a field that is generally constrained by a primary key is habitually called the primary key of the table.

Single field primary key constraint

Either way is fine.

CREATE TABLE student (CREATE TABLE student (

Id INT (11) PRIMARY KEY, id INT (11)

Name VARCHAR (12), name VARCHAR (12)

Age INT (11) age INT

); PRIMARY KEY (id))

Multi-field primary key constraint (compound primary key)

The id and name city keys indicate that in future inserted records, id and name cannot be the same at the same time, for example. One record is id=1,name=yyy and the other record is id=1,name=zzz. It's okay to do this. It's not that the two fields you understand can't be the same.

CREATE TABLE student (CREATE TABLE student (

Id INT (11) PRIMARY KEY, id INT (11)

Name VARCHAR (12) PRIMARY KEY, name VARCHAR (12)

Age INT (11) age INT

); PRIMARY KEY (id,name))

1.2.2: foreign key constraint

What is a foreign key, for example, there are two tables, one is the emp (employee) table, the other is the dept (department) table, an employee belongs to a department, so how can we make ourselves in which department through the employee? Then you can only add a field to the employee table that represents the employee's department, then this field can only be the primary key in the storage dept (because the primary key is unique, which is the department, and then represents the employee's department, if it is the department name, some departments may have the same name. You can't tell the difference.) A field like this matches the characteristics of a foreign key, and you can use a foreign key constraint so that the field can only store the primary key of another table. If it is not constrained by a foreign key, there is no guarantee that the value stored in the field must be the primary key value of another table.

Characteristics of foreign key constraints:

1. Foreign key constraints can describe any field (including primary keys), can be empty, and can have multiple foreign keys in a table. However, the value in the foreign key field must be the primary key in another table.

2. The relationship between the two tables associated by foreign keys can be called a parent-child table or a master-slave table. The child table (slave table) has a table with foreign key fields, and the parent table (master table) is the table to which the foreign key field points.

3. The fields in which the child table is modified by foreign key constraints must be of the same type as the primary key field of the parent table.

Note: if a table has a field modified by a foreign key, it is said that the table has a foreign key (there is a foreign key). Instead of "is a foreign key"), and will give a name to the foreign key constraint in the table, so whether the table has a foreign key or not does not refer to the name of the field modified by the foreign key constraint, but whether there is a foreign key constraint in the table. In other words, it is wrong to say that the foreign key of this table is xxx (the name of the field in the table modified by foreign key constraints), but most people are used to it, although it doesn't have much impact, but it can cause some trouble when you need to understand something in many cases.

Format: CONSTRAINT foreign key name FOREIGN KEY (field name constrained by foreign key) REFERENCES primary table name (primary key field)

English explanation: CONSTRAINT: constraint REFERENCES: reference

CREATE TABLE tableA

(

Id INT (11)

Name VARCHAR (22)

Location VARCHAR (50)

PRIMARY KEY (id)

);

CREATE TABLE tableB

(

Id INT (11)

Name VARCHAR (22) NOT NULL

DeptId INT (11)

PRIMARY KEY (id)

CONSTRAINT tableA_tableB_1 FOREIGH KEY (deptId) REFERENCES tableA (id)

);

Explanation: a foreign key named tableA_tableB_1 in tableB associates two tables tableA and tableB. The field modified by the foreign key constraint is deptId in tableB, and the primary key field is id in tableA.

1.2.3: non-null constraint

NOT NULL. Fields modified by this constraint cannot be empty, and this constraint is included in the primary key constraint

CREATE TABLE tableA

(

Id INT (11)

Name VARCHAR (22) NOT NULL

Location VARCHAR (50)

PRIMARY KEY (id)

);

1.2.4: unique constraint

A field whose UNIQUE is modified by a unique constraint means that the value in the field is unique and cannot have the same value. In popular terms, it is like inserting two records. The values in the field in the two records cannot be the same.

CREATE TABLE tableA

(

Id INT (11)

Name VARCHAR (22) UNIQUE

Location VARCHAR (50)

PRIMARY KEY (id)

);

That is, in an inserted record, the name value of each record cannot be the same.

1.2.5: default constraint

Default specifies the default value for this column. For example, if there are more male students, the gender can be set to the default male. If the gender is not filled in when inserting a row of records, male can be added by default.

CREATE TABLE table

(

Id INT (11) PRIMARY KEY

Name VARCHAR (22) NOT NULL

DeptId INT (11) DEFAULT 1111

Salary FLOAT

);

1.2.6: automatically increase

AUTO_INCREMENT A table can only use AUTO_INCREMENT for one field, and fields that use this constraint can only be of integer type (any integer type TINYINT,SMALLIN,INT,BIGINT). The default value is 1, which means increasing from 1. Generally, it is used for the primary key, which is automatically increased, so that the value of each primary key is different, and there is no need for us to manage it, so that the primary key can be generated automatically.

CREATE TABLE table (id INT (11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR (22) NOT NULL)

2. Query table structure

2.1. View the basic table structure statement

The format 1:DESCRIBE table name / DESC table name these two functions are the same, abbreviated the word describe

DESCRIBE student

2.2. View the statement that creates the table

Format: SHOW CREATE TABLE table name

SHOW CREATE TABLE student

The format shown in this way is too bad to see clearly, so there is the following statement

Format: SHOW CREATE TABLE table name\ G

SHOW CREATE TABLE student\ G

3. Modify the data table

Modifying the data table includes: adding, deleting and modifying the fields in the table. The keyword used in this is ALTER

3.1. Modify the table name

Format: ALTER TABLE RENAME [TO]

Change the student table name to student1 (change it back after the change)

ALTER TABLE student RENAME TO student1

3.2. Modify the field name in the table

Format: ALTER TABLE CHANGE

Change the name field name in the student table to username

ALTER TABLE student CHANGE name username VARCHAR (30)

3.3. Modify the data type in the table

Format: ALTER TABLE MODIFY

ALTER TABLE student MODIFY username VARCHAR (20)

Explanation: you can only modify the data type of the field name, but the principle is the same as what change does above. There is also a process of modifying the field name, except that the modified field name is the same as the field name before modification, but the data type is different.

3.4. Modify the arrangement position of the fields

| method 1:ALTER TABLE MODIFY FIRST | AFTER |

Explanation: put the position of field 1 first, or after the specified field 2

ALTER TABLE student MODIFY username VARCHAR (20) AFTER age

| method 2:ALTER TABLE CHANGE FIRST | AFTER |

Explanation: in fact, it is the same, field 2 will overwrite field 1, and then sort

ALTER TABLE student CHANGE username username VARCHAR (20) AFTER age

Summary

The difference between CHANGE and MODIFY?

The principle is the same, MODIFY can only modify data types, but CHANGE can change data types and field names, which means that MODIFY is a more specific operation of CHANGE. You may find it uncomfortable to use CHANGE to change only one data type, so add a keyword MODIFY that can change the data type directly.

3.5. Add a field

Format: ALTER TABLE ADD [constraints] [FIRST | AFTER]

Explanation: add a new field to a specific location. If you do not specify a location, the default is the last one.

ALTER TABLE student ADD sex VARCHAR (11)

3.6. Delete field

Format: ALTER TABLE DROP

ALTER TABLE student DROP sex

3.7. Delete the foreign key constraint of the table

Format: ALTER TABLE DROP FOREIGN KEY

Note: the foreign key constraint name does not refer to the field name modified by the foreign key constraint, remember, but the name we chose when creating the foreign key constraint relationship.

3.8. Change the storage engine of the table

Format: ALTER TABLE ENGINE=

At present, I am not very clear about this storage engine, although I know which engines there are, but I am not clear if I go a little deeper, so I intend to save it for later.

4. Delete the table

4.1. Delete unassociated tables

Format: DROP TABLE

ALTER TABLE student

4.2. Delete the primary table associated with other tables

This is a more important point, in the two tables with foreign key association, if you delete the main table, then it can not be deleted, and will report an error. Because there's a table that depends on him. What are we going to do? There are two ways to deal with this situation.

1. Delete your child table first, and then delete the parent table. This will achieve the purpose of deleting the parent table, but the child table will also be deleted.

2. First undo the foreign key relationship, and then delete the parent table, which can also achieve the goal, and retain the child table, and delete only the parent table that we do not need. How to delete a foreign key relationship is explained in 3.7.

Thank you for reading this article carefully. I hope the article "sample Analysis of the basic Operation of MySQL Database" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.