Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL development common query summary     - CentOS 6 kernel upgrade to Kernel 3.x (Linux)

- SQL Server 2008 database synchronization Notes (Database)

- A detailed introduction to the Hadoop ecosystem (Server)

- Oracle inverted reverse function (Database)

- CentOS Nginx achieve 3 virtual machine load balancing (Server)

- RedHat Linux 6.5 Enterprise Edition installation Redis 3.0.3 (Database)

- Tmux Getting Start (Linux)

- To install minimize RHEL / CentOS 7 (Linux)

- MySQL simple operation notes under Linux (Database)

- wget command usage Explanation (Linux)

- Ubuntu manually set the DSL broadband connection (Linux)

- Some of the bibliographic management tools to good use on linux (Linux)

- Shell Scripting Basics (Linux)

- Linux systems for entry-learning - Install Go language in Linux (Linux)

- Swift string common method (Programming)

- Use Linux built-in firewall to improve network access control (Linux)

- Spring declarative transaction management (Programming)

- Linux system started to learn: Teaches you install Fedora 22 on VirtualBox (Linux)

- System with Windows Remote Desktop to connect Ubuntu 15.04 (Linux)

- The file name is garbled or deleted files with special characters under Linux (Linux)

 
         
  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:      
 
- Yii2 Advanced Version Copy New Project Problem Solved (Programming)
- SSH security note (Linux)
- Linux System Tutorial: How to Check MariaDB server versions (Database)
- Linux set the maximum number of open files nofile and nr_open, file-max Description (Linux)
- Install minimize RHEL / CentOS 7 some things need to do (Linux)
- 10 Regulation of painless SQL Schema (Database)
- Linux Mint brightness adjustment --xrandr command learning (Linux)
- grub boot error resolution examples (Linux)
- Java, hashcode, equals and == (Programming)
- Linux security concerns again (Linux)
- In addition to wget and curl, what better alternatives (Linux)
- Solve the compatibility problem between Linux and Java at the source in bold font (Linux)
- Java abstract class instantiation (Programming)
- Ubuntu 14.10 Apache installation and configuration (Server)
- Installation Mate Desktop in FreeBSD 10.1 (Linux)
- Android Custom View step (Programming)
- VMware Workstation + Ubuntu 10.04 Download the Android 2.2 source code (Linux)
- SQL Beginner Guide (Database)
- Vim configuration instructions (Linux)
- The difference between VMware virtual machine network mode (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.