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;
更多语句
进入数据库:
1 | mysql -u root -p |
列出数据库:
1 | show databases; |
选择数据库:
1 | use databases_name; |
列出数据表:
1 | show tables; |
显示表格列的属性:
1 | show columns from table_name; |
导出整个数据库:
1 | mysqldump -u user_name -p database_name > /tmp/file_name |
例如:mysqldump -u root -p test_db > d:/test_db.sql
导出一个表:
1 | mysqldump -u user_name -p database_name table_name > /tmp/file_name |
例如:mysqldump -u root -p test_db table1 > d:/table1.sql
导出一个数据库结构:
1 | 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
导入数据库:
1 | source 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”
将文本文件导入数据表中(excel 与之相同)
1 | 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;
将数据表导出为文本文件(excel 与之相同)
1 | 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;
创建数据库时先判断数据库是否存在:
1 | create database if not exists database_name; |
例如:create database if not exists bbs
创建数据库:
1 | create database database_name; |
例如:create database bbs;
删除数据库:
1 | drop database database_name; |
例如:drop database bbs;
创建数据表:
1 | mysql> 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);
删除数据表中数据:
1 | delete from table_name; |
例如:
delete from bbs;
delete from bbs where id=2;
删除数据库中的数据表:
1 | drop table table_name; |
例如:
drop table test_db;
rm -f database_name/table_name.* (linux 下)
例如:
rm -rf bbs/accp.*
向数据库中添加数据:
1 | insert into table_name set column_name1=value1,column_name2=value2; |
例如:insert into bbs set name=”jack”,date_year=”1993-10-01”;
1 | insert into table_name values (column1,column2,...); |
例如:insert into bbs (“2”,”jack”,”1993-10-02”)
1 | insert into table_name (column_name1,column_name2,...) values (value1,value2); |
例如:insert into bbs (name,data_year) values (“jack”,”1993-10-01”);
查询数据表中的数据:
1 | select * from table_name; |
例如:select * from bbs where id=1;
修改数据表中的数据:
1 | update table_name set col_name=new_value where id=1; |
例如:update bbs set name=”tom”,age=18 where name=”jack”;
增加一个字段:
1 | alter table table_name add column field_name datatype not null default "1"; |
例如:alter table bbs add column tel char(16) not null;
增加多个字段:(column 可省略不写)
1 | 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;
删除一个字段:
1 | alter table table_name drop field_name; |
例如:alter table bbs drop tel;
修改字段的数据类型:
1 | 1. alter table table_name modify id int unsigned; |
修改一个字段的默认值:
1 | alter table table_name modify column_name datatype not null default ""; |
例如:alter table test_db modify name char(16) default not null “yourname”;
对表重新命名:
1 | alter table table_name rename as new_table_name; |
例如:alter table bbs rename as bbs_table;
1 | rename table old_table_name to new_table_name; |
例如:rename table test_db to accp;
从已经有的表中复制表的结构:
1 | create table table2 select * from table1 where 1<>1; |
例如:create table test_db select * from accp where 1<>1;
查询时间:
1 | select now(); |
查询当前用户:
1 | select user(); |
查询数据库版本:
1 | select version(); |
创建索引:
1 | alter table table1 add index ind_id(id); |
删除索引:
1 | drop index idx_id on table1; |
联合字符或者多个列(将 id 与”:”和列 name 和”=”连接)
1 | select concat(id,':',name,'=') from table; |
limit(选出 10 到 20 条)
1 | select * from bbs order by id limit 9,10; |
(从查询结果中列出第几到几条的记录)
增加一个管理员账号:
1 | grant all on *.* to user@localhost identified by "password"; |
创建表是先判断表是否存在
1 | create table if not exists students(……); |
复制表:
1 | create table table2 select * from table1; |
例如:create table test_db select * from accp;
授于用户远程访问 mysql 的权限
1 | grant all privileges on *.* to "root"@"%" identified by "password" with grant option; |
查看当前状态
1 | show status; |
查看当前连接的用户
1 | show processlist; |
(如果是 root 用户,则查看全部的线程,得到的用户连接数同 show status;里的 Threads_connected 值是相同的)!
替换字段中的部分数据
UPDATE 你的表名 SET 你要查找的字段明=REPLACE(你要查找的字段明,’你要替换的字符串’,’你需要替换成什么’);
案例代码:
1 | UPDATE cmf_article SET title=REPLACE(title,'hello','你好'); |