mysqladmin -uroot -p password "123" 默认空密码
windows启动跳过密码
mysqld --skip-grant-table
update mysql.user set password=password("新密码") where user="root" and host="localhost";
flush privileges;
mysql -uroot -p123 -h 127.0.0.1 -P 3306 默认格式
MAC
停止服务(系统偏好设置)
mysqld_safe --skip-grant-tables 命令行启动
5.6及以下版本:
mysql> update mysql.user set password=password("0219") where user="root" and host="localhost";
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
5.7版本更改为authentication
mysql> update mysql.user set authentication_string=password("0219") where user="root" and host="localhost";
统一字符编码
\s 查看字符编码
#1. 修改配置文件
[mysqld]
default-character-set=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#mysql5.5以上:修改方式有所改动
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#2. 重启服务
#3. 查看修改结果:
\s
show variables like '%char%'
SQL语句
操作文件夹(库)
增
create database db1 charset utf8;
查
show create database db1;
show databases;
改
alter database db1 charset gbk;
删
drop database db1;
操作文件(表)
切换文件夹: use db1;
查看当前所在文件夹: select database();
增
create table t1(id int,name char);
查
show create table t1;
show tables;
describe t1; desc t1;
改
alter table t1 modify name char(6);
alter table t1 change name NAME char(7);
删
drop table t1;
操作文件内容(记录)
增
insert t1(id,name) values(1,'wyq'),(2,'yqw'),(3,'qyw');
insert t1 values(1,'wyq'),(2,'yqw'),(3,'qyw');
查
select id,name from t1;
select id,name from db1.t1;
改
update db1.t1 set name='NB';
update db1.t1 set name="wyq" where id=2;
删
delete from t1 where id=1;
delete from t1;
库操作
库的增删改查
系统数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
information_schema 虚拟库,不占磁盘空间(安装目录无文件夹)
mysql 授权库
performance_schema 5.5后新增的库,收集数据库服务器性能参数
test mysql自动创建的测试库
数据库命名规则:
可以由字母、数字、下划线、@、#、$
区分大小写
唯一性
不能使用关键字 如create select
不能单独使用数字
最长128位
什么是存储引擎
存储引擎就是表的类型
查看mysql支持的存储引擎
show engines;
默认InnoDB
指定表类型/存储引擎
create table t1(id int)engine=innodb;
create table t2(id int)engine=memory;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=myisam;
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
表的增删改查
修改表结构
语法:
1. 修改表名
alter table 表名
rename 新表名;
2. 增加字段
alter table 表名
add 字段名 数据类型 [完整性约束条件],
add 字段名 数据类型 [完整性约束条件];
add 字段名 数据类型 [完整性约束条件] first;
add 字段名 数据类型 [完整性约束条件] after 字段名;
3. 删除字段
alter table 表名
drop 字段名;
4. 修改字段
alter table 表名
modify 字段名 数据类型 [完整性约束条件];
change 旧字段名 新字段名 旧数据类型 [完整性约束条件];
change 旧字段名 新字段名 新数据类型 [完整性约束条件];
整数类型
tinyint 1字节
smallint 2字节
mediumint 3字节
int 4字节
bigint 8字节
无符号 unsigned insert into t1(x tinyint ensigned);
用0填充 zerofill
对于整型,创建表的时候不需要加宽度
那个宽度并不是存储宽度,指的是显示宽度
不指定宽度,默认是最大宽度
浮点型
float
float[(M,D)][unsigned][zerofill]
单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数,m最大值为255,d最大值为30
随着小数的增多,精度会变的不准确
double
double[(M,D)][unsigned][zerofill]
双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数,m最大值为255,d最大值为30
随着小数的增多,精度会变的不准确,精度比float高
decimal
decimal[(M,D)][unsigned][zerofill]
准确的小数值,没事数字总个数(负号不算),d是小数点后个数,m最大值为65,d最大值为30
三种浮点型精度对比
mysql> desc t8;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| x | float(255,30) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.02 sec)
mysql> desc t9;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| x | double(255,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> desc t10;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| x | decimal(65,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
mysql> select * from t8;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t9;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t10;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.01 sec)
日期类型
date time datetime timestamp year
create table student(
id int,
name char(6),
born_year year,
birth_date date,
class_time time,
reg_time datetime
);
insert into student vlaues
(1,'wyq',now(),now(),now(),now());
+------+------+-----------+------------+------------+---------------------+
| id | name | born_year | birth_date | class_time | reg_time |
+------+------+-----------+------------+------------+---------------------+
| 1 | wyq | 2018 | 2018-12-31 | 13:26:40 | 2018-12-31 13:26:40 |
+------+------+-----------+------------+------------+---------------------+
mysql终止运行命令 \c
字符类型
char 定长
varchar 变长
create table t13(name char(5));
create table t14(name varchar(5));
insert into t13 values('王先生 ');
insert into t14 values('王先生 ');
mysql> select char_length(name) from t13;
+-------------------+
| char_length(name) |
+-------------------+
| 3 |
+-------------------+
1 row in set (0.01 sec)
mysql> select char_length(name) from t14;
+-------------------+
| char_length(name) |
+-------------------+
| 4 |
+-------------------+
1 row in set (0.00 sec)
函数:
length:查看字节数
char_length:查看字符数
char类型
优点:简单粗暴
缺点:浪费空间
varchar
优点:更加节省空间
缺点:存取都慢
大部分场景都用char类型
建表的时候尽量定长的数据往前放
枚举类型与集合类型
enum 枚举类型,单选,只能在给定的范围中选择一个值
set 集合类型,多选,在给定的范围内可以选择一个或多个值
create table consumer(
id int,
name char(16),
sex enum('male','female'),
level enum('vip','vp'),
hobbies set('play','run','sleep','music')
);
insert into consumer values
(1,'wyq','male','vip','run,sleep,music');
mysql> select * from consumer;
+------+------+------+-------+-----------------+
| id | name | sex | level | hobbies |
+------+------+------+-------+-----------------+
| 1 | wyq | male | vip | run,sleep,music |
+------+------+------+-------+-----------------+
1 row in set (0.00 sec)
约束条件
not null:默认不允许为空
default:默认值
create table t16(
id int,
name char(16),
sex enum('male','female') not null default 'male'
);
mysql> desc t16;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
mysql> insert into t16(id,name) values(1,'wyq');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t16;
+------+------+------+
| id | name | sex |
+------+------+------+
| 1 | wyq | male |
+------+------+------+
1 row in set (0.01 sec)
unique key:标识该字段的值是唯一的
单列唯一
方式一:
create table department(
id int unique,
name char(10) unique
);
方式二:
create table department(
id int,
name char(10),
unique(id),
unique(name)
);
联合唯一
create table services(
id int,
ip char(15),
port int,
unique(id),
unique(ip,port)
);
mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| ip | char(15) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
insert into services values
(1,'192.168.0.100',80),
(2,'192.168.0.101',80),
(3,'192.168.0.100',81);
mysql> select * from services;
+------+---------------+------+
| id | ip | port |
+------+---------------+------+
| 1 | 192.168.0.100 | 80 |
| 2 | 192.168.0.101 | 80 |
| 3 | 192.168.0.100 | 81 |
+------+---------------+------+
3 rows in set (0.00 sec)
primary key:主键 不为空且唯一(not null unique)
存储引擎 innodb:对于innodb存储引擎来说,一张表必须有一个主键
如果不指定主键,mysql会自动找一个不为空且唯一的字段当主键,如果没有符合条件的会自动生成一个隐藏字段作为主键。
单列主键
create table t17(
id int primary key,
name char(16)
);
mysql> desc t17;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec
复合主键
create table t18(
ip char(15),
port int,
primary key(ip,port)
);
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip | char(15) | NO | PRI | NULL | |
| port | int(11) | NO | PRI | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
auto_increment:自增长
create table t20(
id int primary key auto_increment,
name char(16)
);
mysql> desc t20;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
insert into t20(name) values('wyq'),('qyw'),('yqw');
mysql> select * from t20;
+----+------+
| id | name |
+----+------+
| 1 | wyq |
| 2 | qyw |
| 3 | yqw |
+----+------+
3 rows in set (0.00 sec)
也可以插入自增长字段:
insert into t20(id,name) values(7,'wyq1');
mysql> select * from t20;
+----+------+
| id | name |
+----+------+
| 1 | wyq |
| 2 | qyw |
| 3 | yqw |
| 7 | wyq1 |
+----+------+
4 rows in set (0.00 sec)
再插入新内容时从最新的开始增长:
insert into t20(name) values('wyq1'),('qyw1'),('yqw1');
mysql> select * from t20;
+----+------+
| id | name |
+----+------+
| 1 | wyq |
| 2 | qyw |
| 3 | yqw |
| 7 | wyq1 |
| 8 | wyq1 |
| 9 | qyw1 |
| 10 | yqw1 |
+----+------+
7 rows in set (0.00 sec)
自增长默认从1开始,步长也是1.
show variables like 'auto_inc%'; (%代表任意个数任意字符)
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 | 步长 默认为1
| auto_increment_offset | 1 | 起始偏移量 默认为1
+--------------------------+-------+
2 rows in set (0.02 sec)
设置步长
set session auto_increment_increment=5;
set session auto_increment_offset=3;
强调:起始偏移量一定要小于等于步长,否则失效
session 会话级别,只在本次链接生效
global 全局级别
create table t21(
id int primary key auto_increment,
name char(16)
);
mysql> desc t21;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into t21(name) values('wyq'),('yqw');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t21;
+----+------+
| id | name |
+----+------+
| 3 | wyq |
| 8 | yqw |
+----+------+
2 rows in set (0.00 sec)
清空表:
delete from t20;自增长的AUTO_INCREMENT=11没有重置,还是=11.
使用truncate t20; 清空表应该用truncate清空。
mysql> select * from t20;
+----+------+
| id | name |
+----+------+
| 1 | wyq |
| 2 | qyw |
| 3 | yqw |
| 7 | wyq1 |
| 8 | wyq1 |
| 9 | qyw1 |
| 10 | yqw1 |
+----+------+
7 rows in set (0.00 sec)
mysql> show create table t20;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t20 | CREATE TABLE `t20` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> delete from t20;
Query OK, 7 rows affected (0.01 sec)
mysql> show create table t20;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t20 | CREATE TABLE `t20` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t20(name) values('www');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t20;
+----+------+
| id | name |
+----+------+
| 11 | www |
+----+------+
1 row in set (0.00 sec)
truncate t20;
mysql> show create table t20;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| t20 | CREATE TABLE `t20` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
foreign key: 外键,建立表之间的关系
不建议使用外键,不利于扩展,从逻辑上关联
先建立被关联的表,并且保证被关联的字段唯一
create table dep(
id int primary key,
name char(16),
comment char(50)
);
再建立关联的表
create table emp(
id int,
name char(16),
sex enum('male','female'),
dep_id int,
foreign key(dep_id) references dep(id) on delete cascade on update cascade
);
插入数据
insert into dep values(1,'IT','牛逼部门'),(2,'销售','一般部门'),(3,'财务','钱特别多部门');
insert into emp values(1,'wyq','male',1);
insert into emp values(2,'www','male',2);
insert into emp values(3,'w2w','male',3);
insert into emp values(4,'wyw','male',1);
mysql> update dep set id=202 where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dep;
+-----+--------+--------------------+
| id | name | comment |
+-----+--------+--------------------+
| 1 | IT | 牛逼部门 |
| 3 | 财务 | 钱特别多部门 |
| 202 | 销售 | 一般部门 |
+-----+--------+--------------------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+------+------+------+--------+
| id | name | sex | dep_id |
+------+------+------+--------+
| 1 | wyq | male | 1 |
| 2 | www | male | 202 |
| 3 | w2w | male | 3 |
| 4 | wyw | male | 1 |
+------+------+------+--------+
4 rows in set (0.00 sec)
表关系之 多对一
表关系之 多对多
表关系之 一对一
记录的增删改查
插入数据insert
1. 插入完整数据(顺序插入)
语法一:
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二:
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法:
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法:
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法:
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
更新数据UPDATE
语法:
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
示例:
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;
删除数据DELETE
语法:
DELETE FROM 表名
WHERE CONITION;
示例:
DELETE FROM mysql.user
WHERE password=’’;
查询数据select
单表查询
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n; 限制显示条数
distinct 去重
单表查询
语法顺序
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组
having 过滤
order by 排序
limit n;
避免重复distinct
通过四则运算查询
定义显示格式
concat()函数用于连接字符串
select concat('姓名:', name) as Name from employee;
concat_ws()第一个参数为分隔符
select concat_ws(':',name,salary) as salary from employee;
where约束
where字句中可以使用:
1、比较运算符:> < >= <= <> !=
2、between 80 and 100
3、in(80,90,100)
4、like 'egon%'
pattern可以是%或者_
%代表任意多字符
_代表一个字符
5、逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
group by分组
set global sql_mode="ONLY_FULL_GROUP_BY"
分组之后只能取分组的字段,以及每个组聚合结果
聚合函数
max 最大
min 最小
avg 平均
sum 求和
count 计数
没有group by则默认整体算作一组
group_concat
select post,group_concat(name) from employee group by post;
having过滤
执行优先级从高到低 where > group by > having
where发生在分组group by之前,因而where中可以有任意字段,但绝对不能使用聚合函数
having发生在分组group by之后,因而having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
order by排序
select * from employee order by age asc; 升序
select * from employee order by age desc; 降序
select * from employee order by age asc,id desc; 先按照age升序,一样的情况下按照id降序
limit 控制显示条数
总结:
语法顺序:
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组
having 过滤
order by 排序
limit n;
执行顺序:
select-->from-->where-->group by-->having-->distinct-->order by-->limit
正则查询
select * from employee where name like 'jin%';
select * from employee where name regexp '^jin';
select * from employee where name regexp '^jin.*(g|n)$';
多表查询
连表方式
1、内连接
只取两张表的共同部分
select * from employee inner join department on employee.dep_id = department.id;
2、左连接
在内连接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id;
3、右连接
在内连接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id;
4、全外连接
在内连接的基础上保留左右两表没有对应关系的记录
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;
select语句关键字的定义顺序
select distinct <select_list>
from <left_table>
<join_type> join <right_table>
on <join_condition>
where <where_condition>
group up <group_by_list>
having <having_condition>
order_by <order_by_condition>
limit <limit_number>
select语句关键字的执行顺序
select
distinct <select_list>
from <left_table>
<join_type> join <right_table>
on <join_condition>
where <where_condition>
group up <group_by_list>
having <having_condition>
order_by <order_by_condition>
limit <limit_number>
子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果,可以为外层查询语句提供查询条件。
子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS、NOT EXISTS等关键字
还可以包含比较运算符:=、!=、>、<等
select
cid,cname
from
course
where cid in (
select
course_id
from
score
group by
course_id
having
count(sid) = (
select
count(sid)
from
student
)
);
权限管理
1、创建账号
本地账号
create user 'egon1'@'localhost' identified by '123'
mysql -uegon1 -p123
远程账号
create user 'egon2'@'192.168.0.1' identified by '123'
create user 'egon2'@'192.168.0.%' identified by '123' %代表任意
create user 'egon2'@'%' identified by '123' %代表任意
mysql -uegon2 -p123 -h 服务端ip
2、授权
user:*.*
db:db1.*
tables_priv:db1.t1
columns_priv:id,name
授权grant select/all on *.* to 'egon1'@'localhost';
回收权限revoke select on *.* from 'egon1'@'localhost';
pymysql模块
连接数据库
# _author_:wyq
# _date_:2019/2/10
import pymysql
user = input('user>>:').strip()
pwd = input('password>>:').strip()
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='lhwyq1314',
db='db6',
charset='utf8'
)
# 拿到游标
cursor = conn.cursor()
# 执行sql语句
# sql = 'select * from userinfo where user="{}" and pwd="{}"'.format(user, pwd) 有sql注入风险,username" -- xxx
sql = 'select * from userinfo where user=%s and pwd=%s'
print(sql)
res = cursor.execute(sql, (user, pwd))
cursor.close()
conn.close()
if res:
print('登录成功')
else:
print('登录失败')
pymysql模块之增删改查
# _author_:wyq
# _date_:2019/2/11
import pymysql
# 建立连接
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='lhwyq1314',
db='db6',
charset='utf8'
)
# 拿游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 执行sql语句
# 增、删、改
sql = 'insert into userinfo(user,pwd) values(%s,%s)'
# rows = cursor.execute(sql, ('wyq', '123'))
rows = cursor.executemany(sql, [('www1', '321'), ('yyy1', '333')])
print(cursor.lastrowid) # 查询该到哪个id了,如果已经有4条,会返回5,显示该第五条了
print(rows)
conn.commit()
# 关闭连接
cursor.close()
conn.close()
# 执行sql语句
# 查询
# rows = cursor.execute('select * from userinfo;')
# print(cursor.fetchone())
# cursor.scroll(2, mode='relative')
# print(cursor.fetchone())
# print(rows)
# cursor.close()
# conn.close()
MySQL内置功能介绍
视图(虚拟表)
create view course2teacher as select * from course inner join teacher on course.teacher_id = teacher.tid;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+----------------+
| Tables_in_db7 |
+----------------+
| class |
| course |
| course2teacher |
| score |
| student |
| teacher |
+----------------+
6 rows in set (0.00 sec)
只有表结构 没有表数据
注意:不推荐使用视图,视图太多的话不利于数据库后期扩展
触发器
使用触发器可以定制用户对表进行增删改操作时前后的行为,注意,没有查询
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
存储过程
存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql
使用存储过程的优点:
1、用于替代程序写的sql语句,实现程序与sql的解耦
2、基于网络传输,传别名的数据量小,而直接传sql数据量大
使用存储过程的缺点:
1、程序员扩展功能不方便
无参存储过程
delimiter //
create procedure p1()
BEGIN
select * from db7.teacher;
END //
delimiter ;
MySQL中调用
call p1();
Python中调用
cursor.callproc('p1')
有参存储过程
mysql> delimiter //
mysql> create procedure p2(in n1 int,in n2 int,out res int)
-> BEGIN
-> select * from db7.teacher where tid > n1 and tid < n2;
-> set res = 1;
-> END //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
MySQL中调用
set @x=0
call p2(2,4,@x);
select @x
Python中调用
cursor.callproc('p2', (2, 4, 0))
# print(cursor.fetchall())
cursor.execute('select @_p2_2')
print(cursor.fetchone())
程序与数据库结合使用的三种方式
方式一:
MySQL:存储过程
程序:调用存储过程
方式二:
MySQL:
程序:纯SQL语句
方式三:
MySQL:
程序:类和对象 ORM->纯SQL语句
事务
事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据的完整性。
start transaction; 开启事务
sql语句
rollback; 回滚
commit; 提交
索引
移步林海峰老师博客
http://www.cnblogs.com/linhaifeng/articles/7274563.html