MySQL对数据的操作

先创建表来进行记录数据的操作：

use sh;
create table employee(
   id int primary key,
   name varchar(255),
   age int,
   salary decimal,
   address varchar(255),
   depId int);
create table depat(
   depId int primary key,
   dname varchar(255));
alter table employee 
add constraint fk1_name foreign key (depId)  
    references depat(depId);

1、添加数据（针对字符、日期类型，需要使用''，整型数据直接写数据），有以下几种方法：

1）insert 语句：set 语句

insert into employee set id=101,name='王',age=20,salary=20000,address='成都';

2）insert 语句：values 语句

      a）添加一行的部分数据，按照列名顺序添加，注意非空列

   insert into employee(id,name,age,salary) values(103,'赵',22,15000);

       b）添加所有列的数据，可省略列名，也就必须添加所有列数据

   insert into employee values(105,'周',22,14000,'韩国');

      c）添加多条数据：

   insert into employee values
   (106,'刘',22,16000,'北京'),
   (107,'谢',22,16000,'上海'),
   (108,'张',22,16000,'深圳');

2）replace 语句：先判断主键是否存在，不存在添加，存在则替换（先删除再添加）：

replace into employee values(105,'青',21,5000,'韩国1');

2、删除数据

1）无条件删除，删除所有数据，容易发生灾难：

delete from employee;

2）删除指定条件的数据：

delete from employee where id=108;

3、修改数据

1）不指定更新的条件，更新所有行的数据

update employee set age=23,address='美国';

2）更新指定条件的数据

update employee set age=21,address='北京' where id=105;

3）注意外键约束，如果depId在depet表中不存在会报错

update employee set depId=1002;

4、查询数据

1）查询表中所有数据

select * from employee;

2）查询表中某些列的所有数据

select id,name,age from employee;

3）按条件查询 按行

select * from employee where id='105';

4）使用as关键字对查询数据列设置别名

select id as '编号',name as '姓名',age as '年龄' from employee;

as关键字可省略：

select id '编号',name '姓名',age '年龄' from employee;

也可对表设置别名：

select e.id,e.name,e.age from employee e;

5）在查询数据中使用函数：

       a）使用字符串函数：left（从前端开始取字符串）、right（从后段开始取字符串）、substr（从指定位置取出指定个数的字符串）、reverse（反序显示）

       其中，left、right函数中第一个参数是目标字符串，第二个参数代表从前端开始截取数据的位数，如下：

   select left(name,1) from employee;#截取name的前一位
   select right(name,1) from employee;#截取name的后一位
   select substr(name from 2 for 1) from employee;#从name第2位开始截取总长度为1的字符串
   select reverse(name) from employee;#name反序显示

       b）使用正则表达式函数：

   select * from employee where name regexp '李?';

      c）使用日期函数（以yyyy-MM-dd HH:ii:ss格式为例）：

   select ADDDATE('2016-8-11 21:29:45',INTERVAL '2:30' HOUR_MINUTE)-- 加上日期,HOUR_MINUTE参数表示日期格式
   select date('2016-8-11');#获取年月日
   select year('2016-8-11');#获取年
   select month('2016-8-11');#获取月
   select day('2016-8-11');#获取日

      对日期进行格式化，使用date_format函数，如下：

   select date_format('2016-8-11 21:29:45','%M');#对日期进行格式化,取月，返回8
   select date_format('2016-8-11 21:29:45','%H:%i:%S');#对日期进行格式化,取时分秒 返回21:29:45

      d）使用数学函数：

   select floor(12.3);#向下取整，返回整数：12
   select ceiling(12.3);#向上取整，返回整数：13
   select ceil(12.3);#向上取整，返回整数：13
   select round(12.5);#四舍五入，返回整数：13

      e）使用聚合函数（对满足条件的所有记录进行计算）：

   # avg 计算平均数
   select avg(age) '平均年龄' from employee;
   # sum 求和
   select sum(salary) from employee;
   # max 求最大值
   select max(salary) from employee;
   # min 求最小值
   select min(salary) from employee;
   # count 计数：对记录进行计数
   select count(*) from employee;

5、where子句的用法

1）在where子句使用逻辑运算符

       a）and：并且，两个条件同时成立

       b）or：或者，两个条件中满足其中一个就可以，当and和or同时出现时，谁在前面谁先执行

   select * from employee where
   id=107 and salary=16000 OR age =23

       c）not：取反，也可以使用!=

   select * from employee where not id=107;
   select * from employee where id!=107;

2）在where子句使用比较运算符 < > <= >= != <>(不等于）

3）在where子句使用between...and 查询指定范围的数据，相当于>= <

select * from employee where age BETWEEN 20 and 50;

4）在where子句使用like进行模糊查询

       a）使用%代表其他所有字符：

   select * from employee where name like'赵%';//以赵开始的所有name

       b）使用_代表一个字符：

   select * from employee where name like'赵_';//以赵开始的2个字符的name

5）在where子句使用in关键字（查询条件处于某一集合的情况）：

select * from employee where address in('美国','北京','欧洲');

6）在where子句使用算术运算符 + - *  /

select id,salary+200 as '工资' from employee

7）在where子句使用limit，对于查询的结果再次过滤，一般用于分页，limit需要2个参数，其中第一个参数起始的行号，第二个参数需要取出数据的行数

select * from employee limit 5,3;//从第5条开始取3条数据

       注意：第一个参数当行数不存在时，数据返回为空。第二个参数当数据显示条数不足时，则返回剩下的所有数据。

       limit在分页中的应用：根据页码单独查询单页数据，这样可实时更新数据

   -- 1 总数据的条数 select count(*) from employee
   -- 2 每页显示多少条数据（固定）20
   -- 3 总页数 能整除取商，不能整除+1
   -- 4 当前页面 
   select * from employee limit (i-1)*20,20 #i表示当前页码

6、数据的排序和分组

1）对查询的数据进行排序，使用order by关键字，默认按主键升序排列，升序使用asc关键字，降序使用desc关键字

select * from employee;#默认顺序按主键，默认升序
select * from employee order by age asc;#按age升序
select * from employee order by age desc;#按age降序

其中，多列排序是在order by中写多列，数据按照从左向右排序。如下，先按age升序，age相等时按id降序：

select * from employee order by age asc,id desc;

2）分组，使用group by关键字，一般和聚合函数一起使用：

select age,avg(salary) from employee group by age #按照age分组，求salary的平均值
select age,max(salary) from employee group by age #按照age分组，求salary的最大值

3）having 子句：在分组的基础上进行过滤，如下在已完成分组的情况下去除age=18,19的组：

select age,max(salary) from employee group by age having age!=18 and age!=19;

having与where子句查询的数据相同，如下：

select age,max(salary) from employee where age!=18 group by age ;

having与where子句的区别：having先分组再过滤；where先过滤再分组。在数据量比较大的情况，having性能好一些。

4）综合group by、having、order by语句，只能对group by分组完的数据进行排序才有用，如下应用：

select age,max(salary) from employee 
group by age having age!=18 and age!=19 order by age desc;