mysql常用基础指令大全

mysql指令

启动 net start mysql
退出mysql quit
登录 mysql -uroot -p
逻辑非 not ! 逻辑与 and && 或者 or || 逻辑异或 xor
范围查询 where 字段 between 开始值 and结束值
消除重复值 distinct select distinct 字段名 from 表名
乱 码 set names gbk
创建 create database 数据库名；
查看数据库 show databases;
选择 use 数据库名；
删除 drop database 数据库名；
创建表 create table [if not exists] 表名(字段1 数据类型 [字段属性|约束][索引][注释],...)[表类型][表字符集][注释];
主键 primary key
注释 comment
查看表 select * from 表名
查看建表语句 show create table 表名;
字段约束
非空约束 not null 字段不可以为空 必填
默认约束 default 赋予某字段默认值
唯一约束 unique(uk)设置字段的值是唯一的 允许为空，但只能有一个空值
主键约束 peimary key(pk)设置该字段为表的主键可唯一标识该表记录
外键约束 foreign key(fk)用于在两表之间建立关系，需要指定引用主表的哪一字段
自动增长 auto_increment设置该列为自增字段默认每条自增1通常用于设置主键
查看表 show tables describe 表名 desc 表名
删除表 drop table if exists 表名；
设置表的引擎 engine=myisam;
查看存储引擎 show engines \g;
声明无符号数 unsigned
位数不足以0补位 zerofill
字段定义
修改表名 alter table 旧表名 rename[to] 新表名 修改表的定义
添加字段 alter table 表名 add 字段名 数据类型 [属性]
修改字段的数据类型 alter table 表名 modify 字段名 数据类型
修改字段名 alter table 表名 change 原字段名 新字段名 数据类型[属性]
修改字段位置 alter table 表名 modify 字段1 数据类型 first (字段1为第一个)||after 字段2 (将字段1插的字段2后面)
删除字段 alter table 表名 drop 字段名
删除表的外键约束 alter table 表名 drop foreign key 外键约束名
添加主键 alter table 表名 add constraint 约束名（主键） primary key 表名(主键字段)
添加外键 alter table 表名(从表) add constraint 外键名 foreign key(外键字段) references 关联表名:主表(关联字段):主表字段
添加外键：constraint 外键名 foreign key(外键字段) references 关联表名(关联字段);
ALTER TABLE t_student ADD CONSTRAINT t_cname1 FOREIGN KEY(cname) REFERENCES t_class(NAME);
t_comment 是评论表，n_id 是其字段，为n_id 添加外键，对应的关联表是t_news.
ALTER TABLE t_comment ADD CONSTRAINT n_id FOREIGN KEY(n_id) REFERENCES t_news(id)

数据操作
插入数据(单) insert into 表名[(字段名列表)] values(值列表);
插入数据(复) insert into 表名[(字段名列表)](可不写) values(值列表1),(值列表2),(值列表3)....;
数据更新 update set 字段1=值1,字段2=值2...[where条件]
建一个新表并复制数据 create table 新表名 as select * from 被复制表名 where;
通过结果集方式插入 insert into 需要复制数据的表名 select*from 被复制表名
清空表delete from 表名(是逐行删除，不会删除表结构，但是也不会重置自增);truncate table 表名(清空表，,快，会重置自增);drop table 表名(直接删除表结构)
删除数据(记录) delete from 表名 [where 条件]
修改数据(记录) update 表名 set sss=ssss where sss;
数据查询 
select 字段名 from 表名 where 条件 group by 分组的字段名 having （分组筛选条件）条件 order by 排序的字段名 正序asc 倒序desc
select 字段名（用，分割字段名） from 表名 where 条件 group by 分组的字段名 having （分组筛选条件）条件 order by 排序的字段名
正序asc 倒序desc limit 位置偏移量（默认可以不写，从0开始） 行数；
函数
聚合函数
select 函数（字段） as 中文 from 表名；
avg() 返回某字段的平均值
count() 返回某字段的行数
max() 返回某字段的最大值
min() 返回某字段的最小值
sum() 返回某字段的和
字符串函数
concat(‘str1‘,‘str2‘...) 字符串连接
insert(‘原字符‘,起始位置,终止位置,‘替换字符‘) 字符串替换
lower(‘str‘) 将字符串转为小写
upper(‘str‘) 将字符串转为大写
substring(字符,起始位置,终止位置) 字符串截取
日期时间函数
select 函数（对象）；
curdate() 获取当前时间年月日
curtime() 获取当前时间时分秒
now() 获取当前日期和时间
week(date) 返回日期date为一年的第几周
year(date) 返回日期date的年份
month（date） 返回日期的天数
hour(time) 返回时间time的小时值
minute(time) 返回时间time的分钟值
datediff(date1,date2) 返回日期参数1和2之间的天数
adddate(date,n) 计算日期参数date加上n天后的日期
数学函数
ceil(x) 返回大于或等于数值x的最小整数
floor(x) 返回小于或等于数值x的最大整数
rand() 返回0~1之间的随机数
子查询
select ...from 表1 where 字段1 比较运算符(子查询):用于嵌套在select,insert,update,delete 语句中
内连接(等值连接)
inner join 连接的表名 on 连接条件 （等值连接）
in子查询 查询结果为多头是可以用in代替= 比较运算符=只能返回单个数值
not in 反转 查询结果不为多条的
exists子查询 select ...from 表名 where exists(子查询语句)子查询结果为true时，执行外层代码 为false时外层查询不执行
not exists 子查询结果不为true时执行
嵌套子查询包括select from （where group having 可选子句 ）等子句
临时表 create temporary table 表名（查询语句）

事务
事务是作为单个逻辑工作单元执行的一系列操作
多个操作作为一个整体向系统提交，要么都执行，要么都不执行
事务是不可分割的工作逻辑单元
事务具有四个属性acid
原子性：一个完整的操作，要么全部执行完毕，要么都不执行
一致性：事务完成前后，数据要保持一致状态
隔离性：事务与事务在同时运行时，不依赖影响其他事务
持久性：事务结束后，提交到数据库中的修改会被永久保存
开始事务begin 或start transaction 提交事务 commit 回滚事务 rollback
事务默认状态是自动提交，关闭|开启set autocommit=0|1,关闭后需要手动提交

视图
视图是一张虚拟表，表示一张表的部分数据或多张表的综合数据，结构和数据是建立在对表的查询基础上
，视图不存放数据，仅仅是引用原始表中的数据，一个表可以创建多个视图
视图可以用来筛选表中的行，防止未授权用户访问数据，降低数据库的复杂程度，把多个物理数据库抽象为一个逻辑数据库
创建视图 create view view_name(字段别名) as select 字段名 from 表名(可以用来修改视图)
修改视图 alter view 视图 select 字段名 from 表名
更新视图 update 视图名 set 字段重新赋值
查看定义 desc 视图名
删除视图 drop view if exists view_name
查看视图 select 字段1，字段2... from view_name 查看视图信息 show table status like‘视图名‘\g查看详细信息show create view 视图名\g 查看所有视图 select*from information_schema.views;
查看所有视图 use information_schema select*from views\g;

索引
创建索引 create [unique 唯一索引| fulltext全文索引| spatial 空间索引] index_name on table_name(columm_name[length]..);
删除索引 drop index index_name on table_name 该表的所有索引都同时被删除
查看索引 show index from table_name
查看库中的全部索引 use 数据库 show index from student\g

数据库备份恢复
mysqldump命令 mysql备份工具将create和insert into语句保存到文本文件 属于dos命令
mysqldump [options 选项参数] -u username 用户名 -h host 登录主机名 -p password密码
dbname备份的数据库名 [tbname1备份的表名 不写是全部]>filen.sql备份后的文件名称
恢复数据库 mysql -u root -p 数据库名 <之前备份的文件名 (DOS命令,需要创建新的数据库后使用)
恢复数据库 source filename 在mysql服务中使用需创建新数据库并选择后使用
导出仅数据信息的目标文件select*from 表名 where 条件 into outfile 硬盘路径文本文件
导入数据文件 load data infile 之前导出的文本文件 into table 表名
常用参数选项
mysql数据类型

数字
tinyint 非常小的数 有符值(-128，127) 无符值(0，255) 1字节
smallint 较小的数据 有符值(-32 768，32 767) 无符值 (0，65 535) 2字节
mediumint 中等大小的数据 有符值 (-8 388 608，8 388 607) 无符值 (0，16 777 215) 3字节
int 标准整数 有符值(-2 147 483 648，2 147 483 647) 无符值(0，4 294 967 295) 4字节
bigint 较大的整数 有符值(-9,223,372,036,854,775,808，9 223 372 036 854 775 807) 无符值(0，18 446 744 073 709 551 615) 8字节
float 单精度浮点型 有符值(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 无符值0，(1.175 494 351 E-38，3.402 823 466 E+38) 4字节
double 双精度浮点型 有符值(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 无符值 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 8字节
decimal(m,d) 字符串型浮点数 有符值依赖于M和D的值 无符值 m+2字节 如果M>D，为M+2否则为D+2
字符
char[(m)] 定长字符串 m字节0-255
varchar[(m)]可变字符串 可改变长度0-65535
tinytext 微型文本串 0-255
text 文本串 0-65 535
mediumtext 0-16 777 215 bytes 中等长度文本数据
longtext 0-4 294 967 295 bytes 极大文本数据
日期
date yyyy-mm-dd 1000-01-01~ 9999-12-31
datetime yy-mm-dd hh:mm:ss 1000-01-01 00：00：00 ~9999-12-31 23：59：59
time hh:mm:ss -835:59:59 ~ 838:59:59
*timestamp yyyymmddhhmmss 1970年某时刻~2038年某时刻，精度为1秒
year yyyy 1901~2155