数据库(二)
数据库数据模型包括:层次模型、网状模型、关系模型和对象模型
主键: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;