Mysql Novice Starter Guide-Quick reference _mysql

First, SQL crash


Here are some important SQL quick references, for SQL syntax and added features on standard SQL, please check the MySQL manual.

1. Create a table

A table is one of the most basic elements of a database, and can be independent of each other or associated with a table. The basic syntax for creating a table is as follows:

CREATE TABLE table_name

(column_name datatype {identity |null|not null},

...)

Where the parameters table_name and COLUMN_NAME must meet the requirements of the recognizer (identifier) in the user database, the parameter datatype is a standard SQL type or a type provided by the user database. Users use non-null clauses to enter data for each field.

CREATE table has other options, such as creating temporary tables and reading some fields from other tables using the SELECT clause to make a new table. Also, in the creation table is available primary key, key, index, and other identifiers to set some fields as the primary key or index.

To note in writing:

The complete list of fields is listed in a pair of parentheses.

Field names are separated by commas.

Add a space after the comma between the field names.

The last field name is not followed by a comma.

All SQL statements are semicolon ";" End.

Cases:

mysql> CREATE TABLE Test (Blob_col blob, index (Blob_col (10)));

2. Create an index

The index is used for queries against the database. The general database is built with multiple indexing schemes, each of which is proficient in a particular query class. Indexes can speed up the query process for a database. The basic syntax for creating an index is as follows:

CREATE INDEX Index_name

On table_name (col_name[(length)],...)

Cases:

Mysql> CREATE INDEX part_of_name on customer (name (10));

3. Change Table structure

In the process of using a database, it is sometimes necessary to change its table structure, including changing the name of the field, or even changing the relationship between different database fields. The command that can achieve this change is alter, whose basic syntax is as follows:

ALTER TABLE table_name ALTER_SPEC [, Alter_spec ...]

Cases:

mysql> ALTER TABLE T1 change a b INTEGER;

4. Delete a Data object

Many databases are used dynamically, and sometimes you may need to delete a table or index. Most database objects can be removed by the following command:

Drop object_name

mysql> DROP TABLE tb1;



5. Execute Query


The query is the SQL command with the most use. Querying a database requires factors such as structure, index, and field type. Most databases contain an optimizer (optimizer) that converts a user's query statement into an optional form to improve query efficiency.


It is noteworthy that MySQL does not support the SQL92 standard nested WHERE clause, which supports only a 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 to define the selection criteria, where_definition can have different formats, but all follow the following form:


Field name Action expression


Field name Action field name


In the first form, the standard compares the value of a field to an expression, and in the second form, compares the values of two fields. Depending on the data type being compared, the actions in Search_condition may choose the following:


= Check for equality


！ = Check whether unequal


> (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 equal to) the right value [not] between check whether the left value is within a range [not] in check whether the left side is a member of a particular set [not] like checking whether the left side is the right substring


is [not] null check for null value on left


Here, you can use the wildcard character _ to represent any one character, and% to represent any string. Use keywords, and can generate complex words that run checks using multiple standard sets of Boolean expressions.


Cases:


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 data in a table


When using a database, you often modify the data in its tables, such as adding new data to the table, deleting the existing data in the table, or making changes to the existing data in the table. Their basic syntax is as follows:


Data add:


Insert [INTO] table_name [(column (s))]


VALUES (expression (s))


Cases:


Mysql> INSERT into Tbl_name (col1,col2) VALUES (15,col1*2);


Data deletion:


Delete from table_name where search_condition


Data changes:


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 that users want to use:


Use database_name


8. Statistical functions


SQL has some statistical functions that are useful for generating data tables. Here are a few common statistical functions:


SUM (exepression) evaluates expressions and


AVG (exepression) calculates the average of an expression


Count (exepression) a simple count of an expression


COUNT (*) Statistics records


Max (exepression) max value


Min (exepression) to find the minimum value


Where exepression is any valid SQL expression, it can be one or more records, or it can be a combination of other SQL functions.



Second, the use of MySQL guide


1. Using MySQL to create a new database


Run under the shell:


$>mysqladmin Create Database01


Database "Database01" created.


2. Start MySQL


Run under the shell:


$>mysql


Welcome to the MySQL Monitor. Commands End With; or G.


Your MySQL Connection ID is to server version:3.21. 29a-gamma-debug


Type ' help ' for help.


3. Replacing a 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 List of tables


Mysql>show tables;


Tables in Database01


Table01


Table02


6. List the fields in the table


Mysql>show columns from TABLE01;


Field Type Null Key Default Extra


FIELD01 Int (one) YES


FIELD02 Char YES


7. Table Data Fill in


Inserting data


Mysql>insert into Table01 (FIELD01, FIELD02) VALUES (1, ' a ');


Query OK, 1 row Affected (0.00 sec)


8. Increase of 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 once


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 be restarted with "add column".


Has it run yet? Let's see.


Mysql>select * from TABLE01;


FIELD01 field02 field03 field04 field05


1-null NULL NULL



9. Multi-line command input


The MySQL command-line interface allows the statement to be entered as a single line, or it can be expanded into multiline input. There is no grammatical difference between the two. With multiple lines of input, you can break down the SQL statement step-by-step, making it easier to understand.


In multiline mode, the annotation adds each row to the previous line until you use the semicolon ";" To end this SQL statement. Once you type a semicolon and press ENTER, the statement is executed.


The following example is the two input methods for the same strict SQL statement:


Single line input


Mysql>create table table33 (field01 integer, field02 char (30));


Multi-Line Input


Mysql>create Table Table33


-> (field01


->integer,


->field02


->char (30));


Note You cannot break a word, such as:


That's right


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 a field's string to multiple lines, or hard return will be stored in the data:


Standard operation


Mysql>insert into Table33 (FIELD02)


->values


-> (' Who thought of Foo? ');


Hard return is stored in the data


Mysql>insert into Table33 (FIELD02)


->values


-> (' Who thought


->of foo? ');


The results are as follows:


Mysql>select * from Table33;


FIELD01 field02


NULL who thought of Foo?


NULL who thought


of Foo?


10. Data embedding in a table


Mysql>insert into Table01 (FIELD01, FIELD02, Field03, FIELD04, FIELD05) values


-> (2, ' second ', ' Another ', ' 1999-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".


Quotation marks are required to give the above standard date and time format.


The date can also be "YYYYMMDD" and the time can be entered as "HHMMSS", but the value does not need to be quoted again.


Numeric values do not need to be quoted. This save is not related to the data type, which has a formatted column to contain (for example: text, date, time, integer, etc.).


MySQL has a very useful command buffer. It keeps the SQL statement you've typed into it, and you don't have to repeat it over and over again for the same command. Next we'll look at an example of this.


Add another data using the command buffer (and any date and time format)


Press the UP ARROW key on the keyboard two times.


Enter.


Enter a new value within the parentheses and end with a semicolon.


(3, ' A third ', ' more ', 19991024, 103004);


Enter.


Does the new value exist inside?


Mysql>select * from TABLE01;


FIELD01 field02 field03 field04 field05


1-null NULL NULL


2 Second another 1999-10-23 10:30:00


3 A third more 1999-10-24 10:30:04


11. Data updates for tables


Modify one field at a time


Pay attention to grammar again. Text needs to be enclosed in quotes but not in numbers.


Mysql>update table01 Set field03= ' new info ' where field01=1;


Query OK, 1 row Affected (0.00 sec)


Change multiple fields at once


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 once


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've learned a few basic commands for running a database in MySQL. Because MySQL is operated by executing SQL calls, a sufficient array of powerful tools is needed in your processing. For example, by joining related fields, you can display data from several tables at the same time. Similarly, SQL allows you to synthesize, update, or delete multiple data that meet specific criteria. If you also want to master it, the next step is to learn all the knowledge of SQL.


In addition, MySQL provides a good network operation security features. For more information about MySQL's security and other features, please check the MySQL website: http://www.mysql.com


In order to facilitate the query using the MySQL manual, we will be the official manual (Manaul) in the CD-ROM/docs/manaul/mysql directory. You can browse the query with your browser.