Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL development common query summary     - Linux system security check method (Linux)

- How the program is executed (Programming)

- Linux non-root user uses less than 1024 ports (Linux)

- Ubuntu install image browser and manager Phototonic 1.6.17 (Linux)

- Ubuntu uses the / etc / profile file to configure the JAVA environment variable (Linux)

- To access an Oracle database using Instant Client (Database)

- Nginx version of helloworld (Server)

- Use Docker / LXC quickly launch a desktop system (Linux)

- Java Foundation - The relationship between abstract classes and interfaces (Programming)

- Summarize small problems encountered in the use Lua (Programming)

- RabbitMQ installation, configuration, monitoring (Linux)

- ORA-01000 Solution (Database)

- Linux rpm command Detailed (Linux)

- Linux kernel RCU (Read Copy Update) lock Brief (Linux)

- Java Prototype Pattern (Programming)

- Installation under Linux to deploy Java (Linux)

- Internal class broadcasting needs public and static (Programming)

- Ubuntu 14.10 install KDE Plasma 5.2 (Linux)

- Use smartmontools view the health status of hard disk (Linux)

- Node.js developers must know four JavaScript concepts (Programming)

 
         
  MySQL development common query summary
     
  Add Date : 2017-04-13      
         
       
         
  1, Numerical data query:

SELECT * FROM tb_name WHERE sum> 100;

Query predicate: >, =, <, < >, =,!>, < , =, >, =

2, the query string

SELECT * FROM tb_stu WHERE sname = 'Cricode.com'

SELECT * FROM tb_stu WHERE sname like 'Uncle% Too'

SELECT * FROM tb_stu WHERE sname like '% programmer'

SELECT * FROM tb_stu WHERE sname like '% PHP%'

3, the query date data type

SELECT * FROM tb_stu WHERE date = '2011-04-08'

Note: Different databases exist differences date data type::

(1) MySQL: SELECT * from tb_name WHERE birthday = '2011-04-08'

(2) SQL Server: SELECT * from tb_name WHERE birthday = '2011-04-08'

(3) Access: SELECT * from tb_name WHERE birthday = # 2011-04-08 #

4. Data query logic

SELECT * FROM tb_name WHERE type = 'T'

SELECT * FROM tb_name WHERE type = 'F'

Logical operators: and or not

5, the query is not empty data

SELECT * FROM tb_name WHERE address < > "order by addtime desc

NOTE: <> = equivalent in PHP!

6, the use of variable numerical data query

SELECT * FROM tb_name WHERE id = '$ _POST [text]'

NOTE: When you use the variable query data, incoming SQL variable does not need to use quotes, because PHP string and numeric data connection, the program will automatically convert numeric data into strings, and then to be connected string connection

7, the use of query string variable data

SELECT * FROM tb_name WHERE name LIKE '% $ _ POST [name]%'

Exact match method "%%" indicates that can appear in any position

8, query the top n records

SELECT * FROM tb_name LIMIT 0, $ N;

limit the other statements, and other statements such as order by joint use of ever-changing uses SQL statements, make the program very flexible

9, n records after inquiry

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $ n

10, n query records from the specified starting position

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $ _POST [begin], $ n

Note: The data id is starting from 0

11, query statistics in the first n records

SELECT *, (yw + sx + wy) AS total FROM tb_score ORDER BY (yw + sx + wy) DESC LIMIT 0, $ num

12, query data specified period of time

Field FROM table WHERE field names BETWEEN initial value AND SELECT termination value you want to find

SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18

13, monthly statistical data queries

SELECT * FROM tb_stu WHERE month (date) = '$ _POST [date]' ORDER BY date;

NOTE: SQL language provides the following functions using these functions can be easily achieved by year, month, day queries
year (data): Returns the data expression of the year corresponding to the value branch
month (data): Returns the data expression value corresponding month branch
day (data): Returns the data corresponding to the date of the expression values

14, the query is greater than the record specified conditions

SELECT * FROM tb_stu WHERE age> $ _ POST [age] ORDER BY age;

15, the query results do not display duplicate records

SELECT DISTINCT field FROM table WHERE name query condition

Note: SQL statement DISTINCT must be used in conjunction with a WHERE clause, or the output of information will not change, and the field can not be used in place of *

16, NOT predicate query condition combination

(1) NOT BERWEEN ... AND ... the rows can be changed when the query data between start and end values between termination value

(2) IS NOT NULL values of non-empty query

(3) IS NULL for null query

(4) NOT IN Depending on the type of keywords are included in the list or exclusion from the list, the search for the specified expression, search expression can be a constant or a column name, column name can be a constant, but more under many cases subquery

17, the display data in the table duplicate records and the number of records

SELECT name, age, count (*), age FROM tb_stu WHERE age = '19' group by date

18, the data in descending / ascending order inquiry

SELECT field name FROM tb_stu WHERE condition ORDER BY field DESC DESC

SELECT field name FROM tb_stu WHERE condition ORDER BY field ASC ASC

NOTE: When you sort the fields do not specify a sort order, the default is ascending order ASC

19, the data multi-criteria query

SELECT field name FROM tb_stu WHERE condition ORDER BY 1 ASC field Field 2 DESC ...

Note: For more information query condition sort is common to limit the recording of output, under normal circumstances, since it is not a single condition restrictions, so there are some differences in the output.

20, the statistical results are sorted

Function SUM ([ALL] field name) or SUM ([DISTINCT] field names) can be realized on the field summation, when a function is ALL for all fields of all records that sum, if it is not all that the field was DISTINCT summation field duplicate records

Such as: SELECT name, SUM (price) AS sumprice FROM tb_price GROUP BY name

SELECT * FROM tb_name ORDER BY mount DESC, price ASC

21, single data packet statistics

SELECT id, name, SUM (price) AS title, date FROM tb_price GROUP BY pid ORDER BY title DESC

NOTE: When the packet group by statement to sort order by statement also appear in the SQL statement to the grouping of statements written in front sorted statement, or an error

22, multi-column data packet statistics

Similar multi-column data packet data packet statistics and separate statistics

SELECT *, SUM (1 * Field Field 2) AS (new field 1) FROM table GROUP BY field ORDER BY 1 DESC new field

SELECT id, name, SUM (price * num) AS sumprice FROM tb_price GROUP BY pid ORDER BY sumprice DESC

NOTE: by group behind the statement is generally the number of columns is not an aggregate function, that is, not to be grouped columns

23, multi-table packet statistics

SELECT a.name, AVG (a.price), b.name, AVG (b.price) FROM tb_demo058 AS a, tb_demo058_1 AS b WHERE a.id = b.id GROUP BY b.type;
     
         
       
         
  More:      
 
- Batch kill processes using awk command (Linux)
- Linux uses a screen recording under RecordMyDesktop (Linux)
- Linux, set and view environment variables (Linux)
- How to test your MongoDB application upgrade? (Database)
- Linux Learning Notes: Users and Groups (Linux)
- MySQL to manage multiple instances of method (Database)
- How to restart after a crash Cinnamon (Linux)
- GNU Linux use diff to generate a patch with the patch (Linux)
- Source Analysis: Java object memory allocation (Programming)
- Installation Experience open source car Automotive Grade Linux system (Linux)
- ImageMagick Tutorial: How to cut images in Linux command line (Linux)
- Use py2exe to generate exe files Python script (Programming)
- MySQL 5.6 Open full query log (Database)
- Bubble Sort Algorithms (Programming)
- MySQL monitoring tool -Innotop (Database)
- Linux System Getting Started Learning: The Linux log (Linux)
- RedHat / CentOS ext4 partition can not be formatted large supplementary ext4 formatting (Linux)
- Set up MySQL master and slave servers under Ubuntu 14.04 (Server)
- Mhddfs: multiple smaller partitions into one large virtual storage (Linux)
- Oracle inverted reverse function (Database)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.