MySQL入门

SQL语句

• SQL：结构化的查询语句。由W3C组织制定。标准的数据库的操作语言，不区分数据库。

• SQL语句的分类：

  • DML：数据操纵语言，主要针对的是insert、update、delete操作

  • DCL：数据控制语言，主要针对的用户权限，表和库的权限，事务控制等等

  • DQL：数据查询语句，主要针对的是select语句

  • DDL：数据定义语言，主要针对的是库和表的CRUD（create, read, update, delete）语句。

数据库的操作

查询数据库

-- 查询所有数据库
SHOW DATABASES;
-- 查询某个数据库的编码
SHOW CREATE DATABASE test;

创建数据库

-- 创建库
CREATE DATABASE demo1;
-- 创建数据库的时候指定编码表
-- GB2312、GBK、GB18030、UTF-8(unicode)、ISO-8859-1(拉丁文)
CREATE DATABASE demo2 CHARACTER SET gbk;
-- 修改库的校对规则
CREATE DATABASE demo3 CHARACTER SET utf8 COLLATE utf8_bin;

删除数据库

-- 删除数据库
DROP DATABASE demo4;

修改数据库

-- 修改数据库编码表
ALTER DATABASE demo3 CHARACTER SET gbk;

数据表的操作

在操作数据表之前，一定要记住：切换到某个库下。

-- 切换数据库
use 库名;

创建数据表

-- 创建表
CREATE TABLE tb_user(
  id INT,
  username VARCHAR(10),
  age INT
);

创建表的时候，表的每个列名和表名不要和SQL中的关键字重名。

定义数据表中的每列都必须明确具体的数据类型

• 数字：TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE

• 字符串：char(长度)、varchar(长度)

char(10)：数据表的列最多只能存放10字符数据，如果不足10个，使用空格补够10个，超过报错

varchar(10):数据表的列最多只能存放10字符数据，如果不足10个，长度会发生变化，超过报错

• 日期：

date (只有日期)

time(只有时间)

datetime(日期时间都有)

timestamp(日期时间都有 )：时间戳

• 文件数据类型：

blob(存放大二进制数据)

text(存放大的文本文件)

tinyblob tinytext 255字节

blob text 64KB

mediumblob mediumtext 16MB

longblob longtext 4GB

实际开发中，文件不会给数据库中存储。采用专业的文件管理服务器存储，然后将文件在服务器上的存储位置存放在数据库中。

单表约束

主键约束：primary key , 它是保证当前这一行数据与其他行不重复。主键列的数据不能重复,也不能存放null. 非空约束：not null , 这个列中不能包含null。 唯一约束：unique ， 这列的数据不能重复。 一个表中主键一般只有一列或两列（联合主键）。主键会自动添加索引（主键索引）。

一个表中的唯一约束可以添加在多列上。

删除表

-- 删除表
drop table 表名;

修改表

表结构修改语法：

alter table 表名 增/删/改 列名 类型( 长度) 约束；

1、增加列： alter table 表名 add [column]列名 类型(长度) 约束;

2、修改现有列类型、长度和约束 语法：alter table 表名 modify 列名 类型(长度) 约束;

3、修改现有列名称 语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;

4、删除现有列 语法：alter table 表名 drop 列名 ;

5、修改表名 ：rename table 旧表名 to 新表名;

6、修改表的字符集：alter table 表名 character set 编码表名;

查询表结构

desc 表名; 查看表结构

show tables ; 查看当前库内所有表名

show create table 表名; 查看建表语句和字符集

所有数据表结构操作语句都是 DDL

表中的数据操作

创建数据表

-- 创建学生表
create table student(
    id int primary key auto_increment,
    sage int ,
    sname varchar(32) not null,
    gender varchar(10) not null,
    score double not null,
    birthday date
);

在创建表结构的时候，如果主键是整数类型，那么可以借助mysql的主键自增长策略，在插入数据的时候，可以省略主键的数据。

表中插入数据

语法：insert into 表名 (列名，列名，列名…) values (值，值，值…);

• 如果表的主键是自增长的，这时插入的时候，主键可以写null，但是不是将null存放到主键列上，而是让数据库自己维护主键的数据。

• 如果列的类型是varchar、char、date、datetime类型需要插入数据的时候，使用单引号引用

alter table student  character set utf8;
alter table student change sname sname varchar(50) character set utf8;
alter table student change gender gender varchar(50) character set utf8;
INSERT INTO student
            (id,
             sage,
             sname,
             gender,
             score,
             birthday)
VALUES (NULL,
        22,
        '张三',
        '男',
        99.89,
        '2020-07-18');

这里有个恶心坑，将student表的字符集改成utf8之后，还要特意将列的字符集也改过来，才能成功插入

• 如果某些列不需要插入数据，可以在插入的时候省略这些列

INSERT INTO student(sname , gender,score)VALUES('李四','男',89.00); 

• 如果是给表中的所有列都插入数据，这时可以省略列名

INSERT INTO student VALUES(NULL,18,'赵四','男',60,'1999-01-01');

修改表数据

语法：update 表名 set 列名=值 ,列名=值 ,列名=值 ,列名=值 [ where 条件 ]

一般要求在数据表数据修改的时候必须添加where进行条件的限制。否则整张表的数据修改。

-- 修改表数据
-- 不添加条件修改
UPDATE student SET sage = 28;
​
-- 添加条件修改
UPDATE student SET sname = '王五' WHERE id = 1;
UPDATE student SET sname = 'zhaoliu' , gender = '女' ,score = 100 WHERE id = 2;
UPDATE student SET gender = '女' ,score = 100 WHERE sname = '赵四';

删除表数据

语法：delete from 表名 [where 条件]

删除操作也应该添加条件，否则删除整张表的数据。

-- 删除操作
DELETE FROM student WHERE id = 5;
INSERT INTO student VALUES(NULL,22,'张三','女',88,'2020-02-02');
​
DELETE FROM student WHERE sname = 'abc';
​
-- 删除整张表中的数据
DELETE FROM student ;
-- 删除整张表中的数据
TRUNCATE TABLE student;

DELETE : 将表的中的数据逐行删除。

TRUNCATE：直接使用drop将表删除，然后重新创建一个新的表。

delete语句以通过事务进行控制，TRUNCATE属于DDL语句，无法通过事务控制。

数据查询

基础查询

查询语法：select 列名,列名,列名,列名,列名,列名… from 表名 where 条件

-- 查询操作
INSERT INTO student (id,sage,sname,gender,score,birthday) VALUES(1,23,'zhangsan','male',98.99,'1990-09-09');
INSERT INTO student (id,sage,sname,gender,score,birthday) VALUES(2,23,'lisi','男',56.99,'1990-02-09');
INSERT INTO student (id,sage,sname,gender,score,birthday) VALUES(3,24,'王五','女',75.99,'1988-01-01');
INSERT INTO student (id,sage,sname,gender,score,birthday) VALUES(4,25,'赵六','男',80.99,'1980-11-12');
INSERT INTO student (id,sage,sname,gender,score,birthday) VALUES(5,18,'田七','女',80.99,'1980-11-12');
-- 查询表中所有数据
SELECT * FROM student;
-- 添加条件查询
SELECT * FROM student WHERE id = 1;
-- 根据姓名查询
SELECT * FROM student WHERE sname = 'lisi';
-- 查询姓名和成绩
SELECT sname ,score FROM student;
-- 查询成绩，相同只显示一个
SELECT score FROM student;
-- 使用DISTINCT去重
SELECT  DISTINCT score FROM student;

在查询的时候，给查询数据直接进行运算，只是查询的结构数据发生变化，而数据库中的数据没有改变：

-- 查询的成绩+10分
SELECT sname , sage , score + 10 FROM student;

上面的查询操作，出现小数精度问题（计算中所有的小数都是近似值）。

如果数据库中需要小数精确值，列的类型可以使用DECIMAL(M,D), NUMERIC(M,D)

-- m 表示数字的个数，d小数位数
ALTER TABLE student MODIFY score NUMERIC(5,2);

-- 别名： 列名或表名 [as] 别名
-- 给查询列或者表使用别名
SELECT sname AS 姓名 , sage AS 年龄 , score + 10 成绩 FROM student  s;

-- 别名的面试题
-- 查询表中的sage 和score两列的数据
SELECT sage , score FROM student;
-- 查询表中的sage列的数，并起别名为score
SELECT sage  score FROM student;

and、or、null使用

-- 查询成绩在80~100的学生
SELECT * FROM student WHERE score >= 80 AND score <= 100;
SELECT * FROM student WHERE score >= 80 && score <= 100;
SELECT * FROM student WHERE score BETWEEN 80 AND 100;
​
-- 查询年龄为null的学生信息
SELECT * FROM student WHERE sage IS NULL;
-- 查询年龄不为null的学生信息
SELECT * FROM student WHERE sage IS NOT NULL;
​
-- 查询性别为男，或者成绩大于80
SELECT * FROM student WHERE gender = '男' OR score > 80;

重要事项：不同的数据库对 BETWEEN…AND 操作符的处理方式是有差异的。有的是左闭右开，有的是都闭合，between前面可加not。

列表in的使用

-- in的用法
-- 查询年龄为23或25的学生
SELECT * FROM student WHERE sage  IN( 23 , 25 );
-- 查询年龄不为23或25的学生
SELECT * FROM student WHERE sage  NOT IN( 23 , 25 );

显然年龄为24的学生出现在NOT IN里面。

模糊条件使用

模糊查询的like关键字后面的值书写有两种情况：

• % :匹配任意个字符

• _ : 匹配一个字符

-- 模糊查询
-- 查询所有姓张的学生数据
SELECT * FROM student WHERE sname LIKE '王%';
-- 查询拥有两个字姓名的学生
SELECT * FROM student WHERE sname LIKE '__';
-- 查询姓名中包含某个字的学生
SELECT * FROM student WHERE sname LIKE '%王%';

当%，_需要作为模糊查询的查询条件时（例如有个学生名字里有%，我们要找他），需要通过转译：

SELECT * FROM students WHERE sname LIKE '/%__/_' ESCAPE '/';

ESCAPE '/'表示，斜杠之后的第一个百分号或下划线不作为通配符。

查询并排序

-- 排序查询 order by 列名 asc | desc 
-- 查询所有的学生数据，按照年龄升序
SELECT * FROM student ORDER BY sage ASC;
-- 查询所有的学生数据，按照年龄升序,年龄相同，成绩降序
SELECT * FROM student ORDER BY sage DESC , score ASC;
-- 查询年龄大于15 小于 25 的升序  , 先执行的条件过滤，然后将结果排序
SELECT * FROM student WHERE sage > 20 && sage < 25 ORDER BY sage ASC;

默认就是DESC。

分组函数

分组：是对数据表中的数据按照一定的规律进行划分档次。同一档次的属于一组。

聚集函数：也成为分组函数，主要是指SQL语句中的内置函数。用于分组统计。

count计数

count：统计符合条件的数据个数。

-- 统计学生个数
-- select count(列名) from student;
SELECT COUNT(id) FROM student;

count函数在使用的时候：传递的参数：

COUNT(*): 在统计的时候，如果这列值为null，找本列后面的一列，如果当前一行都是null，不统计

COUNT(主键): 只能统计主键这列，由于主键唯一并不为null，统计整个表中的记录数。

count(列名)： 统计指定的那列的数据个数，如果有null，不统计。

-- 统计成绩大于80分个数
SELECT COUNT(id) FROM student WHERE score > 80;

sum求和

-- 统计总成绩
SELECT SUM(score) FROM student;
​
-- 统计年龄和成绩
SELECT SUM(sage) , SUM(score) FROM student;
​
-- 将年龄和成绩总和相加
SELECT SUM(sage) + SUM(score) FROM student;
-- 如果sage中存在null，下行结果就不对
SELECT SUM(sage + score) FROM student;
​
-- 在mysql中有个ifnull的函数，可以判断某个列的值是否为null，然后给其指定默认值
SELECT SUM( IFNULL(sage , 0) + IFNULL(score , 0) ) FROM student;

max、min求最值

-- 最值max   min
-- 年龄的最大的和最小的
SELECT MAX(sage) , MIN(sage) FROM student;

avg求平均数

-- 成绩的平均值
SELECT AVG(score) FROM student;
SELECT SUM(score) / COUNT(id) FROM student;

group by 分组

（这个没有数据，看着先）

-- 分组
-- 统计每类商品的总价
SELECT product , SUM(price) FROM orders GROUP BY product;
​
-- 查询购买了几类商品，并且每类总价大于100的商品
-- 聚集（分组）函数，不能使用在where的条件中，必须使用having关键字代替where
-- select count(product) from orders  GROUP BY product where sum(price) > 100;
​
SELECT product , COUNT(product) FROM orders  GROUP BY product HAVING SUM(price) > 100;
​
-- 分组 ，过滤， 排序
SELECT product , COUNT(product) FROM orders  GROUP BY product HAVING SUM(price) > 100 ORDER BY product ASC;

where 和 having 条件语句的区别 ？

where 是在分组前进行条件过滤，having 是在分组后进行条件过滤

使用where地方都可以用 having替换 ， 但是having可以使用分组函数，而where后不可以用分组函数