mysql介绍自己的话_一句话介绍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 '/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)

-> AS age

-> FROM pet;

计算宠物年龄，并按名字排序

mysql> SELECT name, birth, CURDATE(),

-> (YEAR(CURDATE())-YEAR(birth))

-> - (RIGHT(CURDATE(),5)

-> AS age

-> FROM pet ORDER BY name;

计算已故宠物年龄

mysql> SELECT name, birth, death,

-> (YEAR(death)-YEAR(birth)) - (RIGHT(death,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;