MySQL基础知识（一）

数据库的创建和修改

• 创建数据库：create database 数据库名;
• 查看已创建数据库：show databases;
• 查看已创建数据库信息：show create database 数据库名; （整理结果在数据库名后面加上\G）
• 修改数据库的编码：alter database 数据库名 default character set 编码方式 collate 编码方式_bin; (如：utf8)
• 删除数据库：drop database 数据库名;

---

数据表的基本操作

• 想操作表需要先使用数据库（use 数据库名;）

• 创建数据表：create table 表名( 字段名1 数据类型 完整性约束条件, 字段名2 数据类型 完整性约束条件, 字段名3 数据类型 完整性约束条件)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

• 查看已创建数据表信息：show create table 表名; （整理结果在表名后面加上\G）

• 查看表的字段信息(describe)：describe 表名; 或者 desc 表名;

• 修改表名：alter table 旧表名 rename [to] 新表名; (to是可选的，是否出现不影响语句的执行)

• 修改字段名：alter table 表名 change 旧字段名 新字段名 新数据类型;

• 修改字段的数据类型：alter table 表名 modify 字段名 新数据类型;

• 添加字段：alter table 表名 add 新字段名 数据类型 [约束条件] [first|after 已存在的字段名]；
  （first表示将字段设置为表的第一个字段，after 已存在的字段名表示将字段添加到指定字段后面）

• 删除字段：alter table 表名 drop 字段名;

• 修改字段的排列位置：alter table 表名 modify 字段名1 数据类型 first|after 字段名2;
  （first表示将字段1设置为表的第一个字段，after 字段名2表示将字段1添加到指定字段2后面）

• 删除数据表：drop table 表名;

• 表的约束：

  • primary key–主键约束，用于唯一标识对应的纪录–不能重复且不能为null
  • foreign key–外键约束 not null–非空约束 unique–唯一性约束–不能重复
  • default–默认值约束，用于设置字段的默认值–(字段名 数据类型 default 默认值)
  • 注：主键约束分为单字段主键和多字段主键。单字段主键指由一个字段构成的主键（语法格式：字段名 数据类型 primary key），多字段主键指有多个字段组合而成的主键（语法格式：primary key(字段名1,字段名2,字段名3)），每个数据表中最多只能有一个主键约束，定义为主键的字段不能有重复值且不能为null值。

• 设置表的字段值自动增加：auto_increment–约束的字段可以是任何整数类型，默认情况下从1开始自增

• 索引：

  • 普通索引（由key或index定义，可以创建在任何数据类型中）
  • 唯一性索引（由unique定义，字段值必须唯一）
  • 全文索引（由fulltext定义，只能创建在char、varchar、text类型字段上，而且只有MyISAM存储引擎支持）
  • 单列索引（指在表中单个字段上创建索引，可以是普通索引、唯一性索引或全文索引，只要保证该索引只对应表中一个字段即可）
  • 多列索引（指在表中多个字段上创建索引，只有在查询条件中使用了这些字段中的第一个字段时该索引才会被使用）
  • 空间索引（由spatial定义，只能创建在空间数据类型的字段上，MySQL空间数据类型：geometry、point、linestring和polygon。创建空间索引的字段必须为not null，并且只能在MyISAM存储引擎的表中创建）

• 创建索引：

• 创建表的时候创建：

  create table 表名( 字段名 数据类型 约束条件， 字段名 数据类型 约束条件， key|index|unique|fulltext|spatial [别名] (字段名1[(长度)] [asc|desc]) );

  • 注：别名：可选参数，表示索引的名称。字段名1：表示索引对应的字段名称。长度：可选参数，表示索引的长度。asc和desc：可选参数，asc表示升序排列，desc表示降序排列。查看索引是否被使用：explain select * from 表名 where id=1 \G;

• 使用create index语句在已经存在的表上创建索引：create unique|fulltext|spatial|index 索引名 on 表名 (字段名[(长度)] [asc|desc]);

• 使用alter table语句在已经存在的表上创建索引：alter table 表名 add unique|fulltext|spatial|index 索引名(字段名[(长度)] [asc|desc]);

• 删除索引：由于索引会占用一定的磁盘空间，所以删除不再使用的索引。
  1、使用alter table删除索引：alter table 表名 drop index 索引名;
  2、使用drop index删除索引：drop index 索引名 on 表名;

---

数据的基本操作

添加数据

• insert语句指定所有字段名：insert into 表名(字段名1,字段名2,...) values(值1,值2,...);
• insert语句不指定字段名：insert into 表名 values(值1,值2,...); ----不指定字段名就需要为所有字段添加值
• 为表的指定字段添加数据：insert into 表名(字段1,字段2,...) values(值1,值2,...);
• insert语句的其他写法：insert into 表名 set 字段名1=值1,字段名2=值2,...;
• 同时添加多条记录：insert into 表名(字段名1,字段名2,...) values(值1,值2,...), (值1,值2,...), ..., (值1,值2,...);

更新数据

• update 表名 set 字段名1=值1,字段名2=值2,... where 条件表达式; -----使用where更新部分数据，不用where更新全部数据

删除数据

• delete from 表名 where 条件表达式;
• 使用truncate删除表中所有数据：truncate [table] 表名;
• delete语句和truncate语句的区别：
  • delete语句是DML语句，truncate语句通常被认为是DDL语句；
  • delete语句通过where可以删除部分记录，而truncate语句只能用于删除表中所有记录；
  • truncate语句删除表中的数据后，再次向表中添加记录时，自动增加字段的默认初始值重新由1开始，而使用delete语句删除表中记录后，再次向表中添加记录时，自动增加字段的值为删除时该字段的最大值加1；
  • 使用delete语句时，每删除一条记录都会在日志中记录，而使用truncate语句时，不会在日志中记录删除的内容，因此truncate语句的执行效率比delete语句高。

---

单表查询

简单查询

• select语句：select [distinct] *|{字段名1,字段名2,...} from 表名 [where 条件表达式1] [group by 字段名 [having 条件表达式2]] [order by 字段名 [asc|desc]] [limit [offset,] 记录数];

  • 注：distinct为可选参数，用于剔除查询结果中重复的数据；
  • 通配符*表示表中所有的字段；
  • group by为可选参数，用于将查询结果按照指定字段进行分组；
  • having为可选参数，用于对分组后的结果进行过滤；
  • order by为可选参数，用于将查询结果按照指定字段进行排序，排序方式由asc|desc控制，不指定默认为升序；
  • limit为可选参数，用于限制查询结果的数量；
  • offset为可选值，表示偏移量，如果偏移量为0，则从查询结果的第一条记录开始，偏移量为1，则从查询结果的第二条记录开始，以此类推，不指定默认值为0；
  • 记录数表示返回查询记录的条数。

• 查询所有字段：select * from 表名;或select 字段名1,字段名2,... from 表名;----查询记录时最好不要使用*通配符，虽然可以节省输入查询语句的时间，但由于获取的数据过多会降低查询的效率

• 查询指定字段：select 字段名1,字段名2 from 表名;

• 按条件查询

• 带关系运算符的查询：select 字段名1,字段名2,... from 表名 where 条件表达式;
  关系运算符：=、<>、!=、<、<=、>、>=(<>和!=都便是不等于)

• 带in关键字的查询：用于判断某个字段的值是否在指定集合中。select * from 表名 where 字段名 [not] in (元素1,元素2,..); --not为可选参数，表示查询不在指定集合范围内的记录

• 带between and关键字的查询：用于判断某个字段的值是否在指定范围内。select * from 表名 where 字段名 [not] between 值1 and 值2; ------not为可选参数，表示查询指定范围外的记录；值1为起始值，值2为结束值，值1<值2，否则查询不到结果，如…between 2 and 5；查询值为2、3、4、5的记录。

• 空值查询：用is [not] null判断值是否为空，空值不同于0，也不同于空字符串。select * from 表名 where 字段名 is [not] null;

• 带distinct关键字的查询：用于剔除查询结果的重复记录。select distinct 字段名 from 表名; —字段名表示要过滤重复记录的字段

• 带distinct关键字的查询(作用于多字段)：select distinct 字段名1,字段名2,.. from 表名; —只有指定的多个字段的值都有重复时才会过滤

• 带like关键字的查询：select * from 表名 where 字段名 [not] like '匹配字符串'; —not为可选参数，表示查询与指定字符串不匹配的记录；匹配字符串指定用来匹配的字符串，值可以是一个普通字符串，也可以是包含%和_的通配字符串。

  • 通配符%：匹配任意长度的字符串，包括空字符串。
    如：c%表示以c开始的任意长度的字符串(ct,cut,current);
    c%r表示以c开始以r结尾的任意长度的字符串(char,coutr);
    %h%表示包含h的任意长度的字符串(char,varchar,hi,hiante,length)
  • 通配符_：下划线通配符只匹配单个字符，如需匹配多个字符需使用多个下划线通配符。
    如：cu_表示cu开头的三个长度的字符串(cut,cup);
    c__l表示c开头l结尾的四个长度字符串(cool,coal);
    M_ _QL表示M开头QL结尾中间含有一个空格的字符串(只能匹配My SQL，不能匹配MySQL)。
  • 注：因为%和_在通配字符串中具有特殊含义，因此如果需要使用字符串%和_，需要使用\进行转义(\%、\_)。如：%\%%表示含有%的任意长度的字符串(cc%ui)

• 带and关键字的多条件查询：and可以连接多个查询条件，只有满足所有的条件的记录才会被返回。
  语句：select * from 表名 where 条件表达式1 and 条件表达式2 and 条件表达式3...;

• 带or关键字的多条件查询：使用or关键字连接多个条件时，只需满足其中一个条件即可返回查询结果。
  语句：select * from 表名 where 条件表达式1 or 条件表达式2 or 条件表达式3...;

• 带or和and关键字的多条件查询：or和and可以一起使用，需要注意，and的优先级高于or，因此它们一起使用时，先运算and两边的条件表达式，再运算or两边的条件表达式。如：select * from 表名 where gender='女' or gender=‘男’ and g=10;

高级查询

• 聚合函数：

  • count()返回某列的行数；
  • sum()返回某列值的和；
  • avg()返回某列的平均值；
  • max()返回某列的最大值；
  • min()返回某列的最小值。
    • count()函数：统计记录的条数，语法select count(*) from 表名;
    • sum()函数：select sum(字段名) from 表名; --返回指定字段所有值的总和
    • avg()函数：select avg(字段名) from 表名; --返回指定字段所有值的平均值
    • max()函数：select max(字段名) from 表名; --返回指定字段的最大值
    • min()函数：select min(字段名) from 表名; --返回指定字段的最小值

• 使用order by对查询结果排序：select * from 表名 order by 字段名1 [asc|desc],字段名2 [asc|desc],...; —默认选择asc升序排序；如果字段中有null值，null值会被认为是最小值。对多个字段排序时先对字段1排序，再对字段2排序。

• 使用group by分组查询：可以使用group by按照某个字段或者多个字段中的值进行分组，字段中值相同的为一组。
  语法：select * from 表名 group by 字段名1,字段名2,字段名3,... [having 条件表达式]; --having关键字指定的条件表达式对分组后的内容过滤；一般group by和聚合函数一起使用。

  • 单独使用group by分组：select * from 表名 group by 字段名; —返回的分组后的第一条记录的值
  • 和聚合函数一起使用：如select count(*),字段名 from 表名 group by 字段名; --按照字段名分组后各个值有多少条记录
  • 和having关键字一起使用：having和where关键字作用相同，都用于设置条件表达式对查询结果过滤，区别在于，having后面可以跟聚合函数，而where不能，通常having跟group by一起使用对分组后的结果进行过滤。
    如：select sum(grade),gender from 表名 group by grade having sum(grade)<300;

• 使用limit限制查询结果的数量：可以指定查询结果从哪一条记录开始以及一共查询多少条信息。
  语法：select * from 表名 limit [offset,] 记录数;
  如：select * from 表名 limit 4; ----返回表中的前4条记录
  select * from 表名 limit 4,4; —返回表中第5-8条记录，前一个4表示偏移量为4，后一个4表示查询4条记录
  select * from 表名 order by grade desc limit 4,4; --返回成绩降序的第5条记录开始后面的4条记录(第5-8条记录)

• 函数(列表)：

  • 数学函数：
    • abs(x)返回x的绝对值
    • sqrt(x)返回x的非负2次方根
    • mod(x,y)返回x被y除后的余数
    • ceiling(x)返回不小于x的最小整数
    • floor(x)返回不大于x的最大整数
    • round(x,y)对x进行四舍五入，小数点后保留y位
    • truncate(x,y)舍去x中小数点y位后面的数
    • sign(x)返回x的符号，-1、0或者1
  • 字符串函数：
    • length(str)返回str的长度
    • concat(s1,s2,…)返回一个或者多个字符串连接产生的新的字符串
    • trim(str)删除字符串两侧的空格
    • replace(str,s1,s2)使用字符串s2替换字符串str中所有的字符串s1
    • substring(str,n,len)返回字符串str的子串，起始位置为n，长度为len
    • reverse(str)返回字符串反转后的结果
    • locate(s1,str)返回子串s1在字符串str中的起始位置
  • 日期和时间函数：
    • curdate()获取系统当前日期
    • curtime()获取系统当前时间
    • sysdate()获取当前系统日期和时间
    • time_to_sec()返回将时间转换成秒的结果
    • adddate()执行日期的加运算
    • sbudate()执行日期的减运算
    • date_format()格式化输出日期和时间值
  • 条件判断函数：
    • if(expr,v1,v2)如果expr表达式为true返回v1，否则返回v2
    • ifnull(v1,v2)如果v1不为null返回v1，否则返回v2
    • case expr when v1 then r1 [when v2 then r2…] [else rn] end 如果expr值等于v1、v2等，则返回对应位置then后面的结果，否则返回else后的结果rn
  • 加密函数：
    • encode(str,pwd_str)使用pwd作为密码加密字符串str
    • decode(str,pwd_str)使用pwd作为密码解密字符串str
    • MD5(str)对字符串str进行MD5加密

• 为表和字段取别名 --该别名可以代替其指定的表和字段

  • 为表取别名：select * from 表名 [as] 别名; —as关键字用于指定表名的别名，可以省略（用 别名.字段名 表示原来的字段）
  • 为字段取别名：select 字段名 [as] 别名 [,字段名 [as] 别名,...] from 表名;

---

多表操作

外键 —外键是指引用另一张表中的一列或多列，被引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表之间的连接（引入外键后，外键列只能插入参照列存在的值，参照列被参照的值不能删除，这保证了数据的参照完整性）

• 为表添加外键约束：alter table 表名 add constraint FK_ID foreign key (外键字段名) references 外表表名 (主键字段名);

  • 注：FK_ID为外键名字，可以随便取，但名字不能加引号；建立外键的表必须是InnoDB型，不能是临时表，因为在MySQL中只有InnoDB类型的表才支持外键

• 创建表的时候为表添加外键约束：如下：
  create table teacher_course(tea_id int not null, cou_id int not null, foreign key(tea_id) references teacher(tea_id), foreign key(cou_id) references course(cou_id), primary key(tea_id,cou_id) );

• 添加外键约束的参数说明：如果主表中的数据被删除或修改，那么从表中对应的数据也该被删除或修改，否则数据库中会存在很多无意义的垃圾数据。MySQL可以在建立外键时添加on delete或on update子句告诉数据库怎么避免垃圾数据的产生
  语法：alter table 表名 add constraint FK_ID foreign key (外键字段名) references 外表表名 (主键字段名) [on delete cascade|set null|no action|restrict] [on update cascade|set null|no action|restrict];

  • 注：cascade(级联)：删除包含与已删除键值有参照关系的所有记录
    set null：修改包含与已删除键值有参照关系的所有记录，使用null值替换(不能用于已标记为not null的字段)
    no action：不进行任何操作
    restrict(约束)：拒绝主表删除或修改外键关联列。(在不定义on delete和on update子句时，这是默认设置，也是最安全的设置)
    NO ACTION和RESTRICT的区别：只有在及个别的情况下会导致区别，no action是在其他约束的动作之后执行，restrict具有最高的优先权执行。

• 删除外键约束：alter table 表名 drop foreign key 外键名;

操作关联表

• 关联关系：
  • 多对一(外键应该建立在多的一方，否则会造成数据的冗余)
  • 多对多(要实现这种关系需要定义一张中间表称为连接表，该表会有两个外键，连接表的两个外键可以重复，但两个外键之间的关系不能重复，所以称这两个外键又是连接表的联合主键)
  • 一对一(这种关联关系需要分清主从关系，从表需要主表的存在才有意义，在从表中建立外键的字段需要非空唯一)
  • 注：实际开发中，一对一并不常见，主要应用于：分割具有很多列的表；由于安全原因而隔离表的一部分；保存临时数据，并且可以毫不费力地通过删除该表面而删除这些数据
• 添加数据：

连接查询

• 连接查询：交叉连接查询(cross join)、内连接查询(inner join)、外连接查询()