经典SQL语句大全

合集下载

数据库sql查询语句大全

数据库sql查询语句大全数据库SQL查询语句是用来从数据库中检索数据的命令。

以下是一些常见的SQL查询语句大全：1. SELECT语句，用于从数据库中选择数据。

例如，SELECT FROM 表名;2. WHERE子句，用于过滤数据，只返回满足特定条件的行。

例如，SELECT FROM 表名 WHERE 列名 = '条件';3. ORDER BY子句，用于对结果集按照指定列进行排序。

例如，SELECT FROM 表名 ORDER BY 列名;4. GROUP BY子句，用于对结果集按照指定列进行分组。

例如，SELECT 列名1, 列名2 FROM 表名 GROUP BY 列名1;5. HAVING子句，用于过滤分组后的数据。

例如，SELECT 列名1, COUNT(列名2) FROM 表名 GROUP BY 列名1 HAVING COUNT(列名2) > 10;6. JOIN子句，用于在多个表之间建立关联。

例如，SELECT FROM 表名1 INNER JOIN 表名2 ON 表名1.列名 = 表名2.列名;7. UNION操作符，用于合并两个或多个SELECT语句的结果集。

例如，SELECT 列名1 FROM 表名1 UNION SELECT 列名2 FROM 表名2;8. INSERT INTO语句，用于向数据库表中插入新记录。

例如，INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);9. UPDATE语句，用于更新数据库表中的记录。

例如，UPDATE 表名 SET 列名 = 值 WHERE 条件;10. DELETE FROM语句，用于从数据库表中删除记录。

例如，DELETE FROM 表名 WHERE 条件;以上是一些常见的SQL查询语句，它们可以帮助用户从数据库中检索、过滤、排序、分组和更新数据。

当然，SQL语言还有很多其他的功能和语法，这些只是其中的一部分。

sql经典语句（SQLclassicsentences）

sql经典语句（SQL classic sentences）I. Basic operation1) DESC, the describe role is to display the structure of the data table using the form: desc data table name2) distinct eliminates duplicate data usage: select, distinct, field name, from data table3) order by field 1 ASC, field 2 desc4) nested queries select, emp.empno, emp.ename, emp.job, emp.salFrom scott.empWhere sal>= (select, Sal, from, scott.emp, where, ename='WARD');5) nested queries select, emp.empno, emp.ename, emp.job, in, emp.salFrom scott.empWhere, Sal, in (select, Sal, from, scott.emp, where, ename ='WARD');6) nested queries select, emp.empno, emp.ename, emp.job, any, emp.salFrom scott.empWhere Sal > any (select, Sal, from, scott.emp, where, job='MANAGER');Equivalent to (1) select, Sal, from, scott.emp, where, job ='MANAGER'(2) select, emp.empno, emp.ename, emp.job, emp.salFrom scott.empData found in where Sal > (1) data a, or, Sal > (1), data found in B, or, Sal > (1), CEg:Select, Sal, from, scott.emp, where, job ='MANAGER'results; 12,10,13Equivalent to sal=12,10,13 or SAL> (12, OR, 10, OR, 13)7) the intersection operation is the intersection concept in the set. The sum of the elements that belong to the set A and belongs to the set B is the intersection. In the command edit area, execute the following statement.Eg:(select, djbh, from, ck_rwd_hz) intersect (select, djbh, from, ck_rwd_mx) documents numbered the sameSelect * from ck_rwd_mx a,((select, djbh, from, ck_rwd_hz) intersect (select, djbh, from, ck_rwd_mx)) BWhere a.djbh =b.djbhTwo function1) ceil takes the minimum integer ceil (N) greater than or equal to the value N; select, Mgr, mgr/100, ceil (mgr/100), from, scott.emp;2) floor takes the maximum integer floor (N) less than or equal to the value N; select, Mgr, mgr/100, floor (mgr/100), from, scott.emp;3) the remainder of mod m divisible by mod (m, n) n4) power, m, N, square, mod (m, n)5) round m four, five, keep the n bit mod (m, n)Select round (8.655,2) from dual; 8.66Select round (8.653,2) from dual; 8.656) sign n>0, take 1; n=0, take 0; n<0, take -1;7) AVG averages AVG (field name)8) count statistics total count (field name) select (*) from scott.emp; select count (distinct job) from scott.emp;9) min calculates numeric fields minimum, select, min (SAL), minimum salary from scott.emp;10) max calculates numeric field maximum, select, max (SAL), highest salary from scott.emp;11) sum calculates the sum of numeric fields, select, sum (SAL), the sum of salaries, from, scott.emp;Three input data1) single data entryInsert into data tables (fields 1, fields 2,...) valuse (field name 1 values, field name 2 values,...)Numeric fields can write values directly; character fields add single quotation marks; date fields add single quotes; at the same time pay attention to the order of days and months2) multi line data entryInsert into data table (field name 1, field name 2,...)(select (field name 1 or operation, field name 2 or operation,...) from data table where condition)3) data replication between tablesCreate table scott.testAs(Select, distinct, empno, ename, hiredate, from, scott.emp, where, empno>=7000);Create table spkpk_liu as select * from spkfk; creates tables and copies data, but creates incomplete table informationThis is all the way to full table backups.Usually after the table is built, you need to see if you want to build the index and primary key again.And "create, table, spkpk_liu, as, select * from, spkfk.""After this table is built, many of the parameter values of the table are the default minimum values, such as the initial value of the original table 10M, and the new table is probably only 256K.Formal environment used in the table, generally do not recommend such a table built.In this way, just a little lazy, if you do so,A statement can achieve the purpose of building tables and inserting data.For example, you need to modify the data in table A, and you might want to back up the data from the A table before you modify it.This time you can use create table... As...This makes it easy to retrieve data from the A table in the futureYou can do this when you debug your own program, but you can't create processes, packages, functions like thisFour delete dataDelete deletes data; truncate deletes the entire table data, but retains the structure1) delete recordsDelete from scott.test where empno and empno <=8000 > = 7500;2) delete the entire dataTruncate table scott.test;Similarities and differences between truncate, delete and dropNote: the delete here refers to the delete statement without the where clauseThe same thing: truncate and the delete without the where clause, and drop will delete the data in the tableDifference:1. truncate and delete only delete data and do not delete the structure of the table (definition)The drop statement will delete the structure of the table, the dependent constraints (constrain), the trigger, and the index (index); the stored procedure / function that depends on the table will be retained, but the invalid state will be changedThe 2.delete statement is DML, which is put into the rollback segement before the transaction is committed, and if you have the corresponding trigger, the execution will be triggeredTruncate, drop is DDL, the operation takes effect immediately, and the original data is not put into the rollback segment and cannot be rolled back. The operation does not trigger trigger.. Obviously, the drop statement releases all the space occupied by the table3. speed, in general: drop>, truncate > deleteOn use, you want to delete part of the data rows, with delete,take care to bring the where clause. The rollback section is large enough to be rolled back through the ROBACK, and there is considerable room for recoveryTo delete tables, of course, use dropYou want to keep the table and delete all the data. If you have nothing to do with the transaction, you can use truncate. Truncate, table, XX, delete the entire table of data, there is no room for recovery, the benefits can be arranged in the table debris, release spaceSo it's better to back up the data firstIf you are tidying up the inner fragments of a table, you can use truncate to catch up with reuse stroage and then import / insert data againFive update dataUpdate data sheetSet field name, 1=, new assignment, field name, 2=, new assignment,...Where conditionUpdate scott.empSet, empno=8888, ename='TOM', hiredate='03-9, -2002'Where empno = 7566;Update scott.empSet sal=(select, sal+300, from, scott.emp, where, empno = 8099)Where empno=8099;Decode (condition, value 1, translation value 1, value 2, translation value 2, value n, translation value n, default value)Six data export1 export the database TEST completely, export the user name system password manager to D:\daochu.dmpExpsystem/manager@TESTfile=d:\daochu.dmp full=y2 export the system user in the database to the table of the sys userExpsystem/manager@TESTfile=d:\daochu.dmp only wner= (system, Sys)3 export tables table1 and table2 from the databaseExpsystem/manager@TESTfile=d:\daochu.dmp tables= (table1, table2)4 export the field filed1 in the table table1 in the database to data that starts with "00"Expsystem/manager@TESTfile=d:\daochu.dmp tables= (table1) query=\ "where filed1 like'00%'\""Explmis_wh/lmis@lmisbuffer=10000 only WNER=lmis_wh rows=nfile=d:\lmis_wh_nodata.dmp log=d:\lmis_wh_nodata.logImplmis/lmis@lmisbuffer=10000, fromuser=lmis_wh, touser=lmis, file=d:\lmis_wh_nodata.dmp, log=d:\lmis_wh_nodata.logC:\>implmis/lmis@lmisbuffer=50000000, full=n,file=e:\daochu.dmp, ignore=y, rows=yCommit=y compile=y fromuser=lmis_wh touser=lmisSeven data import1 import data from the D:\daochu.dmp into the TEST database.Impsystem/manager@TEST file=d:\daochu.dmpThere may be a problem with it because some tables already exist, and then it is reported wrong, and the table is not imported.It's OK to add ignore=y at the back.2 import table table1 from d:\daochu.dmpImpsystem/manager@TEST file=d:\daochu.dmp tables= (table1)SQL definition: SQL is a database oriented general data processing language specification, can complete the following functions: data extraction query, insert modify delete data generation, modify and delete database objects, database security, database integrity control and data protection.SQL classification:DDL - Data Definition Language (CREATE, ALTER, DROP, DECLARE)DML - Data Manipulation Language (SELECT, DELETE, UPDATE, INSERT)DCL - data control language (GRANT, REVOKE, COMMIT, ROLLBACK) DDL - database definition language: direct submission. CREATE: used to create database objects.DECLARE: in addition to creating temporary tables that are used only during the process, the DECLARE statements are very similar to the CREATE statements. The only object that can be declared is the table. And must be added to the user temporary tablespace.DROP: you can delete any object created with CREATE (database object) and DECLARE (table).ALTER: allows you to modify information about certain databaseobjects. Cannot modify index.Eight, the following is mainly based on object presentation of basic grammar1, database:Create database: CREATE, DATABASE, database-name, [USING, CODESET, codeset, TERRITORY, territory]Note: the code page problem.Delete database: drop, database, dbname2, table:Create new table:Create, table, tabname (col1, Type1, [not, null], [primary, key], col2, type2, [not, null],...)Create a new table based on the existing table:A:create, table, tab_new, like, tab_oldB:create, table, tab_new, as, select, col1, col2... From tab_old definition onlyModify table:Add a column:Alter, table, tabname, add, column, col, typeNote: column added will not be deleted. The DB2 column after the data type does not change, the only change is to increase the size of the varchar. Add primary key:Alter, table, tabname, add, primary, key (Col)Delete Primary key:Alter, table, tabname, drop, primary, key (Col)Delete table: drop, table, tabnameAlter table BMDOC_LIUFDrop constraint PK1_BMDOC cascade;3, table space:Create table spaces: create, tablespace, tbsname, PageSize, 4K, managed, by, database, using (file, file, size)Adding containers to tablespace: alter, tablespace, tablespace_name, add (file,'filename', size)Note: the operation is irreversible and will not be removed after adding the container. Therefore, when it is added, pay attention to it.Delete tablespace: drop, tablespace, tbsname4, index:Create indexes: create, [unique], index, idxname, on, tabname (col... ).Delete index: drop, index, idxnameNote: the index is not modifiable. If you want to change it, you must delete it.5 views:Create views: create, view, VIEWNAME, as, select, statementDelete view: drop view VIEWNAMENote: the only change to the view is the reference type column, which changes the range of columns. None of the other definitions can be modified. The view becomes invalid when the view is based on the base table drop.DML - the database manipulation language, which does not implicitly submit the current transaction and is committed to the setting of the visual environment.SELECT: querying data from tablesNote: the connection in the condition avoids Cartesian productDELETE: delete data from existing tablesUPDATE: update data for existing tablesINSERT: insert data into existing tablesNote: whether DELETE, UPDATE, and INSERT are submitted directly depends on the environment in which the statement is executed.Pay attention to the full transaction log when executing.2, DELETE: delete records from the tableSyntax format:DELETE, FROM, tablename, WHERE (conditions)3, INSERT: insert a record into the tableSyntax format:INSERT INTO tablename (col1, col2),... ) VALUES (value1, Value2),... );INSERT INTO tablename (col1, col2),... ) VALUES (value1, Value2),... ) (value1, value2,... ),......Insert does not wait for any program and does not cause locking4, UPDATE:Syntax format:UPDATE tabname SET (col1=values1, col2=values2),... (WHERE) (conditions);Note: update is slower and requires indexing on the corresponding column.Nine permissionsDCL - Data Control LanguageGRANT - Grant user permissionsREVOKE - revoke user rightsCOMMIT - commit transactions can permanently modify the databaseROLLBACK - rollback transactions, eliminating all changes made after the last COMMIT command, so that the contents of the database are restored to the state after the last COMMIT execution.1, GRANT: all or administrators assign access rights to other usersSyntax format:Grant [all privileges|privileges,... On tabname VIEWNAME to [public|user |,... .2 and REVOKE: cancel one of the user's access rightsSyntax format:Revoke [all privileges|privileges,... On tabname VIEWNAME from [public|user |,... .Note: any permissions of users of instance level cannot be canceled. They are not authorized by grant, but are permissions implemented by groups.3, COMMIT: permanently records changes made in the transaction to the database.Syntax format:Commit [work]4, ROLLBACK: will undo all changes made since the last submission.Syntax format:Rollback [work]Ten advanced SQL brief introductionFirst, the query between the use of computing wordsA:UNION operatorThe UNION operator derives a result table by combining two other result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table.When ALL is used with UNION (that is, UNION ALL), the duplicate rows are not eliminated. In the two case, each row of the derived table does not come from TABLE1, or from TABLE2.B:EXCEPT operatorThe EXCEPT operator derives a result table by including all rows in TABLE1, but not in TABLE2, and eliminating all duplicate rows. When ALL is used with EXCEPT (EXCEPT ALL), the duplicate rows are not eliminated.C:INTERSECT operatorThe INTERSECT operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), the duplicate rows are not eliminated.Note: several query results lines using arithmetic words must be consistent.Appendix: introduction to commonly used functions1, type conversion function:Converted to a numeric type:Decimal, double, Integer, smallint, realHex (ARG): 16 hexadecimal representation converted into parameters.Converted to string type:Char, varcharDigits (ARG): returns the string representation of Arg, and Arg must be decimal.Converted to date or time:Date, time, timestamp2, time and date:Year, quarter, month, week, day, hour, minute, secondDayofyear (ARG): returns the daily value of Arg in the yearDayofweek (ARG): returns the daily value of Arg in the weekDays (ARG): the integer representation of the return date, the number of days from the 0001-01-01.Midnight_seconds (ARG): the number of seconds between midnight and arg.Monthname (ARG): returns the month name of arg.Dayname (ARG): the week that returns arg.3 string function:Length, lcase, ucase, ltrim, rtrimCoalesce (arg1, arg2)... ) returns the first non null parameter in the argument set.Concat (arg1, arg2): connect two strings, arg1 and arg2.Insert (arg1, POS, size, arg2): returns a arg1 that removes size characters from POS and inserts arg2 into that location.Left (Arg, length): returns the leftmost length string of arg.Locate (arg1, arg2, &lt, pos>): find the location of the first occurrence of arg1 in arg2, specify POS, and start looking for the location of the arg1 from the POS of arg2.Posstr (arg1, arg2): returns the position where arg2 first appeared in arg1.Repeat (arg1, num_times): returns the string arg1 repeated num_times times.Replace (arg1, arg2, ARG3): replace all arg2 in arg1 to arg3.Right (Arg, length): returns a string consisting of lengthbytes on the left of the arg.Space (ARG): returns a string containing Arg spaces.Substr (arg1, POS, &lt, length>): returns the length character at the start of the POS position in arg1, and returns the remaining characters if no length is specified.4. Mathematical function:Abs, count, Max, min, sumCeil (ARG): returns the smallest integer greater than or equal to arg.Floor (ARG): returns the smallest integer less than or equal to the parameter.Mod (arg1,Arg2) returns arg1 by the remainder of the arg2, with the same symbol as arg1.Rand (): returns a random number between 1 and 1.Power (arg1, arg2): returns the arg2 power of arg1.Round (arg1, arg2): four, five into the truncated processing, arg2 is the number of bits, if arg2 is negative, then the number of decimal points before four to five processing.Sigh (ARG): symbolic designator to return arg. -1,0,1 representation.Truncate (arg1, arg2): truncate arg1, arg2 is the number of digits, and if arg2 is negative, keep the arg2 bits before the arg1 decimal point.5, others:Nullif (arg1, arg2): returns if the 2 arguments are equal, otherwise the argument 1 is returned。

50个常用sql语句实例(学生表_课程表_成绩表_教师表)

Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,C#,score) 成绩表Teacher(T#,Tname) 教师表create table Student(S# varchar(20),Sname varchar(10),Sage int,Ssex varchar(2))前面加一列序号：ifexists(select table_name from information_schema.tableswhere table_name='Temp_Table')drop table Temp_Tablegoselect 排名=identity(int,1,1),* INTO Temp_Table from Studentgoselect * from Temp_Tablegodrop database [ ] --删除空的没有名字的数据库问题：1、查询“”课程比“”课程成绩高的所有学生的学号；select a.S# from (select s#,score from SC where C#='001') a,(select s#,scorefrom SC where C#='002') bwhere a.score>b.score and a.s#=b.s#;2、查询平均成绩大于分的同学的学号和平均成绩；select S#,avg(score)from scgroup by S# having avg(score) >60;3、查询所有同学的学号、姓名、选课数、总成绩；select Student.S#,Student.Sname,count(SC.C#),sum(score)from Student left Outer join SC on Student.S#=SC.S#group by Student.S#,Sname4、查询姓“李”的老师的个数；select count(distinct(Tname))from Teacherwhere Tname like '李%';5、查询没学过“叶平”老师课的同学的学号、姓名；select Student.S#,Student.Snamefrom Studentwhere S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');6、查询学过“”并且也学过编号“”课程的同学的学号、姓名；select Student.S#,Student.Sname from Student,SC whereStudent.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；select S#,Snamefrom Studentwhere S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher whereTeacher.T#=Course.T# and Tname='叶平'));8、查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名；Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;9、查询所有课程成绩小于分的同学的学号、姓名；select S#,Snamefrom Studentwhere S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);10、查询没有学全所有课的同学的学号、姓名；select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);11、查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名；select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';12、查询至少学过学号为“”同学所有一门课的其他同学学号和姓名；select distinct SC.S#,Snamefrom Student,SCwhere Student.S#=SC.S# and C# in (select C# from SC where S#='001');13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；update SC set score=(select avg(SC_2.score)from SC SC_2where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');14、查询和“”号的同学学习的课程完全相同的其他同学学号和姓名；select S# from SC where C# in (select C# from SC where S#='1002') group by S# having count(*)=(select count(*) from SC whereS#='1002');15、删除学习“叶平”老师课的SC表记录；Delect SCfrom course ,Teacherwhere Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“”课程的同学学号、、号课的平均成绩；Insert SC select S#,'002',(Select avg(score)from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分SELECT S# as 学生ID,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩FROM SC AS tGROUP BY S#ORDER BY avg(t.score)18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分FROM SC L ,SC AS RWHERE L.C# = R.C# andL.score = (SELECT MAX(IL.score)FROM SC AS IL,Student AS IMWHERE L.C# = IL.C# and IM.S#=IL.S#GROUP BY IL.C#)ANDR.Score = (SELECT MIN(IR.score)FROM SC AS IRWHERE R.C# = IR.C#GROUP BY IR.C#);19、按各科平均成绩从低到高和及格率的百分数从高到低顺序SELECT t.C# AS 课程号,max(ame)AS 课程名,isnull(AVG(score),0) AS 平均成绩,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数FROM SC T,Coursewhere t.C#=course.C#GROUP BY t.C#ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（），马克思（），OO&UML （），数据库（）SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数FROM SC21、查询不同老师所教不同课程平均分从高到低显示SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(ame) AS 课程名称,AVG(Score) AS 平均成绩FROM SC AS T,Course AS C ,Teacher AS Zwhere T.C#=C.C# and C.T#=Z.T#GROUP BY C.C#ORDER BY AVG(Score) DESC22、查询如下课程成绩第名到第名的学生成绩单：企业管理（），马克思（），UML （），数据库（）[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩SELECT DISTINCT top 3SC.S# As 学生学号,Student.Sname AS 学生姓名,T1.score AS 企业管理,T2.score AS 马克思,T3.score AS UML,T4.score AS 数据库,ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分FROM Student,SC LEFT JOIN SC AS T1ON SC.S# = T1.S# AND T1.C# = '001'LEFT JOIN SC AS T2ON SC.S# = T2.S# AND T2.C# = '002'LEFT JOIN SC AS T3ON SC.S# = T3.S# AND T3.C# = '003'LEFT JOIN SC AS T4ON SC.S# = T4.S# AND T4.C# = '004'WHERE student.S#=SC.S# andISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)NOT IN(SELECTDISTINCTTOP 15 WITH TIESISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)FROM scLEFT JOIN sc AS T1ON sc.S# = T1.S# AND T1.C# = 'k1'LEFT JOIN sc AS T2ON sc.S# = T2.S# AND T2.C# = 'k2'LEFT JOIN sc AS T3ON sc.S# = T3.S# AND T3.C# = 'k3'LEFT JOIN sc AS T4ON sc.S# = T4.S# AND T4.C# = 'k4'ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) +ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]SELECT SC.C# as 课程ID, Cname as 课程名称,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85],SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70],SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60],SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -] FROM SC,Coursewhere SC.C#=Course.C#GROUP BY SC.C#,Cname;24、查询学生平均成绩及其名次SELECT 1+(SELECT COUNT( distinct 平均成绩)FROM (SELECT S#,AVG(score) AS 平均成绩FROM SCGROUP BY S#) AS T1WHERE 平均成绩> T2.平均成绩) as 名次,S# as 学生学号,平均成绩FROM (SELECT S#,AVG(score) 平均成绩FROM SCGROUP BY S#) AS T2ORDER BY 平均成绩desc;25、查询各科成绩前三名的记录:(不考虑成绩并列情况)SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#;26、查询每门课程被选修的学生数select c#,count(S#) from sc group by C#;27、查询出只选修了一门课程的全部学生的学号和姓名select SC.S#,Student.Sname,count(C#) AS 选课数from SC ,Studentwhere SC.S#=Student.S# group by SC.S# ,Student.Sname havingcount(C#)=1;28、查询男生、女生人数Select count(Ssex) as 男生人数from Student group by Ssex having Ssex='男';Select count(Ssex) as 女生人数from Student group by Ssex having Ssex='女'；29、查询姓“张”的学生名单SELECT Sname FROM Student WHERE Sname like '张%';30、查询同名同性学生名单，并统计同名人数select Sname,count(*) from Student group by Sname having count(*)>1;;31、年出生的学生名单(注：Student表中Sage列的类型是datetime)select Sname, CONVERT(char (11),DATEPART(year,Sage)) as agefrom studentwhere CONVERT(char(11),DATEPART(year,Sage))='1981';32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;33、查询平均成绩大于的所有学生的学号、姓名和平均成绩select Sname,SC.S# ,avg(score)from Student,SCwhere Student.S#=SC.S# group by SC.S#,Sname havingavg(score)>85;34、查询课程名称为“数据库”，且分数低于的学生姓名和分数Select Sname,isnull(score,0)from Student,SC,Coursewhere SC.S#=Student.S# and SC.C#=Course.C# and ame='数据库'and score <60;35、查询所有学生的选课情况；SELECT SC.S#,SC.C#,Sname,CnameFROM SC,Student,Coursewhere SC.S#=Student.S# and SC.C#=Course.C# ;36、查询任何一门课程成绩在分以上的姓名、课程名称和分数；SELECT distinct student.S#,student.Sname,SC.C#,SC.scoreFROM student,ScWHERE SC.score>=70 AND SC.S#=student.S#;37、查询不及格的课程，并按课程号从大到小排列select c# from sc where scor e <60 order by C# ;38、查询课程编号为且课程成绩在分以上的学生的学号和姓名；select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';39、求选了课程的学生人数select count(*) from sc;40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩select Student.Sname,scorefrom Student,SC,Course C,Teacherwhere Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC whereC#=C.C# );41、查询各个课程及相应的选修人数select count(*) from sc group by C#;42、查询不同课程成绩相同的学生的学号、课程号、学生成绩select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;43、查询每门功成绩最好的前两名SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#;44、统计每门课程的学生选修人数（超过人的课程才统计）。

经典SQL语句大全(超全)

一、基础1、说明：创建数据库CREATE DATABASE database-name2、说明：删除数据库drop database dbname3、说明：备份sql server--- 创建备份数据的 deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1. dat'--- 开始备份BACKUP DATABASE pubs TO testBack4、说明：创建新表create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根据已有的表创建新表：A：create table tab_new like tab_old (使用旧表创建新表)B：create table tab_new as select col1,col2… from tab_old definition only5、说明：删除新表drop table tabname6、说明：增加一个列Alter table tabname add column col type注：列增加后将不能删除。

DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：添加主键：Alter table tabname add primary key(col)说明：删除主键： Alter table tabname drop primary key(col)8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：create view viewname as select statement删除视图：drop view viewname10、说明：几个简单的基本的sql语句选择：select * from table1 where 范围插入：insert into table1(field1,field2) values(value1,value2)删除：delete from table1 where 范围更新：update table1 set field1=value1 where 范围查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!排序：select * from table1 order by field1,field2 [desc]总数：select count as totalcount from table1求和：select sum(field1) as sumvalue from table1平均：select avg(field1) as avgvalue from table1最大：select max(field1) as maxvalue from table1最小：select min(field1) as minvalue from table111、说明：几个高级查询运算词A：UNION 运算符UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。

常用的SQL语句

常用的SQL语句下面列举了一些我们在开发中常常会使用到的SQL语句，供大家参考学习。

1. 查询所有数据：SELECT * FROM table_name;2. 查询指定列数据：SELECT column1, column2 FROM table_name;3. 带条件查询：SELECT * FROM table_name WHERE condition;4. 带条件查询并排序：SELECT * FROM table_name WHERE condition ORDER BY column_name ASC/DESC;5. 带分组的查询：SELECT column1, COUNT(*) FROM table_name GROUP BY column1;6. 带分组和排序的查询：SELECT column1, COUNT(*) FROM table_name GROUP BY column1 ORDER BY COUNT(*) DESC;7. 带聚合函数的查询：SELECT AVG(column1), MAX(column2), MIN(column3) FROM table_name;8. 带子查询的查询：SELECT * FROM table_name WHERE column1 IN (SELECT column1 FROM other_table);9. 带连接条件的查询：SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2;10. 带连接和排序条件的查询：SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2 ORDER BY table1.column2 ASC;11. 带连接和分组条件的查询：SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2 GROUP BY table1.column2;12. 带连接和聚合函数条件的查询：SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2 GROUP BY table1.column2 HAVING AVG(table2.column3) > 0;13. 插入数据：INSERT INTO table_name (column1, column2) VALUES (value1, value2);14. 更新数据：UPDATE table_name SET column1 = value1 WHERE condition;15. 删除数据：DELETE FROM table_name WHERE condition;16. 清空表数据：TRUNCATE TABLE table_name;17. 创建表：CREATE TABLE table_name (column1 datatype, column2 datatype, ...);18. 修改表结构：ALTER TABLE table_name ADD column_name datatype;19. 删除表：DROP TABLE table_name;20. 查看表结构：DESCRIBE table_name;21. 查看表数据量：SELECT COUNT(*) FROM table_name;22. 查看表索引：SHOW INDEX FROM table_name;23. 创建索引：CREATE INDEX index_name ON table_name (column_name);24. 删除索引：DROP INDEX index_name ON table_name;25. 批量插入数据：INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ...;26. 批量更新数据：UPDATE table_name SET column1 = value1 WHERE condition, column2 = value2 WHERE condition, ...;27. 批量删除数据：DELETE FROM table_name WHERE condition, ...;28. 分页查询：SELECT * FROM table_name LIMIT offset, limit;29. 子查询嵌套查询：(SELECT column1 FROM other_table WHERE condition) IN (SELECT column1 FROM another_table WHERE condition);30. UNION操作符查询：SELECT * FROM table1 UNION SELECT * FROM table2。

sql经典50题建表语句

sql经典50题建表语句1、题目：创建一个名为"employees"的表，包含"id"、"name"和"salary"三个字段。

sql：CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50),salary DECIMAL(10, 2));2、题目：创建一个名为"orders"的表，包含"order_id"、"customer_id"和"order_date"三个字段。

sql：CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT,order_date DATE);3、题目：创建一个名为"products"的表，包含"product_id"、"product_name"和"price"三个字段。

sql：CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(50),price DECIMAL(10, 2));4、题目：创建一个名为"customers"的表，包含"customer_id"、"first_name"、"last_name"和"email"四个字段。

sql：CREATE TABLE customers (customer_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100));5、题目：创建一个名为"addresses"的表，包含"address_id"、"street"、"city"和"state"四个字段。

sql语句大全（详细）

sql语句大全（详细）sql语句大全（详细）数据库操作1.查看所有数据库show databases;2.查看当前使用的数据库select database();3.创建数据库create databases 数据库名 charset=utf8;4.删除数据库drop database 数据库名5.使用数据句库use database 数据库名6.查看数据库中所有表show tables;表的操作1.查看表结构desc 表名2.创建表结构的语法create table table_name(字段名数据类型可选的约束条件）；demo：创建班级和学生表create table classes(id int unsigned auto_increment primary key not null, name varchar(10));create table students(id int unsigned primary key auto_increment not null, name varchar(20) default '',age tinyint unsigned default 0,height decimal(5,2),gender enum('男','女','人妖','保密'),cls_id int unsigned default 0)3.修改表–添加字段alter table 表名 add 列名类型demo：alter table students add birthday datetime;4.修改表–修改字段–重命名版alert table 表名 change 原名新名类型及约束demo：alter table syudents change birthday birth datetime not null;5.修改表–修改字段–不重命名alter table 表名 modify 列名类型及约束demo : alter table students modify birth date nout noll;6.删除表–删除字段alter table 表名 drop 列名demo :later table students drop birthday;7.删除表drop table 表名demo:drop table students;8.查看表的创建语句–详细过程show create table 表名demo : show create tabele students;查询基本使用1.查询所有列select * from 表名例：select * from classes;2.查询指定列select 列1，列2，...from 表名；例：select id,name from classes;增加说明：主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null) ; 字段默认值 default 来占位，插入成功后以实际数据为准1.全列插入：值的顺序与表结构字段的顺序完全一一对应此时字段名列表不用填写insert into 表名 values (...)例：insert into students values(0,’郭靖',1,'蒙古','2016-1-2');2.部分列插入：值的顺序与给出的列顺序对应此时需要根据实际的数据的特点填写对应字段列表insert into 表名 (列1,...) values(值1,...)例：insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');上面的语句一次可以向表中插入一行数据，还可以一次性插入多行数据，这样可以减少与数据库的通信3.全列多行插入insert into 表名 values(...),(...)...;例：insert into classes values(0,'python1'),(0,'python2');4.部分列多行插入insert into 表名(列1,...) values(值1,...),(值1,...)...;例：insert into students(name) values('杨康'),('杨过'),('小龙女');修改update 表名 set 列1=值1,列2=值2... where 条件例：update students set gender=0,hometown='北京' where id=5;删除delete from 表名 where 条件例：delete from students where id=5;逻辑删除，本质就是修改操作update students set isdelete=1 where id=1;as关键字1.使用 as 给字段起别名select id as 序号, name as 名字, gender as 性别 from students;2.可以通过 as 给表起别名select s.id,,s.gender from students as s;条件语句查询where后面支持多种运算符，进行条件的处理比较运算符逻辑运算符模糊查询范围查询空判断比较运算符等于: =大于: >大于等于: >=小于等于: <=不等于: != 或 <>例1：查询编号大于3的学生select * from students where id > 3;例2：查询编号不大于4的学生select * from students where id <= 4;例3：查询姓名不是“黄蓉”的学生select * from students where name != '黄蓉';例4：查询没被删除的学生select * from students where is_delete=0;逻辑运算符andornot例5：查询编号大于3的女同学select * from students where id > 3 and gender=0;例6：查询编号小于4或没被删除的学生select * from students where id < 4 or is_delete=0;模糊查询like%表示任意多个任意字符_表示一个任意字符例7：查询姓黄的学生select * from students where name like '黄%';例8：查询姓黄并且“名”是一个字的学生select * from students where name like '黄_';例9：查询姓黄或叫靖的学生select * from students where name like '黄%' or name like '%靖';范围查询分为连续范围查询和非连续范围查询in表示在一个非连续的范围内例10：查询编号是1或3或8的学生select * from students where id in(1,3,8);between … and …表示在一个连续的范围内例11：查询编号为3至8的学生select * from students where id between 3 and 8;例12：查询编号是3至8的男生select * from students where (id between 3 and 8) and gender=1;空判断判断为空例13：查询没有填写身高的学生select * from students where height is null;注意： 1. null与’'是不同的 2. is null判非空is not null例14：查询填写了身高的学生select * from students where height is not null;例15：查询填写了身高的男生select * from students where height is not null and gender=1;优先级优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符and比or先运算，如果同时出现并希望先算or，需要结合()使用排序排序查询语法：select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]语法说明:将行数据按照列1进行排序，如果某些行列1 的值相同时，则按照列2 排序，以此类推asc从小到大排列，即升序desc从大到小排序，即降序默认按照列值从小到大排列（即asc关键字）例1：查询未删除男生信息，按学号降序select * from students where gender=1 and is_delete=0 order by id desc;例2：查询未删除学生信息，按名称升序select * from students where is_delete=0 order by name;例3：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时按照身高从高–>矮排序select * from students order by age desc,height desc;分页select * from 表名 limit start=0,count说明从start开始，获取count条数据start默认值为0也就是当用户需要获取数据的前n条的时候可以直接写上xxx limit n;例1：查询前3行男生信息select * from students where gender=1 limit 0,3;关于分页的一个有趣的推导公式已知：每页显示m条数据，当前显示第n页求总页数：此段逻辑后面会在python项目中实现查询总条数p1使用p1除以m得到p2如果整除则p2为总数页如果不整除则p2+1为总页数获取第n页的数据的SQL语句求解思路第n页前有n-1页所在第n页前已经显示的数据的总量是(n-1)*m由于数据的下标从0开始所以第n页前所有的网页的下标是0,1,…,(n-1)*m-1所以第n页的数据起始下标是(n-1)*m获取第n页数据的SQL语句select * from students where is_delete=0 limit (n-1)*m,m注意：在sql语句中limit后不可以直接加公式聚合函数总数count(*) 表示计算总行数，括号中写星与列名，结果是相同的例1：查询学生总数select count(*) from students;最大值max(列) 表示求此列的最大值例2：查询女生的编号最大值select max(id) from students where gender=2;最小值min(列) 表示求此列的最小值例3：查询未删除的学生最小编号select min(id) from students where is_delete=0;求和sum(列) 表示求此列的和例4：查询男生的总年龄select sum(age) from students where gender=1;–平均年龄select sum(age)/count(*) from students where gender=1;平均值avg(列) 表示求此列的平均值例5：查询未删除女生的编号平均值select avg(id) from students where is_delete=0 andgender=2;分组group bygroup by + group_concat()group_concat(字段名)根据分组结果，使用group_concat()来放置每一个分组中某字段的集合group by + 聚合函数通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作group by + havinghaving 条件表达式：用来过滤分组结果having作用和where类似，但having只能用于group by 而where是用来过滤表数据group by + with rollupwith rollup的作用是：在最后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果。

常用sql查询语句大全

常用sql查询语句大全常用SQL查询语句大全SQL (Structured Query Language) 是一种用于管理关系型数据库的编程语言，它可以用来从数据库中检索、插入、更新和删除数据。

以下是一些常用的SQL查询语句：1. SELECT语句：用于检索数据库中的数据。

- SELECT * FROM table_name; -- 检索表中的所有列和行- SELECT column1, column2 FROM table_name; -- 检索表中指定的列- SELECT column1, column2 FROM table_name WHERE condition; -- 检索满足条件的行2. INSERT语句：用于向数据库中插入新的数据。

- INSERT INTO table_name (column1, column2) VALUES (value1, value2); -- 插入指定的列和值- INSERT INTO table_name VALUES (value1, value2); -- 插入所有列和值3. UPDATE语句：用于更新数据库中的数据。

- UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; -- 更新满足条件的行的值4. DELETE语句：用于从数据库中删除数据。

- DELETE FROM table_name WHERE condition; -- 删除满足条件的行5. WHERE子句：用于指定条件。

- SELECT * FROM table_name WHERE column_name = value; -- 检索满足条件的行- SELECT * FROM table_name WHERE column_name LIKE 'value%'; -- 使用通配符进行模糊匹配- SELECT * FROM table_name WHERE column_name IN (value1, value2); -- 检索列值在指定列表中的行6. ORDER BY子句：用于对结果进行排序。

SQL语句大全大全(经典珍藏版)

SQL语句大全--语句功能--数据操作SELECT --从数据库表中检索数据行和列INSERT --向数据库表添加新数据行DELETE --从数据库表中删除数据行UPDATE --更新数据库表中的数据-数据定义CREATE TABLE --创建一个数据库表DROP TABLE --从数据库中删除表ALTER TABLE --修改数据库表结构CREATE VIEW --创建一个视图DROP VIEW --从数据库中删除视图CREATE INDEX --为数据库表创建一个索引DROP INDEX --从数据库中删除索引CREATE PROCEDURE --创建一个存储过程DROP PROCEDURE --从数据库中删除存储过程CREATE TRIGGER --创建一个触发器DROP TRIGGER --从数据库中删除触发器CREATE SCHEMA --向数据库添加一个新模式DROP SCHEMA --从数据库中删除一个模式CREATE DOMAIN --创建一个数据值域ALTER DOMAIN --改变域定义DROP DOMAIN --从数据库中删除一个域--数据控制GRANT --授予用户访问权限DENY --拒绝用户访问REVOKE --解除用户访问权限--事务控制COMMIT --结束当前事务ROLLBACK --中止当前事务SET TRANSACTION --定义当前事务数据访问特征--程序化SQLDECLARE --为查询设定游标EXPLAN --为查询描述数据访问计划OPEN --检索查询结果打开一个游标FETCH --检索一行查询结果CLOSE --关闭游标PREPARE --为动态执行准备SQL 语句EXECUTE --动态地执行SQL 语句DESCRIBE --描述准备好的查询---局部变量declare @id char(10)--set @id = '10010001'select @id = '10010001'---全局变量---必须以@@开头--IF ELSEdeclare @x int @y int @z intselect @x = 1 @y = 2 @z=3if @x > @yprint 'x > y' --打印字符串'x > y'else if @y > @zprint 'y > z'else print 'z > y'--CASEuse panguupdate employeeset e_wage =casewhen job_level = ‟1‟ then e_wage*1.08 when job_level = ‟2‟ then e_wage*1.07 when job_level = ‟3‟ then e_wage*1.06 else e_wage*1.05end--WHILE CONTINUE BREAK declare @x int @y int @c intselect @x = 1 @y=1while @x < 3beginprint @x --打印变量x 的值while @y < 3beginselect @c = 100*@x + @yprint @c --打印变量c 的值select @y = @y + 1endselect @x = @x + 1select @y = 1end--WAITFOR--例等待1 小时2 分零3 秒后才执行SELECT 语句wai tfor delay ‟01:02:03‟select * from employee--例等到晚上11 点零8 分后才执行SELECT 语句waitfor time ‟23:08:00‟select * from employee***SELECT***select *(列名) from table_name(表名) where column_name operator value ex:(宿主)select * from stock_information where stockid = str(nid)stockname = 'str_name'stockname like '% find this %'stockname like '[a-zA-Z]%' --------- ([]指定值的范围)stockname like '[^F-M]%' --------- (^排除指定范围)--------- 只能在使用like关键字的where子句中使用通配符)or stockpath = 'stock_path'or stocknumber < 1000and stockindex = 24not stock*** = 'man'stocknumber between 20 and 100stocknumber in(10,20,30)order by stockid desc(asc) --------- 排序，desc-降序，asc-升序order by 1,2 --------- by列号stockname = (select stockname from stock_information where stockid = 4)--------- 子查询--------- 除非能确保内层select只返回一个行的值，--------- 否则应在外层where子句中用一个in限定符select distinct column_name form table_name --------- distinct指定检索独有的列值，不重复select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name select stockname , "stocknumber" = count(*) from table_name group by stockname--------- group by 将表按行分组,指定列中有相同的值having count(*) = 2 --------- having选定指定的组select *from table1, table2where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示table1.id =* table2.id -------- 右外部连接select stockname from table1union [all] ----- union合并查询结果集，all-保留重复行select stockname from table2***insert***insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")value (select Stockname , Stocknumber from Stock_table2)---value为select语句***update***update table_name set Stockname = "xxx" [where Stockid = 3]Stockname = defaultStockname = nullStocknumber = Stockname + 4***delete***delete from table_name where Stockid = 3truncate table_name ----------- 删除表中所有行，仍保持表的完整性drop table table_name --------------- 完全删除表***alter table*** --- 修改数据库表结构alter table database.owner.table_name add column_name char(2) null .....sp_help table_name ---- 显示表已有特征create table table_name (name char(20), age smallint, lname varchar(30))insert into table_name select ......... ----- 实现删除列的方法（创建新表）alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束***function(/*常用函数*/)***----统计函数----AVG --求平均值COUNT --统计数目MAX --求最大值MIN --求最小值SUM --求和--AVGuse panguselect avg(e_wage) as dept_avgWagefrom employeegroup by dept_id--MAX--求工资最高的员工姓名use panguselect e_namefrom employeewhere e_wage =(select max(e_wage)from employee)--STDEV()--STDEV()函数返回表达式中所有数据的标准差--STDEVP()--STDEVP()函数返回总体标准差--VAR()--VAR()函数返回表达式中所有值的统计变异数--VARP()--VARP()函数返回总体变异数----算术函数----/***三角函数***/SIN(float_expression) --返回以弧度表示的角的正弦COS(float_expression) --返回以弧度表示的角的余弦TAN(float_expression) --返回以弧度表示的角的正切COT(float_expression) --返回以弧度表示的角的余切/***反三角函数***/ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角ATAN2(float_expression1,float_expression2)--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角DEGREES(numeric_expression)--把弧度转换为角度返回与表达式相同的数据类型可为--INTEGER/MONEY/REAL/FLOAT 类型RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为--INTEGER/MONEY/REAL/FLOAT 类型EXP(float_expression) --返回表达式的指数值LOG(float_expression) --返回表达式的自然对数值LOG10(float_expression)--返回表达式的以10 为底的对数值SQRT(float_expression) --返回表达式的平方根/***取近似值函数***/CEILING(numeric_expression) --返回>=表达式的最小整数返回的数据类型与表达式相同可为--INTEGER/MONEY/REAL/FLOAT 类型FLOOR(numeric_expression) --返回<=表达式的最小整数返回的数据类型与表达式相同可为--INTEGER/MONEY/REAL/FLOAT 类型ROUND(numeric_expression) --返回以integer_expression 为精度的四舍五入值返回的数据--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型ABS(numeric_expression) --返回表达式的绝对值返回的数据类型与表达式相同可为--INTEGER/MONEY/REAL/FLOAT 类型SIGN(numeric_expression) --测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型--与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型PI() --返回值为π 即3.1415926535897936RAND([integer_expression]) --用任选的[integer_expression]做种子值得出0-1 间的随机浮点数----字符串函数----ASCII() --函数返回字符表达式最左端字符的ASCII 码值CHAR() --函数用于将ASCII 码转换为字符--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值LOWER() --函数把字符串全部转换为小写UPPER() --函数把字符串全部转换为大写STR() --函数把数值型数据转换为字符型数据LTRIM() --函数把字符串头部的空格去掉RTRIM() --函数把字符串尾部的空格去掉LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串CHARINDEX(),PATINDEX() --函数返回字符串中某个指定的子串出现的开始位置SOUNDEX() --函数返回一个四位字符码--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值DIFFERENCE() --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异--0 两个SOUNDEX 函数返回值的第一个字符不同--1 两个SOUNDEX 函数返回值的第一个字符相同--2 两个SOUNDEX 函数返回值的第一二个字符相同--3 两个SOUNDEX 函数返回值的第一二三个字符相同--4 两个SOUNDEX 函数返回值完全相同QUOTENAME() --函数返回被特定字符括起来的字符串/*select quotename('abc', '{') quotename('abc')运行结果如下----------------------------------{{abc} [abc]*/REPLICATE() --函数返回一个重复character_expression 指定次数的字符串/*select replicate('abc', 3) replicate( 'abc', -2)运行结果如下----------- -----------abcabcabc NULL*/REVERSE() --函数将指定的字符串的字符排列顺序颠倒REPLACE() --函数返回被替换了指定子串的字符串/*select replace('abc123g', '123', 'def')运行结果如下----------- -----------abcdefg*/SPACE() --函数返回一个有指定长度的空白字符串STUFF() --函数用另一子串替换字符串指定位置长度的子串----数据类型转换函数----CAST() 函数语法如下CAST() (<expression> AS <data_ type>[ length ])CONVERT() 函数语法如下CONVERT() (<data_ type>[ length ], <expression> [, style])select cast(100+99 as char) convert(varchar(12), getdate())运行结果如下------------------------------ ------------199 Jan 15 2000----日期函数----DAY() --函数返回date_expression 中的日期值MONTH() --函数返回date_expression 中的月份值YEAR() --函数返回date_expression 中的年份值DATEADD(<datepart> ,<number> ,<date>)--函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期DATEDIFF(<datepart> ,<number> ,<date>)--函数返回两个指定日期在datepart 方面的不同之处DATENAME(<datepart> , <date>) --函数以字符串的形式返回日期的指定部分DATEPART(<datepart> , <date>) --函数以整数值的形式返回日期的指定部分GETDATE() --函数以DATETIME 的缺省格式返回系统当前的日期和时间----系统函数----APP_NAME() --函数返回当前执行的应用程序的名称COALESCE() --函数返回众多表达式中第一个非NULL 表达式的值COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值COL_NAME(<table_id>, <column_id>) --函数返回表中指定字段的名称即列名DATALENGTH() --函数返回数据表达式的数据的实际长度DB_ID(['database_name']) --函数返回数据库的编号DB_NAME(database_id) --函数返回数据库的名称HOST_ID() --函数返回服务器端计算机的名称HOST_NAME() --函数返回服务器端计算机的名称IDENTITY(<data_type>[, seed increment]) [AS column_name])--IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中/*select identity(int, 1, 1) as column_nameinto newtablefrom oldtable*/ISDATE() --函数判断所给定的表达式是否为合理日期ISNULL(<check_expression>, <replacement_value>) --函数将表达式中的NULL 值用指定值替换ISNUMERIC() --函数判断所给定的表达式是否为合理的数值NEWID() --函数返回一个UNIQUEIDENTIFIER 类型的数值NULLIF(<expression1>, <expression2>)--NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值sql中的保留字action add aggregate allalter after and asasc avg avg_row_length auto_incrementbetween bigint bit binaryblob bool both bycascade case char characterchange check checksum columncolumns comment constraint createcross current_date current_time current_timestampdata database databases datedatetime day day_hour day_minuteday_second dayofmonth dayofweek dayofyeardec decimal default delayeddelay_key_write delete desc describedistinct distinctrow double dropend else escape escapedenclosed enum explain existsfields file first floatfloat4 float8 flush foreignfrom for full functionglobal grant grants grouphaving heap high_priority hourhour_minute hour_second hosts identifiedignore in index infileinner insert insert_id intinteger interval int1 int2int3 int4 int8 intoif is isam joinkey keys kill last_insert_idleading left length likelines limit load locallock logs long longbloblongtext low_priority max max_rowsmatch mediumblob mediumtext mediumintmiddleint min_rows minute minute_secondmodify month monthname myisamnatural numeric no notnull on optimize optionoptionally or order outeroutfile pack_keys partial passwordprecision primary procedure processprocesslist privileges read realreferences reload regexp renamereplace restrict returns revokerlike row rows secondselect set show shutdownsmallint soname sql_big_tables sql_big_selectssql_low_priority_updates sql_log_off sql_log_update sql_select_limit sql_small_result sql_big_result sql_warnings straight_joinstarting status string tabletables temporary terminated textthen time timestamp tinyblobtinytext tinyint trailing totype use using uniqueunlock unsigned update usagevalues varchar variables varyingvarbinary with write whenwhere year year_month zerofill查看全文常用SQL命令和ASP编程在进行数据库操作时，无非就是添加、删除、修改，这得设计到一些常用的SQL 语句，如下：SQL常用命令使用方法：(1) 数据记录筛选：sql="select * from 数据表where 字段名=字段值order by 字段名[desc]"sql="select * from 数据表where 字段名like %字段值% order by 字段名[desc]"sql="select top 10 * from 数据表where 字段名order by 字段名[desc]"sql="select * from 数据表where 字段名in (值1,值2,值3)"sql="select * from 数据表where 字段名between 值1 and 值2"(2) 更新数据记录：sql="update 数据表set 字段名=字段值where 条件表达式"sql="update 数据表set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式"(3) 删除数据记录：sql="delete from 数据表where 条件表达式"sql="delete from 数据表" (将数据表所有记录删除)(4) 添加数据记录：sql="insert into 数据表(字段1,字段2,字段3 …) valuess (值1,值2,值3 …)"sql="insert into 目标数据表select * from 源数据表" (把源数据表的记录添加到目标数据表)(5) 数据记录统计函数：AVG(字段名) 得出一个表格栏平均值COUNT(*|字段名) 对数据行数的统计或对某一栏有值的数据行数统计MAX(字段名) 取得一个表格栏最大的值MIN(字段名) 取得一个表格栏最小的值SUM(字段名) 把数据栏的值相加引用以上函数的方法：sql="select sum(字段名) as 别名from 数据表where 条件表达式"set rs=conn.excute(sql)用rs("别名") 获取统的计值，其它函数运用同上。

sql查询语句大全及实例

sql查询语句大全及实例1.提取查询（SELECT）SELECT 语句用于从数据库表中提取数据：实例：从"Persons"表中提取数据：SELECT * FROM Persons3.排序（ORDER BY）ORDER BY 语句用于在查询结果中对取出的数据排序：实例：依据金额（Amount）和日期（OrderDate）字段，对"Orders"表中的记录进行从高到低（desc）排序：SELECT * FROM Orders ORDER BY Amount DESC, OrderDate4.过滤（WHERE）WHERE 子句通常用于在 SELECT、UPDATE 和 DELETE 语句中来过滤选择的数据：5.上下文过滤（HAVING）HAVING 子句仅对聚合函数（AVG，COUNT，SUM 等）出现的结果进行过滤：实例：从"Orders"表中选择超过 5000 的订单：SELECT * FROM OrdersHAVING SUM(Amount) > 50006.插入（INSERT）INSERT 语句用于向数据库插入新记录：实例：向"Persons"表中插入一条新记录：INSERT INTO Persons (firstname, lastname, age, address, city)VALUES ('Glenn', 'Quagmire', 33, 'Coolsville', 'Anchorage');7.更新（UPDATE）UPDATE 语句用于更新数据库中的记录：9.创建数据库（CREATE DATABASE）CREATE DATABASE 语句用于创建新的数据库：实例：创建新的数据库“MyDataBase”：CREATE DATABASE MyDataBase实例：在“MyDataBase”中创建一个新表“Persons”：CREATE TABLE Persons(PersonID int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255))。

超全SQL语句 ,含详细备注示例

从命令行连接到SQLPLUS：Sqlplus sys/密码as sysdbaAlter user scott account unlock;用户已更改。

conn scott/tiger已连接。

SQL单条语句：SQL>desc emp(表名); 描述这张表EMPNO：雇员编号COMM：津贴DNAME：部门名称LOC：部门所在地desc salgrade：描述薪水等级select语句：select * from emp：把这个表中的内容全部取出来select ename, sal * 12,from emp;取出雇员的年薪select 2 * 3 from emp;Desc dual;（dual 代表输出一个字段）select * from dual;select 2 * 3 from dual;只出一个结果select sysdate from dual;取出系统时间select ename, sal * 12 anunal_sal(或者”anunal sal”，加引号是保持原来的格式输出，它代表年薪名) from emp;select ename, comm from emp;输出每个人的津贴select ename, sal * 12 + comm from emp;年薪+津贴select ename || salfrom emp;|| 代表字符连接符select ename || ‘abc’ from emp;’’是把字符串内容加上select ename || ‘ abc‘‘def’ from emp;中间会打印出一个单引号Where是过滤语句：select * from emp where deptno = 10;取出部门编号为10的成员select * from emp where ename = ‘Clark’;=是等值判断select ename, sal from emp where sal > 1500;取出薪水大于1500的成员select ename, sal, deptno from where deptno < >10;取出部门编号不等于10 的部门成员和薪水select ename, sal from emp where ename > ‘cba’;字符串比较select ename,sal from emp where sal between 800 and 1500(where sal >= 800 and sl <= 1500);取出薪水在800到1500之间的成员select ename, sal, comm from emp where comm is null;取出津贴为空的成员select ename, sal, comm from emp where comm is not null;取出津贴不为空的成员select ename, sal, comm from emp where sal in <800, 1500, 2000>;取出薪水等于800,1500,2000的成员select ename, sal, comm from emp where ename in <’sMITH’, ‘KING’, ‘ABC’>;取出这三个人select ename, sa, hiredate from emp where hiredate > ‘20-2月-81’（‘20-2-1981’）;取出入职时间为1981年2月20号的成员select ename, sal from emp where deptno = 10 and sal > 1000;select ename, sal from emp where deptno =10 or sal > 1000;注意两者结果不一样select ename from emp where ename like ‘%ALL%’;取出其中带有ALL字符串的成员名select ename from emp where ename like ‘_A%’;取出第二个字符是A的成员select ename from emp where ename like ‘%\%%’（‘%$%%’）（where ename like ‘%$%%’ escape ‘$’）;告诉它这是转义字符数据排序：desc是降序、asc是升序（不写的话默认是升序）select * from dept order by deptno desc;按照deptno降序排列select empno, ename from emp order by empno asc;按照empno;升序排列//select empno , ename from emp where deptno < > 10 order by sal asc;select ename, sal * 12 annual_sal from emp where ename not like ‘_A%’ and sal > 800 order by sal desc;取出名字中第二个字母不是A且薪水大于800 的成员按降序排列sQL中的函数：select lower <ename> from emp;取出的名字都是小写select ename from emp where lower <ename> like ‘_a%’;先小写名字再取出第二个字母是a的成员select ename from emp where like ‘_a%’ or ename like ‘_A%’;select substr <ename, 2,3> from emp;从第二个开始截取三个名字select chr <65> from dual;把AsCII码转为字符select ascii <’A’> from dual;把A转为AsCII码select round <23.652> from dual;四舍五入输出为24(默认四舍五入到个位)select round <23.652, 2> from dual;保留两位小数，输出23.65select round <23.652, -1> from dual;四舍五入到十位，输出为20select to_char <sal, ‘$99,999.9999’> from emp;把sal转为固定格式，小数点后四位，小数点前五位（小数点后的必写，小数点前的位数不够可以省略）select to_char <sal, ‘L99,999.9999’> from emp;L为转为本地货币符select to_char <sal, ‘L00,000.0000’> from emp;注意：即使小数点前位数不够，也得把0补上select to_char <hiredate, ‘YYYY-MM-DD HH:MI:ss’> from emp;把入职时间改为固定格式select to_char <hiredate, ‘YYYY-MM-DD HH24:MI:ss’> from emp;HH为12进制，HH24为24进制select to_char <sysdate, ‘YYYY-MM-DD HH24:MI:ss’> from dual;select ename, hiredate from emp where hiredate > to_char <’1981-2-20 12:34:56’, ‘YYYY-MM-DD HH24:MI:ss’>;select sal from emp where sal > to_number <’$1,250.00’,’$9,999.00’>;取出薪水大于1250的select ename ,sal * 12 + nvl <comm,0> from emp;如果comm不存在则以0计算，nvl 就是为了避免空值select count <ename> from emp;select count <distinct deptno> from emp;Group by 分组语句：select avg <sal> from emp group by deptno;select deptno, avg <sal> from emp group by deptno;上述两项都是求部门的平均薪水select deptno,job max <sal> from emp group by deptno, job;select ename from emp where sal = < select max <sal>from emp>;取出薪水最高的那个人名select avg <sal>, deptno from emp group by deptno having avg<sal> > 2000;取出平均薪水大于2000的那些组（不能用where，因为它只控制单行输出，having是对分组进行限制）总结语句：select avg<sal> from emp where sal > 1200 group by deptno having avg<sal> > 1500 order by avg<sal> desc;子查询：Select ename, sal from emp where sal = <select max<sal> from emp>; select嵌套Select ename,sal from emp where sal > <select avg<sal> from emp>;//Select ename, sal, deptno from emp where sal in <select max<sal> from emp grup by emptno>;注意这句话有问题表连接：Select ename,sal from empjoin <select max <sal>, deptno from emp group by deptno> tOn <emp.sal = t.max_sal and emp.deptno = t.deptno>;Jion是连接的意思on里面是连接条件自连接：Select e1.ename, e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;老版写法Select e1.ename,e2.ename from emp e1 join emp e2 on <e1.mgr = e2.empno>;新版写法Select e1.ename,e2.ename from emp e1 left join emp e2 on <e1.mgr =e2.empno>;左外连接：注意left的区别，把左边多余的拿出来等值连接：Select ename,dname from emp, dept where emp.deptno = dept.deptno;老版写法Select ensme,dname from emp join dept on <emp.deptno = dept.deptno>;新版写法Select ensme,dname from emp join dept using <deptno>; 和上面的写法一样，但不推荐使用using的用法非等值连接：Select ename, grade from emp e (e代表的是表的别名) join salgrade s on <e.sal between s.losal between and s.hisal>;三个表的连接写法：Select ename, dname, grade from emp e join dept d on <e.deptno = d.deptno> join salgrade s on <e.sal between s.losal and s.hisal> where ename not like ‘_A%’;求部门中哪些人的薪水最高：Select ename ,sal from emp join <select max<sal> max_sal, deptno from emp group by deptno> t on <emp.sal = t.max_sal and emp.deptno = t.deptno>;求部门平均薪水的等级：Select deptno, avg<sal>, grade from <select deptno, avg<sal> avg_sal from empgroup by deptno> t join salgrade s on <t.avg_sal between s.losal and s.hisal>;求出所有部门的每个人的薪水等级：Select deptno, ename,grade from emp join salgrade s on <emp.sal between s.losal and s.hisal>;求部门平均薪水的等级：Select deptno, avg<grade> from <Select deptno, ename,grade from emp join salgrade s on <emp.sal between s.losal and s.hisal>> t group by deptno;雇员中哪些人是经理人：Select ename from emp where empno in <select mgr from emp>;Select ename from emp where empno in <select distinct mgr from emp>;有效率的写法不准用组函数，求薪水的最高值（面试题）：提示：用自连接select distinct sal from emp where sal not in <select distinct (distinct 是去掉重复的项) e1.sal from emp e1.job emp e2 on < e1.sal < e2.sal > >;求平均薪水最高的部门编号：Select deptno, avg_sal from<select avg<sal> avg_sal, deptno from emp group by deptno>where avg_sal =<select max<avg_sal> from<select avg<sal> avg_sal,deptno from emp group by deptno> >;另一种写法：Select deptno, avg_sal from<select avg<sal> avg_sal, deptno from emp group by deptno>where avg_sal =<select max<avg<sal>> from emp group by deptno >;求平均薪水最高的部门称号：Select dname from dept where deptno =<select deptno from<select avg<sal> avg_sal, deptno from emp group by deptno> where avg_sal =<select max<avg_sal> from<select avg<sal> avg_sal,deptno from emp group by deptno> >>;求平均薪水的等级最低的部门名称：Select dname, t1.deptno, grade, avg_sal from(Select deptno, grade, avg_sal from(Select deptno, avg<sal> avg_sal from emp group by deptno) tJoin salgrade s on (t.avg_sal between s.losal and s.hisal)) t1Join dept on (t1.deptno = dept.deptno)Where t1.grade =(Select min(grade) from(Select deptno, grade, avg_sal from(select deptno, avg(sal) avg_sal from emp group by deptno) t Join salgrade s on (t.avg_sal between s.losal and s.hisal)))求比普通员工最高薪水还要高的经理名称：select ename from empWhere empno in <select distinct mgr from emp where mgr is not null> and sal > select max<sal> from empwhere empno not in<select distinct mgr from emp where mgr is not null>求部门经理人中平均薪水最低的部门名称？求薪水最高的前5名雇员？//Select ename,sal from (select ename,sal from emp where empno in (select sal, //deptno from emp order by sal desc)) where rownum <= 5;求薪水最高的第六到第十名雇员？求最后入职的5名雇员？1、查找选了‘黎明’老师课的的姓名2、查有2门课以上不及格的学生姓名和平均成绩select sname,avgsalfrom (select sno,avg(scgrade) avgsalfrom (select s.sno,sname,cno,scgradefrom sc,swhere scgrade<60 and s.sno=sc.sno)group by snohaving count(*)>=2)table1, swhere s.sno=table1.sno3、查选了1和选了2的学生的姓名1、select snamefrom swhere sno not in(select snofrom c,scwhere o=oand c.cteacher='黎明')2、select sname,avgsalfrom (select sno,avg(scgrade) avgsalfrom (select s.sno,sname,cno,scgradefrom sc,swhere scgrade<60 and s.sno=sc.sno )group by snohaving count(*)>=2)table1, swhere s.sno=table1.sno3、select snamefrom s,scwhere s.sno=sc.snoand cno='1'intersectselect snamefrom s,scwhere s.sno=sc.snoand cno='2'1).求部门中哪些人的薪水最高select ename,emp.deptno,salfrom(select max(sal) maxsalary,deptnofrom empgroup by deptno)table1,empwhere emp.sal=table1.maxsalary2).求部门平均薪水的等级select deptno,gradefrom(select avg(sal) avgsal,deptnofrom empgroup by deptno)table1,salgradewhere avgsal between losal and hisal3).雇员中有哪些人是经理人select *from empwhere job='MANAGER'4).不准用组函数，求薪水的最高值（面试题）select salfrom empwhere sal>=all(select salfrom emp)5).求平均薪水最高的部门的部门的编号select deptno, avg(sal) maxsalfrom empgroup by deptnohaving avg(sal)>=all(select avg(sal) from emp group by deptno)6).求平均薪水最高的部门的部门名称select dnamefrom(select deptno, avg(sal) maxsalfrom empgroup by deptnohaving avg(sal)>=all(select avg(sal) from emp group by deptno))table1,deptwhere table1.deptno=dept.deptno7).求平均薪水等级最低的部门名称select dnamefrom deptwhere deptno=(select deptnofrom(select deptno,gradefrom(select avg(sal) avgsal,deptnofrom empgroup by deptno)table1,salgradewhere avgsal between losal and hisal)where grade<=all(select gradefrom(select deptno,gradefrom(select avg(sal) avgsal,deptnofrom empgroup by deptno)table1,salgradewhere avgsal between losal and hisal)) )8).求比普通员工的最高薪水还要高的经理人的名称select enamefrom empwhere job='MANAGER' and sal>=(select max(sal)from emp where job='CLERK')9).求薪水最高的前5名雇员select *from(select emp.*from emporder by sal desc)where rownum<=510.求薪水最高的第6到10名雇员（重点掌握）select *from(select table1.*,rownum rnfrom(select emp.*from emporder by sal desc)table1)where rn<=10 and rn>5比较效率：1、select * from emp where deptno = 10 and ename like ‘%A%’;(理论上效率高，如同短路语句)2、Select * from emp where ename like ‘%A%’ and deptno = 10;Create table stu(Id number(6),Name varchar2(20) constraint stu_name_nn not null,Sex number(1),Age number(3),Sdate date,Grade number(2) default 1,Class number(4),Email varchar2(50),Constraint stu_class_fk foreign key(class) references class(id),//定义外键Constraint stu_id_pk primary key(id),//定义主键Constraint stu_name_email_uni unique(email,name) //email和name必须相同)/Create table class(Id number(4) primary key,Name varchar2(20)not null)Alter table stu add(addr varchar2(100));(在表中加入一个字段)alter table stu modify(addr varchar2(50));（修改）Alter table stu drop(addr);（删除表中的字段）Insert into stu(id,name,email) values (1,’’a,’a’);Alter table stu add constraint stu_class_fk foreign key (class) references class(id);（加约束条件）Alter table stu drop constraint stu_class_fk;（删除约束条件）Select table_name from user_tables;Select conatraint_name from user_constraints;Select view_name from user_views;Select constraint_name,table_name from user_constraints;DML（数据操纵语言）语句：select、insert、delete、update(修改)Distinct:消除重复行Insert into dept values (50,’game’,’bj’);（已插入一行）Rollback;(回退已完成：回到没导入之前的状态)Create table emp2 as select * from emp;(备份一个表，内容和emp一模一样)Insert into dept2 values (50,’game’,’bj’);Insert into dept2(deptno,dname) values (60,’game2’);(增加指定的值)Insert into dept2 select * from dept;(把dept的值全部插入dept2中)Update emp2 set sal = sal*2,ename = ename || ‘-’ where deptno = 10;Select ename,sal from emp2 where deptno = 10;Update emp2 set sal = sal*2；Commit;(提交完成)此时，rollback已不起作用了，不会回到原始状态Delete from emp2;(所有都删掉)Delete from dept2 where deptno = 10;Rollback;DDL语句（数据定义语言）：Create table t (a varchar2(10)); （创建表）Drop table t;（删除表）Alter user scott account unlock;（修改）DCL（数据控制语言）语句：Grant授权语句: grant [权限] on [要授予权限的数据库对象] to [使用者账户名称] with [授权选项]Revoke: deny [权限] on [要授予权限的数据库对象] to [使用者账户名称]常用的权限分配：grant授权：于为用户分配权限或角色GRANT CONNECT TO MARTIN;CONNECT角色允许用户连接至数据库，并创建数据库对象GRANT RESOURCE TO MARTIN;RESOURCE角色允许用户使用数据库中的存储空间GRANT CREATE SEQUENCE TO MARTIN；此系统权限允许用户在当前模式中创建序列，此权限包含在CONNECT角色中授予用户MARTIN 操作TEST表对象的权限允许用户查询TEST 表的记录:GRANT SELECT ON TEST TO MARTIN;允许用户更新TEST 表中的记录:GRANT UPDATE ON TEST TO MARTIN;允许用户插入、删除、更新和查询TEST 表中的记录:GRANT ALL ON TEST TO MARTIN;用新建用户来进行链接，发现权限不足。

sql 语言最常用的语句

sql 语言最常用的语句SQL语言是一种用于管理和操作关系型数据库的标准化语言。

它提供了一系列的命令和语句，用于查询、插入、更新和删除数据。

以下是SQL语言中最常用的十个语句：1. SELECT语句：用于从数据库中查询数据。

可以选择特定的列或所有的列，并可以设置条件来过滤数据。

示例：SELECT * FROM table_name WHERE condition;2. INSERT语句：用于向数据库表中插入新的数据记录。

示例：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);3. UPDATE语句：用于更新数据库表中的数据记录。

可以更新特定的列或所有的列，并可以设置条件来过滤要更新的数据。

示例：UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;4. DELETE语句：用于从数据库表中删除数据记录。

可以设置条件来过滤要删除的数据。

示例：DELETE FROM table_name WHERE condition;5. CREATE TABLE语句：用于创建新的数据库表。

示例：CREATE TABLE table_name (column1 datatype, column2 datatype, ...);6. ALTER TABLE语句：用于修改数据库表的结构，例如添加或删除列。

示例：ALTER TABLE table_name ADD column_name datatype;7. DROP TABLE语句：用于删除数据库表。

示例：DROP TABLE table_name;8. JOIN语句：用于在多个表之间建立关联，并基于关联条件查询数据。

示例：SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name;9. GROUP BY语句：用于对查询结果进行分组，并对每个组进行聚合操作。

SQL的基本查询语句大全

SQL（结构化查询语言）是用于管理关系数据库系统的标准语言。

以下是一些基本的SQL查询语句的示例：1. **选择所有列**```sqlSELECT * FROM 表名;```2. **选择特定列**```sqlSELECT 列名1, 列名2 FROM 表名;```3. **添加条件**```sqlSELECT * FROM 表名WHERE 条件;```4. **排序结果**```sqlSELECT * FROM 表名ORDER BY 列名ASC/DESC;```5. **插入数据**```sqlINSERT INTO 表名(列名1, 列名2, ...) VALUES (值1, 值2, ...);```6. **更新数据**```sqlUPDATE 表名SET 列名1 = 值1, 列名2 = 值2, ... WHERE 条件; ```7. **删除数据**```sqlDELETE FROM 表名WHERE 条件;```8. **连接表**```sqlSELECT * FROM 表名1 JOIN 表名2 ON 表名1.列名= 表名2.列名; ```9. **聚合函数**```sqlSELECT COUNT(列名) FROM 表名WHERE 条件; --计数SELECT SUM(列名) FROM 表名WHERE 条件; --求和SELECT AVG(列名) FROM 表名WHERE 条件; --平均值SELECT MAX(列名) FROM 表名WHERE 条件; --最大值SELECT MIN(列名) FROM 表名WHERE 条件; --最小值```10. **分组和筛选**```sqlSELECT 列名, COUNT(*) FROM 表名GROUP BY 列名HAVING COUNT(*) > 值; --分组和筛选聚合数据```11. **子查询**```sqlSELECT * FROM 表名WHERE 列名IN (SELECT 列名FROM 表名WHERE 条件); -- IN 子查询SELECT * FROM 表名WHERE 列名= (SELECT 列名FROM 表名WHERE 条件); -- = 子查询(通常用于单行子查询)```12. **插入多行数据** (在某些数据库中可能不支持)```sqlINSERT INTO 表名(列名1, 列名2, ...) VALUES (值1a, 值2a, ...), (值1b, 值2b, ...), ...;```请注意，上述SQL语句仅为基本示例，实际使用时可能需要根据具体的数据库系统和需求进行调整。

(word完整版)数据库SQL经典语句(包含几乎所有的经典操作语言)

1、说明:复制表(只复制结构，源表名:a 新表名：b）(Access可用）法一：select ＊ into b from a where 1〈〉1法二：select top 0 * into b from a2、说明：拷贝表（拷贝数据,源表名:a 目标表名：b) (Access可用）insert into b(a, b， c) select d,e，f from b;3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)insert into b(a, b， c) select d，e,f from b in ‘具体数据库' where 条件例子：。

.from b in ’”&Server.MapPath(”。

”)＆”\data.mdb” &"’ where.。

4、说明：子查询(表名1：a 表名2：b）select a，b,c from a where a IN (select d from b ）或者： select a,b，c from a where a IN （1，2,3）5、说明：显示文章、提交人和最后回复时间select a.title，a。

username,b。

adddate from table a，(select max(adddate) adddate from table where table。

title=a。

title) b6、说明：外连接查询（表名1：a 表名2：b)select a.a， a.b, a。

c, b。

c， b.d, b。

f from a LEFT OUT JOIN b ON a.a = b.c7、说明：在线视图查询（表名1:a ）select ＊ from （SELECT a,b,c FROM a) T where t.a 〉 1;8、说明：between的用法，between限制查询数据范围时包括了边界值,not between不包括select ＊ from table1 where time between time1 and time2select a,b,c， from table1 where a not between 数值1 and 数值29、说明：in 的使用方法select * from table1 where a [not] in （‘值1'，’值2',’值4'，’值6’）10、说明：两张关联表，删除主表中已经在副表中没有的信息delete from table1 where not exists （ select ＊ from table2 where table1.field1=table2。

SQL查询语句大全集锦(经典)

SQL查询语句大全集锦MYSQL查询语句大全集锦一、简单查询简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。

它们分别说明所查询列、查询的表或视图、以及搜索条件等。

例如，下面的语句查询testtable表中姓名为“张三”的nickname字段和email字段。

复制内容到剪贴板代码:SELECT `nickname`,`email`FROM `testtable`WHERE `name`='张三'(一) 选择列表选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。

1、选择所有列例如，下面语句显示testtable表中所有列的数据：复制内容到剪贴板代码:SELECT * FROM testtable2、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。

例如：复制内容到剪贴板代码:SELECT nickname,email FROM testtable3、更改列标题在选择列表中，可重新指定列标题。

定义格式为：列标题=列名列名列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：复制内容到剪贴板代码:SELECT 昵称=nickname,电子邮件=email FROM testtable4、删除重复行SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。

使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。

5、限制返回的行数使用TOP n [PERCENT]选项限制返回的数据行数，TOP n说明返回n行，而TOP n PERCENT 时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。

例如：复制内容到剪贴板代码:SELECT TOP 2 * FROM `testtable`复制内容到剪贴板代码:SELECT TOP 20 PERCENT * FROM `testtable`(二) FROM子句FROM子句指定SELECT语句查询及与查询相关的表或视图。

SQL数据库语句大全大全(完全整理版)

SQL语句大全——语句功能-—数据操作SELECT —-从数据库表中检索数据行和列INSERT --向数据库表添加新数据行DELETE —-从数据库表中删除数据行UPDATE ——更新数据库表中的数据-数据定义CREATE TABLE -—创建一个数据库表DROP TABLE --从数据库中删除表ALTER TABLE -—修改数据库表结构CREATE VIEW -—创建一个视图DROP VIEW --从数据库中删除视图CREATE INDEX -—为数据库表创建一个索引DROP INDEX ——从数据库中删除索引CREATE PROCEDURE —-创建一个存储过程DROP PROCEDURE —-从数据库中删除存储过程CREATE TRIGGER —-创建一个触发器DROP TRIGGER —-从数据库中删除触发器CREATE SCHEMA ——向数据库添加一个新模式DROP SCHEMA --从数据库中删除一个模式CREATE DOMAIN ——创建一个数据值域ALTER DOMAIN --改变域定义DROP DOMAIN —-从数据库中删除一个域-—数据控制GRANT -—授予用户访问权限DENY --拒绝用户访问REVOKE ——解除用户访问权限--事务控制COMMIT --结束当前事务ROLLBACK --中止当前事务SET TRANSACTION --定义当前事务数据访问特征-—程序化SQLDECLARE -—为查询设定游标EXPLAN —-为查询描述数据访问计划OPEN ——检索查询结果打开一个游标FETCH -—检索一行查询结果CLOSE -—关闭游标PREPARE —-为动态执行准备SQL 语句EXECUTE --动态地执行SQL 语句DESCRIBE ——描述准备好的查询———局部变量declare ＠id char(10）—-set @id = ’10010001'select ＠id = ’10010001’—-—全局变量—-—必须以＠@开头—-IF ELSEdeclare @x int ＠y int @z intselect ＠x = 1 ＠y = 2 ＠z=3if ＠x > @yprint 'x 〉y’ ——打印字符串'x 〉y’else if ＠y 〉@zprint 'y 〉z’else print 'z > y’--CASEuse panguupdate employeeset e_wage =casewhen job_level = ’1’ then e_wage*1。

常用sql查询语句大全

常用sql查询语句大全以下是SQL中常用的查询语句:1. SELECT语句:选择表中的数据。

```SELECT column1, column2, column3FROM table_nameWHERE conditionORDER BY column_name DESC, column_name ASC```上面的查询语句将选择名为“column1”、“column2”和“column3”的表中的所有数据,并按照“column_name”的升序和降序进行排序。

2. INSERT INTO语句:将数据插入到表中。

```INSERT INTO table_name (column1, column2, column3)VALUES (value1, value2, value3)```上面的查询语句将创建一个名为“table_name”的表,该表包含三个字段,分别为“column1”、“column2”和“column3”,并插入三条数据,分别为“value1”、“value2”和“value3”。

3. UPDATE语句:更新表中的数据。

```UPDATE table_nameSET column1 = value1, column2 = value2, column3 = value3 WHERE condition```上面的查询语句将根据给定的“condition”更新名为“table_name”的表中的“column1”、“column2”和“column3”三个字段中的数据,并将结果输出到查询结果中。

4. DELETE语句:删除表中的数据。

```DELETE FROM table_nameWHERE condition```上面的查询语句将根据给定的“condition”从名为“table_name”的表中删除所有数据。

sql常用语句大全

sql常用语句大全以下是SQL中常用的语句:1. 查询语句:用于从数据库中检索数据。

- SELECT语句:用于从表中选择数据。

- FROM语句:用于从表中选择数据。

- WHERE语句:用于筛选数据。

- ORDER BY语句:用于排序数据。

- BY语句:用于对查询结果进行分组和排序。

2. 更新语句:用于更新数据库中的数据。

- UPDATE语句:用于在表中更新数据。

- WHERE语句:用于指定更新条件。

- SET语句:用于更新数据。

3. 删除语句:用于在数据库中删除数据。

- DELETE语句:用于从表中删除数据。

- WHERE语句:用于指定删除条件。

4. 创建语句:用于创建数据库、表、索引等。

-CREATE TABLE语句:用于创建一个表。

- AS语句:用于为表命名并提供别名。

- CONSTRAINT语句:用于为表创建约束条件。

5. 插入语句:用于向数据库中插入数据。

-INSERT INTO语句:用于向表中插入数据。

- VALUES语句:用于指定插入的数据。

6. 数据定义语句:用于定义数据库中的数据模型。

- PRIMARY KEY语句:用于为表创建主键。

- FOREIGN KEY语句:用于为表创建外键。

- KEY语句:用于为表创建索引。

7. 查询优化语句:用于优化查询性能。

- ANSI JOIN语句:用于连接两个表。

- NOT NULL语句:用于指定字段是否为非空。

- UNIQUE KEY语句:用于指定字段是否唯一。

8. 视图语句:用于简化复杂的查询。

- 视图定义语句:用于定义视图。

- 视图查询语句:用于查询视图中的数据。

9. 存储过程语句:用于执行复杂的操作并将结果存储回数据库中。

- 存储过程定义语句:用于定义存储过程。

- 存储过程执行语句:用于执行存储过程。

以上是SQL中常用的语句列表,SQL语句的使用可以极大地提高数据库的性能和灵活性。

数据库查询sql语句大全

数据库查询sql语句大全一、常用查询语句1. SELECT：查询语句，用于从表中选取数据。

2. FROM：指定要查询的表名称。

4. ORDER BY：将结果集按照指定的列进行排序。

二、数据过滤语句1. WHERESELECT * FROM 表名 WHERE 列名=值； --- 查找指定值SELECT * FROM 表名 WHERE 列名<>值； --- 不等于指定值SELECT * FROM 表名 WHERE 列名>值； --- 大于指定值SELECT * FROM 表名 WHERE 列名>=值； --- 大于等于指定值SELECT * FROM 表名 WHERE 列名<值； --- 小于指定值SELECT * FROM 表名 WHERE 列名<=值； --- 小于等于指定值SELECT * FROM 表名 WHERE 列名 BETWEEN 值1 AND 值2； --- 查找指定范围内的值SELECT * FROM 表名 WHERE 列名 LIKE '值%'； --- 查找以指定值开头的值SELECT * FROM 表名 WHERE 列名 LIKE '%值'； --- 查找以指定值结尾的值SELECT * FROM 表名 WHERE 列名 LIKE '%值%'； --- 查找包含指定值的值2. INSELECT * FROM 表名 WHERE 列名 IN (值1，值2，...)； --- 查找在指定值列表中的值3. NOT IN4. EXISTS6. LIKE三、运算符1. 加法 +SELECT 列名1 + 列名2 AS 别名 FROM 表名；2. 减法 -3. 乘法 *4. 除法 /5. 取模 %四、数据排序1. ORDER BYSELECT * FROM 表名 ORDER BY 列名 ASC； --- 升序排序SELECT * FROM 表名 ORDER BY 列名 DESC； --- 降序排序2. 多列排序五、数据聚合1. COUNT2. SUM3. AVG4. MAX六、数据分组SELECT 列名1,COUNT(列名2) AS 别名 FROM 表名 GROUP BY 列名1； --- 按照列名1分组，计算列名2的计数七、数据连接1. 内连接SELECT * FROM 表1 INNER JOIN 表2 ON 条件； --- 内连接表1和表2，并按照条件进行匹配八、数据更新和删除1. UPDATE2. DELETEDELETE FROM 表名 WHERE 条件； --- 删除表中满足条件的记录1. CREATE TABLEDROP TABLE 表名; --- 删除表十、数据备份和还原1. 备份mysqldump -u用户名 -p密码数据库名 > 备份文件.sql;2. 还原以上就是数据库查询SQL语句大全中文部分了。