SQL语言操作
创建表:
create table 表名(
字段名 类型 约束,
字段名 类型 约束
...
)
删除表:
格式一:drop table 表名
格式二:drop table if exists 表名
逻辑删除:
逻辑删除:对于重要的数据,不能轻易执行delete语句进行删除,一旦删除,数据无法恢复,这时可以进行逻辑删除。
1、给表添加字段,代表数据是否删除,一般起名isdelete,0代表未删除,1代表删除,默认值为0
2、当要删除某条数据时,只需要设置这条数据的isdelete字段为1
3、以后在查询数据时,只查询出isdelete为0的数据
查询:
条件select 字段1,字段2... from 表名 where 条件;
where后面支持多种运算符,进行条件的处理
比较运算
逻辑运算
模糊查询
范围查询
空判断
比较运算符:
等于: =
大于: >
大于等于: >=
小于: <
小于等于: <=
不等于: != 或 <>
逻辑运算符
and
or
not
模糊查询:
like
%表示任意多个任意字符
_表示一个任意字符
范围查询:
in表示在一个非连续的范围内
between ... and ...表示在一个连续的范围内
空判断:
注意:null与''是不同的
判空is null
判非空is not null
排序:
select * from 表名
order by 列1 asc|desc,列2 asc|desc,...
将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
默认按照列值从小到大排列
asc从小到大排列,即升序
desc从大到小排序,即降序
聚合函数:
为了快速得到统计数据,经常会用到如下5个聚合函数
count(*)表示计算总行数,括号中写星与列名,结果是相同的
聚合函数不能在 where 中使用
max(列)表示求此列的最大值
min(列)表示求此列的最小值
sum(列)表示求此列的和
avg(列)表示求此列的平均值
分组:
按照字段分组,表示此字段相同的数据会被放到一个组中
分组后,分组的依据列会显示在结果集中,其他列不会显示在结果集中
可以对分组后的数据进行统计,做聚合运算
语法:
select 列1,列2,聚合... from 表名 group by 列1,列2...
分组后的数据筛选
语法:
select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...
having后面的条件运算符与where的相同
对比where与having
where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
having是对group by的结果进行筛选
分页:
当数据量过大时,在一页中查看数据是一件非常麻烦的事情
语法
select * from 表名
limit start,count
从start开始,获取count条数据
start索引从0开始
分页
已知:每页显示m条数据,求:显示第n页的数据
求总页数
查询总条数p1
使用p1除以m得到p2
如果整除则p2为总数页
如果不整除则p2+1为总页数
连接查询:
当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回
等值连接查询:查询的结果为两个表匹配到的数据
方式一
select * from 表1,表2 where 表1.列=表2.列
方式二(又称内连接)
select * from 表1
inner join 表2 on 表1.列=表2.列
左连接查询:查询的结果为两个表匹配到的数据加左表特有的数据,对于右表中不存在的数据使用null填充
select * from 表1
left join 表2 on 表1.列=表2.列
右连接查询:查询的结果为两个表匹配到的数据加右表特有的数据,对于左表中不存在的数据使用null填充
select * from 表1
right join 表2 on 表1.列=表2.列
自关联:
省市区自关联查询
子查询:
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
主查询
主要查询的对象,第一条 select 语句
主查询和子查询的关系:
子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句
子查询分类:
标量子查询: 子查询返回的结果是一个数据(一行一列)
列子查询: 返回的结果是一列(一列多行)
行子查询: 返回的结果是一行(一行多列)
表级子查询: 返回的结果是多行多列
子查询中特定关键字使用
in 范围
格式: 主查询 where 条件 in (列子查询)
any | some 任意一个
格式: 主查询 where 列 = any (列子查询)
在条件查询的结果中匹配任意一个即可,等价于 in
all
格式: 主查询 where 列 = all(列子查询) : 等于里面所有
格式: 主查询 where 列 <>all(列子查询) : 不等一其中所有
7.数据库设计
E-R模型
E-R模型的基本元素是:实体、联系和属性
E表示entry,实体:一个数据对象,描述具有相同特征的事物
R表示relationship,联系:表示一个或多个实体之间的关联关系,关系的类型包括包括一对一、一对多、多对多
属性:实体的某一特性称为属性
关系也是一种数据,需要通过一个字段存储在表中:
1、实体A对实体B为1对1,则在表A或表B中创建一个字段,存储另一个表的主键值
2、实体A对实体B为1对多:在表B中创建一个字段,存储表A的主键值
3、实体A对实体B为多对多:新建一张表C,这个表只有两个字段,一个用于存储A的主键值,一个用于存储B的主键值
想一想:举些例子,满足一对一、一对多、多对多的对应关系
8.命令行操作mysql
1、进入mysql的bin目录
cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin
2、连接mysql
mysql -uroot -p
数据库:
查看所有数据库
show databases;
使用数据库
use 数据库名;
查看当前使用的数据库
select database();
创建数据库
create database 数据库名 charset=utf8;
例:
create database ceshi charset=utf8;
删除数据库
drop database 数据库名;
例:
drop database ceshi;
数据表:
查看当前数据库中所有表
show tables;
查看表结构
desc 表名;
查看表的创建语句
show create table 表名;
例:
show create table students;
备份:
以管理员身份运行cmd程序
运行mysqldump命令
cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin
mysqldump –uroot –p 数据库名 > ceshi.sql
# 按提示输入mysql的密码
恢复:
先创建新的数据库
mysql -uroot –p 新数据库名 < ceshi.sql
# 根据提示输入mysql密码
函数:
字符串函数
拼接字符串concat(str1,str2...)
select concat(12,34,'ab');
包含字符个数length(str)
select length('abc');
截取字符串
left(str,len)返回字符串str的左端len个字符
right(str,len)返回字符串str的右端len个字符
substring(str,pos,len)返回字符串str的位置pos起len个字符
select substring('abc123',2,3);
去除空格
ltrim(str)返回删除了左空格的字符串str
rtrim(str)返回删除了右空格的字符串str
select ltrim(' bar ');
大小写转换,函数如下
lower(str)
upper(str)
select lower('aBcD');
数学函数:
求四舍五入值round(n,d),n表示原数,d表示小数位置,默认为0
select round(1.6);
求x的y次幂pow(x,y)
select pow(2,3);
获取圆周率PI()
select PI();
随机数rand(),值为0-1.0的浮点数
select rand();
日期时间函数:
当前日期current_date()
select current_date();
当前时间current_time()
select current_time();
当前日期时间now()
select now();
日期格式化date_format(date,format)
参数format可选值如下
%Y 获取年,返回完整年份
%y 获取年,返回简写年份
%m 获取月,返回月份
%d 获取日,返回天值
%H 获取时,返回24进制的小时数
%h 获取时,返回12进制的小时数
%i 获取分,返回分钟数
%s 获取秒,返回秒数
例:将使用-拼接的日期转换为使用空格拼接
select date_format('2016-12-21','%Y %m %d');
流程控制:
case语法:等值判断
说明:当值等于某个比较值的时候,对应的结果会被返回;如果所有的比较值都不相等则返回else的结果;如果没有else并且所有比较值都不相等则返回null
case 值 when 比较值1 then 结果1 when 比较值2 then 结果2 ... else 结果 end
例:
select case 1 when 1 then 'one' when 2 then 'two' else 'zero' end as result;
自定义函数:
创建
语法如下
delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter ;
说明:delimiter用于设置分割符,默认为分号
在“sql语句”部分编写的语句需要以分号结尾,此时回车会直接执行,所以要创建存储过程前需要指定其它符号作为分割符,此处使用//,也可以使用其它字符
示例
要求:创建函数py_trim,用于删除字符串左右两侧的空格
step1:设置分割符
delimiter $$
step2:创建函数
create function my_trim(str varchar(100)) returns varchar(100)
begin
return ltrim(rtrim(str));
end
$$
step3:还原分割符
delimiter ;
使用自定义函数
select ' abc ',my_trim(' abc ')
9.存储过程
创建
语法如下
delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//
delimiter ;
说明:delimiter用于设置分割符,默认为分号
在“sql语句”部分编写的语句需要以分号结尾,此时回车会直接执行,所以要创建存储过程前需要指定其它符号作为分割符,此处使用//,也可以使用其它字符
调用:
语法如下
call 存储过程(参数列表);
调用存储过程proc_stu
call proc_stu();
存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合.
存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 sql, 不需要重复编译
减少网络交互,减少网络访问流量
视图:
对于复杂的查询,在多个地方被使用,如果需求发生了改变,需要更改sql语句,则需要在多个地方进行修改,维护起来非常麻烦
解决:定义视图
视图本质就是对查询的封装
定义视图,建议以v_开头
create view 视图名称 as select语句;
例:创建视图,查询学生对应的成绩信息
create view v_stu_score_course as
select
stu.*,cs.courseNo,cs.name courseName,sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on cs.courseNo = sc.courseNo
查看视图:查看表会将所有的视图也列出来
show tables;
删除视图
drop view 视图名称;
例:
drop view v_stu_score_course;
使用:视图的用途就是查询
select * from v_stu_score_course;
事务:
为什么要有事务
事务广泛的运用于订单系统、银行系统等多种场景
例如:A用户和B用户是银行的储户,现在A要给B转账500元,那么需要做以下几件事:
检查A的账户余额>500元;
A 账户中扣除500元;
B 账户中增加500元;
正常的流程走下来,A账户扣了500,B账户加了500,皆大欢喜。那如果A账户扣了钱之后,系统出故障了呢?A白白损失了500,而B也没有收到本该属于他的500。以上的案例中,隐藏着一个前提条件:A扣钱和B加钱,要么同时成功,要么同时失败。事务的需求就在于此
所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。例如,银行转帐工作:从一个帐号扣款并使另一个帐号增款,这两个操作要么都执行,要么都不执行。所以,应该把他们看成一个事务。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性
事务命令
要求:表的引擎类型必须是innodb类型才可以使用事务,这是mysql表的默认引擎
查看表的创建语句,可以看到engine=innodb
show create table students;
修改数据的命令会触发事务,包括insert、update、delete
开启事务,命令如下:
开启事务后执行修改命令,变更会维护到本地缓存中,而不维护到物理表中
begin;
提交事务,命令如下
将缓存中的数据变更维护到物理表中
commit;
回滚事务,命令如下:
放弃缓存中变更的数据
rollback;
提交:
为了演示效果,需要打开两个命令行窗口,使用同一个数据库,操作同一张表
step1:连接
命令行1:查询学生信息
select * from students;
step2:增加数据
命令行2:开启事务,插入数据
begin;
insert into students(studentNo,name) values ('013','我是新来的');
命令行2:查询数据,此时有新增的数据
select * from students;
step3:查询
命令行1:查询数据,发现并没有新增的数据
select * from students;
step4:提交
命令行2:完成提交
commit;
step5:查询
命令行1:查询,发现有新增的数据
select * from students;
回滚:
为了演示效果,需要打开两个命令行窗口,使用同一个数据库,操作同一张表
step1:连接
命令行1
select * from students;
step2:增加数据
命令行2:开启事务,插入数据
begin;
insert into students(studentNo,name) values ('014','又来一个');
命令行2:查询数据,此时有新增的数据
select * from students;
step3:查询
命令行1:查询数据,发现并没有新增的数据
select * from students;
step4:回滚
命令行2:完成回滚
rollback;
step5:查询
命令行1:查询数据,发现没有新增的数据
select * from students;
索引:
思考:在图书馆中是如何找到一本书的?
一般的应用系统对比数据库的读写比例在10:1左右,而且插入操作和更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重
当数据库中数据量很大时,查找数据会变得很慢
优化方案:索引
语法
查看索引
show index from 表名;
创建索引
方式一:建表时创建索引
create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key (age)
);
方式二:对于已经存在的表,添加索引
如果指定字段是字符串,需要指定长度,建议长度与定义字段时的长度一致
字段类型如果不是字符串,可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))
例:
create index age_index on create_index(age);
create index name_index on create_index(name(10));
删除索引:
drop index 索引名称 on 表名;
示例
创建测试表testindex
create table test_index(title varchar(10));
向表中加入十万条数据
创建存储过程proc_test,在存储过程中实现插入数据的操作
step1:定义分割符
delimiter //
step2:定义存储过程
create procedure proc_test()
begin
declare i int default 0;
while i<100000 do
insert into test_index(title) values(concat('test',i));
set i=i+1;
end while;
end
//
step3:还原分割符
delimiter ;
执行存储过程proc_test
call proc_test();
查询
开启运行时间监测:
set profiling=1;
查找第1万条数据test10000
select * from test_index where title='test10000';
查看执行的时间:
show profiles;
为表title_index的title列创建索引:
create index title_index on test_index(title(10));
执行查询语句:
select * from test_index where title='test10000';
再次查看执行的时间
show profiles;
缺点
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE,因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
但是,在互联网应用中,查询的语句远远大于增删改的语句,甚至可以占到80%~90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引
分析查询
explain
select * from test_index where title='test10000'
外键:
外键foreign key
如果一个实体的某个字段指向另一个实体的主键,就称为外键。被指向的实体,称之为主实体(主表),也叫父实体(父表)。负责指向的实体,称之为从实体(从表),也叫子实体(子表)
对关系字段进行约束,当为从表中的关系字段填写值时,会到关联的主表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并报错
语法
查看外键
show create table 表名
设置外键约束
方式一:创建数据表的时候设置外键约束
注意: goods 中的 cate_id 的类型一定要和 goods_cates 表中的 cate_id 类型一致
create table goods_fk(
id int unsigned primary key auto_increment,
name varchar(150),
cate_id int unsigned,
brand_id int unsigned,
price decimal(10,3) default 0,
is_show bit default 1,
is_saleoff bit default 0,
foreign key(cate_id) references goods_cates(cate_id),
foreign key(brand_id) references goods_brands(brand_id)
);
foreign key(自己的字段) references 主表(主表字段)
方式二:对于已经存在的数据表设置外键约束
alter table 从表名 add foreign key (从表字段) references 主表(主表字段);
alter table goods add foreign key (cate_id) references goods_cates(cate_id);
alter table goods add foreign key (brand_id) references goods_brands(brand_id);
删除外键
-- 需要先获取外键约束名称
show create table goods;
-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop foreign key goods_ibfk_2;
从表中插入数据
insert into goods_fk (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印机','20','20','1849'); -- 插入不成功,因为主表中没有20这个值
insert into goods_fk (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印机','1','1','1849'); -- 可以插入成功
在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率
修改密码:
修改密码
使用root登录,修改mysql数据库的user表
使用password()函数进行密码加密
注意修改完成后需要刷新权限
use mysql;
update user set password=password('新密码') where user='用户名';
例:
update user set password=password('123') where user='root';
刷新权限:flush privileges;
忘记 root 账户密码怎么办
1、配置mysql登录时不需要密码,修改配置文件
Centos中:配置文件位置为/data/server/mysql/my.cnf
Windows中:配置文件位置为C:\Program Files (x86)\MySQL\MySQL Server 5.1\my.ini
修改,找到mysqld,在它的下一行,添加skip-grant-tables
[mysqld]
skip-grant-tables
2、重启mysql,免密码登录,修改mysql数据库的user表
use mysql;
update user set password=password('新密码') where user='用户名';
例:
update user set password=password('123') where user='root';
刷新权限:flush privileges;
3、还原配置文件,把刚才添加的skip-grant-tables删除,重启
10.mysql操作日志
1、mysql操作日志
general-log:能记录mysql所有的操作日志(包括增删改查),不过他会耗费数据库5%-10%的性能,所以一般没特别需要时不打开此功能,一般在查找问题时才打开,完成后及时关闭
1、客户端连接mysql
2、查询日志是否开启:show variables like 'general%';
3、打开日志:set global general_log=1
4、不需要使用日志时关闭日志:set global general_log=0
5、打开对应目录下的日志文件,操作mysql后,观察文件变化
centos中查看文件自动刷新:tail -f 文件名
11.浏览器、网站程序、数据库之间的关系
2、浏览器、网站程序、数据库之间的关系
1、拷贝test.php到centos中
2、把test.php移动到目录/data/server/nginx/html/iwebshop
su
cd Desktop
mv test.php /data/server/nginx/html/iwebshop
-- 开启nginx服务
cd /data/server/php/sbin
./php-fpm
-- 开启php服务
cd /data/server/nginx/sbin
./nginx
3、打开浏览器,访问 http://iwebshop.itcast.com/test.php
4、修改test.php
cd /data/server/nginx/html/iwebshop
gedit test.php
把"没有搜索到结果:"引号里面的内容改一下,保存
5、打开浏览器,访问 http://iwebshop.itcast.com/test.php