数据库常用指令 DDL
用户登录
mysql -u 用户名 -p 密码
mysql -u root -p
查看数据库
show databases;
创建一个简单默认字符集的数据库
create database 数据库名称;
create database test;
mysql的默认编码为latin1，存储中文时总会出现乱码
创建数据库，判断是否存在如果存在什么都不做
create database if not exists 数据库名称;
- 不会报任何错误
create database if not exists test;
创建数据库，并指定字符集
create database 数据库名 default character set 字符集;
create database test1 default character set gbk;
查看数据库
- 查看所有的:但是看不到字符集
show databases;
- 查看单个的数据库并查看基本信息
show create database 数据名称;
show create database test;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
删除数据库
drop database 数据库名称;
- 删除怎么查看：show databases;
drop database test;
修改数据库
- 修改字符集
- 修改数据库的名称(建议直接新创建)
alter database 数据名称 default character set 字符集;
alter database test default character set UTF8;
切换数据库
use 数据库名称;
use test;
查看目前正在使用的数据库
select database();
表的常用指令 DDL
创建表
建立数据表，就是建立表结构，指定数据表中的一共有多少列，每一列的数据类型
create 指的是【创建】，table 指的是【数据表】。
create table 表名 (
-- 可以定义多个列
字段名 类型(长度) 约束,
字段名 类型(长度) 约束
);
【案例】
-- 创建用户表
create table t_user (
uid int primary key auto_increment, -- 用户 id
uname varchar(20) -- 用户名
);
-- auto_increment 标识自增 (前提是 int 类型)
字符类型
常用的类型有：
- 数字型：int、integer、bigint、mediumint、smallint、tinyint
- 浮点型：double、float、decimal（精确小数类型）
- 字符型：char（定长字符串）、varchar（可变长字符串）
- 日期类型：date（只有年月日）、time（只有时分秒）、datetime（年月日，时分秒）、year
（年） - 二进制字符串类型：binary（定长，以二进制形式保存字符串）、varbinary（可边长）
单表约束
- 一个表要有一个主键
- 唯一约束
- 如:身份证号
- 非空约束(维护数据的唯一性完整性)
- 不能不填:如:男女
主键约束：primary key
唯一约束：unique
非空约束：not null
注意
主键约束 = 唯一约束 + 非空约束
查看表
要先选中数据库要不会报错
描述一下表
- Field(字段)
- Type(类型)
- Null(是不是非空)
- Key(是不是主键)
- Extra(扩展信息)
desc t_user;
查看数据中的所有表
show tables;
查看表的Sql语句
show create table t_user;
快速创建一个表
create table 新表名 linke 旧表名;
- 会和旧的表一样
create table tb_user like t_user;
删除一个表
drop table 表名;
drop table tb_user;
- 删除一个表，判断一下删除表是否存在 让他不报错
- 存在就删除不存在就不删
drop table if exists tb_user;
修改表
-- 1.修改表添加列
alter table 表名 add 列名 类型(长度) 约束;
-- 2.修改表修改列的类型长度及约束
alter table 表名 modify 列名 类型(长度) 约束;
-- 3.修改表修改列名
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
-- 4.修改表删除列
alter table 表名 drop 列名;
-- 5.修改表名
rename table 表名 to 新表名;
[案例]/(有数据需要谨慎)
-- 修改表添加列
alter table user add address varchar(50);
-- 修改表修改列的类型长度及约束
alter table user modify address int(30);
-- 修改表修改列名
alter table user change address addr varchar(50);
-- 修改表删除列
alter table user drop addr;
-- 修改表名
rename table user to tb_user;
数据操作语音 DML
插入记录：insert
语法：
-- 1.向表中插入某些列
insert into 表 (列名1,列名2,列名3..) values (值1,值2,值3..);
-- 2.向表中插入所有列
insert into 表 values (值1,值2,值3..);
-- 3.从另外一张表查某些列的结果插入当前表
insert into 表 (列名1, 列名2, 列名3..) values select (列名1,列名2,列名3..) from 表
-- 4.从另外一张表查所有列的结果插入当前表
insert into 表 values select * from 表
【案例】
-- 向表中插入某些列，必须写列名
insert into user (uid, uname) values (001, 'cuihua');
-- 向表中插入所有列
insert into user values (002, 'aqiang');
注意：
- ① 列名数与 values 后面的值的个数相等
- ② 列的顺序与插入的值得顺序一致
- ③ 列名的类型与插入的值要一致.
- ④ 插入值得时候不能超过最大长度.
- ⑤ 值如果是字符串或者日期需要加引号’’ （一般是单引号或者双引号）
更新记录：update
语法格式：update 更新、set 修改的列值、where 指定条件。
-- 1.不指定条件，会修改表中当前列所有数据
update 表名 set 字段名=值, 字段名=值;
-- 2.指定条件，符合条件的才会修改
update 表名 set 字段名=值, 字段名=值 where 条件;
【案例】
-- 更新所有字段的值
update user set uname='xiaodong';
-- 根据指定的条件来更新
update user set uname='hashiqi' where uid = 2;
注意：
- ① 列名的类型与修改的值要一致
- ② 修改值得时候不能超过最大长度
- ③ 值如果是字符串或者日期需要加==``==引号
删除记录：delete & truncate
语法格式:
delete from 表名 [where 条件];
truncate table 表名
注意
删除表中所有记录使用【delete from 表名】，还是用【truncate table 表名】？
删除方式的区别：
delete:一条一条删除，不清空 auto_increment 记录数。
truncate:直接将表删除，重新建表，auto_increment 将置为零，从新开始
【案例】
-- 删除所有数据
delete from user;
-- 删除 uid = 1 的用户
delete from user where uid = 1;
查询DQL
查询语句的顺序
select 列表 from where 条件 group by(分组) having(过滤) order by(排序)
select * | 列名, 列名 from 表名 [where 条件];
- select是查询指令，可以读1-n行数据；
- 列名换成==*==号,可以查询所有字段数据；
- 使用where来指定对应的条件
为了后续查询语句准备工作【样例表】
CREATE TABLE products (
-- 自增加 AUTO_INCREMENT
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20), -- 商品名称
price DOUBLE, -- 商品价格
pdate DATE, -- 日期
sid VARCHAR(20) -- 分类ID
);
INSERT INTO products VALUES(NULL,'泰国大榴莲', 98, NULL, 's001');
INSERT INTO products VALUES(NULL,'新疆大枣', 38, NULL, 's002');
INSERT INTO products VALUES(NULL,'新疆切糕', 68, NULL, 's001');
INSERT INTO products VALUES(NULL,'十三香', 10, NULL, 's002');
INSERT INTO products VALUES(NULL,'老干妈', 20, NULL, 's002');
INSERT INTO products VALUES(NULL,'老干妈', 20, NULL, 's002');
简答查询
-- 查询所有
select * from 表名
【案例】
-- 查询所有
select * from products
查询所有商品的指定列
select 列名, 列名... from products;
【案例】
select pname, sid from products;
别名查询
-- 语法
-- 使用关键字as, as可以写上也可以省略
-- 表的别名、列的别名
select 列名 as 别名, 列名 as 别名 .... from 表名 as 表别名
-- or
select 列名 别名, 列名 别名 .... from 表名 表别名
【案例】
-- as or No as
select * from products as p;
select * from products p;
-- 表的别名为什么是 表别名.列明【是不是脱了裤子放p】
-- 实则不是的 因为在多表查询中会可能会有重复列名，就会用到【表别名.列明】
-- 表的别名 还可以简化SQL,区分表
-- 列别名的作用：改变查询结果中列名的名称, 还可以增加可读性
select p.pid pname from products as p;
去掉重复值
select distinct 列名, 列名 from 表名;
【案例】
select distinct pname,sid from products;
为一列上所有的的数据加上2
select 列名 + 数值 from 表名;
【案例】
select pname, price + 2 from products;
条件查询
使用条件查询，可以根据当下具体情况直查想要的那部分数据，对记录进行过滤。
SQL 语法关键字： WHERE
语法格式：
select * from 表名 where 条件;
条件怎么写:
| 操作符 | 描述 | 实例 |
|---|---|---|
| = | 等号,检测两个值是否相等，如果相等返回true | (A = B) 返回false |
| <>,!= | 不等于,检测两个值是否相等，如果不相等返回true | (A != B) 返回 true |
| > | 大于号，检测左边的值是否大于右边的值,如果左边的值大于右边的值返回true | (A > B) 返回 false |
| < | 小于号,检测左边的值是否小于右边的值,如果左边的值小于右边的值返回true | (A < B) 返回 true |
| >= | 大于等于号,检测左边的值是否大于或等于右边的值,如果左边的值大于或等于右边的值返回true | (A >= B) 返回 false |
| <= | 小于等于号,检测左边的值是否小于于或等于右边的值,如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true |
运算符：局限性只能针对一个字段(不使用逻辑运算符的前提下)
-- 查询商品名称为十三香的商品所有信息
select * from products where pname='十三香';
-- 查询商品名价格 >60 商品所有信息
select * from products where price > 60;
-- 不等于
select * from products where price <> 60;
select * from products where price != 60;
逻辑运算符
| NOT | 逻辑非【!】 |
|---|---|
| AND | 逻辑与【&&】 |
| OR | 逻辑或【||】 |
逻辑运算符：作用是将两个及以上的条件组合在一起
逻辑运算符：
- NOT 【非】
- AND 【与】
- OR 【或】
-- 语法
-- 查询大商品价格 大于40且小于30的所有商品 【逻辑有问题，只是语法没有错误】
select * from 表名 where 条件1 逻辑运算符 条件2;
-- 查询大商品价格 大于40或小于30的所有商品
select * from 表名 where 条件1 逻辑运算符 条件2;
【案例】
-- 查询大商品价格 大于40或小于30的所有商品
select * from products where price > 30 OR price < 30;
查询条件in
作用指定范围内的所有数据
select * from products where 列名 in (3, 2);
【案例】
-- 在范围内
select * from products where pid in (3, 2);
-- 不在范围内
select * from products where pid not in (38, 38);
范围查询
范围查询:作用查询指定条件内的数据，价格[30 - 100]
- 01写法查询
select *from products where price >= 38 && price <= 98;
- 02写法: Between … and … 【>= and <=】
select * from products where price between 38 and 98;
模糊查询
模糊查询：作用是帮助匹配模糊的字段内容
如有字段：一号学生、二号学生、三号学生、四号学生… …
模糊查询：查询学生只要字段中包含了学生就回查询到!
-- 语法: where 之后加上 like 语法。【通配符有】 % (百分号)， _ (下划线)
% (百分号) 代表任意长度的内容模糊匹配：
- 【%xxx】以 xxx 结尾的任意内容。
- 【xxx%】以 xxx 开头的任意内容。
- 【%xxx%】包含xxx的任意内容。
_ (下划线) 代表任意一个字符的内容模糊匹配：
-
【_xxx】以任意单个字符开头的 xxx 的内容。
-
-
【xxx__x】以 xxx 中间任意二个字符以 x 结尾的内容。
-- 语法
-- 查询所有新疆的产品
select * from 表名 where 列名 like 'xx%';
-- 以打大枣结尾的，但是头两个字符任意
select * from 表名 where 列名 like '__xx';
【案例】
-- 查询所有新疆的产品 %
select * from products where pname like '新疆%';
-- 以打大枣结尾的，但是头两个字符任意 __
select * from products where pname like '__大枣';
排序查询
排序查询：作用将查询结果进行排序
-- 语法
select * from 表名 where 字段=值 order by 字段名 排序规则[升序、降序]
-- 升序 asc、降序desc
【案例】
-- 根基价格 升序
select *from products order by price asc;
-- 查询所有包含'新'的商品，并且价格是从大大小
select * from products where pname like '%新%' order by price desc;
聚合查询
聚合查询：作用对单列数据进行聚合分析【求和、求平均值、求最大值，求最小值】
-- 语法
select 聚合函数 from 表 where 条件;
聚合函数例子：
- 求和sum()
- 求平均值avg()
- 求最大值max()
- 求最小值min()
- 求元素个数count()
注意：SQL语句是基于需求实现的，同一个需求可能出现n多种SQL写法。如果SQL语句没有需求背景，SQL如无根之水，毫无意义。
【案例】
-- 求所有商品价格的总和
select sum(price) from products;
-- 显示的比较模糊 就用别名
select sum(price) as 所有商品总价 from products;
-- 求所有商品的平均价格/最小价格/最高价格
select avg(price) as 商品平均价格, max(price) as 最大商品价格, min(price) as 商品最小价格 from products;
-- 元素个数
select count(*) from products;
分组查询
分组查询：作用将查询结果按照某列数据来进行分组呈现
-- 语法格式
select 列名 from 表 where group by 分组字段 having 条件;
注意：
- select 语句中的列必须出现在 group by 的子句中。
- group by的列，不一定非要出现在select列中。
- 聚合函数只能出现在select列，或者是having语句中，一定不能出现在where条件中。
【案例】
-- 需求
-- 统计，指定分类下商品的个数
select sid as 商品分类, count(*) as 商品个数 from products group by sid;
-- 统计，某类商品的平均价格，商品的平均价不能低于60
select sid as 商品分类 , avg(price) as 商品的平均价格 from products group by sid having avg(price) >= 60;
分页查询
关键字: limit[offset] rows
分页查询:作用是将查询的结果按照一页一页的形式输出。
-- 语法
select 列 from 表 limit offset, lenght
【案例】
-- 分页查询，将数据分为三页，查询第一页数据
select * from products limit 0, 2;
-- 0 代表偏移量（开始行数）# 这个值是算出来的 那么么第二页开始就是 2 2
-- 2 每页显示的行数
limit 关键字不是SQL92标准提出的关键字，它是MySQL独有的语法。
通过 limit 关键字，MySQL实现了物理分页。
分页分为逻辑分页和物理分页
逻辑分页：将数据库中的数据查询到内存之后再进行分页。
物理分页：通过 LIMIT 关键字，直接再数据库中进行分页，最终返回的数据，只是分页后的数据。
【输出】
# 第一页输出
mysql> select * from products limit 0, 2;
+-----+-----------------+-------+-------+------+
| pid | pname | price | pdate | sid |
+-----+-----------------+-------+-------+------+
| 1 | 泰国大榴莲 | 98 | NULL | s001 |
| 2 | 新疆大枣 | 38 | NULL | s002 |
+-----+-----------------+-------+-------+------+
# 第二页输出
mysql> select * from products limit 2, 2;
+-----+--------------+-------+-------+------+
| pid | pname | price | pdate | sid |
+-----+--------------+-------+-------+------+
| 3 | 新疆切糕 | 68 | NULL | s001 |
| 4 | 十三香 | 10 | NULL | s002 |
+-----+--------------+-------+-------+------+
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
