Mysql基础知识

1、数据类型

1.1 整数类型

1.2 浮点数类型和定点数类型

1.3 字符串

char 和 varchar，两者的区别varchar是可变长度的

1.4 文本类型

1.5 日期与时间类型

1.6 二进制类型

2、数据库和表的基本操作

2.1、数据库

# 建库
create database {数据库名称}
# 删库
drop database {数据库名称}
# 查询所有数据库名
show databases
# 修改编码方式
alter database {数据库名} character set gbk
# 更换数据库
use {数据库名}
# 查询当前数据库名
select database()
建数据库
create table tabblename (
    字段1 字段类型 not null, 
    字段2 字段类型 default '123', 
    … 
    字段n 字段类型 unique,
    primary key(字段名)
)

2.2 表的操作

# 删除表
drop table 表名;
# 查看表信息
desc {表名}
# 修改表字段名字
alter table {表名} change 字段名 新字段名 类型
# 修改表字段类型
alter table {表名} modify 字段名 类型
# 增加字段名字
alter table {表名} add 字段名 类型
# 删除字段名
alter table {表名} drop 字段名

# 建外键和删外键
-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

# 删除外键
alter table 从表名 drop foreign key 外键名；

2.2 用户管理和权限管理

创建用户
    create user '用户名'@'IP地址' identified by '密码';
删除用户
    drop user '用户名'@'IP地址';
修改用户
    rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
修改密码
    set password for '用户名'@'IP地址' = Password('新密码')

# 查看权限：
show grants for '用户'@'IP地址' 
# 授权
grant  权限 on 数据库.表 to '用户'@'IP地址'
# 取消授权
revoke 权限 on 数据库.表 from '用户'@'IP地址'

grant all privileges on db1.tb1 TO '用户名'@'IP'
grant select on db1.* TO '用户名'@'IP'
grant select,insert on *.* TO '用户名'@'IP'
revoke select on db1.tb1 from '用户名'@'IP'

3、增删改查语句

3.1、插入数据

插入单条数据
INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);
插入多条数据
INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;
插入查询出来的数据
INSERT INTO 表名（字段名1,字段名2,...) SELECT * FROM 表名

3.2、删除数据

DELETE FROM 表名 [where 条件表达式]；
和TRUNCATE的区别
TRUNCATE只能删除全部记录，并且删除之后，自增的字段会从0开始

3.3、更新数据

UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];

3.4、查询数据

select * from 表名 （其余略）

3.5、聚合函数

聚合函数使用规则：
只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。

count(*),max(),min(),sum(),avg()

#时间函数
SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');

#字符串函数

--连接函数
SELECT CONCAT ()
--
SELECT INSTR ();
--统计长度
SELECT LENGTH();

#数学函数

-- 绝对值
SELECT ABS(-136);
-- 向下取整
SELECT FLOOR(3.14);
-- 向上取整
SELECT CEILING(3.14);

3.6、条件查询

# IN
select * from student where sid in ('S_1002','S_1003');
# NOT IN
select * from student where sid not in ('S_1001');
# BETWEEN AND
select * from student where age between 15 and 18;
# NOT BETWEEN AND
select * from student where age not between 15 and 18;
# IS NOT NULL
select * from student where sname is not null;
# LIKE
select * from student where sname like '%g';

3.7、GROUP BY,HAVING,ORDER BY

# 统计各部门人数
select count(*), departmentnumber from employee group by departmentnumber;

# 和having一起使用
统计工资总和大于8000的部门 MySQL命令：
select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;

# ORDER BY
SELECT 字段名1,字段名2,… FROM 表名 ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];
select * from student order by age asc limit 3;

4、多表联合

4.1、表关系

在实际开发中数据表之间存在着各种关联关系。在此，介绍MySQL中数据表的三种关联关系。

• 多对一
  多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。在多对一的表关系 中，应将外键建在多的一方否则会造成数据的冗余。

• 多对多
  多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。

• 一对一
  在开发过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。

4.2、表连接

# 内连接（满足条件的记录出现在查询结果中）

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

# 外连接

LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。
RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

4.3、子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。

select * from class where cid=(select classid from student where sname=’张三’);
select * from class where exists (select * from student where sname=’王五’);
select * from class where cid > any (select classid from student);
select * from class where cid > all (select classid from student);

select ===> from ===> where ===> group by ===> having ===> order by ===> limit