Mysql Database Operation statement

MySQL Format statement specification
How do I log into your database?
Example!
If you are compiling the installation of the flowers, then you have to compile the installation of the directory to go, my is installed into the/usr/local/mysql database: Cd/usr/local/mysql bin/mysql-u root-p and then enter the password
mysql-> show databases; View database
Remember to take each sentence to carry;

First, MySQL commonly used statements
Create, delete, and most basic queries: Display Database mysql->showdatabases; Create database mysql->createdatabase database name; Delete database Mysql->drop database name; Select database mysql->use database name to create TABLE mysql->create tables table name (name varchar), sex (char (1), birth date); (the parameter in parentheses, which is the primary key) Delete table Mysql->drop tables name; Display the contents of the table mysql->show tables; Displays the structure of the table mysql->describe the database name. Table name;
Update: 1, Action on column: Add a field to a table mysql->alter table yourtableadd name varchar (notnull); Delete a field mysql->altertable yourtable drop name; 2, the operation of the line: Insert a record mysql->insert into Mytablevalues (' Summer ', ' m ', ' 1983-08-24 '); Delete a record mysql->deletefrom mytable where name= ' summer '; Modify a record mysql->updatemytable set sex= ' VM ' where name= ' summer '; Insert more than one record Mysql->insert intomytable select *fromyourtable; In this form of an INSERT statement, the data value of the new row is not explicitly specified in the body of the statement. Instead, a database query specified in the statement. The logical limit of the query:» The query cannot contain an ORDER BY clause. » The query result should contain the same columns as the number of columns in the INSERT statement, and the data type must be column-compatible. )
Simple query: 1. Display the column name in the query results A. With the AS keyword: select name as ' name ' from students order by age B. Direct: Select name ' name ' fromstudents order b Y age. Exact lookup: A. With in bounds: SELECT * from students where native in (' Hunan ', ' Sichuan ') b.between...and:select * from students where AG e between And30 C. Comparison tester: (including =,<>,<,<=,>,>=) select* from students where name = ' Li Shan ' d.like:select * fro M students wherename like ' li% ' (note that there is a "%" in the query condition, then it is a partial match, and there is a message in it, that is, to find a match that begins with "Li". Therefore, if the query has "Li" all objects, should command: '% li% '; If the second word is Li, it should be ' _ Li% ' or ' _ Li ' or ' _ li _ '. ) e.[] Match checker: SELECT * from courses where CNO like ' [ac]% ' (representation or relationship, with "in (...)" Similarly, and "[]" can represent a range, such as: SELECT * from courses where cnolike ' [a-c]% ') Note: about this character I used in MySQL when MySQL took it as two ordinary self-character processing. [^]stockname like ' [^f-m]% '---------(^ exclude specified range) A.count () the total, such as: SelectCount (*) from students (for the total number of students) b.avg (column) averaging, For example: Selectavg (Mark) from grades wherecno= ' B2 ' c.max (column) and min (column) for maximum and minimum
--------------------------------------------------------------------------------------------------------------- ------------------------------second, some commonly used MySQL statement Php+mysql+linux is now gradually becoming a classic combination of small Web servers. Building and debugging MySQL databases in a Windows environment is a top choice for many Web developers.
Here are some common MySQL statements:
First, connect MySQL. Format: mysql-h host address-u user name-p user Password 1, example 1: Connect to MySQL on this computer. First open the DOS window, and then enter the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, The prompt for MySQL is:mysql> 2, example 2: Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command: mysql-h110.110.110.110-uroot-pabcd123 (Note: You and root can not add a space, the other is the same) 3, quit MySQL command: Exit (enter)
Second, change the password. Format: Mysqladmin-u User Name----old password password new password 1, example 1: Add a password to root ab12. First enter directory Mysqlbin under DOS, and then type the following command Mysqladmin-uroot-password AB12 Note: Because Root does not have a password at the beginning, the-p old password can be omitted. 2, Example 2: Then change the root password to djg345. MYSQLADMIN-UROOT-PAB12 Password djg345
Third, add new users. (Note: Unlike above, the following is because it is a command in the MySQL environment, so it is followed by a semicolon as the command Terminator) format: Grant Select on database. * To User name @ login host identified by "password" Example 1, Add a user test1 password to ABC so that he can log on on any host and have access to queries, insertions, modifications, and deletions to all databases. First use the root user to connect to MySQL, and then type the following command: Grant Select,insert,update,delete on * * to [email protected] "%" identified by "ABC"; But example 1 increases the user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log on your MySQL database and to your data can do whatever, workaround see Example 2. Example 2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host. Grant Select,insert,update,delete on mydb.* to [e-mail protected] by "ABC"; If you do not want to test2 have a password, you can call another command to erase the password. Grant Select,insert,update,delete on mydb.* to [e-mail protected] by "";
4, the first command mysql> Select version (), current_date (); +----------------+-----------------+ | Version () | Current_date () | +----------------+-----------------+ | 3.23.25a-debug | 2001-05-17 | +----------------+-----------------+ 1 row in Set (0.01 sec) mysql>
This command requires the MySQL server to tell you its version number and the current date. Try the above command with a different case, and see what the result is. The result shows that the case of the MySQL command is consistent. Practice the following: Mysql>select (20+5); Mysql>select (20+5) *4,sin (Pi ()/3); Mysql>select (20+5) as Result,sin (Pi ()/3); (as: Specify Kana as result)
5, Multiline statement a command can be divided into multiple lines of input until a semicolon ";" is present: Mysql> Select, USER (), now ()->; +--------------------+---------------------+ | USER () | Now () | +--------------------+---------------------+ | [Email protected] | 2001-05-17 2215 | +--------------------+---------------------+ 1 row in Set (0.06 sec) Mysql> Note the middle of the comma and the last semicolon used.
6, one line multiple command input the following command: Mysql> Select USER (); Select now (); +------------------+ | USER () | +------------------+ | [Email protected] | +------------------+ 1 row in Set (0.00 sec)
+---------------------+ | Now () | +---------------------+ | 2001-05-17 23:06:15 | +---------------------+ 1 row in Set (0.00 sec) Mysql> Note the middle semicolon, separating the commands with semicolons.
7, display the current database mysql> show databases; +----------+ | Database | +----------+ | mysql| | Test | +----------+ 2 row in Set (0.06 sec) mysql>
8, select the database and display the currently selected database mysql> use MySQL database changed mysql> (the Take and QUIT commands do not require a semicolon end.) ) mysql> Select Database (); +---------------+ | Database () | +---------------+ | MySQL | +---------------+ 1 row in Set (0.00 sec)
9, display the current database exists in the table mysql> show TABLES;
10, the contents of the Display table (db) Mysql>select * from DB;
11, the cancellation of the command when the command input error and cannot be changed (multi-line statement case), as long as the semicolon appears before you can use C to cancel the command mysql> Select, User (), C mysql>
These are some of the most basic operational commands that are most commonly used, and can be tightly covered with many exercises.
Study article
Having learned some of the most basic operational commands, let's learn how to create a database and database table.
1. Use the show statement to find out what database currently exists on the server:
Mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql| | Test | +----------+ 3 rows in Set (0.00 sec)
2. Create a database Abccs mysql> create databases Abccs; Note the sensitivity of the different operating systems to the case.
3. Select the database you have created mysql> use ABCCS database changed at this point you have entered the databases you have just established ABCCS.
4. Create a database table first look at what tables are present in your database: mysql> SHOW TABLES; Empty Set (0.00 sec) indicates that there are no database tables in the database that you just created. Next, create a database table MyTable:
We are going to create a birthday table for your employees whose contents include the employee's name, gender, date of birth, and city of birth. Mysql> Create TABLE mytable (name VARCHAR), Sexchar (1), birth DATE, Birthaddr VARCHAR (20)); Query OK, 0 rows Affected (0.00 sec)
Because the column values of name and Birthadd are variable, you choose varchar, whose length is not necessarily 20. You can choose any length from 1 to 255, and if you need to change its word size later, you can use the ALTER TABLE statement. ）; The gender can be represented by a single character: "M" or "F", so a char (1) is chosen; The birth column uses the date data type.
After creating a table, we can take a look at the results we just made, and show TABLES what tables are in the database: Mysql> show TABLES; +---------------------+ | Tables in Menagerie | +---------------------+ | mytables| +---------------------+
5, the structure of the display table: mysql> DESCRIBE mytable; +-------------+-------------+------+-----+---------+-------+ | Field | type| Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | name| varchar (20) | YES | | null| | | sex | char (1) | YES | | null| | | Birth | date| YES | | null| | | deathaddr | varchar (20) | YES | | null| | +-------------+-------------+------+-----+---------+-------+ 4 rows in Set (0.00 sec)
6. Add a record to the table we first use the Select command to view the data in the table: mysql> select * FROM MyTable; Empty Set (0.00 sec) This means that the table you just created is not yet documented.
Add a new record: mysql> INSERT INTO MyTable-values (' Abccs ', ' f ', ' 1977-07-07 ', ' China '); Query OK, 1 row affected (0.05 sec) then use the Select command above to see what has changed.
We can add the records of all employees to the table in this way, one at a line.
7. Loading data into a database table in text mode if you enter it in one line, it is troublesome. We can add all the records to your database table in the form of a text file. Create a text file "Mysql.txt", each line containing a record, separating the values with a locator (tab), and given in the order of the columns listed in the CREATE TABLE statement, for example:
Abccs F 1977-07-07 China Mary F 1978-12-12 USA Tom M 1970-09-02 USA
Use the following command to load the text file "Mytable.txt" into the mytable table: mysql> load DATA LOCAL INFILE "Mytable.txt" intotable pet;
Then use the following command to see if the data has been entered into the database table: mysql> select * FROM MyTable;
In this article we learned how to create a database and database table and how to add records to a database table. So how do we retrieve data from a database table?
1. Retrieving information from a database table in fact, we've already used the SELECT statement, which is used to retrieve information from a database table. The SELECT statement format is generally:
Select retrieves the keyword from the retrieved table Where the search condition (optional)
The "*" used previously indicates that all columns are selected. The following continues with the table mytable we created in the previous article:
2. Query all data: mysql> select * FROM MyTable; +----------+------+------------+----------+ | name | sex | Birth | birthaddr | +----------+------+------------+--------+ | Abccs|f| 1977-07-07 | China | | Mary |f| 1978-12-12 | USA | | Tom |m| 1970-09-02 | USA | +----------+------+------------+----------+ 3 row in Set (0.00 sec)
3, fixed Error Record: If Tom's birth date is wrong, it should be 1973-09-02, you can use the UPDATE statement to fix: mysql> update mytable set birth = "1973-09-02" Wherename = "Tom "; Then use the 2 statement to see if it has been corrected.
4, select a specific line above modified Tom's birth date, we can choose Tom this line to see if there has been a change: mysql> select * FROM mytable WHERE name = "Tom"; +--------+------+------------+------------+ | Name |sex | Birth | birthaddr | +--------+------+------------+------------+ | tom|m| 1973-09-02 | usa| +--------+------+------------+------------+ 1 row in Set (0.06 sec)
The Where parameter above specifies the search criteria. We can also query with combination conditions: mysql> Select * from mytable Where sex = "F" andbirthaddr = "China"; +--------+------+------------+------------+ | Name |sex | Birth | birthaddr | +--------+------+------------+------------+ | Abccs |f| 1977-07-07 | China | +--------+------+------------+------------+ 1 row in Set (0.06 sec)
5. Select a specific column if you want to see the names of everyone in the table, you can do this: mysql> select name from MyTable; +----------+ | name | +----------+ | Abccs | | Mary | | Tom | +----------+ 3 row in Set (0.00 sec) If you want to list name and gender columns, you can separate the keyword name and birth with commas: myaql> select Name,birth from MyTable;
6. Sort rows We can sort the records in the table by birthday size: mysql> Select name, birth from MyTable order Bybirth; +----------+------------+ | name | Birth | +----------+------------+ | Tom | 1973-09-02 | | abccs| 1977-07-07 | | Mary | 1978-12-12 | +----------+------------+ 3 row in Set (0.00 sec)
We can use DESC to sort the reverse order: mysql> Select name, birth from MyTable OrDER Bybirth DESC; +----------+------------+ | name | Birth | +----------+------------+ | Mary | 1978-12-12 | | abccs| 1977-07-07 | | Tom | 1973-09-02 | +----------+------------+ 3 row in Set (0.00 sec)
7, row count database often to statistics some data, such as the number of employees in the table, we need to use the row Count function count (). The count () function is used to count records for non-null results: Mysql> Select COUNT (*) from mytable; +----------+ | COUNT (*) | +----------+ |3 | +----------+ 1 row in Set (0.06 sec)
Number of men and women in the workforce: mysql> Select Sex, COUNT (*) from mytable GROUP bysex; +------+----------+ | sex | COUNT (*) | +------+----------+ | f|2 | | m|1 | +------+----------+ 2 row in Set (0.00 sec)
Note that we used group by to group sex.
Multi-table operation
We are familiar with the basic operations of database and database tables, and now let's look at how to manipulate multiple tables.
In a database, there may be more than one table, and the tables are interrelated. Let's continue with the previous example. The table that was created earlier contains some basic information about the employee, such as name, gender, date of birth, place of birth. We then create a table that describes the articles that the employee publishes, including the author's name, the title of the article, and the date of publication.
1. View the contents of the first table mytable: mysql> select * FROM MyTable; +----------+------+------------+-----------+ | name | sex | Birth | birthaddr | +----------+------+------------+-----------+ | abccs|f | 1977-07-07 | China | | Mary |f | 1978-12-12 | USA | | Tom |m | 1970-09-02 | USA | +----------+------+------------+-----------+
2. Create a second table title (including author, article title, date of publication): mysql> CREATE TABLE title (writer varchar (notnull), title varchar () NOT NULL, Senddate date);
Add a record to the table and the final table reads as follows: Mysql> select * from title; +--------+-------+------------+ | Writer | Title | Senddate | +--------+-------+------------+ | Abccs | c1> 2000-01-23 | | Mary | b1| 1998-03-21 | | Abccs | c2> 2000-12-04 | | Tom| c1| 1992-05-16 | | Tom| c2| 1999-12-12 | +--------+-------+------------+ 5 rows in Set (0.00SEC)
3. Multi-Table Query now we have two tables: MyTable and title. Using these two tables we can combine queries: for example we want to query author Abccs's name, Gender, article: mysql> Select name,sex,title frommytable,title, Where name=writer and Name= ' Abccs '; +-------+------+-------+ | name | sex | Title | +-------+------+-------+ | Abccs | F| a1| | Abccs | F| c2> +-------+------+-------+
In the example above, because the author's name, gender, and article are recorded in two different tables, a combination must be used for querying. You must specify how records in one table match records in other tables. Note: If the writer column in the title of the second table is also named name (same as the Name column in the MyTable table) instead of writer, you must use Mytable.name and title.name to indicate the difference.
One more example for querying the author, birthplace, and date of birth of the article A2: mysql> select Title,writer,birthaddr,birth frommytable,title, where mytable.name =title.writer andtitle= ' A2 '; +-------+--------+-----------+------------+ | Title | Writer | birthaddr | Birth | +-------+--------+-----------+------------+ | c2> Abccs | China | 1977-07-07 | +-------+--------+-----------+------------+
Modification and backup, batch processing
Sometimes we want to modify and delete database tables and databases, which can be implemented as follows:
1. Add a column: If the MyTable table in the previous example adds a column indicating whether single single:mysql> ALTER TABLE mytable add column Singlechar (1);
2, modify the record to change the Abccs single record to "Y": mysql> update mytable set single= ' y ' wherename= ' Abccs ';
Now let's see what happened: mysql> select * FROM MyTable; +----------+------+------------+-----------+--------+ | name | sex | Birth | birthaddr | Single | +----------+------+------------+-----------+--------+ | abccs|f | 1977-07-07 | China | y | | Mary |f | 1978-12-12 | USA | NULL | | Tom |m | 1970-09-02 | USA | NULL | +----------+------+------------+-----------+--------+
3. Add a record of how to add a record, for easy viewing, repeat with this: mysql> INSERT INTO mytable values (' abc ', ' F ', ' 1966-08-17 ', ' China ', ' n '); Query OK, 1 row affected (0.05 sec) View: mysql> select * FROM MyTable; +----------+------+------------+-----------+--------+ | name | sex | Birth | birthaddr | Single | +----------+------+------------+-----------+--------+ | abccs|f | 1977-07-07 | China | y | | Mary |f | 1978-12-12 | USA | NULL | | Tom |m | 1970-09-02 | USA | NULL | | ABC |f | 1966-08-17 | China | n | +----------+------+------------+-----------+--------+
3. Delete records Delete a record in the table with the following command: mysql> delete from mytable where name= ' abc '; Delete Deletes a record from the table that satisfies the conditions given by where.
Show the result again: mysql> select * FROM MyTable; +----------+------+------------+-----------+--------+ | name | sex | Birth | birthaddr | Single | +----------+------+------------+-----------+--------+ | abccs|f | 1977-07-07 | China | y | | Mary |f | 1978-12-12 | USA | NULL | | Tom |m | 1970-09-02 | USA | NULL | +----------+------+------------+-----------+--------+
4. Delete tables: mysql> DROP TABLE * * * * (Name of table 1), * * * name of table 2; You can delete one or more tables and use them with care.
5, the deletion of the database: mysql> drop database name; Use carefully.
6. Backup of database: Back to dos:mysql> quit D:mysqlbin use the following command to back up the database Abccs: mysqldump--opt ABCCS&GT;ABCCS.DBB ABCCS.DBB is the backup file for your database Abccs.
7. Use MySQL in batch mode:
First set up a batch file Mytest.sql, the contents are as follows: Use ABCCS; SELECT * FROM MyTable; Select Name,sex from mytable where name= ' Abccs ';
Run the following command under DOS: D:mysqlbin MySQL < mytest.sql the execution results are displayed on the screen.
If you want to see the results and output a lot of results, you can use the command: MySQL < mytest.sql | More
We can also output the results to a file: MySQL < mytest.sql >mytest.out

Mysql Database Operation statement