kingcwt
Appearance

MySQL

kingcwt2023-07-16前端MySQL

MySQL

  • 整理一下基础命令

连接数据库

mysql -u root -p

查看当前有哪些数据库

show databases;

选择你要操作的数据库

use beego;

查看当前数据库下有哪些表

show tables;

查看当前表下所有数据

select * from users;

指定字段查找数据

select id,username from users;

指定字段且加入筛选条件查找数据

select id,username from users where id=1;

创建一个数据库

create database books;

创建一个表

create table persons(uid int(11),username varchar(255),age int(3), sex int(1))

查看当前表有哪些字段

desc persons;

表里面添加字段

ALTER TABLE persons ADD title varchar(255);

给表添加数据

insert into persons (id,username,age,sex) values (1,'chr',27,1);

修改指定数据

update persons set sex=2 where id=2;
// 找到id等于2的这一条数据 修改sex的值为2；

update persons set username='cc',age=32 where id=1;
// 修改多个字段

删除指定数据

delete from persons where id=3;

按指定的数据排序

  • asc 升序；desc 降序;
select * from persons order by id asc;

select * from persons order by age asc,sex desc;
//指定age升序 sex倒序 [age相同的情况下按照sex倒序排列]

统计数量

select count(1) from persons; // 1表示第一列

Limit

select * from persons limit 2; //分页查看前两条数据
select * from persons limit 2,2; //跳过两条数据查询两条；

删除表

drop table test;

删除数据库

drop database test;

MySQL字段的常用数据类型

  • 整数型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT 占用字节数[1,2,3,4,8] ,最大长度[4,6,8,11,20] 长度中第一位是符号
  • 浮点数：FLOAT,DOUBLE,DECIMAL(M,D)
  • 字符型：CHAR,VARCHAR【varchar是动态的,占用字节数和你保存的字符长度有关,char是固定的长度】
  • 备注型：TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT 占用的字节数[255,65535,16777215,4294967295] 【一个汉字占用3个字节】

MySQL运算符

MySQL运算符含义
=等于
<小于
>大于
≤小于或等于
≥大于或等于
≠不等于
IS NOT NULL具有一个值
IS NULL没有值
BETWEEN在范围内
NOT BTWEEN不在范围内
IN指定的范围
OR两个条件语句之一为真
AND两个条件语句都为真
NOT条件语句不为真
LIKE搜索列中的指定模式

MySQL分组函数

函数用法描述
AVG()AVG(column)返回列的平均值
COUNT()COUNT(column)统计行数
MAX()MAX(column)求列中最大值
MIN()MIN(column)求列中最小值
SUM()SUM(column)求列中的和
select sum(score) from class; //求分数的总和
select * from class where score in(select max(score) from class); // 查找分数最高的一条数据

MySQL别名

select score as s from class;

MySQL笛卡尔积连接,内连接,左外连接,右外连接

  • 查询数据的时候能不用连接语句尽量不用,笛卡尔积连接查询速度最慢,项目中用的比较多的是内连接

笛卡尔连接

- 查找文章的时候显示文章的分类 一对多的关系 一个分类下有多个文章
- 分类表article_cate 文章表article
SELECT article.id as id,article.title as title,article.state as state,article_cate.title as cate FROM article,article_cate WHERE article.cate_id=article_cate.id
内连接 
mysql> select article.id as id,article.title as title,article.state as state,article.cate_id as cateId,article_cate.title as uidTitle from article INNER JOIN article_cate ON article.cate_id=article_cate.id;
  • 关于多对多的关系
  • 1张是学生表,1张是选修课程表,还有一张中间表
  • 学生表
mysql> select * from student;
+------+--------+-------------+---------+--------------+
| id   | number | password    | calssId | name         |
+------+--------+-------------+---------+--------------+
|    1 |   1001 | pass123     |       1 | 张三         |
|    2 |   1002 | secret456   |       1 | 李四         |
|    3 |   1003 | password789 |       2 | 王武         |
| NULL |   1004 | secure321   |       2 | Sarah Lee    |
| NULL |   1005 | qwerty123   |       3 | Alex Brown   |
| NULL |   1006 | abcdef456   |       3 | Emily Davis  |
| NULL |   1007 | 123456      |       4 | James Wilson |
| NULL |   1008 | password    |       4 | Lisa Chen    |
| NULL |   1009 | test123     |       5 | Kevin Wang   |
| NULL |   1010 | passpass    |       5 | Anna Taylor  |
+------+--------+-------------+---------+--------------+
  • 选修课程表
mysql> select * from lesson;
+------+------------------------+
| id   | name                   |
+------+------------------------+
|    1 | 计算机网络             |
|    2 | java程序设计           |
|    3 | javascript程序设计     |
+------+------------------------+
3 rows in set (0.00 sec)
  • 中间表
mysql> select * from lesson_student;
+-----------+------------+
| lesson_id | student_id |
+-----------+------------+
|         1 |          1 |
|         2 |          2 |
|         3 |          3 |
|         2 |          1 |
+-----------+------------+
4 rows in set (0.00 sec)

条件是查询张三选修了哪些课程

  • 1 查询张三选修的课程id
SELECT lesson_id from lesson_student where student_id=1;

//
mysql> SELECT lesson_id from lesson_student where student_id=1;
+-----------+
| lesson_id |
+-----------+
|         1 |
|         2 |
+-----------+
2 rows in set (0.00 sec)
  • 2 查询出课程id对应的课程
SELECT * from lesson where id in (SELECT lesson_id from lesson_student where student_id=1);

//
mysql> SELECT * from lesson where id in (SELECT lesson_id from lesson_student where student_id=1);
+------+------------------+
| id   | name             |
+------+------------------+
|    1 | 计算机网络       |
|    2 | java程序设计     |
+------+------------------+
2 rows in set (0.00 sec)

第二种方法内连接

mysql> select lesson.id as id,lesson.name as name from lesson INNER JOIN lesson_student ON lesson.id=lesson_student.lesson_id AND lesson_student.student_id=1;
+------+------------------+
| id   | name             |
+------+------------------+
|    1 | 计算机网络       |
|    2 | java程序设计     |
+------+------------------+
2 rows in set (0.00 sec)

查询java程序设计被哪些学生选修了

  • 基础查询
mysql> select student_id from lesson_student where lesson_id=2;
+------------+
| student_id |
+------------+
|          2 |
|          1 |
+------------+
2 rows in set (0.00 sec)

mysql> select * from student where id in (select student_id from lesson_student where lesson_id=2);
+------+--------+-----------+---------+--------+
| id   | number | password  | calssId | name   |
+------+--------+-----------+---------+--------+
|    1 |   1001 | pass123   |       1 | 张三   |
|    2 |   1002 | secret456 |       1 | 李四   |
+------+--------+-----------+---------+--------+
2 rows in set (0.00 sec)
  • 内连接查询
mysql> select student.id as id,student.name as name from student INNER JOIN lesson_student ON student_id=student.id AND lesson_id=2;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+
2 rows in set (0.01 sec)
  • 将id字段类型更改为varchar(36)
ALTER TABLE bgm MODIFY COLUMN id varchar(36);
  • 修改字段名称
ALTER TABLE employees
CHANGE COLUMN old_column_name new_column_name 数据类型;
  • 随机生成一条数据并插入到bgm表
INSERT INTO bgm (id, username)
VALUES (UUID(), CONCAT('user_', FLOOR(RAND() * 1000000)));
  • 创建一个自增id和username字符串类型
CREATE TABLE give (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(11)
);
  • 复制插入数据
insert into give(username) select username from give;
// 会插入当前give已经有的username的数据
  • 查看索引
show index from give;
  • 格式化索引信息展示
show index from give\G;

mysql> show index from give\G;
*************************** 1. row ***************************
        Table: give // 表示查询的结果是关于 give 表的索引信息
   Non_unique: 0  // 这是索引的属性之一，它表示索引是否允许重复值。在这里，值为0表示这是一个唯一索引，即索引中的值都是唯一的
     Key_name: PRIMARY  // 这是索引的名称，PRIMARY 表示这是主键索引，也就是表的主键
 Seq_in_index: 1  // 表示该字段是主键索引的第一个字段。在复合主键索引中，可能会有多个字段。
  Column_name: id //* 表示该索引是基于 id 字段创建的，也就是主键索引是基于 id 字段的。
    Collation: A // 这是排序规则的标识符，A 表示采用默认排序规则
  Cardinality: 25499400 // 表示该索引中唯一值的数量，这里是 25499400
     Sub_part: NULL // 这是表示索引的子部分，对于主键索引，通常为 NULL。
       Packed: NULL // 这是索引的压缩信息，对于主键索引，通常为 NULL。
         Null: // 表示索引字段是否允许为空，这里没有具体的信息，因为主键字段通常不允许为空。
   Index_type: BTREE // 表示索引的类型，BTREE 表示是一个B-tree索引，这是MySQL默认的索引类型。
      Comment: // 这是索引的注释，对于主键索引，通常为空
Index_comment: // 这是索引的注释，对于主键索引，通常为空
      Visible: YES // 表示该索引是否可见，对于主键索引，通常为 YES
   Expression: NULL // 这是一个表达式索引的信息，对于主键索引，通常为 NULL。
1 row in set (0.01 sec)
  • 设置索引
create index index_username on give(username);
  • 删除索引
drop index index_username on give;

设置索引前
mysql> select * from give where username='chr1';
+----------+----------+
| id       | username |
+----------+----------+
|    10000 | chr1     |
|   991231 | chr1     |
|  1843186 | chr1     |
|  2662386 | chr1     |
|  3547096 | chr1     |
|  4366296 | chr1     |
|  5185496 | chr1     |
|  6004696 | chr1     |
|  6889381 | chr1     |
|  7708581 | chr1     |
|  8527781 | chr1     |
|  9346981 | chr1     |
| 10166181 | chr1     |
| 10985381 | chr1     |
| 11804581 | chr1     |
| 12623781 | chr1     |
| 13508416 | chr1     |
| 14327616 | chr1     |
| 15146816 | chr1     |
| 15966016 | chr1     |
| 16785216 | chr1     |
| 17604416 | chr1     |
| 18423616 | chr1     |
| 19242816 | chr1     |
| 20062016 | chr1     |
| 20881216 | chr1     |
| 21700416 | chr1     |
| 22519616 | chr1     |
| 23338816 | chr1     |
| 24158016 | chr1     |
| 24977216 | chr1     |
| 25796416 | chr1     |
+----------+----------+
32 rows in set (2.82 sec)

设置索引后

mysql> select * from give where username='chr1';
+----------+----------+
| id       | username |
+----------+----------+
|    10000 | chr1     |
|   991231 | chr1     |
|  1843186 | chr1     |
|  2662386 | chr1     |
|  3547096 | chr1     |
|  4366296 | chr1     |
|  5185496 | chr1     |
|  6004696 | chr1     |
|  6889381 | chr1     |
|  7708581 | chr1     |
|  8527781 | chr1     |
|  9346981 | chr1     |
| 10166181 | chr1     |
| 10985381 | chr1     |
| 11804581 | chr1     |
| 12623781 | chr1     |
| 13508416 | chr1     |
| 14327616 | chr1     |
| 15146816 | chr1     |
| 15966016 | chr1     |
| 16785216 | chr1     |
| 17604416 | chr1     |
| 18423616 | chr1     |
| 19242816 | chr1     |
| 20062016 | chr1     |
| 20881216 | chr1     |
| 21700416 | chr1     |
| 22519616 | chr1     |
| 23338816 | chr1     |
| 24158016 | chr1     |
| 24977216 | chr1     |
| 25796416 | chr1     |
+----------+----------+
32 rows in set (0.00 sec)
  • 查看查询详情
设置索引后 只查询了一条
mysql> desc select * from give where username='chr1';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | give  | NULL       | ref  | index_username | index_username | 36      | const |   32 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • 另一种设置索引的方式
alter table give add index index_username(username);
  • 另一种删除索引的方式
alter table give drop index index_username;
  • 创建唯一索引
create unique index index_id on give(id);

关于索引 上面创建的都是普通索引,unique创建的是主索引也叫唯一索引 不能重复

MySQL事务

事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行

  • begin 开启事务
  • commit 提交事务
  • rollback 回滚操作
mysql> select * from user;
+----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | 张三     |     100 |
|  2 | 李四     |    1000 |
|  3 | 王武     |   10000 |
+----+----------+---------+
3 rows in set (0.00 sec)

// 张三给李四转了100块

begin; //开启事务 
update user set balance=balance-100 where id=1;
update user set balance=balance+100 where id=2;
commit; //提交事务

// todo: 以上保证数据的完整性 只有commit执行之后 才会同步数据库 否则begin开始后的操作都是在内存中

---
rollback; //如果在begin;执行后 操作中遇到错误 执行rollback;会回滚数据 保证数据一致性

MySQL锁

  • 添加锁

可以并发读,但是不能并发写,读锁期间,没释放锁之前不能进行操作

使用场景: 读取结果集的最新版本,同时防止其他事务产生更新该结果集,主要用在需要数据依存关系时确认某行记录是否存在,并确保没有人对这个记录进行update或则delete操作

lock table user read; // 添加读锁
unlock tables; // 释放
  • 添加写锁

当并发去操作一条数据时 只有当前用户可以去读取和操作 其他人不能操作

lock table user write; //添加写锁
unlock tables; // 释放
Last Updated 10/16/2023, 7:06:22 AM
What do you think?
  • 0
  • 0
  • 0
  • 0
  • 0
  • 0
0  Words
Comments
  • Latest
  • Oldest
  • Hottest
Powered by Waline v2.15.8
ON THIS PAGE