MYSQL（一）

零、安装与配置

参考网址：Window10下Mysql的安装和配置。_自在如风。的博客-CSDN博客_window10 安装mysql

补充：

下载时选择大的安装包，不选择小的web版

DBMS：数据库管理系统

SQL：结构化查询语言，不区分大小写，搜索时根据DBMS的设置是否区分大小写，要以【；】结尾

一、基本操作

mysql -u root -p 进入sql

show databases ; 显示所含有的数据库

use xxx；使用xxx数据库

show tables ；显示所含有的表

describe xxx;显示数据库中某表的信息，等价于show columns  from xxx；

二、检索

select CountryCode from city; 检索单列

 select CountryCode,Name from city;检索多列，以【，】分隔

 select * from city; 检索所有列，利用了*表示所有

三、排序

 select CountryCode from city order by CountryCode;按单个列排序，可以用显示的数据排序，也可以用不显示的数据

select CountryCode,Name from city order by Name,District;按多列排序，排序时有优先级

select CountryCode,District from city order by 1；按select时列的相对位置排序，等价于select CountryCode,District from city order by CountryCode。注意这里的相对位置排序是从1开始，非0.

select ID,Name from city order by ID DESC limit 2,3;DESC按照降序排列，默认按照升序排序，【limit 2,3】限制只显示第2行之后开始的3列，注意这里是第2行之后（不包含第二行）

四、操作符与过滤

 select ID,Name from city  where ID <5; 过滤条件为ID小于5

其他过滤条件：=  ！> between     is NUll（无值，与包含0、空字符串或仅仅包含空格不同）

select ID,Name from city  where ID between 2 and 4;检查值范围

select ID,Name from city where  ID<100 and Name ='Herat'; 操作符除AND外还有OR、IN、NOT

注意：SQL处理操作符时先与后或（先and后or），需要带上必要的括号，规范的同时消除歧义

select ID,Name from city where  ID  in (2,22,222);IN操作符指定了范围

select ID,Name from city where NOT ID >4;注意NOT的位置，否定后跟的关键字

五、通配符与过滤

LIKE 属于操作符

%表示任何字符出现任意次数（包含0个），Microsoft Access使用*

_匹配单个字符，Microsoft Access使用？

[]匹配指定位置的一个字符，方括号中为集合,不是所有DBMS都支持

^脱字号 进行否定，LIKE  '[^JM]% ' ，Microsoft Access使用[!JM]

示例：

select ID,Name from city where Name like 'Her%';

注意：尽量不要用通配符，时间长

六、计算字段

+ ,拼接两个列，Access，SQL server，Sybase适用

||，拼接两个列，DB2、Oraclc、PostgreSQL、Sybase适用

CONCAT，拼接，MYSQL适用 

示例：

select CONCAT(ID,'(',Name,')') from city where ID <5;

RTRIM（）去掉右边空格，LTRIM（）去掉左边空格，TRIM（）去掉两边空格

as 使用别名

+-*/算数计算

七、函数

提取串的组成部分 SUBSTRING（）

数据类型转换 CONVERT（）

提取当前日期 CURDATE（）

文本处理函数：

SOUNDEX是语音信息，可用于查找类似发音的项 select XXX from XXX where SOUNDEX(XXX)=SOUNDEX(XXX)

举个例子：使用upper函数返回大写的信息

数值处理函数：

八、聚集

聚集函数：

举个组合聚集参数的例子：select AVG(id)， MAX(id)  from city where id <5;

DISTINCT参数：聚集不同的值，不给参数实际是ALL参数，对所有的行执行计算

举个例子：select AVG(DISTINCT id) from city where id <5;

九、分组

group by 字句创建分组

顺序：

group by 可用相对位置，如上语句，可修改为select CountryCode,Count(*)as num_CC from city where id <50 group by 1 ;

有时需要count一下别的列符合某些条件的数量,如下例，需要统计每个doc下it为5和it为6的数量，‘OR NULL’不可省略，否则不区分0和1无法统计正确个数。

select doc,Count(it = 05 OR NULL)as it5,Count(it = 06 OR NULL)as it6 from city  group by doc ;

having 过滤分组

相对的，where在分组前过滤行

举个例子：select CountryCode,Count(*)as num_CC from city group by 1 HAVING num_CC > 200 ;

再举个例子：where group having 都有

十、子查询

含义：嵌套在其他查询中的查询

形式一：先处理内查询后处理外查询,本质上是将一个select的返回结果作为另一个select的条件

select XXX from XXX where XXX IN（select XXX from XXX where XXX）

注意：只能查询单个列

形式二：对外查询的每个结果建立一次子查询

select  XXX,(select XXX from B where B.xxx=A.xxx)as XXX from A order by xxx

这种A.xxx的写法叫做完全限定列名

十一、联结（join）表

设计原因：信息分解，避免时间空间浪费及一改多改，数据一致性。理论上似乎可以实现子查询的功能？

举个例子（内部联结、等值联结）：

select A.a，A.b，B.c from TableA as A，TableB as B where A.xxx =B.xxx

等同于：

select XA，XB，XC from TableA INTER JOIN TableB ON TableA.xxx =TableB.xxx

不使用联结返回笛卡尔积，即第一个表的行数乘以第二个表的行数

联结多个表时，用AND加条件，

十二、高级联结

除列有列别名外，表也有表别名：

select XXX from YYY as ZZZ

自联结：联结的两个表是同一个，as时用了不同的别名

自然联结：虽然联结时使用了相同的列，但展示时排除多次出现，使每个列只出现一次

举个例子：select A.*,B.xxx from city as A,countrylanguage as B where A.CountryCode =B.CountryCode limit 2,4;

通过对A.*,B.xxx进行控制，可实现每个列只返回一次

内部联结：

select XXX from XXX INTER JOIN XXX ON XXX=XXX；

外部联结：

可包含没有关联行的行

select XXX from XXX LEFT OUTER JOIN XXX ON XXX=XXX；

LEFT、RIGHT指定包含其所有行的表，RIGHT指右边的表，LEFT指左边的表

FULL OUTER JOIN  ： 全外部联结

*=     ：左外部联结操作符，从左边表检索所有行

select XXX from A，B where A*=B；

=*    ：右外部联结操作符

十三、组合查询

union 组合不同select的结果集，自动去重

union all 返回所有匹配行，不去重

对最终结果可用一个order进行排序

十四、增

insert into 插入单行数据

举个例子：INSERT INTO city VALUES(10000,'testname','NLD','testD',66666);

插入有时间的一行：INSERT INTO city VALUES(10000,'testname','NLD','testD',66666,'2015-1-3 00:01:02');

给出列名的方法：INSERT INTO city(id,name,countrycode,district,population) values(10001,'TN','nld','testd',667)

注：给出列名的方法可以不给出所有的列，插入部分行，但某些缺少会报错。比如上面的数据库Population可以缺失，countrycode不可缺失。这是由于Population给出了默认值0。

insert select 从一个表中插入多行数据

可实现从一个表中读取数据并插入新表中，这里select实现的是检索多行而不是列出，举个例子：

select into 将一个表的内容复制到一个全新的表，包含表的创建及填充

注：复制表进行SQL语句的测试，可不影响实际的原始数据

十五、改

更新单个列：update city set CountryCode='NLD' where ID>10000;

更新多个列：update city set CountryCode='NLD'，Population=666  where ID>10000;

update 指定表，set指定要更新的列

注意：删除某列的值，可设置为NUll（若允许为空）

十六、删

delete from city where id=10003;

注意：删改若不使用where将应用到所有行

十七、创建和操纵

creat table 创建表：

create table newtables(key1  char(20) NOT NULL,key2  int(11) NOT NULL);

这里列名用了key1，key2，实际不是describe时候的key

注意：是create 不是creat

注意：NOT NULL是指不允许插入或更新行时，该列没有值或缺值，如果不要求NOT NULL的话，创建时不写NOT NULL就行了，不指定则认为指定的是NULL。

default 指定默认值：

create table newtables2(key1  char(20) NOT NULL,key2  int(11) NOT NULL DEFAULT 666);

alter table更新表（add增加列）：

alter table newtables2 add newkey char(20);

alter table更新表（drop column删除列）：

alter table newtables2 drop column key2;

alter table更新表（rename重命名表）：

alter table newtables rename newtable;

drop table 删除表：

drop table newtables2;

十八、主键&外键

PRIMARY KEY主键

在创建时指定为PRIMARY KEY 即为设定该列为主键

REFERENCES外健：

值必须在另一个表的主键列出

UNIQUE唯一约束

十九、查看端口

show global variables like 'port';

二十、转义

存储或查询时，需要对单引号及\反斜杠进行转义。

二十一、索引

创建表时增加索引

create table newtables(

col1  char(20) NOT NULL comment 'xxx',

col2  int(11) NOT NULL  comment 'yyy',

col3  int(11) NOT NULL  comment 'zzz',

PRIMARY KEY(列名),

KEY 索引名(列名) using btree

);

对某个表增加索引

alter table 表名 add index 索引名(列名);

creat index 索引名ON 表名（列名）

对某个表增加联合索引进行速度优化

alter table 表名 add index 索引名(列名a,列名b) using btree comment('索引注释');

creat index 索引名ON 表名（列名a,列名b）

对某个表删除索引

alter table 表名 drop index 索引名;

drop index 索引名 on 表名;

其他

check检查约束

使一个或一组列的数据满足一组条件，如最大、最小、范围、特定几个值

trigger触发器

与insert、update delete相关联，比如转换为大写

附录：常见报错

1.You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 。。。

语法错误，比如我把insert into 打成了insert info

2. Data too long for column 'CountryCode' at row 1

数据太长了，修改对应列的数据

3. Cannot add or update a child row: a foreign key constraint fails

关键词错误，修改对应列的数据。比如deasribe 表的时候，显示这个列的key是MUL ，就不能瞎写

4.insert单行插入数据

 可能是不能缺失某列数据