命令行输入cmd,进入MySQL/bin目录
mysql -u root -p #登录
show databases; #列出所有数据库
use xxx; #进入数据库xxx
show tables; #列出数据库所有表
desc yyy; #列出数据库xxx中表yyy的结构
select * from yyy\G; #列出yyy表中所有数据
grant all on test.* to 'alex'@'localhost' identified by'alex7314';
#给用户alex授权(所有权限all,包括增删改查)访问localhost下数据库test下的所有表(.*中的*表
示所有表),并且访问密码设置为alex7314。
show grants for alex; #展示用户alex的权限
先进入mysql库;输入select * from user\G 展示所有用户权限
show columns from yyy; #作用同desc yyy;
create database test; #创建数据库test #默认拉丁语系
drop database test; #删除数据库test
create database test charset utf8; #创建数据库test,并且设定编码格式为utf-8
show create database test; #查看表test的编码格式
#创建表student(需要先进入数据库)
create table student(
-> id int auto_increment,
-> name char(32) not null,
-> age int not null,
-> register_date date not null,
-> primary key (id));
#在student表中插入数据
insert into student (name,age,register_date) values("ZhangYang",3,"2016-06-22");
select * from student limit 2 offset 1;#列出student表中从1之后开始(2号开始)的两条数据
select * from student where id >2 and age >13; #查询id号大于2,age大于13的数据条
select * from student where register_date like "2016-06%"; #模糊查询2016-06
select * from student where name like "Zhang%"; #模糊查询姓Zhang的数据条
update student set name="AiGuo",age=21 where id=5;#修改id=5的数据条
delete from student where name ="AiGuo"; #删除name="AiGuo"的数据条
select * from student order by id desc; #将表按id的降序排列
select * from student order by id; #将表按id的升序排列(表格默认升序排列)
select name,count(*) from student group by name;#将表按name分组并将相同name的数据条计数为count(*)
select name,count(*) as num from student group by name;#同上句一样,只是将count(*)命名为num
alter table student add sex enum("M","F"); #在student表中添加sex字段,并只识别M,F
alter table student drop sex; #删除student表中的sex字段