Mysql 运用

于 2023-04-26 20:36:21 发布
阅读量258 收藏
点赞数
文章标签： 数据库 sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_67024075/article/details/130392655
版权

SQL约束

主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

  • 主键必须包含唯一的值。

  • 主键列不能包含 NULL 值。

  • 每个表都应该有一个主键，并且每个表只能有一个主键。

添加主键约束

  • 方式一：创建表时，在字段描述处，声明指定字段为主键：

CREATE TABLE persons

(
  id_p int PRIMARY KEY,
  lastname varchar(255),
  firstname varchar(255),
  address varchar(255),
  city varchar(255)
)

  • 方式二：创建表时，在constraint约束区域，声明指定字段为主键：

    • 格式：[constraint 名称] primary key (字段列表)

    • 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。

    • 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为联合主键。

CREATE TABLE persons
(
firstname varchar(255),
lastname varchar(255),
address varchar(255),
city varchar(255),
CONSTRAINT pk_personID PRIMARY KEY (firstname,lastname)
)

  • 方式三：创建表之后，通过修改表结构，声明指定字段为主键：

    • 格式：ALTER TABLE persons ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)

CREATE TABLE persons
(
firstname varchar(255),
lastname varchar(255),
address varchar(255),
city varchar(255)
)
ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname)

删除主键约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

ALTER TABLE persons DROP PRIMARY KEY

自动增长列

我们通常希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键(一般是主键)。

  • 下列 SQL 语句把 "persons" 表中的 "p_id" 列定义为 auto_increment 主键

CREATE TABLE persons
(
p_id int PRIMARY KEY AUTO_INCREMENT,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
)

  • 向persons添加数据时，可以不为p_id字段设置值，也可以设置成null，数据库将自动维护主键值：

INSERT INTO persons (firstname,lastname) VALUES ('Bill','Gates')
INSERT INTO persons (p_id,firstname,lastname) VALUES (NULL,'Bill','Gates')

  • 扩展：默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：

ALTER TABLE persons AUTO_INCREMENT=100

  • 面试题

问：针对auto_increment ，删除表中所有记录使用 delete from 表名 或使用 truncate table 表名，二者有什么区别？

删除方式：
    delete 一条一条删除，不清空auto_increment记录数。
    truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。

非空约束

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

  • 下面的 SQL 语句强制 "id_p" 列和 "lastname" 列不接受 NULL 值：

CREATE TABLE persons
(
id_p int NOT NULL,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255)
)

唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。 UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。 PRIMARY KEY 拥有自动定义的 UNIQUE 约束。 请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

添加唯一约束

与主键添加方式相同，共有3种

  • 方式一：创建表时，在字段描述处，声明唯一：

CREATE TABLE persons
(
id_p int UNIQUE,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255)
)

  • 方式二：创建表时，在约束区域，声明唯一：

CREATE TABLE persons
(
id_p int,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255),
CONSTRAINT 名称UNIQUE (Id_P)
)

  • 方式三：创建表后，修改表结构，声明字段唯一：

ALTER TABLE persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)

删除唯一约束

  • 如需撤销 UNIQUE 约束，请使用下面的 SQL：

ALTER TABLE persons DROP INDEX 名称

  • 如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。

默认约束

default 约束 用于指定字段默认值。当向表中插入记录时，如果没有明确的为字段赋值，则自动赋予默认值。

  • 添加默认约束，在创建表时候添加

CREATE TABLE t_user(user_id INT(10) DEFAULT 3);

  • 删除默认约束

ALTER TABLE t_user MODIFY user_id INT(10);

约束总结

#常见约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
​
​
分类：
    NOT NULL：非空，用于保证该字段的值不能为空
    DEFAULT:默认，用于保证该字段有默认值
    PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
    UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
添加约束的时机：
    1.创建表时
    2.修改表时
    
约束的添加分类：
    列级约束：
        约束语法上都支持        
    表级约束：
        主键、唯一
*/
​
CREATE TABLE 表名(
    字段名 字段类型 列级约束,
    字段名 字段类型,
    表级约束
)
#一、创建表时添加约束
#1.添加列级约束
/*
语法：
直接在字段名和类型后面追加 约束类型即可。
支持：默认、非空、主键、唯一
​
*/
CREATE TABLE student(
    id INT PRIMARY KEY,#主键
    name VARCHAR(20) NOT NULL UNIQUE,#非空
    seat INT UNIQUE,#唯一
    age INT DEFAULT  18#默认约束
);
​
#查看student中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM student;
​
#2.添加表级约束
/*
​
语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名) 
*/
​
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT,
    name VARCHAR(20),
    seat INT,
    age INT,
    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat)#唯一键
);
​
#二、修改表时添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
​
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT,
    name VARCHAR(20),
    seat INT,
    age INT
)
DESC student;
#1.添加非空约束
ALTER TABLE student MODIFY  name VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE student MODIFY  age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE student MODIFY  id INT PRIMARY KEY;
#②表级约束
ALTER TABLE student ADD PRIMARY KEY(id);
​
#4.添加唯一
#①列级约束
ALTER TABLE student MODIFY  seat INT UNIQUE;
#②表级约束
ALTER TABLE student ADD UNIQUE(seat);
​
#三、修改表时删除约束
​
#1.删除非空约束
ALTER TABLE student MODIFY name VARCHAR(20);
​
#2.删除默认约束
ALTER TABLE student MODIFY  age INT ;
​
#3.删除主键
ALTER TABLE student DROP PRIMARY KEY;
​
#4.删除唯一
ALTER TABLE student DROP INDEX seat;
​
​

 SQL语句(DQL)

DQL准备工作和语法

准备工作

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

语法

select [distinct] 
* | 列名,列名 
from 表 
where 条件

简单查询

  • 练习

 #.查询常量值
 SELECT 100;
 SELECT 'hello';
 #.查询表达式
 SELECT 100%98;
 #.查询函数
 SELECT NOW();
 SELECT VERSION();
 #两个操作数都为数值型，则做加法运算
 SELECT 100+90; 
 #只要其中一方为字符型，试图将字符型数值转换成数值型 如果转换成功，则继续做加  法运算
 SELECT '123'+90;
 #如果转换失败，则将字符型数值转换成0
 SELECT 'hello'+90; 
 #只要其中一方为NULL，则结果肯定为NULL
 SELECT NULL+10; 
 #连接字符串
 SELECT CONCAT('a','b','c')
#查询所有的商品.   
select * from product;
#查询商品名和商品价格.
select pname,price from product;
#别名查询.使用的关键字是as（as可以省略的）.表别名:
select * from product as p;
#别名查询.使用的关键字是as（as可以省略的）.列别名:
select pname as pn from product;
#去掉重复值.
select distinct price from product;
#查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示. 
select pname,price+10 from product;

条件查询

比较运算符> < >= <= = <> !=大于、小于、大于(小于)等于、不等于
BETWEEN ...AND...显示在某一区间的值(含头含尾)
IN(set)显示在in列表中的值，例：in(100,200)
LIKE ‘张pattern’模糊查询，Like语句中，% 代表零个或多个任意字符，_ 代表一个字符， 例如：first_name like '_a%';
IS NULL判断是否为空
逻辑运行符and多个条件同时成立
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 >= 200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
#查询商品价格是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 '_想%';
#商品没有分类的商品
SELECT * FROM product WHERE category_id IS NULL
​
#查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL

排序查询

通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。

  • 格式:

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
#ASC 升序 (默认)
#DESC 降序

  • 练习：

#使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
#在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
#显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;

聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

今天我们学习如下五个聚合函数：

  • count：统计指定列不为NULL的记录行数；

  • sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

  • max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

  • min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

  • avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

练习：

#查询商品的总条数
SELECT COUNT(*) FROM product; 
#查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;
#查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
#查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';
#查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;

分组查询

分组查询是指使用group by字句对查询信息进行分组。

  • 格式：

SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

  • having与where的区别:

    • having是在分组后对数据进行过滤.

      where是在分组前对数据进行过滤

    • having后面可以使用分组函数(统计函数)

      where后面不可以使用分组函数。

练习：

#统计各个分类商品的个数
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;
#统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

分页查询

select 列名 from 表名 limit page,pageSize;
​
0   10  
10  10 
20  10 
30  10 
(当前页-1)*pageSize
​

 

 

-- 查看所有数据库
SHOW DATABASES;

-- 使用数据库 
USE mysql;

-- 创建数据库 
CREATE DATABASE day01;
-- 创建数据库指定编码 编码已经修改的 可以不用
#CREATE DATABASE day01 CHARACTER SET utf8;

-- 使用day01
USE day01;


-- 查看某个数据库的定义的信息:
SHOW CREATE DATABASE day01;

-- 删除数据库
DROP DATABASE day01;

-- 查看正在使用的数据库 
SELECT DATABASE();

 

/*
    创建表 
    create table 表名(
        列名 数据类型(长度)  [约束],
        列名 数据类型(长度)  [约束],
        列名 数据类型(长度)  [约束]
    );
    
    主键约束:限制当前列数据 非空 唯一 
             一张表只能有一个主键约束
             一般情况主键约束都应该是与业务逻辑无关的列
                primary key 
    主键自动增长:由mysql自己来维护主键  auto_increment

*/
USE day01;

CREATE TABLE users(
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100),
    `password` VARCHAR(100)
);

CREATE TABLE category(
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100)
);
/*
    查看数据库中所有的表 
    show tables;
*/
SHOW TABLES;

/*
    查看表结构 
    desc 表名
*/
DESC users;
DESC category;

/*
    删除表
    drop table 表名;
*/
DROP TABLE users;
/*
    修改表结构
*/
/*
    添加列
    alter table 表名 add 列名 数据类型 [约束];
*/
ALTER TABLE users ADD addr VARCHAR(200) ;
/*
    修改列  数据类型和约束
    alter table 表名 modify 列名 数据类型 [约束];
    
*/
ALTER TABLE users MODIFY addr INT NOT NULL;
ALTER TABLE users MODIFY addr VARCHAR(200) ;
/*
    修改列  列名 数据类型 约束
    alter table 表名 change 旧列名 新列名 数据类型 [约束];
*/
ALTER TABLE users CHANGE addr address VARCHAR(100) NOT NULL;
/*
    删除列 
    alter table 表名 drop 列名;
*/
ALTER TABLE users DROP address;
/*
    修改表名
    rename table  旧表面 to 新表名;
*/
RENAME TABLE users TO `user`;
RENAME TABLE `user` TO users;
/*
    修改字符集
    alter table 表名 character set 字符集(了解);
*/
 

                      竹  石

                【清】郑板桥

咬定青山不放松，立根原在破岩中。

千磨万击还坚劲，任尔东西南北风。

All996
关注
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值