先创建表来进行记录数据的操作:
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;