MySQL essay one

Source: Internet
Author: User

1. Built-in function query

Query server version number and current date mysql> select version (), current_date;
+-----------+--------------+ | version ()  | current_date |
+-----------+--------------+ | 5.1.73    | 2017-03-13   | +-----------+--------------+ 1 row in set  (0.04 sec) MySQL is not case-sensitive, so the following query results with the above is a set of MySQL
> select version (), current_date;
Mysql> select version (), current_date;
This is in manual sql injection, the third way can effectively avoid shielding case of filter query current time Mysql> select now (); +---------------------+ | now ()             
   |
+---------------------+ | 2017-03-13 12:46:48 | +---------------------+ 1 row in set  (0.00 sec) query the current system host name mysql> select 
User ();
+----------------+ | user ()          |
+----------------+ | [email protected] | +----------------+ 1 row in set  (0.00 sec) 

2, create and use the database

View the databases that exist in the system mysql> show databases;
+--------------------+ | database           | +--------------------+ | information_schema | | mysql               | | test         
      | +--------------------+ 3 rows in set  (0.01 sec) to create a database mysql> create 

database test1; Use database Mysql> use test1 database changed here, the SQL statement is not added; the ending, of course, can be added; The QUIT statement is the same. View tables in the Test1 database MySQL
> show tables; empty set  (0.00 sec) CREATE TABLE mysql> create table t1  (Name VARCHAR), age
 char (4), Sex char (1), Birthday date,address varchar (20));
query ok, 0 rows affected  (0.07 sec) View table mysql> describe t1; +----------+-------------+------+-----+---------+-------+ | field    | type    
    | null | key | default | extra | +----------+-------------+------+-----+---------+-------+ | name     |  varchar ( | yes  |     | null    |)        | | age      | char (4)       | YES  |     | NULL     |       | | sex      | char (1)      | YES  |     | NULL     |       | | birthday | date         | yes  |     | null    |        | | address  | varchar ( | YES  |     )
| null    |       |

+----------+-------------+------+-----+---------+-------+ 5 rows in set  (0.00 sec) Inserting data into a table mysql> insert into t1     -> values  (' xjp ', ' + ', NULL,
' 1994-01-10 ', ' [email protected] ');

query ok, 1 row affected  (0.00 SEC) here represents a value that does not exist, and can be entered at the end of a line if the command is too long.
Querying the inserted data mysql> select * from t1; +------+------+------+------------+------------+ | name | age  | sex  |
 birthday   | address    | +------+------+------+------------+------------+ | xjp  | 18   | null | 1994-01-10 | [email protected] | +------+------+------+------------+------------+ 1 row in set  (0.00 sec)

The SELECT statement is used to retrieve information from the data table. The general format of the statement is:

SELECT what_to_select from
which_table
WHERE conditions_to_satisfy;

What_to_select points out what you want to see, which can be a table for a column, or * denotes "all columns." Which_table indicates the table from which you want to retrieve data. The WHERE clause is optional, and if selected, conditions_to_satisfy specifies the search criteria that the row must satisfy.

modifying data in a table mysql> update t1 set age =  '  WHERE name =  ' XJP '
; query ok, 1 row affected  (0.00 sec) rows matched: 1  changed:
 1  warnings: 0 mysql> select * from t1; +------+------+------+------------+------------+ | name | age  | sex  |
 birthday   | address    | +------+------+------+------------+------------+ | xjp  | 22   | null
 | 1994-01-10 | [email protected] | +------+------+------+------------+------------+ 1 row in set  (0.00 sec) insert more data MySQL
> insert into t1 values  (' WLS ', ' + ', NULL, ' 1995-03-22 ', ' [email protected] '); query ok, 1 row affected  (0.00 sec) mysql> insert into t1  Values  (' Alex ', ' ' n ', NULL, ' 1987-06-22 ', ' [email protected] '); query ok, 1 row affected  (0.00 sec) mysql> insert into t1 
values  (' GG ', ' + ', NULL, ' 1990-06-10 ', ' [email protected] '); query ok, 1 row affected  (0.00 sec) mysql> insert into t1 
values  (' Kyf ', ' n ', NULL, ' 1985-07-24 ', ' [email protected] '); query ok, 1 row affected  (0.00 SEC)


3, row query

Check for people over the age of 25 and people under 25 years old

mysql> select * from t1 where age >  ' 25 '; +------+------+------+------------+--------------------+ | name | age  | sex   | birthday   | address        
    | +------+------+------+------------+--------------------+ | alex | 50   |  null | 1987-06-22 | [email protected]    | | gg    | 30   | NULL | 1990-06-10 | [email protected]    | | kyf  | 33   | null | 1985-07-24 | [
email protected] | +------+------+------+------------+--------------------+ 3 rows in set  (0.00 sec) MySQL
> SELECT * FROM t1 WHERE age <  ' 25 '; +------+------+------+------------+--------------+ | name | age  | sex  | 
birthday   | address      | +------+------+------+------------+--------------+ | xjp  | 22   | null  | 1994-01-10 | [email protected]   | | wls  | 18
   | null | 1995-03-22 | [email protected] | +------+------+------+------------+--------------+ 2 rows in set  (0.00 sec)


Search for people older than 25 years old, and email [email protected]

Mysql> SELECT * from T1 WHERE > ' and address = ' [email protected] ';
+------+------+------+------------+-----------------+
| name | age | sex | birthday | address |
+------+------+------+------------+-----------------+
| alex | 50 | NULL | 1987-06-22 | [Email protected] |
+------+------+------+------------+-----------------+
1 row in Set (0.00 sec)


Query age is 30 or 50 years old person

Mysql> SELECT * from t1 WHERE age = ' + ' or age = ' + ';
+------+------+------+------------+------------------+
| name | age | sex | birthday | address |
+------+------+------+------------+------------------+
| alex | 50 | NULL | 1987-06-22 | [Email protected] |
| GG | 30 | NULL | 1990-06-10 | [Email protected] |
+------+------+------+------------+------------------+
2 rows in Set (0.00 sec)


And and or can be mixed, but and has a higher priority than or. If you use two operators, it's a good idea to use parentheses to indicate how to group the conditions.

Mysql> SELECT * from the T1 WHERE (age = '/' or age = ' + ') or (name = ' WLS ' and name = ' Kyf ');
+------+------+------+------------+------------------+
| name | age | sex | birthday | address |
+------+------+------+------------+------------------+
| alex | 50 | NULL | 1987-06-22 | [Email protected] |
| GG | 30 | NULL | 1990-06-10 | [Email protected] |
+------+------+------+------------+------------------+
2 rows in Set (0.00 sec)


4, column query

Find each person's birthday; separate them with commas

Mysql> SELECT name,birthday from T1;
+------+------------+
| name | birthday |
+------+------------+
| xjp | 1994-01-10 |
| WLS | 1995-03-22 |
| Alex | 1987-06-22 |
| GG | 1990-06-10 |
| KYF | 1985-07-24 |
+------+------------+
5 rows in Set (0.00 sec)


Find the birthdays of GG and KYF

Mysql> SELECT name,birthday from t1 WHERE name = ' gg ' OR ' name = ' KYF ';
+------+------------+
| name | birthday |
+------+------------+
| gg | 1990-06-10 |
| KYF | 1985-07-24 |
+------+------------+
2 rows in Set (0.00 sec)


5. ORDER BY

Sort by age (default to Ascending)

Mysql> SELECT name,age from T1 ORDER by age;
+------+------+
| name | age |
+------+------+
| wls | |
XJP | |
| GG | |
| KYF | |
| Alex | |
+------+------+
5 rows in Set (0.00 sec)

On the character type column, similar to all other comparison operations, the classification function is normally performed in case-sensitive manner. This means that the order is not defined for columns that are identical but differ in case. For a column, you can use binary to enforce a case-sensitive classification feature


Sort by age (descending)

Mysql> SELECT name,age from T1 ORDER by age DESC;
+------+------+
| name | age |
+------+------+
| alex | | |
KYF | |
| GG | |
| XJP | |
| WLS | |
+------+------+
5 rows in Set (0.00 sec)


ORDER BY in SQL injection can be used to query the current page of the statement total number of fields, such as this article, there are 5 fields, then order by 6,mysql output error message, it proves that the current query has 5 fields

mysql> select * from t1 order by 4; +------+------+------+------------+--------------------+ | name | age  | sex   | birthday   | address        
    | +------+------+------+------------+--------------------+ | kyf  | 33   |  null | 1985-07-24 | [email protected] | | alex | 50    | null | 1987-06-22 | [email protected]    | |  gg   | 30   | null | 1990-06-10 | [email  protected]   | | xjp  | 22   | null |  1994-01-10 | [email protected]         | | wls   | 18   | null | 1995-03-22 | [email protected]      
 | +------+------+------+------------+--------------------+ 5 rows in set  (0.00 sec) MySQL
> SELECT * FROM t1 ORDER BY 5; +------+------+------+------------+--------------------+ | name | age  | sex   | birthday   | address        
    | +------+------+------+------------+--------------------+ | xjp  | 22   |  NULL | 1994-01-10 | [email protected]          | | alex | 50   | null | 1987-06-22 | [email  protected]    | | wls  | 18   | null |  1995-03-22 |  [Email protected]       | | gg   | 30    | null | 1990-06-10 | [email protected]   | |&NBSP;KYF   | 33   | null | 1985-07-24 | [email protected]
 | +------+------+------+------------+--------------------+ 5 rows in set  (0.00 sec) MySQL
> SELECT * FROM t1 ORDER BY 6; error 1054  (42S22): unknown column  ' 6 '  in  ' Order clause '


This article is from the "Linux Sailing" blog, make sure to keep this source http://jiayimeng.blog.51cto.com/10604001/1906128

MySQL essay one

Related Article

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.