MySql数据库基础

主要记录学习mysql的一些常用的命令。

数据库基础命令

查询当前所有的数据库

show databases;

创建一个数据库

CREATE DATABASE <数据库名>;

由于创建数据库时没有指定编码表，因此会使用安装数据库时默认的编码表。

创建创建一个数据库并指定字符集

create database mydb2 character set utf8；

删除数据库

drop database <数据库名>;

修改数据库编码集

alter database 数据库名称 character set 字符集;

查看正在使用的数据库

select database();

切换数据库

use 数据库名;

数据库表结构的CRUD

• 新增数据
• 修改数据
• 删除数据
• 查询数据

单表部分：

创建数据库表

create table 表名(
  列名 类型(长度),  -- 某一列,存放的数据是什么类型.长度表示允许列名存储数据的最大长度.
  列名 类型(长度),
  …
  列名 类型(长度)   -- 最后一个列不需要逗号。
);

注意：

1. 一个数据表可以存在很多列（字段），每列具有类型和长度。
2. 多个列之间使用逗号隔开，最后一个列不需要输入逗号。
3. 创建表的时候，一定切换到某一个具体的数据库。

数据类型：

• Varchar – 字符串
• Double – 小数
• Int – 整数
• Date – 日期

字符类型：（sql 中没有 字符与字符串的区别）

• varchar(列的长度)：列的长度可变。

  name varchar(10):设置name字段的长度为10,name的储存长度在10以内都可以，并且会自动适应长短。

  补充：保存字符个数：0-255.

• char(列的长度)：列的长度固定，不能改变。 手机号 身份证号 存储固定长度

  name char(10):设置name字段的长度为10，name的存储长度在10以内，如果不满10，用空格补足。

查看表

show tables  -- 查看该数据库的所有的表
desc 表名  -- 查看表的列的信息（查看表结构）(describe缩写)

删除表

drop table 表名；

单表创建时约束

约束语法：列名 数据类型 约束条件

1. 主键约束：primary key – 非空 唯一
2. 唯一约束：unique
3. 非空约束：not null 表示该字段的值不能为空

   数据表结构修改

   修改数据表：可以对表名、表中的列名、列的类型等进行增删改。

   alter table 表名 增/删/改 列名*类型(长度) 约束；

   增加列

   alter table 表名 add 列名 类型(长度) 约束;

   例如：修改birthday列不能为null。

   alter table emp2 modify birthday date not null;

修改现有列类型、长度和约束

alter table 表名 modify 列名 类型(长度) 约束;

例如：修改name列的长度为60。

alter table emp2 modify name varchar(60) unique not null;

修改现有列的名称

alter table 表名 change 旧列名 新列名 类型(长度) 约束;

例如：修改列名name变为username。

alter table emp2 change name username varchar(60) unique not null;

删除现有列

alter table 表名 drop 列名;

修改表名

rename table 旧表名 to 新表名;

修改表的字符集

alter table 表名 character set 编码集;

例如： 将emp的编码修改成utf8。

第一步：查询此时表的编码：show create table emp;

第二步：将emp的编码修改成utf8：alter table emp character set utf8;

简单数据表内容的增删改

insert语句

• 向数据表插入数据

-- 方式一：全写（根据列名插入对应的值）
insert into 表名(列名，列名，列名...) values（值，值，值...）

-- 方式二：省略部分列（省略列名直接插入所有的列）
insert into 表名 values（值，值，值...）

-- 方式三：一次性插入多行数据（常用）
insert into 表名 values（值，值，值，值）,（值，值，值，值）...；

update语句

• 数据记录修改操作

update 表名 set 列名=值，列名=值...[where条件语句]；

注意：

（1）如果不加条件语句，将会修改某一列的所有值。

（2）一般修改数据时，都需要增加条件。

例如：把id为 3 的用户的username和password修改为 zhaoliu。

修改语句：update person set username=’zhaoliu’, password=’zhaoliu’ where id=3;

delete语句

• 删除表中数据的语句

语法：delete from 表名 [where条件语句]

注意：

1）如果删除表中的记录时，没有添加where条件，这时会把表中的所有数据删除。表依然存在，是空的表。

2）delete是删除行的数据。

例如：删除person表中username为zhaoliu的用户记录。

删除语句： delete from person where username=‘zhaoliu’;

例如：删除表中所有记录。

删除语句：delete from person;

truncate 语句

• 删除数据

-- 语法1：
truncate table 表名；
-- 语法2：
truncate 表名；

执行顺序：先删除表,再创建表，就等于将数据全部删除了。

也就是说上述语句执行时会把表的所有内容都删除掉，然后在创建一个新的表。

truncate 缺点：不能有条件的删除。不能恢复数据

delete 是逐行删除，并没有恢复初始化值。 可以恢复数据

truncate 和delete区别

• delete：属于DML语言，它是逐条删除的，并没有恢复初始化值，速度慢一些，删除的数据还可以恢复过来。后面可以加where 进行有条件的删除。
• truncate：属于DDL语言，删除的数据不可恢复，删除速度比delete快，先将整个表删除，再重新创建表。不能有条件的删除。

  数据表记录的查询

  查询数据库中表的所有数据

  select * from 表名;

  查询某张表中指定的列

  select 列名,列名... from 表名;

  按条件查询

  select 列名,列名... from 表名 where 条件;

  查询时使用的运算符

1. >(大于) 、<(小于) 、>=(大于等于) 、<=(小于等于) 、=(相等) 、<>或!=(不相等)
2. 与 或 非
   - and：逻辑与，多个条件同时成立。&&
   - or：逻辑或，多个条件任意一个成立。||
   - not：逻辑非，相反的意思。!
3. 区间：between … and … 在两者之间取值。
   格式：列名 **between **开始值 **and **结束值;
4. in(值1,值2,值3) 在指定值中任意取。
5. 模糊查询：like ‘模糊查询部分’
6. 表达式有两个占位符：
   %表示零或任意多个字符;
   _任意单个字符;
7. is null：判断该值是否为空
8. 判断不是空：is not null

过滤重复数据

-- 查询排重：
select distinct 列名,列名... from 表名[where 条件];

说明：distinct： 去重复。显示distinct后面列的内容，并且过滤掉重复的数据。

对查询结果进行排序

使用order by 子句排序查询结果

select * from 表名 order by 列名1 asc|desc，列名2(asc | desc)...

按照列名1，列名2，列名3 进行排序输出。asc是升序排列，desc是降序排列。默认是asc升序。

别名：可以对查询出来的列名起别名

select 列名 as 别名,列名 as 别名... from 表名 where 条件;

注意：在使用别名的时候，as关键字可以忽略。

SQL中的聚合函数

聚合函数(聚集函数)：多个数据进行运算，运算出一个结果。例如，求记录数，求和，平均值，最大值，最小值。

• count(数目) : 统计个数（行数）,统计有多少行，或者有多少条数据。
• sum(和)函数：求和。
• avg(均值)函数：求平均值。
• max(最大值)：求最大值。
• min(最小值)：求最小值。

  count函数

  select count(*) 或 count(具体列名) from 表名;

  注意：count列统计时，不会统计null

select count(列名) from 表名;  -- 按具体的列来进行统计行数
select count(*) from 表名;   -- 统计表中的行数

sum函数

select sum(列名),sum(列名)... from 表名;

注意：使用sum 多列进行求和的时候，如果某一列中含有null，这一列所在的行中的其他数据不会被加到总和中。

ifnull(列名, 默认值)函数表示判断该列名是否为null,如果为null，返回默认值，如果不为null，返回实际的值。

数据库中的函数：truncate(列名, 截取的小数位) 表示截取的意思，保留几位小数。

avg函数

select avg(列名) from 表名；

注意：求某列的平均值，avg里面的null不作为统计

max/min函数

max函数：求最大值；min函数：求最小值；max(列) /min(列) 统计该列的最大值或者最小值。

select max(列名),min(列名) from 表名;

注意：null 排除在外

分组查询group by

group by 分组函数

分组：按照某一列或者某几列，把相同的数据进行合并输出。

select ... from ... group by 列名,列名

having

having和group by一起使用，用法和where一模一样，where怎么使用having就怎么使用，where不能使用的，having也可以使用

比如说where后面不可以使用聚合函数，但是在having后面可以使用聚合函数。

小结：

一、关于group by 和having一起使用的规则

语法：group by 列名 having 条件

分组之后加过滤条件。

开发中什么情况下使用分组？当在需求中遇到每种，每个等字眼的时候就使用分组。

二、where 和 having 的区别。

（1）having 通常与group by 分组结合使用。 where 和分组无关。

（2）having是从筛选出来的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

（3）where 后面的条件可以写在having 中，但是 having中的条件不一定能写在where中。

（4）having 可以书写聚合函数 (聚合函数出现的位置： having 之后)

例如having中的 聚合函数（count，sum，avg，max，min），是不可以出现where条件中。

（5）where 是在分组之前进行过滤的。having 是在分组之后进行过滤的。

sql语句顺序SFWGHO

查询关键字的出现的顺序是固定的

select（要显示的内容）from（表名）where（条件）group by（分组的列）having（分组后的条件）order by（排序）

查询语句可以省略 但是顺序不能变

比如 将分组省略 having 跟在 where后面

select …5… from …1… where …2… group by …3…having …4… order by .6.

查询的执行顺序：

1）from : 表名

2）where：条件过滤

3）group by : 分组

4）having ： 分组之后进行过滤。

5）select ：执行完毕之后，显示内容。

6）order by ： 根据查询的内容进行排序 （asc升序，desc 降序，默认降序）