curd操作查询select

-- 此语句再公司的真实项目中是不允许使用的
select * from books;

-- 推荐使用如下limit 限制结果集行数，用于分页查询
select * from books limit 10;


select date_format(now(),'%y年%m月%d日');

-- 列别名
select id 编号,
author as '作者',
name 书籍名称,
price 原价,
round(price *.9,1) 九折价格,
date_format(pubtime,'%Y年%m月%d日') 出版日期
from books;

select id 编号,author 作者,name 书籍名称 from books;

-- 查询条件 > < != <> >= = <= not取反 1为true true 0为false
select * from books where 1; -- 查询所有
select * from books where false;
select * from books where 0;
select * from books where not true;

select * from books where author='李强';
select * from books where price <= 80 and author = '张强';
select * from books where author != '张强';
select * from books where author <> '张强';


-- 查询条件 null、is null、is not null
select * from books where pubtime = null;
select * from books where pubtime is null;
select * from books where not pubtime is null;
select * from books where pubtime is not null; -- 建议使用


-- 删除作者信息为null
delete from books where author is null;

-- in()  not in()
select * from books where id in(100,105);
select * from books where id not in(100,105);


-- between and  not between and
select * from books where pubtime between '2020-1-1' and '2020-12-30';
select * from books where pubtime not between '2020-1-1' and '2020-12-30';


-- 模糊查询 like  not like  下划线代表一个符号 %代表0个或多个符号
select * from books where author like '张%';
select * from books where author not like '张%';
select * from books where author like '_三_';


select * from books where name like '%项目%';


-- 正则表达式条件
select * from 
select 'java' regexp '.[0-9]+.';

-- 查询书籍信息作者名字有英文字母的
select * from books where author regexp '.*[a-zA-Z]+.*';


-- 查询结果消除重复，并显示  distinct消除重复
select distinct author from books;  

truncate books;

select * from books;

-- 将查询的文件插入到表中
insert books select * from book;

use dbshop;

select * into outfile 'f:/dbs/b.txt' from books where author like '李%';

存入文件失败需要在mysql文件夹中添加secure_file_priv=''

my.ini配置文件

# 'c:/tmp' ''任何目录都可以加载文件 导出文件
secure_file_priv=''