一、SQL基本知识
1）. 常用数据类型：
- TINYINT: 一个字节，范围为-128-127或者0-255（无符号）；
- SMALLINT: 两个字节，范围为-32768-32767或者0-65535（无符号）；
- MEDIUINT：三个字节，范围为-223-223-1或者0-224-1（无符号）；
- INT：四个字节，......................
- BIGINT：八个字节，.................
- CHAR: 字符类型，申请多少空间就占用多少空间，CHAR(20)：所有字段都占用20字节；
- VARCHAR:可变字符串类型，存储空间为实际长度+1;
- ENUM:枚举类型，SEX ENUM("F","M") DEFAULT "M";
- SET:可多选枚举类型，值可为列举的0个或者多个；
2）.时间变量的使用
CURDATE(): 返回当前的日期；
mysql> select curdate(); +------------+ | curdate() | +------------+ | 2013-12-10 | +------------+ 1 row in set (0.07 sec)
NOW():返回当前日期和时间；
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2013-12-10 10:38:36 | +---------------------+ 1 row in set (0.06 sec)
TIMESTAMP:一种时间类型，修改该行数据后自动更新时间到当前时间；
备注：需要设置“刷新时间戳”记时。
时间范围查询可用>/>=/</<=等运算符比较大小。
二、SQL命令使用：
CREATE TABLE:
mysql> create table employee (id int not NULL,name varchar(20) not null,sex enum("F","M") default "M",age int); Query OK, 0 rows affected (0.01 sec)
describe tb_name; #查看表的结构（包含字段、类型、长度...）
mysql> describe employee; +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | NO | | NULL | | | sex | enum('F','M') | YES | | M | | | age | int(11) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
INSERT；#插入数据
语法：
INSERT [INTO] tbl_name [(col_name,...)] VALUES (pression,...),…
INSERT [INTO] tbl_name SET col_name=expression, ...
mysql> insert into employee values(1,'ABC','M',20); Query OK, 1 row affected (0.04 sec) mysql> insert into employee set id=2,name='DEF',sex='F',age=25; Query OK, 1 row affected (0.00 sec)
UPDATE: #修改数据
语法：UPDATE tbl_name SET 要更改的列；
mysql> update employee set age=33 where name='ABC'; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employee where name='ABC'; +----+------+--------+------+------+ | id | name | depart | sex | age | +----+------+--------+------+------+ | 1 | ABC | HVS | M | 33 | +----+------+--------+------+------+ 1 row in set (0.00 sec)
DELETE: #删除数据
语法：DELETE FROM tbl_name WHERE 要删除的记录；
mysql> delete from employee where id=6; Query OK, 1 row affected (0.33 sec) mysql> select * from employee; +----+------+--------+------+------+ | id | name | depart | sex | age | +----+------+--------+------+------+ | 1 | ABC | HVS | M | 33 | | 2 | DEF | XVE | F | 25 | | 3 | GHI | XVE | M | 30 | | 4 | LKD | NEXT | M | 38 | | 5 | EDO | HVS | F | 28 | | 7 | MJE | OTHERS | M | 18 | | 8 | CUT | XVE | M | 23 | +----+------+--------+------+------+ 7 rows in set (0.00 sec)
SELECT: #查找数据记录
SELECT 语句的语法如下：
SELECT selection_list 选择哪些列
FROM table_list 从何处选择行
WHERE primary_constraint 行必须满足什么条件
GROUP BY grouping_columns 怎样对结果分组
HAVING secondary_constraint 行必须满足的第二条件
ORDER BY sorting_columns 怎样对结果排序(ASC:升序；DESC:降序)
LIMIT count 结果限定
注意：所有使用的关键词必须精确地以上面的顺序给出。例如，一个HAVING 子句必
须跟在GROUP BY子句之后和ORDER BY子句之前。
1）.普通查询：
mysql> select * from employee; +----+------+--------+------+------+ | id | name | depart | sex | age | +----+------+--------+------+------+ | 1 | ABC | HVS | M | 20 | | 2 | DEF | XVE | F | 25 | | 3 | GHI | XVE | M | 30 | | 4 | LKD | NEXT | M | 38 | | 5 | EDO | HVS | F | 28 | | 6 | LEU | HVS | F | 53 | | 7 | MJE | OTHERS | M | 18 | | 8 | CUT | XVE | M | 23 | +----+------+--------+------+------+ 8 rows in set (0.00 sec)
2）.查询每个部门人数：
mysql> select depart,count(*) from employee group by depart; +--------+----------+ | depart | count(*) | +--------+----------+ | HVS | 3 | | NEXT | 1 | | OTHERS | 1 | | XVE | 3 | +--------+----------+ 4 rows in set (0.00 sec)
分组查询用group by；
count（*）：所有满足条件的列数；
COUNT()：函数计数非NULL结果的数目
3）.查询部门列表
mysql> select distinct depart from employee; +--------+ | depart | +--------+ | HVS | | XVE | | NEXT | | OTHERS | +--------+ 4 rows in set (0.00 sec)
mysql> select count(distinct depart) from employee; +------------------------+ | count(distinct depart) | +------------------------+ | 4 | +------------------------+ 1 row in set (0.00 sec)
distinct：查找互不相同的值。
mysql> select * from employee where age>20 and sex='M' order by name limit 2; +----+------+--------+------+------+ | id | name | depart | sex | age | +----+------+--------+------+------+ | 8 | CUT | XVE | M | 23 | | 3 | GHI | XVE | M | 30 | +----+------+--------+------+------+ 2 rows in set (0.00 sec)
LIMIT:限定显示的行数，
LIMIT 5 #只显示5行；
LIMIT 5,10 #显示5-10行；
4）.数值的特殊查询
AVG():#返回一个表中符合条件字段的平均值；
SUM():#返回一个表中符合条件字段的和；
MAX():#返回一个表中符合条件字段的最大值；
MIN():#返回一个表中符合条件字段的最小值。
mysql> select AVG(age) from employee; +----------+ | AVG(age) | +----------+ | 29.3750 | +----------+ 1 row in set (0.00 sec) mysql> select SUM(age) from employee; +----------+ | SUM(age) | +----------+ | 235 | +----------+ 1 row in set (0.00 sec) mysql> select MAX(age) from employee; +----------+ | MAX(age) | +----------+ | 53 | +----------+ 1 row in set (0.00 sec) mysql> select MIN(age) from employee; +----------+ | MIN(age) | +----------+ | 18 | +----------+ 1 row in set (0.00 sec)
5). LIKE...%与NOT LIKE...%查询：
mysql> select * from employee where name like "L%"; +----+------+--------+------+------+ | id | name | depart | sex | age | +----+------+--------+------+------+ | 4 | LKD | NEXT | M | 38 | | 6 | LEU | HVS | F | 53 | +----+------+--------+------+------+ 2 rows in set (0.00 sec)
mysql> select * from employee where name not like "L%"; +----+------+--------+------+------+ | id | name | depart | sex | age | +----+------+--------+------+------+ | 1 | ABC | HVS | M | 20 | | 2 | DEF | XVE | F | 25 | | 3 | GHI | XVE | M | 30 | | 5 | EDO | HVS | F | 28 | | 7 | MJE | OTHERS | M | 18 | | 8 | CUT | XVE | M | 23 | +----+------+--------+------+------+ 6 rows in set (0.00 sec)
LIKE...%:模糊查询，与通配符%一起使用；
NOT LIKE...%:与LIKE相反，不满足条件的值；
LIKE "_":查看某字段为单个字符的项；_：匹配单个字符。
6). BETWEEN...AND...用法
BETWEEN...AND...查询一个范围的值，包含边界值；
mysql> select * from employee where age between 20 and 30; +----+------+--------+------+------+ | id | name | depart | sex | age | +----+------+--------+------+------+ | 1 | ABC | HVS | M | 20 | | 2 | DEF | XVE | F | 25 | | 3 | GHI | XVE | M | 30 | | 5 | EDO | HVS | F | 28 | | 8 | CUT | XVE | M | 23 | +----+------+--------+------+------+ 5 rows in set (0.00 sec)
7). 多表的查询
mysql> select employee.name,employee.depart,score.score from employee,score wher e score.score>60 and employee.name=score.name; +------+--------+-------+ | name | depart | score | +------+--------+-------+ | ABC | HVS | 85.5 | | DEF | XVE | 88 | | LEU | HVS | 78 | | CUT | XVE | 100 | +------+--------+-------+ 4 rows in set (0.08 sec)
当组合(联结-join)来自多个表的信息时，你需要指定在一个表中的记录怎样能匹配其它表的记录。
这很简单，因为它们都有一个name列。查询使用WHERE子句基于name值来匹配2个表中的记录。
查询各个部门中分数最高的人名字/部门/分数：
mysql> select employee.depart,score.name,max(score.score) from employee,score wh ere employee.name=score.name group by employee.depart order by employee.depart; +--------+------+------------------+ | depart | name | max(score.score) | +--------+------+------------------+ | HVS | ABC | 85.5 | | NEXT | LKD | 81 | | OTHERS | MJE | 95 | | XVE | DEF | 100 | +--------+------+------------------+ 4 rows in set (0.00 sec)
8). 正则表达式查询
正则表达式匹配使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)
- 一个字符类“[...]”匹配在方括号内的任何字符，例如[abc]/[0-9]/[a-z]/[a-zA-Z]等。
- 在模式开始处使用“^”或在模式的结尾用“$”。
- “.”匹配任何单个的字符。
mysql> select * from employee where name regexp "^[aA]"; +----+------+--------+------+------+ | id | name | depart | sex | age | +----+------+--------+------+------+ | 1 | ABC | HVS | M | 33 | +----+------+--------+------+------+ 1 row in set (0.00 sec) mysql> select * from employee where name NOT REGEXP "^[aA]"; +----+------+--------+------+------+ | id | name | depart | sex | age | +----+------+--------+------+------+ | 2 | DEF | XVE | F | 25 | | 3 | GHI | XVE | M | 30 | | 4 | LKD | NEXT | M | 38 | | 5 | EDO | HVS | F | 28 | | 6 | LEU | HVS | F | 53 | | 7 | MJE | OTHERS | M | 18 | | 8 | CUT | XVE | M | 23 | +----+------+--------+------+------+ 7 rows in set (0.00 sec)
其他命令
show databases; #显示所有数据库列表
show tables; #显示当前数据库表列表
use database_name; #使用或切换数据库
drop table tb_name; #删除表格
drop database db_name; #删除数据库
三、SQL高级知识点
1).表的锁定与解锁
内部锁定可以避免客户机的请求相互干扰。
语法：
锁定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]
解锁表：UNLOCK TABLES
LOCK TABLES tbl_name READ #添加读锁，添加后不能写，只能查询数据；
LOCK TABLES tbl_name WRITE #添加写锁，其他用户不能读也不能写，读写命令会一直排队到解锁，用户本身能读写。
mysql> LOCK TABLES employee READ; Query OK, 0 rows affected (0.00 sec) mysql> update employee set depart='C10' where name='ABC'; ERROR 1099 (HY000): Table 'employee' was locked with a READ lock and can't be up dated
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
2）.索引的原理
索引的原理是通过一些算法和结构存储数据，不通数据库采用不用的算法，比如B-Tree，哈希表等，MYSQL采用B-Tree存储。
字符串是自动的压缩前缀和结尾空间，存储到B-Tree中，如果表对应查询的列有一个索引，能快速的到达一个位置搜寻到数据文件。
非索引的查询采用的是顺序查询，逐行对比的方式，大数据情况下会比较慢。
索引弊端：
- 要占用磁盘空间，可能比数据文件更大；
- 加快了查询速度，但是增加了插入/删除/更新的时间，降低了性能；
索引使用准则：
- 索引列，不一定是所要查询的字段，最好是条件字段；
- 优先使用唯一索引（主键、值唯一的字段）；
- 使用短索引，长字符串不比使用全部值；
- 不要过度索引。
浙公网安备 33010602011771号