1. mysql -u root -p 123456; # 进行数据库的登录，cmd进入mysql的安装bin路径下
2. show databases; # 查看当前的数据库
show databases;
3. use oldboydb; # 使用oldboydb 数据库，用于进行数据库的切换
use oldboydb;
4.show tables; 查看当前数据库下的表格
show tables;
5.desc student; 查看表格的数据结构
desc student;
6. select * from student; 查看表格的数据
select * from student;
7.create user 'alex'@'localhost' identified by 'alex123' # 进行用户的创建
create user 'alex'@'localhost' identified by 'alex123';
8. grant all privileges on oldboydb.* to 'alex@localhost' # oldboydb数据库下的将所有的私有权限都授权给本地的alex用户
grant all privileges on oldboydb.* to 'alex'@'localhost';
9. show grants for 'alex'@'locahost' # 查看用户的权限
show grants for 'alex'@'localhost';
10. show columns from stundent # 查看student的表结构
show columns from student;
11. create database testdb charset utf8 # 创建testdb数据库
create database testdb charset utf8;
12. drop database testdb; # 将testdb数据库进行删除
drop database testdb;
13. show index from student; # 查看数据库的主键
show index from oldboydb;
14. 创建一个student表 # create table student(stu_id int not null auto_increment, name char(32) not null, age int not null, register_data date not null, primary key(stu_id)); # not null 不可以空，char(32) , int, date 表示类型, auto_increment表示自增, primary_key(stu_id)表示主键
create table student_1( -> stu_id int not null auto_increment, -> name char(32) not null, -> age int not null, -> register_data date not null, -> primary key(stu_id));
15. insert into student_1 (name, age, register_data) values ('alex', '22', '2015-02-06') # 插入一条数据，name=alex, age=22, register_data=2015-02-06
insert into student_1 (name, age, register_data) values ('alex', '22', '2016-02-03');
16. select * from student_1 limit 1 offset 2; 从数据第二条开始，向后数一条，即第三条数据
select * from student_1 limit 1 offset 2;
17. select * from student_1 where id = 2; 条件获取，获取id=2的数据
select * from student where id = 2;
18. select * from student_1 where register_data like '2016-02%'; # 进行模糊匹配，找出注册时间为2016-02月的数据
select * from student_1 where register_data like '2016-02%';
19. update student_1 set name = 'chengronghua', age = 33 where stu_id = 4; # 进行数据内容的修改
update student_1 set name='chengronghua', age=33 where stu_id = 4;
20. delete from student_1 where name = 'chengronghua' # 对查找出的记录进行删除
delete from student_1 where name = 'chengronghua';
21. select * from student order by stu_id desc; # 根据stu_id 进行降序desc，默认是升序
select * from student_1 order by id desc;
22. select name count(*) as stu_num group by name # 对名字进行统计，使用stu_num 作为索引值
select name, count(*) as stu_num from student_1 group by name;
23. select name, sum(age) from student_1 group by name; 对名字进行分类，统计单个名字的年龄之和
select name, sum(age) from student_1 group by name;
24. select name, sum(age) from student_1 group by name with rollup; 对名字进行分类，统计每个名字的年龄之和，同时将年龄进行总和
select name, sum(age) from student_1 group by name with rollup;
25. select coalesce(name, 'Total Age'), sum(age) from student group by name with rollup; # 对名字进行分类，统计每个名字的年龄之和，并求年龄总和，对特征Total Age
select coalesce(name, 'Total Age'), sum(age) from student_1 group by name with rollup;
26. alter table student_1 add sex enum('M', 'F'); # 在student_1 添加sex 属性，enum('M', 'F')构造选择属性
alter table student_1 add sex enum('M', 'F');
insert into student_1 (name, age, register_data, sex) values('Wangseng', 22, '2016-02-03', 'M') # 插入数据
27. alter table student modify name char(32); # 将名字的属性进行修改
alter table student modify sex char(32);
alter table study_record modify id int(11) primary key; 将id设置为主键
28. update student_1 set sex = 'M' where sex is null; # 如果性别为空，默认为男性
update student_1 set sex = 'M' where sex is null;
29. alter table student_1 change sex gender char(32) not null default 'X'; 对属性的名字进行修改，同时重新定义其属性和默认值
alter table student_1 change gender sex char(32) not null default 'X';
30. create table study_record(id int not null, day int not null, status char not null, stu_id int not null, stu_id int not null, key fk_student_key (stu_id), constraint fk_student_key foreign key (stu_id) references student(id)); # 构造study_record数据库，将stu_id 与学生的id做外键关联
create table study_record( -> id int not null, -> day int not null, -> status char not null, -> stu_id int not null, -> key fk_student_key (stu_id), -> constraint fk_student_key foreign key (stu_id) references student(id));
31. show create table study_record; # 查看创建study_record时的命令
show create table study_record;
32. delete from study_record where id = 3; # 删除数据的记录
delete from study_record where id = 3;
33. select * from student inner join student_1 on student.name = student_1.name # 找出两个数据集名字的交集
select * from student inner join student_1 on student.name = student_1.name;
34. select * from student left join student_1 on student.name = student_1.name # 找出student对应student_1 的 差集
select * from student left join student_1 on student.name = student_1.name;
35. select * from student right join student_1 on student.name = student_1.name # 找出student_1 对应的student 的差集
select * from student right join student_1 on student.name = student_1.name;
36. select * from student left join student_1 on student.name = student_1.name union select * from student right join student_1 on student.name = student_1.name; 求两个数据集对应的交集
select * from student left join student_1 on student.name = student_1.name union select * from student right join student_1 on student.name = student_1.name;
37. 使用begin 开启事务， 使用rollback 进行回滚, 使用commit 进行事务的传递
# 进行数据的回滚操作
begin; insert into student (name, register_data, sex) values('Jackes', '2016-02-03', 'M'); rollback; # 进行数据的提交 beigin; insert into student (name, register_data, sex) values('Jackes', '2016-02-03', 'M'); commit;
38. show index from student # 查看student里面的索引
show index from student
39. create index index_name on student(name) # 对名字创建主键
create index index_name on student(name);
40. drop index index_name on student; # 删除name主键
drop index index_name on student;
