前言
为啥学习MySQL
呢?因为MySQL
是最风行的关系型数据库管理系统之一,在web
利用方面,MySQL
是最好的软件。MySQL
所应用的sql
语言是用于拜访数据库的最罕用标准化语言。
释怀,读这期内容的敌人们都适宜:网站开发,软件开发或者爱好者。
1.MySQL的入门
什么是数据库呢?
- 数据库,它是依照数据结构来组织,存储和治理数据的仓库。
- 数据库管理系统, 指数据库系统中对数据进行治理的软件系统。
让我来整顿一张思维导图:
细节把握:
- 装置配置,常用命令,操作数据库;
- 整型与浮点型,日期工夫型与字符型;
- 创立与查看数据库表,批改数据库表,删除数据库表;
- 非空束缚,主键束缚,惟一束缚,默认束缚,外键束缚;
- 管理工具:
MySQL Workbench,SQLyog
; - 单表数据记录的插入与主动编号,单表数据记录的更新,单表数据记录的删除,单表数据记录的查问,对查问后果进行分组,对查问后果进行排序,通过limit语句限度查问记录的数量;
- mysql的运算符,数值函数,字符函数,日期工夫函数,聚合函数,信息函数与加密函数;
- 应用比拟运算符引发的子查问,插入记录时应用的子查问
- 多表连贯,内连贯,外连贯,自连贯,多表更新,多表删除
- 创立,应用自定义函数
- 创立存储过程,应用存储过程
mysql官网:
安装包下载:(安装操作)
点击装置:
产品配置的操作:
关上服务框用win+r
,输出services.msc
2. mysql目录构造
bin
目录:用于存储一些可执行文件include
目录:用于存储蕴含的一些头文件lib
目录:用于存储一些库文件share
目录:用于存储错误信息,字符集文件等data
目录:用于搁置一些日志文件以及数据库my.ini
文件:数据库的配置文件
启动与进行:
mysql
参数:
参数 | 形容 |
---|---|
-u | 用户名 |
-p | 明码 |
-V | 输入版本信息并且退出 |
-h | 主机地址 |
3.常用命令
批改用户明码的命令:
mysqladmin 命令用于批改用户明码mysqladmin 命令格局:mysqladmin -u用户名 -p旧明码 password新密码
显示数据库的命令
show databases;
应用数据库的命令
use 数据库的名称
显示以后连贯的信息
- 显示以后连贯的数据库:
select database();
- 显示以后服务器版本:
select version();
- 显示以后日期工夫:
select now();
- 显示以后用户:
select user();
4.操作数据库(创立,批改,删除)
创立数据库SQL
:
create database [if not exists] db_name[default] character set [=] charset_name
create database database_name;
批改数据库的语法格局:
alter database db_name [default] character set [=] charset_name
删除数据库语法格局:
drop database [if exitsts] db_name;
5.数据库-数据类型
理解数据类型:(借助图书管理系统)
图书类别表:
类别编号(category_id) 类别名称(category) 父类别(parent_id)1 计算机 02 医学 0
图书信息表:
图书编号(book_id) 类别编号(book_category_id) 书名(book_name) 作者(author) 价格(price) 出版社(press) 出版工夫(pubdate) 库存(store)
借阅信息表:
图书编号(book_id) 身份证号(card_id) 借出日期(borrow_date) 偿还日期(return_date) 是否偿还(status)
读者信息表
身份证号(card_id) 姓名(name) 性别(sex) 年龄(age) 联系电话(tel) 余额(balance)
数据类型:
整型:TINYINT
-1字节 SMALLINT
-2字节 MEDIUMINT
-3字节 INT
-4字节 BIGINT
-8字节
浮点数类型和定点数类型:
float-4个字节double-8个字节decimal
日期工夫类型:
字符型:
6.数据库表构造的操作
- 创立和查看数据表
创立数据表:create tablecreate table <表名>( 列名1 数据类型[列级别约束条件][默认值], 列名2 数据类型[列级别约束条件][默认值], ... [表级别约束条件]);
- 查看数据库表:
show tables [from db_name];
- 查看数据表根本构造:
show columns from tbl_name;describe <表名> /DESC<表名>
show create table tbl_name;
- 批改数据库表
增加列:
alter table <表名> add <新列名> <数据类型> [ 约束条件 ] [first | after 已存在列名];
批改列名:
alter table <表名> change <旧列名> <新列名> <新数据类型>;
批改列的数据类型:
alter table <表名> MODIFY <列名> <数据类型>
批改列的排列地位
alter table<表名>MODIFY <列1> <数据类型> FIRST|AFTER<列2>
删除列:
alter table <表名> drop <列名>;
批改表名:
alter table <旧表名> RENAME [TO] <新表名>;
- 删除数据库表
drop table [if exists] 表1,表2,...表n;
查看表分区
创立表分区:应用partition by
类型(字段)
应用values less than
操作符定义分区
create table bookinfo( book_id int, book_name varchar(20))partition by range(book_id)( partition p1 values less than(20101010), partition p3 values less than MAXVALUE);
7.子查问
select price from bookinfo where book_id = 20101010;select * from readerinfo;update readerinfo set balance = balance-(select price from bookinfo where book_id = 20101010) * 0.05 where card_id = '2323232342sxxxxx';
什么是子查问呢?
它是指嵌套在其余sql
语句内的查问语句。
select * from table1 where col1 = (select col2 from table2);
insert into bookcategory(category,parent_id)values('x',2),('y',2);insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)values(45245244, 6, 'x', '1,2,3 等', 115, '出版社', '2020-06-01',10),(45342545, 6, 'y', '1, 2',27.8, '出版社', '2020-07-01', 5);update readerinfo set balance = 500 where card_id = '683246';insert into borrowinfo(book_id,card_id,borrow_date,return_date,status)values(35452455,'5724154','2020-10-10','2020-11-10','否');
查问借阅信息表, 显示借 xx这本书的借阅记录
select * from borrowinfo where book_id = (select book_id from bookinfo where book_name = 'xx');
查问图书信息表, 显示图书价格小于图书平均价格的所有图书信息
select * from bookinfo where price < (select round(avg(price),2) from bookinfo);
查问图书信息表,显示图书类别不是’数据库’的所有图书信息
select * from bookinfo where book_category_id<>(select category_id from bookcategory where category = '数据库');
查问图书信息表,显示图书类别为’计算机’的所有图书信息
select * from bookcategory;select * from bookinfo where book_category_id = ANY(select category_id from bookcategory where parent_id = 1);select * from bookinfo where price > ANY (select price from bookinfo where book_category_id =4); select * from bookinfo where price > ALL (select price from bookinfo where book_category_id =4);
查问图书信息表,显示图书类别为’2’的所有图书信息
in 前面的子查问返回一个数据列,等于数据列里的任意一个值都是满足条件的select * from bookinfo where book_category_id in (select category_id from bookcategory where parent_id = 2);select * from bookinfo where book_category_id = any (select category_id from bookcategory where parent_id = 2);
查看图书类别表中是否有’y’的类别,如果有,则查看图书信息表
select * from bookinfo where exists (select category_id from bookcategory where category='y');select * from bookinfo where exists (select category_id from bookcategory where category='x');
insert into select
语句从一个表复制数据,而后把数据插入到一个已存在的表中。
insert into table2 select * from table1;
须要创立一张罚款记录信息表,蕴含如下信息:图书编号、身份证号、应还日期、理论还书日期,罚款金额
记录来源于借阅信息表超出还书工夫还未还书的读者
create table readerfee( book_id int, card_id char(18), return_date date, actual_return_date date, book_fee decimal(7,3), primary key(book_id,card_id));select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = '否';insert into readerfee(book_id,card_id,return_date) select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = '否';select * from readerfee;
身份证号为5461xxxxxxx的读者将超限的图书20201101偿还,依据形容实现如下需要:
- 更新借阅信息表,将借阅状态(status)更新为‘是’。
- 更新罚款记录信息表,更新理论还书日期和罚款金额,罚款金额为每超出一天扣0.2元。
update borrowinfo set status = '是' where book_id = 20201101 and card_id = '5461xxxxxxx';select * from borrowinfo;update readerfee set actual_return_date=sysdate(), book_fee=datediff(sysdate(),return_date)*0.2 where book_id = 20201101 and card_id = '5461xxxxxxx';select * from readerfee;
8.mysql的束缚
它事一种限度,通过对表的行或列的数据做出限度,来确保表的数据的完整性,唯一性。
表构造:
图书(图书编号book_id
,类别编号book_category_id
,书名book_name
,作者author
)
在mysql
中罕用的几种束缚类型:
束缚类型 | 非空束缚 | 主键束缚 | 惟一束缚 | 默认束缚 | 外键束缚 |
---|---|---|---|---|---|
关键字 | not null | primary key | unique | default | foreign key |
图书信息表:
(图书编号book_id
,类别编号book_category_id
,书名book_name
,作者author
,价格price
,出版社press
,出版工夫pubdate
,库存store
)
图书类别表:
(类别编号category_id
- 主键,类别名称category
- 惟一,父类别parent_id
-非空)
读者信息表:
(身份证号card_id
,姓名name
,性别sex
,年龄age
,联系电话tel
,余额balance
)
借阅信息表:
(图书编号book_id
,身份证号card_id
,借出日期borrow_date
,偿还日期return_date
,是否偿还status
)
非空束缚
null
字段值能够为空
not null
字段值禁止为空
非空束缚
非空束缚指字段的值不能为空。对于应用了非空束缚的字段如果用户在增加数据时,没有指定值,数据库系统会报错。
列名 数据类型 not null
创立表时增加非空束缚
create table bookinfo( book_id int, book_name varchar(20) not null);
删除非空束缚
alter table bookinfo modify book_name varchar(20);
通过批改表增加非空束缚
alter table bookinfo modify book_name varchar(20) not null;
主键束缚
主键束缚:要求主键列的数据惟一,并且不容许为空,主键可能惟一地标识表中的一条记录。
主键的类型:
主键分为单字段主键和多字段联结主键
单字段主键:是由一个字段组成
在定义列的同时指定主键列名 数据类型 primary key;在列定义的后边指定主键[constraint<束缚名>] primary key(列名);
创立表时增加主键束缚
create table bookinfo( book_id int primary key, book_name varchar(20) not null);
create table bookinfo( book_id int, book_name varchar(20) not null, constraint pk_id primary key(book_id));
删除主键束缚
ALTER TABLE bookinfo DROP PRIMARY KEY;
通过批改表的形式增加主键束缚
ALTER TABLE bookinfo ADD PRIMARY KEY(book_id);
多字段联结主键,复合主键
主键有多个字段联结组成。primary key(字段1,字段2,...字段n);
create table borrowinfo(book_id int,card_id char(18),primary key(book_id,card_id));
通过批改表为列增加主键
create table bookinfo( book_id int, book_name varchar(20) not null);alter table bookinfo modify book_id int primary key;alter table bookinfo add primary key(book_id);alter table bookinfo add constraint pk_id primary key(book_id);
惟一束缚
惟一束缚要求该列惟一,容许为空,惟一束缚能够确保一列或者几列不呈现反复值。
语法规定:
列名 数据类型 unique[constraint <束缚名>] unique(<列名>)
创立表时增加惟一束缚
CREATE TABLE bookinfo( book_id INT PRIMARY KEY, book_name VARCHAR(20) NOT NULL UNIQUE );或:create table bookinfo(book_id int primary key,book_name varchar(20) not null,constraint uk_bname unique(book_name));
通过批改表的形式增加惟一束缚
alter table bookinfo modify book_name varchar(20) unique;ALTER TABLE bookinfo ADD UNIQUE(book_name);alter table bookinfo add constraint uk_bname unique(book_name);
删除惟一束缚
ALTER TABLE book_info DROP KEY uk_bname;ALTER TABLE book_info DROP INDEX uk_bname;
惟一束缚和主键束缚的区别
- 一个表中能够有多个
unique
申明,但只能有一个primary key
申明 - 申明为
primary key
的列不容许有空值 - 申明为
unique
的列容许空值
默认束缚
默认束缚是指某列的默认值
列名 数据类型 default 默认值
创立表时增加默认束缚
CREATE TABLE bookinfo( book_id INT PRIMARY KEY, press VARCHAR(20) DEFAULT '出版社');
通过批改表的形式增加默认束缚
ALTER TABLE bookinfo ALTER COLUMN press SET DEFAULT '出版社';alter table bookinfo modify press varchar(10) default '出版社';
删除默认束缚
alter table bookinfo modify press varchar(20);ALTER TABLE bookinfo ALTER COLUMN press DROP DEFAULT;
外键束缚
外键是用来在两个表的数据之间建设链接,能够是一列或者多列,一个表能够有一个或者多个外键。
外键对应的是参照完整性,一个表的外键能够为空值,若不为空值,则每一个外键必须等于另一个表中主键的某个值。
作用:保持数据的一致性,完整性。
创立表时增加外键束缚
图书类别表(父表)CREATE TABLE bookcategory( category_id INT PRIMARY KEY, category VARCHAR(20), parent_id INT);图书信息表(子表)CREATE TABLE bookinfo( book_id INT PRIMARY KEY, book_category_id INT, CONSTRAINT fk_cid FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id));
通过批改表的形式增加外键束缚
ALTER TABLE bookinfo ADD FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id);
删除外键束缚
ALTER TABLE bookinfo DROP FOREIGN KEY fk_cid;
外键束缚的参照操作
cascade
,从父表删除或更新且主动删除或更新子表中匹配的行
create table bookinfo( book_id int primary key, book_category_id int, constraint fk_cid foreign key (book_category_id) references bookcategory(category_id) on delete cascade);
创立图书管理系统表
图书类别表
create table bookcategory(category_id int primary key,category varchar(20) not null unique,parent_id int not null);
图书信息表
create table bookinfo(book_id int primary key,book_category_id int,book_name varchar(20) not null unique,author varchar(20) not null,price float(5,2) not null,press varchar(20) default '机械工业出版社',pubdate date not null,store int not null,constraint fk_bcid foreign key(book_category_id) references bookcategory(category_id));
读者信息表
create table readerinfo(card_id char(18) primary key,name varchar(20) not null,sex enum('男','女','窃密') default '窃密',age tinyint,tel char(11) not null,balance decimal(7,3) default 200);
借阅信息表
create table borrowinfo(book_id int,card_id char(18),borrow_date date not null,return_date date not null,status char(11) not null,primary key(book_id,card_id));
9.数据库表记录的操作
单表数据记录的插入
语法格局:
insert into table_name(column_list) values(value_list);
为表的所有列插入数据
insert into bookcategory(category_id,category,parent_id)values(1,'x',0);insert into bookcategory values(2,'y',0);
为表的指定列插入数据
insert into readerinfo(card_id,name,tel)values('4562135465','张飞','4651354651');
同时插入多条记录
insert into bookcategory(category_id,category,parent_id)values(3,'x',1),(4,'y',1),(5,'z',2);
将查问后果插入的表中
insert into bookcategory select * from test where id>5;
主动减少
设置表的属性值主动减少:
列名 数据类型 auto_increment
创立表时增加自增列
create table bookcategory_tmp( category_id int primary key auto_increment, category varchar(20) not null unique, parent_id int not null)auto_increment=5;
测试自增列
insert into bookcategory_tmp(category,parent_id)values('dadaqianduan',0);
去掉自增列
alter table bookcategory_tmp modify category_id int;
增加自增列
alter table bookcategory_tmp modify category_id int auto_increment;
批改自增列的起始值
alter table bookcategory_tmp auto_increment = 15;insert into bookcategory_tmp(category,parent_id)values('文学',0);
删除图书信息表的外键
alter table bookinfo drop foreign key fk_bcid;
为图书类别表增加主动编号的性能
alter table bookcategory modify category_id int auto_increment;
复原关联
alter table bookinfo add constraint fk_bcid foreign key(book_category_id)references bookcategory(category_id);
单表数据记录的更新
向借阅信息表插入一条借阅信息
insert into borrowinfo(book_id,card_id,borrow_date,return_date,status)values(20202010,46516874,'2020-11-29','2020-12-29','否');
更新读者信息表中的余额
查看书的价格 79.80select price from bookinfo where book_id = 20202010;
更新余额
update readerinfo set balance = balance - 79.80*0.05 where card_id = '46516874';select * from readerinfo;
更新图书信息表的库存
update bookinfo set store = store -1 where book_id = 20150201;select * from bookinfo;
单表数据记录的删除
删除指定条件的记录
delete from readerinfo where card_id = '46461265464565';
删除表中所有记录
delete from readerinfo;truncate table readerinfo;快
想要删除表中的所有记录,能够应用truncate table
语句,truncate
将间接删除原来的表,并从新创立一个表,其语法结构:
truncate table table_name
查问儿科学的类别编号
select category_id from bookcategory where category='儿科学';
删除图书编号为5的图书信息
delete from bookinfo where book_category_id = 5;
删除图书类别表中儿科学这个类别
delete from bookcategory where category = '儿科学';
单表数据记录的查问
查问所有列
select * from bookcategory;select category_id,category,parent_id from bookcategory;
查问指定列
select category from bookcategory;select category_id,category from bookcategory;
查问指定条件的记录
select book_id,book_name,price from bookinfo where press='出版社';
查问后果不反复的记录
select distinct press from bookinfo;
查看空值
select * from readerinfo where age is null;
分组
统计读者信息表中男读者的人数
select count(*) from readerinfo where sex='男';
将读者信息表中的记录按性别进行分组
select sex from readerinfo group by sex;
将读者信息表中的记录按性别进行分组,并统计每种性别的人数
select sex,count(*) from readerinfo group by sex;
将读者信息表中的记录按性别进行分组,分组后人数大于的性别
select sex from readerinfo group by sex having count(sex)>2;
排序
通过order by子句对查问的后果进行排序
order by 列名 [asc|desc]
排序方向:
- 排序分为升序和降序,默认为升序
- 升序
asc
- 降序
desc
单列排序
select * from bookinfo order by price;
多列排序
select * from bookinfo order by price,store;
指定排序方向
select * from bookinfo order by price,store desc;
limit语句限度查问记录的数量
前3行记录
select * from bookinfo limit 3;
从第3条记录开始的后2条记录
select * from bookinfo limit 2,2;select * from bookinfo limit 2 offset 2;
insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)values(454235424,4, '123', 'xxx',85.8, '出版社', '2020-04-01', 10),(452454542,4, '456', 'xxx', 35.5, '出版社', '2020-08-01', 20),(454578754,4, '789', 'xxx', 46.6, '出版社', '2020-05-01',8);
将图书信息依照库存进行分组,统计每组库存下的个数,而后按库存进行降序排序,并查看后果中的前四条记录
select store,count(*)from bookinfo group by store order by store desc limit 4;
10.运算符与函数
MySQL 次要有以下几种运算符:
- 算术运算符
- 比拟运算符
- 逻辑运算符
- 位运算符
算术运算符
比拟运算符
逻辑运算符
位运算符
运算符优先级
读者的身份证号,姓名,电话,余额。
select card_id, name, tel, balance from readerinfo where balance-200<=0;
查看读者信息表中,余额大于200的读者信息。
select * from readerinfo where balance>200;
查看读者信息表中,余额不等于200的读者信息。
select * from readerinfo where balance <> 200;
查看读者信息表中,年龄不为空的读者信息。
select * from readerinfo where age is not null;
查看读者信息表中,余额在350到450之间的读者信息。
select * from readerinfo where balance between 350 and 450;
select * from readerinfo where name in('dada','dada1','dada2');select * from readerinfo where name like '张_';select * from readerinfo where tel like '135%';select * from bookinfo where price>50 and store<5;select * from bookinfo where price>80 or press = '出版社';select * from bookinfo where price not between 50 and 100;
数值函数
ceil返回大于x的最小整数值
select ceil(28.55); // 29
floor返回小于x的最大整数值
select floor(28.55); // 28
四舍五入
round返回最靠近于参数x的整数,对参数x进行四舍五入
select round(28.55); // 29select round(28.55,1),round(28.55,0),round(28.55,-1);// 28.6 29 30
截断函数
select truncate(28.55,1),truncate(28.55,0),truncate(28.55,-1);// 28.5 28 20
取模,返回x被y除后的余数
select mod(11,2); // 1
select book_id,book_name,price, round(price) from bookinfo;select * from bookinfo where mod(book_id,2)=0;
字符函数
字符串连贯
select concat('hello','world');select concat_ws('-','hello','world');
字母转换大小写
select lower('Hello World');select upper('Hello World');
求长度
select length(' hello ');
删除空格
select ltrim(' hello '),length(ltrim(' hello '));select rtrim(' hello '),length(rtrim(' hello '));select trim(' hello '),length(trim(' hello '));
截取字符串
select substring('hello world',1,5);select substring('hello world',-5,2);
获取指定长度的字符串
select left('hello world', 5); // helloselect right('hello world', 5); // world
替换函数
select replace('hello world','world','mysql'); // hello mysql
格式化函数
select format(1234.5678,2),format(1234.5,2),format(1234.5678,0);//1234.57 1234.50 12345select book_id,book_name,format(price,2)from bookinfo;
日期和工夫函数
查看以后的零碎日期
select curdate();// 2020-02-02
select curdate()+0;select curtime()+0;
查看以后的零碎日期和工夫
select now(); // 2020-10-10 12:12:12select sysdate(); // 2020-10-10 12:12:12
date_add(date,interval expr type):
year,month,day,week,hour
日期的加运算select date_add('2020-01-01', interval 5 month); // 2020-06-01计算两个日期之间距离的天数select datediff('2020-02-10','2020-02-01');日期格式化select date_format('2020-02-01', '%Y%m');
聚合函数(分组函数)
| 名称 | 形容 |
| avg()
| 返回某列的平均值 |
| count()
| 返回某列的行数 |
| max()
| 返回某列的最大值 |
| min()
| 返回某列的最小值 |
| sum()
| 返回某列值的和 |
求图书信息表中,所有图书的平均价格。
select avg(price) from bookinfo;
求图书信息表中,所有图书的总价格。
select sum(price) from bookinfo;
求图书信息表中的最大库存。
select max(store) from bookinfo;
求图书信息表中的最小库存。
select min(store) from bookinfo;
求图书信息表中有多少种图书。
select count(*) from bookinfo;
按类别进行分组, 查问每种类别下有多少种图书以及每种类别图书的库存总和。
select book_category_id as '图书类别',count(book_id) as '图书品种', sum(store) as '库存总和' from bookinfo group by book_category_id;
信息函数与加密函数
零碎信息函数
查看以后MySQL服务器版本的版本号select version();查看MySQL服务器以后连贯的次数select connection_id();查看以后的数据库名select schema();查看以后登录的用户名select user();
加密函数
select md5('test');create table myuser( username varchar(10), password varchar(35));insert into myuser values('user1',md5('pwd1'));select * from myuser;select * from myuser where username = 'user1' and password = md5('pwd1');select password('rootpwd');set password = password('rootpwd');select user,authentication_string from mysql.user;
11.多表连贯查问
多表连贯查问是从多个表中获取数据。
由图书信息表:(图书编号book_id
,类别编号book_category_id
,书名book_name
)
由图书类别表:(类别编号category_id
,类别名称category
,父类别parent_id
)
获取表:(图书编号book_id
,书名book_name
,类别名称category
)
多表连贯的语法结构:
table_reference[INNER] JOIN | {LEFT|RIGHT} [OUTER] JOINtable_referenceon conditional_expr
多表连贯
通过查看图书信息表和图书类别表
来获取图书编号、图书名称、图书类别
select book_id,book_name,category from bookinfo inner join bookcategory on bookinfo.book_category_id = bookcategory.category_id;
内连贯
依据连贯条件从多个表中查问抉择数据,显示这些表中与连贯条件相匹配的数据行,组合成新记录。(内连贯就是两者独特都有的)
内连贯的语法结构:
select column_listfrom t1[INNER] JOIN t2 ON join_condition1[INNER] JOIN t3 ON join_condition2...]where where_conditions;
因为图书借阅统计的须要,想查问未偿还图书的图书编号,图书名称,身份证号,姓名,电话,偿还日期, 是否偿还。
select borrowinfo.book_id,book_name,borrowinfo.card_id, name, tel, return_date, status from borrowinfoinner join bookinfo on borrowinfo.book_id = bookinfo.book_idinner join readerinfo on borrowinfo.card_id = readerinfo.card_idwhere borrowinfo.status = '否';select t1.book_id,book_name,t1.card_id, name, tel, return_date, status from borrowinfo t1join bookinfo t2 on t1.book_id = t2.book_idjoin readerinfo t3 on t1.card_id = t3.card_idwhere t1.status = '否';
外连贯
外连贯将查问多个表中相关联的行。
外连贯分为:左外连贯 left outer join
;右外连贯right outer join
依据业务须要,咱们须要查看图书类别表中的所有类别下都有哪些图书。
select book_id, book_name, category from bookcategoryleft join bookinfo on bookcategory.category_id = bookinfo.book_category_idwhere parent_id<>0;select book_id, book_name, category from bookinfo aright join bookcategory b on b.category_id = a.book_category_id;select * from bookcategory;
左外连贯:显示左表全副记录,右表满足连贯条件的记录。
右外连贯:显示右表全副记录,左表满足连贯条件的记录。
语法结构:
select column_listfrom t1left | right [outer] join t2 on join_condition1;
自连贯
如果在一个连贯查问中,波及的两个表都是同一个表,这种查问称为自连贯
查问所有图书类别的图书类别编号,类别名称,下级分类名称。
select * from bookcategory;select s.category_id as'图书类别编号', s.category as '图书类别名称', p.category as'图书的下级分类名称' from bookcategory sinner join bookcategory pon s.parent_id = p.category_id;
多表更新
update table1 {[inner] join | {left|right} [outer] join} table2on conditional_exprset col1 = {expr1|default}[,col2 = {expr2|default}]...[where where_condition]
身份证号为432xxxxxx的读者将超时的图书86154偿还,依据形容实现如下需要:
- 更新借阅信息表,将借阅状态(status)更新为‘是’。
- 更新罚款记录信息表,更新理论还书日期和罚款金额,罚款金额为每超出一天扣0.2元。
- 同时更新读者信息表的余额。(在余额中扣除罚款金额)
update readerfee t1 join readerinfo t2 on t1.card_id = t2.card_idset actual_return_date = sysdate(),book_fee=datediff(sysdate(),return_date)*0.2,balance = balance - book_feewhere t1.book_id = 86154 and t1.card_id = '432xxxxxx';select * from readerinfo;
多表删除
delete table1[.*], table2[.*]from table1 {[inner]join|{left|right}[outer]join} table2on conditional_expr[where where_condition]
图书类别表,图书信息表:
因为业务需要,须要删除图书类别表中在图书信息表中没有图书记录的类别。
select book_id,book_name,category from bookcategory_bak t1left join bookinfo_bak t2 on t1.category_id = t2.book_category_idwhere parent_id<>0;delete t1 from bookcategory_bak t1left join bookinfo_bak t2 on t1.category_id = t2.book_category_idwhere parent_id<>0 and book_id is null;select * from bookcategory_bak;
须要删除图书类别表的编程语言的类别,以及图书信息表中对于编程语言的图书记录。
select book_id,book_name,category_id,category from bookcategory_bak t1inner join bookinfo_bak t2on t1.category_id = t2.book_category_id;delete t1,t2 from bookcategory_bak t1inner join bookinfo_bak t2on t1.category_id = t2.book_category_idwhere t1.category_id = 3;
多表连贯
依据连贯查问返回的后果:内连贯(inner join),外连贯(outer join),穿插连贯(cross join)。
依据连贯条件所应用的操作符:相等连贯,不等连贯。
12.自定义函数
创立函数
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型BEGIN 函数体END
调用函数
SELECT 函数名(参数列表)
查看函数
SHOW FUNCTION STATUS;
删除函数
DROP FUNCTION IF EXISTS function_name;
函数:须要有返回值,能够指定0~n个参数
创立自定义函数:
create function function_name([func_parameter])returns type[characteristics..] routine_body
Characteristics
指定存储函数的个性,取值举例:
sql security{definer|invoker}指明谁有权限来执行。definer示意只有定义者能力执行。invoker示意领有权限的调用者才能够执行,默认状况下,零碎指定为definer。comment 'string':正文信息,能够用来形容存储函数。
函数体是由sql代码形成,能够简略的sql语句。如果为复合构造须要应用begin...end
语句,复合构造能够蕴含申明,流程管制。
select length('hello');select date_format(pubdate,'%Y-%m') from bookinfo;delimiter // create function ym_date(mydate date)returns varchar(15)beginreturn date_format(mydate,'%Y-%m');end//delimiter;select ym_date(pubdate) from bookinfo;
创立自定义函数:
语法格局:
create function function_name([func_parameter])returns type[characteristics...] routine_body
select length('hello');select date_format(pubdate,'%Y-%m') from bookinfo;
delimiter //create function ym_date(mydate date)returns varchar(15)beginreturn date_format(mydate,'%Y-%m');end//delimiter ;应用(调用)自定义函数select ym_date(pubdate) from bookinfo;
实例剖析函数:
创立一个函数delimiter $$ --定界符--- 开始创立函数create function user_main_fn(v_id int)returns varchar(50)begin--定义变量declare v_userName varchar(50);--给定义的变量赋值select f_userName info v_userName from t_user_mainwhere f_userId = v_id;--返回函数处理结果return v_userName;end $$ --函数创立定界符delimiter;
自定义函数两个必要条件:参数,返回值
创立自定义函数
create function function_namereturns{string|integer|real|decimal}routine_body语法格局:CREATE FUNCTION function_name([func_parameter])RETURNS type[characteristics … ] routine_body
- function_name : 函数名称
- func_parameter : 函数的参数列表
- RETURNS type : 指定返回值的类型
- Characteristics : 指定存储函数的个性
- routine_body : 函数体
创立无参的自定义函数:
删除自定义函数
DROP FUNCTION [IF EXISTS] func_name;
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点:%i分:%s秒')CREATE FUNCTION f1() RETURNS VARCHAR(30)RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点:%i分:%s秒');SELECT f1();
复合构造体的函数
-- 将语句结束符改为$$,为了避免上面的函数将;看成是语句的完结DELIMITER $$CREATE FUNCTION adduser(username VARCHAR(20))RETURNS INT UNSIGNEDRETURNBEGININSERT INTO table_1(username) VALUES(username);LAST_INSERT_ID();END;-- 将分隔符改回来DELIMITER ;
流程管制的应用
罕用的流程管制语句:
- IF条件判断语句-if
- CASE条件判断语句-case
- WHILE循环语句-while
- LOOP循环语句-loop
- REPEAT循环语句-repeat
13.存储过程
局部变量以关键字DECLARE
申明
DECLARE var_name [, varname2, varname3 …] date_type [DEFAULT value];例:DECARE num INT DEFAULE 10;
外部BEGIN…END
块中定义的变量只在该块内无效
会话变量的作用范畴为整个程序
语法后果
create procedure proc_name([proc_parameter])[characteristics...] routine_body
delimiter // create procedure selectproc1()beginselect book_id, book_name, price, store from bookinfo;end //delimiter;call selectproc();
删除存储过程:
drop procedure [if exists] proc_name;
创立一个查问图书的编号、书名、价格和库存的存储过程。delimiter //create procedure selectproc1()beginselect book_id,book_name,price,store from bookinfo;end//delimiter ;调用存储过程call selectproc1();
创立查问图书编号、书名、图书类别的存储过程delimiter //create procedure proc1()beginselect book_id,book_name,category from bookinfo t1join bookcategory t2on t1.book_category_id = t2.category_id;end//delimiter ;call proc1();设计一个存储过程,删除一个读者,并输入残余读者的个数。delimiter //create procedure proc2(in cid char(18), out num int)begindelete from readerinfo where card_id = cid;select count(card_id) into num from readerinfo;end//delimiter ;select * from readerinfo;call proc2('6545xx', @num);select @num;设计一个存储过程,实现替换两个数的解决。delimiter //create procedure proc3(inout num1 int, inout num2 int)begindeclare t int default 0;set t = num1;set num1 = num2;set num2 = t;end//delimiter ;set @n1 = 3, @n2 = 5;call proc3(@n1,@n2);select @n1,@n2;删除存储过程drop procedure proc1;drop procedure if exists proc2;
存储过程和函数的区别
存储过程,存储过程实现的性能比拟复制,功能强大,能够执行包含批改表等一系列数据库操作。
存储函数,实现的性能针对性比拟强。
返回值上的不同
存储过程:能够返回多个值,也能够不返回值,只是实现某种成果或动作。
存储函数:必须有返回值,而且只能有一个返回值。
参数不同
存储过程:存储过程的参数类型有三种,in,out,inout。
存储函数:参数类型只有一种,相似于in参数,调用函数时须要依照参数的类型指定值即可。
语法结构
存储过程,存储过程申明时不须要指定返回类型。
存储函数,函数申明时须要指定返回类型,且在函数体中必须蕴含一个无效的return语句。
调用形式
存储过程,用call
语句进行调用
存储函数,嵌入在sql
中应用的,能够在select
中调用
14.事务
事务必须满足的四个条件:
atomicity 原子性consistency 一致性lsolation 隔离性durability 持久性
管制事务处理
rollback
,回滚会完结用户的事务,并撤销正在进行的所有未提交的批改
commit
,会提交事务,并使已对数据库进行的所有批改称为永久性的
savepoint identifier
,容许在事务中创立一个保留点,一个事务中能够有多个savepoint
rollback to identifier
,把事务回滚到标记点
事务处理次要有两种办法
用begin, rollback, commit
来实现
begin,start transaction
开始一个事务rollback
事务回滚commit
事务确认
间接用set来扭转mysql的主动提交模式
set autocommit = 0
禁止主动提交set autocommit = 1
开始主动提交
innodb应用事务
从Mysql5.5版本开始,InnoDB是默认的表存储引擎。
innodb
是事务型数据库的首选引擎,反对事务平安表。
MySql中 delimiter
默认下,delimiter是分号,在命令行客户端中,如果有一行命令以分号完结,那么回车后,mysql将会执行该命令。
(通知mysql解释器,该段命令是否曾经完结了,mysql是否能够执行了。 )
什么是存储引擎:数据库存储引擎是数据库底层软件组件。数据库管理系统应用数据引擎进行创立,查问,更新和删除数据的操作。
mysql
的外围就是存储引擎。
innodb
存储引擎
- 它为
mysql
提供了具备提交,回滚和解体恢复能力的事务平安存储引擎。 - 对于解决微小数据量的数据领有很好的性能
innodb
存储引擎反对外键完整性束缚innodb
被用在泛滥须要高性能的大型数据库站点上
设置存储引擎:
- 设置服务器的存储引擎
- 在配置文件my.ini中的mysqld上面设置须要的存储引擎
default-storage-engine=InnoDB
- 重启mysql服务器
创立表(单个)设置存储引擎create table mytest( id int primary key, name varchar(10)) engine = innodb default charset = utf8;
批改表的存储引擎
alter table tablename engine = engineName
15.治理与保护
治理用户
USE mysql;select user from user;
权限表:存储账号的权限信息表:user,db,host,tables_priv,columns_priv
和procs_priv
各个权限表的作用
tables_priv
表用来对表设置操作权限;columns_priv
表用来对表的某一列设置权限;procs_priv
表能够对存储过程和存储函数设置操作权限。
应用CREATE USER语句创立新用户
语法格局:
CREATE USER “user”@“host” [IDENTIFIED BY “password”];
应用DROP USER 语句删除用户
语法格局:
DROP USER user[, user];例:应用DROP USER删除账户"rose"@"localhost":DROP USER "rose"@"localhost";
示例:
查看日志文件的门路show variables like 'log_error';创立新的日志信息表flush logs;创立新的日志信息表mysqladmin -uroot -p flush-logs
点关注,不迷路
好了各位,以上就是这篇文章的全部内容,能看到这里的人都是人才。我前面会不断更新技术相干的文章,如果感觉文章对你有用,欢送给个“赞”,也欢送分享,感激大家 !!