《Linux与数据库》课件4. SQL语言.pptx

1、复习测试复习测试-选择题选择题 关系模型理论在_年提出A 1879 B 1970 C 1983 逻辑设计的任务是A 得到ER图 B 得到建表语句 C 在数据库产品中创建表 一个表满足第二范式，而且不存在_依赖，则它满足第三范式A 部分依赖 B 完全依赖 C 传递依赖 在Oracle Linux 8启动服务的工具是A system B systemctl C mysqld 若root帐号的密码为Root1995，以mysql工具连接服务器的命令为A mysql-uroot-pRoot1995 B mysql-uroot-p Root1995 C mysql-u root-p Root1995 D

2、 mysql-uroot-pRoot1995 复习测试复习测试-选择题选择题 在mysql中切换至db数据库的命令为A mysql use db B mysql connect db C mysql change db D mysql db 在db数据库中执行/scripts/scott.sql的命令为 A mysql source/scripts/scott.sql B mysql source scott.sql C mysql./scripts/scott.sql D mysql/scripts/scott.sql 复习测试复习测试-简答题简答题 如何把不满足第三范式的表转化为满足第三范

3、式 简述关系模型的三个要素 简述关系模型的特点 简述画出ER图之前的准备工作 简述如何把一对多的联系转化为表SQL 操作关系型数据库的标准语言 Structured Query LanguageSQL历史历史 1970，Codd提出关系模型 1973，IBM启动System R项目 Donald D.Chamberlin和Raymond F.Boyce负责操作工具的开发 1974，DML与DDL成形 称为SEQUEL(Structured English Query Language)Raymond F.Boyce因为脑动脉瘤去世 1980，重命名为SQL SEQUEL已被英国飞机公司Hawk

4、er Siddeley用作商标.SQL,Structured Query Language，结构化查询语言 SQL标准 SQL-86,SQL-89,SQL-92,SQL:1999,SQL:2003,SQL:2006,SQL:2008,SQL:2011,SQL:2016Chamberlin(1944-)1966,BS,Engineering,Harvey Mudd College 1967,MS,EE,Stanford University 1971,PhD,EE,Stanford UniversitySQL分类分类 DML Data Mnaipulation Language select,d

5、elete,update,insert DDL Definition create,drop,alter,truncate table DCL Control grant,revokeMySQL的客户端工具的客户端工具SQL执行方式 直接执行 嵌入至其他编程语言 使用API 字符界面 mysql 图形界面 MySQL Workbench 第三方工具(Windows)SQLyog navicat使用使用mysql客户端工具客户端工具 连接至本地服务器rootlaw#mysql-uroot-p 连接服务器时，指定数据库rootlaw#mysql-uroot-p hr简单信息查询简单信息查询 查看M

6、ySQL版本mysql select version();查看当前用户mysql select user();查看所有数据库mysql show databases;查看当前数据库mysql select database();查看当前时间mysql select now();查看当前数据库中的所有表mysql show tables;查看表结构mysql desc t;基本操作基本操作 创建数据库mysql create database db;切换数据库mysql use db 执行OS命令(system或!)mysql system clearmysql!lsanaconda-ks.cf

7、g scott.sql 执行SQL脚本mysql source scott.sql 修改执行过的代码mysql edit执行执行SQL脚本文件创建测试数据脚本文件创建测试数据 用vi提前在当前目录下准备好scott.sql脚本文件，然后执行下面命令mysql create database hr;Query OK,1 row affected(0.00 sec)mysql use hrDatabase changedmysql source scott.sql最简单的查询最简单的查询mysql select*from dept;+-+-+-+|deptno|dname|loc|+-+-+-+|

8、10|ACCOUNTING|NEW YORK|20|RESEARCH|DALLAS|30|SALES|CHICAGO|40|OPERATIONS|BOSTON|+-+-+-+简单查询简单查询-where条件条件mysql select*-from emp -where sal 2000;mysql select ename,sal -from emp -where sal 2000 and deptno=10;mysql select ename,sal -from emp -where sal between 1500 and 3000;mysql select ename,sal -fro

9、m emp -where comm is null;where comm is null;简单查询简单查询-列别名列别名mysql select deptno as dno,dname as dept name,loc city -from dept -;+-+-+-+|dno|dept name|city|+-+-+-+|10|ACCOUNTING|NEW YORK|20|RESEARCH|DALLAS|30|SALES|CHICAGO|40|OPERATIONS|BOSTON|+-+-+-+简单查询简单查询-order by与与limit子句子句mysql select ename,sal

10、 from emp -where deptno=10 -order by sal -;mysql select ename,sal from emp -where deptno=10 -order by sal -limit 2,3 -;说明：limit m,n的用法是略过前m行，然后取出n行table关键字关键字(始自始自8.0.19)select*from table_name可以替换为table table_namemysql table dept;mysql table dept order by dname limit 1,2;使用使用row关键字，自组表关键字，自组表(始自始自8.

11、0.19)mysql select*from -(values row(1,2,3),row(4,5,6)as t;+-+-+-+|column_0|column_1|column_2|+-+-+-+|1|2|3|4|5|6|+-+-+-+mysql select*from -(values row(1,2,3),row(4,5,6)as t(a,b,c)-where a=1;+-+-+-+|a|b|c|+-+-+-+|1|2|3|+-+-+-+字符串条件查询字符串条件查询 字符串常量表示方法 默认是否区分大小写 模糊查询，%,_ 条件中包括特殊字符：%,_,复习测试复习测试1.update命

12、令属于_。A.DDL B.DCL C.DML 2.select语句的from子句用于指定_。A.列名 B.表名 C.数据库名 D.表名或列名3.下面描述查询语句的顺序中，正确的是_。A.select.from.where.order by.B.select.from.order by.where.C.select.order by.from.where.复习测试复习测试mysql select distinct job from emp;+-+|job|+-+|CLERK|SALESMAN|%RD%|R_D|+-+1.select job from emp where job=CLERK 的查

13、询结果为：A.CLERK B.执行时，出现语法错误 C.空集2.select job from emp where job like%的查询结果为：A。%RD%B.与上面的查询结果相同 C.空集 D.以上均错 E.执行时，出现语法错误 3.select job from emp where job like%_ 的查询结果为：A.R_D B.与上面的查询结果相同 C.空集 D.以上均错 E.执行时，出现语法错误 4.select job from emp where job=%_%的查询结果为:A。%RD%B.R_D C.空集 D.以上均错 E.执行时，出现语法错误 复习测试复习测试mysql

14、 select empno from emp order by empno;+-+|empno|+-+|7369|7499|7521|7566|7654|7698|7782|7839|+-+说出下面查询结果1.select empno from emp order by empno limit 1,2;2.select empno from emp order by empno limit 2;3.select empno from emp order by empno desc limit 2;分类汇总分类汇总mysql select deptno,max(sal)-from emp -gr

15、oup by deptno -;+-+-+|deptno|max(sal)|+-+-+|10|5000.00|20|3000.00|30|2850.00|+-+-+汇总函数汇总函数 max(),min(),avg(),count(),sum(),group_concat group_concat(column_name1 order by column_name2 separator.)where子句：限定参与汇总的行 group by子句：指定分组列 having子句：过滤分组结果 order by子句：指定排序列 MySQL不支持汇总函数的嵌套：max(count(*)简单汇总练习简单汇总

16、练习 查询emp表的最高工资 查询emp表的最低工资 查询emp表的记录个数 查询emp表中部门号20中的记录个数 查询emp表中不重复的部门编号 查询emp表中20号部门的最高工资使用使用group by执行分类汇总练习执行分类汇总练习 查询emp表中每个部门的sal总和。查询emp表中，每个部门工资超过2000的那些员工的工资总和。使用使用having子句过滤分组汇总的结果子句过滤分组汇总的结果 查询工资总额超过7000的部门编号及其工资总额，并以部门编号从小到大排序各子句的处理顺序各子句的处理顺序 执行where子句，过滤符合条件的记录。执行group by子句，对以上结果以分组汇总。执

17、行having子句，对分组后的查询结果进行过滤。执行order by子句，对查询结果以指定列排序。执行select子句，显示查询结果。分组汇总的注意事项分组汇总的注意事项 where子句置于group by之前。不能在where子句中使用分组函数。having子句一般会使用分组函数，若不需使用分组函数，则推荐使用where子句。在语法顺序上，having子句可放置于group by子句之前或之后，推荐放置于group by子句之后。若未被分组函数作用，select、having和order by子句中的列只能选用group by子句中的列。子查询子查询mysql select*from -(s

18、elect ename,sal from emp)as t -;mysql select ename,(select avg(sal)from emp where deptno=e.deptno)dept_avg_sal -from emp e -;mysql select ename,sal -from emp -where sal=(select max(sal)from emp where deptno=10)-;mysql select dname from dept -where deptno in -(select deptno from emp group by deptno h

19、aving avg(sal)2000)-;not in查询结果为空查询结果为空 查询没有员工的部门名称mysql select dname from dept where deptno not in(select distinct deptno from emp);+-+|dname|+-+|OPERATIONS|+-+1 row in set(0.00 sec)mysql update emp set deptno=null where ename=MILLER;Query OK,1 row affected(0.00 sec)Rows matched:1 Changed:1 Warning

20、s:0mysql select dname from dept where deptno not in(select distinct deptno from emp);Empty set(0.00 sec)not exists与与exists使用子查询实现汇总函数嵌套调用效果使用子查询实现汇总函数嵌套调用效果 查询emp表中平均工资最高的deptno 查询emp表中部门人数最多的deptno,dname单选单选-查询获得最高工资的员工名称查询获得最高工资的员工名称 select ename sal from emp where sal=max(sal)select ename,sal fro

21、m emp where sal=(select max(sal)from emp)select ename,max(sal)from emp select ename,(select max(sal)from emp)from emp单选单选-查询每个员工名称及其部门名称查询每个员工名称及其部门名称 select ename,(select dname from dept)from emp select ename,(select dname from dept where deptno=deptno)from emp select ename,(select dname from dept

22、where deptno=e.deptno)from emp e select(select ename from emp where deptno=d.deptno),dname from dept d多选多选-查询各部门人数的最大值查询各部门人数的最大值 select max(count(*)from emp select max(cnt)from(select count(*)cnt from emp)a select max(count(*)from emp group by deptno select max(cnt)from(select count(*)cnt from emp

23、group by deptno)a select max(cnt)from(select deptno,count(*)cnt from emp group by deptno)a select max(cnt)from(select count(*)cnt from emp)a group by deptno表连接表连接-传统语法传统语法 交叉连接mysql select*from emp,dept;内连接mysql select e.ename,d.dname -from emp e,dept d -where e.deptno=d.deptno -;表连接表连接-SQL-92语法语法 交

24、叉连接mysql select*from emp cross join dept;内连接mysql select e.ename,d.dname -from emp e inner join dept d -on e.deptno=d.deptno;mysql select e.ename,d.dname -from emp e natural join dept d -;mysql select e.ename,d.dname -from emp e join dept d -using(deptno)-;SQL-92语法语法-单表条件附加方法单表条件附加方法mysql select e.e

25、name,d.dname -from emp e inner join dept d -on e.deptno=d.deptno -where e.sal 2500 -;mysql select e.ename,d.dname -from emp e inner join dept d -on e.deptno=d.deptno -and e.sal 2500 -;外连接外连接 left outer join right outer join 不支持full outer join 可以使用左外连接和右外连接的union得到构造复杂查询构造复杂查询#mysql law select ename,

26、sal -from emp -order by sal limit 3;+-+-+|ename|sal|+-+-+|SMITH|800.00|JAMES|950.00|WARD|1250.00|+-+-+mysql select ename,sal from emp order by sal limit 1,2;+-+-+|ename|sal|+-+-+|JAMES|950.00|WARD|1250.00|+-+-+集合操作集合操作 union/union all intersect(至8.0.27，MySQL尚未支持)except(至8.0.27，MySQL尚未支持)增删改增删改 inser

27、tmysql insert into emp(empno,ename,sal)-values(8888,Clinton,3000),(9999,Bush,null);mysql create table emp_copy like emp;mysql insert into emp_copy select*from emp;updatemysql update emp set sal=sal+1000,comm=1500 -where deptno=10;deletemysql delete from emp where empno 8000;增删改增删改 replacemysql selec

28、t*from dept;+-+-+-+|deptno|dname|loc|+-+-+-+|10|ACCOUNTING|NEW YORK|20|RESEARCH|DALLAS|30|SALES|CHICAGO|40|OPERATIONS|BOSTON|+-+-+-+4 rows in set(0.00 sec)mysql replace dept values(50,OPR,PITTS);Query OK,2 rows affected(0.11 sec)mysql select*from dept;+-+-+-+|deptno|dname|loc|+-+-+-+|10|ACCOUNTING|N

29、EW YORK|20|RESEARCH|DALLAS|30|SALES|CHICAGO|40|OPR|PITTS|+-+-+-+4 rows in set(0.00 sec)数据类型数据类型 int decimal(p,s)或numeric(p,s)1 p 65,0 s 30,s p 若省略参数，则表示整数，p默认为10 若省略s，则默认为0datetime(p)以8B存储，秒可以精确至小数点后6位，p默认为01000-01-01 00:00:00.000000 9999-12-31 23:59:59.999999timestamp(p)以4B存储，秒可以精确至小数点后6位，p默认为01970

30、-01-01 00:00:01 UTC 2038-01-19 03:14:07 UTCinsert或update记录时，此类型的列可设置自动以当前时间填充存储时，会转换为存储时，会转换为UTC时间时间 客户端的当前时区为服务器时区 数据类型数据类型-字符串字符串 char(n)0 n 255 varchar(n)0 n 65,535 blob与text 长度 create table t(a int,b char(10);自增列自增列 建表时对列附加自增选项mysql create table t(a int auto_increment primary key,b int);自增整数类型可以

31、用serial简化mysql create table tt(a serial,b int);说明：serial等同于BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 系统变量auto_increment_offset和auto_increment_increment分别设置初值和步长，默认均为1自填充/更新的timestamp/datetime列create table t1(a timestamp default current_timestamp on update current_timestamp,b int);mysql insert i

32、nto t1(b)values(1);mysql select*from t1;+-+-+|a|b|+-+-+|2018-05-15 09:45:59|1|+-+-+约束种类约束种类 primary key 8.0.13开始，增加sql_require_primary_key参数，以设置是否强制主键 unique foreign key check 8.0.16开始支持，之前版本语法不报错，但实际不支持 not null建表时附加约束建表时附加约束mysql create table p(-a int,-b int,-c varchar(5)not null,-primary key(a,),

33、#名称不会生效，其名称总为PRIMARY -constraint ck_p check(b100),#8.0.16版本开始生效 -constraint uq_p unique(c);mysql create table c(-x int primary key,-y int,-constraint fk_c foreign key(y)references p(a)ON update CASCADE -);说明：MySQL的外键只能加在表级，列级references不报错，但无效。外键指向的列不需要附加唯一或主键约束，但指向的列需要附加索引拷贝表拷贝表 只拷贝表结构(连同约束一起拷贝)mysq

34、l create table t like dept;表和数据一起拷贝(不会拷贝约束)mysql create table t as select*from dept;mysql insert into t select*from dept;修改表的结构修改表的结构mysql alter table p modify c varchar(10);mysql alter table t add constraint uq_p unique(b);mysql alter table t add column c int after a;mysql alter table t add column

35、c int first;mysql alter table t drop column c;mysql alter table t modify b int not null;mysql alter table t rename column b to bb;mysql alter table t change column c cc int;#数据类型不能省略mysql alter table t add column d char(10),add column e int after d,-change column cc c int;删除约束删除约束 alter table命令不支持dr

36、op constraint选项(8.0.19已经支持)通过删除索引，删除唯一约束mysql drop index uq_ename on emp;删除主键约束mysql alter table t drop primary key;删除指定外键约束mysql alter table t drop foreign key fk_t;删除检查约束mysql alter table t drop check ck_t;alter table t drop constraint ck_t;删除表删除表 drop table t1,t2 可附加restrict或cascade，但无实际作用表的系统信息查

37、询表的系统信息查询 数据库中所有的表mysql show tables;表结构mysql desc t;建表语句mysql show create table t G;*1.row*Table:tCreate Table:CREATE TABLE t(a char(10)DEFAULT NULL)ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set(0.00 sec)查询约束信息查询约束信息 mysql show create table emp 查询emp表上的所有约束mysql select table_name,constraint_name,

38、constraint_type -from information_schema.table_constraints where table_name=emp;+-+-+-+|table_name|constraint_name|constraint_type|+-+-+-+|emp|PRIMARY|PRIMARY KEY|emp|fk_deptno|FOREIGN KEY|+-+-+-+查询外键约束信息mysql select table_name,constraint_name,-referenced_table_name,unique_constraint_name -from info

39、rmation_schema.referential_constraints where table_name=emp;查询外键信息查询外键信息mysql select table_name,constraint_name,column_name,-referenced_table_name as ref_table,referenced_column_name as ref_column -from information_schema.key_column_usage -where table_schema=db and table_name=emp;+-+-+-+-+-+|table_n

40、ame|constraint_name|column_name|ref_table|ref_column|+-+-+-+-+-+|emp|PRIMARY|empno|NULL|NULL|emp|fk_deptno|deptno|dept|deptno|+-+-+-+-+-+视图视图 起名称的查询 查看定义 SHOW CREATE VIEW information_schema.views可更新视图可更新视图 视图中的行与表中的行的关系：一对一 不可更新视图，含有以下内容 汇总函数 distinct group by having unino或union all select子句中的子查询(非相

41、关子查询可以执行insert)where子句中的子查询引用了from子句的表 ALGORITHM=TEMPTABLE 确认是否可更新 INFORMATION_SCHEMA.VIEWS.is_updatable g和和Gg，默认，与;同义mysql select ename,sal from emp where sal 3000 -g+-+-+|ename|sal|+-+-+|KING|5000.00|+-+-+mysql select ename,sal from emp where sal=3000G*1.row*ename:KING sal:5000.00*2.row*ename:FORD

42、 sal:3000.002 rows in set(0.00 sec)设置设置pager 设置pager为more(分屏显示输出结果)mysql pager more 去除pager设置mysql pager 过滤指定行mysql pager grep NEWPAGER set to grep NEWmysql select*from dept;|10|ACCOUNTING|NEW YORK|显示查询结果的md5哈希值mysql pager md5sumPAGER set to md5summysql select*from dept;746fa39a7581d849a48b9ca47563c

43、1f2 -把查询结果导出为文件把查询结果导出为文件rootlaw mysql#mysql-uroot-proot law-e select*from dept dept.dat设置设置promptmysql prompt SQLd uPROMPT set to SQLd u SQLlaw root把把mysql环境配置保存至文件环境配置保存至文件rootlaw#fclient#silentuser=rootpassword=Root1995auto_vertical_outputdatabase=lawmysqlpager=more#prompt=SQLd delimiter=/导出会话内容导

44、出会话内容-tee/noteemysql tee law.txtLogging to file law.txt#由此开始的所有内容除了输出至屏幕外，还会存入law.txt，直到执行notee命令mysql notee执行执行sql脚本脚本rootlaw#mysql-uroot-p law source scott.sqlmysql.scott.sql下载下载sakila示例数据库示例数据库示例数据库网址示例数据库网址http:/ Downloads#wget Downloads#tar-xzf sakila-db.tar.gz创建数据库创建数据库rootlaw sakila-db#mysql-

45、u root-p sakila-schema.sql装载数据装载数据rootlaw sakila-db#mysql-u root-p select table_name from information_schema.tables -where table_type=BASE TABLE and table_schema=sakila;查看sakila数据库中的视图(7个)mysql select table_name from information_schema.views -where table_schema=sakila;sakila示例数据库说明文档示例数据库说明文档 https:/ 服务器端关闭防火墙 解决低版本navicat密码验证方法与MySQL不一致mysql create user tian identified with mysql_native_password by Tian1995;mysql grant all on*.*to tian with grant option;