mysql通配符使用 .NET009-MySql Foundation

MySql For Mac

MySql Support Website

You can download the script from this site() to create the database, but you need to set one database as default schema.

Database defined

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system(DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database.Data within the most common types of databases in operation today is typically modeled in rows and columns in a series of tables to make processing and data querying efficient. The data can then be easily accessed, managed, modified, updated, controlled, and organized. Most databases use structured query language(SQL) for writing and querying data.

What is Structured Query Language(SQL)?

SQL is a programming language used by nearly all relational databases to query, manipulate, and define data, and to provide access control. SQL was first developed at IBM in the 1970s. SQL has spurred many extensions from companies such as IBM, Oracle, and Microsoft.

Use MySQL

MySQL Basic Operation

SQL is a standard language for storing, manipulating and retrieving data in databases.

Tips: Many SQL developers prefer upper case for all SQL keywords and lower case for all column and table names, which makes the code easier to read and debug.

SELECT

This statement is used to retrieve data from a database. The data returned is stored in a result table, called the result-set. You must know that what and where you want to choose.

Distinct Column

The [SELECT DISTINCT] statement is used to return only distinct(different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different(distinct) values.

Limit

[SELECT prod_name FROM products LIMIT 5,5;] This statement indicates that MySQL returns 5 lines beginning on line 5. The first number is the start position and the second number is the number of lines to retrieve.

ORDER BY

The [ORDER BY] keyword is used to sort the result-set in ascending or descending order. This statement sorts the records in ascending order by default. To sort the records in descending order, use the [DESC] keyword. The [DESC] keyword applies only to column in front of this.

WHERE

The [WHERE] clause is used to filter records.

The [WHERE] clause can be combined with [AND] [OR] [NOT] operators.

Use parentheses in the [WHERE] clause whenever you use a [WHERE] clause with [AND] [OR] operators. Don't relay too much on the default order of calculation, even if it's really what you want. There is no harm in using parentheses, it removes ambiguity.

Tips: When using both the [ORDER BY] and [WHERE] statements, you must leave [ORDER BY] after [WHERE], otherwise an error will be generated. For example, [SELECT * FROM products WHERE xxx ORDER BY prod_name DESC]

[IN] operator is used to specify a range of conditions, and each condition in the range can be matched.

[NOT]

MySql supports the uese of [NOT] to reverse the [IN] [BETWEEN] and [EXIST].

LIKE

The [LIKE] operator is used in a [WHERE] clause to search for a specified pattern in a column.

Wild card(in computers, a symbol that has no particular meaning of its own so that its space can be filled by any real character that is necessary)

SELECT prod_name,prod_id FROM products WHERE prod_name LIKE 'jet%'SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '%se%'

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '_ ton anvil'SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '% ton anvil'

notes:

1.不要过度使用通配符

2.除非有绝对必要，否则不要把通配符用在搜索模式的开始处。

select prod_id,prod_name from products where prod_name like '%jet_

正则表达式 REGEXP

如何在WHERE子句内使用正则表达式更好的控制数据过滤。

检索prod_name包含文本1000的所有行:

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name

这里要注意和LIKE表达式的区别，LIKE表达式如果不适用通配符，会默认匹配整个列值，例如 SELECT prod_name FROM products WHERE prod_name LIKE '1000'将不会返回任何行，如果那一列值仅仅有1000则会返回mysql通配符使用，如果想要匹配带有1000的行则可以用00%

SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name DESC LIMIT 1,2

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000|3000' ORDER BY prod_name

匹配特定字符[] 这个是另一种形式的OR |

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] ton' ORDER BY prod_name DESC

[^123]匹配除这些字符外的任何东西

[0-9]匹配数字

[a-z]匹配任意字母字符

\\. 转义字符(escaping)，查找.

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name DESC LIMIT 3,3

计算字段

字段（field） 基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

连接字段 Contact()

SELECT Contact(A,'(',B,')') AS A_B FROM vendors ORDER BY vend_name SELECT Contact(Trim(A),'(',RTrim(B),')') FROM vendors ORDER BY vend_name

2.别名

SELECT A,B,A*B AS A*B FROM vendors WHERE vend_price>70

3.简单计算

SELECT 3*2 SELECT Trim(A)

函数

文本处理函数

SELECT cus_name,cus_price FROM cus WHERE Soundex(cus_name) = Soundex('Li Yet')

2. 日期和时间处理函数

MySQL 4.1增加了很多关于时间处理的函数，如果在此版本之前则需要参考文档具体了解相关数据库支持的时间日期函数。

SELECT cust_id,order_num FROM orders WHERE Year(order_date) ANDMonth(order_date)匹配出九月的所有订单

3. 数值处理函数

聚集函数

SELECT AVG(A) AS avg_A FROM Customers//聚焦不同值SELECT AVG(DISTINCT A) AS avg_dist_A FROM Customers

分组数据

SELECT prod_id,Count(*) as count_price FROM products GROUP BY prod_id分组// WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。SELECT prod_id,Count(*) AS order FROM orders GROUP BY prod_id Having Count(*)>2

（编辑：91站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!