MySQL数据库入门知识点

一、写在前面黑窗口启动mysql服务：netstartmysql

登录：mysql-uroot-p

备份数据库:mysqldump-uroot-p1234store28>g:/1.sql

建议使用Navicat工具操作MySQL二、SQL语句的分类DDL：数据定义语言

create、drop、alter

DML：数据操作语句

insert、delete、update、truncate

DQL：数据查询语言：

select、show三、操作数据库①查询所有数据库

showdatabases;

查看活跃连接

showprocesslist;

②创建数据库

createdatabaseblogdefaultcharactersetutf8;

③查看数据库的默认字符集

showcreatedatabaseblog;

④删除数据库

dropdatabaseblog;

⑤修改数据库

alterdatabaseblogdefaultcharactersetgbk;

⑥选择数据库

useblog;四、操作表①查看所有表

showtables;

②创建表

createtablestudent(sidint,snamevarchar(20));

③查看表结构

descstudent;

④删除表

droptablestudent;

⑤修改表

添加字段

altertablestudentaddsgendervarchar(2);

删除字段

altertablestudentdropsgender;

修改字段类型

altertablestudentmodifysgendervarchar(100);

修改字段名称

altertablestudentchangesgendergendervarchar(2);

修改表名称

altertablestudentrenametoteacher;五、增删改数据①增加数据

insertintostudentvalues(1,'张三','男',20);

insertintostudent(id,name)values(2,'李四');

②修改数据

updatestudentsetgender='女';

updatestudentsetgender='男'whereid=1;

updatestudentsetgender='男',age=30whereid=2;

③删除数据

deletefromstudent;

deletefromstudentwhereid=2;

truncatetablestudent;

删除对比：

deletefrom可以带条件删除；只能删除表的数据，不能删除表的约束；可以回滚（事务）

truncatetable不能带条件删除；既可以删除表的数据，也可以删除表的约束；不能回滚六、查询数据（重点）①查询时添加常量列

selectid,name,gender,age,'java学习'as'java'

fromstudent;

查询时合并列（合并列只能合并数值类型的字段）

selectid,name,(servlet+jsp)as'总成绩'fromstudent;

查询时去除重复记录

selectdistinctgenderfromstudent;

selectdistinct(gender)fromstudent;

②条件查询<>(不等于)

select*fromstudentwherejspbetween75and90;

select*fromstudentwheregender<>'男';

判空条件(null空字符串)

select*fromstudentwhereaddressisnulloraddress='';

select*fromstudentwhereaddressisnotnullandaddress<>'';

模糊条件：like

%表示任意个字符

_表示一个字符

③聚合查询

常用的聚合函数：

sum()

avg()

max()

min()

count()

注意：count（）函数统计的数量不包含null的数据

④分页查询（开始行,行数）0开始

select*fromstudentlimit(当前页-1)*每页显示条数,每页显示条数;

⑤查询排序

select*fromstudentorderbyservletasc,jspdesc;

⑥分组查询

selectgender,count(*)fromstudentgroupbygender;

⑦分组查询后筛选（分组之前条件使用where关键字，分组之后条件使用having关键字）

selectgender,count(*)fromstudentgroupbygenderhavingcount(*)>2;七、六种约束类型①默认值

default

②非空notnull

③唯一unique

④主键primarykey非空且唯一

⑤自增长

AUTO_INCREMENT

--自增长，从0开始

zerofill零填充

--删除：delete不能影响自增长约束，truncate可以影响自增长约束

例：idint(4)zerofillprimarykeyauto_increment,

⑥外键

例：constraintemlyee_dept_fkforeignkey(deptid)referencesdept(id)

注意：

1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上。

2）主表的参考字段通用为主键。

3）添加数据：先添加主表，再添加副表。

级联操作：

级联修改：onupdatecascade

级联删除：ondeletecascade

constraintemlyee_dept_fkforeignkey(deptid)referencesdept(id)onupdatecascadeondeletecascade八、数据库(表)设计①设计原则：设计的表尽量遵守三大范式。

②三大范式：

第一范式:确保每列的原子性.

第二范式:在第一范式的基础上,确保表中的每列都和主键相关.

第三范式:在第二范式的基础上,确保每列都和主键列直接相关,而不是间接相关.（非主键字段不能相互依赖）九、关联查询(多表查询)多表查询规则：

a.确定查询哪些表

b.确定哪些哪些字段

c.表与表之间连接条件(规律：连接条件数量是表数量-1)

①

内连接查询

selecte.empname,d.deptnamefromemployeeeinnerjoindeptdone.deptid=d.id;

②

左[外]连接查询：使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

selectd.deptname,e.empnamefromdeptdleftjoinemployeeeond.id=e.deptid;

③

右[外]连接查询：使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

selectd.deptname,e.empnamefromemployeeerightjoindeptdond.id=e.deptid;

④

自连接查询:

selecte.empname,b.empnamefromemployeeeleftouterjoinemployeebone.bossid=b.id;十、UNION和UNIONALL的作用和语法UNION用于合并两个或多个SELECT语句的结果集，并消去表中任何重复行。

UNION内部的SELECT语句必须拥有相同数量的列，列也必须拥有相似的数据类型。

同时，每条SELECT语句中的列的顺序必须相同.

示例：

SELECTcolumn_nameFROMtable1

UNION

SELECTcolumn_nameFROMtable2

注意：

默认地，UNION操作符选取不同的值。如果允许重复的值，使用UNIONALL。UNIONALL不消除重复行

另外，UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名。

UNION结果集中的列名总是等于第一个SELECT语句中的列名十一、存储过程①描述：带有逻辑的sql语句。

②优缺点：

执行效率非常快，存储过程是在数据库的服务器端执行的。

移植性很差，不同数据库的存储过程是不能移植。

③语法：

--全局变量（内置变量）：mysql数据库内置的变量（所有连接都起作用）

--查看所有全局变量：showvariables

--查看某个全局变量：select@@变量名

--修改全局变量：set变量名=新值

--character_set_client:mysql服务器的接收数据的编码

--character_set_results：mysql服务器输出数据的编码

--会话变量：只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失

--定义会话变量:set@变量=值

--查看会话变量：select@变量

--局部变量：在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失

④示例：

delimiter$

createprocedurepro_test(outresultVARCHAR(20))

begin

DECLAREscoreintdefault0;

SELECTavg(english)intoscorefromstudent;

ifscore>90then

setresult='优秀';

elseifscore>70andscore<=90then

setresult='良好';

else

setresult='一般';

endif;end$CALLpro_test(@result);select@result;

dropprocedurepro_test;十二、触发器①描述：当操作了某张表时，希望同时触发一些动作行为，使用触发器完成。

②示例：

createtriggertri_empaddafterinsertonemployeeforeachrow

--当往员工表插入一条记录时

insertintotest_log(content)values('员工表插入了一条记录');十三、MySQL权限问题--

mysql数据库，用户配置:user表

usemysql;

--查看密码

selectpassword('root');

--修改密码

updateusersetpassword=password('123456')whereuser='root';

--分配权限账户

grant权限on数据库.表to'账户名'@'账户类型'identifiedby'密码';

权限：selectinsertdeleteupdatedropcreate/

或,all

举例：grantselectonday16.employeeto'eric'@'localhost'identifiedby'123456';

--备份（无需登陆）

mysqldump-uroot-pday17>c:/bak.sql

--恢复（无需登陆）

mysql-uroot-pday17<d:/back.sql十四、MySQL常用函数version()

返回mysql服务器的版本

from_unixtime(ts,fmt)

根据指定的fmt格式，格式化unix时间戳ts

time_format(time,fmt)

依照字符串fmt格式化时间time值

date_format(date,fmt)

依照字符串fmt格式化日期date值

format(x,y)

把x格式化为以逗号隔开的数字序列，y是结果的小数位数

substring（str,pos）字段,开始位置

substring（str,pos,length）

字段,开始位置,长度n

now()返回当前的日期和时间

curdate()

返回当前的日期

curtime()

返回当前