单表查询
一、单表查询
1、查询语法及限制优先级
#语法 select【distinct】(*|字段|四则运算|聚合函数) from 表名 distinct 去除重复 where 查询条件 group by 分组 having 分组后的过滤 order by 排序 limit 限制获取后的数据 #重点中的重点:关键字的执行优先级 from where group by having select distinct order by limit
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1、找到:from 2、拿着where指定的约束条件,去文件/表中取出一条条的记录 3、将取出来的的一条条记录进行分组group by,如果没有group by,则整体作为一组 4、将分组的结果进行having过滤 5、执行select 6、去重 7、将执行结果按条件排序:order by 8、限制结果的显示条数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table stu(id int primary key auto_increment,name char(10),math float,english float); insert into stu values(null,"赵云",90,30); insert into stu values(null,"小乔",90,60); insert into stu values(null,"小乔",90,60); insert into stu values(null,"大乔",10,70); insert into stu values(null,"李清照",100,100); insert into stu values(null,"铁拐李",20,55); insert into stu values(null,"小李子",20,55);
2、where约束
1、比较运算符:> , < , >= , <= ,<>,!=
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select *from stu; +----+-----------+------+---------+ | id | name | math | english | +----+-----------+------+---------+ | 1 | 赵云 | 90 | 30 | | 2 | 小乔 | 90 | 60 | | 3 | 小乔 | 90 | 60 | | 4 | 大乔 | 10 | 70 | | 5 | 李清照 | 100 | 100 | | 6 | 铁拐李 | 20 | 55 | | 7 | 小李子 | 20 | 55 | +----+-----------+------+---------+ mysql> select *from stu where english = 100; +----+-----------+------+---------+ | id | name | math | english | +----+-----------+------+---------+ | 5 | 李清照 | 100 | 100 | +----+-----------+------+---------+ 1 row in set (0.00 sec) mysql> select *from stu where math >=90; +----+-----------+------+---------+ | id | name | math | english | +----+-----------+------+---------+ | 1 | 赵云 | 90 | 30 | | 2 | 小乔 | 90 | 60 | | 3 | 小乔 | 90 | 60 | | 5 | 李清照 | 100 | 100 | +----+-----------+------+---------+ 4 rows in set (0.00 sec) mysql> select *from stu where 90<=math<=100; +----+-----------+------+---------+ | id | name | math | english | +----+-----------+------+---------+ | 1 | 赵云 | 90 | 30 | | 2 | 小乔 | 90 | 60 | | 3 | 小乔 | 90 | 60 | | 4 | 大乔 | 10 | 70 | | 5 | 李清照 | 100 | 100 | | 6 | 铁拐李 | 20 | 55 | | 7 | 小李子 | 20 | 55 | +----+-----------+------+---------+ 7 rows in set (1.52 sec) mysql> select *from stu where math>=60 and english >= 60; +----+-----------+------+---------+ | id | name | math | english | +----+-----------+------+---------+ | 2 | 小乔 | 90 | 60 | | 3 | 小乔 | 90 | 60 | | 5 | 李清照 | 100 | 100 | +----+-----------+------+---------+
2、between
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select name,math from stu where math between 60 and 90; +--------+------+ | name | math | +--------+------+ | 赵云 | 90 | | 小乔 | 90 | | 小乔 | 90 | +--------+------+ 3 rows in set (0.00 sec) mysql> select name,math from stu where math not between 60 and 90; +-----------+------+ | name | math | +-----------+------+ | 大乔 | 10 | | 李清照 | 100 | | 铁拐李 | 20 | | 小李子 | 20 | +-----------+------+ 4 rows in set (0.00 sec)
3、in
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select id,name,english from stu where english=60 or english=70 or english=100; +----+-----------+---------+ | id | name | english | +----+-----------+---------+ | 2 | 小乔 | 60 | | 3 | 小乔 | 60 | | 4 | 大乔 | 70 | | 5 | 李清照 | 100 | +----+-----------+---------+ 4 rows in set (0.00 sec) mysql> select id,name,english from stu where english in(60,70,100); +----+-----------+---------+ | id | name | english | +----+-----------+---------+ | 2 | 小乔 | 60 | | 3 | 小乔 | 60 | | 4 | 大乔 | 70 | | 5 | 李清照 | 100 | +----+-----------+---------+ 4 rows in set (0.00 sec)
4、like
#like 长得像 模糊匹配 #% 任意个任意字符 #_ 一个任意字符 select *from stu where name like "李%"; 开头带李的 select *from stu where name like "%李%"; 名字带有李的 select *from stu where name like "%李"; 最后一个字是李的
3、group by 分组查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table emp (id int,name char(10),sex char,dept char(10),job char(10),salary double); insert into emp values (1,"刘备","男","市场","总监",5800), (2,"张飞","男","市场","员工",3000), (3,"关羽","男","市场","员工",4000), (4,"孙权","男","行政","总监",6000), (5,"周瑜","男","行政","员工",5000), (6,"小乔","女","行政","员工",4000), (7,"曹操","男","财务","总监",10000), (8,"司马懿","男","财务","员工",6000);
#单独使用GROUP BY关键字分组 SELECT post FROM employee GROUP BY post; 注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数 #GROUP BY关键字和GROUP_CONCAT()函数一起使用 SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组,并查看组内成员名 SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post; #GROUP BY与聚合函数一起使用 select post,count(id) as count from employee group by post;#按照岗位分组,并查看每个组有多少人
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#最简单使用 mysql> select dept from emp group by dept; +--------+ | dept | +--------+ | 市场 | | 行政 | | 财务 | +--------+ ------------------------------- #通过下面两个例子,可以看出,不与group_concat连用,不能查出两个字段 mysql> select dept id from emp group by dept; +--------+ | id | +--------+ | 市场 | | 行政 | | 财务 | +--------+ 3 rows in set (0.00 sec) mysql> select dept sex from emp group by dept; +--------+ | sex | +--------+ | 市场 | | 行政 | | 财务 | +--------+ ------------------------------------- #与group_concat连用 mysql> select dept, group_concat(name) from emp group by dept; +--------+----------------------+ | dept | group_concat(name) | +--------+----------------------+ | 市场 | 刘备,张飞,关羽 | | 行政 | 孙权,周瑜,小乔 | | 财务 | 曹操,司马懿 | +--------+----------------------+ ----------------------------------- #与内置函数连用 mysql> select dept,count(id) from emp group by dept; +--------+-----------+ | dept | count(id) | +--------+-----------+ | 市场 | 3 | | 行政 | 3 | | 财务 | 2 | +--------+-----------+
4、聚合函数
#sum 求和 #avg 平均数 #max 最大值 #min 最小值 #count 计数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select avg(salary) from emp; +-------------+ | avg(salary) | +-------------+ | 5475 | +-------------+ 1 row in set (0.00 sec) mysql> select count(salary) from emp; +---------------+ | count(salary) | +---------------+ | 8 | +---------------+ 1 row in set (0.00 sec)
5、having过滤
#having与where不同之处 #!!!执行的优先级从高到底:where > group by > having #1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
6、查询排序 order by
#order by默认是升序 #单字段 select *from emp order by salary; #多字段(无论 升降序,先按前边的排序,如果有相同的,再按后边的) select *from emp order by salary id; (先按salary,工资相同的再按id) #可以使用desc 来指定为降序 select *from emp order by salary desc; select *from emp order by id desc,salary desc;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select *from emp order by salary; +------+-----------+------+--------+--------+--------+ | id | name | sex | dept | job | salary | +------+-----------+------+--------+--------+--------+ | 2 | 张飞 | 男 | 市场 | 员工 | 3000 | | 3 | 关羽 | 男 | 市场 | 员工 | 4000 | | 6 | 小乔 | 女 | 行政 | 员工 | 4000 | | 5 | 周瑜 | 男 | 行政 | 员工 | 5000 | | 1 | 刘备 | 男 | 市场 | 总监 | 5800 | | 4 | 孙权 | 男 | 行政 | 总监 | 6000 | | 8 | 司马懿 | 男 | 财务 | 员工 | 6000 | | 7 | 曹操 | 男 | 财务 | 总监 | 10000 | +------+-----------+------+--------+--------+--------+ mysql> select *from emp order by salary desc; +------+-----------+------+--------+--------+--------+ | id | name | sex | dept | job | salary | +------+-----------+------+--------+--------+--------+ | 7 | 曹操 | 男 | 财务 | 总监 | 10000 | | 4 | 孙权 | 男 | 行政 | 总监 | 6000 | | 8 | 司马懿 | 男 | 财务 | 员工 | 6000 | | 1 | 刘备 | 男 | 市场 | 总监 | 5800 | | 5 | 周瑜 | 男 | 行政 | 员工 | 5000 | | 3 | 关羽 | 男 | 市场 | 员工 | 4000 | | 6 | 小乔 | 女 | 行政 | 员工 | 4000 | | 2 | 张飞 | 男 | 市场 | 员工 | 3000 | +------+-----------+------+--------+--------+--------+ mysql> select *from emp order by salary desc,id desc; +------+-----------+------+--------+--------+--------+ | id | name | sex | dept | job | salary | +------+-----------+------+--------+--------+--------+ | 7 | 曹操 | 男 | 财务 | 总监 | 10000 | | 8 | 司马懿 | 男 | 财务 | 员工 | 6000 | | 4 | 孙权 | 男 | 行政 | 总监 | 6000 | | 1 | 刘备 | 男 | 市场 | 总监 | 5800 | | 5 | 周瑜 | 男 | 行政 | 员工 | 5000 | | 6 | 小乔 | 女 | 行政 | 员工 | 4000 | | 3 | 关羽 | 男 | 市场 | 员工 | 4000 | | 2 | 张飞 | 男 | 市场 | 员工 | 3000 | +------+-----------+------+--------+--------+--------+ 8 rows in set (0.00 sec)
7、限制查询的记录数 limit
#select *from emp limit a,b; a表示起始位置 b表示获取的条数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select *from emp -> ; +------+-----------+------+--------+--------+--------+ | id | name | sex | dept | job | salary | +------+-----------+------+--------+--------+--------+ | 1 | 刘备 | 男 | 市场 | 总监 | 5800 | | 2 | 张飞 | 男 | 市场 | 员工 | 3000 | | 3 | 关羽 | 男 | 市场 | 员工 | 4000 | | 4 | 孙权 | 男 | 行政 | 总监 | 6000 | | 5 | 周瑜 | 男 | 行政 | 员工 | 5000 | | 6 | 小乔 | 女 | 行政 | 员工 | 4000 | | 7 | 曹操 | 男 | 财务 | 总监 | 10000 | | 8 | 司马懿 | 男 | 财务 | 员工 | 6000 | +------+-----------+------+--------+--------+--------+ 8 rows in set (0.00 sec) -------------------------------- mysql> select *from emp limit 0,3; +------+--------+------+--------+--------+--------+ | id | name | sex | dept | job | salary | +------+--------+------+--------+--------+--------+ | 1 | 刘备 | 男 | 市场 | 总监 | 5800 | | 2 | 张飞 | 男 | 市场 | 员工 | 3000 | | 3 | 关羽 | 男 | 市场 | 员工 | 4000 | +------+--------+------+--------+--------+--------+ 3 rows in set (0.00 sec) mysql> select *from emp limit 3,3; +------+--------+------+--------+--------+--------+ | id | name | sex | dept | job | salary | +------+--------+------+--------+--------+--------+ | 4 | 孙权 | 男 | 行政 | 总监 | 6000 | | 5 | 周瑜 | 男 | 行政 | 员工 | 5000 | | 6 | 小乔 | 女 | 行政 | 员工 | 4000 | +------+--------+------+--------+--------+--------+ 3 rows in set (0.00 sec)
8、正则表达式匹配(也是模糊匹配)
# like 只有 % 和 _ 灵活度没有 regexp高 #语法: select *from emp regexp "表达式"; select *from emp where name regexp ".*ba$";
----------------------------------------------------------------------
用户管理
mysql用户指的是和客户端连接服务器时使用的账户
在一些公司中,很多项目的数据 可能会放在同一个服务器
那就必须要为每一个用户明确其所拥有的权限
通常 到公司之后 都会给你个一个账号的名称和密码 并且 为你制定可以访问哪些数据库和表
对用户这个账号的增删改查,以及权限的增删改查
mysql与权限相关的表
user
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
数据库30分 设计图书管理系统,图书表包含,书名,售价,出版社,页数,作者信息 其中包含作者信息管理,作者表包含,姓名,年龄,性别,国籍信息 1.创建图书数据库 2.创建相关的表 3.添加作者信息 莫言,男,65,中国 尼古拉·奥斯特洛夫斯基,女,65,USA 金庸,男,94,中国 古龙,男,48中国 4.添加 图书信息 提示 先添加图书信息 到图书表 在添加关系到中间表 绝代双骄,68元,500页,(古龙,金庸) 射雕英雄传,198元,800页,(金庸) 钢铁是怎样炼成的,88元,300页, (尼古拉·奥斯特洛夫斯基,古龙) 丰乳肥臀,78元,200页,(莫言)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create database book_db charset utf8; Query OK, 1 row affected (0.16 sec) mysql> use book_db; Database changed mysql> create table author( -> id int primary key auto_increment, -> name varchar(20), -> sex enum("男","女") not null, -> age int, -> nationnality varchar(20)); Query OK, 0 rows affected (2.19 sec) mysql> insert into author value(1,"莫言","男",65,"中国"); Query OK, 1 row affected (1.87 sec) mysql> insert into author value(2,"尼古拉斯",65,"USA"); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into author value(2,"尼古拉斯","女",65,"USA"); Query OK, 1 row affected (1.84 sec) mysql> insert into author value(3,"金庸","男",94,"中国"); Query OK, 1 row affected (0.13 sec) mysql> insert into author value(4,"古龙","男",48,"中国"); Query OK, 1 row affected (0.14 sec) mysql> create table book( -> id int primary key auto_increment, -> book_name varchar(20), -> book_price int, -> num int); Query OK, 0 rows affected (2.04 sec) mysql> mysql> insert into book values( -> 1,"绝代双骄",68,500), -> (2,"射雕英雄传",198,800), -> (3,"钢铁是怎样炼成的",88,300), -> (4,"丰乳肥臀",78,200); mysql> create table book_author( -> id int primary key auto_increment, -> book_id int, -> author_id int -> ); Query OK, 0 rows affected (2.05 sec) mysql> insert into book_author values(null,1,3),(null,1,4),(null,2,3),(null,3,2), -> (null,3,4),(null,4,1); Query OK, 6 rows affected (1.79 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select *from author join book join book_author -> on book_id = book.id and author_id = author.id -> where author.name = "金庸"; mysql> select *from author join book join book_author -> on book_id = book.id and author_id = author.id -> where book.book_name = "绝代双骄"; mysql> delete from book where book_name = "钢铁是怎样炼成的"; Query OK, 1 row affected (1.86 sec) mysql> mysql> mysql> delete from book_author where id = 1; Query OK, 1 row affected (1.87 sec)