LIKE :使用“_”匹配任何单个字符,而“%”匹配任意数目字符(包括零字符)。
select * from pet where name like 'B____';
select * from pet where name like 'B%';
REGEXP 或者 NOT REGEXP 匹配正则表达式
‘.’匹配任何单个的字符。
SELECT * FROM pet WHERE name REGEXP '.....';
SELECT * FROM pet WHERE name REGEXP '..A..';
“[...]”匹配在方括号内的任何字符
SELECT * FROM pet WHERE name REGEXP '[A-Z]{8}';
“ * ”匹配零个或多个在它前面的字符
SELECT * FROM pet WHERE name REGEXP '[*B]';
开始处使用“^”或在模式的结尾用“$”
SELECT * FROM pet WHERE name REGEXP '^bu';
SELECT * FROM pet WHERE name REGEXP 'py#39;;
SELECT * FROM pet WHERE name NOT REGEXP 'py#39;;
区别
REGEXP模式与被测试值的任何地方匹配,模式就匹配
LIKE模式匹配,只有与整个值匹配,模式才匹配
根据品种和性别分组
select owner,count(*) from pet group by name;
select species,sex,count(*) from pet where sex is not null group by species,sex;
每个人分别养了哪些动物
select owner,species,count(*) from pet group by owner,species;
每个人分别养了几只动物
select owner,count(*) from pet group by owner;
多少公的多少母的
select sex,count(*) from pet group by sex;
创建新表
CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
加载数据
event.txt
连接服务器的时候
MYSQL-> --local-infile =1 -h 127.0.0.1 -u root -p
客户端设置
set global local_infile =1
LOAD DATA LOCAL INFILE 'G:/event.txt' INTO TABLE event LINES TERMINATED BY '\r
';
联合事件表查询宠物事件
SELECT PET.NAME, (YEAR(DATE)-YEAR(BIRTH))- (RIGHT(DATE,5)
同种类宠物配型
select p1.name,p1.species,p1.sex,p2.name,p2.sex,p2.species from pet as p1,pet as p2 where p1.species = p2.species and p1.sex <> p2.sex
数据库和表信息
SHOW DATABASE;
SHOW TABLES;
DESCRIBE PET;
批处理使用MYSQL命令
mysql < batch-file ??
source G:/test.sql;
源路径打开
\. G: est.sql
使用路径/.打开文件(加个转义符)
/. G://test.sql
留言与评论(共有 0 条评论) “” |