mysql的基础命令

sql中的数据类型

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

1.DDL之数据库操作：database

  1. 创建数据库

    1
    2
    1.关键字: create database
    2.语法:create database 数据库名 charset utf8

    建议:

    写表名,库名,列名时最好用 `` 包裹

  2. 查看数据库

    1
    语法:show databases

  3. 删除数据库

    1
    语法: drop database 数据库名;

  4. 使用数据库(切换数据库)

    1
    2
    3
    语法:use 数据库名字

    只有切换到指定的库上,才能在此库中进行创建表等其他操作

2.DDL之表操作->table

  1. 创建表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    1.关键字:create table
    2.语法:
      create table 表名(
        列名 数据类型(长度) [约束],
        列名 数据类型(长度) [约束],
        列名 数据类型(长度) [约束]  
      );
      
    3.注意:
      如果在创建表的时候,是最后一列了,那么就不要加,了

  2. 删除表

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

  3. 修改表结构

    1
    2
    3
    alter table 表名 add 列名 类型(长度) [约束];
    作用：添加列. 
    ALTER TABLE product ADD `desc` VARCHAR(100);
    1
    2
    3
    alter table 表名 modify 列名 类型(长度) [约束];
      作用：修改列的类型,长度及约束.
      ALTER TABLE product MODIFY `desc` VARCHAR(50);
    1
    2
    3
    alter table 表名 change 旧列名 新列名 类型(长度) [约束]; 
    作用：修改列名.
    ALTER TABLE product CHANGE `desc` `miaoshu` VARCHAR(50);
    1
    2
    3
    alter table 表名 drop 列名; 
    作用：修改表_删除列.
    ALTER TABLE product DROP miaoshu;
    1
    2
    3
    rename table 表名 to 新表名; 
    作用：修改表名
    RENAME TABLE shangpin TO product;

3.DML之数据操作语言

  1. 插入数据

    1
    2
    3
    4
    5
    6
    1.关键字:insert into
            into可以 省略
    2.语法:
      a.insert into 表名 (列名,列名)  values (值1,值2);
      b.insert into 表名 (列名,列名) values  (值1,值2), (值1,值2), (值1,值2);-> 一次添加多条数据
      c.insert into 表名 values (值1,值2); -> 如果不写列名,赋值时需要将所有列都匹配上

  2. 删除数据

    1
    2
    3
    4
    1.关键字:delete from
    2.语法:
      a.delete from 表名  -> 删除所有数据
      b.delete from 表名 where 条件 -> 根据条件删除指定的数据

  3. 修改数据

    1
    2
    3
    4
    1.关键字:update set
    2.语法:
      a.update 表名 set 列名 = 新值
      b.update 表名 set 列名 = 新值 where 条件

约束

约束是对指定列的数据进行约束

主键约束

1
2
3
4
5
1.关键字:primary key -> 好比是人的身份证号
2.特点:
  a.每一个表中都应该有一个主键,而且只能有一个主键
  b.主键列的数据不能重复
  c.主键列的数据不能是NULL

添加方式1:在创建表时,在字段后面直接指定(重点)

1
2
3
4
5
6
7
8
9
CREATE TABLE product(
   pid INT PRIMARY KEY,
   pname VARCHAR(10),
   price INT
);

INSERT INTO product (pid,pname,price) VALUES (1,'丝袜',9);
-- INSERT INTO product (pid,pname,price) VALUES (1,'内裤',50);主键列数据不能重复
-- INSERT INTO product (pid,pname,price) VALUES (NULL,'背心',10);主键列数据不能为NULL

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

1
2
3
4
5
1.什么叫做constraint域
  创建表的时候,最后一列和右半个小括号之间的区域
2.语法:
  [constraint 名字] primary key (字段名)
3.注意:[constraint 名字]:可写可不写    
1
2
3
4
5
6
CREATE TABLE product(
   pid INT,
   pname VARCHAR(10),
   price INT,
   PRIMARY KEY (pid)   
);

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

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

联合主键

1
2
3
4
1.什么叫做联合主键
  多个列一起称之为一个主键
2.特点:
  联合主键中的数据不能完全一样,不能为NULL
1
2
3
4
5
6
CREATE TABLE persons(
  xing VARCHAR(5),
  ming VARCHAR(5),
  address VARCHAR(10),
  PRIMARY KEY (xing,ming)
);

删除主键约束

1
2
ALTER TABLE 表名 DROP PRIMARY KEY->删除主键约束
ALTER TABLE person DROP PRIMARY KEY;

自增长约束

1
2
3
4
1.关键字:auto_increment
2.注意:自增长都是和主键一起使用
3.特点:
  主键自增长的列中的数据不用我们自己维护,mysql自动维护
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE category(
  cid INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(10)
);

INSERT INTO category (cid,cname) VALUES (3,'服装');

INSERT INTO category (cid,cname) VALUES (NULL,'服装');

INSERT INTO category (cid,cname) VALUES (0,'箱包');

INSERT INTO category (cname) VALUES ('居家');

INSERT INTO category VALUES (NULL,'家电');

DELETE FROM category WHERE cid = 7;

DELETE FROM category;

TRUNCATE TABLE category;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*
自增长是一个约束,操作起来和其他约束不太一样

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

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

*/

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;

truncate和delete区别

1
2
delete:如果删除的是主键自增数据,再次添加不会从头编号,而是从最后一个id开始继续往下编号
truncate:摧毁表结构,删除所有数据,再次添加会重新编号

非空约束

1
2
3
4
1.关键字:
  NOT NULL
2.特点:
  被非空约束修饰的列数据不能为NULL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE product(
  pid INT PRIMARY KEY AUTO_INCREMENT,
  pname VARCHAR(10) NOT NULL
);

INSERT INTO product (pname) VALUES ('丝袜');

-- 好比是 String s = null
INSERT INTO product (pname) VALUES (NULL); -- pname不能为NULL

-- 好比是 String s = "null"
INSERT INTO product (pname) VALUES ('null');

-- 好比是 String s = ""
INSERT INTO product (pname) VALUES ('');

唯一约束

1
2
3
4
5
6
7
8
1.关键字:
  UNIQUE
2.特点:
  被UNIQUE修饰的列的数据不能重复
3.UNIQUE和主键约束区别:
  主键能代表一条数据,一个表只能有一个主键约束
  
  UNIQUE不能,因为一个表可以有多个唯一约束
1
2
3
4
5
CREATE TABLE student(
  sid INT PRIMARY KEY AUTO_INCREMENT,
  sname VARCHAR(10),
  score INT UNIQUE
);
1
2
删除唯一约束:
 ALTER TABLE 表名 DROP INDEX 名称   [名称是CONSTRAINT后面的名称]

单表查询

简单查询

1
2
3
4
5
6
7
1.关键字:select from where
2.格式:
  a.select * from 表名 -> 查询所有数据,展示所有列的数据
  b.select 列名 from 表名 -> 查询所有,展示指定列的数据
  c.select 列名 as 别名 from 表名 as 别名 -> 给列和表起别名 as可以省略
3.注意:
  查询出来的结果也是一个表格,但是这个表示伪表,此表示只读的,不能改

条件查询

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

排序查询

1
2
3
4
5
6
7
8
9
1.关键字:order by
2.语法:
  select 列名 from 表名 order by 排序字段 desc|asc
  
  desc:降序
  asc:升序 -> order by 默认排序规则
      
3.问题:先排序还是先查询
  先查询,再排序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
书写sql语句关键字的顺序
select 
from 
where 
group by 
having 
order by

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

先定位到要查询哪个表,然后根据什么条件去查,表确定好了,条件也确定好了,开始利用select查询
查询得出一个结果,在针对这个结果进行一个排序
1
2
3
4
5
6
7
8
9
10
-- 使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;

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

SELECT * FROM product ORDER BY price;

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

聚合查询

1
2
3
4
5
6
7
8
1.查询方式:纵向查询,对指定列的数据进行聚合查询,需要用到聚合函数
2.语法:  select 聚合函数(列名) from 表名 [where 条件]
3.常见的聚合函数:  
count(列名) -> 统计总记录数 
sum(列名) -> 对指定列求和 
avg(列名) -> 对指定列求平均值  
max(列名) -> 对指定列求最大值  
min(列名) -> 对指定列求最小值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 统计product的总记录数
SELECT COUNT(*) FROM product; -- 查询所有,表中有多少条记录,都统计出来
SELECT COUNT(pid) FROM product;
SELECT COUNT(0) FROM product; -- 即使有NULL也能统计
SELECT COUNT(1) FROM product; -- 即使有NULL也能统计
SELECT COUNT(pname) FROM product; -- 如果指定的列中有NULL,统计不出来

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

-- 查询pid为1,3,7 商品的价格平均值
SELECT AVG(price) FROM product WHERE pid IN(1,3,7);
-- 查询商品的最高价格以及最低价格
SELECT MIN(price),MAX(price) FROM product;

分组查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1.关键字:group by
2.语法:
  select 聚合函数(列名) from 表名 group by 分组列 having 条件
3.注意:
  a.分组查询都是和聚合函数一起使用
4.分组小技巧:
  观察以哪一组分组展示
  相同的合并为一组展示
  不同的单独为一组展示 
  
5.having和where的区别:
  a.相同点:都是条件筛选
  b.不同点:
    where在分组之前执行
    having在分组之后执行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
书写sql语句关键字的顺序
select 
from 
where 
group by 
having 
order by

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

先定位到要查询哪个表,然后根据什么条件去查,表确定好了,条件也确定好了,开始利用select查询
查询得出一个结果,在针对这个结果进行一个排序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 查询相同商品的价格总和
SELECT pname, SUM(price) FROM product GROUP BY pname;

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

SELECT pname,SUM(price) FROM product GROUP BY pname ORDER BY SUM(price) ASC;

SELECT pname,SUM(price) `newprice` FROM product GROUP BY pname ORDER BY newprice ASC;

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


/*
  where关键字要写在group by前面,现在写在后面了,所以报错了
*/
SELECT pname,SUM(price) `newprice` FROM product GROUP BY pname WHERE newprice>=2000;

/*
  先走where,在走where的时候别名还没产生了,所以报错
*/
SELECT pname,SUM(price) `newprice` FROM product WHERE newprice>=2000 GROUP BY pname ;

/*
  执行where的时候,还没分组呢
  此时第一个果9是1块钱;第二个果9是1999,都没有到2000,所以直接被where筛掉了
  所以结果不对
*/
SELECT pname,SUM(price) `newprice` FROM product WHERE price>=2000 GROUP BY pname ;


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

分页查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
1.语法:
  select * from 表名 limit m,n
2.字母代表啥:
  m:每页的起始位置
  n:每页显示条数
3.小技巧:
  我们将整个表的每一条数据进行编号,从0开始
  
4.每页的起始位置快速算法:
  (当前页-1)*每页显示条数
  
5.其他分页参数:
  a.每页的起始位置:
    (当前页-1)*每页显示条数
  b.int curPage = 2; -- 当前页数
  c.int pageSize = 5; -- 每页显示数量
  d.int startRow = (curPage - 1) * pageSize; -- 当前页, 记录开始的位置(行数)计算
  e.int totalSize = select count(*) from products; -- 记录总数量
  f.int totalPage = Math.ceil(totalSize * 1.0 / pageSize); -- 总页数
                总页数 = (总记录数/每页显示条数)向上取整
1
2
3
4
5
6
7
8
9
10
11
-- 第一页
SELECT * FROM product LIMIT 0,5;

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

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

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

多表之间的关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
1.一对一: 两张表之间怎么看都是一对一
         人和身份证号
         
2.一对多: 两张表,从一张表出发看是一对多,反过来看多对一
         比如:商品分类表和商品信息表
             一个分类对应多个商品 -> 一对多
             多个商品对应同一个分类 -> 多对一

3.多对多:两张表,怎么看都是一对多
        比如:商品信息表和订单表
            一个商品可以在多个订单中 -> 一对多
            一个订单中存在多个商品 -> 一对多
            
两个表中的数据想要牵制,需要将两张表约束起来

创建外键约束

1
格式:alter table 从表 add [constraint 外键名称(自定义)] foreign key 从表(外键列名) references 主表(主键列名)

一对多的表创建外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);

#商品表->从表
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price DOUBLE,
  category_id VARCHAR(32)-- 外键  存储的是主表的主键内容
);

  ALTER TABLE products ADD CONSTRAINT cp1 FOREIGN KEY products(category_id) REFERENCES category(cid);

多对多的表创建外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 订单表 -> 主表
 CREATE TABLE `orders`(
  `oid` VARCHAR(32) PRIMARY KEY ,
  `totalprice` DOUBLE 	#总计
  );
   
#订单项表->中间表->从表
CREATE TABLE orderitem(
  pid VARCHAR(50),-- 商品id->外键
  oid VARCHAR(50)-- 订单id ->外键
);

-- 商品表和中间表做
ALTER TABLE orderitem ADD CONSTRAINT po1 FOREIGN KEY orderitem(pid) REFERENCES products(pid);

-- 订单表和中间表做
ALTER TABLE orderitem ADD CONSTRAINT po2 FOREIGN KEY orderitem(oid) REFERENCES orders(oid);

多表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 # 分类表
    CREATE TABLE category (
      cid VARCHAR(32) PRIMARY KEY ,
      cname VARCHAR(50)
    );

    #商品表
    CREATE TABLE products(
      pid VARCHAR(32) PRIMARY KEY ,
      pname VARCHAR(50),
      price DOUBLE,
      flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
      category_id VARCHAR(32), -- 外键
      CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
    );
    #分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

交叉查询

1
2
3
4
1.语法:
   select 列名 from 表A,表B
2.注意:
  交叉查询容易出现笛卡尔乘积
1
2
3
4
5
6
-- 查询所有商品的具体信息
SELECT * FROM category,products;  -- 出现了笛卡尔乘积

SELECT * FROM category,products WHERE category.cid = products.category_id;

SELECT * FROM category c,products p WHERE c.cid = p.category_id;

内连接查询

1
2
3
4
5
1.关键字:inner join on -> inner 可以省略

2.语法:
  a.显示内连接:select 列名 from 表A join 表B on 条件
  b.隐式内连接:select 列名 from 表A,表B where 条件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查询具体的商品信息->隐式内连接
SELECT * FROM category c,products p WHERE c.`cid` = p.`category_id`;

-- 查询具体的商品信息->显示内连接
SELECT * FROM category c JOIN products p ON c.`cid` = p.`category_id`;

-- 用显示内连接的方式查询"化妆品"的商品信息

/*
  on 条件1 and 条件2  -> 条件1 and 条件2是一个大条件
  
  on 条件1 where 条件2 -> 两个小条件
*/
SELECT * FROM category c JOIN products p ON c.`cid` = p.`category_id` AND cname='化妆品';

SELECT * FROM category c JOIN products p ON c.`cid` = p.`category_id` WHERE cname='化妆品';

外连接

1
2
3
4
5
6
7
8
9
10
11
12
1.关键字:outer join on -> outer可以省略
2.分类:  
左外连接:select * from 表A left join 表B on 条件  
右外连接:select * from 表A right join 表B on 条件
3.左外连接,右外连接,内连接 
a.左外连接:查询的是左表的全部和右表的交集 
b.右外连接:查询的是右表的全部和左表的交集 
c.内连接:只查询两个表的交集
4.怎么分区左表和右表  
看join  
在join左边的是左表  
在join右边的是右表
1
2
3
4
5
6
7
8
-- 查询所有的商品信息 左外连接
SELECT * FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`;

-- 查询所有的商品信息 右外连接
SELECT * FROM category c RIGHT JOIN products p ON c.`cid` = p.`category_id`;

-- 查询所有的商品信息内连接
SELECT * FROM category c JOIN products p ON c.`cid` = p.`category_id`;

union联合查询实现全外连接查询（了解）

首先要明确，联合查询不是多表连接查询的一种方式。联合查询是将多条查询语句的查询结果合并成一个结果并去掉重复数据。
全外连接查询的意思就是将左表和右表的数据都查询出来，然后按照连接条件连接
只要将两个结果一连接,左表和右表没有交叉的部分也就都查出来了

1
2
1.union的语法:
  查询语句1 union 查询语句2 union 查询语句3 ...
1
2
3
4
5
6
-- 全外连接
SELECT * FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`

UNION

SELECT * FROM category c RIGHT JOIN products p ON c.`cid` = p.`category_id`;

子查询

1
1.一条查询语句作为另外一条查询语句的条件使用 -> 查询语句嵌套
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 查询products表中'化妆品'的商品信息
SELECT * FROM products WHERE category_id = 'c003'; 

/*
  我们单纯看products表,我们并不确定c003就一定是化妆品
  c003是category表来的
  所以c003应该是查询出来的数据
  
  c003不确定是化妆品,但是化妆品这三个字是确定的吧
  我们只需要根据化妆品从category中查询出对应的id,就可以了
*/
-- 先根据化妆品将id从category表中差出来
SELECT cid FROM category WHERE cname = '化妆品';

-- 再将上面的sql语句放到另外一个查询语句中做条件使用
SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = '化妆品');


-- 查询products表中化妆品和家电的商品信息
SELECT * FROM products WHERE category_id = 'c001' OR category_id = 'c003';
SELECT * FROM products WHERE category_id IN ('c001','c003');

SELECT cid FROM category WHERE cname IN ('家电','化妆品');

SELECT * FROM products WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('家电','化妆品'));