说明
上节内容学习了数据库 MySQL
的安装、验证、数据库管理工具、数据库的基本操作命令,还没有学习的同学可以从主页去看上一篇推送内容
本节内容就学习有关数据库中表的操作,这其中包括 表内
和 表关联
的:创建、列数据类型、数据查询、筛选、删除、添加、修改等等操作,这块内容极为重要,重点不但需要掌握基本的 SQL使用规则,还要掌握一些系统提供的SQL函数。
提示:你可以将sql理解为一种编程语言格式,那么这其中用到的所有标点符号都是英文的,括号都是成对出现的,这种低级错误千万别给自己养成"习惯"。
表(CRUD)
1,创建表
create table 表名(列名 列的类型(长度) 列的约束,列名2 列的类型(长度) 列的约束);
列的类型
int
:和Java
中相同char
:固定长度(长度指的是字符个数)varchar
:可变长度(长度指的是字符个数)double
:float
:boolean
:date
:YYYY-MM-DDtime
:hh:mm:ssdatetime
:YYYY-MM-DD hh:mm:ss,默认值是 nulltimestamp
:YYYY-MM-DD hh:mm:ss,默认使用当前时间text
:主要用来存放文本。blob
:存放二进制。列的约束
主键约束:
primary key
唯一约束:
unique
非空约束:
not null
举例,创建学生表
登录
MySql
mysql -u root -p密码
进入指定数据库(如果没有数据库,则利用前面的创建数据库语句进行创建)
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 语法,<>
才是标准的不等于。逻辑运算符:
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;
注:如果不指定条件,则会将表中的数据一条一条全部删除。
truncate
和delete 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!