mysql第二天
Mysql 第二天
补充
索引
增删改查
多表联合查询
补充
表字段改名 change
1.修改字段类型 modify 2.添加字段 add first/after3.删除字段 drop4.表改名 rename 补充 5.字段改名 change ?alter table users change 旧字段 新字段 类型(长度);
MySQL 查看帮助
? 关键字
数据类型 排序
最大到最小 字符串类型->时间类型->数值类型
性别 一般存数值类型
时间可以存时间戳
如果精度要求高 选择 decimal 字符串型的浮点数
索引
比如新华字典的 目录 比如书的目录
目的是 快速找到制定的内容
如果不用索引 需要从头到尾轮询 效率慢 索引 就是 先划定一个范围
类型
普通索引
唯一索引
主键索引
全文索引
复合索引
普通索引
最基本的索引没有任何限制的
? index 发现创建索引有两种方式 1.alter table 2.create index ?区别: create index 不能创建主键索引 ?alter table 表名 add index(字段名);#创建索引 这个没有制定索引名称 那么名称跟字段名相同alter table 表名 add index 索引名称(字段名); #创建索引并且指定索引的名称 show index from 表名\G #查看索引 alter table 表名 drop index 索引名字; #删除索引??create index 索引名字 on 表名(字段名); #创建索引 drop index 索引名称 on 表名; #删除索引 show index from 表名\G??
唯一索引 unique
要求这一列不能有重复值 比如年龄 性别 这样的列
alter table 表名 add unique 索引名称(username);alter table 表名 drop index 索引名称;?
主键索引
每个表一定有个主键 不能有重复值 不能有空值 一般创建表的时候 我们就创建主键索引 比如 id
特殊的唯一索引
alter table users drop primary key; #删除主键索引 如果自动递增这一步报错 ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key?alter table users id int(11) unsigned not null;#消除自动递增 alter table users drop primary key; #再次执行成功 ??添加主键索引 ? alter table users add primary key(id);#创建主键索引 alter table users modify id int(11) unsigned not null auto_increment; #让主键自动递增
全文索引
文章中 或者电商 或者社交网站 对需要全局搜索的内容 进行全文索引
alter table 表名 add fulltext 索引名字(字段名);
复合索引
对多个字段 同时添加索引
text 类型不能添加 普通索引、复合索引
alter table users add index a_test(username,age);
总结 建表的同时添加索引
create table if not exists in_test( id int(11) unsigned not null primary key auto_increment, username varchar(50) not null, password varchar(50) not null, content text not null, index pw(password), unique(username), fulltext(content))engine=myisam default charset=utf8;?
增删改查
增 insert
mysql> desc in_test; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(50) | NO | UNI | NULL | | | password | varchar(50) | NO | MUL | NULL | | | content | text | NO | MUL | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) #上面有几个字段 就要写 对应字段的内容 一个都不能落下 insert into in_test values(1,‘abc‘,‘123456‘,‘kangbazi666‘); #主键 username不能出现重复值 否则报错 #只是插入指定字段对应的内容即可 主键自增 sex 默认值 可以不用插入 其它字段不能为空 insert into in_test(username,password,content) values(‘zhangsan‘,‘123456‘,‘kangbazi888‘); insert into money(username,password,balance,province,age,sex) values(‘laowang‘,‘123456‘,‘1000000‘,‘湖北‘,28,0),(‘laoshen‘,‘aaa‘,123.45,‘湖北‘,40,0),(‘laoning‘,‘11111‘,999999999,‘辽宁‘,38,0),(‘caixukun‘,‘xxxxxxs‘,23456.12,‘日本‘,29,1); #一次性插入多条记录
查 select
select * from 表名; # *代表 所有的字段的意思 select 字段1,字段2 from 表名; # 查看指定的字段 mysql> select username,content from in_test; +----------+-------------+ | username | content | +----------+-------------+ | abc | kangbazi666 | +----------+-------------+ 1 row in set (0.00 sec) mysql> select username as 姓名,content as 内容 from in_test; #as 显示结果的别名 +--------+-------------+ | 姓名 | 内容 | +--------+-------------+ | abc | kangbazi666 | +--------+-------------+
删 delete
delete from 表名#清空表 原来有3条记录 下次再插入数据 id 从4开始 除非指定id的值 delete from 表名 where 字段=值;#删除指定的内容 where 是条件
truncate table 清空表
truncate table 表名; #下次插入数据 id下标就从1开始
改 update
update 表名 set 字段=‘新内容‘ where 字段=值; #更新指定的内容
数据库查询 扩展
create table if not exists money( id int(11) unsigned not null primary key auto_increment, username varchar(64) not null, password char(64) not null, province varchar(16) not null, balance float not null, age tinyint default 18, sex tinyint default 0 )engine=myisam default charset=utf8; insert into money(username,password,balance,province,age,sex) values(‘laowang‘,‘123456‘,‘1000000‘,‘湖北‘,28,0),(‘laoshen‘,‘aaa‘,123.45,‘湖北‘,40,0),(‘laoning‘,‘11111‘,999999999,‘辽宁‘,38,0),(‘caixukun‘,‘xxxxxxs‘,23456.12,‘日本‘,29,1); #一次性插入多条记录
distinct 查询单个字段不重复记录
select distinct age from money; #查询money 表年龄唯一的结果 select distinct 字段 from 表名;
where 条件
select * from money where age=18; select 字段 from 表名 where 字段=值;
| 运算符 | 说明 |
|---|---|
| > | |
| < | |
| >= | |
| <= | |
| != | |
| = | |
| or | 或者 |
| and | 并且 |
select username as ‘姓名‘,balance as ‘余额‘,province as ‘省份‘ from money where id<10 and province=‘湖北‘;
结果集排序 order by
asc 升序
desc 降序
select id,username,balance,province from money order by balance desc;#按照余额降序排列 select id,username,balance,province from money order by balance asc;#升序 select 字段1,字段2,字段n from 表名 order by 字段 关键词; 多个字段排序 如果第一个字段已经将结果排序ok,那么第二个字段将会被忽略 mysql> select id,username,balance,province,age from money order by balance desc,age asc;
结果集限制 limit
mysql> select * from money limit 5;
限制结果集并且排序
mysql> select id,username,balance,province,age from money order by balance desc,age asc limit 5;
结果集区间选择 limit
select 字段 from 表名 limit 偏移量,数量; 下标从0开始 mysql> select * from money limit 2,5; #下标为2 第三条记录开始 取5条结果 用户从前端提交 下标 数量(程序员自己设定) 第1页 0 3 第2页 3 3 第3页 6 3 第4页 9 3 下标 = （第几页-1）* 每页显示的数量
统计函数
| 函数 | 作用 |
|---|---|
| count（） | 统计总数 |
| sum() | 求和 |
| avg() | 平均数 |
| max() | 最大值 |
| min() | 最小值 |
mysql> select count(id) from money; #统计总数 +----------+ | count(id) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) mysql> select sum(balance) from money; +-------------------+ | sum(balance) | +-------------------+ | 9756671443.651169 | +-------------------+ 1 row in set (0.00 sec) mysql> select avg(balance) from money; +-------------------+ | avg(balance) | +-------------------+ | 1219583930.456396 | +-------------------+ 1 row in set (0.01 sec) mysql> select max(balance) from money; +--------------+ | max(balance) | +--------------+ | 7666670080 | +--------------+ 1 row in set (0.00 sec) mysql> select min(balance) from money; +--------------------+ | min(balance) | +--------------------+ | 123.44999694824219 | +--------------------+ 1 row in set (0.00 sec)
分组 group by
进入mysql select @@sql_mode; 会发现有 ONLY_FULL_GROUP_BY 表名这是按照Oracle的分组规则 也就是说 select 所有的列都要在分组中 mysql> select @@sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 修改配置文件 sudo vim /etc/mysql/mysqld.conf.d/mysqld.cnf [mysqld] sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION service mysql restart 重新登录mysql select @@sql_mode; 就发现 ONLY_FULL_GROUP_BY 取消了 mysql> select * from money group by province; # 相同的省份只显示一个 +----+------------+----------+------------+----------+------+------+ | id | username | password | balance | province | age | sex | +----+------------+----------+------------+----------+------+------+ | 6 | 郭德纲 | 1112212 | 1000000000 | 天津 | 50 | 0 | | 7 | bingbing | 1122345 | 88888900 | 山东 | 45 | 1 | | 4 | caixukun | xxxxxxs | 23456.1 | 日本 | 29 | 1 | | 5 | xiaoyueyue | 11233 | 88888.1 | 河南 | 18 | 0 | | 1 | laowang | 123456 | 1000000 | 湖北 | 28 | 0 | | 3 | laoning | 11111 | 1000000000 | 辽宁 | 38 | 0 | +----+------------+----------+------------+----------+------+------+ 6 rows in set (0.00 sec) mysql> select count(province) as ‘人数‘,province from money group by province; #统计省份的数量以后再显示 +--------+----------+ | 人数 | province | +--------+----------+ | 1 | 天津 | | 1 | 山东 | | 1 | 日本 | | 1 | 河南 | | 3 | 湖北 | | 1 | 辽宁 | +--------+----------+ 6 rows in set (0.00 sec) mysql> select count(province) as ‘人数‘,province from money group by province with rollup; # 相比较上面结果 多了同一总数的统计 +--------+----------+ | 人数 | province | +--------+----------+ | 1 | 天津 | | 1 | 山东 | | 1 | 日本 | | 1 | 河南 | | 3 | 湖北 | | 1 | 辽宁 | | 8 | NULL | #这里多了一行总的结果 +--------+----------+ 7 rows in set (0.01 sec)
分组的结果过滤一下 having
mysql> select count(province) as result,province from money group by province having result>1; +--------+----------+ | result | province | +--------+----------+ | 3 | 湖北 | +--------+----------+
总结
select 你选择的列 [as "显示的别名"] from 表 where 条件 group by 分组 [having 分组过滤的条件] order by 字段 limit 偏移量,数量或者 数量
多表联合查询
内连接 inner join on
外连接
左连接 left join on
右连接 right join on
子查询
准备
mysql> create table if not exists users(
uid int(11) unsigned not null primary key auto_increment,
username varchar(64) not null,
password varchar(64) not null
)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> create table if not exists order_goods(
oid int(11) unsigned not null primary key auto_increment,
uid int(11) not null,
name varchar(64) not null,
buytime int(11) not null
)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)内连接
1.select 表1.字段1,表1.字段2 as ‘别名‘,表2.字段1 as ‘别名‘,表2.字段2 from 表1,表2 where 表1.字段=表2.字段; mysql> select users.uid,users.username as ‘用户名‘,order_goods.name as ‘商品名‘,order_goods.buytime from users,order_goods where users.uid=order_goods.uid; +-----+-----------+------------+---------+ | uid | 用户名 | 商品名 | buytime | +-----+-----------+------------+---------+ | 2 | zhicheng | iPhone11 | 1111112 | | 4 | huwei | 杜蕾斯 | 12345 | | 6 | Lee | wawa | 123455 | | 3 | daoke | jinpingmei | 1121212 | +-----+-----------+------------+---------+ select u.uid,u.username as ‘用户名‘,o.name as ‘商品名‘,o.buytime from users u,order_goods o where u.uid=o.uid; #上面的简化版 字段显示别名 as 表起别名 空格即可 2. select 表1.字段1 [as 别名],表n.字段 from 表1 inner join 表n on 条件; mysql> select users.uid as ID,users.username as ‘姓名‘,order_goods.name as "商品名",order_goods.buytime from users inner join order_goods on users.uid=order_goods.uid;
外连接
1.左连接 select 表1.字段1 [as 别名],表n.字段 from 表1 left join 表n on 条件; 以lef join 左边的表为准 用户表 买东西的 和没买的都给你显示 mysql> select users.uid as ID,users.username as ‘姓名‘,order_goods.name as "商品名",order_goods.buytime from users left join order_goods on users.uid=order_goods.uid; +----+----------+------------+---------+ | ID | 姓名 | 商品名 | buytime | +----+----------+------------+---------+ | 2 | zhicheng | iPhone11 | 1111112 | | 4 | huwei | 杜蕾斯 | 12345 | | 6 | Lee | wawa | 123455 | | 3 | daoke | jinpingmei | 1121212 | | 1 | xuelin | NULL | NULL | | 5 | xinxin | NULL | NULL | +----+----------+------------+---------+ 6 rows in set (0.00 sec) 2.右连接 select 表1.字段1 [as 别名],表n.字段 from 表1 right join 表n on 条件; 以right join 右边的表为准 mysql> select users.uid as ID,users.username as ‘姓名‘,order_goods.name as "商品名",order_goods.buytime from users right join order_goods on users.uid=order_goods.uid; +------+----------+------------+---------+ | ID | 姓名 | 商品名 | buytime | +------+----------+------------+---------+ | 2 | zhicheng | iPhone11 | 1111112 | | 4 | huwei | 杜蕾斯 | 12345 | | 6 | Lee | wawa | 123455 | | 3 | daoke | jinpingmei | 1121212 | +------+----------+------------+---------+ 4 rows in set (0.00 sec) 3.如果上面左连接为例子 null 可以显示默认值 （case when 表.字段 is null then 默认值 else 表.字段 end） as ‘别名‘ mysql> SELECT users.uid,users.username as 用户名,(case when order_goods.`name` is NULL THEN ‘没买东西‘ ELSE order_goods.name END) as 商品名 from users LEFT JOIN order_goods on users.uid=order_goods.uid; +-----+-----------+--------------+ | uid | 用户名 | 商品名 | +-----+-----------+--------------+ | 2 | zhicheng | iPhone11 | | 4 | huwei | 杜蕾斯 | | 6 | Lee | wawa | | 3 | daoke | jinpingmei | | 1 | xuelin | 没买东西 | | 5 | xinxin | 没买东西 | +-----+-----------+--------------+ 6 rows in set (0.00 sec) 4.如果上面左连接为例子 null 可以显示默认值 ifnull(表.字段，‘默认值‘) mysql> SELECT users.uid,users.username as 用户名, IFNULL(order_goods.name,"空空 如也") as 商品名 from users LEFT JOIN order_goods on users.uid=order_goods.uid; +-----+-----------+--------------+ | uid | 用户名 | 商品名 | +-----+-----------+--------------+ | 2 | zhicheng | iPhone11 | | 4 | huwei | 杜蕾斯 | | 6 | Lee | wawa | | 3 | daoke | jinpingmei | | 1 | xuelin | 空空如也 | | 5 | xinxin | 空空如也 | +-----+-----------+--------------+ 6 rows in set (0.00 sec)
子查询
1.SELECT * from users where uid in(2,3,4,6);# 查询购买用户的详细信息 括号中的值是写死的 2.SELECT * from users where uid in(select uid from order_goods);#先从一个表中查出记录再 将结果作为条件 从另外表中查询 in not in = != exists not exists
union union all
两个表的数据按照一定条件查询出来 结果并到一起显示
mysql> select uid from users union all select uid from order_goods; +-----+ | uid | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 2 | | 4 | | 6 | | 3 | +-----+ 10 rows in set (0.00 sec) mysql> select uid from users union select uid from order_goods; #去除重复记录 +-----+ | uid | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +-----+ 6 rows in set (0.00 sec)
DCL 数据库控制语句
首先选中 mysql
每个数据库服务器都有一个mysql数据库
mysql 数据库存放着 权限用户等 重要信息 每个数据库都是不同的
grant 权限 on 数据库.数据表 to ‘用户名‘@‘主机‘ identified by ‘密码‘ with grant option; 权限: all 所有权限 select insert update delete 数据库: * 所有的数据库 数据表: * 所有的数据表 用户名: 用户名如果不是存在的 那么就会新建 主机: % 任意主机 flush privileges; #刷新权限 mysql> grant select,insert on test.users to ‘mxl‘@‘%‘ identified by ‘666666‘; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) revoke 权限 on 数据库.数据表 from ‘用户名‘@‘主机‘; flush privileges; #刷新权限 mysql> revoke insert on test.users from ‘mxl‘@‘%‘; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
数据库的备份及恢复 Linux命令
备份 mysqldump
mysqldump -u root -p test > /tmp/test.sql
恢复 mysql
mysql -u root -p test < /tmp/test.sql
计划任务 备份数据库
which mysqldump crontab -e 0 2 * * * /usr/bin/mysqldump -u root -p123456 数据库名 > /tmp/test.sql
Python 操作mysql
python不能直接操作mysql 需要通过 扩展
pymysql(开源)
安装pymysql
1.pip install pymysql 2.sudo apt-get install git git clone https://github.com/PyMySQL/PyMySQL.git cd PyMySQL/ python setup.py install
Python 操作 mysql 五行拳
连接数据库
创建游标
执行sql 语句
获取结果集
关闭连接
#encoding:utf-8
import pymysql
#连接数据库
#主机地址
#用户名
#密码
#数据库名字
#五行拳第一拳
db = pymysql.connect("127.0.0.1",‘root‘,‘123456‘,‘test‘)
#第二拳
cursor =db.cursor()
#准备sql语句
sql = ‘select * from money order by balance limit 3‘
#执行sql语句
cursor.execute(sql)
#获取结果集
data = cursor.fetchone()
print(data)
#释放句柄对象
cursor.close()
#关闭mysql 连接
db.close()创建数据表
#encoding:utf-8
import pymysql
#连接数据库
#主机地址
#用户名
#密码
#数据库名字
#五行拳第一拳
db = pymysql.connect("127.0.0.1",‘root‘,‘123456‘,‘test‘)
#第二拳
cursor =db.cursor()
#准备sql语句
sql = ‘create table if not exists kangbazi(id int(11) unsigned not null primary key auto_increment,username varchar(64) not null)engine=innodb default charset=utf8‘
#执行sql语句
cursor.execute(sql)
#释放句柄对象
cursor.close()
#关闭mysql 连接
db.close()插入数据
#encoding:utf-8
import pymysql
#连接数据库
#主机地址
#用户名
#密码
#数据库名字
#五行拳第一拳
db = pymysql.connect("127.0.0.1",‘root‘,‘123456‘,‘test‘)
#第二拳
cursor =db.cursor()
#准备sql语句
sql = ‘insert into kangbazi(username) values("kangbazi1"),("kangbazi2")‘
try:
#执行sql语句
cursor.execute(sql)
db.commit()
except:
db.rollback() #只有innodb引擎才支持事务
#释放句柄对象
cursor.close()
#关闭mysql 连接
db.close()作业
1.封装一个类实现增删改查
相关推荐
CoderToy 2020-11-16
emmm00 2020-11-17
王艺强 2020-11-17
ribavnu 2020-11-16
bianruifeng 2020-11-16
wangshuangbao 2020-11-13
苏康申 2020-11-13
vivenwan 2020-11-13
moyekongling 2020-11-13
云中舞步 2020-11-12
要啥自行车一把梭 2020-11-12
aydh 2020-11-12
kuwoyinlehe 2020-11-12
minerk 2020-11-12
vitasfly 2020-11-12
jazywoo在路上 2020-11-11
敏敏张 2020-11-11
世樹 2020-11-11