数据库day02

常用的数据库命令

1.数据库相关SQL
- 查询所有 show databases;
- 创建 create database db1 charset=utf8/gbk;
- 查询信息 show create database db1;
- 删除 drop database db1;
- 使用  use db1;

2.表相关SQL
- 创建 create table t1(name varchar(20)) charset=utf8/gbk;
- 查询所有 show tables;
- 查询表信息  show create table t1;
- 表字段  desc t1;
- 删除表 drop table t1;
- 修改表名  rename table t1 to t2;
- 添加字段 alter table t1 add age int first/ after xxx;
- 删除字段 alter table t1 drop age;
- 修改字段 alter table t1 change 原名 新名 新类型;

3.数据相关SQL
- 插入数据  insert into t1(字段1名,字段2名) values(值1,值2),(值1,值2),(值1,值2);
- 查询数据 select 字段信息 from 表名 where 条件;
- 修改数据 update 表名 set xxx=xxx,xxx=xxx where 条件;
- 删除数据 delete from 表名 where 条件;

其他命令
- 去重distinct  
- and or       not
- between x and y 两者之间
- in(xxx,xxx)
- 模糊查询like         %0或多个未知字符   _1个未知字符
- 排序  order by 字段名 asc/desc,字段名 asc/desc
- 分页 limit 跳过的条数,请求条数(每页条数)
- 别名     select name 名字  from emp;
- 聚合函数:    求平均值avg(), 最大值max(), 最小值min(),求和sum(),计数count(*)

一.查询数据

格式：select 字段信息 from 表名 where 条件；
想要查询到的信息放到select后面，查询条件放到where后面

insert into person values('刘备',40),('关羽',30),('悟空',20),('八戒',10),('张学友',5);

// 查询语句练习
1. select name from person;
2. select name,age from person;
3. select * from person;
4. select * from person where age = 50;
5. select age from person where name = '悟空';

二. 修改数据

格式：update 表名 set 字段名 = 值 where 条件；

//修改数据练习
1. update person set age = 88 where name = '刘备';
2. update person set name = '张飞',age = 18 where name = '关羽';
3.update person set name = '黎明' where age = 5;

三.删除数据

格式:delete from 表明 where 条件；

删除数据练习：
1. delete from person where name = '张飞';
2. delete from person where age < 30;
3. delete from person;

综合练习

1.创建数据库day1db,字符集utf8并且使用
create database day1db charset = utf8;
use day1db;
2.创建t_hero表,有name字段 字符集utf8
create table t_hero(name varchar(50))charset = utf8;
3.修改表名为hero
rename table t_hero to hero;
4.最后面添加价格字段money,最前面添加id字段,name后面添加age
alter table hero add money int;
alter table hero add id int first;
alter table hero add age int after name;
5.表中添加一下数据1,李白,50,6888  2,赵云,30,13888          3,刘备,25,6888 
insert into table hero values(1,'李白',50,6888),(2,'赵云',30,13888),(3,'刘备',25,6888)
6.查询价格为6888的英雄名
select name from table hero where money = 6888;
7.修改刘备年龄为52岁
update hero set age = 52 where name = '刘备';
8.修改年龄小于等于50岁的价格为5000
update hero set money = 5000 where age < = 50;
9.删除价格为5000的信息
delete from hero where money = 5000;
10.删除表,删除数据库
drop table hero;
drop database day1db;

四.主键约束

- 主键: 表示数据唯一性的字段称为主键
- 约束: 创建表时给表字段添加的限制条件
- 主键约束: 限制主键的值,唯一且非空(单独使用如果为空就不是唯一的)

//如何使用主键约束
use day2db;
create table ti(id int primary key,name varchar(50))charset = utf8;
insert into t1 values(1,'aaa');
insert into t1 values(1,'bbb');	//报错,主键值重复
insert into t1 values(null,'ccc'); //报错,主键不能为null

五.主键约束 + 自增

自增规则:从历史最大值基础上 + 1
格式: primary key auto_increment
主键本来是不能为空,但是与自增auto_increment配合使用,设置为空反倒是触发自增的条件

格式: create table t2(id int primary key auto_increment,name varchar(50)) charset = utf8;

六.SQL语句分类

• DDL:数据库定义语言,包括数据库相关和表相关的SQL语句
• 解释:建库建表的属于数据定义语言
• DML:数据操作语言,包括增删改查
• 解释: select查询既属于DML又属于DQL
• DQL:数据查询语言,只包含select查询相关的SQL语句
• 解释:平常使用最多的就是DQL
• TCL:事务控制语言
• DCL:数据控制语言
• 解释: 包括权限管理,主要是创建账户,分配权限

七.数据类型

1. 整数: int(m)和bigint(m)

• m 表示显示长度,存入18,查询得到00018,这里的m是限制显示,如果存入的数据的长度大于m则正常显示，如果没有达到m添加zerofill 则会在前面自动补零达到限制显示的长度

• bigint(m) 相当于java语言中的long

create table t3(age int(5) zerofill);

2.浮点数

• double(m,d) m代表总长度，d代表小数长度，整数长度为m-d
• 即添加进去的浮点数数据整数长度不能超过m-d，小数长度不能超过d

create table t5(price double(5,3));
insert into t5 values(23.3213);	//报错，小数长度超出范围
insert into t5 values(233.23);	//报错，整数长度超出范围
insert into t5 values(2.3);	//正常

3.字符串

后面跟的参数是限制字符串的最大长度

• char(m) 固定长度,m=10 存入abc,占10 执行效率略高,当保存数据的长度相对固定时使用,最大值255
• varchar(m),可变长度,m=10存abc,占3,更节省空间,最大值65535,但推荐保存短的数据(255以内)
• text(m),可变长度,最大值65535,建议保存长度大于255的

4.日期

• date: 只能保存年月日
• time: 只能保存时分秒
• datetime: 保存年月日时分秒,默认值为null.最大值9999-12-31
• timestamp,(时间戳,距离1970年1月1日的毫秒数,默认值为当前系统时间,最大值2038-1-19(完全不同担心,到时间会自动更改算法延长时间)),即使存进去的数为null,也会自动编译为当前系统时间

create table t6(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t6 values('2022-5-15',null,null,null);
insert into t6 values(null,'14:20:25','2022-10-22 10:20:30',null);

八.去重distinct

1.查询员工表中所有不同的工作
select distinct job from emp;
2.查询员工表中出现了哪几种不同的部门id
select distinct dept_id from emp; 

九.is null 和 is not null

1.查询有领导的员工姓名和领导id
select name,manager from emp where manager is not null;
2.查询没有领导的员工姓名
select name from emp where manager is null;

十.and 和 or

1.查询1号部门工资高于2000的员工信息
select * from emp where dept_id = 1 and sal > 2000;
2.查询3号部门或工资等于5000的员工信息
select * from emp where dept_id = 3 or sal > = 5000;
3.查询出孙悟空和猪八戒的员工信息
select * form emp where name = '孙悟空' or name = '猪八戒';

十一.比较运算符 > < >= <= = !=和<>

• != 和 <>都可以代表不等于

1.查询工资大于等于3000的员工信息
select * from emp where sal >= 3000;
2.查询工作不是程序员的员工信息(两种写法)
select * from emp where job != '程序员';
select * from emp where job <> '程序员';

十二.between x and y 两者之间

1.between x and y  两者之间查询工资在2000到3000之间的员工信息
select * from emp where sal >2000 and sal<3000;
select * from emp where sal between 2000 and 3000;
2.between x and y  两者之间查询工资不在2000到3000之间的员工信息
select * from emp where sal not between 2000 and 3000;

十三. in 关键字

1.查询工资等于5000,1500,3000的员工信息
select * from emp where sal=5000 or sal=1500 or 3000;
select * from emp where sal in (5000,1500,3000);
2.查询工资不等于5000,1500,3000的员工信息
select * from emp where sal not in(5000,1500,3000);

十四.模糊查询like

• %: 代表0或多个未知字符
• _: 代表1个未知字符
• 举例:
  1. 以x开头: x%
  2. 以x结尾: %x
  3. 包含x: %x%
  4. 第二个字符是x: _x%
  5. 以x开头以y结尾: x%y_
  6. 第二个是x倒数第三个是y: x%y

1.查询名字姓孙的员工信息
select * from emp where name like 'sun%';
2.查询名字以精结尾的员工姓名
select * from emp where name like '%精';
3.查询工作第二个字是售的员工姓名和工作
select name,job from emp where name like '_售%';
4.查询名字中包含僧并且工资大于2000的员工姓名和工资
select name,sal from emp where name like '%僧%' and sal >2000;

十五.order by

格式: order by 字段名 asc(默认升序ascend) /desc(descend降序)

1.查询所有员工姓名和工资并按照工资升序排序
select name,sal from emp order by sal;
select name,sal from emp order by sal asc;
2.查询所有员工姓名和工资并按照工资降序排序
select name,sal from emp order by sal desc;
3.查询所有员工姓名,工资和部门id并且按照部门id升序排序,如果部门id一致则按照工资降序排序
select name,sal,dept_id from emp order by dept_id,sal desc;

十六.分页查询(limit 后面不跟括号)

格式: limit跳过的条数,请求的条数(每页的条数)
跳过的条数 = (请求的页数-1)*请求的条数(每页的条数)

举例:

1. 查询第1页的5条数据(1-5条) limit 0,5
2. 查询第2页的5条数据(6-10条) limit 5,5
3. 请求第1页的10条数据 limit 0,10
4. 请求第3页的10条数据 limit 20,10
5. 请求第8页的10条数据 limit 70,10
6. 请求第6页的8条数数据 limit 40,8

1.查询工资最低的3个员工信息(换句话说:查询按照工资升序排序的第一页的3条数据)
select * from emp order by sal limit 0,3;
2.按照入职日期(hiredate) 升序排序 查询第3页的3条数据
select * from emp order by hirdate limit 6,3;
3.查询工资最高的员工信息
select + from emp order by sal limit 0,1;
4.查询按照工资降序第2页的5条数据
select * from emp order by sal desc limit 5,5;

十七.别名

格式: 字段名 + 别名(可以是中文可以是英文)

1.select name as '姓名' from emp;
2.select name '姓名' from emp;
3.select name 姓名 from emp; 

十八.聚合函数

通过聚合函数可以对查询的多条数据进行统计,统计查询的方式包括:求平均值,求最大值,求最小值,求和,计数

1. 平均值:avg(字段名)

1.查询1号部门的平均工资
select avg(sal) from emp where dept_id = 1;
2.查询销售的平均工资
select avg(sal) from emp where job = '销售';

2.最大值max(字段名)

查询程序员的最高工资
select max(sal) from emp where job = '程序员';

3.最小值min(字段名)

查询3号部门的最低工资
select min(sal) from emp where dept_id = 3;

4.求和sum(字段名)

查询2号部门的工资总和
select sum(sal) from emp wher dept_id = 2;

5.计数count(*)

查询程序员的数量
select count(*) from emp where job = '程序员';

十九.数值计算+ - * / %

1.查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)
select name,sal,sal*5 年终奖 from emp;
2.给3号部门的员工每人涨薪5块钱  
select sal+5 涨薪后薪资 from emp where dept_id = 5;