数据库基础解析【Mysql】

一.DDL操作数据库

1. 创建

CREATE DATABASE语句用于创建新的数据库：

编码方式:gb2312,utf-8,gbk,iso-8859-1

//create database 数据库名 CREATE DATABASE mydb1; 
//create database 数据库名 character set 编码方式 CREATE DATABASE mydb2 character SET GBK; 
//create database 数据库名 set 编码方式 collate 排序规则 CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

2. 查看数据库

查看当前数据库服务器中的所有数据库

show databases;

查看前面创建的mydb2数据库的定义信息

show create database 数据库名; 

3 .修改数据库

alter database 数据库名 character set 编码方式

4. 删除数据库

 drop database 数据库名

5.查看当前使用的数据库

Select database();

6.切换数据库

use 数据库名

二. DDL操作表

常用数据类型

 int：整型
 double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；默认支持四舍五入
 char：固定长度字符串类型； char(10) 'aaa ' 占10位
 varchar：可变长度字符串类型； varchar(10) 'aaa' 占3位
 text：字符串类型，比如小说信息；
 blob：字节类型，保存文件信息(视频，音频，图片)；
 date：日期类型，格式为：yyyy-MM-dd；
 time：时间类型，格式为：hh:mm:ss
 timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
 datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

1.创建新表

CREATE TABLE语句

CREATE TABLE 表名( 
    列名1 数据类型 [约束], 
    列名2 数据类型 [约束], 
    列名n 数据类型 [约束] 
);

2.删除表

drop table 表名; 

3.查看当前数据库中的所有表

 show tables;

4.查看表的字段信息(数据)

 desc 表名；

5.增加列

alter table 表名 add 新列名 新的数据类型;

6.修改列

 alter table 表名 change 旧列名 新列名 新的数据类型;

7.删除列

alter table 表名 drop 列名;

8.修改表名

alter table 旧表名 rename 新表名;

9.查看表格的创建细节

show create table 表名;

10.修改表的字符集

 alter table 表名 character set 编码方式

三. DML操作

1.插入操作

insert into 表名(列名) values(数据值);

2.修改（更新）操作

update 表名 set 列名1=列值1,列名2=列值2 ... where 列名=值;

3.删除操作

delete from 表名 【where 列名=值】;
使用truncate删除表中记录。 
truncate table emp;

-DELETE 删除表中的数据，表结构还在;
删除后的数据可以找回.

-TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
删除的数据不能找回。执行速度比DELETE快。

4.查看表中所有数据

select * from 表名;

四. DQL数据查询

查询关键字：select
(*表示所有列)

select 列名 from 表名 【WHERE --> BROUP BY-->HAVING--> ORDER BY】

1. 简单查询

查询所有列

SELECT * FROM stu;

查询指定列

SELECT sid, sname, age FROM stu; 

2. 条件查询

运算符及关键字：
=、!=、<>、<、<=、>、>=; BETWEEN…AND； IN()； IS NULL； AND；OR； NOT；

3. 模糊查询(搜索引擎)

通配符:

_(下划线): 任意一个字符
%：任意0~n个字符,'张%'

select * from 表名 where 列名 like '表达式' //表达式必须是字符串

4. 字段控制查询

(1)去除重复记录

select distinct 字段 from 表名;

(2)查看不同列的数据之和

//select *,表达式 from 表名;
//select 表达式 from 表名;
SELECT *,sal+comm FROM emp;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

select *,sal+ifnull(comm,0) from emp;

(3)给列名添加别名

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时，是可以省略AS关键字的

SELECT *,sal+IFNULL(comm,0) total FROM emp;

5.排序

 select * from 表名 order by 列名 asc/desc
 //asc 升序 desc 降序 默认不写的话是升序

6. 聚合函数

聚合函数是用来做纵向运算的函数：

 COUNT(列名)：统计指定列不为NULL的记录行数；
 MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
 MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
 SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
 AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

语法

select 函数名(列名) from 表名;

(1) COUNT(列名 或者 *)

查询emp表中记录数：

SELECT COUNT(*) AS cnt FROM emp;

查询emp表中有佣金的人数：

SELECT COUNT(comm) cnt FROM emp;

(2) SUM(纵向求和)和AVG(平均值)

7. 分组查询

1. group by子句

当需要分组查询时需要使用group by子句，例如查询每个部门的工资和，这说明要使用部分来分组。
注意**:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名**

查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

2.having子句

查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal) 
FROM emp 
GROUP BY deptno 
HAVING SUM(sal) > 9000;

注：having与where的区别:

1.having是在分组后对数据进行过滤,where是在分组前对数据进行过滤

2.having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。

where是对分组前记录的条件，如果某行记录没有满足where子句的条件，那么这行记录不会参加分组；而having是对分组后数据的约束。

8.limit

LIMIT用来限定查询结果的起始行，以及总行数.

 limit 开始下标,显示条数;//开始下标从0开始
 limit 显示条数;//表示默认从0开始获取数据

查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0,5;

1. 分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？

l 第一页记录起始行为0，一共查询10行； limit 0,10

l 第二页记录起始行为10，一共查询10行；limit 10,10

l 第三页记录起始行为20，一共查询10行； limit 20,10

pageIndex 页码值 pageSize 每页显示条数

limit (pageindex-1)*pagesize,pagesize;

查询语句书写顺序：select – from- where- groupby- having- order by-limit

查询语句执行顺序：from - where -group by -having - select - order by-limit