INSERT INTO Student(name,sex,age,id) VALUES('hehe2','m',18,19070002);
INSERT INTO Student(name,sex,age,id) VALUES('hehe3','m',19,19070003);
INSERT INTO Student(name,sex,age,id) VALUES('hehe4','w',20,19070004);
CREATE TABLE Teacher(id int,name char(20),subject char(20),salary float,start_time date,birth date);
CREATE TABLE Teacher(id int,name char(20),subject char(20),salary float);
INSERT INTO Teacher VALUES(1001,'mage1','S1',6666.666);
INSERT INTO Teacher VALUES(1002,'mage2','S2',6667.666);
INSERT INTO Teacher VALUES(1003,'mage3','S3',6668.666);
INSERT INTO Teacher VALUES(1004,'mage4','S4',6669.666);
INSERT INTO Teacher VALUES(1005,'mage5','S5',6670.666);
INSERT INTO Teacher VALUES(1006,'mage6','S6',6671.666);
INSERT INTO Teacher VALUES(1007,'mage7','S1',6672.666);
INSERT INTO Teacher VALUES(1008,'pingge','S1',6672.666);
INSERT INTO Teacher VALUES(1009,'taoge','S1',6672.666);
INSERT INTO Teacher VALUES(1010,'maoge','S1',6672.666);
INSERT INTO Teacher VALUES(1001,'mage1','S1',6666.666);
CREATE TABLE Employee(id int,name char(20),post char(20),base_sal float,royalty float);
INSERT INTO Employee VALUES(19001,'xiu','SSS',6000,1.6);
INSERT INTO Employee VALUES(19002,'xie','SS',5500,1.4);
INSERT INTO Employee VALUES(19003,'pang','SSSSS',10000,1.8);
INSERT INTO Employee(id,name,post,base_sal) VALUES(19004,'sun','A',15000);
INSERT INTO Employee VALUES(19005,'xiu1','SSS',6000,1.7);
INSERT INTO Employee VALUES(19006,'xiu2','SSS',6000,1.8);
CREATE TABLE emp(id int,dept_id int,name char(20),post char(20),sal float);
INSERT INTO emp VALUES(1001,101,'hehe1','S1',5000.0);
INSERT INTO emp VALUES(1002,101,'hehe2','S2',5000.0);
INSERT INTO emp VALUES(1003,102,'hehe3','S3',6000.0);
INSERT INTO emp VALUES(1004,102,'hehe4','S4',6000.0);
INSERT INTO emp VALUES(1005,103,'hehe5','S5',7000.0);
INSERT INTO emp VALUES(1006,103,'hehe6','S6',7000.0);
CREATE TABLE dept(id int,name char(20),addr char(20));
INSERT INTO dept VALUES(101,'HAHA1','zhizhen1');
INSERT INTO dept VALUES(102,'HAHA2','zhizhen2');
INSERT INTO dept VALUES(103,'HAHA3','zhizhen3');
1、查询表中某些字段
SELECT 字段1,字段2... FROM 表名;
例:查询姓名与年龄
SELECT name,age FROM Student;
* 在SQL中也是通配符,代表所有字段
2、SQL中的数学运算 + - * /
例:查询每个教师的日薪,按每月30天计算
SELECT name,salary/30 FROM Teacher;
例:查询每个教师的年薪
3、字段别名
为查询结果的新取一个字段名
例:查询每个教师的年薪,并为查询结果设置的字段名为yearsal
SELECT name,salary/30 yearsal FROM Teacher;
4、字符串操作
Oracle中使用 || 连接字符串,而在MySQL它逻辑或运算。
CONCAT(s1,s2,...)
返回连接参数产生的字符串,一个或多个待拼接的内容,任意一个为NULL则返回值为NULL。
例:显示所有教师名和所教科目中间有下划线连接
SELECT CONCAT(name,'_',subject) FROM Teacher;
CONCAT_WS(x,s1,s2,...)
返回多个字符串拼接之后的字符串,每个字符串之间有一个x。
例:连接字符串,并使用空格分隔
SELECT CONCAT_WS(' ',name,subject,'HEHE') FROM Teacher;
SUBSTRING(s,n,len)、MID(s,n,len)
两个函数作用相同,从字符串s中返回一个第n个字符开始、长度为len的字符串。
例:获取小马哥 编号
SELECT MID(NAME,5,1) FROM Teacher;
LEFT(s,n)、RIGHT(s,n)
前者返回字符串s从最左边开始的n个字符,后者返回字符串s从最右边开始的n个字符。
例:获取小马哥名字,不要编号
SELECT LEFT(name,4) FROM Teacher;
INSERT(s1,x,len,s2)
返回字符串s1,其子字符串起始于位置x,被字符串s2取代s1len个字符。
SELECT INSERT('12345678',3,4,'ABCDEF');
REPLACE(s,s1,s2)
返回一个字符串,用字符串s2替代字符串s中所有的字符串s1。
SELECT REPLACE('12345678','345','ABC');
LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1) 三个函数作用相同,返回子字符串str1在字符串str中的开始位置(从第几个字符开始)。
SELECT LOCATE('123','ABCDD123DEF');
FIELD(s,s1,s2,...) 返回第一个与字符串s匹配的字符串的位置。
SELECT FIELD('123','ABC','DEF','123','HEHE');
重点:SQL语言中下村从1开始
5、排重显示
SELECT DISTINCT 字段 FROM 表名;
例:查询学校一共开设多少科目
SELECT DISTINCT subject FROM Teacher;
6、条件查询
SELECT 字段 FROM 表名 WHERE 条件;
当WHERE条件为真时显示相关数据,配合相关比较运算符。
> < >= <= = !=
例:查询所有教S1科目的老师
SELECT * FROM Teacher WHERE subject = 'S1';
例:查询所有不教S1科目的老师
SELECT * FROM Teacher WHERE subject!='S1';
例:查询所有年薪超过8W的老师
SELECT name,id,salary*12 FROM Teacher WHERE salary*12>=80000;
注意:SQL中字符串可以直接使用 比较运算符,与strcmp比较规则一致。
SELECT * FROM 表名 WHERE x BETWEEN n AND m;
显示x值 在n到m之间的数据,(n,m]
例:查询月薪在6669.67,6670.67之间的教师信息
SELECT * FROM Teacher WHERE salary BETWEEN 6669.67 AND 6670.67;
SELECT * FROM Teacher WHERE salary>6669.67 AND salary <= 6670.67;
SELECT * FROM 表名 WHERE x in(n1,n2,n3);
显示x值在(n1,n2,n3,...)列表中,的相关数据
例:查询教授S1,S4,S5等科目的老师信息
SELECT * FROM Teacher WHERE subject in('S1','S4','S5');
SELECT * FROM 表名 WHERE x LIKE str;
SQL中字符串可以进行模糊查询,str中可以使用通配符
%代表任意多个字符 _代表一个字符
例:查询名字mege开头的相关老师信息
SELECT * FROM Teacher WHERE name LIKE 'mage%';
例:查询名字以1结尾的相关老师信息
SELECT * FROM Teacher WHERE name LIKE '%1';
SELECT * FROM Teacher WHERE name LIKE '%ge_';
注意:在SQL中也可使用逻辑去处,AND OR NOT;
7、空值处理
在SQL中空值是一种特殊数据,任何数据与空值进行计算结果为空。
IFNULL(x,n) 如果x的值为空,则用n来替换(MySQL,Oracle使用nvl函数)。
例:查询员式表中的年薪
SELECT name,base_sal*ifnull(royalty,1)*12 FROM Employee;
例:查询没有年终奖的员工。
SELECT * FROM Employee WHERE royalty IS NULL;
例:查询有年终奖的员工。
SELECT * FROM Employee WHERE royalty IS NOT NULL;
8、排序
是对查询结果进行排序,而不是对数据库中表的数据进行排序。
SELECT 数据 FROM 表名 ORDER BY 字段 标准;
默认升序,DESC降序
例:按基本工资进行排序
SELECT * FROM Employee ORDER by base_sal;
例:按年终奖系数进行排序
SELECT * FROM Employee ORDER by royalty;
例:基本工资为第一个排序字段,年终奖系数为第二排序字段
SELECT * FROM Employee ORDER by base_sal,royalty desc;
9、单行函数
对表中的数据第处理一行就返回一个结果,这种函数叫单行函数。
UPPER 小写转大写
SELECT id,UPPER(name) FROM Employee;
LOWER 大写转小写
SELECT id,UPPER(name),LOWER(post) FROM Employee;
INITCAP 首字母大写,MySQL中不支持
SELECT id,INITCAP(name) FROM Employee;
LENGTH 计算字符串长度
SELECT id,LENGTH(name) FROM Employee;
to_char 其它类型的数据转换成字符串,MySQL中不支持
10、组函数
一次查询只得到一个结果,如果在数据进行分组的情况下,一个分组得到一个结果。
COUNT 计数
例:统计101部门的员工数量
SELECT COUNT(id) FROM emp WHERE dept_id=101;
MAX 求最大值
例:求基本工资最高值
SELECT MAX(base_sal) FROM Employee;
MIN 求最小值
例:求基本工资最低值
SELECT MIN(base_sal) FROM Employee;
SUM 求和
例:求基本工资总支出
SELECT SUM(base_sal) FROM Employee;
AVG 求平均
例:求员工平均工资
SELECT AVG(base_sal) FROM Employee;
11、多表查询
根据员工表,部门表查询出每个员工的上班地点。
SELECT emp.name,addr FROM emp,dept WHERE dept_id=dept.id;
12、分组查询
SELECT 组函数(字段) FROM 表名 GROUP BY 分组条件;
例:每个部门的平均工资
SELECT dept.name,AVG(sal) FROM emp,dept WHERE dept_id=dept.id GROUP BY dept_id;
13、日期数据
注意:数据库中的日期是一种特殊类型,需要一些函数进行转换。
Oracle数据库
TO_DATE(date_str,format) 把字符串按照一定的格式转换成日期数据
TO_CHAR(date,format) 把日期数据按照一定的格式转换成字符串
yyyy 四位年份
yy 两位年份
mm 整数月份
mon 月份简拼
month 月份全拼
dd 整数天数
dy 周几简拼
day 周几全拼
hh12 12小时制
hh24 24小时制
mi 分钟
ss 秒
MySQL数据库
STR_TO_DATE(str,format) 字符转日期
DATE_FORMAT(date,format) 日期转字符串
%Y 四位年份
%y 两位年份
%M 月份全拼
%m 整数月份
%D 带英文后缀的天数
%d 整数天数
sysdate() 获取当前系统时间
14、子查询
把一个查询的结果当作另一个查询的数据源。
例:查询员工表中谁是领导
select empno,ename from emp where empno in (select distinct mgr from emp);
例:查询普通员工信息
select empno,ename from emp where empno not in (select distinct mgr from emp where mgr not is NULL);
注意:空值能和任意数据匹配