基础
1. My SQL 是用于操作远程服务文件的一个软件
- 服务端软件
- socket 服务端
- 远程之间要进行访问,绝对要用socket
- 本地文件操作
- 解析指令【SQL语句】
- socket 服务端
- 客户端软件(很多种样)
- socket 客户端
- 发送指令
- 解析指令【SQL语句】
- DBMS
- 数据库管理系统
2. 安装调试
1) 安装 客户端和服务端
- 1. 放置任意目录
- 2. 服务端初始化
- 服务端:D:\Data-Science\mysql-8.0.20-winx64\bin\mysqld --initialize-insecure
- 用户名:root, 密码为空
- 3. 启动服务端
- D:\Data-Science\mysql-8.0.20-winx64\bin\mysqld
- 4. 客户端链接
- D:\Data-Science\mysql-8.0.20-winx64\bin\mysql -u root -p
- show databases;
- 注意末尾加分号。作用是查看所有目录
- create database db1;
- 创建数据库db1
- 5. 增加环境变量,以后直接就在命令行里输入mysqld 和 mysql
- 6. Windows 服务
- D:\Data-Science\mysql-8.0.20-winx64\bin\mysqld --install
- D:\Data-Science\mysql-8.0.20-winx64\bin\mysqld --remove
- 用 net start/stop 启动/停止
- 7. 转储sql文件
- 数据表结构+数据:mysqldump -u root db1 > db1.sql -p
- 数据表结构:mysqldump -u root -d db1 > db1.sql -p
- 即把所有insert into的命令都不保存
- 8. 导入sql文件
- 先创建新数据库,再进行导入
- create database dbnew;
- mysqldump -u root -d dbnew < db1.sq1 -p
2) 连接
- 1. 结构
- 文件夹【也就是所谓的数据库】
- 文件【也就是表】
- 数据行【行】
- 2. 用户操作
- 默认:用户root
- 创建用户
- CREATE USER 'username'@'host' IDENTIFIED BY 'password';
- create user 'alex'@'192.168.1.1' identified by '123123';
- 限定用户Alex只能在IP地址为192.168.1.1 下登录,密码为123123
- create user 'alex'@'localhost' indentified by '123456';
- create user 'alex'@'192.168.1.%' identified by '123123';
- 限定用户Alex只能在IP地址为192.168.1..。 下登录,密码为123123
- create user 'alex'@'%' identified by '123123';
- 用户Alex可以在任意地址登录
- CREATE USER 'alex'@'%' IDENTIFIED BY '';
- username 你将创建的用户名
- host
- 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%
- 删除用户
- drop user '用户名'@'IP地址';
- 修改用户
- rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';
- 修改密码
- set password for 'username'@'host' = password('newpassword');
- 当前用户修改密码:set password = password("newpassword");
- 3. 权限操作
- 授权
- grant privileges on databasename.tablename to 'username'@'host';
- grant all privileges on db1.* to 'alex'@'%';
- 用户只对db1里的所有表拥有权限
- privileges
- 用户的操作权限,如SELECT , INSERT , UPDATE 等,如果要授予所 的权限则使用ALL说明
- GRANT SELECT, INSERT ON test.user TO 'javacui'@'%';
- GRANT ALL ON *.* TO 'alex'@'%';
- 撤销用户权限
- REVOKE all ON databasename.tablename FROM 'username'@'host';
- 撤销用户所有权限
- 撤销权限的语句必须与创建语句对应
- 如果创建用户权限用 GRANT SELECT ON test.user TO ‘a’@'%’; 那么 REVOKE SELECT ON *.* FROM ‘a’@'%’; 命令并不能撤销该用户对test数据库中user表的 SELECT 操作
- 如果创建用户权限用 GRANT SELECT ON *.* TO ‘a’@'%’; 那么 REVOKE SELECT ON test.user FROM ‘a’@'%’; 命令也不能撤销该用户对test数据库中user表的 Select 权限
- 查看用户权限
- SHOW GRANTS FOR ‘用户名’@'%’
- 4. 刷新数据库
- flush privileges;
3) 数据类型
- 整数型
- int
- bigint
- smallint
- tinyint
- 浮点型
- float
- double
- decimal
- 按照数据原型记录数据
- 字符串型
- char(10)
- 就占10个位置,超出10个字符就报错,少于10个字符也会占10个位置.
- varchar(10)
- 只占数据原本的字符数。比如存入root,只占4个位置。
- 时间类型
- DATE
- YYYY-MM-DD
- TIME
- HH:MM:SS
- YEAR
- YYYY
- DATETIME
- YYYY-MM-DD HH:MM:SS
- TIMESTAMP
- YYYYMMDD HHMMSS
- 枚举类型
- enum 以后插入这个名称时只能在枚举字符串里的选择
- 集合类型
- set 可以插入给定字符串的组合
- 二进制型
4) 外键:别的表的主键,一对多
- 基础结构
- 外键的语句加在主表里,可以增加多个外键,用逗号隔开,最后一个外键后面不要加逗号
- 语句结构为:
- constraint 外键名称 foreign key(主表里的外键连接列名称) references 连接的表名(列名)
- 外键名称不能重复
- 给已经存在的表里增加外键
- 主键
- 一张表只能有一个主键 primary key
- 一个主键可以由多列组成,且主键不能为空
- 当主键组合起来后,可以进行外键同时关联
- 外键的变种
- 一对一
- fk() + 唯一
- 举例:id 对应 身份证
- 多对多 联合索引唯一
- 因为上面单纯的俩张表不能表达关系,所以要引入双向的 foreign key. 如果要求主机关系表里的 user 和 hostid 不能重复,需要给二者做联合唯一。
5) 唯一索引
- 功能:加速查找;不能重复
- 索引的目的是加速查找,唯一是指约束不能重复,可以为空。——> 主键也是不能重复但可以为空
- 联合索引
- 绑定多个列表,使其值唯一
- unique uq1 (id,num)
3. 其他类似软件
- 关系型数据库(有约束)
- sqllite, db2, oracle, access, sql, server, MySQL
- 非关系型数据库(无约束)
- MongDB, redis
SQL语句
1. 基础连接语句
- show databases;
- 查看所有的数据库
- use 数据库名称;
- 进入数据库
- show tables;
- 查看数据库里的所有表
- select * from 表名;
- 查找这个表名下所有的列
- select name,age,id from 表名;
- 从这个表名里查找 name、age、id这三列
- mysql 数据库 user 表
- use mysql
- select user,host from user;
- 进入user 表里查找 user和host列
2. 操作文件夹
- create database db2;
- create database db2 default charset utf8;
- show databases;
- drop database db2;
- 触发器
- 创建一个触发器
- 创建多个触发器
- 查看触发器
- SHOW TRIGGERS;
3. 操作文件
-
use db1;
- show tables;
-
create table t1(id int,name char(10)) engine=innodb default charset=utf8;
- t1的格式为:列名 类型 null #可以为空值
- 列名 类型 not null #不可以为空值
- innodb 支持事务,原子性操作
- myisam
- 列名 类型 not null auto_increment primary key #数据不为空且自增
- auto_increment 表示自增
- primary key 表示约束(不能重复且不能为空);加速查找
- #一个表里只能有一个自增列,且只能有一个primary key
-
创建临时表 as B --子查询
- (select * from score where num>80) as B;
-
视图 view
- 创建
- create view 视图名称 as SQL语句
- view 是虚拟存在的,不可对其进行插入操作。当原表修改时,view也会相应修改
- 修改
- alter view 视图名称 as SQL语句
- 删除
- drop view 视图名称;
- 创建
4. 操作文件中的内容
- 增
- 插入单条数据
- insert into t1(id,name) values(1,'alex');
- 插入多条数据
- insert into t1(id,name) values(1,'alex'),(2,'c'),(3,'dewf');
- 把已经存在的一个表的数据导入新建的表中
- insert into 新表名 (列名1,列名2) select 原列名1,列名2 from 原表名;
- 删
- 清空表
- delete from t1;
- 把表清空但下次加入数据时,从原来表的最后一列开始自增,即 auto_increment还保留着之前的数值
- truncate table t1;
- 清空数据,且下次插入数据时从1开始
- delete from t1 where id<6;
- 删除id<6的数据
- 删除表
- drop table t1;
- 去重
- distinct
- select distinct name,id from tb1;
- 查
- select * from t1;
- 查t1所有列名称
- select age,id from t1;
- 查指定列
- select id,name as cname from tb12 where id > 10 or name ='xxx';
- 指把name列名改为 cname 但是数据不改变
- 查的同时增加额外的列
- select name,age,11 from t1; # 在age后面增加一列都为11.
- 查看某张表的字段类型: desc 表名
- 查看如何创建的表
- show create table 表名;
- show create table 表名 \G;
- 筛选条件
- select * from tb1 where id != 1;
- select * from tb1 where id in (1,2,3);
- select * from tb1 where id in (select name from tb2); #动态查询,查找交集
- select *from tb1 where id not in (1,2,4);
- select * from tb1 where id between 5 and 12; # 闭区间,包含5和12
- 通配符
- 百分号%,后面有多少数字就取多少位
- %a
- 以a结尾
- a%
- 以a开头
- %a%
- 所有包含有a的
- 下划线_,后面只取一位
- a_
- _a
- 分页
- select * from tb1 limit 10; #查看前10条
- select * from tb1 limit 1,4; #从索引位置2开始,往后取4条
- select * from tb1 limit 10 offset 20; # 从20条开始取10条
- 排序 order by
- select * from tb1 order by id desc; # 按照id顺序从大到小
- select * from tb1 order by id asc; # 按照id顺序从小到大
- select * from tb1 order by id desc limit 10; # 取倒数10条
- select * from tb1 order by 列1 desc, 列2 asc; # 根据列1从大到小排列,如果有相同数据,则按照列2从小到大。
- 多条件排序
- 分组 group by
- select * from tb1 group by part_id; # 聚合tb1里part_id 相同的数据
- group by 必须在where之后,order by之前
- where + group by
- group by + order by
- select count(id),part_id from tb1 group by part_id;
- select max(id),part_id from tb1 group by part_id;
- select min(id),part_id from tb1 group by part_id;
- select sum(id),part_id from tb1 group by part_id;
- select avg(id),part_id from tb1 group by part_id;
- 如果对于聚合函数的结果进行二次筛选,必须使用having,也就是where语句里一定不能出现聚合函数
- select count(id),min(id),part_id from tb1 group by part_id having count(id) > 2;
- 数数
- select count(id) from tb1;
- 连表操作:把N张表放在一起显示 join / union
- 左右连表
- select * from tb1 left join tb2 on tb1.id = tb2.id;
- tb1 左边表全部显示,如果tb2中无对应关系,则值为null
- select * from tb1 right join tb2 on tb1.id = tb2.id;
- tb2 右边表全部显示,如果tb1中无对应关系,则值为null
- select * from tb1 inner join tb2 on tb1.id = tb2.id;
- 将出现null时的整行隐藏
- 多张表连起来
- 上下连表
- select id from tb1 union select name from tb2;
- union,自动处理重合
- select id from tb1 union all select name from tb2;
- union all,不处理重合
- 笛卡儿积
- 把同一张表as成俩张表,对同一列的内容进行筛选
- 指定映射
- select id,name,1,sum(x)/count(1)
- 改
- 更新 update
- update t1 set age =18;
- update t1 set age=18 where age =17;
- update t1 set age=18,name=alex where age =17;
- 修改 alter
- 自增
- 设定步长,尽量使用session
- show session variables like 'auto_inc%'; ##查看全局变量
- set session auto_increment_increment = 2; ##设置会话步长,只是基于会话级别的,打开这个会话窗口设置一下就有,但不能保存。
- set global auto_increment_increment = 200; ##设置全局步长
- 设定初始值
- 修改起始值 alter table t1 auto_increment=3;
- 当delete数据之后,想要重新设定自增开始的值,只需设定 auto_increment 为多少就好。
- set session auto_increment_offset = 10; #会话起始值
- set global auto_increment_offset=10; #全局起始值
- 修改列名称
- alter table course change tearch_id teacher_id int;
- alter table 表名 change 原列名 新列名 类型;
MySQL函数
-
数学函数
- 绝对值函数 ABS(x)
- 返回圆周率的函数 PI()
- 平方根函数 SQRT(x)
- 求余函数 MOD(x,y)
- 时间格式化
- CURDATE()
- CURRENT_DATE()
-
条件函数
- 三元运算 if(isnull(xx),0,1)
- case when id>10 then 0 else 1 end
上传文件
- 文件存硬盘
- 数据库里存路径
练习题
https://www.cnblogs.com/wupeiqi/articles/5729934.html
https://www.cnblogs.com/wupeiqi/articles/5748496.html