Mysql超详解

2023-02-05 08:17:18

Mysql超详解

一、命令框基本操作及连接Mysql

找到Mysql安装路径，查看版本

同时按快捷键win+R会弹出一个框，在框中输入cmd
点击确定后会出现一个黑框，这是命令框，我们的操作要在这命令框中进行。
现在查看你的Mysql服务是否启动，如若未启动，有两种启动方式：
- 第一种：同时按Ctrl+Shift+Esc快捷键，然后按下图中提示的部分操作，最后看到MySQL服务状态未正在运行即可
- 第二种：输入net start mysql80(80是对应的MySQL版本如果是其他版本则输入对应的版本号，如5.7版本输入net start mysql57),然后按回车键，若显示服务已启动，则服务启动成功。（根据电脑情况而定，我的电脑输入net start mysql命令即可），可能在此会出现一个问题，有的电脑在命令框中输入后会出现拒绝访问或服务错误，如果这样的话，就用管理员的身份启动命令框，按下图操作，完成后再输入上面的命令：
MySQL服务启动成功后，在命令框中输入mysql -u root -p,按回车键，输入你的密码，敲回车，登陆成功进入MYSQL。

二、数据库基本操作

查看数据库

 show databases;

操作演示：

创建数据库

 create database 数据库名;

操作演示：

使用数据库

 use 数据库名;

操作演示：

删除数据库

 drop database 数据库名;

三、数据库中表的基本操作

创建表

创建数据表：

 create table 数据表名 (属性1 类型, 属性2 类型,);

类型后边可以加限定条件：primary key(主键)、not null(不能为空)、unique(唯一的)、foreign key(外键)、default(定义初值)、

primary key：主键是一个表的特殊字段，可以唯一标识表的每条信息，主键的目的是为了快速查找表中的某条信息，主键必须是唯一的，主键值是非空的，主键可以是单一字段也可以是多个字段组合。
foreign key(外键)：外键表是一个特殊的字段，设置外键原则，必须依赖于数据库已存在的父表的主键，外键可以为空值，建立改表与其父表的关联关系。foreign key(父表主键属性) references 外键名（同父表相同的属性）
not null：字段不能有空值，保证所有记录该字段都有值
unique：所有记录该字段的值不能重复出现
auto_increment：用于为表插入的新纪录自动生成唯一的id,一个表只能有一个字段使用该约束，必须为主键的一部分，约束字段可以是任何整数类型，默认值从一开始自增
default：创建表时指定该字段的默认值，当插入一条型记录没有为该字段赋值时，系统会自动为该字段插入默认值

删除表

 drop table 数据表名;

修改表

alter table 修改表名，修改字段数据类型，条件约束...等等

1.修改表名：alter table 旧表名 rename 新表名;

2.修改字段的数据类型：alter table 表名 modify 属性名数据类型;

3.修改字段名：alter table 表名 change 旧属性名新属性名新数据类型;

4.增加字段：alter table 表名 add 属性1 数据类型（完整约束）first | after 属性名2; 若不设置位置，默认加到最后的位置

5.删除字段：alter table 表名 drop 属性名;

6.修改字段排列位置：alter table 表名 modify 属性1 数据类型 first | after 属性名2;

7.修改数据引擎：alter table 表名 engine = 储备引擎名;

8.删除表的外键约束：alter table 表名 drop foreign key 外键别名;

9.删除表的主键约束：alter table 表名 drop primary key;

10.删除表：drop table (if exists) 表名;

查询表

查看表结构：desc 数据表名;
查看表的详细定义：show create table 数据表名;

索引

索引是由数据表中一列或多列组合而成，作用提高对表中数据的查询速度。

优点：提高检索数据的速度，对于有依赖关系的父表和子表之间的联合查询，可以提高查询速度使用分组和排序子句进行查询时，节省查询中分组和排序时间。

缺点：创建和维护索引需要浪费时间，耗费时间数量随数据量增加而增加，索引需要占用物理空间，增加，删除，修改数据时，要动态维护索引

索引的创建方式：

create table 表名 (属性名数据类型 [完整约束]，属性名数据类型 [完整约束]，[unique] index 索引名(属性名 [(长度)] [asc | desc])；

1.普通索引:这是最基本的索引，它没有任何限制。

2.唯一性索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式

3.全文索引：全文索引只设置在char、varchar、text类型字段上，查询数据量较大的字符串时可以提高查找效率

4.单列索引：在表中单个字段上创建索引

5.多列索引：在表中多个字段上创建一个索引

6.空间索引

在存在的表上创建索引

create [unique | fulltext | spatial] index 索引名 on 表名 (属性名 [(长度)] [asc | desc]);

alter table 创建索引

alter table 表名 add [unique | fulltext | spatial] index 索引名 (属性名 [(长度)] [asc | desc]);

删除索引

drop index 索引名 on 表名;

alter table 表名 drop index 索引名;

案例演示

  CREATE DATABASE IF NOT EXISTS king DEFAULT CHARACTER SET 'UTF8';
  USE king;
  CREATE TABLE IF NOT EXISTS user(
  id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
  username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
  age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
  email VARCHAR(50) NOT NULL DEFAULT 'abc@qq.com' COMMENT '邮箱'
  )ENGINE=INNODB CHARSET=UTF8;

四、MySQL数据操作

添加记录

 insert [into] 表名[(字段名称,...)] {value|values}(值，...)

不指定字段名称：

 insert 表名 value(值，...)

需要按照建表时的字段顺序给每一个字段赋值

 INSERT user VALUE(1,'张三',21,'110@qq.com');
 INSERT user VALUES(NULL,'李四',22,'lisi@qq.com');
 INSERT user VALUES(DEFAULT,'王麻子',23,'wmz@qq.com');

Mysql超详解

列出指定字段：

 insert 表名(字段名称,...) values (值,...)

 INSERT user(username,email) VALUES('小明','xiaoming@qq.com');

Mysql超详解

一次添加多条记录：

 insert 表名[(字段名称,...)] values (值,...),(值,...),(值,...)

 INSERT user VALUES(NULL,'小花',DEFAULT,DEFAULT),(NULL,'小草',24,'xiaocao@qq.com'),(NULL,'小天',25,'xiaotian@qq.com');

Mysql超详解

INSERT ... SET的形式：

 INSERT 表名 SET 字段名称=值，...;

 INSERT user SET username='诸葛亮',age=26,email='zhugeliang@qq.com';

Mysql超详解

INSERT ... SELECT

 INSERT 表名[(字段名称...)] SELECT 字段名称，...FROM 表名[WHERE 条件]

修改记录

 UPDATE 表名 SET 字段名称=值，字段名称=值[WHERE 条件]

如果不添加条件，整个表中的记录都会被更新

 -- 测试更新语句
 -- 修改第一个用户的信息 id=1
 UPDATE user SET age=29 WHERE id=1;
 
 -- 修改id=3的用户，username age email
 UPDATE user SET age=47,email='lilys@qq.com',username='lilys' WHERE id=3;
 
 -- 所有用户年龄+10
 UPDATE user SET age=age+10;
 
 -- 将id<=5的用户年龄改为-20，将邮箱改为默认值
 UPDATE user SET age=age-20,email=DEFAULT WHERE id<=5;

删除记录

 DELETE FROM 表名[WHERE 条件]

如果不添加条件，表中所有记录都会被删除

 -- 测试删除语句
 -- 删除用户名为lilys
 DELETE FROM user WHERE username='lilys';
 
 -- 删除年龄为36的用户
 DELETE FROM user WHERE age=36;
 
 -- 删除表中所有记录
 DELETE FROM user;

修改AUTO_INCREMENT的值

DELETE清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER语句将其重置为1，详见如下：

清空表后再插入两条记录，发现id是从9开始增长的，

 INSERT user VALUES(NULL,'queen',25,'queen@qq.com');
 INSERT user VALUES(DEFAULT,'lily',26,'lily@qq.com');

先用下面的语句查看自增长值

 SHOW CREATE TABLE user;

再次清空数据表

如何让自增长值从1开始：

 ALTER TABLE user AUTO_INCREMENT=1;

彻底清空数据表

 TRUNCATE[TABLE] 表名;

这有两项功能：

一是清除表中所有记录

二是会重置AUTO_INCREMENT的值

查询记录

 SELECT 字段名称或表达式,... FROM 表名
 [WHERE 条件]
 [GROUP BY {字段名称|位置} HAVING 二次筛选]
 [ORDER BY{字段名称|位置|表达式}[ASC|DESC]]
 [LIMIT 限制结果集的显示条数]

整个语句的含义为：根据 WHERE 子句的条件表达式，从 FROM 子句指定的基本表中找出满足条件的记录，在按照 SELECT 子句中的目标列表达式，选出记录中的属性值形成结果表。

查询表中所有记录
```
 SELECT * FROM 表名；
```
*代表所有字段
指定字段的信息
```
 SELECT 字段名称,...FROM 表名
```

库名.表名

 SELECT 字段名称，... FROM 库名.表名;

给字段起别名

 SELECT 字段名称 [AS] 别名名称,... FROM 库名.表名;

给数据表起别名

 SELECT 字段名称,... FROM 表名[AS] 别名;

表名.字段名

 SELECT 表名.字段名,... FROM 表名;

WHERE 条件

筛选出符合条件的记录
1. 比较运算符
2. IS [NOT] NULL:检测值是否为NULL或者NOT NULL
3. 指定范围：[NOT] BETWEEN v1 AND v2 ： [不] 在 v1 至 v2 之间（包含 v1,v2）
4. 指定集合：[NOT] IN (v1,v2..vn) ： [不] 在范围内
5. 逻辑运算符
6. 匹配字符（模糊查询）:[NOT] LIKE
WHERE常用运算符:
- 比较运算符
  
  【>,< , = （<=>）, != （< >）, >= , <=】
  
  =和（<=>）的区别：(<=>):可以检测NULL值
- 逻辑运算符 NOT ( ! ) 逻辑非 OR( || ) 逻辑或 AND ( && ) 逻辑与
模糊查询 LIKE 像 % （百分号）任意个字符通配符: _ （下划线）任意一个字符
GROUP BY 分组
1. 把值相同的放到一个组中，最终查询出来的结果只会显示组中一条记录。
2. 分组配合GROUP_CONCAT()查看组中某个字段的详细信息
3. 配合聚合函数使用
  - COUNT():统计记录总数
    - 如果写的是COUNT(字段名称),字段中的值为NULL,不统计进来
    - 如果写COUNT(*)会统计NULL值
  - SUM()：求和
  - MAX()：求最大值
  - MIN()：求最小值
  - AVG()：求平均值
4. 配合WITH ROLLUP 关键字使用
  
  会在记录末尾添加一条记录，是上面所有记录的总和
5. HAVING子句对分组结果进行二次筛选
ORDER BY 排序

ORDER BY 字段名称 ASC|DESC
LIMIT 限制结果集显示条数
- LIMIT值：显示结果集的前几条记录
- LIMIT offset,row_count:从offset开始，显示row_count条记录，offset从0开始

查询案例演示

再建一张user1表

 -- 测试查询操作
 CREATE TABLE user1(
 id INT UNSIGNED AUTO_INCREMENT KEY,
 username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
 age TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT '年龄',
 sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
 addr VARCHAR(20) NOT NULL DEFAULT '北京',
 married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚，1代表已婚',
 salary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
 )ENGINE=INNODB CHARSET=UTF8;
 
 
 INSERT user1 VALUES(1,'king',23,'男','北京',1,50000);
 INSERT user1(username,age,sex,addr,married,salary) VALUES('queen',27,'女','上海',0,25000);
 INSERT user1 SET username='imooc',age=31,sex='女',addr='北京',salary=40000;
 INSERT user1 VALUES(NULL,'张三',38,'男','上海',0,15000),
 (NULL,'张三风',38,'男','上海',0,15000),
 (NULL,'张子怡',39,'女','北京',1,85000),
 (NULL,'汪峰',42,'男','深圳',1,95000),
 (NULL,'刘德华',58,'男','广州',0,115000),
 (NULL,'吴亦凡',28,'男','北京',0,75000),
 (NULL,'奶茶妹',18,'女','北京',1,65000),
 (NULL,'刘嘉玲',36,'女','广州',0,15000);

 -- 查询表中所有记录
 SELECT * FROM user1;
 
 -- username,addr,age
 SELECT username,addr,age FROM user1;
 
 -- 查询king数据库下user1表中的所有记录
 SELECT * FROM king.user1;
 
 -- 查询user1表中的id 编号 username 用户名 sex 性别
 SELECT id AS '编号',username AS '用户名', sex AS '性别'
 FROM user1;
 
 SELECT id age,age id,username FROM user1;

 -- 给表起别名
 SELECT id,username FROM user1 AS u;
 
 -- 测试表名.字段名
 SELECT user1.id,user1.username,user1.age FROM user1 ;
 
 SELECT u.id,u.username,u.addr,u.sex FROM user1 AS u;

 -- 测试WHERE 条件的比较运算符
 -- 查询id,username,age id=5的用户
 SELECT id,username,age FROM user1
 WHERE id=5;
 
 SELECT id,username,age FROM user1
 WHERE id=50;

 -- 添加desc字段 VARCHAR(100)
 ALTER TABLE user1
 ADD userDesc VARCHAR(100);
 
 -- 更新id<=9的用户 userDesc='this is a test'
 
 UPDATE user1 SET userDesc='this is a test'
 WHERE id<=9;
 
 -- 查询用户userDesc 为NULL的用户
 SELECT id,username,age,userDesc FROM user1
 WHERE userDesc=NULL;
 
 -- 检测NULL值
 SELECT id,username,age,userDesc FROM user1
 WHERE userDesc<=>NULL;
 
 -- IS [NOT] NULL检测NULL值
 SELECT id,username,age,userDesc FROM user1
 WHERE userDesc IS NULL;

查询NULL值用下面这种方法查询不出来，要用别的方法查询。

 -- 测试范围BETWEEN AND
 -- 查询年龄在18~30之间的用户
 SELECT id,username,age,sex FROM user1
 WHERE age BETWEEN 18 AND 30;
 
 -- 查询薪水在10000~50000之间的用户
 SELECT id,username,age,salary FROM user1
 WHERE salary BETWEEN 10000 AND 50000;
 
 SELECT id,username,age,salary FROM user1
 WHERE salary NOT BETWEEN 10000 AND 50000;

 -- 测试指定集合 IN
 
 -- 查询编号为1,3,5,7,9
 SELECT id,username,age FROM user1
 WHERE id IN(1,3,5,7,9,29,45,78);
 
 SELECT id,username,age FROM user1
 WHERE username IN('king','queen','lily','rose');

 -- 测试逻辑运算符
 -- 查询性别为男并且年龄>=20的用户
 SELECT id,username,age,sex FROM user1
 WHERE sex='男' AND age>=20;
 
 -- id>=5 && age<=30
 SELECT id,username,age,sex FROM user1
 WHERE id>=5 AND age<=30;
 
 SELECT id,username,age,sex FROM user1
 WHERE id>=5 AND age<=30 AND sex='男';
 
 -- 要求sex='女' 并且 addr='北京'
 SELECT id,username,age,sex,addr FROM user1
 WHERE sex='女' AND addr='北京';
 
 -- 查询薪水范围在60000~10000并且性别为男 addr='北京'
 SELECT id,username,age,sex,salary,addr FROM user1
 WHERE salary BETWEEN 60000 AND 100000 AND sex='男' AND addr='北京';
 
 -- 查询id=1 或者 用户名为queen
 SELECT id,username,age FROM user1
 WHERE id=1 OR username='queen';

 -- 测试模糊查询
 SELECT id,username,age FROM user1
 WHERE username='king';
 
 SELECT id,username,age FROM user1
 WHERE username LIKE 'king';
 
 -- 要求用户名中包含三
 SELECT id,username,age,sex FROM user1
 WHERE username LIKE '%三%';
 
 -- 用户名中包含in
 SELECT id,username,age FROM user1
 WHERE username LIKE '%in%';
 
 -- 要求查询出姓张的用户
 SELECT id,username,age FROM user1
 WHERE username LIKE '张%';
 
 -- 查询以风结尾的用户
 SELECT id,username,age FROM user1
 WHERE username LIKE '%风';
 
 
 -- 用户名长度为三位的用户
 SELECT id,username,age,sex FROM user1
 WHERE username LIKE '___';
 
 SELECT id,username,age,sex FROM user1
 WHERE username LIKE '张_';
 
 SELECT id,username,age,sex FROM user1
 WHERE username LIKE '张_%';

 -- 测试分组
 -- 按照性别分组sex
 SELECT id,username,age,sex FROM user1
 GROUP BY sex;
 
 -- 按照addr分组
 SELECT username,age,sex,addr FROM user1
 GROUP BY addr;
 
 -- 按照性别分组，查询组中的用户名有哪些
 SELECT GROUP_CONCAT(username),age,sex,addr FROM user1
 GROUP BY sex;
 
 SELECT GROUP_CONCAT(username),age,sex,GROUP_CONCAT(addr) FROM user1 GROUP BY sex;

 -- 测试COUNT()
 SELECT COUNT(*) FROM user1;
 
 SELECT COUNT(id) FROM user1;

 -- 按照addr分组，得到用户名的详情，总人数，得到组中年龄的总和，年龄的最大值、最小值、平均值和
 SELECT addr,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers,
 SUM(age) AS sum_age,
 MAX(age) AS max_age,
 MIN(age) AS min_age,
 AVG(age) AS avg_age
 FROM user1
 GROUP BY addr;

 -- 按照sex分组，统计组中总人数、用户名详情，得到薪水总和，薪水最大值、最小值、平均值
 SELECT sex,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers,
 SUM(salary) AS sum_salary,
 MAX(salary) AS max_salary,
 MIN(salary) AS min_salary,
 AVG(salary) AS avg_salary
 FROM user1
 GROUP BY sex;

 SELECT GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers
 FROM user1
 GROUP BY sex
 WITH ROLLUP;

 -- 按照字段的位置来分组
 SELECT id,sex,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers,
 SUM(salary) AS sum_salary,
 MAX(salary) AS max_salary,
 MIN(salary) AS min_salary,
 AVG(salary) AS avg_salary
 FROM user1
 GROUP BY 2;

 -- 查询age>=30的用户并且按照sex分组
 SELECT sex,GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers
 FROM user1
 WHERE age>=30
 GROUP BY sex;

 -- 按照addr分组，统计总人数
 SELECT addr,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers
 FROM user1
 GROUP BY addr;

 -- 对于分组结果进行二次筛选，条件是组中总人数>=3
 SELECT addr,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers
 FROM user1
 GROUP BY addr
 HAVING COUNT(*)>=3;

 SELECT addr,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers
 FROM user1
 GROUP BY addr
 HAVING totalUsers>=3;

 -- 按照addr分组，
 SELECT addr,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers,
 SUM(salary) AS sum_salary,
 MAX(salary) AS max_salary,
 MIN(salary) AS min_salary,
 AVG(salary) AS avg_salary
 FROM user1
 GROUP BY addr;
 
 -- 要求平均薪水>=40000
 SELECT addr,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers,
 SUM(salary) AS sum_salary,
 MAX(salary) AS max_salary,
 MIN(salary) AS min_salary,
 AVG(salary) AS avg_salary
 FROM user1
 GROUP BY addr
 HAVING avg_salary>=40000;

 -- 测试排序
 -- 按照id降序排列
 SELECT id,username,age
 FROM user1
 ORDER BY id DESC;
 
 -- 按照age升序
 SELECT id,username,age
 FROM user1
 ORDER BY age ;

 -- 按照多个字段排序
 SELECT id,username,age
 FROM user1
 ORDER BY age ASC,id ASC;
 
 -- 测试条件+排序
 SELECT id,username,age
 FROM user1
 WHERE age>=30;
 
 SELECT id,username,age
 FROM user1
 WHERE age>=30
 ORDER BY age DESC;

 -- 实现随机记录
 SELECT id,username,age
 FROM user1
 ORDER BY RAND();

 -- 测试LIMIT语句
 -- 显示结果集的前5条记录
 SELECT id,username,age,sex
 FROM user1
 LIMIT 5;
 
 SELECT id,username,age,sex
 FROM user1
 LIMIT 0,5;

 -- 显示前3条记录
 SELECT id,username,age,sex
 FROM user1
 LIMIT 0,3;
 
 SELECT id,username,age,sex
 FROM user1
 LIMIT 3,3;

 -- 更新前3条记录，将age+5
 UPDATE user1 SET age=age+5 LIMIT 3;
 
 -- 按照id降序排列，更新前三条记录，将age-10
 UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3;

 -- 删除前三条记录
 
 DELETE FROM user1
 LIMIT 3;
 
 DELETE FROM user1
 ORDER BY id DESC
 LIMIT 3;

 -- 测试完整SELECT 语句的形式
 SELECT addr,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers,
 SUM(age) AS sum_age,
 MAX(age) AS max_age,
 MIN(age) AS min_age,
 AVG(age) AS avg_age
 FROM user1
 WHERE id>=2
 GROUP BY addr;

 SELECT addr,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers,
 SUM(age) AS sum_age,
 MAX(age) AS max_age,
 MIN(age) AS min_age,
 AVG(age) AS avg_age
 FROM user1
 WHERE id>=2
 GROUP BY addr
 HAVING totalUsers>=2;

 SELECT addr,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers,
 SUM(age) AS sum_age,
 MAX(age) AS max_age,
 MIN(age) AS min_age,
 AVG(age) AS avg_age
 FROM user1
 WHERE id>=2
 GROUP BY addr
 HAVING totalUsers>=1
 ORDER BY totalUsers ASC;

 SELECT addr,
 GROUP_CONCAT(username) AS usersDetail,
 COUNT(*) AS totalUsers,
 SUM(age) AS sum_age,
 MAX(age) AS max_age,
 MIN(age) AS min_age,
 AVG(age) AS avg_age
 FROM user1
 WHERE id>=2
 GROUP BY addr
 HAVING totalUsers>=1
 ORDER BY totalUsers ASC
 LIMIT 0,2;

*****************************************************************************************************

我的博客园地址：https://www.cnblogs.com/zyx110/

码农公寓

Mysql超详解

一、命令框基本操作及连接Mysql

二、数据库基本操作

查看数据库

创建数据库

使用数据库

删除数据库

三、数据库中表的基本操作

创建表

删除表

修改表

查询表

索引

案例演示

四、MySQL数据操作

添加记录

修改记录

删除记录

修改AUTO_INCREMENT的值

彻底清空数据表

查询记录

查询案例演示

相关文章