MySQL的使用
一、MySQL的安装
- Windows下的安装:
- 官网下载MySQL的压缩包:https://dev.mysql.com/downloads/mysql/
- 将MySQL安装包解压到本地(D:\Mysql57)
- 在mysql的解压目录下(D:\Mysql57\mysql-5.7.24-winx64)新建my.ini配置文件,并填写以下内容:
[client]
port=3306
default-character-set=utf8
[mysqld]
#设置为自己MYSQL的安装目录
basedir=E:\mysql\mysql-5.7.24-winx64
#设置为MYSQL的数据目录
datadir=E:\mysql\mysql-5.7.24-winx64\data
port=3306
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
#开启查询缓存
explicit_defaults_for_timestamp=true
#skip-grant-tables - MySQL初始化:
在安装目录的bin目录下(D:\Mysql57\mysql-5.7.24-winx64\bin),打开命令窗口。也可配置环境变量(在path下添加bin的路径)。以管理员身份打开命令窗口输入mysqld --initialize进行初始化。 - 安装mysql服务:
在管理员命令窗口输入mysqld -install - 启动mysql服务:
管理员命令窗口输入net start mysql - 登录mysql:
在命令窗口输入mysql –u root –p,系统初始密码在data目录下的 主机名.err文件中(D:\Mysql57\mysql-5.7.24-winx64\data\ DESKTOP-GOS079K.err)的
[Note] A temporary password is generated for root@localhost: )>YczV9G*To1 - 修改密码:
Alter user ‘root’@’localhost’ identified by ‘自定义密码’;
set password for root@localhost = password(‘123’);
Flush privileges;更新修改配置注:若报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements。是因为Mysql更新后权限设置严格,密码不能设置过于简单,但可以跳过这个权限,然后修改密码。在命令窗口输入:
set global validate_password_policity=0(mysql5.7)
set global validate_password.policity=0(mysql5.8)
- Linux下安装(Centos7):
Linux有自带的mariaDB数据库,用法类似mysql。若Java WEB开发用的是mysql,部署到服务器上要改为mysql。
- 下载mysql-server:
链接:https://pan.baidu.com/s/1URw55keXCTmF8RUE4cmDjQ
提取码:8s07 - rpm –ivh mysql80-community-release-el7-1.noarch.rpm安装mysql-server
- 用yum –install mysql mysql-devel
- 启动/关闭mysql服务:
systemctl start mysqld (Centos7)/service mysqld start (centos7以下)
systemctl stop mysqld (Centos7)/service mysqld stop (centos7以下) - 登录mysql:
Mysql -u root –p,密码在/var/log/mysqld.log中
2018-11-29T10:18:16.514176Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: lF10banuqo;F
登录后修改密码同Windows;
二、mysql的基本操作
- 创建/删除数据库:Create/drop database Test;
- 选择数据库:use Test;
- 删除/创建表:drop table student;
create table student(
sid bigint not null primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sage int,
sdept bigint,
sclass bigint
);
create table course(
cid bigint not null primary key,
cname varchar(20) not null,
chours int,
ccredit int
);
create table score(
sid bigint,
cid bigint,
score float,
//级联删除
foreign key (sid) references student(sid) on delete cascade, foreign key (cid) references course(cid)
); - 添加数据:
insert into student values
(1120120001,‘张一’,‘男’,19,03,03011201),
(1120120002,‘张二’,‘男’,21,03,03011201);
insert into student(sid,sname,ssex) values(1120120003,‘张三’,'男’); - 更该数据
update student set sname=’王五’ where sid=’1120120001’; - 删除数据
Delete from student where sid=’1120120001’; - 查询数据库:(从附加的Test中操作)
a.查询所有学生的所有信息
Select *from student;
b.降序查询所有学生的学号、姓名
Select sid,sname from student order by sid desc;升序是asc
c.除重复查询:
Select distinct sid from score where score=80;
d.取别名查询:
/按号课程成绩由高到低显示所有学生的学号、姓名、成绩/
select st.sid,st.sname,sc.score
from student st,score sc
where st.sid=sc.sid and sc.cid=‘0001’
order by sc.score desc;
/按“高等数学”成绩降序序显示所有学生姓名、学号、分数、课程名/
select st.sname,st.sid,sc.score,c.cname
from student st,score sc,course c
where st.sid=sc.sid and sc.cid=c.cid and c.cname=‘高等数学’
order by sc.score desc;
/查询有薪水差额的任意两位教师/
select t1.tid as ‘教工号’,t1.tname as ‘教师姓名’,t2.tid as ‘教工号’,t2.tname as ‘教师姓名’
from teacher t1,teacher t2
where t1.tsalary > t2.tsalary;
/查询既学过“”又学过“”号课程的所有学生学号/
select sc1.sid
from score sc1,score sc2
where sc1.sid=sc2.sid and sc1.cid=‘0001’ and sc2.cid=‘0002’;
/查询“”号课程比“”号课程成绩低的所有学生学号姓名/
select st.sname,sc1.cid,sc1.score,sc2.cid,sc2.score
from student st,score sc1,score sc2
where st.sid=sc1.sid and sc1.sid=sc2.sid
and sc1.cid=‘0001’ and sc2.cid=‘0002’
and sc1.score<sc2.score;
e.聚合函数查询:
/查询学生的学号、姓名、所在的系和总成绩/
select st.sid,st.sname,st.sdept,SUM(sc.score)
from student st,score sc
where st.sid=sc.sid
group by st.sid,st.sname,st.sdept;
注:当查询字段包含聚合函数时必须使用group by对其他字段进行分组查询
/查询平均成绩大于80的学生的学号和平均成绩/
select sid,AVG(score) from score group by sid having AVG(score)>80;
注:group by不能对聚合函数分组,要结合having使用
三、数据库的其他操作
- 创建触发器:
create trigger trg_student_insert
on student
for insert
as
declare @sid bigint,@sname varchar(20),@ssex varchar(10);
select @sid=sid,@sname=sname,@ssex=ssex from inserted;
set @sid=@sid+10
insert into student(sid,sname,ssex) values(@sid,@sname,@ssex);
print ‘添加学生成功’;
go
注:inserted和deleted是两个临时表,在用户插入或删除数据时,临时表有该记录。 - 修改数据库表的结构
a.增加、删除:
alter table user drop money/add money float
b.修改字段类型:
alter table user modify money float
alter table user change money salary float
c.修改默认字段:
alter table user alter money set default 0
d.修改表名:
Alter table user rename to student
e.查看表结构
desc user; - 导出数据库:
未登录数据库是输入mysqldump –u root –p Test> D://Test.sql
注:导出数据库语句末尾不能加分号(;) - 导入数据库:
登录数据库->use Test;->source D://Test.sql;