Detailed explanation of MySQL database (based on Ubuntu 14.0.4 LTS 64 bit)

1. Composition and related concepts of MySQL database

First of all, you need to understand that MySQL is a relational database. The biggest difference between it and a non-relational database is the concept of tables.

+The entire MySQL environment can be understood as a single largest database: A

+Database B created with mysql belongs to A and is a data warehouse, equivalent to a folder in the system

+Data table C: is the specific place to store data, equivalent to the file in the system. A database B contains several data tables C (note that database B and A here are different)

+Record D: A row in a data table is called a record. Therefore, when we create a data table, we must create an id column to identify "which record this is". The value of the id column cannot be the same and must be unique, just like an ID number. There can be multiple records in a table C.

+Field E: Each column in a table is called a "field". Each field in the database has regulations, such as: field data type, empty or not empty judgment, automatic growth, etc. A record D can have multiple fields E.

+Data F: The intersection of rows and columns is the real "data" F.

2. Basic database operations (under Ubuntu environment) and common command examples.

2.1 Log in to mysql: mysql-uroot-pu: log in with username, -p: enter password

2.2 View the database: After entering the above command, you will be asked to enter the password. After successful login, the interface shown above will be displayed to view the data B that comes with system A

showdatabases; Note that the semicolon must be included. The following figure shows the system's built-in database.

2.3 Create a database: create database mytest; this creates a database B named mytest.

2.4 Among the above 4 databases, how do we choose the database we want? usemytest, so our database mytest is selected.

2.5 If you want to delete the database: dropdatabasemytest. This will delete the database without any reminder.

2.6 Check the current status: status;

2.7 Display table C in the current database in the following two ways. a:showtables;b:showtablesFROMmytest;.Mytest is the database we created.

2.8 Creating and Deleting Data Tables

createtablemytable(idint,nameint);

droptablemytable;

2.9 How to use the established data table, that is, how to insert data into the data table? insertintomytable(id,name)values(1,10);

2.10 Query the data in the table, selectid,namefrommytablewhereid=1order; or select*frommytable

2.11 Update the data in the table, update mytable set name=20 where id=1;

2.12 Deleting a table: deletefrommytable; or droptablemytable;

2.13 Modification of table structure

(1) Add a field format:

altertabletable_nameaddcolumn(field name field type);—This method has brackets

(2) Specify the location where the field is inserted:

altertabletable_nameaddcolumnfield namefield typeafter a field;

(3) Modify field name/type

altertabletable_namechange old field name new field name new field type;

(4) Change the name of the table

altertabletable_namerenametonew_table_name;

(5) Clear all data in the table at one time

truncatetabletable_name; This method will also make the ID in the table start from 1

(6) Delete a field:

altertabletable_namedrop field name;

2.14 Change password format: mysqladmin -u username -p old password password new password;

2.15 Display the structure of the table: describemytables;

2.16 Display the current database and user show database(); show user();

3 Note:

3.1 If you forget to add a semicolon after pressing Enter when typing a command, you do not need to retype the command, just type a semicolon and press Enter.

3.2 You can use the cursor up and down keys to call up previous commands.

Summarize

The above is all the content of this article about the detailed explanation of MySQL database (based on Ubuntu 14.0.4 LTS 64-bit). I hope it will be helpful to everyone. Interested friends can refer to other related topics on this site. If there are any deficiencies, please leave a message to point them out. Thank you friends for supporting this site!