MySQL学习笔记

最新推荐文章于 2024-04-22 11:29:54 发布
最新推荐文章于 2024-04-22 11:29:54 发布
阅读量186 收藏
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Marvin_Wind/article/details/80765503
版权

一、数据库操作（DDL）

1，创建数据库

CREATE DATABASE test;

实际上是在data目录下创建

   

CREATE DATABASE if not exists test;

如果同名是不会创建

2，查看

SHOW DATABASES;                 查看所有数据库

SHOW CREATE DATABASE test;     查看创建方式

3，修改数据库

ALTER DATABASE test character set gbk;

 

4,删除数据库

DROP DATABASE test;                    删库不存在时会报错

DROP DATABASE if exists test1;      不存在不会报错

 

5，使用数据库

USE test;

进入数据库后不会退出，只能切换。

 SELECT DATABASE();      查看当前库

 

二、数据类型

 

1，数值类型

 

2，日期类型

 

3，字符串类型

 

三、数据表操作

1，创建表

CREATE TABLE student(
id int primary key auto_increment,
name varchar(20),
gender bit default 1,
age tinyint

);


约束条件：
       primary key (非空且唯一)  :能够唯一区分出当前记录的字段称为主键！
       unique         唯一
       not null        非空
       auto_increment  自增加 主键字段必须是数字类型。
       外键约束 foreign key  

2，查看表信息

DESC student;   查看表结构

SHOW columns FROM student;   (同上)

SHOW TABLES;        查看当前数据库所有的表。

SHOW CREATE TABLE student；查看建表语句

3，修改表结构

（1）增加列【字段】

ALTER TABLE student ADD aa int first,ADD bb tinyint after  age,ADD cc varchar(20);    插入位置first/after来控制

（2）修改列类型

ALTER TABLE student MODIFY aa varchar(20) after age;

（3）修改列名

ALTER TABLE student CHANGE bb BB int after cc;

（4）删除列

ALTER TABLE student DROP aa,DROP bb,DROP cc;

（5）修改表名

RENAME TABLE student TO student11;

（6）修改字符集

ALTER TABLE student character set gbk;

4，删除表

DROP TABLE employee;

 

5，添加删除主键

删除主键

ALTER TABLE student MODIFY id int;

ALTER TABLE student DROP PRIMARY KEY;

创建主键

ALTER TABLE student ADD PRIMARY KEY pk_id(id);                  主键名（字段）

ALTER TABLE student MODIFY id int auto_increment;              添加主键自增

 

                    

四、表记录操作

1，增加记录

增加一条记录

INSERT INTO student (id,name,gender,age) VALUES (11,'BOB',1,15);

INSERT INTO student VALUES (15,'LUCY',0,16);

INSERT INTO student (name,age) VALUES ('LILY',16);

INSERT INTO student SET name='GREEN',age=19;

增加多条记录

INSERT INTO student (name,gender,age) VALUES ('ESON',0,17),('WINDY',0,18);

2,修改表记录

UPDATE student SET age = 17 where id=19;

3,删除表记录

DELETE FROM student where id=19;

TRUNCATE TABLE student;          删除表，再创建同名空表

 

五、单表查询

格式：

SELECT *|field1,filed2 ...   FROM tab_name
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

所有内容

SELECT * FROM student;

查看某列

SELECT name FROM student;

查看某行某列

SELECT name FROM student where id=15 ;       

去重

SELECT DISTINCT age FROM student;

别名

SELECT name as 姓名 FROM student;

过滤

SELECT * FROM student WHERE age >16 AND age<18;

SELECT * FROM student WHERE age between 16 and 18;         范围是[16,18]

SELECT * FROM student WHERE age in (17,18);

SELECT * FROM student WHERE  name like'L%';                       所有名字为L开头的信息

SELECT * FROM student WHERE  name is null;                         名字为空

排序

SELECT * FROM student ORDER BY age DESC;                         降序，默认asc升序

SELECT * FROM student ORDER BY age ASC;  

 

group by

创建表单

CREATE TABLE order_menu(
             id INT PRIMARY KEY auto_increment,
             product_name VARCHAR (20),
             price FLOAT(6,2),
             born_date DATE,
             class VARCHAR (20)

                                );

插入数据

INSERT INTO order_menu (product_name,price,born_date,class) VALUES
                                             ("苹果",20,20170612,"水果"),
                                             ("香蕉",80,20170602,"水果"),
                                             ("水壶",120,20170612,"电器"),
                                             ("被罩",70,20170612,"床上用品"),
                                             ("音响",420,20170612,"电器"),
                                             ("床单",55,20170612,"床上用品"),
                                             ("草莓",34,20170612,"水果");

 

 

 

SELECT * FROM order_menu group by 5;                   按字段位置查询

SELECT class,SUM(price) FROM order_menu group by class;        组内运算

SELECT class,SUM(price) FROM order_menu group by class HAVING SUM(price);    筛选

 

                  PS:having 和 where两者都可以对查询结果进行进一步的过滤，差别有：    

                 <1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；        

                 <2>使用where语句的地方都可以用having进行替换      

                 <3>having中可以用聚合函数，where中就不行。

         

聚合函数

（1）count：统计行数

SELECT COUNT(*) FROM student;                               统计所有行数

SELECT COUNT(age) FROM student WHERE age>16;   统计大于16岁的行数

studen增加字段：

    

ALTER TABLE student ADD math tinyint ,ADD eng tinyint ,ADD cns tinyint; 

    UPDATE student SET math = 99,eng = 89,cns=90 where id=18;


    UPDATE student SET math = 79,eng = 89,cns=92 where id=17;

    UPDATE student SET math = 92,eng = 99,cns=95 where id=16;

    UPDATE student SET math = 97,eng = 88,cns=91 where id=15;

    UPDATE student SET math = 99,eng = 99,cns=99 where id=11;

 

 

 

 

SELECT COUNT(name) FROM student where (ifnull(math,0) + ifnull(eng,0) + ifnull(cns,0)) > 270; 统计总成绩大于270

SELECT SUM(math) AS math总成绩,

            SUM(eng) AS eng总成绩, 

            SUM(cns) AS cns总成绩 FROM student;            各科总成绩总成绩

 

SELECT SUM(math)/COUNT(*) AS math平均分 FROM student;       平均分

 

AVG求平均:

SELECT AVG(IFNULL(math,0) + IFNULL(eng,0) + IFNULL(cns,0)) AS 总分平均 FROM student;

 

 MAX、MIN:

SELECT MAX(math) AS math最高分,MIN(IFNULL(math,0)) AS math最低分 FROM student;       math最高分、最低分

 

 -- Mysql在执行sql语句时的执行顺序：from  where  select group by  having order by

 

LIMIT:

SELECT * FROM student LIMIT 1;     显示一条

 

 

SELECT * FROM student LIMIT 2,2;    跳过2条显示两条

 

 

REGEXP:正则表达

SELECT * FROM student where name REGEXP '^L';

 

六、外键约束

主表：

创建班主任表

 CREATE TABLE classteacher(

                        id TINYINT PRIMARY KEY AUTO_INCREMENT,

                        name varchar(20),

                        age TINYINT,

                        gender bit);


INSERT INTO classteacher (name,age,gender) VALUES  ('李老师',33,0),

                                                   ('王老师',24,1),

                                                   ('宋老师',55,0);

子表：

 

CREATE TABLE student1(

       id INT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       charger_id TINYINT,     
       FOREIGN KEY (charger_id) REFERENCES classteacher(id)

) ENGINE=INNODB;
--切记:作为外键一定要和关联主键的数据类型保持一致
-- 
ALTER TABLE student1ADD CONSTRAINT charger_fk_stu   增加约束条件，关联外键，双方数据类型必须一致
ALTER TABLE student1 DROP FOREIGN KEY charger_fk_stu;

 

因为外键约束的原因而导致插入失败也会影响到主键自增

 

INSERT INTO student1(name,charger_id) VALUES ("aaa",2),
                                            ('bbb',1),

                                            ('ccc',3),

                                            ('ddd',1),

                                            ('eee',3),

                                            ('fff',3);

 

 

 

级联删除

创建表时在外键后加上ON DELETE CASCADE;

ON DELETE SET NULL:主表删除后，关联字段设为空值；

Restrict方式 :拒绝对父表进行删除更新操作(了解)

No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作（了解）

 

 

七、多表查询

创建两张表

create table employee(
      emp_id int auto_increment primary key not null,
      emp_name varchar(50),
      age int,
      dept_id int
      );

insert into employee(emp_name,age,dept_id) values
        ('A',19,200),
        ('B',26,201),
        ('C',30,201),
        ('D',24,202),
        ('E',20,200),
        ('F',38,204);


create table department(
       dept_id int,
       dept_name varchar(100)
      );

insert into department values
      (200,'人事部'),
      (201,'技术部'),
      (202,'销售部'),
      (203,'财务部');

1，笛卡尔积查询

SELECT * FROM employee,department;

 

+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | A        |   19 |     200 |     200 | 人事部    |
|      1 | A        |   19 |     200 |     201 | 技术部    |
|      1 | A        |   19 |     200 |     202 | 销售部    |
|      1 | A        |   19 |     200 |     203 | 财务部    |
|      2 | B        |   26 |     201 |     200 | 人事部    |
|      2 | B        |   26 |     201 |     201 | 技术部    |
|      2 | B        |   26 |     201 |     202 | 销售部    |
|      2 | B        |   26 |     201 |     203 | 财务部    |
|      3 | C        |   30 |     201 |     200 | 人事部    |
|      3 | C        |   30 |     201 |     201 | 技术部    |
|      3 | C        |   30 |     201 |     202 | 销售部    |
|      3 | C        |   30 |     201 |     203 | 财务部    |
|      4 | D        |   24 |     202 |     200 | 人事部    |
|      4 | D        |   24 |     202 |     201 | 技术部    |
|      4 | D        |   24 |     202 |     202 | 销售部    |
|      4 | D        |   24 |     202 |     203 | 财务部    |
|      5 | E        |   20 |     200 |     200 | 人事部    |
|      5 | E        |   20 |     200 |     201 | 技术部    |
|      5 | E        |   20 |     200 |     202 | 销售部    |
|      5 | E        |   20 |     200 |     203 | 财务部    |
|      6 | F        |   38 |     204 |     200 | 人事部    |
|      6 | F        |   38 |     204 |     201 | 技术部    |
|      6 | F        |   38 |     204 |     202 | 销售部    |
|      6 | F        |   38 |     204 |     203 | 财务部    |
+--------+----------+------+---------+---------+-----------+
24 rows in set (0.00 sec)

总行数为两表行数之积

 

2，内连接查询

SELECT * FROM employee,department where employee.dept_id = department.dept_id;

SELECT * FROM employee INNER JOIN department on employee.dept_id = department.dept_id;

3,外连接

左连接

SELECT * FROM employee LEFT JOIN department on employee.dept_id = department.dept_id;

SELECT * FROM department LEFT JOIN employee  on employee.dept_id = department.dept_id;

如图，左连接会完整展示左表，剩余部分以null补全

SELECT * FROM employee RIGHT JOIN department on employee.dept_id = department.dept_id;

SELECT * FROM department RIGHT JOIN employee  on employee.dept_id = department.dept_id;

 

全连接full join ：mysql语法不支持

SELECT * FROM employee FULL JOIN department on employee.dept_id = department.dept_id;

SELECT * FROM employee LEFT JOIN department on employee.dept_id = department.dept_id 

UNION SELECT * FROM employee RIGHT JOIN department on employee.dept_id = department.dept_id;

 

5，子表查询



子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符：= 、 !=、> 、<等



（1）带IN关键字的子查询


   查询employee表，但dept_id必须在department表中出现过


   select * from employee
            where dept_id IN

            (select dept_id from department);

 

（2） 带比较运算符的子查询
      =、!=、>、>=、<、<=、<>


     查询员工年龄大于等于25岁的部门
     select dept_id,dept_name from department
           where dept_id IN
          (select DISTINCT dept_id from employee where age>=25);


（3） 带EXISTS关键字的子查询


EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。Ture或False
当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询


     select * from employee
              WHERE EXISTS
              (SELECT dept_name from department where dept_id=203);


      --department表中存在dept_id=203，Ture




     select * from employee
                WHERE EXISTS
              (SELECT dept_name from department where dept_id=205);


     -- Empty set (0.00 sec)




    复制表:  create table t1(select * from t2);

 

八、索引

普通索引：

CREATE TABLE table1(

              id INT,name VARCHAR(20),

              index index_name (name));

               或者用key代替index

唯一索引

UNIQUE INDEX index_a (a) ，不命名自动以字段名命名

全文索引

FULLTEXT INDEX index_a (a)       字段a

多列索引

INDEX index_a_b (a,b)                字段a,字段b

delimiter  设置分隔符

 

 

 

 

Marvin_Kai
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
mysql学习笔记
04-20
个人学习mysql的一些笔记，包含一些基本的操作以及讲解，希望可以帮到一些初学者
Mysql学习笔记（全）
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
尚硅谷MySQL学习笔记
IAMLSL的博客
02-11 1万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
mysql 学习笔记
javaTalk
01-23 3412
mysql 学习记录，包含日志、事务、索引、锁等功能。
Mysql学习笔记
huangbaokang的博客
07-22 1075
查看mysql默认的存储引擎 show variable like 'storage_engine%'; 查看表的详情 show create table table_name \G mysql修改表名 alter table old_table_name rename [to] new_table_name mysql修改字段名字 alter table table_name change...
MySQL学习笔记1：数据库概述（尚硅谷）
qq_43101414的博客
02-22 3185
MySQL学习系列1
MYSQL学习笔记-SQL
Hhg66hh的博客
01-11 453
数据库mysql学习笔记
m0_53853047的博客
09-18 1764
数据库mysql学习笔记
MySQL学习笔记整理（上部）
热门推荐
霁晨晨晨的博客
05-01 3万+
以下是我整理了关于MySQL数据库的常用命令和使用场景，一些基本的概念，比如索引、约束和事务，以及常见的问题和解决方案，欢迎大家浏览并留言，若有错误的地方请大家指正。
MySQL学习笔记5——函数和索引
qq_44778824的博客
04-22 2255
- 删除索引DROP INDEX 索引名 ON 表名；--删除主键索引ALTER TABLE 表名 DROP PRIMARY KEY;索引的成本存储空间的开销，是指索引|需要单独占用存储空间。数据操作上的开销，是指一旦数据表有变动，无论是插入一条新数据，还是删除一条旧的数据，甚至是修改数据，如果涉及索引字段，都需要对索引本身进行修改，以确保索弓|能够指向正确的记录。因此，索引也不是越多越好，创建索引有存储开销和操作开销，需要综合考虑。
MySQL学习笔记.zip
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
MySql学习笔记（详细）
05-18
4、Mysql函数 5、事务和索引 6、权限设计 7、JDBC 能学到什么：详细了解MYSQL基础以及使用 阅读建议： 建议配合狂神教学视频使用：...
MySQL学习笔记 MySQL学习笔记
09-28
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用...学习MySQL不仅涉及理论知识，还需要大量的实践操作来熟悉其语法和功能。掌握MySQL能让你在数据管理、Web开发和系统集成等领域具备更强的竞争力。
MYSQL学习笔记
02-28
数据库：就是在服务器中安装一个可以管理数据的软件，这个文件称为“DBMS”...Mysql：甲骨文公司提供一个免费的中小型数据库 Sql_server：微软提供的一个收费的大型数据库 Oracle：甲骨文公司提供一个收费的大型数据库
MySQL学习笔记666
最新发布
06-05
mysql学习笔记
MySQL学习笔记：从基础到高级
"一千行+MySQL+学习笔记.pdf" MySQL是一种广泛应用的关系型数据库管理系统（RDBMS），它以其高效、稳定和易用性受到广大开发者的青睐。这份学习笔记涵盖了MySQL的多个关键知识点，包括基本的SQL语句、索引、数据...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值