MySQL常用操作

1.查看用户

select user();

 2.更改账户密码

use mysql;
update user set password = password('pwd') where user = 'root' and host='localhost';

 3.查看所有数据库

show databases;

 4.查看某个数据库的所有数据表

show tables from db_test;

 5.查看某个数据表的结构

use db_test;
desc tb_test;

 6.创建数据库

create db_test character set utf8;

7.创建数据表

use db_test;
create table tb_test(
          id int not null   auto_increment primary key,
          name nvarchar(50) not null,
          password nvarchar(40) nut null);        

8.alter语句，有时alter table必须大写才行,alter的功能有添加字段，删除字段，添加主键，删除主键，更改表明，修改字段属性以及更改数据库字符集。

ALTER TABLE tb_test add column lastime int not null;  
ALTER TABLE tb_test drop column lastime;                          
ALTER TABLE employee add primary key(id);                                      
ALTER TABLE t1 CHANGE name user bigint not null;                                
ALTER TABLE userinfo modify name nvarchar(50) NOT NULL;                
ALTER TABLE userinfo rename us;                                               
ALTER TABLE pet1 add primary key(id);                                            
ALTER TABLE pet1 drop primary key;                                               
ALTER DATABASE db1 character set utf8

9.查看字符集

show character set;

10.查看字符集相应的校对

SHOW COLLATION LIKE 'utf8%';

11.select语句

select * from dbo.Me ;
select * from dbo.Me where id between 2 and 3;
select * from dbo.Me order by name asc;                                           
select * from dbo.Me order by name desc,id asc;                                   
select * from dbo.Me where age >30 order by name desc;  
select name,password from userinfo                                                
select name as 姓名,password as 密码 from dbo.USErinfo;                           
select * from where name is null;                                                 
select top 10 * from dbo.Me;      -sql server
select top 10 * from dbo.Me where ID = 1; -sql server
select * from dbo.Me where name in ('root','xtyang');
select * from dbo.Me where name like '%yang%';                                    
select * from dbo.Me where name like '_ang';                                      
select age,count(*) from dbo.Me group by age;  

子查询

select * ,(select num from t1 where t1.id = userinfo.id) as num from userinfo;
//t1和 userinfo都是表格

 

12.update语句

update t1 set name ='root',pwd='xtyang' where name ='root';
update t1 set name='xtyang' where name='root';
update t1 set age = 30;
update t1 set name = 'xyang' ,age =30;
update t1 set age = age +1;
update t1 set name = N'中国人';  

13.delete语句

delete from t1 where name = 'root';
delete from t1 ;

14.insert语句

insert into t1(ID ,name,pwd) values(1,'xtyang','root') ;
insert into t1 values(2,'root','root');
insert into userinfo(name,password) values('root','root');                        
insert into userinfo(name,password) values('root','root'),('xtyang','xtyang');