连接与断开服务器
mysql -h host -u user -p
help
quit
版本号和当前日期
SELECT VERSION(), CURRENT_DATE;
SELECT VERSION(); SELECT NOW();
SELECT
USER()
,
CURRENT_DATE;
/* 输入\c取消 */
创建并使用数据库
SHOW DATABASES; //显示所有数据库
USE test; //使用某个数据库
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host'; //授权
创建并选择数据库
CREATE DATABASE menagerie; //创建数据库
USE menagerie； //使用该数据库
创建表
SHOW TABLES; //展示所有表
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); //创建一个宠物表;
DESCRIBE pet; //打印表结构
将数据装入表中
//load 文件数据到表中
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
LINES TERMINATED BY '\r
';
//ISNERT
INSERT INTO pet
VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
查询
//所有记录
SELECT * FROM pet;
//清空并重新装载
DELETE FROM pet;
LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE 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';
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
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; //DISTINCT 唯一记录
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;
日期计算
// YEAR() 取年份 RIGHT() 从右边截取字符串
SELECT name, birth, CURDATE(),(YEAR(CURDATE())-YEAR(birth))-
(RIGHT(CURDATE(),5) WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
NULL值操作
//MySQL中，0或 NULL意味着假而其它值意味着真
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
SELECT 1 IS NULL, 1 IS NOT NULL;
//death IS NOT NULL而不使用death != NULL(death != false)
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
模式匹配
//使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)。
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';
// ‘.’匹配任何单个的字符。 字符类“[...]”匹配在方括号内的任何字符。
//REGEXP和NOT REGEXP操作符
//^x 以X开头的字符， x$ 以x结尾的字符
SELECT * FROM pet WHERE name REGEXP '^b'; //以“b”开头的名字
SELECT * FROM pet WHERE name REGEXP BINARY '^b'; //以2进制b开头的字符串
SELECT * FROM pet WHERE name REGEXP 'fy#39;; //以fy结尾的字符串
SELECT * FROM pet WHERE name REGEXP 'w'; //包含w的字符串
//有5个字符串的字符串
SELECT * FROM pet WHERE name REGEXP '^.....#39;;
//同上｛5｝匹配5次
SELECT * FROM pet WHERE name REGEXP '^.{5}#39;;
计数行
SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT owner, COUNT(*) FROM pet;
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;
SELECT species, sex, COUNT(*) FROM pet
WHERE species = 'dog' OR species = 'cat'
GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet
WHERE sex IS NOT NULL
GROUP BY species, sex;
多表查询
//创建一个新表
CREATE TABLE event (name VARCHAR(20), date DATE,
type VARCHAR(15), remark VARCHAR(255));
//导入数据
LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
//两个表联合查询
SELECT pet.name,
(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)
获得数据库和表的信息
SELECT DATABASE(); //选择数据库
SHOW TABLES; //显示所有表
DESCRIBE pet; //打印表结构
批处理模式下使用mysql
//把MysqL语句放入一个文件中，使用batch-file 读取文件
shell> mysql < batch-file
//windows 下运行MysqL文件
mysql -e "source batch-file"
//指定连接
mysql -h host -u user -p < batch-file
mysql < batch-file | more
mysql < batch-file > mysql.out
