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
--课程描述 courseDescCREATE 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 '[e-mail]',
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 user10ADD 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 user10DROP test3,
DROP test4;--批量处理添加删除操作
ALTER TABLE user10ADD 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 '[e-mail]';
--修改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 ='[e-mail]';
--表单的多个有限制条件的记录更新
UPDATE user1 SET password = 'king123',email = '[e-mail]',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;