说明

上节内容学习了数据库 MySQL 的安装、验证、数据库管理工具、数据库的基本操作命令,还没有学习的同学可以从主页去看上一篇推送内容

本节内容就学习有关数据库中表的操作,这其中包括 表内表关联 的:创建、列数据类型、数据查询、筛选、删除、添加、修改等等操作,这块内容极为重要,重点不但需要掌握基本的 SQL使用规则,还要掌握一些系统提供的SQL函数。

提示:你可以将sql理解为一种编程语言格式,那么这其中用到的所有标点符号都是英文的,括号都是成对出现的,这种低级错误千万别给自己养成"习惯"。

表操作知识点总结.png

表(CRUD)

1,创建表

create table 表名(
列名 列的类型(长度) 列的约束,
列名2 列的类型(长度) 列的约束);
  • 列的类型

    int:和 Java中相同

    char:固定长度(长度指的是字符个数)

    varchar:可变长度(长度指的是字符个数)

    double

    float

    boolean

    date:YYYY-MM-DD

    time:hh:mm:ss

    datetime:YYYY-MM-DD hh:mm:ss,默认值是 null

    timestamp:YYYY-MM-DD hh:mm:ss,默认使用当前时间

    text:主要用来存放文本。

    blob:存放二进制。

  • 列的约束

    主键约束:primary key

    唯一约束:unique

    非空约束:not null

举例,创建学生表

  1. 登录MySql

    mysql -u root -p密码
  2. 进入指定数据库(如果没有数据库,则利用前面的创建数据库语句进行创建)

    use 数据库名字;

这两步是必须的,我们的表都是在库的基础上才有的,所以在创建表之前我们需要选对要使用的数据库。

1,创建学生表

create table student(
    sid int primary key,
    sname varchar(30),
    ssex int,
    sage int
);

2,查看表

show tables;

3,查看表的创建过程

show create table 表名;

4,查看表结构

desc 表名;

5,修改表

  • 添加列(add)

    alter table 表名 add 列名 列的类型 列的约束
    alter table student add chengji int not null;
  • 修改列(modify)

    alter table 表名 modify 列名 列的类型(长度);
  • 修改列名(change)

    alter table 表名 change 原列名 新列名 列名的类型(长度);
  • 删除列(drop)

    alter table 表名 drop chengji;
  • 修改表名(rename)

    rename table 原表名 to 新表名;
  • 修改表的字符集

    alter table 表名 character set 字符集名称;

6,删除表

drop table 表名;

表内、表与表之间的 SQL

1,插入数据

insert into 表名(列名1,列名2,列名3...) values (列名1对应的值,列名2对应的值,列名3对应的值);

批量插入

insert into student values(103,'lunzima',1,18),(104,'houyi',0,20),(105,'hanbin',1,22);

批量插入效率高于单条插入,但是批量插入其中一条如果出错,可能引起同批插入的其他条数据错误。

  • 如果是全列名插入,则可以省略表名后的列名不写,例如下面这样:
insert into student values(100,'yasuo',0,25);
  • 设置了主键的列,插入时要保证主键不重复。

  • 插入中文乱码解决方法

    在 MySql 的安装目录(比如我的是:C:\Program Files\MySQL\MySQL Server 5.5)下,找到my.ini文件,打开后编辑default-character-set=gbk这句代码即可,默认是utf8

    然后在命令行重新登录账户,操作即可。

  • 部分列插入时,列名不能省略。

    insert into 表名(要插入的列1,列2...) values(对应列的值...);

2,查询表中记录

select * from 表名;
  • 查看表中指定列的数据
select 列名1,列名2 from 表名;
  • 别名as查询

    select 表名的别名.列名1,表名的别名.列名2 from 表名 as 表名的别名;

    别名as是可以省略的。也可以给列名加别名,像下面这样

    select 表名的别名.列名1 列名1的别名,表名的别名.列名2 列名2的别名 from 表名 表名的别名;
  • 去重查询

    // 查询表中某列数据,并去掉重复值
    select distinct 列名 from 表名;
  • select 运算查询

    select *,列名*0.85 from 表名;

    这里的运算符可以是+,-,*,/。增加的列也可以添加别名;增加的列仅仅是在查询结果上显示,不会真正改变表中的结构。

  • where后的条件写法

    关系运算符>,>=,<,<=,!=,<>

    select * from 表名 where 列名 关系运算符 限定条件的值;
    // 比如这样
    select * from product where price <> 500;

    其中,!=不是标准的 SQL 语法,&lt;&gt;才是标准的不等于。

    逻辑运算符and,or,no

    // 举例
    select * from product where price > 10 and price < 2000;
    // 上面这句和下面这句等价
    select * from product where price between 11 and 1999;

    模糊查询:like

    • _:代表一个字符。
    • %:代表多个字符。
    // 查询商品名字中带有'代码'两个字的所有商品
    select * from product where pname like '%代码%';
    // 查询商品名字中第二个字符是'一'的所有商品
    select * from product where panme like '_一%';

    在某个范围获得值in

    // 查出商品编号为 3 和 5 的所有商品
    select * from product where cno in (3,5);
  • 排序查询:order by

    最后执行,对select的结果进行操作。

    • asc:升序(默认排序方式)
    • desc:降序
    // 按照某列进行排序
    select * from 表名 order by 列名 desc/asc;
    // 结合 where 条件的结果,在进行排序
    select * from 表名 where 列名 条件运算符 条件值 order by desc/asc;
  • 聚合函数

    • sum():求和
    • avg():求平均值
    • max():最大值
    • min():最小值
    • count():统计数量
    // 求和
    select sum(要求和的列名) from 表名;
    // 求平均值
    select avg(要求平均值的列名) from 表名;
    // 求最大值
    select max(要求最大值的列名) from 表名;
    // 求最小值
    select min(要求最小值的列名) from 列名;
    // 统计数量
    select count(表中任意一列或者直接写`*`) from 表名;

    注意:聚合函数不能直接跟在where后面。

    // 比如:查出价格大于平均值的所有商品
    select * from product where price > (select avg(price) from product); 
  • 分组:group by

    将表中某列值相同的记录放在一起,称为一组。

    // 按照某列去分组,对于列名相同的记录默认会显示排在前面的
    select * from 表名 group by 列名;
    // 按照某列排序,并统计该列的数量
    select 列名,count(列名) from 表名 group by 列名;
  • having

    条件筛选。出现在分组之后,其后可以接聚合函数。where关键字出现在分组之前,其后不可接聚合函数。

    // 比如:查询商品表中,按照商品编号分组显示每组的平均价格,并查询平均价格大于 60元的所有商品。
    select *,avg(price) from product group by cno having avg(price) > 60;

3,删除表中记录

  • 删除指定某条记录

    delete from 表名 where 条件;
    // 例如
    delete from student where sid=105;

    :如果不指定条件,则会将表中的数据一条一条全部删除。

  • truncatedelete from 表名; 删除表中数据有何区别?

    前者是将表直接删除,然后重新创建表,表中无数据。后者是一条一条删除表中所有数据。

    在数据量较少的情况下,后者效率高;反之,前者高。

4,更新表中记录

  • 更新某条记录

    update 表名 set 要更新的列名1=列的值1,要更新的列名2=列的值2 where 条件;

    比如这样:

    update student set sname='寒冰',sage=23 where sid=105;
  • 更新所有记录的某些列

    update 表名 set 要更新的列1=值1,要更新的列2=值2;

总结

  • 表的操作,根据个人职位和功能需求来定,一个完整体系表的构建是一个具体业务的逻辑体现。
  • 大多开发者在表内和表与表之间进行操作的时候多,其中最主要也是用的做多的操作是查询和筛选。

小编特意创建了一个公众号:推荐学java,分享与java相关的内容,并且以原创为主,欢迎大家搜索关注(关注即送视频教程),一起学Java!