MySQL常用命令1

SQL分类

DDL

数据库

• 查询所有数据库

show databases;

• 查询当前数据库

select DATABASE();

• 创建数据库

create DATABASE [if not exists] db_name [Default charset 字符集][COLLATE 排序规则]

• 删除数据库

drop database [if exists] db_name;

• 使用数据库

use db_name;

数据库中的表

• 查询当前数据库所有表

show tables;

• 查询表结构

desc table_name;

• 查询指定表的建表语句

show create table table_name;

• 表操作

create table table_name(
	字段1 字段1类型[comment 字段1注释],
	字段2 字段2类型[comment 字段2注释],
	.......
	字段n 字段n类型[comment 字段n注释]
)[comment表注释];

• 数据类型
  
  
  

• 添加字段

alter table table_name add 字段名 类型(长度)[comment 注释][约束];

• 修改数据类型

alter table table_name modify 字段名 新数据类型(长度);

• 修改字段名和字段类型

alter table table_name CHANGE 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

• 删除字段

alter table table_name drop 字段名;

• 修改表名

alter table table_name rename to 新表名;

• 删除表

drop table [if exists] 表名;

• 删除指定表，并重新创建该表

truncate table table_name;

DML

• 给指定字段添加数据

insert into table_name(字段名1,字段名2)values(值1,值2);

• 给全部字段添加数据

insert into table_name values(值1,值2，..);

• 批量添加数据

insert into table_name(字段名1,字段名2)values(值1,值2),(值1,值2),(值1,值2);
insert into table_name values(值1,值2，..),(值1,值2，..);
#字符串和日期类型数据应该包含在引号中
#插入的数据大小应该在字段的规定范围内

• 修改数据

update table_name set 字段名1=值1,字段名2=值2,...[where 条件];

• 删除数据

delete from table_name [where 条件];
# delete语句不能删除某一个字段可以用update

DQL

• 基本语法

select 字段列表 from 表名 where 条件列表;

• 聚合函数

# 将一列数据作为一个整体，进行纵向计算
# count : 统计数量
# max : 最大值
# min : 最小值
# avg : 平均值
# sum : 求和
select 聚合函数(字段) from  table_name;
# null值不参数所有聚合函数运算

• 分组查询

select 字段列表 from 表名 [where 条件] group by 字段名[having 分组过滤后条件];

where 与 having 的区别

1.执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
2.判断条件不同：where不能对聚合函数进行判断，having可以。
3.执行顺序：where>聚合函数>having
4.分组之后，查询的字段一般是聚合函数和分组字段，查询其它字段不合法也没有任何意义

• 排序查询

select 字段列表 from table_name order  by 字段1 排序方式1 , 字段2 排序方式2; 
# asc升序(默认)desc降序

• 分页查询

select 字段列表 from 表名 limit 起始索引 , 查询记录数;
# 起始索引从0开始，起始索引=(查询页码-1)*每一页记录数
# 分页查询是数据库的方言
# 如果查询的是一页数据，起始索引可以省略，直接简写为limit 10;

DCL

DCL(Data Control Language),用来管理数据库用户、控制数据库访问权限。

• 查询用户

use mysql;
select * from user;

• 创建用户

create user '用户名'@'主机名' identified by '密码';

• 修改用户密码

alter user '用户名'@'主机名' indetified with mysql_native_password by '新密码';

• 删除用户

drop user '用户名'@'主机名';

• 权限控制
  

• 查询权限

show grants for '用户名'@'主机名';

• 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

• 撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

函数

• 字符串函数
  
• 数值函数
  
• 日期函数
  
• 流程函数
  

约束

• 外键约束

create table table_name(
	字段名 数据类型;
	[constraint] [外键名称] foreign key [外键字段名] references 主表(主表列名);
);
alter table table_name add constraint 外键名称 foreign key (外键字段名) references 主表(主表字段);

• 删除更新行为
  

alter table table_name add constraint 外键名称 foreign key (外键字段名) references 主表(主表字段)
on update cascade on delete cascade;

多表查询

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多(多对一)

案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键。

部门表

mysql> create table dept(
    -> id int primary key auto_increment comment '编号ID',
    -> name varchar(50) not null comment '部门名称'
    -> )comment '部门表';

员工信息表

mysql> create table emp(
    -> id int primary key auto_increment comment 'ID',
    -> name varchar(50) not null comment '姓名',
    -> age int default null comment '年龄',
    -> job varchar(20) default null comment '职位',
    -> salary float default null comment '薪资',
    -> entrydate date default null comment '入职时间',
    -> managerid int default null comment '直属领导ID',
    -> dept_id int default null comment '部门ID',
    -> constraint fk_deptid foreign key (dept_id) references dept(id)
    -> );

插入数据

mysql> insert into dept (name) values
    -> ('研发部'),
    -> ('市场部'),
    -> ('财务部'),
    -> ('销售部'),
    -> ('总经办'),
    -> ('人事部');
mysql> insert into emp values
    -> (null,'金庸',66,'总裁',20000.0,'2000-01-01',null,5),
    -> (null,'张无忌',20,'项目经理',12500.0,'2005-12-05',1,1),
    -> (null,'杨逍',33,'开发',8400.0,'2000-11-03',2,1),
    -> (null,'韦一笑',48,'开发',11000.0,'2002-02-05',2,1),
    -> (null,'常遇春',43,'开发',10500.0,'2004-09-07',3,1),
    -> (null,'小昭',19,'程序员鼓励师',6600.0,'2004-10-12',2,1),
    -> (null,'灭绝',60,'财务总监',8500.0,'2002-09-12',1,3),
    -> (null,'周芷若',19,'会计',48000.0,'2006-06-02',7,3),
    -> (null,'丁敏君',23,'出纳',5250.0,'2009-05-13',7,3),
    -> (null,'赵敏',20,'市场部总监',12500.0,'2004-10-12',1,2),
    -> (null,'鹿仗客',56,'职员',3750.0,'2006-10-03',10,2),
    -> (null,'鹤笔翁',19,'职员',3750.0,'2007-05-09',10,2),
    -> (null,'方东白',19,'职员',5500.0,'2009-02-12',10,2),
    -> (null,'张三丰',88,'销售总监',14000.0,'2004-10-12',1,4),
    -> (null,'俞莲舟',38,'销售',4600.0,'2004-10-12',14,4),
    -> (null,'宋远桥',40,'销售',4600.0,'2004-10-12',14,4),
    -> (null,'陈友谅',42,null,2000.0,'2011-10-12',1,null);

• 多对多

案例：学生与课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择。
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

学生表

mysql> create table student (
    -> id int auto_increment primary key comment '主键ID',
    -> name varchar(10) comment '课程名称',
    -> no varchar(10) comment '学号'
    -> )comment '学生表';

课程表

mysql> create table course(
    -> id int auto_increment primary key comment '主键ID',
    -> name varchar(10) comment '课程名称'
    -> )comment '课程表';

学生课程中间表

mysql> create table student_course(
    -> id int auto_increment primary key comment '主键',
    -> studentid int not null comment '学生ID',
    -> courseid int not null comment '课程ID',
    -> constraint fk_courseid foreign key (courseid) references course(id),
    -> constraint fk_studentid foreign key (studentid) references student(id)
    -> )comment '学生课程中间表';

插入数据

insert into student(name,no) values
 ('张三','2000100101'),
 ('李四','2000100102'),
 ('王五','2000100103'),
 ('小六','2000100104');
insert into course (name)values 
('Java'),('PHP'),('MySQL'),('Hadoop');
insert into student_course(studentid,courseid) values
(1,1),(1,2),(1,3),(2,2),(2,3),(3,4);

• 一对一

案例：用户与用户详情的关系
一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其它详情字段放在另一张表中，以提升操作效率。(用户基本信息表tb_user，用户教育信息表tb_user_edu)
实现：在任意一方加入外键，关联另外一方的主键并设置外键为唯一的(UNIQUE)

用户基本信息表

mysql> create table tb_user(
    -> id int auto_increment primary key comment '主键ID',
    -> name varchar(10) comment '姓名',
    -> age int comment '年龄',
    -> gender char(1) comment '1:男 2:女',
    -> phone char(11) comment '手机号'
    -> )comment '用户基本信息表';

用户受教育信息表

mysql> create table tb_user_edu(
    -> id int auto_increment primary key comment '主键ID',
    -> degree varchar(20) comment '学历'，
    -> major varchar(50) comment '专业',
    -> primaryschool varchar(50) comment '小学',
    -> middleschool varchar(50) comment '中学',
    -> university varchar(50) comment '大学',
    -> userid int unique comment '用户ID',
    -> constraint fk_userid foreign key (userid) references tb_user(id)
    -> )comment '用户教育信息表';

插入数据

mysql> insert into tb_user values(null,'黄渤',45,'1',18800001111),
    -> (null,'冰冰',35,'2','18800001122'),
    -> (null,'码云',55,'1','18800001133'),
    -> (null,'李云',50,'1','18800001144');
mysql> insert into tb_user_edu values(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
    -> (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
    -> (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
    -> (null,'本科','应用数学','阳泉区第一小学','阳泉区第一中学','清华大学',4);

多表查询概述

1. 概述：指从多张表中查询数据
2. 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A和B的所有组合情况(在多表查询时需要消除无效的笛卡尔积)。

-- 消除无效的笛卡尔积
SELECT * from emp,dept where emp.dept_id = dept.id ORDER BY emp.id;

• 多表查询分类
  

内连接

• 隐式内连接

select 字段列表 from 表1,表2 where 条件....;

• 显示内连接

select 字段列表 from 表1 [INNER] JOIN 表2 on 连接条件...;

查询每一个员工的姓名，及关联的部门的名称(隐式内连接实现)

select emp.name '员工姓名',dept.name '部门名称' from emp,dept where emp.dept_id = dept.id;

查询每一个员工的姓名，及关联的部门的名称(显示内连接实现)

select emp.name '员工姓名',dept.name '部门名称' from emp inner join dept on emp.dept_id = dept.id;

当表名很长的时候给表起一个别名

select e.name,d.name from emp e,dept d where e.dept_id = d.id;

外连接

• 左外连接

select 字段列表 from 表 LEFT [OUTER] JOIN 表2 on 条件 ....;

• 右外连接

select 字段列表 from 表 RIGHT [OUTER] JOIN 表2 on 条件 ....;

查询emp表的所有数据，和对应的部门信息(左外连接)

select e.*,d.name from emp e LEFT JOIN dept d on  e.dept_id = d.id;

查询dept表的所有数据，和对应的员工信息(右外连接)

select e.*,d.name from emp e RIGHT JOIN dept d on e.dept_id = d.id;

自连接

select 字段列表 from 表A 别名A [inner|left|right]join 表B 别名B on 条件

查询员工及其所属领导的名字

-- e表是员工表 ee是领导表--
select e.name '员工',ee.name '领导' from emp e inner JOIN emp ee on e.managerid = ee.id;

查询所有员工emp及其领导的名字，如果员工没有员工，也要查询。

select e.name '员工',ee.name '领导' from emp e left JOIN emp ee on e.managerid = ee.id;

联合查询

select 字段列表 from 表A ... union[ALL] select 字段列表 from 表B.....

将薪资低于5000的员工，和年龄大于50岁的员工全部查出来

select name from emp where salary<5000 union all --会出现重复的结果--
select name from emp where age>50;

select name from emp where salary<5000 union --不会出现重复的结果--
select name from emp where age>50;

• 对于联合查询的多张表的列数必须把保持一致，字段类型也需要保持一致。

子查询

概念：SQL语句中嵌套select语句，称为嵌套查询(子查询)

select * from t1 where column1 = (select column1 from t2);

子查询外部的语句可以是insert、update、delete、select中的任何一个。

标量子查询

子查询返回的结果为单个值(数字、字符串、日期等)
常用的操作符：=、<>、>、>=、<、<=

查询“销售部”的所有员工信息

select * from emp where dept_id = (select id from dept where name = "销售部" );

查询在“方东白”入职之后的员工信息

select * from emp where entrydate > (select entrydate from emp where name="方东白");

列子查询

子查询结果是一列(可以是多行)。
常用的操作符：IN、NOT IN、ANY、SOME、ALL
IN：在指定的集合范围之内，多选一
NOT IN：不在指定的集合范围内
ANY：子查询返回列表中，有任意一个满足即可
SOME：与ANY等同
ALL：子查询返回列表的所有值都必须满足。

查询“销售部”和“市场部”的所有员工信息

select * from emp where dept_id in (select id from dept where name in ('销售部','市场部'));

查询比财务部所有人工资都高的员工信息

select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = "财务部"));

查询比研发部其中任意一人工资高的员工信息

select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = "研发部"));

行子查询

子查询返回的结果是一行(可以是多列)。
常用的操作符：=、<>、in、not in。

查询与“张无忌”的薪资及直属领导相同的员工信息。

select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

表子查询

子查询结果是多行多列。
常用的操作符是in。
表子查询经常出现在from之后，表子查询返回的结果作为一张临时表再和其他表进行联查操作。

查询与“鹿仗客”、"宋远桥"的职位和薪资相同的员工信息。

--子查询返回一张临时表--
select * from emp inner join (select job,salary from emp where name in('鹿仗客','宋远桥')) d where emp.job = d.job and emp.salary = d.salary;

简单写法

select * from emp where (job,salary) in (select job,salary from emp where name='鹿仗客' or name='宋远桥');

查询入职日期是“2006-01-01”之后的员工信息，及其部门信息。

select e.*,d.name from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;
select d.name,e.* from dept d right join (select * from emp where entrydate>'2006-01-01') e on e.dept_id = d.id;

多表查询案例

一共需要三个表emp、dept、salgrade

mysql> create table salgrade(
    -> grade int,
    -> losal int,
    -> hisal int
    -> )comment '薪资等级表';
mysql> insert into salgrade values (1,0,3000),
    -> (2,3001,5000),
    -> (3,5001,8000),
    -> (4,8001,10000),
    -> (5,10001,15000),
    -> (6,15001,20000),
    -> (7,20001,25000),
    -> (8,25001,30000);

1-3 内连接

1. 查询员工的姓名，年龄，职位，部门信息(隐式内连接)

select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id = d.id;

2. 查询年龄小于30岁的员工的姓名，年龄，职位、部门信息(显示内连接)

select e.name,e.age,e.job,d.name from (emp e inner join dept d on e.dept_id = d.id) where e.age<30;
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id = d.id where e.age<30;

3. 查询拥有员工的部门ID、部门名称。

select distinct d.* from dept d inner join emp e on d.id = e.dept_id;

4. 查询所有年龄大于40岁的员工，及其所属的部门名称；如果没有分配部门，也需要展示出来

select e.*,d.name from emp e left join dept d on e.dept_id = d.id where e.age>40;

5. 查询所有员工的工资等级
   涉及到的表emp、salgrade
   连接条件：e.salary >= s.losal && e.salary <= s.hisal

select e.* ,s.grade from emp e ,salgrade s where s.losal <= e.salary and e.salary<=s.hisal;
select e.* ,s.grade from emp e ,salgrade s where e.salary between s.losal and s.hisal;

6. 查询“研发部”所有员工的信息及工资等级

--先筛选出研发部的部门信息，再去匹配等级--
select e.*,s.grade from (select * from emp where emp.dept_id = (select id from dept where name='研发部')) e join salgrade s on e.salary between s.losal and s.hisal;
--先匹配等级，在筛选出研发部的信息--
select e.*,s.grade from (emp e inner join salgrade s on e.salary between s.losal and s.hisal) where e.dept_id=(select id from dept where name = '研发部');
--三张表一起连接--
select e.* , s.grade from emp e,dept d,salgrade s where e.dept_id=d.id and (e.salary between  s.losal and s.hisal) and d.name="研发部";

7. 查询“研发部”员工的平均工资

select avg(salary) '研发部平均工资' from emp e group by dept_id having dept_id = (select id from dept where name ='研发部');
select avg(e.salary) from emp e,dept d where e.dept_id = d.id and d.name = '研发部';

8. 查询工资比“灭绝”高的员工信息

select * from emp where salary > (select salary from emp where name = '灭绝');

9. 查询比平均薪资高的员工信息

select * from emp where salary > (select avg(salary) from emp);

10. 查询低于本部门平均工资的员工信息

select e.* from emp e where e.salary < (select avg(e1.salary) from emp e1 group by e1.dept_id having e1.dept_id = e.dept_id );

11. 查询所有的部门信息，并统计部门的人数

select d.*,count(e.dept_id) from dept d left join emp e on d.id=e.dept_id group by e.dept_id;
select d.id,d.name,(select count(*) from emp e where e.dept_id = d.id) from dept d;

12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称

select s.name '学生姓名',s.no '学生学号',c.name '课程名称' from student s,course c,student_course sc where sc.studentid=s.id and sc.courseid=c.id;

事务

事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失效。

事务操作

CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '编号ID',
  name varchar(10) DEFAULT NULL COMMENT '姓名',
  money int(11) DEFAULT NULL COMMENT '余额',
  PRIMARY KEY (`id`)
) COMMENT='账户表'
insert into account (name,money) values('张三',2000),('李四',2000);

模拟转账操作

• 查询张三的余额
• 将张三账户的余额减1000
• 将李四账户的余额加1000

--查看事务提交方式--
select @@autocommit;--或者--
show variables like 'autocommit';
--设置事务提交方式--
set @@autocommit=0;
----

提交事务

commit;

回滚事务

rollback;

在不设置手动提交的前提下开启事务
开启事务

begin;

查看mysql文件在磁盘上的存储位置

show variables like '%datadir%';

事务隔离级别

读未提交 read-uncommitted
读已提交read-committed
可重复读repeatable-read
串行化serializable
查看事务隔离级别

select @@tx_isolation;
select @@global.tx_isolation;

设置隔离级别

set session tx_isolation= 隔离级别["read-committed"];
set global  tx_isolation=隔离级别["read-committed"];

mysql版本为5.5.6