字符函数

描述  函数名称
字符连接CONCAT()
使用指定的分隔符进行字符连接CONCAT_WS()

数字格式化FORMATO)
转换成小写字母LOWER()
转换成大写字母UPPER()
获取左侧字符LEFT()
获取右侧字符RIGHT()

描述名称
获取字符串长度LENGTH()
删除前导空格RTRIM()
删除前导和后续空格TRIM()
字符串截取SUBSTRING()
模式匹配[NOT] LIKE
字符串替换REPLACE()

如：

SELECT CONCAT('imooc','-','mysql','-','function') ;

SELECT CONCAT_WS('-','imooc','myaql','function');

这两个返回结果是一样的：imooc-mysql-function

SELECT FORMAT(12345.678,2);  将数字进行保留两位小数，第二参数为0时表示保留成整数

SELECT LOWER('MYSQL');  得到是纯粹小写的mysql.

SELECT UPPER('mysql');  得到纯粹大写的MYSQL.

SELECT LEFT/RIGHT('Mysql',2);   得到My/ql

SELECT LENGTH('My SQL');  获取的My SQL的长度为6（注意空格也算为长度内）

SELECT LTRIM/RTRIM('  MySQL    ');  删除前面、后面（前导/后导）的空格。

SELECT TRIM('  MySQL    ');  删除所有的空格。

还有：SELECT TRIM(LEADING '?' FROM '??MySQL???');  得到的就是删除??MySQL???中的前导的？符号。

SELECT TRIM(TRAILING '?' FROM '??MySQL???');  删除后导的？符号。

如果是要删除前导和后导的全部的符号，则用SELECT TRIM(BOTH '?' FROM '??MySQL???');  所以他删除不掉MySQL中的？号。

但是如果要删除掉所有的符号，则采用SELECT REPLEACE('??My??SQL???','?','');  用空格替换这其中的'?'符号。（注意这里可以采用一对多或者多对一的替换：如?? -> !）。

SELECT SUBSTRING('MySQL',1,2);  在MySQL中从第1位开始截取2位，获得My(注意MySQL中索引号从1开始；如果没有给出截取几位的话，则是从该位置截取到最后；假如起始位置索引号为负值则是倒序截取)

SELECT 'MySQL' LIKE 'M%';  （'%'代表的是任意字符；_代表一个字符）得到的就是模式匹配

数值运算符与函数

进一取整CEIL()
整数除法DIV

舍一取整FLOOR() 

取余数(取模)MOD
幂运算POWER()
四舍五入ROUND()

数字截取TRUNCATE()

[不]在范围之内 [NOT] BETWEEN...AND...

[不]在列出值范围之内 [NOT] IN()

[不]为空 IS [NOT] NULL

如：

SELECT CELL/FLOOR(3.01);  得到4/3

SELECT 3/4;  得到0.7500   如果是  SELECT 3 DIV 4;  得到为0

SELECT 5.3%3; 与 SELECT 5.3 MOD 3;  等同，结果为余数2.3

SELECT POWER(3,3);  得到的是3^3，结果为27

SELECT ROUND(3.652,0);   结果为四舍五入数字：4

SELECT TRUNCATE(135.89,0);  得到的结果为截取135

SELECT 15 BETWEEN 1 AND 20;   返回值为真 1

SELECT ' ' IS NULL;  返回值为错 0 

SELECT * FROM test WHERE frist_name IS NULL;   

日期时间函数

当前日期和时间NOW()

当前日期CURDATE()

当前时间CURTIME()

日期变化DATE_ADD()

日期差值DATEDIFF()

日期格式化DATE_FORMAT()

如：

SELECT DURDATE()/DURTIME();   得到当前日期/得到当前时间   

SELECT DATE_ADD('2018-11-22',INTERVAL -365 DAY);   得到的是107-11-22，这里面的DAY可以是WEEK,MONTH,YEAR等

SELECT DATEDIFF('2017-11-22','2018-11-22');   得到的是日期的差值

SELECT DATE_FORMAT('2018-11-22','%m/%d/&Y');   得到的就是22/11/2018

日期函数是很少被用到的！！！！！

信息函数：

连接IDCONNECTION_ID()
当前数据库DATEBASE()
最后插入记录的ID号LAST_INSERT_ID()

当前用户USER()

版本信息VERSION()

如：

SELECT CONNECT_ID()/DATEBASE()/LAST_INSERT_ID()/USER()/VERSION();   返回的是链接的ID/当前数据库/最后插入的记录的ID号/当前的用户/当前的版本信息

【复习：ALTER TABLE test ADD id ——给表添加字段的操作】

聚合函数：（）只能返回一个值

平均值：AVG()

计数：COUNT()

最大/小值：MAX()/MIN()

求和：SUM()

SELECT AVG(goods_price)AS bieming FROM tdb_goods;   聚合函数都是这样的格式

加密函数：

加密：MD5()   多用于web开发

密码的计算：PASSWORD()    修改用户的密码

函数很少单独使用，所示嵌套的使用，而且可以用在数据表的相关字段上

UNIQUE KEY 唯一约束

AUTO_INCREMENT属性 必须与主键PRIMARY KEY一起使用

但主键不一定要和AUTO_INCREMENT一起使用

PRIMARY KEY 主键约束

保持记录的唯一性

在数据表中 使用AUTO_INCREMENT属性 为字段自动编号

调用存储过程

存储过程-特性

存储过程-参数

CREATE TABLE tb2(

username VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED NULL);   //字段 username age 空和非空设置。

 show colums from tb2;   //查看表数据结构

insert table_name values();  //表添加数据

select * from table_name;  //查看表数据

show columns from table_name;  //查看数据表的结构

这是数据库期末考试要考的内容

1

2

3

6

8

9

10

12

14

16

17

21

--高级查询综合实训

--问题：有一个图书借阅系统

--数据库：BookStore

--三张数据表:

--分类表：categories(cid,cname)

--出版商：publishers(pid,pname,address,postcode,linkman)

--图书表：books(bid,bname,author,price,cid,pid)

--读者表：readers(rid,rname,identityid,gender)

--借阅表：lends(rid,bid,lenddate,returndate)

create database BookStore

go

use bookstore

go

if(exists(select * from sysobjects where name='categories' and type='u'))

drop table categories

go

create table categories

(

cid char(3),

cname char(20)

)

go

insert into categories values('001','计算机')

insert into categories values('002','医学')

insert into categories values('003','经济')

insert into categories values('004','会计')

insert into categories values('005','法律')

if(exists(select * from sysobjects where name='publishers' and type='u'))

drop table publishers

go

create table publishers 

(

pid char(3),

pname char(20),

address char(40),

postcode char(6),

linkmand char(10)

)

go

insert into publishers values('001','人民邮电出版社','北京','100000','赵青山')

insert into publishers values('002','清华大学出版社','北京','201200','张超')

insert into publishers values('003','电子工业出版社','北京','410012','刘玉芬')

insert into publishers values('004','人民出版社','上海','140020','王永生')

insert into publishers values('005','人民教育出版社','北京','302122','陈陆祥')

go

--图书表：books(bid,bname,author,price,cid,pid)

if(exists(select * from sysobjects where name='books' and type='u'))

drop table books

go    

create table books 

(

bid char(5),

bname char(40),

author char(20),

price float,

cid char(3),

pid char(3)

)

go

insert into books values('00001','sql server实训','张玉祥',60.5,'001','001')

insert into books values('00002','新手学sql sever 2000','赵海山',25.3,'001','002')

insert into books values('00003','atuocad 2000i','张学',51,'001','003')

insert into books values('00004','电工基础','蔡玉生',10,'001','001')

insert into books values('00005','审计','周玫',74,'004','005')

go

--读者表：readers(rid,rname,identityid,gender)

if(exists(select * from sysobjects where name='readers' and type='u'))

drop table readers

go

create table readers 

(

rid char(6),

rname char(40),

identityid char(18),

gender char(2) default '男'

)

go

insert into readers values('010001','张三','440255196502051234','男')

insert into readers values('010002','李四','440255199201021289','男')

insert into readers values('010003','赵小光','440255198106063692','男')

insert into readers values('010004','蔡波','440255197405087451','男')

insert into readers values('010005','黄梦迷','440255198506302356','女')

go

--借阅表：lends(rid,bid,lenddate,returndate)

if(exists(select * from sysobjects where name='lends' and type='u'))

drop table lends

go

create table lends 

(

rid char(6),

bid char(5),

lenddate datetime,

returndate datetime

)

go

insert into lends values('010001','00001','2/20/2010','')

insert into lends values('010002','00002','4/20/2010','')

insert into lends values('010003','00001','5/1/2009','')

insert into lends values('010004','00001','3/25/2010','4/24/2010')

insert into lends values('010005','00005','3/26/2010','4/10/2010')

go

----完成如下查询内容：

--1.查询books表中全部记录；*************************************************期末要考

select * 

from books

--2.查询readers表中全部记录；*************************************************期末要考

select * 

from readers

--3.查询所有读者的借阅情况，只显示rid,bid字段;*************************************************期末要考

select rid,bid from lends

--4.查询所有读者的借阅情况，显示rid,rname,bid,bname,lenddate,returndate字段;

select  lends.rid,rname,bname,lenddate,returndate

from lends,books,readers

where readers.rid=lends.bid and lends.rid = books.bid

--5.统计每位读者的借书本数;

select rid ,COUNT(rid)

from readers

group by rid

--6.查询含有“sql”的书籍记录,显示书籍所有字段信息；*************************************************期末要考

select * from books where bname like '%sql%'

--7.查询借阅书名中含有"sql"字样的书籍的读者的rid,rname,bid,bname；

select readers.rid,rname,books.bid,bname

from readers,books,lends

where readers.rid=lends.rid and books.bid=lends.bid and bname like '%sql%'

--8.查询每类书籍的本数，显示cid,cname和本数;*************************************************期末要考

select categories.cid,cname,COUNT(*)

from categories,books

where categories.cid = books.cid

group by categories.cid,cname

--9.查询每类书籍的平均价格，显示cid,cname和平均价格;*************************************************期末要考

select categories.cid,cname,AVG(price)

from categories,books

where categories.cid = books.cid

group by categories.cid,cname

--10.查询未借书的读者的rid,rname;*************************************************期末要考

select rid ,rname

from readers

where rid not in(select rid from lends)

--11.查询每类图书被借阅的本数,显示cid,cname和被借阅的本数;

--12.查询未被借阅的图书bid,bname;*************************************************期末要考

select *  from books

where bid not in

(

select bid from lends

)

--13.查询借阅图书最多的读者的rid,rname和借阅本数;

--14.删除“张三”、“李四”两位读者的借阅记录；*************************************************期末要考

delete 

from lends

where rid in

(

select rid from readers where rname in ('张三','李四')

)  

--15.查询每类图书最贵的两本bid,bname,price,cid,cname;

--16.查询每本书的被借阅的次数；*************************************************期末要考

select rid,COUNT(*)

from lends group by rid

--17.查询至少借了两本书的读者的rid;*************************************************期末要考

select rid 

from lends

group by rid 

having COUNT(*)>=2

--18.查询所有读者都借阅的书籍rid,rname;

--19.查询没有借阅“人民出版社”图书的读者rid,rname;

--20.查询“计算机”类图书价格低于50元的图书的全部信息，按价格降序排序；

--21.删除读者“010001”借阅"00001"图书的记录。*************************************************期末要考

delete 

from lends

where rid like '01001' and bid like '00001'

use database_name;  //使用数据库/打开数据库

select database();  //查看当前数据库

create table table_name();   //创建表

show tables;  //查看数据表列表

show tables from mysql;  //查看mysql中存在的表