约束： 1 not null ： 用于控制字段的内容一定不能为空（NULL）。 用法 ：Create table MyTable ( id varchar(32) not null, name varchar (32) ) 2 Unique ：控件字段内容不能重复，一个表允许有多个 在Sql Server、 My Sql 支持的添加 Unique 语法：可同时添加几个字段为约束 如: unique (id,name) Create table MyTable ( id varchar(32) not null, name varchar (32), unique (id,.....) ) 3 Primary Key ：也是用于控件字段内容不能重复，但它在一个表只允许出现一个。 Create table myTB1 ( id nvarchar(32) not null, name nvarchar(32), primary key (id) ) 4 Foreign Key ： 外键约束：用于限制某个表中列数据，存放的必须是了另一种那个表中主键列数据； create table test( id int(5) primary key , dno int(5), foreign key (dno) REFERENCES dept(deptno) ) 5 Check ：用于控制字段的值范围。 Create table myCheck ( id nvarchar(32) not null, age int not null, check (age>0 and age <150) ) 6 Default ：用于设置新记录的默认值。 Create table myDefault ( id int, name nvarchar(32) default 'celly' ) 排序： order by 只能用在sql语句最后面，where最后面 例如：select * from user order by id ; select * from user order by id desc ; select * from user order by age asc, id desc ; asc升序，desc降序 多列排序时，先按照第一列排序，第一列数据相同的一组，再按照第二列排序，以此类推； CREATE TABLE emp( empno INT PRIMARY key, ename VARCHAR(30), job VARCHAR(30), mgr INT, hiredate DATE, sal FLOAT, comm FLOAT, deptno INT ); INSERT into emp VALUES(1007,'张三','Clerk',7902,'1980-12-17',1800,0,20); INSERT into emp VALUES(7499,'Allen','Salesman',7698,'1981-2-20',1600,300,30); INSERT into emp VALUES(7844,'Turner','Salesman',7499,'1981-9-8',1500,0,30); INSERT into emp VALUES(7698,'Tom','Manager',0,'1981-9-8',6100,600,40); INSERT into emp VALUES(7876,'Adams','Clerk',7900,'1987-5-23',1100,0,20); INSERT into emp VALUES(7900,'James','Clerk',7698,'1981-12-3',2400,0,30); INSERT into emp VALUES(7902,'Ford','Analyst',7698,'1981-12-3',3000,null,20); INSERT into emp VALUES(7901,'Kik','Clerk',7900,'1981-12-3',1900,0,30); mysql常用函数： ifnull(列，值)判断是否为空，如果为空，取第二个参数值； 日期格式化： mysql默认日期格式%Y-%m-%d %h:%i:%s select now();--得到当前时间 select date_format(now(),'%Y年%m月%d日 %H点%i分%s秒'); --格式化时间 转存失败 重新上传 取消 转存失败 重新上传 取消 获取年、月、日 select year('1987-10-10'); select month('1987-10-10'); select day('1987-10-11'); SELECT CURDATE();--当前日期 SELECT DATEDIFF('1997-12-30 23:59:59','1997-12-20'); --两个日期之间的天数 SELECT DAYOFMONTH () 第几天 SELECT ADDDATE('1997-12-30 23:59:59',10);--加日期 SELECT SUBDATE ('1997-12-30 23:59:59',10);--减日期 去重复：distinct last_day 指定日期所在月的最后一天，返回日期类型 例如：查询所有员工入职日期所在月最后一天是几号； select LAST_DAY(hiredate) from emp substr(字符串或者列 from 下标1开始 for 截取长度) 例如：select SUBSTR(hiredate FROM 1 FOR 4) from emp concat函数，用于连接字符串，参数个数不固定 例如：查询emp数据，要求 ename empno hiredate 显示在一列中，用|隔开 select CONCAT(ename,'|',empno,'|',hiredate) str from emp 注意： null 连接其他字符串，使用concat会产生空 例如：select concat(ifnull(null,''),'dfd') from emp; 可以解决该问题； 大小写转化： lower（str） upper(str) 例如：select CONCAT(lower(ename),'|',empno,'|',hiredate) str from emp 这里也说明函数可以嵌套； 求字符串中字符个数：CHAR_LENGTH（str） 注意：select length('张三李四') ; length方法求的是字节数； select CHAR_LENGTH('张三李四') 字符串的替换： replace select REPLACE('abcdefg','a','A') 将第一个参数字符串，中第二个参数指定的子字符串，替换为第三个参数指定的字符串 四舍五入：round(number) round (number,number) 例如：select ROUND(123.325,2) 保留两位小数 例如：select ROUND(123.325) 取整 adddate(日期，数字天数) 添加天数 例如：查询员工试用期满三个月，转正的日期； select ADDDATE(hiredate,90) ,hiredate from emp 类型的转化：cast（列或者字段 as 类型） 例如：select concat(CAST(10 as char),20,sal) from emp 这里转不转都无所谓； 分组查询： 分组函数：什么是分组函数 分组函数运算每一组记录,每一组返回一个结果.（忽略空） ysum()求和 min(）最大值 max（）最小值 count()统计函数（统计行记录数） 分组操作：group by AVG ([DISTINCT|ALL]n) 求平均值 SELECT AVG(age) FROM user; COUNT ({ *|[DISTINCT|ALL]expr})计数， SELECT COUNT(id) FROM user; MAX ([DISTINCT|ALL]expr)最大数 SELECT MAX(age) FROM user; MIN ([DISTINCT|ALL]expr)最小数 SELECT MIN(age) FROM user; SUM ([DISTINCT|ALL]n)求合 SELECT SUM(age) FROM user; 注意：组函数分组group by使用时注意 GROUP BY子句 出现在SELECT列表中的字段，如果出现的位置不是在组函数中，那么必须出现在GROUP BY子句中 SELECT SUM(age) ,name FROM user GROUP BY name; HAVING having 相当于where 与where的唯一区别是 当查询语句中有 组函数 的时候 就不能用where 了 只能用having SELECT SUM(age) ,name FROM user GROUP BY name HAVING name='王帅'; 多表查询： 连接多个表 在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。 使用表连接从多个表中查询数据等值连接 SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; 在 WHERE 子句中写入连接条件 当多个表中有重名列时，必须在列的名字前加上表名作为前缀 使用表的别名 使用表的别名简化了查询 提高了查询的性能 新建学生-课程数据库的三个表: 学生表:Student(Sno,Sname,Ssex,Sage,Sdept) Sno为主码; 学号Sno 姓名Sname 性别Ssex 年龄Sage 所在系Sdept 课程表:Course(Cno,Cname,Cpno,Credeit) Cno为主码; Course: 课程号Sno 课程名Cname 先行课Cpno 学分Credit 学生选修表:SC(ID,Sno,Cno,Grade) 学号Sno 课程号Cno 成绩Grade DROP TABLE Student; DROP TABLE Course; DROP TABLE SC; CREATE TABLE Student ( Sno INT AUTO_INCREMENT PRIMARY KEY, Sname VARCHAR(30) , Ssex VARCHAR(4) , Sage INT, Sdept VARCHAR(30) ); CREATE TABLE Course ( Cno INT AUTO_INCREMENT PRIMARY KEY, Cname VARCHAR(30) , Cpno INT, Credeit INT ); CREATE TABLE SC ( ID INT AUTO_INCREMENT PRIMARY KEY, Sno INT, Cno INT, Grade INT ); INSERT INTO Student (Sname,Ssex,sage, Sdept) VALUES ('同学1', '男',28, '计算机') ; INSERT INTO Student (Sname,Ssex,sage, Sdept) VALUES ('同学2', '女',28, '计算机') ; INSERT INTO Student (Sname,Ssex,sage, Sdept) VALUES ('同学3', '男',28, '计算机') ; INSERT INTO Student (Sname,Ssex,sage, Sdept) VALUES ('同学4', '女',28, '计算机') ; INSERT INTO Student (Sname,Ssex,sage, Sdept) VALUES ('同学5', '男',28, '计算机') ; INSERT INTO Student (Sname,Ssex,sage, Sdept) VALUES ('同学6', '女',28, '计算机') ; INSERT INTO Student (Sname,Ssex,sage, Sdept) VALUES ('同学7', '男',28, '计算机') ; INSERT INTO Student (Sname,Ssex,sage, Sdept) VALUES ('同学8', '女',28, '计算机') ; INSERT INTO Course (Cname, Cpno, Credeit) VALUES ('JAVA',1,90) ; INSERT INTO Course (Cname, Cpno, Credeit) VALUES ('JAVA',2,91) ; INSERT INTO Course (Cname, Cpno, Credeit) VALUES ('JAVA',3,92) ; INSERT INTO Course (Cname, Cpno, Credeit) VALUES ('JAVA',4,93) ; INSERT INTO Course (Cname, Cpno, Credeit) VALUES ('JAVA',5,94) ; INSERT INTO Course (Cname, Cpno, Credeit) VALUES ('JAVA',6,95) ; INSERT INTO Course (Cname, Cpno, Credeit) VALUES ('JAVA',7,96) ; INSERT INTO Course (Cname, Cpno, Credeit) VALUES ('JAVA',8,97) ; INSERT INTO Course (Cname, Cpno, Credeit) VALUES ('JAVA',9,98) ; INSERT INTO SC (Sno, Cno, Grade) VALUES (1,1,1) ; INSERT INTO SC (Sno, Cno, Grade) VALUES (2,2,2) ; INSERT INTO SC (Sno, Cno, Grade) VALUES (3,3,3) ; INSERT INTO SC (Sno, Cno, Grade) VALUES (4,4,4) ; INSERT INTO SC (Sno, Cno, Grade) VALUES (5,5,5) ; INSERT INTO SC (Sno, Cno, Grade) VALUES (6,6,6) ; INSERT INTO SC (Sno, Cno, Grade) VALUES (7,7,7) ; 在介绍左连接、右连接和全连接前， 有一个数据库中重要的概念要介绍一下，即空值(NULL)。 有时表中，更确切的说是某些字段值，可能会出现空值, 这是因为这个数据不知道是什么值或根本就不存在。 空值不等同于字符串中的空格，也不是数字类型的0。因此，判断某个字段值是否为空值时不能使用=,<>这些 判断符。必需有专用的短语：IS NULL 来选出有空值字段的记录，同理，可用 IS NOT NULL 选出不包含空值的记录。 内连接 inner join select * from Student s inner join Course c on s.sno=c.cno; 左连接left join（左表中所有数据，右表中对应数据） select * from Student s left join Course c on s.sno=c.cno; 右连接right join（右表中所有数据，左表中对应数据）select * from Student s right join Course c on s.sno=c.cno; 全连接full outer join select * from user where id in (select id from user where id>10) 分页查询： 通过limit关键词实现 例如：select * from emp limit 0,5; 表示从0下标那行记录开始，查5条记录； 子查询： • 当一个查询是另一个查询的条件时，称之为子查询。 • 子查询可以使用几个简单命令构造功能强大的复合命令。 • 子查询最常用于SELECT-SQL命令的WHERE子句中。 一个子查询必须放在圆括号中。 将子查询放在比较条件的右边以增加可读性。 子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句， 并且如果指定了它就必须放在主 SELECT 语句的最后。 谁的薪水比张三 多? 为了解决这个问题，你需要两个查询：一个找出张三 的收入，第二个查询找出收入高于张三 的人。 你可以用组合两个查询的方法解决这个问题，放置一个查询到另一个查询中。 内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值 子查询(内查询) 在主查询之前执行一次 子查询的结果被用于主查询(外查询) 子查询是一个SELECT 语句，它是嵌在另一个 SELECT 语句中的子句。 用子查询你可以用简单的语句构建功能强大的语句。当你需要从表中用依赖于表本身的数据选择行时它们是非常有用的。 你可以将子查询放在许多的 SQL 子句中，包括： � WHERE 子句 � HAVING 子句 � FROM 子句 （1）一个查询sql获取到的结果集，可能会作为条件出现即select语句会出现在where子句中； （2）查询结果集作为一个虚表出现； 注意：select 语句作为where子句出现时，如果用> = >= <= != 比较时不能反回多行； 题:::找出平均薪水为最低平均薪水的工作岗位。 求最低各工作岗位的平均薪水,再求最低,比较 select job from emp group by job having avg(sal)=( select min(asjob) from (select avg(sal) asjob from emp group by job) ttt); select avg(sal) asjob,job from emp group by job;安岗位分组 得到平均薪水 也就是说单行子查询就是用子查询来生成“一个”参考值让我们来比较 注意：如果子查询返回的是零值，不会对主程序造成影响； 如果子查询返回的是空值，那么会影响主程序的返回值； 多行子查询：从内 SELECT 语句返回多行的查询 select * from emp where deptno in(SELECT deptno from dept) 通过MySQL客户端对数据库进行管理 MySQL是一个真正的多用户、多线程SQL数据库服务器。 表类型 ISAM表 索引顺序储存方法。是旧的MySQL标准，用于3.23.0以前的版本。 MyISAM表 从3.23.0开始，MyISAM表代替了ISAM表。 MyISAM索引ISAM表小得多，相通会使用更少的资源来执行带有索引的SELECT 语句。 MERGE表 MERGE表是相通的MyISAM表的合并，从3.23.24中开始引进的。 优点:很多情况速度更快、表的尺寸小、 缺点：对于某些搜索(eq_ref)，速度很慢。 HEAP表 存储在内存里，速度最快的表。 数据可能会在出现问题后丢失，而且不能保留太多数据。 InnoDB表 是事务安全的表类型。 MyISAM表中在插入是整个表被锁定。InnoDB 只锁定该记录。所以如果执行大量的UPDATE 和INSERT，应该使用InnoDB；如果主要用来执行SELECT，MyISAM是更好的选择。 BDB表 伯克利数据库。接口不甚稳定。 事务（面试题）： 事务是一组数据库端的执行单元， 要么都执行，要么恢复到原始状态； 多条ｉｎｓｅｒｔ/ｄｅｌｅｔｅ／ｕｐｄａｔｅ语句，要么都执行，如果其中一条sql语句执行失败，此时所有sql语句都不执行 特性 事务是恢复和并发控制的基本单位。 事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 原子性和一致性是相辅相成的就是说我们事物的一系列操作不可分割如果成功就全部成功，如果失败就全部失败，这即是一种原子性其实也是事物一致性的一种体现。 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。 很多的事物同时在数据库时执行，他们相互之间应该是隔离的不应该相互干扰。 持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。 数据库范式： mysql、Oracle、sqlServer都是关系型数据库 对象型数据库：nosql 主要用于对象的缓存 数据库范式是针对关系型数据库在设计表时需要遵循某些规则，这些规则有助于优化数据的存储，减少数据的冗余； 数据库范式有六种（面试题：范式有几种？你项目中表示如何设计的？三种范式什么规则-你对数据库三种范式怎么理解的？）： 必须知道：一范式、二范式、第三范式 ；其他忽略；巴斯-科德范式， 第四范式，第五范式 数据库的三范式： 第一范式：数据库表中列，拆到不能拆为止。 例如： 此时，电话列，存放固定电话号码也存移动电话，此时这一列使整体数据有冗余。 编号 姓名 电话 1001 zyg 15845689182 1001 zyg 010-67676767 修改方法： 编号 姓名 固定电话 移动电话 1001 zyg 010-67676767 15845689182 第二范式：（满足第一范式前提下）减少非主键列之间的依赖关系，适当的使用外键（拆成多个表） 也就是说要求数据表里的所有数据都要和该数据表的主键有完全依赖关系；如果有哪些数据只和主键的一部份有关的话，它就不符合第二范式。 例如： 学生信息表以及成绩，课程都设计在一个表中。（有冗余数据） 学号 姓名 课程 成绩 1001 zyg 1 78 1001 zyg 2 90 修改为： 成绩表： 学生表： 课程表： 序号 成绩，课程编号，学号 学号 姓名 课程 课程名称 1 89 1 1001 1001 zyg 1 css 2 90 2 1001 2 HTML 例如： 下面满足一范式，二范式，不满足三范式： 学生表： 课程表： 序号 成绩，课程编号，学号 学号 姓名 学院编号 学院名称 课程 课程名称 1 89 1 1001 1001 zyg 1 信息学院 1 css 2 90 2 1001 1002 abc 1 学院信息表： 学院编号 学院名称 1 信息学院 第三范式：在满足一范式，二范式基础上，在表中不能含有另一张表的非主键列。 例如上面学生表中，出现的学院名称，在学员信息表中也存在，这是不满足第三范式的。 case when --简单Case函数CASE sex WHEN '1' THEN '男'WHEN '2' THEN '女'ELSE '其他' END --Case搜索函数CASE WHEN sex = '1' THEN '男'WHEN sex = '2' THEN '女'ELSE '其他' END 例如：查询工资在1000-2000 C级别 2001-3000 B级别 3001- A级别 ename , sal,strand 张三 2000 C 李四 4000 A select empno,ename ,(case when sal between 1000 and 2000 then 'C' when sal between 2001 and 3000 then 'B' else 'A' end ) stand from emp;
