mysql基础

极梦网络无忧

于 2021-07-29 06:55:50 发布

阅读量544

点赞数

分类专栏： MySQL学习记录

本文链接：https://blog.csdn.net/wangzhae/article/details/119201085

版权

MySQL学习记录专栏收录该内容

20 篇文章 2 订阅

订阅专栏

创建

数据库

CREATE DATABASE 数据库名;

表

CREATE TABLE 表名(
     列名称1 数据类型,
     列名称2 数据类型
);

例子：

CREATE TABLE account 
(
    id serial PRIMARY KEY ,
    name VARCHAR NOT NULL ,
    gender CHAR NOT NULL DEFAULT '0', -- 0 表示未知性别  1 表示女性 2 表示男性
    mobile CHAR(11),
    is_return BOOLEAN NOT NULL DEFAULT False, 
    date_created TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

常用数据类型:

1. 数值类型

名字	描述	范围
smallint	小范围整数	-32768 到 +32767
integer	常用的整数	-2147483648 到 +2147483647
bigint	大范围的整数	-9223372036854775808 到 9223372036854775807
decimal/numeric	用户定义精度，可以精确地表示小数	无限制
real	精度可变，不能精确地表示小数	精度是6个十进制位
double precision	精度可变，不能精确地表示小数	精度是15个十进制位
serial	小范围的自增整数	大范围的自增整数
bigserial	大范围的自增整数	1 到 9223372036854775807

1.整数类型

类型smallint、integer和bigint存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用smallint。而只有在integer的范围不够的时候才使用bigint，因为前者(integer)绝对快得多。

2.任意精度数值
类型numeric可以存储最多1000位精度的数字并且准确地进行计算。因此非常适合用于货币金额和其它要求计算准确的数量。不过，numeric类型上的算术运算比整数类型或者浮点数类型要慢很多。

3.浮点数类型
数据类型real和double是不准确的、牺牲精度的数字类型。不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储后再把数据打印出来可能显示一些缺失。

4.自增整数
serial和bigserial类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。

2. 字符类型

名字	描述
varchar(n)	变长，有长度限制
char(n)	定长,不足补空白
text	变长，无长度限制

SQL 定义了两种基本的字符类型，varchar(n)和char(n)，这里的n是一个正整数。两种类型都可以存储最多n个字符长的字串，试图存储更长的字串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字串将被截断为最大长度。如果没有长度声明，char等于char(1)，而varchar则可以接受任何长度的字串。

3. 日期/时间类型

名字	描述
timestamp[无时区]	包括日期和时间
timestamp[含时区]	日期和时间，带时区
interval	时间间隔
date	只用于日期
time[无时区]	只用于一日内时间

4. 布尔类型

boolean数据类型。boolean只能有两个状态之一：真(True)或假(False)。

CREATE TABLE date_type 
(
    -- 数字类型
    id serial PRIMARY key,
    col_int INTEGER DEFAULT 0,
    col_float DECIMAL DEFAULT 0.0,
    -- 字符类型
    col_char CHAR(10),
    col_varchar VARCHAR(10),
    col_text text, 
    -- 日期类型
    col_date DATE DEFAULT CURRENT_DATE ,
    col_time TIME DEFAULT CURRENT_TIME ,
    col_timestamp TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP ,
    col_timestamp_with_timezone TIMESTAMP(0) WITH TIME zone DEFAULT CURRENT_TIMESTAMP ,
    -- bool类型
    col_bool BOOLEAN DEFAULT FALSE 
);

增加字段（一列）
ALTER TABLE 表名 ADD COLUMN 列名数据类型;
删除字段（一列）
ALTER TABLE 表名 DROP COLUMN 列名;
更改字段（列）的数据类型
ALTER TABLE 表名 ALTER COLUMN 列名 TYPE 数据类型;
给列添加|删除default
ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 7.77
表的重命名
ALTER TABLE 表名 RENAME TO 新表名;
更改字段名
ALTER TABLE 表名 RENAME 列名 TO 新列名;
更改约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (列名(可以多个));
ALTER TABLE 表名 ALTER COLUMN 列名 SET NOT NULL

 
-- 增加
ALTER TABLE account add COLUMN test CHAR(11) ;

ALTER TABLE account add COLUMN test1 INTEGER ;

-- 删除
ALTER TABLE account DROP COLUMN test;

-- 重命名
ALTER TABLE account rename COLUMN test1 TO department ;

-- 修改字段类型
ALTER TABLE account ALTER COLUMN department type VARCHAR;

-- 给字段增加非空约束
ALTER TABLE account_score add CONSTRAINT account_score_account_id UNIQUE (account_id);

NOT NULL 约束（非空约束）
默认情况下，列可以保存NULL值。如果不希望一列具有NULL值，那么需要在此列指定NULL约束定义。 NOT NULL约束总是写成一列约束。
NULL并不相同，因为没有数据，而是它代表着未知的数据。
UNIQUE 约束（唯一约束）
唯一约束防止两个记录在一个特定的列具有相同的值。可以为一个，也可以为多个
PRIMARY KEY 约束（主键约束）
数据库表中的PRIMARY KEY约束唯一标识每条记录。可以有多个UNIQUE的列，但在一个表中只有一个主键。在设计数据库表的主键是重要的。主键是唯一的ID。
FOREIGN KEY 约束（外键约束）
外键约束指定一列（或一组列）中的值的值必须符合另一个表中出现的一些行。我们说这是维持两个相关的表之间的引用完整性。它们被称为外键，因为约束是外部的，也就是说表的外部。有时也被称为外键引用的关键。
其他约束
CHECK 约束（检查约束）
EXCLUSION 约束（扩展约束）
删除约束

 
-- 主键约束，唯一约束
CREATE TABLE study_group 
(
    id serial PRIMARY KEY ,
    name CHAR(30),
    summary text,
    leader_id INTEGER UNIQUE 
);

-- 多个字段组合成唯一约束
CREATE TABLE follow 
(
    follower_id INTEGER NOT NULL , 
    following_id INTEGER NOT NULL , 
    date_created TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    CONSTRAINT uk_follow_follower_id_following_id UNIQUE (follower_id,following_id)
);

-- 创建外键约束
CREATE TABLE account_score (
    account_id INTEGER NOT NULL ,
    score serial ,
    FOREIGN KEY (account_id) REFERENCES account(id)
);

-- 增加外键约束
ALTER TABLE account add  FOREIGN KEY (group_id) REFERENCES study_group;

插入

按照列的顺序插入：

INSERT INTO 表名称 VALUES (值1, 值2,....)

我们也可以指定所要插入数据的列：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

-- 按顺序插入
INSERT INTO follow VALUES (1,3);
-- 指定字段插入一组数据
INSERT INTO account (name, mobile) VALUES ('haha', '12345677890');
-- 指定字段插入多组数据
INSERT INTO account (name, mobile) VALUES ('haha1', '12345677890'),
('haha2', '123234325');

注意：

自增的字段，不要给赋值
默认值如果不需要赋值的话，不需要给赋值
如果赋值，需要注意字段类型和格式

修改

修改全部值（!!!慎用）

UPDATE 表名称 SET 列名称 = 新值

修改某个条件下的值

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

`UPDATE account SET name = '1345' WHERE id = 1`

删除

删除全部的行（!!!慎用）

DELETE FROM 表名称

删除某一个条件下的行

DELETE FROM 表名称 WHERE 列名称 = 值

`DELETE FROM account WHERE id = 2;`

查询

语法

1. 全部查询

SELECT * FROM 表名

2. 单列查询

SELECT 列名 FROM 表名

3. 多列查询

SELECT 列名1, 列名2 FROM 表名

4. 带条件查询

SELECT * FROM 表名 WHERE 列1=值1 AND 列2>值2 OR 列3 NOT IN (值1, 值2) AND 列4 LIKE 'abc%' ;

5. 分组排序汇总查询

以列1分组，并汇总、排序
SELECT 列1, SUM(列2) FROM 表名 WHERE 列3 ＝值 GROUP BY 列1 HAVING SUM(列2) > 10 ORDER BY SUM(列2) DESC;

6. 限制条目查询

获取第20条开始的10条数据
SELECT * FROM 表名 ORDER BY 列1 LIMIT 10 OFFSET 20;
唯一不同值
SELECT DISTINCT 列名称 FROM 表名称

数据汇总

sum
avg
max
min
sum

条件查询

where{and or in like}

分组

group by
having

排序

order by desc

-- 按照id 倒叙排列
select * from account order by id desc

限制条目

limit offset

-- 按照id正序排列 从第11个位置开始 限制10个条目
selece * from account order by id limit 10 offset 10

多表联查

传统链接

SELECT * FROM 表名1 T1,表名2 T2 WHERE T1.column=T2.column;

SELECT follower.name, following.name, follow.date_created 
FROM follow, account AS follower,account AS following  
WHERE follower.id =follow.follower_id AND following.id=follow.following_id;

连表查询

Union 联合查询
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
INNER JOIN（内连接）
(INNER) JOIN（内连接），也成为自然连接，根据两个或多个表中的列之间的关系，从这些表中查询数据。
外连接
与内连接相比，即使没有匹配行，也会返回一个表的全集。
1）LEFT OUTER JOIN，简称LEFT JOIN，左外连接（左连接）
2）RIGHT JOIN(right outer join)右外连接(右连接)
CROSS JOIN（交叉连接）
交叉连接。交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。

-- UNION
SELECT id,name FROM account
UNION
SELECT id,name FROM study_group;

-- 内链接
SELECT account.id, account.name, account.group_id, study_group.name FROM account INNER JOIN study_group ON account.group_id = study_group.id;

-- 左外链接
SELECT account.id, account.name, account.group_id, study_group.name FROM account LEFT JOIN study_group ON account.group_id = study_group.id;

-- 右外连接
SELECT account.id, account.name, account.group_id, study_group.name FROM account RIGHT JOIN study_group ON account.group_id = study_group.id;

-- 交叉连接
SELECT account.id, account.name, study_group.name FROM account CROSS JOIN study_group;

子查询

`SELECT * FROM account WHERE id IN (SELECT follow.follower_id FROM follow);`

丢弃

丢弃表

drop table 表名;

`DROP TABLE test;`

丢弃数据库

DROP DATABASE 数据库名;