Java SQL常用语句总结大全2

DCL

使用root用户 创建新用户
-- 需求：创建u1用户可以在任何电脑上登录mysql服务器，密码为1234
create user 'u1'@'%' identified by '1234';
使用root管理员创建一个d1的数据库
create database d1;
给用户授权
grant create,alter,insert,update,select on d1.* to 'u1'@'%';
取消授权
revoke all on d1.* from 'u1'@'%';
删除用户u1
drop user 'u1'@'%';
修改管理员密码
-- 新密码不需要加上引号
mysqladmin -u root -p password 新密码
修改普通用户密码
set password for '用户名'@'主机名' = password('新密码');

数据库备份与还原

命令行方式备份与还原

图形化界面备份与还原

数据库约束

数据库约束：对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。

约束种类

• primary key: 主键约束（主键必须包含唯一的值，主键列不能包含NULL值）
• unique: 唯一约束
• not null: 非空约束
• default: 默认值 了解
• foreign key: 外键约束

create table studen (
	id int primary key auto_increment,-- 主键自增
    name varchar(20) unique,-- 唯一约束
    age int not null，-- 非空约束
    address varchar(20) default '上海'  -- 地址默认为上海
);

多对多： 需要创建第三张表，并在第三张表中增加两列，引入其他两张表的主键作为自己的外键。

外键约束： 当前第三张表中的列由于都是引用其他表中的列，我们把第三张表中的这些列称为引用其他表的外键约束。

-- 创建程序员表
create table coder(
	id int primary key auto_increment,
	name varchar(50),
	salary double
);
-- 创建项目表
create table project(
	id int primary key auto_increment,
	name varchar(50)
);
-- 创建中间关系表
create table coder_project(
	coder_id int,  --这个外键来自于coder表中的主键
	project_id int
    -- 添加外键约束 constraint: 添加约束，可以不写。 references：引用
    constranint c_id_fk foreign key(coder_id) references coder(id),
    foreign key(project_id) references project(id)
);

外键的级联：在修改和删除主表的主键时，同时更新或删除从表的外键值，称为级联操作

-- 创建中间关系表,添加级联更新和级联删除
create table coder_project(
	coder_id int,  --这个外键来自于coder表中的主键
	project_id int
    -- 添加外键约束,并且添加级联更新和级联删除
    constranint c_id_fk foreign key(coder_id) references coder(id) on update cascade on delete cascade,
    foreign key(project_id) references project(id) on update cascade on delete cascade
);

表关系

表关系可分为：多对多关系、一对多（多对一）、一对一(极少)。

多对多关系前面已经说过了。

一对多： 如果是一对多的关系，那么设计表的时候需要在多的一方增加一列，引入一方的主键作为自己的外键。

-- 1、教师表（一）
create table teacher(
	id int primary key auto_increment,
	name varchar(50)
);
-- 2、课程表（多）
create table course(
	id int primary key auto_increment,
	name varchar(50),
	teacher_id int,
	foreign key(teacher_id) references teacher(id)
);

一对一： 在建表的时候，可以在任意一方的表中添加另外一方的主键作为外键即可。

数据库三大范式

第一范式

第一范式：表中每一列不能再拆分，称为原子性。

第二范式

第二范式的特点：

1. 一张表只描述一件事情。

2. 表中的每一列都完全依赖于主键

第三范式

第三范式：从表的外键必须使用主表的主键

反三范式

没有冗余的数据库未必是最好的数据库，有时为了提高数据库的运行效率，就必须降低范式标准，适当保留冗余数据。降低范式就是增加适当的字段，减少了查询时的关联，提高查询效率，但是要在满足三范式基础上适度调整。

多表查询

多表查询的意思是：通过查询多张表以获取需要的数据。

笛卡尔积问题： 把多张表放在一起，同时去查询，会得到一个结果，而这结果并不是我们想要的数据，这个结果称为笛卡尔积。

那么，如何进行清除呢？

清除笛卡尔积的方法： 查询两张表的同时添加过滤条件，比如两个表的id必须相同。

select * from fruit,price where fruit.price_id = price.id;

表连接查询

内连接

内连接： 用左边表的记录去匹配右边表的记录，如果符合条件的则显示。内连接查询的结果：两表的公共部分。

隐式内连接：看不到`JOIN`关键字，条件使用`WHERE`指定
select * from fruit,price where fruit.price_id = price.id;

显示内连接：select * from 表名1  inner join 表名2 on 条件; 可以省略inner
使用显示内连接解决上述笛卡尔积问题
select * from fruit join price on fruit.price_id = price.id;

外连接

左外连接: 用左边表去右边表中查询对应记录，不管是否找到，都将显示左边表中全部记录。

select * from 表1 left outer join 表2 on 条件；

右外连接: 用右边表去左边表中查询对应记录，不管是否找到，都将显示右边表中全部记录。

select * from 表1 right outer join 表2 on 条件；

左右外连接可以相互转换；

子查询

子查询： 一条查询语句结果作为另一条查询语法一部分。子查询要放在（）里。

按子查询结果可分为三种情况：

准备数据

单行单列

-- 需求：查询工资最高的员工？
	1.查询最高工资是多少
	select max(salary) from emp;  -- 结果是单行单列
	2.根据最高工资到员工表查询到对应的员工信息
	select * from emp where salary=(select max(salary) from emp);
	

多行单列

-- 需求：查询工资大于5000的员工，来自于哪些部门的名字 
    1.先查询大于5000的员工所在的部门id
    select dept_id from emp where salary>5000;
    2.再查询在这些部门id中部门的名字
    select dept.name from dept where dept.id in (select dept_id from emp where salary>5000);
    

多行多列

-- 查询出2011年以后入职的员工信息，包括部门名称
	1.在员工表中查询2011-1-1以后入职的员工
	select * from emp where join_date > '2011-1-1';
	2.查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于dept_id
	select * from dept as d,(select * from emp where join_date > '2011-1-1') as e where e.dept_id = d.id;

后续下期更新！