1.数据库链接操作
/*连接mysql*/
mysql -h 地址 -P 端口 -u 用户名 -p 密码
例如: mysql -h 127.0.0.1 -P 3306 -u root -p ****
/*退出mysql*/
exit;
2.数据库操作
#数据库操作
/*关键字:create 创建数据库(增)*/
create database 数据库名 [数据库选项];
例如: create database test default charset utf8 collate utf8_bin;
/*数据库选项:字符集和校对规则*/
字符集:一般默认utf8;
校对规则常见: ⑴ci结尾的:不分区大小写 ⑵cs结尾的:区分大小写 ⑶bin结尾的:二进制编码进行比较
/*关键字:show 查看当前有哪些数据库(查)*/
show databases;
/*查看数据库的创建语句*/
show create database 数据库名;
/*关键字:alter 修改数据库的选项信息(改)*/
alter database 数据库名 [新的数据库选项];
例如: alter database test default charset gbk;
/*关键字:drop 删除数据库(删)*/
drop database 数据库名;
/*关键字:use 进入指定的数据库*/
use 数据库名;
3.表操作
3.1 建表三大范式
1.第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。
2.第二范式(确保表中的每列都和主键相关)
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。
3.第三范式(确保每列都和主键列直接相关,而不是间接相关)
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
3.2 数据库一对一、一对多、多对多关系
1. 一对一关系
一对一关系是最好理解的一种关系,在数据库建表的时候可以将人表的主键放置与身份证表里面,也可以将身份证表的主键放置于人表里面
2. 一对多关系
班级是1端,学生是多端,结合面向对象的思想,1端是父亲,多端是儿子,所以多端具有1端的属性,也就是说多端里面应该放置1端的主键,那么学生表里面应该放置班级表里面的主键
3. 多对多关系
对于多对多关系,需要转换成1对多关系,那么就需要一张中间表来转换,这张中间表里面需要存放学生表里面的主键和课程表里面的主键,此时学生与中间表示1对多关系,课程与中间表是1对多关系,学生与课程是多对多关系
总结:最重要的关系就是1对多关系,根据面向对象思想在建表的时候将1端主键置于多端即可。
#表的操作
/*关键字:create 创建数据表(增)*/
create table 表名(
字段1 字段1类型 [字段选项],
字段2 字段2类型 [字段选项],
字段n 字段n类型 [字段选项]
)表选项信息;
例如: create table test(
id int(10) unsigned not null auto_increment comment 'id',
content varchar(100) not null default '' comment '内容',
time int(10) not null default 0 comment '时间',
primary key (id)
)engine=InnoDB default charset=utf8 comment='测试表';
语法解析(下文MySQL列属性单独解析):
如果不想字段为NULL可以设置字段的属性为NOT NUL,在操作数据库时如果输入该字段的数据为NULL,就会报错.
AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1.
PRIMARY KEY关键字用于定义列为主键.可以使用多列来定义主键,列间以逗号分隔.
ENGINE 设置存储引擎,CHARSET 设置编码, comment 备注信息.
/*关键字:show 查询当前数据库下有哪些数据表(查)*/
show tables;
/*关键字:like 模糊查询*/
通配符:_可以代表任意的单个字符,%可以代表任意的字符
show tables like '模糊查询表名%';
/*查看表的创建语句*/
show create table 表名;
/*查看表的结构*/
desc 表名;
/*关键字:drop 删除数据表(删)*/
drop table [if exists] 表名
例如: drop table if exists test;
/*关键字:delete,truncate 删除表中的数据*/
delete from 表名
truncate table 表名
例如: truncate table if exists test;
/*关键字:alter 修改表名(改)*/
alter table 旧表名 rename to 新表名;
/*修改列定义*/
/*关键字:add 增加一列*/
alter table 表名 add 新列名 字段类型 [字段选项];
例如: alter table test add name char(10) not null default '' comment '名字';
/*关键字:drop 删除一列*/
alter table 表名 drop 字段名;
例如: alter table test drop content;
/*关键字:modify 修改字段类型*/
alter table 表名 modify 字段名 新的字段类型 [新的字段选项];
例如: alter table test modify name varchar(100) not null default 'admin' comment '修改后名字';
4. 注意尽量不使用外键
性能问题(查询控制权限)
并发问题(死锁)
扩展性问题(迁移,分表分库外键无法生效)
技术问题(数据库开销变大)
4.数据操作
#数据操作
/*关键字:insert 插入数据(增)*/
insert into 表名(字段列表) values (值列表);
例如: create table user(
id int(10) unsigned not null auto_increment comment 'id',
name char(10) not null default '' comment '名字',
age int(3) not null default 0 comment '年龄',
primary key (id)
)engine=InnoDB default charset=utf8 comment='用户表';
/*外键,一个特殊的索引,只能是指定内容*/
create table color(
nid int not null primary key,
name char(16) not null
)
create table fruit(
nid int not null primary key,
smt char(32) null ,
color_id int not null,
constraint fk_cc foreign key (color_id) references color(nid)
)
4.1 增
insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from 表
4.2 删
delete from 表
delete from 表 where id=1 and name='alex'
4.3 改
update 表名 set 字段1=新值1,字段n=新值n [修改条件]
例如:update 表 set name = 'alex' where id>1
4.4 查
4.4.1 普通查询
select * from 表
select nid,name,gender as gg from 表
a、条件
select * from 表 where id > 1 and name != 'alex' and num = 12;
select * from 表 where id between 5 and 16;
select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select nid from 表)
b、限制
select * from 表 limit 5; - 前5行
select * from 表 limit 4,5; - 从第4行开始的5行
oracle 的分页:
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT *
FROM table_name) a
WHERE ROWNUM <= 40)
WHERE rn >= 21
sql sever的分页
。。。
更多选项查询
4.4.2、数据排序(查询)
select * from 表 order by 列 asc - 根据 “列” 从小到大排列
select * from 表 order by 列 desc - 根据 “列” 从大到小排列
select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序
4.4.3、模糊查询
select * from 表 where name like 'ale%' - ale开头的所有(多个字符串)
select * from 表 where name like 'ale_' - ale开头的所有(一个字符)
4.4.4、聚合函数
聚合函数的特点
1.每个组函数接收一个参数(字段名或者表达式) 统计结果中默认忽略字段为NULL的记录
2.要想列值为NULL的行也参与组函数的计算,必须使用IFNULL函数对NULL值做转换。
3.不允许出现嵌套 比如sum(max(xx))
1.聚合函数 count (),求数据表的行数
select count(*/字段名) from 数据表)——不建议conunt(*),建议用count(0)
2.聚合函数 max(),求某列的最大数值
select max(字段名)from 数据表
3.聚合函数min(),求某列的最小值
select min(字段名) from 数据表
4.聚合函数sum(),对数据表的某列进行求和操作
select sum(字段名) from 数据表
5.聚合函数avg(),对数据表的某列进行求平均值操作
select avg(字段名) from 数据表
6.聚合函数和分组一起使用
select count(*), group_concat(age) from students group by age;
1、功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
2、语法:group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'])
7.数学函数
SELECT ABS(-8); /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
8.字符串函数
SELECT CHAR_LENGTH(''); /*返回字符串包含的字符数*/
SELECT CONCAT('','',''); /*合并字符串,参数可以有多个*/
SELECT INSERT('',1,2,''); /*替换字符串,从某个位置开始替换某个长度*/
SELECT LOWER(''); /*小写*/
SELECT UPPER(''); /*大写*/
SELECT LEFT('hello,world',5); /*从左边截取*/
SELECT RIGHT('hello,world',5); /*从右边截取*/
SELECT REPLACE('','',''); /*替换字符串*/
SELECT SUBSTR('',4,6); /*截取字符串,开始和长度*/
SELECT REVERSE(''); /*反转*/
9.日期和时间函数
SELECT CURRENT_DATE(); /*获取当前日期*/
SELECT CURDATE(); /*获取当前日期*/
SELECT NOW(); /*获取当前日期和时间*/
SELECT LOCALTIME(); /*获取当前日期和时间*/
SELECT SYSDATE(); /*获取当前日期和时间*/
/*获取年月日,时分秒*/
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
4.5、分组查询
分组
select num from 表 group by num
select num,nid from 表 group by num,nid
select num,nid from 表 where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
select num from 表 group by num having max(id) > 10
特别的:group by 必须在where之后,order by之前
4.6、多表查询
a、连表
无对应关系则不显示
select A.num, A.name, B.name
from A,B
Where A.nid = B.nid
无对应关系则不显示
select A.num, A.name, B.name
from A inner join B
on A.nid = B.nid
A表所有显示,如果B中无对应关系,则值为null
select A.num, A.name, B.name
from A left join B
on A.nid = B.nid
B表所有显示,如果B中无对应关系,则值为null
select A.num, A.name, B.name
from A right join B
on A.nid = B.nid
b、组合
组合,自动处理重合
select nickname
from A
union
select name
from B
组合,不处理重合
select nickname
from A
union all
select name
from B
5.MySQL数据类型
#MySQL数据类型
/*MySQL三大数据类型:数值型、字符串型和日期时间型*/
/*数值型*/
/*字符串型*/
/*日期时间型*/
- | 关系型数据库 | 非关系型数据库(NoSql--->not only sql) |
---|---|---|
应用 | mariaDB, MySQL, Oracle, SQL Server 等 | mongoDB (文档型数据库), redis (键值型数据库), 列存储数据库 (HBase), Neo4j 等 |
优点 | 1.易于维护:都是使用表结构,格式一致 2.使用方便:SQL语言通用,可用于复杂查询; 3.复杂操作:支持SQL,可用于一个表以及多个表之间非常复杂的查询。 |
1.格式灵活:存储数据的格式可以是key,value形式、文档形式、图片形式等等,文档形式、图片形式等等,使用灵活,应用场景广泛,而关系型数据库则只支持基础类型。 2.速度快:nosql可以使用硬盘或者随机存储器作为载体,而关系型数据库只能使用硬盘; 3.高扩展性 4.成本低:nosql数据库部署简单,基本都是开源软件。。 5.支持分布式集群,负载均衡,性能高 |
缺点 | 1.每次操作都要进行sql语句的解析,消耗较大 2.读写性能比较差,尤其是海量数据的高效率读写; 3.固定的表结构,灵活度稍欠;(一致性) 4.高并发读写需求,传统关系型数据库来说,硬盘I/O是一个很大的瓶颈 |
1.技术起步晚,维护工具以及技术资料有限 2.不支持 sql 工业标准 3.没有join等复杂的连接操作 4.事务处理能力弱 5.没有完整性约束,对于复杂业务场景支持较差 |
应用场景 | 1.为有数据更新的表做索引或表结构变更 2.字段不固定时的应用 3.对简单查询需要快速返回结果的处理 |
首先一般非关系型数据库是基于CAP模型,而传统的关系型数据库是基于ACID模型的