mysql数据库

mysql：开源、免费、跨平台
常见的DBMS：甲骨文的oracle。 IBM的db2， sql server， Access ，mysql；

sql规范

不区分带小写；
默认分号为结尾不能省略；
可以换行写但是不能拆单词；
可以用缩进提高可读性；
单行注释-- 多行注释/…/；
DDL（定义型）,DML（操作型）,DCL（权限型）共同构成操作语句；

DDL型语句

查看数据库：

show databases;

删除数据库

drop database db_name;

创建数据库（在磁盘上建立文件夹）

create database db_name;
create database if not exists db_name; #如果不存在的话创建，如果存在会有警告不会报错，更不会进行覆盖
create database if not exists db_name charater set gbk; # 创建的同时设置编码方式

显示创建的数据库的信息（默认的编码方式）

show create database db_name;

更改数据库的信息(少)

alter database dn_name character set utf8;

进入数据库

use db_name;
select database(); #检测现在是在哪个数据库中，注意括号
use db_name; #可以直接切换到其他数据库中

mysql数据类型

数值类型：8种；
字符串类型：10种；其中char定长字符串、varchar变长字符串；
时间数据类型：5种；

创建表

主键用来标识唯一的一行；
主键：非空且唯一；

CREATE TABLE a20191020(
    id TINYINT PRIMARY KEY auto_increment, # 主键不为空且自动增加
    name VARCHAR(25),
    gender boolean, # 系统自动转换成tinyinit
    age INT,
    department VARCHAR(20),
    salary DOUBLE(7,2)

)

显示表

show tables;

查看表的信息

show create table tab_name;
desc tab_name;

修改表信息

修改表结构（字段/列）

添加字段

alter table tab_name add 字段名 type（约束条件）;
alter table a20191020 add is_marry tinyint(4);
alter table a20191020 add entry_data data not null;

删除字段

alter table tab_name drop 字段名；

修改字段类型

alter table tab_name modify 字段名 类型（约束条件）；
alter table employee modify age samllint not null default id ;

修改字段(列)名称

alter table tab_name change 字段名 修改之后字段名 类型（约束条件）；
alter table employee change department depart varchar after salary ;

更改表名

rename table 表名 to 新的表名；

删除表名

drop table tab_name;
show tables;

表记录操作（表里的内容）

增加表记录

insert into emp values(., ., ., .);
insert into emp (id, age, name, gender, salary, depart, is_empty) values(0, 18 wang nan 1000 jishu yes), (1, 19, li nan 1000 jishu yes) ；
insert into emp set name = 'zhao' ;

修改表记录
where用来进行筛选，否则是对所有进行操作

update emp set salary = salary + 2000， age = 18 where id = 3;

删除表记录
where进行筛选

delete from emp where id = 3 or id = 4;
delete from emp; (一条一条删，表在但内容为空)
truncate table emp ;(将表一下子删除，再创建新表)

查询表记录
执行顺序：from选择表 where过滤 group分组 having过滤 select取出 order排序

select * from emp; （全部取出） 
select name from emp; (查看某列)
select name from emp order by age (desc); （默认升序） 
select name from emp where age > 18; （< > =）
select name from emp where age is null; 
select name from emp where age between 18 and 20; 
select name from emp where age in (18 ,19, 20); 
select name from emp where name like 'yuan';  (%可以是任意多字符，_只能是一个字符)
select name + 10, age + 20 from emp; (只是显示的时候临时变)
select name as names, age as ages from emp; (只是显示的时候临时变)
select name, score1 + score2 + score3 as from emp score order by score desc
select distinct name from emp; (自动去除此列中重复的)
# group按照分组显示第一条数据
select * from emp group by age; (按年龄分了许多组，只显示每个年龄组的第一个)
select * from emp group by 2; （字段的序号）
select name, sum(score) from emp group by 2; (求每一组的和)
select name, sum(score) from emp group by name having sum(score) > 150 ;
# 统计个数、最大最小、平均值
select count(name) from emp where score > 70 ;
select avg(age) from emp ;
select max(age) from emp ;
select avg(age) from emp where ifnull(age, 0) > 20 ; (ifnull如果是空则为0)

select * from emp limit 3; (取前三个)

多表查询

外键：与另一个表的联系

CREATE TABLE Student(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20),
	charger_id TINYINT,
	# 注意：在关联的时候两者的数据类型要完全一致，外键charger_id与classcharger中的id相关联
	FOREIGN KEY (charger_id) PEFERENCES classcharger(id) 
) ENGLISH=INNODB;

当要删除主表内容时，需要先对从表表记录进行删除；
当要插入从表内容时，需要先对主表表记录进行插入；

添加外键

# abc这里是外键的名字
ALTER TABLE student ADD CONSTRAINT abc FOREIGN KEY (charge_id) PERFERENCES classcharger (id);

删除外键

ALTER TABLE student DROP CONSTRAINT FOREIGN KEY abc;

级联删除

FOREIGN KEY (charger_id) PEFERENCES classcharger(id) ON DELETE CASEADE

set null
当删除主表表记录时，从表对应内容变为空值

FOREIGN KEY (charger_id) PEFERENCES classcharger(id) ON DELETE SET NULL

连接查询

内连接：inner join
外连接：left join right join
全连接：full join

笛卡尔积

两张表中一条一条对应，共m*n条

SELECT * FROM tableA, tableB WHERE tableA.id = tableB.tableA_id;

内连接

顺序可以颠倒,取交集

SELECT * FROM tableB INNER JOIN tableA ON tableA.id = tableB.tableA_id;

外连接

左连接中左表内容全显示

SELECT * FROM tableB LEFT JOIN tableA ON tableA.id = tableB.tableA_id;

右连接中右表内容全显示

SELECT * FROM tableB RIGHT JOIN tableA ON tableA.id = tableB.tableA_id;

全连接

mysql不支持

子查询

子查询就是嵌套
IN

SELECT * FROM emp WHERE dept_id IN (SELECT dept_id FROM department) ; 

EXISTS
where后面是一个布尔类型

SELECT * FROM emp WHERE EXISTS (SELECT dept_name from department where dept_id)

索引

创建于维护索引会消耗时间与空间，但是大大提高查询速度

创建索引

CREATE TABLE 表名（
	字段名1 数据类型 【完整约束条件】，
	字段名2 数据类型 【完整约束条件】，
	[UNIQUE | FULLEXT | SPATIAL ] INDEX | KEY
	[索引名]  （字段名[（长度）][ASC | DESC]）
	...
	）
CREATE TABLE test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20),
	resume TEXT DEFAULT "XXXXXXX"
	)
INSERT INTO test1(name) VALUES 
	("111"),
	("222"),
	("333");
SHOW CREATE TABLE test1;

普通索引

CREATE TABLE emp(
	id INT,
	name VARCHAR(20),
	INDEX index_name (name)
);

唯一索引

CREATE TABLE emp(
	id INT,
	name VARCHAR(20),
	bank_num CHAR(18) UNIQUE,
	resume VARCHAR(50),
	UNIQUE INDEX index_emp_name (name)
);

全文索引

CREATE TABLE emp(
	id INT,
	name VARCHAR(20),
	resume VARCHAR(50),
	FULLTEXT INDEX inde_resume (resume)

多列索引

CREATE TABLE emp(
	id INT,
	name VARCHAR(20),
	resume VARCHAR(50),
	INDEX inde_name_resume (name,resume)

添加索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
	ON 表名 （字段名[(长度)] [ASC | DESC]）

索引测试

# 创建表
CREATE TABLE Indexdb.t1 (id int, name varchar(20)));
# 储存过程
delimiter $$
CREATE PROCEDURE autoinsert()
BEGIN
declare i int default 1;
while (i<500000) do
insert into Indexdb.t1 values (i,'yuan');
set i = i + 1 ;
end while;
END$$

delimiter ;
# 调用函数
call autoinsert();
# 花费时间比较
# 创建索引前
select * from t1 where id = 4000
# 添加索引
CREATE INDEX index_name ON t1(id)
# 创建索引后
select * from t1 where id = 4000
# 删除索引
DROP INDEX index_emp_name ON emp1;

python操作MYSQL

pymsql是python中操作MYSQL的模块，其使用方法和py2的MYSQLdb几乎相同。
在python中使用mysql：

import pymysql
conn = pymysql.connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = '',db = 'yyy')

# 查询数据
cursor = conn.cursor() #相当于一个数据游标
sql = "CREATE TABLR test(id INT, name VARCHAR(20))"
cursor.excute("sql")
cursor.excute("INSERT INTO test VALUES (1,'alex'),(2,'alvin')")
ret = cursor.excute("SELSECT * FROM test")
print(ret)
# fetch取数据
print(cursor.fetchone()) # 取出后游标后移
print(cursor.fetchmany(3))
print(cursor.fetchall())
#移动光标
cursor.scroll(1,mode = 'relative') # 1代表向下移1，-1代表向上移1
cursor.scroll(1,mode = 'absolution') # 游标直接到1

cursor.commit() #提交之后才有用
cursor.close() # 关闭cursor
conn.close() # 关闭接口

事务

事务指逻辑上的一组操作，要么全部成功，要么全部不成功

CREATE TABLE account (id int ,name varchar(20), balance double);
INSERT INTO account values(1, 'c', 8000);
INSERT INTO account values(2, 'z', 8000);

事务
start transaction 开启事务
roll back 回滚事务
commit 提交事务
savepoint保留点

update account set balance = balance - 5000 where id = 1 ;
update account set balance = balance + 5000 where id = 2 ;

import pymysql
conn = pymysql.connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = '',db = 'yyy')
cursor = conn.cursor()

try:
	insertSQL0 = ""
	insertSQL1 = ""
	insertSQL2 = ""
	cursor = conn.cursor()
	cursor.excute(insertSQL0)
	conn.commit()

	cursor.excute(insertSQL1)
	raise Expection
	cursor.excute(insertSQL2)
	cursor.close()
	conn.close()
expect Exception as e:
	conn.rollback() # 出错时撤销
	conn.commit()