Mysql database usage and understanding (based on ubuntu14.0.4LTS64-bit)
1. mysql database composition and related concepts first understand that mysql is a relational database. The biggest difference between mysql and non-relational databases is that the table concept is different. + The entire mysql environment can be understood as one of the largest databases: A + database B created with mysql belongs to A and is the data warehouse, which is equivalent to the folder + Data Table C in the system: is the specific place where data is stored. It is equivalent to A file in the system. A database B contains several data tables C (note that database B here is different from database A) + record D: A row in a data table is called a record. Therefore, when creating a data table, you must create an id column to identify the number of records. The values of the id column cannot be the same, it must be unique, just like the ID card number. A table C can have multiple records D + field E: each column in a table is called a "field". Each field in the database is specified. For example: field data type, null and null 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. Common command instances for basic database operations (in the ubuntu environment. 2.1 log on to mysql: mysql-u root-p-u: Log On with the user name,-p: enter the password
2.2 view database: after entering the above command, you will be asked to enter the password. After successful login, the interface shown in figure is displayed to view the data B that comes with system.
Show databases;Note that there must be no fewer semicolons, such as displaying the database that comes with the system.
2.3 create a database:
Create database mytestIn this way, a database B named mytest is created.
2.4 In the above four databases, how should we select the desired database?
Use mytestIn this way, our database mytest is selected.
2.5 if you want to delete a database:
Drop database mytestThis will delete the database and will not remind you. 2.6 view the current status:
Status;
2.7 display table C in the current database in the following two ways. A:
Show tables;B:
Show tables FROM mytest;. Mytest is the database we created.
2.8 create and delete a data table
Create table mytable (id int, name int );
Drop table mytable;
2.9 how to use a data table, that is, how to insert data into the data table?
Insert into mytable (id, name) values (1, 10 );2.10 query table data,
Select id, name from mytable where id = 1 order;Or
Select * from mytable
2.11 updating data in the table,
Update mytable set name = 20 where id = 1;
2.12 delete a table:
Delete from mytable;Or
Drop table mytable;2.13 modify the table structure (1) Add a Field Format:
Alter table table_name add column (field Name field type );-- This method contains parentheses (2) to specify the field insertion position:
Alter table table_name add column field Name field type after a field;(3) modify the field name/Type
Alter table table_name change the type of the new field in the old field name;(4) change the table name
Alter table table_name rename to new_table_name;(5) Clear all data in the table at one time
Truncate table table_name;This method also deletes a field starting from 1 (6) in the table's number generator (ID:
Alter table table_name drop field name;2.14 modify the password format:
Mysqladmin-u username-p old password new password;2.15 display table structure:
Describe mytables;
2.16 display the current database and user
Show database (); show user ();
3. Note: 3.1 if you forget the extra points after you press Enter when you press the command, you do not need to re-run the command. You only need to press a semicolon to press Enter. 3.2 you can use the cursor up/down key to call up previous commands.
