1.简单使用
[root@mysql ~]# mysql -uroot -p123 < hellodb_innodb.sql 所有命令都是在hellodb库中执行，此库可以在MySQL官网下载
进入库 [root@mysql ~]# mysql -u root -p123 查询所有的库 mysql> show databases; 进入库 mysql> use hellodb; 查询库里的所有表 mysql> show tables;
2.检索数据
2.1检索classes表的所有列 mysql> select * from classes;
mysql> select * from classes; +---------+----------------+----------+ | ClassID | Class | NumOfStu | +---------+----------------+----------+ | 1 | Shaolin Pai | 10 | | 2 | Emei Pai | 7 | | 3 | QingCheng Pai | 11 | | 4 | Wudang Pai | 12 | | 5 | Riyue Shenjiao | 31 | | 6 | Lianshan Pai | 27 | | 7 | Ming Jiao | 27 | | 8 | Xiaoyao Pai | 15 | +---------+----------------+----------+ 8 rows in set (0.00 sec)
2.2检索单个列 mysql> select ClassID from classes;
mysql> select ClassID from classes; +---------+ | ClassID | +---------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | +---------+ 8 rows in set (0.00 sec)
2.3 检索多个列 ,检索不同行，使用distinct关键字
mysql> select distinct ClassID from students;
mysql> select distinct ClassID from students; +---------+ | ClassID | +---------+ | 2 | | 1 | | 4 | | 3 | | 5 | | 7 | | 6 | | NULL | +---------+ 8 rows in set (0.00 sec)
3.限制结果，使用limit子句
3.1检索students表ClassID列的前五行； mysql> select ClassID from students limit 5;
mysql> select ClassID from students limit 5; +---------+ | ClassID | +---------+ | 2 | | 1 | | 2 | | 4 | | 3 | +---------+ 5 rows in set (0.00 sec)
3.2检索students表ClassID列，从第五行开始的10行；
mysql> select ClassID from students limit 5,10;
1 mysql> select ClassID from students limit 5,10; 2 +---------+ 3 | ClassID | 4 +---------+ 5 | 5 | 6 | 3 | 7 | 7 | 8 | 6 | 9 | 3 | 10 | 6 | 11 | 1 | 12 | 2 | 13 | 3 | 14 | 4 | 15 +---------+ 16 10 rows in set (0.00 sec)
3.3使用完全限定的表名
mysql> select students.ClassID from students;
mysql> select students.ClassID from students; +---------+ | ClassID | +---------+ | 2 | | 1 | | 2 | | 4 | | 3 | | 5 | | 3 | | 7 | | 6 | | 3 | | 6 | | 1 | | 2 | | 3 | | 4 | | 1 | | 4 | | 7 | | 6 | | 7 | | 6 | | 1 | | 4 | | NULL | | NULL | +---------+ 25 rows in set (0.00 sec)
3.3.1表名完全限定
mysql> select students.ClassID from hellodb.students;
mysql> select students.ClassID from hellodb.students; +---------+ | ClassID | +---------+ | 2 | | 1 | | 2 | | 4 | | 3 | | 5 | | 3 | | 7 | | 6 | | 3 | | 6 | | 1 | | 2 | | 3 | | 4 | | 1 | | 4 | | 7 | | 6 | | 7 | | 6 | | 1 | | 4 | | NULL | | NULL | +---------+ 25 rows in set (0.00 sec)
4.排序检索数据
order by子句,取一个列或多个列，据此对输出进行排序 4.1单列排序 mysql> select ClassID from students order by ClassID;
mysql> select ClassID from students order by ClassID; +---------+ | ClassID | +---------+ | NULL | | NULL | | 1 | | 1 | | 1 | | 1 | | 2 | | 2 | | 2 | | 3 | | 3 | | 3 | | 3 | | 4 | | 4 | | 4 | | 4 | | 5 | | 6 | | 6 | | 6 | | 6 | | 7 | | 7 | | 7 | +---------+ 25 rows in set (0.00 sec)
4.2多列排序
mysql> select Name,ClassID from students order by Name,ClassID;
mysql> select Name,ClassID from students order by Name,ClassID; +---------------+---------+ | Name | ClassID | +---------------+---------+ | Diao Chan | 7 | | Ding Dian | 4 | | Duan Yu | 4 | | Hua Rong | 7 | | Huang Yueying | 6 | | Lin Chong | 4 | | Lin Daiyu | 7 | | Lu Wushuang | 3 | | Ma Chao | 4 | | Ren Yingying | 6 | | Shi Potian | 1 | | Shi Qing | 5 | | Shi Zhongyu | 2 | | Sun Dasheng | NULL | | Tian Boguang | 2 | | Wen Qingqing | 1 | | Xi Ren | 3 | | Xiao Qiao | 1 | | Xie Yanke | 2 | | Xu Xian | NULL | | Xu Zhu | 1 | | Xue Baochai | 6 | | Yu Yutong | 3 | | Yuan Chengzhi | 6 | | Yue Lingshan | 3 | +---------------+---------+ 25 rows in set (0.00 sec)
4.3指定排序方向
降序 desc mysql> select ClassID from students order by ClassID desc;
mysql> select ClassID from students order by ClassID desc; +---------+ | ClassID | +---------+ | 7 | | 7 | | 7 | | 6 | | 6 | | 6 | | 6 | | 5 | | 4 | | 4 | | 4 | | 4 | | 3 | | 3 | | 3 | | 3 | | 2 | | 2 | | 2 | | 1 | | 1 | | 1 | | 1 | | NULL | | NULL | +---------+ 25 rows in set (0.00 sec)
升序asc mysql> select ClassID from students order by ClassID asc;
mysql> select ClassID from students order by ClassID asc; +---------+ | ClassID | +---------+ | NULL | | NULL | | 1 | | 1 | | 1 | | 1 | | 2 | | 2 | | 2 | | 3 | | 3 | | 3 | | 3 | | 4 | | 4 | | 4 | | 4 | | 5 | | 6 | | 6 | | 6 | | 6 | | 7 | | 7 | | 7 | +---------+ 25 rows in set (0.00 sec)
4.4列出classid最大的值
mysql> select Age,ClassID from students order by ClassID desc limit 1;
mysql> select Age,ClassID from students order by ClassID desc limit 1; +-----+---------+ | Age | ClassID | +-----+---------+ | 17 | 7 | +-----+---------+ 1 row in set (0.00 sec)
5.过滤数据 where
where 5.1列出classid的值为7的行 mysql> select Name,ClassID from students where ClassID = 7;
mysql> select Name,ClassID from students where ClassID = 7; +-----------+---------+ | Name | ClassID | +-----------+---------+ | Lin Daiyu | 7 | | Hua Rong | 7 | | Diao Chan | 7 | +-----------+---------+ 3 rows in set (0.00 sec)
where子句操作符
= 等于 <> 不等于 < 小于 >= 大于等于 between 在指定两个值之间 5.2检查单个值 mysql> select Name,ClassID from students where Name = 'lin daiyu'; 不区分大小写
mysql> select Name,ClassID from students where Name = 'lin daiyu'; +-----------+---------+ | Name | ClassID | +-----------+---------+ | Lin Daiyu | 7 | +-----------+---------+ 1 row in set (0.00 sec)
mysql> select Name,ClassID from students where ClassID >= 3;
mysql> select Name,ClassID from students where ClassID >= 3; +---------------+---------+ | Name | ClassID | +---------------+---------+ | Ding Dian | 4 | | Yu Yutong | 3 | | Shi Qing | 5 | | Xi Ren | 3 | | Lin Daiyu | 7 | | Ren Yingying | 6 | | Yue Lingshan | 3 | | Yuan Chengzhi | 6 | | Lu Wushuang | 3 | | Duan Yu | 4 | | Lin Chong | 4 | | Hua Rong | 7 | | Xue Baochai | 6 | | Diao Chan | 7 | | Huang Yueying | 6 | | Ma Chao | 4 | +---------------+---------+ 16 rows in set (0.00 sec)
5.3不匹配检查 mysql> select Name,ClassID from students where ClassID <> 7;
mysql> select Name,ClassID from students where ClassID <> 7; +---------------+---------+ | Name | ClassID | +---------------+---------+ | Shi Zhongyu | 2 | | Shi Potian | 1 | | Xie Yanke | 2 | | Ding Dian | 4 | | Yu Yutong | 3 | | Shi Qing | 5 | | Xi Ren | 3 | | Ren Yingying | 6 | | Yue Lingshan | 3 | | Yuan Chengzhi | 6 | | Wen Qingqing | 1 | | Tian Boguang | 2 | | Lu Wushuang | 3 | | Duan Yu | 4 | | Xu Zhu | 1 | | Lin Chong | 4 | | Xue Baochai | 6 | | Huang Yueying | 6 | | Xiao Qiao | 1 | | Ma Chao | 4 | +---------------+---------+ 20 rows in set (0.00 sec)
5.4范围检查
mysql> select Name,ClassID from students where ClassID between 3 and 7;
mysql> select Name,ClassID from students where ClassID between 3 and 7; +---------------+---------+ | Name | ClassID | +---------------+---------+ | Ding Dian | 4 | | Yu Yutong | 3 | | Shi Qing | 5 | | Xi Ren | 3 | | Lin Daiyu | 7 | | Ren Yingying | 6 | | Yue Lingshan | 3 | | Yuan Chengzhi | 6 | | Lu Wushuang | 3 | | Duan Yu | 4 | | Lin Chong | 4 | | Hua Rong | 7 | | Xue Baochai | 6 | | Diao Chan | 7 | | Huang Yueying | 6 | | Ma Chao | 4 | +---------------+---------+ 16 rows in set (0.00 sec)
5.5空值检查 mysql> select ClassID from students where ClassID is null;
mysql> select ClassID from students where ClassID is null; +---------+ | ClassID | +---------+ | NULL | | NULL | +---------+ 2 rows in set (0.01 sec)
6.数据过滤 组合where子句
6.1and操作符 显示满足所有条件的行 mysql> select ClassID,Age from students where ClassID = 7 and Age >= 19;
mysql> select ClassID,Age from students where ClassID = 7 and Age >= 19; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 23 | | 7 | 19 | +---------+-----+ 2 rows in set (0.01 sec)
6.2or操作符 显示任一条件的行
mysql> select ClassID,Age from students where ClassID = 7 or Age >= 25;
mysql> select ClassID,Age from students where ClassID = 7 or Age >= 25; +---------+-----+ | ClassID | Age | +---------+-----+ | 2 | 53 | | 4 | 32 | | 3 | 26 | | 5 | 46 | | 7 | 17 | | 2 | 33 | | 4 | 25 | | 7 | 23 | | 7 | 19 | | NULL | 27 | | NULL | 100 | +---------+-----+ 11 rows in set (0.00 sec)
6.3计算次序 mysql> select ClassID,Age from students where ClassID = 7 or Age >= 25 and Gender = 'F';
mysql> select ClassID,Age from students where ClassID = 7 or Age >= 25 and Gender = 'F'; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 17 | | 7 | 23 | | 7 | 19 | +---------+-----+ 3 rows in set (0.00 sec)
mysql> select ClassID,Age from students where (ClassID = 7 or Age >= 25) and Gender = 'F';
mysql> select ClassID,Age from students where (ClassID = 7 or Age >= 25) and Gender = 'F'; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 17 | | 7 | 19 | +---------+-----+ 2 rows in set (0.00 sec)
6.4in操作符 指定条件范围 mysql> select ClassID,Age from students where ClassID in (1,7) order by Age;
mysql> select ClassID,Age from students where ClassID in (1,7) order by Age; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 17 | | 1 | 19 | | 7 | 19 | | 1 | 20 | | 1 | 21 | | 1 | 22 | | 7 | 23 | +---------+-----+ 7 rows in set (0.00 sec)
mysql> select ClassID,Age from students where ClassID in (1,7) and Gender = 'F';
mysql> select ClassID,Age from students where ClassID in (1,7) and Gender = 'F'; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 17 | | 1 | 19 | | 7 | 19 | | 1 | 20 | +---------+-----+ 4 rows in set (0.00 sec)
mysql> select ClassID,Age from students where ClassID = 1 OR ClassID = 7 order by Age;
mysql> select ClassID,Age from students where ClassID = 1 OR ClassID = 7 order by Age; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 17 | | 1 | 19 | | 7 | 19 | | 1 | 20 | | 1 | 21 | | 1 | 22 | | 7 | 23 | +---------+-----+ 7 rows in set (0.00 sec)
6.5NOT操作符 否定后跟条件 mysql> select ClassID,Age from students where ClassID not in (1,7) order by Age;
mysql> select ClassID,Age from students where ClassID not in (1,7) order by Age; +---------+-----+ | ClassID | Age | +---------+-----+ | 3 | 17 | | 6 | 18 | | 3 | 19 | | 3 | 19 | | 4 | 19 | | 6 | 20 | | 2 | 22 | | 6 | 22 | | 6 | 23 | | 4 | 23 | | 4 | 25 | | 3 | 26 | | 4 | 32 | | 2 | 33 | | 5 | 46 | | 2 | 53 | +---------+-----+ 16 rows in set (0.01 sec)
7.用通配符进行过滤 like操作符
like操作符 区分大小写
7.1%通配符 %表示任意 匹配多个字符 以S开头 mysql> select ClassID,Age,Name from students where Name like 'S%';
mysql> select ClassID,Age,Name from students where Name like 'S%'; +---------+-----+-------------+ | ClassID | Age | Name | +---------+-----+-------------+ | 2 | 22 | Shi Zhongyu | | 1 | 22 | Shi Potian | | 5 | 46 | Shi Qing | | NULL | 100 | Sun Dasheng | +---------+-----+-------------+ 4 rows in set (0.00 sec)
包含ong
mysql> select ClassID,Age,Name from students where Name like '%ong%';
mysql> select ClassID,Age,Name from students where Name like '%ong%'; +---------+-----+-------------+ | ClassID | Age | Name | +---------+-----+-------------+ | 2 | 22 | Shi Zhongyu | | 3 | 26 | Yu Yutong | | 4 | 25 | Lin Chong | | 7 | 23 | Hua Rong | +---------+-----+-------------+ 4 rows in set (0.00 sec)
mysql> select ClassID,Age,Name from students where Name like 'S%g';
mysql> select ClassID,Age,Name from students where Name like 'S%g'; +---------+-----+-------------+ | ClassID | Age | Name | +---------+-----+-------------+ | 5 | 46 | Shi Qing | | NULL | 100 | Sun Dasheng | +---------+-----+-------------+ 2 rows in set (0.00 sec)
7.2_下划线通配符 只匹配单个字符 需要1 anc zzz 查找时为like '_ anc zzz' 格式
8.用正则表达式搜索
不区分大小写 8.1基本字符匹配 regexp 后的作为正则表达式 mysql> select Age from students where Age regexp '100' order by Age;
mysql> select Age from students where Age regexp '100' order by Age; +-----+ | Age | +-----+ | 100 | +-----+ 1 row in set (0.00 sec)
mysql> select Age from students where Age regexp '.2' order by Age;
mysql> select Age from students where Age regexp '.2' order by Age; +-----+ | Age | +-----+ | 22 | | 22 | | 22 | | 32 | +-----+ 4 rows in set (0.00 sec)
. 匹配任意一个字符
8.2 进行or匹配 使用 | mysql> select Age from students where Age regexp '19|22' order by Age;
mysql> select Age from students where Age regexp '19|22' order by Age; +-----+ | Age | +-----+ | 19 | | 19 | | 19 | | 19 | | 19 | | 22 | | 22 | | 22 | +-----+ 8 rows in set (0.00 sec)
8.3匹配几个字符之一 mysql> select Age from students where Age regexp '[123]' order by Age;
mysql> select Age from students where Age regexp '[123]' order by Age; +-----+ | Age | +-----+ | 17 | | 17 | | 18 | | 19 | | 19 | | 19 | | 19 | | 19 | | 20 | | 20 | | 21 | | 22 | | 22 | | 22 | | 23 | | 23 | | 23 | | 25 | | 26 | | 27 | | 32 | | 33 | | 53 | | 100 | +-----+ 24 rows in set (0.00 sec)
[123]定义一组字符，匹配1或2或3
8.4匹配范围