Mysql

Mysql笔记

1.数据库的基本概念

1.数据库的英文单词： database 简称:DB
2. 什么数据库？
    * 用于存储和管理数据的仓库。
    * 按照一定的形式来组织存放数据，目的是为了更快的操作数据——增删改查
3. 数据库的特点：
	1. 持久化存储数据的。其实数据库就是一个文件系统
	2. 方便存储和管理数据
	3. 使用了统一的方式操作数据库 --- SQL
4.mysql数据库
    Oracal：mysql
    马丁：MariaDB
    Xampp 服务器套装，包含多个服务器软件，例如mysql，Apache…
(1)部署结构
  服务器端：负责存储维护数据 —— 银行的数据库服务器
     启动文件  C:/xampp/mysql/bin/mysqld.exe  占用3306端口
  客户端：负责连接服务器端，对数据进行操作 —— ATM机
     客户端工具  C:/xampp/mysql/bin/mysql.exe
(2)使用客户端连接服务器端
   mysql.exe  -h127.0.0.1  -P3306  -uroot  -p
  -h   host    IP地址/域名   127.0.0.1/localhost
  -P   port    端口
  -u   user    用户名      root 管理员账户
  -p   password   密码     xampp下root的密码是空
  mysql  -uroot    简写形式
  连接的命令结尾不能加分号

2.常用的sql命令

1.操作数据库
(1).删除
   *删除数据库
       * drop database 数据库名称;
(2).创建
   * 设置客户端连接服务器的编码为utf8
       * set names utf8;
   * 创建数据库
       * create database 数据库名称;
   * 丢弃数据库，如果存在
	   * drop database if exists 数据库名称;
   * 创建新的数据库，并设置存储的编码为utf8
	   *create database 数据库名称 charset=utf8;
(3).操作表
   * 创建表
	语法：
	  create table 表名(
		  列名1 数据类型1,
		  列名2 数据类型2,
		  ....
		  列名n 数据类型n
		);
   * 注意：最后一列，不需要加逗号（,）
   * 复制表：
	 * create table 表名 like 被复制的表名;	
(4).查询
   * 查询所有数据库的名称:
       * show databases;
   * 进入创建的数据库
       * use 数据库名称;
(5)常用管理命令(连接成功后)
   quit;   退出服务器的连接
   show  databases;   显示当前所有的数据库
   use  数据库名称;   进入到指定的数据库
   show  tables;  显示当前数据库中所有的数据表
   desc  数据表名称;  描述数据表中都有哪些列
  所有的管理命令都以英文的分号结尾

3.增删改查

      1.添加数据

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

       注意: ① 列名和值要一一对应。②如果表名后，不定义列名，则默认给所有列添加值   ③除了数字类型，其他类型需要使用引号(单双都可以)引起来

      2.删除数据(删除多条数据)

delete from 表名 [where 条件];
delete from 表名 where id in (a,b,c);

         注意:  如果不加条件，则删除表中所有记录。  * delete from 表名; -- 不推荐使用。删除全部数据

      3.修改数据

update 表名称 set 列名称 = 新值 where 列名称 = 某值;

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

  4.查询数据

select * from 表名;
select 列名称 from 表名称

4.Sql常用数据类型

    (1)数值型 —— 可以不加引号

       tinyint   微整型，占1个字节，范围-128~127

       smallint   小整型，占2个字节，范围-32768~32767

       int   整型，占4个字节，范围-2147483648~2147483647

       bigint   大整型，占8个字

       decimal(M,D)   定点小数，小数点位置不会发生变化，M代表总的有效位数，D代表小数点后的有效位数

       boolean   布尔型，只有两个值，分别是true和false，代表真和假，用于存储只有两个值的数据，例如是否在线、性别、是否在售…  在使用的过程中会自动转为tinyint，true转为1，false转为0，也可以直接使用1和0      true和false是关键字，使用的时候不能加引号

       float   单精度浮点型，占4个字节，范围比int大的多，存储的数字越大精度越低，以牺牲小数点后的若干位为代价。最大3.4e+38

       double   双精度浮点型，占8个字节，范围比bigint大的多

    (2)日期时间型 —— 必须加引号

       date    日期型    2020-12-25

       time    时间型    15:20:30

       datetime  日期时间型    2020-12-25 15:20:30

    (3)字符串型 —— 必须加引号

       varchar(M)   变长字符串，几乎不会产生空间浪费，数据操作速度相对慢，M最大值是65535，用于存储变化长度的数据，例如用户名、邮箱、标题、文章详情…

       char(M)   定长字符串，可能会产生空间浪费，数据操作速度相对快，M的最大值是255，用于存储固定长度的数据，例如手机号码、身份证号码

       text(M)   大型变长字符串，M最大值是2G

5.列约束

       Mysql可以对要插入的数据进行特定的验证，只有符合格式才允许插入，例如编号不允许重复，一个人的性别只能是男或者女，一个人成绩范围0~100之间         

create table t1( 
    lid  int  列约束
);

    (1)主键约束 —— primary key

          声明了主键约束的列上不允许插入重复的值，一个表中只有能有一个主键约束，通常是在编号列，会加快数据的查找速度

          null  表示空，在插入数据的时候，表示暂时无法确定的值，例如无法确定一个人的有效，无法确定一个商品的价格…

          null是关键字，不能加引号  主键约束的列上不允许插入null

    (2)非空约束 —— not  null

        声明了非空约束的列上不允许插入null

    (3)唯一约束 —— unique

         声明了唯一约束的列上不允许插入重复的值，允许插入null，甚至多个null

         两个无法确定的值(null)是不一定相同的，所以允许多个

    (4)默认值约束 —— default

         可以使用关键字default来设置默认值     insert  into  表名  values(1 ,’张三’, default ….);

    (5)检查约束 —— check

         也称为自定义约束，用户自己添加的约束

create  table  student(

    score  tinyint  check(score>=0  and  score<=100)

);

          mysql不支持检查约束，会极大的影响数据的插入速度。将来可以通过js完成

    (5)外键约束

          声明了外键约束的列，取值会另一个表的主键列去找，允许插入null；外键列和对应的主键列两者的列类型要保持一致。

          foreign key(外键列) references 另一个表(主键列)

          外键约束是为了让两个表之间建立关联

     * 自增列

           auto_increment  自动增长，如果设置了自增列，在插入数据的时候，只需要设置为null，就会获取当前的最大值然后加1插入

           注意事项：

           自增列只能添加在整数形式的主键列上

           自增列允许手动赋值

6.简单查询

   数据库名tedu;    表1：部门表dept;   表2：员工表emp;

(1)查询特定的列

   示例 :查询出所有员工的姓名、生日、工资

select  ename,birthday,salary  from emp;

(2)查询所有的列(两种方式)

select  eid,ename,sex,birthday,salary,deptId  from emp;
select * from emp;

(3)给列起别名

   示例：查询出所有员工的编号和姓名，使用汉字别名

   as关键字可以省略，保留即可。 给列起别名的目的是为了简化列名称。

select  eid as 编号,ename as 姓名  from emp;

(4)显示不同的记录

   示例：查询出都有哪些性别的员工

select  distinct sex  from  emp;
distinct 显示不同的记录

(5)查询时执行计算

   示例：

        ①计算 1+3+5+8*7.4+5*4.39 

        ②练习：查询出所有员工的姓名及其年薪 

        ③假设每个员工的工资增长1000，年终奖20000，查询出所有员工的姓名及其年薪，使用汉字别名

select  1+3+5+8*7.4+5*4.39;
select  ename,salary*12  from emp;
select  ename 姓名,(salary+1000)*12+20000 年薪 from emp;

(6)查询结果排序(升序、降序)

   示例：查询出所有的部门，结果按照部门编号升序排列(asc全称：ascendant 升序的。 可省略)

select * from dept order by did asc;

   示例：查询出所有的部门，结果按照部门编号降序排列(desc全称：descendant  降序。不可省略)

select * from dept order by did desc;

(7)条件查询

and (&&)  并且，两个条件都满足
or (||)  或者，两个条件满足其一
between  and   在两者之间
not  between  and  不在两者之间
is  null   值为null
is  not  null   值不为null
in(  )   满足其中一个
not  in(  )    都不满足

   示例(1)：

         ①查询出编号为5的员工所有列 

         ②查询出姓名为king的员工所有列

select * from emp where eid=5;
select * from emp where ename='king';

   示例(2)：

        ①查询出不在20号部门下的员工有哪些       

         ②查询出没有明确部门的员工有哪些     

         ③查询出有明确部门的员工有哪些

select * from emp where deptId!=20;
select * from emp where deptId is null;
select * from emp where deptId is not null;

   示例(3)：查询出工资在7000以上的男员工有哪些  (两种写法：and 或 &&)

select * from emp where salary>7000 and sex=1;
select * from emp where salary>7000 && sex=1;

   示例(4)：查询出工资在6000~9000直接的员工有哪些

select * from emp where salary>=6000 && salary<=9000;
select * from emp where salary between 6000 and 9000;

   示例(5)：查询出工资在6000以下或者9000以上的员工有哪些

select * from emp where salary<6000  or  salary>9000;
select * from emp where salary<6000  ||  salary>9000;
select * from emp where salary not between 6000 and 9000;

   示例(6)：查询出20号部门或者30号部门的员工有哪些

select * from emp where deptId=20 || deptId=30;
select * from emp where deptId in(20,30);

   示例(7)：查询出不在20号部门并且不在30号部门的员工有哪些

select * from emp where deptId!=20 && deptId!=30;
select * from emp where deptId not in(20,30);

(8)模糊条件查询(使用like关键字)

              %  匹配任意个字符   >=0           _ 表示 任意一个字符      =1

   示例：

         ①查询出姓名中含有字母o的员工有哪些

         ②查询出姓名中以o结尾的员工有哪些

          ③查询出姓名中第二个字符是o员工有哪些

select * from emp where ename like '%o%';
select * from emp where ename like '%o';
select * from emp where ename like '_o%';

(9)分页查询

        查询的结果中有太多的数据，一次显示不完可以做成分页显示

        需要用两个已知的条件：当前的页码、每页的数据量

        公式：开始查询的值 = (当前的页码-1)*每页的数据量

         select * from emp  limit  开始查询的值,每页的数据量;

   示例：假设每页显示5条数据，分别查询出前3页

select * from emp limit 0,5;
select * from emp limit 5,5;
select * from emp limit 10,5;

7.复杂查询

(1)聚合查询 / 分组查询

     聚合查询：

        函数：就是一个功能体，需要提供若干个数据，最终返回结果

        聚合函数：

           count()/sum()/avg()/max()/min()

            数量   总和   平均   最大   最小

   示例：

          ①查询出所有员工的数量

          ②计算出所有员工工资的总和

          ③计算出所有女员工的平均工资

          ④查询出年龄最大的员工的生日

          ⑤查询出10号部门的最高工资

select count(ename) from emp;
select sum(salary) from emp;
select avg(salary) from emp where sex=0;
select min(birthday) from emp;
select max(salary) from emp where deptId=10;

     分组查询：

          按照指定的列分组，不是每一个列都适合分组

          分组查询只能查询分组条件和聚合函数

   示例：

         ①查询出男女员工的数量，平均工资

         ②查询出各个部门员工的最高工资，最低工资，工资总和

select count(eid),avg(salary),sex from emp  group by sex;
select max(salary),min(salary),sum(salary),deptId from emp group by deptId;

       md5()  加密函数

select  md5('123456');

       year()  获取日期中的年份

select  year('2021-5-8');

   示例：查询出1993年出生的员工有哪些

select * from emp where year(birthday)=1993;

(2)子查询

    子查询是多个SQL命令的组合，将一个SQL命令的结果作为另一个的条件使用

   示例(1)：查询出工资最高的员工所有的列

          步骤1：查询出工资的最高值

          步骤2：通过最高值查询员工

          步骤3：综合：

select max(salary) from emp;
select * from emp where salary=22000;
select * from emp where salary=(select max(salary) from emp);

    示例(2)：查询出和tom同一个部门的员工有哪些

           步骤1：查询tom的部门编号 —— 20

           步骤2：通过部门编号查询员工

           步骤3：综合：

select deptId from emp where ename='tom';
select * from emp where deptId=20 and ename!='tom';
select * from emp where deptId=(select deptId from emp where ename='tom') and ename!='tom';

    示例(3)：查询出和Maria同一年出生的员工有哪些

          步骤1：查询出Maria出生的年份

          步骤2：查询出这一年出生的员工，排除Maria

          步骤3：综合：

select year(birthday) from emp where ename='Maria';
select * from emp where year(birthday)=1992 and ename!='Maria';
select * from emp where year(birthday)=(select year(birthday) from emp where ename='Maria') and ename!='Maria';

(3)多表查询

     多表查询查询的数据是分布在多个表中，前提是表之间是有关联的。

    示例：查询出所有的员工姓名及其部门名称(两种方法)

select ename,dname from emp,dept where deptId=did;
select emp.ename,dept.dname from emp,dept where emp.deptId=dept.did;

     内连接(和之前的结果是一样的)

select ename,dname from emp inner join dept on deptId=did;

     左外连接(显示左侧表中所有的记录，先写哪个表哪个是左)

select ename,dname from emp left outer join dept on deptId=did;

     右外连接(显示右侧表中所有的记录，后写哪个表哪个是右)

     注意：左外连接和右外连接中的outer关键字可以省略的

select ename,dname from emp right outer join dept on deptId=did;

     全连接 ：full join ... on  (左侧和右侧所有记录都显示)  ：mysql不支持

          全连接解决方案：左外连接和右外连接的结果联合，合并相同的记录

     联合：union all   联合后不合并相同的记录 

                union   联合后合并相同的记录

(select ename,dname from emp left outer join dept on deptId=did)
   union
(select ename,dname from emp right outer join dept on deptId=did);

总：

列约束:
    主键约束 —— primary key
    非空约束 —— not  null
    唯一约束 —— unique
    默认值约束 —— default
    检查约束 —— check
    外键约束 —— foreign key(外键列) references 另一个表(主键列)
    自增列 —— auto_increment

sql语句关键字：
    给列起别名 —— as(可省略)
    显示不同的记录 —— distinct 
    升序 —— order by 列名 asc (asc可省略)
    降序 —— order by 列名 desc  
    默认值为null —— is null
    默认值不为null —— is not null
    并且 —— and
    或者 —— or / ||  / 
    满足其中一个 —— in(n1,n2)  都不满足 —— not in(n1,n2)
    在两者之间 —— between ... and  ...
    不在两者之间 —— not between... and ...
    模糊条件查询 —— 使用'like'关键字
                   % 匹配任意个字符   >=0
                   _  任意一个字符     =1
    分页查询 —— limit(当前的页码,每页的数据量) 
               当前的页码=开始查询的值 = (当前的页码-1)*每页的数据量
               开始查询的值和每页的数据量必须是数值型，不能加引号
    分组查询 —— group by
聚合函数：
    数量 —— count()
    总和 —— sum()
    平均 —— avg()
    最大 —— max()
    最小 —— min()
    加密函数 —— md5()
    获取日期中的年份 —— year()
多表查询：
    内连接 —— inner join...on...
    左外连接 —— left outer join...on...
    右外连接 —— right outer join...on...
    全连接 —— full join ... on...(mysql不支持)
 ----全连接解决方案：左外连接和右外连接的结果联合，合并相同的记录 ----
    联合后合并相同的记录 —— union
    联合后不合并相同的记录 —— union all