MySQL数据库入门教程超详细

墨蓝 2021-11-02 04:46 453阅读 0赞

##### [点击查看MySQL][MySQL][优化][MySQL][文章][MySQL] #####

##### 一、写在前面 #####

黑窗口启动mysql服务： net start mysql  
登录：mysql -u root -p  
备份数据库: mysqldump -uroot -p1234 store28 > g:/1.sql  
建议使用Navicat工具操作MySQL

##### 二、SQL语句的分类 #####

DDL： 数据定义语言  
create、drop、alter  
DML：数据操作语句  
insert、delete、update、truncate  
DQL： 数据查询语言：  
select、show

##### 三、操作数据库 #####

①查询所有数据库  
show databases;  
   查看活跃连接  
show processlist;  
②创建数据库  
create database blog default character set utf8;  
③查看数据库的默认字符集  
show create database blog;  
④删除数据库  
drop database blog;  
⑤修改数据库  
alter database blog default character set gbk;  
⑥选择数据库  
use blog;

##### 四、操作表 #####

①查看所有表  
show tables;  
②创建表  
create table student(sid int,sname varchar(20));  
③查看表结构  
desc student;  
④删除表  
drop table student;  
⑤修改表  
添加字段  
alter table student add sgender varchar(2);  
删除字段  
alter table student drop sgender;  
修改字段类型  
alter table student modify sgender varchar(100);  
修改字段名称  
alter table student change sgender gender varchar(2);  
修改表名称  
alter table student rename to teacher;

##### 五、增删改数据 #####

①增加数据  
insert into student values(1,'张三','男',20);  
insert into student(id,name) values(2,'李四');  
②修改数据  
update student set gender='女';  
update student set gender='男' where id=1;  
update student set gender='男',age=30 where id=2;  
③删除数据  
delete from student;  
delete from student where id=2;  
truncate table student;  
删除对比：  
delete from可以带条件删除；只能删除表的数据，不能删除表的约束 ；可以回滚（事务）  
truncate table不能带条件删除 ；既可以删除表的数据，也可以删除表的约束；不能回滚

##### 六、查询数据（重点） #####

①查询时添加常量列  
select id,name,gender,age,'java学习' as 'java'  from student;  
 查询时合并列（合并列只能合并数值类型的字段）  
select id,name,(servlet+jsp) as '总成绩' from student;  
 查询时去除重复记录  
select distinct gender from student;  
select distinct(gender) from student;  
②条件查询 <>(不等于)  
select \* from student where jsp between 75 and 90;  
select \* from student where gender<>'男';  
 判空条件(null 空字符串)  
select \* from student where address is null or address='';   
select \* from student where address is not null and address<>'';  
 模糊条件： like  
% 表示任意个字符  
\_ 表示一个字符  
③聚合查询  
常用的聚合函数：   
sum()  
avg()   
max()  
min()   
count()  
注意：count（）函数统计的数量不包含null的数据  
④分页查询（开始行,行数）0开始  
select \* from student limit (当前页-1)\*每页显示条数,每页显示条数;  
⑤查询排序  
select \* from student order by servlet asc,jsp desc;  
⑥分组查询  
select gender,count(\*) from student group by gender;  
⑦分组查询后筛选（分组之前条件使用where关键字，分组之后条件使用having关键字）  
select gender,count(\*) from student group by gender having count(\*)>2;

##### 七、六种约束类型 #####

① 默认值         default  
② 非空 not null  
③ 唯一 unique  
④ 主键 primary key 非空且唯一  
⑤ 自增长         AUTO\_INCREMENT  
\--自增长，从0开始  zerofill 零填充  
\--删除：delete不能影响自增长约束，truncate可以影响自增长约束  
例：id int(4) zerofill primary key auto\_increment,   
⑥ 外键  
例：constraint emlyee\_dept\_fk foreign key(deptid) references dept(id)  
注意：  
1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上。  
2）主表的参考字段通用为主键。  
3）添加数据： 先添加主表，再添加副表。  
级联操作：  
级联修改： on update cascade  
级联删除： on delete cascade  
constraint emlyee\_dept\_fk foreign key(deptid) references dept(id) on update cascade on delete cascade

##### 八、数据库(表)设计 #####

①设计原则：设计的表尽量遵守三大范式。  
②三大范式：  
第一范式:确保每列的原子性.  
第二范式:在第一范式的基础上,确保表中的每列都和主键相关.  
第三范式:在第二范式的基础上,确保每列都和主键列直接相关,而不是间接相关.（非主键字段不能相互依赖）

##### 九、关联查询(多表查询) #####

多表查询规则：  
a.确定查询哪些表    
b.确定哪些哪些字段    
c.表与表之间连接条件 (规律：连接条件数量是表数量-1)  
① **内连接查询**  
select e.empname,d.deptname from employee e inner join dept d on e.deptid=d.id;  
② **左\[外\]连接查询**：使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null  
select d.deptname,e.empname from dept d left join employee e on d.id=e.deptid;  
③ **右\[外\]连接查询**：使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null  
select d.deptname,e.empname from employee e right join dept d on d.id=e.deptid;  
④ **自连接查询:**  
select e.empname,b.empname from employee e left outer join employee b on e.bossid=b.id;

##### 十、UNION和UNION ALL的作用和语法 #####

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。  
UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。  
同时，每条 SELECT 语句中的列的顺序必须相同.  
示例：  
SELECT column\_name FROM table1  
UNION  
SELECT column\_name FROM table2  
注意：  
默认地，UNION 操作符选取不同的值。如果允许重复的值，使用 UNION ALL。UNION ALL不消除重复行  
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。  
UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名

##### 十一、存储过程 #####

①描述：带有逻辑的sql语句。  
②优缺点：  
执行效率非常快，存储过程是在数据库的服务器端执行的。  
移植性很差，不同数据库的存储过程是不能移植。  
③语法：  
\--全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）  
\-- 查看所有全局变量： show variables  
\-- 查看某个全局变量： select @@变量名  
\-- 修改全局变量： set 变量名=新值  
\-- character\_set\_client: mysql服务器的接收数据的编码  
\-- character\_set\_results：mysql服务器输出数据的编码  
\--会话变量：只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失  
\-- 定义会话变量: set @变量=值  
\-- 查看会话变量： select @变量  
\--局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失  
④示例：  
delimiter $  
create procedure pro\_test(out result VARCHAR(20))  
begin   
 DECLARE score int default 0;  
 SELECT avg(english) into score from student;  
 if score>90 then  
set result = '优秀';  
 elseif score>70 and score <=90 then  
set result = '良好';  
 else  
set result = '一般';  
 end if;

end $

CALL pro\_test(@result);

select @result;  
drop procedure pro\_test;

##### 十二、触发器 #####

①描述：当操作了某张表时，希望同时触发一些动作行为，使用触发器完成。  
②示例：  
create trigger tri\_empadd after insert on employee for each row    -- 当往员工表插入一条记录时  
 insert into test\_log(content) values('员工表插入了一条记录');

##### 十三、MySQL权限问题 #####

\--  mysql数据库，用户配置 : user表  
use mysql;  
\-- 查看密码  
select password('root');  
\-- 修改密码  
update user set password=password('123456') where user='root';  
\-- 分配权限账户  
grant 权限 on 数据库.表 to '账户名'@'账户类型' identified by '密码';  
权限： select insert delete update drop create/  或,all  
举例：grant select on day16.employee to 'eric'@'localhost' identified by '123456';  
\-- 备份（无需登陆）  
mysqldump -u root -p day17 > c:/bak.sql  
\-- 恢复（无需登陆）  
mysql -u root -p day17 < 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()   返回当前的时间  
MD5(str) 返回字符串str的MD5加密值  
length(str)     返回字符串str中的字符数  
concat(s1,s2...,sn) 将s1,s2...,sn连接成字符串  
concat\_ws(sep,s1,s2...,sn)          将s1,s2...,sn连接成字符串，并用sep字符间隔  
reverse(str)             返回颠倒字符串str的结果  
trim(str)     去除字符串首部和尾部的所有空格  
ltrim(str)     去掉字符串str开头的空格  
rtrim(str)     去掉字符串str尾部的空格

[MySQL]: https://blog.csdn.net/zhou870498/article/details/79873977