MySQL-notes(updating......)

一、SQL

• sql分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

• DQL-执行顺序

  编写顺序        执行顺序
  select
  	字段列表-------⑤
  from
  	表名列表-------①
  where
  	条件列表-------②
  group by
  	分组字段列表----③
  having
  	分组后条件列表--④
  order by
  	排序字段列表----⑥
  limit
  	分页参数-------⑦
  

• DCL-权限控制

  权限	说明
  all,all privileges	所有权限
  select	查询数据
  insert	插入数据
  update	修改数据
  delete	删除数据
  alter	修改表
  drop	删除数据库/表/视图
  create	创建数据库/表

二、常见的数据类型

• 整形：tinyint、smallint、mediumint、int、bigint
• 浮点型：float、double、decimal
• 日期时间型：year、time、date、datetime
• 字符型：char、varchar(数据长度)、text、enum('数据')

三、常用命令

操作表格	语法
添加数据	insert into 表名(字段1,字段2,…) values (值1,值2,…),(值1,值2,…),(值1,值2,…)；
修改数据	update 表名 set 字段1=值1,字段2 = 值2,… [where 条件];
删除数据	delete from 表名 [where 条件];
聚合函数	select 聚合函数(字段列表) from 表名 [where 条件];
分组查询	select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
排序查询	select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2,…;
分页查询	select 字段列表 from 表名 limit 起始索引 查询记录数；
查询用户	use mysql; select * from user;
创建用户	create user ‘用户名’@‘主机名’ identified by ‘密码’;
修改用户密码	alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’;
删除用户	drop user ‘用户名’@‘主机名’;
查询权限	show grants for ‘用户名’@‘主机名’;
授予权限	grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
撤销权限	grant 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;

条件查询

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或!=	不等于
between…and…	在某个范围之内(含最小、最大值)
in(…)	在in之后的列表中的值，多选一
like占位符	模糊匹配(_匹配单个字符，%匹配任意个字符)
is null	是null

逻辑运算符	功能
and 或 &&	并且(多个条件同时成立)
or 或 ||	或者(多个条件任意一个成立)
not 或 ！	非，不是

针对表内数据的操作

建

• 创建数据库：create database 库名；

• 创建并判断数据库是否存在：create database if not exists 库名；

• 使用数据库：use 库名；

• 建表：create table 表名(字段名+空格+数据类型)；

• 创建字段为非空约束：在表中：字段名 数据类型 not null；

• 创建数据库并指定字符集

  • create database 库名 character set 字符集名字；
  • create database 名字 default character set=字符集名字；

• 创建主键约束

  • 创建设置单个
    • create table 表名 字段名 数据类型 主键约束；
    • create table 表名 字段名 数据类型 constraint 约束名 主键约束（字段名）；
  • 创建设置复合
    • create table 表名 字段名 数据类型，【constraint 约束名】 primary key（字段名，字段名）；

• 创建外键约束：create table 表名 字段名 数据类型 constraint 约束名 foreign key(从表字段名) references 主表名(主表字段名)；

• 创建唯一约束

  • create table 表名 字段名 数据类型 unique；
  • create table 表名 字段名 数据类型 ，constraint 约束名 unqiue(字段名，字段名);

• 创建默认约束：create table 表名 字段名 数据类型 default 默认值;

• 设置自动增长(只能设置主键)：create table 表名 字段名 数据类型 auto_increment【auto_increment=n】;

插

• 插入所有字段数据：insert into 表名 values(值1，值2,…)；
• 插入指定字段数据：insert into 表名(字段名1，字段名2，…) values(值1，值2,…)；
• 批量插入多条数据
  • insert into 表名(字段名1，字段名2，…) values(值1，值2，…)，(值1，值2，…)，(值1，值2，…)；
  • insert into 表名 values (值1，值2，…)，(值1，值2，…)，(值1，值2，…)；
• 将另一个表的数据全部使用：insert into 使用数据表名 select * from 被使用表名【where 限制】；

增

• 添值：insert into 表名 （列名）values（值）；

• 添加主键约束

  • alter table 表名 add primary key (字段名);
  • alter table 表名 modify 字段名 数据类型 primary key;
  • alter table 表名 add constraint 约束名称 primary key（字段名，字段名）；

• 添加外键约束：alter table 从表 add foreign key (外键) references 主表(主键);

• 增加唯一约束

  • alter table 表名 modify 字段名 数据类型 unique；
  • alter table 表名 add unique(字段名，字段名);
  • alter table 表名 add constraint 约束名 unique(字段名，字段名);

• 增加默认约束

  • alter table 表名 modify 字段名 数据类型 default 默认值;
  • alter table 表名 alter column 字段名 set default 默认值;

• 增加非空约束：alter able 表名 modify 字段名 数据类型 not null；

• 增加自动增长：alter table 表名 modify 字段名 数据类型 auto_increment;

• 在表中增加字段

  • alter table 表名 add 新字段名 新字段名的数据类型[first（首列，后面不接字段名）或者after（在字段之后，需要在后面接字段名)]；
  • 增加多个字段
    • alter table 表名 add 字段名 数据类型，add 字段名 数据类型；
    • alter table 表名 add（字段名 数据类型，字段名 数据类型）；

删

• 删除

  • 删除部分数据：delete from 表名 where 条件;
  • (效率慢)删除全部数据：delete from 表名;
  • (效率快)删除表并且新建相同表：truncate table 表名；

• 删除主键约束：alter table 表名 drop primary key;

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

• 删除非空约束：alter table 表名 modify 字段名 数据类型；

• 删除唯一约束：alter table 表名 drop key/index 约束名；

• 删除默认约束

  • alter table 表名 modify 字段名 数据类型；
  • alter table 表名 alter column 字段名 drop default 默认值；

• 删除自动增长：alter table 表名 modify 字段名 数据类型；

• 删除字段中的值：delete from 表名 where 条件；

• 删除库：drop database [if exists]库名；

• 删除表

  • drop table 表名；
  • drop table 【if exists】表名，表名；

• 删除表中的字段

  • alter table 表名 drop 字段名；
  • 删除多个字段：alter table 表名 drop 字段名，drop 字段名；

查

• 查询：select * from 表名 where 条件；
• 查看表：show tables；
• 查询建表时的结构
  • show create table 表名；
  • show columns from 表名；
  • desc 表名；（describe）
• 查询当前使用数据库：select 库名()；
• 查看表中所有数据：select * from 表名；
• 限制查询数据：select * from 表名 limit 0,n;(显示从第一行开始，0也可以省略)
  • limit 0,n = limit n offest 0
• 查看当前系统下的数据库：show databases;
• 查看创建数据库的方法：show create database 库名；
• 查看数据库变量：show variables like 'character(可以更改)%';
• 查询时过滤重复数据：select distinct 字段名 from 表名；
• 查询另一个库中的表
  • select * from 库名.表名；
  • show tables from 库名；

改

• 更新数据：update 表名 set 字段名1=值1，字段名2 = 值2，… [where 条件];
• 修改字段名：alter table 表名 change 旧字段名 新字段名 新字段名的数据类型；
• 修改数据类型：alter table 表名 modify 字段名 新数据类型；
• 修改表名
  • alter table 旧表名 rename 新表名；
  • rename table 旧表名 to 新表名；
• 修改数据库字符集：alter database 名字 [default] character set[=]字符集名字；
• 在设置了默认自动增长之后，更改自动增长最小值：alter table 表名 auto_increment=n;

移

• 移动字段：alter table 表名 modify 需要移动的字段名 需要移动的字段数据类型 after 字段名；

排

• 排序：order by 字段名；（asc 升序、desc降序）

  • 单字段排序：select 字段名 from 表名 order by 字段名 asc(默认)/desc；
  • 多字段排序：select * from 表名 order by 字段名，字段名；(先满足前面字段排序，若最后字段后接顺序，则顺序优先按照最后字段)
  • 自定义排序：select * from 表名 order by 字段名 顺序，字段名 顺序；

分

• 分组查询

  • select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

  • group by 字段 ：将查询结果按一列/多列的值分组，值相等为一列

  • having 字段：二次判断，用到聚合函数后，又需筛选条件时，having和group by组合用

  • 例：select 列名1 ,count(列名2) 别名 from 表名 group by 列名1 having 别名 >2;

  • 执行顺序：where>聚合函数>having

  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段五任何意义

• where与having区别

  • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
  • 判断条件不同：where不能对聚合函数进行判断，而having可以

算

• 统计所有数据：select count(*) from 表名；
• 分组统计相同的数据数量：select 字段名，count(*) from 表名 group by 字段名；
• 分组统计并且筛选数量：select 字段名 from 表名 group by 字段名 having count(字段名)需要筛选的条件；

函数

语法：select 函数(参数);

字符串函数	功能
concat(s1,s2,…sn)	字符串拼接
concat(‘-’,s1,s2,…sn)	以连接符进行字符串拼接
lower(str)	全部小写
upper(str)	全部大写
lpad(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)	右填充
trim(str)	去掉字符串头部和尾部的空格
ltrim(str)	去掉字符串头部空格
rtrim(str)	去掉字符串尾部空格
substring(str,start,len)	返回从字符串str从start位置起的len个长度的字符串
left(s,n)	返回字符串s开始的最左边n个字符
right(s,n)	返回字符串s的最右边n个字符
replace(s,from_s,to_s)	把字符串s中的from_s字符串替换为to_s

数值函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	取余
rand()	返回0~1的随机数
round(x,y)	求参数x的四舍五入的值，保留y位小数
truncate(x,y)	返回截取至y位并让后面的数变为0的x的参数
format(x,y)	返回参数x的四舍五入的值，保留y位小数

• format、round和truncate的区别
  • round可以进行四舍五入，返回类型是float浮点型
  • truncate直接截取，不进行四舍五入
  • format会四舍五入，返回类型是str字符串

日期函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now() 或 sysdate()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日份
date_add(date,interval expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2)	返回起始时间date1和结束时间date2之间的天数
date_format(date,format)	根据format指定的格式显示date值

• format格式	描述
  %b	月份的缩写名称(Jan…Dec)
  %c	月份，数字形式(0…12)
  %m	月份，数字形式(00…12)
  %M	月份名称(January..December)
  %d	该月日期，数字形式(00…31)
  %e	该月日期，数字形式(0…31)
  %Y	4位数形式表示年份
  %y	2位数形式表示年份

流程函数	功能
if(value,t,f)	如果value为true，则返回t，否则返回f
ifnull(value1,value2)	如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1] … else [default] end	如果val1为true，返回res1，… 否则返回default默认值
case [expr] when [val1] then [res1] … else [default] end	如果expr的值等于val1，返回res1，…否则返回default默认值

聚合函数	描述
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值的和

信息函数	描述
version()	返回当前MySQL服务器版本号
connection_id()	返回MySQL服务器当前连接的次数，每个连接都各自唯一的id
database()和schema()	返回当前的数据库名
user()	获取用户名的函数，返回当前登录的用户名称

自定义函数	语法
创建函数1	create function 函数名(参数名 参数数据类型) returns 返回值数据类型 return 构造函数；
创建函数2	delimiter// create function 函数名(参数名 参数数据类型) returns 返回值数据类型 begin return 构造函数; end// delimiter;
无参函数	create function 函数名() returns 返回值数据类型 return 构造函数；
调用函数	select 函数名(字段名) from 表名;
删除函数	drop function [if exits] 函数名;

• 加密函数
• MD5() 信息摘要算法加密
  • select MD5(str); 对数据进行加密和界面处理，以32位十六进制数字的二进制字符串形式返回，若参数位null，则返回null
  • password() 密码算法加密
    • set password=password(‘新密码’); --在MySQL中修改密码
    • mysqladmin -u用户名 -p旧密码 password 新密码 --在DOS中修改密码
  • 区别
    • 不同点：MD5返回数字加小写字母，password返回数字加大写字母加特殊符号
    • 相同点：①都是加密的函数 ②都可以输入null

表

多表关系	描述
一对多	在多的一方设置外键，关联一的一方的主键
多对多	建立中间表，中间表包含两个外键，关联两张表的主键
一对一	用于表结构拆分，在其中任何一方设置外键(unique)，关联另一方的主键

多表查询	语法
隐式内连接	select 字段列表 from 表1，表2 where 条件 …;
显式内连接	select 字段列表 from 表1 [inner] join 表2 on 连接条件…;
左外连接	select 字段列表 from 表1 left [outer] join 表2 on 条件…;
右外连接	select 字段列表 from 表1 right [outer] join 表2 on 条件…;
自连接	select 字段列表 from 表1 别名1 join 表1 别名2 on 条件…；
联合查询	select 字段列表 from 表1 … union[all] select 字段列表 from 表2 …;
嵌套查询(子查询)	select 字段列表 from 表1 where 字段1=(select 字段1 from 表2);

• 内连接查询的是两张表交集的部分
• select * from 表1 as t1 [inner] join 表2 as t2 on t1.id=t2.id [inner] join 表3 as t3 on t1.id=t3.id；
• 外连接相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据(右表同理)
• 自连接查询，可以是内连接查询，也可以是外连接查询
• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
• union all会将全部的数据直接合并在一起，union会对合并之后的数据去重
• 子查询外部的语句可以是insert/update/delete/select的任意一个
• distinct注意事项
  • distinct [查询字段]，必须放在要查询字段的开头，即放在第一个参数；
  • 只能在SELECT 语句中使用，不能在 INSERT, DELETE, UPDATE 中使用；
  • DISTINCT 表示对后面的所有参数的拼接取不重复的记录，即查出的参数拼接每行记录都是唯一的
  • 不能与all同时使用，默认情况下，查询时返回的就是所有的结果

子查询

子查询	常用操作符	返回结果
标量子查询	=、<>、>、>=、<、<=	单个值
列子查询	in、not in、any、some、all	一列或多行
行子查询	=、<>、in、not in	一行或多列
表子查询	in	多行多列

• 用any、all、some关键字修饰子查询

  • 例：select * from 表1 where 字段1 >any (select 字段2 from 表2)；

  • any、all、some关键字放在比较运算符的后面

  • any和some是同义词，表示满足内层子查询的任何一个条件

  • all需要满足所有条件

• 使用[not] in 或exists的子查询

  • 例：select * from 表1 where 字段1 in (select 字段2 from 表2)；
  • [not] in 与 in 的作用相反，exists同理

• 插入记录时使用子查询

  • insert into 表1(字段1) select 字段1 from 表2 where 条件；

• 多表更新

  update 表1 join 表2 on 表1.id = 表2.id
  set 旧值=新值
  where 条件;
  

• 多表删除

  //创建并复制表2的数据到
  create table 表1 as select * from 表2; 
  //删除
  delete from 
  表1 left|right[outer] join 表2 on 表1.id=表2.id
  where 条件;
  

事务

事务操作	语法
查看/设置事务提交方式	select @@autocommit;//自动提交,默认为1 set @@autocommit=0;//手动设置提交
开启事务	start transaction或begin;
提交事务	commit;
回滚事务	rollback;
查看事务隔离级别	select @@transaction_isolation;
设置事务隔离级别	set [session|global] transaction isolation level

事务特性	描述
原子性	事务是不可分割的最小操作单元，要么全部成功，要么全部失败
一致性	事务完成时，必须使所有的数据都保持一致状态
隔离性	数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性	事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

并发事务问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”

事务隔离级别	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read(默认)	×	×	√
serializable	×	×	×

• 事务隔离级别越高，数据越安全，但是性能越低

四、约束

• MySQL中常用的几种约束类型：

约束	描述	关键字
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
外键约束	用来让两张表的而数据之间建立连接，保证数据的一致性和完整性	foreign key
非空约束	限制该字段的数据不能为null	not null
唯一约束	保证该字段的所有数据都是唯一、不重复的	unique
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	default
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	check

MySQL相关问题

• mysql默认端口号为3306，更改端口号之后cmd进入方式由mysql -u root -p变为mysql -u root -p -Pxxx(xxx是更改之后的端口号)

  mysql -u root -p -P3308
  

• 查看端口号

  mysql> show global variables like 'port';
  

• sqlyog报错1251

  mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';#更新用户密码
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> FLUSH PRIVILEGES;
  Query OK, 0 rows affected (0.00 sec)
  

• sqlyog报错1418(无法创建函数)

  //1.在客户端上执行
  SET GLOBAL log_bin_trust_function_creators = 1;
  
  //2.MySQL启动时，加上
  –log-bin-trust-function-creators选贤，参数设置为1
  
  //3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加
  log-bin-trust-function-creators=1
  

• 如何修改MySQL的登录密码?

  mysqladmin -uroot -proot password mysql