MySQL数据库——CRUD、约束、多表和事务

事务"/>

MySQL数据库——CRUD、约束、多表和事务

MySQL目录

• MySQL基础
• • 数据库的基本概念
  • MySQL数据库软件
• SQL
• • DDL:操作数据库、表
  • DML：增删改表中数据
  • DQL：查询表中的记录
• MySQL约束
• • DQL:查询语句
  • 约束
  • 数据库的设计—多表关系和范式
  • • . 多表之间的关系
    • . 数据库设计的三大范式
  • 数据库的备份和还原
• MySQL多表&事务
• • 多表查询：
  • * 多表查询练习
  • 事务
  • DCL：
  • * mysql中忘记了root用户的密码？

MySQL基础

1. 数据库的基本概念

2. MySQL数据库软件

   1. 安装
   2. 卸载
   3. 配置

3. SQL

数据库的基本概念

1. 数据库的英文单词： DataBase 简称 ： DB
2. 什么数据库？* 用于存储和管理数据的仓库。* 本质上是一个文件系统，还是以文件的形式存在服务器的电脑上* 所有的关系型数据库都可以使用通用的SQL语句进行管理3. 数据库的特点：1. 持久化存储数据的。其实数据库就是一个文件系统2. 方便存储和管理数据3. 使用了统一的方式操作数据库 -- SQL

MySQL数据库软件

1. 安装* 参见《MySQL基础.pdf》
2. 卸载1. 去mysql的安装目录找到my.ini文件* 复制 datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"2. 卸载MySQL3. 删除C:/ProgramData目录下的MySQL文件夹。3. 配置* MySQL服务启动1. 手动。2. cmd--> services.msc 打开服务的窗口3. 使用管理员打开cmd* net start mysql : 启动mysql的服务* net stop mysql:关闭mysql服务* MySQL登录1. mysql -uroot -p密码2. mysql -hip -uroot -p连接目标的密码3. mysql --host=ip --user=root --password=连接目标的密码* MySQL退出1. exit2. quit* MySQL目录结构1. MySQL安装目录：basedir="D:/develop/MySQL/"* 配置文件 my.ini2. MySQL数据目录：datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"* 几个概念* 数据库：文件夹* 表：文件* 数据：数据

总结：
1)一个数据库服务器包含多个库
2)一个数据库包含多张表
3)一张表包含多条记录

SQL

1.什么是SQL？Structured Query Language：结构化查询语言其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。2.SQL通用语法1. SQL 语句可以单行或多行书写，以分号结尾。2. 可使用空格和缩进来增强语句的可读性。3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。4. 3 种注释* 单行注释: -- 注释内容 或 # 注释内容(mysql 特有) * 多行注释: /* 注释 */3. SQL分类1) DDL(Data Definition Language)数据定义语言用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等2) DML(Data Manipulation Language)数据操作语言用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等3) DQL(Data Query Language)数据查询语言用来查询数据库中表的记录(数据)。关键字：select, where 等4) DCL(Data Control Language)数据控制语言(了解)用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

DDL:操作数据库、表

1. 操作数据库：CRUD

   1. C(Create):创建

      • 创建数据库：

        • create database 数据库名称;

      • 创建数据库，判断不存在，再创建：

        • create database if not exists 数据库名称;

      • 创建数据库，并指定字符集

        • create database 数据库名称 character set 字符集名;

      • 练习： 创建db4数据库，判断是否存在，并制定字符集为gbk

        • create database if not exists db4 character set gbk;

   2. R(Retrieve)：查询

      • 查询所有数据库的名称:
        • show databases;
      • 查询某个数据库的字符集:查询某个数据库的创建语句
        • show create database 数据库名称;

   3. U(Update):修改

      • 修改数据库的字符集
        • alter database 数据库名称 character set 字符集名称;

   4. D(Delete):删除

      • 删除数据库
        • drop database 数据库名称;
      • 判断数据库存在，存在再删除
        • drop database if exists 数据库名称;

   5. 使用数据库

      • 查询当前正在使用的数据库名称
        • select database();
      • 使用数据库
        • use 数据库名称;

2. 操作表

   1. C(Create):创建
      1. 语法：
         create table 表名(
         列名1 数据类型1,
         列名2 数据类型2,
         …
         列名n 数据类型n
         );
         • 注意：最后一列，不需要加逗号（,）
         • 数据库类型：
           1. int：整数类型
              • age int,
           2. double:小数类型
              • score double(5,2)
           3. date:日期，只包含年月日，yyyy-MM-dd
           4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
           5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
              • 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
           6. varchar：字符串
              • name varchar(20):姓名最大20个字符
              • zhangsan 8个字符 张三 2个字符

   
   -- 创建表create table student(id int,name varchar(32),age int ,score double(4,1),birthday date,insert_time timestamp);
   -- 复制表/快速创建一个表结构相同的表 ：create table 表名 like 被复制的表名;	  	
   

   2. R(Retrieve)：查询
      • 查询某个数据库中所有的表名称
        • show tables;
      • 查询表结构
        • desc 表名;
      • 查看创建表的SQL语句
        • show create table 表名;
        • 注意： 存在的目的是为了避免关键字的冲突
   3. U(Update):修改
      1. 修改表名
         alter table 表名 rename to 新的表名;
      2. 修改表的字符集
         alter table 表名 character set 字符集名称;
      3. 添加一列
         alter table 表名 add 列名 数据类型;
      4. 修改列名称 CHANGE 类型 MODIFY
         alter table 表名 change 列名 新列别 新数据类型;
         alter table 表名 modify 列名 新数据类型;
      5. 删除列
         alter table 表名 drop 列名;
   4. D(Delete):删除
      • drop table 表名;
      • drop table if exists 表名 ;

• 客户端图形化工具：SQLYog

DML：增删改表中数据

1. 添加数据：
   • 语法：
     • insert into 表名(列名1,列名2,…列名n) values(值1,值2,…值n);
       INSERT INTO 表名：表示往哪张表中添加数据
       (字段名1, 字段名2, …)：要给哪些字段设置值
       VALUES (值1, 值2, …)：设置具体的值
   • 注意：
     1. 列名和值要一一对应。
     2. 如果表名后，不定义列名，则默认给所有列添加值
        insert into 表名 values(值1,值2,…值n);
     3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
     4. 不指定列或使用null，表示插入空值。

-- 1.插入所有的列，向学生表中
insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');
insert into student (id,name,age,sex) values (2, '孙悟天', 16, '男');-- 2.向表中插入所有字段
-- 插入所有列
insert into student values (3, '孙悟饭', 18, '男', '龟仙人洞中');
-- 如果只插入部分列，必须写列名
insert into student values (3, '孙悟饭', 18, '男');
select * from student;

执行结果：
1.

2.

2. 删除数据：

   • 语法：

     • delete from 表名 [where 条件]

   • 注意：

     1. 如果不加条件，则删除表中所有记录。
     2. 如果要删除所有记录
        1. delete from 表名; – 不推荐使用。有多少条记录就会执行多少次删除操作
        2. TRUNCATE TABLE 表名; – 推荐使用，效率更高 先删除表，然后再创建一张一样的表。

3. 修改数据：

   • 语法：
     • update 表名 set 列名1 = 值1, 列名2 = 值2,… [where 条件];
       UPDATE: 需要更新的表名
       SET: 修改的列值
       WHERE: 符合条件的记录才更新
     • 可以同时更新一个或多个字段、可以在 WHERE 子句中指定任何条件。
   • 注意：
     1. 如果不加任何条件，则会将表中所有记录全部修改。

-- UPDATE 表名 SET 字段名=值; -- 修改所有的行
-- UPDATE 表名 SET 字段名=值 WHERE 字段名=值;-- 不带条件修改数据，将所有的性别改成女
update student set sex = '女';
-- 带条件修改数据，将id号为2的学生性别改成男
update student set sex='男' where id=2;
-- 一次修改多个列，把id为3的学生，年龄改成26岁，address改成北京
update student set age=26, address='北京' where id=3;

4. 蠕虫复制
   将一张已经存在的表中的数据复制到另一张表中。
   1. 将表名2中的所有的列复制到表名1中
      INSERT INTO 表名1 SELECT * FROM 表名2;
   2. 只复制部分列
      INSERT INTO 表名1(列1, 列2) SELECT 列1, 列2 FROM student;

-- 蠕虫复制的具体操作
-- 创建student2表，student2结构和student表结构一样
drop table student2;
create table student2 like student;
-- 将student表中的数据添加到student2表中
insert into student2 select * from student;
-- 如果只想复制student表中name,age字段数据到student2表中，两张表都写出相应的列名
insert into student2 (name,age) select name,age from student;
select * from student2;

执行结果：

实现例子：

-- 创建表student
CREATE TABLE student(id INT,NAME VARCHAR(32),age INT ,score DOUBLE(4,1),birthday DATE,insert_time TIMESTAMP);INSERT INTO student(id,NAME,age) VALUES(1,'candy',18);SELECT * FROM student;INSERT INTO student VALUES(2,'lucy',18,99,NULL,NULL);INSERT INTO student VALUES(3,'john',17,90,"1997-12-12",NULL);DELETE FROM student WHERE id = 1;SELECT * FROM student;DELETE FROM student;TRUNCATE TABLE student;-- 删除表，然后再创建一个一模一样的空表SELECT * FROM student;
-- 修改数据
UPDATE student SET age = 117 WHERE id = 3;
UPDATE student SET age = 18,score =100 WHERE id = 2;

DQL：查询表中的记录

查询不会对数据库中的数据进行修改.只是一种显示数据的方式

• select * from 表名;

  1. 语法：
     select
     字段列表
     from
     表名列表
     where
     条件列表
     group by
     分组字段
     having
     分组之后的条件
     order by
     排序
     limit
     分页限定

  2. 基础查询

     1. 多个字段的查询
        select 字段名1，字段名2… from 表名；
        • 注意：
          • 如果查询所有字段，则可以使用*来替代字段列表。
     2. 去除重复：
        • distinct
     3. 计算列
        • 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
        • ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
          • 表达式1：哪个字段需要判断是否为null
          • 如果该字段为null后的替换值。
     4. 起别名：
        • as：as也可以省略

  3. 条件查询

     1. where子句后跟条件
     2. 运算符

        • 、< 、<= 、>= 、= 、<>

        • BETWEEN…AND
        • IN( 集合)
        • LIKE：模糊查询
          • 占位符：
            • _:单个任意字符
            • %：多个任意字符
        • IS NULL
        • and 或 &&
        • or 或 ||
        • not 或 !

例子实现：

-- 创建表student3
CREATE TABLE student3 (
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
INSERT INTO student3(id,NAME,age,sex,address,math,english) 
VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77)