数据库操作

##1.聚合(分组)函数 max min sum avg count
SELECT SUM(userage),MAX(userage),MIN(userage),AVG(userage),COUNT(userage) FROM users;
#注意1:sum和avg只能应用于数值类型字段
#注意2:count会忽略null行
#注意3:如果使用了聚合函数,那么在select 和 from之间只能书写聚合函数和分组字段

##2.分组 group by ，having
#2.1:统计每个城市的人数
SELECT COUNT(userId) ,useraddress FROM users GROUP BY useraddress;
#如果使用了分组字段,那么在select 和 from之间只能书写聚合函数和分组字段
#2.2:统计每个城市男生的人数
#先where后group by
SELECT COUNT(userid),useraddress FROM users WHERE usersex=‘男’ GROUP BY useraddress;
#2.3:统计有哪些城市有2个以上的男生
usersex=‘男’ 这个条件在分组之前
COUNT(userid)>2 这个条件在分组之后
SELECT COUNT(userid),useraddress FROM users
WHERE usersex=‘男’ GROUP BY useraddress HAVING COUNT(userid)>2;
/*
where:筛选条件,必须在分组之前(分组之前的筛选)
group by:分组,对内容进行分组
having:筛选条件,分组之后的筛选,只能在分组里边使用
*/
SELECT 部门号,COUNT(员工号) FROM 员工表 WHERE 工资>=2000 GROUP BY 部门号 HAVING COUNT(员工号)>2;

##3.条件中的分支
SELECT * FROM users;
#if分支
#if(expr1,expr2,expr3),当expr1为真时,表达式为expr2,否则为expr3;
SELECT u.,IF(usersex=‘男’,‘先生’,‘女士’) FROM users u;
#用户有姓名和性别字段,请显示具体称谓:比如张三 男 显示 张先生
SELECT u., CONCAT(LEFT(username,1),IF(usersex=‘男’,‘Mr’,‘Mi’)) FROM users u;
#case分支
/*
18以下 少年
18-35 青年
35-50 中年
50以上 老年
/
SELECT u.,
CASE
WHEN userage<18 THEN ‘少年’
WHEN userage<36 THEN ‘青年’
WHEN userage<51 THEN ‘中年’
ELSE ‘老年’
END AS ‘年龄’
FROM users u;

SELECT * FROM users;

##案例一:
SELECT * FROM card;
#用户反映表中的i和1,o和0看不清楚,请编写一条SQL语句将i–>1,o–>0
UPDATE card SET password1=REPLACE(REPLACE(password1,‘o’,‘0’),‘i’,‘1’);

##案例二:
#用sellrecord这样的一张表。
#请编写SQL语句按顺序显示listNumber的内容,排序方式为首先按照-左边的排,在按照-右边的排
SELECT * FROM sellrecord ORDER BY listnumber;
#分析:
#1.order by
#2.截取字符串
#3.类型转换
#获取-的位置
SELECT INSTR(listnumber,’-’) FROM sellrecord;
#获取-左边的内容
SELECT LEFT(listnumber,(INSTR(listnumber,’-’))-1) FROM sellrecord;
#将刚才取出来的内容进行类型转换
SELECT CAST(LEFT(listnumber,(INSTR(listnumber,’-’))-1) AS SIGNED INTEGER) FROM sellrecord;
#获取-右边的内容
SELECT CAST(SUBSTR(listnumber,INSTR(listnumber,’-’)+1,(CHAR_LENGTH(listnumber)-INSTR(listnumber,’-’)))AS SIGNED INTEGER ) FROM sellrecord;

#排序
SELECT * FROM sellrecord
ORDER BY
CAST(LEFT(listnumber,(INSTR(listnumber,’-’))-1) AS SIGNED INTEGER),
CAST(SUBSTR(listnumber,INSTR(listnumber,’-’)+1,(CHAR_LENGTH(listnumber)-INSTR(listnumber,’-’)))AS SIGNED INTEGER );

##4.子查询:当一个查询是另一个查询的条件时,这个查询就是子查询,外边的查询就是父查询.
SELECT * FROM users;
#4.1:查询年龄比‘白莲女’大的人
SELECT userage FROM users WHERE username=‘白莲女’;
SELECT * FROM users WHERE userage>‘33’;
SELECT * FROM users WHERE userage>(SELECT userage FROM users WHERE username=‘白莲女’);
#4.2:查询年龄比苏州人年龄都大的人
SELECT MAX(userage) FROM users WHERE useraddress=‘苏州’;
SELECT * FROM users WHERE userage>(SELECT MAX(userage) FROM users WHERE useraddress=‘苏州’);
SELECT userage FROM users WHERE useraddress=‘苏州’;
SELECT * FROM users WHERE userage>ALL(SELECT userage FROM users WHERE useraddress=‘苏州’);
#4.3:查询年龄比任意一个苏州人年龄大的人
SELECT * FROM users WHERE userage>ANY(SELECT userage FROM users WHERE useraddress=‘苏州’);
SELECT * FROM users WHERE userage>(SELECT MIN(userage) FROM users WHERE useraddress=‘苏州’);

#注意1:使用子查询,返回列数有且只能为1
#注意2:使用子查询,在进行关系运算时,有且只能返回一行
#注意3:使用子查询,如果返回行数超过一行,不能使用关系运算符,只能使用in 或者 not in

##5.表连接
#5.1:内连接
SELECT 待显示列字段 FROM 表A INNER JOIN 表B ON 连接条件 [WHERE 筛选条件];
#5.2:左外链接
SELECT 待显示列字段 FROM 表A LEFT [OUTER] JOIN 表B ON 连接条件 [WHERE 筛选条件];
#5.3:右外链接
SELECT 待显示列字段 FROM 表A RIGHT [OUTER] JOIN 表B ON 连接条件 [WHERE 筛选条件];

SELECT * FROM emp;#员工表
#m:1
SELECT * FROM dept;#部门表

关系:维护两张表(外键)
外键:一定是另一张表的主键

#显示员工姓名，职位，所属部门以及所在地
SELECT ename,job,dname,loc FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
SELECT ename,job,dname,loc FROM dept d INNER JOIN emp e ON e.deptno=d.deptno;
#内连接两张表之间的关系是平等关系
#左连接的处理方式:以左表为主,右表为辅,如果左表的数据在右表中没有对应,则以null填充
#一般处理缺考性问题
SELECT * FROM dept d LEFT JOIN emp e ON d.DEPTNO=e.deptno;
#统计每个部门的人数
SELECT dname,COUNT(empno) FROM dept d LEFT JOIN emp e ON d.DEPTNO=e.deptno GROUP BY dname;
SELECT * FROM dept d LEFT JOIN emp e ON d.DEPTNO=e.deptno ;

SELECT dname,COUNT(empno) FROM dept d INNER JOIN emp e ON d.DEPTNO=e.deptno GROUP BY dname;

#右连接的处理方式:以右表为主,左表为辅,如果右表的数据在左表中没有对应,则以null填充
#表连接是在拼接列
#union是在拼接行

DML:INSERT UPDATE DELETE
SELECT (筛选查询,排序查询,分页查询,别名查询,常量查询,函数查询,分组查询,子查询,拼接查询,链接查询等)
ORDER BY ④
LIMIT ⑤
GROUP BY ②
WHERE ①
HAVING ③
DDL:CREATE ALTER DROP
DCL:GRANT REVOKE
TCL:BEGIN COMMIT ROLLBACK SAVEPOINT

//文件
/*
SQLyog v12.2.6 (64 bit)
MySQL - 5.1.73-community : Database - test

---

*/

/*!40101 SET NAMES utf8 */;

/!40101 SET SQL_MODE=’’/;

/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;
CREATE DATABASE /!32312 IF NOT EXISTS/test /*!40100 DEFAULT CHARACTER SET gbk */;

USE test;

/*Table structure for table account */

DROP TABLE IF EXISTS account;

CREATE TABLE account (
countId int(11) DEFAULT NULL,
userid int(11) DEFAULT NULL,
score decimal(8,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table account */

insert into account(countId,userid,score) values

(1,16,34.5000),

(2,25,300.0000),

(3,29,1.5000);

/*Table structure for table card */

DROP TABLE IF EXISTS card;

CREATE TABLE card (
password1 varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table card */

insert into card(password1) values

(‘3y78s1f1q8e1ed3a’),

(‘4sue8gu387r1qwrw’),

(‘93ha9178r1ds0jhf’),

(‘r932u1f1asr3r0wf’),

(‘20171u1df8q78673’),

(‘s1048z91u2eeet5y’),

(‘0a0281913r4asad’),

(‘9w72j00fp10df0aw’),

(‘0z8173ja82j085qs’);

/*Table structure for table dept */

DROP TABLE IF EXISTS dept;

CREATE TABLE dept (
DEPTNO int(11) NOT NULL,
DNAME varchar(14) DEFAULT NULL,
LOC varchar(13) DEFAULT NULL,
PRIMARY KEY (DEPTNO)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table dept */

insert into dept(DEPTNO,DNAME,LOC) values

(10,‘ACCOUNTING’,‘NEW YORK’),

(20,‘RESEARCH’,‘DALLAS’),

(30,‘SALES’,‘CHICAGO’),

(40,‘OPERATIONS’,'BOSTON ');

/*Table structure for table emp */

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (
empno int(11) NOT NULL AUTO_INCREMENT,
ename varchar(20) DEFAULT NULL,
job varchar(20) DEFAULT NULL,
mgr int(11) DEFAULT NULL,
hiredate varchar(20) DEFAULT NULL,
sal float DEFAULT NULL,
comm float DEFAULT NULL,
deptno int(11) DEFAULT NULL,
PRIMARY KEY (empno)
) ENGINE=InnoDB AUTO_INCREMENT=7936 DEFAULT CHARSET=gbk;

/*Data for the table emp */

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values

(7369,‘SMITH’,‘CLERK’,7902,‘2018-10-28 10:22:37’,800,NULL,20),

(7499,‘Allen’,‘SALESMAN’,7698,‘2018-10-31 10:22:37’,1600,300,30),

(7521,‘Ward’,‘SALESMAN’,7698,‘2018-10-28 10:22:37’,1250,500,30),

(7566,‘JONES’,‘MANAGER’,7839,‘2018-10-28 10:22:37’,2975,NULL,20),

(7654,‘MARTIN’,‘SALESMAN’,7698,‘2018-10-28 10:22:37’,1250,1400,30),

(7698,‘BLAKE’,‘MANAGER’,7839,‘2018-10-28 10:22:37’,2850,NULL,30),

(7782,‘CLARK’,‘MANAGER’,7839,‘2006-10-27 10:22:37’,2450,NULL,10),

(7788,‘SCOTT’,‘ANALYST’,7566,‘2007-11-29 10:22:38’,3000,NULL,20),

(7839,‘KING’,‘PRESIDENT’,NULL,‘2018-10-28 10:22:38’,5000,NULL,10),

(7844,‘TURNER’,‘SALESMAN’,7698,‘2018-10-28 10:22:38’,1500,0,30),

(7876,‘ADAMS’,‘CLERK’,7788,‘2018-10-28 10:22:38’,1100,NULL,20),

(7900,‘JAMES’,‘CLERK’,7698,‘2018-10-28 10:22:38’,950,NULL,30),

(7902,‘FORD’,‘ANALYST’,7566,‘2018-10-28 10:22:38’,3000,NULL,20),

(7934,‘MILLER’,‘CLERK’,7782,‘2018-10-28 10:22:38’,1300,NULL,10),

(7935,‘张小三’,‘CLERK’,7499,‘1990-1-15’,3000,0,10);

/*Table structure for table sellrecord */

DROP TABLE IF EXISTS sellrecord;

CREATE TABLE sellrecord (
ListNumber varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table sellrecord */

insert into sellrecord(ListNumber) values

(‘13-1’),

(‘13-2’),

(‘13-3’),

(‘13-10’),

(‘13-100’),

(‘13-108’),

(‘13-18’),

(‘13-11’),

(‘13-15’),

(‘14-1’),

(‘14-2’);

/*Table structure for table users */

DROP TABLE IF EXISTS users;

CREATE TABLE users (
userid int(11) NOT NULL DEFAULT ‘0’,
userName varchar(60) NOT NULL,
userSex char(9) DEFAULT NULL,
userAge int(11) DEFAULT NULL,
userAddress varchar(300) DEFAULT NULL,
userpass varchar(100) DEFAULT NULL,
userstate int(11) DEFAULT ‘1’
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table users */

insert into users(userid,userName,userSex,userAge,userAddress,userpass,userstate) values

(1,‘白莲女’,‘女’,33,‘武汉’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(2,‘折为民’,‘女’,22,‘宝鸡’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(3,‘张大三’,‘男’,24,‘宝鸡’,‘e10adc3949ba59abbe56e057f20f883e’,0),

(4,‘折挨成’,‘男’,33,‘香港’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(5,‘折改何’,‘女’,41,‘商洛’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(6,‘孙成红’,‘男’,46,‘苏州’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(8,‘丁冬芹’,‘女’,23,‘北京’,‘e10adc3949ba59abbe56e057f20f883e’,0),

(9,‘孙立军’,‘男’,48,‘苏州’,‘e10adc3949ba59abbe56e057f20f883e’,0),

(10,‘张虎豹’,‘男’,57,‘洛杉矶’,‘e10adc3949ba59abbe56e057f20f883e’,0),

(11,‘白雪’,‘女’,23,‘福州’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(12,‘贾大林’,‘女’,37,‘北京’,‘e10adc3949ba59abbe56e057f20f883e’,0),

(15,‘张国厚’,‘女’,38,‘上海’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(16,‘杨福林’,‘女’,38,‘苏州’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(17,‘马果树’,‘女’,51,‘北京’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(18,‘刘埃牛’,‘女’,27,‘北京’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(19,‘杨万富’,‘女’,28,‘重庆’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(20,‘郭二旦’,‘女’,52,‘上海’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(21,‘马占云’,‘女’,46,‘苏州’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(22,‘石玉兰’,‘女’,48,‘上海’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(23,‘尚文清’,‘男’,47,‘武汉’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(24,‘杨平’,‘女’,55,‘罗马’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(26,‘王越建’,‘男’,32,‘重庆’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(27,‘王利祥’,‘女’,30,‘宝鸡’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(28,‘王飞云’,‘男’,25,‘宝鸡’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(29,‘王银喜’,‘女’,28,‘苏州’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(30,‘王树林’,‘女’,35,‘成都’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(31,‘贾飞世’,‘女’,48,‘洛杉矶’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(32,‘贾埃罗’,‘女’,48,‘成都’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(33,‘贾永世’,‘男’,45,‘罗马’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(34,‘贾虎世’,‘女’,40,‘洛杉矶’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(35,‘贾熹’,‘男’,43,‘苏州’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(36,‘贾小龙’,‘女’,41,‘商洛’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(37,‘贾飞小’,‘女’,45,‘上海’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(38,‘刘埃生’,‘女’,43,‘苏州’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(39,‘马祥’,‘女’,55,‘重庆’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(40,‘贾桂林’,‘男’,49,‘成都’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(41,‘申根换’,‘男’,47,‘苏州’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(42,‘申换换’,‘女’,55,‘重庆’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(43,‘訾存贵’,‘女’,29,‘重庆’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(44,‘杜喜喜’,‘男’,31,‘重庆’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(45,‘刘满贵’,‘女’,50,‘商洛’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(46,‘刘润生’,‘男’,48,‘苏州’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(47,‘韩刚龙’,‘女’,31,‘罗马’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(48,‘辛文斌’,‘男’,38,‘洛杉矶’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(49,‘石根厚’,‘女’,57,‘洛杉矶’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(50,‘石建军’,‘男’,35,‘北京’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(52,‘王焕树’,‘男’,38,‘苏州’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(53,‘高桂清’,‘男’,38,‘香港’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(55,‘杜文渊’,‘女’,54,‘香港’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(56,‘高二媛’,‘女’,31,‘重庆’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(57,‘王二锁’,‘男’,39,NULL,‘e10adc3949ba59abbe56e057f20f883e’,1),

(61,‘戴双宝’,‘男’,36,‘罗马’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(63,‘李四’,‘女’,29,‘北京’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(64,‘王王王’,‘男’,27,‘湖北武汉’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(68,‘马三立’,‘男’,67,‘天津’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(69,‘五彩白兰’,‘女’,27,‘宝鸡’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(70,‘小黑’,‘女’,31,‘咸阳’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(71,‘小绿’,‘女’,23,‘山西太原’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(72,‘郭德纲’,‘男’,22,‘北京’,‘f379eaf3c831b04de153469d1bec345e’,1),

(74,‘张小三’,‘男’,23,‘山西太原’,‘dc483e80a7a0bd9ef71d8cf973673924’,1),

(77,‘真的白’,‘女’,24,‘临潼’,‘aaa’,1),

(78,‘詹大三’,‘男’,87,‘宝鸡’,‘aaa’,1),

(79,‘李大思’,‘男’,28,‘榆林’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(80,‘小绿2’,‘女’,33,‘武汉’,‘e10adc3949ba59abbe56e057f20f883e’,1),

(81,‘测试1’,‘男’,33,NULL,‘202cb962ac59075b964b07152d234b70’,1),

(82,‘一级缓存’,‘男’,22,‘陕西西安’,‘47bce5c74f589f4867dbd57e9ca9f808’,1);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;