devbox
AllinToyou
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Mysql——SQL语言入门_创建数据库表student

作者：AllinToyou | 2024-05-01 02:50:31

赞

踩

创建数据库表student

1.创建数据库表

【1】创建数据库表t_student

（1）创建数据库：

（2）新建查询

（3）创建数据库表

##单行注释
/*
多行注释
多行注释
*/

/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别、年龄、入学日期、班级、email等信息

*/

##创建数据库表：
CREATE TABLE t_student(
  sno int(6),-- 6显示长度
	sname VARCHAR(10),
	sex char(1),
	age int(3),
	enterdate date,
	classname VARCHAR(10),
	email VARCHAR(15)
);

-- 查看表的结构：展示表的字段的详细信息
desc t_student;


-- 查看表中数据：
SELECT * FROM t_student;


-- 查看建表语句：
SHOW CREATE TABLE t_student;

1.1数据库表列类型

2.对数据的增删改查操作

2.1查询、添加数据

-- 查看表中记录：
SELECT * FROM t_student;


-- 在t_student数据库表中插入数据：
INSERT INTO t_student VALUES(1,'zhangsan','男',18,'2022.5.8','软件1班','123@163.com');
INSERT INTO t_student VALUES(100100100,'zhangsan','男',18,'2022.5.8','软件1班','123@163.com');
INSERT INTO t_student VALUES(100100100,"zhangsan","男",18,"2022.5.8","软件2班","123@163.com");
INSERT INTO t_student VALUES(7,"zhangsan","男",18,NOW(),"软件2班","123@163.com");
-- 如果不是全字段插入数据的话，需要加入字段的名字。
INSERT INTO t_student (sno,sname,sex)VALUES(7,"zhangsan","男");

插入数据的注意事项：

2.2修改、删除数据

-- 查看表中记录：
SELECT * FROM t_student;


-- 在t_student数据库表中插入数据：
INSERT INTO t_student VALUES(1,'zhangsan','男',18,'2022.5.8','软件1班','123@163.com');
INSERT INTO t_student VALUES(100100100,'zhangsan','男',18,'2022.5.8','软件1班','123@163.com');
INSERT INTO t_student VALUES(100100100,"zhangsan","男",18,"2022.5.8","软件2班","123@163.com");
INSERT INTO t_student VALUES(7,"zhangsan","男",18,NOW(),"软件2班","123@163.com");
-- 如果不是全字段插入数据的话，需要加入字段的名字。
INSERT INTO t_student (sno,sname,sex)VALUES(7,"zhangsan","男");


-- 修改表中的数据
UPDATE t_student SET sex = '女';
UPDATE t_student SET sex = '男' WHERE sno = 7;
UPDATE t_student SET classname = 'java01' WHERE sno = 100100100;
UPDATE t_student SET classname = 'JAVA01' WHERE sno = 1;
UPDATE t_student SET age = 29 WHERE classname = 'java01';


-- 删除表中的数据
DELETE FROM t_student WHERE sno = 1;

2.3修改、删除数据库表

-- 查看数据
SELECT * FROM t_student;

-- 修改表的结构
-- 增加一列
ALTER TABLE t_student ADD score DOUBLE(5,2);-- 5是总位数  2是小数位数
UPDATE t_student SET score = 90.589 WHERE sno = 7;

-- 增加一列 放在最前面
ALTER TABLE t_student ADD score DOUBLE(5,2) FIRST;-- 5是总位数  2是小数位数

-- 增加一列 放在sex列的后面
ALTER TABLE t_student ADD score DOUBLE(5,2) AFTER sex;-- 5是总位数  2是小数位数

ALTER TABLE t_student DROP score;-- 删除一列

-- 修改一列
ALTER TABLE t_student MODIFY score FLOAT(4,1);-- modify修改的是列的类型的定义，但是不会改变列的名字
ALTER TABLE t_student CHANGE score score1 DOUBLE(3,1);-- change 修改的是列名和列的类型定义

-- 删除表
DROP TABLE t_student;

3.表的完整性约束

3.1非外键约束

/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，emai1等信息
约束:
建立一张用来存储学生信息的表字段包含学号、姓名、性别，年龄、入学日期、班级，emai1等信息
【1】学号是主键  不能为空 + 唯一 ，主键的作用:可以通过主键查到唯一的一条记录
【2】如果主键是整数类型，那么需要自增
【3】姓名不能为空
【4】Email唯一
【5】性别默认值是男
【6】性别只能是男女
【7】年龄只能在18-50之间

*/

CREATE TABLE t_student(
  sno int(6) PRIMARY key auto_increment,
	sname VARCHAR(10) NOT NULL,
	sex char(1) DEFAULT'男' CHECK(sex='男'||sex = '女'),
	age int(3) CHECK(age>=18&&age<=40),
	enterdate date,
	classname VARCHAR(10),
	email VARCHAR(15) UNIQUE
	
);

【1】约束从作用上可以分为两类：

（1）表级约束：可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用'，'分隔；必须指出要约束列的名称。

（2）列级约束：包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名

-- 删除表
DROP TABLE t_student;



CREATE TABLE t_student(
  sno int(6) auto_increment,  -- auto_increment 只能是列级约束
	sname VARCHAR(10) not null, -- not null 只能是列级约束
	sex char(1) DEFAULT '男' ,-- DEFAULT 只能是列级约束
	age int(3),
	enterdate date,
	classname VARCHAR(10),
	email VARCHAR(15),
	CONSTRAINT pk_stu PRIMARY KEY(sno), -- pk_stu 主键约束的名字
	CONSTRAINT ck_stu_sex CHECK(sex='男'||sex='女'),
	CONSTRAINT ck_stu_age CHECK(age>=20&&age<=40),
	CONSTRAINT uq_stu_email UNIQUE(email)
);

-- 添加数据

【2】在创建表以后添加约束（注意，要想一起添加 要在每个句子后面添加分号）

查看表的结构的语句： desc t_student;

3.2外键约束

主表（父表）：班级表 - 班级编号 - 主键

从表（子表）：学生表 - 编辑编号 - 外键

【2】sql演示：

-- 先创建父表：班级表：
CREATE TABLE t_class(
		cno int(4) PRIMARY KEY auto_increment,
		cname VARCHAR(10) not null,
		croom CHAR(8)
);

-- 添加班级数据
INSERT into t_class value(null,'java001','803教室')
INSERT into t_class value(null,'java002','416教室')
INSERT into t_class value(null,'大数据001','103教室')

-- 可以一次性添加多条记录：
INSERT into t_class value(null,'java001','803教室'),(null,'java002','416教室'),(null,'大数据001','103教室');

-- 查询一下班级表
select * FROM t_class;

-- 创建子表
DROP TABLE t_student;
CREATE TABLE t_student(
  sno int(6) PRIMARY key auto_increment,
	sname VARCHAR(10) NOT NULL,
	classname VARCHAR(10),
	email VARCHAR(15) UNIQUE,
	classno VARCHAR(10) -- 取值参考t_class表中的cno字段，不要求字段名字完全一致，但是长度定义 尽量保证相同。
);

INSERT into t_student (sno,sname,classname)values (null,'张三',1),(null,'李四',1),(null,'王五',3);

-- 查看学生表
SELECT * FROM t_student;


-- 出现问题：
-- 1.我想添加一个学生对应的班级编码为4
INSERT into t_student (sno,sname,classname)values (null,'丽丽',4);
-- 2.想删除班级2
DELETE FROM t_class where cno= 2;

-- 出现问题的原因：因为你的外键约束，没用语法添加进去，现在只是在逻辑上认为班级编号是外键，没有在语法上定义

-- 解决办法：添加外键约束
-- 注意：外键约束只有表级约束，没有列级约束
CREATE TABLE t_student(
  sno int(6) PRIMARY key auto_increment,
	sname VARCHAR(10) NOT NULL,
	classname int(4), -- 取值参考t_class表中的cno字段，不要求字段名字完全一致，但是长度定义 尽量保证相同。
	email VARCHAR(15) UNIQUE,
	classno VARCHAR(10), 
	CONSTRAINT fk_stu_classno FOREIGN KEY (classname) REFERENCES t_class(cno)
);


CREATE TABLE t_student(
  sno int(6) PRIMARY key auto_increment,
	sname VARCHAR(10) NOT NULL,
	classname VARCHAR(10),
	email VARCHAR(15) UNIQUE,
	classno VARCHAR(10),-- 取值参考t_class表中的cno字段，不要求字段名字完全一致，但是长度定义 尽量保证相同。
);
-- 在创建表以后添加外键约束：
ALTER TABLE t_student add CONSTRAINT fk_stu_classno FOREIGN KEY (classname) REFERENCES t_class(cno);

【3】外键策略

-- 先创建父表：班级表：
CREATE TABLE t_class(
		cno int(4) PRIMARY KEY auto_increment,
		cname VARCHAR(10) not null,
		croom CHAR(8)
);

-- 添加班级数据
INSERT into t_class value(null,'java001','803教室'),(null,'java002','416教室'),(null,'大数据001','103教室');

-- 创建子表
CREATE TABLE t_student(
  sno int(6) PRIMARY key auto_increment,
	sname VARCHAR(10) NOT NULL,
	classname int(4), -- 取值参考t_class表中的cno字段，不要求字段名字完全一致，但是长度定义 尽量保证相同。
	CONSTRAINT fk_stu_classno FOREIGN KEY (classname) REFERENCES t_class(cno)
);

INSERT into t_student (sno,sname,classname)values (null,'张三',1),(null,'李四',1),(null,'王五',3);

-- 查看学生表
SELECT * FROM t_student;
-- 查询一下班级表
select * FROM t_class;

-- 删除班级1：如果直接删除的话肯定不行因为有外键约束：
-- 为了解决这个问题加入外键策略：
-- 策略1： no action 不允许操作
-- 通过操作sql来完成： 先把班级2的学生对应的班级 改为null
UPDATE t_student SET CLASSNAME = NULL WHERE CLASSNAME = 1;
DELETE FROM t_class where cno = 1;


-- 策略2：cascade 级联操作：操作主表的时候影响从表的外键信息：
-- 先删除之前的外键约束：
alter TABLE t_student DROP FOREIGN KEY fk_stu_classno;
-- 重新添加外键
alter TABLE t_student add CONSTRAINT fk_stu_classno FOREIGN KEY (classname) REFERENCES t_class(cno) on UPDATE CASCADE ON DELETE CASCADE;


-- 策略3：set null 置空操作：
-- 先删除之前的外键约束：
alter TABLE t_student DROP FOREIGN KEY fk_stu_classno;
-- 重新添加外键
alter TABLE t_student add CONSTRAINT fk_stu_classno FOREIGN KEY (classname) REFERENCES t_class(cno) on UPDATE set NULL ON DELETE set NULL;

-- 注意：
-- 1.策略2 级联操作 和策略3 的删除操作可以混着使用：
alter TABLE t_student add CONSTRAINT fk_stu_classno FOREIGN KEY (classname) REFERENCES t_class(cno) on UPDATE CASCADE ON DELETE set NULL;

-- 2.应用场合：
-- （1）朋友圈删除，点赞和评论都删除 级联操作
-- （2）解散班级，对应的就是置空操作

3.3对DDL和DML的补充

【1】sql展示：

-- 创建表
CREATE TABLE t_student(
  sno int(6) PRIMARY key auto_increment,
	sname VARCHAR(10) NOT NULL,
	sex char(1) DEFAULT'男' CHECK(sex='男'||sex = '女'),
	age int(3) CHECK(age>=18&&age<=40),
	enterdate date,
	classname VARCHAR(10),
	email VARCHAR(15) UNIQUE
);

-- 添加数据
INSERT INTO t_student VALUES (null,'王','男',18,'2020.1.2','java01班','123@789');
INSERT INTO t_student VALUES (null,'雪','男',19,'2020.1.2','java01班','12@789');
INSERT INTO t_student VALUES (null,'健','男',20,'2020.1.2','java01班','1@789');

-- 查看学生表
SELECT * FROM t_student;

-- 添加一个表：快速添加：结构和数据跟t_student都是一致的
CREATE TABLE t_student2
AS
SELECT * FROM t_student;

-- 查看学生表2
SELECT * FROM t_student2;

-- 快速添加：结构跟student一致，数据没有
CREATE TABLE t_student3
AS
SELECT * FROM t_student WHERE 1 = 2;

-- 查看学生表3
SELECT * FROM t_student3;

-- 快速添加：只要部分列，部分数据：
CREATE TABLE t_student4
AS
SELECT sno,sname,age FROM t_student WHERE sno =2;

-- 查看学生表4
SELECT * FROM t_student4;.

-- 删除数据操作：只是清空数据
DELETE FROM t_student;
TRUNCATE TABLE t_student;

【2】delete和truncate的区别：

4.DQL-查询操作

4.1表的准备

准备四张表：dept（部门表），emp（员工表），salgrade（薪资等级表），bonus（奖金表）

-- 创建部门表：属性有部门号，部门名字，LOC 
CREATE table DEPT(
		deptno int(2) not null,
		deptname VARCHAR(14),
		loc VARCHAR(13)
)

-- 给部门表加了一个主键
ALTER TABLE dept 
add CONSTRAINT PK_DEPT PRIMARY KEY (deptno);

-- 创建一个EMP员工表
CREATE table EMP(
		empno int(4) PRIMARY KEY,
		empname VARCHAR(10),
		job VARCHAR(9),
		mgr  int(4),
		hiredate DATE,
		sal DOUBLE(7,2),
		comm DOUBLE(7,2),
		deptno int(2)
)

-- 给员工表加一个外键，关联的是DEPT表中的deptno
ALTER TABLE emp 
add CONSTRAINT FK_deptno 
FOREIGN KEY (deptno)
REFERENCES DEPT (deptno)

-- 创建薪资等级表SALGRADE
CREATE TABLE SALGRADE(
	grade int PRIMARY KEY,
	losal DOUBLE(7,2),
	hisal DOUBLE(7,2)
)

-- 创建奖金表
CREATE TABLE BONUS(
	ename VARCHAR(10),
	job VARCHAR(9),
	sal DOUBLE(7,2),
	comm DOUBLE(7,2)
)

INSERT INTO DEPT(deptno,deptname,loc)
VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT(deptno,deptname,loc)
VALUES(20,'RESEARCH','DALLAS');
INSERT INTO DEPT(deptno,deptname,loc)
VALUES(30,'SALES','CHIGAGO');
INSERT INTO DEPT(deptno,deptname,loc)
VALUES(40,'OPERATIONS','BOSTON');

INSERT INTO EMP(empno,empname,job,mgr,hiredate,sal,comm,deptno)
VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
INSERT INTO EMP(empno,empname,job,mgr,hiredate,sal,comm,deptno)
VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP(empno,empname,job,mgr,hiredate,sal,comm,deptno)
VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP(empno,empname,job,mgr,hiredate,sal,comm,deptno)
VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
INSERT INTO EMP(empno,empname,job,mgr,hiredate,sal,comm,deptno)
VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP(empno,empname,job,mgr,hiredate,sal,comm,deptno)
VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
INSERT INTO EMP(empno,empname,job,mgr,hiredate,sal,comm,deptno)
VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);

INSERT INTO SALGRADE(grade,losal,hisal)
VALUES(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

-- 查看表
-- 部门表：dept：department 部门，loc - location 位置
SELECT * FROM DEPT;

-- 员工表：emp：employee 员工 ，mgr：manager 上级领导编号，hiredate：入职日期， common：补助，
-- deptno 外键 参考的是 dept里面的deptno字段
-- mgr 外键 参考的是 自身表emp里面的empno产生了自关联的效果
SELECT * FROM emp;
SELECT * FROM salgrade;
SELECT * FROM bonus;

4.2单表查询

4.2.1最简单的SQL查询

-- 对emp表查询：
SELECT * FROM emp; -- *代表所有数据
-- 显示部分列：
SELECT empno,empname,sal FROM emp;
-- 显示部分行：
SELECT * FROM emp where sal>2000;
-- 显示部分列，部分行：
SELECT empno,empname,sal FROM emp where sal>2000;

-- 起别名：
SELECT empno 员工编号,empname 姓名,sal 工资 FROM emp;
SELECT empno as 员工编号,empname as 姓名,sal as 工资 FROM emp;
-- 在别名中有特殊符号的时候，''或者""不可以省略不写
SELECT empno as '员工编号',empname as "姓名",sal as 工资 FROM emp;

-- 算数运算符：
SELECT empno,empname,sal,sal+1000 涨薪后,deptno FROM emp where sal>2000;
SELECT * FROM emp;

-- 去重操作：
SELECT job FROM emp;
SELECT DISTINCT job FROM emp;
SELECT DISTINCT job,deptno FROM emp;-- 对后面的所有列组合去重，而不是单独的某一列去重

-- 排序：
SELECT * FROM emp order by sal; -- 默认情况下按照升序排列
SELECT * FROM emp order by sal asc;-- 升序
SELECT * FROM emp order by sal desc;-- 降序
SELECT * FROM emp order by sal asc,deptno desc;-- 在工资升序的情况下，deptno按照降序排列

4.2.2where子句

-- 查看emp表
SELECT * FROM emp;

-- where子句：将过滤条件放在where子句的后面，可以筛选/过滤出我们想要的复合条件的数据
-- where子句 + 关系运算符
SELECT * FROM emp WHERE deptno = 10;
SELECT * FROM emp WHERE deptno >= 10;
SELECT * FROM emp WHERE deptno <= 10;
SELECT * FROM emp WHERE deptno <> 10;-- 不等于
SELECT * FROM emp WHERE deptno != 10;-- 不等于
SELECT * FROM emp WHERE job ='clerk';-- 默认情况下不区分大小写
SELECT * FROM emp WHERE BINARY job ='clerk';-- binary区分大小写
SELECT * FROM emp WHERE BINARY hiredate ='1981-12-25';


-- where子句 +逻辑运算符 and
SELECT * FROM emp where sal>1500 and sal< 2500;-- (1500,3000)
SELECT * FROM emp where sal>1500 && sal< 2500;-- (1500,3000)
SELECT * FROM emp where sal>1500 and sal< 2500 ORDER BY sal;
SELECT * FROM emp where BETWEEN 1500 and 3000  ORDER BY sal;-- [1500,3000]

-- where子句 +逻辑运算符 or
SELECT * FROM emp where deptno = 10 or deptno = 20;
SELECT * FROM emp where deptno = 10 || deptno = 20;
SELECT * FROM emp where deptno in (10,20) ;

-- where子句 + 模糊查询
-- 查询名字中带A的员工   -- %代表任意多个字符 0，1，2，......
SELECT * FROM emp WHERE empname LIKE '%A%';
-- 这个_代表任意一个字符
SELECT * FROM emp WHERE empname LIKE '_A%';
SELECT * FROM emp WHERE empname LIKE '__A%';

-- 关于null的判断：
SELECT * FROM emp WHERE comm is null;
SELECT * FROM emp WHERE comm is not null;

-- 关于小括号的使用：
SELECT * FROM emp WHERE (deptno = 10 or deptno = 20) and sal >2000;
SELECT * FROM emp WHERE deptno = 10 or deptno = 20 and sal >2000;-- 先and 再 or and 的优先级比or要高

4.2.3函数

PS：除了多行函数（max,min,count,sum,avg）除了这五个以外，都是单行函数

4.2.3.1单行函数

-- 单行函数包含：
-- 1.字符串函数:substring字符串截取，2：从字符下标2开始，3：截取长度3   （下标从1开始）
SELECT empname,length(ename),substring(empname,2,3) FROM emp;
-- 2.数值函数:abs取绝对值 ceil向上取整 floor向下取整 round四舍五入
SELECT abs(-5)，ceil(5.3),FLOOR(5.9),ROUND(3.14) FROM DUAL; -- dual实际上是一个伪表
SELECT abs(-5) 绝对值,ceil(5.3)向上取整,FLOOR(5.9)向下取整,ROUND(3.14)四舍五入; -- 如果没有where条件的话，from dual可以省略不写
SELECT ceil(sal) FROM emp;
SELECT 10/3,10%3,MOD(10,3);
-- 3.日期和时间函数
SELECT * FROM emp;
SELECT CURDATE(),CURTIME();-- CURDATE()年月日  CURTIME()时分秒
SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE();-- NOW()返回当前日期和时间  SYSDATE返回该函数执行时的日期和时间
-- NOW()可以表示年月日时分秒，但是插入数据的时候还是要参照表的结构
-- 4.流程函数
-- if相关
SELECT empno,empname,sal,IF(sal>=2500,'高薪','低薪')薪资等级 FROM emp;-- if ELSE 双分支结构
SELECT empno,empname,sal,comm,sal+IFNULL(comm,0) FROM emp;-- 如果comm是null，那么取值为0  -- 单分支
SELECT NULLIF(1,1),NULLIF(1,2); -- 如果value1等于value2，则返回null，否则返回value1
-- case相关:case等值判断
SELECT empno,empname,job,
CASE job
when 'CLERK' then '店员'
else '其他'
end 岗位,
sal FROM emp;
-- case区间判断
SELECT empno,empname,job,
CASE job
when sal<=1000 then 'A'
when sal<=2000 then 'B'
when sal<=3000 then 'C'
else 'D'
end 工资等级,
deptno FROM emp;

-- 5.JSON函数  
-- 6.其他函数 -- DATABASE()当前数据库是啥  USER()使用者是谁  VERSION()对应的数据库的版本
SELECT DATABASE(),USER(),VERSION() ;

4.2.3.2多行函数

-- 单行函数包含：
-- 1.字符串函数:substring字符串截取，2：从字符下标2开始，3：截取长度3   （下标从1开始）
SELECT empname,length(ename),substring(empname,2,3) FROM emp;
-- 2.数值函数:abs取绝对值 ceil向上取整 floor向下取整 round四舍五入
SELECT abs(-5)，ceil(5.3),FLOOR(5.9),ROUND(3.14) FROM DUAL; -- dual实际上是一个伪表
SELECT abs(-5) 绝对值,ceil(5.3)向上取整,FLOOR(5.9)向下取整,ROUND(3.14)四舍五入; -- 如果没有where条件的话，from dual可以省略不写
SELECT ceil(sal) FROM emp;
SELECT 10/3,10%3,MOD(10,3);
-- 3.日期和时间函数
SELECT * FROM emp;
SELECT CURDATE(),CURTIME();-- CURDATE()年月日  CURTIME()时分秒
SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE();-- NOW()返回当前日期和时间  SYSDATE返回该函数执行时的日期和时间
-- NOW()可以表示年月日时分秒，但是插入数据的时候还是要参照表的结构
-- 4.流程函数
-- if相关
SELECT empno,empname,sal,IF(sal>=2500,'高薪','低薪')薪资等级 FROM emp;-- if ELSE 双分支结构
SELECT empno,empname,sal,comm,sal+IFNULL(comm,0) FROM emp;-- 如果comm是null，那么取值为0  -- 单分支
SELECT NULLIF(1,1),NULLIF(1,2); -- 如果value1等于value2，则返回null，否则返回value1
-- case相关:case等值判断
SELECT empno,empname,job,
CASE job
when 'CLERK' then '店员'
else '其他'
end 岗位,
sal FROM emp;
-- case区间判断
SELECT empno,empname,job,
CASE job
when sal<=1000 then 'A'
when sal<=2000 then 'B'
when sal<=3000 then 'C'
else 'D'
end 工资等级,
deptno FROM emp;

-- 5.JSON函数  
-- 6.其他函数 -- DATABASE()当前数据库是啥  USER()使用者是谁  VERSION()对应的数据库的版本
SELECT DATABASE(),USER(),VERSION() ;

-- 多行函数
SELECT max(sal),min(sal),count(sal),sum(sal),avg(sal) FROM emp;
-- 多行函数会自动忽略null值
SELECT max(comm),min(comm),count(comm),sum(comm),avg(comm) FROM emp;
-- max(), min(), count() 针对所有类型  sum(),avg() 只针对数值类型有效


-- count -- 计数
-- 统计表的记录数：方式1
SELECT * FROM emp;
SELECT count(*) FROM emp;

-- 方式2
SELECT 1 FROM dual;
SELECT 1 FROM emp;
SELECT count(1) FROM emp;

4.2.4 group by分组和having筛选

【1】group by：用来进行分组

【2】sql展示：

-- 统计各个部门的平均工资
SELECT deptno,avg(sal) FROM emp; -- 字段和多行函数不能同时使用
SELECT deptno,avg(sal) FROM emp GROUP BY deptno ORDER BY deptno desc; -- 字段和多行函数不能同时使用，除非这个字段属于分组


-- 统计各个部门的平均工资
SELECT job,avg(sal) FROM emp GROUP BY job;

【3】having：进行分组后的筛选

【4】sql展示：

-- 统计各个部门的平均工资,只显示平均工资2000以上的  分组以后进行二次筛选
SELECT deptno,avg(sal) FROM emp GROUP BY deptno HAVING avg(sal)>2000;
SELECT deptno,avg(sal) 平均工资 FROM emp GROUP BY deptno HAVING 平均工资>2000;
SELECT deptno,avg(sal) 平均工资 FROM emp GROUP BY deptno HAVING 平均工资>2000 ORDER BY deptno desc;

-- 统计各个岗位的平均工资,除了MANAGER
-- 方法1
SELECT job,AVG(sal) FROM emp where job !='MANAGER' GROUP BY job;
-- 方法2
SELECT job,AVG(sal) FROM emp GROUP BY job HAVING job!='MANAGER';
-- where 在分组前进行过滤的，having是在分组后进行过滤的

4.2.5 单表查询总结

【1】select语句总结

【2】select语句的执行顺序

【3】单表查询练习：

-- 单表查询练习：
-- 列出工资最小值小于2000的职位
SELECT job,MIN(sal) FROM emp GROUP BY job HAVING MIN(sal)<2000;
SELECT * FROM emp;
SELECT * FROM dept;


-- 列出平均工资大于1200元的部门和工作搭配组合
SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno,job HAVING AVG(sal)>1200 ORDER BY deptno;

-- 统计[人数小于4的]部门的平均工资。
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING COUNT(deptno)<4;

-- 统计各部门的最高工资，排除最高工资小于3000的部门。
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)>=3000;

4.3多表查询

4.3.1交叉连接，自然连接，内连接

-- 查询员工得编号，姓名，部门编号：
SELECT * FROM emp;
SELECT empno,empname,deptno FROM emp;

-- 查询员工得编号，姓名，部门编号，部门名称：
SELECT * FROM emp; -- 14条记录
SELECT * FROM dept; -- 4条记录

-- 多表查询：
-- 交叉链接：cross join
SELECT * 
FROM emp
cross join dept; -- 14*4 = 56条  笛卡尔乘积： 没有实际意义，有理论意义
  
-- 自然链接：natural join 
-- 优点：自动匹配所有的同名列， 同名列只展示一次，简单
SELECT * 
FROM emp
NATURAL join dept;

-- 缺点：查询字段的时候，没有指定字段所属的数据库表，效率低
-- 解决：指定表名
SELECT emp.empno,emp.empname,dept.deptno 
FROM emp
NATURAL JOIN dept;

-- 缺点：表名太长咋办啊
-- 解决：表可以起别名
SELECT e.empno,e.empname,d.deptno 
FROM emp e
NATURAL JOIN dept d;

-- 自然链接natural join 的缺点：自动表中的匹配所有的同名列。但是有时候我们只希望匹配部分同名列
-- 解决：内连接 - using子句
SELECT * 
FROM emp
inner join dept -- inner 内连接
using(deptno);  -- 这里不能写natural join了，这里是内连接

-- using缺点：关联的字段，必须是同名的
-- 解决：内连接的里面的on子句
SELECT * 
FROM emp e
inner join dept d
on (e.aaa = d.bbb);

-- 多表链接查询的类型：
/*
1.交叉链接 cross join 
2.自然链接 natural join 
3.内连接 - using子句
4.内连接 - on子句 
综合看 内连接 - on子句用的最多
*/

-- 条件：
/*
1.筛选条件  where  having 
2.连接条件  on,using,natural,cross
SQL99语法： 筛选条件和连接条件是分开的
*/

4.3.2外连接

-- inner join - on 子句： 显示的是所有匹配的信息

-- 外连接：除了显示匹配的数据之外，还可以显示部门或者全部不匹配的数据
-- 左外连接： left outer join  --左面哪个表的信息，即使不匹配也可以查看出效果
SELECT * 
FROM emp e
left join dept d
on e.deptno = d.deptno;

-- 右外连接： right outer join  --右面哪个表的信息，即使不匹配也可以查看出效果
SELECT * 
FROM emp e
right outer join dept d
on e.deptno = d.deptno ;

-- 全外连接 
SELECT * 
FROM emp e
left join dept d
on e.deptno = d.deptno
union -- 并集 去重 效率低
SELECT * 
FROM emp e
right outer join dept d
on e.deptno = d.deptno;


SELECT * 
FROM emp e
left join dept d
on e.deptno = d.deptno
union all -- 并集 不去重 效率高
SELECT * 
FROM emp e
right outer join dept d
on e.deptno = d.deptno ;

4.3.3三表连接查询

4.3.4自连接

想象成两张表。

4.4子查询

【1】什么是子查询？

一条SQL语句含有多个select

【2】执行顺序：

先执行子查询，再执行外查询

【3】不相关子查询：

子查询可以独立运行，称为不相关子查询。

【4】不相关子查询分类：

根据子查询的结果行数，可以分为单行子查询和多行子查询。

4.4.1不相关子查询

4.4.1.1单行子查询

4.4.1.2多行子查询

4.4.2相关子查询

【1】相关子查询引入

【2】相关子查询的优缺点

5.数据库对象

5.1事务及其特征

事务是用来维护数据库完整性的，它能够保证一系列的MySQL操作要么全部执行，要么全不执行

【1】事务的概念

【2】事务的特性

但是不是所有的操作序列都可以成为事务，要成为事务要满足四个特性，ACID特性

【3】sql展示：

5.2事务并发问题

【1】脏读

【2】不可重复读

【3】幻读

【4】不可重复读和幻读的区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

解决不可重复读的问题只需要锁住满足条件的行，解决幻读需要锁住表。

5.2事务隔离级别

为了解决事务的并发问题，引入了事务的并发级别。

5.3视图

【3】sql展示：

5.3存储过程

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/AllinToyou/article/detail/516593
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号