MySql小白常用语法

mysql操作手册大全

DDL操作数据库,表

一. 操作数据库:CRUD

1.c(create):创建

创建新数据库:
    create database 数据库名
创建数据库,判断不存在,再创建
    create database if not exists 数据库名;
创建数据库,并指定字符集
    create databse if not exists 数据库名 character set 字符集名;

2.r(retrieve):查询

查询所有的数据库
    show databses;
查询某个数据库的字符集,查询某个数据库的创建语句
    show create database 数据库名

3.u(update):修改

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

4.d(delete):删除

删除数据库
    drop databse 数据库名
判断数据库是否存在再删除
     drop databse if  exists 数据库名;

5.使用数据库

use 数据库名

二. 操作表:CRUD

1.c(create):创建

* 语法:
    create table 表名(
        列名称1 数据类型1,
        列名称2 数据类型2,
        列名称2 数据类型2,
        ... ...
        列名称n 数据类型n
    );
* 数据类型:
    int 整数类型
        age int,
    double 小数类型
        score deuble(5,2)//(小数的尾数,小数点后面保留的尾数)
    data 日期类型(只包含年月日) yyyy-mm-dd
    datatime 日期类型(包含年月日时分秒) yyyy-mm-dd HH:mm:ss
    timestamp 时间戳类型 (包含年月日时分秒) yyyy-mm-dd HH:mm:ss
        如果不给这个字段赋值,或者赋值为null,则默认的使用当前的系统时间自动赋值
    varchar 字符创类型
        name varchar(20) (字符串的最大长度)
创建学生表:
    create table student(
        id int,
        name varchar(32),
        age int,
        score double(4,1),
        birthday data,
        insert_time timestamp
    );
复制表
    create table student2 like student;

2.r(retrieve):查询

查询某个数据库中的所有表名称
    show tables;
查询表结构
    desc 表名;

3.u(update):修改

修改表名
    alter table 表名 rename to 新的表名
修改表的字符集
    alter 表明 character set 字符集名称
添加一列
    alter table 表名 add 列名 数据类型
修改列名 类型
    alter table 表名 change 旧列名 新列名 新数据类型;
    alter table 表名 modify 列名  新数据类型
删除列
    alter table 表名 drop 列名

4.d(delete):删除

drop table  if exists 表名称;

DML 增删改表中的数据

1.添加数据

* 语法:
    insert into 表名(列名1,列名2,列名3,...) values(值1,值2,值3...);
* 注意:
    a. 列名和值要一一对应
    b. 如果表名后不写列名,则要为所有列添加数据
    c. 除了数字类型要用引号引起来(单引号,双引号都可以)

2.删除数据

* 语法:
    delete from 表名 [where 条件];
* 注意:
    a. 如果不加条件,则删除所有的记录
    b. 如果要删除所有的记录
        delete from 表名; ---不推荐,有多少条记录这个语句就需要执行多少次,效率低下
        truncate table 表名;---推荐使用,先删除表,然后再创建一张一样的表,效率高

3.修改数据

* 语法:
    update 表名 set 列名1=值1, 列名2=值2, ... ;
* 注意
    a. 若不加条件则会修改所有的数据

DQL 查询表中的记录

1. 语法

select
    字段列表
from
    表名
where
    条件
group by
    分组字段
having
    分组之后的条件
order by
    排序
limit
    分页

2. 基础查询

1.查询多个字段
    select 字段1,字段2,... from 表名l;
    select * from 表名;(查询所有的字段)
2.去除重复
    distinct
3.计算列
    * 一般可以使用四则运算计算列的值
    * ifnull(表达式1,表达式2): null 参与运算的时候会使其结果为null
           表达式1:哪个字段需要判断是否为null
           表达式2:若该字段为null后的替换值
4.起别名:
    * as : as也可以省略
5.案列
     创建表
        create table student2(
            id int,
            name varchar(20),
            age int,
            sex varchar(5),
            address varchar(100),
            math int,
            english int
        );

    插入实验数据
        insert into student2 (id, name, age, sex, address, math, english)
        values (1,'马云',55,'男','杭州',66,78),
        (2,'马化腾',45,'男','深圳',98,87),
        (3,'马景甜',45,'男','香港',56,77),
        (4,'柳岩',20,'女','湖南',79,65),
        (5,'柳青',20,'男','杭州',66,null),
        (6,'德玛西亚',18,'男','南京',56,55);


    distinct  去除重复的数据
        select  distinct address from student2;

    计算math 和 english 的总和
    * 若果有null参与的运算,计算结果都是null
    * ifnull(english,0) 英语成绩为null则替换为0
    * as 给列起别名 ,或者直接空格
        select name,math,english, ifnull(math,0)+ ifnull(english,0) as sumScore from student2;

        select name,math,english, ifnull(math,0)+ ifnull(english,0)  sumScore2 from student2;

3. 条件查询

1.where 子句后跟条件
2.运算符
    =	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
    <>, != 	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
    >	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
    <	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
    >=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
    <=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。
    like 模糊查询
        占位符:
            _:单个字符
            %:任意多个字符
        案例:
            #查询班级里姓 马的
            select * from student2 where name like '马%';

            #查询班级里姓名中第二个字是 化 的人
            select * from student2 where name like '_化%';

            #查询班级里姓名是三个字的人
            select * from student2 where name like '___';

            # 查询班级里姓名包含 德 的人
            select * from student2 where name like '%德%';

4.排序查询

语法
    order by 子句
方式升序
    ASC : 排序 (默认的)
    DESC : 降序
注意:
    如果有多个排序条件,只有当前面的条件一样时候,才会考虑后面的条件

5.聚合函数

将一列数据作为一个整体,进行纵向计算
1.count 计算个数
2.max 计算最大
3.min 计算最小
4.sum 求和
5.avg 求平均
* 注意 聚合函数的计算会排除 null 值
    解决
        1.使用非空的列,例如使用 主键
        2.使用ifnull()

6.分组查询

语法
    group by 分组字段
注意
    a. 分组查询后的字段只能是 : 分组字段 或者 聚合函数 ,其他字段没意义
    b. where 和 having 的区别?
      1. where 在分组之前进行限定,如果不满足条件则不参与分组,having 在分组之后进行限定,如果不满足条件,则不会被查询出来
      2. where 后面不可以跟聚合函数,having 后可以接聚合函数.

7.分页查询

语法
    limit 开始的索引 ,每页查询的条数
公式
    开始的索引 = (当前的页码-1)*每页显示的的条数
注意
    limit是mysql的一个方言,不同的数据库可能不同
    使用一个参数，该值指定从结果集的开头返回的行数

例题：编写一个 SQL 查询，获取 Employee 表中第 2高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

Limit有两个参数，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。初始行的偏移量是0（不是1）

SELECT
    (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC
      LIMIT 1 OFFSET 1) AS SecondHighestSalary;

约束

1.概念

    对表中数据进行限定,保证数据的正确性,有效性和完整性

2.分类:

    * 主键约束: primary key
        注意:
            1.含义:非空且唯一
            2.一张表只有一个字段为主键
            3.主键就是表中记录的唯一标识
        删除主键:
            alter table 表名 drop primary key;
        创建完表后添加主键
            alter table 表名 modify 字段 数据类型 primary key;
        主键自动增长
            _increment,修饰数值,可以使其自动增长,一般和主键一起使用
    * 非空约束: not null
         1. 创建表时添加约束
            create table stu(id int,name varchar(20) not null);
         2.创建表后添加
            alter table stu modify name varchar(20) not null;
         3.删除约束
            alter table stu modify name varchar(20);
    * 唯一约束: unique
        1. 创建表时添加约束
           create table stu(id int,phone_number varchar(20) unique);
        2.创建表后添加
           alter table stu modify phone_number varchar(20) unique;
        3.删除约束
           alter table drop index phone_number;
        4.注意
            mysql中唯一约束限定的值可以有多个null
    * 外键约束: foreign key
        作用:让表和表之间产生关系,从而保证数据的正确性
        1.在创建表时添加外键
            create table 表名(
                ... ...
                外键列
                constraint 外键名称 foreign 外键列名称 references 主表的名称(主表列名称)
            );
        案列:
            员工表和部门表通过外键约束
            create table departement(
                id int primary key _increment,
                dep_name varchar(20) not null ,
                dep_localtion varchar(30)
            );


            create table employee(
                id int primary key _increment,
                name varchar(20) not null ,
                age int,
                dep_id int, -- 外键对应主表的主键
                constraint emp_dep_fk foreign key (dep_id) references departement(id)
            );
        2.删除外键
            alter table 表名 drop foreign key 外键名称;
        3.添加外键
            alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表的名称(主表列名称);

        4.级联操作(慎用)
            1.添加级联更新,级联删除
                alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表的名称(主表列名称)
                on update cascad on delete cascade;

数据库的设计

1.多表之间的关系

分类

一对一
    例如:人和身份证
    分析: 一个人有一个身份证
一对多
    例如:部门和员工
    分析: 一个部门有多个员工,一个员工对应一个部门
多对多
    例如:学生和课程
    分析: 一个学生可以选多门课程,一门课程可以被多名学生选修

关系的实现

1.一对一
    在任意一方建立外键来指向另一方的主键,并且让唯一约束修饰外键
2.一对多的实现
    在多的一方建立外键,指向一的一方,例如上文的外键约束案列
3.多对多的关系
    需要借助中间表,中间表至少需要两个字段,这两个字段作为第三张表的外键,分别直指向两张表的主键

• 多对多案列
  学生表(student)

s_id	name	age
1	赵敏	17
2	周芷若	18
3	黄蓉	23

课程表(class)

c_id	name
1	语文
2	英语

中间表(student_class)

s_id	c_id
1	1
1	2
2	1
2	2

2.数据库范式

概念

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，
各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）
和第五范式（5NF，又称完美范式）。

分类

案列表

学号	姓名	系名	系主任	课程名称	分数
10010	张无忌	体育系	张三丰	太极拳	96
10011	岳云鹏	曲乐系	郭德纲	相声	90

几个概念:

1.函数依赖: A-->B 如果通过A属性(属性组)的值,可以唯一确定B属性的值,简称B依赖于A
    例如: 学号--> 姓名
         (学号,课程名称) --> 分数

2.完全函数依赖: A-->B 如果A是一个属性组,若要确定B属性值需要依赖于A属性组中所有的属性值
    例如: (学号,课程名称) --> 分数
3.部分函数依赖: A-->B如果A是一个属性组,若要确定B属性值需要依赖于A属性组中部分的属性值
    例如: (学号,课程名称) --> 姓名
4.传递函数依赖: A-->B,B-->C ,如果通过A属性(属性组)的值,可以唯一确定B属性的值,再通过B属性(属性组)的值,可以唯一确定C属性的值
    例如: 学号-->系名  系名-->系主任
5.码: 如果在一张表中,一个属性或者属性组,被其他所有属性完全依赖,则称该属性(属性组)是码
    例如:该表中码是 (学号,课程名)
6.主属性: 码属性组中的所有属性
7.非主属性: 除了码以外的属性

1.第一范式（1NF）：是指在关系模型中，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项。

存在的问题:
    1.存在非常多的数据冗余(重复)
    2.数据添加存在问题
    3.数据删除存在问题

2.第二范式（2NF）:在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
3.第三范式（3NF) :在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

数据库的备份

1.命令行
    备份: mysqldump -u username -p password 数据库名 > 保存文件的路径
    还原: 1.登入数据库 2.创建数据库 3.使用数据库 4.执行文件 : source 保存文件的路径

多表查询

内连接多表查询

1.隐式内连接

使用where 条件消除无用数据

2.显示内连接

语法: select 字段列表 from 表名1 [inner] jion 表2 on 条件

外内连接多表查询

左外连接

语法: select 字段列表 from 表名1 left [outer] jion 表2 on 条件
查询的是左表(表名1)的所有信息及其交集部分

右外连接

语法: select 字段列表 from 表名1 right [outer] jion 表2 on 条件
    查询的是右表(表2)的所有信息及其交集部分

子查询

• 概念 :查询中嵌套查询,称嵌套的查询为子查询

• 子查询结果的不同情况

    1.结果是单行单列的
        子查询可以作为条件,使用运算符去判断
        select * from student2 s1 where s1.math < (select avg(math) from student2);
    2.结果是多行单列的
        子查询可以作为条件,使用in来判断
        案列 
            查询财务部和开发部的所有员工的信息
            select * from employee where dep_id in (select id from departement where dep_name='开发部' or dep_name='财务部');
    3.结果是多行多列的
        子查询可以作为一张虚拟表.
        案列
            查询员工年龄大于20的员工信息和部门信息
            select * from   departement  t1, (select * from employee where employee.age > 20) t2 where t1.id = t2.dep_id;
  

事务

事务的概念

如果一个包含多个步骤的业务操作,被事物管理,那么这些操作要么同时成功要么同时失败 
操作:
    1.开启事物 start transaction;
    2.回滚 rollback
    3.提交 commit
事务的使用:
    start transaction;
    执行sql语句
    commit;
    出错了
    rollback;
注意:
    mysql数据库中事物默认提交,一条DML语句就提交一次
    select @@commit 查看默认提交的方式: 1表示自动提交,0表示手动提交
    手动提交需要先开启事务,再提交事务

事物的四大特征

1.原子性:是不可分割给的最小单位,要么同时成功,要么同时失败
2.持久性:当事务提交或者回滚后,数据会持久化的保存数据
3.隔离性:多和事务之间相互独立
4.一致性:事务操作前,后数据总量不变

事物的隔离级别

概念:
    多个事务之间是隔离的,相互独立的.但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题.
存在的问题
    1.脏读: 一个事务读取到另一个事务没有提交的数据
    2.不可重复读(虚读):在同一个事务中,两次读取到的数据不一样
    3.幻读:一个事务操作(DML)数据表中的所有记录,另一个事务添加了一条数据,则第一个数据查询不到自己的修改.
隔离级别:
    1.read uncommitted: 读未提交
        产生的问题:脏读,不可重复读,幻读
    2.read committed: 读已提交(Oracle)
        产生的问题:不可重复读,幻读
    3.repeatable read: 可重复读 (Mysql默认)
        产生的问题: 幻读
    4.serializable: 串行化
        可以解决所有问题
注意:
    以上隔离级别从小到大,安全性越来越高,但是效率越来越低
查询当前隔离级别:
    select @@tx_isolation;
设置隔离级别:
    set global transaction isolation level 级别字符串;

DCL

管理用户

1.查询用户

use mysql;
select * from user;

2.添加用户

create user '用户名'@'主机名' identified by '密码';

3.删除用户

drop user '用户名'@'主机名';

4. 修改用户的密码

set password for '用户名'@'主机名' = password('新密码');
update user set password = password('新密码') where user = '用户名';

5.忘记root密码

停止mysql服务:
    cmd--> net stop mysql ( 管理员身份)
使用无验证方式启动mysql
    mysql --skip-grant-tables
打开新命令窗口输入命令进入mysql
    mysql
使用mysql数据库
    use mysql;
修改root密码    
    update user set password = password('新密码') where user = 'root';      
手动结束mysql服务进程
再次启动mysql服务

权限管理

1.查询权限

show grants for '用户名'@'主机名';

2.授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
grant all on *.*  to '用户名'@'主机名';(授予所有表所有权限)

3.撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
 grant all on *.* from '用户名'@'主机名';(撤销所有表所有权限)