15-2.2 数据库Mybatis及mariadb的简单操作(sql语句增删改查)

数据库

增

create database mydb1; //创建新库mydb1
create database mydb1 charset utf8;
create database if not exists mydb1 charset utf8;

删

drop database mydb1;
drop database mydb1 if exists mydb1;

改

查

show databases; //查看当前数据库服务器中的所有库
show create database mydb1;  //查看建库时的语句

数据库里的表

use mysql;   //进入到mysql数据库

增

create table stu(id int primary key auto_increment,
				neme varchar(50),
				gender varchar(10) not null,
				birthday date,
				score double)

删

drop table if exists stu;

改

查

desc stu;  //查看表结构!!!!
show tables;  //查看某一个数据库里的表
select database();  //查看已经入的库

数据库表里的表记录

(针对cmd窗口)在插入数据之前，先设置编码：set names gbk;

增

insert into stu(id,name,gender,birthday,score) value(null,'tom','male','2000-3-4',96);
insert into stu value(null,'tom','male','2000-3-4',96);

删

delete from stu where id>2;

改

update stu set score=83,gender='male' where id=3;
update emp set bonus=null where name='韩少云';  //null加任何数值都是null

查

select * from stu;
select name,sal+bonus from emp where sal+bonus>3500;
select distinct dept,job from emp;//使用distinct剔除重复的记录
select name as 姓名,sal+ifnull(bonus,0) 总薪资 from emp where sal+ifnull(bonus,0)>3500;// ifnull(列名, 值)函数: 判断指定的列是否包含null值，如果有null值，用第二个值替换null值
selcet name,sal from emp where sal>=3000 and sal<=4500;
                         where sal between 3000 and 4500;  //包括头和尾
                         where sal=1400 or sal=1600 or sal=1800;
                         where sal in(1400,1600,1800);
                         where not(sal=1400 or sal=1600 or sal=1800);
                         where sal not in(1400,1600,1800);
                         where sal>3000 and ifnull(bonus,0)<600;//null值和任何值比较结果都是false
select * from emp where dept is null;  //询列中null值 不能用=号需要用 is 如果要把某一列值更新为null可以用=号
select * from emp where not(dept is null);
select * from emp where dept is not null;
--模糊查询 like
select name from emp where name like '刘%';
select name from emp where name like '刘__';
书写顺序  where 字句--group by分组 --order by排序 --limit 分页
--多行查询
select job,count(*) from emp where group by job;
select max(sal) from emp group by gender;
select sum(sal) from emp;
select avg(sal) from emp;
select name,bonus from emp order by bonus desc;  //降序
select * from emp limit 0,3;  //limit (页码-1)*每页显示记录数, 每页显示记录数
| 函数名               | 解释说明                                                     |
| -------------------- | ------------------------------------------------------------ |
| curdate()            | 获取当前日期，格式是：年月日                                 |
| curtime()            | 获取当前时间 ，格式是：时分秒                                |
| sysdate()/now()      | 获取当前日期+时间，格式是：年月日 时分秒                     |
| year(date)           | 返回date中的年份                                             |
| month(date)          | 返回date中的月份                                             |
| day(date)            | 返回date中的天数                                             |
| hour(date)           | 返回date中的小时                                             |
| minute(date)         | 返回date中的分钟                                             |
| second(date)         | 返回date中的秒                                               |
| CONCAT(s1,s2..)      | 将s1,s2 等多个字符串合并为一个字符串                         |
| CONCAT_WS(x,s1,s2..) | 同CONCAT(s1,s2,..)函数，但是每个字符串之间要加上x，x是分隔符 |
select name,birthday from emp where year(birthday) between 1993 and 1995;
select name,birthday from emp where birthday>='1993-1-1' and birthday<='1995-12-31';
select name,birthday from emp where month(now())=month(birthday);
select name,sal+'(元)' from emp;  //只在表头加上了
select name,concat(sal,'(元)') from emp;  //字符串拼接
select name,concat(sal,'/元') from emp;
select name,concat_ws('/',sal,'元') from emp;
--多表关联查询
select * from dept,emp; //笛卡尔积查询
select * from dept,emp where emp.dept_id=dept.id;
select * from dept inner join emp on emp.dept_id=dept.id;
select * from dept left join emp on emp.dept_id=dept.id;
select * from dept right join emp on emp.dept_id=dept.id;

select * from dept left join emp on dept_id=dept.id
union
select * from dept right join emp on dept_id=dept.id;
/*
union可以将两条SQL语句执行的结果合并，但是有前提:
(1)两条SQL语句查询的结果列数必须一致
(2)两条SQL语句查询的结果列名、顺序也必须一致
并且union默认就会将两个查询中重复的记录去除(如果不希望去除重复记录，可以使用union all)
*/
--多表关联查询中子查询(嵌套查询)
select name,sal from emp where sal>(select sal from emp where name='王海涛');
select emp.name,sal,dept,name from dept,emp where dept_id=dept.id
and sal>(select max(sal) from emp where dept_id=30);
select emp.name,sal,dept.name from dept right join emp on emp.dept_id=dept.id
where sal>(select max(sal) from emp where dept_id=30);  //重点!!当没有where时貌似必须首先加where作为查询条件,再有条件时再加and,貌似没有where时不先加where直接加and貌似就报错..
select d.name,e.name from dept as d,emp e where e.dept_id=d.id and d.name='培优部';
select job,min(sal) from emp group by job having min(sal)>1500;
/*
where和having子句
(1)相同点: where和having都可以对记录进行筛选过滤。
(2)区别：where是在分组之前,对记录进行筛选过滤,并且where子句中不能使用多行函数以及列别名(但是可以使用表别名)
(3)区别：having是在分组之后,对记录进行筛选过滤,并且having子句中可以使用多行函数以及列别名、表别名。
*/
select d.id,d.name,d.loc,count(*) from dept d,emp e where d.id=e.dept_id 
group by d.name having count(*)>0;  //having count(*)>0 可以省略

select e1.id,e1.name,d.name from emp e1,emp e2,dept d 
where e1.topid=e2.id and e1.dept_id=d.id and e1.hdate<e2.hdate;

select * from emp order by sal desc limit 0,1;
select * from emp where sal=(select max(sal) from emp);  //等价

Mysql的数据类型

数值类型

tinyint：占用1个字节，相对于java中的byte
smallint：占用2个字节，相对于java中的short
int：占用4个字节，相对于java中的int
bigint：占用8个字节，相对于java中的long
float：4字节单精度浮点类型，相对于java中的float
double：8字节双精度浮点类型，相对于java中的double

字符串类型

char(n) 定长字符串,最长255个字符
varchar(n) 变长字符串,最长不超过65535个字节,n表示字符数
text 大文本（长文本）类型

日期类型

date：年月日
time：时分秒
datetime：年月日 时分秒 --字符串类型
timestamp：时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同

• timestamp 1970~最大表示2038年，而datetime范围是1000~9999
• timestamp在插入数据、修改数据时，可以自动更新成系统当前时间

mysql的字段约束

主键约束 --primary key;
非空约束 --not null;
唯一约束 --unique;
外键约束 --foreign key(dept_id) references dept(id); 创建表时添加
create table emp(
id int,
name carchar(50),
dept_id int,
foreign key(dept_id) references dept(id));
添加了外键约束等同于通知数据库，两张表之间存在对应关系，dept_id列中的数据要参考部门的主键，数据库一旦知道部门和员工表之间存在关系，就会帮我们维护这层关系。

数据库备份与恢复

备份数据库
mysqldump -u用户名 -p 数据库的名字 > 备份文件的位置

mysqldump -uroot -p db40 > d:/db40.sql
mysqldump -uroot -p --all-database > d:/all.sql

恢复数据库
mysql -u用户名 -p 数据库的名字 < 备份文件的位置

create database db60 charset utf8;
mysql -uroot -p db60 < d:/db40.sql

create database db60 charset utf8;
use db80;
source d:/db40.sql