Mysql数据库的基础操作

Mysql数据库

1 数据库：

   存放数据的仓库  DB(database)

2 相关的功能

   数据库  DB(database)

   数据库管理员 DBA(database administrator)

   数据库管理系统  DBMS (database manager system)

   关系型数据库管理系统  RDBMS (relational database manager system)

3 常见的数据库软件

   Oracle,mysql    ：甲骨文

   DB2           ：IBM

   Sybase         ：赛贝斯

   SqlServer,Access ：微软(ms)microsoft

4 数据库模型

   数据关联系统根据数据模型对数据进行存储和管理

   层次模型：以树形的层次模型来组织数据

             数据分级别  数据等级过于分明  不适用于所有数据

   网状模型：每个节点之间都相互关联  关系复杂

             数据增删改查比较困难      

   关系模型：通过二维表来存储数据

             列：字段(表示一类数据)  ：字段名(列名)、字段类型(数据类型)

         行:  记录(表示一个对象)  ：多个数据的形成的整体

   关系对象模型: 某个字段可以是一个记录

5 其他概念

   表结构：列名+列类型    (表的组成结构)    查看表结构： desc 表名;

   表记录：表中每行的数据 (表中存放的数据)  查看表记录： select * from 表名;

   先有表结构  再有表记录 创建表时要确定表结构然后再插入表记录

6 数据库管理系统作用：

     数据定义：对表结构的管理

     数据操作：对表记录的增删改查

     数据约束：对数据的完整性 合理性进行控制

     权限管理：对数据库表操作的权限分配和管理

     并发控制：多线程并发控制 防止线程安全问题(通过事务管理实现)

     数据恢复：数据库备份

7 sql的基本语法

     >sql中语句结束 必须以;

     >sql中字符串必须写在 单引号 中

     >sql中可以通过空格和换行对语句进行格式化

     >sql语言不区分大小写  (关键字用大写 名字和数据小写)

     >sql语句中通过/**/来进行注释

     >sql中定义名字不能使用sql语言的关键字

8  sql :structured query language  结构化查询语言

         程序通过数据库操作系统来操作数据库时使用的语言

         按用途划分成4种

               dql：data query language          数据查询 对表记录的查询 select

               dcl：data control language        权限语言 对用户权限的管理

               dml：data manipulation  language  数据操作 对表记录的增删改insert delete update

               ddl：data definition language      数据定义 对表结构的操作

         按标准划分

               sql标准：所有数据库操作系统都能识别

               sql方言：只有读取数据库操作系统可以识别的sql语句  (如limit)

9 数据类型

整数类型：

           bit:        1-64个bit   bit(2) 2个二进制(0-3)

    tinyint:     1个byte

        *int/integer:  4个byte

    bigint:      8个byte

       字符类型：

          *char       1-255个byte    固定长度  char(10)    占固定10个字节空间

         *varchar     1-65535个byte  可变长度  varchar(10) 占最多10个字节的空间

           text        1-64kb

    tinytext     1-256byte

     二进制类型：

           binary      1-255byte     固定长度  binary(10)

    varbinary    1-255byte    可变长度  varbinary(10)

          *blob        1-64kb  

    tinyblob     1-256b

    longblob    1-4Gb

 

    浮点类型：

        *float      -3.4E38---+3.4E38      float(5,2)整数部分3位 小数部分2位 23.12345

         *double    -1.79E308---+1.79E308  double(5,2)整数部分3位 小数部分2位 23.12345  

       *decimal      65位             转用于表示钱的数据类型(不会自动四舍五入)

    逻辑类型：

         *boolean/bool    true(1)   false(0)

    日期类型：

          year   表示年  year(2) 只有两位

         *date   表示年月日  格式：yyyy-mm-dd

   time   表示时分秒  格式：hh:mm:ss

    *datetime   表示年月日时分秒  格式：yyyy-mm-dd hh:mm:ss

          timestamp   表示年月日时分秒 格式：yyyy-mm-dd hh:mm:ss

 select str_to_date(str,'%Y%m%d %H%i%s');     把指定格式的字符串 转换为date

 select date_format(date,'%Y%m%d %H%i%s');   把参数date转换为指定格式的字符串

 

10  ddl语句

 

   >显示当前数据库的名字

   select database();

   >创建表

    create table if not exists tableName(

             fieldName11 fieldType11 primary key,

             fieldName12 fieldType12,

             fieldName13 fieldType13            

    ) charset=’gbk’;

   注意：所有的表必须定义主键：

         在关系表中，如果一个字段或几个字段组合的值可唯一标志其对应记录，

         则称该字段或字段组合为码、主键

   >显示表结构

    desc tableName;

   >显示创建表的语句

    show create table tableName;

   >删除表

    drop table tableName;

   >修改统一前缀

    alter table tableName......

   >修改表名

    ALTER TABLE cunmin RENAME linju;

   >添加列

    alter table cunmin add fieldName fieldType;

   >删除列

    alter table cunmin drop fieldName;

   >更改列名

    alter table cunmin change oldFieldName  newFieldName newFieldType;

   >更改列类型

    alter table cunmin modify fieldName newFieldType;

 

   >查询当前时间

    select now();显示当前时间   2018-03-01 17:08:44

    select current_date();显示当前年月日   2018-03-01

    select current_time();显示当前时分秒   17:08:44

    select current_timestamp();显示当前时间戳   2018-03-01 17:08:44   

 查询不会修改数据库表记录！

   alter table stu charset='gbk';

 

12 dql:数据查询  对表记录的查询(select)

           查询所有列

select * from tableName where 查询条件;

查询指定列

select field1,field2,field3.... from tableName where 查询条件;

去除重复行  distinct

select distinct field1,field2,field3.... from tableName where 查询条件;

列运算：

    1：加减乘除求余数

       null与任何数据运算结果都是null

    2：字符串可以进行连接运算：concat

       null与任何数据连接结果都是null

    3：进行null转换

       select concat(name,'的分数是：',score) from student;

       select concat(ifnull(name,'无名氏'),'的分数是：',ifnull(score,0)) from student;

            4:起列别名(给查询结果集  的列起个别称)

       select name as 名字,socre 分数 from student;

    排序：结果集安指定列进行排序

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

  select * from student where name like '李%' order by score desc;  按分数降序排序

  select * from student where name like '李%' order by score [asc]; 按分数升序排序

  select * from student where name like '李%' order by score [asc], id desc; 按分数升序排序 如果分数

相同,按id降序排序

 

一、 基本查询    select

1. 字段(列)控制

 1) 查询所有列

    select * from tableName;

 2) 查询指定列

    select fieldName1,fieldName2,... from tableName;

 3) 完全重复的记录只一次 DISTINCT

    select sname,sex,score from stu;

    select distinct sname,sex,score from stu;  

 4) 列运算

 4.1数量类型的列可以做加、减、乘、除,求余 运算

    select score*2 from stu;

    update stu set score=score*2 where sid=1;

    update stu set score=score*sid where sid=2;

select score%2.5 from stu where sid=3;

 

 4.2字符串类型可以做连续运算  CONCAT()

    select concat("我叫","苗天宝");

    select concat('我叫','苗天宝');

    select concat('我叫','苗天宝',',今年',1,'岁');

    select concat('名字=',sname,',性别=',sex,',考了',score,'分!') from stu;

    select concat(sname,',',sex,',',score) from stu;

    /*出现乱码！*/

    select concat(1,1);

    select concat('abcd',null);/*null*/

select 1+null;/*null*/

 

 4.3转换NULL值  IFNULL()

   update stu set score=score+1;

   update stu set score=ifnull(score,0)+1;

   select sid,score from stu;

   select sid,ifnull(score,0) from stu;

   select concat(sname,"分数是:",score) from stu;

   select concat(ifnull(sname,'无名'),'分数是:',ifnull(score,0)) from stu;

 

 4.4给列起别名  AS

    select concat(ifnull(sname,'无名'),'分数是:',ifnull(score,0)) as '自我介绍' from stu;

    select concat(ifnull(sname,'无名'),'分数是:',ifnull(score,0)) as 自我介绍 from stu;

    select concat(ifnull(sname,'无名'),'分数是:',ifnull(score,0)) as "自我介绍" from stu;

    select sid as 编号,sname as 名字,sex as 性别,score as 分数 from stu;

    select sid  编号,sname  名字,sex 性别,score  分数 from stu;

2. 条件控制

1) 条件查询

   =  >= <= != <>  <  >

   or    and

   id between  a and b   等价于  id>=a and id <=b

   id in(id1,id2,id3)    等价于 id=id1 or id=di2 or id=id3

   is null               

   is not   null

   select * from stu where sid not in(1,3,5);   

2) 模糊查询 like

   like   %  0到多个字符  _1个字符

 

二、排序

1) 升序  ORDER BY xx ASC;

2) 降序  ORDER BY xx DESC;

3) 使用多列作为排序条件

   select * from stu where sex='女' order by  score DESC;

   select * from stu where sex='女' order by  score asc;

   select * from stu where sex='女' order by  score;

   insert into stu values(112,'新来的','男',-11,1);

   insert into stu values(122,'新来的','男',-11,1);

   insert into stu values(113,'新来的','男',-11,1);

   insert into stu values(67,'新来的','男',-11,1);

   /*查询所有的男生  结果以分数倒序  分数相同以编号升序*/

   select * from stu where sex='男' order by score desc ,sid asc;

   select * from stu where sex='男' order by score desc;

   select * from stu where sex='男' order by sname desc;

   /*字符串之间可以做比较 逐个字符按编码集比较*/

 

三、聚合函数(结果是一个值)

  聚合函数用来做某列的纵向运算。

1) COUNT

       select count(*) from stu;/*获取总行数*/

       select count(score) from stu;/*获取score不为null的记录数*/

       select count(sname) from stu;/*获取sname不为null的记录数*/

2) MAX

       select max(score) from stu;

3) MIN

       select min(score) from stu;

       select count(*) 总人数,count(score) as 考试人数,

              max(score) as 最高分,min(score) as 最低分 from stu;

4) SUM

      select sum(score) from stu;  

5) AVG

      select avg(score) from stu;

      /*获取参加考试的学生的平均分*/

      select sum(score)/count(*) from stu where score is not null;

      select count(*) from stu where score is not null;

      select count(score) from stu ;

      /*获取所有学生的平均分*/

      select sum(ifnull(score,0))/count(*) from stu;

      select sum(score)/count(*) from stu;

  count max min sum avg 后面的()，要紧跟，不能有空格

 

四、分组查询  GROUP BY

  分组查询是把记录使用某一列进行分组，然后查询组信息。

   组条件  HAVING

 

  >获取男生的个数

   select * from tab1;

   select count(sex) from tab1;/*获取有性别的总记录数*/

   select count(sex) from tab1 where sex='男';

   select sex 性别,count(*) 人数,min(score) 最低分 from tab1 group by sex;/*按性别分类*/

   select sex 性别,count(*) 人数,min(score) 最低分 from tab1 group by sex;

   select sex 性别,count(*) 人数,min(score) 最低分 from tab1 group by sex having sex='男';

   select sex 性别,count(*) 人数,min(score) 最低分 from tab1 where sex='男';

 

  >获取所有班级的班级名称 总分数 总人数 平均分 最高分

   select  className 班级名称,count(*) 班级人数,sum(score) 班级总分数,

           avg(score) 班级平均分,max(score) 班级最高分 from tab1 group by className

 

  >获取所有有名称的班级的班级名称 总分数 总人数 平均分 最高分

   select  className 班级名称,count(*) 班级人数,sum(score) 班级总分数,

           avg(score) 班级平均分,max(score) 班级最高分 

   from tab1

   where className is not null

   group by className;

 

   select  className 班级名称,count(*) 班级人数,sum(score) 班级总分数,

           avg(score) 班级平均分,max(score) 班级最高分 

   from tab1

   group by className

   having className is not null;

 

  >获取总人数大于等于7 并且 平均分大于30的班级信息

   select  className 班级名称,count(*) 班级人数,sum(score) 班级总分数,

           avg(score) 班级平均分,max(score) 班级最高分 

   from tab1

   group by className

   having count(*) >=7 and avg(score) >30;

 

   select  className 班级名称,count(*) 班级人数,sum(score) 班级总分数,

           avg(score) 班级平均分,max(score) 班级最高分 

   from tab1

   group by className

   having 班级人数 >=7 and 班级平均分 >30;

 

  >获取班级信息 并按平均分倒序

   select  className 班级名称,count(*) 班级人数,sum(score) 班级总分数,

           sum(score)/count(*) 班级平均分,max(score) 班级最高分 

   from tab1

   group by className

   order by 班级平均分 desc;

  

  >获取每个有名称的班级的 参加考试的学生的班级信息 按平均分排序 只要前2个班级

 select  className 班级名称,count(*) 班级人数,sum(score) 班级总分数,

           sum(score)/count(*) 班级平均分,max(score) 班级最高分 

 from  tab1 where score is not null  group by className  having className is not null

 order by 班级平均分 desc  limit 2,3;

  

五、limit子句(方言)  limit 20, 10

  LIMIT用来限定查询结果的起始行，以及总行数。

  select * from tab1 order by score desc;

  select * from tab1 order by score desc limit 1,3;//获取前三名

  

select 字段名 别称  from  表名 where 查询条件 group by 字段名 having 组条件order by 字段名 desc

limit n,m;