Mysql笔记--1

MySql

- 数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等 

- 数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行操作。关键字：insert，delete，update等

- 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。

- 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等

类型名称	说明
int（整数长度）	整数类型
double	小数类型
decimal（m,d）	指定整数位与小数位长度的小数类型
date	日期类型，格式为yyyy-MM-dd，包含年月日，不包含时分秒 2020-01-01
datetime	日期类型，格式为 YYYY-MM-DD HH:MM:SS，包含年月日时分秒
timestamp	日期类型，时间戳
varchar（字符串长度）	文本类型， M为0~65535之间的整数

1.DDL之数据库操作：database

1.1 创建数据库

1.关键字:  create database
2.语法:
  create database 数据库名字 charset utf8;
  
  CREATE DATABASE 210906_java CHARSET utf8;

1.2 查看数据库(了解)

#查看所有的数据库
show databases;
/*
 查看某个数据库的定义的信息:
 show create database 数据库名;
 */
 show create database day01;

1.3 删除,切换数据库

1.关键字: drop
2.语法: drop database 数据库名字

 DROP DATABASE 210906_java2;
 
  -- 切换数据库
 USE 210906_java;

2.DDL之表操作->table

2.1创建表

1.关键字: create table
2.语法:
  create table 表名(
    列名 数据类型(长度) [约束],
    列名 数据类型(长度) [约束],
    列名 数据类型(长度) [约束]
  );
3.注意:
  a.如果定义完一个列之后,后面还有其他的列,需要,
  b.如果定义最后一列了,就不用加,
  c.一张表应该有一个主键(约束),primary key
    主键列的数据是唯一的,不能重复
    一个主键代表一条数据(主键好比身份证号)
 

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

2.3 查看表

查看所有表
	show tables;

查看表结构
	desc 表名;
	
	
SHOW TABLES;

DESC category;

2.4删除表

1.关键字: drop table
2.语法:drop table 表名

-- 语法:drop table 表名
DROP TABLE product;

2.5修改表结构

语法: alter table 表名 add 列名 类型(长度) [约束];
作用：修改表_添加列. 

ALTER TABLE category ADD `desc` VARCHAR(100);

语法: alter table 表名 modify 列名 类型(长度) 约束;
作用：修改表_修改列的类型,长度及约束.

ALTER TABLE category MODIFY `desc` VARCHAR(10);

语法: alter table 表名 change 旧列名 新列名 类型(长度) 约束; 
作用：修改表_修改列名.

ALTER TABLE category CHANGE `desc` `miaoshu` VARCHAR(10);

语法:   alter table 表名 drop 列名; 
作用：  修改表_删除列.

ALTER TABLE category DROP miaoshu;

语法:  rename table 表名 to 新表名; 
作用：修改表名

RENAME TABLE kind TO category;

3.DML之数据操作语言

3.1 插入数据

1.关键字:insert into values
2.语法:insert into 表名 (列名,列名) values (具体的数据,具体的数据)

/*插入数据*/
INSERT INTO category (id,cname) VALUES (1,"蔬菜");
INSERT INTO category (id,cname) VALUES (2,'水果');
/*
  其他insert 方式:不用直接指定列名,直接存数据
                  存储的数据要全
*/
INSERT INTO category VALUES (3,'玩具');
/*
  批量添加
  insert into 表名 (列名,列名) values (数据,数据),(数据,数据),(数据,数据),(数据,数据)
*/
INSERT INTO category (id,cname) VALUES (4,'服装'),(5,'箱包'),(6,'电脑');

3.2 删除数据

1.关键字: delete from
2.语法: delete from 表名  -> 全部一次全删
       delete from 表名 where 条件 ->按照条件删除
3.条件:
  java        mysql
    >           >
    <           <
    >=          >=
    <=          <=
    ==          = 
    !=          <>

DELETE FROM category;
-- 删除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);

3.3 修改数据

1.关键字:update set
2.语法:
  update 表名 set 列名 = 值
  update 表名 set 列名 = 值 where 条件

/*
1.关键字:update set
2.语法:
  update 表名 set 列名 = 值
*/
UPDATE category SET cname = '柳岩';
-- 将表中的蔬菜改成家具
UPDATE category SET cname = '家具' WHERE cname = '蔬菜';
-- 将cid为7的改成电脑
UPDATE category SET cname = '电脑' WHERE cid = 7;
-- 将cid不等于1的cname都改成"涛哥"
UPDATE category SET cname = '涛哥' WHERE cid<>1;

4.约束

4.1主键约束

1.主键约束: primary key
2.主键列中的数据有什么特点
  a.数据唯一,不能重复
  b.不能为NULL
  c.一个主键相当于一个人的身份证号,代表一整条数据
3.注意:
  每一张表都应该有有一个主键列,代表一条数据
  
  -- 添加方式1:在创建表时,在字段后面直接指定
  CREATE TABLE category(
  cid INT PRIMARY KEY,
  cname VARCHAR(10)
);
  -- 添加方式2:在constraint约束区域,去指定主键约束
  /*1.什么是constraint区域:
  最后一列后面和右半个小括号前面的区域  
  2.格式:[constraint 名称] primary key (字段列表)
  3.注意:[constraint 名称]可以省略
  */
CREATE TABLE category(
  cid INT,
  cname VARCHAR(10),
  PRIMARY KEY (cid)
);
  -- 添加方式3:通过修改表结构的方式
 /*
 1.格式:ALTER TABLE 表名 ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
 2.注意:[CONSTRAINT 名称]可以省略不写
*/

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

ALTER TABLE category ADD PRIMARY KEY (cid);
--------------------------------------------------

INSERT INTO category (cid,cname) VALUES (1,'蔬菜');
INSERT INTO category (cid,cname) VALUES (1,'水果'); -- 不行,和上面的主键冲突
INSERT INTO category (cid,cname) VALUES (NULL,'箱包');-- 不行,主键列中的数据不能是NULL

联合主键

1.什么是联合主键:
  多个列结合称之为一个主键
2.特点:
  主键的多个列的数据不能完全一样

CREATE TABLE person
(
firstname VARCHAR(255), -- 名字
lastname VARCHAR(255),  -- 姓
address VARCHAR(255),   -- 地址
city VARCHAR(255)    -- 城市
)
ALTER TABLE person ADD PRIMARY KEY (firstname,lastname);

删除主键约束

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

4.2.自增长约束

自动增长列
  1.关键字:auto_increment
  2.使用:一般和primary key 一起使用
  3.注意:
    a.主键是能代表一条数据
    b.自增长列只能自动维护数据,不能代表一条数据
    c.如果一个主键列设置成自增长,那么删除之后,自增长的数据不会重新编号
      如果一个主键列没有设置自增长,那么删除之后,数据可以挨着编号
      
create table category(
  cid int primary key auto_increment,
  cname varchar(100)
);
-- 添加数据
insert into category (cid,cname) values (3,'家具');
-- 主键都自动维护了,所以我们添加数据时,就不用特意指定主键列了
INSERT INTO category (cname) VALUES ('手机'),('玩具'),('电脑');           

truncate table 表名 ->清空表数据

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

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

先删除自增长约束
再删除主键约束

*/

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;

4.3truncate和delete区别

在自增长的情况下:
 delete之后,自增长列不会重新编号
 truncate之后,摧毁表结构,删除表记录,编号将重新开始

4.4非空约束

1.关键字:  NOT NULL
2.特点:
  此列不能为NULL

4.5唯一约束

1.关键字:UNIQUE
2.特点:  UNIQUE修饰的列中的数据不能重复


DROP TABLE `person`;
CREATE TABLE person
(
id_p INT UNIQUE, #唯一约束
lastname VARCHAR(255) NOT NULL, #非空约束
firstname VARCHAR(255),
address VARCHAR(255),
city VARCHAR(255)
)

唯一约束和主键约束有什么区别:
  1.相同点:
    数据都是唯一的
  2.不同点:
    主键约束:一个表中只有一个主键
    唯一约束:一个表中可以都多个唯一约束

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

5.简单查询

5.1简单查询

1.关键字:  select from  where
2.语法:
  select 列名,列名 from 表名 [where 条件]
  select * from 表名  -> 查询所有数据
  select 列名 from 表名->查询 指定这一列的数据
  select 列名/* from 表名 where 条件-> 按照指定的条件查询数据
3.注意:
  查询出来的结果也是一张表,但是这个表为伪表,这个表中的数据是只读的

-- 查询product表中所有的数据
SELECT * FROM product;

#查询商品名和商品价格.
SELECT pname,price FROM product;
/*
  查询的过程中,可以给表起别名
  as 别名 
  注意:as 可以省略
*/
SELECT * FROM product AS p;
SELECT * FROM product p;

/*
  给列起别名
  
  原列名 as 别名   ->  as可以省略
*/
SELECT pname '商品名',price '价格' FROM product;

  /*
    #去掉重复值
    关键字:distinct (列名)  
  */
  
  SELECT DISTINCT(price) FROM product
  
#查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示 取别名. 
SELECT pname,price+10 'newPrice' FROM product;

5.2条件查询

比较运算符	< <= >= = <>	大于、小于、大于(小于)等于、不等于
	BETWEEN ...AND...	显示在某一区间的值(含头含尾)
	IN(set)	显示在in列表中的值，例：in(100,200) 查询1,3,7的商品 列名 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;
#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;
#查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price>=200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 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;

5.3(关键字执行顺序)排序查询***

1.关键字: order by  desc|asc
2.注意
  a.order by 后面默认是升序
  b.先查询后排序还是先排序后查询
    先查询,后排序,而且是最后针对查询结果进行最后的排序
    
3.语法:
   SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;->要指明对哪一列进行排序
        asc:升序(默认)
        desc:降序

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

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

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

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

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

#显示商品的价格(去重复)(distinct)，并排序(降序)
SELECT DISTINCT(price) FROM product ORDER BY price DESC;

5.4聚合查询

1.作用:纵向操作数据
2.聚合函数:
  count(列名)-> 统计表中有多少条数据
  sum(列名)-> 针对指定列求和
  avg(列名)-> 针对指定列求平均值
  max(列名)-> 求指定列的最大值
  min(列名)-> 求指定列的最小值
  
3.格式:
  select 聚合函数(列名) from 表名 where 条件

-- 查询product的总条数
SELECT COUNT(*) FROM product;
SELECT COUNT(pid) FROM product;
SELECT COUNT(1) FROM product;

-- 查询所有商品价格的总和
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;

CREATE TABLE student(
  sid INT,
  firstname VARCHAR(100),
  lastname VARCHAR(100)
);

SELECT COUNT(*) FROM student; -- 包含null
SELECT COUNT(1) FROM student; -- 包含null
SELECT COUNT(0) FROM student; -- 包含null
SELECT COUNT(sid) FROM student;-- 不包含null

5.5分组查询

1.关键字:group by 列名
2.语法:
  SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 HAVING 分组条件;
3.分组的小诀窍:相同的为一组,不同的单独为一组
4.关键字: having 条件
5.having和where有什么区别?
  a.where是在分组之前筛选
  b.having是在分组之后筛选
  
6.注意:
  分组查询都是和聚合函数一起使用

-- 查询相同商品的价格总和
SELECT pname,SUM(price) FROM product GROUP BY pname;
-- 查询相同商品的价格总和 并 排序
/*
  1.问题:
    如果排序时使用的是price字段,排序失败了
    因为我们是先查询,后排序,而排序是在查询结果的基础上排的
    但是查询之后价格字段叫SUM(price),所以我们最后排序的时候也应该用SUM(price)
*/
SELECT pname,SUM(price) FROM product GROUP BY pname ORDER BY SUM(price) DESC;

SELECT pname,SUM(price) 'newprice' FROM product GROUP BY pname ORDER BY newprice DESC;

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

/*
  下面的sql有错误,因为where 写到了 group by后面,书写顺序有问题
*/
SELECT pname,SUM(price) 'newprice' FROM product GROUP BY pname WHERE newprice>=2000;
/*
   下面的sql有错误,因为先执行where ,执行where 的时候newprice字段还没产生呢
*/
SELECT pname,SUM(price) 'newprice' FROM product WHERE newprice>=2000 GROUP BY pname;
/*
  下面的sql还是有问题,我类个去
  因为先执行where,先筛选,直接将果9筛除了
*/
SELECT pname,SUM(price) 'newprice' FROM product WHERE price>=2000 GROUP BY pname;
/*
  下面的sql是正确的,我们应该在分组查询之后,筛选
  所以使用having
*/

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

5.6分页查询

1.语法
    select * from 表名 limit m,n
       m:代表起始位置->从0开始
       n:每页显示的条数
    分页查询中起始位置的计算方式:
       (当前页-1)*每页显示条数
       
-- 后台计算出页码、页数(页大小)
-- 分页需要的相关数据结果分析如下,
-- 注意:下面是伪代码不用于执行
int curPage = 2; -- 当前页数
int pageSize = 5; -- 每页显示数量
int startRow = (curPage - 1) * pageSize; -- 当前页, 记录开始的位置(行数)计算
int totalSize = select count(*) from products; -- 记录总数量
int totalPage = Math.ceil(totalSize * 1.0 / pageSize); -- 总页数
                总页数 = (总记录数/每页显示条数)向上取整

/*
   select * from 表名 limit m,n
   
   m:代表的是每一页的起始位置  从0开始
   n:代表每一页展示多少条数据
   
   
   1.起始位置怎么算:
     (当前页-1)*每页显示条数
*/

SELECT * FROM product LIMIT 0,5;  -- 第一页

SELECT * FROM product LIMIT 5,5;  -- 第二页

SELECT * FROM product LIMIT 10,5; -- 第三页

SELECT * FROM product LIMIT 15,5; -- 第四页