注意：如果查询的结果有中文乱码问题解决办法
- 在cmd上登录mysql数据库(-u 账号 -p 回车 输入密码)
- show variables(查看全局变量)
- character_set_client (接收数据的编码), character_set_results(输出数据的编码)
- 修改全局变量的值（set character_set_client = utf8/gbk）保证接收数据的编码格式和输出数据的编码格式一致
一 数据库管理
1.1 查询所有的数据库
show databases;1.2 创建数据库
create database name default character set utf8;1.3 查看数据库默认字符集
show create database name;1.4 删除数据库
drop database name1.5 修改数据库
alter database name default character set utf8 //修改字符集二 表管理
2.1 选择数据库
use databasename2.3 查看表
show tables2.4 创建表
create table tablename (id int,name varchar(20),age int);2.5 删除表
drop table tablename2.6 修改表
//添加字段 column可省略不写
alter table tablename add column gender varchar(5);
//删除字段(gender) column可省略不写
alter table tablename drop column gender;
//修改表的字段(remark)类型
alter table tablename modify column remark varchar(100);
//修改字段名 gender旧的字段名 sgender新的字段名
alter table tablename change gender sgender varchar(5);
//修改表的名称 to 可省略不写
alter table tablename rename to newtablename;三 操作数据(CRUD)
CREATE TABLE student (id INT,name VARCHAR(20),gender VARCHAR(5),age INT)//创建一张表先3.1 增加数据
//插入所有字段，依次按顺序插入,字段值不能少也不能多
insert into student values(1,'张三','男',20)
//插入部分字段
insert into student (id,name)values(2,'李四')3.2 修改数据
//修改所有的gender字段的值为女
update student set gender='女'
//带条件修改字段
update student set gender='男' where id = 1 //修改id为1的性别为男
//修改多个字段 注意: set 字段名=值，字段名=值,....
update student set gender='男',age = 30 where id = 13.3 删除数据
//删除所有的数据
delete from student
//带条件的删除
delete from student where id = 23.4 查询数据
1 查询所有列
select * from student2 查询指定列
//查询多列 字段名之间以逗号隔开
select id,name from student3 查询时添加常量列
//'一年级101班'字符串就是常量列
select id,name,gender,age,'一年级101班' from student4 查询时合并列
需求：查询每个学生的servlet和jsp总成绩
// 注意：合并列只能合并数值类型的字段
select id,name,(servlet+jsp) as "总成绩" from student5 查询时去除重复记录
需求：查询学生的性别（只有男和女）
select distinct gender from student6 条件查询
1)逻辑条件：and(与) or(或)
select * from student where id=2 and name='李四'//查询id为2且姓名为李四的学生
2)比较条件：> < >= <= = <>(不等于) between and(最小值>=xx 且 xx<=最大值)
select * from student where servlet>70//查询servlet分数大于70的学生
3) 判空条件(null): is null is not null ='' <>''
select * from student where address is null or address =''//查询address字段没有值的数据
4) 模糊条件(like)
//通常使用的替换标记：%--表示替换任意字符 _--表示一个字符
select * from student where name like '张%'//查询name的值以张开头的数据
select * from student where name like '李_'//查询name的值以李开头并且只有两个字的数据7 聚合查询
常用的聚合函数：sum() avg() max() min() count()
select sum(servlet) from student //查询所有学生的servlet的总成绩
select avg(servlet) from student //查询学生的servlet的平均成绩
select max(servlet) as '最高分' from student //查询学生的servlet的最高分
select count(*) from student //统计表中有多少学生8 分页查询(limit)
语法： limit 起始行,查询几行
select * from student limit 0,2 //查询第1和第2条数据9 查询排序(order by)
语法：order by 字段 asc/desc
asc：正序
desc：倒序
select * from student order by id asc //按照id顺序排序
select * from student order by servlet asc,jsp desc//按照servlet正序，按照jsp倒序10 分组查询
语法：group by
select gender,count(*) from group by gender;//查询每个性别有多少人11 分组查询后筛选
select gender,count(*) from student group by gender having count(*)>2//查询总人数大于2的性别四 表的约束
作用：对表中的字段进行约束
4.1 默认值
关键字: default
//设置了默认值的字段没有插入值的时候，mysql自动给该字段分配默认值
create table student (id int,name varchar(20),address varchar(20) default '深圳南山') //设置address的默认值为'深圳南山'4.2 非空
关键字：not null
create table student (id int,name varchar(20),gender varchar(2) not null)//gender 字段非空4.3 唯一
关键字：unique
create table student (id int unique,name varchar(20));//id字段唯一4.4 主键
关键字：primary key
//主键：非空+唯一
create table student(id int primary key,name varchar(20)) //id字段是主键4.5 自增长
关键字：auto_increment
create table student(id int(4) zerofill primary key auto_increment)//id字段是主键并且自增长
//注释: int(4)：表示id的位数一定是4个 zerofill：表示位数为空的以0补充，如:00024.6 外键
作用：约束两张表的数据
给表添加外键的语句一
//表一
create table dept (id int primary key,deptName varchar(20))
//表二
create table employee(id int primary key,empName varchar(20),deptId int,constraint emlyee_dept_fk foreign key(deptId) references dept(id))//表二中的deptId的值要参考表一种的id值
//注释：emlyee_dept_fk 外键名称 deptId:外键 dept:参考表 id:参考字段给表添加外键的语句二
alter table dept add constranint emlyee_dept_fk foreign key(deptId) references dept(id);注意：
1 被约束的表称为副表，约束别人的表示主表，外键设置在副表上
2 主表的参考字段通用为主键
3 添加数据：先添加主表，再添加副表
4 修改数据：先修改副表，再修改主表
5 删除数据：先删除副表，在删除主表
级联操作:
作用：修改或删除主表数据，会影响副表数据
级联修改：ON UPDATE CASCADE
级联删除：ON DELETE CASCADE
create table employee(id int primary key,empName varchar(20),deptId int,constratint emlyee_dept_fk foreign key(deptId) references dept(id) on update cascade)五 数据库设计
三大范式(主要是依据业务逻辑)
第一范式：表的每个字段必须是不可分割的独立单元
第二范式：在第一范式的基础上，要求每张表只表达一个意思，表的每个字段都和表的主键有依赖。
员工表：员工编号(主键) 员工姓名 部门名称 订单名称
//订单名称在业务上和员工是没有业务逻辑关系的，不能放在一个表里面，因此违反了第二范式第三范式：在第二范式的基础上，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。
员工表：员工编号(主键) 员工姓名 部门编号 部门名称
//业务上通过部门编号可以找到部门名称，业务上部门名称和主键没有依赖关系。因此违反了第三范式六 关联查询(多表查询)
多表查询规则：
- 确定查询那些表
- 确定那些字段
- 表与表之间连接条件(规律：连接条件数量=表数量-1)
create table employee(id int primary key,empName varchar(20),deptId int,constratint emlyee_dept_fk foreign key(deptId) references dept(id));//创建员工表
create table dept(id primary key,deptName varchar(20));//创建部门名称表6.1 内连接查询
需求：查询员工及其所在部门
第一种语法：
//员工表：employee 部门表：dept 员工名：empName 部门名:deptName
select empName,deptName from employee,dept where employee.deptId = dept.id；
第二种语法
select empName,deptName from employee inner join dept on employee.deptId = dept.id;6.2 左(外)连接查询：使用左边表的数据去匹配右边表的数据，符合连接条件的结果显示，不符合条件显示null。
注意：左表的数据一定会完全显示
//dept d:给表dept取别名d employee e：给表employee取别名e outer可以省略
//dept 为左表
select d.deptName,e.empName from dept d left outer join employee e on d.id = e.deptId6.3 自连接查询
alter table employee add bossId int;//添加上司id
需求：查询每个员工的上司
//虚拟出了一张boss表，然后结合左外连接
select e.empName,b.empName from employee e left outer join employee b on e.bossId = b.id;七 存储过程
存储过程就是带上逻辑(if while)的sql语句
优点：执行效率高
缺点：移植性差
7.1 没有参数的存储过程
delimiter $ ----声明结束标记符
create procedure pro_test()
begin
//可以写多个sql语句
select * from employee
end $
call pro_test(); ---执行存储过程7.2 带有存储输入参数的存储过程
delimiter $ ----声明结束标记符
create procedure pro_findById(in eid int) ---in:输入参数
begin
select * from employee where id=eid;
end $
call pro_findById(1); ---执行存储过程7.3 带有输出参数的存储过程
delimiter $ ----声明结束标记符
create procedure pro_testOut(out str varchar(20)) ---out:输出参数
begin
//set 给参数赋值关键字
set str = '输出参数'
end $
call pro_testOut(@name); ---执行存储过程
select @name --- 查看变量值7.4 存储过程中if语句使用
DELIMITER $
CREATE PROCEDURE pro_testif(IN num INT,OUT str varchar(20))
BEGIN
if 条件 then
set str = ‘星期一’
elseif 条件 then
set str = ‘星期二’
else
set str = ‘输入错误’
end if
END $
CALL pro_testif(4,@str);
select @str;7.5 存储过程中do while 语句使用
DELIMITER $
CREATE PROCEDURE pro_testwhile(IN num INT,OUT result INT)
BEGIN
—定义一个局部变量—
declare i int default 1;
declare vsum int default 0;
while i<=num do
set vsum = vsum +i;
i++;
end while;
set result = vsum;
END $
CALL pro_testwhile(100,@result);
select @ result;7.6 存储过程中使用查询结果作为返回值
DELIMITER $
CREATE PROCEDURE pro_findbyid(in eid int ,out vname varchar(20))
BEGIN
SELECT empName into vname FROM emplTable where eid = id;
END $
CALL pro_findbyid(1,@name);
select @name八 触发器
需求：当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据
//创建日志表
create table test_log(id int primary key auto_increment,content varchar(100));
//创建添加触发器 向employee表添加数据会触发
create trigger tri_empAdd after insert on employee for each row insert into test_log(conten) values("员工表插入了一条数据")
// 创建修改的触发器 employee表数据被修改会触发
create trigger tri_empUdp after update on employee for each row insert into test_log(conten) values("员工表修改了一条数据")
// 创建删除的触发器 employee表数据被删除会触发
create trigger tri_empDel after delete on employee for each row insert into test_log(conten) values("员工表删除了一条数据")九 mysql权限和备份
9.1 修改mysql的用户密码
1) use mysql
2) select * from user
3) update table user set password = password(‘123456’) where user='账户名'；9.2 分配权限用户
权限用户只有部分的权限
//localhost 表示只能是在本机登录 % 表示任意ip都可以登录
crant select on 数据库名.表名 to 账户名 @'localhost' identified by 密码；//只有查看权限
crant delete on 数据库名.表名 to 账户名 @'localhost' identified by 密码；//分配删除的权限9.3 备份和恢复
备份：在cmd输入以下命令
mysqldump -u 账户名 -p 要备份的数据库名 > 备份的路径（c:/save.sql）恢复：在cmd输入以下命令
mysql -u 账户名 -p 要恢复的数据库名 < 备份的路径（c:/save.sql）
扫一扫
2353
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
