mysql
2017-10-10 本文已影响10人
帅哥_刷哥
连接
启动服务 net start mysql
停止服务 net stop mysql
连接服务器 mysql -uroot -proot
（标准 mysql -hlocalhost -P3306 -uroot -proot）
mysql -h localhost -u root -p
//如果不写默认连接localhost
mysql -uroot -p
mysql -uroot -proot
查看连接数
show processlist
mysql查看最大允许的上传数据
my.ini中
max_allowed_packet=1M
数据库操作
1.针对数据库和表的操作
创建 create
查看 show
删除 drop
修改 alter
2.针对表中数据操作
增加 create
删除 delete
修改 update （更新）
查询 select
查看数据库
show databases;
这三张表不能动
information_schema
performance_schema
mysql
数据库字符集
查看数据库和编码相关的变量
show variables like 'character%';
修改客户端字符集(这样就可以插入中文了)只是当前dos有效
set character_set_client=gbk;
修改客户端输出字符集(这样在查询时就可以显示中文了)只是当前dos有效
set character_set_results=gbk;
集中修改客户端配置信息
到安装目录下找到my.ini文件。
修改：
default-character-set = gbk
服务重启即可。
创建数据库
创建一个javatest数据库
create database javatest;
创建一个使用utf-8字符集的mydb数据库
create database mydb character set utf8;
创建一个使用utf-8字符集,并带校验规则的mydb数据库
create database mydb character set utf8 collate utf8_general_ci;
使用某个数据库
use javatest;
删除数据库
drop database javatest;
查看mydb数据库的定义信息
show create database mydb;
修改数据库
修改一个mydb数据库的字符集为gbk
alter database mydb character set gbk;
备份数据库（要先退出数据库）
mysqldump -u用户名 -p密码 数据库名称 > 文件名.sql
备份mydb数据库到D盘下的a.sql中,(注意:没有分号结尾)
mysqldump -uroot -p123456 mydb > D:\a.sql
恢复数据库（不要退出数据库）
注意:数据库是无法恢复的，恢复的是数据库中的表和数据
1先创建mydb数据库，名字必须相同。
2使用mydb数据库库：use mydb；
3恢复mydb数据库：soure D:\a.sql (注意：这个命令不能写分号)
查看所有的表
show tables;
字符集问题
建表时默认是UTF-8
而在windows下窗口是GBK
//windows 窗口给mysql的数据是GBK的
set names gbk;
创建一张表
创建一张表
create table person(
id int,
name varchar(20),
age int
)
语法：
create table 表名(
列名 列类型 [约束] [默认值]
)engine 引擎名 charset 字符集;
创建一张表
create table employee(
id int, //整形
name varchar(20),
gender varchar(6), //字符型
birthday date,
entry_day date, //日期型
job char(20), //字符型
salary float, //小数型
resume text //这个是大文本
);
查看employee表的定义信息
show create table employee;
查看employee表结构
desc employee;
Field 列名
Type 类型
Null 是否可以空
Key 是键吗
Default 默认值是什么
Extra 额外说明
在employee表的基础上增加一个image字段
alert table employee add image blob;
修改employee表中的job列的长度。
alert table employee modify job varchar(60);
删除employee表中的gender列
alert table employee drop gender;
修改employee表中的name列的名字为username
alert table employee change name username;
修改表的字符集为utf-8
alter table employee character set utf8;
把employee表名修改为users
rename table employee to users;
删除users表
drop table users;
表的约束
给表中的字段添加一些约束。
create table a
(
id int unique(约束),
name varchar(20) not null,
userid int primary key
);
唯一约束：unique 表中这个字段的数据不能重复
非空约束：not null 表中这个字段不能有空值
主键约束：primary key 就等于非空+唯一
增加主键约束
alter table user add primary key(id);
删除主键约束
alter table user drop primary key;
建立联合主键 （让两个字段合在一起变成主键）
所有的主键不能为null,所有的主键加在一起不允许出现重复
create table a(
firstname varchar(20),
lastname varchar(20),
primary key(firstname,lastname)
);
定义主键自增长
create table a(
id int primary key auto_increment
);
外键约束
作用:被参照的列不允许删除，参照列必须写被参照列存在值
create table husband(
id int primary key auto_increment,
name varchar(20)
);
create table wife(
id int primary key auto_increment,
name varchar(20),
husbandid int,
constraint husbandid_FK foreign key(husbandid) references husband(id) //这个就是在给 husbandid 添加外键约束
);
解除外键参照关系
update wife set husbandid = null where name = 'xiaohong';
字段的类型
说明
建表：就是声明列的过程
数据以文件的形式放在硬盘中（也有放在内存中）
列：不同的类型占的空间不一样
列的原则：够用又不浪费
分类
整型
tinyint 1个字节
smallint 2个字节
mediumint 3个字节
int 4个字节
bigint 8个字节
浮点型
float(M,D) 浮点型
decimal(M,D) 定点型
字符型
char(M)
varchar(M)
text
时间/日期型
datetime 日期时间
date 日期
time 时间
year 年
时间戳 int类型保存Long值
枚举
enum
整型tinyint(M) unsigned zerofill
M : 宽度（在0填充时才有意义）
unsigned ：无符号类型（非负）
zerofill ：0填充，（默认无符号）
注意：M必须要和zerofill一起使用，否则无效。
tinyint 默认存储值的范围是 -128 ~ 127
tinyint unsigned 存储值的范围是 0 ~ 255
tinyint(M) zerofill 的数据库默认是 tinyint(M) unsigned zerofill
默认数据不够M位在前边补0
只是显示效果，不会影响数据的真正存储。
//增加一列
alter table person add money int unsigned
alter table person add money1 int unsigned not null default 0; 默认值是0
//unsigned用法
create table person(
id int,
name varchar(20),
age tinyint, //存储范围是 -128 ~ 127
money tinyint unsigned //存储范围是 0 ~ 255
);
小数型float(M,D)/decimal(M,D)
M:精度（总位数，不包含点）
D:标度（小数位）
注意：decimal 更加精确，就是往数据库插入值时，会出现数据精确问题。
举例：
float(6,2) 存储值的范围是 -9999.99 ~ 9999.99
float(6,2) unsigned 存储值的范围是 0.00 ~ 9999.99
create table goods(
name varchar(20) default '',
price float(6,2) default 0.0 //存储值的范围是 -9999.99 ~ 9999.99
);
//结果是1000.00 会进位
insert into goods (name,price) values ('zhangsan',999.998);
字符型 char(M)/varchar(M)/text
char(M) 定长字符串 0 ~ 255
M 可容纳的字符数
如果存储小于M个字符，实占M个字符
如果末尾有空格，取出数据时空格将被清除。
速度上快
varchar(M) 变长字符串 0 ~ 65535
M 可容纳的字符数
如果存储小于M个字符，存入几个实占几个字符
text 文本串 2W ~ 6W
不能加默认值
例子
create table user(
name char(4), //只能存储4个字符
firstname varchar(4),//可以存储8个字符
description text
);
insert into user(name,firstname,description) values ('中国','aaaa','aaaa');
时间/日期型 datetime/date/time/year/时间戳
datetime 日期时间 典型格式：2017-08-29 22:09:30
存储范围 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
date 日期 典型格式 2017-08-29
存储范围 1000-01-01 ~ 9999-12-31
time 时间 典型格式 hh:mm:ss
存储范围 -838:59:59 ~ 838:59:59
year 年 1个字节 1901~2155 出错时是0000年
如果输入2位 00-69 表示 2000-2069
70-99 表示 1970-1999
时间戳
用int类型来存储时间戳，方便计算。
枚举 enum
create table user(
gender enum('男','女') //只能存 男或者女
);
insert into user (gender) values ('男');
表中数据操作
增加(插入)
insert into student (id,name,age) values (1,'张三',20);
第一个括号中的是列 ，后一个括号中的是值，要一一对应
加单引号的是字符串
insert into 表名(列名) values(值);
注意：
1.列名可以省略，但是值必须要全部赋值。
insert into user(id,name) values(1,'zhangsan');
insert into user(id,name) values (1,'zhangsan'),values (2,'lisi');
insert into users(id,name,gender,birthday,salary,entry_date,resume) values(1,'zhangsan','male','1988-12-3',1000,'2008-4-15','good boy');
修改表中数据
update student set name='王五' where id=1;
update 表名 set 列名=值,列名=值 where 列名=值;
update user set name='zhangsan' where id=1;
修改user表中每条数据的name字段为lisi
update user set name = 'lisi';
修改user表中名字为zhangsan的salary为3000
update user set salary = 3000 where name = 'zhangsan';
修改user表中名字为zhangsan的salary为3000和gender为female
update user set salary = 3000,gender = 'female' where name = 'zhangsan';
修改user表中名字为zhangsan的salary的值增加1000
updat user set salary = salary + 1000 where name = 'zhangsan';
删除数据
删除所有
delete from student;//一行一行的删除
truncate user; //先摧毁表,再创建表。（效率高）
删除某条数据
delete from student where id = 1;
delete from user where name = 'zhangsan';
语法
delete from 表名 where 列名=值;
delete from user where id=2;
查询数据
说明
select的5种子句
where 条件查询
模糊查询
group by 分组
having 筛选
order by 排序
limit 限制结果条件
5个统计函数
max 最大值
min 最小值
sum 求总和
avg 求平均
count 求总行数
查看student表中所有数据
select * from student;
select * from goods;
查看student表中所有学生的名字和英语成绩
select name,english from student;
select goods_id,goods_name,shop_price from goods;
查看student表中所有学生的名字和英语成绩,并且去掉重复数据
select distinct name,english from student;
查看student表中所有学生的所有成绩，并且在成绩中+10分，然后显示
select name,english+10,chinese+10,math+10 from student;
修改查出来的字段名字（起别名）并且as可以省略
select name,english+10 as english,chinese+10 as chinese,math+10 as math from student;
统计学生的总分
select name,english+math+chinese as sum from student;
查询名字为lisi的学生成绩
select * from student where name = 'lisi';
select * from student where id = 1;
select * from goods where goods_id = 10;
select * from goods where shop_price = 2000;
查询英语成绩大于90分的同学
select *from student where english > 90;
select * from student where id > 1;
select * from goods where goods_id > 10;
select * from goods where shop_price > 2000;
查询总分大于200分的所有同学
select *from student where english+chinese+math>200;
select * from goods where shop_price-goods_price > 300;
查询商品价格大于等于2000的商品
select * from goods where shop_price >= 2000;
查询商品价格小于2000的商品
select * from goods where shop_price < 2000;
查询商品价格小于等于2000的商品
select * from goods where shop_price <= 2000;
查询商品不等于2000的商品
select * from goods where shop_price <> 2000;
查询英语分数在80-90之间的同学
select * from student where english between 80 and 90;
select * from goods where id between 2 and 5; 范围是：[2,5]
查询数学分数为89,90,91的同学
select * from student where math in (89,90,91);
select * from goods where id in(4,5);
查询商品id不是4和5的商品
select * from goods where id not in (4,5);
查询所有姓李的学生（% 通配任意字符）
select * from student where name like '李%';
select * from goods where goods_name like '诺基亚%';
查询所有姓李的,并且名字为两个字的学生成绩（_ 通配单个字符）
select * from student where name like '李_';
查询数学分数>80,语文分数>80的同学
select *from student where math > 80 and chinese > 80;
查询英语>80 或者 总分>200的同学
select * from student where english>80 or chinese+math+english>200;
排序-对数学成绩排序后输出
select * from student order by math; //升序 asc 默认
select * from student order by math desc;//降序
排序-对总分排序后输出,然后再按照从高到底的顺序输出
select * from student order by english+chinese+math desc;
排序-对姓李的学生成绩排序输出
select * from student where name like '李%' order by math;
统计记录-统计一个班级共有多少学生
select count(*) from student;//会对所有的学生统计，没问题
selct count(chinese) from student;//会对chinese非空的所有学生统计。
select count(*) from goods;
统计记录-统计数学成绩大于90的学生有多少个
select count(*) from student where math>90;
统计记录-统计总分大于250的人数有多少
select count(*) from student where english+chinese+math>250;
统计一个班级数学总成绩
select sum(math) from student;
select sum(goods_number) from goods;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(chinese),sum(english) from student;
统计一个班级语文、英语、数学的成绩总和
select sum(math+chinese+english) from student;
统计一个班级语文成绩的平均分
select sum(chinese)/count(*) from student;
select sum(chinese)/count(chinese) from student;
求一个班级数学平均分
select avg(chinese) from student;
select avg(price) from goods;
求一个班级总分平均分
select avg(chinese+math+chinese) from student;
求班级的最高分(也就是一列数据中的最大值)
select max(math) from student;
select max(price) from goods;
根据cat_id 分组，从每组中找出最贵的价格
select cat_id,max(price) from goods group by cat_id;
求班级的最低分(也就是一列数据中的最小值)
select min(math) from student;
select min(goods_id) from goods;
查询每类商品中最便宜的
select cat_id,min(goods_price) from goods group by cat_id;
在orders商品表中按照商品的名称product分组
select * from orders group by product;//每种名称只显示一次
select *,count(*) from orders group by product;//每种名称只显示一次，并且显示每组有多少个。
select *,count(*),count(price) from orders group by product;//每种名称只显示一次，显示每组有多少个,并且显示每组的总和。
显示商品总价大于100的商品（注意:having只能跟在group by 后边）
select *,count(*),sum(price) from orders group by product having sum(price) > 100;
查询每类商品中的平均价格
select cat_id,avg(goods_price) from goods group by cat_id;
查询每类商品的商品种类
select cat_id,count(*) from goods group by cat_id;
查询本店每个商品比市场价格低多少
select goods_id,goods_name,market_price-goods_price from goods;
查询每类商品下积压的货款
select cat_id,sum(shop_price * goods_number) from goods group by cat_id;
给列起别名
select cat_id,sum(shop_price * goods_number) as hk from goods group by cat_id;
select cat_id,sum(shop_price * goods_number) hk from goods group by cat_id;
查询本店每个商品比市场价格低多少。并且把大于200的选出
select goods_id,goods_name,market_price-goods_price as sheng from goods having sheng market_price-goods_price > 200;
查询本店每个商品比市场价格低多少。并且把第3类商品的大于200的选出
select goods_id,goods_name,market_price-goods_price as sheng where goods_id = 3 from goods having sheng market_price-goods_price > 200;
查询积压货款超过2W元的分类，以及分类积压的货款
select cat_id,goods_name,count(goods_price*goods_number) as hk from goods group by cat_id having hk > 20000;
查询该店积压的货款
select sum(goods_price * goods_number) from goods;
存储函数
无参返回字符串
DELIMITER $$
CREATE FUNCTION fun1() RETURNS CHAR(50)
RETURN 'hello';
$$
无参返回字符串
DELIMITER $$
CREATE FUNCTION fun4() RETURNS CHAR(50)
BEGIN
RETURN 'hello';
END $$
无参返回int
DELIMITER $$
CREATE FUNCTION fun3() RETURNS INT
RETURN 10;
$$
无参返回int
DELIMITER $$
CREATE FUNCTION fun4() RETURNS INT
BEGIN
RETURN 10;
END $$
有参返回int
DELIMITER $$
CREATE FUNCTION fun5(num int) RETURNS INT
BEGIN
RETURN num+10;
END $$
select fun5(5);
有参，有定义变量，并且初始化
DELIMITER $$
CREATE FUNCTION fun6(num INT) RETURNS INT
BEGIN
DECLARE num1 INT;
SET num1 = 10;
RETURN num+num1;
END $$
SELECT fun6(10);
有参返回int，并且定义变量，初始化变量从表中查取
DELIMITER $$
CREATE FUNCTION fun7(num INT) RETURNS INT
BEGIN
DECLARE num1 INT;
SELECT COUNT(*) INTO num1 FROM goods;
RETURN num+num1;
END $$
SELECT fun7(10);
存储过程
无参无返回值
delimiter $$
create procedure one()
begin
select id,name,money from master;
end $$
call one();
有输入参数无返回值
delimiter $$
create procedure two(tid int)
begin
select id,name,money from master where id = tid;
end $$
call two(2);
无参有返回值
delimiter $$
create procedure three(out tmoney int)
begin
select money into tmoney from master where id = 3;
end $$
set @a = '';
call three(@a);
select @a;
有输入输出参数
delimiter $$
create procedure four(in tid int,out tmoney int)
begin
select money into tmoney from master where id = tid;
end $$
set @a = '';
call four(3,@a);
select @a;
无参有输出参数，有定义变量
delimiter $$
create procedure six(out tmoney int)
begin
declare tid int;
set tid = 2;
select money into tmoney from master where id = tid;
end $$
set @a = '';
call six(@a);
select @a;
查询新闻总记录数存储过程
create or replace procedure getNewsCount(out v_totalCount number) as
begin
select count(*) into v_totalCount from news;
end;
重置数据库密码
1、编辑MySQL的配置文件：my.ini
一般在MySQL安装目录下有my.ini即MySQL的配置文件。
在此配置文件的最后添加如下一行：
skip-grant-tables
保存退出编辑。
2、然后重启MySQL服务
在命令行下执行：
net stop MySQL
net start MySQL
3、设置新的ROOT密码
然后再在命令行下执行：
MySQL -u root -p MySQL或mysql -u root -p
直接回车无需密码即可进入数据库了。
此时,在命令行下执行 use mysql;
现在我们执行如下语句把root密码更新为：
update user set password=PASSWORD("root") where user='root';
(注意:此时不用使用mysqladmin -u root -p password '你的新密码'这条命令修改密码,因为'skip-grant-tables'配置,
不信的话,你可以试用一下,它肯定会报如下所示的错误:
F:\Documents and Settings\long>mysqladmin -u root -p password 'root'
Enter password:
Warning: single quotes were not trimmed from the password by your command
line client, as you might have expected.
mysqladmin:
You cannot use 'password' command as mysqld runs
with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead)
exit 退出MySQL。
4、还原配置文件并重启服务
然后修改MySQL配置文件把刚才添加的那一行'skip-grant-tables'删除。
再次重起MySQL服务，密码修改完毕。
