1、连接数据库
mysql -h ip地址 -P 端口号 -u 用户名 -p 密码
2、显示所有数据库
show databases;
3、创建数据库
create database 数据库名字 default charset=utf8;
4、使用数据库
use 数据库名字;
5、删除数据库
drop 数据库名字;
6、查询数据库下所有的表
use 数据库名;
show tables;
7、创建表
create table 表名(
列名 类型,
列名 类型,
列名 类型
);
8、常用的类型
数字 int,float,decimal
字符串 char,varchar,longtext
日期 date,datetime
9、删除表
drop table 表名
10、约束
1、主键约束
2、非空约束
3、唯一约束
4、默认约束
5、外键约束
/*外键表*/
create table student(
id int primary key,
name varchar(100) not null,
idcard char(18) unique,
addres varchar(100) default '郑州',
gid int not null,
foreign key(gid) references grade(id)
);
/*主键表*/
create table grade(
id int primary key,
name varchar(100) not null
);
11、描述表的信息
desc 表名
12、显示表的创建sql语句
show create table 表名
13、主键的生成策略
1、int 自动增长 auto_increment
2、字符串 uuid 了解
create table grade(
id int auto_increment primary key,
name varchar(100) not null
);
create table grade2(
id char(36) primary key,
name varchar(100) not null
);
insert into grade(name) values('a');
insert into grade(name) values('b');
insert into grade2(id,name) values(uuid(),'a');
insert into grade2(id,name) values(uuid(),'b');
14、基本的增删改查
create table student(
id int auto_increment primary key,
name varchar(100) not null,
sex char(1) not null,
address varchar(100) default '郑州',
phone varchar(11),
birthday date
);
insert into student(name,sex,address,phone,birthday) values('老王','男','开封','11111111111','1998-2-2');
select * from student;
select name,phone from student;
select name 姓名,phone 电话 from student;
delete from student where id = 3;
update student set address='开封' where id = 5;
update student set sex='女',address='曼谷' where id = 5;
UPDATE
student
SET
sex='女',address='曼谷'
WHERE
id = 5;
15、单表查询
/*1、查询所有EMP信息*/
select * from EMP;
/*2、查询所有job*/
select job from EMP;
/*3、去重:查询所有job*/
select distinct job from EMP;
/*4、去重:查询所有deptno,job的组合*/
select distinct deptno,job from EMP;
/*5、条件:查询工资大于2000的*/
select * from EMP where sal > 2000;
/*6、条件:查询工资大于2000的并且部门编号是10的*/
select * from EMP where sal > 2000 and deptno = 10;
/*7、条件:查询工资2000-3000之间的*/
select * from EMP where sal >= 2000 and sal <= 3000;
select * from EMP where sal between 2000 and 3000;
/*8、模糊:查询以S开头的员工信息*/
select * from EMP where ename like 'S%';
/*9、模糊:查询含有S的员工信息*/
select * from EMP where ename like '%S%';
/*10、模糊:查询含第三个字符是R的员工信息*/
select * from EMP where ename like '__R%';
/*11、范围:查询部门编号是10,20的员工信息*/
select * from EMP where (deptno = 10) or (deptno=20);
select * from EMP where deptno in (10,20);
/*12、空:查询没有有奖金的员工信息*/
select * from EMP where comm is null;
/*13、空:查询奖金大于400的员工信息*/
select * from EMP where comm > 400;
/*14、空:查询员工的编号,年薪 null参与的运算,结果还是null*/
select empno 编号,(sal+ifnull(comm,0))*12 年薪 from EMP;
/*15、聚合:统计员工的数量*/
select count(*) from EMP;
/*16、聚合:统计有奖金员工的数量*/
select count(*) from EMP where comm is not null;
select count(comm) from EMP;
/*17、聚合:最高的工资,最低的工资,平均工资,工资的总和*/
select max(sal),min(sal),avg(sal),sum(sal) from EMP;
/*
分组需要注意:
1、分组之后只能查询两种
1、被分组的列
2、聚合函数
2、数据过滤
1、过滤的数据是分组之前,where
2、过滤的数据是分组之后,having
3、关键词的顺序
select
from
where 分组之前的过滤
group by
having 分组之后的过滤
limit
*/
/*18、分组:每个部门的平均工资~~~*/
select deptno,avg(sal)
from EMP
group by deptno;
/*19、分组:每个部门员工工资高于1000的平均工资*/
select deptno,avg(sal)
from EMP
where sal > 1000
group by deptno;
/*20、分组:每个部门员工工资高于1000的平均工资,平均工资高于2000*/
select deptno,avg(sal)
from EMP
where sal > 1000
group by deptno
having avg(sal)>2000;
select deptno,avg(sal) avg_sal
from EMP
where sal > 1000
group by deptno
having avg_sal>2000;
/*21、分组:每个部门每个工种的最高工资*/
select deptno,job,max(sal)
from EMP
group by deptno,job;
/*22、排序:查询所有员工信息,按照工资排序*/
select * from EMP
order by sal asc;
select * from EMP
order by sal desc;
/*23、排序:查询所有员工信息,按照按照部门正序,按照工资倒序*/
select * from EMP
order by deptno,sal desc;
/*24、分页*/
select * from EMP
order by empno;
select * from EMP
order by empno
limit 3;
select * from EMP
order by empno
limit 2,3;
/*25、分页:按照编号排序,每页显示2(page_size)条,查第5(page_now)页信息*/
/*
0 1
2 3
4 5
...
limit (page_now-1)*page_size,page_size
*/
select * from EMP
order by empno
limit 8,2;
16、三种映射关系
/*
1:1
任选一个表当作主键表,另一个表当作外键表
并且外键列必须唯一
*/
drop table if exists husband;
drop table if exists wife;
create table wife(
id int auto_increment primary key,
name varchar(100)
);
create table husband(
id int auto_increment primary key,
name varchar(100),
wid int unique,
foreign key(wid) references wife(id)
);
/*
1:M
设置外键
*/
create table dept(
id int auto_increment primary key,
name varchar(100)
);
create table emp(
id int auto_increment primary key,
name varchar(100),
did int,
foreign key(did) references dept(id) on delete cascade
);
/*
M:N
创建中间表
一般中间表也有用
*/
create table student(
id int auto_increment primary key,
name varchar(100)
);
create table subject(
id int auto_increment primary key,
name varchar(100)
);
create table student_subject(
id int auto_increment primary key,
stuid int,
subid int,
foreign key(stuid) references student(id),
foreign key(subid) references subject(id)
);
17、关联查询
/*1、内连接*/
select * from DEPT,EMP
where DEPT.DEPTNO = EMP.DEPTNO;
select
EMP.EMPNO,EMP.ENAME,DEPT.DNAME
from
DEPT
inner join
EMP
on
DEPT.DEPTNO = EMP.DEPTNO;
/*查询员工的编号,姓名,所在部门的名字*/
select
EMP.EMPNO,EMP.ENAME,DEPT.DNAME
from
DEPT,EMP
where
DEPT.DEPTNO = EMP.DEPTNO;
select
EMP.EMPNO,EMP.ENAME,DEPT.DNAME
from
DEPT,EMP
where
DEPT.DEPTNO = EMP.DEPTNO
and
DEPT.DEPTNO = 10;
/*外连接*/
select
t2.EMPNO,t2.ENAME,t1.DEPTNO,t1.DNAME
from
DEPT t1
left join
EMP t2
on
t1.DEPTNO = t2.DEPTNO;
/*创建自关联的表*/
create table EMP2(
id int auto_increment primary key,
name varchar(100),
mgr int foreign key(mgr) references EMP2(id)
);
/*查询员工的编号,姓名,上级名字*/
select t1.empno,t1.ename,t2.ename
from EMP t1,EMP t2
where t1.mgr = t2.empno
select t1.empno 员工的编号 ,t1.ename 员工的姓名,t2.ename 上级的姓名
from EMP t1 left join EMP t2
on t1.mgr = t2.empno;
小练习
/*1、一个表能完成不?*/
1、查询emp中最高薪水人的名字
1、查询最高的薪水
select max(sal) from EMP;
2、将1作为条件
select ename from EMP
where sal = (select max(sal) from EMP);
2、查询每个部门中的最高薪水人的名字和所在的部门编号
1、各个部门的最高薪水
select max(sal),deptno from EMP
group by deptno;
2、将1的结果作为一个新的表,联表查询
select t1.ename,t1.deptno
from
EMP t1
inner join
(select max(sal) max_sal,deptno from EMP group by deptno) t2
on t1.deptno = t2.deptno and sal = t2.max_sal;
3、查询薪水在平均薪水之上的雇员的名字
select ename from EMP where sal >(select avg(sal) from EMP);
4、查询雇员的名字和所在部门的名字
select t1.ename,t2.dname
from EMP t1,DEPT t2
where t1.deptno = t2.deptno;
5、查询薪水在在本部门平均薪水之上的雇员的名字
select t1.ename,t1.deptno
from
EMP t1
inner join
(select avg(sal) avg_sal,deptno from EMP group by deptno) t2
on t1.deptno = t2.deptno and sal > t2.avg_sal;
6、查询每个员工的薪水的等级,员工的姓名
select * from EMP;
select * from SALGRADE;
select t1.ename,t2.grade
from EMP t1,SALGRADE t2
where t1.sal between t2.losal and t2.hisal;
7、查询每个部门的平均薪水的等级,部门的编号
1、各个部门的平均薪水
select avg(sal),deptno from EMP group by deptno;
2、将1的结果当作一个表
select
t1.grade,t2.deptno
from
SALGRADE t1,(select avg(sal) avg_sal,deptno from EMP group by deptno) t2
where
t2.avg_sal between t1.losal and t1.hisal
8、查询雇员的名字,所在部门的名字,工资的等级
select t1.ename,t2.dname,t3.grade
from
EMP t1,DEPT t2,SALGRADE t3
where
t1.deptno = t2.deptno
and
t1.sal between t3.losal and t3.hisal;
select t1.ename,t2.dname,t3.grade
from
EMP t1
inner join
DEPT t2
on
t1.deptno = t2.deptno
inner join
SALGRADE t3
on
t1.sal between t3.losal and t3.hisal;
9、查询雇员的名字和其经理的名字
select employee.ename,employer.ename
from EMP employer,EMP employee
where employee.mgr = employer.empno;
10、查询雇员中是经理人的名字
select * from EMP;
1、经理上的编号
select distinct mgr from EMP;
2、将1当作条件
select
ename
from
EMP
where
empno in(select distinct mgr from EMP);
11、查询平均薪水最高的部门的编号和名称
1、每个部门的平均薪水
select avg(sal) avg_sal,deptno from EMP group by deptno;
2、求1表中的最高平均薪水
select max(t.avg_sal) from (select avg(sal) avg_sal,deptno from EMP group by deptno) t
3、将2的结果当作条件
select t1.deptno
from (select avg(sal) avg_sal,deptno from EMP group by deptno) t1
where t1.avg_sal = (select max(t2.avg_sal) from (select avg(sal) avg_sal,deptno from EMP group by deptno) t2)
4、将3作为条件
select t.deptno,t.dname
from DEPT t
where deptno in (
select t1.deptno
from (select avg(sal) avg_sal,deptno from EMP group by deptno) t1
where t1.avg_sal = (select max(t2.avg_sal) from (select avg(sal) avg_sal,deptno from EMP group by deptno) t2)
);
12、查询平均薪水等级最低的部门的部门名称
select t3.dname
from DEPT t3
where deptno in (
select t1.deptno
from (select avg(sal) avg_sal,deptno from EMP group by deptno) t1
where t1.avg_sal =(select min(t.avg_sal) from (select avg(sal) avg_sal,deptno from EMP group by deptno) t)
);
13、查询部门经理人中平均薪水最低的部门名称
(1)查询部门经理
select* from EMP where empno in(select distinct mgr from EMP);
(2)讲(1)的结果作为条件
select t3.dname,t3.deptno
from DEPT t3
where deptno in (
select t1.deptno
from (select avg(sal) avg_sal,deptno from ((select* from EMP where empno in(select distinct mgr from EMP))as a) group by deptno) t1
where t1.avg_sal =(select min(t.avg_sal) from (select avg(sal) avg_sal,deptno from ( (select* from EMP where empno in(select distinct mgr from EMP))as a) group by deptno) t)
);
14、查询薪水最高的前5名雇员编号,名称,薪水
select empno,ename,sal
from EMP
order by sal desc
limit 5;
15、查询薪水最高的第6名到第10名雇员编号,名称,薪水
select empno,ename,sal
from EMP
order by sal desc
limit 5,5;
16、查询部门的名字和部门的人数(如果部门里没有人数,显示0个)
select t1.dname,ifnull(t2.num,0)
from
DEPT t1
left join
(select count(*) num,deptno from EMP group by deptno) t2
on t1.deptno = t2.deptno;
17、查询员工的编号,工资和所在部门的平均工资
select
empno,ename,sal,t2.deptno,(select avg(sal) from EMP t1 where t1.deptno = t2.deptno)
from
EMP t2;
select
empno,ename,sal,t2.deptno,t2.avg_sal
from
EMP t1
inner join
(select avg(sal) avg_sal,deptno from EMP group by deptno) t2
on
t1.deptno = t2.deptno;
18、函数
参考网站
1、字符串
1、length
select LENGTH("abc中国");
select ename,LENGTH(ename) from EMP;
2、concat
select CONCAT("a","bc","xx");
3、str_to_date
select STR_TO_DATE("2018年02月03日","%Y年%m月%d日");
create table tt(
id int auto_increment primary key,
birthday date
);
insert into tt(birthday) values("2018-2-4");
insert into tt(birthday) values(STR_TO_DATE("2018年02月03日","%Y年%m月%d日"));
select * from tt;
2、数字
1、floor,ceil
select FLOOR(1.56),CEIL(1.16);
2、rand
select RAND(10);
3、日期
1、now
select NOW();
select DAYOFWEEK('2018-08-26');
2、date_format 日期转字符串
select DATE_FORMAT(NOW(),"%Y年%m月%d日 %H时%i分%s秒");
select DATE_FORMAT("2018-2-3","%Y年%m月%d日");
19、视图
创建视图
create view myview
as
select avg(sal) avg_sal,deptno from EMP group by deptno;
使用视图
select * from myview;
20、事务
(1)所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。
(2)事务四大特性(简称ACID):
1.原子性:一组事务,要么成功;要么撤回。
2.稳定性 :有非法数据(外键约束之类),事务撤回。
3.隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
4.可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得,
innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。
(3)事务语句:
开启begin; 或者 start transaction;
提交commit;
回滚rollback;
(4)例子:
drop table bank;
create table bank(
id int primary key,
money int
);
insert into bank values(1,10);
insert into bank values(2,1);
select * from bank;
begin;
update bank
set money = money-5
where id = 1;
update bank
set money = money+5
where id = 2;
rollback;
commit;
select * from bank;
begin;
savepoint p1;
update bank
set money = money-1
where id = 1;
savepoint p2;
update bank
set money = money-1
where id = 1;
savepoint p3;
update bank
set money = money-1
where id = 1;
savepoint p4;
rollback to p4;
commit;
21,pymysql基本操作
(1)增删改格式
import pymysql
#连接对象
my_conn = pymysql.connect(host="localhost", user="root", password="root", database="laowang", port=3306, charset="utf8")
#执行工具
my_cursor = my_conn.cursor()
#执行sql
my_cursor.execute("delete from student where id = 3");
#提交,增删改
my_conn.commit();
#关闭
my_conn.close()
(2)查询格式
import pymysql
#连接对象
my_conn = pymysql.connect(host="localhost", user="root", password="root", database="laowang", port=3306, charset="utf8")
#执行工具
my_cursor = my_conn.cursor()
#执行sql
my_cursor.execute("select * from student");
#查询,元组
content = my_cursor.fetchall()
print(content)
#关闭
my_conn.close()
附录一 数据库表
/********************************部门表dept********************************/
/*创建表*/
DROP TABLE IF EXISTS DEPT;
CREATE TABLE DEPT(
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(14) ,
LOC VARCHAR(13)
);
/*插入数据*/
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');
/*查询数据*/
SELECT * FROM DEPT;
/********************************员工表emp********************************/
/*创建表*/
DROP TABLE IF EXISTS EMP;
CREATE TABLE EMP(
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(14) ,
JOB VARCHAR(9),
MGR INT,
HIREDATE DATE,
SAL DECIMAL(7,2),
COMM DECIMAL(7,2),
DEPTNO int REFERENCES DEPT
);
/*插入数据*/
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-7-13',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-7-13',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);
/*查询数据*/
SELECT * FROM EMP;
/********************************工资等级表salgrade********************************/
/*创建表*/
DROP TABLE IF EXISTS SALGRADE;
CREATE TABLE SALGRADE(
GRADE INT PRIMARY KEY,
LOSAL INT,
HISAL INT
);
/*插入数据*/
INSERT INTO SALGRADE VALUES
(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);
/*查询数据*/
SELECT * FROM SALGRADE;
附录二 数据库图