数据库基本命令
- 登录:
mysql -u 用户 -p
- 创建数据库:
create database 库名 default charset=utf8
- 查看所有库:
show databases;
此处;不能少 - 使用特定的库:
use 库名;
- 删除库:
drop database 库名;
- 查看一个库中的所有表:
show tables;
- 创建一张表:
create table if not exists 表名( #如果表不存在才创建,也可以直接创建
字段1 类型 字段约束;#auto_increment 自增,从0开始
字段2 类型 字段约束;
字段3 类型 字段约束;
...
)engine=innodb default charset=utf8;
- 删除表:
drop table 表名;
- 查看一个表的完整结构:
desc 表名;
- 插入数据:
insert into 表名(字段名1,字段名2,字段名3,...) values (值1,值2,值3,...);
- 查看数据:
select * from 表名 where 查询条件;
- 更新数据:
update 表名 set 字段1=值1,字段2=值2,... where 条件;
- 删除:
delete from 表名 where 字段=值;
SQL(结构化查询语言)
SQL语言四部分:DDL(定义)、DML(操作)、DQL(查询)、DCL(控制)
mysql中的数据类型
- 字符串类型
- 定长串:char(长度) :存储数据长度小于设置的长度值,且始终都用设置的长度个字节空
间来存储它,长度范围为1~255字节 - 变长串:
- varchar(长度) :存储数据长度小于设置的长度值,存储长度为实际存储的数据
长度,长度范围不超过255字节 - text:最大长度为64K的文本长度
- varchar(长度) :存储数据长度小于设置的长度值,存储长度为实际存储的数据
- 定长串:char(长度) :存储数据长度小于设置的长度值,且始终都用设置的长度个字节空
- 数值类型:
- int:整数值,范围为int类型的取值范围
- tinyint:整数值,范围为-128127,如果是无符号则为0255
- float:单精度浮点值
- double:双精度浮点值
- decimal:精度可变的浮点值,如:decimal(5,2),数字总共5位,小数点后2位。
- 日期和时间
- Date:表示1000-01-01~9999-12-31中的日期,格式为yyyy-MM-dd
- DATETIME:
- TIME:格式为HH:MM:SS
- YEAR:两位数字,范围是70 (1970年)+69;四位数字,范围1901年~2155年
- 二进制数据类型:一般不用
- BLOB:最大长度64KB
- MEDIUMBLOB:最大长度16MB
- LONGBLOB:最大长度4GB
- TINYBLOB:最大长度255B
表的字段约束
- unsigned:无符号
- 长度限制:
- 如,varchar(20)
- int(4),无意义,默认的无符号的int为int(11),有符号的为int(10)
- 只有当int类型设置有前导0(zerofill)时,设置int长度才有意义
- not null:不能为空
- default:设置默认值
- primary key:主键,主键不能为空,一旦插入表中最好不要再修改
- auto_increment:定义为自增属性,数字会加1
unique:唯一索引(数据不能重复),可以增加查询速度,但是会降低插入和更新速度
mysql特有的运算符
- in:
- not in:
- is null:
- is not null:
- like:支持特殊符号%(表示任意数量字符)和_(任意一位字符)
- between:
- and:
修改表结构
语法格式:
alter table 表名 action(更改的选项);
- 添加字段:
--在指定表的最后添加一个字段:
alter table 表名 add 添加的字段信息;
-- 如,在 users 表中追加一个 num 字段:
alter table users add num int not null;
-- 在指定字段后添加一个字段:
-- 如,在 user 表的 age 字段后添加一个 email字段:
alter table users add email varchar(20) after age;
-- 在指定字段前添加一个字段
-- 如,在users表的age字段前添加sex字段:
alter table users add sex char(5)
-- 在表的最前面添加一个字段
-- 如,在users表的最前面添加AA字段:
alter table users add AA int first;
- 删除字段
# 删除字段: alter table 表名 drop 被删除的字段名;
-- 删除 users 表中的AA字段
alter table user drop AA;
- 修改字段
# 语法格式: alter table 表名 change|modify 被修改的字段信息;
-- change:可以修改字段名
-- modify:不能修改字段名
- 修改表名
# 语法:alter table 原表名 rename as 新表名;
- 修改表中的自增值
# 在常规情况下 auto_increment 默认从1开始连续递增
-- 修改 users 表的 auto_increment 递增初始值为10。
alter table users auto_increment = 10;
- 修改表引擎
# 推荐在定义表时,表引擎选为innodb
# 如果不知道可以通过查看建表语句获取当前的表引擎:show create table 表名;
# 查看当前表状态
show table status from 库名 where name='表名'\G;
# 修改表引擎语句
alter table 表名 engine = 'MyISAM';
mysql数据库表引擎
- mysql服务器吧数据的存储和提取都封装到了一个叫存储引擎的模块里。逻辑上表是一行一行的记录组成,但
是物理上如何记录,怎么读取,怎么写入,这都是存储引擎负责的事。 - 常用的数据库存储引擎
- innodb:具备外键支持功能的事务存储引擎
- MyISAM:非事务处理的存储引擎
- 两者区别
innodb支持事务,myisam不支持事务
myisam:每个myisam在磁盘上存储成了三个文件:1).frm文件存储表结构
2).MDY文件存储数据、3).MYI文件存储引擎
innodb主要分为两个文件进行存储:1).frm存储表结构、2).idb存储数据和索引
myisam:只支持表级所,不支持外键
innodb:支持事务和行级锁,支持外键
字符集
- utf8字符集表示一个字符需要使用1~4个字节
- utf8mb3:只使用1~3个字节表示一个字符
- utf8mb4:使用1~4个字节表示一个字符
- 查看mysql字符集:
show charset;
mysql查询条件
- 语法:select 字段列表|* from 表名 条件;
- 条件可以为:
[where 搜索条件]
[group by 分组字段 [having 分组条件]]
[order by 排序字段 排序规则]
[limit 分页参数]- where条件查询
- 可以在where子句中指定任何条件
- 可以使用 and 或者 or 指定一个或多个条件
select * from users where age>=20 and age<=30; select *from users where age between 20 and 30;
- like子句:模糊搜索,%:表示任意个字符。_:任意一个字符。
- where条件查询
mysql中的统计函数(聚合函数)
max(),min(),count(),sum(),avg()。
# 计算users表中的年龄字段的最大,最小,平均,求和年龄并为每项设置别名
select max(age) as max_age,
min(age) as min_age,
avg(age) as avg_age,
sum(age) as sum_age
from users;
# 统计函数count(统计列)
select count(*|指定列) from users;
--count(*)是按照表中所有列统计。只要其中一列有数据,就可以计算
--count(指定列)如果指定列上的某一个为空,那么null将不会被统计
聚合函数一般配合分组(group by)使用
group by分组 : 根据一个或多个列对结果进行分组
sql -- 统计users表中的男女生人数,根据男女生分组后统计,并把统计后的结果都记为num select sex,count(*) from users group by sex --统计每个班的男生女生人数 select classid,sex,count(*) as num from users group by classid,sex;
order by 排序
- 由大到小(降序): desc
--按年龄从大到小
select * from users order by age desc;
- 由小到大(升序): asc
--按年龄从小到大排序
select * from users order by age asc;
- 按多个字段排序
--先按年龄排,年龄相同则按id排
select * from users order by age,id;
limit数据分页
- limit n :提取n条数据
- limit m,n :跳过m条数据,提取n条数据
having
- 用于在分组聚合计算后,对结果再一次过滤
# 作业
--1.统计班级 classid 为2 的男女生人数
select sex,count(*) from users group by sex where classid=2;
--2.获取每个班的平均年龄,并按降序排
select classid,avg(age) as avg_age from users group by classid
order by avg_age desc;
--3.统计每个班的人数,并按降序排
select count(*) as count from users order by count desc;
--4.获取班级人数最多的班级id信息
select classid,count(*) as count,max(count),classid from users
group by classid;
mysql数据库导入和导出
- 数据导出:
- 退出mysql客户端
- 命令:
mysqldump -u 用户名 -p 库名 表名 > 导出后的存储位置
- 导出指定数据库中的所有表,会在本地形成一个.sql文件。
- 注意:只写库名导出整个库中的所有表,加上表名,导出指定库中的指定表中的数据
- 数据导入:
- 退出mysql客户端
- 命令:
mysql -u 用户名 -p 导入到的库名 < 要导入的文件存储位置
数据库权限管理
最高权限用户root,一般采用权限收到限制的用户,用于项目开发
- 创建用户语法:
grant 授权操作 on 授权的库.授权的表 to 账户@登录地址 identified by '密码';
子查询与表连接
1、子查询(嵌套sql)
- 在一条sql语句中,它的where条件是另一条sql的查询结果。
2、关系表
- 一个表中相同数据出现多次不是一件好事,关系表设计保证把信息分解成多个表,即一类数据一个表
各表通过关系(一些常用值)相关联
2.1、一对一关系和外键
- 一对一:一个表中的数据,对应着另一张表中的一个数据。
- 外键:一个表中的一个字段,这个字段是另外一张表的主键
- 物理外键:在创建表时,就指定这一个表中的一个字段是外键,并且强关联某个表中的字段。
- 逻辑外键:创建一个普通字段,没有强关联关系,需要通过程序逻辑来实现
2.2、一对多关系
- 一个表中的一条数据对应着另一个表的多条数据
2.3、表联结
- 就是一种查询机制,用在一个select语句中关联表进行查询,成为联结
- 等值联结:
- 联结方式:
联结vendor和products表
- where联结:
select vend_name,prod_name,prod_price from vendor,products where vendor_id=products_id;
- inner join on联结:on后面跟联结条件,如果在某个表中还有查询限制条件
则需要在 where 后面添加,如果没有,where即以后的语句可以省略。
select vend_name,prod_name,prod_price from vendor inner join products on vendor_id=products_id;
- 联结方式:
- 自联结:一个表与自身联结
- 自联结时需要为表取两个别名
- 外部联结:如果被联结的两个表中有字段为空,如果使用内部联结无法查询出不满足联结条件的元组(如上
一条sql语句,vendor表中有一个vendor_id=3,但products表中没有一个products_id=3的元组,
那么查询结果中不会有vendor_id=3的元组,如果想要包含它,需要使用外连接)- left join :左侧表为基准,去关联查询右侧表中的数据,如果有未关联的数据,那么结果为空
- right join :右侧表为基准,去关联查询左时侧表中的数据,如果有未关联的数据,那么结果为空
2.4、组合查询 union
- union必须由两条或两条以上得到select语句组成,语句之间用union关键字分隔
- union中的每个查询必须包含相同的列、表达式或聚集函数
- 列数据类型必须兼容
-
如果两个或多个select查询语句的查询字段相同,且想将查询的所有结果作为一个结果集
返回,那么可以使用union来连接两条或多条select语句,此时查询的结果如果重复,union会自动去重
如果想留下重复的元组,需要使用union all
。 - 在使用union组合查询时,只能使用一条order by语句,它必须出现在最后一条select语句之后。
此时order by是对整个结果集的排序,并不是针对其中某一个select查询结果排序
批处理
- 将多条语句放到一起批量处理
- 批处理原理:将多条sql语句,转换为一个SQL指令。显著的提高大量sql语句执行时的数据库性能
- Statement对象所使用的流程:
- 1、得到statement对象
- 2、将一条SQL语句加入到批处理中
state.addBatch(String sql);
- 3、执行批处理
state.executeBatch();
- 4、清空批处理
state.clearBatch();
- PreparedStatement对象使用流程:
- 1、得到
PrepareStatement
对象 - 2、填充预编译参数
- 3、将一条SQL语句加入到批处理中
preState.addBatch(String sql);
- 4、执行批处理
preState.executeBatch();
- 5、清空批处理
preState.clearBatch();
- 1、得到