一、单表查询
1、查询语法及限制优先级
#语法 select【distinct】（*|字段|四则运算|聚合函数） from 表名 distinct 去除重复 where 查询条件 group by 分组 having 分组后的过滤 order by 排序 limit 限制获取后的数据 #重点中的重点：关键字的执行优先级 from where group by having select distinct order by limit
1、找到：from 2、拿着where指定的约束条件，去文件/表中取出一条条的记录 3、将取出来的的一条条记录进行分组group by，如果没有group by，则整体作为一组 4、将分组的结果进行having过滤 5、执行select 6、去重 7、将执行结果按条件排序：order by 8、限制结果的显示条数
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、比较运算符：> , < , >= , <= ,<>,!=
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
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
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 分组查询
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;#按照岗位分组，并查看每个组有多少人
#最简单使用 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 计数
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;
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表示获取的条数
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
数据库30分 设计图书管理系统,图书表包含,书名,售价,出版社,页数,作者信息 其中包含作者信息管理,作者表包含,姓名,年龄,性别,国籍信息 1.创建图书数据库 2.创建相关的表 3.添加作者信息 莫言,男,65,中国 尼古拉·奥斯特洛夫斯基,女,65,USA 金庸,男,94,中国 古龙,男,48中国 4.添加 图书信息 提示 先添加图书信息 到图书表 在添加关系到中间表 绝代双骄,68元,500页,(古龙,金庸) 射雕英雄传,198元,800页,(金庸) 钢铁是怎样炼成的,88元,300页, (尼古拉·奥斯特洛夫斯基,古龙) 丰乳肥臀,78元,200页,(莫言)
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)