SQL学习小记(更新中。。。)

 

利用sql语句创建一张表

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(15) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`gender` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`address` VARCHAR(100) DEFAULT '无' COMMENT '地址',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8

表的修改删除操作

-- 修改表名
ALTER TABLE test RENAME AS test1

-- 增加表字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE test1 ADD age INT(10)

-- 修改表的字段(重命名，修改约束) 
-- MODIFY只能修改字段类型（约束）CHANGE 用来字段重命名
ALTER TABLE test1 MODIFY age VARCHAR(10)
ALTER TABLE test1 CHANGE age age1 INT(3)

-- 删除表字段
ALTER TABLE test1 DROP age1
-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS test1

表的查询操作

-- 查询全部信息
SELECT * FROM xxx

-- 去重查询
SELECT DISTINCT job FROM emp
-- 注意：DISTINCT只能出现在所有字段的最前面

-- 查询job为salesman或者manager的人的名字
SELECT ename,job FROM emp WHERE job IN('salesman','manager')
SELECT ename,job FROM emp WHERE job='salesman' OR job='manager'

-- 模糊查询中必须掌握两个特殊符号，一个是%，一个是_
-- 查询名字中带O的
SELECT ename FROM emp WHERE ename LIKE '%o%'

-- 找出第二个字母是A的
SELECT ename FROM emp WHERE ename LIKE '_A%'

-- 查询名字中带下划线的
SELECT ename FROM emp WHERE ename LIKE '%\_%'

-- 查询名字中最后一个字母是T的
SELECT ename FROM emp WHERE ename LIKE '%T'

排序操作

-- 按照工资升序，找出员工名和薪资(默认升序)
-- asc表示升序，desc表示降序
SELECT ename,sal FROM emp ORDER BY sal ASC
SELECT ename,sal FROM emp ORDER BY sal DESC

-- 按照工资的降序排列，当工资相同的时候再按名字的升序排列
SELECT ename,sal FROM emp ORDER BY sal DESC , ename ASC
-- 越靠前的字段越能起主导地位，只有在前面字段无法完成排序的时候，才启用后面字段

分组函数

-- 找出工资总和
SELECT SUM(sal) FROM emp
-- 找出最高工资
SELECT MAX(sal) FROM emp
-- 找出最低工资
SELECT MIN(sal) FROM emp
-- 找出平均工资
SELECT AVG(sal) FROM emp
-- 找出总人数
SELECT COUNT(*) FROM emp

-- 计算每个员工的年薪
SELECT ename,(sal+IFNULL(comm,0))*12 AS yearsal FROM emp
-- comm为每个月补助有可能为null，所以用IFNULL来处理
-- IFNULL(有可能为null的字段，被当做什么处理) :属于单行操作函数

-- 查询工资高于平均工资的员工
SELECT ename,sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp)

连接查询

-- 内连接之等值连接以及避免笛卡尔积现象
-- 避免笛卡尔积不会减少底层匹配次数


-- 查询员工的姓名和部门
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno --92语法
SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno --99语法

-- select n.XXX,m.xxx from xxx n join xxx m on n.xxx = m.xxx

-- 内连接之非等值连接
-- 查询员工的姓名，工资和工资等级
SELECT e.ename,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN losal AND hisal

-- 自连接
-- 查询员工的上级领导，要求显示员工名和领导名
SELECT a.ename AS '员工',b.ename AS '领导' FROM emp a JOIN emp b ON a.mgr = b.empno

 

/*外连接：一定有一张表是主表，一张表是副表，即使主表条件和副表匹配不上也会将主表中的
所有字段查询出来*/

-- 查询所有员工的上级领导，要求显示所有员工
SELECT a.ename AS '员工',b.ename AS '领导' FROM emp a LEFT JOIN emp b ON a.mgr = b.empno

-- LEFT JOIN :左边表为主表
-- RIGHT JOIN :右边表为主表

-- 多表查询
--查询每个员工的部门，工资等级和上级领导

SELECT e.ename,d.dname,s.grade,b.ename AS '领导'
FROM emp e JOIN 
dept d 
ON 
e.deptno = d.deptno 
JOIN salgrade s 
ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN emp b ON e.mgr = b.empno