2020-12-01

sql和navicat基础编程

1. SQL 分类

DDL：数据定义语言，用来定义数据库对象：数据库、表。
DML：数据操作语言，对数据库中表的数据进行增删改
DQL：数据查询语言，用来查询数据库中表的记录
DCL：数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户

2. DDL：操作数据库、表

2.1 操作数据库：CRUD

create：创建

1
2
3

#create database if not exists 数据库名;
#create database 数据库名 character set 编码格式;
mysql> create database if not exists db1 character set gbk;

Retrieve：查询
- show databases;
- show create database 数据库名;
Delete：删除
- drop database if exist 数据库名;
update：修改
- 修改数据库的字符集
  
  alter database 数据库名 character set 编码格式;
使用数据库
- 查询当前正在使用的数据库名称
  
  select database();

2.2 操作表

创建

create tables 表名(

列名数据类型1,

列名数据类型2

);
数据类型

int ：整数类型

double：小数类型

date：只包含年月日yyyy-MM-dd

datetime：包含年月日时分秒：yyyy-MM-dd HH:mm:ss

timestamp：时间戳，如果不给字段赋值，则系统将自动赋值当前的系统时间。且要设置默认值

alter table 表名 MODIFY 列名 TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP; # 这里是修改的时候设置的默认值CURRENT_TIMESTAMP

varchar：字符串
查询
- 查询某个数据库中所有的表名称
  
  show tables;
- 查看表结构
  
  desc 表名;
更新
- 修改表名
  
  alter table 表名 rename to 新表名
- 修改表的字符集
  - 查看表的字符信息
    
    show create table 表名;
  - 修改
    
    alter table character set 字符集;
- 添加一列
  
  alter table 表名 add gender varchar(10);
- 修改列名称类型
  
  alter table 表名 modify 列名新类型; 只改类型
  
  alter table 表名 change 旧列名新列名新类型;
- 删除列
  
  alter table 表名 drop 列名;
删除

drop table if exists 表名；
复制表

create table 表名 like 被复制的表名;

3.DML：增删改表中数据

添加数据

insert into 表名(列名1,.......,列名n) values(值1,......值n);

insert into 表名 values(值1,......值n); # 需要为每一列都添加值
删除数据

delete from 表名 [where 条件];

删除表中所有记录
- truncate table 表名; # 删除表，然后再创建一样S的空表
- delete from 表名; # 不推荐使用，因为是有多少条记录执行多少次删除
修改数据

update 表名 set 列名1=值1，.....，列名n=值n [where 条件];

不加where将会把表中该列所有的数据修改

4.DQL：查询语句

基础查询

/*
select 字段列表
from 表名列表
where 条件列表
group by 分组字段
having 分组之后的操作
order by 排序
limit 分页限定
*/
-- 计算英语和数学总数
select test_name,math,english,IFNULL(math,0)+IFNULL(english,0) 总分 from student;
select * from student where test_name like '祁%';

条件查询

运算符：
- < <= > >= = <> !=
- BETWEEN…AND
- IN(集合)
- LIKE 模糊查询
  - 占位符
    
    _ (下划线)：单个任意字符
    
    %：任意多个字符
- IS NULL
- and 或者&&
- or 或者||
- not 或者 !

排序查询：升序：ASC（默认），降序：DESC

1 2	-- order by 排序字段1 排序方式1，排序字段2 排序方式2... select *from student ORDER BY math ASC,english ASC;

聚合函数：count、max、min、sum、avg

-- select count(列名) from 表名;  -- 排除了空值得出的记录个数
-- count
select count(math)  from student; -- 得出有几条记录，排除空数据
select count(IFNULL(english,0)) from student; -- 如果存在null值则将其换为0,原表不会改动
select count(*)  from student; -- 只要这一行有一个不为null就算一个记录，但不推荐使用
select count(test_id)  from student; -- 推荐参数选择主键 
-- max
select max(math) from student;
select min(math) from student;

-- sum
select sum(math) from student;

-- avg
select avg(math) from student;

分组查询：分组之后查询的字段：分组字段、聚合函数

-- group by 分组字段
-- 按照性别分组，分别查询男女的平均分
select sex,avg(math) math_avg,avg(english)math_english,count(test_id) from student group by sex;
select english,count(english)  from student group by english;
-- 英语分数大于80才参与分组，且只计算组内记录数目大于1的数据
-- 给count(test_id)命名为人数，在having中可以直接使用人数代替count(test_id)
select sex,count(test_id) 人数,avg(english) english_avg from student where english>= 80 group by sex HAVING 人数>1;

where和having的区别：

where在分组之前进行限定，如果不满足条件，则不参与分组；having在分组之后进行限定，如果不满足条件，则不会被查询出来。
where后不可以跟聚合函数，而having可以进行聚合函数的判断。

分页查询

MySQL下：limit 开始的索引,每页查询的条数

1
2
3

-- 每页显示两条记录
-- 开始索引=(当前的页码 - 1) * 每页显示的条数
select * from student limit 2,2;

5.约束

主键约束：primary key
非空约束：not null

唯一约束：unique

create table stu(
	id INT primary key auto_increment, -- 主键约束,自动增长
	stu_name varchar(20) not null, -- 非空
	stu_phone varchar(13) unique -- 唯一约束/唯一索引
);
-- 删除非空约束
alter table stu modify stu_name varchar(20);
-- 创建完表后添加非空约束
alter table stu modify stu_name varchar(20) not null default "";

-- mysql中唯一约束限定的列的值可以有多个null
-- 删除唯一约束
alter table stu drop index stu_phone;
-- 创建表后添加唯一约束
alter table stu modify stu_phone varchar(13) unique;

-- 删除主键
alter table stu drop primary key;
-- 创建完表后添加主键
alter table stu modify id int primary key;
alter table stu add primary key(id);

外键约束：foreign key

创建表时添加外键
删除外键
创建表之后添加外键
级联操作 on update cascade on delete cascade

级联删除\级联更新：restrict、cascade、set null、no action

-- 拆分为两张表 emp和department
-- constraint 外键名称 foreign key 外键列字段名称 references 主表名称(主表列名称)
-- 删除外键
-- alter table 表名 drop foreign key 外键名称（注意不是外键那个字段名称）
alter table emp drop foreign key  emp_dep_id;
-- 创建表之后增加外键
alter table emp add constraint emp_dep_id foreign key (dep_id) references  department(dep_id);

6.设计数据库

多表之间的关系
- 一对一：
  - 一个人只有一个身份证
  - 实现方式：可以任意一方添加唯一外键指向另一方的主键
- 一对多
  - 一个部门有多个员工，一个员工只能对应一个部门
  - 实现方式：在多的一方建立外键，指向一的一方的主键。
- 多对多
  - 一个学生可以选择多门课程，一门课程可以被多个学生选择
  - 实现方式：多对多关系中实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。
数据库设计的范式

7. 多表查询

department表

dep_id	dep_name	dep_location
1	研发部	广州
2	销售部	深圳

emp表

id	name	age	dep_id	salary
1	张三	21	1	25
2	王五	21	2	20

内连接

-- 隐式内连接
select 
		t1.name,
		t2.dep_name 
from	
		emp t1,
		department t2
where 
		t1.dep_id  = t2.dep_id;
-- 显示内连接
-- select 字段列表 from 表名1 inner join 表名2 on 条件;
select emp.`name`,department.dep_name from emp INNER JOIN department on emp.dep_id = department.dep_id;

外连接

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门则不显示
-- 左连接查询
-- select 字段列表 from 表1 LEFT JOIN 表2 on 条件;
select emp.*,department.dep_name from emp LEFT JOIN department on emp.dep_id = department.dep_id;

子查询

-- 子查询的结果是单行单列
-- 子查询可以作为条件，使用运算符做判断
-- 1.查询员工工资小于平均工资的人
select emp.* from emp where emp.salary < (select avg(emp.salary) from emp);

-- 子查询的结果是多行单列,使用运算符in
-- 2.查询财务部和销售部所有的员工信息
select emp.* from emp where emp.dep_id in (select department.dep_id from department where dep_name = "研发部" or dep_name = "销售部");

-- 子查询的结果是多行多列,子查询可以作为一张虚拟表
-- 3.查询员工工资大于15的员工信息和部门信息
select emp.*,department.dep_name from emp,department  where emp.salary > 15 and department.dep_id = emp.dep_id;


-- 将下面这个查询的结果当作一个新表
-- select emp.* from emp where emp.salary > 15;

select t2.*,t1.dep_name,t1.dep_location
from 
		department t1,
		(select emp.* from emp where emp.salary > 15) t2
where 
		t1.dep_id = t2.dep_id;

8.事务

事务的概念

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。在MySQL中执行一条DML语句默认提交一次事务，而Oracle默认手动提交事务。
- Mysql事务的默认提交方式
1
2
select @@autocommit;-- 1代表自动提交，0代表手动提交
set @@autocommit = 0;

操作

开始事务：start transaction

回滚：rollback

提交：commit

start TRANSACTION;
update emp set salary = salary-10 where id=2;
update emp set salary = salary+10 where id=3;
-- 如果没有出错就提交
commit;
-- 如果出错回滚
rollback;

事务的四大特征
- 原子性
- 持久性
- 隔离性：多个事务之间
- 一致性：前后操作之后数据总量不变
事务的隔离级别

多个事务之间是隔离的、相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题
- 存在问题
  - 脏读：一个事务读取到另一个事务中没有提交的数据
  - 不可重复读：在同一个事务中两次读取到的数据不一样
  - 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
- 隔离级别：隔离级别从小到大安全性越来越高，但是效率越来越低
  - read uncommitted：读未提交
    - 会产生的问题：脏读、不可重复读、幻读
  - read committed：读已提交（Oracle默认）
    - 会产生的问题：不可重复读、幻读
  - repeatable read：可重复读（Mysql默认）
    - 会产生的问题：幻读
  - serializable：串行化
- 数据库查询隔离级别
  1
  2
  select @@transaction_isolation;
  show global variables like '%isolation%';
- 数据库设置隔离界别
  1
  set global transaction_isolation ='REPEATABLE-READ';

9.DCL管理用户、授权

管理用户

添加用户
删除用户
修改用户密码

查询用户

-- 切换数据库 mysql
use mysql;
select * from user;
-- 创建用户
-- create user '用户名'@'主机名' identified by '密码';
-- 主机名为%时表示任意电脑上该用户都可访问本数据库
-- 创建用户
create user 'hxx'@'localhost' identified by '123456';
-- 删除用户
drop user  'hxx'@'localhost';
-- 修改用户密码
-- ALTER USER 'test'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '新密码';
ALTER USER 'hxx'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '147852';

权限管理

查询权限、授予权限、撤销权限

-- 查询权限
show grants for '用户名'@'主机名';

-- 授予权限
-- grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
grant select on test.stu to 'hxx'@'localhost';
grant all on *.* to 'hxx'@'localhost';

-- revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
revoke all on *.* from 'hxx'@'localhost';