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尾部的空格