mysql常用语句总结

连接数据：mysql -h host -u root -p password

修改密码：mysqladmin -u root -p old_password password new_password

增加用户：create user ‘user1‘@‘%‘ identified by ‘password‘  

用户授权：grant select,update,insert,delete on database.table to ‘user1‘@‘%‘  

2.数据库操作

查所有库：show databases  

切换数库：use database_name  

新数据库：create database database_name  

删数据库：drop database database_name  

3.数据表操作

查所有表：show tables  

查表结构：describe table_name  

新数据表：create table (field1 not null auto_increment,field varchar(20) not null，primary key (‘id‘))

删数据表：drop table_name

转换存储：alter database.table engine=‘MyISAM‘

表名修改：alter table old_name rename to new_name  

列出字段：show field from table like ‘%word%‘  

所有表格：select * from information_schema.tables  

4.记录操作类

增加记录：insert into table (field1,field2,field3) values (‘value1‘,‘value2‘,‘value3‘)  

修改记录：update table set field1=‘value1‘,field2=‘value2‘ where id=1  

删除记录：delete from table where id=1  

查询记录：select field1,field2,field3 from table where id>0 order by id asc

连接外表：select a.*,b.* from table1 as a join table2 as b on a.id=b.aid

mysql常用语句总结

标签：