Mysql常用命令汇总
文章目录
- 基本命令
- 连接数据库
- 查看数据库
- 使用数据库
- 查看表
- 查看表结构
- 显示表的细节
- 修改用户密码
- 解决MySQL 5.5,出现乱码问题
- 声明字符集
- DDL
- 创建表
- 查看表中的结构
- 修改表
- 添加表中的列
- 删除表中的列
- 修改表中的列中属性的数据类型
- 修改表名
- 修改表的字符集为gbk
- 修改表中的列名
- 删除表：
- 重命名
- 创建主键
- DML
- 普通查询
- 单表全字段查询
- 单表个别字段查询
- 多表查询
- 条件查询
- 单表条件查询
- 多表条件查询
- 嵌套查询
- 并查询
- 交查询
- 插入
- 全字段插入
- 多全字段插入
- 个别字段插入
- 更新
- 删除
- 删除单个数据
- 删除表中全部数据，数据与结构丢失
- 删除表，数据丢失，结构保留
- DQL
- 查询指定列的数据
- 条件查询
- 模糊查询
- 对数据排序
- 升序（asc，默认）
- 降序（desc）
- 聚合函数
- 日期类型
- where 用法
- group用法
- having用法
- order by用法
- limit查询结果数限制
- 索引
- 什么是索引
- 索引的优点
- 索引的缺点
- 索引的类型
- 普通索引
- 唯一索引
- 主键索引
- 聚集索引
- 非聚集索引
- 索引举例
- 删除表数据drop、truncate和delete的用法
DDL（data definition language）：数据定义语言，用来定义数据库对象；创建库、表、列等。 DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。 是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
DQL ：数据查询语言，用来查询数据。
基本命令
连接数据库
mysql -u root -p查看数据库
show databases;使用数据库
use data_analysis;查看表
show tables;查看表结构
desc table显示表的细节
show create table 表名;
修改用户密码
方法一：
5.7以前：
5.7以后：
update user set authention_string=password('1234') where user='root' and Host = 'localhost';然后刷新MySQL的系统权限相关表：flush privileges;
方法二：
修改密码，使用bin文件中的语句，前提是先配好坏境变量：
解决MySQL 5.5,出现乱码问题
SET character_set_client =gbk; //设置客服端的编码
SET character_set_results =gbk; //设置服务器端结果返回的编码
SET character_set_connection =gbk; //设置客服端与服务端连接时的编码
声明字符集
set names 模式名;（字符集不同会报错） set names gbk;DDL
创建表
create tables 表名(内容) create table class( stu int, name varchar（20）, age int );查看表中的结构
desc 表名;
修改表
添加表中的列
alter table 表名 add 列名 数据类型；
alter table skt add(score int not null);删除表中的列
alter table 表名 drop 列名；（若只剩最后一个字段则无法删除）
alter table skt drop column score;修改表中的列中属性的数据类型
alter table 表名 modify 属性名（id） 数据类型;
修改表名
rename table 原表名 to 新表名；
修改表的字符集为gbk
alter table 表名 character set 字符集名称；
修改表中的列名
alter table 表名 change 原始列名 新列名 数据类型；
alter table skt change name score int not null;删除表：
drop table 表名；
重命名
alter table 旧表名 rename to 新表名；
创建主键
alter table 表名 add constraint 主键名 primary key(列名);
DML
在sql命令格式使用的先后顺序上，group by先于order by。
普通查询
select命令的标准格式如下：
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression_r ]
[ HAVING search_condition ]
[ ORDER BY order_expression_r [ ASC | DESC ] ]
单表全字段查询
查询表中的所有数据
select * from 表名;
单表个别字段查询
select 字段一,字段二 from 表名;
select username from t1;多表查询
select 表一字段,表二字段,表三字段,…… from 表一,表二,表三,……；
select skt.username,skt.score,ig.sno from skt,ig;条件查询
单表条件查询
select 字段1,字段2 from 表名 where 条件；
select * from skt where socre>90;多表条件查询
select 表一字段，表二字段 from 表一，表二 where 条件；
select skt.username,skt.score,ig.sno from skt,ig where skt.id=ig.id;嵌套查询
select 字段一,字段二…… from 表名 where 条件（查询）;
select name from ig where id=(select id from skt where id=90);并查询
(select id from skt )union(select id from ig);交查询
select id from skt where id in (select id from ig);插入
全字段插入
insert into 表名 (id,Sname,Ssex) values (1,'xxx','male');
insert into msg (id，title，name，content) values （1，‘初来乍到’，‘张三’，‘我刚来’）多全字段插入
insert into 表名 （列1，列2...列N） values （值1，值2...值N）;（允许一次插入多行，在内容处增加）
个别字段插入
insert inton 表名(字段名) values(v1),(v2);
insert into skt(id) values(004);更新
update 表名 set 更改的字段名=值 where 条件；
把一个表中所有学生成绩都改变为 90 ：update 表名 set score 列名=90;
修改指定学生的成绩：update 表名 set score=60 where name='zws';
修改指定学生的成绩和年龄：
删除
删除单个数据
delete from 表名 where 列名=值; //单个数据
delete from ig where id=12;删除表中全部数据，数据与结构丢失
delete from 表名; //表中全部数据 结构丢失删除表，数据丢失，结构保留
truncate table 表名; //表中全部数据 空表DQL
查询指定列的数据
select 列名1，列名2，···from 表名;
条件查询
select * from 表名 where 列名=(>,>=,<,<=,!=)值 （and,or....);in(set) 固定的范围值;模糊查询
select * from 表名 where 列名 like '__'(几个下划线，表示几个字母或数字);
模糊查询：用到的词:’‘like’’,’’%’’,"__".
“like”->像，"%"->通配任意字符，“_”->单个字符。
实例：
（1）查询名字（可类比）有5个字符组成，并且第五个字符是b:
（2）查询名字（可类比) 开头是 z ：
select * from 表名 where 列名 like 'z%';(% 表示后面的）;（3）查询名字中包含 z 的信息：
select * from 表名 where 列名 like '%z%';（4）查询名字中第二个字母为 z 的信息：
select * from 表名 where 列名 like '_z%';（5）查询单独一个属性的信息：
select 列名 from 表名; select *,age+score from 表名; select *,ifnull(age,0) + ifnull(score,0) as 别名 from student;//为空的变为零（6）过滤重复的值
select distinct 列名 from 表名; //去掉重复的信息对数据排序
升序（asc，默认）
select * from 表名 order by 列名 （或asc); //默认是升序;降序（desc）
select * from 表名 order by 列名 desc; //降序 select * from 表名 order by 列名 desc,列名 desc;聚合函数
count（）： 统计指定不为NULL的记录行数 ，count(字段名)不包含NULL；
select conut(*) from 表名；max()：计算指定列的最大值,max(colunm)返回字母序最大的，返回数值最大的
select max(name) from skt;avg() ：计算指定列的平均值，如果指定的列不是数值型，那么计算为0,对字符串和时间无效。
select avg(score) from skt;min()：计算指定列的最小值,min(colunm)返回字母序最小值，返回数值最小值
select min(name) from skt;sum() ：计算指定列的数值和,对字符串和时间无效
select sum(score) from t1;日期类型
year类型：eg.'2018'; data类型：eg.'1992-08-12'; time类型：eg.'12:12:32'; datetime类型:eg.'1987-05-03 14:32:08'where 用法
where 用法: where id>2;where id!=2; where id in (值1，值2...值N);(查询id在1到N中的数据) where id between A and B;（查询在A到B中的数据） （where用来找位置）group用法
group通常与统计函数连用；
统计函数：max（最大），min（最小），sum（求总和），avg（求平均），count（求总行数）。
having用法
select 列名1，列名2 from 表名 where 位置 having 筛选条件; select name,id,price from goods where id=3 having price>200; （having用来筛选）order by用法
order by 列名;（升序排列） order by 列名 desc;（降序排列） order by的多字段排序:order by 列1,列2;（先按列1排再按列2排）limit查询结果数限制
在语句最后，起限制条目的作用。 limit offset n; 指跳过前offset个，然后再取n个。 select * from ig limit 2;索引
什么是索引
索引，使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。
在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。
索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识值的数据页的逻辑指针清单。
索引的优点
1、大大的加快了数据的检索速度；
2、创建唯一索引，保证数据库表中每条数据的唯一性；
3、加快表与表之间的连接；
4、使用排序和分组检索数据时，可以显著的加快排序和分组的时间；
索引的缺点
1、索引需要占物理空间；
2、当对表中的数据进行增删改是，索引也要动态维护，降低了数据库的维护性；
3、在创建索引时，必须要确定要创建的索引列和索引类型；
索引的类型
普通索引
这是最基本的索引，而且没有唯一性之类的限制
创建普通索引：create index 索引名 on 表名（列名 asc/desc）;（升序/降序索引）
唯一索引
唯一索引是不允许其中任何两行具有相同索引值的索引。
当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。
数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。
创建唯一索引：create unique index 索引名 on 表名 (列名1,列名2,……)；
主键索引
数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。建议建立唯一索引时，直接创建主键索引就好了。
聚集索引
该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引类似于电话簿，按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。
--创建 CREATE CLUSTERED INDEX [IndexName] ON [TableName]([ColumnName],[ColumnName],...) --删除 DROP INDEX [TableName].[IndexName]聚集索引使用注意事项
(1) 定义聚集索引键时使用的列越少越好；
(2) 使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=,可以按物理顺序更快的返回一个范围;
(3) 被连续访问的列;
(4) 返回大型结果集的查询；
(5) 经常被使用表联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能;
(6) OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引;
聚集索引不适用于
(1) 频繁更改的列 。这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值，修改了一行数据，改行物流顺序改变了，其他行的物流顺序也要跟着改变）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的;
(2) 宽键。来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内;
非聚集索引
数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。非聚集索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。对于非聚集索引，可以为在表非聚集索引中查找数据时常用的每个列创建一个非聚集索引。有些书籍包含多个索引。例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。
--创建 CREATE NONCLUSTERED INDEX [IndexName] ON [TableName]([ColumnName],[ColumnName]...) --删除 DROP INDEX [TableName].[IndexName]索引举例
删除索引：drop index 索引名 on 表名;
drop index stuIndex on student; where型的子查询：指把内层查询的结果作为外层查询的比较条件。 from型的子查询：把内层的查询结果当成临时表，供外层sql再次查询 exists型的子查询：把外层的查询结果，拿到内层，看内层的查询是否成立。用SQL语言CREATE INDEX语句定义表STUDENT的SNAME字段的降序索引;
Create unique index stusname on student(sname desc);用SQL语言CREATE INDEX语句定义表SC的GRADE字段的升序索引;
Create unique index scgrade on sc(grade);用SQL语言CREATE INDEX语句定义表SC的GRADE字段的升序索引;
Create unique index scgrade on sc(grade);int(19):
指定数字的显示宽度为19,与实际存储数值的范围无关float(7,2):
7是显示宽度指示器,指定显示的浮点数为7位数字(与float实际存储值的范围无关), 2 代表小数点后只有两位小数(第三位会四舍五入后插入数据库)decimal(7,2):
7表示数值的精度,即实际保存到数据库的有效数字的总个数; 2代表小数点后的位数(同上)删除表数据drop、truncate和delete的用法
drop
drop table tb --tb表示数据表的名字删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。
例如:一个班就是一个表,学生就是表中的数据,学生的职务就是定义
drop table class,就是把整个班移除.学生和职务都消失
truncate
truncate table tb删除内容、释放空间但不删除定义。与drop不同的是,他只是清空表数据而已，保留表的数据结构。
同样也是一个班,他只去除所有的学生.班还在,职务还在,如果有新增的学生可以进去,也可以分配上职务
注意:truncate 不能删除行数据,要删就要把表清空
关于truncate的总结:
truncate table 在功能上与不带 WHERE 子句的 delete语句相同：二者均删除表中的全部行。
但 truncate 比 delete速度快，且使用的系统和事务日志资源少。
delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。所以可以对delete操作进行roll back
1、truncate 在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令Delete将被撤销，而 truncate 则不会被撤销。
2、truncate 是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对 truncate 使用ROLLBACK命令。
3、truncate 将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过 truncate 操作后的表比Delete操作后的表要快得多。
4、truncate 不能触发任何Delete触发器。
5、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。
6、不能清空父表
delete
delete table tb 或 delete table tb where 条件
虽然也是删除整个表的数据,但是过程是痛苦的(系统一行一行地删,效率较truncate低)
删除内容不删除定义，不释放空间。三兄弟之中最容易欺负的一个
总结
以上是生活随笔为你收集整理的Mysql常用命令汇总的全部内容，希望文章能够帮你解决所遇到的问题。
- 上一篇： 如何在屏幕实时显示自己键盘的输入字符？
- 下一篇： mysql从库应用负载_线上MySQL数