Working with MySQL Database

Inserting data into a table

The INSERT statement can be used to insert one or more rows of data into a database table, using the following general form:

Insert [into] table name [(column name 1, column name 2, column name 3, ...)] VALUES (value 1, value 2, value 3, ...);

where [] The content is optional, for example, to insert a record into the students table in the SAMP_DB database, execute the statement:

INSERT into students values (NULL, "Wang Gang", "male", 20, "13811371377");

Press ENTER to confirm that if you prompt Query Ok, 1 row affected (0.05 sec) indicates that the data was inserted successfully. If the insertion fails, check that the database you want to manipulate is selected.

Sometimes we just need to insert some of the data, or not in the order of the columns, you can insert them in this form:

INSERT into students (name, sex, age) VALUES ("Sun Lihua", "female", 21);

Querying data in a table

Select statements are commonly used to obtain data from a database based on certain query rules, with the following basic usage:

Select Column name from table name [query condition];

For example, to query the names and ages of all students in the students table, enter the statement select name, age from students; The results of the implementation are as follows:

Mysql> select name, age from students;+--------+-----+| Name   | Age |+--------+-----+| Wang Gang   |  20 | | Sun Lihua |  21 | | Engineering |  23 | | Zheng June |  19 | | Chen Fang   |  22 | | Zhang Weibong |  |+--------+-----+6 rows in Set (0.00 sec) mysql>

You can also use the wildcard character * to query all the contents of the table, statements: SELECT * from students;

query by specific criteria:

The WHERE keyword is used to specify the query condition, in the form of the Select column name from the table name where condition;

To query all gender-based information as an example, enter a query statement: SELECT * from students where sex= "female";

The WHERE clause does not only support the "where Column name = value" Query form, which is named equal to the value, and is supported for the operators of general comparison operations such as =, >, <, >=, <,! =, and some extension operators are [not] null, in, and like Wait a minute. You can also combine queries with OR and and for query criteria, and later learn more advanced conditional query methods, which are no longer introduced.

Example:

Find information for everyone over the age of 21: SELECT * from students where ages > 21;

Query everyone with the word "King" in the name: SELECT * from students where name is like "% king";

Information for anyone with a query ID of less than 5 and older than 20: SELECT * from students where id<5 and age>20;

Updating data in a table

The UPDATE statement can be used to modify the data in the table, using the following basic form:

Update table name set column name = new value where update condition;

Examples of Use:

Change the phone number with ID 5 to the default "-": Update students set tel=default where id=5;

Increase the age of all 1:update students set age=age+1;

Change the name of the phone number 13288097888 to "Zhang Weipeng" and change the age to 19:update students set name= "Zhang Weipeng" age=19 where tel= "13288097888";

Delete data from a table

The DELETE statement is used to delete data from a table, with the following basic usage:

Delete from table name where delete condition;

Examples of Use:

Delete the row with ID 2: Delete from students where id=2;

Delete all data older than 21 years: Delete from students where age<20;

Delete all data from the table: delete from students;

Modifications to the table after creation

The ALTER TABLE statement is used to modify the table after it is created, using the following basic usage:

Adding columns

Basic form: ALTER TABLE name add column list data type [after insertion position];

Example:

The last appended column of the table Address:alter table students add address char (60);

Inserts a column after the column named Age birthday:alter table students add birthday date after age;

modifying columns

Basic form: ALTER TABLE name change column Name column new name new data type;

Example:

Rename Table Tel column to Telphone:alter table students Change Tel telphone char (+) Default "-";

Change the data type of the Name column to char: ALTER TABLE students changes name name char (+) not null;

Delete Column

Basic form: ALTER TABLE name drop column name;

Example:

Delete birthday column: ALTER TABLE students drop birthday;

Renaming a table

Basic: ALTER TABLE name rename new table name;

Example:

Rename students table for workmates:alter table students rename workmates;

Delete entire table

Basic form: drop table name;

Example: Deleting a workmates table: drop-table workmates;

Delete entire database

Basic form: drop database name;

Example: Delete a samp_db database: Drop the DB samp_db;

Appendix Modifying the root user password

According to the installation of this article, the root user does not have a password by default, there are more ways to reset the root password, this is only a more common way.

Using the Mysqladmin method:

Open command Prompt interface, execute command: mysqladmin-u root-p password New password

After executing prompts to enter the old password to complete the password modification, when the old password is empty directly press ENTER to confirm.

Visual Management Tools MySQL Workbench

Although we can execute the MySQL statement at the command prompt through a line of input or through a redirected file, this is inefficient, because there is no automatic check of the syntax before execution, and the likelihood of some errors caused by input errors is greatly increased, so try some visual MySQL database management tools , MySQL Workbench is a visual management tool that MySQL officially provides to MySQL, where you can directly manage the contents of the database in a visual way, and the SQL Script Editor of MySQL Workbench supports syntax highlighting and input language The law examines, of course, its powerful, not limited to these two points.

MySQL Workbench Official Description: http://www.mysql.com/products/workbench/

MySQL Workbench download page: http://dev.mysql.com/downloads/tools/workbench/

Working with MySQL Database