MySql
1. 数据库基本概念
1. 数据库:
DateBase 简写DB
2.概述:
数据库是用于存储和管理数据的仓库
3.特点:
- 用于持久化存储数据的,数据库其实就是一个文件系统
- 方便存储和管理数据
- 使用统一的操作数据语言—SQL(普通话)
4.常见的数据库:
- MySQL
- 特点:开源,免费,深受中小企业喜爱
- Oracle
- 特点:安全,扩展性强,功能完备
- SQL Server
- 特点:C# 和 .NET项目中应用比较多
5.启动和关闭
MySQL安装好之后,需要启动服务,什么是**服务?**服务就是没有界面的应用程序
- 启动,关闭服务
- 方式1:(开始键 + R) 运行-> services.msc
- 方式2:管理员身份cmd -> net start MySql 或 net stopMySql
- 注意:命令中的MySql 一定要与你的mysql服务的名称一致。
6. MySql 登录
- 方式1:
- mysql -u用户名 -p密码
- 方式2:
- mysql -h目标主机IP地址 -u用户名 -p密码
- 方式3:
- mysql --host=目标主机IP地址 --user=用户名 --password=密码
7. MySql 退出
- quit
- exit
2 SQL
2.1 概述:
SQL:结构化查询语言(Structured Query Language)简称SQL。只要是关系型数据库,都可以用SQL语言区操作数据。不同的数据库之间的SQL操作略有不同,但是主体部分还是一样的。对于数据库之间的差异,我们称之为:“方言”。
2.2 SQL语法
-
SQL语句可以是单行,也可以是多行,多行以分号结尾
->show databases ->;
-
使用空格 和 缩进来增强语句的可读性
-
MySql数据库的SQL语句不区分大小写,关键词建议使用大写
-
注释
- 单行注释 :
--
注释内容 或#
注释内容(Mysql特有) - 多行注释:/* 注释内容 */
- 单行注释 :
2.3 SQL语言的分类
-
DDL(Data Definition Language):数据库定义语言
用来定义数据库对象:数据库,表,列表等。关键字:create ,drop ,alter 等
-
DML(Data Manipulation Language):数据库操作语言
用来对数据库中表的数据进行增删改操作的。关键字:insert,delete,update等
-
DQL(Data Query Language):数据库查询语言
用来查询表中的数据。关键字:select , where
-
DCL(Data Control Language): 数据库控制语言
用来定义数据库的访问权限 和 安全级别,创建用户。关键字 grant ,revoke等
3.DDL- 数据库定义语言
3.1 操作数据库 - CRUD
1.C – Create 创建
-- 创建数据库
CREATE DATABASE test;
-- 查看test数据库-编码集
SHOW CREATE DATABASE test;
注意:
1. 数据库已存在,再次创建就会报错
-- 如果test库不存在,就创建
CREATE DATABASE IF NOT EXISTS test;
- 创建数据库时指定编码集
-- 创建数据时指定编码集
CREATE DATABASE test2 char set utf8;-- 将数据库test2 的编码集指定为utf-8
show create database test2; --查看test2数据库-编码集
练习: 创建一个数据库test3 ,并在创建时判断其是否存在,然后指定它的编码集为gbk;
-- 练习:创建一个数据库test3 ,并在创建时判断其是否存在,然后指定它的编码集为gbk
CREATE DATABASE IF NOT EXISTS test3 CHAR SET gbk;
SHOW CREATE DATABASE test3;
2.R – Retrieve 查询
- 查询所有数据库名称:
show databases;
- 查询指定数据库的创建语句 及 给数据库的编码集
SHOW CREATE DATABASE 数据库名;
3.U – Update 修改
修改数据库编码集
ALTER DATABASE 数据库名 CHAR SET 字符集;
4.D – Drop 删除
DROP DATABASE IF EXISTS 数据库名;
5.使用数据库
USE 数据库名;
4.2 操作表
- C:Create 创建
- 语法:
1. 语法:
CREATE TABLE 表名(
列名1 数据类型1,
列名2 数据类型2,
列名3 数据类型3,
……
列名n 数据类型n )
注意:
- 最后一列,不需要加 , 2. 常见数据类型:
1. int:整数类型
例: age int ,
SHOW CREATE DATABASE 数据库名; ALTER DATABASE 数据库 CHAR SET 字符集; DROP DATABASE IF EXISTS 数据库名; USE 数据库名;
2. float ,double:小数类型
例:score float( 4, 2 ) 共4个数,小数位为2位 3. date:日期类型,只包含年月日,yyyy-MM-dd
4. datetime日期类型 包含年月日时分秒 yyyy-MM-dd HH-mm-ss
5. timestamp时间戳类型,包含年月日时分秒 yyyy-MM-dd HH-mm-ss
注意:该类型若在插入数据时,不赋值或赋值为null,则默认使用当前系统时间,来
自动复制。
6. char ,varchar
char(n):定长度数据类型若存入少于n个字符,则用空格补齐,查询之时再去掉
空格,所以 char型数据后面不能有空格. 并且当数据<n 时,依旧是占n个
字节
varchar(n):变长度数据类型,该类型比较常用。
案例:
CREATE TABLE student(
id INT,
NAME VARCHAR(32),
age INT,
score DOUBLE(5,2),
brithday DATE,
insert_time TIMESTAMP );
- R:Retrieve 查询
- 查询某个数据库中所有的表名称
SHOW TABLES;
- 查询表结构
DESC 表名;
- U:Update 修改
- 修改表名
alter table 表名 rename to 新的表名;
- 修改表字符集
alter table 表名 char set 字符集;
- 添加一列
alter table 表名 add 列名 数据类型;
- 修改列名称,数据类型
方式1:alter table 表名 change 列名 新列名 新数据类型;
方式2:alter table 表名 modify 列名 新数据类型;
- 删除列
alter table 表名 drop 列名;
- D:Drop 删除
drop table 表名;
drop table if exists 表名;--方式2
5. 增删改表中数据-DML
5.1 添加数据
语法:
insert into 表名(列名1,列名2,列名3) values (value1 , value2 , value3) ;
INSERT INTO stu VALUES(2,'林俊杰',19,98.50,NULL,NULL);--正确
INSERT INTO stu VALUES(3,'刘德华',20,97.50,'1988-02-04',NULL);--正确
INSERT INTO stu VALUES(4,'刘德华',20,97.50,'1988-0204',NULL);--错误,date格式不 符
INSERT INTO stu VALUES(5,'刘德华',20,97.50,NULL);--错误,缺少字段
注意:
- 列名 和 值 需要一一对应。
- 如果表名后不加列名,则表示默认给所有列添加值
- 除了数字类型,其他数据类型都需要用引号(单双都可以)括起来
5.2 删除数据
语法:
delete from 表名 [where 条件]
注意:
- 如果不带条件,则会把表中所有数据都删除
- 如果要删除表中所有记录
a. delete from 表名,不推荐使用,因为效率比较低,有多少条记录,它就会执行多少次。
b. truncate table 表名,推荐使用,先删除表,然后再创建一个一模一样的表。
DELETE FROM stu WHERE id=3;--删除id=3的数据
TRUNCATE TABLE stu;--删除stu表,然后创建一个一模一样的新表
5.3 修改数据
语法:
update 表名 set 列名1 = 值1,列名2 = 值2 ,… [where 条件]
注意:
- 如果不加条件,则是将表中所有数据进行修改
UPDATE stu SET age = 20;--修改表中所有数据
UPDATE stu SET age = 19 WHERE id = 1;--将id=1这条数据的年龄设置为19岁
UPDATE stu SET age =21,score=100 WHERE NAME='林俊杰';--将name='林俊杰'的年龄修改为 21,成绩修改为100
6.数据库查询语言-DQL
6.1 语法
select
字段列表
from
表名列表
where
条件列表
grop by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
USE test;
CREATE TABLE IF NOT EXISTS student(
id INT,#学号
NAME VARCHAR(20),#姓名
age INT,#年龄
sex VARCHAR(5),#性别
address VARCHAR(100),#地址
math INT,#数学
english INT#英语 );
DROP TABLE IF EXISTS student; INSERT INTO student (id,NAME,age,sex,address,math,english) VALUES
(1,'马云',18,'男','杭州',80,80),
(2,'马化腾',19,'男','深圳',75,60),
(3,'埃隆马斯克',31,'男','美国',76,93),
(4,'扎克伯格',27,'男','美国',65,NULL),
(5,'郎平',16,'女','上海',90,98),
(6,'姚明',32,'男','上海',80,81);
6.2 基础查询
1. Select * from 表名;--查询该表中所有字段对应的数据
2.Select 字段1,字段2,... from 表名;--查询该表中所有字段对应的数据
--查询 姓名 和 年龄
SELECT NAME,age FROM student;
--一些公司会要求查询语句不能出现*,必须写字段名加注释
--查询姓名 和 年龄
SELECTNAME,#姓名
age #年龄
FROMstudent;#学生表
3. 去重查询:Select distinct 字段1,字段2,... from 表名
SELECT address FROM student;--有重复地名
SELECT DISTINCT address FROM student;
4. 求和查询:Select distinct 字段1,字段2,(字段1+字段2),... from 表名
SELECT NAME,math,english,(math + english) FROM student;
注意:
5. 当字段中有null值参与运算时,结果也为null,可以使用ifnull()
SELECT NAME,math,english,(math + IFNULL(english,0)) FROM student;
6. math+english 起别名
SELECT NAME math,english,(math + IFNULL(english,0)) '总成绩' FROM student; 或SELECT NAME math,english,(math + IFNULL(english,0)) AS '总成绩' FROM student;
6.3 条件查询
1.where子句后面跟的条件
2.运算符
>, < , >= , <= , = , <>
BETWEEN ... AND
IN(集合)
LIKE:模糊查询
占位符
_:一个任意字符
%:多个任意字符
IS NULL
AND 或 &&
OR 或 ||
NOT 或 !
-- 查询年龄大于20的学生信息
SELECT * FROM student WHERE age >20;
-- 查询年龄大于20的学生信息,包括20岁
SELECT * FROM student WHERE age>=20;
-- 查询年龄等于20的学生信息
SELECT * FROM student WHERE age = 20;
-- 查询年龄不等于20岁的学生信息
SELECT * FROM student WHERE age != 20; SELECT * FROM student WHERE age <>20;
-- 查询年龄大于等于20 小于等于30的学生信息
SELECT * FROM student WHERE age>=20 && age<=30; SELECT * FROM student WHERE age>=20 AND age<=30; SELECT * FROM student WHERE age BETWEEN 20 AND 30;#简化写法
-- 查询年龄 16 ,18 ,19 的学生信息
SELECT * FROM student WHERE age = 16 OR age = 18 OR age = 19; SELECT * FROM student WHERE age = 16 || age = 18 || age = 19; SELECT * FROM student WHERE age IN (16,18,19);#简化写法
-- 查询没有英语成绩的学生信息
SELECT * FROM student WHERE english = NULL;-- 该条语句不正确,因为null不能用 = ,!= 判断
SELECT * FROM student WHERE english IS NULL;
-- 查询有英语成绩的学生信息 SELECT * FROM student WHERE english IS NOT NULL;
3. like:模糊查询
模糊查询在项目中用的比较多,因为大多数搜索框都是模糊查询
# like 查询
-- 查询 姓马的学生信息
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询第二字为克的学生信息
SELECT * FROM student WHERE NAME LIKE '_克%';
-- 查询名字中包含马字的学生信息
SELECT * FROM student WHERE NAME LIKE '%马%';
-- 查询名字是3个字的学生信息
SELECT * FROM student WHERE NAME LIKE '___';
6.4 排序查询
语法:
order by 子句
具体写法:
order by 排序字段1,排序方式1 ,排序字段2,排序方式2
排序方式:
ASC :升序 (默认)
DESC: 降序
-- 查询学生信息,并按照数学成绩升序排列
SELECT * FROM student ORDER BY math; SELECT * FROM student ORDER BY math ASC;
-- 查询学生信息,并按照数学成绩降序排列
SELECT * FROM student ORDER BY math DESC;
-- 查询学生信息,并按照数学成绩升序排列,如果数学成绩相同就按英语成绩升序排列
SELECT * FROM student ORDER BY math ASC ,english ASC ; SELECT * FROM student WHERE english IS NOT NULL ORDER BY math ASC ,english ASC ;
6.5 聚合函数
概述:
-- 查询有英语成绩的学生信息 SELECT * FROM student WHERE english IS NOT NULL; # like 查询 -- 查询 姓马的学生信息 SELECT * FROM student WHERE NAME LIKE '马%'; -- 查询第二字为克的学生信息 SELECT * FROM student WHERE NAME LIKE '_克%'; -- 查询名字中包含马字的学生信息 SELECT * FROM student WHERE NAME LIKE '%马%'; -- 查询名字是3个字的学生信息 SELECT * FROM student WHERE NAME LIKE '___'; -- 查询学生信息,并按照数学成绩升序排列 SELECT * FROM student ORDER BY math; SELECT * FROM student ORDER BY math ASC; -- 查询学生信息,并按照数学成绩降序排列 SELECT * FROM student ORDER BY math DESC; -- 查询学生信息,并按照数学成绩升序排列,如果数学成绩相同就按英语成绩升序排列 SELECT * FROM student ORDER BY math ASC ,english ASC ; SELECT * FROM student WHERE english IS NOT NULL ORDER BY math ASC ,english ASC ;
将一列数据作为一个整体,进行纵向计算
格式:
select 聚合函数(列名)[别名] from 表名
分类:
count:计算个数
1. 选择非空列:主键(后面说)
2. count(*)
max:计算最大值
-- 求学生中数学成绩最高分
SELECT MAX(math) FROM student;
min:计算最小值
-- 求学生中英语成绩最低分
SELECT MIN(IFNULL(english,0)) FROM student;
sum:计算和
-- 求学生的学生数学总成绩
SELECT SUM(math) FROM student;
avg:计算平均值
-- 求学生的学生数学平均分
SELECT AVG(math) FROM student;
注意事项:
聚合函数会自动排除null值。
解决方案:
1.选择非空列进行计算
2.ifnull函数
6.6 分组查询
格式:
group by 分组字段
注意:
1.分组之后查询的字段:分组字段,聚合函数
SELECT * FROM student GROUP BY sex;-- 没有任何意义 结果:查出局部数据
2.where 和 having的区别
a. where 在分组前进行限定,如果不满足条件,则不参与分组 。having 是对结果进行限定,
如果不满 足则不会被查询出来
b. where后不能跟聚合函数,having后可以跟聚合函数
-- 按照性别分组,分别查询男,女同学的数学平均分
SELECT * FROM student GROUP BY sex;-- 没有任何意义
SELECT sex,AVG(math) FROM student GROUP BY sex;
-- 按照性别分组,分别查询男,女同学的数学平均分,及相应性别对应的人数
SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;
-- 分组前添加一些限定条件
-- 按照性别分组,分别查询男,女同学的英语平均分,及相应性别对应的人数,分组要求:分数小于60的不 参与统计
SELECT sex,AVG(IFNULL(english,0)),COUNT(id) FROM student WHERE english >60 GROUP BY sex; SELECT sex,AVG(IFNULL(english,0)),COUNT(id) FROM student GROUP BY sex HAVING english >60;
-- 错误,因为条件是分组前的条件,having只对分组后的结果进行条件添加
-- 按照性别分组,分别查询男,女同学的英语平均分,及相应性别对应的人数,分组要求:分数小于60的不 参与统计,并且只查看人数大于2的分组数据
SELECT sex,AVG(IFNULL(english,0)),COUNT(id) FROM student WHERE english >60 GROUP BY sex HAVING COUNT(id)>2;
-- 另一种写法
SELECT sex,AVG(IFNULL(english,0)),COUNT(id) 人数 FROM student WHERE english >60 GROUP BY sex HAVING 人数>2;
6.7 分页查询
格式:
limit 开始的索引,每页显示的条数;
分页公式:
开始的索引 = (当前页码-1)* 每页显示的条数
推导过程:
-- 每页显示2条数据
SELECT * FROM student LIMIT 0,2;-- 第1页
SELECT * FROM student LIMIT 2,2;-- 第2页
SELECT * FROM student LIMIT 4,2;-- 第3页
SELECT * FROM student LIMIT 6,2;-- 第4页
-- 后面在做网站实现分页效果的时候,我们只需要确定每页起始的索引就可以实现分页效果 -- 分页公式:开始的索引 = (当前页码-1)* 每页显示的条数
注意:
分页操作limit语法是MySql的“方言”