How to use SQL common statements

One, the root user password is lost or reset

Method 1: Use the Set password command

First log in to MySQL.

Format:mysql> set password for user name @localhost = password (' new password ');

Example:mysql> set password for [email protected] = password (' 123 ');

Method 2: Use Mysqladmin

Format: Mysqladmin-u username-P Old password password new password

Example: mysqladmin-uroot-p123456 password 123

Method 3: Edit the user table directly with update

First log in to MySQL.

mysql> use MySQL;

mysql> Update user set Password=password (' 123 ') where user= ' root ' and host= ' localhost ';

mysql> flush Privileges;

Method 4: When you forget the root password, you can do this

1. Close the running MySQL service.

3. Enter Mysqld_safe--skip-grant-tables carriage return. --skip-grant-tables

This means that when you start the MySQL service, you skip permission table authentication.

5. Enter MySQL return and, if successful, the MySQL prompt > will appear.

6. Connection rights database: use MySQL;.

6. Change Password: Update user set Password=password ("123") where user= "root"; (don't forget the last semicolon).

7. Refresh permissions (required steps): flush privileges;.

8. Quit quit.

9. Log out of the system and then enter, using the username root and the new password you just set 123 to log in.

Second, the search data:

1. Retrieve a single column:

SELECT Prod_name from Products;

2. Retrieving multiple columns:

SELECT prod_id, Prod_name, prod_price from the products;

3. Retrieve all Columns:

SELECT * FROM Products;

4. Retrieving different values

SELECT DISTINCT vend_id from Products;

5. Limit Display results:

SELECT Prod_name from Products LIMIT 5; --Display the first five elements

Select name from USERTB where age > Limit 0, 1; Limit starting from the first bar, showing 1 articles

Select name from USERTB where age > Limit 1; One effect with the above

Select name from USERTB where age > Limit 4, 1; The display starts with the fifth bar, showing 1

6. Notes:

1) in the MySQL script,--The following field is the comment information,

2) or use # to annotate,

When executed at the command line, the comment is followed by a, indicating the end of the comment.

3) Comments from/* start, to */end, */* and/* Any content is a comment. This approach is often used to annotate code, as shown in this example, where two select words are defined

Sentence, but the first one will not be executed because it has been commented out.

Third, sort the retrieval data

1. Sort by a single column

SELECT Prod_name from the products ORDER by Prod_name;

In addition to instructing the DBMS software to sort the Prod_name column alphabetically by the ORDER BY clause, this statement is identical to select Prod_name from the products.

Note: Typically, the columns used in the ORDER BY clause will be the columns that are selected for display. However, it is not necessarily true that sorting data with non-retrieved columns is completely legal

2. Sort by multiple columns

SELECT prod_id, Prod_price, prod_name from the products ORDER by Prod_price, Prod_name;

It is important to understand that when sorting by more than one column, the order of sorts is done exactly as specified. In other words, for the output in the above example, only if more than one row has the same Prod_price value

Sort the products by prod_name. If all the values in the Prod_price column are unique, they are not sorted by Prod_name.

3. Sort By column position:

SELECT Prod_id,prod_price,prod_name from the products ORDER by 2, 3;

The select list specifies the relative position of the selection column instead of the column name. ORDER by 3 table

The prod_name is sorted by the second column in the select list. Order BY 2,3 indicates that the prod_price is first pressed and then sorted by Prod_name.

The main benefit of this technique is that you do not have to re-enter column names. But it also has shortcomings. First, it is possible to sort the column names incorrectly by not explicitly giving them names. Second, the select list is more

It is easy to sort the data incorrectly (forget to change the ORDER BY clause accordingly). Finally, if the column being sorted is not in the select list, it is obviously not possible to use this technique

Postoperative

Sort by non-select column

Obviously, this technique cannot be used when sorting based on columns that do not appear in the select list. However, if necessary, you can mix matches using the actual column name and relative column position.

4. Specify the sort direction

Data sorting is not limited to ascending sort (from A to Z), which is just the default sort order. You can also use the ORDER BY clause to sort in descending order (from Z to a). To sort in descending order, you must specify the DESC keyword.

SELECT prod_id, Prod_price, prod_name from the products ORDER by Prod_price DESC;

Sort multiple columns:

SELECT prod_id, Prod_price, prod_name from the products ORDER by Prod_price DESC, Prod_name;

The DESC keyword is applied only to the column name immediately preceding it. In the example above, DESC is specified only for the Prod_price column, not for the Prod_name column. Therefore, the Prod_price column is sorted in descending order

Prod_name columns (within each price) are still sorted in ascending order by standard.

If you want to sort in descending order on more than one column, you must specify the DESC keyword for each column.

Relative to DESC is ASC (or ascending), which can be specified in ascending order. But the actual

, ASC is not of much use, because ascending is the default (if ASC is not specified and DESC is not specified, ASC is assumed).

Iv. using where to filter data

1.SELECT Prod_name, Prod_price from products WHERE Prod_price = 3.49;--use equals, not equal to use! =

Note: When using both the order BY and the WHERE clauses, you should leave the order by in the Where, otherwise an error will occur

2.WHERE operator:

=: equals;<> or! =: Not equal to;<;<=;>;>=; Between between two values specified; is null: empty;

Example: Retrieving a single value:

SELECT Prod_name, Prod_price from Products WHERE Prod_price < 10;

SELECT Prod_name, Prod_price from Products WHERE Prod_price < 10;

Mismatch check:

SELECT vend_id, prod_name from Products WHERE vend_id <> ' DLL01 ';

Range value Check

SELECT Prod_id,prod_price,prod_name from Products WHERE Prod_price between 5.99 and 9.49;

Null value check:

SELECT Prod_name from Products WHERE Prod_price is NULL;

Tip: When to use quotation marks

If you look closely at the Where condition above, you will see that some values are enclosed in single quotation marks, and some values are not enclosed. Single quotation marks are used to qualify strings. If you compare a value to a column of type string, you need to limit the quotation marks. The value used to compare with the numeric column is not quoted.

V. Advanced DATA filtering: multiple where clauses are connected with and OR or

1.AND operator

SELECT prod_id, Prod_price, prod_name from products WHERE vend_id = ' DLL01 ' and Prod_price <= 4;

SELECT Prod_id,prod_price,prod_name from products WHERE vend_id = ' DLL01 ' and Prod_price <= 4 ORDER by prod_id DESC;

2.OR

The keyword used in the WHERE clause to represent the retrieval of rows that match any given condition.

SELECT Vend_id,prod_id,prod_price,prod_name from products WHERE vend_id = ' DLL01 ' OR prod_price <= 4 ORDER by prod_id DESC;

3. Order of Evaluation:

If you need to list all products manufactured by DLL01 or BRS01 for a price of USD 10 and above.

SELECT Vend_id,prod_id,prod_name,prod_price from Products WHERE prod_price > (vend_id = ' DLL01 ' OR vend_id = ' BR S01 ');

Tip: Use parentheses in the WHERE clause any time you use a WHERE clause with and and OR operators, you should use parentheses to explicitly group the operators. Do not rely too much on the default evaluation order, even if it is exactly what you want it to be. There is nothing bad about using parentheses, it can eliminate ambiguity.

4.IN Operation:

The in operator is used to specify a range of conditions, and each condition in the range can be matched. In takes a set of valid values separated by commas, enclosed in parentheses. Finished or the same effect

SELECT Vend_id,prod_name, Prod_price from Products WHERE vend_id in (' DLL01 ', ' BRS01 ') ORDER by prod_name; equivalent to the following statement

SELECT Prod_name, Prod_price from products WHERE vend_id = ' DLL01 ' OR vend_id = ' BRS01 ' ORDER by Prod_name;

Why do I use the in operator? The advantages are:

In the case of many legal options, the in operator's syntax is clearer and more intuitive.

When you use in combination with other and and or operators, the order of evaluation is easier to manage.

The in operator is generally faster than a set of or operators (in the case of a few legal options above, you do not see performance differences).

The greatest advantage of in is that it can contain other SELECT statements, which can be used to create a WHERE clause more dynamically.

5.NOT operator

The not operator in the WHERE clause has only one function, which is to negate any subsequent conditions.

SELECT Prod_name,prod_price from Products WHERE Prod_price not in (3.49,5.99) ORDER by prod_id DESC;

VI. filtering with wildcard characters

1. Predicates (predicate)

When is the operator not an operator? The answer is that it acts as a predicate. Technically, like is a predicate rather than an operator. Although the end result is the same, you should be aware of the term in order to avoid having to understand the term in a SQL document or manual.

2. Wildcard search can only be used with text fields (strings), non-Text data type fields cannot be searched using wildcards.

3. Percent symbol (%) wildcard

The most commonly used wildcard character is the percent sign (%). In the search string,% indicates any number of occurrences of any character. For example, to find all products that start with the word fish, you can publish the following SELECT statement:

SELECT prod_id, prod_name from the products WHERE the prod_name like ' fish% ';

Wildcards can be used anywhere in the search pattern, and multiple wildcard characters can be used. The following example uses two wildcard characters, which are located at both ends of the pattern:

SELECT prod_id, prod_name from products WHERE prod_name like '%bean bag% ';

Wildcard characters can also appear in the middle of the search pattern, although this is not useful. The following example finds all products with the beginning of F and ending with Y:

SELECT Prod_name from Products WHERE prod_name like ' f%y ';

Note: Be aware of the following spaces

Many DBMS, including access, use spaces to fill in the contents of a field. For example, if a column has 50 characters and the stored text is Fish bean bag toy (17 characters), filling the column requires 33 spaces appended to the text. This generally has no effect on the data and its use, but may have a negative impact on the SQL statements described above. Clause whereprod_name like ' f%y ' matches only prod_name that begins with F and ends with Y. If the value is followed by a space, it does not end with Y, so fish bean bag toy is not retrieved. The simple solution is to add a% number to the search pattern: ' f%y% ' also matches the character (or space) after Y. A better solution is to use a function to remove spaces.

WARNING: please note null

The wildcard% looks like it can match anything, but with an exception, this is null. The clause where prod_name like '% ' does not match the line where the product name is NULL.

4. Underline (_) wildcard characters

Another useful wildcard character is an underscore (_). The underscore is used in the same way as%, but it matches only a single character, not more than one character.

SELECT prod_id, prod_name from products WHERE prod_name like ' __ inch teddy bear ';

5. Tips for using wildcard characters:

As you can see, a wildcard for SQL is useful. However, this functionality comes at a cost, which is that wildcard searches typically take longer to process than other searches discussed earlier.

Tips for using

Do not use wildcard characters excessively. If other operators can achieve the same purpose, other operators should be used.

When you do need to use wildcards, try not to use them at the beginning of the search pattern. The wildcard character is placed at the beginning and the search is the slowest.

Pay close attention to the position of the wildcard character. If misplaced, you may not return the data you want.

In short, wildcards are an extremely important and useful search tool that we often use in the future.

This article is from "vinegar with soy sauce bottles" blog, please be sure to keep this source http://sauce.blog.51cto.com/11880696/1861490

How to use SQL common statements