mysql 基础整合大全

时间:2024-01-23 21:25:29

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 标识该属性为该表的外键,与某表的主键关联。如果一个表中的某一列是另外一个表的中的主键,那么设置这列为外键。

 

数据表数据插入,

insert into t_hero(name, age, gender, state) values("曹操","45","","");
insert into t_hero(name, age, gender, state) values("刘备","43","","");
insert into t_hero(name, age, gender, state) values("孙权","42","","");
insert into t_hero(name, age, gender, state) values("诸葛亮","36","","");
insert into t_hero(name, age, gender, state) values("司马懿","35","","");
insert into t_hero(name, age, gender) values("貂蝉","22","");
insert into t_hero(name, age, gender) values("吕布","30","");
insert into t_hero(name, age, gender) values("小乔","20","");
insert into t_hero(name, age, gender, state) values("关羽","35","","");
insert into t_hero(name, gender, state) values("孙尚香","","");
insert into t_hero(name, age, gender, state) values("张飞","33","","");
insert into t_hero(name, gender, state) values("小张飞","","");
insert into t_hero(name, age, gender, state) values("小张飞儿","33","","");
insert into t_hero(name, age, state) values("张小飞儿","33","");
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));
  

举例:查询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

 


上面是一些我们经常会用到的一些查询,接下来我要大家了解一下连表查询,先建表插数据。

create table t_book(
  id int unsigned auto_increment primary key,
  bookName varchar(255) default null,
  price decimal(6,2) default null,
  author varchar(255) default null,
  bookTypeId int(11) default null
); 
insert into t_book values(1, '追风筝的人', '29.00', '胡赛尼', 1);
insert into t_book values(2, '计算机', '45.00', 'Harold', 2);
insert into t_book values(3, '代码大全', '128.00', '史蒂夫', 2);
insert into t_book values(4, '哥德尔 ', '88.00', '侯世达', 3);
insert into t_book values(5, '赵迎伟', '4.50', '[中]老子', 4);

create table t_bookType(
  id int(11) auto_increment not null primary key,
  bookTypeName varchar(10) default null
);
insert into t_bookType values(1, '小说类');
insert into t_bookType values(2, '编程类');
insert into t_bookType values(3, '哲学类');
insert into t_bookType values(5, '艺术类');
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,否则返回False
SELECT *
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);

 

 

 

 

 

 就算是一条咸鱼,也要做最咸的那一条