50个经典sql语句总结

合集下载

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 top 10 * from 数据表 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 …) values (值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（仅用于SQlServer）法二：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 " 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、说明：显示文章、提交人和最终回复时间selecta.title,ername,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 FROMa) 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 tabl e1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)10、说明：两张关联表，删除主表中已经在副表中没有的信息delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )11、说明：四表联查问题：select * from a left inner join b ona.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where.....12、说明：日程支配提前五分钟提示SQL: select * from日程支配where datediff(minute,f开头时间,getdate())>513、说明：一条sql语句搞定数据库分页select top 10 b.* from (select top 20主键字段,排序字段from表名order by排序字段desc) a,表名b where b.主键字段= a.主键字段order by a.排序字段详细实现：关于数据库分页：declare @start int,@end int@sqlnvarchar(600)set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’exec sp_executesql @sql留意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特别的处理。

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。

mysql必背50条语句

mysql必背50条语句1. 创建数据库:```sqlCREATE DATABASE dbname;```2. 删除数据库:```sqlDROP DATABASE dbname;```3. 选择数据库:```sqlUSE dbname;```4. 显示所有数据库:```sqlSHOW DATABASES;```5. 创建表:```sqlCREATE TABLE tablename (column1 datatype,column2 datatype,...);```6. 查看表结构:```sqlDESC tablename;```7. 删除表:```sqlDROP TABLE tablename;```8. 插入数据:```sqlINSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);```9. 查询数据:```sqlSELECT * FROM tablename;```10. 条件查询:```sqlSELECT * FROM tablename WHERE condition;```11. 更新数据:```sqlUPDATE tablename SET column1 = value1, column2 = value2 WHERE condition;```12. 删除数据:```sqlDELETE FROM tablename WHERE condition;```13. 查找唯一值:```sqlSELECT DISTINCT column FROM tablename;```14. 排序数据:```sqlSELECT * FROM tablename ORDER BY column ASC/DESC;```15. 限制结果集:```sqlSELECT * FROM tablename LIMIT 10;```16. 分页查询:```sqlSELECT * FROM tablename LIMIT 10 OFFSET 20;```17. 计算行数:```sqlSELECT COUNT(*) FROM tablename;```18. 聚合函数:```sqlSELECT AVG(column), SUM(column), MIN(column), MAX(column) FROM tablename;```19. 连接表:```sqlSELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;```20. 创建索引:```sqlCREATE INDEX indexname ON tablename (column);```21. 查看索引:```sqlSHOW INDEX FROM tablename;```22. 删除索引:```sqlDROP INDEX indexname ON tablename;```23. 备份整个数据库:```sqlmysqldump -u username -p dbname > backup.sql;```24. 恢复数据库:```sqlmysql -u username -p dbname < backup.sql;```25. 修改表结构:```sqlALTER TABLE tablename ADD COLUMN newcolumn datatype;```26. 重命名表:```sqlRENAME TABLE oldname TO newname;```27. 增加主键:```sqlALTER TABLE tablename ADD PRIMARY KEY (column);```28. 删除主键:```sqlALTER TABLE tablename DROP PRIMARY KEY;```29. 增加外键:```sqlALTER TABLE tablename ADD CONSTRAINT fk_name FOREIGN KEY (column) REFERENCES othertable(column);```30. 删除外键:```sqlALTER TABLE tablename DROP FOREIGN KEY fk_name;```31. 查看活动进程:```sqlSHOW PROCESSLIST;```32. 杀死进程:```sqlKILL process_id;```33. 给用户授权:```sqlGRANT permission ON dbname.tablename TO 'username'@'host';```34. 撤销用户权限:```sqlREVOKE permission ON dbname.tablename FROM 'username'@'host';```35. 创建用户:```sqlCREATE USER 'username'@'host' IDENTIFIED BY 'password';```36. 删除用户:```sqlDROP USER 'username'@'host';```37. 修改用户密码:```sqlSET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');```38. 查看用户权限:```sqlSHOW GRANTS FOR 'username'@'host';```39. 启用外键约束:```sqlSET foreign_key_checks = 1;```40. 禁用外键约束:```sqlSET foreign_key_checks = 0;```41. 启用查询缓存:```sqlSET query_cache_type = 1;```42. 禁用查询缓存:```sqlSET query_cache_type = 0;```43. 查看服务器版本:```sqlSELECT VERSION();```44. 查看当前日期和时间:```sqlSELECT NOW();```45. 查找匹配模式:```sqlSELECT * FROM tablename WHERE column LIKE 'pattern';```46. 计算平均值:```sqlSELECT AVG(column) FROM tablename;```47. 查找空值:```sqlSELECT * FROM tablename WHERE column IS NULL;```48. 日期比较:```sqlSELECT * FROM tablename WHERE date_column > '2022-01-01';```49. 将结果导出为CSV 文件:```sqlSELECT * INTO OUTFILE 'output.csv' FIELDS TERMINATED BY ',' FROM tablename;```50. 将结果导入其他表:```sqlLOAD DATA INFILE 'input.csv' INTO TABLE tablename FIELDS TERMINATED BY ',';。

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经典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语句大全

一、基础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],..)5、明：除新表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）并消去表中任何重复行而派生出一个结果表。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

一个项目涉及到的50个Sql语句(整理版)--1.学生表Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号--3.教师表Teacher(T,Tname) --T 教师编号,Tname 教师姓名--4.成绩表SC(S,C,score) --S 学生编号,C 课程编号,score 分数*/--创建测试数据create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')insert into Student values('04' , N'李云' , '1990-08-06' , N'男')insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')create table Course(C varchar(10),Cname nvarchar(10),T varchar(10))insert into Course values('01' , N'语文' , '02')insert into Course values('02' , N'数学' , '01')insert into Course values('03' , N'英语' , '03')create table Teacher(T varchar(10),Tname nvarchar(10))insert into Teacher values('01' , N'张三')insert into Teacher values('02' , N'李四')insert into Teacher values('03' , N'王五')create table SC(S varchar(10),C varchar(10),score decimal(18,1))insert into SC values('01' , '01' , 80)insert into SC values('01' , '02' , 90)insert into SC values('01' , '03' , 99)insert into SC values('02' , '01' , 70)insert into SC values('02' , '02' , 60)insert into SC values('02' , '03' , 80)insert into SC values('03' , '01' , 80)insert into SC values('03' , '02' , 80)insert into SC values('03' , '03' , 80)insert into SC values('04' , '01' , 50)insert into SC values('04' , '02' , 30)insert into SC values('04' , '03' , 20)insert into SC values('05' , '01' , 76)insert into SC values('05' , '02' , 87)insert into SC values('06' , '01' , 31)insert into SC values('06' , '03' , 34)insert into SC values('07' , '02' , 89)insert into SC values('07' , '03' , 98)go--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数--1.1、查询同时存在"01"课程和"02"课程的情况select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c where a.S = b.S and a.S = c.S and b.C = '01' and c.C = '02' and b.score > c.score--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student aleft join SC b on a.S = b.S and b.C = '01'left join SC c on a.S = c.S and c.C = '02'where b.score > isnull(c.score,0)--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数--2.1、查询同时存在"01"课程和"02"课程的情况select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c where a.S = b.S and a.S = c.S and b.C = '01' and c.C = '02' and b.score < c.score--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student aleft join SC b on a.S = b.S and b.C = '01'left join SC c on a.S = c.S and c.C = '02'where isnull(b.score,0) < c.score--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩select a.S , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_scorefrom Student a , sc bwhere a.S = b.Sgroup by a.S , a.Snamehaving cast(avg(b.score) as decimal(18,2)) >= 60order by a.S--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩--4.1、查询在sc表存在成绩的学生信息的SQL语句。

select a.S , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_scorefrom Student a , sc bwhere a.S = b.Sgroup by a.S , a.Snamehaving cast(avg(b.score) as decimal(18,2)) < 60order by a.S--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。

select a.S , a.Sname , isnull(cast(avg(b.score) as decimal(18,2)),0) avg_scorefrom Student a left join sc bon a.S = b.Sgroup by a.S , a.Snamehaving isnull(cast(avg(b.score) as decimal(18,2)),0) < 60order by a.S--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩--5.1、查询所有有成绩的SQL。