Mysql使用介绍

quit 退出

/c 清除

show databases; 查看所有数据库

create database XX; 创建数据库XX

use XX; 使用数据库

select database(); 查看使用的数据库，无返回NULL

show tables; 显示使用数据库表格

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

DESCRIBE YY; 查看表的创建方法是否符合预期要求

LOAD DATA LOCAL INFILE ‘/path/pet.txt’ INTO TABLE YY LINES TERMINATED BY ‘\r\n’; 加载pet.txt（空用\N） 到表YY（win必加YY后行止符）

INSERT INTO YY VALUES (‘Puffball’,’Diane’,’hamster’,’f’,’1999-03-30’,NULL); 在表YY追加一行

SELECT * FROM YY; 查看整个表YY

DELETE FROM YY; 清空表YY

UPDATE YY SET birth = ‘1989-08-31’ WHERE name = ‘Bowser’; 修改表YY

SELECT * FROM YY WHERE name = ‘Bowser’; 查询表YY中name = ‘Bowser’行

SELECT * FROM YY WHERE birth >= ‘1998-1-1’; 查询表YY中birth >= ‘1998-1-1’所有项目并可以结合多个筛选条件，如species = ‘dog’ AND sex = ‘f’; 后面可以接OR，如； (species = ‘cat’ AND sex = ‘m’) OR (species = ‘dog’ AND sex = ‘f’);

SELECT birth FROM YY; 选择表YY整列项birth，多个项目用，分隔如：name，birth

SELECT DISTINCT owner FROM YY; 选择表YY中所有不一样的owner。 DISTINCT限定不同项

SELECT name, species, birth FROM YY WHERE species = ‘dog’ OR species = ‘cat’; 选择表YY中name，species ，birth列，限定条件为 species下的dog和cat

SELECT name, birth FROM YY ORDER BY birth; 选择表YY中的name，birth列通过birth排序

SELECT name, birth FROM YY ORDER BY birth DESC; 选择表YY中的name，birth列通过birth排序DESC代表反序，从 大到小

SELECT name, species, birth FROM pet ORDER BY species, birth DESC; 选择表YY中的name，species，birth列 对species升序，对birth反序，说明：一个列后面加上desc即是反序，都可以加

SELECT name, birth,CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) as age from pet; 选择表YY中的name， birth，显示CURDATE()（当前时间）,
TIMESTAMPDIFF（）通过year比较birth和CURDATE() 的差显示为age列

SELECT name, birth,CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) as age from pet order by age; 显示项 目并通过age从小到大排序

SELECT name,birth,death,TIMESTAMPDIFF(year,birth,death) as age from pet where death is not NULL order by age; death不为空就通过年计算birth和death差值赋值给age 并通过age 排序

SELECT name，birth，month（birth） from YY; 选出表YY中的name，birth 计算birth的月份显示，month（）月 份，year（）年份，dayofmonth（）一个月中的哪一天

SELECT name, birth FROM YY WHERE MONTH(birth) = 5; 选中所有5月份出生

SELECT name, birth FROM YY WHERE MONTH(birth) = MONTH(date_add(CURDATE(),interval 1 month)); date_add月份加1，提取month，得出下个月的月份。

SELECT name, birth FROM YY WHERE MONTH(birth) = MOD(MODTH(CURDATE(),12)) + 1; MOD，当前月/12余数+1可得 到下一个月月份

SELECT * FROM YY WHERE name LIKE ‘b%’; 匹配所有name下面b开头的项目 LIKE 是，NOT LIKE 不是， %匹配多个 （放后面则后面多个，字符前面则前面多个，前后都有则是包含），只匹配一个（多使用多个 ,匹配限定 个数）

SELECT * FROM YY WHERE name REGEXP ‘^b’; 正则匹配^，前面是b开头的name，$匹配结尾，放后面，binary强制匹 配大小写

SELECT * FROM YY WHERE name REGEXP ‘^…..′;通配符前后各一个匹配5个项目，也可以′.5’

SELECT COUNT(*) FROM YY; 计数，多少行

SELECT owner，COUNT(*) FROM YY WHERE GROUP BY owener; 计算每个owener拥有多少宠物并分组显示

SELECT species, sex, COUNT(*) FROM YY GROUP BY species, sex; 每种species和sex组合的数量

SELECT species, sex, COUNT(*) FROM YY WHERE species = ‘dog’ OR species = ‘cat’GROUP BY species, sex;只 在dog和cat上执行检索

SELECT pet.name, timestampdiff(year,birth,date) as age, remark from pet inner join event on pet.name = event.name where event.type = ‘litter’; 内嵌表格，计算age数据并通过event.type筛选

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 @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price; 自定义变量查询最大最小