MySQL——总结

数据库命令：
创建create database 数据库名 charset=utf8;
删除drop database 数据库名;
查看所有数据库：show databases;
使用数据库：use 数据库名;
----------------------------------------
表命令：
create table 表名(列...);
唯一标识的要求：id
类型：int unsigned
约束1：not null
约束2：primary key
约束3：auto_increment
列的格式：列的名称 类型 约束
create table stu(
-> id int not null primary key auto_increment,
-> name varchar(10) not null,
-> gender bit default 1,
-> birthday datetime,
-> isDelete bit default 0,
-> );
查看表show tables;
查看表结构desc 表名;
修改表：alter table 表名 add|modify|drop 列名 类型 约束;
alter table stu modify column isDelete bit not null default 0;
删除表：drop table 表名;
----------------------------------------
数据命令：
添加数据：insert into 表名(列名) values(值),(值)...;
修改数据：update 表名 set 列1=值1,... where ...;
删除数据：delete from 表名 where ...;
逻辑删除：update ....
备份：mysqldump >
恢复：mysql <

数据库、表、字段、行

问：查询姓黄或洪的男生
分析：数据从哪来，哪个表stu
条件：姓黄或洪name or
and
男生gender
答：select * from stu where gender=1 and (name like '黄%' or name like '洪%')

distinct
条件：where 字段 运算符 常量
分组聚合：group by ... having ...

关系的存储方案
1：1-》存储在任何一个表中
1：n-》存储在n的表中，新增一个字段
m：n-》新建表

成绩表：id,成绩，学生，科目
关系，第三范式，外键

问题：两个表之间有关系吗？分析的依据是当前系统的业务，够用就行
怎么存储这个关系？参照“关系的存储方案”
关系字段的类型是什么？根据第三范式，引用主键，所以主键的类型，就是这个字段的类型
关系字段的数据有效性怎么保证？外键

create table sco(
id int not null auto_increment primary key,
stu_id int,
sub_id int,
score int(3),
foreign key(stu_id) references stu(id),
foreign key(sub_id) references sub(id)
);

insert into sco values(0,1,1,100);

select distinct * from 表名
where ...
group by ...
having ...
order by ...
limit ...

关系的问题
（1）是什么样的对应关系
（2）存储关系的字段，使用什么类型
（3）存入数据时错了怎么办？

查：学生姓名及所在的班级名称
分析：stu,class
stu.class_id=class.id
答：select * from stu inner join class on stu.class_id=class.id

查询学生的姓名、平均分
分析：姓名->stu
平均分->先sco查分数，再聚合avg
需要从两张表中获取数据，所以需要连接
连接的条件：stu.id=sco.stu_id
实现一：获取所有的原始数据
select name,score from stu inner join sco on stu.id=sco.stu_id
继续分析：对每个学生求平均分
让姓名相同的信息，分成一组
select name,avg(score) from stu inner join sco on stu.id=sco.stu_id
group by name

查询男生的姓名、总分
分析：姓名->stu
男生->stu
总分->sum(),分数->sco
连接条件：stu.id=sco.stu_id
实现一：select * from sco inner join stu on stu.id=sco.stu_id
where gender=1
实现二：分组
。。。 group by name

查询科目的名称、平均分
sub.title->sub
avg(),score->sco
sub.id=sco.sub_id

查询学生姓名、科目名称、分数
stu
sub
sco

查询省的名称为“山西省”的所有城市
select * from areas where title='山西省'

查询‘广州市’的所有区县
#select * from areas where title='淄博市' #370300
#select * from areas where pid='370300' #370301
#select * from areas where pid='370301'

#areas as shi where shi.title='广州市'
#areas as qu on qu.pid=shi.id
#areas as qu1 on qu1.pid=qu.id

select qu.*,qu1.*
from areas as shi
inner join areas as qu on qu.pid=shi.id
left join areas as qu1 on qu1.pid=qu.id
where shi.title='淄博市'

子查询
#查询广州市、淄博市的所有区
#select id from areas where title='广州市' or title='淄博市'
select * from areas where pid in(select id from areas where title='广州市' or title='淄博市')

源码安装：python setup.py install

MySQLdb
Connection
Cursor

查询：fetchone(),fetchall()
封装：将数据库操作的代码封装到一个类中，helper
用户的登录注册成功
hashlib
sha1()
update()
hexdigest()

linux下数据库操作：

mysql -h localhost -u 用戶名 -p密碼                //連接數據庫
use desk_show;                                          //使用數據庫
show tables;                                              //顯示數據表
describe desk6_0;                                     //顯示表結構

mysql其他命令：

show databases; 显示数据库

create database name; 创建数据库

use databasename; 选择数据库

drop database name 直接删除数据库，不提醒

show tables; 显示表

describe tablename; 显示具体的表结构

select 中加上distinct去除重复字段

mysqladmin drop databasename 

删除数据库前，有提示。

显示当前mysql版本和当前日期

select version(),current_date; 

 

 

我们quit或者exit退出mysql。

 

1、显示数据库

 show databases;

2、选择数据库

use 数据库名;

3、显示数据库中的表

show tables;

4、显示数据表的结构 

describe 表名;

 5、显示表中记录 

SELECT * FROM 表名

 6、建库

 create databse 库名;

 7、建表

create table 表名 (字段设定列表)；

mysql> create table name(
    -> id int auto_increment not null primary key ,
    -> uname char(8),
    -> gender char(2),
    -> birthday date );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_userdb |
+------------------+
| name             |
+------------------+
1 row in set (0.00 sec)

mysql> describe name;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| uname    | char(8) | YES  |     | NULL    |                |
| gender   | char(2) | YES  |     | NULL    |                |
| birthday | date    | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

注： auto_increment 自增
     primary key    主键

 8、增加记录

 insert into name(uname,gender,birthday) values('张三','男','1971-10-01');

 9、修改记录

update name set birthday='1971-01-10' where uname='张三';

 10、删除记录

delete from name where uname='张三';

 11、删除表

drop table 表名

 12、删除库

 drop database 库名;

13、备份数据库 

mysqldump -u root -p --opt 数据库名>备份名; //进入到库目录

14、恢复

mysql -u root -p 数据库名<备份名; //恢复时数据库必须存在，可以为空数据库

 15、数据库授权

 　格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"

例1、增加一个用户user001密码为123456，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL，然后键入以下命令：

 mysql> grant select,insert,update,delete on *.* to user001@"%" Identified by "123456";

例2、增加一个用户user002密码为123456,让此用户只可以在localhost上登录,也可以设置指定IP，并可以对数据库test进行查询、插入、修改、删除的操作 (localhost指本地主机，即MySQL数据库所在的那台主机）

        //这样用户即使用知道user_2的密码，他也无法从网上直接访问数据库，只能通过MYSQL主机来操作test库。
        //首先用以root用户连入MySQL，然后键入以下命令：

  mysql>grant select,insert,update,delete on test.* to user002@localhost identified by "123456";