DDL：操作数据库和表

1.操作数据库（CRUD）

1.1创建(create)：
  • create database 数据库名称;
  • create database if not exists 数据库名称;【创建数据库，如果已经存在就不创建，不存在则创建】
  • create database 数据库名称 character set 字符集名(utf8/gbk)【创建数据库并指定字符集】

  • [ 举例:创建一个数据库db1，判断是否存在并且指定字符集为gbk] create database if not exists db4 character set gbk;

    1.2.查询(retrieve)：

  • show databases; 查看所有数据库的名称

  • show create database 数据库名称; 查询数据库的创建语句及字符集；

    1.3.修改(update)：

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

    1.4.删除(delete)

  • drop database 数据库名称; 删除数据库

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

    1.5.使用数据库

  • use 数据库名称;使用数据库。

  • select database(); ：查询当前使用的数据库

    2.操作表（CRUD）

    2.1.创建：

  • 语法：

    1. create table 表名（
    2.   列名1 数据类型1，
    3.   列名2 数据类型2,
    4.   ....
    5.   列名n 数据类型n
    6.   );

  • 创建表：

    create table student(
  id int,
  name varchar(32),
  age int,
  score double(4,1),
  birthday date,
  insert_time timestamp--时间戳，如果为null，会自动赋值
  );
复制代码

    2.2.查询

  • show tables;：查询某个数据库中所有的表名称

  • desc 表名;：查询某个表的表结构

  • show create table 表名;：查询创建表的SQL语句

    2.3.修改

  • alter table 表名 rename to 新的表名;：修改表名

  • alter table 表名 character set 字符集名称;：修改表的字符集
  • alter table 表名 add 列名 数据类型； 添加一列
  • alter table 表名 modify 列名 新数据类型; ：修改列的 类型。
  • alter table 表名 change 列名 新列名 新数据类型; 修改列名

  • alter table 表名 drop 列名; : 删除列

    2.4.删除

  • drop table 表名 :删除表

  • drop table if exists 表名：删除表，若存在，则删除

    2.5.复制表结构

  • create table 表名 like 被复制的表名; :复制表的结构

    四 DML：增删改表中的数据

    1.添加数据：

    语法：

  • insert into 表名(列名1，列名2....列名n) values(值1，值2,...值n);

    注意：

  • 上面的语句中字段名可以全部写出来，也可以只写一部分，但是列名和值要一一对应 。 没有添加数据的字段会使用null。

  • 如果表名后不定义列名，则默认给所有列添加值，例如insert into 表名 values(值1，值2...值n);
  • 除了数字类型，其他类型需要使用引号(单双都可以)引起来

    插入多条数据一次：insert into 表名 (列名1，列名2...) values(值1，值2...),(值1，值2...),(值1，值2...)mysql的语法 - 图1

2.删除数据：

语法：

  • delete from 表名 [where 条件]

    注意：

  • 如果不加条件，则删除表中所有记录

  • 如果要删除所有记录

delete from 表名 —不推荐使用，有多少条记录就会执行多少次删除操作。TRUNCATE TABLE 表名; —推荐使用，效率更高，先删除表，然后再创建一张一样的表。

3.修改数据：

语法：

  • update 表名 set 列名1=值1，列名2=值2.....[where 条件]；

    注意：

  • 如果不加任何条件，会将表中所有记录全部修改。

    五 DQL：查询表中的记录

    1.基础查询

  1. 查询所有列：select * from 表名;
  2. 查询指定列：select 字段名1，字段名2，字段名3...from 表名;
  3. 起别名：select 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名;
  4. 清除重复值：select distinct 字段名 from 表名;
  5. 查询结果参与运算：select 列名1 +列名2（或固定值） from 表名;

    注意参与运算的必须是数值型

2.条件查询(where)

  1. 基本语法：select 字段名 from 表名 where 条件;
  2. 运算符

mysql的语法 - 图2

  1. 逻辑运算符

mysql的语法 - 图34. in关键字mysql的语法 - 图45. 范围查询mysql的语法 - 图5

  1. like关键字

mysql的语法 - 图6mysql的语法 - 图7

  • 查询姓马的同学

select * from student where name like '马%';

  • 查询第二个字是化的同学

select * from student where name like '_化%';

  • 查询姓名是三个字的同学

select * from student where name like '___';

3.排序查询(order by)

  1. 基本语法：select 字段名 from 表名 [where 条件] order by 排序字段1 排序方式1，排序字段2 排序方式2... ;
  2. 排序方式:asc升序，默认的。desc：降序。
  3. 注意：有多个排序条件时，当前边的条件值一样时才会判断第二个条件。
    • 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序

    select * from student order by age desc,math asc;

4.聚合查询(函数)

  1. 基本语法：select 聚合函数(列名) from 表名;
  2. 常用的聚合函数： | 聚合函数 | 作用 | | —- | —- | | max(列名) | 求这一列最大值 | | min(列名) | 求这一列最小值 | | avg(列名) | 求这一列的平均值 | | count(列名) | 统计这一列有多少条记录 | | sum(列名) | 求这一列的总和 | | 3. 注意聚合函数的查询是纵向查询，它对一列值进行计算，然后返回一个结果值，但它会自动忽略空值null;如果要想把包含null值的行也统计进去可以使用ifnull(列名,0)来将null替换成0值（默认值，也可以不设0，设成别的）。 | | | 4. ifnull函数的形式为ifnull(a,b),如果a不为null，则返回a，若为null，则返回b的值。 | |
  • 查询年龄大于20岁的人的总数(不包括null)

select count(*) from student where age>20;

  • 查询年龄大于20岁的人的总数（包括null）,如果为null，则默认21岁，并统计进去。

select count(*) from student where ifnull(age,21)>20;

5.分组查询（gruop by[having]）

  1. 基本语法：select 字段名 from 表名 group by 分组字段[having 条件]
  2. 分组的含义：在查询前通过分组字段将表中的数据分成不同的组，然后供查询使用。通常和聚合函数一起使用。

  3. 注意：当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的

    • 分别查询出男生和女生的数学平均分

    select sex,avg(math) from student group by sex;

    • 查询男生和女生的数学最高分

    select sex ,max(math)form student group by sex;

    • 查询男生和女生中成绩大于80分的人，并统计各自的人数

    select sex,count(*) from student where score>80 group by sex;比较复杂时，一步步分解：先查成绩大于80分的，然后再按性别分组，最后统计人数。

    • 查询男生和女生中成绩大于80分的人，并统计各自的人数，要求只显示人数大于5的结果。

    select sex,count(*) from student where score>80 group by sex having count(*)>5;注意下面的语句是错误的写法：(group by 后面不能用where，而且where后面不能用聚合函数)select sex,count(*) from student where score>80 group by sex where count(*)>5;

  4. having和where的区别

mysql的语法 - 图8

  • 注意这里的having后面不能使用简单的属性查询，例如在上面例子中改为select sex,count(*) from student group by sex having age>39中的having age >39在逻辑上是错误的，因为having是对group by后的结果进行再处理，而group by已经对数据进行分组，每个分组里都有相应的属性，再having一个普通的属性会造成1对n的查询错误，系统不能找到具体要查哪一个分组里的属性，从而出错。

mysql的语法 - 图9

6.分页查询(limit)

  1. 基本语法：

    select 
 字段列表 [as 别名] 
FROM 
 表名 
~~[WHERE 子句]~~  
~~[GROUP BY 子句]~~
~~[HAVING 子句]~~ 
~~[ORDER BY 子句]~~ 
[LIMIT 子句];
//limit 子句：limit offset，length
//offset(开始的索引，不写的话默认是0)
//length（每页查询的条数）
复制代码

  2. 查询公式：开始的索引=（当前的页数-1）* 每页显示的条数

    • 假设一共有19条数据，每页查询5条

    select * from student limit 0,5;//查询第一页select * from student limit 5,5;//查询第二页,开始索引=(2-1)5`select from student limit 10,5;//查询第三页select * from student limit 15,5;`//查询第四页，如果最后不够5条，则有多少显示多少

7.多表查询（连接查询）

  1. 为什么要用到多表查询：当我们要查询的多个字段不在同一个表中就要将涉及到的多个表连接起来，把它看成一张表，然后再查询。
  2. 注意：多表查询时要有表的连接条件，不然会出现笛卡尔积现象。

select * from table1,table 2;:这种语句就会产生笛卡尔积现象，因为没有指定连接条件，会出现许多没有用的数据。 3. 分类mysql的语法 - 图104. 内连接

  • 隐式内连接

    • 看不到JOIN关键字
    • 语法：

    select 字段名 from 左表，右表 where 条件;

    • 举例：

    查询表a中的name和表b中的scoreselect a.name ,b.score from a,b where a.id=b.id;

  • 显示内连接

    • 使用inner join ….on 语句，可以省略inner
    • 语法：

    select 字段名 from 左表 [inner] join 右表 on 条件;

    • 举例

    查询表a中的name和表b中的scoreselect a.name,b.score from a join b on a.id=b.id;

  1. 外连接

  • 左外连接：

    • 使用left outer join …on ,outer可以省略
    • 语法：

    select 字段名 from 左表 left [outer] join 右表 on 条件;

    • 说明;

    左外连接可以理解为在内连接的基础上保证左表的数据全部显示

    • 举例：

    查询a,b两张表的所有记录select * from a left join b on a.id=b.id;mysql的语法 - 图11

    • 注意：左外连接就是根据on后边的条件用左边的表的记录去匹配右边表的记录，如果匹配的上就显示左表和右表的匹配结果，否则就只显示左表，右表为null。即在内连接的基础上保证左表的记录数据全部显示。

  • 右外连接：

    • 使用right outer join … on,outer可以省略
    • 语法：

    select 字段名 from 左表 right [outer] join 右表 on 条件；

    • 说明：右外连接可以理解为在内连接的基础上保证右表的数据全部显示。
    • 举例：

    查询a,b两张表的所有记录select * from a right join b on a.id=b.id;mysql的语法 - 图12

8.子查询（嵌套查询）

  1. 什么是子查询：子查询就是一个查询中再嵌套一个查询，一个查询的结果作为另一个查询的条件。
  2. 子查询的分类：根据子查询的结果为单行单列，多行单列和多行多列将查询分为三类。

  3. 结果为单行单列的查询：

    • 语法：

    select 查询字段 from 表 where 字段=(子查询);

    • 举例：

    假设两张表，person表存员工的id,name,date（入职日期）信息，salary表存员工的id，money（工资），bonus(奖金)等； 现在来查询薪资最高的员工的个人信息select * from person where id=( select id from salary where salary=( select max(money) from salary) );

  4. 结果为多行单列的查询：

    • 语法：

    select 查询字段 from 表 where 字段 in(子查询);

    • 举例：

    查询薪资大于5000的人的个人信息select * from person where id in (select id from salary where money>5000);

  5. 结果为多行多列的查询：

    • 语法：

    select 查询字段 from (子查询) 表别名 where 条件;

    • 举例：

    查询员工zhangsand的工资和奖金及个人信息select * from person,(select money ,bonus from salary) table1 where name='zhangsan' and table1.id=person.id ;或者使用多表连接查询：select person.id,person.name,person.date ,salary.money,salary.bonus from salary,person where person.id=salary.id and person.name='zhangsan';

9.小结

  • 查询的执行顺序
    select  5
   ..   
  from   1
   ..
  where   2
   ..
  group by  3
   ..
  having  4
   ..
  order by  6
   ..
复制代码

    六 DCL：管理用户，授权

    我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面

可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管 理和维护数据库

1.管理用户

  • 首先了解几个 ‘关键字’

mysql的语法 - 图13

  • mysql中管理登录用户及密码的表在mysql库中的user表，使用命令：USE mysql; `

SELECT * FROM USER;`来查看
mysql的语法 - 图14

1.1 添加用户

  • 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

    1.2 删除用户

  • 语法：DROP USER '用户名'@'主机名';

    1.3 修改用户密码

  • UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';

  • SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

    1.4修改root用户密码

  • mysqladmin -uroot -p password 新密码

  • 这个语句需要在未登录mysql的情况下操作，新密码不用加引号。

mysql的语法 - 图15

1.5 mysql忘记root用户密码
  1. cmd —>net stop mysql ,停止mysql服务（管理员权限）
  2. 使用无验证方式启动mysql服务：mysqld --skip-grant-tables
  3. 打开新的cmd窗口，直接输入命令mysql，敲回车，就可以登录成功
  4. 修改新的密码：use mysql; 和 update user set password = password('你的新密码') where user = 'root';
  5. 关闭两个窗口
  6. 打开任务管理器，手动结束mysqld.exe的进程
  7. 重新启动mysql，用新密码登录

2.权限管理

2.1 查询权限

  • SHOW GRANTS FOR '用户名'@'主机名';

    2.2 授予权限

  • grant 权限1,权限2.... on 数据库名.表名 to '用户名'@'主机名';

  • mysql的语法 - 图16

    2.3 撤销权限

  • revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名';

  • mysql的语法 - 图17

    七 约束

    1.概念

  • 对表中的数据进行限定，保证数据的正确性，有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表时添加比较合适。

  • 约束的种类：
    mysql的语法 - 图18

    2.非空约束—not null

  • 什么是非空约束：某一列不能为null。

  • 创建表时添加约束

    CERATE TABLE stu(
  id INT,
  NAME VARCHAR(20) NOT NULL--name为非空
  );
复制代码

  • 创建表完成后，添加非空约束

    ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
复制代码

  • 删除name的非空约束

    ALTER TABLE stu MODIFY NAME VARCHAR(20);
复制代码

  • 默认值

    字段名 字段类型 default 默认值
复制代码

    3.唯一约束—unique

  • 什么是唯一约束：表中某一列不能出现重复的值。

  • 注意：唯一约束可以有null值，但是只能有一条记录为null

  • 在创建表时，添加唯一约束

    CREATE TABLE stu(
  id INT,
  phone_number VARCHAR(20) UNIQUE--手机号唯一
  );
复制代码

  • 删除唯一约束

    ALTER TABLE stu DROP INDEX phone_number;
复制代码

  • 在表创建完后，添加唯一约束

    ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
复制代码

    4.主键约束—primary key

  • 注意：主键就是表中记录的唯一标识，一张表只有一个字段为主键，主键非空且唯一。

  • 在创建表时，添加主键约束primary key

    create table stu(
  id int primary key,--给id 添加主键约束
  name varchar(20)
  );
复制代码

  • 删除主键

    alter table stu drop primary ket;
复制代码

  • 创建完表后添加主键

    alter table stu modify id int primary key;
复制代码

  • 设置主键自动增长

    1. 概念：如果某一列是数值类型的，使用anto_increment 可以完成主键值的自动增长
    2. 在创建表时，添加主键约束，并且自动完成主键自增长。
create table stu(
              id int primary key auto_increment,--给id添加主键约束,并自动增长
              name varchar(20)
          );
复制代码
  1. 删除自动增长
ALTER TABLE stu MODIFY id INT;
复制代码
  1. 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
复制代码

  • delete和TRUNCATE对自增长的影响：
    delete:删除所有记录之后，自增长没有影响。
    TRUNCATE：删除之后，自增长又重新开始。

    5.外键约束—foreign key

  • 让表和表产生关系，从而保证数据的正确性

  • 创建表时，添加外键

    create table 表名(
  ...
  外键列
  constraint 外键名称 foreign key (外键名称) references 主表名称(主表列名称)
  );
复制代码

  • 删除外键

    alter table 表名 drop foreign key 外键名称;
复制代码

  • 创建表之后，添加外键

    alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);
复制代码

  • 级联操作

    什么是级联操作:在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作。
复制代码

    mysql的语法 - 图19

    八 TCL：事务控制

    1.什么是事务

  • 在实际开发中，我们进行的一些业务操作，例如转账（一个用户扣钱，另一个用户加钱）往往需要多次访问数据库，通过执行多条SQL语句才能完成，但是如果这些SQL语句在执行时由于系统出现故障原因造成其中一条SQL语句执行失败，那么虽然其他SQL执行成功了，但对于这个转账操作来说，这个操作是失败的。

  • 因此我们引入了“事务”这一概念，主要概念是将一个操作中要执行的所有SQL语句当成一个整体，它们必须全部执行成功，如果有一条SQL执行失败，那么所有的SQL都要重新执行（rollback），来确保不正确的提交的发生，只有所有SQL都执行成功，才会提交进数据库(commit)

    2.为什么要使用事务

  • 在mysql实际开发中使用事务来管理执行的SQL语句更加符合逻辑，避免不正确操作造成损失。

    3.如何使用事务

  • 在mysql中有两种方式来进行事务操作。一是手动提交事务，一是自动提交事务。

    • 手动提交事务：
    1. 要使用的SQL语句

    start transaction;：开启事务commit：提交事务rollback;：回滚事务 2. 使用流程：mysql的语法 - 图203. 案例演示

    • 创建数据表并插入数据来演示转账操作
create table account(-- 创建数据表
   id int primary key auto_increment,
    name varchar(10),
    balance double
);
--添加数据
insert into account (name,balance) values('zhangsan',1000),('lisi',1000);
复制代码
  • 转账演示

mysql的语法 - 图21这里说明一下，在start transaction;后，执行的SQL语句如果没有问题就提交（commit），然后数据就会保存到数据库中，如果出现了问题就回滚（rollback），数据表中的数据会恢复到start transaction;时状态。同时当没有commit时我们查询数据表发现数据表中数据已经改变，这只是临时日志文件中的数据，只能代表这个时候的状态，数据库中并没有变化。通过其他端登录mysql查询这个表发现数据并没有变就能说明。

  • 自动提交事务：
  1. 在mysql中默认DML语句都是一个单独的事务，，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，MySQL 默认开始自动提交事务。这在之前DML的学习中就可以看出。
  2. 通过select @@autocommit;命令来查看mysql是否开启自动提交事务。1表示开启，0表示关闭。

mysql的语法 - 图223. 通过set @@autocommit =0来取消自动提交事务，之后我们在执行DML语句时要自动commit才能生效，否则不会保存进数据库。

4.事务的原理

  • 事务开启后，所有的操作都会临时保存到事务日志中，事务日志只有在得到commit命令才会将将操作同步到数据库中，其他情况都会清空事务日志（例如rollback或者与数据库断开连接）

  • 事务的执行步骤：

    • 客户端连接数据库服务器，在连接成功后会自动创建一个此用户的临时日志文件
    • 开启事务后，所有的操作会先写入临时日志文件中
    • 所有的查询从表中查询，但会经过日志文件的加工处理，因此未提交前的一些操作也可以通过日志文件读取到。

    事务提交后会将日志文件中的数据写入数据库中，然后清空日志文件。而回滚或者断开连接则会直接清空日志文件。

  • 原理图

mysql的语法 - 图23

5.回滚点（对事务的优化）

  • 在上面的事务操作中，如果SQL执行失败，我们要重新再执行一遍，如果SQL语句较少的话还行，但SQL语句很多的话仅仅因为一行出错就重新全部执行效率比较低，所以我们引入了“回滚点”。
  • 回滚点就是在执行一个成功的操作后，为了避免后续的操作错误导致前边的操作也要执行而设立的一个标记点。它设立在一个成功的操作后，当后续的操作失败后可以返回这个点重新执行。
  • 回滚点的操作SQL语句：

mysql的语法 - 图24

  • 注意设置回滚点仅仅是回到回滚点，不能回到事务开启的时候。

    6.事务的四大特征（ACID）

  • 原子性（Atomicity）：每个事务都是一个整体，不可再分割，事务中的所有SQL语句要么都执行成功，要么都失败。

  • 一致性（Consistency）：事务操作前后，数据总量不变
  • 隔离性（Isolation）：多个事务之间相互独立，操作时不会相互影响。

  • 持久性(Durability)：事务提交后，数据库中会持久化的保存数据。

    7.事务的隔离级别

  • 事务在操作时的理想状态是所有的事务之间都保持隔离，可以相互独立的运行。但如果多个事务同时对同一数据进行操作，则会引发一些问题，我们可以通过设置不同的隔离级别解决这些问题。

  • 首先了解一下存在的问题：

    1. 脏读：一个事务读取到了另一个事务没有提交的数据
    2. 不可重复读（虚读）：在同一个事务中，两次读取的数据不一样。
    3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

  • mysql的四种隔离级别

mysql的语法 - 图25

  • 查看和设置mysql的事务隔离级别
    • select @@tx_isolation;：查看全局事务隔离级别

    mysql的语法 - 图26

    • set global transaction isolation level 级别字符串：设置隔离级别。
    • 注意：设置事务隔离级别，需要退出mysql再重新登录才能看到隔离级别的变化。
    • 级别字符串：read uncommitted ,read committed ,repeatable read ,serializable

    mysql的语法 - 图27重新登录mysql才能看到变化

九 mysql的还原与备份

9.1 命令行方式

  • mysql备份

    • 在dos下(win+cmd)执行命令mysqldump -u用户名 -p密码 要备份的数据库 > sql文件路径;
    • 注意：如果不指定sql路径，会在当前的目录下创建一个sql文件并将备份的sql写入。
    • 举例：

    mysql的语法 - 图28这个命令在当前的C:\Users\coderchen目录下存储mysql.sql文件mysql的语法 - 图29这个命令会在d盘根目录存储mysql.sql文件

  • mysql还原

    1. 登录mysql mysql -uroot -proot
    2. 创建一个数据库 create database 数据库名;
    3. 使用这个数据库：use 数据库名;
    4. 使用source命令还原数据:source + 备份的sql文件 可以直接拖进来
    5. 如果显示表被锁住，打不开：UNLOCK TABLES;
    6. 成功导入，查看结果

    mysql的语法 - 图30