mysql的简单了解
程序员文章站
2022-06-17 10:50:13
...
数据库概述
- 什么是数据库: 数据库就是一个文件系统，只不过需要通过命令(sql)来操作这个文件系统
- 数据库的作用：存储数据，数据的仓局，带有不同访问权限的人可以有不同的操作
- 常见的关系型数据库：mysql, oracle, mariadb(mysql分支，和mysql非常相似),db2, sqlserver
- 常见的非关系型数据库：MongoDB, redis
- 关系型数据库：主要描述实体与实体的关系
mysql的简单使用
安装和卸载
安装：安装教程
卸载：卸载mysqlServer，删除mysql目录下所有文件，删除mysql所有存储数据。
mysql的一些语句
DDL(定义): create drop alter(修改)
DML(操作): insert update delete
DCL(数据控制语句): 定义访问权限，取消访问权限，安全设置，grant
DQL(查询)：select from子句 where子句
常用的数据库crud操作
创建数据库
- 登录
mysql -uroot -proot //(账号密码为root root)
- 创建一个数据库DeeJay
create database DeeJay1;
创建数据库的时候要指定字符集的话可以这么写：
create database DeeJay1 character set utf8;create database DeeJay1 character set utf8 collate 校对规则;
-
查看数据库
- 查看所有数据库
输出如下：show databases;
可以看到还有一些默认库。mysql> show databases; +--------------------+ | Database | +--------------------+ | deejay1 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.02 sec)- 查看数据库定义的语句：
show create database 数据库名;mysql> show create database DeeJay1; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | DeeJay1 | CREATE DATABASE `DeeJay1` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.01 sec)
- 查看所有数据库
-
修改数据库的操作
- 修改数据库的字符集
alter database 数据库名 character set 字符集;mysql> alter database DeeJay1 character set gbk; Query OK, 1 row affected (0.13 sec) mysql> show create database DeeJay1; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | DeeJay1 | CREATE DATABASE `DeeJay1` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) 删除数据库
drop database 数据库名;
mysql> drop database DeeJay1;
Query OK, 0 rows affected (0.19 sec)
mysql> show create database DeeJay1;
ERROR 1049 (42000): Unknown database 'deejay1'
- 其他数据库操作
- 切换数据库（选中数据库）
use 数据库名;- 查看当前正在使用的数据库
select database();mysql> create database base1; Query OK, 1 row affected (0.15 sec) mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> use base1; Database changed mysql> select database(); +------------+ | database() | +------------+ | base1 | +------------+ 1 row in set (0.00 sec)
常用的表的crud操作
创建表
create table 表名(
列名 列的类型 约束,
列名2 列2的类型 约束
);
列的类型(和java做下对比)：
| java | sql |
|---|---|
| int | int |
| char/String | char/varchar |
| double | double |
| float | float |
| boolean | boolean |
| date | date(YYYY-MM-DD)/time(hh:mm:ss)/datetime(YYYY-MM-DD hh:mm:ss 默认为null)/timestamp(YYYY-MM-DD hh:mm:ss 默认为当前时间) |
char: 固定长度 varchar:可变长度
数据库中的列类型还有text(主要用来存储文本)和blob(主要用来存放二进制)
列的约束：
主键约束： primary key
唯一约束： unique
非空约束： not null
创建表举例：
- 分析实体： 学生
- 学生ID
- 姓名
- 性别
- 年龄
mysql> create table student(studentId int primary key, name varchar(25), gender boolean, age int);
Query OK, 0 rows affected (0.72 sec)
查看表
- 查看所有的表
show tables;
mysql> show tables;
+-----------------+
| Tables_in_base1 |
+-----------------+
| student |
+-----------------+
1 row in set (0.07 sec)
- 查看表的定义
show create table 表名;
mysql> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`studentId` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`gender` tinyint(1) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`studentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
- 查看表结构
desc 表名;
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| studentId | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)
修改表
alter table 表名 修改的类型 列名 列的类型 列的约束;
修改的类型: 添加列(add), 修改列(modify), 修改列名(change), 删除列(drop), 修改表名(rename)
- 添加列(add)
mysql> alter table student add score int not null; // 给student添加一列成绩，为int型，并且不能为null
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| studentId | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| score | int(11) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 修改列(modify)
mysql> alter table student modify gender int;// 修改student的gender列的类型为int
Query OK, 0 rows affected (0.75 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| studentId | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| gender | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| score | int(11) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 修改列名(change)
mysql> alter table student change name studentname varchar(25);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| studentId | int(11) | NO | PRI | NULL | |
| studentname | varchar(25) | YES | | NULL | |
| gender | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| score | int(11) | NO | | NULL | |
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 删除列(drop)
mysql> alter table student drop score;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| studentId | int(11) | NO | PRI | NULL | |
| studentname | varchar(25) | YES | | NULL | |
| gender | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- 修改表名(rename), 修改表的字符集
这两种方法一般不常用，慎用。
rename table student to normalstudent;
alter table student character set gbk;
删除表
drop table 表名;
mysql> drop table student;
Query OK, 0 rows affected (0.24 sec)
mysql> show tables;
Empty set (0.00 sec)
sql对表中数据的CRUD操作
现有如下结构的一个表:
mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| ismale | tinyint(1) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
插入数据
insert into 表名(列名1,列名2,列名3) values(值1, 值2, 值3);
mysql> insert into student(id, name, ismale, age) values(1, "zhangsan", true, 20);
Query OK, 1 row affected (0.08 sec)
mysql> select * from student;
+----+----------+--------+------+
| id | name | ismale | age |
+----+----------+--------+------+
| 1 | zhangsan | 1 | 20 |
+----+----------+--------+------+
1 row in set (0.00 sec)
对于上述的表中所有列都插入数据的情况，可以简写为：
insert into 表名 values(值1, 值2, 值3);
mysql> insert into student values(2, "lisi", true, 18);
Query OK, 1 row affected (0.08 sec)
mysql> select * from student;
+----+----------+--------+------+
| id | name | ismale | age |
+----+----------+--------+------+
| 1 | zhangsan | 1 | 20 |
| 2 | lisi | 1 | 18 |
+----+----------+--------+------+
2 rows in set (0.00 sec)
也可以灵活指定想插入的列。
批量插入：
insert into 表名 values(值1, 值2, 值3),(值1, 值2, 值3),(值1, 值2, 值3);
单条插入和批量插入的效率问题： 批量会比较快，但是如果某一条数据有问题的话会都失败。
mysql> insert into student values(3, "Yang", true, 23),(4, "Wen", false, 25);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+----------+--------+------+
| id | name | ismale | age |
+----+----------+--------+------+
| 1 | zhangsan | 1 | 20 |
| 2 | lisi | 1 | 18 |
| 3 | Yang | 1 | 23 |
| 4 | Wen | 0 | 25 |
+----+----------+--------+------+
4 rows in set (0.00 sec)
删除记录
delete from 表名 [where 条件]
mysql> delete from student where name='zhangsan';
Query OK, 1 row affected (0.06 sec)
mysql> select * from student;
+----+------+--------+------+
| id | name | ismale | age |
+----+------+--------+------+
| 2 | lisi | 1 | 18 |
| 3 | Yang | 1 | 23 |
| 4 | Wen | 0 | 25 |
+----+------+--------+------+
3 rows in set (0.00 sec)
delete from student;如果没有where条件，执行这个语句的话会将数据一条一条全部删除。
delete删除数据和truncate删除数据的区别:delete属于DML,是一条一条删除表中数据, truncate属于DDL，先去删除表再去重建表。
如果数据较少那么delete较快，如果数据多，那么truncate较快。
修改表记录
update 表名 set 列名=列的值,列名2=值2 [where 条件]
mysql> update student set name="zhangsan" where id=2; // 将id为2的那一列的name改为zhangsan
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+----------+--------+------+
| id | name | ismale | age |
+----+----------+--------+------+
| 2 | zhangsan | 1 | 18 |
| 3 | Yang | 1 | 23 |
| 4 | Wen | 0 | 25 |
+----+----------+--------+------+
3 rows in set (0.00 sec)
update student set name="zhangsan",ismale=false;如果后面不跟where条件，那么表中所有的列的name和ismale列都会改。
查询记录
select [distinct] [ * ] [列名1,列名2] from 表名 [where 条件];
distinct: 去除重复的数据 distinct详细用法
首先创建一个商品分类的表：
create table category(
cid int primary key auto_increment,
cname varchar(25),
cdesc varchar(31)
);
然后插入数据：
insert into category values(null, "手机数码", "电子产品");
insert into category values(null, "鞋靴箱包", "江南皮鞋厂打造");
insert into category values(null, "香烟酒水", "芙蓉王，茅台");
insert into category values(null, "酸奶饼干", "哇哈哈");
insert into category values(null, "馋嘴零食", "瓜子花生");
现在这个表的结构为：
mysql> select * from category;
+-----+--------------+-----------------------+
| cid | cname | cdesc |
+-----+--------------+-----------------------+
| 1 | 手机数码 | 电子产品 |
| 2 | 鞋靴箱包 | 江南皮鞋厂打造 |
| 3 | 香烟酒水 | 芙蓉王，茅台 |
| 4 | 酸奶饼干 | 哇哈哈 |
| 5 | 馋嘴零食 | 瓜子花生 |
+-----+--------------+-----------------------+
5 rows in set (0.00 sec)
对于指定列的查询：
mysql> select cname,cdesc from category;
+--------------+-----------------------+
| cname | cdesc |
+--------------+-----------------------+
| 手机数码 | 电子产品 |
| 鞋靴箱包 | 江南皮鞋厂打造 |
| 香烟酒水 | 芙蓉王，茅台 |
| 酸奶饼干 | 哇哈哈 |
| 馋嘴零食 | 瓜子花生 |
+--------------+-----------------------+
5 rows in set (0.00 sec)
再来创建一张商品的表，商品和商品分类的关系为所属关系:
create table product(
pid int primary key auto_increment,
pname varchar(25),
price double, // 价格
pdate timestamp, // 生产日期
cno int // 商品分类的id
);
插入一些数据：
insert into product values(null, "小米mix2s", 2700, current_timestamp, 1);
insert into product values(null, "华为p30", 4788, current_timestamp, 1);
insert into product values(null, "阿迪王", 99, current_timestamp, 2);
insert into product values(null, "老村长", 88, current_timestamp, 3);
insert into product values(null, "劲酒", 35, current_timestamp, 3);
insert into product values(null, "小熊饼干", 3, current_timestamp, 4);
insert into product values(null, "卫龙辣条", 1, current_timestamp, 5);
insert into product values(null, "旺旺雪饼", 2, current_timestamp, 5);
当前product表为：
mysql> select * from product;
+-----+--------------+-------+---------------------+------+
| pid | pname | price | pdate | cno |
+-----+--------------+-------+---------------------+------+
| 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 |
| 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 |
| 6 | 阿迪王 | 99 | 2019-04-14 13:14:32 | 2 |
| 7 | 老村长 | 88 | 2019-04-14 13:14:32 | 3 |
| 8 | 劲酒 | 35 | 2019-04-14 13:14:32 | 3 |
| 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 |
| 10 | 卫龙辣条 | 1 | 2019-04-14 13:14:32 | 5 |
| 11 | 旺旺雪饼 | 1 | 2019-04-14 13:14:32 | 5 |
+-----+--------------+-------+---------------------+------+
8 rows in set (0.00 sec)
-
简单查询
-
查询所有商品:
select * from product; -
查询商品名称和价格
select pname,price from product; -
别名查询.
as关键字， as可以省略- 表别名,
select p.pname, p.price from product as p（主要用于多表查询）select p.pname, p.price from product as p; - 列别名,
select pname as 商品名称, price as 商品价格 from product;
可以看到，输出的列别名已经发生了更改。mysql> select pname as 商品名称, price as 商品价格 from product; +--------------+--------------+ | 商品名称 | 商品价格 | +--------------+--------------+ | 小米mix2s | 2700 | | 华为p30 | 4788 | | 阿迪王 | 99 | | 老村长 | 88 | | 劲酒 | 35 | | 小熊饼干 | 3 | | 卫龙辣条 | 1 | | 旺旺雪饼 | 1 | +--------------+--------------+ 8 rows in set (0.00 sec)
- 表别名,
-
去掉重复的值
- 查询商品所有的价格:
输出的值有些是重复的，那么我们可以引入distinct来去掉重复的值：mysql> select price from product; +-------+ | price | +-------+ | 2700 | | 4788 | | 99 | | 88 | | 35 | | 3 | | 1 | | 1 | +-------+ 8 rows in set (0.00 sec)mysql> select distinct price from product; +-------+ | price | +-------+ | 2700 | | 4788 | | 99 | | 88 | | 35 | | 3 | | 1 | +-------+ 7 rows in set (0.00 sec)
- 查询商品所有的价格:
-
select查询运算
假设我们现在所有的商品都要打8折，那么可以这样查询输出:
mysql> select *,price*0.8 from product; +-----+--------------+-------+---------------------+------+--------------------+ | pid | pname | price | pdate | cno | price*0.8 | +-----+--------------+-------+---------------------+------+--------------------+ | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | 2160 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | 3830.4 | | 6 | 阿迪王 | 99 | 2019-04-14 13:14:32 | 2 | 79.2 | | 7 | 老村长 | 88 | 2019-04-14 13:14:32 | 3 | 70.4 | | 8 | 劲酒 | 35 | 2019-04-14 13:14:32 | 3 | 28 | | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | 2.4000000000000004 | | 10 | 卫龙辣条 | 1 | 2019-04-14 13:14:32 | 5 | 0.8 | | 11 | 旺旺雪饼 | 1 | 2019-04-14 13:14:32 | 5 | 0.8 | +-----+--------------+-------+---------------------+------+--------------------+ 8 rows in set (0.00 sec)可以加一个列别名:
mysql> select *,price*0.8 as 8折折后价 from product; +-----+--------------+-------+---------------------+------+--------------------+ | pid | pname | price | pdate | cno | 8折折后价 | +-----+--------------+-------+---------------------+------+--------------------+ | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | 2160 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | 3830.4 | | 6 | 阿迪王 | 99 | 2019-04-14 13:14:32 | 2 | 79.2 | | 7 | 老村长 | 88 | 2019-04-14 13:14:32 | 3 | 70.4 | | 8 | 劲酒 | 35 | 2019-04-14 13:14:32 | 3 | 28 | | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | 2.4000000000000004 | | 10 | 卫龙辣条 | 1 | 2019-04-14 13:14:32 | 5 | 0.8 | | 11 | 旺旺雪饼 | 1 | 2019-04-14 13:14:32 | 5 | 0.8 | +-----+--------------+-------+---------------------+------+--------------------+ 8 rows in set (0.00 sec)上述的输出，其实并不改变库内的数据，仅仅是在查询结果上做了运算。
-
where关键字进行条件查询
指定条件，确定要查询记录
- 查询价格大于60的商品：
select * from where price > 60; - where后的关系运算符：
> >= < <= = != <><>和!=都为不等于，区别在于<>是标准sql语法而!=是非标准的sql语法 - where后的逻辑运算符：
and, or, not，判断某一列是否为空:is null,is not null - 查询价格大于10并且小于100的商品:
select * from product where price < 100 and price > 10;
也可以使用between...and...,select * from product where price between 10 and 100; - like 模糊查询
-
_代表的是一个字符 -
%代表的是多个字符 - 查询出商品名称中带有"小"的所有商品：
select * from product where pname like "%小%";mysql> select * from product where pname like "%小%"; +-----+--------------+-------+---------------------+------+ | pid | pname | price | pdate | cno | +-----+--------------+-------+---------------------+------+ | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | +-----+--------------+-------+---------------------+------+ 2 rows in set (0.00 sec) - 查询名字第二个字是'熊'的商品:
select * from product where pname like "_熊%";mysql> select * from product where pname like "_熊%"; +-----+--------------+-------+---------------------+------+ | pid | pname | price | pdate | cno | +-----+--------------+-------+---------------------+------+ | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | +-----+--------------+-------+---------------------+------+ 1 row in set (0.00 sec)
-
- in 在某个范围中获得值
- 查询出商品分类id在1,4,5里面的所有商品
select * from product where cno in (1,4,5);
- 查询出商品分类id在1,4,5里面的所有商品
- 查询价格大于60的商品：
-
排序查询：order by 关键字. 关键字: asc(ascend升序,为默认值), desc(descend降序).
- 查询所有商品进行按价格降序排列:
select * from product order by price desc; - 查询商品名称中含有"小"的所有商品并且进行按价格降序排列:
select * from product where pname like "%小%" order by price desc;
- 查询所有商品进行按价格降序排列:
-
-
复杂查询
- 聚合函数：
函数 | 作用
sum() | 求和
count() | 统计数量
max() | 最大值
min() | 最小值
avg() | 平均值- 获得所有商品价格的总和：
select sum(price) from product; - 获得所有商品价格的平均值：
select avg(price) from product; - 获得所有商品的个数：
select count(*) from product;
需要注意的是：where后面不能接聚合函数！
由于where后面不能加聚合函数，所以如果想要查询到价格大于平均价格的所有商品时。需要这么写（子查询）:
select * from product where price > (select avg(price) from product);mysql> select * from product where price > (select avg(price) from product); +-----+-------------+-------+---------------------+------+ | pid | pname | price | pdate | cno | +-----+-------------+-------+---------------------+------+ | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | +-----+-------------+-------+---------------------+------+ 2 rows in set (0.00 sec)-
分组： group by
- 根据cno字段去进行分组，统计各个种类商品的个数：
select cno as 商品种类,count(*) as 商品个数 from product group by cno;mysql> select cno as 商品种类,count(*) as 商品个数 from product group by cno; +--------------+--------------+ | 商品种类 | 商品个数 | +--------------+--------------+ | 1 | 2 | | 2 | 1 | | 3 | 2 | | 4 | 1 | | 5 | 2 | +--------------+--------------+ 5 rows in set (0.00 sec)- 根据cno分组，分组统计每组商品的平均价格，并且商品平均价格>60
直接根据cno分组，分组统计每组商品的平均价格可以这样写：
select cno as 商品种类, avg(price) as 平均价格 from product group by cno;mysql> select cno as 商品种类, avg(price) as 平均价格 from product group by cno; +--------------+--------------+ | 商品种类 | 平均价格 | +--------------+--------------+ | 1 | 3744 | | 2 | 99 | | 3 | 61.5 | | 4 | 3 | | 5 | 1 | +--------------+--------------+ 5 rows in set (0.00 sec)还有过滤掉平均价格不大于60的商品，要使用
having关键字：select cno as 商品种类, avg(price) as 平均价格 from product group by cno having avg(price) > 60;mysql> select cno as 商品种类, avg(price) as 平均价格 from product group by cno having avg(price) > 60; +--------------+--------------+ | 商品种类 | 平均价格 | +--------------+--------------+ | 1 | 3744 | | 2 | 99 | | 3 | 61.5 | +--------------+--------------+ 3 rows in set (0.00 sec)having关键字，可以接聚合函数，并且出现在分组之后
where关键字，不可以接聚合函数，并且出现在分组之前
sql语句的编写顺序和执行顺序
- 编写顺序：
select ... from ... where ... group by ... having ... order by ... - 执行顺序：
from ... where ... group by ... having ... select ... order by ...