MySQL基础二

Mysql 更新 删除 权限管理 高级应用 优化

增删改查之 改 更新 update

update 表名 set 字段1=值1,字段2=值2 where 条件；
​
mysql> update user set username="哈哈",password="123456" where uid=8;  #多个字段 用,号隔开

字段加别名 as 别名 表名 加别名 空格 别名

同时对两个表更新

update 表1,表2 set 字段1=值1 where 条件  
​
update money m,users u set m.balance=m.balance*u.id,u.password=m.balance where m.id=u.id;

 

删除操作 delete

delete from 表名 where 条件；
mysql> delete from money where id>9;
Query OK, 2 rows affected (0.00 sec)

清空表

delete from  表名；
插入元素 id 从原来的继续累加  
​

truncate 清空表以后 id 从1 开始

mysql> truncate table users;
Query OK, 0 rows affected (0.02 sec)
​
mysql> insert into users(username,password) values("haha","123asd"),("xixi","123weqwqsa");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
​
mysql> select * from users;
+----+----------+------------+
| id | username | password   |
+----+----------+------------+
|  1 | haha     | 123asd     |
|  2 | xixi     | 123weqwqsa |

 

 

数据库权限管理　　　

创建数据库用户

grant 权限 on 库名.表名 to '用户名'@'ip地址' identified by 密码；
​
mysql> grant select,insert on python1804.* to 'jinxingping'@'localhost' identified by '123321';
新建一个用户 jinxingping  密码 123321 只对 Python1804数据库下面所有的表 拥有查询 和插入的权限  
​
​
授权 windows 远程连接 mysql  
grant all on *.* to 'root'@'主机地址' identified by '密码';
​
flush  privileges； 刷新权限  
​

删除权限

mysql> revoke insert on python1804.* from 'jinxingping'@'localhost';  
删除 jinxingping用户 对 Python1804下面所有表的  插入权限  

高级应用 -表复制

users 已经存在的表
复制表结构  
create table user1 like users；
​
复制表数据 
insert into user1 select * from users；

索引

alter table  
create 
上面两个都能创建索引 区别在于 create不能创建主键索引  
​
? index  mysql查看帮助文件   
​
mysql> create index in_name on t1(name); 
                   索引名字
mysql> show index from t1\G;  查看索引
mysql> drop index in_name on t1; 删除索引
​
​
mysql> create unique index un_name on t1(name); 
mysql> drop index un_name on t1;
​
​
删除主键索引 首先 取消主键自动递增   
​
mysql> 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     
mysql> alter table users modify id int(11) unsigned not null;      
Query OK, 4 rows affected (0.03 sec)                                                             
Records: 4  Duplicates: 0  Warnings: 0                                                                                                                                                            mysql> alter table users drop primary key;                                                       Query OK, 4 rows affected (0.03 sec)                                                            Records: 4  Duplicates: 0  Warnings: 0   
​
alter table users add index in_name(name);
alter table users add unique un_name(name);
alter table users add primary key(id);
alter table users add fulltext fu_name(content);
alter table users drop index 索引名字；
​
创建符合索引  
alter table users add index in_name(name,content);

mysql 视图 将经常查询的区间内容放到临时表里

mysql> insert into t1(name) values("user1"),("user2"),("user3"),("user4"),("user5"),("user6"),("user7"),("user8"),("user9"),("user10");
? view  
​
mysql> create view v_t1 as select * from t1 where id>2 and id<8;
Query OK, 0 rows affected (0.02 sec)
​
v_t1 就是临时表的名字   
​
​
主表存在 临时表存在   主表消失 临时表不能用   
​
mysql> drop view v_t1; 删除视图   
​
mysql> show create view v_t1;

 

mysql 内置函数

mysql 显示内容 用 select  
字符串函数  
mysql> select concat("hello","world") as nihao; #拼接字符串
select lcase("MYSQL");#转小写
select ucase("mysql"); #转大写
mysql> select length("haha"); 字符串的长度
mysql> select ltrim("    haha"); 去除左侧空格
+-------------------+
| ltrim("    haha") |
+-------------------+
| haha              |
+-------------------+
1 row in set (0.00 sec)
​
mysql> select rtrim("    haha      ");去除右侧空格
mysql> select repeat("重要的事情说三遍",6);
select replace("java 是世界上最好的语言","java","python");
mysql> select substr("python is so good",2,7); 从第二个字符开始  截取7个  mysql 从1开始 
mysql> select concat(space(20),'haha'); #生成20个空格   
数学函数  
mysql> select ceiling(123.1);
mysql> select floor(123.1);
mysql> select rand(); 求随机数  0-1之间的小数 
mysql> select bin(10);
​
​
日期函数
mysql> select curdate(); 返回日期  2018-7-25
mysql> select curtime(); 11:21:33
mysql> select now(); 日期+时间  2018-07-25 11:22:14  
mysql> select week(now()); 返回29 表示当前第几周   
mysql> select year(now()); 返回2018 
mysql> select unix_timestamp(now()); 当前时间的时间戳 
mysql> select from_unixtime(1532489069);
+---------------------------+
| from_unixtime(1532489069) |
+---------------------------+
| 2018-07-25 11:24:29       |
+---------------------------+ 将当前的时间戳格式化为日期  

 

mysql 预处理

select * from users where id>?;
​
mysql> prepare ready from "select * from t1 where id>?"; 已经预执行了select * from t1 where id>?
​
set @i=2;
​
execute ready using @i;
​
我们只需要重新设置@i的值 然后execute就可以  

 

mysql 事务

什么是事务 ？

数据库系统区别其它以其文件系统类型的重要特性
可以使具有原子性的sql语句 或者是一个独立的工作单元  

四个特性 acid

• 原子性 全部成功 才算成功 有失败 全部回滚 atomicity

• 一致性 总金额保持不变 consistent

• 隔离性 isolation

  • 四个等级

    • 读未提交 read uncommitted 脏读 事务读到的是还没有提交的事务

    • 读提交 read committed 解决脏读 带来的问题 就是 进行读操作的 时候 没有拦住 其他更新的 这个问题叫做 不可重复读 读到的结果是更新之前的数据

    • 重复读 repeatable read 解决上一个不可重复读的问题 一个事务进行更新 另外一个事务 进行读取 读到的结果是更新以后的数据 幻读

    • 序列化 seriallize 解决脏读 不可重复读 幻读

    • 上面四个等级 由低到高 读未提交最低 序列化 最高

    • Oracle sqlserver 默认的隔离级别是 读提交

    • mysql 默认的隔离级别是 重复读

• 持久性 durablity 只要 硬盘不损坏 就行

 

 

mysql 5.6及以后默认的引擎是 innodb 以前是myisam 只有innodb 引擎支持事物

开启事务之前  关闭自动提交  
mysql> set autocommit=0;
​
select * from t1；
delete from t1 where id>1;
rollback;回滚
​
show create table t1；查看引擎 是什么  
alter table t1 engine=myisam；
​
mysql> insert into t1(id,name) values(6,"user6"),(7,"user7");
​
​
mysql> savepoint p1;
​
​
mysql> insert into t1(id,name) values(8,"user8"),(9,"user9");
​
​
mysql> savepoint;
Query OK, 0 rows affected (0.00 sec)
​
mysql> insert into t1(id,name) values(10,"user10"),(11,"user11");
​
​
mysql> savepoint p3;
​
​
delete from t1;
rollback to p2； 
​
delete from t1;
rollback to p1；  可以
​
delete from t1;
rollback to p3；  不可以回到 p2以后的时间点 

 

mysql 存储 类似于函数 存储的是一个代码段

 ? procedure
 往t1 数据库中插入 10000条数据  
\d //  更改结束符  以// 结束  
mysql> create procedure p2()
    begin
    set @i=1;
    while @i<=10000 do
   insert into t1(id,name) values(@i,concat("user",@i));
   set @i=@i+1;
   end while;
   end //
   
  \d ;
  
  show procedure status; 了解  
  show create procedure p1\G 查看创建 过程   
  
  call p2；

 

mysql 触发器

t1 t2两张表 一模一样 如果t1中数据发生变动 t2表也跟着变动

 ? trigger 
 \d // 
 mysql> create trigger t_1 before insert on t1 for each row  在插入到t1之前 循环讲数据插入到t2中 
    -> begin
    -> insert into t2(name) values(new.name);
    -> end //
    \d ;
    
    show triggers;列出所有的触发器
    
  \d // 
 mysql> create trigger d_1 before delete on t1 for each row  在插入到t1之前 循环讲数据插入到t2中 
    -> begin
    ->delete from t2 where name=old.name;
    -> end //
    \d ;   
    
    
   \d // 
 mysql> create trigger u_1 before update on t1 for each row  在插入到t1之前 循环讲数据插入到t2中 
    -> begin
    -> update t2 set name=new.name where name=old.name;
    -> end //
    \d ;      
  
    

 

mysql 分区

腾讯QQ号 表 10几亿条数据  
分成 100张表   
​
10101 %100 = 1
41334424234 %100 =4
12112121 %100 =1 
434343%100 =43
​
​
ads1223
23dar 
dsfsdf12   分成36 张表  截取 前2位  
1234sdf
887adfasdf
asdfasd122
​
​
​
中文  
  Python中文转拼音类库
  转成hash值  字母加数字   
​

 

mysql 分区 数据库在服务器上 是以文件的形式存在 一个表就是一个目录

对于用户来说 还是把数据表当成一张表来对待 物理形态上分成了五个分区

mysql 端口号 3306 
安装 mysql   
   sudo apt-get install mysql-server  
   sudo apt-get install mysql-client 
   sudo apt-get install libmysqlclient-dev 
看是否安装成功 
 sudo netstat -ntlp | grep mysql 
 
 
 mysql -u root -p  连接数据库  
 
 mysql> grant all on *.* to 'root'@'localhost' identified by '123456' with grant option;
mysql> flush privileges;
​
cd /etc/mysql/mysql.conf.d
vim mysqld.cnf
bind-address            = 0.0.0.0
​
 重启 service mysql restart  
​
 
 
mysqlisam 在服务上 以文件的形式存在   frm 表结构文件  myd 数据文件  myi 索引文件 
mysql> create table t1(id int)engine=myisam partition by hash(id) partitions 5;
Query OK, 0 rows affected, 1 warning (0.01 sec)
​
 cd /var/lib/mysql/python1804   
​
​
mysql> create index in_id on t1(id);  
​
​
insert into t1 select * from t1; 倍增的方式写入数据  
存储过程  procedure  也可以写入数据  
​
​
cd /var/lib/mysql/python1804 
watch -n1 ls -lh

 

 

优化的步骤 :

show status 查看sql的执行频率  也就是说 你这个表 改的多还是 读的多 
mysql> show status like 'Com_select';
mysql> show status like 'Com_insert';
mysql> show status like 'Com_update';
mysql> show status like 'Com_delete';
​
mysql> show status like 'InnoDB_rows_read';
mysql> show status like 'InnoDB_rows_inserted';
mysql> show status like 'InnoDB_rows_updated';
mysql> show status like 'InnoDB_rows_deleted';
​
​
mysql> show status like "connections";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 19    |
+---------------+-------+
mysql> show status like "uptime";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 3494  |
+---------------+-------+
​
mysql> show status like "%slow%";  慢查询的次数  
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 0     |
+---------------------+-------+
​
开启慢查询 
​
   vim /etc/mysql/mysql.conf.d/mysqld.cnf  
   
   [mysqld] 
   long-query-time = 1  记录查询超过1秒的
   slow-query-log = on  开启慢查询
   slow-query-log-file = slow.txt 慢查询语句记录的位置
   log-queries-not-using-indexes 记录那些语句没有使用到慢查询  
​
​
接下来 我们就可以使用 explain  或者desc 去分析
​