mysql必会知识

1 了解SQL

数据库基础
database table column row primary key

2 mysql 简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

3 库和表

  use database;
  show databases;
  show tables;
  show column from table_name;
  show status; #服务器状态

4 检索数据

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 排序检索数据

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

6 过滤数据

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:]  # 任意字母和数组 [a-zA-Z0-9]
[:alpha:]  # 任意字符 [a-zA-Z]
[:blank:]  # 空格和制表[\\t]
[:cntrl:]  # ASCII控制字符(ASCII 0到37和127)
[:digit:]    # 任意数组 [0-9]
[:graph:]  # 与[:print:]相同，但不包括空格
[:print:] # 任意 可打印字符
[:lower:]  # 任意小写字母 [a-z]
[:punct:]  # 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] # 包括 空格在内的任意空白字符 [\\f\\n\\r\\t\\v]
[:upper:] #任意大写字母[A-Z]
[:xdigit:]  # 任意十六进制数组[a-fA-F0-9]
#重复元字符
*  # 0个或多个匹配
+ # 1个或多个匹配 {1，}
? # 0个或1个匹配{0,1}
{n} # 指定数目匹配
{n,} # 不少于指定数目匹配
{n,m} # 匹配数目的范围 （m不超过225）
eq: select prod_name from products where prod_name PRGXP '[[:digit:]]{4}'
#定位元字符
^ # 文本开始
$ # 文本介绍
[[:<:]] # 词的开始
[[:>:]] # 词的结束
eq: select prod_name from products where prod_name PRGXP '^[0-9\\.]'