MySQL学习笔记,基础命令

997755.com澳门葡京 1

MySQL基本知识及演习(5)

1.求3个班级数学平均分。
(1). select sum(math) / count(math) as 数学平均分
from student;

(2). select avg(math) as 数学平均分
from student;

(3).select avg(name) as 小明平均分
from student;//0

2.求多个班级总分平均分。
(1).select (sum(chinese)+sum(math)+sum(english)) / count(*)
from student;

(2).select avg(chinese+math+english)
from student;

3.求班级语文最高分和最低分。
select max(name),min(name)
from student;

drop table if exists teacher;
create table teacher(
id int,
name varchar(20),
birthday date
);
insert into teacher(id,name,birthday) values(1,’jack’,’2011-1-1′);
insert into teacher(id,name,birthday) values(2,’marry’,’2011-2-2′);
insert into teacher(id,name,birthday) values(3,’sisi’,’2011-3-3′);

select max(birthday),min(birthday)
from teacher;

4.对订单表中货物分类后,彰显每一类商品的总价
select product as 连串称,sum(price) as 商品种类总价
from orders
group by product;

5.查询购买了几类货物,并且每类总价超过100的货品
select product as 类外号,sum(price) as 商品品种总价
from orders
group by product
having sum(price) > 100;

6.where v.s. having区别:

where首要用来行过滤器
having首要用来项目过滤器,平日有having就必然出现group by,但有group
by的地点,不肯定现身having。hving能够说是本着结果集在开始展览询问的。

drop table if exists teacher;
create table teacher(
id int primary key auto_increment,
name varchar(20) not null unique,
birthday date
);
insert into teacher(name,birthday) values(NULL,’2011-1-1′);
insert into teacher(name,birthday) values(‘marry’,’2011-2-2′);
insert into teacher(id,name,birthday) values(3,’sisi’,’2011-3-3′);

select max(birthday),min(birthday)
from teacher;

7.剔除主键,主键在表中只有2个,要么是一列,要么是多列
alter table teacher drop primary key;

8.一对一涉嫌(方案一):
drop table if exists card;
drop table if exists person;

create table person(
id int primary key auto_increment,
name varchar(20) not null
);

insert into person(name) values(‘jack’);
insert into person(name) values(‘marry’);

create table card(
id int primary key auto_increment,
location varchar(20) not null,
pid int,
constraint pid_FK foreign key(pid) references person(id)
);

insert into card(location,pid) values(‘BJ’,1);
insert into card(location,pid) values(‘GZ’,2);
insert into card(location,pid) values(‘CS’,NULL);
insert into card(location,pid) values(‘NJ’,3);//出错

//删除person表的某记录
delete from person where name = ‘jack’;

9.一对一关联(方案二):
drop table if exists card;
drop table if exists person;

create table person(
id int primary key auto_increment,
name varchar(20) not null
);
insert into person(name) values(‘jack’);
insert into person(name) values(‘marry’);

create table card(
id int primary key auto_increment,
location varchar(20) not null,
constraint id_FK foreign key(id) references person(id)
);
insert into card(location) values(‘BJ’);
insert into card(location) values(‘GZ’);
insert into card(location) values(‘CS’);//出错
insert into card(location) values(NULL);

10.一对多/多对一涉嫌:
drop table if exists employee;
drop table if exists department;

create table department(
id int primary key auto_increment,
name varchar(20) not null
);
insert into department(name) values(‘软件部’);
insert into department(name) values(‘销售部’);

create table employee(
id int primary key auto_increment,
name varchar(20) not null,
did int,
constraint did_FK foreign key(did) references department(id)
);
insert into employee(name,did) values(‘jack’,1);
insert into employee(name,did) values(‘marry’,1);

11.标题?查询”软件部”的保有职员和工人(组合式)
select d.name as 部门名,e.name as 员工名
from department as d,employee as e
where d.name = ‘软件部’;

讨论:还有没有别的措施?

分解:
(1)select id from department where name=’软件部’;
(2)select name from employee where did = 1;
(总)嵌入式SQL

select name as 员工
from employee
where did = (
select id
from department
where name=’软件部’
);

12.多对多涉及:
drop table if exists middle;
drop table if exists student;
drop table if exists teacher;

create table if not exists student(
id int primary key auto_increment,
name varchar(20) not null
);
insert into student(name) values(‘jack’);
insert into student(name) values(‘marry’);

create table if not exists teacher(
id int primary key auto_increment,
name varchar(20) not null
);
insert into teacher(name) values(‘赵’);
insert into teacher(name) values(‘蔡’);

create table if not exists middle(
sid int,
tid int,
constraint sid_FK foreign key(sid) references student(id),
constraint tid_FK foreign key(tid) references teacher(id),
primary key(sid,tid)
);
insert into middle(sid,tid) values(1,1);
insert into middle(sid,tid) values(1,2);
insert into middle(sid,tid) values(2,1);
insert into middle(sid,tid) values(2,2);

13.难点?查询”赵”所教过的有所学员
select t.name as 老师, s.name as 学员
from teacher as t,student as s,middle as m
where t.name = ‘赵’and m.sid=s.id and m.tid=t.id;

14.模式:
select 列出须求出示的字段
from 列出所涉及到的保有表,建议写别名
where 业务规范 and 表关联条件

15.运用MySQL特有函数:
到年根儿还有几少天?
select datediff(‘2011-12-31’,now());

16.截取字符串
select substring(‘mysql’,1,2); //从1开始

17.保留小数点后四位(四舍五入)
select format(3.1415926535657989,3);

18.向下取整(截取)
select floor(3.14);
select floor(-3.14);
select floor(3.54);
select floor(-3.54);

19.取随机值
select format(rand(),2);

20.取1-6里头的轻易整数值
select floor(rand()*6) + 1;

21.MySQL扩展知识:
查MySQL文档,利用MySQL的函数:随机发生’a’-‘z’之间的随机字符。
私自产生’a’-‘z’之间的随意字符
(1)查询’a’-‘z’对应的Unicode值
select ascii(‘a’);//97
select ascii(‘z’);//122

(2)产生97-122以内的自由整数
select floor(rand()*26)+97;

(3)发生97-122应和的字符
select char(floor(rand()*26)+97);

22.查MySQL文档,利用MySQL的函数:对密码’123456’进行MD5加密。
select md5(‘123456’);

drop table user;
create table user(
id int primary key auto_increment,
name varchar(20),
gender varchar(6),
salary float
);
insert into user(name,gender,salary) values(‘jack’,’male’,4000);
insert into user(name,gender,salary) values(‘marry’,’female’,5000);
insert into user(name,gender,salary) values(‘jim’,’male’,6000);
insert into user(name,gender,salary) values(‘tom’,’male’,7000);
insert into user(name,gender,salary) values(‘soso’,’female’,NULL);
insert into user(name,gender,salary) values(‘haha’,’female’,3500);
insert into user(name,gender,salary) values(‘hehe’,’female’,4500);
select * from user;

23.MySQL特有流程序控制制函数:
1) if(value,第一值,第二值);
value为真,取第①值,不然取第2值
将5000元(含)以上的职员和工人标识为”高薪”,不然标识为”起薪”
好像于Java中的三目运算符

select if(salary>=5000,’高薪’,’起薪’)
from user;

2) ifnull(value1,value2)
value1为NULL,用value2替代
将薪酬为NULL的职工标识为”无薪”

select name as 职员和工人,ifnull(salary,’无薪’) as 薪资情状
from user;

3) case when [value] then [result1] else [result2] end;
当value表明式的值为true时,取result1的值,不然取result2的值(if…else…)
将四千元(含)以上的职工标识为”高薪”,否则标识为”起薪”

select
case when salary>=5000 then ‘高薪’
else ‘起薪’ end
from user;

4) case [express] when [value1] then [result1] when [value2]
then [result2] else [result3] end;
当express满足value1时,取result1的值,满足value2时,取result2的值,否则取result3的值(switch…case..)
将八千元的职工标识为”高薪”,四千元的职员和工人标识为”中薪”,6000元则标识为”起薪”,不然标识为”低薪”

select
case salary
when 7000 then ‘高薪’
when 6000 then ‘中薪’
when 5000 then ‘起薪’
else ‘低薪’ end
from user;

25.询问同一性其他职工业总会人数>2的工资总数,并按薪金总额降序排列
select count(*) as 员人数,gender as 性别,sum(salary) as 工资和
from user
group by gender
having count(*)>2
order by sum(salary) desc;

26.将性别为男的职员和工人薪水-一千,性别为女的职员和工人薪俸+一千,在一条SQL上做到
select if(gender=’female’,salary+1000,salary-1000) as 工资 from user;  

27.常用函数举例

select now();
select year(now());
select month(now());
select day(now());
select floor(datediff(now(),‘1999-01-01’)/365);//间隔年
select format(rand(),2);
select floor(rand()*5)+1;[1-5]随机值
select length(trim(‘ jack ‘));
select strcmp(‘a’,’w’);

总结:

1 .关联的完整性

(1)实体(行)完整性:每条记下有二个唯一标识符,平日用无其余业务含义的字段表示
(2)参照完整性:一张(A)表的某部字段必须引用另一张(B)表的某些字段值,而且B表
的字段必须先存在。
(3)域(列)完整性:域即单元数据,域中的数值必须符合一定的规则,例如字段的值域、字
段的类型等的束缚。

2 键的概念
(1)主键:唯有唯一字段
(2)组合主键:由七个字段组合起来,形成唯一字段
(3)外键:针对多张表之间的关系

3 主键的特点
(1)主键不可能重复
(2)主键不可能为NULL
(3)auto_increment是MySQL特有的,私下认可从1开始,该ID值与表同生亡
(4)五人项目中,平日接纳UUID来变化唯一的主键值,便于八个联合数据时照旧维持实体完整性

4 唯一约束的特点
(1)非NULL值无法重新
(2)能够插入四个NULL值
(3)’NULL’空串和NULL是例外的定义

5 非空约束特点
(1)不能够插入NULL值
(2)主键约束=非NULL约束+唯一约束

6 外健特点
(1)外键值必须来自所引用别多少个表主键值,或NULL

7 关联关系
(1)一对一(外健根业务有关)
(2)一对多或多对一(外键放置在绝超越53%)

(3)多对多(外健放置在论及表中,即将1个多对多拆分成2个一对多涉及)

8.常用函数:
(1).日期函数:

997755.com澳门葡京 2

2.数学函数:

997755.com澳门葡京 3

3.字符串函数997755.com澳门葡京 4

) 1.求三个班级数学平均分。
(1). select sum(math) / count(math) as 数学平均分 from student; (2).
select avg(math) as 数学平均分 from…

 

SQL语法基础

篇幅简介

时间:2016-3-28 19:40

Create database语句

  • Create database语句是在MySQL实例上创设二个点名名称的数据 库,create
    schema语句的语义和create database是同一的
  • 当创立的数据库自个儿存在而且尚未写明if not exists子句时,则创立数据库的语句会报错

一 、Msql数据类型

 

Create database语句

  • create_specification子句指明创造的数据库的性质,并储存在db.opt文
    件中
  • Character set属性指明此数据库的暗中同意字符集
  • Collate属性指明此数据库的暗中同意排序规则
  • 开创后的数据库在数据文件所在目录会创造二个和好的文件目录,用
    来含有后续创立的表文件
  • 也能够直接通过mkdir在数额目录创立目录,
    则MySQL会识别为三个数据库,并在实践show databases命令时能够看 到

    mysql> create database test3;       -- 创建数据库成功 
    Query OK, 1 row affected (0.00 sec)
    mysql> create database test3;       -- 再次创建数据库失败
    ERROR 1007 (HY000): Can't create database 'test3'; database exists
    mysql> create database if not exists test3;         -- 语句执行成功 
    Query OK, 1 row affected, 1 warning (0.00 sec)
    mysql> use test3;       -- 切换到test3数据库
    

1、整型

tinyint,  占 1字节 ,有符号: -128~127,无符号位 :0~255

smallint, 占 2字节 ,有符号: -32768~32767无标志位 :0~65535

mediumint 占 3字节 ,有符号: -8388608~8388607,无符号位:0~16777215:

int, 占 4字节 ,有符号: -2147483648~2147483647,,无符号位 无符号位 :0~4
284967295

bigint, bigint,bigint, 占 8字节

bool  等价于 tinyint

 

create table语句

  • tbl_name表示被创建的表名,默许在当前数据库下创制此表,当
    然也足以钦定在某些数据库下成立表
  • if not exists表示当相同的表名存在时,则不实施此创设语句,防止语句执行错误

    create table students2(sid int,sname varchar(10));
    create table test3.students2(sid int,sname varchar(10));        --在test3这个数据库下创建students2表
    create table if not exists students2(sid int,sname varchar(10));
    

2、浮点型

float([m[,d]])  占 4字节 ,1.17E-38~3.4E+3838~3.4E

double([m[,d]])  占 8字节

decimal([m[,d]])  以字符串情势表示的浮点数 

上学就怕心浮气躁,有个三脚猫的素养,就以为啥都会了。                  
                                                                       
                                                        ——  赠自个儿

Temporary关键词

  • 表示成立的是权且表,暂时表仅对本链接可知,其余的数据库链接不可知,
    当本链接断开时,近期表也自动被drop掉

    mysql> create temporary table temp1(sid int,sname varchar(10)); 
    Query OK, 0 rows affected (0.00 sec)
    mysql> insert into temp1 values(1,'a'); 
    Query OK, 1 row affected (0.00 sec)
    mysql> select * from temp1;
    +------+-------+
    |sid |sname|
    +------+-------+
    | 1 |a |
    +------+-------+
    1 row in set (0.00 sec)
    
  • 另三个数据库链接执行同一的查询语句查不到数据

    mysql> select * from temp1;
    ERROR 1146 (42S02): Table 'test.temp1' doesn't exist        -- 本数据库链接断开后再链接,临时表也不存在
    mysql> select * from temp1;
    ERROR 1146 (42S02): Table 'test.temp1' doesn't exist
    

3、字符型

char([m]): :定长的字符 ,占用 m字节

varchar[(m)]::变长的字符 ,占用 m+1m+1 字节,大于 255 个字符:占用
m+2m+2

tinytext,255 个字符 (2 的 8次方 )

text,65535 个字符 (2 的 16 次方 )

mediumtext,16777215字符 (2 的 24 次方 )

longtext (2的 32 次方 )

enum(value,value,…)占 八分之四个字节 最多能够有 65535 个分子 个成员

set(value,value,…) 占 四分之二/百分之七十五/七个字节,最多能够 有 陆拾1个成员

 

Like关键词

  • 表示依照其余叁个表的概念复制一个新的空表,空表上的字段属性和索
    引都和原表相同

     mysql>create table students_copy like students;
     Query OK, 0 rows affected (0.01 sec)
     mysql> show create table students_copy;
    CREATE TABLE `students_copy` (
     `sid` int(11) DEFAULT NULL,
     `sname` varchar(20) DEFAULT NULL,
    `sex` int(11) DEFAULT NULL,
     UNIQUE KEY `idx_st_sid` (`sid`),
    KEY `idx_st_union` (`sname`,`sex`)
     )ENGINE=InnoDB DEFAULT CHAR SET=latin1
    

贰 、Mysql数据运算

新手不挨训,怎么发展。

Create table … as select语句

  • 意味着制造表的还要将select的查询结果数据
    插入到表中,但索引和主外键新闻都不会共同过来

    mysql> create table students_copy2 as select * from students;
    Query OK, 0 rows affected (0.03 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    mysql> desc students_copy2;
    +——-+————-+——+—–+———+——-+
    | Field | Type | Null | Key | Default | Extra |
    +——-+————-+——+—–+———+——-+
    | sid | int(11) | YES | | NULL | |
    | sname | varchar(20) | YES | | NULL
    | sex | int(11) | YES | | NULL | |
    +——-+————-+——+—–+———+——-+

① 、逻辑运算 and or not

for example:

选料出 书籍价格 为(30,60,40,50)的记录

sql> select bName,publishing,price from books where price=30 or
price=40 or price=50 or price=60; 

997755.com澳门葡京 5

1

 

Ignore和replace

  • 意味着在插入数据的历程中一经新表中遇见违反唯一约束 的情景下怎么处理
  • ignore表示不插入,replace表示替换已有的数据,
    暗中认可三个基本点词都不写则碰着违反的图景会报错

2、in 运算符

in 运算符用于 WHERE 表明式,以列表的花样援助多少个选用,语法如下

where colunmm in (value1,value2,…….)

where colunmm not in (value1,value2,……….)

当in后面加上not时,表示与in相反,既不在结果中

sql> select bName,publishing,price from books where  price in
(30,40,50,60)order by price asc;

997755.com澳门葡京 6

2

 
——数据库的基本概念

Data_type

  • 代表定义的字段类型

③ 、算术运算符  >= | <=| <> |=

for example

找出价格低于70的笔录

mysql> select bName,price from books where price <= 70;

 

997755.com澳门葡京 7

3

一、定义:
    壹 、数据库指的是以自然措施存款和储蓄在联合署名、能为八个用户提供共享,具有尽只怕小的冗余度的性状,是与应用程序相互独立的数量集合。

Not null/null

  • 表示字段是或不是同意为空,默许为null表示同意为空,
  • not null表示须求对此字段鲜明数值,也许要有默许值,否则报错

    mysql> create table students2(sid int not null,sname varchar(10));
    Query OK, 0 rows affected (0.00 sec)
    mysql> insert into students2(sname) values(‘eee’);
    ERROR 1364 (HY000): Field ‘sid’ doesn’t have a default value

④ 、模糊查询  like ‘%…%’

字段名 [not] like  ‘%……%’   通配符  任意多少个字符

查询书中隐含程序字样的记录

mysql> select bName,price from books where bName like ‘%程序%’

997755.com澳门葡京 8

4

    ② 、数据库是依据某种数据结构(层次性,网状型,关系型)组织起来并存放二级存款和储蓄器(外存)中的数据集合。那种数量集合具有如下特点:尽只怕不另行,以最优办法为某些特定协会的四种应用服务,其数据结构独立于接纳它的应用程序,对数码的增加和删除改查由统一软件进行政管理理和控制。

Default表示设置字段的私下认可值

create table students3(sid int,sname varchar(10),gender int default 0); 
insert into students3 values(1,'a',default);
insert into students3(sid,sname) values(2,'b');
mysql> select * from students3;
+------+-------+--------+
|sid         |sname    |gender        | 
+------+-------+--------+ 
|1|a|0| 
|2|b|0|

五 、范围运算 [not] between …….and

摸递价格不在30和60时期的书名和价格

mysql> select bName,price from books where price not between 30 and
60  order by price desc;

997755.com澳门葡京 9

5

② 、数据库发展阶段:
    一 、人工管理阶段
    二 、文件系统阶段
        文件共享性差,不易于同步。
    ③ 、数据库系统阶段

Auto_increment

  • 代表字段为整数还是浮点数类型的value+1递增数值,value为当前表
    中该字段最大的值,暗许是从1伊始递增
  • 一个表中只只怕有八个自增字段,且该字段必须有key属性,不能够含有default属性,且插入负值会被当成相当的大的正数

    mysql>create table students4(sid int auto_increment,sname varchar(10));
    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a
    key
    mysql>create table students4(sid int primary key auto_increment,sname varchar(10));
    Query OK, 0 rows affected (0.01 sec)
    mysql> insert into students4(sname) values('a');
    Query OK, 1 row affected (0.00 sec)
    mysql> insert into students4(sid,sname) values(3,'b');
    Query OK, 1 row affected (0.01 sec)
    mysql> insert into students4(sname) values('c');
    Query OK, 1 row affected (0.00 sec) 
    mysql> select * from students4;
    +-----+-------+
    |sid        |sname    | 
     +-----+-------+ 
    |1|a| 
    |3|b| 
    |4|c|
    

6、Mysql 子查询

select where条件中又现身select

询问类型为网络技术的书本

mysql> select bName,bTypeId from books where bTypeId=(select
bTypeId from category where bTypeName=’互联网技术’);

 

997755.com澳门葡京 10

6

三 、数据库种类:
    ① 、层次模型(树状结构)
    二 、网状模型(唯有概念,尚未落实)
    叁 、关系模型(一数据库中,七个表正是几个涉及)

Constraint

  • 代表为主键、唯一键、外键等约束规范命名,尽管没有命名则MySQL会暗中同意给三个

    #### Primary key

  • 意味着该字段为主键,主键字段必须唯一,必须非空,

  • 3个表中只可以有二个主键,主键能够包蕴一个或四个字段

    #### Key/index

  • 意味着索引字段

    #### Unique

  • 代表该字段为唯一属性字段,且允许包罗多个null值

    #### Foreign key

  • 代表该字段为外键字段

    CREATE TABLE gender (
    gender_id int(11) NOT NULL,
    name varchar(10) DEFAULT NULL,
    PRIMARY KEY (gender_id)
    );
    create table students5(sid int not null primary key auto_increment,sname varchar(10) unique,gender int,constraint for_1 foreign key (gender) references gender(gender_id));

柒 、limit 限定突显的条款

LIMIT子句能够被用来强制 SELECT语句重临钦定的记录数。 LIMIT
接受二个或两数字参。必 须是2个整数常量。即便给定两 个数,第1内定再次来到记录行的偏移量,第四个参数再次来到记录行的最大数额。起初偏移量是 0( 而不是
1)。

语法 : select * from limit m,n

里面 m是指记录起先的 index indexindex,从
0起头,表示第②条记下,n是指从第 m+1 条发轫,取 n。

询问books表中第一条到六行的笔录

mysql>select * from books limit 1,6;

997755.com澳门葡京 11

7

四 、常见的关系型数据库:
    1、SqlServer:
        微软旗下数据库
        适用于Windows平台
        服务器版须要付费
    2、MySQL
        属于Oracle公司
        品质较高
        社区版免费
        体量小、速度快、总体拥有花费低
        开源
        适用平台:
            Windows
            Linux
            Unix
    3、DB2
        IBM集团旗下数据库
            IBM1966年第叁次建议关系型数据库模型。
            IBM一九七四年建议了SEQUEL语言,即SQL语言的前身。

设计选课数据库系统

  • 创建一个名为course的数据库
  • 在该数据库下开创以下多少个表:
  • Students表:sid整型自增主键,sname字符串陆九个人,gender字符串十位,dept_id整型并外键到dept表的id字段
  • Dept表:id整型自增主键,dept_name字符串64位
  • Course表:id整型自增字段主键,course_name字符串64位,teacher_id整型外键到teacher表的id字段
  • Teacher表:id整型自增字段主键,name字符串六11个人,dept_id整型外键到dept表的id字段
  • Students表和teacher表的dept_id为非空

    mysql> create database course;
    mysql> use course;

    mysql> create table dept(id int auto_increment primary key,dept_name varchar(64));

    mysql> create table students(sid int auto_increment primary key,sname varchar(54),gender varchar(12),dept_id int not null,constraint for_1 foreign key(dept_id) references dept(id));

    mysql> create table teacher(id int auto_increment primary key,name varchar(64),dept_id int not null, constraint for_2 foreign key(dept_id) references dept(id));

    mysql> create table course(id int auto_increment primary key,course_name varchar(64),teacher_id int,constraint for_3 foreign key(teacher_id) references teacher(id));

八 、连接查询

以2个手拉手的字段,求两张表当中符合条件并集。 通过
共同字段把这两张表的同台字段把那两张表连 接起来。

常用的连日:

内连接:依据表中的一块儿字段实行匹配

外接连:现实某数据表的 全体记下和其它数码表中符合连接条件的笔录。

外连接:左连接、右连接

内连接:for exmaple

create table student(sit int(4) primary key auto_increment,name
varchar(40));

insert into student
values(1,‘张三’),(2,‘李四’),(3,‘王五’),(4,‘mikel’);

create table teachers(sit int(4),id int(4) primary key
auto_increment,score varchar(40));

insert into teachers
values(1,1,‘1234’),(1,2,‘2345’),(3,3,‘2467’),(4,4,‘2134’);

select s.* ,t.* from student as s,teachers as t where s.sid=t.sid;

997755.com澳门葡京 12

8

左连接: select 语句 a表 left[outer] join b 表  on 连接条件
,a表是主,都显得。

b表是从,主表内容全都有,主表多出来的字段,从表没有的就显得
null,从表多出主表的字段不出示。

select * from student as s left join teachers as t on  s.sit=t.sit; 

997755.com澳门葡京 13

9

右连接:select 语句 a表 right[outer] join b 表  on 连接条件
,b表是主,都显得。

a表是从,主表内容全都有,主表多出来的字段,从表没有的就显得
null,从表多出主表的字段不显得。

select * from student as sright join teachers as t on  s.sit=t.sit;

997755.com澳门葡京 14

10

5、从八个方面学习数据库

叁 、聚合函数

    ① 、数据库是何许存储数据的

1、sam() 求和

select sum (id+score) as g from teachers;

        字段  记录  表
约束(主键、外键、唯一键、非空、check、default、触发器)

贰 、avg() 求平均值

select avg (id+score) as g from teachers;

    二 、数据库是怎么样操作数据的

3、max() 最大值

select max (id) as g from teachers;

        insert、update、delete、T-Sql、存储进程、函数、触发器

4、min() 最小值

select min(id) as g from teachers;

    叁 、数据库是什么突显数据的

5、substr(string,start,len) 截取

select substr(soucr,1,2) as g from teachers;

从start开始,截取len长度,start从1开始

concat(str1,str2,str3………………….)字符串拼接,将五个字符串拼接在共同

select concat(id,score,sit) as g from teachers;

        select(重点的根本)

陆 、count() 总括计数 记录字段数据条数

select count(id) as g from teachers;

——数据库是怎么样解决多少存储难题的

7、upper() 大写

select upper(name) as g from student; 
#将字段name中国和英国文全体成为大写,但不更改原值

表的连带数据

8、lower() 小写

select lower(name) as g from
student;  #将字段name中国和英国文全体制改善为小写,但不更改原值

    字段(列)

四、索引

mysql中索引是以文件格局存放的,对表进行增删改,会共同到目录,索引和表保持一致,常用在where
后字段查询就加索引。

亮点:加快查询速度,减弱查询时间

缺陷:索引占据一定磁盘空间,会潜移默化insert,delete,update执行时间

        三个东西的某1个静态特征(属性)。

壹 、索引类型

常见索引:最基本索引,不富有唯一性

唯一索引:索引列的值必须唯一,但允许有空值。假诺是整合索引,则列值的组成必须唯一

主键索引:记录值唯一,主键字段很少被更改,不能够为空,不能够改改,可用以一个字段大概三个字段

全文索引:检索文本消息的, 针对较大的数额,生成全文索引查询速度快,但也很浪费时间和空中

组合索引:一个索引包括多个列

    记录(行)

贰 、创设索引

一般索引:

# 创制普通索引

create table demo(id int(4),uName varchar(20),uPwd varchar(20),index
(uPwd));

# 查看建表进程

show create table demo;

demo | CREATE TABLE `demo` (

  `id` int(4) DEFAULT NULL,

  `uName` varchar(20) DEFAULT NULL,

  `uPwd` varchar(20) DEFAULT NULL,

  KEY `uPwd` (`uPwd`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

唯一索引:字段值只允许出现贰回,能够有空值

# 创设唯一索引

create table demo1(id int(4),uName varchar(20),uPwd varchar(20),unique
index (uName));

# 查看建表进程

show create table demo1;

demo1 | CREATE TABLE `demo1` (

  `id` int(4) DEFAULT NULL,

  `uName` varchar(20) DEFAULT NULL,

  `uPwd` varchar(20) DEFAULT NULL,

  UNIQUE KEY `uName` (`uName`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

主键索引:字段记录值唯一,字段很少被涂改,一般主键约束为auto_increment恐怕not
null unique,无法为空,不能够重复。

# 创立主键索引

create table demo2(id int(4) auto_increment primary key,uName
varchar(20),uPwd varchar(20));

# 查看建表语句

demo2 | CREATE TABLE `demo2` (

  `id` int(4) NOT NULL AUTO_INCREMENT,

  `uName` varchar(20) DEFAULT NULL,

  `uPwd` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

全文索引:提升全文字笔迹检验索效能,解决模糊查询

# 创造全文索引

create table demo3(id int(4),uName varchar(20),uPwd
varchar(20),fulltext(uName,uPwd));

# 查看建表语句

| demo3 | CREATE TABLE `demo3` (

  `id` int(4) DEFAULT NULL,

  `uName` varchar(20) DEFAULT NULL,

  `uPwd` varchar(20) DEFAULT NULL,

  FULLTEXT KEY `uName` (`uName`,`uPwd`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

        字段的结合,表示的是贰个现实的东西

伍 、外键约束

外键约束:foreign key
表与表之间的一种约定关系,由于那种关涉存在,让表与表之间的数目进一步具有完整性,越发具有关联性。

    表

创立外键约束

创建user主表

create table user1(id int(11)auto_increment primary key,name
varchar(50),sex int(1));

插入数据

insert into user1(name,sex)values(“mikel”,4),(“plyx”,6);

创建order外键表

create table `order`(order_id int(11)auto_increment primary
key,u_id int(11),username varchar(50),monery int(11),foreign key(u_id)
references user1(id)  on delete cascade on update cascade
)engine=innodb);

安排数据

INSERT INTO `order`
(order_id,u_id,username,monery)values(1,1,’mikel’,2345),(2,2,’plyx’,3456)

测试级联删除

delete from user1 where id=1

查看order表记录

997755.com澳门葡京 15

12

测试级联更新

update  user1 set id=5 where id=2

997755.com澳门葡京 16

13

测试数据完整性

在order表中插入一条u_id为6的记录

insert into `orser` (u_id)values(6);

Cannot add or update a child row: a foreign key constraint fails
(`school`.`order`, CONSTRAINT `order_ibfk_1` FOREIGN KEY
(`u_id`) REFERENCES `user1` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE)

user1中不设有id为6的笔录,今后增进一条id为6的记录

insert into user1(id)values(6);

997755.com澳门葡京 17

14

能够看来数据现已插入进去了

        记录的结合,表示的是如出一辙品种事物的集聚

视图

是一张虚拟表,由 select select
select语句钦赐的数据结构和多少,不生成真正文件

create view mikel as select * from school.books;

select  * from mikel;

997755.com澳门葡京 18

15

    表和字段、记录的涉及

六 、存款和储蓄进程

储存进程用来封装mysql代码,也就是函数,一遍编写翻译,生成二进制文件,永久有效,提升功效。

        字段表示的是事物的性质

① 、定义存款和储蓄进度

create procedure 过程名(参数1,参数2,………….)

begin

      sql语句

end

        记录表示的是东西本人

贰 、调用存储进程

call 过程名(参数1,参数2,……………….)

example:定义3个囤积进程查看books表中享有数据

    1. 修改sql私下认可执行符号

    delimiter //

   create  procedure seebooks();

   begin

         select * from sctudent.books;

    end //

    call seebooks() //

997755.com澳门葡京 19

16

        表是事物的成团

三 、存款和储蓄进程参数字传送递

in 传入参数 int 赋值

IN输入参数:表示调用者向进程传入值(传入值能够是字面量或变量)

OUT输出参数:表示经过向调用者传出值(能够再次回到五个值)(传出值只可以是变量)

INOUT输入输出参数:既代表调用者向经过传入值,又象征经过向调用者传出值(值只可以是变量)

create procedure seebook(in b int)

begin

  select * from school.books where bId=b;

end //

call seebook(4)

997755.com澳门葡京 20

16

out ————–传出参数

select into 在经过中赋值传给变量,并查阅变量值

create procedure seebook2(out b varchar(100))

begin

  select bName into b  from school.books where bId=4;

end //

997755.com澳门葡京 21

17

进程内的变量使用方法

宣示变量名称,类型,declare 进度内的变量没有@

赋值 set 变量名=(select 语句)

create procedure seebook3()

begin 

        declare str varchar(100);

        set str=(select bName from school.books where bId=20);

        select str;

end//

call seebook3() //

997755.com澳门葡京 22

18

 

1、触发器

与数据表有关,当表出现(增,删,改,查)时,自动执行其一定的操作

语法:create trigger 触发器名称 触发器时机 触发器动作 on 表名 for each
row

触发器名称:自定义

触发器时机:after/before   之后/从前

触发器动作:insert  update  delete

创建触发器:

 create trigger delstudent after delete on grade for each now

delete from student where sid=’4′;

delete from grade where sid=4;

mysql> select sid from student where sid=4;

Empty set

翻看是或不是还有sid=4的值,能够发现已经被删除

    列

2、事务

单个逻辑单元执行的一文山会海操作,通过将一组操作结合一个,执行的时依旧全体打响,要么全部惜败,使程序更保障,简化错误恢复生机。

MySQL
事务主要用来拍卖操作量大,复杂度高的数据。比如说,在人口管理类别中,你剔除一个人手,你即需求删除职员的基本资料,也要删减和该职员相关的新闻,如信箱,小说等等,那样,这几个数据库操作语句就组成1个作业!

在 MySQL 中只有选择了 Innodb 数据库引擎的数据库或表才扶助理工程师作。

事务处理能够用来保卫安全数据库的完整性,保险成批的 SQL
语句要么全体实施,要么整体不实施。事务用来治本 insert,update,delete
语句。

MYSQL 事务处理首要有二种艺术:

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 起头3个事情

ROLLBACK 事务回滚

COMMIT 事务确认

贰 、直接用 SET 来改变 MySQL 的电动提交格局:

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 拉开自动提交

创制工作

begin;

update books set bName=”plyx” where bId=1;

update books set bName=”plyx” where bId=2;

commit//

查看记录,已经修改了

select * from books;

997755.com澳门葡京 23

19

        字段的另一称呼

七 、mysql数据结构

主配置文件  my.cnf

数量目录:/var/lib/mysql

经过通讯sock文件 :/var/lib/mysql/mysql.sock

荒谬日志文件

[mysqld_safe]

log-error=/var/log/mysqld.log

进程PID文件:pid-file=/var/run/mysqld/mysqld.pid

二进制文件:log-bin=mysql-bin.log

    属性(列)

八.大面积的仓库储存引擎介绍

myisam :

特点: ① 、不扶助理工科程师作,不支持外键,宕机时会破坏表

            ② 、使用较小的内部存款和储蓄器和磁盘空间,访问速度快

            叁 、基于表的锁,表级锁

            4、mysql 只缓存index索引, 数据由OS缓存

适用场景:日志系统,门户网站,低产出。

Innodb:

特点:① 、具有提交,回滚,崩溃复苏能力的事体安全存款和储蓄引擎

           ② 、帮忙电动拉长列,扶助外键约束

           三 、占用更加多的磁盘空间以保存数据和目录

           四 、不帮助全文索引

适用场景:须求工作应用,高并发,自动还原,轻快基于主键操作

MEMORY:

特点:壹 、Memory存款和储蓄引擎使用存在于内部存款和储蓄器中的始末来成立表。

         
 ② 、各个memory表只实际对应二个磁盘文件,格式是.frm。memory类型的表访问相当的快,因为它的数据是坐落内部存款和储蓄器中的,并且暗中认可使用HASH索引,不过若是服务关闭,表中的数目就会丢掉掉。 

          三 、MEMOSportageY存款和储蓄引擎的表能够选取使用BTREE索引也许HASH索引。

        字段的另一种称谓

⑨ 、思考与总结

到此首要介绍,mysql一些应用技术,包蕴数据类型,查询方式,存款和储蓄进度,外键约束,索引。触发器,事务,还带有部分储存引擎介绍,到此基础部分了结,还有前边的分享将会陆续推出,敬请期待!

997755.com澳门葡京 24

总结

作者是MIkel Pan,云总括爱好者,定期更新生活感悟,心灵进化者就在MIkel
Pan,喜欢作者就来找小编吗!

微博地方:

简书地址:

    元组(行)

        行、记录的另一种称谓

 

——MySQL数据库的下载和安装

设置到位后装置数据库字符集为UTF-8:
    查看字符集:show variables like ‘character%’;
    修改字符集:
        打开my.ini文件,在ini文件中#意味着注释。
        添加:default-character-set=utf8
                  character-set-server=utf8
    修改形成后重启服务器。

壹 、MySQL安装成功后会在八个目录中储存文件
    >   D:\Program Files\MySQL Server 5.6
        DBMS管理程序
    >   C:\ProgramData\MySQL Server 5.6\data
        DBMS数据库文件,卸载数据库时不会去除该目录,需求协调手动删除。

贰 、MySQL主要文件
    >   D:\Program Files\MySQL Server 5.6\bin\mysql.exe
        客户端程序,用来操作服务器,但必须保险服务器已经运转才能延续数据库。
    >   D:\Program Files\MySQL Server 5.6\bin\mysqld.exe
        服务器程序,必须先运行它,然后客户端才能一连服务器。
    >   D:\Program Files\MySQL Server 5.6\bin\my.ini
        服务器配置文件。

3、C:\ProgramData\MySQL\MySQL Server 5.6\data
    该目录下的每二个目录都意味一个数据库,例如该目录下有1个mysql目录,表明DBMS中有二个名为mysql的databases。
    在各样数据库目录下会有0~N个扩充名为frm的公文,各类frm文件表示三个table,不要接纳文本编辑器打开。

4、my.ini
    配置MySQL的端口:默认为3306,
    配置字符编码:
        *   [client]下布置客户端编码:default-character-set=utf8
        *   [mysqld]下布署服务器编码:character-set-server=utf8
    配置二进制数据大小上限:
        *   在[mysqld]下配置:max_allowed_packet=8M

⑤ 、导出数据库及数码
    mysqldump -uroot -pAdmin123 mydb3 >f:\sql1.sql

——MySQL数据库基本操作

一 、服务器的运维与截止
    在services中运营或终止。
    1)开启服务:net start mysql
    2)关闭服务:net stop mysql
    服务器:向来处在监听状态,等待接受命令
② 、MySQL客户端登录
    mysql -uroot -p123 -hlocalhost
    >   -u:表示用户名
    >   -p:表示密码
    >   -h:表示主机(IP地址)
        *   root用户能够动用任意IP,普通用户则绑定IP。
③ 、退出客户端
    exit、quit
叁 、查看数据库
    查看全体数据库:show databases;
    information_schema:提供访问数据库元数据的艺术。
    mysql:与数据库系统服务有关的多少。
    performance_schema:收集服务器数据库品质的参数
    test:用户测试表。
            
翻开全部数据库:show databases;
切换来近日数据库:use 数据库名;
翻开当前数据库中所有表:show tables;
查询某表中的数据:select * from 表名;

——管理数据库

一 、数据库相关概念
    1)数据库 DataBase
        分类:
            针对于系统数据库
                information_schema
                mysql
                performance_schema
                    用于帮衬数据库系统健康运维的数据库。
            用户数据库:
                针对于特定类型须求所创办的数据库
        组成:
            逻辑结构:
                数据库中文件的团组织结构。
            物理构造:
                存款和储蓄在磁盘上的文件结构。
    2)数据库管理系列 DBMS
        DataBase Management System
        管理数据库的阳台:
        服务器
        客户端

二 、管理艺术
        1)可视化管理
            例如navicat管理数据库
        2)SQL语言管理
            Structured Query Language
MySQL学习笔记,基础命令。            针对于数据库操作的语言,程序支付进程中所需求的言语。
            分类:
                DDL:Data Description Language,数据定义语言
                    用于创设,从无到有。

                    对数据库或表的布局进行操作。

                DML:Data Manipulation Language,数据操作语言
                    操作数据库,例如增加和删除改。
                    insert  delete  update 

                DQL:Data Query Language,数据查询语言
                    select  查询

                DCL:Data Control Language,数据控制语言
                    对用户的创立及授权。
                    revoke  create  table  from  user1

                DAC:Data Administrate Command,数据管理命令。

                TCC:Thing Control Command,事务控制命令。

三 、数据库的保管表达
    1)创造:create database 数据库名;    创制在默许data目录下。
    2)删除:drop database 数据库名;

——管理表

壹 、表的相关概念
    关全面据库(Relational DataBase,
LacrosseDB):便是依照关系模型的数据库,在电脑中,关周到据库是数量和数据库对象的三结合关系型数据库基础数据是数据表。

2、实体
    数据表是由行row和列column组成的二维表,每行描述三个实体,数据表中的列平常叫做字段,他意味着数据表中储存实体的共有属性。
    属性值的分歧代表不相同的实业。
    数据表中存款和储蓄的多少便是具有分化属性的的例外实体的联谊。

叁 、数据表的保管
    设计某类实体具有怎么样属性(字段)、表结构
        类似于规划类,该类具有啥样属性。
        依据项目实际必要安插怎么样表,每一种表包括哪些字段。
    对品质有哪些限定条件——约束。

    数据类型:
        整型:
            int
                占4个字节。
            tinyint
                占1个字节,在数据库优化的时候能够动用tinyint。
        浮点型:
            float / double
                double(5,
2)表示最多两个人整数,当中必须有肆人小数,即最大值为999.99
        浮点型:
            decimal(6,2)
               
用于开始展览十进制运算,精确度高,不会丢掉二进制数据,常用于表示金额。
                一共伍位数,小数占4人。
                此时decimal占8个字节。
                在设计数据库时要注意不要浪费空间。 
        日期:
            date:
                占3个字节。
                日期类型,不可能保存时间
                格式:YYYY-MM-DD
                范围:1000-01-01 到 9999-12-31
                如若超出范围,则存款和储蓄0000-00-00
            time:
                占3个字节。
                时间档次,无法保留日期
            datetime:
                占8个字节。
                日期、时间项目,即年月日时分秒
            timestamp:
                占多少个字节,并且能够代表年月日时分秒.
                能够代表皮秒。
                ts timestamp default
CURRENT_TIMESTAMP(那是三个常量,表示系统当下岁月。)
                因为timestamp也足以代表年月日时分秒,并且所需空间是datetime的50%,所以在陈设数据库时得以用时间戳表示时间
            year:
                表示年,占一个字节,范围是1900-2155年,假如没有则0000。
        字符:
            char(m)
                m指的是字符数,而不是字节数。
               
定长字符串,数据长度相差钦点长度,会自动补足到内定长度。
                最大值为255。
                一般用来保存时间等稳定长度字符串。
            varchar
                可变长度字符串类型,最大值为65535。
                会单独使用一个字节保存字符串长度。
        大字符串类型(CLOB):(MySQL独有)
            tinytext:2^8-1B  255
            text:2^16-1B  65535
            mediumtext:2^24-1B  3KB
            longtext:2^32-1B  4GB
        BLOB:(大字节类型)

    注意:数据库优化
        当每一条记下都是定长数据时,会十分大的提升数据库的检索功效。
        因为每一行的长度都以定点的,所以寻找时的定址、寻址尤其快。
        当存在多少浪费和频率增高三种状态还要设有是,以功用为准。(不可能浪费太多)
        时间与上空是一对争辩体,无非是时间换空间,恐怕空间换时间。
        当数码表中的某一列影响了全体进程并且不能进展空间优化时,并且该列修改频率也不高,
        那么可以将该列单独取出存为一张表。
        在付出进度中,数据库优化往往是把频仍用到的信息存储到一张表中,优先考虑寻找效用。
        不常用的新闻和相比较占据空间的音信,优先考虑空间占据,单独存储到别的一张表中。
        并且一般时间并不采纳datetime存储,而是使用时间戳,因为datetime纵然直观,但不便宜计算,影响成效。

④ 、SQL管理表结构
    1)建表语法
        use 数据库名;
        create table [if not exists] 表名
        (
            id int not null,
            name varchar(20) not null
        );

    2)查看表结构
        desc t1;    description描述
        查看表结构。
        show create table t1;
        查看创立表的长河。

    3)列的表征
        是或不是为主键
        是还是不是为空
        是不是为活动增加列
        是还是不是有暗许值

        以上特征都属于数据表的牢笼

    4)表字段修改
        修改字段定义:
            修改数据类型用modify
            alter table 表名
            modify 字段名 数据类型;
 
        修改字段名用change
            alter table 表名;
            change 旧字段名 新字段名 新数据类型;
 
        扩张字段:
            alter table 表名 
            add 字段名 数据类型 (完整性约束规范)

            /*假使想在第②个任务扩大字段,要求在数据类型前边加上first*/
            /*假若想在内定位置添加,能够选拔after 字段名;  
在该字段之后添加*/

        删除字段:
            alter table 表名 drop 字段名;

        删除表:
            drop table 表名;

⑤ 、管理约束
    概念:
        对多少个表中的性质操作的限量叫做约束。
        约束是数据库提供的机动强制数据完整性的一种艺术,它是透过定义列的取值规则来维护数据的完整性。
    数据完整性:
        实体完整性:必要数据表中不能够出现重复行(完全相同数据行)
        域完整性:域,范围,其实正是性质,供给列的数据类型,取值范围,特定值等符合规定供给
        引用完整性:须求几个或三个以上表之间的涉及,关体系在数值上保持一致。
        自定义完整性:针对于具体表的有血有肉规定。
    约束类型:
        1)主键约束
            primary key
            维护实体完整性
            该字段唯一,并且不能够为null
            日常是一向不事情含义的编号做主键
            含有主键的表叫做主键表

            主键平常皆以整数,不建议利用字符串当主键(假诺主键是用以集群式服务,能够设想用字符串当主键)。

            主键的值一般不允许修改,除非本记录被去除。

        2)外键约束
            foreign key
            维护引用完整性
            不是本表的主键,但却是另一个表的主键。
        问题:

            假使删表,先删主键表如故先删外键表?

        答案:先删外键表

            如若先删主键表,会报错,因为那会促成外键表中的数量援引退步。

            假若外键表存在,则主键表不能够删除。

        3)唯一约束
            unique
            维护实体完整性
            和主键的分歧:unique允许有且唯有一个null(Oracle中允许null
重复)

            八个表中能够有多少个unique列。

        4)检查约束
            check
            维护数据完整性
            在MySQL中数据库引擎会分析check语句,可是会忽视check约束。
            所以有关字段范围的操作,能够在先后中限定。
        5)暗中认可约束
            default
            维护域完整性
        6)非空约束
            not null
            维护域完整性
            假如不加约束,私下认可同以为空。
        7)自动增进列
            auto_increment
            能够活动拉长的品类必须是整型
            默认从1开始
            即使想改变开首值,代码如下:
            create table 表名
            (
                id int primary key auto_increment
            )auto_increment=20160001;
            这样就将电动拉长的开始值改为201四千1了。
            修改约束:
                alter table 表名
                auto_increment = 1001;

陆 、怎么着设计主外键:

    create table product

    (

        pid int not null default 0,

        name varchar(10) not null default ”,

        price int,

        store int

    );

 

    create table sale

    (

        sid int not null default 0,

        pid int not null default 0,

        salecount int default 0,

        saledate date default ‘0000-00-00’

    );
 

/*

— 创设外键表

商品表中pid是商品编号,应该是唯一的,在商品表中pid应该安装为主键,

在销售表中,只要迈出一件货物,就要求在销售表中添加一条记下,因为三个货物能够卖多次,

之所以该表中pid允许再度,所以pid无法作为该表的主键,不过她在商品表中作为主键,

由此pid可以看作sale表中的外键。

*/

⑦ 、SQL管理约束
    添加约束:
        创设表时加上
            非空约束
                not null

                一般不点名列为null值,因为不易于相比与查询。

                因为null是一连串型,比较时不得不用尤其的is null和is not
null举办相比较,费时费劲,如果接纳运算符进行相比,则一律重临null,功用不高且不便于数据库优化(影响索引效果)。
                因而在建表时,一般钦赐not null default ’ ’ 只怕default
0。

            主键约束
                primary key,主键暗中认可非空。
            唯一约束
                直接写
            缺省封锁
                default ‘男’
            自动拉长列
                auto_increment
                创制表后添加
                    格式
                        alter table 表名
                        add
                            constraint 约束名
                            具体约束表达;
            外键约束
                格式
                    alter table 外键表名
                    add constraint 约束名   
(fk_主键表_外键表_外键)
                    foreign key(外键)references 主键表(主键)
                删除却键约束:
                    alter table 表名 drop foeign key 外键外号;

——数据库操作

    增删改中都不晤面世table关键字。

    增:
        1、insert [into] <表名> [(列明)]
values<(值列表)>
            < >必写、[ ] 可选,最棒都写,方便移植。
           
能够不点名列名,不过值列表中值的一一必须与表中字段顺序保持一致。
           
能够钦定列名,并且不遵从数据库表中字段顺序插入,但是值要求和点名列名保持一致。
        贰 、对于自动增进列的处理:
            1)能够钦赐自动增进列的数值。
                insert into t1 values(10,’qq’,21,2);
            2)要是不钦赐,则依据近年来列的最大值自动扩展。
                insert into t1 (name,age,class)values(‘dd’,19,5);
            3)能够以内定自动增加列,赋值时赋null,依旧自动拉长。
                insert into t1 (id,name,age,class) values
(null,’qq’,21,2);
            4)不钦命列,并且自动拉长赋null。
                insert into t1 values(null,’aa’,21,2);
            只要插入值和插入列不协作,就要内定列。
        ③ 、当存在默许值时,有三种插入形式:
            1)钦定全体值
            2)指定列,指定值
            3)使用default关键字来钦点使用默许值。
        四 、叁遍性插入多行
            insert into 表名 values (值列表1),(值列表2),(值列表);

    改
        修改数据
            常常依据主键来修改,因为主键是绝无仅有的。
            语法
                修改单列值:
                    update 表名
                    set 字段名 = 修改的值
                    where 字段名 = 值;

                修改多列值:
                    update 表名
                    set 字段名1 = 值1,字段名2 = 值2
                    where 字段名 = 值;

                多条件
                    where 字段名1 = 值1 and 字段名2 = 值2; 

    删
        delete from 表名
        where 删除条件
            删除表中数据,保留表结构。
            事务提交后才生效,不然回滚。
            固然有照应的触发器,会重罚执行。

        delete from 表名
            删除表中全部数据,保留表结构,记录日志。
        
        truncate 表名
            删除表中数据,保留表结构。
            不记录日志,删除后不足苏醒。
           
删除后相当于保留表结构,全数情况上升到最初。(无数据,也正是格式化)
            truncate属于DDL,数据定义语言。
    
        drop table 表名
            直接从数据库中删除表。

——单表查询

    壹 、查询和记录集的定义
        查询:在存活的数据表中过滤符合条件的消息。
        记录集:查询的结果平时称为“记录集”,记录集通常是三个虚拟表。

    ② 、基本select查询语句
        语法格式:
                select 列名 from 表名
                where 查询条件表明式
                group by 属性名1 having 条件表达式    — 用于分组
                order by 排序的列名 asc | desc;  — 用于排序
        查询表中全体列:
                select * from 表名;
                查询全数列,顺序依照表的原本顺序显示。
                select 列名2,列名1 from 表名,调整列名的逐条。
        查询表中有个别列:
                select 列名1,列名2 from 表名 where 条件;
                在查询全部记录的功底上过滤消息。
                条件大小顺序不可能改变,必须扩张。
                条件:
                        比较
                        内定范围:between and  /  not between and
                            能够and四个规格
                        钦命集合:in  /  not in
                        匹配字符:like  /  not like
                            %:匹配任意多个字符
                            _: 匹配任意三个字符
                             [a-f]

                                能够是字符列[a-f]中的任意一个字符。

                               
–查询ename字段中第二个字符是A到F中专擅3个单一字符的职员和工人音信。

                                    select * from emp

                                        where ename like ‘_[A-F]%’ 

                            [a,f]

                                能够是a或f的单一字符。

                               
–查询ename字段中第②个字符是A或F的员工消息。

                                    select * from emp

                                        where ename like ‘_[A,F]%’ 

                            [^a-c]

                                不在字符列中的任意多少个字符。

                               
–查询ename字段中首个字符不在A到F的界定内的职工音信。

                                    select * from emp

                                        where ename like ‘_[A-F]%’

                       
注意:匹配的原则必须用单引号括起来。(单引号代表字符串,双引号表示二个对象的名字)

                        –假诺要查询% _ 通配符等特殊字符,能够接纳:

                            select * from emp

                                where ename like ‘%\%%’ escape ‘\’

                            –个中escape关键字将’\’转换来“转义字符”,使
‘\’ 之后的字符不再是本来意义。

                        是或不是为空值:is null  /  is not null
                        几个查询条件:and  /  or
                

        使用distinct关键字屏蔽重复数据:
                select distinct 列名 from 表名;
                    对点名列屏蔽重复值。

        使用limit关键字查询表中限定行:
                select * from 表名 limit m,n
                m是开场记录,从0开首,n为查询的记录数
                当m为3时,从第伍条记下发轫,展现五行。
                不内定起初地方时,暗许从记录第三行发轫。

            公式:(当前页-1) * 每页记录数,得出的就是开头行

    ③ 、对查询结果开始展览排序
        select 列名 from order by 排序字段 排序格局
        order by 放在查询的尾声面。
        order by私下认可升序排序。
                asc:升序    desc:降序
       
还是能按多列实行排序,当在那之中一个行列值相同时按另一列排序(多门学科战表)
                order by sclass,sbirthday;    —
先依据班级排序,当班级相同时,再遵照生日排。

——分组总括与多表关联合检查询
    ① 、聚合函数
        where中无法包括聚合函数。
        凡是聚合函数,重回的都以单值。
            sum
            avg
            max
            min
        以上三种聚合函数通用特点:
            select 聚合函数(列名) from 表名

            count
                总括记录数,但是不记录null。
                count(*)   count(列名)

    ② 、分组查询
     
  –分组查询只可以彰显分组之后的总体音信,无法展现组内部某一字段的新闻。
        语法:
                select 字段列表 [聚合函数] from 表
                [where 条件]
                group by 字段列表
                [having 筛选标准]
                [order by 排序字段]

        单列分组:
                对中间一列分组,对另一列进行计算。
                查询项只含有分组列和总计列。
                例:总结教师表中各职位的平均年龄。
                        — 计算助教表中各岗位的平均年龄

                        select tjob,avg(tage) from t_teacher

                        group by tjob;
                        总计学生表中各班级人数
                            — 计算学生表中各班级人数

                            select sclass,count(*) from t_student

                            group by sclass 

        多列分组:
                对多列进行分组,对另一列举行总结。
               
对分组列按顺序进行分组总计,先对tjob进行分组,再对tsex进行分组。
                select tjob,tsex,avg(tage) from t_teacher
                group by tjob,tsex;
        HAVING:    
            使用having子句对分组信息重新过滤。
            筛选分组后的音信。
            必须合作group by使用。
         
  having语句能够遵从select字段名举办过滤,能够用聚合函数实行过滤,不得以采纳外号和模糊查询实行查询。

            having中不得不查询组的总体音信,但不可能查询组的详细新闻。
             where必须写在having前面,顺序不可爱毛反裘,不然运转出错。

        对分组结果实行排序:
            order by放在查询语句的最后。

        select语句中查询列必须在聚合函数中或group by中。
            
    叁 、条件总计
        where
            针对于表中拥有音信实行筛选。
        having
            针对于分组后的音讯实行筛选。

    肆 、多表关联合检查询
        壹 、使用多表关联合检查询的原委:
                查询的新闻分布在多个表中。
                三个表四个三番五次条件,八个表四个三番五次条件。
        贰 、交叉连接
               
获得的结果是四个笛卡尔积,是八个表记录的穿插乘积,列是七个列表的聚集。
                一旦表名内定了别称,则整个询问必须使用外号。
        3、内连接
                语法
                        join on法
                            select 字段名
                            from 表1 inner join 表2
                            on 表1.字段名 = 表2.字段名
                            where 筛选标准
                            order by 排序列
                            条件运算符 = 或 <>
                        说明
                            列名能够试表1和表第22中学的任意字段。
                           
若查询列出现共有字段则必须内定该字段取自哪个表,格式是:表名.列名。
                            表1.列名 =
表2.列名:使用三个表共有的字段建立联系。
                          
 能够钦定表的外号,一旦内定外号后,全数出现表名的地点都要使用别名。
                        where条件法
                            select 字段名 from 表1,表2
                步骤总括
                    列出查询字段
                    旁观询问字段在怎么样表中
                    提取个表的国有字段作为三番五次条件
                    分析条件
        4、外连接
                左外连接
                   
什么人做主表,什么人的音讯整体来得,尽管音信不兼容,则右表新闻展示null。
                        语法
                            select 字段名 from 左表 left [outer] join
右表
                                on 左表.列名 条件运算符 右表.列名
                                where 条件
                            含义
                             
  左外连接是以左表为主表,去老是右表(从表),结果集中包括主表全体数据行,假设主表的某行在从表中没
                                有十分时,则从表的挑选列为null值。
                                例如:
                                        以学员表作为主表,连接战表表。
                右外接连
                    主从表与左外连接相反。
                    
——子查询
    壹 、子查询的定义
       
倘诺叁个select语句能够回来二个单值或一列值并嵌套在2个select、insert、update或delete语句中,则称之为子查询或许
        内层查询,而富含3个子询问的讲话则变成主查询或外层查询。
        执行进度:
            先执行子查询,再执行主查询。
        难题分析:
            查询出生日期小于雷军的上学的儿童新闻:
                第一步:
                        分析查询字段-学生表的具备新闻字段。
                        select * from student
                第二部:
                        分析查询条件,出生日期小于雷军的出生日期。
                        where birthday < (雷布斯的出生日期)
                第3部:查询雷布斯的出生日期。
                        select birthday from student
                        where name = ‘雷军’
    贰 、子查询的归类
        1)相比子查询
            带有比较运算符的查询
                单值比较查询
                    where id = (子查询)
                批量相比较子查询
                    997755.com澳门葡京 25
                    语法:
                        select 字段名 from 表名
                        where 字段名 比较运算符 any | all ( 子查询 )
                    说明:
                       
在包含any或all运算符的子查询中,子查询的结果是四个凑合,在利用时必须同时选择比较运算符。
                    示例:
                       
在上学的儿童表中查询比2班中某一学生出生日期小的学习者音讯。
                            select * from student
                            where birthday < any
                            (
                                select birthday from student
                                where class = 2
                            );
        2)带有in或not in的子查询
            语法:
                select 字段名 from 表名
                where 字段名 [not] in (子查询)
                示例:
                    查询选修了课程的学员消息
                    select * from student
                    where sid in 
                    (
                        select sid from score
                    )
        3)连接查询和子查询的总计
            连接查询:
                查询的字段存在于多个表中
                    首先分析查询字段
                    再查看查询字段分布的表
                    然后找出表之间的集体字段(建立连接的基准)
                    最后再分析任何标准
            子查询:
                查询的字段在同贰个表中
                    首先分析查询字段
                    再分析查询条件
                    然后找出查询条件用到的字段
                    再分析该字段关联的表

——函数
    具有特定功用的措施    
    常用在创建表和查询表中。
    
    mod(x,y)
    rand()
    round(x,y)

    curdate()
    curtime()
    now()
    datediff(expr1,expr2)

    concat(str1,str2)
    lower(str)
    upper(str)
    length(str)
    trim(str)
    replace(str,oldstr,newstr)
    substring(str,pos,len)

    database()
    version()
    user()

    常用函数
 

        — database()

        — 重回当前多少库名

        select database();

 

        — version()

        — 重回当前数据库版本

        select version();

 

        — user()

        — 再次来到当前报到用户名

        select user();

 

        — inet_aton(ip)

        — 再次回到IP地址的数字代表情势

        select inet_aton(‘192.168.1.127’);

 

        — inet_ntoa(ip)

        — 重临数字代表的IP地址

        select inet_ntoa(3232235903);

 

        — password(str)

        —
再次回到字符串str的加密版本,加密是单向的(不可逆),适用于MySQL数据库的用户密码加密,41个人稳定长度。

        select password(‘aaa’);

 

        — md5(str)

        —
重临字符串str的MD5值,该值以三十七人十六进制数字的二进制字符串的款式重临。

997755.com澳门葡京 ,        select md5(‘aaa’);

    字符串函数

        — concat(str1,str2)

        — 连接字符串,再次来到一个子串。

        select concat(‘aaa’,’222′);

    

        — insert(str,pos,len,newstr)

        — 将字符串str从第pos地方上马的len个字符替换为新串newstr

        select insert(‘aaaaaa’,1,4,’ss’)

        — 要是新串长度不足len,则将len长度的字符串全体轮换为newstr

        select insert(‘aaaaaa’,1,4,’ss’)

        str = ssaa

        —
假设newstr长度当先len,则将newstr全体安排钦命位置,固然超越长度。

        select insert(‘aaaaaa’,1,4,’sssss’)

        str = sssssaa

        

        — lower(str)

        — 转换到小写

        select lower(‘AAA’);

        

        — upper(str)

        — 转换来大写

        select upper(‘aaa’);

        

        — length(str)

        — 获取字符串长度

        select length(‘aa’);

        

        — char_length(str)

        — 重回字符串str的长短

        select char_length(‘aa’);

        

        — lpad(str,len,padstr)

        — 再次来到字符串str,其右边由字符串padstr填补到len字符长度。

        select lpad(‘aaaaa’,5,’c’);

        —
当str不满足len长度时,才会动用padstr填补,如若str超出len长度限制,则不进行填空。

        

        — rpad(str,len,padstr)

        — 与lpad相反。

        

        — trim(str)

        — 去掉字符串两边的空格。

        select trim(‘  a’);

        

        — repeat(str,count)

        — 重临str重复count次的结果。

        select repeat(‘a’,5);

        

        — replace(str,from_str,to_str);

        — 用字符串to_str替换字符串str中保有的字符串from_str

        select replace(‘aabbccdd’,’bb’,’ss’);

        

        — substring(str,pos,len)

        — 重回从字符串str的pos地方起len个字符长度的子串。

        select substring(‘aaabbbccc’,3,2);

    时间函数

        — curdate()

        — 重临当明日期 年月日

        select curdate();

        

        — curtime()

        — 重返当前时光 时分秒

        select curtime();

        

        — now()

        — 重回当前的日子和岁月

        select now();

        

        — week(date)

        — 重临钦点日期为一年中的第几周

        — 其中date可以是date time datetime timestamp

        select week(timestamp(now()));

        

        select timestamp(curdate())

        

        — year(date)

        — 重回钦定日期的年份

        select year(now());

        

        — hour(time)

        — 重回日期的小时值

        select hour(now());

        

        — minute(time)

        — 重回time的分钟值

        select minute(now());

        

        — monthname(date)

        — 重临date的月份名

        select monthname(now());

        

        — date_format(date,fmt)

        — 重返按字符串fmt格式化日期date的值

        — %a:缩写星期名

        — %b:缩写月名

        — %d:日(05,06)

        — %e:日(5,6)

        — %H:24小时制(05,06)

        — %h:12小时制(05,06)

        — %k:24小时制(5,6)

        — %l:12小时制(5,6)

        — %s:秒

        — %S:秒

        — %Y:年,4位

        — %y:年,2位

        select date_format(now(),’%yy年%M月%d日’);

        

        — date_add(date,interval exp type)

        — 重临一个日子或时刻值加上上一个岁月距离的年华值。

        — year:年 — YY

        — month:月 — MM

        — day:日 — DD

        — hour:时 — hh

        — minute:分 — mm

        — second:秒 — ss

        select date_add(now(),interval 1 year);

        date = 2017-04-05 12:29:19

        

        — datediff(expr1,expr2)

        — 重返初叶时间expr1和终止时间expr2之间的天命。

        select datediff(‘2012-08-08’,now());

        — 注意:expr必须是规范时间格式。

 

    数学函数

        — abs(x)

        — 重返x的相对值

        select abs(-1);

 

        — ceil(x)

        — 再次回到相当的大于x的小小整数值

        select ceil(3.8);

 

        — floor(x)

        — 重返非常的小于x的最大整数值

        select floor(3.9);

    

        — mod(x,y)

        — 返回x/y的模(取余)

        select mod(5,3);

        select round(1.5,0);   该方法能够四舍五入取整数。

        

        — rand()

        — 再次回到1个0-1.0之内的私自浮点数,(0 <= x <=1.0)

        select rand();

        

        — round(x,y)

        — 再次回到参数x的四舍五入的y位小数的值

        — 只四舍五入小数位,不取整

        select round(1.2338,3);

        

        — truncate(x,y)

        — 再次回到数字x截断为y位小数的结果

        select truncate(1.222333,5);

——索引

    索引管理
        概念:
               
索引是确立在表上,是对数据库表上的一列或然多列举办排序的一种结构
                作用:
                        升高对数码库表查询的进度。
                       
也就是依据拼音或部首查找某字在词典中的地方——词典的目录。
                       
对于数据库来讲,创制索引,正是将数据库中重点字的职责树立目录,方便于查找。
                索引的积存类型:
                        BTREE:
                        HASH:
                        分化的存款和储蓄引擎接纳索引的蕴藏类型不一致。
                        查看数据库引擎:
                                show engines;
                       
各个引擎扶助的遵守不一致,所以索引的蕴藏类型也不比,innodb存储类型是BTREE
        使用索引的带价:
                一 、索引必要占用数据表以外的物理存款和储蓄空间。
               
二 、创设索引和维护索引要费用时间(数据库实行的操作,而不是程序员举办的操作)
                         
只要插入只怕去除数据,索引都要修改。(能够全方位去除,然后重新建表,速度略快)
               
叁 、当对表进行更新操作时,索引要求被重建,降低数据的保险速度。
        索引类型:
                一 、普通索引
                        能够在数据表的任意列建立该索引。
                        创制格局:
                            1)创设表时创办索引
                                create table 表名
                                (
                                    [字段表达]
                                    index [索引名] ( 列名 [长度] )
                                );
                            2)修改表时添加索引
                                alter table 表名
                                add index[索引] (列名 [长度])
                            3)创造表后成立索引
                                create index 索引名 on
表名(列名[长度])
                        注意:
                               
假诺要创制索引的列是char或varchar类型,长度能够低于实际尺寸。
                二 、唯一索引
                        创建索引列的唯一值
                       
相对于平日索引,唯一索引创造索引时在index前增进unique
                叁 、主键索引
                        主键索引是一种新鲜的唯一索引,不一致意为null。
                        主键索引是在给表设置主键时自动成立。
                        二个表只好有三个主键,即只好有一个主键索引。
                四 、全文索引
                ⑤ 、单列索引和多列索引
                        单列索引:
                                索引建立在表中的某一列上。
                        多列索引:
                                索引建立在表中的多少个列上
                                例如:
                                        create index 索引名 on
表名(列名1[长度],列名2[长度])
                                        create index index_id_name on
t1(id,name(2))
                                           
将多个列作为2个目录,能够增加查询成效。
                六 、删除索引
                    不能修改索引,只可以删除索引,再次创下造。
                        alter table 表名
                        drop index 索引名;
                    或者
                        alter table 表名
                        drop index 索引名 on 表名;
                    例如
                        alter table t1
                        drop index index_id;
                ⑦ 、建立目录的原则
                    最符合建立目录的列是where子句中的列。
                    索引列的值不雷同的愈来愈多,索引效果越好
                           
比如:不建议在x性别列建索引,因为重新值太多。
                    使用短索引
                            取字符类型的前几个字符。
                    利用最左前缀
                            多列索引
                            create index index_id_name on
t1(id,name(2))
                               
当创制了index_id_name索引时,查询id可以,查询id,name也可以。
                                只如若左侧的索引列即可。
                    不要过分施用索引
                            因为创立和爱抚索引须求开支时间。

——视图

    视图、存款和储蓄进度和自定义函数的区分:
            视图仅限于查询,而存款和储蓄过程和自定义函数能够操作表。

    能够将全体消息寄存到一张视图中,再由其它条件举办筛选。
    定义:
            视图是一种数据库对象,其情节由询问(来自于查询)定义
           
实例:创制视图,查询学号,姓名,联系电话,班级,选课号,课程名,成绩,教授名称
                    create view view_v

                    as

                    select
t_student.sid,sname,stelephone,sclass,t_score.cid,cname,score,tname

                    from
t_student,t_score,t_course,t_teacher,t_teachcourse

                    where t_student.sid = t_score.sid and t_score.cid
= t_course.cid

                    and t_course.cid = t_teachcourse.cid and
t_teachcourse.tid = t_teacher.tid

 

                    select * from view_v
    目的:
            用于集中、简化和定制显示数据库中的数据新闻。
            扩大数据的安全性。
            升高表的逻辑独立性。
                数据的修改不会影响视图定义。 
    创立视图
            T-SQL语句
            语法:
                create [or replace] view 视图名
                as
                <select 语句> 

            or replace 是替换当前视图。
    对视图的尤其验证
        一 、视图是七个虚拟表,从三个或多少个表中导出(查询)
        二 、视图也足以从视图中程导弹出。
        ③ 、其剧情由询问语句(select)定义生成
       
肆 、在数据库中不存在视图的询问内容,只设有视图的定义,哪天用实体,哪天实施视图的概念。
    管理视图
        ① 、使用视图:对视图能够像对待表一样进行查询和修改
                查询:select * from view_v
                修改:
                        update view_v
                        set score = 65
                        where sid = 2016001;
                        因为视图是表的映射,所以修改视图也正是修改表。
                        只好修改表的原数据,不能够修改总括出来的数量。
        二 、查看视图定义(命令)
                desc 视图名;
                show create view 视图名;
        叁 、修改视图定义
                适用于初次创设和改动:
                    create or replace view 视图名
                    as
                    select 语句;
                    如若视图不存在,则创制视图,若是存在则修改视图

                必须保障视图已经存在:
                    alter view 视图名
                    as
                    select 语句;
                    仅限于修改已存在的视图。

        四 、删除视图
                drop view [if exists] 视图名
                判断假诺视图存在,则删除视图视图。

目录演习-student数据库

    1、在t_student 表中的sname列创建唯一索引

        create index index_sname on t_student(sname(2));

 

        alter table t_student

        add index ss (sname(2)); 

    2、在t_student表中的sname和stelephone列创设多列索引

    3、在t_teacher表中的tname创设普通索引

    4、在t_course表中的cname列创立唯一索引

    伍 、删除上述索引

    drop index index_sname on t_student;

 

    alter table t_student

    drop index index_sname;

 

视图演习-student数据库

 

    一 、成立视图

        a)创设视图view_selStu,能够显示选修课程的学生的学号,姓名,选修科目代号,科目名称,科目战表

            create view view_selstu

            as

            select
t_student.sid,t_student.sname,t_course.cid,cname,score

            from t_student,t_course,t_score

            where t_student.sid = t_score.sid 

            and t_score.cid = t_course.cid;

        b)创制视图view_selSubject,能够显示选修科目代号,科目名称和学科战表

            create view view_selstu

            as

            select
t_student.sid,t_student.sname,t_course.cid,cname,score

            from t_student,t_course,t_score

            where t_student.sid = t_score.sid 

            and t_score.cid = t_course.cid;

        c)成立视图view_execllent,能够体现选修课战绩>85的学习者的学号,姓名,选修科目名称,成绩

            create view view_execllent

            as

            select t_student.sid,sname,cname,score

            from t_student,t_course,t_score

            where t_student.sid = t_score.sid

            and t_score.cid = t_course.cid

            and score > 85;

        d)创立视图view_fail,能够显得选修课成绩不及格的学员学号,姓名,班级,课程号,课程名,战表新闻

            create view view_fail

            as

            select t_student.sid,sname,sclass,t_course.cid,cname,score

            from t_score,t_student,t_course

            where t_student.sid = t_score.sid

            and t_score.cid = t_course.cid

            and score < 60

    

    贰 、通过所创制的视图查询数据消息

        a)在view_execllent 中询问选修课成绩在85-95的音讯

            select * from view_execllent

            where score between 85 and 95;

        b)在view_fail中询问各班不及格的成绩音讯

            select * from view_fail

            where score < 60;
        c)在view_selSubject中执会调查计算局计各科目标最高战绩,最低成绩,平均战绩

            select max(score) ‘最高战表’,min(score)
‘最低成绩’,avg(score) ‘平均战表’

            from view_selsubject

    叁 、通过视图修改数据

        a)修改view_fail,将某一不及格战表新闻修改成57分

            update view_fail

            set score = 60

        b)修改view_selStu,修改某一学童的选修科目代号及教程名称

            update view_selstu

            set cname = ‘2’

            where cid = 2

    四 、修改视图定义

        a)修改视图view_execllent,
能够展现选修课战绩>85的学员的学号,姓名,选修科目,成绩,科目先生

            alter view view_execllent

            as

            select t_student.sid,sname,cname,score,tname

            from t_student,t_score,t_course,t_teachcourse,t_teacher

            where t_student.sid = t_score.sid

            and t_score.cid = t_course.cid

            and t_course.cid = t_teachcourse.cid

            and t_teachcourse.tid = t_teacher.tid

            and score > 85;

        b)修改视图view_selStu,能够显示选修课程的学习者的学号,姓名,选修科目代号,科目名称,科目成绩,班级,教授姓名

            create or replace view view_selstu

            as

            select
t_student.sid,sname,t_course.cid,cname,score,sclass,tname

            from t_student,t_course,t_score,t_teachcourse,t_teacher

            where t_student.sid = t_score.sid

            and t_score.cid = t_course.cid

            and t_course.cid = t_teachcourse.cid

            and t_teachcourse.tid = t_teacher.tid

    

    5)删除视图:将上述有个别视图删除

        drop view if exists view_selstu

 

——存款和储蓄进度

    存款和储蓄进度概述
            概念:
                   
存储进度是一种数据库对象,是为了实现有些特定职分,将一组预编写翻译的SQL语句以二个存款和储蓄单元的格局储存在服务器上
                    ,由用户通过点名存储进程的名字钦点它。
                    MySQL5.0从前不协助存款和储蓄进度。
                    示例:
                           
创制2个储存进度,输入学生的学号,查询学生的详细消息。

                            delimiter &&           
//分隔符,当下二次相见&&时就会终止     能够动用别的字符 例如://

                            create procedure sp_stu(in inputsid int ) 
  in 输入     int 参数类型

                            reads sql data

                            begin 

                            select * from t_student

                            where sid = inputsid;

                            end && 
                            delimiter;            //
将分隔符再度设为分号  ;
 

                            call sp_stu(2014003);        //
使用存储过程

                            对于 select * from where sid =
二零一四001,数据库每一趟执行都急需分析查询语句,转成执行。
                           
而对于仓库储存进度,在服务器已经编写翻译好,不须求再去分析语句。 

                意义:
                       
壹 、存款和储蓄进度只在开创时进行编写翻译,现在推行都不供给编写翻译,而貌似的SQL语句执行二回就必要编写翻译一遍,所以选择存款和储蓄
                             进程会增强施行功能。
                       
贰 、存款和储蓄进程能够重复使用,能够减去数据库开发职员的工作量。

 

    创立存款和储蓄进程
            不带参数的仓库储存进度
                    语法:
                            delimiter &&
                            create procedure 存款和储蓄进度名()
                            reads sql data
                            begin
                                    存款和储蓄进程语句
                            end &&
                            delimiter ;
                    示例:
                           
创造存款和储蓄进度sp_fail,查询全体选修课成绩不及格的学习者学号,姓名,课程号,课程名。

                            delimiter &&

                            create procedure sp_fail()

                            reads sql data

                            begin

                                select
t_student.sid,sname,t_course.cid,cname from
t_student,t_course,t_score

                                where t_student.sid = t_score.sid

                                and t_score.cid = t_course.cid

                                and score < 60;

                            end &&

                            delimiter ;

                            执行:call sp_fail(); 

                带有输入与参数的存款和储蓄进度。
                        语法:
                                delimiter &&
                                create procedure 存款和储蓄进程名(in 参数名
参数数据类型,……)    // 能够输入多个参数。
                                reads sql data
                                begin
                                        存款和储蓄进程语句
                                end &&
                                delimiter ;

                        说明:
                                输入参数经常作为查询条件。
                                 
                        示例:
                               
成立存款和储蓄进度sp_stuscore输入学生学号,查询学生学号,姓名,课程名和成就。
                                delimiter &&

                                create procedure sp_stuscore(in
inputsid int)

                                reads sql data

                                begin

                                    select
t_student.sid,sname,cname,score

                                    from t_student,t_course,t_score

                                    where t_student.sid = t_score.sid

                                    and t_score.cid = t_course.cid

                                    and t_student.sid = inputsid;

                                end &&

                                delimiter ;

                        执行:call sp_stuscore(2016001)

                带输出参数的存款和储蓄进度:
                        语法:
                                delimiter &&

                                create procedure sp_phone(in inputsname
varchar(20),out otelephone varchar(11))

                                reads sql DATA

                                begin

                                    select stelephone into otelephone
from t_student    — 将查找到的stelephnoe赋值给otelephone

                                    where sname = inputsname;

                                end&&

                                delimiter;

 

                                — 调用

                                call sp_phone(‘马云’,@telephone);

                                — 输出

                                select @telephone;

 

                        示例:
                                —
定义存款和储蓄进度sp_score,输入学生学号和课程号,重回学生的成就

                                delimiter &&

                                create procedure sp_score(in inputsid
int,in inputcid int,out outscore int)

                                reads sql data

                                begin

                                    select score into outscore 

                                    from t_student,t_score

                                    where t_score.sid = t_student.sid

                                    and inputsid = t_student.sid

                                    and inputcid = t_score.cid;

                                end &&

                                delimiter;

 

                                drop procedure sp_score;

 

                                — 调用

                                call sp_score(2016001,2,@score);

 

                                — 输出

                                select @score;

                        银行转化示例:

                                — 创制银行表

                                create table bank

                                (

                                    id int not null primary key,

                                    name varchar(5) not null default”,

                                    balance float not null default 0

                                );

 

                                insert into bank
values(10001,’张三’,100);

                                insert into bank
values(10002,’李四’,200);

 

                                — 创立存储进度

                                delimiter &&

                                create procedure bank_test(in inputid
int,in outputid int, in money float)

                                modifies sql data    // 此处使用midifies
 因为供给修改四个内容

                                begin

                                    — 转入账户

                                    update bank

                                    set balance = balance + money

                                    where id = inputid;

 

                                    — 转出账户

                                    update bank

                                    set balance = balance – money

                                    where id = outputid;

                                end &&

                                delimiter;

 

                                call bank_test(10001,10002,100);

 

                                select * from bank;

 

                删除存款和储蓄进程:
                        drop procedure 存储进度名;

                        

——触发器

    触发器的概念
            触发器是由inert update delete等事件来触发某种特定事件

    触发器的多个脾性
            原子性:Atomiity
                   
事务是1个完全的操作,事务的各步操作都以不可分的(原子的);要么都进行,要么都不进行。
             一致性:Consistency
                   
当事情完结时,数据必须处于相同状态。也便是说,在业务开始在此以前,数据存款和储蓄中的数据处于同一状态。在正在进
                    行的政工中,数据可能处于不一致的情形,例如,数据或然有部分修改。但是,当工作成功完毕时,数据必须再一次
                    回到已知的同等状态,通过业务对数码所做的修改不能破坏数据,或然说事务无法使数据存款和储蓄处于不安宁的情形。
            隔离性:Isolation
                   
对数码实行修改的具有并发事务是并行隔开分离的,这标志工作必须是独自的,它不应以其余形式注重于或影响别的作业。
            永久性:Durability
                   
事务完毕后,它对数据库的改动被永远保存,事务日志能够维持工作的永久性。
    创立触发器的语法
            语法格式
                    create trigger 触发器名
                    before | after 触发事件
                    on 表名 for each row
                    触发执行语句
            说明:
                    trigger:触发器关键字
                    before | after:钦点执行语句在接触前照旧触发后。
                    触发事件:insert  delete  update
                    触发执行语句:当有接触事件发生所执行的讲话。
                    on表名:触发事件发生的表。
    创建触发器示例
            new代表新加上的数据行对象。
            old表示刚删除的数据行对象。
            壹 、扩大一条选课音信后,相应的学科实际选课人数增1。
                    — ① 、扩张一条选课新闻后,相应的骨子里选课人数扩大1

                    create trigger tr_choose

                    after insert

                    on t_score for each row

                    — 插入一条选课音信后 要求精晓选课号是有个别

                    update t_course

                    set realcount = realcount + 1

                    where cid = new.cid

 

                    select * from t_course

                    — 使用触发器

                    insert into t_score (sid,cid)values(2016001,1)

            贰 、删除一条选课音讯后,相应的课程实际选课人数减1

                    create trigger tr_del

                    after delete

                    on t_score

                    for each row

 

                    update t_course

                    set realcount = realcount -1

                    where cid = old.cid;

 

                    select * from t_course;

                    select * from t_score

 

                    delete from t_score

                    where cid = 1 and sid = 2016001;

    删除触发器
            drop trigger 触发器名;

——MySQL保存大数据类型

专业SQL中提供了如下类型来保存大数据类型:
    BLOB:binary 字节流
    CLOB:character 字符流
    类型                        长度
tinyblob                    2^8-1B(256B)
blob                          2^16-1B(64KB)
mediumblob             2^64-1B(16MB)
longblob                   2^32-1B(4GB)

tinyclob                    2^8-1B(256B)
clob                          2^16-1B(64KB)
mediumclob             2^64-1B(16MB)
longclob                   2^32-1B(4GB)

只是在MySQL中从不提供以上字符流的八种数据类型,二十使用如下各样档次来处理大文本数据:
    tinytext、text、mediumtext、longtext

第1要求创设一张表,表中有二个mediumblob(16M)类型的字段:
create table tab_bin(
    id int primary key auto_increment,
    filename varchar(100),
    data mediumblob
);

向数据库插入二进制数据要求选择PreparedStatement为原serBinaryStream(int,
InputStream)方法来完毕。

还亟需在my.ini中添加如下配置:
    max_allowed_packet=102400
安装最大可传输数据包 大小。

示范代码:

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.OutputStream;

import java.sql.Blob;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

 

import javax.sql.rowset.serial.SerialBlob;

 

import org.apache.commons.io.IOUtils;

import org.junit.Test;

 

import sun.nio.ch.IOUtil;

 

import com.wyc.demo03.JDBCUtils;

 

/*

 * 2016年12月1日15:45:48

 * 向数据库中插入大文本数据

 */

public class Demo04 {

    /*

     * 把mp4保存到数据库中

     */

    @Test

    public void fun1() throws Exception {

        /*

         * 壹 、获得Connection对象 二 、给出SQL模板,创造pstmt
③ 、设置SQL模板中的参数

         * 四 、调用pstmt的executeUpdate()方法执行SQL语句

         */

        Connection conn = JDBCUtils.getConnection();

        String sql = “insert into tab_bin values(?,?,?)”;

 

        PreparedStatement pstmt = conn.prepareStatement(sql);

        pstmt.setInt(1, 1);

        pstmt.setString(2, “Alan Walker – Fade.mp3”);

        /*

         * 获得Blob 一 、现把公文变成byte数组 二 、再利用byte数组创造Blob

         */

        // 使用字节数组成立Blob

        byte[] bytes = IOUtils.toByteArray(new
FileInputStream(“F:/Alan Walker – Fade.mp3”));

        // Blob是一个接口,能够应用它的兑现类来创制对象

        Blob blob = new SerialBlob(bytes);

        // 设置参数

        pstmt.setBlob(3, blob);

        pstmt.executeUpdate();

    }

 

    /*

     * 从数据库中取出VCD

     */

    @Test

    public void fun2() throws Exception {

        // 得到Connection对象

        Connection conn = JDBCUtils.getConnection();

        // 给出select模板,创建pstmt

        String sql = “select * from tab_bin”;

        PreparedStatement pstmt = conn.prepareStatement(sql);

 

        // pstmt执行executeQuery方法,执行查询,得到ResultSet

        ResultSet rs = pstmt.executeQuery();

 

        // 获取第一列名为data数据

        if (rs.next()) {

            Blob blob = rs.getBlob(“data”);

            /*

             * 将Blob保存到硬盘 壹 、通过Blob获得输入流对象
二 、自个儿创办输出流对象 叁 、把输入流的数量写入到输出流中

             */

            InputStream in = blob.getBinaryStream();

            OutputStream out = new FileOutputStream(“D:/Alan Walker –
Fade.mp3”);

            IOUtils.copy(in, out);

        }

    }

}

——MySQL事务

为了方便业务的操作,须要创建四个account表:
create table account(
    id int primary key auto_increment,
    name varchar(20),
    balance number(10,2)
);
insert into account(name, balance) values (“张三”, 100000);
insert into account(name, balance) values (“李四”, 100000);
insert into account(name, balance) values (“王五”, 100000);

面试简单问到:
① 、事务的四大特点(ACID)
   
1)原子性(Atomicity):事务中颇具操作是不足再分开的原子单位,事务中兼有操作依然全体执行成功,要么全体实施破产。
   
2)一致性(Consistency):事务执行后,数据库状态与其余业务规则保持一致,如转账业务,无论业务执行成功与否,参加转发的四个账号越之和应该是不变的。别的天性都以为了这一特点服务的。
   
3)隔开分离性(Isolation):隔绝性是指在出现操作中,差异事物之间应该隔绝开来,使种种并发中的实物不会相互烦扰。
   
4)持久性(Durability):一旦事情提交成功,事务中持有的数目操作必须被持久化到数据库中,尽管付出业务后,数据库发生崩溃,在重启数据库时,也务必能确认保证通过某种机制复苏数据。

二 、MySQL中的事务
   
在暗中认可意况下,MySQL每执行一条SQL语句,都是贰个单身的事情,假设要求在一个工作中包罗三个SQL语句,那么要求敞开事务和了结工作:
        *   开启事务:start transaction;
        *   甘休工作:commit或rollback
    在实践SQL语句此前,先举办start
transaction,那就敞开了叁个工作(事务的起源),然后能够推行多条SQL语句,最终要甘休工作,可以利用commit提交事务,即工作中的多条SQL语句所作出的熏陶会被持久化到数据库中,大概使用rollback回滚,即回滚到事情的起源,此前所作的富有操作都被撤消了。

——事务隔开级别

① 、事务的面世读取难点
    *   脏读:读取到另一个作业未提交的数码,即读取到了脏数据。
    *  
不可重复读:两回读取内容分裂,因为另一工作对该记录做了改动。
    *  
虚读:对同样张表的三回搜索内容不平等,读到另一政工已交由的多少。

    不可重复读和虚读的分别:
        *   不可重复读是读取到了另一政工的翻新。
        *   虚读是读取到了另一事物的插入(MySQL中不或者测试虚读)。

② 、四大隔绝级别
   
五个等级的政工隔开级别,在平等数量环境下,使用同样的输入,执行同一的干活,遵照差异的割裂级别,能够导致差异的结果,差别工作隔断级别能够消除的多少出现难题的能力是例外的。
    1)SERIALIZABLE(串行化):
        *  
不会现出别的并发难题,因为它是对相同数据的访问是串行的,非并发访问。
        *   质量最差。
    2)REPEARABLE READ(可重新读):(MySQL暗中同意)
        *   防止脏读和不足重复读,无法处理虚读难题。
        *   性能比SERIALIZABLE好。
    3)READ COMMITTED(读取已交由数据):(Oracle暗许)
        *   幸免脏读,不可能处理不可重复读,也不能处理虚读。
        *   性能比REPEATABLE READ好。
    4)READ UNCOMMITTED(读取未提交数据):
        *   或然出现其余事情并发难题。
        *   品质最高。

    MySQL默许的割裂级别为REPEATABLE READ,能够通过下边包车型地铁语句查看:
        select @@tx_isolation
    也能够通过上面语句来安装当前延续的隔开级别:
        set transaction isolationlevel [4 选 1]

——MySQL语句

     建表

            通过图片化界面建表。

            通过命令建表:

                create table Course

                (

                        Cou_name nvarchar(50) not null,

                        Cou_id int not null primary key,

                        Cou_tercher nvarchar(20) not null

                )

 

                create table Student

                (

                        Stu_id int not null primary key,

                        Stu_name nvarchar(50) not null,

                        Stu_sex nchar(2) not null,

                        Stu_age int not null,

                        Cou_id int foreign key references
Course(Cou_id) not null

                )

            create table 最终四个字段的后边建议不用写逗号。

                references的功力是外键表制造外键时引用主键表的主键。

    修改字段属性:
        update 表名
        set 字段名 = 须求修改的值
        where 字段名 = 值;    //假诺不加限制,则会将该字段全体改动。
    
    添加记录:
        insert into 表名 (列名) values(值列表);
        列名能够不难,依照值列表实行判断。

    删除:
        delete from 表名
        where 字段 = 值;

    创建表:
        create table 表名
        (
            字段名 数据类型
        );

    查看表结构:
        desc 表名;
        show create table 表名;

    字段修改属性:
        修改字段数据类型:
            alter table 表名
            modify 字段名 数据类型;
    
    修改字段名:
            alter table 表名
            change 旧属性名 新属性名 新属性类型;
            当不点名原有字段的自增加时,自拉长会丢失。

    扩张字段:
        alter table 表名
        add 字段名 数据类型(完整性约束原则);
            假如想在率先个职位扩充字段,必要在数据类型后边加上first
            借使想在钦命地点添加,能够应用 after 字段名;   
意思是在该字段前边添加。

    删除字段:
        alter table 表名
        drop 字段名;

    删除表:
        drop table 表名;

    主键约束:
        create table 表名
        (
            主键 数据类型 primary key
        );

    外键约束:
        create table 表名
        (
            外键名 数据类型,
            constraint fk_主键约束名 foreign key (外键名) references
主键表(主键)
        );

    唯一约束:
        create table 表名
        (
            字段名 数据类型 unique
        );

    暗中认可约束:
        create table 表名
        (
            字段 数据类型 default 暗中同意值
        );

    检查约束:
       
在MySQL中数据库引擎会分析check语句,然则会忽视check约束,所以有关字段限制范围的操作,可以在先后中限定。

    非空约束:
        create table 表名
        (
            字段名 数据类型 not null
        );

    自动增进列:
        create table 表名
        (
            字段名 数据类型(必须是int型) primary key(必须是主键)
auto_increment
        ) auto_increment = 一千1;(能够在此间钦赐暗中同意值开始值)

——MySQL入门最基本语句
    连接服务器:
        mysql -uusername -ppassword
   
当连上服务器后,我们第叁面对的是库,库有3个或七个,假设不明白有怎样库,需求查阅全体的库,语句如下
    查询全体库:
        show databases;

    设置字符集:
        set names gbk;
        设置mysql字符集为gbk。 

   大家要想对表/行实行操作的话,首先要选库
    选库语句:
        use 库名;

    选库之后,面对的是多张表:
    查看全部表:
        show tables;

    创立三个数据库:
        create database [IF NOT EXISTS] 数据库名 [charset=字符集]; 
  — 中括号内容可选
        create database if not exists mydb charset=utf8;

    删除数据库:
        drop database 数据库名;

    修改数据库编码:
        alter database mydb character set utf8;

    数据库怎么样改名?
        MySQL中,表和列能够改名,可是database无法改名。
        修改表名
            rename table oldname to newname;
            或者:alter table 原表名 rename to 新表名;

        PHPMyAdmin:新建库,把全体表复制到新库,再删除旧库完结的。

    创建表:
        create table test
        (
            id int
        ); 

    清空表
        truncate 表名;
        truncate和delete的区别:
            truncate清空表并且保留表结构,不记录日志。
            delete只删除数据,记录日志。

    退出
        exit;
        quit;
        \c  跳出执行

——错误提醒
    1366:客户端未注明字符集。
    1064:语法错误。

 

————————————————————————————————————————————————————————

查询!!!!!!!!!!!!!!!!!!

以goods表作为练习表。

— 创建goods表

create table goods

(

    goods_id mediumint(8) unsigned primary key 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 smallint(5) 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’

);

 

— 将shop.goods表中的数据插入testshop.goods表

insert into testshop.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 shop.goods;

goods_id cat_id goods_sn goods_name click_count goods_number
market_price shop_price add_time is_best is_new is_hot

|  1 |  4| ECS000000 | KD876                        |       7 |        
        1 |         1665.60 |    1388.00 | 1240902890 |       1 |      1
|      1 |

|  3 |  8| ECS000002 | 三星原装5800动圈耳机  |       3 |               24
|             81.60 |        68.00 | 1241422082 |       0 |      0 |    
 0 |

|  4 |  8| ECS000004 | SamsungN85原装充电器|       0 |               17 |
            69.60 |        58.00 | 1241422402 |       0 |      0 |    
 0 |

|  5 |11| ECS000005 | vivo原装M2卡读卡器  |       3 |                8 |
             24.00 |        20.00 | 1241422518 |       1 |      1 |    
 0 |

|  6 |11| ECS000006 | 胜创KINGMAX内存卡 |       0 |               15 |  
           50.40 |       42.00 | 1241422573 |       0 |      0 |      0
|

|  7 | 8| ECS000007  | 中兴N85原装立体声动圈耳机HS-82 | 0 |   20 |        
  120.00        100.00 | 1241422785 |       0 |      0 |      0 |

|  8 | 3| ECS000008  | 飞利浦9@9v              |       9 |            
     1 |           478.79 |      399.00 | 1241425512 |       1 |      1
|      1 |

|  9 | 3| ECS000009  | 诺基亚E66                 |      20 |            
    4 |         2757.60 |    2298.00 | 1241511871 |       1 |      1 |  
   1 |

|10 | 3| ECS000010  | 索爱C702c                |      11 |             
    7 |         1593.60 |    1328.00 | 1241965622 |       0 |      0 |  
   1 |

|11 | 3| ECS000011  | 索爱C702c                 |       0 |             
    1 |               0.00 |    1300.00 | 1241966951 |       0 |      0
|      0 |

|12 | 3| ECS000012  | 一加A810           |      13 |                
8 |          1179.60 |      983.00 | 1245297652 |       0 |      1 |    
 0 |

|13 | 3| ECS000013  | 诺基亚5320 XpressMusic |13|                 8 |  
      1573.20      1311.00 | 1241967762 |       0 |      0 |      1 |

|14 | 4| ECS000014  | 诺基亚5800XM          |       6 |                 
1 |         3150.00      2625.00 | 1241968492 |       0 |      0 |    
 1 |

|15 | 3| ECS000015  | NokiaA810           |       8 |               
  3 |           945.60 |       788.00 | 124一九七零703 |       0 |      1 |
     1 |

|16 | 2| ECS000016  | 恒基伟业G101           |       3 |                
0 |           988.00 |       823.33 | 1241966949 |       0 |      0 |  
   0 |

|17 | 3| ECS000017  | 夏新N7                      |       2 |          
      1 |          2760.00 |    2300.00 | 1241969394 |       1 |      0
|      1 |

|18 | 4| ECS000018  | 夏新T5                      |       0 |          
      1 |          3453.60 |    2878.00 | 1241969533 |       0 |      0
|      0 |

|19 | 3| ECS000019  | 三星SGH-F258         |       7 |               12
|          1029.60 |      858.00 | 1241970139 |       1 |      1 |    
 1 |

|20 | 3| ECS000020  | 三星BC01                 |      14 |             
12 |            336.00 |      280.00 | 1241970417 |       1 |      1 |  
   1 |

|21 | 3| ECS000021  | 金立 A30                   |       4 |           
   40 |          2400.00 |   2000.00 | 1241970634 |       0 |      0 |  
   0 |

|22 | 3| ECS000022  | 多普达Touch HD       |      15 |                1
|          7198.80 |   5999.00 | 1241971076 |       1 |      1 |      0
|

|23 | 5| ECS000023  | 诺基亚N96                |      17 |             
  8 |          4440.00 |   3700.00 | 1241971488 |       1 |      1 |    
 0 |

|24 | 3| ECS000024  | P806                         |      35 |         
  100 |          2400.00 |   2000.00 | 1241971981 |       1 |      1 |  
   1 |

|25 |13| ECS000025 | 小灵通/固话50元充值卡 |    0 |                2 |  
           57.59 |       48.00 | 124一九七三709 |       1 |      0 |      1
|

|26 |13| ECS000026 | 小灵通/固话20元充值卡 |    0 |                2 |  
           22.80 |       19.00 | 1241973789 |       0 |      0 |      1
|

|27 |15| ECS000027 | 联通100元充值卡       |        0 |               2
|             100.00 |       95.00 | 1241975894 |       1 |      1 |    
 1 |

|28 |15| ECS000028 | 联通50元充值卡         |        0 |               0
|              50.00 |        45.00 | 124壹玖柒叁976 |       0 |      0 |  
   1 |

|29 |14| ECS000029 | 移动100元充值卡       |        0 |               0
|                0.00 |        90.00 | 124一九七一022 |       1 |      0 |  
   1 |

|30 |14| ECS000030 | 移动20元充值卡         |        1 |               9
|              21.00 |        18.00 | 124一九七五114 |       1 |      0 |  
   1 |

|31 | 3| ECS000031  | 中兴E8                |        5 |            
  1 |          1604.39 |    1337.00 | 1242110412 |       0 |      0 |  
   0 |

|32 | 3| ECS000032  | 诺基亚N85                 |        9 |            
  4 |          3612.00 |    3010.00 | 1242110760 |       0 |      1 |  
   1 |

——where条件查询

    相比运算符
        <    <=    =    >    >=    <>    in
    逻辑运算符
        not 或 !    or 或 ||    and 或 &&
    — 一 、查询主键为32的商品

    select * from goods

    where goods_id = 32;
     

    — ② 、查询不属于第②栏的拥有商品

    select * from goods

    where cat_id <> 3;

    

    — 叁 、本店价格当先三千元的货色

    select * from goods

    where shop_price > 3000;

    

    — ④ 、本店价格小于或等于100元的商品

    select * from goods

    where shop_price <= 100;
    

    — 伍 、取出第伍栏和者第三1栏的货物(不能够用or)

    select * from goods

    where cat_id in (4,11);
    

    — 六 、取出100<=店铺价格<=500的货品(不许用and)

    select * from goods

    where shop_price between 100 and 500;
    

    — 七 、取出不属于第一栏目且不属于第①1栏目标货品(and,或not in分别实现)

    select * from goods

    where cat_id <> 3 and cat_id <> 11;

    

    select * from goods

    where cat_id not in(3,11);

    

    — ⑧ 、取出店铺价格高于100且小于300,或然抢先陆仟且低于4000的货物()

    select * from goods

    where (shop_price > 100 and shop_price < 300)

    or (shop_price > 4000 and shop_price < 5000);

        

    — ⑨ 、取出第三个栏目下边价格<一千或>贰仟,并且点击量>5的泛滥成灾商品

    select * from goods

    where cat_id = 3 and (shop_price < 1000 or shop_price >
3000) and click_count > 5;

        注意:必须加括号,不然暗中同意or两边各为3个条件。
                   or的优先级最低。
    

    — ⑩ 、取出第②个栏目上边包车型客车货色(注意:1栏目下边没商品,但其子栏目下有)
       
无限极分类,顶尖分类,也正是该大类上面没有直接数据,而是二级分类。
    

    — 1一 、取知名字以”魅族”起首的货色

    select * from goods

    where goods_name like ‘诺基亚%’;
        此处使用模糊查询,而不是正则匹配,因为正则匹配功效低。
        %:匹配零到多少个字符。
        _:匹配二个字符。
    
    — 1二 、取知名字不以”BlackBerry”开首的货物

    select * from goods

    where goods_name not like ‘诺基亚%’;

    

    — 1叁 、取出首个栏目下边价格在一千到2000之内,并且点击量>5
“三星”开端的类别商品

    select * from goods

    where (cat_id = 3 and shop_price > 1000 and shop_price <
3000 and click_count > 5)and goods_name like ‘诺基亚%’;

    

    面试题

    有如下表和数组,把num值处于[20,29]的数改为20,把num值处于[30,39]时期的数改为30。

    +——+

    | num  |

    +——+

    |    3 |

    |   12 |

    |   15 |

    |   25 |

    |   23 |

    |   29 |

    |   34 |

    |   37 |

    |   32 |

    |   45 |

    |   48 |

    |   52 |

    +——+

    

    update test

    set num = 20

    where num between 20 and 29;

    update test 

    set num = 30

    where num between 30 and 39;
    
    或者:
    
    update test
    set num = floor(num/10)*10
    where num >= 20 and num <= 30; 

    
    where查询模型
       
能够把字段看成变量,把where看做Java中if语句中的条件,正是哪条记下能让if为真,就能取出哪条记下。
        非零 并且 非null 非字符串 都为真。
        既然字段是变量,那么变量之间就足以运算。
        例如:
                select market_price – shop_price as discount from
goods

                where goods_id = 1;
               
注意:无法在where中选拔discount,因为where是对原表举行筛选生成二个一时半刻表,discount存在于暂时表中,所以where无
                           法使用discount对原表实行筛选。 
                          
如若想对结果也便是discount实行筛选,只好利用having,因为在where生成结果后,having是对结果再一回开始展览筛选。
     
 ——group by分组查询

    
        max()函数取最大值。
        min()函数取最小值。
        avg()总计平均值
       
count(*)函数总括个数,假设count(列名),在内定列名时则不总结null值。
        那么使用count(*)和count(1)何人更好啊?
           
对于myisam引擎的数据库没有区分,那种发动机的数据库内部有一个计数器总计那行数,当须求输出游数的时候,会一向取出游数
           
对于使用innodb引擎的数据库,使用count(*)直接读行数,会造作用率低下,因为innodb会一行一行的数。
    
        有如下语句:
            select goods_id,sum(goods_number) from goods;
            取出结果为:
            goods_id    sum(goods_number)
                  1                        314
           
对于SQL标准来说,该语句是错误的,不可能被执行,不过在MySQL中得以被执行,但是为了可移植性和规范性,不推荐使用。
    

    — 一 、查询出最贵的商品价位

    select max(shop_price) from goods;

    

    — ② 、查询最新的商品编号

    select max(goods_id) from goods;

    

    — ③ 、查询最有利于的的货色价位

    select min(shop_price) from goods;

    

    — 肆 、查询最旧的商品编号

    select min(goods_id) from goods;

    

    — 五 、查询该店全数商品的仓库储存总量

    select sum(goods_id) from goods;

 

    — 陆 、查询全部商品的平均价

    select avg(shop_price) from goods;

    

    — 七 、查询该店一共有多少种商品

    select count(goods_id) from goods;

    

    — 八 、查询各样栏目上面

    — 最贵商品价位

    — 最低商品价位

    — 商品平均价格

    — 仓库储存总量

    — 商品连串

    — (提醒:伍个聚合函数sum avg max min count 与 group by综合应用)

    select
cat_id,max(shop_price),min(shop_price),avg(shop_price),sum(goods_number)
from goods

    group by cat_id;

    
    
——having分组筛选

 

    — ① 、查询该店的货物比市场价所节省的价钱

    select goods_id,market_price – shop_price from goods;

 

    — 贰 、查询各类商品所积压的货款(仓库储存*单价)

    select shop_price,(goods_number * shop_price) from goods;

    

    — 三 、查询该店积压的总货款

    select sum(goods_number * shop_price) from goods;

    

    — 四 、查询该店每一种栏目上边积压的货款

    select cat_id,goods_number * shop_price from goods

    group by cat_id;

    

    — ⑤ 、查询比市集价省钱200元以上的货物及该商品所省的钱。

    — 用where和having分别完结

    select goods_id,market_price – shop_price as price

    from goods

    where market_price – shop_price > 200;

    

    select goods_id,market_price – shop_price as price

    from goods

    having price > 200;

    

    — ⑥ 、查询积压货款超过2万元的栏目,以及该栏目积压的货款

    select cat_id,sum(goods_number * shop_price) as price

    from goods

    group by cat_id

    having price > 20000;

    

    — 七 、where group by having综联合排练习题

    — 有如下表及数量

    — 查询出2门及2门以上不及格者的平分战表

    — +——+———+——-+

    — | name | subject | score |

    — +——+———+——-+

    — | 张三 | 数学    |    90 |

    — | 张三 | 语文    |    50 |

    — | 张三 | 地理    |    40 |

    — | 李四 | 语文    |    55 |

    — | 李四 | 政治    |    45 |

    — | 王五 | 政治    |    30 |

    — +——+———+——-+

    create table score

    (

        sname char(4) not null,

        ssubject char(5) not null,

        sscore float not null

    );

    insert into score values(‘张三’,’数学’,90);

    insert into score values(‘张三’,’语文’,50);

    insert into score values(‘张三’,’地理’,40);

    insert into score values(‘李四’,’语文’,55);

    insert into score values(‘李四’,’政治’,45);

    insert into score values(‘王五’,’政治’,30);

    insert into score values(‘赵六’,’政治’,90);

    insert into score values(‘赵六’,’政治’,80);

    

    select sname,count(sscore < 60) as c,avg(sscore) as a 

    from score

    group by sname

    having c >= 2        —
错误!因为无论count()中是还是不是为真,都会将行数全体取出,纵然全体过关,会整整取出。

    ## 一种错误做法

    mysql> select name,count(score<60) as k,avg(score) from stu
group by name having k>=2;

    +——+—+————+

    | name | k | avg(score) |

    +——+—+————+

    | 张三     | 3 |    60.0000 |

    | 李四     | 2 |    50.0000 |

    +——+—+————+

    2 rows in set (0.00 sec)

    

    mysql> select name,count(score<60) as k,avg(score) from stu
group by name;

    +——+—+————+

    | name | k | avg(score) |

    +——+—+————+

    | 张三     | 3 |    60.0000 |

    | 李四     | 2 |    50.0000 |

    | 王五     | 1 |    30.0000 |

    +——+—+————+

    3 rows in set (0.00 sec)

    

    mysql> select name,count(score<60) as k,avg(score) from stu
group by name having k>=2;

    +——+—+————+

    | name | k | avg(score) |

    +——+—+————+

    | 张三     | 3 |    60.0000 |

    | 李四     | 2 |    50.0000 |

    +——+—+————+

    2 rows in set (0.00 sec)

    

    #增进赵六后错误暴光

    mysql> insert into stu 

        -> values 

        -> (‘赵六’,’A’,100),

        -> (‘赵六’,’B’,99),

        -> (‘赵六’,’C’,98);

    Query OK, 3 rows affected (0.05 sec)

    Records: 3  Duplicates: 0  Warnings: 0

    

    #荒唐显现

    mysql> select name,count(score<60) as k,avg(score) from stu
group by name having k>=2;

    +——+—+————+

    | name | k | avg(score) |

    +——+—+————+

    | 张三 | 3 |    60.0000 |

    | 李四 | 2 |    50.0000 |

    | 赵六 | 3 |    99.0000 |

    +——+—+————+

    3 rows in set (0.00 sec)

    

    #是的思路,先查看各样人的平分战表

    mysql> select name,avg(score) from stu group by name;

    +——+————+

    | name | avg(score) |

    +——+————+

    | 张三 |    60.0000 |

    | 李四 |    50.0000 |

    | 王五 |    30.0000 |

    | 赵六 |    99.0000 |

    +——+————+

    4 rows in set (0.00 sec)

    

    mysql> # 看各个人挂科情形

    mysql> select name,score < 60 from stu;

    +——+————+

    | name | score < 60 |

    +——+————+

    | 张三 |          0 |

    | 张三 |          1 |

    | 张三 |          1 |

    | 李四 |          1 |

    | 李四 |          1 |

    | 王五 |          1 |

    | 赵六 |          0 |

    | 赵六 |          0 |

    | 赵六 |          0 |

    +——+————+

    9 rows in set (0.00 sec)

    

    mysql> #测算各类人的挂科学科

    mysql> select name,sum(score < 60) from stu group by name;

    +——+—————–+

    | name | sum(score < 60) |

    +——+—————–+

    | 张三 |               2 |

    | 李四 |               2 |

    | 王五 |               1 |

    | 赵六 |               0 |

    +——+—————–+

    4 rows in set (0.00 sec)

    

    #并且总结每人的平分分

    mysql> select name,sum(score < 60),avg(score) as pj from stu
group by name;

    +——+—————–+———+

    | name | sum(score < 60) | pj      |

    +——+—————–+———+

    | 张三 |               2 | 60.0000 |

    | 李四 |               2 | 50.0000 |

    | 王五 |               1 | 30.0000 |

    | 赵六 |               0 | 99.0000 |

    +——+—————–+———+

    4 rows in set (0.00 sec)

    

    #利用having筛选挂科2门以上的.

    mysql> select name,sum(score < 60) as gk ,avg(score) as pj
from stu group by name having gk >=2; 

    +——+——+———+

    | name | gk   | pj      |

    +——+——+———+

    | 张三 |    2 | 60.0000 |

    | 李四 |    2 | 50.0000 |

    +——+——+———+

    2 rows in set (0.00 sec)

    正确答案:
        select sname,sum(sscore < 60) as s,avg(sscore) from score

        group by sname

        having s >= 2; 

——order by排序
    order by 列名 desc/asc

    当需求开展频仍排序时:

        order by 列名1 desc/asc,列名2 desc/asc,

    当得到终极结出集现在,才足以展开排序,因为在结果集生成进程当中举办排序无意义。

    反过来说,排序是针对最终结出,即:order by要放在where/group by
having之后,顺序不可能颠倒。

 

    知识点:

        回收站机制,逻辑删除。

 

——limit限制结果条数
    limit [offset,] n(不包含offset)

    offset:偏移量(跳过多少行,也正是从什么职位上马取出记录,不包蕴offset)

    n:取出条目。

    offset假使不写,也正是limit 0,n

 

    — ① 、按价格由高到低排序

    select * from goods

    order by shop_price;

    

    — 二 、按宣布时间由早到晚排序

    select * from goods

    order by add_time 

    

    — ③ 、按栏目由低到高排序,栏目之中按价格由高到低排序

    select * from goods

    order by cat_id,shop_price desc;

    

    — 肆 、取出价格最高的前四个商品

    select * from goods

    order by shop_price desc

    limit 3;

    

    — 伍 、取出点击量前三名到前五名的货品

    select * from goods

    order by click_count desc

    limit 2,3

    

    多少个子句的演算顺序

        where  group by  having  order by  limit

 

——where型子查询

    把内层查询结果作为外层查询的可比规范

    例题:

        — 取出每种栏目下的新型的货物

        — 第二步:先查询种种栏目下风行商品的goods_id

        select max(goods_id),cat_id from goods

        group by cat_id;

        — 第二步:把goods_id对应的商品新闻输出即可

        select * from goods

        where goods_id in

        (

            select max(goods_id) from goods

            group by cat_id

        );

    where子查询的SQL语句无法select * from表

        因为:

            要是where 列名 = (内层SQL)
则内层SQL重返的总得是单列单行值,也正是单个值。

            假如where 列名 in (内层SQL)
则内层SQL只可以回去单列值,能够多行,也得以单个值。

 

——from型子查询

    将内层查询SQL的询问结果,当成一张一时半刻表,供外层SQL再一次进行询问。

    注意:

        一时半刻表一定要起1个别称。

    例题:

        — 取出每一种栏目下的新式的货物

            select * from 

            (

                select * from goods

                order by cat_id,goods_id desc

            ) as t

            group by cat_id

            order by goods_id;

 

 

——exists型子查询

    将外层的查询结果(查询字段)得到内层查询,判断内层的询问是不是建立,假如内层的查询where创建,则该行取出,
    并在外行输出展现,假设不成立则不出口。

    
    exists效能超越in,因为exists遇到满足条件就赶回,而in则全部相比。

——两表之间的全连接查询

表与聚集的关系

一张表就是1个集合。

每一行便是2个要素。

疑问:

集合不可能重新,不过有只怕有两行数据完全相同,怎么样处理?

答:

MySQL内部每一行又三个rowid

在数据库中,怎么样操作表来博取笛Carl积:

select * from table1,table2;

 

——左连接

A表与B表通过一个关联来筛选B表的行

语法:

A left join B on 筛选标准

如果条件为真,则B表取出对应的行。

该语句获得的结果也是3个一时半刻表,能够当做一张表,设为C。

既是,就足以对C表做询问,所以where group by having order by
limit都足以照常使用。

多表连接中的where和on

在多表连接之后,形成的是第壹张新表,第一张新表中无任何索引字段,由此子表的目录发挥不了成效。

 

 

相关文章

发表评论

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

*
*
Website