mysql常用命令集合

1、修改表的列结构
①、修改列定义:Alter table…modify… alter table 表名 modify 列名 数据类型
②、添加列：alter table …ADD… alter table 表名 add 列名 数据类型 位置
③、修改列名和定义：alter table…change… alter table 表名 change 修改前的列名 修改后的列名 修改后的数据类型
④、删除列：alter table…drop… alter table 表名 Drop 列名
2、初始化auto_increment的值
Alter table 表名 auto_increment = 1
3、给列添加默认值
Alter tble 表名 modify 列名 数据类型 DEFAULT 默认值
4、创建索引
Create index 索引名 on 表名（列名）
5、显示索引
Show index from 表名
6、删除索引
Drop index 索引名 on 表名
7、平均值函数AVG()
8、总和函数SUM()
9、显示总和count()
10、显示信息的函数：SELECT Version()显示mysql的服务器版本，select database（）当前使用的数据库，select user()显示当前用户 select Charset(‘这个字符’)显示由参数指定的字符编码
11、连接字符串：concat（a,b,c）
12、从右取出RIGHT(empid,2),此时若empid=‘A101’,则为01，若left(empid,2)则为A1
13、substring（empid，2，3）则显示为101,第二个字符开始的连续三个字符
14、repeat(‘.’,age)‘.’被重复了age次
15、reverse（）反转函数，reverse(name)名字反转
16、分组显示group by
17、按组处理，先通过group by 进行分组，然后用having设置的条件适用于分组的结果值例如
Select empid,sum(sales) from tb group by empid having sum(sales)>=200
18、提取记录后分组在调用avg函数
Select empid,Avg（sales）from tb where sales>=50 group by empid;
19、分组后排序 select empid，avg(sales) from tb group by empid order by avg(sales) desc;
20、顺序where – group by – order by、
21、更改记录 update 表名 set 列名 = 设置的值 where 条件
22、创建表复制指定记录 create table 表名 select * from tb where xx
23、Insert into 已存在的表 select * from xx where xx
24、offset 偏移量，若表的某列数据排序为降序排列offset为1则去掉第一名，从第二名开始
25、删除数据delete from 表名 where 条件
26、将两个表连接起来union，显示连接得重复记录 union all
（select empid from tb where sales>=200）union all (select empid from tb1 where age>=35 )
27、内连接 select 表x.列名 from 表一 join 要连接的表2 on 表1的列=表2的列,连接键需要在两个表中都存在
28、外连接 若其中一个表与其他表的记录在不匹配的情况下也会被提取出来的连接方式，分为左外连接和右外连接
29、自连接，将表自身与自身做连接，select * from 表名 as 别名1 join 表名 as 别名2；
30、子查询 select * from tb where sales in(select max(sales) from tb);
31、复制表结构 create table tablename like tablename1
32、视图，create view 视图名 as select 列名 from 表名 where 条件；select * from v1；
33、更新视图，update v1 set name =‘xxx’ where name = ‘xx’
34、更新视图基表的内容也会被更新
35、插入不符合视图内容的数据，虽然不会在视图上显示，但会在基表内插入，若要防止这种情况，可以在视图创建时加上with check option如
Create view v4 as select empid，sales from tb where sales >100 with check option
36、替换视图，create or replace view v1 as select now（）
37、修改视图 alter view v1 as select name，age from tb1
38、删除视图 drop view 视图名，加条件 drop view 视图名 if exist v1
39、存储过程 create procedure 存储过程名()Begin sql语句1 sql语句2 end begin到end是存储过程的主体如Begin SELECT * FROM tb； select * from tb1 END
40、修改分隔符设置，避免执行不完整的存储过程 delimiter //
Mysql>delimiter //
Mysql>create procedure pr1()
->Begin
->select * from tb;
->select * from tb1;
->END
Query ok,0 rows affected
Mysql>delimiter ;
41、执行存储过程call pr1
42、create procedure 存储过程名（参数名 数据类型）
delimiter //
create procedure pr2（d int）
Begin
Select * from tb where sales>=d
END
//
Delimiter ;
43、显示存储过程的内容 show create procedure 存储过程名；
44、删除存储过程 drop procedure 存储过程名；drop procedure pr1
45、存储函数的思考方式和操作方法与存储过程基本相同，与存储过程稍微不同的一点是，存储函数在执行后会返回一个值。存储函数也称为用户自定义函数
46、创建存储函数
Create function 存储函数名（参数 数据类型）returns 返回的数据类型
Begin
Sql语句
Return 返回值·表达式
47、如何开启修改函数
Set global log_bin_trust_function_creators=1;
查看设置是否已经正确修改，执行下面的命令 show variables like‘log_bin_trust_function_creators’;
48、使用存储函数计算标准体重
delimiter ；
CREATE FUNCTION ful(height int) RETURN DOUBLE
BEGIN
RETURN heightheight22/10000;
END
//
Delimiter ;
48、存储函数声明变量
Mysql>delimiter //
Mysqll>create function f2() Return Double
Begin
->Declare r double;
->select AVG(sales) INTO r from tb;
->Return r;
->end
//
Mysql->delimiter ;
49、删除存储函数
DROP FUNCTION 存储函数名
50、显示存储函数名
Show create function 存储函数名；
51、创建触发器
Create trigger 触发器名 before （或者after）Delete等命令
On 表名 for each row
Begin
使用更新前（old.列名）或者更新后（new.列名）的处理
END
52、创建触发器的具体命令(表tb1中的记录删除前将数据存入tb1_from中)
Delimiter //
Create Trigger tr1 before delete on tb1 for each row
Begin
Insert into tb1_from values(old.empid,old.name,old,age)l;
End
//
Delimiter ;
Ps：触发器是自动启动的
52、展示触发器
Show triggers
53、删除触发器
Drop trigger 触发器名；
53、查看存储引擎
Show create table tb（\G）\G为更好的显示信息
54、修改存储引擎
Alter table 表名 EngINE = MYISAM
55、drop命令无法进行回滚
56、开启事务
Start transaction
57、执行rollback后，事务会被关闭
58、开始事务后，命令执行完不会自动提交需要添加commit才能提交
59、关闭自动提交功能set autocommit=0
60、开启自动提交功能 set autocommit=1
61、查看当前自动提交功能的模式select @@autocommit
62、事务的使用范围，启动事务后以下命令并不能执行回滚操作，如
Drop database
Drop table
Drop view
Alter table
63、添加主键
alter table 表名 add primary key(字段);
64、添加外键
ALTER TABLE student ADD FOREIGN KEY (s_cid) REFERENCES class(cid);
65、删除主键
alter table表名drop primary key;
66、导入数据库文件csv文件
需要在my.ini加上secure-file-priv =“”
若在mysql命令行输入select @@global.secure_file_priv显示为空，则说明启动成功
67、导入文件 LOAD DATA INFILE’文件路径’INTO TABLE 表名 选项的描述
如LOAD DATA INFILE ‘c：/data/t.csv’ INTO TABLE tblk FIELDS TERMINATED BY’,’
68、若要设置字符
LOAD DATA INFILE ‘c：/data/t.csv’ INTO TABLE tblk character set GB2312 FIELDS TERMINATED BY’,’
69、将数据写入文本文件 select * into outfile ‘文件名’ 选项的描述 from 表名
如select * into outfile “c:/DATA/out.csv” fileds terminated by ‘，’ from tb1；
70、将sql语句事先写入文本文件中，用source执行命令
Sourece 文本文件名，不需要分号
71、通过命令提示符执行sql语句
Mysql 数据库名 -u root -p root -e “mysql 监视器的命令”
Mysql db1 -u root -proot -e “source c:/data/sql.txt”
72、将mysql命令段作为批处理文件内容，如 outfile.bat（必须用一行来描述）
Mysql db1 -u root – proot -e “select * into outfile ‘c:/data/out.csv’fields ruminateded by ‘，’ from tb1”
73、将sql的执行结果保存到文件中
使用>将标准输出输出到别的地方，如dir>abc.txt
74、显示输出，使用type abc.txt
75、从命令行提示符上执行sql语句文本文件sql.txt,然后使用重定向将结果写入文本文件log.txt
Mysql -u root -proot -e ”source c:/data/sql.txt”>log.txt或
Mysql -u root -proot <c:\ata\sql.txt> c:\data\log2.txt
76、使用tee命令将sql语句的执行结果保存到文件中
tee 输出文件的名称，如tee log.txt
执行完tee log3.txt
则接下来的结果内容将记录到log3.txt中，取消则使用
Notee
77、导出数据库
Mysqldump -u root -p密码 数据库名>输出文件名称
78、导入数据库
Mysqldump -u root -p密码 create 数据库名<输出文件名称 – default-character-set=字符编码
79、加锁
Lock tables mytable READ
80、解锁
Unlock tables;