Mysql基础笔记

1 简介

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种
用户或应用共享的数据集合。

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软
件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。
用户通过数据库管理系统访问数据库中的数据。

数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。

数据库：存储、维护和管理数据的集合。

常见的数据库管理系统

• Oricale
• MySQL
• DB2
• SQL Sever

1.1 三大范式

第一范式：无重复的列

第二范式：属性完全依赖于主键[消除部分子函数依赖]。要求数据库表中每一个实例或行必须可以被唯一地区分（表中每一行不能重复）

第三范式：属性不依赖于其他非主属性[消除传递依赖]。第三范式要求一个数据库中不包含已在其他表中已包含的非主键字信息（其他列必须直接依赖于主键）

2 SQL语句

2.1 SQL语句分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。

注意：

sql语句以 ; 结尾

mysql中的关键字不区分大小写

2.2 DDL语句

2.3.1 DDL操作数据库

1 创建

编码方式: 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 数据库名;
Show CREATE DATABASE mydb2;

3 修改数据库

查看服务器中的数据库，并把mydb2的字符集修改为utf8;

//alter database 数据库名 character set 编码方式
ALTER DATABASE mydb2 character SET utf8;

4 删除数据库

//drop database 数据库名
drop database mydb3;

5 其他语句

查看当前使用的数据库

select database();

切换数据库

USE mydb2;

2.3.2 DDL操作表

1 常用数据类型：

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

2 创建新表

create table 语句用于创建新表

语法：

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

create table student(name varchar(5),age int,sex char(1));

3 其他表操作

删除表

//drop table 表名
drop table mydb1;

当前数据库中的所有表

show tables;

查看表的字段信息

desc employee;

增加列:在上面员工表的基本上增加一个image列。

//alter table 表名 add 新列名 新的数据类型
ALTER TABLE employee ADD image blob;

修改job列名为position，修改列长度为100

//alter table 表名 change 旧列名 新列名 新的数据类型
alter table employee change job position varchar(60);
//alter table 表名 modify 列名 新的数据类型
alter table employee modify job varchar(100);

删除image列，一次只能删一列

//alter table 表名 drop 列名
alter table employee drop image;

修改表名，表名改为user

//alter table 旧表名 rename 新表名
alter table user rename users;

查看表格的创建细节

//show create table 表名
show create table user;

修改表的字符串为gbk

//alter table 表名 charaset set 编码方式
alter table user character set gbk;

2.3 DML操作

DML是对标重的数据进行增，删，改的操作。主要包括：INSERT,UPDATE,DELETE

Mysql中，字符串和日期类型要用单引号括起来。空值为null。

1 插入INSERT

//insert into 表名（列名） values（数据值）
insert into student(stuname,stuage,stusex,birthday) values('张三',18,'a','2000-1-1');

注意：

1. 列名与列值要一一对应
2. 给所有列添加数据时可以将列名省略，才是列值顺序按数据表中列的顺序执行

同时添加多行

//insert into 表名（列名） values（第一行数据），（第二行数据），（），（）；
insert into student(stuname,stuname,stusex,birthday)
values('张三1',18,'a','2000-1-1')，
('张三2',18,'a','2000-1-1')，
('张三3',18,'a','2000-1-1')，
('张三4',18,'a','2000-1-1');

注意：插入空值用null

sql中的运算符:

1. 算术运算符:+，-，*，/(除法),求余(%)。示例：5/2, 5%2, 2/5, 2%5

2. 赋值运算符:=。注：赋值方向：从右往左赋值 示例： name=‘张三’

3. 逻辑运算符：
   and(并且),or(或者),not（取非）
   作用:用于连接多个条件时使用

4. 关系运算符：
   >,<,>=,<=,!=(不等于),=(等于),<>(不等于)

补充:查询展示所有数据: select * from 表名

2 更新UPDATE

//UPDATE 表名 SET 列名1=列值1，列名2=列值2...WHERE 列名=值
update emp set gender='boy' where name='zhangsan' and birthday='1990-05-10';

3 删除DELETE

//delete from 表名 where 列名=值
delete from emp where name='zs';
//全部删除: delete from 表名
delete from emp;
truncate table emp;

• DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
• TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
• 删除的数据不能找回。执行速度比DELETE快

为空的条件：

列名 is null
列名 = ''

日期类型值的区别：

date: yyyy-MM-dd(年月日)

time: hh:mm:ss(时分秒)

datetime: yyyy-MM-dd hh:mm:ss(年月日时分秒)

获取当前系统时间: now()

select now();

2.4 DCL操作

1 创建用户：

//create user 用户名@指定ip identified by 密码；
create user test123@localhost identified by '123456';

//create user 用户名@客户端ip identified by 密码；（指定ip才能登陆）
create user test456@10.4.10.18 identified by '456789';

//create user@'%' identified by 密码; 任意IP均可登录
create user test7@'%' identified by '777777';

2 用户授权

grant 权限1，权限2…权限n on 数据库名.* to 用户名@IP;

给指定用户授予指定指定数据库指定权限

grant select,insert,update,delete,create on student.* to 'test7'@'127.0.0.1';
//给予所有数据库的所有权限
grant all on *.* to 'test456'@'127.0.0.1'

3 用户权限查询

//show grants for 用户名@IP
show grants for 'root'@'%'

4 撤销用户权限

revoke 权限1，权限2…权限n on 数据库名.* from 用户名@IP;

revoke select on *.* from 'root'@'%';

5 删除用户

drop user 用户名@IP;
drop user test123@localhost;

2.5 DQL数据查询

查询返回的结果集是一张虚拟表。

查询关键字：select

语法：

select 列名 
from 表名 
where 限定条件 //行条件
group by grouping_columns //对结果分组
having condition //分组后的行条件
order by sorting_columns //对结果进行分组
limit offset_start,row_count //结果限定

1 简单查询

查询所有列

select * from stu;

查询指定列

select sid,sname,age from stu;

2 条件查询

在查询时给出where子句,同时使用下列运算符及关键字：

=、!=、<>、<、<=、>、>=;
BETWEEN…AND； 
IN(set)； 
IS NULL； 
AND、OR、NOT；

select * from stu where gender='female' and age<50;
select * from stu where sid='s_1001' or sname='lisi';
//
select * from stu where sid in('s_1001','s_1002','s_1003');
select * from stu where sid not in('s_1001','s_1002','s_1003');
//
select * from stu where age is null;
//between 包含临界值
select * from stu where age between 20 and 40;
select * from stu where age>=20 and age<=40;
//
select * from stu where gender!='male';
select * from stu where gender<>'male';
select * from stu where not gender='male';
//
select * from stu where sname is not null;
select * from stu where not sname is null;

3 模糊查询

模糊查询需要使用关键字like。

语法：列名 like ‘表达式’ //表达式必须是字符串

通配符：

_ : 任意一个字符

% : 任意0~n个字符，‘张%’

//姓liu的人
select * from stu where sname like 'liu%';
'%a%' : 名字中有a的
'_____i': 名字由6个字母组成，最后一个字母为i

4 字段控制查询

去除重复记录

去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp2表中sal字段存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：

select distinct sal from emp2;

雇员的月薪与佣金之和

因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

select *, sal+comm from emp2;

任何东西与null相加的结果仍为null,使用ifnull()将null转换为数值0：

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

给列名添加别名

在上面查询中出现列名为sal+ifnull(comm,0)，为其取一别名为total:

select *, sal+isfull(comm,0) as total from emp2;

给列其别名时，可以省略as关键字：

select *, sal+isfull(comm,0)  total from emp2;

5 排序

语法： order by 列名 asc/desc

asc 升序/desc 降序 默认不写是升序

 select * from stu order by age desc;

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序

select * from emp2 order by sal desc, empno asc;

6 聚合函数

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

COUNT

COUNT(列名)：统计指定列不为NULL的记录行数；

查询emp2表中的记录数

select count(*) as cnt from emp2;

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

select count(comm) as cnt from emp2;

查询emp2表中月薪大于2500的人数

select count(*) from emp2 where sal>2500;

统计月薪与佣金之和大于2500元的人数：

select count(*) from emp2 where sal+ifnull(comm,0)>2500;

查询有佣金的人数，以及有领导的人数：

select count(comm),count(mgr) from emp2;

SUM 和 AVG

SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

//工资总和
select sum(sal) from emp2;
//工资和佣金总和
select sum(sal),sum(comm) from emp2;
//平均工资
select avg(sal) from emp2;

MIN 和 MAX

MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SELECT MIN(SAL), MAX(SAL) FROM EMP2;

7 分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。

注意:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名

GROUP BY

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

select deptno,count(*) from emp2 group by deptno;

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

select deptno,sum(sal) from emp2 group by deptno;

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

select deptno,count(*) from emp2 where sal>1500 group by deptno;

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是对分组后数据的约束。

补充: 多列分组

select deptno,job,count(*) from emp2 group by deptno,job;

8 LIMIT

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

limit 开始下标,显示条数；//开始下标从0开始

limit 显示条数；//表示默认从0开始获取数据

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

select * from emp limit 0,5;

2. 查询10行记录，起始行从3开始

select * from emp limit 3,10;

pageIndex 页码值 pageSize 每页显示条数

limit (pageindex-1)*pagesize,pagesize;

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

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