Mysql

GBK是在国家标准GB2312基础上扩容后兼容GB2312的标准（好像还不是国家标准）。GBK编码专门用来解决中文编码的，是双字节的。不论中英文都是双字节的。

UTF－8 编码是用以解决国际上字符的一种多字节编码，它对英文使用8位（即一个字节），中文使用24位（三个字节）来编码。对于英文字符较多的论坛则用UTF－8 节省空间。另外，如果是外国人访问你的GBK网页，需要下载中文语言包支持。访问UTF-8编码的网页则不出现这问题。可以直接访问

day001

数据库分类

关系型数据库（SQL）二维表基本单位
非关系型数据库（NoSQL）key-value存储（mongodb，redis…）
- 应用范围：缓存、高并发...

数据库中的操作

创建库

Code

create database tedu;

查看库

Code

show databases;

使用库

Code

use  数据库名;

表的操作

创建表

Code

create table lrl(id int,name varchar(20),age int(10),job varchar(30));//其中id占用11个字节，age占用1个字节，

查看表结构

Code

show create table lrl;

表的引擎（engine）限制表中数据的操作

innodb：表中的数据支持高级操作（表、事务、外键、约束etc)
myisam：表中的数据只能进行增删改查的基本操作。

查看表的字段

Code

desc lrl;

删除表

Code

drop table 表名；

修改表

Code

rename table 原名 to 新名;

修改引擎和字符集

Code

alter table 表名 engine = utf8/gbk（不是utf-8）
engine = innodb/myisam
charset=utf8/gbk

表中添加字段

Code

字段类型 after ***(某一个字段名)

删除一个字段

Code

alter table 表名 drop 字段名

作业

1.创建数据库newdb并使用，里面创建员工表emp，只有name字段，引擎为myisam，字符集为gbk

2.修改表名为temp；

3.修改引擎为innodb

4.添加部门编号字段deptno在最后面

5.添加salary字段在name的后面

6.修改salary字段的字段名为sal，并把sal放在deptno后面

7.删除字段sal

数据的操作

插入数据

全字段插入

Code

insert into emp values(1,'tom',22,6000,'程序猿');

指定字段插入

Code

insert into emp(name,salary,job) values ('jack',5000,'美工')

批量插入

Code

insert into emp values
(1,'唐僧',36,10000,'领导'),
(2,'悟空',610,7000,'打手'),
(3,'悟能',960,8000,'卧底'),
(4,'悟净',780,5000,'后勤')
;

查询数据

Code

select name from emp where salary <8000 and/or age<600;

修改数据

Code

update 表名 set 修改数据
where 修改条件;

Code

修改tom工资为3333
update emp set salary=3333 where name ='tom';
修改800岁以下的工资为4000
update emp set salary = 4000 where age<800;
将id=3的人名字改为吕布
update emp set name="吕布" where id = 3;
修改年龄为Null的人年龄为40
update emp set age = 40
where age is null;

数据约束

id:每一条数据的唯一标识符，特性：唯一性、非空性

约束：主键约束：primary key非空性、唯一性

非空约束：not null

外键约束：foreign key

检查约束：check

自增值：auto_increment

Code

create table empl(id int primary key,
name varchar(10)
);

insert into empl values(null,'jack');


insert into empl (name) values ('rouse');


create table emp2(id int primary key auto_increment,
name varchar(10)
);


insert into emp2 values(null,'张三');
insert into emp2 values(null,'李四');
insert into emp2 values(10,'王五');
insert into emp2 values(null,'赵六');

delete from emp2 where id = 11;
insert into emp2 values(null,'前八');

数据冗余

如果表设计不合理，保存大量的数据同时可能会随之出现大量的重复数据，这些重复的现象称为冗余。

通过拆分的方式可以消除冗余

Code

create table emp3 (
	id int primary key auto_increment,
	name varchar(20),
	salary int,
	deptId int
);

create table dept(
	id int primary key auto_increment,
	name varchar(20),
	parentId int
);

马化腾 8000->市场开拓部->市场部->销售部

insert into emp3 values(1,’马化腾’,8000,3);

insert into dept values(1,’销售部’,null);

insert into dept values(2,’市场部’,1);

insert into dept values(3,’市场开拓部’,2);

insert into dept values(4,’教学部’,null);

insert into dept values(5,’教研部’,4);

insert into dept values(6,’java教研部’,5);

教学部->教研部->java教研部->开金羊 7000

insert into emp3 values(2,’开金羊’,7000,6);

select emp3.name,salary ,dept.name from emp3,dept where emp3.deptId = dept.id ;

day002

数据类型

int: 整数类型
double：浮点类型
时间类型
- date保存年月日
- datetime：保存年月日时分秒
- time：保存时分秒
- timestamp（时间戳）：保存年月日时分秒

Code

create table t2(
	id int,
	t1 date,
	t2 datetime,
	t3 time,
	t4 timestamp
);
show create table t2;

insert into t2 values(1,'2019-9-7','2019-9-7 8:26:32','8:26:32',null);

事务

数据科中执行sql语句的最小工作单位，保证多条sqls语句全部成功或者全部失败

数据库有一个自动提交的过程，查看自动提交是否打开（show variables like ‘%autocommit%’）

事务的状态：提交、被提交

提交（commit）
- 查看自动提交(show variables like ‘%autocommit%’);
- 1/on:打开
- 0/ff:关闭

MariaDB [tedu]> show variables like ‘%autocommit%’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| autocommit | ON |
+—————+——-+
1 row in set (0.01 sec)

Code

create table t3(
	name varchar(20),
	money int
);


insert into t3 values('开金阳',5000);
insert into t3 values('马云',10000);

//开始转账
update t3 set money = money+1000
where name = '开金阳';
update t3 set money = money-1000
where name = '马云';

关闭自动提交

set autocommit = 0

sql语句一开始是存放在缓冲区中的，一个窗口一个缓冲区，如果关闭自动提交。必须使用手动提交（commit）

窗口1（马云）马云转出1000-查询-提交-查询,

Code

update t3 set money = money-1000
where name = '马云';
select * from t3;
commit;
select * from t3;

窗口2（开金阳）我转入1000-查询-提交-查询，

Code

update t3 set money = money + 1000
where name = '开金阳';
select * from t3;
commit;
select * from t3;

回滚

当数据没有提交到数据库之前，执行的所有操作都可以回滚；

设置回滚点: rollback to s1;

savepoint s1;讲数据

模糊查询

like
- %：表示0-n个字符
- _：表示1个字符
Code
select * from t3 where name like '%金%';

排序：order by

升序：asc
降序：desc

Code

select * from emp order by salary;

聚合函数

sum（求和）
min（最小值）
max（最大值）
avg（平均值）
count（统计个数）

Code

select count(*) from emp;

作业：

1.select ename from emp where sal>2500;

2.select max(comm) from emp where sal >= 1000 and sal <= 3000;

3.select max(comm),max(sal),avg(sal),sum(sal) from emp where deptno=30;

select count(*) from emp where ename like ‘%a%’;

5.select empno,ename,sal from emp where mgr is null;

6.select ename , sal from emp where comm is null;

7.select * from emp where comm is not null and comm != 0;

查询emp表中工资是1500，5000，3000的员工信息

Code

select * from emp where sal=1500 or sal  = 3000 or sal = 5000

select * from emp where sal in(1500,3000,5000);

查询工资在2000-3000之间的所有员工信息

查询员工表中工资降序的第三页的4条数据

分页查询

limit：跳过的条数，请求的数量（每页的数量）

Code

select * from emp order by sal desc;
select * from emp order by sal desc limit 8,4;

查询10号部门和30号部门的员工工资在前三名的员工信息；

select * from emp where deptno in(10,30) order by sal desc limit 0,3;

与字符串相关的函数

获取字符串的长度： charlength（str）；
获取所有员工的名字和没名字的字符长度

Code

select ename,charlength(ename) from emp;

获取字符串在另一个字符串中第一次出现的位置instr（str,substr）查找b在abcde中的位置

select instr(‘abcde’,’b’);

插入字符串

Code

insert(str,start,length,newstr);

与数学相关函数

3.84向下取整（3），四舍五入（4），非四舍五入（3）

select floor (3.84)；//3

select round(3.84);//4

select truncate(3.84567,3);3.845;若取整必须写0；

分组查询

分组查询一般和聚合函数结合使用，以组为单位进行统计

1.select max(sal) from emp group by deptno ;

2.select avg(sal) from emp group by deptno;

3.select count(*),deptno from emp where sal > 1500 group by deptno ;

4.select count(*) from emp where mgr is not null group by mgr;

别名

select ename name（别名） from emp e;

作业

查询emp表中的每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列；

select deptno,count(empno) c,sum(sal) s from emp group by deptno order by c ,s desc;

表设计管理：权限管理

关联查询

Code

select e.ename,d.deptno,d.loc from emp e join dept d on e.deptno = d.deptno;

实现权限管理功能需要三张主表和两张关系表

Code

create table user(uid int,uname varchar(20));     //用户表

create table role(rid int,rname varchar(20));     //角色表

create table module(mid int,mname varchar(20));   //功能表

create table ur (uid int, rid int);

create table rm (rid int,mid int);



insert into user values
(1,'梁朝伟'),(2,'凤姐');
insert into role values
(1,'男游客'),(2,'男会员'),(3,'女游客'),(4,'女管理员');
insert into  module values
(1,'男浏览'),(2,'男发帖'),(3,'女浏览'),(4,'女发帖'),(5,'女删帖');

insert into ur values (1,2),(1,3),(2,1),(2,4);
insert into rm values (1,1),(2,1),(2,2),(3,3),(4,3),(4,3),(4,5);


delete from rm where rid = 4 and mid = 3;
insert into rm values(4,3),(4,4);

查询每个用户的权限有哪些？
user ->uname
moudle ->mname
select u.uname,m.mname from user u join  ur r on u.uid = r.uid ;

查询凤姐的权限有哪些？
select u.uname,m.mname from user u join  ur ur on u.uid = ur.uid join rm rm on ur.uid =rm.mid join module m on m.mid = rm.mid;
查询拥有男发帖权限的人有哪些？