Getting started with MySQL-Quick Reference

Source: Internet
Author: User
Tags mysql manual type null
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.