学习SQL笔记

[教师师德学习笔记]师德，即教师的职业道德；师风，即教师的行为作风。师德师风对一个教师特别的重要，因为，教师是学生行动的标杆。其身正，不令而行；其身不正，虽令不从。通过暑期对师德师风的学习，本人深刻...+阅读

SQL 语句语法AND / ORSELECT column_name(s)FROM table_nameWHERE conditionAND|OR conditionALTER TABLEALTER TABLE table_nameADD column_name datatypeorALTER TABLE table_nameDROP COLUMN column_nameAS (alias)SELECT column_name AS column_aliasFROM table_nameorSELECT column_nameFROM table_name AS table_aliasBETWEENSELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2CREATE DATABASECREATE DATABASE database_nameCREATE TABLECREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name2 data_type,。 )CREATE INDEXCREATE INDEX index_nameON table_name (column_name)orCREATE UNIQUE INDEX index_nameON table_name (column_name)CREATE VIEWCREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE conditionDELETEDELETE FROM table_nameWHERE some_column=some_valueorDELETE FROM table_name(Note:Deletes the entire table!)DELETE * FROM table_name(Note:Deletes the entire table!)DROP DATABASEDROP DATABASE database_nameDROP INDEXDROP INDEX table_name。

index_name (SQL Server)DROP INDEX index_name ON table_name (MS Access)DROP INDEX index_name (DB2/Oracle)ALTER TABLE table_nameDROP INDEX index_name (MySQL)DROP TABLEDROP TABLE table_nameGROUP BYSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVINGSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator valueINSELECT column_name(s)FROM table_nameWHERE column_nameIN (value1,value2,)INSERT INTOINSERT INTO table_nameVALUES (value1, value2, value3,)orINSERT INTO table_name(column1, column2, column3,)VALUES (value1, value2, value3,)INNER JOINSELECT column_name(s)FROM table_name1INNER JOIN table_name2ON table_name1。 column_name=table_name2。column_nameLEFT JOINSELECT column_name(s)FROM table_name1LEFT JOIN table_name2ON table_name1。

column_name=table_name2。column_nameRIGHT JOINSELECT column_name(s)FROM table_name1RIGHT JOIN table_name2ON table_name1。 column_name=table_name2。column_nameFULL JOINSELECT column_name(s)FROM table_name1FULL JOIN table_name2ON table_name1。 column_name=table_name2。 column_nameLIKESELECT column_name(s)FROM table_nameWHERE column_name LIKE patternORDER BYSELECT column_name(s)FROM table_nameORDER BY column_name [ASC|DESC]SELECTSELECT column_name(s)FROM table_nameSELECT *SELECT *FROM table_nameSELECT DISTINCTSELECT DISTINCT column_name(s)FROM table_nameSELECT INTOSELECT *INTO new_table_name [IN externaldatabase]FROM old_table_nameorSELECT column_name(s)INTO new_table_name [IN externaldatabase]FROM old_table_nameSELECT TOPSELECT TOP number|percent column_name(s)FROM table_nameTRUNCATE TABLETRUNCATE TABLE table_nameUNIONSELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2UNION ALLSELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2UPDATEUPDATE table_nameSET column1=value, column2=value,WHERE some_column=some_valueWHERESELECT column_name(s)FROM table_nameWHERE column_name operator value学习SQL笔记。

SQLServer学习笔记 gt相关子查询及复杂查询

1 declare i int;2 set i=0;3 while i n。custid 9 ) as N'后一个订单‘10 from sales。orders n其结果如图所示：（2）累计聚合累计聚合在财务统计中，经常用到，比如2007年卖出多少，2008年卖出多少，那么2008年累计卖出就是2007年加上2008年卖出的总和，即累计聚合。在这里我们有视图Sales。OrderTotalsByYear，其中统计的是每一年的订单总量。1 select * from Sales。OrderTotalsByYear我们可以看到2007年有25489张订单，2008年有16247张订单，2006年有9581张订单。加入我们要求每年累计卖了多少订单，就要用到累计聚合。1 select n。orderyear,2 (3 select sum(qty)4 from Sales。OrderTotalsByYear m5 where m。orderyear相关子查询及复杂查询。

数据库各种改表sql写发总结

//修改已存在的表的字段属性ALTER TABLE region_info MODIFY code_name varchar(20) default ''；//删除索引ALTER TABLE poi drop index `uniq_domain_class_name_dim_code`；//增加unique keyALTER TABLE poi add unique key `uniq_domain_type_name` (`domain`,`class`,`name`,`dim`,`code`)；//更新字段属性ALTER TABLE poi MODIFY coords text default NULL；//删除表字段alter table poi drop is_delete；//修改coords不为空ALTER TABLE poi MODIFY coords text NOT NULL；//增加一列 is_deletealter table poi add is_delete int(11) not null default 0；执行sql:alter table message_template add used_by varchar(30) not null default '' comment '负责人‘ after `code` ；回滚sql:alter table message_template drop used_by；执行SQL:ALTER TABLE region_info MODIFY code_name varchar(20) NOT NULL;?ALTER TABLE po drop index `uniq_domain_type_name`;ALTER TABLE po add unique key `uniq_domain_class_name_dim_code` (`domain`,`class`,`name`,`dim`,`code`);ALTER TABLE po MODIFY coords text NOT NULL;ALTER TABLE poi MODIFY data text NOT NULL；回滚SQL:ALTER TABLE region_info MODIFY code_name varchar(20) default '';ALTER TABLE poi drop index `uniq_domain_class_name_dim_code`;ALTER TABLE poi add unique key `uniq_domain_type_name` (`domain`,`class`,`name`,`dim`,`code`);ALTER TABLE poi MODIFY coords text default NULL;ALTER TABLE poi MODIFY data text default NULL；执行：ALTER TABLE region_info MODIFY code_name varchar(20) NOT NULL;ALTER TABLE poi drop index `uniq_domain_type_name`;ALTER TABLE poi add unique key `uniq_domain_class_name_dim_code` (`domain`,`class`,`name`,`dim`,`code`);ALTER TABLE poi MODIFY coords text NOT NULL;ALTER TABLE poi MODIFY data text NOT NULL；回滚：ALTER TABLE region_info MODIFY code_name varchar(20) default '';ALTER TABLE poi drop index `uniq_domain_class_name_dim_code`;ALTER TABLE poi add unique key `uniq_domain_type_name` (`domain`,`class`,`name`,`dim`,`code`);ALTER TABLE poi MODIFY coords text default NULL;ALTER TABLE poi MODIFY data text default NULL;alter table message_template add used_by varchar(30) not null default '' comment '负责人‘ after `code` ；数据库各种改表sql写发总结。