MySQL常用命令语句(建议收藏)


这篇文章主要介绍了Mysql命令大全,用于写mysql语句找不到命令!

常用的语句

  • 查询
select * from bbs where id=1;
  • 增加
insert into bbs (name,data_year) values ("jack","1993-10-01");
  • 修改
update bbs set name="tom",sex=1,age=18 where name="jack";
  • 删除
delete form bbs where id=2;
  • 字符串替换:
UPDATE \`category\` SET guid = REPLACE ( guid, 'articles', 'article' );

更多

  1. 进入数据库:

    mysql -u root -p
     mysql -h localhost -u root -p database_name
    
  2. 列出数据库:

    show databases;
    
  3. 选择数据库:

    use databases_name;
    
  4. 列出数据表:

    show tables;
    
  5. 显示表格列的属性:

    show columns from table_name;
     describe table_name;
    
  6. 导出整个数据库:

    mysqldump -u user\_name -p database\_name > /tmp/file_name
    

    例如:mysqldump -u root -p test\_db > d:/test\_db.sql

  7. 导出一个表:

    mysqldump -u user\_name -p database\_name table\_name > /tmp/file\_name
    

    例如:mysqldump -u root -p test_db table1 > d:/table1.sql

  8. 导出一个数据库结构:

    mysqldump -u user_name -p -d --add--table database\_name > file\_name
    

    例如:mysqldump -u root -p -d --add-drop-table test\_db > test\_db.sql

  9. 导入数据库:

    source file_name;
     
     mysql -u user\_name -p database\_name < file_name
    

    例如:

    source /tmp/bbs.sql
    
     source d:/bbs.sql
    
     mysql -u root -p bbs < "d:/bbs.sql"
    
     mysql -u root -p bbs < "/tmp/bbs.sql"
    
  10. 将文本文件导入数据表中(excel与之相同)

    load data infile "tables.txt" into table table_name;
    

    例如:

    load data infile "/tmp/bbs.txt" into table bbs
    
    load data infile "/tmp/bbs.xls" into table bbs
    
    load data infile "d:/bbs.txt" into table bbs
    
    load data infile "d:/bbs.xls" into table bbs
    
  11. 将数据表导出为文本文件(excel与之相同)

    select * into outfile "path\_file\_name" from table_name;
    

    例如:

    select \* into outfile "/tmp/bbs.txt" from bbs
    
    select * into outfile "/tmp/bbs.xls" from bbs where id=1;
    
    select * into outfile "d:/bbs.txt" from bbs;
    
    select * into outfile "d:/bbs.xls" from bbs where id=1;
    
  12. 创建数据库时先判断数据库是否存在:

    create database if not exists database_name;
    

    例如:create database if not exists bbs

  13. 创建数据库:

    create database database_name;
    

    例如:create database bbs;

  14. 删除数据库:

    drop database database_name;
    

    例如:drop database bbs;

  15. 创建数据表:

    create table <table_name> ( <column 1 name> <col. 1 type> <col. 1 details>,<column 2 name> <col. 2 type> <col. 2 details>, ...);
    

    例如:create table (id int not null auto\_increment primary key,name char(16) not null default "jack",date\_year date not null);

  16. 删除数据表中数据:

    delete from table_name;
    

    例如:

    delete from bbs;
    
    delete from bbs where id=2;
    
  17. 删除数据库中的数据表:

    drop table table_name;
    

    例如:drop table test_db;

    rm -f database\_name/table\_name.* (linux下
    

    例如: rm -rf bbs/accp.*

  18. 向数据库中添加数据:

    insert into table_name set column\_name1=value1,column\_name2=value2;
    

    例如:insert into bbs set name="jack",date_year="1993-10-01";

    insert into table_name values (column1,column2,...);
    

    例如:insert into bbs ("2","jack","1993-10-02")

    insert into table\_name (column\_name1,column_name2,...) values (value1,value2);
    

    例如:insert into bbs (name,data_year) values ("jack","1993-10-01");

  19. 查询数据表中的数据:

    select * from table_name;
    

    例如:select * from bbs where id=1;

  20. 修改数据表中的数据:

    update table_name set col\_name=new\_value where id=1;
    

    例如:update bbs set name="tom",age=18 where name="jack";

  21. 增加一个字段:

    alter table table_name add column field_name datatype not null default "1";
    

    例如:alter table bbs add column tel char(16) not null;

  22. 增加多个字段:(column可省略不写)

    alter table table_name add column filed_name1 datatype,add column filed_name2 datatype;
    

    例如:alter table bbs add column tel char(16) not null,add column address text;

  23. 删除一个字段:

    alter table table_name drop field_name;
    

    例如:alter table bbs drop tel;

  24. 修改字段的数据类型:

    alter table table_name modify id int unsigned;//修改列id的类型为int unsigned
    
    alter table table_name change id sid int unsigned;//修改列id的名字为sid,而且把属性修改为int unsigned
    
  25. 修改一个字段的默认值:

    alter table table_name modify column_name datatype not null default "";
    

    例如:alter table test_db modify name char(16) default not null "yourname";

  26. 对表重新命名:

    alter table table_name rename as new\_table\_name;
    

    例如:alter table bbs rename as bbs_table;

    ``mysql rename table old\_table\_name to new\_table\_name;

    例如:rename table test_db to accp;

  27. 从已经有的表中复制表的结构:

    create table table2 select * from table1 where 1<>1;
    

    例如:create table test_db select * from accp where 1<>1;

  28. 查询时间:

    select now();
    
  29. 查询当前用户:

    select user();
    
  30. 查询数据库版本:

    select version();
    
  31. 创建索引:

    alter table table1 add index ind_id(id);
    create index ind_id on table1(id);
    create unique index ind_id on table1(id);//建立唯一性索引
    
  32. 删除索引:

    drop index idx_id on table1;
    alter table table1 drop index ind_id;
    
  33. 联合字符或者多个列(将id与":"和列name和"="连接)

    select concat(id':',name,'=') from table;
    
  34. limit(选出10到20条)

    select * from bbs order by id limit 9,10;
    (从查询结果中列出第几到几条的记录)
    
  35. 增加一个管理员账号:

    grant all on *.* to user@localhost identified by "password";
    
  36. 创建表是先判断表是否存在

    create table if not exists students(……);
    
  37. 复制表:

    create table table2 select * from table1;
    

    例如:create table test_db select * from accp;

  38. 授于用户远程访问mysql的权限

    grant all privileges on *.* to "root"@"%" identified by "password" with grant option;
    

    或者是修改mysql数据库中的user表中的host字段

    use mysql;
    select user,host from user;
    update user set host="%" where user="user_name";
    
  39. 查看当前状态

    show status;
    
  40. 查看当前连接的用户

    show processlist;
    

    (如果是root用户,则查看全部的线程,得到的用户连接数同show status;里的 Threads_connected值是相同的)