mysql笔记

Posted on 2018年4月2日2018年5月26日

一、数据库的概念:
一台服务器下有多个库,一个库下有我到多张表,一张表下有多行多列的数据。
作为WEB开发程序员,和表的操作相对多
1、数据库服务器是不是mysql呢?
对于数据库的存储数据来说国际上有sql标准,只要遵守这个标准,这套软件就称为数据库管理系统

就好比说,看网页可以用firefox、IE、chrome
管理数据库有mysql、oracle、IBM DB2、sqlite、postgresql

各个厂商的产品可能对sql标准的执行标准有微小的差异
比如mysql就没有全连接fulljoin ;sql server中没有topN的用法
就好像html有w3c标准约束,但是各浏览器也有解析不兼容的地方,但是少数。
**********************************************************************
二、mysql的安装
不要安装最新版,往往bug多,5.1和5.5比较稳定
进程中mysqld是服务器;进程中mysql是客户端
另外客户端还有:navcat,mysqlfront.phpMyAdmin
*********************************************************************
三、十个基本语句
1、查看所有的库 show databases
2、创建库存 creat database
3、删除库 delete databaser
4、选择数据库 use database
5、查看表 show tables
6、创建表 creat table
7、删除表 delete table
8、改表名 rename table
9、清空表
10、创建表单
入门最基本语句
mysql -uusername -ppassword–>连接库
use 库名 –>选库语句
展示所有的库–>showdatabases;
展示所有的表–>showtables;
创建一个数据库–>create database 库名 charset utf8;
删除一个数据库–>drop database 库名;
改个数据库名–>不可以,只能改表,列名
phpMyAdmin有这功能是新建一个库,然后把所有表复制到新库,再删除旧库完成

入门建表语句
create table stu(
snum int,
sname varchar(10)
)engine myisam charset utf8;

删除表格–>drop table stu;
改表格的名字–>rename table old to new
往表格表增加数据–>insert into new values
(1,’zhangsan’),
(2,’lisi’),
(3,’wangliu’);
清空表数据–>truncate new;
********************************************************
四、解决新手答疑
1、出现乱码
解决:set names gbk;
2、sql语句可以分行打,如果发现写错,并且回车了,\c可以退出重打
***************************************************************
五、insert增数据详解
tee d:\1010.sql//把敲的sql语句及结果输出到一个sql文件里
1、创建一个班级薪资表
create table class(
id int primary key auto_increment,
sname varchar(10) not null default ”,
gender varchar(1) not null default ”,
company varchar(20) not null default ”,
salary decimal(6,2) not null default 0.00,
fanbu smallint not null default 0
)engine myisam charset utf8;
2、增加行
set names gbk;//有中文字要设置,不然会有乱码
insert into class
(id,sname,gender,company,salary,fanbu)
values
(1,’张三’,’男’,’百度’,8210.56,500);
**************************************************************
六、update和delete改删数据详解
改哪张表,哪几列,改什么值,还要指定行,不然很危险
update 表名
set
gender=’女’,
company=’千度’
where id=6;

删除就是一整行
delete from 表名 where expression
****************************************************************
七、select基本入门
select 某列 from 表名 where 某行
select * from 表格
***********************************************
八、建表过程与字符类型的意义
建表的过程就是声明字段的过程,也就是声明表头
知识点:列类型
存储同样的数据,不同的列类型,所占据的空间和效率是不一样的,这就是我们建表前声明列类型的意义,所以—-重点学列类型的存储范围与占据的字节关系
*****************************************************
九、深入剖析int列的存储范围与所占字节
三大列类型
1、数值型(整型tinyint、smallint、mediuint、int、bigint)(浮点型/定点型)
tinyint占1个字节,存储范围-128-127;0-255
占2个字节,0—2^16-1(0–65535)
-2^15—2^15-1(-32768—32767)
********************************************************************
十、(M)-unsigned-zerofill详解
什么时候用负的什么时候用正的?
int系列的声明时的参数
(M) unsigned zerofill
(M)与zerofill配合使用,不然没有意义
unsigned:无符号类型

补充:给表再加一个列
alter table class add score tinyint unsigned not null default 0;
************************************************************************
十一、浮点与定点列讲解
float(m,d),decimal(m,d)
m叫“精度”–>代表小数的总位数
d叫“标度”–>代表小数位
float(6,2)//-9999.99-9999.99
浮点能占多大空间呢?10^38
如果 m<=24是按4个字节存储否则是占8个字节
float(9,2)1234567.23–>1234567.25
decimal(9,2)1234567.23–>1234567.23
定点:是把整数部分和小数部分分开存储的,比float精确
***********************************************************************
十二、字符型列详解
char和varchar分别指定长型和变长型
concat连接字符串时,变长型会保留右边的空格,定长型则会丢失后面的空格
text文本类型:一般用于储存文章内容,新闻内容
blob:是二进制类型,用来存储图像音频等二进制信息
意义:二进制0-255都可能出现,防止字符集的问题,导致信息丢失
*********************************************************
十三、日期时间列详解
日期类型 date 0000-00-00 3个字节
时间类型 time 00:00:00 4个字节
日期时间类型 datetime 0000-00-00 00:00:00 8个字节
比较有意思的列
timestamp系统的当前时间 4个字节

year类型范围是1901-2155 1个字节还可以存0000年
**********************************************************
十四、网站建表实战及优化
做一个白领的交友网站,首先得做一张表,再做优化
Id int unsigned primary key auto_increment
姓名 char(20)//这个是定长型,放到另一张表上
性别 char(1)
生日 date
体重 tinyint
薪资 decimal
上次登陆 int unsigned
个人介绍 varchar(1500)//这个是变长型,放到另一张表里

create table member(
id int unsigned primary key auto_increment,
username char(20) not null default ”,
gender char(1) not null default ”,
birth date not null default ‘0000-00-00’,
weight tinyint unsigned not null default 0,
sarlary decimal(8,2) not null default 0.00,
lastlogin int unsigned not null default 0
)engine myisam charset utf8;
*****************************************************************
十五、表修改语法之列的增删改
增加列
alter table表名 add 列名 列参数 列声明//加的列在表的最后
alter table表名 add 列名 列参数 列声明 after 指定列//加的指定列的后面
alter table表名 add 列名 列参数 列声明 first//在表的第一列前加一列
删除列
alter table表名drop 列名
修改列类型
alter table表名 modify 列名 新的列参数 新的列声明
修改列名和列类型
altet table表名 change 旧列名 新列名 新的列参数 新的列声明
*********************************************************************
十六、ecshop安装与商品表分析
php在5.5以上建议装3.0的ecshop不然会有很多错
mysql5.5以下的提示要升级的要修改phpMyAdmin目录下libraries\common.inc.php
原代码: if (PMA_MYSQL_INT_VERSION < 50500)
改为: if (PMA_MYSQL_INT_VERSION < 50000)
查看ecshop后台,增加一个产品,修改其属性,到数据库看表good里的字段下值的变化
下面从数据库里查看good表的结构,导出来修改,做小练习

show create table goods;//查看建goods表的sql语句
1、goods_id
2、cat_id
3、goods_sn
4、goods_name
5、click_count
6、goods_number
7、market_price
8、shop_price
9、add_time
10、is_best
11、is_new
12、is_hot

CREATE TABLE `goods` (
`goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`cat_id` smallint(5) unsigned NOT NULL DEFAULT ‘0’,
`goods_sn` varchar(60) NOT NULL DEFAULT ”,
`goods_name` varchar(120) NOT NULL DEFAULT ”,
`click_count` int(10) unsigned NOT NULL DEFAULT ‘0’,
`goods_number` mediumint(8) unsigned NOT NULL DEFAULT ‘0’,
`market_price` decimal(10,2) unsigned NOT NULL DEFAULT ‘0.00’,
`shop_price` decimal(10,2) unsigned NOT NULL DEFAULT ‘0.00’,
`add_time` int(10) unsigned NOT NULL DEFAULT ‘0’,
`is_best` tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
`is_new` tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
`is_hot` tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

接下来ecshop里的商品内容导到这第表里,供练习用
insert into test.goods select goods_id,
cat_id,
goods_sn,
goods_name,
click_count,
goods_number,
market_price,
shop_price,
add_time,
is_best,
is_new,
is_hot
from gyshop.goods;
***************************************************************************
十七、sql查询表达式详解

1、查询主键是32的商品
select * from goods where goods_id=32;
2、查出不属于第3个栏目的所有商品
select * from goods where cat_id !=3;
3、查出本店价格高于3000元的商品
select goods_id,goods_name,shop_price from goods where shop_price>3000;
4、本店商品价格低于或等于100元的商品
select goods_name,shop_price,market_price from goods where shop_price<=100;
5、取出第4栏或第11栏的商品(不许用or)
select goods_id,cat_id,goods_name from goods where cat_id=4||cat_id=11;
select goods_id,cat_id,goods_name from goods where cat_id in (4,11);
6、商品价格在100到500之间的商品
select good_name,shop_price from goods where shop_price between 100 and 500;
7、取出栏目不是3且也不是4的商品
select goods_id,cat_id,goods_name from goods where cat_id not in (3,4);
select goods_id,cat_id,goods_name from goods where cat_id!=3 and cat_id!=4;
8、查出名称以诺基亚开头的商品
select goods_id,goods_name,goods_number from goods where goods_name like ‘诺基亚%’;
9、查出诺基亚NXX系列的手机
select goods_id,goods_name from goods where goods_name like ‘诺基亚N__%’;
************************************************************************************
十八、sql的查询模型(重要)
1、理解:把列看成变量,where后的表达式相当于if(exp)的exp
因为列是变量,那变量之间就可以运算
2、如:取出商品本店价和市场价的差值
select goods_name,market_price-shop_price from goods where 1;
3、这种列变量之间的运算结果叫‘广义投影’,可以起一个别名
select goods_name,(market_price-shop_price) as discount from goods where 1;
4、取出商品本店价和市场价的差值,且差值大于200
select goods_name,(market_price-shop_price) as discount from goods where (market_price-shop_price)>200;
5、问:括号里的内容可以用discount代替吗?
答:不可以,where是针对表来说的,表里面没有discount列,如果想用discount,只有用having;

6、面试题:如下表:
把num值在[20,29]之间的改为20
把num值在[30,39]之间的改为30

num
3
12
15
25
23
29
34
37
32
45
48
52
update mian set num=20 where num>=20 and num<=29;
update mian set num=30 where num>=30 and num<=40;
上面两个语句只写一条
update mian set num = floor(num/10)*10 where num between 20 and 39;
7、练习题
把goods表里商品名为‘诺基亚XXXX’改成’htcXXX’;
mysql> select goods_name,concat(‘htc’,substring(goods_name,4)) from goods where
goods_name like ‘诺基亚%’;
*******************************************************************************************
十九、sql之group分组及统计函数详解(注意group后面的字段和查询的字段可以相同,别的字段不行,只能是统计的函数,因为别的字段不能有代表它)
1、查最贵的商品
select max(shop_price) from goods;
2、查最便宜的商品
select min(shop_price) from goods;
3、查询商品发布最早的商品
select min(goods_id),goods_name from goods;
4、查询所有商品的总库存量
select sum(goods_number) from goods;
5、查询所有商品的平均价
select avg(shop_price) from goods;
6、本店一共有多少种商品
select count(*) from goods;
count(*)数所有行数,哪怕哪一行所有字段全为null也计算在内
count(某个字段)是读不为空的行数
用count(*),count(1)谁好?

答:对于myisam引擎的表,没有区别,这个引擎内部有一个计数器在维护着行数
innodb的表,用count(*)直接读行数,效率很低

以上五种函数,单独使用,意义不大,要和分组配合起来使用。
1、查询第3个栏目下,所有商品库存量之和
select sum(goods_number) from goods where cat_id=3;
2、查询每个栏目下的库存量
select cat_id,sum(goods_number) from goods group by cat_id;

看下面的语句对不对?
select goods_id,sum(goods_number) from goods where 1;(不对)
答:不符合mysql的标准,虽然在mysql里可以执行,但是语句是错误的。

3、查询每个栏目里商品的平均价格
select cat_id,avg(shop_price) from goods where 1 group by cat_id;
******************************************************************************************
二十、having筛选
1、查询每个商品积压的价钱
select goods_id,goods_number,shop_price,(goods_number*shop_price) from goods;
2、查询该店积压的总贷款
select sum(goods_number*shop_price) as sum from goods;
3、查询每个栏目下,积压贷款
select cat_id,sum(goods_number*shop_price) from goods group by cat_id;
4、查询积压货款超过1000元的栏目,以及该栏目积压的货款
select cat_id,sum(goods_number*shop_price) as k from goods group by cat_id having k>1000;
5、查询本店价比市场价省的钱,且筛选出省出200以上商品
select goods_id,shop_price,market_price,(market_price-shop_price) as discount from goods having discount>200;
6、创建一个学生成绩表
+——+———+——-+
| name | subject | score |
+——+———+——-+
| 张三 | 数学 | 90 |
| 张三 | 语文 | 50 |
| 张三 | 地理 | 40 |
| 李四 | 语文 | 55 |
| 李四 | 政治 | 45 |
| 王五 | 政治 | 30 |
+——+———+——-+
show create table stuscore;
CREATE TABLE `stuscore` (
`name` varchar(20) DEFAULT NULL,
`subject` varchar(20) DEFAULT NULL,
`score` tinyint(3) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
要求:查询出2门及2门以上不及格者的平均成绩
select name,avg(score) from stuscore group by name having count(score<60)>=2;

上面这条语句是错误的:
如果再添加一个人,有三个成绩且都优秀,该人的平均成绩也会显示出来
正确语句是: select name,avg(score) from stuscore group by name having sum(score<60)>=2;
优化下:select name,avg(score),sum(score<60) as gk from stuscore group by name having gk>=2;
+——+————+——+
| name | avg(score) | gk |
+——+————+——+
| 张三 | 60.0000 | 2 |
| 李四 | 50.0000 | 2 |
+——+————+——+

**************************************************************************************
二十一、综合练习
就上面的例子多层嵌套子查询,比较复杂,不建议用,效率低
*****************************************************
二十二、order by与limit详解
1、在php中,获取不同栏目的商品,在地址栏上获取$_GET[‘id’]
select goods_id,cat_id,goods_name from goods where cat_id=5;
这里引出来的两个函数是针对最终结果集的,即order by 放在where/group/having之后,顺序不能乱
当最终结果集出来后,可以进行排序
2、取出第三栏目下的商品,并按价格由高到低排降序
select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 or
der by shop_price desc;
如果是升序把desc改为asc
3、按发布时间升序,即发布早的,时间戳小的靠前
select goods_id,goods_name,add_time from goods order by add_time asc;
4、按栏目升序排列,同一栏目下的商品再按价格降序排列(再一次排序用逗号隔开)
select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;
5、limit限制条目
limit[offset,]N //第一个参数是可选的
offset:偏移量
n:取出条目
offset如果不写,则相当于limit0,N
6、查询本店价格最高的前三名
思路先价格倒序排列,然后取三个条目
select goods_id,goods_name,shop_price from goods order by shop_price desc limit 0,3;
7、查询本商店价格最高的一行
select goods_id,goods_name,shop_price from goods order by shop_price desc limit 0,1;
8、查询本店商品价格最高的从第三名排列
select goods_id,goods_name,shop_price,count(*) as all from goods order by shop_price desc limit 2,all;??//不知道怎么写
9、查询出每个栏目下id号最大(最新)的商品
select goods_id,cat_id,goods_name from goods order by goods_id desc group by cat_id;//错误
*****************************************************************************************
二十三、子句查询陷阱
分析上面例子
选按id从高到低排
select goods_id,cat_id,goods_name from goods order by goods_id desc;
再把上面查询到的表当临时表,再按栏目排序
select goods_id,cat_id,goods_name from (select goods_id,cat_id,goods_name from goods order by goods_id desc) as temp group by cat_id;
******************************************************************************
二十四、where型子查询
1、查出本店最新的即goods_id最大
mysql> select goods_id,goods_name from goods order by goods_id desc limit 1;
2、查出本店最新的即goods_id最大,要求不能用排序写
select goods_id,goods_name from goods having goods_id=(select max(goods_id) from goods);
select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods);
二十五、from型子查询
查询结果集可以在结构上可以当成表看
即内层sql的结果当成一张表,再供外层sql再查询就可以了
1、查询每一个栏目下,goods_id最大的商品
select goods_id,goods_name,cat_id from (select goods_id,cat_id,goods_name from goods order by goods_id desc) as tmp group by cat_id;
二十六、exists型子查询
是指把外层的sql的结果,拿到内层sql去测试,如果内层sql成立,则该取出
1、exist子查询:查出有商品的栏目
2、再建一张栏目表,create table category(
cat_id int auto_increment primary key,
cat_name varchar(20) not null default ”
)engine myisam charset utf8;
3、然后把shop下的表category的内容复制到test.category里
insert into table test.category select cat_id,cat_name from shop.category;

思路:现在有两张表goods和category ,要在category表里把没有商品的栏目去掉,也就是根据goods表里的goods_id和category的goods_id相等来去掉没有商品的栏目
专业的理解:exists后的条件如果为真,那么该行的数据显示出来,也就是外层的sql的要显示的结果拿到内层去测试,如果成功,那么把该行取出
select * from category where exists (select * from goods where cat_id=category.cat_id);
********************************************************************************************
二十七、奇怪的null
建表时,列后面not null default ”,fefault 0 ,这是什么意思?
答:就是让这个列的值不为null.如果某个列确实没填值,也有默认值,也不为Null
为什么不希望让列的值为Null呢?
因为不方便比较,它是一种类型,比较时只能用专门的is null 或者is not null
因此在建表时,not null default 0;
建一张表test9
create table test9(
sname varchar(20)
)engine myisam charset utf8;
mysql> insert into test9 values
-> (‘张d三’),
-> (‘李四’),
-> (‘null’);
+——-+
| sname |
+——-+
| 张d三 |
| 李四 |
| null |
+——-+
mysql> select * from test9 where sname=null;
Empty set (0.00 sec)

mysql> select * from test9 where sname!=null;
Empty set (0.00 sec)
为什么上面两条语句都查不到内容呢?
null要用特殊的查询语句
is null或者是is not null
********************************************************************************************
二十八、 新手1+n模式查询报价表

发表评论

电子邮件地址不会被公开。 必填项已用*标注