计算机二级mysql数据库

计算机二级mysql数据库

I、基础知识

一）、数据库的创建与使用

1.创建数据库：create database 数据库名字；

2.选择数据库：use 数据库名字；

3.修改数据库：alter database 数据库名字 default character set 字符集名字 default collate 校对规则名字； -----设置默认字符集和默认校对规则

4.删除数据库：drop database  if exists 数据库名字；----if exists判断数据库在不在

5.查看所有数据库： show databases like “模糊查询”；---可以用like 进行模糊查询

 

二）创建和操作表

1.创建表：create table 表1（字段1 数据类型 auto_increment primary key ，字段2 数据类型  not null default 默认值 ，constraint 外键起名字  foreign key （表1外键名） references 表2（表2主键名）on delete restrict on update cascade）engine=innodb；----格式：（字段名  数据类型 可选参数）----temporary：创建临时表----float 浮点型----engine=innodb为选用引擎-----限制策略：restrict、cascade  ------check（属性 in （select....））约束放在创建表括号内最后，前面没有逗号，检查取值范围----constraint命名完整性约束

2.更新表：alter table

    2.1）添加字段字句ADD子句：alter table 数据表名 add column 添加的列名 数据类型 not null 可选参数  after 列名，add column......修改多列；----after 是放到什么名之后

    2.2）同时修改列名和数据类型：alter table 数据表 change column 原列名 新列名 数据类型 null  可选参数，change column....；

    2.3）修改或删除表中放入指定列默认值：alter table 数据表 alter column 列名 set default 默认值；

    2.4）只修改数据表的数据类型：alter table 数据表 modify column 修改的列名 数据类型 first；-----first为放在第一列

    2.5）删除列：alter table 数据表名 drop column 删除的列名；

    2.6）重命名表名：alter table 原表名 rename to 新表名；第二种方式：或者：rename table 原表名 to 新表名；

3.复制表：create table 新表名 like 复制的原表名；---只复制表结构，不复制数据 ；用as复制数据内容 

4.删除表：drop table if exists 表名；

5.查看所有表：show tables；

    5.1）查看表结构：show column from 表名；或者 desc 表名；

6.删除完整性约束：alter table 表名 drop constraint 约束名字； 

 

三）插入、删除、修改数据的方法

1.插入数据：insert into 表名（属性1，属性2，...）values（属性1的值，属性2的值.....），......插入多行直接加逗号隔开；

    1.1）insert into 表名 values （该列的所有值用逗号隔开） 

    1.2） insert into 表名 set 属性1=值，属性2=值，.....；

    1.3）数据合并到表中：insert into 表名 （属性1，属性2....） select 属性1，属性2...... from 复制的表；

    1.4）插入的值跟主键有冲突的：replace into 表名 （属性1，属性2，....） values（值1，值2,.......）；-----原理是做替换

2.删除数据：delete from 表名 where 条件；

    2.1）删除多个表：delete 表1，表2 from 表1，表2，表3 from where ；-----delete后面是删除了那几张表的数据----from后面是跟那些表有关系

3.修改数据：update 表名 set 属性1=值，属性2=值 where 条件；

    3.1）修改多个表：update 表1，表2 set 表1.属性1=值，表二.属性2=值  where 条件；

4.查询数据：select 查询的属性 from 表名 where 属性=属性值 group by 需要分组的字段 having 分组进行筛选 order by 排序 limit 选行----distinct 去除重复

    4.1）替换查询结果集中的数据：select 属性1，属性2 case when 属性2=值 then 值的别名1 else 值的别名2 end as 属性的别名 from 表名； ----when...then可一直叠加判断

    4.2）常用的函数：count：计数   max：最大值  min：最小值 sum：求和 avg：平均值  ---- #为转义字符  escape为声明转义字符

    4.3）like为字符串匹配  regexp为文本匹配 后者不需要用%； 文本匹配可以用管道符，意思为满足任意一个条件即可选出 ； 

    4.4）判断范围：between...and  、in    判定空值： is null 

    4.5）嵌套查询多表：select 属性值1，属性值2  from 表1 where 属性3 in（select 属性3 from 表2 where 属性4=属性值）

    4.6）分组：select 字段 from 表名 group by 需要分组的属性；----题目涉及汇总的题目直接加 with rollup

    4.7）having子句：必须与group by 一起使用 用于过滤分组 可以跟函数

    4.8）order by 子句：排序 asc升序   desc 降序

    4.9）limit 子句：limit （从哪里开始选，返回的多少行）

 

四）索引

1.创建索引：create index 索引的名字 可选using用什么方式 on 表名（列名 asc）；   

    1.1）创建表时创建索引create table 表名（字段 类型 限制，...... ，primary key（字段），index 索引名字（字段） ）；

    1.2）修改表的时候创建索引： alter table 表名 add index 索引名字（列名）；---唯一索引 unique idnex

2.查看索引：show index from 表名 

3.删除索引：drop index 删除的索引名 on 表名

    3.1）alter table 表名 drop  index 索引名

 

五）视图

1.创建视图：create view 视图名字 as select   选的列 from 表   with check option ； ---with check option保证今后对视图的修改满足条件

2.删除视图：drop view 视图名字；

3.修改视图：alter view 视图名 as select.....

4.查看视图:show create view 视图名； 

六）表维护语句

1.更新、修复索引的散列程度：analyze table 表名；

2.检查多个表或者一个表是否有错误：check table 表名；

3.保证数据一致：checksum table 表名；

4.重新利用未使用的空间，整理数据文件的碎片：optimize table 表名； 

5.修复多个或者一个损坏的表： repair table 表名；

 

七）触发器

1.创建触发器：create trigger 触发器的名字 触发器的时间 触发器的事件 on 表名 for each row set @str=‘值的内容’；----时间分：before、after  ---触发事件有update、insert、delete  ----for each row每一行都激活触发器  ----动作主体：包括赋值set、如执行多个语句可以跟begin...end等

2.通过相应的动作触发，用select看结果；如select @str

3.删除触发器：drop trigger 触发器的名字；--触发器不能更新，只能先删除再重建；删除表的时候同时删除触发器

4.insert 触发器：引用名为new的虚拟表

5.delete触发器：可以引用old的虚拟表，old的值只能查看不能更新

6.update触发器：既可以用old虚拟表，也可以用new虚拟表；对表自身的更新用before更新，

 

八）事件

1.查看事件调度器是否打开：select@@event_scheduler；打开： set global event_scheduler=1；

2.创建事件：create event 创建事件的名字 on  schedule 时间调度 starts 开始时间  结束时间 do 动作begin. if.....then.....end if；end$$-----时间调度：每月一次：every 1 month   -----开始时间：starts 后面跟curdate（）+interval 1 month意思是下个月开始    结束时间：ends 直接跟具体时间‘’

3.修改事件：alter event 事件的名字 rename to 新事件名字

    3.1）关闭临时事件：alter event 事件名字 disable；

    3.2）开始临时事件：alter event 事件名字 enable；

    3.3）更改名字：alter event 事件名字 rename to 新的事件名字；、

4.删除事件：drop event 事件名字；

5.具体实例：

 

九）存储过程

1.创建存储过程：create procedure 存储过程名字（in 参数名 数据类型，in 参数名 数据类型）begin....end$$  delimiter---更改结束符 、

2.查看存储过程：show procedure status；

    2.1）查看存储过程具体信息：show create procedure 存储过程名字；

3.存储过程体（局部变量）：declare 变量名字 数据类型 ；只能在begin..end中；在开头声明

    3.1）set语句：set cid=910；

    3.2）select ....into语句：set 检索出来的数据 into 存放到变量里； ---检索出来的只能是一行数据

    3.3）流程控制语句（条件判断语句）：if ...then...else....end if；或者（case when...   then.... else...end case）

    3.4）循环语句：循环的名字 while 钱<100  do 动作 end while；

    3.5）游标：declare  游标名字 cursor for 检索的语句；-----------用于存储过程或存储函数，在begin..end游标名字唯一，----打开游标名：open游标名字；----读取数据：fetch 游标名字 into 变量名字；---关闭游标：close 游标名；

    3.6）continue handler：游标用于控制循环语句，实现游标下移；---declare顺序：局部变量、游标、句柄

4.调用存储过程：call 存储过程（参数）；

5.修改存储过程：alter procedure  存储过程名字

6.删除存储过程： drop procedure 存储过程名字；

 

    十）存储函数

1.创建 存储函数：create function 函数名字（参数只有名称和类型） returns 类型（） 主体用begin...end；---return 返回包含select时，结果只能时一行且有一列值

2.查看数据库中有哪些存储函数：show function status

    2.1）查看具体的存储函数：show create function 存储的名字；

3.调用存储函数：select 存储函数的名字 参数；

4.删除存储函数：drop function 存储函数名；

5.修改存储函数:alter function 存储函数名（）；只能修改存储函数的特征

 

十一）用户账号管理：查看所有用户：select user from mysql.user；

1.创建用户账号：create user ‘用户名’@‘主机名’identified by ‘密码’，‘用户名’@‘主机名’ identified by password‘散列值密码’；

2.删除用户：drop user  ‘用户名’@‘主机名’；

3.修改用户账号： rename user 原来用户名@主机名 to 新用户名@主机名；

4.修改用户口令：set password for 用户名@主机名 =新密码；

 

十二）账户权限管理

1.查看权限：show grants for ‘用户名’@‘主机名’；

2.权限授予：grant 赋予的权限（具体列） on 表、类等对象 to 用户@主机名 identified by‘密码’；----同时可创建用户、密码

3.权限转移：grant 赋予的权限（具体列） on 表、类等对象 to 用户@主机名 identified by‘密码’with grant option ；

4.权限的撤销：revoke 权限名 on 表 from 用户名@主机名；

 

十三）备份与恢复

1.全部导出：select * from 表名 into outfile ‘全路径文件名’ ；---like可以复制没有数据的表

2.导入：load data infile 文件名 into table 表；

3.数据一致性的备份：读锁定：lock tables 表名 read；写锁定：lock tables 表名 write ； 解锁：unlock tables；

4.备份表结构的sql语句及数据：mysqldump -h 主机名 -u 用户名 -p 密码  数据库名字.表名字>全路径文件名；

5.恢复数据库：mysql -h 主机名 -u 用户名 -p 密码 数据库<全路径文件名；   

    5.1）恢复表：source  全路径文件；

 

十四）二进制日志文件的使用

1.在my.ini文件里下的mysqld下面添加：log-bin=文件全路径；   

2.通过二进制恢复：mysqlbinlog log_文件名 | mysql -h 主机名 -u 用户名 -p 密码 

3.清除所有的日志文件：reset master；

    3.1）删除部分日志文件：purge logs to 日志文件

 

十五）PHP的mysql数据库编程

1.连接数据库：<?php

                     $con=mysql_connect(本机名，用户名，密码)；

                      if(!$con)

                      {echo''连接失败<br>''

                       echo''错误编号:‘’.mysql_errno()

                       echo''错误信息：‘’.mysql_error()

                             die（）}

                        echo“连接成功<br>”;

                         ?>

2.数据的删除：mysql_query("set names ‘gbk’")

3.数据的查询：读取结果集：mysql_fetch_array

II、考试大纲

一）、第一大题考试内容

1.新增（插入一行数据）：insert into 表名 （列的所有名字）values （该列的所有值）；

                                1.1）insert into 表名 values （该列的所有值用逗号隔开）

1.2）更改数据（某个字段的值）：update 表名 set 字段名=字段新值 where 属性=属性值；

2.嵌套查询多表：select 属性值1，属性值2  from 表1 where 属性3 in（select 属性3 from 表2 where 属性4=属性值）；求平均--ang（）统计--count（）等 与group by连用

3.更改表结构（默认值）：alter table 表名 alter  column 列名 set  default 默认值；

4.创建视图：create view 视图名 as select 视图需要的列名 from 表名 ；

5.创建数据库用户：create user “用户名字”@“主机名字（默认是localhost）”identified by “创建的密码”； --题中没密码红色可省略

6.删除字段（列）：alter table 表名 drop 字段名；

6.1）新增字段（列）：alter table 表名 add 字段名 类型  default 默认值；

7.授予权限：grant 授予权限的动作（权限的名字，名字2）on 授予权限的表 to “用户名@主机名”with grant  option；-----授予动作包括insert、select等等

8.创建索引：alter table 表名 add  unique index 索引名字（创建索引字段）；---- unique为唯一索引

9.创建表：create table 表1（字段1 数据类型 primary key ，字段2 数据类型  not null auto increment/default 默认值 ，constrain 外键名  foreign key （外键关键字） references 其他表名（其他表关键字））； 

 

 

III、考试高频单词

1.给列赋予默认值：alter column子句、default赋予默认值、

2.创建用户：identified by后面跟密码、可在grant..on..to直接新增、with grant option允许分享给其他人

3.创建表：primary(主键) key、constraint 外键名 foreign key(本表外键关键字) references 其他表（其他表主键）、

4.删除列：直接用alter table 表名  drop 列名

5.创建索引：unique index唯一索引

6.触发器：trigger、for each row、 before针对insert和update操作，其余用after

7.存储过程：procedure、declare声明、 cursor游标、fetch...into：读取数据

8.PHP程序：mysql_connect建立非持久连接、mysql_pconnect()建立持久连接、mysql_select_db(数据库名，连接变量名)连接数据库、    mysql_query()提交执行SQL语句、mysql_fetch_array()读取结果中的记录、mysql_sum_row()读取行数、mysql_data_seek(指针，行数)

9.事件：event、on schedule（独有的单词）、starts、select...into