约束_查询

课前回顾:
  1.数据库操作:
    a.创建数据库: create database 数据库名字
    b.使用(切换)数据库: use 数据库名字
    c.删除数据库: drop database 数据库名字
    d.查看数据库: show databases
  2.表操作:
    a.创建表
      create table 表名(
       列名 数据类型 长度 [约束],
       列名 数据类型 长度 [约束]
      );
    b.删除表:
      drop table 表名
    c.修改表:
      alter table 表名 add  -> 新增列
      alter table 表名 modify  -> 修改列(类型 长度 约束)
    d.数据操作:
      添加数据:
      insert into 表名 (列名,列名...) values (数据,数据...);
      insert into 表名 (列名,列名...) values (数据,数据...),(数据,数据...),(数据,数据...);
      insert into 表名 values (数据,数据...);
今日重点:
   all

第一章.mysql中语句

1.DML之数据操作语言

1.1 插入数据

insert into 表名 (列名,列名...) values (数据,数据);

-- 插入数据
insert into `category` (cid,cname) values (1,'蔬菜');
insert into `category` (cid,cname) values (2,'水果');
/*
  我们在操作表中的数据时,如果是varchar类型,数据可以用单引号
  还可以用双引号

  但是,推荐单引号

  注意:以后我们不会单独在mysql中操作数据,我们需要利用Java代码
       操作数据库

       我们需要用String的Java代码表示一个sql语句,然后调用java特有的
       api去执行我们String表示的sql语句,从而间接操作数据库中的数据

  String sql = "INSERT INTO `category` (cid,cname) VALUES (3,"箱包")";->这样不对
  String sql = "INSERT INTO `category` (cid,cname) VALUES (3,'箱包')";->这样是对的
*/
insert into `category` (cid,cname) values (3,'箱包');

/*
  批量添加
  insert into 表名 (列名,列名..) values (数据,数据),(数据,数据)...
*/
insert into category (cid,cname) values (4,'服装'),(5,'玩具'),(6,'手机');

/*
  不指定列名添加
  insert into 表名 values (数据,数据),(数据,数据)...

  要求:给所有的列都要添加数据
*/
INSERT INTO category VALUES (7,'服装');

1.2 删除数据

1.语法格式:delete from 表名  -> 删除所有数据
         delete from 表名 where 条件
2.条件符号:
  java            mysql
  ==               =
  >                >
  <                <
  >=               >=
  <=               <=
  !=               <>

-- 删除数据  delete from 表名  删除所有记录
DELETE FROM category;

/*

1.语法格式:delete from 表名  -> 删除所有数据
         delete from 表名 where 条件
2.条件符号:
  java            mysql
  ==               =
  >                >
  <                <
  >=               >=
  <=               <=
  !=               <>

*/
-- 删除cid为1的记录
DELETE FROM category WHERE cid = 1;

-- 删除cid>=5的记录

DELETE FROM category WHERE cid>=5;

-- 删除cid不等于3的记录
DELETE FROM category WHERE cid<>3;
DELETE FROM category WHERE cid!=3;
DELETE FROM category WHERE NOT(cid=3);
DELETE FROM category WHERE !(cid=3);

1.3 修改数据

1.语法:
  update 表名 set 列名 = 新值   -> 将指定的列中的数据全部改掉
  update 表名 set 列名 = 新值 where 条件

-- 修改数据 update 表名 set 列名 = 新值
UPDATE category SET cname = '鼠标';

-- update 表名 set 列名 = 新值 where 条件

-- 将表中的蔬菜改成家具
UPDATE category SET cname = '家具' WHERE cname = '蔬菜';

-- 将cid为2的改成电脑
UPDATE category SET cname = '电脑' WHERE cid = 2;

-- 将cid不等于1的cname都改成"涛哥"
UPDATE category SET cname = '涛哥' WHERE cid<>1;

第二章.约束

1.主键约束

1.关键字:  primary key
2.编写位置: 在创建表时,放在列的后面
3.特点:
  a.主键列中的数据不能重复,唯一
  b.主键列中的数据不能是NULL
4.注意:
  我们每张表都应该有一个主键列
  此主键列中的数据可以代表一条数据:比如:人的身份证号,就可以代表一个人

1.1.添加方式1:在创建表时,在字段后面直接指定(推荐使用)

/*
1.关键字:  primary key
2.编写位置: 在创建表时,放在列的后面
3.特点:
  a.主键列中的数据不能重复,唯一
  b.主键列中的数据不能是NULL
*/
CREATE TABLE category(
 cid INT PRIMARY KEY,
 cname VARCHAR(10)
);

-- 添加数据
INSERT INTO category (cid,cname) VALUES (1,'蔬菜');

INSERT INTO category (cid,cname) VALUES (1,'水果');-- 不行,因为cid为主键,之前已经有1了,主键列数据唯一

INSERT INTO category (cid,cname) VALUES (NULL,'电脑'); -- 不行,因为cid为主键列,主键列上的数据不能为NULL

1.2.添加方式2:在constraint约束区域,去指定主键约束

1.什么是constraint域:
  在最后一列后面,右半个小括号前面的区域
2.语法:
  [constraint 名称] primary key (字段列表) 
3.注意:
  [constraint 名称]可以省略

/*
 [constraint 名称] primary key (字段列表) 
*/
CREATE TABLE category(
 cid INT,
 cname VARCHAR(10),
 PRIMARY KEY (cid)
);

1.3.添加方式3:通过修改表结构的方式

1.格式:ALTER TABLE 表名 ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
2.注意:[CONSTRAINT 名称]可以省略不写

/*
修改表结构方式添加约束
1.格式:ALTER TABLE 表名 ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
2.注意:[CONSTRAINT 名称]可以省略不写
*/
CREATE TABLE category(
 cid INT,
 cname VARCHAR(10)
);

ALTER TABLE category ADD PRIMARY KEY (cid);

1.4.联合主键

1.什么叫联合主键:多个列并称为一个主键
2.怎么设置:
  alter table 表名 add primary key (列名,列名);
3.特点:
  多个列中的数据不能一样

/*
  联合主键
*/
CREATE TABLE persons
(
firstname VARCHAR(255), -- 名字
lastname VARCHAR(255),  -- 姓
address VARCHAR(255),   -- 地址
city VARCHAR(255)   -- 城市
)

--  alter table 表名 add primary key (列名,列名);
ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname);

-- 添加数据
INSERT INTO persons (firstname,lastname,address,city) VALUES ('岩','柳','湖南','长沙');

INSERT INTO persons (firstname,lastname,address,city) VALUES ('岩','刘','河北','唐山');

INSERT INTO persons (firstname,lastname,address,city) VALUES ('涛','刘','河北','邢台');

INSERT INTO persons (firstname,lastname,address,city) VALUES ('岩','柳','北京','昌平');-- 错误

1.5.删除主键约束

ALTER TABLE persons DROP PRIMARY KEY->删除主键约束

#ALTER TABLE persons DROP PRIMARY KEY->删除主键约束
alter table persons drop primary key;

2.自增长约束

2.1.基本操作

1.关键字: auto_increment
2.特点:
  a.自增长约束一般和主键联合使用
  b.自增长的类数据mysql自动维护,不用我们维护
  c.自增长的列中数据唯一
3.注意:
  a.如果某一列是自增长,我们添加的时候可以直接写NULL 即使此列是一个主键列,也可以写NULL,因为自增长自动维护
  b.自增长的列如果删除之后,在添加新的,不会重新编号

4.某一列设置成主键跟设置成自增长有什么区别:
  主键可以代表一条数据
  自增长不能

  一个表中主键就一个

CREATE TABLE category(
  cid INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(10)
);

-- 添加数据
INSERT INTO category (cid,cname) VALUES (1,'蔬菜');
INSERT INTO category (cid,cname) VALUES (1,'水果');

INSERT INTO category (cname) VALUES ('服装');

/*
  如果某一列是自增长,我们添加的时候可以直接写NULL
  即使此列是一个主键列,也可以写NULL,因为自增长自动维护
*/
INSERT INTO category (cid,cname) VALUES (NULL,'鼠标');

/*
自增长是一个约束,操作起来和其他约束不太一样

如果自增长约束和主键约束合起来使用想删除

先修改自增长约束
再删除主键约束
*/

drop table category;
create table category(
cid int primary key auto_increment,
cname varchar(100)
);

alter table category modify cid int;

alter table category drop primary key;

2.2.truncate和delete区别

前提:某一列自增长
delete:删除数据之后,再次添加,不会重新将自增长的列重新编号
truncate:摧毁表结构,再次添加,会重新将自增长的列重新编号
         truncate 表名

3.非空约束

1.格式:
  NOT NULL
2.特点:
  NOT NULL的列中的数据,不能为NULL

/*
非空约束  NOT NULL

被NOT NULL修饰的类不能为NULL
*/

DROP TABLE category;

CREATE TABLE category(
  cid INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(10) NOT NULL
);

INSERT INTO category (cname) VALUES ('蔬菜');
INSERT INTO category (cname) VALUES ('水果');

/*
可以  因为null带单引号,证明是一个具体的数据字符

好比java代码:
  String s = "NULL";
*/
INSERT INTO category (cname) VALUES ('NULL');

/*
可以  因为null带单引号,证明是一个具体的数据字符

好比java代码:
  String s = "null";
*/
INSERT INTO category (cname) VALUES ('null');

/*
可以  因为带单引号,证明是一个具体的数据字符

好比java代码:
  String s = "";
*/
INSERT INTO category (cname) VALUES ('');

/*
   不行,因为cname不能为null
   好比java代码:
   String s = null
*/

INSERT INTO category (cname) VALUES (NULL);

4.唯一约束

1.关键字:
  UNIQUE
2.特点:
  被unique修饰的列中的数据不能重复
3.primary key 和 unique区别:
  相同点:
    两者的列中的数据都不能重复
  不同点:
    a.每一个表都应该有一个主键,而主键可以代表一条数据
    b.每一个表中可以有多个unique约束,不能代表一条数据

drop table `persons`;
CREATE TABLE persons
(
id_p int UNIQUE, #唯一约束
lastname varchar(255) NOT NULL, #非空约束
firstname varchar(255),
address varchar(255),
city varchar(255)
)

删除唯一约束:
 ALTER TABLE persons DROP INDEX 名称   [名称是CONSTRAINT后面的名称]

5.扩展

如果一个列为主键自增长,怎么删除约束

/*
自增长是一个约束,操作起来和其他约束不太一样

如果自增长约束和主键约束合起来使用想删除

先修改自增长约束
再删除主键约束
*/

drop table category;
create table category(
cid int primary key auto_increment,
cname varchar(100)
);

alter table category modify cid int;

alter table category drop primary key;

第三章.简单查询

1.数据准备

#创建商品表：
create table product(
    pid int primary key,
    pname varchar(20),
    price double
);
INSERT INTO product(pid,pname,price) VALUES(1,'联想',5000);
INSERT INTO product(pid,pname,price) VALUES(2,'海尔',3000);
INSERT INTO product(pid,pname,price) VALUES(3,'雷神',5000);
INSERT INTO product(pid,pname,price) VALUES(4,'JACK JONES',800);
INSERT INTO product(pid,pname,price) VALUES(5,'真维斯',200);
INSERT INTO product(pid,pname,price) VALUES(6,'花花公子',440);
INSERT INTO product(pid,pname,price) VALUES(7,'劲霸',2000);
INSERT INTO product(pid,pname,price) VALUES(8,'香奈儿',800);
INSERT INTO product(pid,pname,price) VALUES(9,'相宜本草',200);
INSERT INTO product(pid,pname,price) VALUES(10,'面霸',5);
INSERT INTO product(pid,pname,price) VALUES(11,'好想你枣',56);
INSERT INTO product(pid,pname,price) VALUES(12,'香飘飘奶茶',1);
INSERT INTO product(pid,pname,price) VALUES(13,'果9',1);

2.简单查询

1.关键字: select from where
2.格式: select 列名,列名 from 表名 [where 条件]  -> 按照指定条件去查询
       select * from 表名  -> 查询所有数据
3.注意:我们查询出来之后,结果也是一张表,此表不是真实存在的,是一张伪表,这张伪表只是用于展示结果的,里面的数据无法改动,只是查看

#查询所有的商品.
SELECT * FROM product;

#查询商品名和商品价格.
SELECT pname,price FROM product;

/*
  查询的表以及列都可以设置别名

  格式: 
  select 列名 from 表名 as 别名

  as可以省略不写

*/

SELECT * FROM product AS p;

/*
  给列起别名

  select 列名 as 别名 from 表名

  as可以省略
*/
SELECT pname AS '商品名',price AS '价格' FROM product;
SELECT pname '商品名',price '价格' FROM product;

SELECT pname 商品名1,price 价格1 FROM product;  -- 不推荐


  /*
    #去掉重复值
    关键字:distinct (列名)  
  */

SELECT DISTINCT(price) FROM product;

/*
  在查询的过程中,可以给某一列的数据做运算
*/

-- 查询product表中的pname,price,并给price列中的数据+10
SELECT pname,price+10 'money' FROM product;

3.条件查询

比较运算符	> < <= >= = <>	大于、小于、大于(小于)等于、不等于
	BETWEEN …AND…	显示在某一区间的值(含头含尾)
	IN(set)	显示在in列表中的值，例：列名 in(100,200) 查询id为1,3,7的商品 id in(1,3,7)
	LIKE ‘张pattern’	模糊查询，Like语句中，% 代表零个或多个任意字符，代表一个字符，例如：`_first_name like '_a%';_` 比如:查询姓张的人: like ‘张%’ 查询商品名中带香的商品:like’%香%’ 查询第二个字为想的商品名:like’想%’ 查询商品名为四个字的商品:like’’
	IS NULL	判断是否为空
逻辑运行符	and (与)	多个条件同时成立全为true,整体才为true
	or(或)	多个条件任一成立有真则真
	not(非)	不成立，例：`where not(salary>100); !`

#查询商品名称为“花花公子”的商品所有信息：
SELECT * FROM product WHERE pname = '花花公子';

#查询价格为800商品
SELECT * FROM product WHERE price = 800;

#查询价格不是800的所有商品
SELECT * FROM product WHERE price<>800;
SELECT * FROM product WHERE price!=800;
SELECT * FROM product WHERE NOT(price=800);

#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price>60;

#查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

SELECT * FROM product WHERE price>=200 AND price<=1000;

SELECT * FROM product WHERE price BETWEEN 1000 AND 200;-- 小的写前面,大的写后面

#查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price IN(200,800);

#查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';

#查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';

#查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';

#查询商品名为4个字的商品
SELECT * FROM product WHERE pname LIKE '____';

#查询商品名为NULL的
SELECT * FROM product WHERE pname IS NULL;

#查询商品名不为NULL的
SELECT * FROM product WHERE pname IS NOT NULL;

4.排序查询

1.关键字: order by asc|desc
         asc:升序  order by默认排序规则
         desc:降序

2.注意: order by放到查询语句的最后面

3.问题: 先查询还是先排序
        先查询  后排序

书写sql语句关键字的顺序
select 
from 
where 
group by 
having 
order by

执行顺序:
from 
where 
group by 
having 
select 
order by

先定位到要查询哪个表,然后根据什么条件去查,表确定好了,条件也确定好了,开始利用select查询
查询得出一个结果,在针对这个结果进行一个排序

/*
  格式:select 列名 from 表名 order by 排序列名 排序规则
*/
#使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;

#使用价格排序(升序)
SELECT * FROM product ORDER BY price;

SELECT * FROM product ORDER BY price ASC;

#显示商品的价格(去重复)(distinct)，并排序(降序)

SELECT DISTINCT(price) FROM product ORDER BY price DESC;

# 查询价格大于500的商品信息,并进行排序
SELECT * FROM product WHERE price>500 ORDER BY price DESC;

5.聚合查询

1.概述:针对于纵向查询
2.聚合函数:
  count(列名)-> 统计总记录数 不包含NULL
  sum(列名)-> 对指定列进行求和
  max(列名)-> 求指定列的最大值
  min(列名)-> 求指定列的最小值
  avg(列名)-> 求指定列的平均值

3.语法:
  select 聚合函数(列名) from 表名 where 条件

4.注意:聚合函数和列名之间不要有空格

/*
  聚合查询:
    sum(列名):针对指定的列进行求和
    avg(列名):针对指定的列进行求平均值
    count(列名):统计指定列不为NULL的记录行数；
    max(列名):求指定列中的最大值
    min(列名):求指定列中的最小值
*/
-- 查询product的总条数
SELECT COUNT(*) FROM product;
SELECT COUNT(pid) FROM product;
SELECT COUNT(1) FROM product;
SELECT COUNT(0) FROM product;


SELECT COUNT(*) FROM persons;-- 带NULL
SELECT COUNT(id_p) FROM persons;-- 不查询NULL
SELECT COUNT(1) FROM persons;-- 带NULL
SELECT COUNT(0) FROM persons;-- 带NULL


-- 查询所有商品价格的总和
SELECT SUM(price) FROM product;

-- 查询pid 为 1,3,7的商品价格平均值

SELECT AVG(price) FROM product WHERE pid IN (1,3,7);

--  查询商品的最高价格以及最低价格
SELECT MAX(price),MIN(price) FROM product;

6.分组查询

1.关键字:group by 按照哪一列分组的列名
2.语法: select 列名 from 表名 where 条件 group by 列名 having 条件 order by 列名 排序规则 

3.如何判定按照哪一列进行分组
  相同的为一组
  不同的单独为一组

4.注意:
  分组查询都和聚合函数联合起来使用

5.在分组之后进行筛选:having 条件

  having 筛选条件  在分组之后执行
  where 筛选条件  在分组之前执行

# 查询相同商品名称的价格总和
SELECT  pname, SUM(price) FROM product GROUP BY pname;

-- 查询相同商品的价格总和 并 排序

-- SELECT  pname, SUM(price) FROM product GROUP BY pname order by SUM(price);

/*
  我们先执行的查询,查询出一个伪表,再对这个伪表进行最后的排序
  我们查询出来之后价格的列名叫newprice,所以我们最后排序的时候使用的列名也应该是newprice
*/
SELECT  pname, SUM(price) newprice FROM product GROUP BY pname ORDER BY newprice;

-- 查询相同商品的价格总和  再查询价格的总和大于等于2000的
/*

有错误:

由于先执行where,在执行select
所以当执行where的时候newprice这个列名还没有出现呢,所以报错了
*/
SELECT  pname, SUM(price) newprice FROM product WHERE newprice>=2000 GROUP BY pname;

/*
 能查出东西来,但是结果不对

 由于先执行where,在执行select以及求和
 当执行where筛选条件的时候,还没有进行求和分组呢,所以直接就将求和之前价格不够的商品晒掉了
 所以结果不对
*/
SELECT  pname, SUM(price) newprice FROM product WHERE price>=2000 GROUP BY pname;


/*
  5.在分组之后进行筛选:having 条件

  having 筛选条件  在分组之后执行
  where 筛选条件  在分组之前执行
*/

SELECT  pname, SUM(price) newprice FROM product GROUP BY pname HAVING newprice>=2000;

SQL

2_数据库基础

约束_查询

第一章.mysql中语句

1.DML之数据操作语言

1.1 插入数据

1.2 删除数据

1.3 修改数据

第二章.约束

1.主键约束

1.1.添加方式1:在创建表时,在字段后面直接指定(推荐使用)

1.2.添加方式2:在constraint约束区域,去指定主键约束

1.3.添加方式3:通过修改表结构的方式

1.4.联合主键

1.5.删除主键约束

2.自增长约束

2.1.基本操作

2.2.truncate和delete区别

3.非空约束

4.唯一约束

5.扩展

第三章.简单查询

1.数据准备

2.简单查询

3.条件查询

4.排序查询

5.聚合查询

6.分组查询