# MySQL

DDL数据定义语句
DML数据操作语句

# 登录

本地登录
远程登录

# 本地登录

mysql -u root -p

# 远程登录

mysql -u root -h 'IPaddress' -p

# MySQL用户操作

用于修改用户对数据库访问的权限等

# 创建用户

create user 'userName'@'IPaddress' identified by 'password'

create user 'login'@'%' identified by '123';
# 创建一个可以从任意地方登陆的用户, 密码为123

create user 'login'@'localhost' identified by '123';
# 创建一个只能在本地登录的用户, 密码为123

create user 'login'@'123.60.138.52' identified by '123';
# 创建一个只能在IP地址为"123.60.138.52"登录的用户，密码为123

# 修改用户数据

alter user 'userName'@'IPaddress' identified by 'password'

alter user 'login'@'%' identified by '234';
# 修改用户('login'@'%')的密码，新密码为234

# 删除用户

drop user 'userName'@'IPaddress'

# 查询用户权限

show grants for 'userName'@'IPaddress';

# 授予用户权限

grant [ 权限 ] on [ tableName ] to 'userName'@'IPaddress';

# 撤销用户权限

revoke [ 权限 ] on [ tableName ] from 'userName'@'IPaddress';

# 权限类型

all - 全部权限 select - 查询权限 insert - 插入权限 update - 修改权限 delete - 删除权限 alter - 修改数据库，表等权限 drop - 删除数据库，表等权限 create - 创建数据库，表等权限

# 其他操作

# use

use [ databaseName ] 访问数据库

# show

show databases show tables 展示全部数据库，数据库中所有的表单，当前数据库

# desc

desc [ tableName ] 显示表单中的内容

# create{#create}

用于创建数据库，表等等

create database [ databaseName ] // 创建数据库
create table [ tableName ] ( 属性 ) // 创建表

create database 'tmp';
# 创建一个数据库，名为'tmp'

create table player (
    id int auto_increment primary key,
    attr1 varchar(100) default "无",
    attr2 varchar(100) comment "备用"
)
# 创建一个表，名为player

约束	解释
not null	非空
unique	唯一
primary key	唯一标识（非空，唯一）
default	默认值为____
check	检查
foreign key	外键

# alter {#alter}

一般用于修改表单的内容 alter table [ tableName ] [ action ]

action	用途
modify column [ fieldName ] [ type ]	修改某个字段的类型
rename column [ oldName ] to [ newName ]	修改字段名字
change column [ oldName ] [ newName ] [ type ]	上面两个的结合
add column [ fieldName ] [ type ]	添加一个字段
add [ constraintName ] [ (fieldName) ]	为字段添加约束
drop column [ fieldName ]	删除某个字段

# drop {#drop}

删除数据库，表单等

drop database [ databaseName ]
drop table [ tableName ]

# DML数据操作语句{#DML}

select
insert
delete
update
拓展

# select查询 {#select}

select [ fieldName ] or * from [ tableName ]

select name from player
# 查询 name 的数据

# insert插入 {#insert}

insert into [ tableName ] [ (属性) ] values [ (数据1), (数据2) ]

insert into player (id,name) values (1,"10");
# 插入一条数据id = 1 name = "10"
insert into player (id,name) values (1,"10"),(2,"11");
# 插入两条数据id = 1 name = "10" 和 id = 2 name = "11"

# delete删除 {#delete}

delete from [ tableName ] where "匹配条件"

delete from player where level = 1
# 将所有level = 1 的数据删除

# update更新 {#update}

update [ tableName ] set [ 属性名1 ] = [ value1 ], [ 属性名2 ] = [ value2 ] where [ 匹配条件 ]

updata player set level = 1 where name = "李四"
# 将属性 name = "李四" 的全部 level 改为 1

# 有关DML语句的拓展 {#DML_MORE}

# where

where "匹配条件"

where level = 1 and level < 5;
# 1 < level < 5

where level in (1,2,3);
# level == 1 , 2, 3其中一个

where level between 1 and 10;
# level 在 [1,10]之内

where level regexp '正则表达式'
# 通过正则表达式查找

# order by

order by [ 属性1 ] [ desc ], [ 属性2 ] [ desc ] 通过(属性)排序

select * from student (order by name asc;) or (order by name;)
# 通过名字升序排列

select * from student order by name desc, id
# 通过名字降序排列，并通过id升序排列

# group by

group by [ 属性 ] 通过(属性)分组

select name from student group by name;
# 通过名字分组

# having

having "判断条件" 对分组后的结果进行筛选

select name avg(id) from student group by name having avg(id) < 2;
# 对分组后的结果筛选出avg(id) < 2的

# limit

限制输出数量

select * from student limit 2;
# 只输出前2个

select * from student limit 1,10;
# 从1开始输出10个

# distinct

去重

select distinct name from student;
# 通过名字查找，但去除重复部分, 相同名字只出现一次

# union

将两个查询结果合并, 并集

select name from student union select id from student;
# 将两个表的筛选结果合并，但是后者的select并没有其作用，并且将结果去重

select name from student union all select id from student;
# 将两个表的筛选结果合并，但是后者的select并没有起作用，结果不去重

# intersect

求交集

select name from student intersect select id from student;
# 求解两个表筛选结果的交集，但是后者的select并没有起作用

# except

求差集，后者减前者

select name from student except select id from student;
# 分别对两个表筛查，并且求解两个结果集的差集

# left join(right join) ... on

将两个表合并成一个

select * from table1
left join table2
on "条件"

# cross join ... on

交叉连接(笛卡尔积) 每个数据会一一比较

select * from table1
cross join table2
on datadiff(table1.date,table2.date) = 1
# 将日期为前一天加一的拿出来

# inner join

内连接，求交集

select * from player
inner join student
on player.id = student.id;
# 将 student内连接到player中，通过id进行连接

# 内置函数

current_date() 当前日期 current_time() 当前时间 current_timestamp() 当前时间戳 now() 当前日期时间 date_add(date,interval,d_value_type) 让时间增加 date_sub(date,interval,d_value_type) 让时间减少 datediff(date1,date2) 两个日期的时间差

# 导入导出数据

load data infile ' [path] '
into table [ tableName ]
fields terminated by ','
lines terminated by '\n';
# 从 [ path ]中导入数据到 [ tableName ]
# 各个数据间隔为 ','
# 行尾为 '\n';
select * from [ tableName ]
into outfile ' [path] '
fields terminated by ','
lines terminated by '\n';
# 从 [ tableName ]中导出数据到 [ path ]
# 各个数据间隔为 ','
# 行尾为 '\n';