mysql的基本使用

用root账号管理其他用户

#创建用户
mysql> create user 'jasonhz'@'localhost' identified by '123456';
#给用户授权
mysql> grant all privileges on myschool.* to 'jasonhz'@'localhost';
#刷新授权
mysql> flush privileges;
#更改用户密码
mysql> set password for 'jasonhz'@'localhost' = password('111222');
#删除用户
mysql> drop user 'jasonhz'@'localhost';

SQL语句(Structured Query Language 结构化查询语言)

• DDL (Data Definition Language 数据定义语言) 如：CREATE DROP ALTER
• DML（ Data Manipulation Language 数据操纵语言) 如： INSERT UPDATE DELETE
• DQL（Data Query Language 数据查询语言) 如：SELECT
• DCL（Data Control Language 数据库控制语言) 如：GRANT COMMIT ROLLBACK

DDL (Data Definition Language 数据库定义语言)

#创建数据库
mysql> create database if not exists hzdb;
#使用数据库
mysql> use hzdb;
#查看所有可访问的数据库
#除了hzdb是自己创建的，mysql系统中有四个默认的库 information_schema 、 mysql、performance_schema、sys 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hzdb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
#删除数据库
mysql> drop database hzdb;

#查看数据库支持的引擎
mysql> show engines;
#查看数据库默认的引擎
mysql> show variables like '%storage_engine%';
#查看当前数据库的编码
mysql> show variables like '%char%';

#创建表
mysql> create table person(
    -> `id` int(4) primary key auto_increment comment '编号',
    -> `name` varchar(50) not null default '无名氏' comment '姓名',
    -> `address` varchar(100) comment '地址' 
    ->  )engine=innodb default charset=utf8 comment='人员信息';
    
#查看所有表
mysql> show tables;
#查看表的结构
mysql> desc person;
+---------+--------------+------+-----+-----------+----------------+
| Field   | Type         | Null | Key | Default   | Extra          |
+---------+--------------+------+-----+-----------+----------------+
| id      | int(4)       | NO   | PRI | NULL      | auto_increment |
| name    | varchar(50)  | NO   |     | 无名氏    |                |
| address | varchar(100) | YES  |     | NULL      |                |
+---------+--------------+------+-----+-----------+----------------+

#修改表名
mysql> alter table person rename student;
#添加字段
mysql> alter table student add `phone` varchar(13) comment '电话';
#修改字段属性，不能修改表名
mysql> alter table student modify `phone` int(11);
#修改字段，同时修改表名
mysql> alter table student change `phone` `tel` varchar(50) not null;
#删除字段
mysql> alter table student drop `tel`;

#删除表，删除数据要慎重
mysql> drop table student;

DML（ Data Manipulation Language 数据操纵语言)

#添加数据
mysql> insert into student (`id`,`name`) values ('1','张三');
mysql> insert into student (`id`,`name`) values ('4','张三'),('6','李四'),('8','王五');
#修改数据
mysql> update student set `name`='李靖' where id=1;
#删除数据
mysql> delete from student where id=1;
#删除所有数据，两种方法
mysql> delete from student;
mysql> truncate table student;

DQL（Data Query Language 数据查询语言)

(有需要sql脚本做练习的请联系我)

#基本查询：
#查询student表中全部信息
mysql> select * from student;

#通过student表，查询`studentNo`,`studentName`,`phone`,`address`,`birthday`字段的信息 
mysql> select studentNo, studentName, phone, address, birthday from student;

#通过student表，查询`studentNo`,`studentName`,`phone`,`address`,`birthday`字段的信息，使用as重命名列名显示
mysql> select studentNo as 学号,studentName as 姓名,phone as 电话,address as 地址,birthday as 生日 from student;

#查询email值为null的空行
mysql> select * from student where email is null;

#把成绩都降低10%后加5分，再查询及格成绩，并按照成绩从高到低排序（order by）
mysql> select studentNo as 学号, (studentResult*0.9+5) as 成绩 from result where (studentResult*0.9+5)>60 order by studentResult desc;

#查询所有年级编号为1的学员信息，按学号升序排序，从第5条记录开始显示4条数据（limit）
mysql> select * from student where gradeId=1 order by studentNo asc limit 4,4;

#编写SQL语句，查看年龄比“李斯文”小的学生，要求显示这些学生的信息 
mysql> select studentName, birthday from student where birthday>(select birthday from student where studentName='李斯文');


#查询参加最近一次Logic Java考试成绩的学生的最高分和最低分
mysql> select max(studentResult) 最高分,min(studentResult) 最低分 from result where examDate=(select max(examDate) from result where subjectNo=(select subjectNo from subject where subjectName='LogicJava')); 


#查询“LogicJava”课程考试成绩为60分的学生名单（采用 IN 子查询 ）
mysql> select studentNo, studentName from student where studentNo in(select studentNo from result where subjectNo=(select subjectNo from subject where subjectName='LogicJava'));

#查询参加“LogicJava”课程最近一次考试的在读学生名单
mysql> select studentNo, studentName from student where studentNo in( select studentNo from result where examDate=(select max(examDate) from result where subjectNo=(select subjectNo from subject where subjectName='LogicJava')));

#查询未参加“LogicJava”课程最近一次考试的在读学生名单（not in）
mysql> select studentNo, studentName from student where studentNo not in( select studentNo from result where examDate=(select max(examDate) from result where subjectNo=(select subjectNo from subject where subjectName='LogicJava')));

#查询大一学期开设的课程
mysql> select subjectName from subject where gradeId=(select gradeId from grade where gradeName='大一');

#查询未参加“HTML”课程最近一次考试的在读学生名单
mysql> select studentNo, studentName from student where studentNo not in( select studentNo from result where examDate=(select max(examDate) from result where subjectNo=(select subjectNo from subject where subjectName='HTML')));

#高级查询：
#查看没有参加考试的学生
mysql> select studentName from student where studentNo in (select studentNo from result where studentResult is null);

#检查“LogicJava”课程最近一次考试成绩,如果有80分以上的成绩，显示分数排在前5名的学员学号和分数
mysql> select studentNo, studentResult from result where exists (select studentResult from result where examDate=(select max(examDate) from result where subjectNo=(select subjectNo from subject where subjectName='LogicJava')) and studentResult > 80) order by studentResult desc limit 5;

#检查“Logic Java”课程最近一次考试成绩,如果全部通过考试（60分及格）计算的该次考试平均分减5分
mysql> select avg(studentResult)-5 平均分 from result where exists(select studentResult from result where examDate=(select max(examDate) from result where subjectNo=(select subjectNo from subject where subjectName='LogicJava')) and studentResult > 60);

#如果有大一的学生，就查询参加大一学科考试的学员学号、科目编号、考试成绩，考试时间
mysql> select studentNo 学号, subjectNo 科目编号, studentResult 成绩, examDate 考试时间 from result where exists (select studentNo from student where gradeId=(select gradeID from grade where gradeName='大一'));

#查询每门课程的平均分，并且按照分数由高到低的顺序排列显示（分组查询）
mysql> select subjectNo 课程编号,avg(studentResult) 平均分 from result group by subjectNo order by avg(studentResult) desc;

#分别统计每个年级男、女生人数
mysql> select count(*) 人数, sex 性别 from student group by sex;

#如何获得课程平均分及格的课程编号？(分组筛选语句)
mysql> select subjectNo 课程编号,avg(studentResult) 平均分 from result group by subjectNo having avg(studentResult)>80;

#查询出学生姓名，学生的年级，学生的课程，考试分数，考试时间字段的学生信息（多表查询）
mysql> select studentName 姓名, gradeName 年级, subjectName 课程,studentResult  成绩,examDate 考试时间 from student inner join grade on student.gradeId = grade.gradeID inner join subject on student.gradeId=subject.gradeID inner join result on student.studentNo=result.studentNo;

DCL（Data Control Language 数据库控制语言)

#事务处理，只支持innodb和bdb类型的数据表
#关闭自动提交，同时开启一个事务
mysql> set autocommit=0;
mysql> update acc set `money`=`money`-500 where `name`='Tom';
mysql> update acc set `money`=`money`+500 where `name`='Jerry';
#提交，将数据写入到数据库保存
mysql> commit;
#回到事务处理前的状态，如果已经提交成功，则回滚无效
mysql> rollback;
#打开自动提交
mysql> set autocommit=1;

数据库备份与恢复

#使用mysqldump备份
$ > mysqldump -u root -p myschool > /users/jason/myschool.sql;
#使用source恢复
mysql> use testdatabase;
mysql> source  /user/jason/myschool.sql;