讲解SQL数据库语句

女爷i 2021-09-15 07:34 427阅读 0赞

# 前言 #

大家好，我是 `Vic`，今天给大家带来`讲解SQL数据库语句`的概述，希望你们喜欢

## 数据库语句 ##

create database teach; use teach; create table `teach`.`producttype`( `pt_id` int not null auto_increment primary key, `pt_name' varchar(20) not null unique); create table `teach`.`client`(`cl_id` char(4) not null primary key, `cl_name` varchar(20) not null, `cl_type` char(6) not null, `cl_guimo` char(2) not null, `cl_tel` varchar(15) not null, `cl_duanjiao` bit(2) not null); create table if not exists product (pr_id int auto_increment primary key not null); alter table product add pr_typeid int not null; desc product; insert into client(cl_id,cl_name,cl_type,cl_guimo,cl_tel,cl_duanjiao) values (1,'vic','hh','xiao',34455); // create table `teach`.`orders`( `or_id` int not null auto_increment primary key, `cl_id` char(4) not null, `pr_id` char(4) not null, `or_price` int not null, `or_num` int not null, `or_date` datetime not null);

select * from producttype;
    update producttype set pt_name='休闲食品' where pt_id=2;
    delete from producttype where pt_id=4;

select cl_id,cl_name,cl_guimo from client where cl_guimo = '大'|| '小'; select * from orders where or_date = 2010; select cl_id '编号' ,cl_name '姓名' from client order by cl_id desc; select * from client where cl_name like '%c';

select pr_num from product group by pr_typeid; select pr_id,or_num from orders where or_num > 2 group by pr_id; select * from orders group by cl_id; select * from client where cl_duanjiao = 1 && cl_guimo = '小' group by cl_id order by cl_id desc; select or_id,cl_name,pr_name,or_price,or_num,or_date from client crass join orders join product; select * from product crass join producttype join orders where producttype = '食品';

use information_schema; show tables; desc tables; select table_name from tables; create database teach; use teach; create table if not exists characters(id int auto_increment primary key,name varchar(20) not null, description text); create table if not exists access(id int auto_increment primary key,name varchar(2) not null, description text); create table if not exists c_a(cid int not null,aid int not null, primary key (cid, aid),foreign key(cid) references characters(id),foreign key (aid) references access(id)); alter table c_a drop foreign key c_a_ibfk_1; create table if not exists c_b(cid int not null references characters(id),aid int not null,primary key(cid, aid),foreign key(aid) references access(id));

alter table accesses add username varchar(20) not null; alter table accesses modify mame varchar(30) not null; alter table characters drop mane; alter table accesses modify description varchar(50); // 修改 rename table acc to accesses; alter table accesses rename acc;

create table if not exists characters(id int auto_increment primary key, name varchar(20) not null, description text); create table if not exists access (id int auto_increment primary key, name varchar(2) not null, description text); create table if not exists c_a(cid int not null , aid int not null , primary key(cid, aid), foreign key(cid) references characters(id) , foreign key(aid) references access(id) ) ; create table if not exists c_b(cid int not null oreign key(cid) references characters(id) , aid int not null , primary key(cid, aid), foreign key(aid) references access(id) ) ; alter table access modify description varchar(50);

select a.id ,a.name from characters a; create table if not exists employess(id int auto_increment primary key, first_name varchar(10) not null,last_name varchar(20),salary float); insert into employees (first_name,last_name,salary)values('junx','zheng',1000),('ting','xue',1300); select last_name,salary,salary*12 sum from employees; //select last_name || job_id from employees; select last_name 'fname' from employees; //创建名 insert into employees(first_name, last_name, salary)values('wang','guang',1000); select distinct salary , id from employees; insert into employees value (4,'ting','cue',1300); select distinct first_name, last_name, salary from employees; select * from employees where salary > 1200; select * from employess first_name like '%j%'; select * from employess where salary in (1200,100); select * from employess where salary is null;

select lower('aBx'); select upper('polill'); select length('lojol'); select char_length('lsjlf'); select replace('zzjjjp','zj','hello'); select substring('zjxx',2,6); select curdate(); select curtime(); select now(); select minute('15:34:21'); select monthname('2017-4-1'); select date_format('2009-10-04 22:23:00','%w %m %y'); select database(); select user(); select version(); select inet_aton('192.168.0.1'); select password('adlfllsf');

SELECT * FROM pet WHERE name LIKE 'b%';
    
    为了找出以“b”开头的名字，使用“^”匹配名字的开始：
    SELECT * FROM pet WHERE name REGEXP '^b';
    
    select * from pet where name regexp binary '^b';
    
    为了找出以“fy”结尾的名字，使用“$”匹配名字的结尾：
    select * from pet where name regexp 'fy$';
    
    为了找出包含一个“w”的名字，使用以下查询：
    SELECT * FROM pet WHERE name REGEXP 'w';
    
    SELECT * FROM pet WHERE name REGEXP '^.....$';
    
    SELECT * FROM pet WHERE name REGEXP '^.{5}$';

SELECT * FROM shop; 列的最大值 SELECT MAX(article) AS article FROM shop; 找出最贵物品的编号、销售商和价格。 SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1; 任务：每项物品的的最高价格是多少？ SELECT article, MAX(price) AS price FROM shop GROUP BY article;

(1)select s#(学号),SName(学生名字) from S where Age < 17 and sex='女生'; (2)select C.C#(课程号),Cname(课程名) from S,SC,C where S.S#=SC.S# and SC.C#=C.C# and sex='男生'; (3)select T.T#,TName from S,SC,C,T where S.S#=SC.S# and SC.C#=C.C# and C.T#=T.T# and sex='男生'; (4)select S# from SC group by S# having count(*)>1; (5)select distinct X.C# from SC as X, SC as Y where X.S#='S2' and Y.S#='S4' and X.C#=Y.C#; (6)select C# from C where C# not in(select C# from S,SC where S.S#=SC.S# and SName='wang'); (7)select C#,Cname from C where not exists (select * from S where not exists(select * from SC where C.C#=SC.C# and SC.S#=S.S#)); (8)select distinct S# from SC as X where not exists (select * from C,T where C.T#=T.T# and TName='liu' and not exists (select * from SC as Y where Y.S#=X.S# and Y.C#=C.C#)); (9)select count(distinct C#) from SC; (10)select avg(age) from S where sex='女生' and S# in(select S# from SC where C#='C4');

脏读：到达广州结果走到一半
    不可重复读：一样的语句，可能被人复读
    幻读：同样两条语句，你在用别人也在用。

设有教学数据库中有4个关系 教师关系 T（T#，TName, Title）工号，名字，职称 课程关系 C（C#，Cname,T#）课程号，课程名，任课老师工号 学生关系S（S#，ＳＮａｍｅ，Ａｇｅ，ｓｅｘ） 选课关系　SC（S#，ｃ＃，Score） （１）检索年龄小于１７岁的女学生的学号和姓名 Select s#,sname from S where age<17 and sex=’f’; （２）检索男学生所学课程的课程号和课程名 Select c#, cname from c where c# in (select distinct b.c# from s a inner join sc b on a.s#=b.s# where a.sex=’m’); （３）检索男学生所学课程的任课老师的工号和姓名 Select T.T#, T.Tname from T inner join C on T.T#=C.T# where C.C# in (select distinct b.c# from s a inner join sc b on a.s#=b.s# where a.sex=’m’); （４）检索至少选修两门课程的学生学号 Select s# from sc group by s# having count()>=2; 检索至少有学号为　Ｓ２和Ｓ４的学生选修的课程的课程号 Select c# from sc where c# in (select c# from sc where s#=’s4’) and c# in (select c# from sc where s#=’s2’); （５）检索ｗａｎｇ同学不学的课程的课程号 select distinct c# from sc where c# not in (select c# from s inner join sc on s.s#=sc.s# where s.sname=’wang’); （６）检索全部学生都选修的课程的课程号与课程名 Select c# from sc group by c# having count()=(Select count() from s); （７）检索选修课程包含ｌｉｕ老师所授全部课程的学生学号 select distinct sc.s# from T inner join c inner join sc on T.T#=c.T# and c.c#=sc.c# where T.Tname=’liu’; （８）统计有学生选修的课程门数 Select count() from c where c# in (select distinct c# from sc); （９）求选修Ｃ４课程的女学生的平均年龄 Select avg(age) from s where sex=’f’ and s# in (select s# from sc where c#=’c4’); （１０）求ｌｉｕ老师所授每门课程的平均成绩 Select avg(score) from sc where c# in (select c# from c inner join t on c.t#=t.t# where t.name=’liu’); （１１）统计选修每门课程的学生人数（超过１０人的课程才统计）。要求显示课程号和人数，查询结果按照人数降序排列，诺人数相同，则按照课程号升序排列。 Select c#, count() number from sc group by c# having count()>=10 order by number desc, c# asc; （１２）检索学号比Ｗａｎｇ同学大，而年龄比他小的同学的学生姓名。 Select sname from s where s#>(select s# from s where sname=’wang’) and age<(select age from s where sname=’wang’); （１３）在表ＳＣ中检索成绩为空值的学生的学号和课程号 （１４）检索姓名以　Ｌ开头的所有学生的学号和课程号 Select s.sname, sc.c# from s inner join sc on s.s#=sc.s# where s.sname like ‘L%’; （１５）求年龄大于女同学平均年龄的男同学的姓名和年龄 Select sname,age from s where sex=’m’ and age>(select avg(age) from s where sex=’f’);

声明光标
    DECLARE cursor_name CURSOR FOR select_statement
    
    光标OPEN语句
    OPEN cursor_name
    
    光标FETCH语句
    FETCH cursor_name INTO var_name [, var_name] ...
    
    光标CLOSE语句
    CLOSE cursor_name

## 数据库技术 ##

![700][]

示意图

![700 1][]

示意图

## 数据库技术 ##

create database teach; use teach; （1） CREATE TABLE `teach`.`producttype` ( `pt_id` INT NOT NULL AUTO_INCREMENT primary key, `pt_name` VARCHAR(20) NOT NULL unique ); （2） CREATE TABLE `teach`.`client` ( `cl_id` CHAR(4) NOT NULL primary key, `cl_name` VARCHAR(20) NOT NULL , `cl_type` CHAR(6) NOT NULL , `cl_guimo` CHAR(2) NOT NULL , `cl_tel` VARCHAR(15) NOT NULL , `cl_duanjiao` BIT(2) NOT NULL ); （3） create table if not exists product(pr_id int auto_increment primary key not null); alter table product add pr_typeid int not null; desc product; insert into client(cl_id,cl_name,cl_type,cl_guimo,cl_tel,cl_duanjiao)values(1,'小 明','经销商','大',88810615,0); insert into client(cl_id,cl_name,cl_type,cl_guimo,cl_tel,cl_duanjiao)values(2,'小 红','经销商','中',88815615,0); insert into client(cl_id,cl_name,cl_type,cl_guimo,cl_tel,cl_duanjiao)values(3,'小 微','零售商','小',88825615,0); （4） CREATE TABLE `teach`.`orders` ( `or_id` INT NOT NULL AUTO_INCREMENT primary key, `cl_id` CHAR(4) NOT NULL , `pr_id` CHAR(4) NOT NULL , `or_price` INT NOT NULL , `or_num` INT NOT NULL , `or_date` DATETIME NOT NULL ); 项目3 insert into producttype(pt_id,pt_name)values(1,'洗里理日用品'),(2,'食品'),(3,'家用电器'),(4,'肉食'); select * from producttype; update producttype set pt_name='休闲食品' where pt_id=2; delete from producttype where pt_id=4;

项目1 （1）select cl_id,cl_name,cl_guimo from client where cl_guimo = '大'|| '小'; (2) select * from orders where or_date = 2010; (3) select cl_id '编号' ,cl_name '姓名' from client order by cl_id desc; (4) select pr_typeid from product; select distinct pr_typeid from product; (5) select * from client where cl_name like '%c'; 项目2 (1) select pr_num from product group by pr_typeid; (2) select pr_id,or_num from orders where or_num > 2 group by pr_id; (3)select * from orders group by cl_id; 项目3 (1) select * from client where cl_duanjiao = 1 && cl_guimo = '小' group by cl_id order by cl_id desc; (2) select or_id,cl_name,pr_name,or_price,or_num,or_date from client crass join orders join product; (3) select * from product crass join producttype join orders where producttype = '食品';

## 关系数据完整性是对关系的某种约束条件 ##

*  实体完整性：对主码进行限制
 *  参照完整性：对外码进行限制
 *  用户定义完整性 ：对具体数据进行限制

## 函数依赖： R（X，Y） ##

（1）完全函数依赖：（学号、课程号） →f 成绩  
（2）部分函数依赖 ：（学号、课程号） →p 姓名  
（3）传递函数依赖 ：学号→所属系号，所属系号→宿舍楼号，学号→t宿舍楼号

## 关系数据库 ##

> 关系数据库是因为采用关系模型而得名，它是目前数据库应用中的主流技术。

> 关系数据库的出现标志着数据库技术走向成熟。

## 关系数据库的特点 ##

（1）数据结构简单。  
（2）功能强。  
（3）使用方便。  
（4）数据独立性高。

## 关系模型的基本术语 ##

（1）关系。  
一个关系对应一个二维表，二维表名就是关系名。  
（2）属性及值域。  
二维表中的列称为关系的属性。  
属性值的取值范围称为值域，每一个属性对应一个值域，不同属性的值域可以相同。  
（3）关系模式。  
二维表中的行定义、记录的类型，即对关系的描述称为关系模式。  
（4）元组。  
每一条记录的值称为关系的一个元组。  
（5）键。  
由一个或多个属性组成。

## 关系模式 ##

关系模式是对关系的描述。

## 关系的完整性 ##

有3类完整性约束：实体完整性、参照完整性和用户定义的完整性。

## SQL的主要功能 ##

（1）数据定义功能。  
（2）数据操纵功能。  
（3）数据控制功能。

> 数据库由3种类型组成：系统数据库、用户数据库数和数据库快照。

## 系统数据库 ##

master 系统信息数据库  
model 模板信息数据库  
msdb 代理信息数据库  
tempdb 临时信息数据库  
resource 资源信息数据库

## 用户数据库 ##

用户数据库包括用户自定义的数据库和系统的示例数据库。

## 数据库快照 ##