MYSQL数据库基础

#####主键约束
-主键：用于表示数据唯一性的字段称为主键
约束：就是对表字段的值添加限制条件
-主键约束：保证主键的值唯一并且非空
格式：create table t1(id int primary key,name varchar(12));
主键具有唯一性和非空性
###主键约束 +自增
格式：create table t2(id int primary key auto_increament,name varchar(10));
自增数值只增不减，从历史最大值的基础上+1
truncate table t2;//删除表并创建新表 自增数值清零
####注释 comment
-格式：create table t3(id int primary key auto_increment comment ‘这是个主键’,
name varchar (10) comment ‘这是名字’);
查看注释内容：show create table t3;
####和单引号的区别 -：用于修饰表名和字段名
create table t4(id int,name varchar(10));
’ :用于修饰文本
数据冗余：
如果表涉及不合里，可能会出现大量的重复数据，这种现象称为数据冗余，通过
拆分表的形式解决此问题
create table t_emp(id int primary key auto_increment,name varchar(10),sal int,age int dept int );
create table t_dept(id int primary key auto_increment,name varchar(10),parent_id int);
//插入数据 先插入部门表数据然后再插入员工表数据
insert into t_dept values(null,‘集团总部’,null),(null,‘财务部’,1),(null,‘财务a部’,2),(null,‘研发部’,1);
insert into t_emo values(null,‘张三’,8000,18,3),(null,‘李四’,800,75,4);

-创建表：
create table category(id int primary key auto_increment,name varchar(10),parent_id int);
create table item(id int primary key auto_increment,name varchar(10),price int ,num int ,category_id int);
-插入数据
insert into category values(null,‘家电’,null),(null,‘洗衣机’,1),(null,‘办公用品’,null),(null,‘必’,3);
insert into item values(null,‘海尔洗衣机’,2300,38,2),(null,'晨光圆珠笔’5,100,4);
#####事务
事务（transaction）是数据库中执行统一业务多条SQL语句的工作单元，事务可以
保证多条SQL语句全部执行成功或全部执行失败。

验证事务
1.创建表
create table user(id int primary key auto_ncrement,name varchar(10),money int,state varchar(5));
2.插入数据
insert into user values(null,‘李雷’,5000,‘正常’),(null,‘韩梅梅’,50,‘正常’),(null,‘Lucy’,10,‘冻结’);
3.给李雷转账SQL
update user set money=4000 where id=1 and state=‘正常’;
update user set money=1010 where id=3 and state=‘正常’;

添加事务保护
-开启事务
begin;
回滚事务：因为一条成功一条失败所以不能把内存中的改变提交到磁盘上去，所以需要执行
回滚命令。执行之前可以打开另外一个窗口数据库查看数据库中的数据是否发生改变
（变的是内存中的数据，数据库里面的数据并没有发生改变）
rollback；
-转账成功执行流程
1.开启事务
begin;
2.让李雷-1000
update user set money=3000 where id=1 and state=‘正常’;
3.让韩梅梅+1000
update user set money=1050 where id=2 and state=‘正常’;
4.提交事务
commit;
和事务相关的SQL
１.begin:开启事务
2.rollback：回滚事务 把内存中的改动清除
3.commit：把内存中的改动一次性的提交到磁盘
第二种开启事务的方式:
数据库中事务默认是自动提交的
1.查看数据库中自动提交的状态
show variables like ‘%autocommit%’;
2.修改自动提交的状态 0关闭 1开启
set autocommit=0;

—savepoint :保存回滚点
格式：savepoint s1;
rollback to s1;
####SQL 分类
####DDL data Definition Language
-数据定义语言
包括：create ，alter，drop，truncate
不支持事务

####DML Data Manipulation language
-数据操作语言
包括：insert delete update select(DQL)
支持事务
####DQL Data Query Language
-数据查询语言
-只包括select
###TCL Transaction Control Language
-事务控制语言
包括：begin,rollback, commit，savepoint xxx,rollback to xxx;
####DCL Data Control Language 数据控制语言
用于分配用户权限相关的SQL
数据库的数据类型
主要包括以下五大类
整数类型：BIT ，BOOL，TINY INT,SMALL INT,MEDIUM INT, INT ,BIG INT
浮点数类型：FLOAT,DOUBLE, DECIMAL
字符串类型：CHAR, VARCAR , TINY BLOB,BLOB,MEDIUM BLOB ,LONG BLOB;
日期类型：Date,DateTime,TimeStamp,Time,Year
其他类型：Binary,varbinary ,enum , set,geometry,point,MultiPoint,LineString,Polygon,GeometryCollection;
1.bigint(m)对应Java中的long,m代表现实长度，需要结合zerofill使用
create table t_int(id int,age int(8),zerofill)
2.浮点数：double(m,d)m代表总长度，d代表小数长度decimal超高精度小数
3.字符串 char(m):固定长度
varchar(可变长度)
可变长度更节省空间，固定长度执行效率高
text 可变长度 最大65535；
日期：date:只能保存年月日
time：只能保存时分秒
datetime:保存年月日时分秒，最大值9999-12-31默认值null
timestampe:保存年月日时分秒，最大位2038-1-19，默认值为当前时间

create table t_date (d1 date,d2 time,d3datetime,d4 timestamp);
insert into t_date values(‘2018-11-15’,‘16:58:33’,null,null);

######导入*.sql数据到数据库
window系统
source d:/tables.sql;
Linux系统：
source /桌面/tables.sql;

#####is null 和 is not null
-查询没有上级领导的员工编号，姓名，工资
select empno,ename,sal,from emp where mgr is null;
2.查询emp中没有奖金comm的员工姓名，工资，奖金
select ename,sal,comm from enp where comm is null;
3.查询emp表中有奖金的员工信息
select * from emp where comm is not null and comm>0;
#####别名
-把查询到的员工姓名enamel 改成 名字
select ename as ‘名字’ from emp;
select ename as 名字 from emp;
select ename 名字 from emp;
####去重
1.查询emp表中出现的所有职位job
select distinct job from emp;//去重
####比较运算符>,<,>=,<=,=,!=和<>
#####and和or
-and和Java中的&&效果一样
or 和Java中的||效果一样
-使用in:
select * from emp where sal in(5000,1500,3000);
select * from emp where sal not in(5000,1500,3000);
–between and
select * from emp where sal between 1000 and 2000;
select * from emp where sal not between 1000 and 2000;
####模糊查询
-_：代表单个未知字符
-%：代表0或多个未知字符
select * from t_item where title like ‘%记事本%’;
#####分页查询 limit
格式：limit 跳过的条数，请求的数量
如：请求第一页20条数据 limit 0,20;
请求第三页十条数据 limit 20,10;
公式：limit(页数-1)*每页数量，每页数量
如：
select * from t_item order by price limit 7,7;
select name,sal from emp order by sal desc limit 6,3;

######数值计算+ - * / %
例如：
select ename,sal, sal*5 年终奖 from emp;
select ‘helloworld’;
1.获取当前时间now()
select now();
2获取当前日期curdate();
select curdate();
3.获取当前时间curtime();
select curtime();
4.从年月日时分秒中提取年月日 和时分秒
select date(now());
select time(now());
5.从年月日时分秒中提取时间分量 extract(year from now())
select extract(year from now());
日期格式化
格式：
%Y四位年 %y两位年
%m两位月 %c一位月
%d日
%H24小时 %h12小时
%i 分
%s 秒
-date_format(时间，格式);

-把非标准格式的时间转回默认格式 str_to_date()
select str_to_date(‘14.08.2018 08:00:00’,’%d.%m.%Y %H:%i:%s’);
####ifnull(x,y)函数
age=if(x,y)如果x的值为null则age=y 如果x值不为null则age=x;

######聚合函数
-聚合函数用于多行数据进行统计，平均数，最大值，最小值，求和，统计数量
平均值；avg(字段名称)
1.查询所有员工的平均工资
select avg(sal) from emp;
最大值：max(字段名);
1.查询所有员工中的最高工资
select max(sal) from emp;
最小值：min（字段名）；
select max(sal) from emp;
求和：sum(字段名)；
select sum(sal) from emp;
统计数量 count(字段名)不统计null
select count(num) from emp;
select count(*) from emp;
字符串相关
1.字符串拼接concat(‘aa’,‘bb’);
2.获取字符串长度 char_length(‘abc’)
select ename,char_length(ename) from emp;
3.获取字符串在另外一个字符串出现的位置
-格式1：instr(str,substr);
格式2：locate（substr,str）；
4插入字符串 insert(str,start,length,newstr);
select insert(‘abcdefg’,3,2,‘m’);结果abmefg
5.转大小写
格式：upper(str) lower(str);
select upper(‘nba’);
6.取出两端的空白 trim(str);
7.截取字符串
-left(str,num);
select left(‘adsdasd’,2);
-right(str,num);
substring(str,start,?end);
8.重复reperat
select repeat(‘ab’,2);
9.替换replace
select replace(‘abcdefg’,‘c’,‘x’);

10.反转 reverse
select reverse(‘abc’);