Getting started with MySQL

I. Creating a database table

1. Enter the MySQL database at the command line
Our operations are done on the command line, so make sure you get to the command line interface (for Windows, Start menu--run--enter CMD to bring up the command line interface, and Linux and Mac don't have to say more.) ）
You can enter the MySQL database at the command line with the following command:
c:\windows\system32> mysql-h localhost-u root-p

The basic explanations are as follows:

-H: followed by the hostname, which represents the MySQL database of which host you want to connect to.

-U: The user name is followed, and MySQL defaults to root.

-P: Specifies that a password must be used to log in to the MySQL database, which can be omitted if the password is empty.

The detailed explanations are as follows:

In front of we filled in after the localhost, that is to log on to the current host MySQL database, in this case, the-h parameter and localhost can be omitted completely, that is, the use of the following command can be logged to the local MySQL database:

2. View Existing Databases
After successfully logging in to the MySQL database, let's start by looking at how many databases are in the MySQL database (you can understand that the MySQL database is a large warehouse, and then there are several small warehouses under the warehouse, and we store our data in these silos). Let's do the following:
mysql> show databases;

3. Create your own database * (create database name;)

Next we will create a database of our own to save the data, see the following actions:
mysql> CREATE DATABASE Students_info;
Query OK, 1 row affected (0.01 sec)

Here we create a database named Students_info, and when we see the query OK, 1 row affected (0.01 sec), it means that our database was created successfully.

4.4. Select the database you want to manipulate
(Use database name [;] At this point you should notice that the semicolon ";" is placed in the brackets [], that is, the addition of semicolons are OK, which means that you use using Students_info to make the database selection is also possible, but in order not easy to confuse, suggest or add to better. ）

In the above database, not every one is what we need to operate, we just need to students_info the database we just created to operate on it, but, if you want to operate this database, first you need to tell the MySQL database system, Next you want to use Students_info this database to carry on the related operation, how to tell the system? Look at the following actions:
mysql> use Students_info;
Database changed

5. Create a database table in the database (CREATE TABLE database table name (column declaration);)

Mysql> CREATE TABLE Network3
()
-ID char (TEN) NOT null primary key,
, name char (+) NOT NULL,
, Sex char (6) NOT NULL,
-age int is not NULL,
Address char (+) NOT NULL
);

A. Create a column with the name ID; The data type is a char character type, the maximum length of the character is 10 characters, and the column content is not allowed to be empty, and this column is used as the primary key of this table to differentiate the table from the non-peers.

B. Create a column named name; the data type is a char character type with a maximum length of 16 characters, and the column content is not allowed to be empty.

About primary Key

Consider that, in the Student information table, if you store a lot of students information, like the above table, there may be two students, their name, sex, age, address are the same, this is normal! So, you have to have a data item is different, we above this table on the specified ID data item must be different, that is, the so-called primary key.

About column names

It is important to note that each column must have a different name.

6.MySQL database, database and database tables

Well, through the above, we not only learned to create a database in the MySQL database, but also learned to create a database table in the new database, we can simply clarify the relationship between the three:

MySQL database: Equivalent to a large warehouse, there are a lot of small warehouses;

Database: The equivalent of a small warehouse in a large warehouse, there are many small containers;

Database tables: Equivalent to the container in a small warehouse, we are in the store content;

Ii. database tables in the operational database

1. Adding data to a data table

After creating the database table, notice that there is no data stored in it, and we will insert the data into the table below:

mysql> INSERT INTO NETWORK3 values ("3114006441", "xpleaf", "Male", "Qingyuan");

Insert into database table name values (value value 1,value 2,.......);

Because there are 6 columns in the table I created, there are 6 values in the value. This is a way to insert data into a table, and of course, you can also specify that you insert data into a specific column, with the following command syntax:

Insert into database table name (column name 1, column name 2,... VALUES (value 1,value value 2,...);

2. Querying data in a database table
(1) Querying all data in the table

Mysql> select * from Network3;

Here the "*" is a wildcard character, it represents any match, if you have learned the basic regular expression, this should be better understood, so "*" in the column position, that is, to query all the columns of data; we are querying all the columns in the table NETWORK3 data.

(2) Querying the data for a specific column in a table

Mysql> select Id,name from Network3;

(3) Querying the data in a table by specific criteria
SELECT * from Network3 where name= ' xpleaf ';

SELECT * from Network3 where sex= ' female ';
SELECT * from Network3 where sex= ' male ' and address= ' Qingyuan ';
SELECT * from Network3 where age > 40;
SELECT * from NETWORK3 where name is like "%leaf";

Select column name from database table name where query condition;

3. Modifying data in a database table

The address of the #将name为 "Xpleaf" was modified to "Yuantan"
Update Network3 set address= "Yuantan" where name= ' xpleaf ';

#将所有人的age加1
Update NETWORK3 set age=age+1;

Update database column name set column is sufficient = new value where update condition;

4. Delete data from a database table

Delete from database table name where delete condition;
Delete from Network3 where name= ' Pei ';

Delete from database table name;
That is, do not add the deletion condition, but here you do not do so, the data we also need to use, one thing to note is that this command only to delete all the data in the table, and did not delete the table, the database table is still there, but this time it is an empty table, Just like when we just created it.

5. Modifying database tables
(1) Modifying columns in a database table
ALTER TABLE database table name change column name new data type [other];

Before modifying, look at the details of the original database table:

Mysql> describe Network3;

To begin the modification operation:

#将列名称 "Adress" is modified to "addr", others remain unchanged
mysql> ALTER TABLE NETWORK3 change address addr char (a) not null;

To view the details of a modified database table:

Mysql> describe Network3;

(2) Delete columns from database tables

Sometimes, over time, something in a database table is less important, or there is no meaning, and we can delete the whole column so that we can save some storage space.

The basic command syntax for deleting a column is as follows:

ALTER TABLE database table name drop column name;
ALTER TABLE NETWORK3 drop addr;
We removed the addr column, although the hint has been successfully deleted, but we habitually check the details of the database table after deleting a column:

(3) Renaming a database table
Through the previous operation, our database table has been refreshed, compared with the original structure has undergone some changes, then we will give this database table rename it.

The basic command syntax for renaming a database table is as follows:

mysql> ALTER TABLE NETWORK3 rename NEW_NETWORK3;
Then use show tables to see the database tables that exist at this time:

Mysql> Show tables;

(4) Deleting a database table

drop table database table name;
drop table New_network3;
then use show tables; to see the database tables in the database:

Mysql> Show tables;

(5) Adding database tables
ALTER TABLE NETWORK3 add Address_now char (+) NOT NULL

6. Deleting a database

At first we created the Students_info database in MySQL database to use it to store student information, now, we do not want to use the MySQL database to store information about students, then you can consider the Students_info this database deleted.

The basic command syntax for deleting a database is as follows:

drop database name;

Drop database Students_info;
We then use Show databases to view the database of the MySQL database:

Four, advanced operation

1. Modify the login password of MySQL database

Anyway, your MySQL database password will not always be the same, maybe one day you accidentally told your friends, and the inside stored a very important data, then you have to modify the database login password.

The basic command syntax for modifying a database login password is as follows:

Mysqladmin-r root-p Password New password

Note that this operation should be done before you log into the MySQL database.

2. Specify the database to be manipulated when logging in to the MySQL database

The following basic command syntax is given directly:

MYSQL-D Select the database name to manipulate-h hostname-U root-p

3. Creating a database table from a file

In front of us when creating a database table, the method used in the MySQL database command line interface in a line of input, so not only easy to lose, and once the wrong, and have to lose again, when you want to create a complex structure of the database table, such a method is obviously a row can not, At this point we can consider the way to use the file to operate.

The following basic command syntax is given directly:

MYSQL-D Select the database name to operate-h hostname-U root-p <. SQL type File

As you can see, in this part of the advanced operation, I did not give you the specific operation, it is because I think, through the previous operation, if you really operate, then you should have a certain understanding of the MySQL database, this time you should extrapolate, yourself to try to do these several advanced operations, When you have completed these steps, believe me, your basic command of MySQL database will definitely rise to a level! Therefore, I do not give a concrete operation here, otherwise, the meaning is not too big.

Getting started with MySQL