Getting started with MySQL-Quick Reference
Getting started with MySQL-Quick Reference
I. SQL Quick Start
The following is an important quick reference for SQL. For SQL syntax and features added to standard SQL, please query the MySQL manual.
1. Create a table
A table is one of the most basic elements of a database. Tables and tables can be independent or associated with each other. The basic syntax for creating a table is as follows:
Create table table_name
(Column_name datatype {identity | null | not null },
...)
The table_name and column_name parameters must meet the requirements of identifier in the user database. The parameter ype is a standard SQL type or a type provided by the user database. You must use the non-null clause to input data for each field.
Create table also has some other options, such as creating a temporary table and reading some fields from other tables using the select clause to form a new table. In addition, when creating a table, you can use the primary key, KEY, INDEX, and other identifiers to set certain fields as PRIMARY keys or indexes.
Note the following when writing:
List the complete fields in a pair of parentheses.
Field names are separated by commas.
Enter a space after the comma (,) between field names.
The last field name is not followed by a comma.
All SQL statements end with a semicolon.
Example:
Mysql> create table test (blob_col BLOB, index (blob_col (10 )));
2. Create an index
Indexes are used to query databases. Generally, a database has a variety of index solutions, each of which is precise to a specific query class. Indexes can accelerate the database query process. The basic syntax for creating an index is as follows:
Create index index_name
On table_name (col_name [(length)],...)
Example:
Mysql> create index part_of_name ON customer (name (10 ));
3. Change the table structure
When using a database, you sometimes need to change its table structure, including changing the field name or even changing the relationship between different database fields. The alter command can be implemented. Its basic syntax is as follows:
Alter table table_name alter_spec [, alter_spec...]
Example:
Mysql> alter table t1 CHANGE a B INTEGER;
4. delete data objects
Many databases are dynamically used, and sometimes a table or index needs to be deleted. Most database objects can be deleted using the following command:
Drop object_name
Mysql> drop table tb1;
5. Execute the query
Query is the most commonly used SQL command. The query database must rely on factors such as structure, index, and field type. Most databases contain an optimizer that converts user query statements into optional forms to improve query efficiency.
It is worth noting that MySQL does not support standard SQL 92 nested where clauses, that is, it only supports one where clause. The basic syntax is as follows:
SELECT [STRAIGHT_JOIN] [SQL _SMALL_RESULT] [SQL _BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL]
Select_expression ,... [INTO {OUTFILE | DUMPFILE} 'file _ name' export_options] [FROM table_references [WHERE where_definition] [group by col_name,...] [HAVING where_definition] [order by {unsigned_integer | col_name | formula} [ASC | DESC],...] [LIMIT [offset,] rows] [PROCEDURE procedure_name]
Where the where clause is the place where the definition is selected. where_definition can have different formats, but they all follow the following format:
Field name Operation expression
Field name operation field name
In the first form, the value of the field is compared with the expression. In the second form, the values of the two fields are compared. Depending on the data type, the following operations may be selected in search_condition:
= Check for Equality
! = Check whether the value is different
> (Or> =) check whether the left value is greater than (or greater than or equal to) the right value
<(Or <=) check whether the left value is less than (or less than or equal) the right value [not] between checks whether the left value is within a certain range [not] in checks whether the left side is a member of a specific set [not] like checks whether the left side is a child string on the right
Is [not] null check whether the left side is null
Here, wildcard _ can be used to represent any character, and % represents any string. Keywords, and complex words can be generated. They use multiple standard sets of boolean expressions when running the check.
Example:
Mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
Mysql> select college, region, seed from tournament
Order by region, seed;
Mysql> select col_name from tbl_name WHERE col_name> 0;
6. Modify Table Data
When using a database, you often need to modify the data in its table, such as adding new data to the table, deleting the original data in the table, or changing the original data in the table. Their basic syntax is as follows:
Add data:
Insert [into] table_name [(column (s)]
Values (expression (s ))
Example:
Mysql> insert into tbl_name (col1, col2) VALUES (15, col1 * 2 );
Data deletion:
Delete from table_name where search_condition
Data Change:
Update table_name
Set column1 = expression1,
Column2 = expression2 ,...
Where search_condition
7. Database Switching
When multiple databases exist, you can use the following command to define the database you want to use:
Use database_name
8. Statistical functions
SQL has some statistical functions, which are helpful for generating data tables. The following describes several common statistical functions:
Sum (exepression) calculates the sum of expressions
Avg (exepression) calculates the average value of the expression
Count (exepression) performs simple counting on the Expression
Count (*) number of statistical records
Max (exepression) returns the maximum value.
Min (exepression) calculates the minimum value
Exepression is any valid SQL expression. It can be one or more records or a combination of other SQL functions.
Ii. MySQL user guide
1. Use MySQL to create a new database
Run in shell:
$> Mysqladmin create database01
Database "database01" created.
2. Start MySQL
Run in shell:
$> Mysql
Welcome to the MySQL monitor. Commands end with; or g.
Your MySQL connection id is 22 to server version: 3.21. 29a-gamma-debug
Type 'help' for help.
3. Change the database
Mysql> use database01
Database changed.
4. Create a table
Mysql> create table table01 (field01 integer, field02 char (10 ));
Query OK, 0 rows affected (0.00 sec)
5. List tables
Mysql> show tables;
Tables in database01
Table01
Table02
6. list fields in the table
Mysql> show columns from table01;
Field Type Null Key Default Extra
Field01 int (11) YES
Field02 char (10) YES
7. Enter data in the table
Insert data
Mysql> insert into table01 (field01, field02) values (1, 'first ');
Query OK, 1 row affected (0.00 sec)
8. Add Fields
... One field at a time
Mysql> alter table table01 add column field03 char (20 );
Query OK, l row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
... Multiple fields at a time
Mysql> alter table table01 add column field04 date, add column field05 time;
Query OK, l row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
Note: Each column must start again with "add column.
Is it running? Let's take a look.
Mysql> select * from table01;
Field01 field02 field03 field04 field05
1 first NULL
9. Multi-line command input
The MySQL command line interface allows a statement to be input as one line, or expanded into multiple lines. There is no syntax difference between the two. With multi-line input, you can break down SQL statements step by step to make it easier to understand.
In the multi-row mode, the annotator adds each row to the previous row until you use the Semicolon ";" to end the SQL statement. Once you type a semicolon and press enter, this statement is executed.
The following example shows two input methods for the same strict SQL statement:
Single Row Input
Mysql> create table table33 (field01 integer, field02 char (30 ));
Multi-line Input
Mysql> create table table33
-> (Field01
-> Integer,
-> Field02
-> Char (30 ));
Note that you cannot disconnect words, such:
Correct
Mysql> create table table33
-> (Field01
-> Integer,
-> Field02
-> Char (30 ));
Error
Mysql> create table table33
-> (Field01 inte
-> Ger,
-> Field02
-> Char (30 ));
When inserting or changing data, you cannot expand the string of the field to multiple rows. Otherwise, press enter to store the data:
Standard Operation
Mysql> insert into table33 (field02)
-> Values
-> ('Who thought of foo? ');
Press enter to store data
Mysql> insert into table33 (field02)
-> Values
-> ('Who thought
-> Of foo? ');
The result is as follows:
Mysql> select * from table33;
Field01 field02
NULL who thought of foo?
NULL who thought
Of foo?
10. Table Data embedding
Mysql> insert into table01 (field01, field02, field03, field04, field05) values
-> (2, 'second', 'Another ', '2017-10-23', '10: 30: 00 ');
Query OK, 1 row affected (0.00 sec)
The standard date format is "yyyy-mm-dd ".
The standard time format is "hh: mm: ss ".
The quotation marks must be in the preceding standard date and time format.
The date format can also be "yyyymmdd" or "hhmmss", but the value does not need to be enclosed in quotation marks.
No quotation marks are required for numeric values. This type of storage is irrelevant to the data type. These data types are included in formatted columns (such as text, date, time, and integer ).
MySQL has a very useful command buffer. It saves the SQL statement you have already typed and used it. For the same command, you do not have to repeat the input over and over again. Next, let's look at this example.
Add another data using the command buffer (and any date and time format)
Press the up arrow on the keyboard twice.
Press enter.
Enter a new value in parentheses and end with a semicolon.
(3, 'a third', 'more', 19991024,103 004 );
Press enter.
Is the new value included?
Mysql> select * from table01;
Field01 field02 field03 field04 field05
1 first NULL
2 second another 10:30:00
3 a third more 10:30:04
11. Update table data
Modify a field at a time
Pay attention to the syntax again. The text must be enclosed in quotation marks but not digits.
Mysql> update table01 set field03 = 'new info' where field01 = 1;
Query OK, 1 row affected (0.00 sec)
Change multiple fields at a time
Remember to separate each updated field with a comma.
Mysql> update table01 set field04 = 19991022, field05 = 062218 where field01 = 1;
Query OK, 1 row affected (0.00 sec)
Update multiple data at a time
Mysql> update table01 set field05 = 152901 where field04> 19990101;
Query OK, 3 rows affected (0.00 sec)
12. delete data
Mysql> delete from table01 where field01 = 3;
Query OK, 1 row affected (0.00 sec)
13. Exit
Mysql> quit
Bye
Now you know some basic commands for running databases in MySQL. Because MySQL is operated by executing SQL calls, you need an adequate array of powerful tools in your processing. For example, by joining related fields, you can display data in several tables at the same time. Similarly, SQL allows you to comprehensively display, update, or delete multiple data that meets specific standards. If you want to master it, you need to learn all the SQL knowledge in the next step.
In addition, MySQL provides good network operation security features. For more information about MySQL security and other features, see the MySQL Website: http://www.mysql.com
To facilitate your query and use of the MySQL manual, we will make the official manual (Manaul) under the/docs/manaul/mysql directory on the CD. You can use your browser to browse and query.
