mySQL简明入门

详情请参考
http://downloads.mysql.com/docs/mysql-tutorial-excerpt-5.1-en.pdf

mysql -u root -p 回车
密码 12345

Ctrl + D 退出

查看版本
SELECT VERSION(), CURRENT_DATE;

查看数据库
SHOW DATABASES;

创建数据库
 CREATE DATABASE menagerie;

使用数据库
 USE menagerie

创建表格
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

显示数据库中的表格的名称
SHOW TABLES;

显示表格各列的属性
DESCRIBE pet;

从本地读取文件
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
注意, 如果是Windows, 要使用 LINES TERMINATED BY 'rn';

在表格中插入一行
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
INSERT INTO pet VALUES ('Fluffy',' Harold ','cat ','f ','1993-02-04',NULL);
INSERT INTO pet VALUES ('Claws ','Gwen ','cat ','m ','1994-03-17',NULL);
INSERT INTO pet VALUES ('Buffy ','Harold ','dog ','f ','1989-05-13',NULL);
INSERT INTO pet VALUES ('Fang ','Benny',' dog ','m ','1990-08-27',NULL);
INSERT INTO pet VALUES ('Bowser ','Diane ','dog ','m ','1979-08-31','1995-07-29');
INSERT INTO pet VALUES ('Chirpy',' Gwen',' bird',' f',' 1998-09-11',NULL)
INSERT INTO pet VALUES ('Whistler',' Gwen',' bird',' NULL', '1997-12-09',NULL)
INSERT INTO pet VALUES ('Slim',' Benny',' snake',' m ','1996-04-29',NULL)

从数据表中获取数据
SELECT 的基本语句格式:
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

### 查询全部数据
SELECT * FROM pet;

### 修改部分数据
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

### 查询部分数据
SELECT * FROM pet WHERE name = 'Bowser';

### 比较运算
SELECT * FROM pet WHERE birth >= '1998-1-1';

### 逻辑运算 AND
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

### 逻辑运算 OR
SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';

### 运算的优先级
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');

### 选择特定的列
SELECT name, birth FROM pet;

SELECT owner FROM pet;

### 去重复
SELECT DISTINCT owner FROM pet;

### SELECT 的基本组合
SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';

### 排序, 默认是 由小到大排序
SELECT name, birth FROM pet ORDER BY birth;

### 由大到小排序
SELECT name, birth FROM pet ORDER BY birth DESC;

### 多个列排序
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

### 当前日期
CURDATE()

### 两个日期相隔时间
TIMESTAMPDIFF()

### 形成新的一列
AS age (见后面的例子)

### 日期计算
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;

### 加入新一列的表格再排序, 按照name
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;

### 加入新一列的表格再排序, 按照 age
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;

### 判断 is not null
SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;

### 时间计算
YEAR(), MONTH(), DAYOFMONTH()

### 哪个月份出生
SELECT name, birth, MONTH(birth) FROM pet;

### 按照运算出的结果查找
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

### 十二月份等的处理
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

### 对NULL的判断
SELECT 1 IS NULL, 1 IS NOT NULL;

#### 匹配与正则表达式, 寻找name中以b开头的
SELECT * FROM pet WHERE name LIKE 'b%';

#### 寻找name中以fy结尾的
SELECT * FROM pet WHERE name LIKE '%fy';

#### 寻找名字中含有w的
SELECT * FROM pet WHERE name LIKE '%w%';

#### 寻找名字只包含五个字母的, 用五个_
SELECT * FROM pet WHERE name LIKE '_____';

#### 正则表达式的关键字
REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE)

#### 正则表达式
. 任意单个字符
[a-z] a到z的任意字符
[0-9] 0到9的任意字符
“*” 之前, 放任何字符, 表示的是任意重复次数的该字符
.* 表示任意重复次数的任意字符
^ 开始
$ 结束
### 正则表达式举例
### 以b开始的人名(不区分大小写)
SELECT * FROM pet WHERE name REGEXP '^b';

### 匹配大小写 BINARY, 名字以小写的b开头的
SELECT * FROM pet WHERE name REGEXP BINARY '^b';

### 名字以fy结尾的
SELECT * FROM pet WHERE name REGEXP 'fy$';

### 名字中含有w的
SELECT * FROM pet WHERE name REGEXP 'w';

### 名字仅由五个字母组成
SELECT * FROM pet WHERE name REGEXP '^.....$';
或者 SELECT * FROM pet WHERE name REGEXP '^.{5}$';

### 统计表格的行数
SELECT COUNT(*) FROM pet;

### GROUP BY, 针对每一个水平进行计算
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
### 分组可以同时考虑几个因素
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

### 进一步筛选, 只要 dog 或者 cat的部分记录
SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;

### 只保留已知性别的, 按照 species 和 sex 统计
SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;

注意: select后面的列, 如果使用了count()函数, 则后面的group by后面, 必须跟上相同的列. 否则mysql会报错.

####### 使用多个表格
#### 创建一个新的表
CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));

#### 载入数据
LOAD DATA INFILE '/home/jinlong/programming/mysql/event.txt' INTO TABLE event;

#### 同时查询两个表格, 注意应该先将两个表格
用 INNER JOIN 合并.
ON 表格合并的凭借 对于pet表格来说, 是name, 所以是 pet.name
对于event表格来说, 也是name, 所以是event.name
而筛选的条件,是 event表格中的type为 'litter'

SELECT pet.name,
 (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,remark
 FROM pet INNER JOIN event
ON pet.name = event.name
 WHERE event.type = 'litter';

###为了计算的方便, 有时候可以将同一个表格进行 INNER JOIN
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
 FROM pet AS p1 INNER JOIN pet AS p2
ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

#### 查询数据库的名称
SELECT DATABASE();

#### 显示表名
SHOW TABLES;

#### 获取表格的结构
DESCRIBE pet;

#### MYSQL的批处理 从terminal运行
UNIX平台
mysql < batch-file
WINDOWS 平台
mysql -e "source batch-file"

####
#### 从terminal运行 mySQL的批处理文件
mysql -h host -u user -p < batch-file

#### 从terminal运行 结果的分屏显示
mysql < batch-file | more

#### 从terminal运行 输出结果导出到文件
mysql < batch-file > mysql.out

#### 从terminal运行 在批处理模式下显示输出
mysql -t

#### 从terminal运行 在批处理模式下, 保存输入的命令
mysql -vvv

#### 在mySQL中运行脚本文件
source filename;
或 . filename;


####################################################################
### 创建一个商品价目表, 并基于该表做各种查询

show databases;
create database test;
use test;

CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20)
DEFAULT ''
NOT NULL,
price
DOUBLE(16,2)
DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));

INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

SELECT * FROM shop;

#### 查找最贵商品
SELECT MAX(article) AS article FROM shop;

#### 查找最高价对应的货品, 供应商
SELECT article, dealer, price
FROM
shop
WHERE price=(SELECT MAX(price) FROM shop);

#### 每一组的最贵商品
SELECT article, MAX(price) AS price
FROM
shop
GROUP BY article;

#### 每组最贵商品所在的行
SELECT article, dealer, price
FROM
shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);

#### 临时变量
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

######################################
#### KEYS

CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;

###### 查询keys
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1'

### 查询每个月的访问量

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);


SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;


##### 为行增加 unique identity for new rows
AUTO_INCREMENT

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;