MySql基础

1、数据库

数据库（DataBase，DB）：指长期保存在计算机的存储设备(硬盘)上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合. 还是以文件的方式存在服务器的电脑上的。

Mysql：是一款开源免费的关系型数据库，后来被Oracle公司收购。

数据库分类

关系型数据库：Oracle、MySQL、SQLServer、Access；
非关系型数据库：MongoDB、Redis、Solr、ElasticSearch、Hive、HBase；

2、SQL语句

SQL（Structure Query Language）：结构化查询语言，通过sql操作数据库(操作数据库,操作表,操作数据)

2.1、SQL分类

• Data Definition Language (DDL数据定义语言) 如：操作数据库，操作表
• Data Manipulation Language(DML数据操纵语言)，如：对表中的记录操作增删改
• Data Query Language(DQL 数据查询语言)，如：对表中的数据查询操作
• Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

3、DDL语句

3.1、DDL语句操作数据库

3.1.1、创建数据库

create database 数据库名 [character set 字符集][collate 校对规则] ;

例如：create database demo character set utf8;

3.1.2、查看所有数据库

查询所有数据库：show databases;
查看数据库的定义结构：show create database 数据库名;

3.1.3、删除数据库

删除数据库：drop dabase 数据库名;

3.1.4、修改数据库

修改数据库字符集：alter database 数据库名 character set utf8;

3.1.5、其他

使用数据库：use 数据库名;
查看正在使用哪个数据库：select database();

3.2、DDL操作表（重要）

3.2.1、创建表

create table 表名(
列名 类型 【约束】,
列名 类型 【约束】,
…
列名 类型 【约束】
);

1、类型

1. 整型 一般使用int 或者bigint
2. 浮点/双精度型
   默认的范围 float或者double
   指定范围 float(M,D) eg: float(4,2) 表达的范围: -99.99~99.99
3. 字符串
   固定长度 char(n) eg: char(20), 最大能存放20个字符. ‘aaa’, 还是占20个字符的空间
   可变长度 varchar(n) eg:varchar(20), 最大能存放20个字符. ‘aaa’, 占3个字符的空间
   一般使用varchar(n) 节省空间; 如果长度(eg:身份证)是固定的话 可以使用char(n) 性能高一点
4. 关于大文件
   一般在数据库里面很少存文件的内容, 一般存文件的路径
   一般不使用二进制存, 使用varchar(n)存文件的路径
5. 日期
   DATE 只有日期
   DATETIME 日期和时间

类型小结

• 1. 整数: int bigint
• 2. 小数: double
• 3. 字符串: varchar(长度)
• 4. 日期: date \ datetime

2、约束

作用：保证用户插入的数据保存到数据库中是符合规范的
约束种类:

• not null: 非空 ; eg: username varchar(40) not null username这个列不能有null值
• unique:唯一约束, 后面的数据不能和前面重复; eg: cardNo char(18) unique; cardNo 列里面不可
  以有重复数据
• primary key；主键约束(非空+唯一); 一般用在表的id列上面. 一张表基本上都有id列的, id列作为唯
  一标识的
• auto_increment: 自动增长,必须是设置了primary key之后,才可以使用auto_increment
• id int primary key auto_increment; id不需要我们自己维护了, 插入数据的时候直接插入null, 自动
  的增长进行填充进去, 避免重复了.
  注意:

1. 先设置了primary key 才能设置auto_increment
2. 只有当设置了auto_increment 才可以插入null 自己维护 否则插入null会报错
   id列:
3. 给id设置为int类型, 添加主键约束, 自动增长
4. 或者给id设置为字符串类型,添加主键约束, 不能设置自动增长

例如：创建一张学生表(含有id字段,姓名字段,性别字段. id为主键自动增长)

create table student(
	id int primary key auto_increment,
	name varchar(20),
	sex varchar(10) not null,
)

3.2.2、查看表

查看数据库里面的所有表：show tables;
查看表结构：desc 表名;

3.2.3、修改表

有如下修改：增加列、修改列的类型约束、修改列的名称，类型，约束、删除列。

1. 增加列：alter table 表名 add 字段 类型 约束;
2. 修改列的类型约束：alter table 表名 modify 字段名 类型 约束;
3. 修改列的名称，类型，约束：alter table 表名 change 旧列 新列 约束;
4. 删除列：alter table 表名 drop 列名;
5. 修改表名：rename table 旧表名 to 新表名;

-- 需求:为学生表增加一个成绩列
alter table student add score double;
-- 语法: alter table 表名 modify 列名 新类型 新约束; 修改列的类型约束
-- 需求: 修改name的类型和约束
alter table student modify name varchar(20) not null;
-- 语法: alter table 表名 change 旧列名 新列名 类型 约束; 修改列的名称，类型，约束
-- 需求: 修改sex列名为gender
alter table student change sex gender varchar(10) not null;
-- 语法: alter table 表名 drop 列名; 删除列
-- 需求: 删除score
alter table student drop score;
-- 语法: rename table 旧表名 to 新表名; 修改表名
rename table student to user;

3.2.4、删除表

删除表：drop table 表名;

4、DML操作表记录-增删改【重点】

4.1、增加数据

语法:insert into 表(列,列,...,列) values(值,值,...,值);
1、向student表中插入一条数据，有id、name、sex三列;

insert into student(id,name,sex) values(10,'张三','男');

2、向student表中插入多条数据，有id、name、sex三列;

insert into student values
(10,'张三','男'),(11,'李四','男'),(12,'王五','男'),
(14,'小美','女'),(15,'小明','男'),(16,'小红','男');

4.2、修改数据

语法：update 表 set 列=值,列=值 [where 条件];
注：如果不加添加会修改表里面的所有这一列的数据
1、修该student表中所有的sex为男;

update student set sex='男';

2、修该student表中id为16的学生的sex为男;

update student set sex='男' where id = 16;

4.3、删除数据

语法：delete from 表 [where条件];
注：没有条件删除表中全部数据
1、删除student表中id为16的学生

delete from student where id = 16;

2、删除student表中全部数据

delete from student;

truncate:truncate table 表;

delete 和truncate区别【面试题】
DELETE： 删除表中的数据，表结构还在; 删除后的数据可以找回,一条一条的删除.
TRUNCATE： 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

5、DQL操作表记录-查询【重点】

5.1、基本查询语句

select [*] [列名 ,列名] [列名 as 别名 …] [distinct 字段] from 表名 [where 条件];

*：表示查询所有字段；
as：表示给该字段或表取一个别名;
distinct：表示去重查询。注意点: 去重针对某列, distinct前面不能先出现列名

5.2、条件查询

5.2.1、between…and…

between…and…：显示在某一区间的值
例：查询student表中成绩（score）为优秀的学生(80~100)的学生的全部信息;

eg:select * from student where score between 80 and 100;相当于80<=score<=100

5.2.2、in

in：显示in列表中的值
例：查询student表中成绩（score）为80和90的学生的全部信息;

eg:select * from student where score in(80,90);

5.2.3、like模糊查询

like 模糊查询 一般和_或者%一起使用

• _ 占一位
• % 占0或者n位
  例：查询名字包含“一”的学生的全部信息;

eg:select * from student where name like "%一%";

例：查询姓“李”的学生的全部信息;

eg:select * from student where name like "李%";

例：查询名字第二个字为“晓”的学生的全部信息;

eg:select * from student where name like "_晓%";

5.2.4、and 多条件同时满足和or 任意条件满足

and：where 条件一 and 条件二 and 条件三;
or：where 条件一 or 条件二 or 条件三;

5.3、排序查询

有时候我们需要对查询出来的结果排序显示，那么就可以通过 ORDER BY 子句将查询出的结果进行排序。排序可以根据一个字段排，也可以根据多个字段排序，排序只是对查询的结果集排序，并不会影响表中数据的顺序。

5.3.1、单列排序

SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC]; //ASC: 升序，默认值; DESC: 降序

练习: 以分数降序查询所有的学生

select * from student order by score DESC;

5.3.2、组合排序

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2[ASC|DESC];

练习: 以分数降序查询所有的学生, 如果分数一致,再以age降序

select * from student order by score DESC , age DESC;

5.4、聚合函数

聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少记录数
sum(列名)	求这一列的和

语法：SELECT 聚合函数(列名) FROM 表名 [where 条件];

-- 求出学生表里面的最高分数
SELECT MAX(score) FROM student
-- 求出学生表里面的最低分数
SELECT MIN(score) FROM student
-- 求出学生表里面的分数的总和(忽略null值)
SELECT SUM(score) FROM student
-- 求出学生表里面的平均分
SELECT AVG(score) FROM student
-- 统计学生的总人数 (忽略null)
SELECT COUNT(sid) FROM student
SELECT COUNT(*) FROM student

5.5、分组查询

分组查询是指使用 GROUP BY语句对查询信息进行分组
GROUP BY怎么分组的？ 将分组字段结果中相同内容作为一组，如按性别将学生分成两组
GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么
用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用
语法

SELECT 字段1,字段2... FROM 表名 [where 条件] GROUP BY 列 [HAVING 条件];

. 练习:根据性别分组, 统计每一组学生的总人数

SELECT sex, count(*) FROM student GROUP BY sex;

分组后筛选 having
练习根据性别分组, 统计每一组学生的总人数> 5的(分组后筛选)

SELECT  sex,count(*) FROM student GROUP BY sex HAVING count(*) > 5;

统计sid为8之前的的, 根据性别分组, 每一组学生的总人数 > 2的(分组后筛选)

select sex,count(*) from student where sid < 8 group by sex having count(*) > 2

5.6、分页查询

LIMIT是限制的意思，所以LIMIT的作用就是限制查询记录的条数. 经常用来做分页查询;
语法：select ... from .... limit a ,b;
a：起始行数，从0开始计数，如果省略，默认就是0; a=(当前页码-1)*b;
b：返回的行数

例：分页查询学生, 每一页查询4条，查询第一页

select * from student limit 0,4;