杂谈篇——mysql基本操作

本文为MySQL入门视频笔记，
https://www.bilibili.com/video/av14716238/?p=1
sql:
语句以;结尾
\c 跳出
字段与数据组成表
每个字段有:名字 数据类型 长度 约束
查看数据库版本：select version();
中文字段名需要加单引号
单引号，尽量不用双引号
不区分大小写
null值参与运算结果为null

数据类型：
数值型，字符串，日期
int（3），整数型，最大999
bigint，
float，
double（7,2），7位有效数字，两位小数位
varchar（3），可变长度字符串
char(3),定长字符串
date，
blob，二进制大对象，存储图片和视频
clob，字符大对象，存储最大4g的字符串，用于存储文本
日期函数，str_to_date(‘10-12-1993’,’%m-%d-%Y’);Y m d H i s
data_format(日期类型的字段名，‘%m-%d-%Y’)
now();获取当前时间

数据库操作:
show databases;
create database db_name;
drop database db_name;
select database(); 当前数据库
source C:\Users\Think\Desktop\wheels\bjpowernode.sql

表操作：
show tables;
show tables from db_name;
show create table table_name;
show index from table_name;
show columns from table_name;
desc table_name;查表结构

语句顺序：
1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE or WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.limit
FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1
ON：对VT1应用ON筛选器。只有那些使为真的行才被插入VT2。
OUTER(JOIN)：如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。
WHERE：对VT3应用WHERE筛选器。只有使为true的行才被插入VT4.
GROUP BY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.
CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.
HAVING：对VT6应用HAVING筛选器。只有使为true的组才会被插入VT7.
SELECT：处理SELECT列表，产生VT8.
DISTINCT：将重复的行从VT8中移除，产生VT9.
ORDER BY：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).
TOP：从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。

select 不改底层数据，只查询
select 字段名 from table_name;
select 字段名，字段名，字段名 from table_name;
select * from table_name;
select 字段名*100 from table_name;对数据操作
select 字段名*100 as 新字段名 from table_name
select 字段名 from table_name where table_name=100;where tables_name between 100 and 200;数字是闭区间，字符是前闭后开区间
运算符：=,!=,<>,<,>,<=,>=,between and ,is null,is not null, and ,or, not,like，in，in （a，b，c）
模糊查询：like ‘%a%’; _代表任意一个字符，%标识任意字符
select 字段名 from table_name order by 字段名 asc;by desc;默认asc，升序
select 字段名 from table_name order by 字段名 asc,字段名1 desc; 用，隔开

select max(字段名) from table_name group by 字段名1
select max(字段名) from table_nam where 条件过滤 group by 字段
select 字段1，字段2，max（字段名3） from table_name group by 字段1，字段2
group by时，select关键字后面只能跟参与分组的字段和分组函数
having 过滤作用，在分组之后过滤，where在分组前过滤

连接查询/跨表查询：
内连接：等值连接，非等值连接，自连接
外连接：左连接，右连接
进行多表连接查询时，尽量给表起别名
select e.字段,d.字段 from talble1 e,table2 d;结果为笛卡尔积现象,e d 为别名
加限制条件避免笛卡尔积，但是匹配次数不变
select e.字段，d.字段 from table1 e, table2 d where e.字段1 = d.字段1；等值连接，92语法
select e.字段，d.字段 from table1 e join table2 d on e.字段1 = d.字段1；等值连接，99语法,可再加where过滤
select e.字段，d.字段 from table1 e inner join table2 d on e.字段1 between d.字段1 and d.字段2；非等值连接，内连接中inner 可以省略
select e.字段，d.字段 from table1 e join talbe1 d on e.字段=d.字段；自连接，一张表看做两张表
select e.字段，d.字段 from table1 e right outer join d on e.字段=d.字段；右外连接，显示全部右表中的数据，左表若匹配对象，则取null，outer可以省略

子查询：先进行一次select，当做临时表或临时数据（avg，max，min等）
select 嵌套select
where 后面嵌套select：select ename，sal fromemp e where sal > (select avg(sal) from emp);
from后嵌套： select t.deptno，t.avgsal，s.grade from (select avg(sal)as avgsal from emp) t join salgrade s on t.avgsal between s.losal and s.hisal;

union：将两个查询结果合并，字段数相等时可以合并，字段名可以不同，按第一个的字段名命名，mysql中可以数据类型不同
select 字段1，字段2 from table1 where 字段2=‘1 ’
union
select 字段1，字段2 from table1 where 字段2=‘2 ’
limit：mysql特色，获取一张表的部分数据，放在查询语句的最后边
select 字段 from table limit 5;从0坐标开始的5个
select 字段 from table limit 0，5;起始下标，长度

分页pageSize
select ename,sal from emp order by sal desc limit (pageNo-1)*pageSize,pageSize;

正则表达式：
SELECT name FROM person_tbl WHERE name REGEXP ‘^st’;

函数：
单行处理函数：
select lower(字段名) from table_name;
select substr(字段名，起始位置，长度) from table_name;
select 字段名 from table_name where substr(字段名，起始下标，长度）=‘a’
select upper(字段名) as 新字段名 from table_name; as可以省略
trim去除空格 ，round（123.45678，保留的小数点位数）；四舍五入；rand()随机数，0到1小数
select ifnull(字段名，替换值） as 新字段名 from table_name; 替换null值

多行处理函数：自动忽略null，分组函数不能用于where 条件中，必须先分组后用分组函数
count(字段名)统计个数，count(*) where 条件，sum(字段名)，avg(平均值) ，max（最大值）min（最小值）
select distinct 字段名，字段名 from table_name ，去除字段数据重复
select count(distinct 字段名) from table_name

创建、删除表：
create table table_name(
id int(10) unique,
name char(4) not null,
字段名 数据类型（长度） default ‘abc’
constraint 约束名字 unique（字段，字段），
constraint 主键名 primary key（字段，字段），
constraint 外键名 foreign key(本表中的字段) references 关联的表名(关联表的字段)
index 索引名字
);
default 默认值

约束：
非空约束：not null
唯一性约束：在该字段后加unique，列约束，不可唯一；
在所有字段后加unique（字段1，字段2）则为表约束，则表示不可两个字段同时相同；
constrain 约束名字 unique（字段1，字段2）后期可对约束操作，比如删除，
首先，use information_schema
然后，select constraint_name from table_constraints where table_name = ‘t_user’;约束保存在table_constraints表中了，

主键约束：唯一标识一条数据，主键非空且唯一，效果除notnull unique外，还会默认添加索引
primary key；单一主键可以列约束和表级约束，同unique
primary key auto increment；自动生成起始为1，递增为1的主键
primary key（字段，字段）；复合主键，只能是表级约束，两个字段组成共同主键

外键约束：外键字段区引用关联表的字段时，该字段需要unique，
外键值可以为空
先建父表，先修父表，先删子表
alter table 表A add constraint FK_B foreign key (ticket_no) references 表B(ticket_no)

索引：每个字段都可以添加索引，使得该字段没有重复值，但是可以有一个空值,数据量少经常更改不会出现在where条件中 一般不加索引
index：create index 索引名称 on table_name(字段名);
drop index 索引名称 on table_name;

删除表：
drop table tablename;
drop table if exits tablename;

复制表：
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;完全相等地复制
create table table_name as select 字段名，字段名 from table_name1;非完全复制
插入值：
insert into table_name(id,name,字段名) values(1,2,3);数据类型和个数必须相等
insert into table_name values（1,2,3）；必须和字段一一对应
insert into table_name(字段，字段） select 字段，字段 from table_name1；
insert into table_name select 字段，字段 from table_name1；
更新表中数据或者改表的结构：
update tablename set 字段名=字段值，字段名=字段值 where 条件；如果没有条件，则先全部清除后添加
delete from tablename where 条件；没有条件时全部清除
alter table table_name add 字段名 类型（长度）；增加字段
alter table table_name drop 字段名 类型（长度）；删除字段
alter table table_name modify 字段名 新类型（长度）；更改字段类型 modify 可以换为change
ALTER TABLE table_name ALTER 字段名 SET DEFAULT 1000;修改字段默认值
ALTER TABLE table_name RENAME TO 新名字;修改表名
delete from table_name where 条件；无条件，则清除

io：
use databasename;
source G:\arcgisworkspace\zypdoc\test.sql；导入sql
load data infile “your excel file path” into table table_name;导入Excel或者CSV
SELECT * FROM mytable
INTO OUTFILE ‘/tmp/mytable.csv’
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”’
LINES TERMINATED BY ‘\n’;导出到CSV