mysql常见操作举例总结之查询

一、 创建自增长表

create table TableName(
num int primary key auto_increment,
book_name char(30)
)
auto_increment=5 //设置从5开始，即第一个数5，后面依次6，7
;
");
insert into booktemp (book_name)values(“book”);
insert into booktemp (book_name)values(“book”);
insert into booktemp (book_name)values(“book”);
alter table booktemp modify num int;//删除自动编号
alter table booktemp modify num int primary key auto_increment;
//增加自动编号

update booktemp set book_name=“rename” where num = 6;//更新编号为6行 的bookname列的名字

delete from booktemp where num =5;//删除指定行数据
truncate booktemp;//删除整个表

四、基本检索

select * from booktemp;//显示所有列

select book_name from booktemp;//显示指定列

select distinct book_name from booktemp;//显示不重复的值

select book_name from booktemp limit 3;//限制显示3行

select book_name from booktemp limit 2,2;//选择从第二行开始的两行

五、选择结果排序

select book_name from booktemp order by book_name ;//按开头字母排序

select mid from booktemp order by mid ;//按照数据升序排列

select mid from booktemp order by mid desc ;//按照降序排列
ASC是升序

select mid ,book_name from booktemp order by book_name, mid ;//多列排序

*

六，数据过滤Where

select mid ,book_name from booktemp where mid<8 ;//选择小于8的

select mid ,book_name from booktemp where book_name<>“book” ;//选择排除名字是“book”的

select mid ,book_name from booktemp where mid between 5 and 10 ;//范围选择

select mid ,book_name from booktemp where book_name is null ;//选择空值

七、组合数据过滤Where

select mid ,num,book_name from booktemp where mid = 3 and num > 7 ;//AND操作

select mid ,num,book_name from booktemp where mid = 3 or num > 8 order by num;//OR操作

**~~

注意：AND和OR同时使用时候，AND的优先级高

~~ **
select mid ,book_name from booktemp where book_name in (“book”,“sook”) ;//IN操作

select mid ,book_name from booktemp where book_name not in (“book”,“sook”) ;//NOT操作

八、模糊查找

select mid ,book_name from booktemp where book_name like ‘%ok’ ;//模糊开头

select mid ,book_name from booktemp where book_name like ‘%oo%’ ;//模糊头尾

九、正则表达式查找

select mid ,book_name from booktemp where book_name regexp ‘ok’ ;//查找包含“ok”字段的// regexp

select mid ,book_name from booktemp where book_name regexp ‘b.’ ;//用.代表任意字符=eg:查找b开头的

select mid ,book_name from booktemp where book_name regexp ‘c.|s.’ ;//OR查找

select mid ,book_name from booktemp where book_name regexp ‘[b c s]ook’ ;//包含查找，开头是bcs中的一个

select mid ,book_name from booktemp where book_name regexp ‘\.’ ;//查找符号

匹配字符类
[:alnum:], [:alpha:], [:upper:], [:lower:], [:digit:]
[:alnum:]同[a-zA-Z0-9]

select mid ,book_name from booktemp where book_name regexp ‘[:digit:]’ ;//查询包含数字的

查找一个或多个重复

字符	说明
*	一个或多个
{m，n}	m-n范围内n<255
+	一个或多个 同{1，}
{n}	n个
？	0个或1个 同{0，1}

例子
select mid ,book_name from booktemp where book_name regexp ‘book?’ ;//查找book包好或者不包含s的

select mid ,book_name from booktemp where book_name regexp ‘[o]{2}’ ;//查找包含两个o的

定位

字符	说明
^	字段的开头
$	字段的结尾
[[:<:]]	词的开头
[[:>:]]	词的结尾

select mid ,book_name from booktemp where book_name regexp ‘¹’ ;//查找数字开头

---

1. 0-9 ↩︎