mysql笔记

关键字：

　　primary key:主键

　　desc 表名：查看表结构

　　source 文件名：使用文件创建表啊

　　auto_increment:自动增长数字

　　为已有表格字段添加auto_increment约束时，需要先删除字段，再重新添加

 

数据类型：

　　enum:枚举类型

　　

　　

 

1.增加、删除修改字段

　　alter table 表名 add 字段名 类型 约束：增加字段

　　alter table 表名 modify 字段名 类型 约束：修改字段

　　alter table 表名 change 字段名原命 字段新名 类型 约束：修改字段名称

　　alter table 表名 drop 字段名：删除字段

　   alter table 表名 add constraint 外键名 foreign key(需要设置为外键的字段名) references 外表表名(对应外表主键字段名):设置外键，如果设置外键不成功很有可能是外键值不能完全匹配或者是两个关联字段数据类型不一样

　　alter tablr 表名 add foreign key(需要设置外键的字段名) references 关联表名(关联表主键名)

2.增加修改数据

　　insert into 表名 values (全部字段信息)

　　insert into 表名 (字段1,字段2,....) values (对应字段信息)

　　update 表名 set 字段=将要修改成什么数据 where 修改条件：修改数据

　　　　例：update Students set class='201102' where id<50

 

3.查询

　　select distinct 字段名 from 表名：查询不重复内容

　　order by 字段名：按照字段升序排名； order by 字段名 desc：按照降序排名

3.1子查询

　　子查询示例1：查询分数最高的其中两个字段

　　　　select sid,degree from Score where degree=(select max(degree) from Score);

　　子查询示例2：查询选修“3-245”课程的成绩高于“109”号同学成绩的所有同学的记录。

　　　　select sid from Score where cid='3-245' and degree>(select degree from Score where cid='3-245' and sid='109');

　　子查询示例3：查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

　　　　select avg(degree) from Score where cid like '3%' and cid in (selcet cid from Score group by cid having count(*)>=5);

　　表级子查询：

　　　　如果子查询出来的结果很多，可以把该结果看成表来对待

　　　　select * from(子查询结果) as 表1名 inner jion 表2名 on 表1.字段名=表2点字段名

　　　　select * from (select logo,max(price) as max_price from goods group by logo) as f inner join goods on f.max_price=goods.price and f.logo=goods.logo;

　　　　先查找到每个分组的最高价格和logo并且将这个查询结果命名为f，然后在使用inner join on关联查询goods 和f有关的信息

　　　　

3.2分组查询

　　得到每个分组内的成员：select group_concat(字段名) from 表名 group by 字段名

　　分组查询示例1：查询各科成绩的平均值

　　　　思路：以各科id 作为分组，然后取平均值

　　　　select cid,avg(degree) from Score group by cid

　　分组查询示例2：查询至少有2名男生的班号。

　　　　select Class from Students where Ssex='男' group by Class having count(*)>=2

3.3关联查询

　　关联查询示例1：查询外键，查询所有学生的Sname、Cname和Degree列。

　　　　分数表结构：

mysql> select * from Score;
+-----+-------+--------+
| sid | cid   | degree |
+-----+-------+--------+
| 103 | 3-245 |  86.00 |
| 105 | 3-245 |  75.00 |
| 109 | 3-245 |  68.00 |
| 103 | 3-105 |  92.00 |
| 105 | 3-105 |  88.00 |
| 109 | 3-105 |  76.00 |
| 103 | 3-105 |  64.00 |
| 105 | 3-105 |  91.00 |
| 109 | 3-105 |  78.00 |
| 103 | 6-166 |  85.00 |
| 105 | 6-166 |  79.00 |
| 109 | 6-166 |  81.00 |
+-----+-------+--------+
12 rows in set (0.00 sec)

　　　　学生表结构：

mysql> select * from Students;
+-----+--------+------+---------------------+-------+
| Sid | Sname  | Ssex | Sbirthday           | Class |
+-----+--------+------+---------------------+-------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95003 |
| 103 | 陆君   | 女   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
6 rows in set (0.00 sec)
　　　　查询外键，查询所有学生的Sname、Cname和Degree列。

　　　　　　：select Sname,cid,degree from Students,Score where Students.Sid=Score.sid;

　　　　关联查询示例2：查询没有外键的关联表

　　　　等级表结构：

mysql> select * from grade;
+------+------+------+
| low  | upp  | rank |
+------+------+------+
|   90 |  100 | A    |
|   80 |   89 | B    |
|   70 |   79 | C    |
|   60 |   69 | D    |
|    0 |   59 | E    |
+------+------+------+
5 rows in set (0.00 sec)
　　　　查询学生sid,cid,rank:

　　　　　　select sid,cid,rank from Score,rank where degree between grade.low and grade.upp;

将多个表的不同字段和在一个字段显示示例：

　　　　查询所有教师表中和学生表中的姓名：

　　　　　　select tname from teacher union select sname from students:　　

 

查询两张表中具有部分有相同信息：　

 

事务

　　　　

            # 以事务形式同时向订单表和订单详情表中插入数据
            try:
                self.cursor.execute(sql_orders) # 插入订单数据

                self.cursor.execute(sql_find_order_id) # 获取刚刚创建的订单编号
                order_id =self.cursor.fetchone()[0] # 获取订单编号，以提供给详情订单表数据

                # 插入订单详情数据
                sql_detail = """insert into order_detail values (0,%d,%d,%d)""" \
                             % (order_id, goods_id, quantity)
                self.cursor.execute(sql_detail)

                #  获取新建订单商品名称
                sql_get_goods = """select t_logo.name,goods.name from goods inner join t_logo on 
                goods.logo_id=t_logo.id where goods.id=%d;"""% goods_id
                self.cursor.execute(sql_get_goods)
                goods = self.cursor.fetchone()
                
                # 如果没有该商品，抛出异常
                if not goods:
                    raise Exception ("没有该商品")

            # 向订单详情表中插入数据
            except Exception as e:
                self.conn.rollback()
                print("购买失败",e)

            else:
                self.conn.commit()
                print("已经成功购买%s个%s%s"% (quantity,goods[0], goods[1]))

 

　　索引

　　　　查看mysql执行语句所用时间：

　　　　　　set profiling=1

　　　　　　sql语句

　　　　　　show profiles

　　　　为字段设置索引：

　　　　　　create index 索引名 on 表名(字段名())

　　　　　　如果字段为varchar、char等有类型，需要在括号里面填写字符长度