MySQL,-----------17

MySql语句规范：

①关键字和函数名称全部大写
②数据库名称、表名称、字段名称全部小写
③SQL语句必须以分号结尾;
==============================================================================================================================

常用语句:

SELECT VERSION(); 查询当前服务器版本
SELECT NOW(); 查询当前日期时间
SELECT USER(); 查询当前用户

1.创建数据库CREATE ｛DATABASE | SCHEMA｝ [IF NOT EXITSTS] db_name [DEFAULT] CHARACTER SET [=] charset_name2.查看数据表列表SHOW {DATABASES | SCHEMA} 3.显示创建数据库列表 SHOW CREATE DATABASE 表名;4.修改数据库编码方式ALTER {DATABASE | SCHEMA} [表名] [DEFAULT] CHARACTER SET [=] charset_name;5.删除数据库DROP {DATABASE | SCHEMA} [IF EXISTS] db_namePs1：大括号：必选项；中括号：可选项（即有或没有）；|：选择其中。Ps2：不推荐使用“IF [NOT] EXISTS”，因为直接ERROR会有说明原因，无需多此一举“SHWO WARNINGS”。Ps3：如果修改的时候，数据库若没有进入（USE db_name;），则必须要写db_name，若进入了，可以省略不写。

Ps：设置主键的时候，可以写“PRIMARY KEY”也可以直接写“KEY”（PRIMARY KEY==KEY）以及自动编号一定要与主键一起出现，而主键不一定要与自动编号一起出现。

Ps：PRIMARY KEY与UNIQUE KEY区别：PRI一张表中只能出现一次且NOT NULL，而UNI一张表中可以出现多次且可以NULL（根据UNI的唯一性，也只能拥有一个该字段为NULL的记录）。

Ps：主键、外键、非空、唯一、check，default都是约束条件。主键、外键、既可以作为表级约束，也可作为列级约束，而像非空、唯一等（ not null， default ）只有列级约束。

=============================================================================================================

在 My.ini 文件中编辑默认的存储引擎：default-storage-engine=INNODB；显示创建表的语句：SHOW CREATE TABLE table_name；查看表是否有索引：SHOW INDEXES FROM table_name；以网格查看表是否有索引：SHOW INDEXES FROM table_name\G。添加父表：mysql> CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pnane VARCHAR(20) NOT NULL);

@MySQL——添加/删除列

1、添加单列：如果指定FIRST，则在整个表的最前方，默认不写为整个表的最后方；如果指定AFTER col_name，则在col_name的后面。ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST AFTER col_name]2、添加多列：无法指定FIRST/AFTER、只能默认为最后方。ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)3、删除单列ALTER TABLE tbl_name DROP [COLUMN] col_name4、删除多列（Ps：删除一列的同时，新增一列。其间用逗号隔开）ALTER TABLE tbl_name DROP [COLUMN] col_name, DROP [COLUMN] col_name,DROP [COLUMN] col_name

====================================================================================================

MySQL——添加约束

1、添加主键约束：ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_column_name,...);2、添加单个唯一约束：ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEXKEY] [index_name] [index_type] (index_column_name,...);3、添加多个唯一约束：ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEXKEY] [index_name] [index_type] (index_column_name,...),ADD [CONSTRAINT [symbol]] UNIQUE [INDEXKEY] [index_name] [index_type] (index_column_name,...);4、添加外键约束：ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [idnex_name] (index_column_name,...) REFERENCES table_name1(index_column_name);5、添加/删除默认约束：ALTER TABLE table_name ALTER [COLUMN] column_name {SET DEFAULT literalDROP DEFAULT}Ps1：CONSTRAINT 可加可不加，加该关键字后还可以选择添加主键别名；Ps2：唯一约束可以有多个，但主键约束有且只能有一个。

@MySQL——删除约束

1、删除主键约束：ALTER TABLE table_name DROP PRIMARY KEY;2、删除唯一约束：ALTER TABLE table_name DROP {INDEXKEY} index_name;3、删除外键约束：ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;Ps1：唯一约束的 index_name 可通过 SHOW INDEX FROM table_name\G; 查询。Ps2：外键约束的名字 fk_symbol 可通过 SHOW CREATE TABLE table_name; 查询。Ps3：INDEX：index是索引标识，和create table name 中的table是相同的标识。

@MySQL——修改列定义和更名数据表

1、修改列定义（列类型/列位置）ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST AFTER col_name];2、修改列名称ALTER TABLE tbl_name CHANGE [COLUMN] col_name new_col_name column_definition [FIRSTAFTER col_name];3、数据表更名方法1：ALTER TABLE tbl_name RENAME [TO/AS] new_tbl_name方法2：RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...Ps1：修改数据类型（特别是大类型转到小类型）,需注意数据丢失的问题。Ps2：尽量不要修改数据表名和列名，以免影响后台等问题。

===========================================================================================================

@MySQL——插入记录INSERT

INSERT [INTO] table_name [(column_name,...)] {VALUESVALUE} ({exprDEFAULT},...),(...),...;/**column_name 表示列名expr 表示表达式DEFAULT 可直接书写，表示默认值当字段有自动增加的属性后，在插入表的所有字段的数据时，可以填写 NULL 或 DEFAULT 这两个关键字，表示按照原先默认自动增长的属性插入多行数据时，可以用逗号隔开*/Eg：INSERT INTO table_name VALUE(DEFAULT,'BeforeDayBreak',md5('123'),DEFAULT,0),(NULL,'Superman',md5('123'),DEFAULT,0);Ps1：默认约束、非空约束不存在表级约束，只能为列级约束，而主键约束、唯一约束、外键约束既存在表级约束，也存在列级约束。Ps2：md5加密函数，mysql自带的。

@MySQL——插入记录SET-SELECT

第一种：insert [column_name]values(...)[,(...),(....)];--比较常用，可以一次性插入多条记录，并且可以输入表达式甚至是函数，但是无法进行子查询。第二种：insert tb_name set column_name={exprdefault};--可以进行子查询，但是只能插入一条记录。第三种：insert table_name [(colname...)] select.....--这种方法就是把查询到的结果插入到指定数据表中。

==================================================================

@MySQL——表单更新记录UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={exp1DEFAULT}[,col_name2=...][WHERE where_condition];例子：不指定位置：UPDATE users set age = age + 5 - id,sex = 0; //使表里所有记录的age加5并且减去当前id值指定位置： UPDATE users set age = age+ + 10 WHERE id % 2=0; //取id为偶数的位置

删除表两种：单表&多表。@单表删除：DELETE FROM 表名 (WHERE 条件;//删除后，添加记录，自动编号是已有编号的最大加1。

==================================================================

@MySQL——查询表达式解析

语法：SELECT select_expr [,select expr2...] 只查找某一个函数或表达式[FROM table_references 查询表名[WHERE where_conditon] 查询条件[GROUP BY {col_nameposition} [ASCDESC],...] 按某个字段进行分组，相同的只显示第一个[HAVING where_conditon] 分组时，给出显示条件[ORDER BY {col_nameexprposition} [ASCDESC],...] 排序[LIMIT {[offset,]row_countrow_count OFFSET offset}] 限制返回数量}

@MySQL——group by语句对查询结果分组

查询结果分组（多个分组，用“，”隔开）：[group by{col_nameposition} [ASCDESC],...]Eg：-select sex from users group by sex;-select sex from users group by 1;(这里的一表示查询的第一个字段，这里查询sex，第一个字段就是sexd，也就是会按照字段进行分组)

@MySQL——having语句设置分组条件

分组条件：利用GROUP BY分组，添加分组条件 [HAVING where_condition] （要么为一个聚合函数【SUM();MAX();etc...】，要么出现在 SELECT 条件中，聚合函数永远只有一个返回结果）[HAVING when where_condition]

@MySQL——order by语句对查询结果排序

1、对查询结果进行排序：[ORDER BY [col_name expr position } [ASCDESC],...]elect * from user order by id desc;2、可以同时按多条字段进行排序，规则是先按前面的字段排，在基础上再按后面字段排。3、如：SELECT * FROM users ORDER BY age,id DESC; 先按照age排序，如果age有重复的，重复的字段里按id排序。

@MySQL——limit语句限制查询数量

-SELECT * FROM users LIMIT 2; // 一个数字限制查询结果数量为 2 条,从最开始算起。-SELECT * FROM users LIMIT 2,3 ;// 从第三个开始（第一个为0），返回三条。-第三种INSERT，数据从一个表插入到另一个表：INSERT test SELECT username FROM users WHERE age >=30; // 字段不匹配提示INSERT test（username） SELECT username FROM users WHERE age >=30;（与前两种相比，这种方法是使用了子选择，这样就相当于将A表中满足条件的数据导入了B表，无需手动写了）-mysql分页要用到：[LIMIT{[offset,]row_countrow_count OFFSET offset}]

================================================================

@子查询注意事项

（1）子查询指嵌套在查询内部，且必须始终出现在圆括号内。（2）子查询可以包含多个关键字或条件，如：DISTINCT / GROUP BY / ORDER BY / LIMIT / 函数等。（3）子查询的外层查询可以是：SELECT, INSERT, UPDATE , SET 或DO（4）子查询可以返回标量、一行、一列或子查询。<span style="font-size:24px;"></span>

===============================================================

@MySQL——由比较运算符引发的子查询

1、使用比较运算符来子查询：SELECT id,name,price FROM goods WHERE price >= (SELECT ROUND(AVG(price),2) FROM goods);2、在子查询时的关键字ANY / SOME / ALL：select goods_name,goods_price from tdb_goods where goods_price >= ANY (select goods_price from tdb_goods where goods_cate='超级本');

@MySQL——由[NOT] IN/EXISTS引发的子查询

使用[NOT]IN的子查询：语法结构：operand comparison_operator[NOT]IN(subquery)=ANY 等价于 IN!=ALL或<>ANY等价于NOT IN使用[NOT]EXIST引发的子查询 （不常用）

@MySQL——使用INSERT...SELECT插入记录

语法：INSERT [INTO] tbl_name [(col_name,...)] SELECT ...//将查询结果写入数据表INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;//在表tdb_goods_cates中插入tdb_goods中的cate种类。

@MySQL——多表更新

语法：UPDATE table_references SET col_name1={expr1 DEFAULT} [,col_name2={expr2 DEFAULT}]... [WHERE where_condition]Eg：update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name set goods_cate=cate_id;//将 tdb_goods 表和 tdb_goods_cates 表通过 goods_cate=cate_name 连接，然后更新 goods_cate=cate_id 

@MySQL——多表更新之一步到位

建表、查询、写入三合一：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 brand_name;多表更新：UPDATE tdb_goods INNER JOIN tdb_goods_brands ON tdb_goods.brand_name = tdb_goods_brands.brand_nameSET tdb_goods.brand_name = tdb_goods_brands.brand_id;通过ALTER TABLE语句修改数据表结构ALTER TABLE tdb_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;<span style="font-size:18px;"></span>

@MySQL——内连接INNER JOIN

1、内连接：在MySQL中JOIN,INNER JOIN,CROSS JOIN是等价的2、外连接：LEFT JOIN左外连接；RIGHT JOIN右外连接3、连接条件：使用ON设定连接条件，也可以用WHERE代替· ON：通常使用它设定连接条件· WHERE：进行结果集记录的过滤4：内连接是返回左表及右表符合连接条件的记录

==================================================================

@MySQL——无限级分类表设计【重点】

显示表：show clumns from 表名;无限分类：即在同一张表中既有父类，又有子类。通过在分类表中再增加多一个字段标识其属于哪一个父类的 ID 来实现。可以通过对同一张数据表的自身连接来进行查询，需要对表标识别名。查找父级对应的名称：select s.type_id ,s.type_name,p.type_name As parent_id from tdb_goods_types s left join tdb_goods_types p on s.parent_id=p.type_id;查找子级对应的名称：select p.type_id ,p.type_name,s.type_name from tdb_goods_types p left join tdb_goods_types s on p.type_id=s.parent_id;查找有多少子级：select p.type_id ,p.type_name,COUNT(s.type_name) child_count from tdb_goods_types p left join tdb_goods_types s on p.type_id=s.parent_id GROUP BY p.type_name ORDER BY p.type_id;

@MySQL——多表删除

1、查找重复记录：SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;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 = t2.goods_name WHERE t1.goods_id > t2.goods_id;

==================================================================================================================

@MySQL——字符函数 I

（1）CONCAT()：字符连接SELECT CONCAT('IMOOC','-','MySQL');//IMOOC-MySQLSELECT CONCAT (first_name,last_name) AS fullname FROM test;（2）CONCAT_WS()：使用指定的分隔符进行字符连接，（第一个位置指定分隔符，后面的为分割的内容）SELECT CONCAT_WS('%','abc','def'); //第一个是指定的分隔符；//abc%def（3）FORMAT()：数字格式化SELECT FORMAT(123560.75,2); //123,560.75;第二个参数若为0，则显示整数（4）LOWER()：转化小写（5）UPPER()：转换大写SELECT UPPER('mysql');//MYSQL（6）LEFT()：获取左侧字符（7）RIGHT()：获取右侧字符SELECT LEFT ('MYSQL',2); //MYSELECT LOWER(LEFT('mYsql',2)); //函数嵌套；//my

@MySQL——字符函数 II

（8）LENGTH()：取得字符串长度LENGTH('MYSQL ’);//6；最后加了个空格（9）LTRIM()：清空字符串前面（左端）空格（10）RTRIM()：清空字符串末尾（右端）空格（11）TRIM()：删除前导、后续空格或者指定字符SELECT TRIM(' MySQL ');SELECT TRIM(LEADING '?' FROM '??MYSQL???'); //删除前导？SELECT TRIM(TRAILING '?' FROM '??MYSQL???'); //删除后续？SELECT TRIM(BOTH '?' FROM '??MYSQL???'); //前后均删除，但不能删除中间的?（12）REPLACE()：替换SELECT REPLACE('MY???SQL','?',''); //将问号换成空白，即去掉？SELECT REPLACE('MY???SQL','?','~~'); //可以将n个？换成m个组合符号（13）SUBSTRING()：字符串截取（从1开始，编程里可能从0开始）SELECT SUBSTRING('mYSQL',1,2); //从字符串中的第一个位置开始截取2个字符mYSELECT SUBSTRING('mYSQL',3); //SQL 从第3的位置开始截取到结束SELECT SUBSTRING('mYSQL',-2); //QL 从倒数第2位开始截取到结束SELECT SUBSTRING('mYSQL',-3,-1); //F，长度不能是负的，但是有些编程语言支持，而mysql不支持（14）[NOT] LIKE：模式匹配（百分号%代表任意个字符； 下划线_代表任意一个字符）例子："tom%"SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1'; // 去除'1'后面的编译，查找包含'%'符号的信息

@MySQL——数值运算符和函数

SELECT CEIL(3.01) ==>4 /*有n.xx 都是n+1 进一取整 向上取整SELECT FLOOR(3.99) ==>3 /*取n.xx 都是n 舍一取整 向下取整SELECT 3 DIV 4 ==> 0 /*整数除法SELECT 3/4 ==>0.75 /*除法SELECT 21 MOD 2 ==>1 /*取余数(取模)整数,小数都可以SELECT 21 % 2 ==>1 /*取余数(取模)整数,小数都可以SELECT POWER(3,4) ==>81 /*3的4次方 幂运算SELECT ROUND(3.1415926,4) ==>3.142 /*四舍五入SELECT TRUNCATE(123.89,1) ==>123.8 /*截取小数点后的位置SELECT TRUNCATE(123.89,0) ==>123 /*截取小数点后的位置,0位为整数部分SELECT TRUNCATE(123.89,-1) ==>120 /*截取-1,从个位起去掉后面的数值替换为0