1.字符集与储存引擎
--没有指定引擎与字符集
create table if not exists `goods`(
`id` int unsigned not null auto_increment primary key,
`goodsname` varchar(255) not null
)engine=innodb default charset=utf8;
1.如果不指定存储引擎,使用的是配置文件的默认值
2.如果不制定表的字符集,使用的是配置文件设置的字符集
工作中没有权限修改
3.设置指定数据库的字符集
alter database 数据库名 character set 字符集;
alter database jn_user chaeacter SET utf8;
4.修改表的字符集
ALTER TABLE `表名` DEFAULT CHARACTER SET 字符集;
ALTER TABLE `goods` DEFAULT CHARACTER SET gbk;
使用黑窗口查正确显示内容,请设置 set names gbk;
这是与操作系统有关
无论如何,在建表时,请设置好字符集与存储引擎,解决不必要的配置
2.查
查询语句,最灵活,最麻烦
--建表
create table if not exists `goods`(
`id` int unsigned not null auto_increment ,
primary key (`id`),
`name` varchar(255) not null ,
`price` float(8,2) not null ,
`image` varchar(255) not null ,
`color` varchar(32) not null ,
`size` varchar(32) not null ,
`evaluate` int unsigned not null
)engine=innodb default charset=utf8;
insert into `goods`(`name`,`price`,`image`,`color`,`size`,`evaluate`) values
('文胸1',199,'1.jpg','黑色','36e','1'),
('文胸2',199,'2.jpg','蓝色','36e','1'),
('文胸3',199,'3.jpg','白色','36e','1'),
('文胸4',199,'4.jpg','黑色','36e','1'),
('文胸5',199,'6.jpg','绿色','36e','1'),
('文胸6',199,'21.jpg','黑色','36e','1'),
('文胸7',199,'13.jpg','黑色','70A','1'),
('文胸8',199,'11.jpg','黑色','36e','1'),
('文胸9',199,'14.jpg','黑色','36e','1'),
('文胸10',199,'51.jpg','黑色','36e','1'),
('文胸11',199,'17.jpg','黑色','71B','1'),
('文胸12',199,'19.jpg','黑色','36e','1'),
('文胸13',199,'12.jpg','黑色','36e','1'),
('文胸14',199,'13.jpg','红色','72C','1'),
('文胸15',199,'12.jpg','黑色','36B','1'),
('文胸16',199,'16.jpg','黑色','73D','1'),
('文胸17',199,'13.jpg','黑色','36e','1'),
('文胸18',199,'12.jpg','黑色','36e','1'),
('文胸19',199,'1.jpg','黑色','36e','1'),
('文胸20',199,'1.jpg','黑色','36e','1'),
('文胸21',199,'1.jpg','黑色','36e','1'),
('文胸22',199,'1.jpg','黑色','36e','1'),
('文胸23',199,'1.jpg','黑色','36e','1'),
('文胸24',199,'1.jpg','黑色','36e','1'),
('文胸25',199,'1.jpg','黑色','36e','1');
1.查询所有字段,所有数据
select * from `goods`;
2.查询指定字段,所有数据
select `id`,`name`,`price` from `goods`;
查询所有数据,会扫描整个数据库。
所以,不要在高峰期使用 查询所有数据。
3.等于 =
select * from `goods` where `size` = '70A';
select * from `goods` where `id` = 16;
4.<=>
添加测试字段
alter table `goods` add `click_num` int unsigned;
--点击量
-- 不识别 null
select * from `goods` where `click_num` = null;
-- 识别 null
select * from `goods` where `click_num` <=> null;
5.查询空格 is null 或 is not null
-- 点击量为空类型的数据
select * from `goods` where `click_num` is null;
-- 点击量不为空的数据
select * from `goods` where `click_num` is not null;
6.不等于 != 或 <> 没有区别
select * from `goods` where `id` != 10;
select * from `goods` where `id` <> 10;
select * from `goods` where `size` != '36e';
--都不识别null
select * from `goods` where `click_num` != null;
select * from `goods` where `click_num` <> null;
8. > , >= ,< , <=
select * from `goods` where `id` >13;
select * from `goods` where `id` >=13;
select * from `goods` where `id` <13;
select * from `goods` where `id` <=13;
9.between and 数值范围 , not between and
--id 11到13的数据
select * from `goods` where `id` between 11 and 13;
--除了12到14的数据
select * from `goods` where `id` not between 12 and 14;
10.and 与 , or 或
and 满足两条件
or 只满足一个条件
update `goods` set `price` = 299 , `image` = '2.jpg' where `id` = 11;
update `goods` set `price` = 399 , `image` = '3.jpg' where `id` = 12;
update `goods` set `price` = 499 , `image` = '4.jpg' where `id` = 13;
update `goods` set `price` = 599 , `image` = '5.jpg' where `id` = 14;
update `goods` set `price` = 99 , `image` = '6.jpg' where `id` = 15;
update `goods` set `price` = 9 , `image` = '7.jpg' where `id` = 16;
update `goods` set `click_num` = 100 where `id` = 11;
update `goods` set `click_num` = 200 where `id` = 12;
update `goods` set `click_num` = 300 where `id` = 13;
update `goods` set `click_num` = 400 where `id` = 14;
update `goods` set `click_num` = 500 where `id` = 15;
update `goods` set `click_num` = 600 where `id` = 16;
--价格小于300,点击量大于400
select * from `goods` where `price` < 300 and `click_num` > 400;
--or 或
select * from `goods` where `price` < 300 or `click_num` > 400;
11.in , not in 范围集合
select * from `goods` where `id` in(11,13,15);
--除了范围集合
select * from `goods` where `size` not in('72C','73D','75B');
12.like 模糊查询
% 任意长度
李%
李开头
%勇
勇结尾
%宝%
包含宝字的
_一位长度
insert into `goods` values
(null , '凹凸文胸3',999,'a.jpg','灰白','100B',2,899),
(null , '凹凸巨大文胸',999,'a.jpg','灰白','100B',2,899),
(null , '凹凸巨大文胸',999,'a.jpg','灰白','100B',2,899),
(null , '凹凸曼妮芬大文胸',999,'a.jpg','灰白','100B',2,899);
insert into `goods` values
(null , '百度',999,'a.jpg','灰白','100B',2,899),
(null , '白百合',999,'a.jpg','灰白','100B',2,899),
(null , '白骨晶',999,'a.jpg','灰白','100B',2,899),
(null , '白日',999,'a.jpg','灰白','100B',2,899);
select * from `goods` where `name` like '凹凸%';
select * from `goods` where `name` like '白%';
select * from `goods` where `name` like '%胸';
select * from `goods` where `name` like '%大%';
select * from `goods` where `name` like '_日';
select * from `goods` where `name` like '白__';
13.去重查询
select distinct 字段 from 表名;
select distinct `image`,`size` from `goods`;
14.排序 order by
select * from `goods` order by `id` asc;--顺序
select * from `goods` order by `id` desc;--倒叙
15.使用表达式
select `id`,`name`,`price` + 1 from `goods`;
-- 取原数据再计算
update `goods` set `price` = `price` + 1 where `id` = 25;
-- 更新点击量
update `goods` set `click_num` = `click_num` + 1 where `id` = 24;
1. concat() 字符链接
select `id`,concat(`name`,' 大特卖') from `goods`;
2.count()总数
select count(*) from `goods`;--计算表的总数量
3.计算和
select sum(`click_num`) from `goods`;
4.avg() 平均值
select avg(`click_num`) from `goods`;
5.max() 最大
select max`click_num`) from `goods`;
min最小
16.分组查询
-- 以指定字段分组,并统计每组数量
select `color`,count(*) from `goods` group by `color`;
-- 查询 evaluate ,并以 evaluate 进行分组,并计算每组的最大值
select `evaluate`,max(`price`) from `goods` group by `evaluate`;
-- 查询 evaluate ,并以 evaluate 进行分组,并计算每组的最大值
select `evaluate`,max(`price`) from `goods` group by `evaluate`;
17.limit 限制查询结果
-- 按照默认顺序,显示一条数据
select * from `goods` limit 1;
select * from `goods` order by `id` desc limit 1;
select * from `goods` where `price` <= 599 order by `price` desc limit 3 ;
order by desc limit 按顺序来
-- 分页 limit 偏移量,长度;
-- 获取总数
select count(*) from `goods`; -- 总数
-- 假设每页显示 5 条数据
-- 求总页码数 : ceil( 总数 / 每页显示条数 ) = 4
-- 偏移量的计算公式:(当前页码 - 1) * 每页显示条数 == 偏移量
-- 第一页 (1-1)*5 = 0
select * from goods order by `id` desc limit 0,5;
-- 第2页 (2 - 1) * 5 = 5
select * from goods order by `id` desc limit 5 ,5; -- 第2页
-- 第3页 (3 - 1) * 5 = 10
select * from goods order by `id` desc limit 10,5; -- 第3页
-- 第4页 (4 - 1) * 5 = 15
select * from goods order by `id` desc limit 15,5; -- 第4页
18.连接查询
--设计用户表
create table if not exists `shop_user`(
`id` int unsigned not null auto_increment,
primary key(`id`),
`user` varchar(255) not null ,
unique un_user(`user`),
`pass` char(32) not null,
`icon` varchar(255) not null default 'default.jpg',
`sex` enum('0','1','2') not null default '2',
`email` varchar(255) not null default '',
`address` varchar(255) not null default '',
`age` int unsigned not null default 0,
`tel` char(11) not null default '',
`grade` tinyint unsigned not null default 2,
`status` tinyint unsigned not null default 1,
`addtime` int unsigned not null
)engine=innodb default charset=utf8;
-- 添加测试数据
insert into `shop_user`(`user`,`pass`,`addtime`) values('admin',md5('123'),unix_timestamp());
insert into `shop_user`(`user`,`pass`,`addtime`) values('大勇',md5('123'),unix_timestamp());
insert into `shop_user`(`user`,`pass`,`addtime`) values('jack',md5('123'),unix_timestamp());
insert into `shop_user`(`user`,`pass`,`addtime`) values('提莫',md5('123'),unix_timestamp());
--创建收货地址表
create table if not exists `shop_address`(
`id` int unsigned not null auto_increment ,
primary key(`id`),
`user_id` int unsigned not null ,
`name` varchar(255) not null ,
`address` varchar(255) not null ,
`tel` char(11) not null ,
`email` varchar(255) not null ,
`status` tinyint unsigned not null default 0,
`addtime` int unsigned not null
)engine=innodb default charset=utf8;
insert into `shop_address` values
(null , 1, '大勇','dongpu大马路','13922130889','123@qq.com',1,unix_timestamp()),
(null , 1, '大勇2b','可能还在读小学','13822130889','123@qq.com',1,unix_timestamp()),
(null , 1, '大勇3','东莞XXXX店','13722130889','123@qq.com',1,unix_timestamp()),
(null , 2, '浪哥','天河白天月','15722130889','123@qq.com',1,unix_timestamp()),
(null , 2, '隔壁老王','天河七天','18722130889','123@qq.com',1,unix_timestamp());
--多表查询
select * from `shop_user` , `shop_address`;
内连查询
SELECT * FROM 表1,表2 WHERE 表1.字段 = 表2.字段;
select * from `shop_user`,`shop_address` where shop_user.id = shop_address.user_id;
--查询 用户表的id、用户名、地址,收货地址表的收货人、收货地址
select shop_user.id , shop_user.user,shop_address.id , shop_address.name , shop_address.address from `shop_user`,`shop_address` where shop_user.id = shop_address.user_id;
--别名的使用
select u.id , u.user , a.id , a.name , a.address from `shop_user` as u,`shop_address` as a where u.id = a.user_id;
--外连接
以左表为主: 左表的所有数据,都会查询出来,没有与之关联,则填空类型
select * from 表1 left join 表2 ON 表1.字段 = 表2.字段
select shop_user.id , shop_user.user , shop_address.user_id , shop_address.name , shop_address.address from shop_user left join shop_address on shop_user.id = shop_address.user_id;
-- 起别名
select u.id , u.user , a.user_id , a.name , a.address from shop_user as u left join shop_address as a on u.id = a.user_id;
-- 以右表为主,右表的所有数据都会查询,并与左表关联
右连:SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段
select u.id,u.user , a.user_id , a.name , a.address from shop_user as u right join shop_address as a ON u.id = a.user_id;
-- 子查询
查询admin用户的所有收货地址
1.知道 admin 的 id
select id from shop_user where `user` = 'admin'; 1
2.拿着 admin 的 id去查询 shop_address 条件就是 id = user_id
select * from shop_address where `user_id` = 1;
合体(子查询)
select * from shop_address where `user_id` = (select id from shop_user where `user` = 'admin');
select * from shop_address where `user_id` in(select id from shop_user where `user` = 'admin');
数据的复制
-- 复制表结构
create table 新表 like 旧表;
create table n_address like shop_address;
-- 复制数据
insert into 新表 select * from 旧表;
insert into n_address select * from shop_address;
创建或授权 必须是 root 用户才可以使用授权功能
grant 权限 on 数据库.数据表 to 用户名@登录主机 identified by "密码"
1.用户已经存在,则更新权限
2.用户不存在,添加用户并授权
% 可远程登录
localhost 本地登录
grant select,update,delete,insert on t4.shop_address to cindy@localhost identified by 'root';
grant select,update,delete,insert on t4.shop_address to cindy@"%" identified by 'root';
all : select , insert , delete , update
*.* 所有库.所有表
grant all on *.* to cindy@localhost identified by 'root';
--远程登录
mysql -h ip -u cindy -proot
--user表在mysql库里
--删除用户
delete from `user` where `user` = 'cindy';
--更新权限
flush privileges;
--更改密码 update
update `user` set `password` = password('123') where `user` = 'zhiyong';
-- 更新权限
flush privileges;
-- 超级管理员的密码忘记了
update `user` set `password` = password('123') where `user` = 'root';
--重启服务器
数据库备份
--备份一个库
mysqldump -u root -p jn > d:/jn.sql
-- 还原一个库
-- 必须先创建一个库
mysql -u root -p t6 < d:/jn.sql
--备份一个表
mysqldump -u root -p 库名 表名 > d:/文件名.sql
--使用source还原数据表
--必须选库
use 库;
source 路径
取别名:
as
select name as '姓名' from jn_user;