公开课网站建设,山东房地产新闻,手机黄山网站,电商网站建设需要多少钱一年阅读目录1.1 SELECT高级应用1.2 select中where子句使用1.3 select中ORDER BY子句1.4 LIMIT子句1.5 多表连接查询1.6 Informatica_schema获取元数据1.7 参考文献1.1 SELECT高级应用1.1.1 前期准备工作本次测试使用的是world数据库#xff0c;由MySQL官方提供下载地址#xff1…阅读目录1.1 SELECT高级应用1.2 select中where子句使用1.3 select中ORDER BY子句1.4 LIMIT子句1.5 多表连接查询1.6 Informatica_schema获取元数据1.7 参考文献1.1 SELECT高级应用1.1.1 前期准备工作本次测试使用的是world数据库由MySQL官方提供下载地址https://dev.mysql.com/doc/index-other.htmlworld文件导入方法官方说明https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html下载sqlyog软件用于之后的数据库管理用http://www.webyog.com创建用户能够让sqlyog登录数据库即可注意权限控制。mysql grant all on *.* to root% identified by 123;Query OK, 0 rows affected (0.00 sec)授权用户后参看mysql select user,host from mysql.user where user like root;-----------------| user | host |-----------------| root | % || root | 10.0.0.1 || root | 127.0.0.1 || root | localhost |-----------------4 rows in set (0.00 sec)1.1.2 select语法格式说明mysql help select;Name: SELECTDescription:Syntax:SELECT[ALL | DISTINCT | DISTINCTROW ][HIGH_PRIORITY][STRAIGHT_JOIN][SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT][SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]select_expr [, select_expr ...][FROM table_references[PARTITION partition_list][WHERE where_condition][GROUP BY {col_name | expr | position}[ASC | DESC], ... [WITH ROLLUP]][HAVING where_condition][ORDER BY {col_name | expr | position}[ASC | DESC], ...][LIMIT {[offset,] row_count | row_count OFFSET offset}][PROCEDURE procedure_name(argument_list)][INTO OUTFILE file_name[CHARACTER SET charset_name]export_options| INTO DUMPFILE file_name| INTO var_name [, var_name]][FOR UPDATE | LOCK IN SHARE MODE]]1.2 select中where子句使用SELECT *|{[DISTINCT] column|select_expr [alias], ...]}[FROM [database.]table][WHERE conditions];where条件的说明WHERE条件又叫做过滤条件它从FROM子句的中间结果中去掉所有条件conditions不为TRUE(而为FALSE或者NULL)的行。WHERE子句跟在FROM子句后面不能在WHERE子句中使用列别名。【示例一】where字句的基本使用SELECT * FROM world.city WHERE CountryCodeCHN;orSELECT * FROM world.city WHERE CountryCodechn;sql说明从数据库中查找是中国的城市。注意WHERE中出现的字符串和日期字面量必须使用引号括起来这里字符串字面量写成大写或小写结果都一样即不区分大小写进行查询。这和Oracle不同ORACLE中WHERE条件中的字面量是区分大小写的【示例二】where字句中的逻辑操作符SELECT * FROM world.cityWHERE CountryCodechn AND district shanxi;sql说明 从数据库中查找是中国的并且是山西的城市逻辑操作符介绍逻辑操作符说明and逻辑与。只有当所有的子条件都为true时and才返回true。否则返回false或nullor逻辑或。只要有一个子条件为trueor就返回true。否则返回false或nullnot逻辑非。如果子条件为true则返回false如果子条件为false则返回truexor逻辑异或。当一个子条件为true而另一个子条件为false时其结果为true当两个条件都为true或都为false时结果为false。否则结果为null【示例三】where字句中的范围比较SELECT * FROM world.cityWHEREpopulation BETWEEN 100000 AND 200000 ;sql说明 从数据库中查找人口数量在 100000-200000 之间的城市【示例四】where字句中的INSELECT * FROM cityWHERE countrycode IN (CHN,JPN);sql说明 查询中国和日本的所有城市【示例五】where字句中的likeUSE world;SELECT * FROM cityWHERE countrycode LIKE ch%;sql说明 从city表中找到国家是一ch开头的。like的语法like ‘匹配模式字符串’实现模式匹配查询或者模糊查询测试一个列值是否匹配给出的模式在‘匹配模式字符串’中可以有两个具有特殊含义的通配字符%表示0个或者任意多个字符_只表示一个任意字符1.3 select中ORDER BY子句1.3.1 order by 子句的作用ORDER BY子句用来排序行如果SELECT语句中没有ORDER BY子句那么结果集中行的顺序是不可预料的语法SELECT exprFROM table[WHERE condition(s)][ORDER BY {column, expr, numeric_position} [Asc|DEsc]];部分参数说明参数参数说明Asc执行升序排序。默认值DEsc执行降序排序使用方法ORDER BY子句一般在SELECT语句的最后面1.3.2 order by 示例【示例一】Order by基本使用SELECT * FROM cityORDER BY population;sql说明将城市表按照人口数量升序排列【示例二】多个排序条件SELECT * FROM cityORDER BY population,countrycode;sql说明 按照人口和国家进行排序【示例三】以select字句列编号排序SELECT * FROM cityORDER BY 5;sql说明按照第5列进行排序【示例四】desc实践SELECT * FROM cityORDER BY 5 DESC;sql说明 按照第列进行逆序排列说明NULL值的排序在MySQL中把NULL值当做一列值中的最小值对待。因此升序排序时它出现在最前面。1.4 LIMIT子句特点说明MySQL特有的子句。它是SELECT语句中的最后一个子句(在order by后面)。它用来表示从结果集中选取最前面或最后面的几行。偏移量offset的最小值为0。语法limit [OFFSET ]或者limit [,] 查询示例SELECT * FROM cityORDER BY 5 DEscLIMIT 4;sql说明 获取排序后的前4行注先按照人口数量进行降序排序然后使用limit从中挑出最前面的4行。如果没有order by子句返回的4行就是不可预料的。1.5 多表连接查询1.5.1 传统的连接写法(使用where)SELECT NAME,ci.countrycode ,cl.language ,ci.populationFROM city ci , countrylanguage clWHERE ci.CountryCodecl.countrycode;sql说明 city定别名为ci 国家定别名问为cl进行连表查询NAME是共同的键值使用where条件进行连接。注意一旦给表定义了别名那么原始的表名就不能在出现在该语句的其它子句中了1.5.2 NATURAL JOIN子句自动到两张表中查找所有同名同类型的列拿来做连接列进行相等连接SELECT NAME,countrycode ,LANGUAGE ,populationFROM city NATURAL JOIN countrylanguageWHERE population 1000000ORDER BY population;sql说明使用natural join 进行相等连接两个表条件为人口大于1000000的进行升序排列。注意在select子句只能出现一个连接列1.5.3 使用using子句SELECT NAME,countrycode ,LANGUAGE ,populationFROM city JOIN countrylanguageUSING(countrycode);sql说明使用join进行两表的来连接using指定countrycode为关联列。1.5.4 集合操作UNION [DISTINCT]UNION ALL语法SELECT ...UNION [ALL | DISTINCT]SELECT ...[UNION [ALL | DISTINCT]SELECT ...]⛳ UNION用于把两个或者多个select查询的结果集合并成一个⛳ 进行合并的两个查询其SELECT列表必须在数量和对应列的数据类型上保持一致⛳ 默认会去掉两个查询结果集中的重复行⛳ 默认结果集不排序⛳ 最终结果集的列名来自于第一个查询的SELECT列表1.5.5 分组操作及分组处理“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组所谓的分组就是将一个“数据集”划分成若干个“小区域”然后针对若干个“小区域”进行数据处理。Having与Where的区别where 子句的作用是在对查询结果进行分组前将不符合where条件的行去掉即在分组之前过滤数据where条件中不能包含聚组函数使用where条件过滤出特定的行。having 子句的作用是筛选满足条件的组即在分组之后过滤数据条件中经常包含聚组函数使用having 条件过滤出特定的组也可以使用多个分组标准进行分组。1.5.6 【select高级应用】数据库备份脚本拼接SELECT CONCAT(mysqldump ,-uroot ,-p123 ,table_schema, ,table_name,/tmp/,table_schema,_,table_name,.sql)FROM information_schema.tablesWHERE table_schemaworldINTO OUTFILE /tmp/world_bak.sh使用concat进行拼接数据备份脚本。-- 显示信息可直接进行运算SELECT CONCAT(132)SELECT CONCAT(132123)SELECT CONCAT(132123)-- 查看引擎是innodb的表SELECT TABLE_NAME FROM TABLES WHERE ENGINEinnodb;SELECT CHARACTER_SET_NAME, COLLATION_NAMEFROM INFORMATION_SCHEMA.COLLATIONSWHERE IS_DEFAULT Yes;-- 显示每个库下有多少表SELECT TABLE_SCHEMA ,COUNT(*)FROM information_schema.TABLESGROUP BY TABLE_SCHEMA;1.5.7 子查询子查询定义在一个表表达中可以调用另一个表表达式这个被调用的表表达式叫做子查询(subquery)我么也称作子选择(subselect)或内嵌选择(inner select)。子查询的结果传递给调用它的表表达式继续处理。子查询(inner query)先执行然后执行主查询(outer query)子查询按对返回结果集的调用方法可分为where型子查询from型子查询及exists型子查询。使用子查询原则一个子查询必须放在圆括号中。将子查询放在比较条件的右边以增加可读性。子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句并且如果指定了它就必须放在主 SELECT 语句的最后。在子查询中可以使用两种比较条件单行运算符(, , , , ) 和多行运算符(IN, ANY, ALL)。不相关子查询子查询中没有使用到外部查询的表中的任何列。先执行子查询然后执行外部查询相关子查询(correlated subquery)子查询中使用到了外部查询的表中的任何列。先执行外部查询然后执行子查询以上两种类型之下又可以分为行子查询(row subquery)返回的结果集是 1 行 N 列列子查询(column subquery)返回的结果集是 N 行 1列表子查询(table subquery)返回的结果集是 N 行 N 列标量子查询(scalar subquery)返回1行1列一个值子查询示例创建数据表1 CREATE TABLE PLAYERS2 (PLAYERNO INTEGER NOT NULL,3 NAME CHAR(15) NOT NULL,4 INITIALS CHAR(3) NOT NULL,5 BIRTH_DATE DATE ,6 SEX CHAR(1) NOT NULL,7 JOINED SMALLINT NOT NULL,8 STREET VARCHAR(30) NOT NULL,9 HOUSENO CHAR(4) ,10 POSTCODE CHAR(6) ,11 TOWN VARCHAR(30) NOT NULL,12 PHONENO CHAR(13) ,13 LEAGUENO CHAR(4) ,14 PRIMARY KEY (PLAYERNO));1516 CREATE TABLE PENALTIES17 (PAYMENTNO INTEGER NOT NULL,18 PLAYERNO INTEGER NOT NULL,19 PAYMENT_DATE DATE NOT NULL,20 AMOUNT DECIMAL(7,2) NOT NULL,21 PRIMARY KEY (PAYMENTNO));2223 INSERT INTO PLAYERS VALUES (2, Everett, R, 1948-09-01, M, 1975, Stoney Road,43, 3575NH, Stratford, 070-237893, 2411);24 INSERT INTO PLAYERS VALUES (6, Parmenter, R, 1964-06-25, M, 1977, Haseltine Lane,80, 1234KK, Stratford, 070-476537, 8467);25 INSERT INTO PLAYERS VALUES (7, Wise, GWS, 1963-05-11, M, 1981, Edgecombe Way,39, 9758VB, Stratford, 070-347689, NULL);26 INSERT INTO PLAYERS VALUES (8, Newcastle, B, 1962-07-08, F, 1980, Station Road,4, 6584WO, Inglewood, 070-458458, 2983);27 INSERT INTO PLAYERS VALUES (27, Collins, DD, 1964-12-28, F, 1983, Long Drive,804, 8457DK, Eltham, 079-234857, 2513);28 INSERT INTO PLAYERS VALUES (28, Collins, C, 1963-06-22, F, 1983, Old Main Road,10, 1294QK, Midhurst, 010-659599, NULL);29 INSERT INTO PLAYERS VALUES (39, Bishop, D, 1956-10-29, M, 1980, Eaton Square,78, 9629CD, Stratford, 070-393435, NULL);30 INSERT INTO PLAYERS VALUES (44, Baker, E, 1963-01-09, M, 1980, Lewis Street,23, 4444LJ, Inglewood, 070-368753, 1124);31 INSERT INTO PLAYERS VALUES (57, Brown, M, 1971-08-17, M, 1985, Edgecombe Way,16, 4377CB, Stratford, 070-473458, 6409);32 INSERT INTO PLAYERS VALUES (83, Hope, PK, 1956-11-11, M, 1982, Magdalene Road,16A, 1812UP, Stratford, 070-353548, 1608);33 INSERT INTO PLAYERS VALUES (95, Miller, P, 1963-05-14, M, 1972, High Street,33A, 5746OP, Douglas, 070-867564, NULL);34 INSERT INTO PLAYERS VALUES (100, Parmenter, P, 1963-02-28, M, 1979, Haseltine Lane,80, 6494SG, Stratford, 070-494593, 6524);35 INSERT INTO PLAYERS VALUES (104, Moorman, D, 1970-05-10, F, 1984, Stout Street,65, 9437AO, Eltham, 079-987571, 7060);36 INSERT INTO PLAYERS VALUES (112, Bailey, IP, 1963-10-01, F, 1984, Vixen Road,8, 6392LK, Plymouth, 010-548745, 1319);3738 INSERT INTO PENALTIES VALUES (1, 6, 1980-12-08,100);39 INSERT INTO PENALTIES VALUES (2, 44, 1981-05-05, 75);40 INSERT INTO PENALTIES VALUES (3, 27, 1983-09-10,100);41 INSERT INTO PENALTIES VALUES (4,104, 1984-12-08, 50);42 INSERT INTO PENALTIES VALUES (5, 44, 1980-12-08, 25);43 INSERT INTO PENALTIES VALUES (6, 8, 1980-12-08, 25);44 INSERT INTO PENALTIES VALUES (7, 44, 1982-12-30, 30);45 INSERT INTO PENALTIES VALUES (8, 27, 1984-11-12, 75);创建数据库语句例一、获取和100号球员性别相同并且居住在同一城市的球员号码。select playernofrom playerswhere (sex, town) (select sex, townfrom playerswhere playerno 100);例二、获取和27号球员出生在同一年的球员的号码select playernofrom playerswhere year(birth_date) (select year(birth_date)from playerswhere playerno 27)and playerno 27;例三、获取那些至少支付了一次罚款的球员的名字和首字母。select name, initialsfrom playerswhere exists(select * from penaltieswhere playerno players.playerno);例四、获取那些从来没有罚款的球员的名字和首字母。select name, initialsfrom playerswhere not exists(select * from penaltieswhere playerno players.playerno);1.6 Informatica_schema获取元数据1.6.1 元数据访问方法查询 INFORMATION_SCHEMA 数据库表。其中包含 MySQL 数据库服务器所管理的所有对象的相关数据使用 SHOW 语句。用于获取数据库和表信息的 MySQL 专用语句使用 DESCRIBE(或 DESC)语句。用于检查表结构和列属性的快捷方式使用 mysqlshow 客户端程序。SHOW 语法的命令行程序INFORMATION_SCHEMA数据库优点介绍充当数据库元数据的中央系统信息库模式和模式对象服务器统计信息(状态变量、设置、连接) 。采用表格式以实现灵活访问使用任意 SELECT 语句。是“虚拟数据库”表并非“真实”表(基表)而是“系统视图”根据当前用户的特权动态填充表。列出 INFORMATION_SCHEMA数据库中所有的表mysql USE information_schema;Database changedmysql SHOW TABLES;---------------------------------------| Tables_in_information_schema |---------------------------------------| CHARACTER_SETS || COLLATIONS || COLLATION_CHARACTER_SET_APPLICABILITY || COLUMNS || COLUMN_PRIVILEGES |1.6.2 对 INFORMATION_SCHEMA 使用 SELECT示例一查找引擎是innodb的表。SELECT TABLE_NAME, ENGINEFROM INFORMATION_SCHEMA.TABLESWHERE ENGINE innodb;示例二:SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE DATA_TYPE set;sql说明查找数据类型是set的表示例三SELECT CHARACTER_SET_NAME, COLLATION_NAME,IS_DEFAULTFROM INFORMATION_SCHEMA.COLLATIONSWHERE IS_DEFAULT Yes;sql说明查看找默认为yes的表示例四SELECT TABLE_SCHEMA, COUNT(*)FROM INFORMATION_SCHEMA.TABLESGROUP BY TABLE_SCHEMA;sql说明查看每个数据库下表的个数使用 INFORMATION_SCHEMA 表获取有关创建 shell 命令的信息。SELECT CONCAT(mysqldump -uroot -p ,TABLE_SCHEMA, , TABLE_NAME, ,TABLE_SCHEMA,.bak.sql)FROM TABLES WHERE TABLE_NAME LIKE Country%1.6.3 使用 mysql 命令创建 SQL 语句。mysql -uroot -p123 --silent --skip-column-names -e SELECT CONCAT(CREATE TABLE , TABLE_SCHEMA, .,TABLE_NAME, _backup LIKE , TABLE_SCHEMA, .,TABLE_NAME, ;)FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA world_innodb;1.6.4 MySQL中的show语句SOHW databases列出所有数据库SHOW TABLES列出默认数据库中的表SHOW TABLES FROM 列出指定数据库中的表SHOW COLUMNS FROM 显示表的列结构SHOW INDEX FROM 显示表中有关索引和索引列的信息SHOW CHARACTER SET显示可用的字符集及其默认整理SHOW COLLATION显示每个字符集的整理SHOW STATUS列出当前数据库状态SHOW VARIABLES列出数据库中的参数定义值1.6.5 DESCRIBE 语句DESCRIBE 语句 等效于 SHOW COLUMNS一般语法mysql DESCRIBE ;显示 INFORMATION_SCHEMA 表信息mysql DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS;--------------------------------------------------------------| Field | Type | Null | Key | Default | Extra |--------------------------------------------------------------| CHARACTER_SET_NAME | varchar(32) | NO | | | || DEFAULT_COLLATE_NAME | varchar(32) | NO | | | || DESCRIPTION | varchar(60) | NO | | | || MAXLEN | bigint(3) | NO | | 0 | |--------------------------------------------------------------4 rows in set (0.00 sec)有关数据库和表的结构的信息与 SHOW 语句相似一般语法shell mysqlshow [options] [db_name [table_name[column_name]]]显示所有数据库或特定数据库、表和/或列的相关信息[rootdb02 ~]# mysqlshow -uroot -p123Warning: Using a password on the command line interface can be insecure.--------------------| Databases |--------------------| information_schema || clsn || haha || mysql || oldboy || performance_schema || world |--------------------查看数据库下的表[rootdb02 ~]# mysqlshow -uroot -p123 worldWarning: Using a password on the command line interface can be insecure.Database: world-----------------| Tables |-----------------| PENALTIES || PLAYERS || city || country || countrylanguage |-----------------查看数据库下表记录[rootdb02 ~]# mysqlshow -uroot -p123 world cityWarning: Using a password on the command line interface can be insecure.Database: world Table: city------------------------------------------------------------------------------------------------------------------------| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |------------------------------------------------------------------------------------------------------------------------| ID | int(11) | | NO | PRI | | auto_increment | select,insert,update,references | || Name | char(35) | latin1_swedish_ci | NO | | | | select,insert,update,references | || CountryCode | char(3) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | || District | char(20) | latin1_swedish_ci | NO | | | | select,insert,update,references | || Population | int(11) | | NO | | 0 | | select,insert,update,references | |------------------------------------------------------------------------------------------------------------------------查看记录信息[rootdb02 ~]# mysqlshow -uroot -p123 world city CountryCodeWarning: Using a password on the command line interface can be insecure.Database: world Table: city Wildcard: CountryCode--------------------------------------------------------------------------------------------------------------| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |--------------------------------------------------------------------------------------------------------------| CountryCode | char(3) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | |--------------------------------------------------------------------------------------------------------------查看数据库类似like。[rootdb02 ~]# mysqlshow -uroot -p123 w%Warning: Using a password on the command line interface can be insecure.Wildcard: w%-----------| Databases |-----------| world |-----------1.7 参考文献https://dev.mysql.com/doc/refman/5.6/en/select.html SELECT语法官方说明
