数据库（二）

数据库数据模型包括：层次模型、网状模型、关系模型和对象模型

主键：primary key   外键：Forergn key  唯一：Unique  auto_increment:自增  默认值：default

关联关系存在三种基本关系类型：一对一，一对多，多对多。

SQL：结构化查询语言

1 数据定义语言：DDL  

create database 创建数据库

drop database 删除数据库

2 数据查询语言：DQL

create/drop/alter/rename  table :创建、删除、修改、重命名 表格

3 数据控制语言: DCL

给予用户访问授权权限的 grant 语句

取消用户访问权限的 revoke语句

关系模型把时间看成实体（Entity）和联系（Rclationship）组成

table表 column列 row行

整数数据类型：tinyint 、smallint 、middleint 、bigint

浮点数据类型：real /float /decimal/numeric/double

字符型：char/varchar

复制表结构：select *into table2 from table1;

修改表：alter table friend add email varchar(20);

创建索引：create index<索引名> on <表名> （<列名>，）

执行流程：from-where-group by -select -having- order by -limit

insert into <表名>（列名列表）values(值列表) 

插入多行可以用逗号隔开

更新表中数据：update<表名> set <列1=新值1,列2= 新值2>where <过滤条件>

update table set name =zhangsan where id = 2;

删除数据:delect from <表名> where <过滤条件>

truncate与delect 的区别

truncate效率比delect高，删除后没法恢复,delect相反

查询：

select* from table

*：表示所有。包含null

all:不包含null的所有

dictinct:去重，非空

like '%mike%'

聚合函数：count/sum/avg/max/min

在子查询中返回使用运算符

exist （存在）如果条件为真，则返回整个结果，如果为假，返回空白

all 运算符 any运算符

union运算符;链接2个表

insert into 添加数据到数据库

CREATE TABLE t_people(
	p_id INT PRIMARY KEY AUTO_INCREMENT,
	p_name VARCHAR(100),
	p_sex CHAR(2) DEFAULT '男',
	p_phone VARCHAR(20)
);
SELECT * FROM t_people;

/*
insert into 添加数据到数据库
*/
INSERT INTO t_people(p_name,p_sex,p_phone) VALUES('隔壁老王','男','1355589001');
INSERT INTO t_people VALUES(2,'小芳','女','9090910');

#一次添加多条语句
INSERT INTO t_people VALUES(3,'小明','男','123456'),(4,'小明2','男','123456'),(5,'小明3','男','123456');

CREATE TABLE t_people2(
	p_id INT PRIMARY KEY AUTO_INCREMENT,
	p_name VARCHAR(100),
	p_sex CHAR(2) DEFAULT '男',
	p_phone VARCHAR(20)
);

#数据的复制
INSERT INTO t_people2 SELECT * FROM t_people;
SELECT * FROM t_people2;



/*
更改数据
update
*/
UPDATE t_people2 SET p_sex = '女';

SELECT * FROM t_people2;

UPDATE t_people2 SET p_sex = '男' WHERE p_id=2 AND p_name="小芳";

#修改多个列
UPDATE t_people2 SET p_name="小小芳",p_phone='123' WHERE p_name="小芳";

/*
删除语句
delete
*/
DELETE FROM t_people2;
SELECT * FROM t_people2;

DELETE FROM t_people2 WHERE p_name="隔壁老王";

#删除一个区间的数据
DELETE FROM t_people2 WHERE p_id BETWEEN 2 AND 4;

DELETE FROM t_people2 WHERE p_id>=2 AND p_id<=4;

#直接删除所有的数据，
TRUNCATE TABLE t_people2;

/*
select
*/

#投影  列别名  表别名
SELECT peo.p_phone AS '电话',peo.p_sex AS '性别' FROM t_people AS peo;   #通配符，匹配所有


#计算列
SELECT * FROM t_people;
SELECT p_id+1 FROM t_people;

#将列拼接起来用concat函数
SELECT CONCAT(p_name,"-",p_id) FROM t_people;



#排除重复数据,查看数据种类
SELECT DISTINCT p_sex FROM t_people;


#返回限定的数目
SELECT * FROM t_people LIMIT 0,5;   #0表示下标 5表示返回的条数
SELECT * FROM t_people LIMIT 5,5;

SELECT * FROM t_people LIMIT 8;  #写一个数默认从0开始


#选择返回的数据
SELECT * FROM t_people WHERE p_sex = "男" AND p_name="小明";
SELECT * FROM t_people WHERE p_sex <> "男";


SELECT * FROM t_people WHERE p_id BETWEEN 2 AND 5;

#in 表匹配一个集合
SELECT * FROM t_people WHERE p_id IN (1,2,3);
#not in
SELECT * FROM t_people WHERE p_id NOT IN (1,2,3);

SELECT * FROM t_people WHERE p_name = "隔壁老王"  #精确匹配


#模糊查询
SELECT * FROM t_people WHERE p_name LIKE "%隔壁"   #匹配零个到多个字符

SELECT * FROM t_people WHERE p_name LIKE "___";    #_表示一个字符

SELECT * FROM t_people WHERE p_name LIKE "隔壁__" 

SELECT * FROM t_people WHERE p_name IS NULL;


#排序 order by
SELECT * FROM t_people ORDER BY p_phone;
SELECT * FROM t_people ORDER BY p_id;


#
SELECT * FROM t_people ORDER BY p_phone,age;
ALTER TABLE t_people ADD age INT;


SELECT * FROM t_people ORDER BY p_phone DESC   #降序排列
SELECT * FROM t_people ORDER BY p_phone ASC   #默认就是 升序


SELECT * FROM t_people WHERE p_name LIKE "%隔壁%" ORDER BY age DESC LIMIT 0,2;

SELECT * FROM t_people;

/*
count()函数统计所有行
*/
# *统计null的行
SELECT COUNT(*) FROM t_people;

CREATE TABLE t_test(
	id INT,
	NAME VARCHAR(20)
);
SELECT * FROM t_test;

SELECT COUNT(*) FROM t_test;

#all
SELECT COUNT(ALL NAME) FROM t_test;  #默认就all不统计空行

#distinct
SELECT COUNT(DISTINCT p_sex) FROM t_people;   #distinct统计非空并且不重复的数据

SELECT COUNT(*) FROM t_people WHERE age IS NULL;  #统计所有的空行

/*
sum 函数统计年龄的总和 all  distinct
*/

SELECT SUM(ALL age) FROM t_people;


/*
avg   统计平均值可以sum/count
*/
SELECT AVG(age) FROM t_people;   #avg求平均值自求非空的平均值
SELECT SUM(age)/COUNT(*) FROM t_people; 
SELECT * FROM t_people;

/*
max
*/

SELECT MAX(ALL age) FROM t_people;
SELECT MIN(ALL age) FROM t_people;


/*
分组
*/
SELECT p_phone,age,COUNT(*) FROM t_people GROUP BY p_phone,age;

DROP TABLE t_student;

CREATE TABLE t_student(
	s_id INT PRIMARY KEY AUTO_INCREMENT,
	s_name VARCHAR(20),
	s_sex CHAR(2) DEFAULT '男',
	s_tid INT,
	CONSTRAINT fore_id FOREIGN KEY(s_tid) REFERENCES t_teacher(t_id)  #外键约束
);

DROP TABLE t_teacher
CREATE TABLE t_teacher(
	t_id INT PRIMARY KEY AUTO_INCREMENT,
	t_name VARCHAR(20)
);

CREATE TABLE t_subject(
	sub_id INT PRIMARY KEY AUTO_INCREMENT,
	sub_name VARCHAR(30)
);

SELECT * FROM t_student;
SELECT * FROM t_teacher;

DROP TABLE t_teacher;  #有外键约束的表不能直接删除

#删除外键
ALTER TABLE t_student DROP FOREIGN KEY fore_id;

#通过alter语句动态增加外键
ALTER TABLE t_student ADD CONSTRAINT for_id FOREIGN KEY (s_tid) REFERENCES t_teacher(t_id);



/*多对多*/
SELECT * FROM t_subject;
SELECT * FROM t_student;

CREATE TABLE t_stu_sub(
	ss_id INT PRIMARY KEY AUTO_INCREMENT,
	ss_stuid INT,
	ss_subid INT,
	FOREIGN KEY (ss_stuid) REFERENCES t_student(s_id),
	FOREIGN KEY (ss_subid) REFERENCES t_subject(sub_id)
);

SELECT * FROM t_student;
SELECT * FROM t_subject;
SELECT * FROM t_stu_sub;

INSERT INTO t_stu_sub(ss_stuid,ss_subid)VALUE(3,3);

#自增长默认从1000开始
ALTER TABLE t_student AUTO_INCREMENT =1000;

CREATE DATABASE j123_day03;
USE j123_day03;
CREATE TABLE t_stu(
	s_id INT,
	s_name VARCHAR(20),
	s_subject VARCHAR(20),
	s_grade INT,
	s_classId INT,
	FOREIGN KEY(s_classId) REFERENCES t_class(c_id)
);

DROP TABLE t_stu;
CREATE TABLE t_class(
	c_id INT PRIMARY KEY AUTO_INCREMENT,
	c_name VARCHAR(20)
);

INSERT INTO t_class VALUES (1,"一班"),(2,"二班");
INSERT INTO t_class VALUES (3,"三班"),(4,"四班");
INSERT INTO t_stu VALUES(1,);

INSERT INTO t_stu VALUES(1,'张三','计算机基础',60,1),(1,'张三','c语言',88,1),(1,'张三','数据库',90,1);

INSERT INTO t_stu VALUES(2,'李四','计算机基础',80,2),(2,'李四','c语言',70,2),(2,'李四','数据库',55,2);

INSERT INTO t_stu VALUES(3,'王五','计算机基础',67,3),(3,'王五','数据库',90,3);

SELECT * FROM t_stu;
SELECT * FROM t_class;


#子查询嵌套在select中，子查询返回的内容必须是单行单列的值
SELECT s_name,(SELECT c_name FROM t_class c WHERE c.c_id = s.s_classId) '班级' FROM t_stu s;


SELECT * FROM (SELECT * FROM t_stu) AS ntable;


#嵌套在from中的子查询，返回的内容可以是多行多列
SELECT MAX(sumgrade) FROM 
(SELECT s_name,SUM(s_grade) sumgrade FROM t_stu GROUP BY s_name) AS netable;


#嵌套在where 条件中的子查询
SELECT * FROM t_stu WHERE s_grade >=ALL
(SELECT s_grade FROM t_stu WHERE s_name = '王五') AND s_name!='王五';

#in 表示一个范围
SELECT * FROM t_stu WHERE s_grade NOT IN 
(SELECT s_grade FROM t_stu WHERE s_name='张三') AND s_name !="张三";



#all匹配子查询里面每一个

SELECT * FROM t_stu WHERE s_grade >=ALL 
(SELECT s_grade FROM t_stu WHERE s_name = "王五") AND s_name !="王五";

SELECT * FROM t_stu WHERE s_grade >=
(SELECT MAX(s_grade) FROM t_stu WHERE s_name = "王五") AND s_name!="王五";

#any  匹配其中一个数据

SELECT * FROM t_stu WHERE s_grade >=ANY
(SELECT s_grade FROM t_stu WHERE s_name = "王五") AND s_name !="王五";


#exists
SELECT * FROM t_stu WHERE NOT EXISTS 
(SELECT * FROM t_stu WHERE s_grade >
(SELECT s_grade FROM t_stu WHERE s_name = '张三' AND s_subject = "数据库"));



# 找一下比c语言的平均成绩高的学科的名称
# 找一下平均成绩最高的班级的名称。
SELECT DISTINCT s_subject FROM t_stu WHERE s_grade >
(SELECT AVG(s_grade) FROM t_stu WHERE s_subject="c语言") AND s_subject!="c语言";

# 找出平均成绩比c语言科目的平均成绩要高的科目名称
SELECT s_subject FROM 
(SELECT s_subject,AVG(s_grade) AS avgs FROM t_stu WHERE s_subject !="c语言" GROUP BY s_subject) AS ntable
WHERE avgs>(SELECT AVG(s_grade) FROM t_stu WHERE s_subject='c语言');

SELECT MAX(avgs),(SELECT c_name FROM t_class cl WHERE tt.s_classId = cl.c_id) '班级名称' FROM 
(SELECT s_classId,AVG(s_grade) avgs FROM t_stu GROUP BY s_classId) AS tt;


SELECT *,(SELECT c_name FROM t_class t WHERE s.s_classId = t.c_id) AS className FROM t_stu s;