数据库基本知识
目标:
- MySQL数据库的安装和基本配置;
- SQL基本语句:
DDL (Data Definition Language) 数据定义语言
DML (Data Manipulation Language) 数据操作语言
DQL (Data Query Language) 数据查询语言
DCL (Data Control Language) 数据控制语言 - 对数据库和表进行增删改查操作(CRUD):
C(Create):增加
R(Retrieve):查询
U(Update):修改
D(Delete):删除
1. MySQL的安装
- MySQL默认端口3306
- 启动和关闭服务
*net start mysql
:启动MySQLl服务
*net stop mysql
:关闭MySQL服务 - 登录和退出:
*mysql -u用户名 -p密码
:MySQL登录
*mysql -u用户名 -p
之后回车,以密文方式输入密码
*mysql -hIP地址 -u用户名 -p密码
:连接目标ip地址的MySQL
*mysql --host=IP地址 --user=用户名 --password==密码
:连接目标ip地址的MySQL
*exit
或者quit
:退出MySQL - 安装目录:
安装路径下的配置文件my.ini,存储了MySQL的基本配置信息。 - 数据目录:
数据目录是一个隐藏文件夹:C:\ProgramData\MySQL\MySQL Server 5.5\data
有多个数据库----对应于文件夹;
每个数据库中存在多个表----对应于文件;
每个表中存在多条数据记录----对应于文件中存储的数据。
2. SQL概述
1) SQL的基本概念
SQL:Structured Query Language,结构化查询语言,定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为"方言"。
2) SQL的通用语法
- 单行或者多行书写,以分号结尾;
- 不区分大小写,关键字建议用大写;
- 3种注释,
单行注释:-- 注释内容
或#注释内容
(MySQL特有);注意横线后一定要有空格
多行注释:/* 注释 */
。
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. SQL基本操作语句
1) DDL 数据定义语言,操作数据库、表
①. 操作数据库:CRUD四个动作
- C(Create):创建
*create database 数据库名称;
创建数据库;
*create database if not exists 数据库名称;
判断数据库是否存在,不存在则创建;
*create database 数据库名称 character set 字符集名;
创建指定字符集类型的数据库;
*create database if not exists 数据库名称 character set 字符集名;
综合使用。 - R(Retrieve):查询
*show databases;
查询所有数据库的名称;
*show create database 数据库名称;
查看某个数据库的创建语句,可以看到字符集类型。 - U(Update):修改
*alter database 数据库名称 character set 字符集名
修改数据库的字符集名称。 - D(Delete):删除
*drop database 数据库名称;
删除数据库;
*drop database if exits 数据库名称;
先判断数据库是否存在,存在则删除。
使用数据库
*select databse();
查询当前正在使用的数据库名称;
*use 数据库名称;
使用指定名称的数据库,注意不用写database了。
②. 操作表
-
表的结构:表头和内容,表名和列名
-
SQL中的数据类型:
*int
整数类型 ;
*double(m,d)
小数类型,其中 m是小数的位数,d是小数点后的位数;
*date
日期类型,只包含年月日,yyyy-MM-dd;
*datetime
日期类型,包含年月日时分秒,yyyy-MM-dd HH:mm:ss;
*timestamp
时间戳类型,如果不赋值或赋值为null,则默认使用当前的系统时间来自动赋值;
*varchar(m)
字符串类型,m为字符最大个数。 -
C(Create):创建
*create table 表名(列名1 数据类型1, 列名2 数据类型2,....列名n 数据类型n);
创建表,注意最后一行不需要加逗号;
*create table 表名 like 被复制的表名;
复制一个表。 -
R(Retrieve):查询
*show tables;
查询某个数据库中所有表的名称;
*desc 表名;
查询表结构;
*sho create table 表名;
查询表的创建,可以看到字符集。 -
U(Update):修改
*alter table 表名 rename to 新表名;
修改表名;
*alter table 表名 character set 字符集名;
修改表的字符集;
*alter table 表名 add 列名 数据类型;
在表中添加列;
*alter table 表名 change 原列名 新列名 新数据类型;
change修改表中某列的名字和数据类型;
*alter table 表名 modify 原列名 新数据类型;
注意modify只修改某列的数据类型;
*alter table 表名 drop 列名;
删除某一列。 -
D(Delete):删除
*drop table 表名;
删除表;
*drop table if exits 表名;
先判断表是否存在,存在则删除。
2) DML 数据操作语言,增、删、改表中的数据
- 添加数据
*insert into 表名(列名1,列名2,...,列名n) values (值1,值2,...,值n);
添加数据,没有添加的值则默认为null。
注意:
A. 列名和值要一一对应;
B. 如果表名后不定义列名,则默认给所有列添加值,要注意值的个数必须完整;
C. 除了数字类型,其他类型的数据都要用引号,单、双引号都行。 - 删除数据
*delete from 表名 [where 条件]
删除满足条件的记录;
*TRUNCATE TABLE 表名;
删除表,然后再创建一个一样的空表。
注意:
A. 如果不加条件,则会删除表中所有记录;
B. 如果要删除所有记录,不建议使用delete,因为有多少条记录就是执行多少次删除操作,效率低;使用TRUNCATE,只会有两次操作,效率更高。 - 修改数据
*update 表名 set 列名1 = 值1, 列名2 = 值2, ...,列名n = 值n [where 条件];
按照条件去修改数据。
注意: 如果不加任何条件,则会把表中的所有数据都修改。
3) DQL 数据查询语言,查询表中的记录
① 语法:
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
② 基础查询:
- 多个字段查询:
*select 字段名1,字段名2... from 表名;
查询某一个表中的多个字段;
*select * from 表名;
查询所有字段。 - distinct关键字去除重复项:
*select distinct 字段名1,字段名2... from 表名;
去除重复项,如果查询多个字段,则多个字段都相同才判定为重复项; - 计算列(一般只进行数值型的计算):
*select 字段名1,字段名2,字段名1+字段名2 AS 别名 from 表名;
查询字段1和字段2,并且得到字段1+字段2的值,取新的名称,AS不写也行。
注意: 如果有null参与运算,则结果会为null;
可以用ifnull(表达式1,表达式2)
解决,表达式1是哪个字段需要判断是否为null,表达式2是替换值。
③ 条件查询:
where
子句后面跟条件。
- 运算符:
(1) 基本运算符号:> 、<、 <=、 >= 、= 、<>不等于
;
(2)BETWEEN... AND ...
: BETWEEN 10 AND 20 表示区间[10,20];
(3)IN(集合)
: IN(10,18,20)表示或者10或者18或者20;
(4) LIKE:模糊查询;
占位符: _ 单个任意字符;%零个或多个任意字符;
(5)IS NULL
,IS NOT NULL
:注意NULL不能用等号来判断,要用IS;
(6) 与:and
或者&&
;或:or
或者||
;非:not
或者!
。
④ 排序查询:
order by
子句后面跟排序字段和排序方式。
- 语法
*order by 排序字段1 排序方式1, 排序字段2 排序方式2
注意:只有当第一方式相同时才会去判断第二方式。
ASC
:升序,默认的。DECS
:降序。
⑤ 聚合函数:
将一列数据作为一个整体,进行纵向计算。
- 语法:
select 聚合函数名(字段名) from 表名;
*count
:计算个数,一般选择非空的列:主键
*max
:计算最大值;
*min
:计算最小值;
*sum
:求和;
*avg
:计算平均值;
注意:count
的计算会排除NULL值;一般选择不包含NULL的列进行计算或者用IFNULL函数
⑥ 分组查询
group by
字句后面跟分组字段,having
字句构面跟分组之后的条件。
- 语法:
group by 分组字段 having 分组之后的条件
注意:
A. 分组之后查询的字段只能是分组字段或聚合函数,其他字段没意义;
B. where在分组之前进行限定,如果不满足条件,则不参与分组;
having在分组之后进行限定,如果不满足结果则不会被查询出来;
where后面不能跟聚合函数,having后面可以跟聚合函数。
⑦ 分页查询:
limit
字句后面跟分页限定。
- 语法:
limit 开始的索引,每页查询的条数
注意:开始的索引=(当前的页码-1)每页显示的条数;
limt是一个MySQL“方言”。
示例:
//创建一个名为student的TABLE,包含id,NAME,sex,Address,Math,English和DATE几列。
CREATE TABLE student(id INT,NAME VARCHAR(20),sex VARCHAR(20),address VARCHAR(10),Math INT,English INT,DATE TIMESTAMP);
SHOW TABLES; //显示数据库中所有的表
//向student表中添加VALUES,student后面的(列名)可以省略,时间戳赋值为null,则默认使用当前的系统时间来自动赋值。
INSERT INTO student VALUES(1,"马云","男","深圳",80,90,NULL),
(2,"刘强东","男","北京",70,92,NULL),
(3,"小龙女","女","古墓",73,82,NULL),
(4,"风清扬","男","华山",60,85,NULL),
(5,"林青霞","女","深圳",65,96,NULL),
(6,"马化腾","男","深圳",NULL,79,NULL),
(7,"林平之","男","华山",65,NULL,NULL),
(8,"小仙女","女","深圳",88,90,NULL);
SELECT * FROM student; //查询student表中的所有字段
查询结果:
*基本查询:
SELECT name,sex FROM student; //只查询name和sex字段内容
SELECT DISTINCT address FROM student; //只查询address字段,并且去除重复项
SELECT NAME,Math,English,Math+English FROM student; //查询数学、英语和数学英语成绩和。但是如果有null参与运算,则结果会为null;
SELECT NAME,Math,English,IFNULL(Math,0)+IFNULL(English,0) FROM student; //用ifnull()解决
SELECT NAME 姓名,Math 数学,English 英语,IFNULL(Math,0)+IFNULL(English,0) 总分 FROM student;//每一列都取别名,分别为姓名、数学、英语和总分
*条件查询
SELECT * FROM student WHERE Math BETWEEN 80 AND 95; //查询数学成绩在80和95之间的
SELECT * FROM student WHERE Math IN(80,90,100);//查询数学成绩在为80或者90或者100的
SELECT * FROM student WHERE Math IS NULL;//查询数学成绩为NULL的
SELECT * FROM student WHERE Math IS NOT NULL;//查询数学成绩不为NULL的
SELECT * FROM student WHERE NAME LIKE "马_";//查询姓马并且名字只有2个字的人
SELECT * FROM student WHERE NAME LIKE "马%";//查询姓马的人,名字个数不限制
SELECT * FROM student WHERE NAME LIKE "___";//查询姓名三个字的人
SELECT * FROM student WHERE NAME LIKE "%马%";//查询姓名中包含的人
*排序查询
SELECT * FROM student ORDER BY Math; //按照数学成绩升序排列,ASC不写默认升序
SELECT * FROM student ORDER BY Math DESC; //按照数学成绩降序排列
SELECT * FROM student ORDER BY Math ASC,English DESC;//先按照Math升序排列,如果Math一样,再按照English降序排列
*聚合函数
SELECT COUNT(Math) FROM student;//7,注意排除了值为NULL的
SELECT COUNT(IFNULL(Math,0)) FROM student;//8,用ifnull改进
SELECT COUNT(*) FROM student;//只要有一列不为NULL的都算上
*分组查询
SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;//分别计算男生和女生的数学平均分,和各自的人数
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math >70 GROUP BY sex;//限定数学分大于70的才参与分组
SELECT sex,AVG(math),COUNT(id) 人数 FROM student WHERE math >70 GROUP BY sex HAVING 人数>1;//分组之后只显示人数大于1的项
*分页查询
SELECT * FROM student LIMIT 0,3; //从索引0开始,每页显示3条(第一页)
SELECT * FROM student LIMIT 3,3; //从索引3开始,每页显示3条(第二页)
SELECT * FROM student LIMIT 6,3; //如果条数不够了,有多少条显示多少条(第三页)
4) DCL 数据控制语言,管理用户、授权
- 查询用户
切换到myqsl数据库,查询user表SELECT * FROM mysql;
- 创建用户
*CREATAE USER '用户名'@‘主机名’ INENTIFIED BY '密码';
注意:通配符%表示在任意主机使用用户登录数据库; - 删除用户
*DROP USER '用户名'@‘主机名’;
- 修改用户密码
*UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USR = '用户名';
*SET PASSWORD FOR '用户名'@'主机名'=PASSWORD('新密码');
- 权限管理
查询权限:SHOW GRANTS FOR '用户名@主机名';
授予权限:GRANT 权限列表 ON 数据库名.表名 to '用户名@主机名';
GRANT ALL ON *.* to '用户名@主机名';
超级管理员,所有表所有权限。
撤销权限:REVOKE 权限列表 ON 数据库名.表名 to '用户名@主机名';