1 了解SQL

數(shù)據(jù)庫基礎
database table column row primary key

2 mysql 簡介

MySQL是一個關系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關系型數(shù)據(jù)庫管理系統(tǒng)之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關系數(shù)據(jù)庫管理系統(tǒng)) 應用軟件之一。

3 庫和表

  use database;
  show databases;
  show tables;
  show column from table_name;
  show status; #服務器狀態(tài)

4 檢索數(shù)據(jù)

select * from  table_name;
select prod_name,xxx,xxx from table_name;

select DISTINCT pro_id from products;
select prod_name from product LIMIT 5;

5 排序檢索數(shù)據(jù)

select prod_name from products ORDER BY prod_name,prod_name;
select prod_name from products order by prod_name DESC(ASC);

6 過濾數(shù)據(jù)

select prod_name,prod_price from products where  prod_price = xxx;
select prod_name from products where prod_price between 5 and 10;

select prod_name from products where vend_id 1003 and prod_price <= 10;
select prod_name from products where vend_id 1003 or prod_price <= 10;
select prod_name from products where vend_id in (1000,1007) order by prod_name desc;
select prod_name from products where vend_id not_in (1000,1007) order by prod_name desc;

select prod_id from products where prod_name like 'vate%';
select prod_id from products where prod_name like '_vate';

7 正則

select prod_name from products where prod_name REGEXP '.000' order by prod_mame;
select prod_name from products where prod_name REGEXP '.000|0001' order by prod_mame;
[123] = 1|2|3
[0-9a-z]
#字符類
[:alnum:]  # 任意字母和數(shù)組 [a-zA-Z0-9]
[:alpha:]  # 任意字符 [a-zA-Z]
[:blank:]  # 空格和制表[\\t]
[:cntrl:]  # ASCII控制字符(ASCII 0到37和127)
[:digit:]    # 任意數(shù)組 [0-9]
[:graph:]  # 與[:print:]相同，但不包括空格
[:print:] # 任意 可打印字符
[:lower:]  # 任意小寫字母 [a-z]
[:punct:]  # 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] # 包括 空格在內(nèi)的任意空白字符 [\\f\\n\\r\\t\\v]
[:upper:] #任意大寫字母[A-Z]
[:xdigit:]  # 任意十六進制數(shù)組[a-fA-F0-9]
#重復元字符
*  # 0個或多個匹配
+ # 1個或多個匹配 {1，}
? # 0個或1個匹配{0,1}
{n} # 指定數(shù)目匹配
{n,} # 不少于指定數(shù)目匹配
{n,m} # 匹配數(shù)目的范圍 （m不超過225）
eq: select prod_name from products where prod_name PRGXP '[[:digit:]]{4}'
#定位元字符
^ # 文本開始
$ # 文本介紹
[[:<:]] # 詞的開始
[[:>:]] # 詞的結(jié)束
eq: select prod_name from products where prod_name PRGXP '^[0-9\\.]'