MySQL (一):
- MySQL的奋斗史:
MySQL是一个开源的重量型关系型数据库,由创始人Monty在1995年5月234日正式推出发行mysq的3.11.1的发行版,这时的mysql性能提升了很多,但还是无法支持实务操作、子查询、存储过程、试图、外键等功能,面对数据库霸主oracle和微软的sql server 没有什么竞争力!面对窘境的Monty在1999的冬天成立了名为Uppsala开发团队,开发了并发行了以BDB为事务存储引擎的mysql-3.23版本,第二年把mysql进行开源免费!后续不仅整合了优化了BDB(innoDB前身)存储引擎,还陆续添加新功能!详细历程可以去自行了解!
-
MySQL的下载:
-
我比较喜欢去官网下载,因为干净且没有垃圾捆绑!
1. 官网下载地址:https://downloads.mysql.com/archives/community/ 小提示: 我用的是Windows版本的5.6, 个人建议下载windows版本的软件最好用源码的压缩包安装,不要用.exe的!.exe安装10分钟卸载头炸裂,因为他要走注册表安装,所以很难卸载干净,后面你要在装就各种不成功,懂得都懂! 小提示: 如果网页下载慢且你有下载工具迅雷,可以在右键点击download按钮,点击复制链接地址,这时开启的迅雷会弹出窗口进行下载! 小提示:在电脑里最好自己建一个安装各种软件的文件夹,起名见名知意即可,最好用英文名字 2. 把安装包放到新建的文件夹中解压, 然后把这源安装包删除!
-
-
安装操作:
-
配置mysql环境变量
win+s快捷键弹出搜索框,输入 系统——>编辑系统环境变量——>环境变量——>找到系统变量的path点击编辑——)新建——>如:D:\Env-windows\mysql-5.6.48\bin——>一直确定退出就配置好了!
-
进入命令提示符进行安装
1. win+s快捷键弹出搜索框,输入命令——>一定要用管理员权限运行命令提示符——>按以下操作进行 C:\Windows\system32>d: D:\>cd D:\Env-windows\mysql-5.6.48\bin D:\Env-windows\mysql-5.6.48\bin>mysqld -install Service successfully installed. 表示安装成功 2. 更名并修改my.default.ini为my.ini配置文件: 添加以下内容: [mysqld] basedir=D:\Env-windows\mysql-5.6.48 datadir=D:\Env-windows\mysql-5.6.48\data port=3306 skip-grant-tables #初次登陆跳过密码验证 保存退出后启动mysql服务 net start mysql 3. 进入mysql,修改密码(这里我遇到了坑,记录如下): 1. mysql -uroot -p 进入mysql,直接跳过密码登陆, show databases; use mysql; update user set password='123456' where user='root'; flush privileges; exit: 2. 找到Mysql/my.ini,把 # skip-grant-tables注释,保存退出 net stop mysql #关闭 net start mysql #重启 3. mysql -uroot -p123456; 这时我报了一个异常:如下 D:\Env-windows\mysql-5.6\bin>mysql -u root -p123456; Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 注解:我查了很多资料,其大致原因是这个root账户权限过期了,得重新赋权!(如有理解错误,请指出并说明) 4. 解决如下: use mysql; update user set password='123456' where user='root'; flush privileges; GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY '123456'; exit: 在进行登陆,就可以正常登陆了,我的问题解决!
-
-
数据类型:
-
字符类型
char 定长字符 特点:实际数字不够位数以空字符填充,相较于varchar搜索效率快很多,但显得浪费空间 长度范围 0-255字节 varchar 不定长字符 特点:根据实际使用来调节所需空间,不浪费资源,但查询效率相对较低 长度范围 0-65535字节 TEXT 长文本数据 长度范围:0-65535 字节
-
日期类型dat
date 占位大小:3字节 格式:YYYY-MM-DD 应用场景:日期值 time 占位大小:3字节 格式:HH:MM:SS 应用场景:时间值或持续时间 datetime 占位大小:8字节 格式:YYYY-MM-DD HH:MM:ss 应用场景:混合日期和时间值
-
数值类型
int或integer 占位大小:4字节 范围:0,4294967295 应用场景:大整数值 tinyint 占位大小:1字节 范围:0,255 应用场景:小整数值 float 占位大小:4字节 范围:0,(1.175494351E-38,3.402823466E+38) 应用场景:单精度浮点数 double 占位大小:8字节 范围:0,(2.2250738585072014 E-308,1.797693134862315 7 E+308) 应用场景:双精度浮点数
-
-
字段 (column):
#增: alter table student add (Sname varchar(20),birther date default sysTime()); #删: alter table student drop column Sname; #改: 1、alter table student modify (Sname varchar2(30)) #修改字段数据类型 2、alter table student rename column oldName to newName; #查: show columns from Student; # 查看表的所有字段属性
-
约束类型( constraint):
# 增: alter table Student add constraint 约束名 约束类型(列) alter table Student add constraint PK_id primary key(Sid); # 删: alter table Student drop constraint (pk_id,xxxx); # 改: 1、先增加一个新约束,再删除旧约束,没有修改约束的命令语句 # 查: select constraint_name, constraint_type from user_constraints where table_name = "STUDENT";
-
主键 (Primary key)
# 主键约束要求主键列的数据是唯一性且不为空,主键约束细分为:单字段主键和多字段主键 1. 单字段主键: 定义语法: 字段名 字段类型 字段约束 create table test_01 { 'id' int(12) primary key, 'name' varchar(20) 'sex' int(10), 'age' int(10), }; 也可以这样: 定义语法:定义完所有字段后在定义约束字段,字段约束(字段名) create table test_01 { 'id' int(12) , 'name' varchar(20) 'sex' int(10), 'age' int(10), primary key(id), }; 2. 多字段组合主键 语法:约束类型(字段1,字段2) create table test_01 { 'id' int(12) , 'name' varchar(20) 'sex' int(10), 'age' int(10), primary key(id,name), };
-
外键 (Foreign key)
# 外键作为表与表之间连接的桥梁,它可以在一个表中有一个或多个外键,而这个外键可以是单子段或多字段组合!如果建立外键一般不会为空,那这个外键的值就必须等同于父表中某个主键的值! 外键一般建立在子表中,且这个外键字段一般是父表的主键字段! 使用场景:一对一、一对多、多对多的表连接查询 建立外键的语法:Constraint (外键名) Foreign Key (字段1,字段2)<子表中跟主表主键连接的字段名> References 主表名(字段1) # 先创建一个test_02的子表: create table test_02 { 'id' int(12) , 'name' varchar(20) 'sex' int(10), 'age' int(10), ‘staffid’ int(12), primary key(id), Constraint test_staffid Foreign Key (staffid) References test_01(id) };
-
检查 (check )
# 对该字段的值设定一个活动范围,不能越出范围 语法:CHECK(<检查约束>) check(字段1 >10 and 字段1 <100) create table test_01 { 'id' int(12) , 'name' varchar(20) 'sex' int(10), 'age' int(10), primary key(id), CHECK(sex="男" or sex="女") };
-
唯一 (unique)
# 唯一约束要求该列具有唯一性,可以为空,但只能有一个值为空值,保证了表中某列和多列数据没有重复数据 语法: 字段名 数据类型 unique Constraint 约束名称 unique (字段名)
-
非空 (not null)
# 是指该字段值不能为空 # 语法: 字段名 数据类型() not null # 实例: create table Student ( 'Sid' int not null, 'Sname' varchar(20) not null, 'Sage' int(10) not null, ) # 添加not null约束 alter table table_name modify Sage not null; # 删除
-
默认(default)
#规定在没有给定赋值时是默认值
-
-
mysql的存储引擎innoDB与myiSAM的对比
MYISAM INNODB 事务支持 不支持 支持 数据库行锁定 不支持 支持 外键约束 不支持 支持 全文检索 支持 不支持 表空间的大小 较小 较大,大约为2倍 表插入数据 较快 较慢 -
DDL数据库操作语言
1.增 语法: 1.insert into table_name(字段1,字段2....) value(值1,值2); #字段和值位置要相对应 2. insert into table_name(字段1 字段1类型 字段1约束类型,字段2.... ); value(值1,值2) #字段和值位置要相对应 3. insert into table_name value(值1,值2); #不写字段时后面的值对应位置默认与表中字段位置一致! 2.删 语法: # delete (不会删除表结构,能根据日志进行回滚) # 不建议使用 1. delete from table_name; # 根据条件删除 2. delete from table_name where id=1; # truncate (完全清空表数据,不改变表结构、索引、约束) truncate table_name; # drop (主要用于删除数据表和数据库,会删除内容和结构,不能回滚) # 删除某数据表 drop table table_name; # 删除某数据库 drop database database_name; # drop、delete、truncate的区别: 相同: 1. delete、truncate都是侧重删除表数据,并不会删除表结构 2. truncate、drop是DDL语言,会立即执行,效率快! 不同:1. drop 会删除所有东西,释放表/数据库的占用空间 2. delete是DML(数据库维护语言)语言,操作记录会被放到rollback segment和日志中,可以回滚! 3. truncate、drop是DLL,操作立即生效,操作记录不会被放到rollback segment,不能回滚! 4. 删除效率:truncate >drop >delete 3.改 语法: # 针对性条件修改 update table_name3set c olnum_name1=xxx,colnum_name2=xxx,colnum_name3=xxx where colnum_name=xxx; # 全局性修改: update table_name set colnum_name=xxxxx; 4.查 语法: 1. where查询 select distinct colnum_name1,colnum_name2 from test_01 where colnum_name (!=、=、>、<、between on、in) 2. 模糊查询 # % 指0-多个字符 _ 指一个字符 select distinct colnum_name1,colnum_name2 from test_01 where name like '谢%' or '谢_' 3. 联表查询 A、B表如下: 表A记录如下: aID aNum 1 a20050111 2 a20050112 3 a20050113 4 a20050114 5 a20050115 表B记录如下: bID bName 1 2006032401 2 2006032402 3 2006032403 4 2006032404 8 2006032408 1. inner join on /join on (内连接:返回两表相连字段中相同的值的记录) select * from A inner join B on A.aID=B.bID 结果: aID aNum bID bName 1 a20050111 1 2006032401 2 a20050112 2 2006032402 3 a20050113 3 2006032403 4 a20050114 4 2006032404 2. left join on (左连接:返回以A表字段的全部内容和右AB表相连字段相匹配的记录,如果没有匹配的值,以NULL填充) select * from A left join B on A.aID=B.bID 结果: aID aNum bID bName 1 a20050111 1 2006032401 2 a20050112 2 2006032402 3 a20050113 3 2006032403 4 a20050114 4 2006032404 5 a20050115 NULL NULL 3. right join on (右链接:和left join相反) select * from A right join B on A.aID=B.bID 结果: aID aNum bID bName 1 a20050111 1 2006032401 2 a20050112 2 2006032402 3 a20050113 3 2006032403 4 a20050114 4 2006032404 NULL NULL 8 2006032408 4. 分页和排序、分组、 * 分页:limit(x,y) x指从记录的哪个下标索引开始选取,y:以x下标位置取y条记录 * 排序:order by * 分组:group by * 增加条件:having *** 执行顺序为:where > group by > having > order by > limit 例: SELECT user_type,is_admin ,SUM(id) FROM saut_m_user WHERE id > 100 GROUP BY user_type,is_admin HAVING SUM(status) < 500 ORDER BY is_admin ASC limit 3,5; # 查询语句基本是先用where进行条件筛选————>得到初步数据,再进行group by数据分组——>然后在分好组得数据中在使用having进行过滤——>达到最终结果 必须: having 必须在group by 后面进行聚合筛选,但group by后面不一定得使用 having, 可以是order by 排序! 举例说明: 已知:score成绩表有以下字段,s_id是学生id,c_id是课程id! s_id c_id socre 01 02 90 02 03 80 03 04 60 04 01 50 05 05 70 ......................... 求:1. 查询出至少两门课程大于70分的学生学号 思路:1. 第一感觉这是一个需要分组后在条件过滤来解决的,或许要用 having 2. 两门课程>70是一个条件,那这两门课程如何表现呢,可以使用 sum()>=2,当sum()函数里面放的是表达式时,它会去检索符合条件记录的个数,然后累计相 加,所以sum(socre>70)>=2就可以解决! 解决: select s_id from score_table group by s_id having sum(socre>70)>=2; 2. 查询仅学过01和03课程的学生编号 思路:1. 一看题目就是要分组筛选在筛选,所以少不了group by 和having 2. 只学过01和03课程,说明count(c_id)=2,那用什么来表示学的就是01和03课程呢? 用sum(c_id='01')=1 and sum(c_id='03')=1表示我group by后的信息组中还有'01'和'03'的课程, 那count(c_id)=2 and sum(c_id='01')=1 and sum(c_id='03')=1 就能证明学过的两名课程就是01和03! 解决: select s_id from score_table group by s_id having count(c_id)=2 and sum(c_id='01')=1 and sum(c_id='03')=1; 5. 子查询和镶嵌查询 # 子查询语法:select s1 from t1 where s1 [>、>=、=、<、<= [any/all/in/exists/some/union] (select s2 from t2); # 子查询的几个关键字含义介绍: 1.any: # any含义等同于or也就是或与非的或 select s1 from t1 where s1 > all (select s2 from t2); 注释: 1. 假如表t1表中s1=[1,5,10], t2表中s2=[2,7,11],那么表达式成立的数据有哪些? 10、5! 2. 如果s2=[null,null,null]都为空,那返回的是unknown 返回结果: t1 s1 5 10 2.all: # all含义等同于and也就是或与非的与 select s1 from t1 where s1 > any (select s1 from t2); 注释: 1. 假如表t1表中s1=[1,5,10], t2表中s2=[2,7,11],那么表达式成立的数据有哪些? 没有返回值! 2. 当整个查询表达式为True时,s2=[0,null,-1],只有有一个null就返回的是unknown 3.in: # in就是如果子查询里返回的数据集里面有包含where 后面的条件查询字段的值的话,就以匹配到的数值作条件参数查询最终结果 语法:select * from table_name where name in (’张山‘,'隔壁老王') not in 就反之 4.exists: 语法: select *from table_name where exists (select null) 理解: 1. exists内查询如过能查询>=1个记录,那么外查询就能返回整个数据! 2. 如果内查询没有查出数据,则不返回数据! 5.some: # some跟any用法相近,不再赘诉! 6.union: # union我个人理解为类似左右表连接,即返回以左边select查询字段和对应值+左右select查询字段值相匹配的值! 例: select a_id from A UNION select b_id from B; 6. 聚合查询和分组过滤 # 常用的聚合查询函数有:sum()、avg()、max()、min()、count() 用法很简单,自查即可!上面也有提到的案例题!
-
常用函数
聚合函数 字符串函数 数值函数 日期函数 avg(): 求平均值 合并字符串函数:concat(str1,str2,str3…) 绝对值函数:abs(x) 获取当前日期:curdate(),current_date() count(): 求某字段的值在表中出现的次数 比较字符串大小函数:strcmp(str1,str2) 向上取整函数:ceil(x) 获取当前时间:curtime(),current_time() max(): 求最大值 获取字符串字节数函数:length(str) 向下取整函数:floor(x) 获取当前日期时间:now() min(): 求最小值 获取字符串字符数函数:char_length(str) 取模函数:mod(x,y) 从日期中选择出月份数:month(date),monthname(date) sum(): 求累计和/可添加表达式 字母大小写转换函数:大写:upper(x),ucase(x);小写lower(x),lcase(x) 随机数函数:rand() 从日期中选择出周数:week(date) 字符串查找函数:find_in_set 四舍五入函数:round(x,y) 从日期中选择出周数:year(date) 获取指定位置的子串:substring_index(str,delim,count) 数值截取函数:truncate(x,y) 从时间中选择出小时数:hour(time) 字符串去空函数:replace(object,search,replace) 从时间中选择出分钟数:minute(time) 字符串替换函数:replace() 从时间中选择出今天是周几:weekday(date),dayname(date)