数据库MySQL学习笔记（六）

对前面叙述的小总结

1、sql语句的分类：

      DDL：数据定义语言(create,drop,alter,truncate)

      DML：数据操纵语言(insert,delete,update)

      DQL：数据查询语言(select)

      DCL：数据控制语言(grant,revoke)

      TCL：事务控制语言(commit,rollback,savepoint)

2、drop、delete、truncate的区别：

      drop和truncate属于数据定义语言DDL，delete属于数据操纵语言DML。drop和truncate不可进行数据的回滚，drop可以用来删除整张表，truncate只是清空表的内容，表的结构依然存在。delete可以进行数据的回滚。

3、查询中关键字的顺序：

      书写顺序：select --> from --> where --> group by --> having --> order by

      执行顺序：from --> where --> group by --> having --> select --> order by

 

sql中的约束constraint

 

1、约束的种类有五种：

   （1）主键约束(primary key)

             要求作为主键的字段的字段值非空且唯一

   （2）非空约束(not null)

             要求有非空约束的字段不可以为null值

   （3）唯一性约束(unique)

             要求有唯一性约束的字段不可以重复，但是可以为null

   （4）检查性约束

             要求在插入数据时必须符合字段的检查条件，但是可以为null

             enum（条件）---  gender enum('f;,'m')

             check（条件）--- check(gender in ('f','m'))

   （5）外键约束(foreign key)

             要求：有外键约束的字段a必须依赖于另外一个字段b，字段b要有主键约束。字段a的值要么为null，要么必须是字段b里的值。

             例：create table t1(

                       tno int,

                       foreign key (tno) references t(tno);)

                       主表：t       从表：t1

             如果想从t表中删除记录的话，要先看主表的记录是否被从表引用，如果引用了，则需要先删除从表中的记录，再删除主表。如果没有引用则可以直接删除。

 

序列

 

      作为主键的字段，通常不是表中的主要信息，可以用来当成信息记录的序号。序号最好是有序的序列。auto_increment 关键字，用来对有主键约束的字段做自增操作。
用法:
  create table tname(
          tid int primary key auto_increment,
          ......
  );

  函数:last_insert_id();   作用：获取序列最后一次的值。
  select last_insert_id();

 

有关本篇涉及到知识的练习题

 

/*1.查询和SMITH同部门的员工信息
select deptno from emp where ename='smith'
select * from emp where deptno = 20

select * from emp where deptno = (
    select deptno from emp where ename='smith'
) and ename <> 'smith'*/

/*2.查询每个员工的工资，姓名和其部门的平均工资，
以及最大工资和最小工资。
select deptno,avg(sal) from emp group by deptno
select ename,sal,t.deptno,t.avg,t.max,t.min from emp,(
select deptno,avg(sal) avg,max(sal) max,min(sal) min 
from emp group by deptno) t 
where emp.deptno = t.deptno*/

/*3.查询工资比本部门平均工资高的员工的姓名和工资
select ename,sal,t.deptno,avg 
from emp join (
    select deptno,avg(sal) avg from emp group by deptno
) t on emp.deptno = t.deptno where emp.sal > t.avg*/

/*4.查询部门平均工资大于30部门平均工资的
部门平均工资，工资之和
select avg(sal) from emp where deptno = 30
select deptno,avg(sal),sum(sal) 
from emp group by deptno 
having avg(sal) > 1566

select deptno,avg(sal),sum(sal) from emp 
group by deptno having avg(sal) > (
    select avg(sal) from emp where deptno = 30
)*/

/*5.查询每个员工的姓名，工资，
及其部门的平均工资,工资之和。
select ename,sal,avg,sum from emp left join 
(
    select avg(sal) avg,sum(sal) sum,deptno 
 from emp group by deptno 
) t on emp.deptno = t.deptno

select ename,sal,(
    select avg(sal) from emp e2 
    where e1.deptno=e2.deptno
) avg,(
    select sum(sal) from emp e3 
    where e1.deptno=e3.deptno 
) sum from emp e1*/

/*6.查询ward,smith,clark的同部门同事
select deptno from emp where ename in 
('ward','smith','clark')

select ename,deptno from emp where deptno in (
    select deptno from emp 
        where ename in ('ward','smith','clark')
) order by deptno*/

/*7.创建表student，字段sno，sname，sage，要求sno字段
为主键
CREATE TABLE student(
    sno INT PRIMARY KEY,
    sname VARCHAR(20),
    sage INT
)

INSERT INTO student VALUES (NULL,'zhangsan',23)
INSERT INTO student VALUES (1,'zhangsan',23)
INSERT INTO student VALUES (2,'zhangsan',23)
INSERT INTO student VALUES (3,'zhangsan',22)*/

/*将年龄为22的学员sno设置为2
UPDATE student SET sno=2 WHERE sage = 22*/

/*8.创建表student，字段sno，sname，sage，要求sno字段
为主键，sname不允许为空

DROP TABLE    student

CREATE TABLE student(
    sno INT PRIMARY KEY ,
    sname VARCHAR(20) NOT NULL,
    sage INT
)

INSERT INTO student (sno,sage) VALUES(1,34)
INSERT INTO student VALUES(1,'zhangsan',NULL)*/

/*9.创建表student，字段sno，sname，sage，要求sno字段
为主键，sname的值要求不重复

DROP TABLE    student

CREATE TABLE student (
    sno INT PRIMARY KEY,
    sname VARCHAR(20) UNIQUE,
    sage INT
)

INSERT INTO student VALUES(1,NULL,NULL)
INSERT INTO student VALUES(2,NULL,NULL)

INSERT INTO student VALUES(4,'zs',NULL)*/

/*10.创建表student，字段sno，sname，sage，ssex
要求sno字段
为主键，sname的值不能为空，sage的值不能重复，ssex
必须为'f','m'
DROP TABLE student

CREATE TABLE student(
    sno INT PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    sage INT UNIQUE,
    ssex enum('f','m')
)

CREATE TABLE student(
    sno INT PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    sage INT UNIQUE,
    ssex VARCHAR(20),
    check(ssex in('f','m'))
)

INSERT INTO student VALUES(2,'ls',12,'x')*/

/*11.为sage字段添加检查约束，要求年龄在18到30之间

CREATE TABLE student(
    sno INT PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    sage INT,
    check(sage BETWEEN 18 AND 30)
)

INSERT INTO student VALUES(2,'zs',10)

select * from student*/

/*12.创建表t，tno主键，tnum依赖于tno
CREATE TABLE t(
    tno INT PRIMARY KEY,
    tnum INT,
    FOREIGN key (tnum) REFERENCES t(tno)
) 

INSERT INTO t VALUES (1,NULL)
INSERT INTO t VALUES (2,NULL)
INSERT INTO t VALUES (3,1)
INSERT INTO t VALUES (4,7)

CREATE TABLE t1(
    tid INT PRIMARY KEY
)

CREATE TABLE t2(
    tid INT,
    FOREIGN KEY (tid) REFERENCES t1(tid)
)*/

/*t1 -- 1,2,3*/
/*t2 -- 1*/
/*删除t1中的1
delete from t1 where tid=1*/

/*13.创建表student，字段sno，sname，sage，ssex
要求sno字段为主键，要求主键自增
CREATE TABLE student(
    sno INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20),
    sage INT,
    ssex enum('f','m')
)

INSERT INTO student VALUES(1,'zs',23,'f')

delete from student where sno=7
INSERT INTO student VALUES(null,'zs',23,'f')*/

select last_insert_id();