Mysql数据库
Mysql数据库
1、概念:数据库就是“数据”的“仓库”,数据库中包含表、关系以及操作对象,数据存放在表中。
2、作用:
- 存储大量数据,方便检索和访问
- 保持数据信息的一致、完整
- 共享和安全
- 通过组合分析,产生新的有用信息
3、Mysql基本命令
(1)通过cmd进入mysql管理(CMD)
mysql -uroot -pxxx
(2)登录到远程主机的mysql(CMD)
mysql -h192.168.3.143 -uroot -p123456
(3)显示数据库服务器中所有实例(mysql-command)
show databases;
(4)创建数据库实例(mysql-command)
----语法:create database 数据库名; create database mytestdb; create database 数据库名 charset=编码 create database wzry charset='utf8';
(5)使用数据库实例(mysql-command)
use mytestdb;
(6)退出mysql命令行(mysql-command)
\q[uit] exit
(7)结束当前输入(mysql-command)
\c[lear]
(8)显示服务器状态(mysql-command)
\s
(9)帮助信息(mysql-command)
\h[elp]
(10)显示当前实例下所有的数据库表(mysql-command)
show tables;
(11)备份数据库实例信息(cmd)
mysqldump -uroot -proot mysql > d:\mysql.sql
(12)还原数据库实例(导入脚本到指定实例中:mysql-command)
source d:\mysql.sql
(13)删除数据库实例(mysql-command)
drop database mytestdb;
(14)修改mysql的登陆密码(cmd)
mysqladmin -uroot -proot password 123456
(15)使用mysqladmin执行创建或删除数据库实例(cmd)
--创建数据库实例 mysqladmin -uroot -proot create mytestdb; --删除数据实例 mysqladmin -uroot -proot drop mytestdb;
4、当忘记mysql登录密码时的解决方法
(1)停止mysql服务(以管理员身份运行cmd)
net stop mysql
(2)运行cmd,切换到mysql安装目录的bin目录(也可以在bin目录中按住shift键,鼠标右键→在此处打开命令行窗口),绕过mysql命令行的登陆(当前窗口不要关闭)
mysqld --skip-grant-tables
(3)重新打开命令行窗口,使用mysql命令登录(无需使用密码)
mysql -uroot -p
(4)使用mysql实例(mysql-command)
use mysql
(5)修改root账户的登陆密码
update user set password=password(‘新密码’)where user=‘root’
(6)刷新权限
fiush privilege
(7)任务管理器结束mysqld.exe
(8)重启mysql服务
net start mysql
5、SQL语句入门
SQL(Structured Query Language结构化查询语言),是一门数据库的第三方操作语言,可以通过一些类似英语口语的操作指令,对数据库进行相关的管理操作,SQL语句也分几钟类别:
- DDL(数据定义语句):create、drop、alter、addd、change、modify
- DML(数据操作语句):insert、update、delete、select
- DCL(数据控制语句):grant、revoke
- 功能函数:聚合函数、字符串函数、日期函数、数学函数、系统函数等
6、DDL语句使用
数据定义语句,主要用于对于数据库实例中元素(表,视图,索引,过程,函数,触发器)的结构进行操作(不包含对数据的操作)
(1)创建表(学生(学号,姓名,性别,年龄,生日,专业,学分)),创建数据库
语法:create table 表名称
(
列名1 数据库类型【(长度)约束信息】,
列名2 数据类型【(长度)约束信息】
……
)
---创建数据库 ---格式:create database 数据库名 例如: create database lc; ------只创建一个时用单数,database ------创建多个,databases create table student ( sno int, sname varchar(20), sex char(2), age int, birthday date, major varchar(20), score float );
(2)显示表结构
desc 表明称;
(3)重命名表
rname table student to stu;
(4)新增列(项stu表中新增marray列,类型为int,默认值为0)
alter table stu add column marray int default 0;
(5)删除列(删除stu表中的age列)
alter table stu drop column age;
(6)修改列信息(修改stu表中birthday列,将列名改为birth,数据类型改为date)
alter table stu change birthday birth date;
(7)删除表,删除数据库
格式: drop table 表名; 例如: drop table stu; 格式: drop database 数据库名 例如: drop database lc;
(8)添加字段
alter table student add 'phone' varchar(13) comment '电话';
7、DML语句
数据操作语句,主要用于对数据库表中的数据进行增删改差等相关操作
(1)insert(插入操作,单双引号都可以)
语法:insert into 表名(列1,列2……)values(值1,值2……);
①向stu表中的sno列和sname列插入数据
insert into stu(sno,sname)values(1000,‘狗蛋’);
②向stu表中的所有列插入一行数据
insert into stu values(1001,‘来福’,‘男’,‘2005-05-10’,‘挖掘机专业’,101.5,1);
③向stu表中的sno,sname,major列插入两行数据
insert into stu(sno,sname,major) values(1002,‘张三’,‘软件工程’),(1003,‘李四’,‘网络工程’);
④蠕虫赋值
insert into stu selecct * from stu;
8、update(修改)
语法:update 表名 set 列1=值,列2=值2…… where 条件
(1)修改stu表中sno=1000的学生,将major设置为“美容美发”
update stu set major=‘美容美发’ where sno=1000;
(2)将stu表中所有sex为null的学生的性别设置为‘女’(为空判断不能使用“=”而是“is null”或者“is not null”)
update stu set sex=‘女’ where sex is null;
(3)将stu表中学号为1000,1002,1003的学生的生日修改为1990-01-01
update stu set birth=‘1990-01-01’ where sno in(1000,1002,1003);
(4)修改stu表中的sex列和marray,根据sname修改
update stu set sex=‘男’,marray=1 where sname=‘张三’;
9、delete(删除)
语法:delete from 表名称 where 条件
(1)删除stu表中sno=1003的学生
delete from stu where sno=1003;
10、select(查询)属于数据查询语言DQL
语法:select 查询列 from 表名称 where 条件
(1)查询stu表中所有数据
select * from stu;
(2)查询stu表中指定列数据
select sno,sname,sex,birth from stu;
(3)查询出stu表中所有未婚女性
select * from stu where sex=‘女’ and marray=0;
11、聚合函数使用(count,max,min,sum,avg)
(1)查询出学生的总人数
select count(*) from stu;
(2)查询出学生最高的学分
select max(score) from stu;
(3)查询出学生最少的学分
select min(score)from stu;
(4)查询出学生的平均学分
select avg(score)from stu;
12、维护数据完整性操作
(1)约束(constrant)
①主键约束(Primary Key)---不允许为空同时不允许重复(标识列)
②不为空约束(not null)
③唯一约束(unique)
④检查约束(check)
⑤外键约束(Foregin Key)----表之间的关系体现
注意:
mysql中check约束不生效,一般使用enum类型来替代
一张表中只能存在一个主键,该主键可以是一列,也可以同时为多列(联合主键:不推荐)
例如:创建以下几张用户表,商品表,订单表,具体如下
商品表:
-
商品id列 主键列
-
商品名称 不允许为空
-
商品价格
-
库存
-
商品的类型 类型只能是(食品,生活用品,文具,服装)几类
-
商品的条形码 不能重复
create table product ( pid int primary key auto_increment, pname varchar(30) not null comment '商品名', price float, pcount int, ptype enum('食品','生活用品','服装'), qrcode varchar(60) unique );
客户表:
-
客户id 主键
-
姓名 不允许为空
-
登录账号 不允许为空并且不能重复
-
密码 不允许为空,默认值是123456
-
性别 (男,女)
create table customer ( cid int primary key auto_increment, cname varchar(30) not null, caccount varchar(60) not null unique comment '登录账号', cpwd varchar(60) not null default '123456', sex enum('男','女') );
订单项表:
-
订单号(id)
-
商品id 外键列(FK)
-
用户id 外键列(FK)
-
购买数量
-
总价格
create table orders ( oid int primary key auto_increment, pid int reference product(pid), cid int , bcount int, total float ); ---在表创建完成后添加外键列 alter table orders add foreign key(cid) references customer(cid);
(2)触发器(trigger):数据库可编程特性
13、mysql中的数据类型
Java Mysql
整数: int int
long bigint
小数: double double
时间类型: data date
字符串类型: String char 或者 varchar
char和varchar的区别:
char 不可以改变长度(定长)
varchar 可以改变长度(变长)
14、mysql的条件查询
(1)例如:查询部门号大于2的部门数据
select * from dept where did > 2;
(2)查询某个区间范围数据:between……and……
①例如:查询薪资大于2000并且小于4000的薪资
select * from salary where jbgz between 2000 and 4000;
注意:between num1 and num2,表示在【num1,num2】区间内取数据,相当于大于等于num1,并且小于等于num2
②第二种(不含等于)
select * from salary where jbgz>2000 and jbgz<4000;
(3)从列表中匹配数据 in (列表)
例如:查出基本工资为3000,或者5000的薪资数据
select * from salary where jbgz=3000 or jbgz=5000; select * from salary where jbgz in(3000,5000);
(4)查询数据时给字段添加别名
格式:select 字段 别名 from 表名
select jbgz basic_salary from salary; ----basic_salary就是给jbgz取得别名
15、排序:order by
--升序(默认) 格式:select * from 表名 order by 字段 表示:查询数据并且根据字段进行排序 例如:查询所有薪资,并且根据jbgz排序 select * from salary order by jbgz; 降序: 格式:select * from 表名 order by 字段 desc;
16、练习
查询薪资数据,将jbgz小于3000的涨500,并根据jbgz降序排列 select (jbgz+500) jbgz from salary where jbgz<3000; 查询jbgz与jtbt的综合收入 select (jbgz+jtbt) zh from salary; 注意:数字和null相加,最后得结果为null
17、limit关键字
作用:限制显示的结果集,常用来制作分页
使用格式:select * from 表名 limit 数据起始位置,显示数据的条数;
例如:查询jbgz,并显示前三条 select * from salary 0,3;
18、子查询
查询出jbgz比eid为1 的jbgz高的数据 1、查询出eid为1的jbgz select jbgz from salary where eid=1; 2、查询出jbgz>3000的数据 select * from salary where jbgz>3000; 综合: select * from salary where jbgz>(select jbgz from salary where eid=1); 练习: select * from emp where birth<(select birth from emp where eid=1);
19、函数的运用
(1)连接字符串函数concat(str1,str2...);
将括号中多个字符串拼接到一起
(2)字符串替换函数insert(str,pos,len,newStr);
str:需要被替换的字符串
pos:从某个位置开始被替换
len:被替换字符串的长度
newStr:代替的字符串
(3)将字符转换为小写字符 lower(str)
将str字符串中的所有大写字母转换成小写字母
(4)将字符转成大写字符 upper(str)
将str字符串中的所有小写字母转成大写字母
(5)截取字符串 substring(str,pos,len);
(6)获取当前日期前 curdate();
(7)获取当前时间 curtime();
(8)获取当前日期和时间 now();
(10)获取日期date的年份 year(date);
(11)获取time的小时 hour(time);
(12)获取time的分钟值 minute(time);
(13)获取两个时间相隔的天数 datediff(date1,date2);
(14)获取指定日期加上n天后的日期 adddate(date,n);
(15)获取大于或等于数值x的最小整数 ceil(x);
(16)获取小于或等于数值x的最小整数 floor(x);
(17)在0-1之间随机生成一个小数 rand();
20、Mysql高级查询
(1)exists关键字在子查询中的作用
格式:select …… from 表名 where exists(子查询)
有结果返回,就为true;无结果返回,就为false
例如:判断是否有成绩大于80的数据
例如:检查“Logic Java”课程最近一次考试成绩,如果考试成绩有80分以上的成绩,显示分数排在前五名的学员学号和分数
(2)分组查询 group by
格式:select …… from 表名 where …… group by 分组字段;
例如:查询出每门课的平均成绩
例如:分别统计每个年级的人数
例如:分别统计每个年级男,女人数
如果需要根据多个字段分组,可以将多个字段都加到group by后面,用逗号隔开
例如:查询每门课的平均成绩,按照成绩从高到低排序
(3)分组筛选
格式:select……from 表名 where ……group by……having……
例如:查询出平均成绩大于68的课程编号
21、distinct关键字:去重
格式:select distinct……from 表名;
例如:查询subjectNo,并去掉重复数据
查询subjectNo,studentResult,并去掉重复的结果数据
注意:distinct会将后面的字段值看做一个整体,只有当字段值都一样时才会认为是重复数据,distinct只能写在select后面
22、like关键字:模糊查询
格式:select ……from 表名 where 字段 like……
注意:下划线表示一个占位符,表示此处一个内容
注意:%表示此处可以有0个或者多个内容
例如:查询出名字中包含 文 的数据
23、多表查询
(1)内连接 inner join
① 格式:select……from 表1 inner join 表2 on 关联字段关系
注意:on 后面写的是多表关联字段
例如:查询出分数大于71分的学生编号,课程编号,分数
② 格式:select ……from 表1,表2 where 关联字段关系
注意:关联查询使用字段时,最好在字段前面都加上表名,表示区分使用的哪张表中的字段
例如:查询出分数大于71的学生姓名,分数,以及课程名称(三张表关联)
(2)左连接
格式:select ……from 表1 left join 表2 on……
例如:查询出每个学生姓名以及成绩
将两张表的位置互换
注意:对于left join而言,left join左边的表为主表,右边的表为从表,主表中的数据会逐条匹配从表中的数据,如果匹配到了数据,就会显示数据,如果匹配不到,就用null填充,主表中的数据都会显示
(3)右外连接(right join)
对于right join而言,right join右边的表为主表,左边的表为从表,主表中的数据会逐条匹配从表中的数据,如果匹配到了数据,就会显示数据,如果匹配不到,就用null填充,主表中的数据都会显示