mysql学习入门到提高篇

记录一下mysql学习和用到的命令行，系统是ubuntu16.04

第一部分：安装篇

1.mysql安装

sudo apt-get install mysql-server mysql-client

根据提示输入密码和确认密码

2.检验安装是否成功，成功则显示mysql的版本号

mysql --version //查看版本

service mysql start //启动服务
service mysql stop //停止服务
service mysql restart //服务重启

3.允许远程链接数据库

cd  /etc/mysql/mysql.conf.d
sudo gedit mysqld.cnf 

将bind-address=127.0.0.1 注释

mysql -uroot -p　//登录mysql,输入密码

mysql> grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
mysql> flush privileges
    -> ;
//之后重启mysql 

service mysql restart

4.现在试一下，找一个win主机或打开虚拟机，下载navicat,地址：https://pan.baidu.com/s/1pLuSoJ5

打开后输入ubuntu 的ip地址，mysql数据库用户名和密码．远程连接成功后即可看到在你电脑上的数据库

第二部分：基础命令入门篇
１．数据库登陆，退出

mysql -uroot -p　 //登录数据库
quit(exit) 　//退出

２．创建一个数据库并展示

create database NewTab charset=utf8;
show databases;

３．使用一个数据库并查看和创建一张表

mysql> use NewTab;
mysql> show tables；
mysql> create table students(id int auto_increment primary key not null,name varchar(10) not null,gendar varchar(1) not null,isDelete varchar(1) not null);

４．插入数据并更新

mysql> insert into students (id,name,gendar,isDelete) values('0','jim',1,0),('0','ken',2,1);
mysql> update students set gendar=1 where isDelete==1;

５．逻辑删除

mysql> select * from students where isDelete=0;

６．条件查询

mysql> select * from students where id>2 and gendar=1 or name='lily';

７．模糊查询

mysql> select * from students where name like 'j%';

８．分组查询

mysql> select gendar as sex,count(*) from students group by gendar having gendar=1;

where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
having是对group by的结果进行筛选

９．排序查询

mysql> select * from students order by isDelete asc;
mysql> select * from students order by isDelete desc;

１０．复合查询

mysql> select gendar,name from students where id>1 group by gendar,name having gendar=1 order by gendar desc limit 1,2;

第三部分：提高篇

有三个表结构如下：

１．查询姓名，学科，分数　

mysql> select students.name,subjects.subject,scores.score from scores inner join students on students.id=scores.sname  inner join subjects on subjects.sid=scores.ssub;

２．查询每个学生的平均分，总分

mysql> select students.name,avg(scores.score) from scores inner join students on students.id=scores.sname group by students.name;

mysql> select students.name,sum(scores.score) as score from scores inner join students on students.id=scores.sname group by students.name;

３．查询单个科目最高分和最低分

mysql> select subjects.subject as subject,max(scores.score),min(scores.score) from scores inner  join subjects on subjects.sid=scores.ssub group by subjects.subject;

４．创建一个视图

mysql> create view stu_sub_sco as select students.*,scores.score,subjects.subject from scores inner join students on students.id=scores.sname inner join subjects on subjects.sid=scores.ssub;

5.修改一个视图

mysql> alter view stu_sub_sco as select students.*,scores.score,subjects.subject from scores inner join students on students.id=scores.sname inner join subjects on subjects.sid=scores.ssub where isDelete=0;

６．创建外键

mysql> create table infoscores( id int primary key auto_increment not null, studentid int, subjectid int, score int not null, foreign key(studentid) references students(id),foreign key(subjectid) references subjects(id));

创建外键有两点：１．数据库引擎必须一致．２．引用主键且类型长度一直．