文章目录
MySQL数据库软件
- 配置
-
MySQL服务启动:没有界面的应用程序,后台服务
- 手动。计算机–>管理–>服务
- cmd–>
services.msc
打开服务的窗口 - 使用管理员打开cmd
net start mysql
: 启动mysql的服务net stop mysql
:关闭mysql服务
-
MySQL登录
mysql -uroot -p
密码mysql -hip -uroot -p连接目标的密码
mysql --host=ip --user=root --password=连接目标的密码
-
MySQL退出
- exit
- quit
-
MySQL目录结构
- MySQL安装目录:basedir=“D:/develop/MySQL/”
- 配置文件 my.ini
- MySQL数据目录:datadir=“C:/ProgramData/MySQL/MySQL Server 5.5/Data/”
- 几个概念
- 数据库:文件夹
- 表:文件
- 数据:数据
- 几个概念
- MySQL安装目录:basedir=“D:/develop/MySQL/”
SQL语句
1.什么是SQL?
Structured Query Language:结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”,主体不变。
2.SQL通用语法
- SQL 语句可以单行或多行书写,以分号结尾。如 show databases;
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL 数据库的 SQL 语句不区分大小写,关键字建议使用大写。
4. 3 种注释
* 单行注释: – 注释内容 或 # 注释内容(mysql 特有) (–后面要空格。#可以不加)
* 多行注释: /* 注释 */
- 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 等
- DDL(Data Definition Language)数据定义语言
DDL:操作数据库、表
1.操作数据库:CRUD(增删改查)
- C(Create):创建
-
创建数据库:
create database 数据库名称;
-
创建数据库,判断不存在,再创建:
create database if not exists 数据库名称;
-
创建数据库,并指定字符集
create database 数据库名称 character set 字符集名;
-
练习: 创建db4数据库,判断是否存在,并制定字符集为gbk
create database if not exists db4 character set gbk;
-
- R(Retrieve):查询
- 查询所有数据库的名称:
show databases;
- 查询某个数据库的字符集:查询某个数据库的创建语句,-- 查看某个数据库的定义信息
show create database 数据库名称;
- 查询所有数据库的名称:
- U(Update):修改
- 修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
- 修改数据库的字符集
- D(Delete):删除
- 删除数据库
drop database 数据库名称;
- 判断数据库存在,存在再删除
drop database if exists 数据库名称;
- 删除数据库
- 使用数据库
- 查询当前正在使用的数据库名称
select database();
- 使用数据库
use 数据库名称;
- 查询当前正在使用的数据库名称
2.操作表
- `C(Create):创建
- 语法:
create table 表名( 列名1 数据类型1, 列名2 数据类型2, .... 列名n 数据类型n
);
- 注意:最后一列,不需要加逗号(,)
- 数据库类型:
-
int:整数类型
age int
, -
double:小数类型
score double(5,2)
//最多5位,小数点后保留两位;最大999.99 -
date:日期,只包含年月日,
yyyy-MM-dd
-
datetime:日期,包含年月日时分秒
yyyy-MM-dd HH:mm:ss
-
timestamp:时间戳类型 包含年月日时分秒
yyyy-MM-dd HH:mm:ss
如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值 -
varchar:字符串
name varchar(20)
:姓名最大20个字符
zhangsan 8个字符 张三 2个字符
-
- 语法:
- 创建学生表
create table student( id int, name varchar(32), age int , score double(4,1), birthday date, insert_time timestamp
);
- 复制表:
create table 表名 like 被复制的表名;
- 复制表:
- R(Retrieve):查询
- 查询某个数据库中所有的表名称
show tables;
- 查询表结构
desc 表名;
- 查询某个数据库中所有的表名称
- U(Update):修改
- 修改表名
alter table 表名 rename to 新的表名;
- 修改表的字符集
alter table 表名 character set 字符集名称
;//utf8、gbk - 添加一列
alter table 表名 add 列名 数据类型;
- 修改列名称 类型
alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型;
//只改类型 - 删除列
alter table 表名 drop 列名;
- 修改表名
- D(Delete):删除
drop table 表名;
drop table if exists 表名 ;
- 客户端图形化工具:SQLYog
DML:增删改表中数据
1.添加数据:
- 语法:
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
- //
INSERT INTO stu(id,NAME,age) VALUES(1,'小明',18);
- 注意:
- 列名和值要一一对应。
- 如果表名后,不定义列名,则默认给所有列添加值
insert into 表名 values(值1,值2,...值n);
//但得知道多少列,列数和数值个数得匹配 - 除了数字类型,其他类型需要使用引号(单双都可以)引起来
2.删除数据:
- 语法:
- delete from 表名 [where 条件]
- //
DELETE FROM stu WHERE id=1;
- 注意:
- 如果不加条件,则删除表中所有记录。
- 如果要删除所有记录
delete from 表名
; – 不推荐使用。有多少条记录就会执行多少次删除操作TRUNCATE TABLE 表名
; – 推荐使用,效率更高 先删除表,然后再创建一张一样的表。
3.修改数据:
-
语法:
update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
- //
UPDATE stu SET age = 35,score = 100 WHERE id = 1;
-
注意:
- 如果不加任何条件,则会将表中所有记录全部修改。
DQL:查询表中的记录
- select * from 表名;//查询所有数据
1.语法:
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
2.基础查询
- 多个字段的查询
select 字段名1,字段名2... from 表名;
//SELECT NAME,age FROM student3;
- 注意:
- 如果查询所有字段,则可以使用*来替代字段列表。
- 注意:
- 去除重复:
- distinct
- //
SELECT DISTINCT address FROM student3;
去除重复字段
- 计算列
- 一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
ifnull(表达式1,表达式2)
:null参与的运算,计算结果都为null- 表达式1:哪个字段需要判断是否为null
- 如果该字段为null后的替换值,不为null用原值。
SELECT NAME,math,english,math+IFNULL(english,0) FROM student3;
- 起别名:
- as:as也可以省略用空格代替。
SELECT NAME,math 数学,english 英语,math+IFNULL(english,0) AS 总分 FROM student3;
3.条件查询
-
where子句后跟条件
-
运算符
-
、< 、<= 、>= 、= 、!=、<>
-
BETWEEN…AND
-
IN( 集合)
-
LIKE:模糊查询
- 占位符:
- _:单个任意字符
- %:多个任意字符
- 占位符:
-
IS NULL
-
and 或 &&
-
or 或 ||
-
not 或 !
– 查询年龄大于20岁
SELECT * FROM student WHERE age > 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;
– 查询年龄22岁,18岁,25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN (22,18,25);
– 查询英语成绩为null
SELECT * FROM student WHERE english = NULL;
– 不对的。null值不能使用 = (!=) 判断SELECT * FROM student WHERE english IS NULL;
– 查询英语成绩不为null
SELECT * FROM student WHERE english IS NOT NULL;
-
– 查询姓马的有哪些? like
SELECT * FROM student WHERE NAME LIKE '马%';
– 查询姓名第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";
– 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';
– 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';
4.排序查询
-
语法:order by 子句
- order by 排序字段1 排序方式1 , 排序字段2 排序方式2…
SELECT * FROM student3 ORDER BY math DESC;
--排序方式降序
-
排序方式:
- ASC:升序,默认的。
- DESC:降序。
-
注意:
- 如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
SELECT * FROM student3 ORDER BY math ASC,english ASC;
//先按照数学成绩升序排,数学成绩一样再按英语成绩升序排
5.聚合函数
将一列数据作为一个整体,进行纵向的计算。
- count:计算个数
SELECT COUNT(NAME) FROM student3;
//表中name纵列的个数- 一般选择非空的列:主键
- count(*)
- max:计算最大值
SELECT MAX(math) FROM student3;
//计算数学成绩最大值 - min:计算最小值
- sum:计算和
- avg:计算平均值
- 注意:聚合函数的计算,排除null值。
解决方案:
1. 选择不包含非空的列进行计算
2. IFNULL函数SELECT COUNT(IFNULL(english,0)) FROM student3;
//将null值换成0,统计english纵列个数。
6.分组查询
-
语法:
group by 分组字段;
-
注意:
- 分组之后查询的字段(select 接):分组字段、聚合函数,接其他的没有意义
- where 和 having 的区别?
- where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
- where 后不可以跟聚合函数,having可以进行聚合函数的判断。
– 按照性别分组。分别查询男、女同学的平均分
SELECT sex , AVG(math) FROM student GROUP BY sex;
– 按照性别分组。分别查询男、女同学的平均分,人数
SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
– 按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
– 按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
//用as起个别名,as可省
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
7.分页查询
-
语法:limit 开始的索引,每页查询的条数;
-
公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
– 每页显示3条记录SELECT * FROM student LIMIT 0,3; -- 第1页
//显示第一页显示的数据,1页3条数据SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页
-
分页操作limit 是一个MySQL"方言"(只能在MySQL里用,oracle不一样),SQL是普通话
8.约束
-
概念: 对表中的数据进行限定,保证数据的正确性、有效性和完整性。
-
分类:
- 主键约束:primary key
- 非空约束:not null
- 唯一约束:unique
- 外键约束:foreign key
-
非空约束:not null,值不能为null
- 创建表时添加约束
CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name为非空,否则报错 );
- 创建表完后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
- 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
- 创建表时添加约束
-
唯一约束:unique,值不能重复
-
创建表时,添加唯一约束
CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
);
- 注意mysql中,唯一约束限定的列的值可以有多个null
-
删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
-
在创建表后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
-
-
主键约束:primary key。
-
注意:
- 含义:非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
-
在创建表时,添加主键约束
create table stu( id int primary key,-- 给id添加主键约束 name varchar(20) );
-
删除主键
– 错误alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;
-
创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-
自动增长:
- 概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长
- 在创建表时,添加主键约束,并且完成主键自增长
create table stu( id int primary key auto_increment,-- 给id添加主键约束 name varchar(20) );
INSERT INTO stu VALUE(NULL,'ccc');
//在之前的id值基础上,添加横列上自动增加id值。- 删除自动增长
ALTER TABLE stu MODIFY id INT;
- 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
-
-
外键约束:foreign key,让表于表产生关系,从而保证数据的正确性,不能随意删除。(从表关联主表)
- 在创建表时,可以添加外键
- 语法:
create table 表名(
…
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)//关联主键
); CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);
//外键名称随意起,但最好有相关性
- 语法:
- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
- 创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
- 级联操作
- 添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
- 分类:
级联更新:ON UPDATE CASCADE
//关联改变
级联删除:ON DELETE CASCADE
//关联删除
- 添加级联操作
- 在创建表时,可以添加外键