INSERT INTO tb_door VALUES(400,'有家面馆','9874-561');
CREATE TABLE tr_order_detail(id INT(3),order_id INT(11),num INT(4),item VARCHAr(30),price DOUBLE);
SELECt * FROM tr_order_detail;
INSERT INTO tr_order_detail VALUES(2324,34535,6565,'御书房',65.54)
#1.主键约束:给字段添加主键约束
#自增策略：AUTO_INCREMENT把主键的值交给数据库维护，值自增
CREATE TABLE A(id INT );
CREATE TABLE B(id INT PRIMARY KEY);#主键（唯一+非空）
CREATE TABLE c(id INT PRIMARY KEY AUTO_INCREMENT );#主键自增
#2.非空约束：not null,给字段加了非空约束，字段值不能为空
CREATE TABLE d(name1 VARCHAr(10));
CREATE TABLE e(NAME1 VARCHAr(10) NOT NULL);
INSERT INTO e VALUES(NULL);#报错必须有值
#3.唯一约束：unique
CREATE TABLE f(tel VARCHAr(10) UNIQUE);
ALTER TABLE student ADD COLUMN id INT PRIMARY KEY AUTO_INCREMENT
CREATE TABLE game(
id INT,
NAME VARCHAr(20),
game VARCHAr(20),
grading VARCHAr(20)
)
SELECt *FROM game;
DELETe FROM game WHERe id=1 OR id=2;
#基础函数：
SELECt * FROM emp;#查所有,低效
SELECt ename FROM emp#查指定的列,(把*换成字段名)，高效
SELECt ename,empno FROM emp;#查询结果和查询顺序一样
#全转大小写：upper lower
SELECt empno,ename,UPPER(ename),LOWER(ename) FROM emp;
#lenght:获取长度，一个字母或数字长度为1，一个汉字长度是3
SELECt dname FROM dept;#查部门名称
SELECt dname,LENGTH(dname),loc,LENGTH(loc) FROM dept;
#concat:拼接字符串
SELECt dname,CONCAt(dname,'hello',' ',545) FROM dept;
#substr:截取字符串
SELECt dname,SUBSTr(dname,4) FROM dept;
#substr(1,2,3):截取字符串,1是字段名，2是从哪个字符开始，3是截取长度
SELECt dname,SUBSTr(dname,4,1) FROM dept;
#replace(1,2,3):1是字段名， 是要把2换成3
SELECT dname,REPLACE(dname,'a',666) FROM dept;
#ifnull(1,2):1是字段名称，判断1如果是null就换成2
SELECt mgr,IFNULL(mgr,800) FROM emp;
#计算月薪
SELECt sal,comm,IFNULL(sal+comm,sal) FROM emp;
#计算年薪
SELECt sal,comm,IFNULL(sal+comm,sal),(sal+IFNULL(comm,0))*12 FROM emp;
#round
SELECt comm,ROUND(comm) FROM emp;
SELECt comm,CEIL(comm),FLOOR(comm) FROM emp;
#日期函数
#now year month day hour minute second
SELECt NOW();
SELECT YEAR(NOW()),MonTH(NOW()),DAY(NOW()),HOUR(NOW());
SELECT CURDATE();
SELECT CURTIME();
SELECT 'xi'an';
SELECT "xi'an";
DELETE FROM car WHERe id=1;
#条件查询
#1.distinct 去重
SELECt DISTINCT loc FROM dept;
#2.where条件
#查询deptno=1的
SELECt * FROM dept WHERe deptno=1;
#查询deptno=2的部门名称
SELECt dname FROM dept WHERe deptno=2;
#查询地址在二区的部门名称
SELECt dname FROM dept WHERe loc='二区';
#查询地址在二区且编号是2的部门信息
SELECt * FROM dept WHERe loc='二区' AND deptno=2;
#查询编号是2的或者编号是3的部门信息
SELECt * FROM dept WHERe deptno=2 OR deptno=3;
SELECt * FROM dept WHERe deptno IN(1,3);
#3.like模糊查询，%通配符，通配了0~n个字符，_通配符，通配了1个字符
#查名称里包含o的部门信息
SELECt * FROM dept WHERe dname LIKE '%o%';#低效
SELECt * FROM dept WHERe dname LIKE 'o%';#高效，以o开始
SELECt * FROM dept WHERe dname LIKE '%o';#以o结束
SELECt * FROM emp WHERe ename LIKE 'l__';#了解
#4.null 操作空数据
SELECt * FROM emp WHERe comm IS NULL;#查comm是null的员工信息
SELECt * FROM emp WHERe comm IS NOT NULL;#查comm不是null的员工信息
#5.区间范围,是包含的关系
SELECt * FROM emp WHERe sal>=3000 AND sal<=10000;
SELECt * FROM emp WHERe sal BETWEEN 3000 AND 10000;
#6.limit:分页
SELECt *FROM emp LIMIT 2;#只展示前两条数据
SELECt *FROM emp LIMIT 3,2;#从第n+1行开始展示，要展示的行数
#7.oder by：排序，默认是升序ASC（可以省略）
SELECt * FROM emp ORDER BY comm;#按照comm排序，默认是升序
SELECt * FROM emp ORDER BY comm DESC;#降序
SELECt * FROM emp ORDER BY ename;#按照ename排序，字典顺序
SELECt * FROM emp ORDER BY hiredate;#按照数值排序
SELECt * FROM emp ORDER BY job;#按照汉字升序排
#统计案例：
#查询2017年以前入职的员工信息
SELECt * FROM emp WHERe hiredate<'2017-1-1';
SELECt * FROM emp WHERe YEAR(hiredate)<2017;
#计算入职年份
SELECt *,YEAR(NOW())-YEAR(hiredate) 入职年份 FROM emp;
#统计年薪
SELECt *,(sal+IFNULL(comm,0))*13 年薪 FROM emp;
#聚合函数：
SELECt MAX(sal) FROM emp;#查最高薪
SELECt MIN(sal) FROM emp;#查最低薪
SELECt AVG(sal) FROM emp;#查平均工资
SELECt SUM(sal) FROM emp;#查工资总和
#count统计个数
SELECt COUNT(*) FROM emp;#低效
SELECt COUNT(1) FROM emp;#高效
SELECt COUNT(empno) FROM emp;
SELECt COUNT(comm) FROM emp;#了解，null不统计
#查询时，出现了混合函数
SELECt empno,SUM(sal) FROM emp;
#分组：当查询是出现了聚合列和非聚合列时，必须按照非聚合列分组
SELECt MAX(sal) FROM emp;#查看最高薪
SELECt MAX(sal) FROM emp GROUP BY deptno;#查询每个部门的最高薪
SELECt deptno,MAX(sal) FROM emp GROUP BY deptno;
#查每个岗位的平均工资
SELECt AVG(sal),job FROM emp GROUP BY job;
#查每年入职的人数
SELECt COUNT(1),YEAR(hiredate) FROM emp GROUP BY YEAR(hiredate);
#查每个部门的人数
SELECt deptno,COUNT(1) FROM emp GROUP BY deptno;
#having:完成分组后的过滤
#查每个部门的人数，人数>1的部门
SELECt deptno,COUNT(1) FROM emp GROUP BY deptno HAVINg COUNT(1)>1;
#查每个岗位的平均工资，只要>8000的
SELECt AVG(sal),job FROM emp GROUP BY job HAVINg AVG(sal)>8000;
#查每年入职的人数,人数>1
SELECt COUNT(1),YEAR(hiredate) FROM emp GROUP BY YEAR(hiredate) HAVINg COUNT(1)>1;#低效，分完组才过滤
SELECt COUNT(1),YEAR(hiredate) FROM emp WHERe COUNT(1)>1 GROUP BY YEAR(hiredate);
#where，里不能出现聚合函数
#高效，先过滤再分组
SELECt SUM(sal) FROM emp;
SELECt AVG(sal) FROM emp GROUP BY job;
SELECt COUNT(1), deptno FROM emp GROUP BY deptno HAVINg COUNT(1)>1;
SELECt job,AVG(sal) FROM emp GROUP BY job HAVINg AVG(sal)>8000;
SELECt YEAR(hiredate),COUNT(1) FROM emp GROUP BY YEAR(hiredate) HAVINg COUNT(1)>1;
.#默认约束default
CREATE TABLE s(
id INT PRIMARY KEY AUTO_INCREMENT,
sex VARCHAr(10) DEFAULT '男'#默认约束
)
#检查约束：检查字段的值是否合法
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
age INT,
CHECK (age>0 AND age<18)#检查约束，不合法时会报错
)
SHOW DATAbaseS;
CREATE DATAbase h DEFAULT CHARACTER utf8;
CREATE TABLE adc(id INT,nane VARCHAr(10));
DROp DATAbase h;
ALTER TABLE adc ADD COLUMN sex VARCHAr(10);
USE h;
DESC adc;
SHOW TABLES;
INSERT INTO adc VALUES(122,'武松');
DROP TABLE adc;
SELECT * FROM adc;
UPDATe adc SET id=1;
DELETE FROM dept WHERe dname='test' ;
CREATE TABLE a(INT);
CREATE TABLE a(INT PRIMARY KEY AUTO_INCREMENT);
CREATE TABLE a(NAME VARCHAr(10));
CREATE TABLE a(NAME VARCHAr(10) NOT NULL)
CREATE TABLE a(NAME VARCHAr(10) UNICODE);
SELECt *FROM emp;
SELECt ename FROM emp;
SELECt ename,empno FROM emp;
SELECt ename,LOWER(ename),UPPER(ename) FROM emp;
SELECt * FROM dept;
SELECt LENGTH(dname),LENGTH(loc) FROM dept;
SELECt dname,CONCAt(dname,996,'八五七') FROM dept;
SELECt dname,SUBSTr(dname,1,5) FROM dept;
SELECt dname,REPLACE(dname,'o','了') FROM dept;
SELECt mgr,IFNULL(mgr,0) FROM emp;
SELECt * FROM emp;
SELECt sal+comm FROM emp;
SELECt sal+IFNULL(comm,0) FROM emp;
SELECt (sal+IFNULL(comm,0))*12 FROM emp;
SELECt comm,ROUND(comm) FROM emp;
SELECt comm,ROUND(IFNULL(comm,2.55)) FROM emp;
SELECt comm,CEIL(IFNULL(comm,2.55)),FLOOR(IFNULL(comm,2.55)) FROM emp;
SELECt NOW();
SELECT YEAR(NOW());
SELECT YEAR('2018.03.6'),MonTH(NOW()),DAY(NOW());
SELECT CURDATE();
SELECT CURTIME();
SELECT 'xi'an';
SELECT "xi'an";
SELECT DISTINCT loc FROM dept;
SELECt * FROM dept;
SELECt * FROM dept WHERe loc='二区';
SELECt dname FROM dept WHERe loc='二区';
SELECt * FROM dept WHERe loc='二区' AND deptno=2;
SELECt * FROM dept WHERe deptno=2 OR deptno=3;
SELECt * FROM dept WHERe deptno IN(1,3);
SELECt * FROM dept WHERe deptno IN(1,2,3);
SELECt * FROM dept WHERe dname LIKE '%o%';
SELECt * FROM dept WHERe dname LIKE 'o%';
SELECt * FROM dept WHERe dname LIKE '%o';
SELECt * FROM emp WHERe ename LIKE 'l__';
SELECt * FROM emp WHERe mgr IS NULL;
SELECt * FROM emp WHERe mgr IS NOT NULL;
SELECt * FROM emp WHERe sal<=10000 AND sal>3000;
SELECt * FROM emp WHERe sal BETWEEN 3000 AND 10000;
SELECt * FROM emp LIMIT 2;
SELECt * FROM emp LIMIT 0,3;
SELECt * FROM emp LIMIT 2,1;
SELECt * FROM emp ORDER BY YEAR(hiredate) ASC;
SELECt * FROM emp ORDER BY YEAR(hiredate) DESC;
SELECt * FROM emp ORDER BY ename;
SELECt * FROM emp ORDER BY job;
SELECt *,YEAR(NOW())-YEAR(hiredate) FROM emp;
#聚合函数
SELECt MAX(sal) FROM emp;
SELECt MIN(sal) FROM emp;
SELECt AVG(sal) FROM emp;
SELECt SUM(sal) FROM emp;
SELECt COUNT(sal) FROM emp;
SELECt COUNT(1) FROM emp;#高效
SELECt COUNT(*) FROM emp;#低效
SELECt job,MAX(sal) FROM emp GROUP BY job;
SELECt deptno,AVG(sal) FROM emp GROUP BY deptno;
SELECt deptno,COUNT(1) FROM emp GROUP BY deptno;
SELECt YEAR(hiredate),COUNT(1) FROM emp GROUP BY YEAR(hiredate);
SELECt deptno,COUNT(1) FROM emp GROUP BY deptno HAVINg COUNT(1)>1;
SELECt job,AVG(sal) FROM emp GROUP BY job HAVINg AVG(sal)>8000;
SELECt YEAR(hiredate),COUNT(1) FROM emp GROUP BY YEAR(hiredate) HAVINg COUNT(1)>1;
CREATE TABLE xxj(id INT PRIMARY KEY AUTO_INCREMENT,
sex VARCHAr(10) DEFAULT '女' ,
age INT,
CHECK (age>18 AND age <25)
)
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAr(50)
)
#外键约束：1，子表的id必须取自主表的id 2,想删除主表的数据必须先删掉子表相关的`tb_user_addr``tb_user`
CREATE TABLE tb_user_addr(
user_id INT PRIMARY KEY ,
addr VARCHAr(50),
#外键：通过特殊字段描述两张表的关系
#foreign key (当前表的主键)reference 对方表（对方表的主题）
FOREIGN KEY (user_id) REFERENCES tb_user(id)
)
#查看索引
SHOW INDEX FROM dept;
#1.创建单值索引（给常用作为查询条件的字段加）
#语法：creat index 索引名 on 表名（字段名）
CREATE INDEX dname_index ON dept(dname);
SHOW INDEX FROM dept;
#2.使用索引(背后的手段)
#观察sql的性能/执行计划(找possible_key的值)
EXPLAIN
SELECt * FROM dept WHERe dname ='java';
#3.创建唯一索引
#语法：creat unique index 索引名 on 表名（字段名）
CREATE UNIQUE INDEX uni_index ON dept(dname);
EXPLAIN
SELECt * FROM dept WHERe dname='accounting';
#4.创建复合索引
CREATE INDEX comp_index ON dept(loc,dname);
EXPLAIN
SELECt * FROM dept WHERe loc='二区' AND dname='java'
ALTER TABLE dept DROp INDEX comp_index;
CREATE INDEX complex_index ON emp(ename,job);
SHOW INDEX FROM emp;
#使用复合索引（最左特效,否则复合索引失效）
#按ename查
EXPLAIN
SELECt * FROM emp WHERe ename='tony';
#按ename和job查
EXPLAIN
SELECt * FROM emp WHERe ename='tony' AND job='员工';
#按job和ename查
EXPLAIN
SELECt * FROM emp WHERe job='员工'AND ename='tony';
#按job查
EXPLAIN
SELECt * FROM emp WHERe job='员工';
ALTER TABLE emp DROp INDEX complex_index;
#多表联查：产生了大量的冗余数据
#1.笛卡尔积:把多张表用逗号隔开
SELECT * FROM dept,emp;
#描述两张表的关系：表名.字段名
SELECt * FROM dept,emp WHERe dept.deptno=emp.deptno;#两张表的关系
#2.连接查询 join on
#描述两张表的关系：表名.字段名
SELECt * FROM dept JOIN emp ON dept.`deptno`=emp.`deptno`;
#3.子查询：把上次的查询结果，用来作为下次查询的条件
#练习语法：teachers/courses
SELECt * FROM teachers,courses WHERe teachers.`tno`=courses.`tno`;
SELECt * FROM teachers JOIN courses ON teachers.`tno`=courses.`tno`;
#练习1：查询部门名称叫accounting的员工姓名
SELECt dept.`deptno`,dname,ename FROM dept,emp
WHERe dept.`deptno`=emp.`deptno` #表关系
AND dept.dname='accounting';#业务条件
SELECt ename FROM dept JOIN emp ON dept.`deptno`=emp.`deptno` WHERe dname='accounting';
#子查询：
SELECt ename FROM emp WHERe deptno=(SELECt deptno FROM dept WHERe dname='accounting' );
#练习2：列出research部门下的所有员工信息
SELECt emp.* FROM emp,dept WHERe dept.`deptno`=emp.`deptno` AND dname='research' ;
#三种连接查询：内连接 inner join:取两个表的交集
#左外连接left join：左表的所有和右表满足条件的，不满足是null
#右外连接right join：右表的所有和左表满足条件的，不满足是null
SELECt * FROM emp JOIN dept ON dept.`deptno`=emp.`deptno`;
SELECt * FROM emp INNER JOIN dept ON dept.`deptno`=emp.`deptno` ;
SELECt * FROM emp LEFT JOIN dept ON dept.`deptno`=emp.`deptno` ;
SELECt * FROM emp RIGHT JOIN dept ON dept.`deptno`=emp.`deptno` ;
#子查询
SELECt * FROM emp WHERe deptno=(SELECt deptno FROM dept WHERe dname='research') ;
#练习3：查询部门地址在二区的所有员工信息
SELECt * FROM dept,emp WHERe dept.`deptno`=emp.`deptno` AND loc='二区';
select * from dept join emp on dept.`deptno`=emp.`deptno` where loc='二区';
#子查询
select * from emp where deptno in(select deptno from dept where loc='二区' );
#视图:计就是一个特殊的表，缓存上次查询的结果
#1.创建视图
CREATE VIEW emp_view AS;
SELECt * FROM emp WHERe ename LIKE '%a%'; #模糊查询，名字里包含a的
#2.使用视图
SELECt * FROM emp_view;
#统计每个岗位的老师的人数
SELECt COUNT(1),prof FROM teachers GROUP BY prof HAVINg prof='副教授';
#统计每个系最年长的老师名字
SELECt MIN(YEAR(tbirthday)),tname,depart FROM teachers GROUP BY depart;
SELECt MAX(YEAR(NOW())-YEAR(tbirthday)) age,tname,depart FROM teachers GROUP BY depart;
#查询大于平均年龄的老师
SELECt tname FROM teachers WHERe tbirthday<(
SELECt AVG(tbirthday) FROM teachers);
SELECt tname FROM teachers WHERe tbirthday<'1969-07-08';
#查询男教师及其所上的课程
#笛卡尔积
SELECt courses.`cname` FROM courses,teachers WHERe courses.`tno`=teachers.`tno` #表关系
AND teachers.tsex='男';#业务关系
#连接查询 :小表驱动大表，高效
SELECt tsex,cname FROM courses INNER JOIN teachers ON courses.`tno`=teachers.`tno` WHERe tsex='男';
#子查询:in子查询，因为第一次查到了多个结果
SELECt cname FROM courses WHERe tno IN(SELECt tno FROM teachers WHERe tsex='男');
#了解insert into
#给指定列插入值
INSERT INTO tb_user(id) VALUES(5);
