一句话介绍MySQL(1)

例子在这里

连接数据库
shell> mysql -h host -u user -p

退出数据库
mysql> QUIT

查询版本号和当前日期
mysql> SELECT VERSION(), CURRENT_DATE;

查询用户
mysql> SELECT USER();

找出服务器上当前存在的数据库
mysql> SHOW DATABASES;

给'your_mysal_name'一个在menagerie数据库上完全权限
mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

创建数据库
mysql> CREATE DATABASE menagerie;

使用数据库
mysql> USE menagerie

查看数据库中的数据表格
mysql> SHOW TABLES;

指定数据库布局
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

验证数据表格布局
mysql> DESCRIBE pet;

利用本地文本pet.txt，创建数据表格
mysql> LOAD DATA LOCAL INFILE '<path>/pet.txt' INTO TABLE pet;

插入数据项
mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

更新数据
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

检索所有项
mysql> SELECT * FROM pet;

检索特别项目
mysql> SELECT * FROM pet WHERE name = 'Bowser';

mysql> SELECT * FROM pet WHERE birth > '1998-1-1';

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
    -> OR (species = 'dog' AND sex = 'f');

选择特殊列
mysql> SELECT name, birth FROM pet;

mysql> SELECT owner FROM pet;

检索出每个唯一的输出记录
mysql> SELECT DISTINCT owner FROM pet;

选择行与选择列同时作用
mysql> SELECT name, species, birth FROM pet
    -> WHERE species = 'dog' OR species = 'cat';

简单排序，升序
mysql> SELECT name, birth FROM pet ORDER BY birth;

降序
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

混合排序
mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;

计算宠物年龄
mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet;

计算宠物年龄，并按名字排序
mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY name;

计算已故宠物年龄
mysql> SELECT name, birth, death,
    -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;

检索宠物生日的月份
mysql> SELECT name, birth, MONTH(birth) FROM pet;

检索下月生日的宠物
mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

检索以“b”开头的名字
mysql> SELECT * FROM pet WHERE name LIKE 'b%';

mysql> SELECT * FROM pet WHERE name REGEXP '^b';

名字区分大小写
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

检索以“ fy ” 结尾的名字：
mysql> SELECT * FROM pet WHERE name LIKE '%fy';

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

检索包含“ w ” 的名字
mysql> SELECT * FROM pet WHERE name LIKE '%w%';

mysql> SELECT * FROM pet WHERE name REGEXP 'w';

找出正好五个字母的名字
mysql> SELECT * FROM pet WHERE name LIKE '_____';

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

计算动物总数
mysql> SELECT COUNT(*) FROM pet;

计算每个主人养的宠物
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

计算每种宠物的数量
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;

计算每种性别宠物的数量
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;

按种类和性别组合的宠物数量
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

同上，但只计算猫或者狗的数量
mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = 'dog' OR species = 'cat'
    -> GROUP BY species, sex;

计算已知性别的宠物数量
mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;

删除所有项
mysql> DELETE FROM pet;