MYSQL入门
Mysql列/数据类型
启动登录
net start mysql;//启动
mysql -u 用户 -p密码
quit//退出
数据库操作
create database test; //创建数据库 为避免关键字 可用反引号 `int`
create database test character set utf8;//创建使用utf8字符集的数据库
create database test character set utf8 collate utf8_bin;//带校验规则 utf8_bin(区分大小写) 默认 utf8_general_ci(不区分大小写)
show databases;//显示数据库
show create database test;//查看创建的数据库定义信息
show database status from test;
drop database test; //删除数据库
mysqldump -u 用户 -p密码 -B 数据库123 >d:\\xx.sql;//数据库拷贝 DOS下 不要登录
source d:\\xx.sql; 登录 恢复数据库
mysqldump -u 用户 -p密码 数据库 表1 表2 表N >d:\\xx.sql;//备份数据库的表
表操作
create table emp(id int,name varchar(32),sex char(1),birthday date,entrydate datetime,job varchar(32),salary double,resume text)charset utf8 collate utf8_bin engine innodb; //创建表
insert into emp values(1,'悟空','男','1000-01-01','1500-10-1','大闹天宫',3000,'花果山水帘洞美猴王');//添加表数据
alter table emp add image varchar(64) Not null default '' after resume; //表添加1列
desc emp; //显示表结构
alter table emp modify job varchar(60) not null default '';//修改表的一列
alter table emp drop sex;//删除表的一列
rename table emp to employee;//修改表名
alter table employee character set utf8;//修改表的字符集
alter table employee change name user_name varchar(32) not null default '';//修改表某列名
CRUD
## CRUD
## CRUD
create table goods(id int ,goods_name varchar(10) ,price double);
insert into goods (id,goods_name,price) values(1,'matebook14',5700);
insert into goods (id,goods_name,price) values(2,'matebookx',9700);
insert into goods (id,goods_name,price) values('2','matebookx',9700);//字符串能转换成paseint 的不报错
insert into goods (id,goods_name,price) values(3,'honorbookx',4700),(4,'macbook',12999);//插入多条用,分隔
update goods set price =5000;//修改表的所有数据
update goods set price = 9999 where id = 4;
update goods set price = price + 1000 where goods_name = 'honorbookx';
update goods set id=6,goods_name ='minote' where price=7000;//修改多条,
delete from goods where goods_name ='matebookx';//删除某条记录
SELECT
#SELECT
create table student(
-> id int not null default 1,
-> name varchar(20) not null default '',
-> chinese float not null default 0.0,
-> english float not null default 0.0,
-> math float not null default 0.0
-> );
select * from student;//查询所有
select distinct name, english from student;//distinct 去重
select name ,(chinese + english +math) from student;//统计总分
select name ,(chinese + english +math) as total from student; //as 取别名
select name id ,(chinese + english +math) total from student;//as 可以省略 要有空格
select name id from student;//重命名
where 条件
# select语句 where常使用
#比较运算符 between ..and..在某一区间
#in(set) 在in列表中的值
#like notlike 模糊查询
#is null 是否为空
# and 多个条件成立 or 任一成立 not 不成立
select * from student where english between 80 and 90;//闭区间
select * from student where english between 80 and 90 and name like '赵%'; //姓赵
select * from student where english in(80,81,84,85);//in 集合
order by 排序
##select order by 排序 asc 升序默认 desc降序 ----order by+列 没有可以取别名
select * from student order by english desc;
select name,(chinese + english +math) total from student order by total desc;//重命名total排序
count 统计
#统计 count(*) count(列)满足条件的某列个数 会排除null
select count(*) from student;
select count(*) from student where math>80;
sum avg max min合计
select sum(english),sum(chinese),sum(math) from student;
select sum(english + math + chinese) from student;
select sum(english)/count(*) from student;//平均分
select avg(english) from student;
select max(english) from student;
select min(english) from student;
select min(english) min from student;//取个别名
分组统计 group by
select avg(sal),max(sal),min(sal),deptno from emp group by deptno;
select avg(sal),max(sal),min(sal),deptno,job from emp group by deptno,job;
### group by having...过滤
select avg(sal) avgsal, deptno from emp group by deptno having avgsal<2000;
字符串函数
select charset(ename) from emp;//返回字符串集
select concat(ename,'的',job) from emp;//多列拼接
select instr('helloworld','world') from dual;//world 在helloworld的位置 返回6
select ucase(ename) from emp;
select lcase(ename) from emp;
select left(ename,2) from emp;
select right(ename,2) from emp;
select length(ename) from emp;sel
select ename, replace(job,'MANAGE','经理') from emp;
select substring(ename,1,2) from emp;
数学函数
日期函数
select unix_timestamp() from dual;//时间戳
select from_unixtime(1670466494,'%Y-%m-%d') from dual;
加密函数
select user() from dual;//查询用户
select datebase() from dual;//当前使用的数据库
select md5('1234') from dual;//md5加密
password('1234')
流程控制函数
select if(true,'hello','world') from dual;//为真 返回前一个
select ifnull(null,'hello') from dual;//为空返回
select ename, (select case when job='CLERK' then '职员' when job = 'MANAGER' then '经理' when job = 'SALESMAN' then '销售' else job end) job from emp;
查询–加强
mysql> select * from emp where hiredate>'1992-01-01';//时间可以直接比较 注意格式即可
select * from emp where ename like '__O%';//第三个字符为大写O
select * from emp where mgr is null; //为空
select * from emp order by deptno,sal desc;//多个排序 用,
分页查询
limit 每页显示记录*(页数-1),每页显示记录
分组增强
select count(*) ,avg(sal),job from emp group by job;//按岗位分组
select count(*)from emp where comm is null;//统计为空
select count(comm)from emp ;//统计为空
select count(if(comm is null,1,null)) from emp;//统计为空
select count(distinct mgr) from emp;//去重统计
查询顺序
select deptno, avg(sal) avgsal from emp group by deptno having avgsal >1000 order by avgsal desc limit 0,2;
多表查询
select * from table1,table2;//第一张表取出一行 和第二张表的每一行拼接 返回
//多表查询默认返回的结果 为笛卡尔集
select ename,sal,dname from emp,dept where emp.deptno = dept.deptno;
//多表查询条件数不能少于表数-1
select emp.deptno,ename,sal from emp,dept where emp.deptno = dept.deptno and dept.deptno =10;//and
select emp.deptno,ename,sal from emp,dept where emp.deptno = dept.deptno having deptno =10;//having
select * from emp,salgrade where sal between losal and hisal;
自连接
//同一张表当成两张表使用 需要取别名
select emp.ename,emp1.ename from emp,emp emp1 where emp.mgr =emp1.empno;//同表关联
子查询
单行子查询
//查询某人所在部门的全体成员
1.得到所在部门
select deptno from emp where ename ='SMITH';
2.查询得到部门的全部成员
select ename from emp where deptno=(select deptno from emp where ename ='SMITH');
多行子查询
select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno = 10)and deptno !=10; // != 或者<>
select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno = 10)having deptno !=10;
子查询临时表
1.查询到的表
select max(sal), deptno from emp group by deptno;
2.查询到的表当临时表用
select emp.deptno, maxsal from emp,(select max(sal) maxsal, deptno from emp group by deptno) temp where emp.sal =temp.maxsal and emp.deptno = temp.deptno;
all any
select * from emp where sal >all(select sal from emp where deptno =30);//>max
select * from emp where sal >any(select sal from emp where deptno =30);//>min
多列子查询
select emp.ename,emp.deptno,emp.job from emp,(select deptno ,job from emp where ename ='ALLEN') temp where emp.deptno =temp.deptno and emp.job = temp.job and emp.ename !='ALLEN';// ⇒ (字段1,字段2...)=( select 字段1,字段2.. from)
等价于
select ename,deptno,job from emp where (deptno,job)=(select deptno,job from emp where ename ='ALLEN') and ename !='ALLEN';
表复制
insert into table01 (select * from table02);
create table table01 like table02;//table02 表结构复制到table01
去重
复制结构相同的表 insert into newtable select distinct * from oldTable;
删除oldTable
重命名newTable
合并查询
union all 不去重
union 去重
两条select 语句合并
外连接
多表关联形成笛卡尔集,不显示不满条件的
select * from emp right join dept on emp.deptno=dept.deptno;
Mysql约束
primary key 主键
复合主键primary key(column1,column2,)
unique
该列不能重复,但是可以多个null(没加 not null)
外键 foreign
foreign key (column)references table(column)
check
check(条件) mysql8.0.16 开始是生效
自增长
primary key auto_increment
Mysql索引
create index empno_index on emp(empno);
优:形成索引的数据结构。查找速度大大提高。
缺:磁盘占用。insert delete update 对索引重新维护,影响速度。
索引类型
1.主键 primary key 主键索引
2.uinque 唯一索引
3.index 普通索引
4.fulltext 全文索引(一般不使用自带的)用 Solr ElasticSearch
索引使用
show indexes from table_name;//查看索引
show index from table_name;//查看索引
show keys from table_name;//查看索引
desc table_name;
create unique index index_name on table_name (column_name);//创建唯一索引
create index index_name on table_name (column_name);//创建普通索引1
alter table table_name add index index_name(column_name)//创建普通索引2
alter table table_name add primary key (column_name)//添加主键索引
drop index (index_name) on table_name;//删除索引
alter table table_name drop primary key;//删除主键索引
//修改索引 (先删除后添加)
Mysql 事务
start transaction;//开始事务
set autocommit = off;//开始事务
savepoint a;//
//dml insert update delete
savepoint b;//
rollback to b;//回退到b 直接回退到a b会被删掉
rollback;//回退全部
不开启事务,dml操作默认commit,不能rollback
innodb 引擎支持事务,myisam 不支持
事务隔离级别
select @@tx_isolation;//查看当前会话隔离级别
select @@glob.tx_isolation//查看系统隔离级别
set session transaction isolation level read uncommitted;//设置当前会话隔离级别
set global transaction isolation level read uncommitted;//设置当前会话隔离级别
脏读:修改操作尚未提交
不可重复度:删除修改提交
幻读:插入提交
事务ACID特性
Mysql存储引擎
show engines;//查看所有引擎
alter table table_name engine = xxx;
引擎特点
视图
create view emp_view01 as select empno,ename,job,deptno from emp;//创建视图
Mysql 用户管理
create user '用户名' @ '允许登录位置' identified by '密码'//创建用户并指定密码
drop user '用户名' @ '允许登录位置'
create user 'admin'@'localhost' indentified by '1234';
set password =psaaword('12345');//修改自己密码
set password for 'admin'@'localhost' = password('1234');
用户权限
权限列表
grant 权限1,2 on 库.对象名 to '用户名'@'登录位置' [identified by 'password'];//identify 用户存在则修改密码 不存在创建
grant all//所有权限
revock 权限 on 库.对象名 from '用户名'@'登录位置';//回收权限
flush privileges;//刷新权限
细节