数据库的学习

尚硅谷数据库视频链接

资料文件
密码：qwh5
如果资料缺失 直接去尚硅谷公众号 自己去根据提示去找
还是需要多敲 我二倍速度看完整个视频 敲了课件里面的数据库语句 感觉sql能力提升不少
一定要多练习，加油

USE myemployees;
SHOW TABLES
SELECT *FROM employees
##定义别名
SELECT last_name AS NAME,commission_pct comm FROM employees;

##定义别名2
SELECT last_name "Name",salary*12 "nianxin" FROM employees

##显示表的结构
DESCRIBE employees

##过滤和排序数据
SELECT * FROM employees

##挑选出工资超过10000的记录
SELECT * FROM employees WHERE salary >10000

##返回部门号为90的所有的员工相关信息
SELECT employee_id,last_name,job_id,department_id FROM employees WHERE department_id =90

##使用between关键字进行查询,查询部门号在30和90之间的员工相关信息
SELECT employee_id,last_name,job_id,department_id FROM employees WHERE department_id BETWEEN 60 AND 90

##使用IN运算 显示列表的值 查询部门号在30,60,90中的员工相关信息
SELECT employee_id,last_name,job_id,department_id FROM employees WHERE department_id IN (30,60,90)

##使用LIke 关键字进行模糊查询 查询first_name 开头是以s开头的first_name
SELECT first_name FROM employees WHERE first_name LIKE 'S%'

##使用LIke 关键字进行模糊查询 查询first_name 开头是以s结束的first_name
##而且前面有个字符 %代表了多个字符 _表示一个占位  只是一个字符
SELECT first_name FROM employees WHERE first_name LIKE '%_S'

##使用is null 判断某个字段为空的记录
SELECT * FROM employees WHERE manager_id IS NULL

##下面使用的逻辑运算 and 表示要求并的关系为真 全真为真
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary>=10000 AND job_id = '%MAN%'

##下面使用的逻辑运算 and 表示要求或的关系为真  有一真为真
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary>=10000 OR job_id = '%MAN%'

##下面使用的逻辑运算 NOT 表示非的关系 原命题是真 结果为假 原命题是假 结果是真 
SELECT employee_id, last_name, job_id, salary FROM employees WHERE job_id  NOT IN('IT_PROG','ST_CLERK','SA_REP')

##使用order by 语句进行排序 其中 asc 代表的是升序，默认就是升序。desc 是代表的是降序 你要记住，order by 语句需要在select语句的结尾
##根据聘用时间进行排序
SELECT * FROM employees ORDER BY hiredate;

##根据聘用时间进行降序排序
SELECT * FROM employees ORDER BY hiredate DESC;

##案按别名进行降序排序
SELECT  employee_id, salary* 12 AS annual FROM employees ORDER BY annual DESC

##多个列进行排序
SELECT last_name, department_id,salary  FROM employees ORDER BY department_id DESC, salary ASC;

##下面介绍的是分组函数
##分组函数有 AVG() COUNT() MAX() MIN() SUM()
##先查询所有数据
SELECT * FROM employees
##查出salary最大值，最小值
SELECT MAX(salary),MIN(salary) FROM employees
##表示有多少条记录  返回不为空的记录总数
SELECT COUNT(salary) FROM employees
#表示求和
SELECT SUM(salary) FROM employees

##使用group by 进行分组 其中需要注意的是where是一定放在from 的后面
##select 列表中所有未包含在组函数中的列都应该包含在group by 语句
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id

##包含在group by 子句中的列不必包含在select 列表中
SELECT AVG(salary) FROM employees GROUP BY department_id

##使用多个列进行分组
SELECT department_id,job_id,SUM(salary) FROM employees GROUP BY department_id,job_id

##其中需要注意的是不能非法使用组函数,但是我们可以在having 关键字后面使用组函数
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary)>1000


##下面介绍多表查询
SHOW TABLES
USE girls
##这边需要说一下  笛卡尔积的 如果一个表有12条记录 另外一个表有4条记录 但是笛卡尔积操作之后就会有48条记录
##笛卡尔积在下面条件产生:1.省略连接条件2.连接条件无效3.所有表中的所有行互相连接
##为了避免笛卡尔积的结果出现 我们需要在where后面加入有效地连接条件
 
##先介绍一下等值连接
#我们在前面加上表名来区分相同列名称比如boys表中有字段是id 以及beauty表中也是有字段id 的
SELECT beauty.`id`,NAME,boyname FROM beauty,boys WHERE beauty.`boyfriend_id` = boys.`id`

##其中需要注意的是  连接n个表需要n-1个连接条件 例如：连接三个表，需要两个连接条件
USE myemployees
SHOW TABLES
DESCRIBE employees
SELECT last_name, department_name, city FROM employees, departments,locations WHERE employees.`department_id` = departments.`department_id` AND departments.`location_id` = locations.`location_id`

##自然连接中以相同名字的列为连接条件，可以使用ON子句指定额外地连接条件 
##On子句具有更高的易读性
#下面介绍下join连接
#分类：内连接 和外连接(左外连接，右外连接)
SELECT employee_id,city,department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id =l.location_id

##下面学习一些函数
##round()表示四舍五入 truncate() 表示截断 MOD表示求余
##后面的2表示保留2位小数 45.93
SELECT ROUND(45.926,2)
##表示截断  保留两位有效小数  45.92
SELECT TRUNCATE(45.926,2)
##表示求余  100
SELECT MOD(1600,300)
##显示当前时间
SELECT NOW()


##z这边向大家展示创建和管理表
##先创建数据库  设置主键代表着
CREATE DATABASE huangbf
USE huangbf
SHOW TABLES
CREATE TABLE dept(deptno INT(2) AUTO_INCREMENT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(100));
SHOW TABLES
DESCRIBE dept
##删除整个表
DROP TABLE dept

##使用子查询创建表
CREATE TABLE emp1 AS SELECT * FROM dept
CREATE TABLE emp2 AS SELECT * FROM dept
SHOW TABLES

CREATE TABLE emp( emp_id INT AUTO_INCREMENT,
emp_name VARCHAR(20),
salary DOUBLE,
birthday DATE,
PRIMARY KEY(emp_id))

SHOW TABLES

##下面给大家展示的是使用alter 语句进行修改数据表
##alter可以实现向已有的表中添加列、修改现有表中的列、删除现有表中的列、重命名现有表中的列

##向一个表中添加一个列
ALTER TABLE dept ADD job_id VARCHAR(15)
DESCRIBE dept

##修改一个数据表中的数据类型 尺寸和默认值
ALTER TABLE dept MODIFY (loc VARCHAR(30));

##删除数据表中某一个列
ALTER TABLE dept DROP COLUMN job_id;
##重命名一个列
ALTER TABLE dept CHANGE dapartment_name department_name VARCHAR(15);
##删除一个表
DROP TABLE dept

SHOW TABLES
 ##现在和大家展示的数据增删改查 也就是DML(数据操纵语言)
 ##其中包括:向表中插入数据 修改现有的数据 删除现有的数据
 ##其中需要注意的是 事务就是完成若干项工作的DML语句完成
##插入一条数据
INSERT INTO dept(deptno,dname,loc)VALUES(1,'anhui university','anhui hefei')
INSERT INTO dept(dname,loc)VALUES('nanjing university','jiangsu nanjing')
##查询数据
SELECT * FROM dept
 
 
##删除deptno=1 和deptno=2 的记录
DELETE FROM dept WHERE deptno =1 
 
#更新数据  将编号改成一
UPDATE dept SET deptno =1 WHERE deptno =2


##下面介绍建表的时候约束，以及分页的具体操作
##NOT NULL 非空
##UNIQUE  唯一约束
##PRIMARY  KEY 主键 非空且唯一
##FOREIGN KEY 外键
##CHECK 检查约束  约束可以分为单列约束：每个约束只能约束一列 多列约束：每个约束可约束多列数据
##DEFAULT  默认值

##根据约束的作用范围划分：
##列级约束：只能作用再一个列上，跟在列的定义后面
##表级约束：可以作用在多个列上，不与列一起，而是单独定义


CREATE TABLE boy(stuno INT PRIMARY KEY,
NAME VARCHAR(20) NOT NULL DEFAULT 'haungbf',
sex VARCHAR(10))

##修改约束
ALTER TABLE boy MODIFY sex VARCHAR(30) NOT NULL;

##描述表的结构
DESCRIBE boy

##取消NOT NULL 约束
ALTER TABLE boy MODIFY sex VARCHAR(30) NULL;

ALTER TABLE boy MODIFY NAME VARCHAR(30) NULL;


SHOW TABLES
##假设存在多个约束限制
##unique 表示唯一


##primary key表示主键  表示其能够自增长  其中需要注意的是 主键相当于唯一约束+非空约束 主键约束列不允许重复，不允许出现空值
##constraint 后面跟的就是约束名字 表示的是name password 两者都是唯一的
CREATE TABLE USER(id INT PRIMARY KEY,
NAME VARCHAR(20),
PASSWORD VARCHAR(20),
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD))


##删除约束
ALTER TABLE USER DROP INDEX uk_name_pwd

##添加约束
ALTER TABLE USER ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)

##修改约束
ALTER TABLE USER MODIFY NAME VARCHAR(20) UNIQUE

DESCRIBE USER

##下面展示一下外键的使用手法
##外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
##从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，
##如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
##还有一种就是级联删除子表数据
##其中需要注意的是：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列
##一对多的关系是需要外表 按照这个东西
##这是主表
CREATE TABLE classes(
id INT,
NAME VARCHAR(20),
number INT,
PRIMARY KEY(NAME,number))
##这是从表
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
classes_name VARCHAR(20),
classes_number INT,
FOREIGN KEY(classes_name,classes_number) REFERENCES classes(NAME,number)
)


##FOREIGN KEY:在表级指定子表中的列 
##REFERENCES: 标示在父表中的列
##ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
##ON DELETE SET NULL(级联置空): 子表中相应的列置空



##check 约束
CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age>20),
PRIMARY KEY(id))

##使用MySQL 使用Limit实现分页
##select * from  user limit (pageNo-1)*PageSize,PageSize;
##limit语句需要放到最后

##视图 只是虚拟的表 不是真实存在的


##子查询
USE myemployees
SHOW TABLES

DESCRIBE employees
DESCRIBE departments
SELECT first_name, last_name FROM employees WHERE
  department_id IN (SELECT department_id FROM departments WHERE location_id= 1400 OR location_id = 1700)






##下面主要介绍的是数据库中关于事务的概念
##事务的定义：事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单
##元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影
##响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行

##事务具有ACID四个特性：
#原子性：事务是一个不可分割的单位i，要么都发生，要么都不发生
#一致性：事务必须使得数据库从一个一致性状态变换到另外一个一致性状态
#隔离性：一个事务的执行不能被其他事务所干扰，说明一个事务中内部的操纵以及使用的数据对并发的其他事务都是隔离的，并发执行的
#各个事务都是不能互相打扰的


##持久性:是指一个事务一旦提交，对数据库中的数据的改变是永久的，接下来其他操作和数据库故障不应该对其有任何影响。

##查看存储引擎
SHOW ENGINES;

 ##这边简单介绍下  用的不多的一些概念
 ##存储过程和函数的相关定义：实现经过编译并存储在数据库中的一段sql语句的集合。
 ##流程控制语句 判断  循环