mysql笔记2 - 1.逻辑运算符补充 2.update 3.Delete 4.like 5.limit 6.order by 7.group by 8.having 9.dis

2017/10/31 - 数据库编程 - day2


回顾练习：
1.关闭服务器
2.开启服务器
3.创建一个数据库myDB,并在库中创建myexcel,表中的字段分别有name varchar(20),phnumber int,height int,liking varchar(25)
4.一次在表中插入4条数据，height的值分别为80，90，100，110，查看表中的内容
5.利用myexcel创建一个新表new_myexcel，新表中包含name和phnumber两列，新表中内容为原表中height<100的，查看新表中的内容
6.删除数据库myDB


1.2.
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql stsrt
3.
sudo mysql -u root -p123456
create database myDB;
use myDB;
4.
create table myexcel(name varchar(20),phnumber int,height int,liking varchar(25));


insert into myexcel(name,phnumber,height,liking) values ('xiao',1,80,'ft'),('yu',2,90,'vo'),('ni',3,100,'piano'),('hao',4,110,'gu');
5.
create table new_myexcel(name varchar(20),phnumber int,height int,liking varchar(25));


insert into new_myexcel(name,phnumber,height,liking) select name,phnumber from excel where where height < 100;


select * from new_myexcel; 


drop database myDB;

========================================================================
1.逻辑运算符补充  2.update  3.Delete  4.like  5.limit  6.order by   
7.group by  8.having  9.distinct

========================================================================

1.逻辑运算符补充
between的用法


select column1,column2,...columnN from 表名 where columnX 第一个值 and 第二个值；


实例：
select name,age from customers where age between 26 and 30;




2.SQL中逻辑操作符的优先级
级别       操作符
1            ~(Bitwise NOT)
2            * / % 取模
3            + - &(bitwise AND),^(bitwise Exclusive OR),|(bitwise OR)
4            =，>,>=,<,<=,<>,!=,!<,!> 比较操作符
5            NOT
6            AND
7            ALL,ANY,BETWEEN,IN,LIKE,OR,SOME
8            = 赋值
注：sql中可以使用圆括号（）来控制优先级






练习：
从customers表中，选取年龄大于24岁，工资在4000到9000元 之间的人，列出这些人的姓名，年龄，工资。




-------------------------------------------------------------------------
总结：
以下两句作用效果相同，得到 not age < 24  等同于 age > 24
select name,age,salary from customers where address = 'beijing' or salary > 4000 and not age < 24;


select id,name,age,salary from customers where address = 'beijing' or salary > 4000 and age >= 24;
-------------------------------------------------------------------------
总会：
以下两句作用效果相同，得到 去除not后，后面所有的运算符都要进行取反：
select id,name,age,salary from customers where not (address = 'beijing' or salary > 4000 and age >= 24);


select id,name,age,salary from customers where address <> 'beijing' and  salary <= 4000 or  age < 24;






2.update 更改


update 表名 set column1 = value,column2 = value2,...,columnN = valueN where 条件；


实例：
update customers set age = 25 where name= 'xiaohu';
注：没有where条件语句时，表示将表中所有的行的 age 改为25。


思考：
将表customers中年龄>=25,且地址是北京或上海，这样的人的工资上调15%
提示：update，and，or，where
update customers set salary = salary*1.15 where age >=25 and (address = 'beijing' or address = 'shanghai');






3.Delete删除操作4.like语句5.limit 限制6.order by 排序7.group by8.having语句9.distinct 不同的


delete from 表名 where 条件；


实例：
delete from customers where id =1;
insert into customers value (1,'Ann',22,'zhuhai',30000);
注意：delete from customers; 不写where语句，结果是把表中所有的记录全部删除


4.like语句
通配符%：0，1，或更多
通配符_：单一数字或单一字符


select * from 表名 where 列名 like 'XXXX%'；
select * from 表名 where 列名 like '_XXXX_'；


+--------------------------------------------------------+
思考：
select * from customers where salary like '_5__.__';
select * from customers where salary like '3%';
select * from customers where salary like '3000%
select * from customers where address like '%zh__';


mysql> select * from customers where salary like '_5__';
Empty set (0.00 sec)


mysql> select * from customers where salary like '3%';
+------+------+------+---------+----------+
| ID   | NAME | AGE  | ADDRESS | SALARY   |
+------+------+------+---------+----------+
|    1 | Ann  |   22 | zhuhai  | 30000.00 |
+------+------+------+---------+----------+
1 row in set (0.00 sec)


mysql> select * from customers where salary like '_5__.__';
+------+------+------+---------+---------+
| ID   | NAME | AGE  | ADDRESS | SALARY  |
+------+------+------+---------+---------+
|    4 | Bob  |   25 | beijing | 8596.25 |
|    5 | aaa  |   25 | beijing | 8596.25 |
|    6 | bbb  |   25 | beijing | 8596.25 |
|    7 | ccc  |   27 | beijing | 8596.25 |
+------+------+------+---------+---------+
4 rows in set (0.00 sec)


mysql> select * from customers where address like '%zh__';
+------+--------+------+-----------+----------+
| ID   | NAME   | AGE  | ADDRESS   | SALARY   |
+------+--------+------+-----------+----------+
|    8 | xioayu |   24 | shenzhen  | 60000.00 |
|    9 | xiaohu |   25 | guangzhou | 60000.00 |
+------+--------+------+-----------+----------+
2 rows in set (0.00 sec)


+--------------------------------------------------------+




5.limit 限制
select * from 表名 limit n;
其中，n指返回表中前n条记录


select * from 表名 limit m,n;
其中，m指记录开始的索引号，第一条代码记录是0，n指从第m条记录开始，取n条


select * from customers limit 3;
select * from customers limit 3,2;


mysql> select * from customers limit 3;
+------+--------+------+----------+---------+
| ID   | NAME   | AGE  | ADDRESS  | SALARY  |
+------+--------+------+----------+---------+
|    2 | li     |   30 | beijing  | 2645.00 |
|    3 | jeremy |   20 | shanghai | 2300.00 |
|    4 | Bob    |   25 | beijing  | 8596.25 |
+------+--------+------+----------+---------+
3 rows in set (0.00 sec)
mysql> select * from customers limit 3,2;
+------+------+------+---------+---------+
| ID   | NAME | AGE  | ADDRESS | SALARY  |
+------+------+------+---------+---------+
|    5 | aaa  |   25 | beijing | 8596.25 |
|    6 | bbb  |   25 | beijing | 8596.25 |
+------+------+------+---------+---------+
2 rows in set (0.00 sec)


思考：
返回customers表中，工资大于4000元，或者年龄小于29岁，满足这样条件的前2条语句记录
提示：limit子句永远放在最后面
select * from customers where salary > 4000 or age < 29 limit 2;


mysql> select * from customers where salary > 4000 or age < 29 limit 2;
+------+--------+------+----------+---------+
| ID   | NAME   | AGE  | ADDRESS  | SALARY  |
+------+--------+------+----------+---------+
|    3 | jeremy |   20 | shanghai | 2300.00 |
|    4 | Bob    |   25 | beijing  | 8596.25 |
+------+--------+------+----------+---------+
2 rows in set (0.00 sec)




6.order by 排序
select 列名 from 表名 where 条件 order by 列名 ASC;
说明：ASC升序   DESC降序
注意：order by后面出现的列名，不必出现在select语句后面


实例：
mysql> select name,age from customers order by age ASC;
+--------+------+
| name   | age  |
+--------+------+
| jeremy |   20 |
| Ann    |   22 |
| xioayu |   24 |
| Bob    |   25 |
| aaa    |   25 |
| bbb    |   25 |
| xiaohu |   25 |
| ccc    |   27 |
| li     |   30 |
+--------+------+
9 rows in set (0.00 sec)


练习：
将customers表中，城市为北京的顾客，按年级升序，工资降序排列，并且只返回结果中的的第一条记录
select * from customers where address = 'beijing' order by age ASC,salary DESC limit 1;


mysql> select * from customers where address = 'beijing' order by age ASC,salary DESC limit 1;
+------+------+------+---------+---------+
| ID   | NAME | AGE  | ADDRESS | SALARY  |
+------+------+------+---------+---------+
|    4 | Bob  |   25 | beijing | 8596.25 |
+------+------+------+---------+---------+
1 row in set (0.00 sec)


注意：
order by age ASC,salary DESC 两种排序方式一起使用的时候，中间需要添加逗号隔开；
order by age ASC,salary DESC ，先进行age排列，再进行salary排列（仅仅在age相同的时候才开始按薪资排列）




7.group by
group by 用来与聚合函数（count总数，sum求和，avg平均值，min最小值，max最大值）联合使用，得到一个或多个列的结果集。


语法：
select column1,column2,...,columnN,聚合函数（表达式） from 表名 where 条件
group by column1，column2，...,columbN
order by column1,column1,...,columnN;


注意：
1.group by 之后的列必须出现在select语句之中，
2.group by语句必须在where语句之后，order by语句之前：where 》group by 》order by


实例：
select address,max(salary) from customers group by address;
mysql> select address,max(salary) from customers group by address;
+-----------+-------------+
| address   | max(salary) |
+-----------+-------------+
| beijing   |     8596.25 |
| guangzhou |    60000.00 |
| shanghai  |     2300.00 |
| shenzhen  |    60000.00 |
| zhuhai    |    30000.00 |
+-----------+-------------+
5 rows in set (0.00 sec)




mysql> select max(salary) from customers group by address;
+-------------+
| max(salary) |
+-------------+
|     8596.25 |
|    60000.00 |
|     2300.00 |
|    60000.00 |
|    30000.00 |
+-------------+
5 rows in set (0.00 sec)




练习：
列出各个城市的平均工资，按照平均工资由小到大排序，并且只返回前三条记录


错解：
select address,salary from customers group by avg(salary) order by avg(salary) desc limit 3;


正解：#@@@@@@@@@@@@@@@@@@@@@@@
select address,avg(salary) from customers group by salary order by avg(salary) desc limit 3;


mysql> select address,avg(salary) from customers group by address order by avg(salary);
+-----------+--------------+
| address   | avg(salary)  |
+-----------+--------------+
| shanghai  |  2300.000000 |
| beijing   |  6505.000000 |
| zhuhai    | 30000.000000 |
| shenzhen  | 60000.000000 |
| guangzhou | 60000.000000 |
+-----------+--------------+
5 rows in set (0.00 sec)




mysql> select address,avg(salary) from customers group by address order by avg(salary) limit 3;
+----------+--------------+
| address  | avg(salary)  |
+----------+--------------+
| shanghai |  2300.000000 |
| beijing  |  6505.000000 |
| zhuhai   | 30000.000000 |
+----------+--------------+
3 rows in set (0.00 sec)


mysql> select address,avg(salary) from customers group by address order by avg(salary) desc limit 3;
+-----------+--------------+
| address   | avg(salary)  |
+-----------+--------------+
| shenzhen  | 60000.000000 |
| guangzhou | 60000.000000 |
| zhuhai    | 30000.000000 |
+-----------+--------------+
3 rows in set (0.00 sec)


注意：
因为聚合函数通过作用与一组数据而只返回一个单个值，因此，在select语句中出现的元素要么为一个聚合函数的输入值，要么为group by语句，否则会出错。
例如以下语句会报错:
select address avg(salary),name from customers group by address;
显示的是平均工资，其数据与name没有一一映射的关系，无法显示而导致报错




8.having语句
where 语句后不能直接跟聚合函数，如下例子是错误的：
select address avg(salary) from customers where avg(salary) > 4000 group by address order by avg(salary);


having语句：
(1)having语句通过常与group by语句联合使用，用来过滤由group by语句返回的记录集合；
(2)having语句的存在弥补了where关键字不能与聚合函数联合使用的不足.


having语法如下：


select column1,column,...,columnN,聚合函数（表达式）from customers where 条件 group by column1,column2,...,columnN having 条件1，条件2，...,条件N;




实例：
select address,avg(salary) from customers group by address having avg(salary) > 4000;


mysql> select address,avg(salary) from customers group by address having avg(salary) > 4000;
+-----------+--------------+
| address   | avg(salary)  |
+-----------+--------------+
| beijing   |  6505.000000 |
| guangzhou | 60000.000000 |
| shenzhen  | 60000.000000 |
| zhuhai    | 30000.000000 |
+-----------+--------------+
4 rows in set (0.00 sec)




练习：
除了北京以外的其他城市，年龄大于25岁的顾客的最高收入，并且该最高收入不得低于3000,将这些信息按照最高工资的升序排列
提示：select...max()from...where...group by...having...order by...limit


错1：
select name,max(salary) from customers group by salary order by salary having max(salary) >= 3000 and age >25 and address != 'beijing';


错2
select address,max(salary) from customers where address != 'beijing' and age > 25 group by address order by max(salary) having max(salary) >= 3000;


正解：
mysql> select address,max(salary) from customers where address != 'beijing' and age > 10 group by address having max(salary) >= 3000 order by max(salary);
+-----------+-------------+
| address   | max(salary) |
+-----------+-------------+
| zhuhai    |    30000.00 |
| shenzhen  |    60000.00 |
| guangzhou |    60000.00 |
+-----------+-------------+
分析：
select 的条件是where ，聚合函数的条件语句是having




9.distinct 不同的
与select一起使用，除去重复项，提取唯一的记录项目


select distinct column1,column2,...,columnN from 表名 where 条件;


实例：
mysql> select distinct age from customers;
+------+
| age  |
+------+
|   30 |
|   20 |
|   25 |
|   27 |
|   24 |
|   22 |
+------+
6 rows in set (0.00 sec)


mysql> select count(distinct age) from customers;
+---------------------+
| count(distinct age) |
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.00 sec)


mysql> select avg(distinct age) from customers;
+-------------------+
| avg(distinct age) |
+-------------------+
|           24.6667 |
+-------------------+
1 row in set (0.00 sec)


mysql> select distinct address from customers;
+-----------+
| address   |
+-----------+
| beijing   |
| shanghai  |
| shenzhen  |
| guangzhou |
| zhuhai    |
+-----------+
5 rows in set (0.00 sec)


mysql> select count(distinct address) from customers;
+-------------------------+
| count(distinct address) |
+-------------------------+
|                       5 |
+-------------------------+
1 row in set (0.00 sec)




========================================================================数据库的设计


步骤:
1.分析需求：解决问题
2.概念设计，逻辑设计，物理设计：组织结构
3.数据库的实现与维护


建立概念模型:实体关系模型（entity-relationship model）
1.确定实体集合
2.选择实体集应包含的属性
3.实体集之间的关系
4.确定联系的类型


实体entity：用信息描述的对象，对应一个表，例如一个人，一本书，例如：
课程(课程号，名，学分)
学生(姓名，年龄，住址，成绩)


概念设计：
实体之间的关系：
1:1 一对一   例如：丈夫-夫人
1:N 一对多   例如：老师-课
N:N 所对所   例如：学生-课


逻辑设计、物理设计：
指明数据类型；
指明主键；
指明外键；
指明约束（非空集，有效性检查，键约束：主键，外键，惟一键）




键：索引，
主键：唯一标识表中的记录（例如身份证号，主键可以有一个属性决定，也可以有多个属性共同决定）


=====================================================================


除了北京以外的其他城市，年龄大于25岁的顾客的最高收入，并且该最高收入不得低于3000,将这些信息按照最高工资的升序排列
提示：
select...max(...)from...where...
group by...having...order by...limit


正解：
select address,max(salary) from customers where address != 'beijing' and age > 10 group by address having max(salary) >= 3000 order by max(salary);






练习：


除了li之外，位于北京或珠海的不同年龄段朋友的的平均工资，并且平均工资不得低于2000，将这些信息按照平均工资的降序排列,去从第1个开始后面的3个记录。


解：
mysql> select address,avg(salary) from customers where name != 'li' and address = 'beijing' or 'zhuhai' group by address having avg(salary)>=2000 order by avg(salary) desc limit 0,3;
+---------+-------------+
| address | avg(salary) |
+---------+-------------+
| beijing | 7277.000000 |
+---------+-------------+
1 row in set, 1 warning (0.00 sec)


总结：
where条件语句是对select中内容进行筛选显示；
having条件语句是对分组后的新数据，根据聚合函数的返回属性进行筛选；
group by...分组依据有很多
having...order by... 这两个条件语句针对的是聚合函数