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 BYname;

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

--- 判断 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 BYspecies, 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;