mysql 基础整合大全
mysql 基础整合大全
mysql 数据库操作：
创建数据库：
create database db_sanguo charset utf8;
切进db_sanguo
use db_sanguo
删除数据库：
drop database db_sanguo;
数据表的增删：
建表
create table t_hero(
id int unsigned auto_increment primary key,
name varchar(10) unique not null,
age tinyint unsigned default 0,
gender set("男", "女"),
state varchar(10)
);
查看表结构 desc t_hero;
或：
MySQL的数据类型
MySQL的数据类型 分为四大类型： 整数和浮点数类型、时间和日期类型、字符串类型、二进制类型。
一、整数与浮点数类型
1. 整数类型：
| 类型名 | 字节 | 无符号(unsigned)取值范围 | 有符号(signed)取值范围(默认) |
|---|---|---|---|
| TINYINT | 1 | 0~255 | -128~127 |
| SMALLINT | 2 | 0~65535 | -32768 ~ 32767 |
| MEDIUMINT | 3 | 0~16777215 | -8388608 ~ 8388607 |
| INT | 4 | 0~4294967295 | -2147483648 ~ 2147483647 |
| INTEGER | 4 | 0~4294967295 | -2147483648 ~ 2147483647 |
| BIGINT | 8 | 0~18446744073709551615 | -9223372036854775808 ~ 9223372036854775807 |
2. 浮点数类型和精准数据类型：
| 类型名 | 字节 | 无符号(unsigned)取值范围 | 有符号(signed)取值范围(默认) |
|---|---|---|---|
| FLOAT | 4 | 0, 1.175494351E-38 ~3.402823466E+38 | -3.402823466E+38 ~1.175494351E-38, 0, 1.175494351E-38 ~ 3.402823466E+38 |
| DOUBLE | 8 | 2.2250738585072014E-308 ~ 1.7976931348623157E-308 | -1.7976931348623157E+308 ~ 2.2250738585072014E-308, 0, 2.2250738585072014E-308 ~ 1.7976931348623157E+308 |
| DECIMAL(M,D) | M+2 | 同 Double | 同 Double |
-
注意：DECIMAL(M, D) 精准数据类型：
M表示：数据的有效总长度(不包括小数点)；D表示：小数位精度，按四舍五入计算如果使用时没有指定参数，则默认参数为 (10, 0)
二、日期和时间类型
| 类型名 | 字节 | 取值范围 | 零值 |
|---|---|---|---|
| YEAR | 1 | 1910~2155 | 0000 |
| DATE | 4 | 1000-01-01~9999-12-31 | 0000:00:00 |
| TIME | 3 | -838:59:59~838:59:59 | 00:00:00 |
| DATETIME | 8 | 1000-01-01 00:00:00 ~9999-12-31 23:59:59 | 0000-00-00 00:00:00 |
| TIMESTAMP | 4 | 19700101080001~20380119111407 | 00000000000000 |
三、字符串类型
| 类型名 | 说明 |
|---|---|
| CHAR | 固定长度字符串 |
| VARCHAR | 可变长度字符串 |
| TEXT | 大文本（TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT） |
| ENUM | 枚举类型（只能取一个元素） |
| SET | 集合类型（能取多个元素） |
四、二进制类型
| 类型名 | 说明 |
|---|---|
| BINARY(M) | 字节数为 M，允许长度为 0~M 的定长二进制字符串 |
| VARBINARY(M) | 允许长度为 0~M 的变长二进制字符串，字节数为值的长度加 1 |
| BIT(M) | M 位二进制数据，最多 255 个字节 |
| TINYBLOB | 可变长二进制数据，最多 255 个字节 |
| BLOB | 可变长二进制数据，最多（2 16 -1）个字节 |
| MEDIUMBLOB | 可变长二进制数据，最多（2 24 -1）个字节 |
| LONGBLOB | 可变长二进制数据，最多（2 32 -1）个字节 |
总结：
- MySQL支持所有标准SQL数值数据类型
- 可以通过查看帮助文档查阅所有支持的数据类型
- 使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间
- 特别说明的类型如下：
- decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位
- 字符串：char,varchar
- char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '
- varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'
- 字符串text表示存储大本，当字符大于4000时推荐使用
- 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径
- mysql支持枚举类型enum，但是enum的本质是tinyint类型，所以推荐使用tinyint类型而不用enum类型
约束
约束是在表中定义的用于维护数据库完整性的一些规则，通过为表中的列定义约束可以防止将错误的数据插入表中，也可以保持表之间数据的一致性。
| 约束条件 | 说明 |
|---|---|
| NOT NULL | 标识该属性不能为空 |
| UNIQUE | 标识该属性的值是唯一的 |
| AUTO_INCREMENT | 标识该属性的值自动增加 |
| DEFAULT | 为该属性设置默认值，当不填写此值时会使用默认值，如果填写则以填写为准 |
PRIMARY KEY |
标识该属性为该表的主键，能确定一条记录的唯一标识（满足第二范式） |
FOREIGN KEY |
标识该属性为该表的外键，与某表的主键关联。如果一个表中的某一列是另外一个表的中的主键，那么设置这列为外键。 |
数据表数据插入，
View Code
数据表结构的增删改查
增：
语法： alter table 表名 add 字段名 类型 约束；
alter table t_hero add address varchar(10);
更改字段名称：
语法： alter table 表名 change 旧字段 新字段 类型 约束;
alter table t_hero change address adds varchar(10);
修改类型或约束：
语法： alter table 表名 modify 字段 类型 约束;
alter table t_hero modify adds int;
删除字段：
语法： alter table 表名 drop 字段;
alter table t_hero drop adds;
删除数据表：
语法：delete from 表名
数据的增删改查，
增：
全列插入：值的顺序与表中字段的顺序对应（说明：主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准）
语法：insert into 表名 values(...)
insert into students values(0,'郭靖',1,'蒙古','2015-1-2');
部分列插入：值的顺序与给出的列顺序对应语法：insert into 表名(列1,...) values(值1,...)insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2015-3-2');
注：上面的语句一次可以向表中插入一行数据，还可以一次性插入多行数据，这样可以减少与数据库的通信全列多行插入：值的顺序与给出的列顺序对应语法：insert into 表名 values(...),(...)...;insert into classes values(0,'python'),(0,'linux'),(0,'mysql'),(0,'js');
还可以：
语法：insert into 表名(列1,...) values(值1,...),(值1,...)...;insert into students(name) values('杨康'),('杨过'),('小龙女');
改:
语法：update 表名 set 列1=值1,列2=值2... where 条件
update t_hero set name='孙悟空' where id=14;
注：没有where条件 则代表修改全部
删：
语法：delete from 表名 where 条件
delete from t_hero where id=14;
注：没有where条件 则代表删除全部
另外一种删除全部数据的方法，TRUNCATE
truncate table 你的表名
注：这样不但将数据全部删除，而且重新定位自增的字段查：
在进行后面的操作之前我们先建立一个新的数据表student2，如下：
CREATE TABLE student2( id INT(3) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, grade FLOAT, gender CHAR(2));
View Code
1，查询指定的字段
语法：SELECT 字段名1，字段名2，… FROM 表名；
2，查询所有的字段
语法：SELECT * FROM 表名；
3，按条件查询
1，带关系运算符的查询
语法：SELECT 字段名1，字段名2，…
FROM 表名
WHERE 条件表达式
在WHERE子句中可以使用如下关系运算符：
| 关系运算符 | 说 明 |
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
举例：查询student2表中id为4的人的id和name字段
,
2，带 IN 关键字的查询
N关键字用于判断某个字段的值是否在指定集合中，若在，则该字段所在的记录将会被查询出来.
语法：SELECT * | 字段名1，字段名2，…
FROM 表名
WHERE 字段名 [ NOT ] IN （元素1，元素2，…）
举例：查询student2表中id值为1,2,3的记录
注意：NOT IN 与 IN 相反，查询的是不在指定范围内的记录
3，带 BETWEEN AND 关键字的查询
BETWEEN AND 用于判断某个字段的值是否在指定范围之内，若在，则该字段所在的记录会被查询出来，反之不会。
语法：SELECT * | { 字段名1，字段名2，… }
FROM 表名
WHERE 字段名 [ NOT ] BETWEEN 值1 AND 值2；
举例：查询student2表中id值在2~5之间的人的id和name
注意：NOT BETWEEN AND 表示查询指定范围外的记录。
4，空值查询
在数据表中有些值可能为空值（NULL），空值不同于0，也不同于空字符串，需要使用 IS NULL 来判断字段的值是否为空值。
语法：SELECT * | 字段名1，字段名2，…
FROM 表名
WHERE 字段名 IS [ NOT ] NULL
举例：查询student2表中gender值为空值的记录。
注意：IS NOT NULL 关键字用来查询字段不为空值的记录。
5，带 DISTINCT 关键字的查询
很多表中某些字段的数据存在重复的值，可以使用DISTINCT关键字来过滤重复的值，只保留一个值。
语法：SELECT DISTINCT 字段名 FROM 表名；
举例：查询student2表中gender字段的值，结果中不允许出行重复的值。
6,带link关键字查询
语法：SELECT * | 字段名1，字段名2，…
FROM 表名
WHERE 字段名 [ NOT ] LIKE ‘匹配字符串’;
1）百分号（%）通配符
匹配任意长度的字符串，包括空字符串。例如，字符串“ c% ”匹配以字符 c 开始，任意长度的字符串，如“ ct ”，“ cut ”，“ current ”等；字符串“ c%g ”表示以字符 c 开始，以 g 结尾的字符串；字符串“ %y% ”表示包含字符“ y ”的字符串，无论“ y ”在字符串的什么位置。
举例1：查询student2表中name字段以字符“ s ”开头的人的id,name
2）下划线（_）通配符
下划线通配符只匹配单个字符，若要匹配多个字符，需要使用多个下划线通配符。例如，字符串“ cu_ ”匹配以字符串“ cu ”开始，长度为3的字符，如“ cut ”，“ cup ”；字符串“ c__l”匹配在“ c ”和“ l ”之间包含两个字符的字符串，如“ cool ”。需要注意的是，连续的“_”之间不能有空格，例如“M_ _QL”只能匹配“My SQL”，不能匹配“MySQL”。
举例：查询在student2表中name字段值以“ wu ”开始，以“ ong ”结束，并且中间只有一个字符的记录。
7，带 AND 关键字的多条件查询
在使用SELECT语句查询数据时，优势为了使查询结果更加精确，可以使用多个查询条件，如使用 AND 关键字可以连接两个或多个查询条件。
语法：SELECT * | 字段名1，字段名2，…
FROM 表名
WHERE 条件表达式1 AND 条件表达式2 [ … AND 条件表达式 n ];
举例：查询student2表中 id 字段小于5，并且 gender 字段值为“ 女 ”的人的id和name
8，带 OR 关键字的多条件查询
与 AND 关键字不同，OR 关键字只要满足任意一个条件就会被查询出来
语法：SELECT * | 字段名1，字段名2，…
FROM 表名
WHERE 条件表达式1 OR 条件表达式2 [ … OR 条件表达式 n ];
举例：查询student2表中 id 字段小于3，或者 gender 字段值为“ 女 ”的人的id，name和gender
9 OR 和 AND 一起使用的情况
OR 和 AND 一起使用的时候，AND 的优先级高于 OR，因此二者一起使用时，会先运算 AND 两边的表达式，再运算 OR 两边的表达式。
举例：查询student2表中gender值为“女”或者gender值为“男”并且grade字段值为100的人的记录
3 高级查询
1，聚合查询
| 函数名称 | 作用 |
| COUNT() | 返回某列的行数 |
| SUM() | 返回某列值的和 |
| AVG() | 返回某列的平均值 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
1) COUNT()函数：统计记录的条数
语法：SELECT COUNT(*) FROM 表名
举例：查询student2表中一共有多少条记录
2) SUM()函数：求出表中某个字段所有值的总和
语法：SELECT SUM(字段名) FROM 表名；
举例：求出student2表中grade字段的总和
......其它几个函数用法一样，在这里我就不一一列举了
2，排序查询
语法：SELECT 字段名1，字段名2，…
FROM 表名
ORDER BY 字段名1 [ ASC | DESC ],字段名2 [ ASC | DESC ]…
在该语法中指定的字段名是对查询结果进行排序的依据，ASC表示升序排列，DESC 表示降序排列，默认情况是升序排列。
举例1：查出student2表中的所有记录，并按照grade字段进行升序排序
或者倒序
2，分组查询
在对表中数据进行统计的时候，可以使用GROUP BY 按某个字段或者多个字段进行分组，字段中值相同的为一组，如男生分为一组，女生分为一组。
语法：SELECT 字段名1，字段名2，…
FROM 表名
GROUP BY 字段名1，字段名2，… [ HAVING 条件表达式 ];
1） 单独使用 GROUP BY 进行分组
单独使用GROUP BY 关键字，查询的是每个分组中的一条记录
举例：查询student2表中的数据，按照gender字段进行分组。
2） GROUP BY 和聚合函数一起使用
GROUP BY 和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等。
举例：将student2表按照gender字段进行分组查询，计算出每组共有多少个人。
3）GROUP BY 和 HAVING 关键字一起使用
HAVING关键字和WHERE关键字的作用相同，区别在于HAVING 关键字可以跟聚合函数，而WHERE 关键字不能。通常HAVING 关键字都和GROUP BY一起使用，用于对分组后的结果进行过滤。
举例：将student2表按照gender字段进行分组查询，查询出grade字段值之和小于300的分组
3 使用 LIMIT 限制查询结果的数量
SELECT 字段名2，字段名2，…
FROM 表名
LIMIT [ OFFSET ,] 记录数
在此语法中，LIMIT 后面可以跟两个参数，第一个参数“ OFFSET ”表示偏移量，如果偏移量为0，则从查询结果的第一条记录开始，偏移量为1则从查询结果中的第二条记录开始，以此类推。OFFSET为可选值，默认值为0，第二个参数“记录数”表示指定返回查询记录的条数。
举例1：查询student2表中的前四条记录。
举例2：查询student2表中grade字段从第五位到第八位的人（从高到低）
4 为表和字段取别名
1）为表起别名
在进行查询操作时，如果表名很长使用起来不方便，可以为表取一个别名来代替表的名称。
语法：SELECT * FROM 表名 [ AS ] 别名；
举例：为student2表起一个别名s，并查询student2表中gender字段值为“女”的记录
2）为字段起别名
语法：SELECT 字段名 [ AS ] 别名 [ ,字段名 [AS] 别名，…] FROM 表名 ；
举例：查询student2表中的所有记录的name和gender字段值，并未这两个字段起别名stu_name和stu_gender
、
上面是一些我们经常会用到的一些查询，接下来我要大家了解一下连表查询，先建表插数据。
View Code
1. 内连接：默认是内连接
内连接特点：
相连接的两张表地位平等
如果一张表中在另一张表中不存在对应数据，则不做连接
连接查询：表示查询所有数据的非NULL部分
select * from t_book, t_bookType where t_book.bookTypeId = t_bookType.id;
注:书面为赵**的书籍外键不存在，所有查询不出来
给每张表起别名用：as
SELECT tb.bookName, tb.price, tby.bookTypeName FROM t_book as tb, t_bookType as tby WHERE tb.bookTypeId = tby.id;
2，显示连接：
显示内连接比隐式内连接相同情况下更快。on是连接条件的限定
SELECT tb.bookName, tb.price, tby.bookTypeName FROM t_book as tb INNER JOIN t_bookType as tby ON tb.bookTypeId = tby.id;
3，外连接：
外链接分为左外连接和有外连接
外连接特点：
做连接的两个表地位不相等，其中有一张是基础表
基础表中的每条数据必须出现，即使另外一张表中没有数据与之匹配，也要用Null补齐
左外连接时左表为基础表，右外连接时右表为基础表
1，左连接： LEFT JOIN ON
两张表连接查询，无论二者是否有数据缺失，一定会把左边的表数据全部显示出来
SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId = t_bookType.id
2，右连接： RIGHT JOIN ON
两张表连接查询，无论二者是否有数据缺失，一定会把右边的表数据全部显示出来
SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId = t_bookType.id
3. 全连接： UNION ：
两张表连接查询，无论二者是否有数据缺失，一定会都显示表数据全部显示出来SELECT tb.bookName, tb.price, tb.author, tby.bookTypeName FROM t_book as tb LEFT JOIN t_bookType as tby ON tb.bookTypeId = tby.id UNION SELECT tb.bookName, tb.price, tb.author, tby.bookTypeName FROM t_book as tb RIGHT JOIN t_bookType as tby ON tb.bookTypeId = tby.id
4，子查询
1. 子查询： IN
在 select 语句的返回值的集合里里查找，复合集合里的数据的部分，才会匹配成功。SELECT * FROM t_book WHERE bookTypeId IN (SELECT id FROM t_bookType);
2. 子查询： NOT IN：
和 in相反，不在集合里的数据，才会匹配成功SELECT * FROM t_book WHERE bookTypeId NOT IN (SELECT id FROM t_bookType);
3. 子查询：EXISTS 返回Bool值，如果子查询语句里查询成功，则返回True，否则返回FalseSELECT * FROM t_book WHERE EXISTS (SELECT id FROM t_bookType where t_book.bookTypeId=t_bookType.id);
4. 子查询：NOT EXISTS： 对子查询语句返回的结果取反。
SELECT * FROM t_book WHERE NOT EXISTS (SELECT id FROM t_bookType where t_book.bookTypeId=t_bookType.id);
