MySQL数据库

最新推荐文章于 2024-08-19 13:32:03 发布

啊呜冷

最新推荐文章于 2024-08-19 13:32:03 发布

阅读量119

点赞数

分类专栏： Java 文章标签：数据库 mysql 服务器 Powered by 金山文档

本文链接：https://blog.csdn.net/Free_Pocket/article/details/129018772

版权

Java 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

知识

安装MySQL数据库，就是在主机安装一个数据库管理系统，其可以管理多个数据库

一个数据库中可以创建多个表，以保存信息或数据

数据库管理系统（DBMS）中有若干数据库，数据库中有若干表

#创建数据库
CREATE DATABASE db01;
#删除数据库
DROP DATABASE xxx;
#创建一个使用utf8字符集的数据库,并带校对规则,校对规则utf8_bin区分大小写，默认的utf8_general不区分大小写
CREATE DATABASE lzh_db02 CHARACTER SET utf8 COLLATE utf8_bin;
#查询，* 表示所有字段
SELECT * FROM `01` WHERE id = 1
#备份，要在Dos下执行mysqldump指令其实在mysql安装目录\bin
#备份文件就是对应的sql语句
-- mysqldump -u root -p -B db01 > e:\\bak.sql
#恢复数据库（进入mysql命令后再执行）
-- source e:\\bak.sql

创建表

CREATE TABLE `user`
(
    id INT,
    `name` VARCHAR(255),
    `password` VARCHAR(32),
    `birthday` DATE
)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

CREATE TABLE `user` LIKE emp;

创建一张跟emp的结构一样的表

列类型

char(4) 是定长，varchar(4) 是变长，按照实际的占用空间来分配长度，但varchar本身还需要1~3个字节来记录存放内容长度

timestamp时间戳在insert和update时，会自动更新

CREATE TABLE s3 (
    id INT,
    `name` VARCHAR(32),
    sex VARCHAR(5),
    birthday DATE,
    entry_time DATE,
    job VARCHAR(32),
    Salary DOUBLE,
    resume TEXT);
    
    INSERT INTO s3 VALUES(
        100, 
        'Tom', 
        'man', 
        '2000-1-1', 
        '2018-1-1 00:00:00', 
        'fisher', 
        2999, 
        'hello world');

修改表

#修改表
ALTER TABLE s4 
    ADD image VARCHAR(32) NOT NULL DEFAULT ''
    AFTER resume 
    DESC s4  -- 显示表结构，查看表中的所有列
#修改job列使其长度为60
ALTER TABLE s4 
    MODIFY job VARCHAR(60) NOT NULL DEFAULT ''
#删除sex列
ALTER TABLE s4 
    DROP sex 
#修改表名为s5
RENAME TABLE s4 TO s5 
#修改表的字符集为utf8
ALTER TABLE s5 CHARACTER SET utf8 
#将列名name改为username
ALTER TABLE s5 
    CHANGE `name` username VARCHAR(64) NOT NULL DEFAULT''

insert 语句

CREATE TABLE s5 (
    id INT,
    goodname VARCHAR(10),
    price DOUBLE);
    
INSERT INTO s5 (id, goodname, price)
    VALUES(10, '华为', 2000);

插入的数据类型应该与字段的数据类型相同

数据的长度应在列的规定范围内

在values中列出的数据位置必须与被加入的列的排列位置相对应

字符和日期型数据应包含在单引号中

列可以插入空值（允许为空的情况下），语句为“insert into table value (null)”

insert into tab_name（列名）value () ，() ，() 形式添加多条记录

如果是给表中所有字段添加数据，可以不写前面的字段名称

默认值得使用，当不给某个字段值时，如果有默认值就会添加，否则报错，如果某个列没有指定not null，那么当添加数据时，没有给它添加数据会添加默认值null

update语句

#全部改动
UPDATE s4 SET salary = 5000
#指定改动
UPDATE s4 SET salary = 3000 WHERE username = 'milk'
#在原有基础上改动
UPDATE s4 SET salary = salary + 1000 WHERE username = 'milk'

update语句用于更新表中的值

set子句指示要修改哪些列和要给予哪些值

where子句指定应更新哪些行，如果没有where子句会更新所有行

如需修改多个字段，可以通过set字段1 = 值1，字段2 = 值2 ...

delete语句

#删除指定内容
DELETE FROM s4 WHERE id = 1;
#删除所有内容
DELETE FROM s4;

删除不能删除某一列的值（可使用update将其设为null或 ' '）

如果不使用where语句会删除所有内容，慎用

select语句

#查询表中所有的信息
SELECT * FROM s4
#查询表中指定的信息
SELECT `name` FROM s4
#过滤表中重复的数据
SELECT DISTINCT id FROM s4

要查询的记录当每个字段都相同时才会去重

*表示查询所有列

from表示查询那张表

distinct表示显示结果时是否去掉重复数据

#统计每个学生的总分
SELECT `name`,(chinese + math + english) FROM s1;
#使用新列名表示学生分数
SELECT `name`, (chinese + math + english) AS total FROM s1;
#查询语文成绩大于60的
SELECT * FROM s1 WHERE (chinese > 60);
#查询姓张的同学
SELECT * FROM s1 WHERE `name` LIKE '张%'
#查询指定分数的人
SELECT * FROM s1 WHERE math IN (60, 90);

order by

#以数学成绩排序
SELECT * FROM s1 ORDER BY math;

order by指定排序的列，既可以是表中的列名，也可以是select语句后指定的列名，其中Asc升序（默认），Desc降序，它应位于select语句的结尾

sum

#统计语文成绩总和
SELECT SUM(chinese) FROM s1;
#统计语数英三门成绩总和
SELECT SUM(chinese + math + english) FROM s1;
#统计语文，数学，英语的总成绩
SELECT SUM(chinese), SUM(math), SUM(english) FROM s1;
#统计语文平均分
SELECT SUM(chinese) / COUNT(*) FROM s1;

统计函数

avg

#求语文平均分
SELECT AVG(chinese) FROM s1;

max，min

#找出最高分和最低分
SELECT MAX(chinese + math + english), MIN(chinese + math + english) FROM s1;

分组统计

SELECT AVG(chinese), chinese FROM s1 GROUP BY chinese HAVING chinese < 80;

字符串函数

数学函数

#绝对值
SELECT ABS(-10) FROM DUAL;
#十进制转二进制
SELECT BIN(10) FROM DUAL;
#向上取整
SELECT CEILING(-1.1) FROM DUAL;
#向下取整
SELECT FLOOR(1.1) FROM DUAL;
#进制转换，下面是将10进制的8转换为2进制输出
SELECT CONV(8,10,2) FROM DUAL;
#保留小数位数
SELECT FORMAT( 3.1415926,2) FROM DUAL;
#求最小值
SELECT LEAST(0,1,-1);
#求余
SELECT MOD(11,5);
#产生随机浮点数,范围为[0,1]
-- RAND(seed) 如果seed不变，随机数也不变
SELECT RAND(2) FROM DUAL;

日期函数

加密函数

流程控制函数

查询函数

多表查询

SELECT * FROM s1, s2;

默认情况下，从第一张表中取出一行和第二张表中的每一行进行组合，返回结果【含有两张表的所有列】

一共返回的记录数为第一张表行数 * 第二张表行数

这样多表查询默认的处理返回结果称为笛卡尔集

解决多表查询的关键就是写出正确的过滤条件where，需要在实际问题中进行分析

对查询进行过滤

SELECT `name`,scale 
    FROM s1, s2
    WHERE s1.id = s2.id

多表查询的条件不能少于表的个数 - 1，否则会出现笛卡尔集（将被查询的每张表的行进行组合返回表1行数 * 表2行数 * 表3行数... * 表n行数）

自连接

SELECT a.ename AS a, b.ename AS b 
    FROM s1 a,s1 b 
    WHERE a.id = b.id;
    
SELECT * FROM s1;

多子句查询

单子句查询：只返回一行数据的子查询语句

SELECT salary FROM s1 WHERE ename = Alice;

SELECT * FROM s1 WHERE salary = (
    SELECT salary FROM s1 WHERE ename = Alice;
    )

因为SELECT salary FROM s1 WHERE ename = Alice;的返回结果只有一行程其为单子句查询

多子句查询：返回多行数据的子查询，使用关键字in

SELECT DISTINCT job FROM s1 WHERE deptno = 1;

SELECT ename, job, deptno FROM s1 WHERE deptno IN (
    SELECT DISTINCT job FROM s1 WHERE deptno = 1;
    )

在mysql中判断两个值是否相等时，用‘=’，不想等时用‘！=’或‘<>’

临时表查询

SELECT cat_id, MAX(shop_price) FROM goods GROUP BY cat_id;

SELECT good_id, goods.cat_id, goodname, shopprice
    FROM (
        SELECT cat_id, MAX(shop_price) AS maxprice
        FROM goods GROUP BY cat_id;
        )temp, goods 
        WHERE temp.cat_id = goods.cat_id 
        AND temp.maxprice = goods.shopprice;

将子查询当作临时表使用在实际工作中应用非常多，能够解决很多复杂的查询

all和any

all是符合所有条件，any是符合其中一个条件就行，相当于数学中的与和或

例如，查询比这里所有人的薪水都高的用all（相当于max），查询比其中一个人薪水高的用any（相当于min）

多列子查询

查询返回多个列数据的子查询语句

SELECT deptno, job FROM s1 WHERE ename = 'Alice'

SELECT * FROM s1 WHERE (deptno, job) = (
    SELECT deptno, job FROM s1 WHERE ename = 'Alice')
    AND ename != 'Alice'

表名.* 表示将表中所有字段显示出来

合并查询

union all 将查询结果合并，不进行去重

SELECT id, ename FROM s1 WHERE sal > 2500;
UNION ALL;
SELECT id, ename FROM s2 WHERE job = 'worker';

union 将查询结果合并并进行去重

SELECT id, ename FROM s1 WHERE sal > 2500;
UNION;
SELECT id, ename FROM s2 WHERE job = 'worker';

表复制

CREATE TABLE s7 (
    id INT,
    `name` VARCHAR(32),
    sal DOUBLE,
    job VARCHAR(32),
    deptno INT);
    
INSERT INTO s7 (id, `name`, sal, job, deptno) SELECT id, ename, sal, job, deptno FROM emp;

INSERT INTO s7 SELECT * FROM s7;

有时，为了对某个sql语句进行效率测试需要海量数据时可以使用自我复制的方法创建海量数据

表去重（在进行效率测试结束时，要对表进行去重）

CREATE TABLE temp LIKE s1;
INSERT INTO temp SELECT DISTINCT * FROM s1;
DROP TABLE s1;
RENAME TABLE temp TO s1;

外连接

左外连接：左侧的表完全显示，即左边的表和右边的表没有完全匹配也会把左边的表的记录显示出来

右外连接：右侧的表完全显示，即右边的表和左边的表没有完全匹配也会把右边的表的记录显示出来

例如，表1有id、name，表2有id、grade，当使用左外连接时，需要显示id，name，grade时，即使左边的表对应的id没有成绩时，也会把他的id显示出来，他的grade会显示为null

主键（primary key）

主键不能重复也不能为空

一张表中最多只能有一个主键，但可以是复合主键（primary key （id， name），id和name同时相同才算违反主键）

unique：当定义了唯一约束后，该列值是不能重复的

CREATE TABLE s1 (id INT UNIQUE);

外键（foreign key）

用于定义主表和从表之间的关系，外键约束要定义在从表上，主表必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或者是null

CHECK

列名类型 check（check条件）

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不满足会报错

oracle和sql server均支持check，但mysql5.7目前还不支持check，只能做语法校验

自增长

字段名整型 primary key auto_increment

在某张表中，存在一个id，在添加的时候使其自动增长

insert into xxx（字段1 字段2 ......）values（null，值）；

inser into xxx（字段2......）values（值1，值2...）；

inser into xxx values（null，值1，......）

索引

适用于较频繁的作为查询条件的字段

select * from 表名 where 列名 = 1

唯一性太差的字段不适合创建索引，即使它经常被查询

更新频繁的字段不适合创建索引

不会出现在where子句中的字段不该创建索引

create index 索引名 on 列名

查询表中的索引

show index from 表名

创建索引会占用内存，会使查询速度大幅加快

创建索引后，只对创建索引的列有效

drop index 索引名 on 表名

删除主键索引

alter table 表名 drop primary key

事物

事物用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败

当执行事物操作时，mysql会在表上加锁，防止其他用户改表的数据

一些bug

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01 WHERE NAME = tom' at line 8

错误原句：SELECT * FROM 01 WHERE NAME = tom

原因：在使用Navicat时，要使用反引号将01括起来（中文状态：· ，英文状态：`）

1366 - Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'name' at row 1

原因：数据库或表的字符集不为utf8，排序规则不为utf8_general_ci的情况下出现中文，会出现编码错误

改正：把中文全部转换为英文或把字符集和排序规则改过来

啊呜冷

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL数据库

安装MySQL数据库，就是在主机安装一个数据库管理系统，其可以管理多个数据库一个数据库中可以创建多个表，以保存信息或数据数据库管理系统（DBMS）中有若干数据库，数据库中有若干表创建表创建一张跟emp的结构一样的表char(4) 是定长，varchar(4) 是变长，按照实际的占用空间来分配长度，但varchar本身还需要1~3个字节来记录存放内容长度timestamp时间戳在insert和update时，会自动更新修改表insert 语句插入的数据类型应该与字段的数据类型相同数据的长度应在列的规定范围内在
复制链接

扫一扫