-
1.字符函数 CONCAT() 字符连接 USE imooc; SELECT CONCAT('immoc', 'MySQL'); SELECT CONCAT('immoc', '-', 'MySQL'); DESC test; SELECT * FROM test; SELECT CONCAT(first_name, last_name) AS full_name FROM test; CONCAT_WS() - 使用指定的分隔符进行字符连接 SELECT CONCAT_WS('|','A',,'B','C'); SELECT CONCAT_WS('|','immoc','MySQL','Functions'); FORMAT() - 数字格式化 SELECT FORMAT(12560.75,2); SELECT FORMAT(12560.75,1); LOWER() - 转换成小写字母 SELECT LOWER('MySQL'); UPPER() - 转换成大写字母 SELECT UPPER('MySQL'); LEFT() - 获取左侧字符 SELECT LEFT('MySQL',2); SELECT LOWER(LEFT('MySQL',2)); RIGHT() - 获取右侧字符 SELECT RIGHT('MySQL',2);查看全部
-
运算符 函数查看全部
-
1.多表删除 DELETE tbl_name[.*][,tbl_name[.*]] ... FROM tbl_references [WHERE where_condition]; SELECT * FROM tdb_goods; SELECT goods_id, goods_name FROM tdb_goods GROUP BY goods_name; SELECT goods_id, goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) > 2; DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id, goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2) AS t2 ON t1.goods_name WHERE t1.goods_id > t2.goods_id;查看全部
-
2.自身连接 同一个数据表对其自身进行连接。 SELECT s.type_id, s.type_name, p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id; SELECT * FROM tdb_goods_types; SELECT p.type_id, p.type_name, s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id; SELECT p.type_id, p.type_name, count(s.type_name) child_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;查看全部
-
1.无限分类数据表 SHOW COLUMNS FROM tdb_goods_cates; SELECT * FROM tdb_goods; CREATE TABLE tdb_goods_types( type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, type_name VARCHAR(20) NOT NULL, parent_id SMALLINT UNSINGED NOT NULL DEFAULT 0 ); INSERT tdb_goods_types(type_name, parent_id) VALUES('家用电器', DEFAULT); INSERT tdb_goods_types(type_name, parent_id) VALUES('电脑、办公', DEFAULT); INSERT tdb_goods_types(type_name, parent_id) VALUES('大家电', 1); INSERT tdb_goods_types(type_name, parent_id) VALUES('生活电器', 1); INSERT tdb_goods_types(type_name, parent_id) VALUES('平板电脑', 3); INSERT tdb_goods_types(type_name, parent_id) VALUES('空调', 3); INSERT tdb_goods_types(type_name, parent_id) VALUES('电风扇', 4); INSERT tdb_goods_types(type_name, parent_id) VALUES('电脑整机', 2); INSERT tdb_goods_types(type_name, parent_id) VALUES('笔记本', 9); INSERT tdb_goods_types(type_name, parent_id) VALUES('CPU', 10); SHOW COLUMNS FROM tdb_goods_types; SELECT * FROM tdb_goods_types;查看全部
-
1.外连接 A LEFT JOIN B join_condition 数据表B的结果集依赖于数据表A。 数据表A的结果集根据左连接条件依赖所有数据表(B表除外). 左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下). 如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录, 将生成一个所有列为空的额外的B行。 SELECT goods_id, brand_name FROM tbl_goods AS g RIGHT JOIN tbl_goods_brands AS b ON g.brand_name = b.brand_name; 如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试一下操作:col_name IS NULL, 如果col_name被指定为NOT NULL，MySQL将在找到符合连接着条件的记录后停止搜索更多的行.查看全部
-
多表连接 SELECT goods_id, goods_name, cate_name, brand_name, goods_price FROM tdb_goods as g INNER JOIN tdb_goods_cates as c ON g.cate_id=c.cate_id INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id;查看全部
-
左外连接 - 显示左表的全部记录及右表符合连接条件的记录 SELECT goods_id, goods_name, cate_name FROM tdb_goods AS g LEFT JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id; SELECT goods_id, goods_name, cate_name FROM tdb_goods AS g RIGHT JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id;查看全部
-
子查询与连接——一INNER JOIN 1、内连接：在MySQL中JOIN,INNER JOIN,CROSS JOIN是等价的;仅显示符合连接条件的记录。 2、外连接：LEFT JOIN左外连接；RIGHT JOIN右外连接 3、连接条件：使用ON设定连接条件，也可以用WHERE代替 SHOW COLUMNS FROM tdb_goods_cates; SELECT goods_id, goods_names, cate_name FROM tdb_goods AS g INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id; SELECT * FROM tdb_goods_cates; · ON：设定连接条件查看全部
-
1.连接 MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。 语法结构 table reference A {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference B ON condition_expr 2.数据表参照 table_reference tbl_name [[AS] alias] | table_subquery [AS] alias 数据表可以使用tbl_name AS alias_name 或 tbl_name alias_name赋予别名。 table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。查看全部
-
多表更新之一步更新 CREATE ... SELECT 在创建数据表的同时将查询结果写入到数据表 CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement; SELECT brand_name FROM tdb_goods GROUP BY brand_name; CREATE TABLE tdb_goods_brands( brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, brand_name VARCHAR(40) NOT NULL ) SELECT brand_name FROM tdb_goods GROUP BY brands_name; SELECT * FROM tdb_goods \G; SELECT * FROM tdb_goods_brands; SHOW COLUMNS FROM tdb_goods; SHOW COLUMNS FROM tdb_goods_brands; UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name SET brand_name = brand_id; //有错, brand_name定义模糊不清 UPDATE tdb_goods AS INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id; SELECT * FROM tdb_goods\G; SHOW COLUMNS FROM tdb_goods; ALTER TABLE tdb_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL, CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL; SHOW COLUMNS FROM tdb_goods; SELECT * FROM tdb_goods \G;查看全部
-
1.多表更新 参照另外的表来修改本表 UPDATE table_references SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}] ... [WHERE where_condition]; UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id; SELECT * FROM tdb_goods \G; 2.连接类型 INNER JOIN 内连接 在MySQL中, JOIN, CROSS JOIN和INNER JOIN是等价的. LEFT [OUTER] JOIN, 左外连接 RIGHT [OUTER] JOIN, 右外连接 2.表的参照关系 table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table reference ON condition_expr查看全部
-
INSERT ... SET ...可以使用子查询 SELECT * FROM tdb_goods \G; CREATE TABLE IF NOT EXISTS tdb_goods_cates( cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, cate_name VARCHAR(40) NOT NULL ); SELECT goods_cate FROM tdb_goods GROUP BY goods_cate; 将查询结果写入数据表 INSERT [INTO] tbl_name [(col_name,...)] SELECT ... SELECT * FROM tdb_gooods_cates; DESC tdb_goods_cates; INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate; SELECT * FROM tdb_goods_cates;查看全部
-
1.使用[NOT]IN的子查询 语法结构 operand comparison_operator [NOT] IN (subquery) =ANY运算符与IN等效; ！=ALL或<>ALL运算符与NOT IN等效。 SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price =ANY (SELECT * FROM tdb_goods WHERE goods_cate='超极本'）; SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price IN (SELECT * FROM tdb_goods WHERE goods_cate='超极本'）; SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price =!ALL (SELECT * FROM tdb_goods WHERE goods_cate='超极本'); SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT * FROM tdb_goods WHERE goods_cate='超极本'）; 2.使用[NOT] EXISTS的子查询 - 用的比较少 如果子查询返回任何行, EXISTS将返回TRUE；否则为FALSE查看全部
-
1.使用比较运算符的子查询 =、》、《、》=、《=、《》、！=、《=》 语法结构 operand comparison_operator subquery. SELECT AVG(goods_price) FROM tdb_goods; SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods; SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price >= 5636.36; SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods); SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本'; SELECT * FROM tdb_goods WHERE goods_cate='超极本'; SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price > (SELECT * FROM tdb_goods WHERE goods_cate='超极本'）;//有错 用ANY、SOME、ALL修饰的比较运算符 operand comparison_operator ANY (subquery) 符合其中的一个 operand comparison_operator SOME (subquery) operand comparison_operator ALL (subquery) 符合所有 SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price > ANY(SELECT * FROM tdb_goods WHERE goods_cate='超极本');查看全部
举报
0/150
提交
取消