【数据库】SQL语言基础语句和练习

最新推荐文章于 2022-03-21 14:41:18 发布

茗樱.Stella

最新推荐文章于 2022-03-21 14:41:18 发布

阅读量710

点赞数

分类专栏：数据库文章标签：数据库 SQL语言

本文链接：https://blog.csdn.net/qq_23996157/article/details/82712543

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

SQL：结构化查询语言，数据库操作的国际标准语言。

SQL分为数据定义（CREATE、ALTER、DROP）、数据查询（SELECT）、数据操纵（INSERT、DELETE、UPDATE）

数据控制（GRANT、REVOKE、COMMIT、ROLLBACK）。

SQL关键字、对象名、列名不区分大小写。

1.按某一列(column)升序排列

select * from table order by column;

select * from scott.emp order by sal;

2.删除表（表的结构、属性以及索引也会被删除）

drop table 表名称;

3.删除表中所有记录（记录清空，表结构还在）【效率比delete高】

truncate table 表名称;

4.删除表中某条/所有记录（记录清空，表结构还在）

delete from 表名称;

delete from 表名称 where 列名称=值;

5.查询表中某列/所有记录

select * from 表名称;

select 列名称 from 表名称;

select * from scott.emp;

select sal from scott.emp;

6.创建表结构

create table dwq_test

(

id number(7) not null,

name varchar2(20) not null,

email varchar2(20) not null,

status number(1) not null,

primary key (id)

);

 create table dwq_test
  (
  id number(7) not null,
  name varchar2(20) not null,
  email varchar2(20) not null,
  status number(1) not null,
  primary key(id)
  );

7.插入表记录

insert into dwq_test (id,name,email,status) values (1,'dwq','570778119@qq.com',1);

insert into dwq_test (id,name,email,status) values (1,'dwq','570778119@qq.com',1);

8.查询表所有记录

select * from dwq_test;

select * from dwq_test;

9.更新表记录中的列

【更新记录中的一列】update dwq_test set name='jtd' where name='dwq';

update dwq_test set name='jtd' where name='dwq';

【更新记录中的多列】update dwq_test set name='lch', email='292290045@qq.com', status=0 where id=1;

update dwq_test set name='lch', email='292290045@qq.com', status=0 where id=1;

10.删除表中记录

delete from dwq_test where id=1;

delete from dwq_test where id=1;

11.排序记录【不可同时排序多个，order by后只能排序一列】

【默认升序排列（省略结尾的ASC），字母/数字】

<1>select * from dwq_t order by email;

<2>select * from dwq_t order by email ASC;

注：<1>和<2>等价。

【指定逆序排列，字母/数字】select * from dwq_t order by email DESC;

12.使用多条件查询记录

【and只能查询一条记录】select * from dwq_t where id=1 and name='dwq';

【or查询多条记录】select * from dwq_t where id=1 or name='jtd';

【and和or同时出现，需要加括号判定结合型】select * from dwq_t where id=1 or (name='lch' and name='dwq');

13.去掉一个列中重复的记录【只能去掉一个列】

select distinct email from dwq_t;

14.PL/SQL中修改表数据

【加表名.rowid才能修改数据】select dwq_t.* , dwq_t.rowid from dwq_t;

15.查询表中前两行记录

select * from dwq_tt where rownum <=2;

16.查询一列中满足前缀/后缀的记录

【满足前缀】select * from dwq_tt where email like '570%';

【满足后缀】select * from dwq_tt where email like '%qq.com';

【满足一段（模糊查询）】select * from dwq_tt where email like '%0045%';

【不满足一段（模糊查询）】select * from dwq_tt where email not like '%0045%';

【用_代替一个字符，查询第一个字符之后的字母，不适用于数字】select * from dwq_ttt where name like '_wq';

【用_代替多个字符，不适用于数字】select * from dwq_ttt where name like '_w_';

17.以某一列/多列为主进行组合

【一列为主组合】select Customer,sum(OrderPrice) from test_customer1 group by Customer;

【多列为主组合】

<1>select Customer,sum(OrderPrice) from test_customer1 group by Customer,OrderPrice;【】

<2>select Customer,OrderPrice from test_customer1;【插入数据顺序】

<3>select Customer,sum(OrderPrice) from test_customer1 group by OrderPrice;【报错，不能以OrderPrice为主】

注：查询结果顺序不同。

原表顺序：

语句<1>顺序：

语句<2>顺序：

18.有函数时使用where条件查询【where 和函数不能同时出现】

select Customer,sum(OrderPrice) from test_customer1 group by Customer having sum(OrderPrice)>3000;

19.返回数据记录的条数

【根据表中某一列（没必要）】select count(OrderPrice) from test_customer1;

【根据表中某一列中满足条件的记录的条数（把列名“count(Customer)”改为“orders”）】select count(Customer) as orders from test_customer1 where Customer='dwq';

【表中所有记录的条数】select count(*) from test_customer1;

【根据某一列的值（不重复计数）】select count(distinct Customer) from test_customer1;

20.求平均数【NULL 值不包括在计算中】

【非嵌套】select avg(orderprice) as avg from test_customer1;

【嵌套】select customer from test_customer1 where orderprice>(select avg(orderprice) from test_customer1);

21.查询某一列的第一条/最后一条记录【oracle中无first/last关键字】

【oracle报错】select first(orderprice) as first_orderprice from test_customer1;

【oracle报错】select last(orderprice) as last_orderprice from test_customer1;

22.求最大/小值【NULL 值不包括在计算中】

【比较一列的最大值】select max(orderprice) as max_orderprice from test_customer;

【比较一列的最小值】select min(orderprice) as min_orderprice from test_customer;

【比较一行（即多个列表）的最大值和最小值】

select id, chinese, math, english, greatest (chinese, math, english) max,least(chinese, math, english) min from score;

23.oracle注释：【”--“后为注释】

select * from test_customer; --查询

24.返回多列第一个非空值

select coalesce (chinese,math,english) from score;

25.字母大小写转换

【大写】select upper(customer) as Upname from test_customer;

【小写】select lower(customer) as Upname from test_customer;

26.求和

【某一列】select sum(orderprice) as sum_orderprice from test_customer;

27.

select * from test_customer;

select customer,max(orderprice) from test_customer;

（不是单组分组函数）

【改为：select customer,max(orderprice) from test_customer group by customer;】

28.check约束

CREATE TABLE Persons ( Id_P int NOT NULL CHECK (Id_P>0), LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

29.default默认值

CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' )

30.unique不可重复

CREATE TABLE Persons ( Id_P int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

31.级联删除【如果主表记录删除，则主表中作为外键的记录也删除】

表a保持不变

create table a

(

id varchar(20) primary key,

password varchar(20) not null

);

表b外键信息中加入级联删除

create table b

(

name varchar(50) not null,

userId varchar(20),

foreign key (userId) references a(id) on delete cascade

);

32.case when

http://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html

33.decode

http://www.cnblogs.com/vinsonLu/p/3512526.html

http://jingyan.baidu.com/article/3a2f7c2e16162a26afd611be.html

34.over

partition by是批量分组，而group by是对某一个分组。

http://blog.csdn.net/an342647823/article/details/9039339

http://www.cnblogs.com/shined/archive/2013/01/16/2862809.html

http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_over.html

http://www.cnblogs.com/sumsen/archive/2012/05/30/2525800.html

35.nvl

nvl(参数1，参数2)

当参数1为空时，值为参数2；

当参数1不为空时，值为参数1.

http://blog.sina.com.cn/s/blog_46e9573c01015ik8.html

36.日期函数

【获取系统日期】select sysdate from dual;

【把系统日期转换成字符串】select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;

【将日期字符串转换为日期类型]】insert into emp(hiredate) values(to_date('2004-10-10','yyyy-mm-dd'));

【将日期字符串转换成数字类型】select to_number(to_char(sysdate,'hh12')) from dual;

http://www.cnblogs.com/chuncn/archive/2009/04/29/1381282.html

37.字符串截取

select substr('abcdef',1,3) from dual;

38.查找子串位置

select instr('abcfdgfdhd','fd') from dual ;

39.连接字符串

select 'HELLO'||'hello world' from dual;

40.字符串长度

select length('abcdef') from dual

41.修改列类型

【新类型和旧类型兼容，旧类型列值为空】alter table 表名 modify(列名新类型);

【新类型和旧类型不兼容，旧类型有列值】

//修改旧列名

alter table 表名 rename column 旧列名 to 新列名;

//增加与旧列名相同的列

alter table 表名 add 列名列类型;

//将修改后的列值更新到增加的列中

update 表名 set 增加的列名=trim(修改后的列名);

//删除修改后的列

alter table 表名 drop column 修改后的列名;

42.sign

oracle sign 函数　取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

SQL> select sign(100),sign(-100),sign(0) from dual;

sql执行顺序

　　1. FROM：对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1

　　2. ON: 对vt1表应用ON筛选器只有满足 join_condition 为真的行才被插入vt2

　　3. OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2，生成t3，如果from包含两个以上表，则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束。

　　4. WHERE：对vt3应用 WHERE 筛选器只有使 where_condition 为true的行才被插入vt4

　　5. GROUP BY：按GROUP BY子句中的列列表对vt4中的行分组生成vt5

　　6. CUBE|ROLLUP：把超组(supergroups)插入vt6，生成vt6

　　7. HAVING：对vt6应用HAVING筛选器只有使 having_condition 为true的组才插入vt7

　　8. SELECT：处理select列表产生vt8

　　9. DISTINCT：将重复的行从vt8中去除产生vt9

　　10. ORDER BY：将vt9的行按order by子句中的列列表排序生成一个游标vc10

　　11. TOP：从vc10的开始处选择指定数量或比例的行生成vt11 并返回调用者

有3张表，学生、课程、成绩

t_student:

student_id number(6) 主键,

student_name varchar2(20) 不能重复不能为空,

student_sex varchar2(1) '1'-男 '2'-女，默认为'1'，进行check约束

t_class:

class_id number(6) 主键,

class_name varchar(20) 不能重复不能为空

t_score:

score_id number(6) 主键,

student_id number(6) 外键约束不能为空,如果主表删除了级联删除,

class_id number(6) 外键约束不能为空,如果主表删除了级联删除,

score_score（成绩） number(4,1) 不能为空，最大100分

score_makeup（补考成绩） number(4,1)，-1表示没有进行补考，最大100分

要求：

1、根据上述信息创建3张表

2、t_student表添加1列student_status varchar2(1) '1'-有效 '0'-无效，默认为'1'，进行check约束，要用alter table实现

3、输入一些测试数据，要用insert into语句

4、用1条sql语句查询所有不及格学生的信息，要求显示：学生姓名不及格课程总数

5、用1条sql语句查询男生、女生分别有多少人，要求显示：男生人数女生人数

6、用1条sql语句查询0-10分、11-20分、21-30分、...81-90分、91-100分分别有多少人，要求显示：分数段区间人数

7、查询补考也不及格的学生信息，要求显示：学生姓名课程名称该课成绩该课补考成绩不及格课程总数补考不及格课程总数

--1.创表

/* Table: "t_student" */

/*drop table ttt_student cascade constraints;

create table ttt_student (

student_id number(6) not null,

student_name varchar2(20) unique not null,

student_sex varchar2(1) default 1 check(student_sex='1' or student_sex='2'),

constraint PK_TTT_STUDENT primary key (student_id)

);*/

DROP TABLE TTT_STUDENT CASCADE CONSTRAINTS;--删除关联

CREATE TABLE TTT_STUDENT (

STUDENT_ID NUMBER(6) NOT NULL,

STUDENT_NAME VARCHAR2(20) UNIQUE NOT NULL,

STUDENT_SEX VARCHAR2(1) DEFAULT 1 CHECK(STUDENT_SEX='1' OR STUDENT_SEX='1'),

PRIMARY KEY(STUDENT_ID)

);

/* Table: "t_class" */

/*drop table ttt_class cascade constraints;

create table ttt_class (

class_id number(6) not null,

class_name varchar2(20) unique not null,

constraint PK_TTT_CLASS primary key (class_id)

);*/

DROP TABLE TTT_CLASS CASCADE CONSTRAINTS;

CREATE TABLE TTT_CLASS(

CLASS_ID NUMBER(6) NOT NULL,

CLASS_NAME VARCHAR(20) UNIQUE NOT NULL,

PRIMARY KEY(CLASS_ID)

);

/* Table: "t_score" */

/*alter table ttt_score

drop constraint FK_TTT_SCORE_REFERENCE_TTT_STUDEN;

alter table ttt_score

drop constraint FK_T_SCORE_REFERENCE_T_CLASS;

drop table ttt_score cascade constraints;

create table "t_score" (

"score_id" number(6) not null,

"student_id" number(6) not null,

"class_id" number(6) not null,

"score_score" number(4,1) not null check("score_score" between 0 and 100),

"score_makeup" number(4,1) default -1 check(("score_makeup" between 0 and 100) or "score_makeup"=-1),

constraint PK_T_SCORE primary key ("score_id"),

foreign key ("student_id") references "t_student"("student_id") on delete cascade,

foreign key ("class_id") references "t_class"("class_id") on delete cascade

);*/

ALTER TABLE TTT_SCORE

DROP CONSTRAINT FK_TTT_SCORE_REFERENCE_TTT_STUDENT;

ALTER TABLE TTT_SCORE

DROP CONSTRAINT FK_TTT_SCORE_REFERENCE_TTT_CLASS;

DROP TABLE TTT_SCORE CASCADE CONSTRAINTS;

CREATE TABLE TTT_SCORE(

SCORE_ID NUMBER(6) NOT NULL,

STUDENT_ID NUMBER(6) NOT NULL,

CLASS_ID NUMBER(6) NOT NULL,

SCORE_SCORE NUMBER(4,1) NOT NULL CHECK(SCORE_SCORE BETWEEN 0 AND 100),

SCORE_MAKEUP NUMBER(4,1) DEFAULT -1 CHECK((SCORE_MAKEUP BETWEEN 0 AND 100) or SCORE_MAKEUP = -1),

PRIMARY KEY(SCORE_ID),

FOREIGN KEY(STUDENT_ID) REFERENCE TTT_STUDENT(STUDENT_ID) ON DELETE CASCADE,

FOREIGN KEY(CLASS_ID) REFERENCE TTT_CLASS(CLASS_ID) ON DELETE CASCADE

);

--------------------------------------------------------------------------------

--2.alter加列

--alter table "t_student" add "student_status" number(1) default 1 check("student_status"=1 or "student_status"=0);

/*alter table "t_student" add "student_status" varchar2(1) default 1 check("student_status"='1' or "student_status"='0');*/

ALTER TABLE TTT_STUDENT ADD STUDENT_STATUS VARCHAR2(1) DEFAULT 1 CHECK(STUDENT_STATUS = 1 OR STUDENT_STATUS = 0);

--------------------------------------------------------------------------------

--3.insert加数据，update改数据

insert into "t_student" ("student_id" , "student_name" ) values (1,'dwq');

insert into "t_student" ("student_id" , "student_name" ) values (2,'jtd');

insert into "t_student" ("student_id" , "student_name" ) values (3,'lch');

update "t_student" set "student_sex"='2' where "student_name" ='lch';

insert into "t_class" ("class_id" , "class_name" ) values (1,'math');

insert into "t_class" ("class_id" , "class_name" ) values (2,'english');

insert into "t_score" ("score_id" ,"student_id","class_id","score_score" ) values (1,1,1,100);

insert into "t_score" ("score_id" ,"student_id","class_id","score_score" ) values (2,2,1,55);

insert into "t_score" ("score_id" ,"student_id","class_id","score_score" ) values (3,3,1,59);

insert into "t_score" ("score_id" ,"student_id","class_id","score_score" ) values (4,2,2,55);

update "t_score" set "score_makeup"=59 where "score_id" =2;

update "t_score" set "score_makeup"=33 where "score_id" =3;

update "t_score" set "score_makeup"=21 where "score_id" =4;

update "t_score" set "score_makeup"=80 where "score_id" =4;

--------------------------------------------------------------------------------

--4.学生姓名不及格课程总数

/*select "t_student"."student_name", count("t_score"."class_id") as "failed"

from "t_student" join "t_score"

on ("t_student"."student_id" = "t_score"."student_id" and "t_score"."score_score" <60)

group by "t_student"."student_name";*/

SELECT TTT_STUDENT.STUDENT

--------------------------------------------------------------------------------

--5.男生人数女生人数

/*select (case when "student_sex"='1' then 'M' when "student_sex"='2' then 'F' end ) "sex",Count(*) "num" from "t_student"

group by "student_sex";*/

select sum(decode("student_sex",'1',1)) boy_sum,sum(decode("student_sex",'2',1)) girl_sum from "t_student";

--------------------------------------------------------------------------------

--6.分数段区间人数

select

case when "score_score" <= 10 then '[0,10]'

when "score_score" > 10 and "score_score" <= 20 then '(10,20]'

when "score_score" > 20 and "score_score" <= 30 then '(20,30]'

when "score_score" > 30 and "score_score" <= 40 then '(30,40]'

when "score_score" > 40 and "score_score" <= 50 then '(40,50]'

when "score_score" > 50 and "score_score" <= 60 then '(50,60]'

when "score_score" > 60 and "score_score" <= 70 then '(60,70]'

when "score_score" > 70 and "score_score" <= 80 then '(70,80]'

when "score_score" > 80 and "score_score" <= 90 then '(80,90]'

when "score_score" > 90 and "score_score" <= 100 then '(90,100]'

end "score",

count(*) "num"

from "t_score"

group by