mysql数据库基础 博客园_MySQL数据库基础

数据库的一些概念题：

DB DBMS 和DBS三者之间的关系

DBS(数据库系统) 包含DB(数据库)和DBMS(数据库管理系统)

DB：是指datebase(数据库)

DBS:是指datebase systerm (数据库系统)

DBMS:是指datebase mangement systerm(数据库管理系统)

区别：数据库就是存放数据的仓库，数据库管理系统就是用来管理数据库的，其中数据库系统是一个通称，包括数据库、数据库管理系统、数据库管理人员等的统称，是最大的范畴。

常见存储引擎的类型

需改表alter

4.修改表结构：

语法：1. 修改表名

ALTER TABLE 表名

RENAME 新表名;2. 增加字段

ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…],

ADD 字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…] FIRST;

ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;3. 删除字段

ALTER TABLE 表名

DROP 字段名;4. 修改字段

ALTER TABLE 表名

MODIFY 字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名

CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

ALTER TABLE 表名

CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

外键的设置

在员工表里面先设置 emp_id的字段然后再关联 emp表里面的字段

1、安装mysql和navicat

2、Mysql数据语法学习

数据库的种类：关系型数据库和非关系型数据库

mysql数据库的操作

查看数据库

show databases;

show create database db1;selectdatabase();

选择数据库

USE 数据库名

删除数据库

DROP DATABASE 数据库名;

修改数据库

alter database db1 charset utf8;

查入数据insert

1. 插入完整数据(顺序插入)

语法一：

INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

语法二：

INSERT INTO 表名 VALUES (值1,值2,值3…值n);2. 指定字段插入数据

语法：

INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);3. 插入多条记录

语法：

INSERT INTO 表名 VALUES

(值1,值2,值3…值n),

(值1,值2,值3…值n),

(值1,值2,值3…值n);4. 插入查询结果

语法：

INSERT INTO 表名(字段1,字段2,字段3…字段n)

SELECT (字段1,字段2,字段3…字段n) FROM 表2

WHERE …;

更新数据UPDATE

语法：

UPDATE 表名 SET

字段1=值1,

字段2=值2,

WHERE CONDITION(条件);

示例：

UPDATE mysql.user SET password=password(‘123’)

where user=’root’ and host=’localhost’;

一定要加where

mysql表的操作

表的数据类型：

#1. 数字：

整型：tinyinitintbigint

小数：float：在位数比较短的情况下不精准double：在位数比较长的情况下不精准0.000001230123123123存成：0.000001230000

decimal：(如果用小数，则用推荐使用decimal)

精准

内部原理是以字符串形式去存

#2. 字符串：char(10)：简单粗暴，浪费空间，存取速度快

root存成root000000

varchar：精准，节省空间，存取速度慢

sql优化：创建表时，定长的类型往前放，变长的往后放

比如性别 比如地址或描述信息>255个字符，超了就把文件路径存放到数据库中。

比如图片，视频等找一个文件服务器，数据库中只存路径或url。

#3. 时间类型：

最常用：datetime

#4. 枚举类型与集合类型enum('male','female')set('play','music','read','study')

MariaDB [db1]> create table consumer(

-> name varchar(50),

-> sex enum('male','female'),

-> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一

-> hobby set('play','music','read','study') #在指定范围内，多选多

-> );

表的约束

#1、建立表关系：

#先建被关联的表,并且保证被关联的字段唯一 #----- 父表--------create table dep(

idintprimary key,

namechar(16),

commentchar(50)

);

部门表和员工表 foreign key设置在员工表

#再建立关联的表

create table emp(

idintprimary key,

namechar(10),

sexenum('male','female'),

dep_idint,

foreign key(dep_id) references dep(id)

on delete cascade # 删除同步 只要被关联的 ，关联的 也删除 解散it部门，其他关联it部门的员工也删除

on update cascade # 更新同步，

);

表与表之间的关系

分析步骤：

#1、先站在左表的角度去找

是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key(外键) 右表一个字段(通常是id)

#2、再站在右表的角度去找

是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段(通常是id)

#3、总结：

#多对一：

如果只有步骤1成立，则是左表多对一右表

如果只有步骤2成立，则是右表多对一左表

#多对多

如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:

如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

mysql字段的操作

mysql表的查询

单表查询和多表查询

select count(学号) as “总人数” FROM student;

这个as可以写也可以不写

单表查询：

http://www.cnblogs.com/linhaifeng/articles/7267592.html

1.单表查询的语法2.关键字的执行优先级(重点)3.简单查询4.where约束5.分组查询：group by 在where之后查询6.having 过滤7.查询排序：order by

8.限制查询得记录数：limit9.使用正则表达式查询

多表查询：

SELECT DISTINCT FROM

JOIN ONWHEREGROUP BYHAVINGORDER BYLIMIT

内连接：只取两张表的共同部分select * from employee inner join department on employee.dep_id =department.id ;

左连接:在内连接的基础上保留左表的记录select * from employee left join department on employee.dep_id =department.id ;

右连接：在内连接的基础上保留右表的记录select * from employee right join department on employee.dep_id =department.id ;

全外连接：在内连接的基础上保留左右两表没有对应关系的记录select * from employee full join department on employee.dep_id =department.id ;select * from employee left join department on employee.dep_id =department.id

unionselect * from employee right join department on employee.dep_id = department.id ;

7.4 使用事务保证数据完整性

事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行。

注意：

MySQL事务处理只支持InnoDB和Berkeley DB数据表类型

ACID，指数据库事务正确执行的四个基本要素的缩写。

包含：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。

一个支持事务(Transaction)的数据库，必须要具有这四种特性，否则在事务过程(Transaction processing)当中无法保证数据的正确性。

使用场景：

银行的交易系统

原子性：执行事务要么都成功，要么都不成功

一致性：执行后，保存数据的一致性

隔离性：事务之间相互独立，互不影响

持久性：数据永远存储在数据库中

start transaction = begin等效

start transaction;

update user set balance=900 where name='wsb'; #买支付100元

update user set balance=1010 where name='egon'; #中介拿走10元

update user set balance=1090 where name='ysb'; #卖家拿到90元

commit;#出现异常，回滚到初始状态

start transaction;

update user set balance=900 where name='wsb'; #买支付100元

update user set balance=1010 where name='egon'; #中介拿走10元

uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到

rollback; #回滚到原来的状态

commit; #只有提交了，事务操作才完成

问题：有没有遇到脏读问题，怎么解决，用事务(隔离级别处理)，事务是什么？

查看默认事务隔离级别

select @@tx_isolation;

如何关闭和开启MySQL的自动提交？

在MySQL中执行命令时，通常都直接被确定提交了。也就是说用户不用意识此事，所有的命令都会被自动COMMIT。特别是当存储引擎为MyISAM的情况下，本身它是不支持事务处理的，只要执行了命令，所有的命令部会被提交。

这样的默认自动提交的功能就被称为自动提交功能。自动提交功能默认被置为ON的状态。但是，如果存储引擎为InnoDB 时，当执行了START TRANSACTION或BEGIN命令(MySQL事务处理-删除后回滚)后，将不会自动提交了，只有明确执行了COMMIT命令后才会被提交，在这之前可以执行ROLLBACK 命令回滚更新操作。

用户可以将自动提交功能强制置为OFF。这样用户执行SQL语句后将不会被提交了，而执行COMMIT命令才提交，执行ROLLBACK命令回壤。

下面是将自动提交功能置为ON以及置为OFF的具体语法。

将自动提交功能置为ON

SET AUTOCOMMIT=0;

将自动提交功能置为OFF

SET AUTOCOMMIT=1;

我们将演示自动提交功能设置为OFF后，会出现的什么样的变化。置为OFF，我们向表user里插入一条数据后，看看是否能回滚。

将自动提交功能设置为OFF，执行结果如下。

mysql> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

View Code

MySQL的事务处理实现流程是什么？

如果存储引擎为InnoDB 时，不需要

将自动提交功能置为ON

SET AUTOCOMMIT=0;

1、关闭自动提交2、开始事务3、执行SQL语句操作4、要么rollback 要么commit

如果存储引擎为InnoDB 时，当执行了START TRANSACTION或BEGIN命令(MySQL事务处理-删除后回滚)后，将不会自动提交了，只有明确执行了COMMIT命令后才会被提交，在这之前可以执行ROLLBACK 命令回滚更新操作。

5、set autocommit=1;把提交变为初试状态

7.5 使用DQL命令查询数据

mysql语言的分类

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。1. 数据查询语言DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE

子句组成的查询块：

SELECTFROMWHERE

2.数据操纵语言DML

数据操纵语言DML主要有三种形式：1) 插入：INSERT2) 更新：UPDATE3) 删除：DELETE3. 数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、

索引、同义词、聚簇等如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER| | | | |表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback4. 数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制

数据库操纵事务发生的时间及效果，对数据库实行监视等。如：1) GRANT：授权。2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。

回滚---ROLLBACK

回滚命令使数据库状态回到上次最后提交的状态。其格式为：

SQL>ROLLBACK;3) COMMIT [WORK]：提交。

在数据库的插入、删除和修改操作时，只有当事务在提交到数据

库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看

到所做的事情，别人只有在最后提交完成后才可以看到。

提交数据有三种类型：显式提交、隐式提交及自动提交。下面分

别说明这三种类型。

(1) 显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：

SQL>COMMIT；

(2) 隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，

EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，

系统将自动进行提交，这就是自动提交。其格式为：

SQL>SET AUTOCOMMIT ON；

View Code

练习：创建学生表、教师表、课程表、成绩表

1、创建学生表：

create table student(学号 int primary key not null ,姓名 char null,年龄 int not null,性别 enum('男','女'))

2、创建教师表：

create table teacher(教师编号 int primary key not null,教师姓名 char)

3、创建课程表：与教师编号一对多关系

create table class(课程编号 int primary key not null,

课程名 char,教师编号 int,

foreign key(教师编号) references teacher(教师编号) on delete cascade on update cascade);

4、创建成绩表：成绩对应多个学生，成绩对应多个课程

create table score(学号 int,课程编号 int,成绩 int,foreign key(学号) references student(学号) on delete cascade on update cascade,

foreign key(课程编号) references class(课程编号) on delete cascade on update cascade);

插入数据：

插入学生数据：

insert into student(学号,姓名,年龄,性别) values(1001,'张三',10,'男'),(1002,'李四',11,'女')