数据库之mysql

1、安装mysql-server的命令：sudo apt-get install mysql-server

　  安装mysql-client客户端:sudo apt-get install mysql-client

　  查是否安装成功并且启用:sudo netstat -tap | grep mysql

　  关闭mysql服务器命令：service mysql stop

　　开头mysql服务器命令：service mysql start

　　重启mysql服务器命令：service mysql restart

2、创建一个用户并且授权:grant all privileges on *.* to test@"%" identified by "123456" with grant option;

　　登录数据库:mysql -utest -p123456

　　显示所有数据库:show databases;

　　选择某一个数据库:use students:

　　查看当前数据库的所有表单:show tables;

　　查看某个表的结构:desc students;

　　查看当前表(user)的所有数据:select * from user;

　　查看当前表(user)的user,host两列的数据:select user,host from user;

　　删除表中某个id=1的数据:delete from students where id=1;

　　查看当前使用的数据库:select database();

　　创建数据库:create database test charset=utf8;

　　删除数据库:drop database test;

　　修改用户密码:mysqladmin -test -p123456 password 110110

3、E-R模型:E-R模型是当前物理的数据库都是按照E-R模型进行设计的，其中

　　　　　  E表示entity,实体;相当于python中的对象包含的信息。

　　　　　 R表示relationship,关系;如当百度王宝强时会有马蓉的相关消息。它是一个实体转换为数据库的一个表。

　　　　　　对字段约束方式:

　　　　　　　　主键primary key;不能重复，唯一标识，物理存储方式，速度快

　　　　　　　　非空not null;当保存为null时就会报错;

　　　　　　　　唯一unique;这个值是唯一的，有重复就会报错。

　　　　　　　　默认default ;如果不写就会有个默认值,这种叫默认，只有类型。

　　　　　　　　外键 foreign key

4、用sql语句创建表:  auto_increment 表示自增长;   primary key:主键    not null：不能为空

　　create table students(

　　　　　　　　　　id int(10)auto_increment primary key  not null,          

　　　　　　　　　　name varchar(40),

　　　　　　　　　　gender bite(1)  default 0

                                   )charset=utf8;

5、删除表单:drop table 表单名;

6、查看表单中的所有数据:select * from students;

     取别名:select name as 姓名,gender as 性别 fromstudents

7、查找表中id为1的所有字段:select * from students where id=1;

8、查找表中id为1的id和name：select id,name from students where id=1;

9、从表中查找所有数据中的id和name的字段数据: select id,name from students

10、insert into students(id,name,gender) values(3,'zhangsan','男'),(4,'lishi','男');

11、删除表单中id为1的或者name为mysql的数据:delete from students where id=1 or name=mysql;

12、修改表单中id为2的,把name改为mysql的数据:update students set name='mysql' where id=2;

13、将表单中的记录清空:delete from students;

14、备份:mysqldump -uroot -p test2 > ~/0000mysql.sql

         还原:mysql -uroot -p test1 < 0000mysql.sql

15、消除重复行，如查询性别:  select distinct gender from students;

16、查询id大于3的女同学:select id,name from students where id>3 and gender =0;

17、查询姓郭的学生:select * from students where name like '郭%';   %表示一个或者多个

18、查询姓黄并且名字是一个字的学生:selcet * from students where name like '黄_';   _表示一个

19、查询姓黄或者叫靖的学生:select name from students where name like '黄%' or name like '%靖%';

20、查询id是1或3或8的学生:select * from students where id in(1,3,8);

21、查询学生id是3至8的学生:select * from students where id between 3 and 8;

22、查询没有写性别的学生:select * from students where gender is null;

23、查询写了性别的学生:select * from students where gender is  not null;

24、查询女生id的最大值:select max(id) from students where gender=0;  最小值用min ,和用sum,平均值用avg ,总个数用count

25、一个语句统计男生和女生总人数:select gender,count(id) from students group by gender;

26、查询男生总人数:

　　使用where语句 :select gender,count(id) from students where gender=1;

　　使用分组后筛选:select gender,count(id) from students group by gender having gender =1;

27、查询未删除男生学生信息，按id降序:select id,name from students where gender=1 order by desc;    (order by asc 升序)

28、分面查找:(n-1)*m   从第一页开始查找    n表示页数，m表示每页显示数据的条数

　　查询学生表，每页5条数据，页数从1开始 ：

　　第一页:select * from students limit 0,5;

       第二页:select * from students limit 5,5;  ......

29、创建分数表(scores)直接创建约束: foreign key(stuid) 表示外键    references表示与...相连

　　　　

　　　　　　　　create table scores(

 

 　　　　　　　　　　　　id int primary key auto_increment ,

 

　　　　　　　　　　　　 score decimal(4,1),

 

　　　　　　　　　　　　 stuid int,

 

 　　　　　　　　　　　　subid int,

 

 　　　　　　　　　　　　foreign key(stuid) references students(id),

 

 　　　　　　　　　　　　foreign key(subid) references students(id)

 

　　　　　　　　　)charset=utf8;

30、插入数据:insert into scores values(1,92,3,3),当插入外键在主表单中不存在时，会报错，这就是不合法数据.

31、查询每个学生的每个科目的分数:

　　第一种写法:

         　  select students.name,subjects.title,scores.score from scores

　　　　inner join students on scores.stuid=students.id

　　　　inner join subjects on scores.subid=subjects.id;

　　第二种写法:

　　　　select students.name,subjects.title,scores.score from students

　　　　inner join scores on scores.stuid=students.id

　　　　inner join subjects on scores.subid=subjects.id;

　　第三种写法:

　　　　select students.name,subjects.title,scores.score from subjects

　　　　inner join scores on scores.stuid=subjects.id

　　　　inner join students on scores.stuid=students.id;

32、左连接(left join):表示表A和表B进行匹配，会完整显示出表A的数据，表B只显示与表A对应的数据

       右连接(right join )与此相反

　　　　select students.name,subjects.title,scores.score from subjects

　　　　left join scores on scores.stuid=subjects.id

　　　　left join students on scores.stuid=students.id;

33、查询男生的姓名、总分:

　　select students.name ,sum(scores.score)from scores

　　inner join students on scores.stuid=students.id where gender=1 group by students.name;

34、查询未删除科目的名称、平均分:

　　select subjects.title,avg(scores.score)from scores

　　inner join subjects on scores.subid=subjects.id where isdelete=0 group by subjects.title;

35、使用python代码实现把mysql数据库封装成MysqlTool模块,该模块中MySqlHelper类：

import pymysql

# 创建MysqlHelper类
class MysqlHelper(object):
    # 创建初始化参数
    def __init__(self,host,user,password,db):
        self.host=host
        self.prot=3306
        self.user=user
        self.password=password
        self.db=db
        self.charset='utf8'

    # 打开数据库链接返回connect对象-conn
    def open(self):
        self.conn=pymysql.connect(
            host=self.host,
            port=self.prot,
            user=self.user,
            password='1122',
            db='python2',
            charset='utf8'
        )
        # 得到cursor对象
        self.cursor=self.conn.cursor()

    # 关闭链接
    def close(self):
        self.cursor.close()
        self.conn.close()

    # 修改提交数据
    def change(self, sql, parms=[]):
        try:
            self.open()
            self.cursor.execute(sql,parms)
            self.conn.commit()
            self.close()
        except Exception as result:
            print(result)
    def get_all(self,sql,prams=[]):
        try:
            self.open()
            self.cursor.execute(sql, prams)
            result = self.cursor.fetchall()
            self.close()
            for i in result:
                print(i)
        except Exception as result:
            print(result)

 

 

 

 

查询未删除科目的名称、平均分、最高分：