1. Mysql
1.1. Index and table searching of Mysql
1.1.1. Basic concepts of Mysql and dbms
- What is sql: a storage for storing data and sql means Structured Query language
- Contents of sql : title and table
- DB: database是指长期存储在计算机内的,有组织,可共享的数据的集合。数据库中的数据按一定的数字模型组织、描述和存储,具有较小的冗余,较高的数据独立性和易扩展性,并可为各种用户共享。
- DMS:database Management System是一种操纵和管理数据库的大型软件,除了mysql外,还有甲骨文oracle的,IBM的db2,sql server,Access
- Select now()不区分大小写,可以写成SELECT now()或者select now()或者Select now()
- 支持换行输出(但要以“;”结尾),不能对单词跨行
- 单行注释:--,多行注释:/*......*/
- 安装mysql步骤:
- 这里我安装的时候出现错误5:说明没有用管理员身份运行。
- 从官网直接下载最新版(压缩版zip,mysql5.以后就不是msi了)
- 解压到本地目录,将mysql/bin文件夹,将间接路径…/bin添加到系统Path的环境变量下
- 有时候会出现解压后的mysql包里没有default.ini文件或者data文件夹,同时在C:/ProgramData里也没有
- 在命令行输入cmd,用管理员身份运行cmd
- 输入mysqld --initialize-insecure --user=mysql,这是在mysql根目录下会出现data文件夹
- 接下来输入mysqld –install,系统提示successfully installed表明安装成功
- 接下来运行net start MySQL,进入MySQL服务,如果服务启动成功,到下一步
- 输入mysql –u root –p,需要输入密码,第一次可以用enter键跳过
- 系统提示如下界面,说明mysql安装成功
删库:drop databases zrc: 出现Query OK表明删除成功
三种退出数据库命令行方法:q,exit,quit
Mysql密码,忘记密码需要删除
- 数据库命令
Create database 创建数据库
Create database if not exists s3; #如果不存在s3,就创建s3
Create database if not exists s4 character set gbk; #设置s4表的字符为gbk
Show database 查看数据库
Show create database; 查看创建的某个数据库
Show warning #查看警告内容
alter database s4 character set utf8 更改数据库编码
select database(); 查看进入的数据库序号
1.1.2. 数据库类型
主键(表头)ID两个特性:非空且唯一;非空 not null,唯一 unique
Mysql三种类型:数值,日期和时间
数值类型:区别是字节不一样
Char(3) 定义一个固定长度的字符串
Varchar(20) 定义一个不固定长度的字符串长
表的创建:
CREATE TABLE employee.Charger(
id TINYINT PRIMARY KEY auto_increment,
name VARCHAR(25),
gender boolean,
age INT DEFAULT 19,
department VARCHAR(20),
salart DOUBLE(7,2)
)
Employee:库名
Charger:数据表名
1.1.3. 数据表操作
desc tab_name 查看表结构
show columns from tab_name 查看表结构
show tables 查看当前数据库中的所有的表
show create table tab_name 查看当前数据库表建表数据
alter table employee add is_married 向主键添加一个字段
alter table employee add entry_date date not null 添加入职时间
alter table employee DROP A 删除A列
alter table employee modify age smallint unique 修改字段类型
alter table employee modify age smallint not null default 18 after id; 移动字段位置
修改前:
修改后:
alter table employee change department depart varchar(20) after salart改字段名称并移动
rename table employee to emp 重命名表头employee 到emp
create table A(id int) id必须有
插入表数据语句
- INSERT INTO emp (id,age,name,gender,salart,is_married) VALUES (1,38,”alex”,0,1700,1)
- select * from emp; 从emp中查询数据表内容
- INSERT INTO emp set name=”珊珊” 向table中插入name珊珊
修改表字段名
- alter table emp change salart salary VARCHAR(20) 修改表table字段名
- UPDATE emp SET salary=salary 20000, depart=”保安部” WHERE NAME=”yuan”; 修改数据
删除表字段名
- DELETE From emp WHERE id=2 OR id=3; 删除记录
- Truncate table emp; 删除记录
上述两者区别:delect逐条删除数据;truncate属于销毁emp表,重新创建新表
Select * from emp 和 desc emp区别:
- · Select * from emp表示查看字段和数据内容;
- · desc emp表示查看字段类型
选择并查看数据库:
- · select database(); 选择数据库
- · show tables; 查看数据库中的表(有哪些表)
- · use s3;选择用户
例1向mysql中插入一个
CREATE TABLE ExamResult(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
JS DOUBLE,
Django DOUBLE,
Flask DOUBLE
);
INSERT INTO ExamResult VALUES (1,"yuan",98,98,98),
(2,"xialv",35,98,67),
(3,"alex",59,59,62),
(4,"wusir",88,89,82),
(5,"alvin",88,98,67),
(6,"yuan",86,100,55);
选择查看所有name: select name from Examresult;
也可以同时查看多个字段名,比如name JS
选择name列,去重复:select distinct name from examresult; 此时重复yuan,消除。
显示所有信息:select name,JS,Django,Flask from examresult;
所有人分数 10分显示:select name,JS 10,Django 10,Flask 10 from examresult;
也可以写成中文名并在数值中加10: select name 姓名,JS 10 as JS成绩,Django 10 as django成绩,Flask 20 from examresult;
显示成绩大于80的字段和数据:select name,JS from examresult where JS>80; (Where 常用于添加条件用。)
查看88到100的数据:select name,JS from examresult where JS between 88 and 100; (包括88和100)
select name,JS from examresult where JS in (88,99,77) 筛选出examresult中有88,99,77的数值
select name,JS from examresult where name like "y%"; 模糊匹配y开头的name数据
select name,JS from examresult where name like "a____"; 匹配a 后四位的name数据
添加一个空值到examresult中 insert into examresult name value (“Tom”)
Select name from examresult where JS is NULL; 查看name中空值的名字
select name,JS from examresult order by JS; 按照升值对name排序
select name,JS from examresult where JS>70 order by JS; name值>70并按照升序排列
select name,JS from examresult where JS>70 order by JS desc; name值>70并按照降序排列
select name,JS Django Flask as 总成绩 from examresult order by 总成绩 desc; 把JS Django Flask赋值给总成绩并按照降序排列;
select name,JS Django Flask as 总成绩 from examresult where name="yuan" order by 总成绩; 选择上述总成绩并挑选出yuan进行升序排序;
错误语句分析:select JS as JS成绩 from examresult where JS成绩 > 70 不能执行,因为首先处理from examresult, 接着找到JS>70处,但是此时没有JS成绩的字段,所以报错
改变字段名:alter table examresult change JS JS成绩 double;
Group_by听所很难!
select name,sum(JS成绩) from examresult group by name; 分组后求和;
Where和having 区别:where分组前过滤,having分组后过滤;
select name,sum(Django) from examresult group by name having sum(Django)>90;
select * from examresult having id=3; 选出id=3
等于 select * from examresult where id =3;
select count(name) from examresult where JS成绩>70; 取出JS成绩>70人的个数
select sum(JS成绩)/count(name) from examresult; 求JS成绩平均数
select AVG(JS成绩) from examresult; 求JS成绩平均数
两者区别在于sum中考虑空值NULL,AVG过滤掉空值NULL
select max(JS Django flask) from examresult; 求JS Django flask的最大值
select * from examresult limit 5; 限制显示的数据条数
以下字符按顺序:
Where条件
Group by field
Having 筛选
Order by field
Limit 限制条数
正则匹配例子:
Select * from examresult where emp_name regexp “yu”;
Select * from examresult where emp_name regexp “yun$”;
1.2. Matters of mysql
1.2.1. 多表查询值连接查询
创建老师表 学生表
mysql> CREATE TABLE lessonmysql.ClassCharger(
id TINYINT PRIMARY KEY auto_increment,
name VARCHAR(20),
age INT,
is_married boolean
);
mysql> CREATE TABLE Student(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
charger_id TINYINT
FOREIGN KEY (charger_id) REFERENCES classcharger(id)
) ENGINE=INNODB;
相关联的两个数据类型必须一致
插入老师名:
Insert into ClassCharger(name,age,is_married) values (
"冰冰",52,0),
("丹丹",34,0),
("玩玩",32,0),
("jiji",28,0),
("dingding",35,0);
插入学生名:
mysql> INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
("alvin2",4),
("alvin3",1),
("alvin4",3),
("alvin5",1),
("alvin6",3),
("alvin7",2);
创建表student2
CREATE TABLE Student2(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
charger_id TINYINT,
FOREIGN KEY (charger_id) REFERENCES classcharger(id)
)ENGINE=INNODB;
删除charger冰冰后再次插值,将1改为5
INSERT INTO Student2(name,charger_id) VALUES ("alvin1",2),
("alvin2",4),
("alvin3",5),
("alvin4",3),
("alvin5",5),
("alvin6",3),
("alvin7",2);
Classcharger主表,student2是子表;
添加外键关联:
alter Table student ADD CONSTRAINT abc
Foreign KEY(charger_id)
references classcharger(id);
删除外键关联:
alter table student2 drop foreign key abc
1.2.2. 级联删除与set NULL
- 外键的级联删除:如果父表中的记录被删除,则子表中对应的记录自动删除
CREATE TABLE Student3(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
charger_id TINYINT,
FOREIGN KEY (charger_id) REFERENCES classcharger(id) on DELETE CASCADE
)ENGINE=INNODB;
- Set NULL:当父表上update/delete记录时,将子表上匹配记录的列设为null
Alter table s3 add constraint s3_fk_cc foreign key (charger_id) References cc(id) on delete set null;
1.2.3. 多表查询之连接查询
多表查询:
连接查询:
内连接:inner join(左连接和右连接)
Select * from tableA where table.tableA_id = tableA.id
外连接:left join right join
(select employee.emp_name,department.dept_name FROM employee LEFT JOIN department on employee.dept_id=department.dept_id; 左连接在左边显示全部字段)
同理左连接显示左边全部字段
全连接:full join(左右字段都显示全)
(select employee.emp_name,department.dept_name FROM employee FULL JOIN department on employee.dept_id=department.dept_id; 左连接在左边显示全部字段
1.2.4. 多表查询之复合查询与子查询
1.2.5. Mysql之索引
添加索引:create index index_name on t2(id) $$
删除索引:drop index 索引名 on 表名
如:drop index index_name on t2;
如:drop index index_emp on emp1;
1.3. Mql事务
Import pymysql
Conn = pymysql.connect()
1.3.1. Python操作数据库pymysql
Mysql设置密码:
- 第一次进入mysql时候不用密码,当password出来时,可以直接按enter进入;
- 用flush.privileges刷新权限;(这一步,必须有,否则会报错)
- 执行alter user “root”@localhost IDENTIFIED BY ”newpassport”
- 用flush privileges再次刷新状态
- 退出mysql,用ctrl z或者”q”
- 管理员身份重新运行cmd,进入mysql的bin目录,执行net start mysql命令启动mysql
- 执行mysql –u root –p
- 输入新密码/修改后密码,登录mysql成功。
这是最后成功的状态:
如果需要修改密码需要在重设密码前停止mysql进程,所以要启动下面命令:
Net stop mysql; 停止进程
Mysqld --shared-memory –skip-grant-tables 进入免密码模式
退出cmd,重启cmd后,输入net start mysql进入启动流程
输入进入密码操作mysql –u root -p
两种操作那个数据库的框架:pymysql和orm
1.3.2. 数据库之事务
事务:指逻辑上的一组操作,组成这组操作的各个单元,要不全部成功,要不全部不成功,数据库开启事务命令。
1.3.3. Mysql事务之savepoint
Savepoint: 保留点,事务处理中设置的临时占位符,可以对它发布回退(区别于整个事务回退rollback)
Savepoint使用,需要配合rollback使用,
如:savepoint delete1---rollback to savepoint