MySQL

系统命令

• 启动MYSQL：net start mysql

• 停止MYSQL：net stop mysql

• 访问MYSQL：mysql -u root -p

进入数据库

• 查看所有数据库：show databases;

• 创建数据库：create database 数据库名称;

• 删除数据库：drop database 数据库名称;

• 进入数据库：use 数据库名称;

• 查看所有表：show tables;

数据表

数据表结构

新增

• 新增表

  create table 表名(
      ​	字段1名 字段数据类型 [约束],
      ​	字段2名 字段数据类型 [约束],
      ​	......
      ​	字段N名 字段数据类型 [约束]
  );
  

• 新增字段

  alter table 表名 add 字段名称 数据类型 [约束];
  

• 复制表

  create table 表名 select * from 被复制的表名；
  //特点：复制结构、内容、约束
  
  create table 表名 select * from 被复制的表名 where 1=0;
  //特点：复制结构、约束，不复制内容
  
  create table 表名 like 被复制的表名；
  //特点：复制结构、约束，不复制内容
  
  insert into 表名 select * from 被复制的表名；
  //特点：复制内容
  //注意：字段个数必须相等，数据类型必须相同
  
  create table 表名 like 数据库名.要复制的表名;
  //创建新的数据表，并复制另一个数据库的数据表结构
  insert 表名 select * from 数据库名.要复制的表名;
  //将数据表中的数据复制到新表中
  

删除

• 删除表

  drop table 表名;
  

• 删除字段

  alter table 表名 drop 字段名称;
  

索引

• 添加索引

  create index 索引名 on 表名(字段名称1，字段名称2...字段名称N);
  //注意：在字段经常用于查询的情况下添加索引，添加索引后查询效率提高，但是增删改的效率会降低。
  //因此，经常查询时用索引，增删改不添加索引。
  

• 删除索引

  alter table 表名 drop index 索引名称;
  

修改

• 修改字段

  alter table 表名 change 旧字段名 新字段名 数据类型 [约束];
  //注意：表中有数据的情况下要保证修改的字段数据类型一致
  //要么就清空数据再修改字段数据类型
  

查询

• 查询表

  desc 表名;
  

约束

通用

• 查询约束名

  select * from information_schema.table_constraints where table_name='表名';
  

​

• 删除约束

  alter table 表名 drop 约束关键字 约束名/字段名称;
  
  //在有自增长时，先删自增长再删主键
  alter table 表名 change 主键字段名称 主键字段名称 数据类型；
  alter table 表名 drop primary key;
  

​

主键

• 建表时添加主键

  create table 表名(
  	字段名称 字段类型 primary key,
  	...
  );
  

• 单独添加主键

  alter table 表名 add constraint 约束名称 primary key(字段1名称，字段2名称，...字段N名称);
  

外键

• 建表时添加外键

  create table 表名(
  	字段1名称 数据类型，
  	字段2名称 数据类型，
  	foreign key(外键) references 主表名称(主键)；
  );
  

• 单独添加外键

  alter table 表名 add constraint 外键名称 foreign key(外键) references 主表名称(主键);
  

联合主键

• 建表时添加联合主键

  create table 表名(
  	字段1名称 字段类型，
  	字段2名称 字段类型，
  	...
  	primary key(字段1名称，字段2名称，...字段N名称)
  );
  

唯一

• 建表时添加唯一约束

  create table 表名(
  	字段名称 数据类型 unique
  );
  

• 单独添加唯一约束

  alter table 表名 add constraint 约束名称 unique(字段名称);
  

自增长

• 建表时添加自增长

  create table 表名(
  	字段名称 数据类型 primary key auto_increment,
  	...
  );
  set@@auto_increment_increment=增长值；
  set@@auto_increment_offset=初始值；
  

非空

• 建表时添加非空约束

  create table 表名(
  	字段名称 数据类型 not null
  );
  

默认值

• 建表时添加默认值约束

  create table 表名(
  	字段名称 数据类型 default 对应数据类型的值
  );
  

检查

• 建表时添加检查约束

  create table 表名(
  	字段名称 数据类型 check(字段名称 条件)
  );
  

数据

新增

• 所有字段都添加数据

  insert into 表名 values(数据1,数据2，...数据N);
  

• 选择性添加数据

  insert into 表名(字段1名，字段2名，...字段N名) values(数据1,数据2，...数据N);
  

• 添加多行数据

  insert into 表名(字段1名，字段2名，...字段N名) values(数据1,数据2，...数据N)，(数据1,数据2，...数据N)，(数据1,数据2，...数据N);
  

删除

• 截断

  truncate table 表名;
  //1.  表结构、列、约束等不被改动
  //2.  不能用于删除有外键的表
  //3.  删除有自增长的表会初始化
  

• 删除

  delete from 表名;
  

  delete from 表名 where 条件;
  

• 两者区别

  • **truncate:**会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；
  • **delete:**逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；

修改

• 修改所有数据

  update 表名 set 字段名称 = 修改的数据;
  

• 选择性修改

  update 表名 set 字段1名称 = 修改的数据,字段2名称 = 修改的数据 where 条件;
  

查询

查询

• 查询所有数据

  select * from 表名;
  

• 选择性查询：where

  select 字段1名称，字段2名称，...字段N名称 from 表名 where 条件;
  

• 行数：limit

  select * from 表名 limit 行数;
  select * from 表名 limit 起始行索引，行数;
  

• 去重复：distinct

  select distinct 字段1名称,字段2名称，...字段N名称 from 表名;
  

• 别名：as

  select 字段1名称 as 别名,字段2名称 as 别名，...字段N名称 as 别名 from 表名;
  

• 常量列

  select 字段1名称，字段2名称，...字段N名称，字段默认值 as 字段名称 from 表名;
  

• 排序：order by

  select * from 表名 where 条件 order by 字段名称 asc/desc;
  

聚合函数查询

**注意：**不能放在where条件中，能放在having条件中

• 求总数：COUNT()

  select count(字段 / *) from 表名;
  //字段值为null时不能统计，查询结果只有求总数结果
  

• 求和：SUN()

  select sum(字段) from 表名;
  

• 求平均：AVG()

  select avg(字段) from 表名;
  

• 求最大：MAX()

  select max(字段) from 表名;
  

• 求最小：MIN()

  select min(字段) from 表名;
  

分组查询

• 基本分组查询

  select 字段名称 from 表名 group by 字段名称;
  //分组的组数为显示的行数，每行的数据为组内第一条数据，不能显示非分组字段
  

• 分组前条件筛选查询

  select 字段名称 from 表名 where 条件 group by 字段名称;
  

• 分组后条件筛选查询

  select * from 表名 group by 字段名称 having 条件;
  

子查询

• SELECT 子查询

  select 字段,(select子查询) from 表名;
  

• WHERE子查询

  select * from 表名 where 字段名称 = (子查询);
  

• IN子查询

  select * from 表名 where 字段名称 in(子查询);
  select * from 表名 where find_in_set (表名.id,"字符串类型：1,2,3,4,5");
  

• EXISTS子查询

  select * from 表名 where exists(子查询);
  

• ANY子查询

  select * from 表名 where 字段名称 > any(子查询);
  

• ALL子查询

  select * from 表名 where 字段名称 > all(子查询);
  

• FROM查询

  select * from 表名,(子查询);
  

联合查询

• 内连接： INNER JOIN

  select * from 表1名 inner join 表2名 on 连接条件；
  select * from 表1名，表2名 where 连接条件;
  

• 左外连接 LEFT JOIN

  select * from 表1名 left join 表2名 on 连接条件；
  

• 右外连接 RIGHT JOIN

  select * from 表1名 right join 表2名 on 连接条件；
  

• 全外连接

  select * from 
  (select * from 别名1 left join 别名2 on 连接条件) as 别名3
  union
  (select * from 别名1 left join 别名2 on 连接条件) as 别名4
  

• 交叉连接

  select *|字段列表 from 表1名 cross join 表2名；
  

• 联合查询

  select 字段列表 from 表1名
  union [all]
  select 字段列表 from 表2名
  

case when 用法

case when 用法

• 简单函数CASE [col_name] WHEN [value1] THEN [result1] ... ELSE [default] END

  SELECT
  	NAME '英雄',
  	CASE NAME
  		WHEN '德莱文' THEN
  			'斧子'
  		WHEN '德玛西亚-盖伦' THEN
  			'大宝剑'
  		WHEN '暗夜猎手-VN' THEN
  			'弩'
  		ELSE
  			'无'
  	END '装备'
  FROM
  	user_info;
  

• 搜索函数CASE WHEN [expr] THEN [result1] ... ELSE [default] END

  # when 表达式中可以使用 and 连接条件
  SELECT
  	NAME '英雄',
  	age '年龄',
  	CASE
  		WHEN age < 18 THEN
  			'少年'
  		WHEN age < 30 THEN
  			'青年'
  		WHEN age >= 30
  		AND age < 50 THEN
  			'中年'
  		ELSE
  			'老年'
  	END '状态'
  FROM
  	user_info;
  

​