mysql基础
配置文件
my.ini
[mysqld]
basedir=E:\mysql\mysql-5.7.36-winx64
datadir=E:\mysql\mysql-5.7.36-winx64\data
port=3306
skip-grant-tables
基本命令行
- 连接数据库
mysql -u root -p 123456 --连接数据库
- 修改用户密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
- 刷新权限
flush privileges;
所有的语句以';'结尾
常用基本命令
show databases; -- 查看所有的数据库
use school; -- 切换数据库
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息
creat database westos;-- 创建数据库
exit; -- 退出连接
-- 单行注释
创建数据库表
1.创建样例
CREATE TABLE `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
PRIMARY KEY(`id`)
)
2.总结格式
CREATE TABLE `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
3.注意
- 表名称和字段尽量使用``括起来
- PRIMARY KEY表示主码,每个表只有一个
- 字符串只用必须要使用''括起来
- 创建每行都要加逗号,除了最后一行
操作数据库及表
- 创建数据库:
CREATE DATABASE [IF NOT EXIXTS] 名
- 删除数据库;
DROP DATABASE [IF NOT EXITS]名
- 使用数据库
use 名
mysql中的特殊字符名被使用需要使用``括起来
- 查看所有数据库
SHOW DATABASE
- 查看创建数据库的语句
SHOW CREATE DATABASE school
- 查看student数检测表的定义语句
SHOW CREATE TABLE student
- 展示表的结构
DESC student
修改表
- 修改表名语句
ALTER TABLE 旧表名RENAME AS 新表名
ALTER TABLE `student` RENAME `xuesheng`
- 增加表的字段
ALTER TABLE 表名 ADD 新字段名 [属性] [约束]
ALTER TABLE `xuesheng` ADD phone BIGINT(10) DEFAULT NULL COMMENT '2323'
- 修改表的字段
- MODIFY(只能修改约束)ALTER TABLE 表名MODIFY 字段 新约束
- CHANGE(用于修改字段名称): **ALTER TABLE 表名 CHANGE 旧字段名 新字段名 **
- 删除表
DROP TABLE IF EXITS 表名
- 删除表的字段
ALTER TABLE 表名 DROP 字段名
- 查看系统版本
select version()
数据库的列类型
数值
| name | 介绍 | 大小 |
|---|---|---|
| tinyint | 十分小的数据 | 1B |
| smallint | 较小的数据 | 2B |
| mediumint | 中等大小的数据 | 3B |
| int | 标准的整形 | 4B |
| big | 较大的数据 | 8B |
| float | 浮点数 | 4B |
| double | 浮点数 | 8B |
| decimal | 字符串式的浮点数 |
字符串
| name | 介绍 | 大小 |
|---|---|---|
| char | 字符串固定大小 | 0~255 |
| varchar | 可变的字符串 | 0~65535 |
| tinytext | 微型文本 | 2^8-1 |
| text | 文本串 | 2^16-1 |
时间日期
- data YYYY-MM-DD,日期格式
- time HH: mm: ss ,时间格式
- datetime YYYY-MM-DD HH: mm: ss 最常用的时间格式
- timestamp 时间戳,1970.1.1到现在的毫秒数 ,也较为常用
- year 年份表示
null
- 没有值,未知
-
注意不要使用NULL进行运算,结果为NULL
数据库的字段属性
Unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill:
- 0填充
- 不足的位数,使用0来填充
自增:
- 通常理解为自增,自动在上一条记录的基础上加1
- 通常用来设置唯一的主键~ 同index,必须是整数类型
- 可以自定义设置主键自增的起始值和步长
非空:
- 默认null
默认:
- 设置默认值
- 不指定该列的值,则会有默认的值
扩展:(阿里巴巴规范)
/*每个表,都必须存在一下五个字段,未来做项目用*/
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
管理数据
实例
CREATE TABLE `grade`(
`id` INT(4) AUTO_INCREMENT NOT NULL COMMENT '主',
`name` VARCHAR(10) NOT NULL COMMENT '年级',
PRIMARY KEY(`id`)
)
CREATE TABLE `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`gradeid` INT(10) NOT NULL COMMENT '年级',
`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
PRIMARY KEY(`id`),
CONSTRAINT `Fk_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`id`)
)
- 公式:CONSTRAINT
FK_名FOREIGN KEY (名) REFERENCES 引用的表(引用的字段)
DML语言
简介:
- insert 插入
- update 修改
- delete 删除
1.插入
INSERT INTO `student` VALUE (5,'周',1,'2004-11-3'),(6,'张',4,'2005-3-5')
insert into 表名 ([字段名1, 字段名2,字段名3....]) values('值1', '值2', '值3')
**多次插入:insert into 表名(字段名) values('值1'), ('值2'), ('值3') **
2.修改
UPDATE `student` SET `name`= '航' WHERE `grade`= 4
UPDATE 表名 set colnum_name = value where [条件]
修改多个属性,逗号隔开:UPDATE 表名 set colnum_name = value, colnum_name = value where [条件]
value可以是个变量
条件:
| 操作符 | 含义 | 示范 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | 5<>6 | true |
| < | |||
| > | |||
| <= | |||
| >= | |||
| between...and... | 在某个范围内 | [2,5] | |
| AND | 同&& | ||
| OR | 同|| |
3.删除:
DELETE FROM `student` WHERE `grade`=3
delete from 表名 [where 条件]
拓展:truncate命令,作用完全清空一个数据库表,结构不变
delete和truncate的区别
相同点:都能删除,但都不会删除表的结构
不同:
truncate 重新设置自增列,计数器会归零
truncate不会影响事务
关于delete删除问题(重启数据库)
- innoDB 自增列会重1开始(存在内存中,断电即失)
- MYISAM 继续从上一个自增量开始(存在文件中,不会丢失)
sql语法
查询
关键字:distinct:去除重复的列
指定全部字段
select * from student
查询指定字段
select id from student
- 给表起别名 :AS
select `grade` as 年级 , `id` as 学生姓名 from student as s
- 函数
select concat('姓名:' ,studentName) as 新名字 from student
语法:select ....from 表
- 计算结果
select 100*3 as 计算结果
- 查询系统版本
select 100*3 as 结果
- 查询自增的步长
select @@auto_increment_increment
- 查询的数据加1
select `studentResult`+1 as '提分后' from result
where条件子句
搜索条件由一个或多个表达式组成,结果为布尔值
模糊查询:
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 如果操作符为null,结果为真 |
| is not null | a is not null | 如果操作符不为null,结果为真 |
| between | a between b and c | 若a在b和c之间,结果为真 |
| like | a like b | sql匹配,如果a能匹配到b,则结果为真 |
| in | a in (a1,a2,a3) | 如果a在括号中,则结果为真 |
- %代表有任意个字符
- _代表有一个字符
实例
select `studentNo`, `studentName`,from `student`
where studentName like '刘%'
--查询姓刘的同学
联表查询
实例
SELECT s.studentno, `studentname`, `subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student AS s
LEFT JOIN result r
ON s.studentno = r.`studentno`
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即便右表中没有匹配 |
| right join | 会从右表中返回所有的值，即便左表中没有匹配 |
三种的区别
假设a表有两个字段，Aid、name, b 表也有两字段 Bid,nameid
其中 nameid是Aid在b 表中的外键。
a表有数据如下:
Aid name
1 a
2 b
3 c
b表有数据如下：
Bid nameid
1 1
2 1
3 1
4 2
5 2
用左连接,就是以左边那个表为标准，左边那表（表a）的所有记录必须得全部出现，例如：
select * from a left join b on a.Aid=b.nameid
结果应应该为:
Aid name Bid nameid
1 a 1 1
1 a 2 1
1 a 3 1
2 b 4 2
2 b 5 2
3 c NULL NULL
表a 的 Aid为1的出现了三次，Aid为2的出现了两次，Aid为3的出现了一次,全部都出现了。但因为在表b中没有以Aid为3对应的记录，所以用null来填充。
同样道理，用右连接，就是以右边那个表为标准，
例如：select * from a right join b on a.Aid=b.nameid
结果应该为:
Aid name Bid nameid
1 a 1 1
1 a 2 1
1 a 3 1
2 b 4 2
2 b 5 2
表b的记录必须全部出现，但没有出现 a表中Aid为3的记录，是因为b表中没有以之对应的记录，而且此时是以b表为标准的。
嵌套查询
通过一个select返回的值作为另一个select的条件
分页和排序
排序
-- 升序
SELECT s.studentNo,s.studentName,r.`studentresult`,subjectname
FROM student AS s
INNER JOIN result AS r
ON s.studentNo=r.studentNo
INNER JOIN `subject` AS sub
ON sub.`subjectno` = r.`subjectno`
ORDER BY studentresult ASC
-- 降序
SELECT s.studentNo,s.studentName,r.`studentresult`,subjectname
FROM student AS s
INNER JOIN result AS r
ON s.studentNo=r.studentNo
INNER JOIN `subject` AS sub
ON sub.`subjectno` = r.`subjectno`
ORDER BY studentresult DESC
- desc:降序
- asc:升序
分页
limit xx,xx
前边的xx代表要作为页的第一个的起始数据
后边的xx代表每页有几个数据
数据从0开始
mysql函数
常用函数
--数学函数
ABS(-4)-- 绝对值
ceiling(3.5)-- 向上取值
floor(34,5)-- 向下取值
rand()-- 生成0到1之间的任意数
sign(10)-- 判断正负号,返回正负一
-- 字符串函数
char_length('dafd')-- 字符串长度
concat(a,b)-- 返回拼接字符串
insert('afdaDF',1,2,'DAFD')-- 查询替换
lower('asdfa')-- 转小写
upper('asdfads')-- 转大写
instr('asdf','df')-- 返回第二个出现在第一个的索引
replace('adsfasdfadsf','打发大水','发电房')-- 在第一个中把第二个出现的字符串变成第三个字符串
substr('afdsfadsfa', 4,5)-- 返回从第四个开始的一共五个字符串大小的字符串,在第一个中取
reverse('adsfasdfasd')-- 翻转字符串
聚合函数(常用)
| 函数名称 | 描述 |
|---|---|
| count() | 计数,count(字段)会忽略null值,count(*)和count(1)会统计所有,count(1)会按行查,把字段都变成一 |
| sum() | 求和 |
| avg() | 平均 |
| max() | 最大值 |
| min() | 最小值 |
| ... | ... |
事务管理（ACID）
谈到事务一般都是以下四点
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
举个简单的例子理解以上四点
原子性
针对同一个事务
这个过程包含两个步骤
A： 800 - 200 = 600
B: 200 + 200 = 400
原子性表示，这两个步骤一起成功，或者一起失败，不能只发生其中一个动作
一致性（Consistency）
针对一个事务操作前与操作后的状态一致
操作前A：800，B：20
操作后A：600，B：400
一致性表示事务完成后，符合逻辑运算
持久性（Durability）
表示事务结束后的数据不随着外界原因导致数据丢失
操作前A：800，B：200
操作后A：600，B：400
如果在操作前（事务还没有提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：800，B：200
如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：600，B：400
隔离性（Isolation）
针对多个用户同时操作，主要是排除其他事务对本次事务的影响
事务一）A向B转账200
事务二）C向B转账100
两个事务同时进行，其中一个事务读取到另外一个事务还没有提交的数据，执行步骤如图所示，按照数字顺序执行
隔离性用于解决以上问题
事务的隔离级别
脏读：
指一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
页面统计查询值
生成报表的时候，B有人转账进来300（B事务已经提交）
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
（一般是行影响，如下图所示：多了一行）
四种隔离级别设置
数据库
set transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别
设置 描述
Serializable 可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read 可避免脏读、不可重复读情况的发生。（可重复读）
Read committed 可避免脏读情况发生（读已提交）。
Read uncommitted 最低级别，以上情况均无法保证。(读未提交)
java
适当的 Connection 方法，比如 setAutoCommit 或 setTransactionIsolation
设置 描述
TRANSACTION_SERIALIZABLE 指示不可以发生脏读、不可重复读和虚读的常量。
TRANSACTION_REPEATABLE_READ 指示不可以发生脏读和不可重复读的常量；虚读可以发生。
TRANSACTION_READ_UNCOMMITTED 指示可以发生脏读 (dirty read)、不可重复读和虚读 (phantom read) 的常量。
TRANSACTION_READ_COMMITTED 指示不可以发生脏读的常量；不可重复读和虚读可以发生
实例
SET autocomit = 0;-- 事务自动提交关闭
SET autocomit = 1;-- 事务自动提交开启
-- 事务开启
START TRANSACTION -- 标记一个事务的开启,从这个之后的sql都在一个事务里
INSERT xxx
INSERT xxx
-- 提交
COMMIT
-- 回滚
ROLLBACK
-- 事务结束
-- 开启默认状态
SET autocommit = 1;
-- 以下了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
-- 实例
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8
INSERT INTO account(`name`,`money`) VALUE ('wang', 2000.00),('qu',300.00)
SET autocommit = 0;
START TRANSACTION -- 开启事务
UPDATE account SET money = money-500 WHERE `name` = 'wang';
UPDATE account SET money = money+500 WHERE `name` = 'qu'
COMMIT
ROLLBACK
SET autocommit = 1;-- 恢复默认
