MySQL基本用例

建立用例表

#创建学生表
CREATE TABLE IF NOT EXISTS stu(
 sid CHAR(6),
 sname VARCHAR(40),
 age INT,
 gender VARCHAR(32)
);

#加入数据
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

# 创建员工表
CREATE TABLE IF NOT EXISTS emp(
 empno INT,
 aname VARCHAR(43),
 job VARCHAR(32),
 mgr INT ,
 hiredate DATE,
 sal  DECIMAL(7,2),
 comm  DECIMAL(7,2),
 deptno  INT
);

#插入数据
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

#创建部门表
CREATE TABLE dept(
 deptno  INT,
 dname  VARCHAR(14),
 loc  VARCHAR(13)
);

#添加数据
INSERT INTO dept VALUES(10, '财务部', 'beijing');
INSERT INTO dept VALUES(20, 'java开发部', 'tianjin');
INSERT INTO dept VALUES(30, '测试部', 'shanghai');
INSERT INTO dept VALUES(40, '销售部', 'shenzheng');

用例

sql



#对 doctor 的表进行查询
####  1：简单查询
#####  1.1  查询所有列
SELECT * FROM stu;
#查询指定列
SELECT sid,sname FROM stu;
##### 2:条件查询
##### 查询性别为女 且年龄小于五十的
SELECT * FROM stu WHERE gender = 'male' AND age<50;
SELECT * FROM stu WHERE gender = 'male' && age<50;
#### 查询 id = s_1001 的或者 叫lisi的
SELECT * FROM stu WHERE sid='s_1001' OR sname = 'lisi';
SELECT * FROM stu WHERE sid='s_1001' || sname = 'lisi';
#查询 学号为 s_1001 或 s_1002 或s_1003的
SELECT * FROM stu WHERE sid = 's_1001' OR sid = 's_1002' OR sid = 's_1003';
SELECT * FROM stu WHERE sid IN ('s_1001','s_1002','s_1003');
#### 查询年龄为空的
SELECT * FROM stu WHERE age IS NULL;
#### 查询年龄在20 40 之间的 
SELECT * FROM stu WHERE age>20 AND age <40;
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
#### 性别非男
SELECT * FROM stu WHERE gender != 'male';
SELECT * FROM stu WHERE NOT gender ='male';
####  模糊查询
#### 名字有三个字组成的人
SELECT * FROM stu WHERE sname LIKE '___';
#### 以 i结尾的名字
SELECT * FROM stu WHERE sname LIKE '%i';
#### 以z 开头的 名字
SELECT * FROM stu WHERE sname LIKE 'z%';
#### 含有a 的记录
SELECT * FROM stu WHERE sname LIKE '%s%';
####  字段控制查询
SELECT DISTINCT sal FROM emp; # 去重
#### 列相加 
SELECT* , sal + comm FROM emp; # 发现两个列中有空值得相加就会为零
#### 有空返回0
SELECT *,sal+IFNULL(comm,0) FROM emp;
#### 字符串连接
SELECT *,CONCAT(aname,'********',job) FROM emp;
#### 别名  
SELECT *,sal+IFNULL(comm,0) AS totol FROM emp;
#### 排序
SELECT * FROM stu ORDER BY age ASC;#升序
SELECT * FROM stu ORDER BY age DESC; #降序排序
#### 聚合函数
#### 查询记录个数
SELECT COUNT(0) AS a FROM emp;  //此处的0 就当新建立了一列
#工资大于2500的人数
SELECT COUNT(0) AS s FROM emp WHERE sal>2500;
#### 工资和佣金大于2500的人数
SELECT COUNT(*) AS s FROM emp WHERE sal+IFNULL(comm,0)>2500;
#### 求和 和平均值
#### 所有员工的月薪和
SELECT SUM(sal) FROM emp;
# 所有员工平均工资
SELECT AVG(sal) FROM emp;
#### 所有工资佣金之二和
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;
SELECT AVG(sal+IFNULL(comm,0)) FROM emp;
#### 最小值 最大值
SELECT MIN(sal),MAX(sal) FROM emp;
####      分组查询
#### 查询 每个部门的 部门编号和工资和
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
#### 部门编号和 各部门人数
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
####  查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
 # 查询工资总和大于9000的部门编号以及工资和：
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

/*
where he having的区别 where是对分组之前的数据进行筛选 having是对分组之后的数据进行筛选
where 前面不可以分组   having前面需要分组
*/
/*
   查询语句书写顺序：select  列 from 表
   【where- group by- having- order by-limit】
​   查询语句执行顺序：from 表
 where -group by -having - select - order by-limit      
*/