《整理mysql常用sql语句及常用语法【mysql常用案例】》由会员分享，可在线阅读，更多相关《整理mysql常用sql语句及常用语法【mysql常用案例】（11页珍藏版）》请在金锄头文库上搜索。
1、整理mysql常用sql语句及常用语法【mysql常用案例】库操作:SHOW DATABASES;#显示所有数据库CREATE DATABASE IF NOT EXISTS db_name CHARACTER SETUTF8; # 创建库(选择字符编码)SHOWCREATE DATABASE db_name CHARACTER SETUTF8;# 查看新建库的方式ALTER DATABASE db_name CHARACTER SETUTF8; # 修改数据库USE db_name; # 使用数据库SELECT DATABASE(); # 查看当前使用的数据库表操作:SHOW TABLES;#
2、创建表CREATE TABLE tab_name (field type 完整性约束条件 CHECK (id0) CHARACTER SETUTF8;-CHECK 为约束，插入更新数据时会检查，保证id是否大于0DESCtab_name; # 查看表结构RENAMETABLE 旧表名 TO新表名; # 重命名表ALTER TABLE tab_name ADD 列名 type 完整约束条件,ADD 列名 type 完整约束条件;ALTER TABLE tab_name DROP列名; # 删除列ALTER TABLE tab_name MODIFY 列名 type 完整约束条件 DEFAULT;
3、ALTER TABLE tab_name CHANGE 旧列名 新列名 type; -改列名DROP TABLE tab_name; -删表#注释/*注释*/-注释SELECT语句:SELECT DISTINCT name FROMemp; #检索出不同的值(检索多列时若两列不完全相同,所有列都会被检索出来)SELECT id FROM emp LIMIT 5 OFFSET 5; #检索从第5行起后5行ORDER BY:SELECTprod_id,prod_price,prod_nameFROMProductsORDER BYprod_price,prod_name;-仅在prod_price
4、出现相同的值时，才会对prod_name进行排序，如果prod_price的值唯一-则不会对prod_name排序SELECTprod_id,prod_price,prod_nameFROMProductsORDER BY 2,3;-先对第2个列进行排序，再对第三个列进行排序SELECTprod_id,prod_price,prod_nameFROMProductsORDER BY prod_price DESC,prod_name;-有DESC的进行升序排列,默认排列为A到Z (ASC)SELECT prod_name as name FROMProduct;-SELECT指定别名或进行运算
5、都不会改动原表WHERE:SELECTvend_id,prod_nameFROMProductsWHERE vend_id lt; #39;DLL01#39;-检索除DLL01的值-进行不匹配，匹配检索时，null值不会出现在检索结果之中SELECTvend_id,prod_nameFROMProductsWHERE NOT vend_id = #39;DLL01#39;SELECTprod_name,prod_priceFROMProductsWHERE prod_price BETWEEN 5 AND 10;-范围检查SELECTprod_name,prod_priceFROMProduc
6、tsWHERE prod_price IS NULL;-检索空值SELECTvend_id,prod_price,prod_nameFROMProductsWHERE vend_id = #39;DLL01#39; AND prod_price lt;=4;-ORDER BY 子句应在WHERE之后SELECTprod_name,prod_priceFROMProductsWHERE (vend_id = #39;DLL01#39; OR vend_id = #39;BRS01#39;) AND prod_price = 10;-AND的优先级比OR高，一般要用圆括号分组-过滤：SELECTp
7、rod_name,prod_priceFROMProductsWHERE vend_id IN (#39;DLL01#39;,#39;BRS01#39;)ORDER BYprod_name;SELECTvend_id,prod_nameFROMProductsWHERE prod_name LIKE #39;Fish%#39;-检索prod_name中以Fish开头的值(区分大小写)，但不会检索出NULL的行-还可以这样用：#39;%fish%#39;SELECTvend_id,prod_nameFROMProductsWHERE prod_name LIKE #39;_inch teddy
8、bear#39;-下划线也是通配符，匹配一个字符-函数：SELECT Concat(vend_name,#39;(#39;,RTRIM(vend_country),#39;)#39;) -合并AS vend_title -去掉右边的FROM Vendors ORDER BY vend_name; -LTRIM()去掉左边的 ，TRIM()去两边SELECT quantity*item_price ASexpanded_priceFROMOrderItemsWHERE order_num = 8;SELECT cust_name,cust_contant FROMCustomersWHERE S
9、OUNDEX(cust_contact)=SOUNDEX(#39;Michael Green#39;); -拟声SELECTorder_numFROMOrdersWHERE YEAR(order_date) = ;GROUP BY:SELECT vend_id ,COUNT(*) ASnum_prodsFROMProductsGROUP BY vend_id; -要在WHERE子句之后，ORDER BY子句之前-GROUP BY 后记得 ORDER BYHAVING -过滤组：SELECT cust_id,COUNT(*) ASordersFROMOrdersGROUP BYcust_idHA
10、VING orders =2;SELECT vend_id,COUNT(*) ASnum_prodsFROMProductsWHERE prod_price =4GROUP BYvend_idHAVING num_prods =2;-子查询：-策略：看最后一个WHERE 和各个SELECT前的检索值SELECTcust_name,cust_contactFROMCustomersWHERE cust_id IN (SELECTcust_idFROMOrdersWHERE order_num IN (SELECTorder_numFROMOrderItemsWHERE prod_id = #39
11、;RGAN01#39;);-还可通过联结进行查询SELECTcust_name,cust_state,(SELECT COUNT(*)FROMOrdersWHERE Orders.cust_id =Customers.cust_id)ASordersFROMCustomersORDER BYcust_name;-联结表：-内联结：-内联结就是将两个表并在一起，将两表中的信息同时检索出来SELECTvend_name,prod_name,prod_priceFROM vendors INNERJION ProductsON Vendors.vend_id = Products.vend_id;
12、-#39;标准写法#39;SELECTprod_name,vend_name,prod_price,quantityFROMOrderItems,Products,VendorsWHERE Products.vend_id =Vendors.vend_idAND OrderItems.prod_id =Products.prod_idAND order_num = 7; -联结三张表SELECTcust_name,cust_contactFROM Customers AS C,Orders AS O,OrderItems ASOIWHERE C.cust_id =O.cust_idAND OI
13、.order_num =O.order_numAND prod_id = #39;RGAN01#39;-自联结:SELECTC1.cust_id,C1.cust_name,C1.cust_contactFROM Customers AS C1,Customers ASC2WHERE C1.cust_name =C2.cust_nameAND C2.cust_contact = #39;Jim Jones#39;-外联结:-包含了那些联结表中没有关联的行，叫外联结SELECTC.cust_id,O.order_numFROM Customers AS C LEFT OUTER JOIN Orde
14、rs ASOON C.cust_id =O.cust_id;-LEFT OUTER 指左边未关联的行，RIGHT 为右边SELECTC.cust_id,COUNT(O.order_num) ASnum_ordFROM Customers AS C LEFT OUTER JOIN Orders ASOON C.cust_id =O.cust_idGROUP BYC.cust_id;-组合查询：-将多条SELECT语句的查询结果按一个结果返回-常用于不同表之间的查询-UNION ALL 返回所有行(包括重复的)SELECTcust_name,cust_contact,cust_emailFROMC
15、ustomersWHERE cust_state IN (#39;IN#39;,#39;IL#39;,#39;MI#39;)UNIONSELECTcust_name,cust_contact,cust_emailFROMCustomersWHERE cust_name = #39;Fun4All#39;ORDER BY cust_name -ORDER BY 语句必须放在最后一个SELECT语句后-插入数据：INSERT INTOemp (name,department)VALUES(远东,销售部),(安发,财政部);INSERT INTOempSET name=hzw; # 插入表记录INSERT INTOCustomers(cust_id)SELECTcust_idFROM CustNew -导入数据SELECT *INTOCustCopyFROM Customers;-导出数据CREATE TABLE CustCopy ASSELECT * FROMCustomers;-运行中创建的表CustCopy将与Customers一样-更新与删除：-UPDATE 修改列数据，DELETE删除行UPDATE emp SET depart =销售部,age= 18WHERE name =ldy # 修改表记录,UPDATE只能用SET修改DELETE FROMemp; # 删
