基本概念
数据库是用来存储和管理数据的仓库
MySQL是关系型数据库,即使用二维表格来存储数据
SQL(Structured Query Language)是“结构化查询语言”,它是对关系型数据库的操作语言。也就是说MySQL数据库使用SQL语言进行操作。
“关系型数据库管理系统(RDBMS - Relational database management system )”,即“数据库服务器”,每个服务器(RDBMS)里面能创建多个数据库(DATABASE)。
每个数据库(DATABASE)中还可以包含多张数据库表(TABLES)。
数据库表就是一个多行多列的表格。在创建表时,需要指定表的列数,以及列名称,列类型等信息。而不用指定表格的行数,行数是没有上限的。下面是tab_student表的结构,左边列是student的一些属性,右边列示对应的数据类型:
当把表格创建好了之后,就可以向表格中添加数据了。向表格添加数据是以行为单位的。下面是s_student表的记录,向表中添加的数据必须符合表结构里的数据类型:
数据库操作指令
1. 查
SHOW DATABASES; #注意sql语句必须要以分号结尾,表示语句的结束
2. 增
CREATE DATABASE [IF NOT EXISTS] 数据库名称; #创建数据库
有两种创建数据库的方法
CREATE DATABASE IF NOT EXISTS MYDAB1; #在MYDB1不存在时创建名为MYDB1的数据库,如果MYDB1存在,不会对其产生影响
CREATE DATABASE MYDB1; #创建MYDB1的数据库,如果数据库存在,报错
//当数据库不存在时,用上述两种方法都是一样的
//上述创建了一个mydb1的数据库,此时再创建同名的数据库时,这两种创建方法就不一样。
//可以看到当数据库存在时再去创建同名数据库,第一种方法CREATE DATABASE IF NOT EXISTS MYDAB1,不会报错,但有警告,原数据库中的内容也都不会改变;但是第二种方法CREATE DATABASE MYDAB1,就会报错。
3. 删
DROP DATABASE [IF EXISTS] 数据库名字; #删除数据库
4. 改
① 查看mysql编码
SHOW VARIABLES LIKE 'CHAR%';
- character_set_client:你发送的数据必须与client指定的编码一致!!!服务器会使用该编码来解读客户端发送过来的数据;
- character_set_connection:通过该编码与client一致!该编码不会导致乱码!当执行的是查询语句时,客户端发送过来的数据会先转换成connection指定的编码。但只要客户端发送过来的数据与client指定的编码一致,那么转换就不会出现问题;
- character_set_database:数据库默认编码,在创建数据库时,如果没有指定编码,那么默认使用database编码;
- character_set_server:MySQL服务器默认编码;
- character_set_results:响应的编码,即查询结果返回给客户端的编码。这说明客户端必须使用result指定的编码来解码;
② 修改
修改character_set_client、character_set_results、character_set_connection为GBK,就不会出现乱码了。但其实只需要修改character_set_client和character_set_results。
控制台的编码只能是GBK,而不能修改为UTF8,这就出现一个问题。客户端发送的数据是GBK,而character_set_client为UTF8,这就说明客户端数据到了服务器端后一定会出现乱码。既然不能修改控制台的编码,那么只能修改character_set_client为GBK了。
服务器发送给客户端的数据编码为character_set_result,它如果是UTF8,那么控制台使用GBK解码也一定会出现乱码。因为无法修改控制台编码,所以只能把character_set_result修改为GBK。
- 修改character_set_client变量:set character_set_client=gbk;
- 修改character_set_results变量:set character_set_results=gbk;
重启MySQL,发现配置的编码失效。
设置编码只对当前连接有效,这说明每次登录MySQL提示符后都要去修改这两个编码,但可以通过修改配置文件来处理这一问题:
配置文件路径:D:\Program Files\MySQL\MySQL Server 5.1\ my.ini(你安装MySQL的地址)
DDL(数据库定义语言)
数据定义语言是用来定义数据库对象:库、表、列等;
1. 增
创建表
CREATE TABLE 表名(
列名 列类型,
列名 列类型,
......
);
其中列名是自己起的名字,列类型是对应的数据类型。
常用类型:
字符 | 表示意思 |
---|---|
int | 整型 |
double | 浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99 |
decimal | 泛型,在钱方面使用该类型,因为不会出现精度缺失问题 |
char | 固定长度字符串类型 |
varchar | 可变长度字符串类型 |
text | 字符串类型 |
blob | 字节类型 |
date | 日期类型,格式为:yyyy-MM-dd |
time | 时间类型,格式为:hh:mm:ss |
timestamp | 时间戳类型;既有年月日又有时分秒 |
2. 查
- 查看当前数据库中所有的表
SHOW TABLES;
- 查看指定表的创建语句
SHOW CREATE TABLE 表名;
- 查看表结构(使用表格的形式展示)
DESC 表名;
3. 删
DROP TABLE 表名; #删除表
4. 改
- 添加列
ALTER TABLE 表名 ADD (列名 列类型);
- 修改列类型
ALTER TABLE 表名 MODIFY 列名 修改后的列类型;
- 修改列名
ALTER TABLE 表名 change 修改前列名 修改后列名 列类型;
- 删除列
ALTER TABLE 表名 DROP 列名;
- 修改表名
ALTER TABLE 原来的表名 RENAME TO 之后的表名;
DML(数据操作语言)
数据操作语言是用来定义数据库记录(数据),是对数据进行操作;
1. 增
INSERT INTO 表名[(列名1,列名2, …)] VALUES(值1, 值2, …)
- 注意:列名如果省略,即没有指定要插入的列,此时要按创建表时列的顺序插入所有列的值;也可写部分列名,此时列名必须和值一一对应
栗子:
2. 删
有两种删除方式
DELETE FROM 表名 [WHERE 条件]
重新创建一张新的表:
TRUNCATE TABLE 表名
注:
虽然TRUNCATE和DELETE都可以删除表的所有记录,但有原理不同。DELETE的效率没有TRUNCATE高!
TRUNCATE其实属性DDL语句,因为它是先DROP TABLE,再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的,但DELETE删除的记录是可以回滚的。
回滚是指事务的回滚,即撤销事务开始后的操作。举一个简单的例子,A给B转账,在数据库中就需要给A,B进行update操作。这2条sql语句必须都执行或者都不执行(称为一个事务)。假如先执行B的update语句,B的金额增加了100,然后执行A的update语句,A的金额减100。如果A的余额大于100,那么2个语句没问题,但是A的余额小于100时,再减100就变成负的了,这不符合实际情况。所以第二条sql就出现无法执行,那么数据库的状态必须回到没有执行B的update语句之前。
当一个事务执行的时候,数据库会依次执行中间的sql语句,当某一条sql发生错误以后,根据事务的原子性,通过2种方式使数据库回到事务没有执行的状态。撤销就是相当于不执行commit;回滚就是执行一遍相反的操作,比如再执行B的update金额减100。
如果commit即提交事务,事务结束;如果rollback即主动回滚之前的数据操作语句DML。注意,数据定义语句DDL不能回滚的。
3. 改
UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]
不设置条件就会修改所有满足条件的列的内容
DQL(数据查询语言)
DQL就是数据查询语言,数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端。
1. 基础查询
- 查询所有列
SELECT * FROM 表名; #查询对应表中的所有数据
- 查询指定列
SELECT 列名1,列名2,…… FROM 表名; #查询指定列名对应的数据
2. 条件查询
条件查询就是在查询时给出WHERE子句,根据WHERE后面跟的条件进行查询。
在WHERE子句中可以使用如下运算符及关键字:
运算符或关键字 | 解释 |
---|---|
= | 等于 |
!= | 不等于 |
<> | 不等于(!=是 SQL92标准 <>是现行标准) |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN…AND | 在什么之间(包含两端值) |
IN(value1,value2,…) | 允许WHERE子句中规定多个值 |
IS NULL | 筛选出NULL值 |
AND | 二者满足时返回结果 |
OR | 其中一个条件满足就返回结果 |
NOT | 求反 |
3. 模糊查询
SELECT 字段 FROM 表 WHERE 某字段 Like 条件
其中关于条件,SQL提供了四种匹配模式:
% :表示任意0个或多个字符,可匹配任意类型和长度的字符。
_ : 表示任意单个字符,可匹配单个任意字符,它常用来限制表达式的字符长度语句:
[ ] :表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。
[^ ] :表示不在括号所列之内的单个字符。其取值和 [] 相同,但它要求所匹配对象为指定字符以外的任一个字符。
4. 字段控制查询
- 删除重复列(DISTINCT)
SELECT DISTINCT 列名 FROM 表名;
- 加运算
SELECT 列名1+IFNULL(列名2) FROM 表名; #一般如果列名有NULL值,NULL和任何值相加都是NULL,所以要用到IFNULL函数把NULL转换成数值0
- 给列名添加别名(AS)
SELECT 列名 AS 别名 FROM 表名;
5. 排序(ORDER BY)
- 升序
SELECT * FROM 表名 ORDER BY 列名 [ASC]; #ASC可省略,因为默认为升序排列
原始数据表:
升序排序后
- 降序
SELECT * FROM 表名 ORDER BY 列名 DESC;
- 混搭
SELECT * FROM 表名 ORDER BY 列名1 DESC,列名2 ASC; #在列名1降序排列时,遇到相同排位的按列名2升序排列
6. 聚合函数
函数名 | 解释 |
---|---|
COUNT() | 统计指定列不为NULL的记录行数; |
SUM() | 计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0; |
AVG() | 计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0; |
MAX() | 计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算; |
MIN() | 计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算 |
7. 分组查询(GROUP BY)
- 分组查询
SELECT 列名1/聚合函数 FROM 表名 GROUP BY 列名2; #先通过列名2进行分组之后对列名1进行操作
- HAVING 字句
SELECT 列名1/聚合函数 FROM 表名 GROUP BY 列名2 HAVING 条件; #先通过列名2进行分组,再根据HAVING筛选出满足的条件,然后利用筛选出的数据对列名1进行操作
注意,WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而HAVING是对分组后数据的约束。
8. LIMIT
LIMIT用来限定查询结果的起始行,以及总行数(可用来分页查询)。
SELECT * FROM 表名 LIMIT 起始行, 查询的行数;
DCL(数据控制语言)
数据控制语言是用来定义访问权限和安全级别;
1. 创建用户
CREATE USER 用户名@地址 IDENTIFIED BY '密码';
注:地址设置为’%’表示创建的该用户能在任何电脑上登录;若地址设置为localhost表示创建的该用户只能在localhost这个IP登录MySQL服务器。
2. 给用户授权
GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@地址
3. 撤销授权
REVOKE 权限1, … , 权限n ON 数据库.* FORM 用户名@地址
4. 查看用户权限
SHOW GRANTS FOR 用户名@地址
5. 删除用户
DROP USER 用户名
6. 修改用户密码
USE mysql;
UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;
FLUSH PRIVILEGES; #刷新一下权限