MySQL 数据库,SQL,数据类型,事务

最新推荐文章于 2024-08-09 21:25:57 发布

EdVzAs

最新推荐文章于 2024-08-09 21:25:57 发布

阅读量186

点赞数

文章标签：数据库 mysql 事务

本文链接：https://blog.csdn.net/weixin_46131409/article/details/105619469

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一.数据库
1.数据库(database,DB)

指长期存储在计算机内的,有组织,可共享的数据的集合
数据库中的数据按一定的数学模型组织/描述/存储,具有较小的冗余,较高的数据独立性和易扩展性,并可为各种用户共享
一个数据库包括多张表,数据存储在表中

2.数据库管理系统软件(Database Management System)

是一种操纵和管理数据库的大型软件,用于建立/使用/维护数据库,简称DBMS
对数据库进行统一的管理和控制,以保证数据库的安全性和完整性:用户通过DBMS访问数据库中的数据,数据库管理员也通过DBMS进行数据库的维护工作
可使多个应用程序和用户用不同的方法在同时或不同时刻去建立/修改/询问数据库
大部分DBMS提供数据定义语言DDL(Data Definition Language)和数据操作语言DML(Data Manipulation Language),用于定义数据库的模式结构/权限约束,实现对数据的追加/删除等操作
是数据库系统的核心,是管理数据库的软件:DBMS就是实现把用户意义下抽象的逻辑数据处理,转换成为计算机中具体的物理数据处理的软件;有了数据库管理系统,用户就可以在抽象意义下处理数据,而不必顾及这些数据在计算机中的布局和物理位置
常见的DBMS:甲骨文的Oracle,IBM的DB2(安全),SQL Server,Access,MySQL(开源,免费,跨平台)

3.数据库系统(Data Base System,DBS)

通常由软件,数据库,数据管理员组成
软件主要包括操作系统,各种宿主语言,实用程序,数据库管理系统
数据库由数据库管理系统统一管理:数据的插入/修改/检索均要通过数据库管理系统进行
数据管理员负责创建/监控/维护整个数据库,使数据能被任何有权使用的人有效使用

在这里插入图片描述
二.MySQL
1.MySQL的管理
(1)Linux:

--yum -y install mariadb mariadb-server
                 or
--yum -y install mysql mysql-server

(2)Windows:

--http://dev.mysql.com/downloads/mysql

(3)MacOS:

http://dev.mysql.com/downloads/mysql/

2.启动

service mysqld start--开启
chkconfig mysqld on--设置开机自启
          OR
systemctl start mariadb
systemctl enable mariadb

3.查看

ps aux |grep mysqld--查看进程
netstat -an |grep 3306--查看端口

4.设置密码

mysqladmin -uroot password '123'--设置初始密码，初始密码为空因此-p选项没有用
mysqladmin -u root -p123 password '1234--修改root用户密码

5.登录退出

--登录:
mysql [-u user_name,-p pwd,-h ser_IP,-P port,--prompt prom,－－delimiter sep]:登录
mysql －h 127.0.0.1 -P 3306 -uroot -p123
--参数说明:
user_name:指定用户名,默认为root
pwd:指定密码;可回车后再输入,此时显示密文
ser_IP:指定服务器IP;默认为本地(127.0.0.1)
port:指定端口号,默认为3306
prom:指定命令提示符
sep:指定分隔符

mysql--本地登录,默认用户root,空密码,用户为root@127.0.0.1
mysql -uroot -p1234--本地登录,指定用户名和密码,用户为root@127.0.0.1
mysql -uroot -p1234 -h 192.168.31.95--远程登录,用户为root@192.168.31.95

#退出:
\q
quit
exit

三.MySQL常用命令

启动mysql服务与停止mysql服务命令：
net start mysql
net stop  mysql
\s;   ------my.ini文件：[mysql] default-character-set=gbk [mysqld] character-set-server=gbk
prompt 命令提示符（\D:当前日期 \d:当前数据库  \u:当前用户）
\T(开始日志) \t(结束日志)
show warnings;
help() ? \h
help/-h:查看帮助
\G；
select now()
select version();
select user;
\c:取消命令
delimiter 指定分隔符

四.处理忘记密码
1.启动MySQL时,跳过授权表:

[root@controller ~]# service mysqld stop
[root@controller ~]# mysqld_safe --skip-grant-table &
[root@controller ~]# mysql
mysql> select user,host,authentication_string from mysql.user;
+----------+-----------------------+-------------------------------------------+
| user     | host                  | authentication_string                     |
+----------+-----------------------+-------------------------------------------+
| root     | localhost             | *A4B6157319038724E3560894F7F932C8886EBFCF |
| root     | localhost.localdomain |                                           |
| root     | 127.0.0.1             |                                           |
| root     | ::1                   |                                           |
|          | localhost             |                                           |
|          | localhost.localdomain |                                           |
| root     | %                     | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-----------------------+-------------------------------------------+
mysql> update mysql.user set authentication_string=password("123") where user="root" and host="localhost";
mysql> flush privileges;
mysql> exit
[root@controller ~]# service mysqld restart
[root@controller ~]# mysql -uroot -p123

2.删库:

删除与权限相关的库mysql，所有的授权信息都丢失，主要用于测试数据库或者刚刚建库不久没有授权数据的情况（从删库到跑路）
[root@controller ~]# rm -rf /var/lib/mysql/mysql
[root@controller ~]# service mysqld restart
[root@controller ~]# mysql

五.SQL及其规范
1.结构化查询语言(Structured Query Language,SQL):SQL是专为数据库而建立的操作命令集,是一种功能齐全的数据库语言

使用时只需要发出"做什么"的命令,不需要考虑"怎么做”
SQL功能强大,简单易学,使用方便,已经成为了数据库操作的基础,并且现在几乎所有的数据库均支持SQL

2.语法规范
(1)在数据库系统中,SQL语句不区分大小写:

但字符串常量区分大小写
建议命令大写,表名库名小写

select now();#打印当前时间
    =
SELECT now();
    =
SeLEct now();

(2)可单/多行书写:支持折行操作

以";“结尾,不加”;"就回车仍视为一行
关键词不能跨多行/简写

mysql> SELECT
     > now()
     > ;
     =
mysql> SELECT now();

(3)用缩进来提高语句的可读性(非强制):子句通常位于独立行,便于编辑,提高可读性

SELECT * FROM tb_table
            WHERE NAME="YUAN";
            =            
SELECT * FROM tb_table WHERE NAME="YUAN";

(4)注释:

单行注释:--...
多行注释:/*...*/

(6) DDL,DML,DCL,DQL:
blog.csdn.net/weixin_43844810/article/details/86666586
cnblogs.com/fan-yuan/p/7879353.html

数据定义语言(DDL):用于创建数据库的各种对象
数据操作语言(DML):用于实现对数据库的基本操作
数据查询语言(DQL):用于查询(而不修改)数据库中数据
数据控制语言(DCL):用于对数据访问权限进行控制,控制事务发生的时间/效果,对数据库实行监视等

六.数据库操作语言(DDL)

MySQL有2个自带的数据库:information_schema和mysql,储存权限/密码等信息

--创建数据库(在磁盘上创建一个对应的文件夹)
  --MySQL数据库默认放在C:\\ProgrammData\MySQL\MySQL Server 版本号\Data
  create database [if not exists] <db_name> [character set <xxx>];
    --if not exists:不存在时创建,否则不操作
    --character set xxx:设置字符编码方式为xxx,默认为utf-8

--查看数据库
  查看所有数据库:show databases;
    mysql> show databases;
    +--------------------+
    | Databases          |
    +--------------------+
    | information_schema |
    | mysql              |
    +--------------------+
  查看数据库的创建方式:show create database db_name;
  查看警告信息:show warnings;

--修改数据库
  --可以修改编码方式/校验信息
  alter database <db_name> [character set <xxx>];

--删除数据库
  drop database [if exists] <db_name>;
    --1次只能删除1个数据库
    --if exists:存在时删除,否则不进行操作

--使用数据库
  切换数据库:use <db_name>;
    --注意:进入某数据库后无法退回之前状态,但可通过use进行切换
  查看当前使用的数据库:select database();

七.MySQL数据类型
1.数值类型
在这里插入图片描述

·各个INT类型区别在于可容纳的最大值/占用空间不同
INT(x):x表示显示的最小长度(没有zerofill时无用)
习惯使用TINYINT(1)表示布尔值

·FLOAT(x,y)/DOUBLE(x,y):x表示总位数,y表示小数点后位数,溢出则无法储存

2.日期和时间类型

每个时间类型有1个有效值范围和1个0值,当指定不合法的MySQL不能表示的值时使用0值

在这里插入图片描述
3.字符串类型

·CHAR和VARCHAR类似
但保存方式/检索方式/最大长度/是否尾部空格被保留等方面不同
在存储/检索过程中不进行大小写转换
·CHAR(x):x表示占用的字节数;只能为指定字节数,不足则用空格不足
·VARCHAR(x):x表示最大占用字节数

·BINARY和VARBINARY类似于CHAR和VARCHAR
但它们包含字节字符串(二进制字符串)而非字符字符串(非二进制字符串)

·BLOB是1个二进制大对象,可以容纳可变数量的数据
有4种BLOB类型:TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB
4种BLOB类型区别只在可容纳值的最大长度不同

·TEXT是1个文本大对象,可以容纳可变数量的数据
有4种TEXT类型:TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT
这些对应4种BLOB类型,有相同的最大长度/存储需求

八.事务
1.事务命令
(1)事务:逻辑上的一组操作;组成这组操作的各个单元,要么全部成功,要么全部不成功

(2)数据库开启事务命令:

START TRANSACTION:开启事务
  --开启事务后输入的命令将在提交后一并执行
  --可在事务中使用Rollback,savepoint,commit命令
ROLLBACK:回滚事务(撤销本次事务内的所有命令)
  --即撤销指定的sql语句,回滚到上一次commit的位置
  --只能回退insert,delete,update语句
COMMIT:提交事务
  --存储未存储的事务,提交命令到数据库
SAVEPOINT p_name_:设置保留点
  --参数说明:
    p_name:保留点名
  --事务处理中设置的临时占位符
  --可以对其发布回退(与整个事务回退不同)
ROLLBACK TO p_name_:退回到保留点p_name
  --回滚到较前的保留点后,之后的保留点失效

(3)实例:

create table test2 (
        id INT PRIMARY KEY auto_increment,
        name VARCHAR(20)
) engine=innodb;
INSERT INTO test2(name)
        VALUE ("alvin"),("yuan"),("xialv");

--开启事务:
start transaction;
insert into test2 (name) values('silv');--未提交到数据库
select * from test2;--显示已经改变,实际(从新窗口查看)未变
commit;--提交到数据库进行存储

--保留点:
start transaction;
insert into test2 (name)values('wu');
savepoint insert_wu;--设置保留点insert_wu
select * from test2;

delete from test2 where id=4;
savepoint delete1;
select * from test2;

delete from test2 where id=1;
savepoint delete2;
select * from test2;

rollback to delete1;--退回到保留点delete1
select * from test2;

转账实例:

CREATE TABLE account(id INT,name VARCHAR(20),balance DOUBLE); 

INSERT INTO account VALUES(1,"cunzhang",8000);
INSERT INTO account VALUES(2,"zhengwen",8000);

START TRANSACTION;

UPDATE account set balance=balance-5000 WHERE name=”yuan”;
SELECT * FROM account;
ROLLBACK;--退回当前事务开始状态,撤销事务中所有sql语句
SELECT * FROM account;
UPDATE account set balance=balance-5000 WHERE name=”yuan”;
UPDATE account set balance=balance+5000 WHERE name=”xialv”;

COMMIT;

2.Python中调用数据库启动事务的方式:

利用conn_.commit()和conn_.rollback()

--实例:
	import pymysq

	conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='',db='yyy')
	cursor=conn.cursor()

	try:
	    --第1个事务:
	    insertSQL0="INSERT INTO ACCOUNT2 (name,balance) VALUES ('oldboy',4000)"
	    --第2个事务:
	    insertSQL1="UPDATE account2 set balance=balance-30 WHERE name='yuan'"
	    insertSQL2="UPDATE account2 set balance=balance+30 WHERE name='xialv'"
	    cursor=conn.cursor()
	    cursor.execute(insertSQL0)--完成第1条命令
	    conn.commit()--提交第1个事务
	    cursor.execute(insertSQL1)--完成第2条命令
	    raise Exception--模拟发生错误
	    cursor.execute(insertSQL1)--重新完成第2条命令
	    cursor.execute(insertSQL2)--完成第3条命令
	    conn.commit()--提交第2个事务

	except Exception as e:
	    conn.rollback()--回滚到上次.commit()
	    conn.commit()

	cursor.close()
	conn.close()

3.事务特性
(1)原子性(Atomicity):事务是一个不可分割的工作单位

事务中的操作要么都发生,要么都不发生

(2)一致性(Consistency):事务前后数据的完整性必须保持一致

在事务执行之前数据库是符合数据完整性约束的,无论事务是否执行成功,事务结束后数据库中的数据也应符合完整性约束
在某一时间点,如果数据库中所有记录都能保证满足当前数据库中的所有约束,则说当前的数据库是符合数据完整性约束的
比如删部门表前应该删掉关联员工(已建立外键),如果数据库服务器发生错误,有一个员工没删掉,那么此时员工的部门表已经删除,那么就不符合完整性约束了,这样的数据库性能也就太差了！

(3)隔离性(Isolation):多个用户并发访问数据库时,一个用户的事务不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离

将数据库设计为串行化程的数据库,让一张表在同一时间内只能有一个线程来操作;如果将数据库设计为这样,那数据库的效率太低了;所以数据库的设计这没有直接将数据库设计为串行化,而是为数据库提供多个隔离级别选项,使数据库的使用者可以根据使用情况自己定义到底需要什么样的隔离级别
不考虑隔离性可能出现的问题:

--脏读:
  --指1个事务读取到了另1个事务未提交的数据
  --这是特别危险的,要尽力防止
    a 1000
    b 1000
    a:
        start transaction；
        update set money=money+100 where name=b;
    b:
        start transaction;
        select * from account where name=b;--1100
        commit;
    a:
        rollback;
    b:
        start transaction;
        select * from account where name=b;--1000

--不可重复读:
  --在一个事务内读取表中的某一行数据,多次读取结果不同
  --(一个事务读取到了另一个事务已经提交的数据--增删改)
  --在某写情况下并不是问题,在另一些情况下就是问题。
    a:
    start transaction;
	select 活期账户 from account where name=b;--1000    活期账户：1000
	select 定期账户 from account where name=b；--1000   定期账户:1000
	select 固定资产 from account where name=b；--1000   固定资产：1000

	b:
	start transaction;
	update set money=0 where name=b;
	commit;

	select 活期+定期+固定 from account where name=b; --2000 总资产： 2000

--虚读:
  --指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致
  --(一个事务读取到了另一个事务已经提交的数据---增删除)
  --在某写情况下并不是问题，在另一些情况下就是问题
	b 1000
	c 2000
	d 3000
	a:
	start transaction
	select sum(money) from account;---3000    3000
	d:start transaction;
	insert into account values(d,3000);
	commit;
	select count(*)from account;---3    3
	3000/3 = 1000    1000

(4)持久性(Durability):一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

4.4个隔离级别:

Serializable:可避免脏读/不可重复读/虚读(串行化)
Repeatable read:可避免脏读/不可重复读(可重复读),不可以避免虚读
Read committed:可避免脏读(读已提交)
Read uncommitted:最低级别,以上情况均无法保证(读未提交)

安全性考虑:Serializable>Repeatable read>Read committed>Read uncommitted
数据库效率：Read uncommitted>Read committed>Repeatable read>Serializable
一般情况下会使用Repeatable read,Read committed
MySQL数据库默认的隔离级别为Repeatable read

5.MySQL中设置数据库隔离级别:

SET [GLOBAL/SESSION] TRANSACTION ISOLATION LEVEL lev_;
--参数说明:
  GLOBAL:声明修改的是数据库的默认隔离级别;所有新窗口的隔离级别继承自这个默认隔离级别
  SESSION:声明修改的是当前客户端的隔离级别,和数据库默认隔离级别无关
  lev:声明隔离级别

select @@tx_isolation;

EdVzAs

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL 数据库,SQL,数据类型,事务

一.数据库1.数据库(database,DB):指长期存储在计算机内的,有组织,可共享的数据的集合数据库中的数据按一定的数学模型组织/描述/存储,具有较小的冗余,较高的数据独立性和易扩展性,并可为各种用户共享2.数据库管理系统软件(Database Management System)是一种操纵和管理数据库的大型软件,用于建立/使用/维护数据库,简称DBMS对数据库进行统一的管理...
复制链接

扫一扫