SQL 基础语句复习总结

基础

1. My SQL 是用于操作远程服务文件的一个软件

  • 服务端软件
    • socket 服务端
      • 远程之间要进行访问,绝对要用socket
    • 本地文件操作
    • 解析指令【SQL语句】
  • 客户端软件(很多种样)
    • 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

©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页