MySQL数据库基础知识总结（适合小白入门使用）一

文章目录

• 一 数据库数据表的创建等基本操作
• 二 数据类型的测试
• 三 完整性约束条件
• 四 数据表结构的相关操作
• 五 对表中数据的操作
• 六 表达式与查询
• 七 高级的查询功能

---

一 数据库数据表的创建等基本操作

#注释内容（与python很像）
-- 也为注释内容
-- 创建一个数据库
CREATE DATABASE IF NOT EXISTS maizi DEFAULT CHARACTER SET 'utf8';

USE maizi;

SET NAMES GBK;

--创建数据表（user）
--编号id
--用户名usename
--性别sex
--邮箱email
--地址addr
--生日birth
--薪水salary
--电话tel
--是否结婚married
--当需要中文的时候，需要临时转换客户端的编码方式
--SET NAMES GBK;
--字段注释，通过comment注释内容给字段添加注释。

CREATE TABLE IF NOT EXISTS first_table(
id SMALLINT,
usename TINYINT,
age TINYINT,
email VARCHAR(50),
addr VARCHAR(200),
birth YEAR,
salary FLOAT(8,2),
tel INT,
married TINYINT(1) COMMENT '0代表未结婚，非零代表结婚'
)ENGINE=innoDB CHARSET=UTF8;

SHOW TABLES;

--创建一个课程表course
--编号cid
--课程名称 courseName
--课程描述 courseDesc

CREATE TABLE IF NOT EXISTS course(
cid TINYINT,
courseName VARCHAR(50),
courseDesc VARCHAR(200)
);

SHOW TABLES;

--作业
--创建新闻类表
--创建新闻表



--查看表的表结构
DESC tbl_name
DESCRIBE tbl_name
SHOW COLUMNS FROM tbl_name

--测试数据的越界处理
CREATE TABLE test1(
num1 TINYINT,
num2 SMALLINT,
num3 MEDIUMINT,
NUM4 INT,
NUM5 BIGINT
);
--向表中插入记录INSERT tbl_name VALUE|VALUES(值，...);
INSERT test1 VALUES(-128,-32768,0,-2147483648,0);
--查询表中所有记录SELECT * FORM tbl_name;
SELECT *FROM test1;


--测试数据填充
CREATE TABLE test3(
num1 TINYINT ZEROFILL,
num2 SMALLINT ZEROFILL,
num3 MEDIUMINT ZEROFILL,
num4 INT ZEROFILL,
num5 BIGINT ZEROFILL
);
INSERT test3 values(1,1,1,1,1);


--测试浮点类型
--定点数内部以字符串形式保存
CREATE TABLE test4(
num1 FLOAT(6,2),
num2 DOUBLE(6,2),
num3 DECIMAL(6,2)
);
INSERT test4 VALUES(3.1415,3.1415,3.1415);
SELECT * FROM test4;
SELECT * FROM test4 WHERE num2 = 3.14;

二 数据类型的测试

--查看表的表结构
DESC tbl_name;
DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;

SHOW CREATE TABLE tbl_name;

--测试数据的越界处理
CREATE TABLE test1(
num1 TINYINT,
num2 SMALLINT,
num3 MEDIUMINT,
NUM4 INT,
NUM5 BIGINT
);
--向表中插入记录INSERT tbl_name VALUE|VALUES(值，...);
INSERT test1 VALUES(-128,-32768,0,-2147483648,0);
--查询表中所有记录SELECT * FORM tbl_name;
SELECT *FROM test1;


--测试数据填充
CREATE TABLE test3(
num1 TINYINT ZEROFILL,
num2 SMALLINT ZEROFILL,
num3 MEDIUMINT ZEROFILL,
num4 INT ZEROFILL,
num5 BIGINT ZEROFILL
);
INSERT test3 values(1,1,1,1,1);


--测试浮点类型
--定点数内部以字符串形式保存
CREATE TABLE test4(
num1 FLOAT(6,2),
num2 DOUBLE(6,2),
num3 DECIMAL(6,2)
);
INSERT test4 VALUES(3.1415,3.1415,3.1415);
SELECT * FROM test4;
SELECT * FROM test4 WHERE num2 = 3.14;


--定长字符串占用空间一定，空间大，速度快
--变长字符串，空间小，速度慢
--char不保存末尾的空格，varchar保存末尾的空格
--CONCAT(,);字符串连接函数
--测试char varchar
CREATE TABLE IF NOT EXISTS test5(
str1 CHAR(5),
str2 VARCHAR(5)
);
INSERT test5 VALUES('1','1');
INSERT test5 VALUES('12345','12345');
INSERT test5 VALUES('123456','123456');

SELECT CONCAT('-',str1),CONCAT('-',str2) FROM test5;
SELECT LENGTH('A')--字符串长度检测函数
--text，储存超长字符串。不能有默认值



CREATE TABLE IF NOT EXISTS test7(
sex ENUM('nan','nv','baomi')
);
INSERT test7 VALUES('nan')
INSERT test7 VALUES('nv')
INSERT test7 VALUES('baomi')


--测试集合类型
CREATE TABLE IF NOT EXISTS test8(
fav SET('A','B','C','D')
);
INSERT test8 VALUES('A,C,D');
INSERT test8 VALUES('A,D,E');
INSERT test8 VALUES(15);
SELECT * FROM test8;



---年份的测试

CREATE TABLE IF NOT EXISTS test9(
birth YEAR
);
INSERT test9 VALUES(1901);
INSERT test9 values('2000');
INSERT test9 VALUES(0);
INSERT test9 VALUES('0');
SELECT * FROM test9;

--time的测试
--DATE
--TIME
--DATETIME
--TIMESTTAMP
--YREAR

三 完整性约束条件

• 主键约束
• 自增长
• 默认值和非空约束
• 唯一约束

--完整性约束条件
--PRIMARY KEY 主键，不能重复，非空，无意义字段，
--AUTO_IINCREMENT
--FOREIGN KEY
--NOT NULL
--UNIQUE KEY
--DEFAULT

--的是主键

CREATE TABLE IF NOT EXISTS USER1(
id INT PRIMARY KEY,
username VARCHAR(20)
);

--查看创建表的定义
SHOW CREATE TABLE user1;

INSERT user1 VALUES(1,'king')
INSERT user1 VALUES(2,'quee')

CREATE TABLE IF NOT EXISTS user2(
id INT,
username VARCHAR(20),
card CHAR(18),
PRIMARY KEY(id, card)
);

INSERT user2 values(1,'king','111');
INSERT user2 values(1,'queue','112');


--AUTO_INCREMENT,对象一定是主键,在已有的最大主键上+1
--测试自增长
CREATE TABLE IF NOT EXISITS user5(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
);

--指定位置插入值
INSERT user5(username) VALUES('queue');

INSERT user5 VALUES(DEFUALT,'HAHA')

INSERT user5 VALUES(null,'HAHA')

--修改自增长的起始值
ALTER TABLE user6 AUTO_INCREMENT = 500;

--NOT NULL 非空
--测试非空
CREATE TABLE IF NOT EXISTS user7(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32)NOT NULL,
age TINYINT UNSIGNED

);
INSERT user7(username,password) VALUES('KING1','KINGJ',12)

--DEFAULT配合NOT NULL使用
CREATE TABLE IF NOT EXISTS user8(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32)NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT 'BEIJING' 

);

#唯一性约束条件
--UNIQUE KEY唯一
CREATE TABLE IF NOT EXISTS user9(
id TINYINT UNDIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(17) UNIQUE
);
--NULL值不算重复

CREATE TABEL [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [unsigned|zerofill] [default 默认这][not null] [[primary]KEY|UNIQUE[KEY]]
)ENGINE = INNODB CHARSET = UTF8 AUTO_INCREMENT = 100;

四 数据表结构的相关操作

• 修改表名称
• 添加删除字段
• 修改字段和完整性约束条件

CREATE TABLE IF NOT EXISTS user10(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL ,
email VARCHAR(50) NOT NULL DEFAULT '4646546@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('man','woman','secret') DEFAULT 'secret',
addr VARCHAR(200) NOT NULL DEFAULT 'beijing',
salary float(6,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default.jpg'
);

--修改表名
--ALTER TABEL tbl_name RENAME [TO|AS] new_name;
--RENAME TABLE tba_name TO new_name;

ALTER TABLE user10 RENAME TO user11;
ALTER TABLE user11 RENAME AS user10;
ALTER TABLE user10 RENAME user11;

RENAME TABLE user11 TO user10;
RENAME TABLE user10 TO user11;
--挺有意思的，感觉SQL语言有点规则，动作+类型+名称+附属操作


--添加或删除字段
ALTER TABEL tbl_name ADD 字段名称 字段类型[完整性约束条件][FIRST|AFTER 字段名称]

ALTER TABLE tbl_name DROP 字段名称

--添加card字段char类型
ALTER TABLE user10 ADD card CHAR(18);
ALTER TABLE user10 ADD test1 VARCHAR(100) NOT NULL UNIQUE;
ALTER TABLE user10 ADD test2 VARCHAR(100) NOT NULL UNIQUE FIRST;
ALTER TABLE user10 ADD test3 VARCHAR(100) NOT NULL DEFAULT 100 AFTER password;

--选中一次表完成多个操作
ALTER TABLE user10

ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
ADD test5 FLOAT(6,2)FIRST,
ADD test6 SET('A','V','C');

--删除某些字段
ALTER TABLE user10 DROP test1;

--删除多个字段
ALTER TABLE user10

DROP test3,
DROP test4;

--批量处理添加删除操作
ALTER TABLE user10

ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,

DROP addr;



--modify修改完整性约束条件时，不能对默认值和主键进行操作。

ALTER TABLE tbl_name MODIFY 字段名称 字段类型[完整性约束条件][FIRST|AFTER 字段名称];

ALTER TABLE tbl_name CHANGE 旧的字段名称 新的字段名称 字段类型 [完整性约束条件][FIRST|AFTER 字段名称];


--修改email的类型
ALTER TABLE user10 MODIFY email VARCHAR(200);
--修改email的完整性约束条件
ALTER TABLE user10 MODIFY email VARCHAR(20) NOT NULL DEFAULT '1651656@qq.com';
--修改email的位置
ALTER TABLE user10 MODIFY email VARCHAR(20) AFTER test;
--使用change更换字段的相关属性
ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123' FIRST;


--添加和删除默认值
ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值;
ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT;


--添加主键删除主键
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]]PRIMARY KEY[index_type](字段名称)

ALTER TABLE tbl_name DROP PRIMARY KEY;

--添加单主键
ALTER TABLE test10 ADD PRIMARY KEY (id);
--添加复合主键
ALTER TABLE test13 ADD PRIMARY KEY(id, card);
--完整表达形式
ALTER TABLE test12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);

--添加删除唯一索引

ALTER TABLE tbl_name ADD [CONSTRAINT [SYMPOL]] UNIQUE [INDEX|KEY][索引名称](字段名称);

ALTER TABLE tbl_name DROP [INDEX|KEY] index_name;

--示例
ALTER TABLE user12 ADD UNIQUE(usename);

ALTER TABLE user12 ADD CONSTRAINT symple  UNIQUE KEY uni_card(card);

LTER TABLE user12 ADD CONSTRAINT symple  UNIQUE INDEX uni_test1_test2(test1,test2)

ALTER TABLE tbl_name DROP INDEX username;
ALTER TABLE tbl_name DROP KEY mulUni_test1_test2;

--修改表的存储引擎
ALTER TABLE tbl_name ENGINE = 存储引擎名称;
--修改表的引擎为MYISAM
ALTER TABLE test12 ENGINE = MYISAM;

--设置自增长的值
ALTER TABLE tbl_name AUTO_INCREMEN = 值;
--修改主键的自增长值为
ALTER TABLE user12 AUTO_INCREMENT = 100;

--删除数据表
DROP TABLE user12;

DROP TABLE IF EXISTS user11,user12,user14;

--在打开MySQL的同时直接打开数据表
mysql -uroot -p -D maizi --promot = \d~\D~\u~\h
--查看打开的数据库
SELECT DATABASE();

五 对表中数据的操作

• 插入记录
• 更新和删除记录

--插入记录的操作
--不指定具体字段名的插入
INSERT [INTO]tbl_name VALUES|VALUE(...);
--列出制定字段
INSERT [INTO] tbl_name(字段名称,...) VALUES|VALUE(...);
--同时插入多条记录
INSERT [INTO] tbl_name [(字段名称,...)] VALUES|VALUE(...)()(...)...;
--通过SET形式插入记录
INSERT [INTO] tbl_name 字段名称=值
--将查询结果插入到表中
INSERT [INTO]tbl_name[(字段名称,...)]SELECT 字段名称 tbl_name[WHERE 条件]


--插入所有的数据的一条记录
INSERT [INTO]user1 VALUES|VALUE(1,'king',20);
--插入具体字段的值的一条记录
INSERT [INTO] user1(username,password) VALUES|VALUE('a','aaa');
--插入多条记录
INSERT user VALUES(6,'D',45),
(233,'FASF',45),
(54,'AF',84);
--通过set的形式插入记录
INSERT INTO user SET id = 98 ,username ='test',password = 'fjie';
--将查询结果插入到表中,字段数目必须匹配,格式也必须相同。
INSERT test1 SELECT id ,username FROM user1;


--更新和删除记录
--有限制条件的更新记录
UPDATE tbl_name SET 字段名称 = 值,...[WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]
--有限值条件的删除记录
DELETE FROM tbl_name [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]
--清空列鬼所有的记录
TRUNCATE [TABLE]tbl_name

--示例
--表单的年龄更新
UPDATE user1 SET age = 5;
--表单的多个记录更新
UPDATE user1 SET age = 20,email ='123@qq.com';
--表单的多个有限制条件的记录更新
UPDATE user1 SET password = 'king123',email = '123@qq.com',age = 99 WHERE id = 1;
--用表达式表示记录的限制条件和更新的内容
UPDATE user1 SET age = age - 5 WHERE id >= 3;

--删除多个记录,但是不会清空AUTO_increment的值
DELETE FROM user;
--有条件的删除记录
DELETE FROM test1 WHERE id = 1;
--truncate彻底清空数据表,包括所有的配置
TRUNCATE TABLE test1;

六 表达式与查询

• 查询表达式
• 带条件的查询
• 范围查询
• 模糊查询
• 逻辑运算符查询

--SELECT 之后表示要查询的字段
--FROM  之后表示要查询的数据库和表的名称
--WHERE 之后表示要查询的记录的描述比较运算符<>=NULL、范围查询的两个关键字BETWEEN/IN、模糊查询LIKE、多条件查询AND /OR



--查询表达式完整形式
SELECT select_expr [,selext_expr...]
[
FROM table_refereces
[WHERE 条件]
[GROUP BY {col_name|position}[ASC|DESC],...分组]
[HAVING 条件 对分组结果进行二次筛选]
[ORDER BY {col_name|position}[ASC|DESC],...排序]
[LIMIT 限制显示条数]
]

--查询表达式完整形式
--每一个表达式表示想要的一列，必须至少有一列，多个列之间用逗号分隔
--*通配符，表示所有列，tbl_name.*可以表示某个表的所有列


--从特定表中选取特定的不同的字段值进行显示

SELECT * FROM cms_admin;

SELECT cms_admin.* FROM cms_admin;

SELECT id,username FROM cms_admin;

--表来自以哪个数据库中的表。
--从特定的数据库中选取特定的表的特定的字段db_name.tbl_name
SELECT id ,username,role FROM cms.cms_admin;
--字段来源于那张表
SELECT cms_admin.id,cms_admin.username,cms_admin.role FROM cms.cms_admin;
--给表别名来访问会更简单一些  AS可以省略
SELECT a.username,a.id FROM cms_admin AS a;
--给字段起别名
SELECT id AS '编号',username AS '用户名'， role '角色' FROM cms_admin;
--当时别名是，显示的名称是别名
SELECT a.id AS id1,a.username AS u FROM cms_admin;



--带条件的查询，where条件的使用,比较运算符的使用

SELECT  id ,uername,email FROM cms_user WHERE id = 1;
--使用<=>检测值是否为NULL，除此之外，与=用法相同
SELECT *FROM cms_admin WHERE age <=> NULL;
--使用is null或者IS NOT NULL
SELECT *FROM cms_admin WHERE age IS NULL;



--范围查询 BETWEEN NOT BETWEEN IN NOT IN
--查询编号在3-10之间的用户
SELECT *FROM cms_user WHERE id BETWEEN 3 AND 10;
--查询编号为1,3,5,7,9的数
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9)
--查询ProID为1和3的用户
SELECT * FROM cms_user WHERE proID IN(1,3)
--查询用户名为king，queen，张三的记录，忽略英文的大小写
SELECT * FROM cms_user WHERE id IN('king','queen','张三')


--模糊查询LINKE/NOT LINKE 
--%:代表另个一个或多个任意字符
--_:代表一个人任意字符
--查询姓张的用户

SELECT *FROM cms_user WHERE username LIKE '%张%';

--查询用户命中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%in%';

--查询用户名为三位的用户
SELECT * FROM cms_user WHERE username LIKE '____';

--_and%同时使用
SELECT * FROM cms_user WHERE username LIKE '_I%';



--逻辑运算符与查询AND OR
--查询用户名为king，密码也为king
SELECT * FROM cms_user WHERE username = 'king' AND password = 'king';

--查询编号不大于三的变量年龄部位null的用户
SELECT * FROM cms_user WHERE id >= 3 AND age IS NOT NULL;

--查询编号在5~10之间的用户或用户名为4位的
SELECT * FROME cms_user WHERE id BETWEEN 5 AND 10 OR username LIKE '____';

七 高级的查询功能

• 分组查询group by
• 聚合函数avg
• 分组筛选having
• 结果排序order by
• 限制数量limit

--分组查询GROUP BY

--按照用户所属省分进行分组
SELECT * FROM cms_user GROUP BY proId;

--按照字段位置进行分组
SELECT * FROM cms_user GROUP BY 7;

--按照多个字段进行分组
SELECT F FROM cms_user GROUP BY sex,proId;

--先写条件，后对满足条件的记录进行分组
SELECT * FROM cms_user WHERE id > 5 GROUP BY sex;



--分组查询配合聚合函数
--配合GROUP_CONCAT()函数进行使用
--查询id，sex，username的详情 按照性别分组
--通过性别分组，分组后得到username的分组详情
SELECT id, sex, GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

--查询ProID，性别详情，注册时间详情，用户名详情 按照ProID进行分组
SELECT proId ,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime) FROM cms_user GROUP BY proId;

--常见的聚合函数
COUNT()
MAX()
MIN()
AVG()
SUM()
--查询编号，sex，用户名详情以及组中总人数按照sex分组
SELECT id,sex,GROUP_CONCAT(username)AS user ,COUNT(*)AS totalUsers FROM cms_user GROUP BY sex;
--COUNT(字段)不统计NULL值
SELECT COUNT(id) AS totalUsers FROM cms_user;

--查询编号，sex，用户名详情以及组中总人数，组中最大年龄，最小年龄，平均年龄，年龄总和按照sex分组
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user GROUP BY sex;


--配合WITH ROLLUP记录上面所有记录的总和
--在末尾加上WITH ROLLUP 属于聚合统计次字段的总的内容

SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user GROUP BY sex WITH ROLLUP;



--having语句对分组结果进行二次筛选
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;


--ORDER BY 对查询结果进行排序；

--查询按照id降序进行排列DESC /ASC
SELECT * FROM cms_user ORDER BY id ASC;

--按照多个字段进行排序
SELECT *FROM cms_user ORDER BY age ASC ,id DESC;

--实现随机提取记录
ORDER BY RAND();


--通过limit限制显示条数
--LIMIT 显示条数
--LIMIT偏移量，显示条数

--查询表中前三条记录
SELECT * FROM cms_user LIMIT 3;

SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;

SELECT * FROM cms_user LIMTI 10, 5;