当前位置: 澳门新濠3559 > 数据库 > 正文

mysql数据库修改用户密码,MYsql的数据备份

时间:2019-12-27 19:58来源:数据库
一、数据库的操作及安装 一、MySQL的数据备份 一:数据库启动/停止 启动 1、安装 语法:# mysqldump -h 服务器 -u用户名 -p密码 数据库名 备份文件.sql#示例:#单库备份mysqldump -uroot -p123 db

一、数据库的操作及安装

一、MySQL的数据备份

一:数据库启动/停止
启动

1、安装

语法:
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

#示例:
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

service mysqld start
/etc/init.d/mysqld start

MySQL`` Commu``nity`` Server ``5.7``.``16   ----   版本

MYsql的数据备份
     备份与恢复操作:

关闭
service mysqld stop
/etc/init.d/mysqld start
mysqladmin -u root -p shutdown

2、初始化

如:

二:mysql数据库修改用户密码,赋权
2.1 修改mysql用户密码

*  mysqld*--inisialize-insecure**

       mysqldump - uroot day43 > E:\day43.sql
    drop database day43; 删除原先的文件夹

方法1
mysqladmin -u用户名 -p'就密码' password '新密码'

*3、启动*

   show databases; 查看全部的文件夹

方法2

**  mysqld**

  create database day43; 创建一个文件夹

通过修改(插入)数据库mysql中user表记录达到目的
# /var/mysql5/bin/mysql -h localhost -uroot -p
Enter password:不输入任何东西(新装数据库root密码为空,直接回车即可)
mysql> use mysql
mysql> update user set password=password('新密码') where USER='用户名' and HOST='主机名';
根据你的需要修改sql语句的where条件,host 可以为%(任意主机),ip地址,localhost

**4、启动MySQL客户端并连接MySQL服务**

 use day43; 切换到day43这个文件夹下
source E:\day43.sql; 恢复数据

##修改密码生效,一定要刷新权限哦
mysql> flush privileges;

mysqladmin -h localhost -uroot -ppassword reload
刷新系统权限表,这个不能少,否则不生效。

*** mysqld*--initialize-insecure命令,其默认未给root账户设置密码**

 

方法3
通过grant赋权命令
# /var/mysql5/bin/mysql -h localhost -uroot -p
Enter password:不输入任何东西(新装数据库root密码为空,直接回车即可)
mysql>grant 权限1,权限2,…权限n on 库.表 to 用户@主机名 identified by '用户密码';
如:
mysql> grant all on *.* to [email protected]'%' identified by '123456'
mysql> grant all on *.* to [email protected] identified by '123456'

***5、查看是否MySQL启动***

 


** tasklist |findstr**mysql**  **

mysqldump - uroot day51 > D:\day51.sql
1、登录MySQL恢复:
drop database day51;
create database day51;
source D:\day51.sql;

权限1,权限2,…权限n代表:
select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。
当权限1,权限2,…权限n被all privileges或者all代替,表示赋予用户全部权限。
当数据库名称.表名称被*.*代替,表示赋予用户操作服务器上所有数据库所有表的权限。
用户地址可以是localhost,也可以是ip地址、机器名字、域名。也可以用’%'表示从任何地址连接。

6、数据库的基本命令

 

‘连接口令’不能为空,否则创建失败。

mysql>flush privileges;

mysqladmin -h localhost -uroot -ppassword reload
刷新系统权限表,这个不能少,否则不生效。

三:mysql常用数据库、表、字段、索引管理操作

3.1 创建数据库命令,建库

#当数据库databasename不存在就增加,并指定字符集utf8和校验字符集utf8_general_ci
create database IF NOT EXISTS databasename default charset utf8 COLLATE utf8_general_ci;

#直接创建数据库databasename,字符集是mysql默认配置指定的(取决于my.cnf的配置)
create database databasename;

#当数据库databasename不存在就增加,并指定字符集utf8
create database IF NOT EXISTS databasename default charset utf8

3.2 建表
#表存在就删除重建(无提示哦)
DROP TABLE IF EXISTS `表名`;
CREATE TABLE `表名` (
`字段值1` int(11) NOT NULL AUTO_INCREMENT,
`字段值2` varchar(30) DEFAULT NULL,
……
PRIMARY KEY (`字段值1`)
) ENGINE=存储引擎(如MyISAM,innodb) AUTO_INCREMENT=1 DEFAULT CHARSET=字符集(如utf8);

#直接建表(表存在的话会提示表已经存在)
CREATE TABLE `表名` (
`字段值1` int(11) NOT NULL AUTO_INCREMENT,
`字段值2` varchar(30) DEFAULT NULL,
……
PRIMARY KEY (`字段值1`)
) ENGINE=存储引擎(如MyISAM,innodb) AUTO_INCREMENT=1 DEFAULT CHARSET=字符集(如utf8);

如:
DROP TABLE IF EXISTS `yuanshi`;
CREATE TABLE `yuanshi` (
`goods_no` varchar(30) DEFAULT NULL,
`pid_color` varchar(30) DEFAULT NULL,
`products_no` varchar(30) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM AUTO_INCREMENT=724 DEFAULT CHARSET=utf8;

CREATE TABLE `yuanshi` (
`goods_no` varchar(30) DEFAULT NULL,
`pid_color` varchar(30) DEFAULT NULL,
`products_no` varchar(30) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`goods_id`)
) ENGINE=INNODB AUTO_INCREMENT=724 DEFAULT CHARSET=utf8;

3.3字段管理
##增加字段
alter table 表名 add 字段名 字段类型

##增加整数性字段
alter table table_name add filed_name int(11) DEFAULT '0';
##增加日期行字段
alter table yuanshi add up_time datetime DEFAULT NULL;
##增加微整形字段
alter table yuanshi add is_del tinyint(1) NOT NULL DEFAULT '0';
##增加字符串字段
alter table yuanshi add unit char(10) DEFAULT NULL;
alter table yuanshi add description varchar(255) DEFAULT NULL;

#在end_time字段后面增加一个字段
alter table table_name add filed_name int(11) DEFAULT '0' AFTER `end_time`;

##修改字段point
ALTER TABLE `linuxshizhan` MODIFY COLUMN `point` int(11) NOT NULL DEFAULT 0 COMMENT '积分' ;
#修改weight字段后面的这个point字段
ALTER TABLE `linuxshizhan` MODIFY COLUMN `point` int(11) NOT NULL DEFAULT 0 COMMENT '积分' AFTER `weight`;

##删除字段tag_ids
ALTER TABLE `linuxshizhan` DROP COLUMN `tag_ids`;

3.4 create index 给某个字段增加索引
语法:
create [unique | fulltext |spatial] index index_name
[using index_type]
on tbl_name (index_col_name,...)
其中:
index_name:索引的名称,索引在一个表中名称必须是唯一的。
USING index_type:部分存储引擎允许在创建索引时指定索引的类型。index_type为存储引擎支持的索引类型的名称,MySQL支持的索引类型有BTREE和HASH。如果不指定USING子句,MySQL自动创建一个BTREE索引。
index_col_name:col_name表示创建索引的列名。length表示使用列的前length个字符创建索引。使用列的一部分创建索引可以使索引文件大大减小,从而节省磁盘空间。在某些情况下,只能对列的前缀进行索引。例如,索引列的长度有一个最大上限,因此,如果索引列的长度超过了这个上限,那么就可能需要利用前缀进行索引。BLOB或TEXT列必须用前缀索引。前缀最长为255字节,但对于MyISAM和InnoDB表,前缀最长为1000字节。另外还可以规定索引按升序(ASC)还是降序(DESC)排列,默认为ASC。如果一条SELECT语句中的某列按照降序排列,那么在该列上定义一个降序索引可以加快处理速度。
UNIQUE | FULLTEXT | SPATIAL:UNIQUE表示创建的是唯一性索引;FULLTEXT表示创建全文索引;SPATIAL表示为空间索引,可以用来索引几何数据类型的列

重要提示:
CREATE INDEX语句并不能创建主键。
在一个索引的定义中包含多个列,中间用逗号隔开,但是它们要属于同一个表。这样的索引叫做复合索引。

如:
CREATE INDEX idx_firstLetter on tb_drug_base(firstLetter);

3.5 通过alter table增加索引

1.PRIMARY KEY(主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE (`column` )

3.INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

5.多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

3.6 查看索引
SHOW INDEX FROM tbl_name [FROM db_name]

澳门新濠3559,四:设置数据库编码(字符集)

4.1.设置整个数据库编码

a.启动mysql的时候,mysqld_safe命令行加入
--default-character-set=gbk
gbk为字符集编码

b.修改my.cnf,在 [mysqld] 中加入
default-character-set=gbk
gbk为字符集编码

4.2.改变某个库的编码格式:在mysql提示符后输入命令
# mysql安装目录/bin/mysql -hlocalhost -uroot -p
mysql>alter database 数据库名 default character set gbk;
gbk为字符集编码
显示字符集编码相关变量
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
………………………………………………
+----------------------+-----------------+
mysql>show variables like 'character_set%'
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
………………………………………………
+--------------------------+--------+
设置字符集相关变量
mysql>SET character_set_client = gbk;
mysql>SET character_set_connection = gbk;
mysql>SET character_set_results = gbk;

五、mysql常用管理命令

mysql>shell下
show databases; 显示所有数据库
show processlist; 查看mysql进程列表
show full processlist; 查看mysql进程列表,含详细SQL
show tables; 显示所有表
show variables; 显示变量
use databasename; 切换数据库
desc tablename; 查看表结构
show table status; 查看表状态
show status; 查看系统状态
show slave status; 查看从库状态
FLUSH TABLES WITH READ LOCK; 锁定数据库,一般执行备份钱或者建立主从快照前
UNLOCK TABLES; 解除锁定,一般执行完备份的时候或者建立主从快照时

mysqladmin -h localhost -uroot -ppassword processlist 查看mysql进程列表

六、mysql日志管理
mysql>flush logs;
# 按文件:删除mysql-bin.000354之前的日志,不包含mysql-bin.000354
MYSQL>purge binary logs to 'mysql-bin.000354';

Query OK, 0 rows affected (0.16 sec)

# 按时间:删除2013-08-10 00:00:00 之前的日志

MYSQL>purge binary logs before '2013-08-10 00:00:00';

# 按时间:请理三天之前的日志

MYSQL> purge master logs before date_sub(now(), interval 3 day);

自动清理日志 :

# 修改my.cnf文件配置bin-log过期时间,如日志保留7天

[mysqld]

expire-logs-days=7

当然,做了flush logs;动作后,直接到mysql数据文件夹,直接用rm -f mysql-bin.相关的文件(最新的那个日志文件不能删除哦!!!)
假设最新的日志文件是mysql-bin.000354这个,那么你就可以直接到mysql数据文件夹
rm -f mysql-bin.000353
rm -f mysql-bin.000352
如你不确定可以通过ls -lht 确认mysql数据文件夹的最新日志文件到底是那个。
当然本人还是建议你使用purge binary logs 去删除比较保险哦

七、常用mysql数据库备份
7.1.免费工具

a.直接拷贝数据文件,需要停止数据库,不适应生成系统。
直接拷贝数据文件最为直接、快速、方便,但缺点是基本上不能实现增量备份。 为了保证数据的一致性,需要在备份文件前,执行以下 SQL 语句:
mysql>FLUSH TABLES WITH READ LOCK; (或者停止数据库,不适应生产系统。)
也就是把内存中的数据都刷新到磁盘中,同时锁定数据表, 以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单, 直接拷贝回原来的数据库目录下即可。
b.对mysqlisam存储方式的,可以使用hotcopy,mysqldump
c.对innodb存储方式的,使用mysqldump
d.对同时使用innodb和mysqlisam的mysqldump
e.主从复制(当主库数据被意外或者恶意删除后,作为备份的从库也就不能用了。)

7.2.商业软件
IBBackup

7.3 mysql数据库物理备份:直接拷贝mysql数据库文件
先停止mysql数据库
service mysqld stop

运行系统命令
# cp -r mysql数据库的data目录 备份文件存放路径
或者
# tar -cvzf 备份文件名.tar.gz mysql数据库的data目录

mysql数据库物理备份的还原:
直接把文件覆盖到相应数据库data目录即可

7.4 mysql hotcopy热备份,只针对mysiam存储引擎
MySQLHotCopy是一个perl脚本,执行时候锁定数据库表,然后使用系统命令cp或者scp做的一种备份。等备份完成的时候,才释放表锁定,并刷新日志,他是一种非常快捷的方式,但是数据备份只能在通一台机器上,只适合mysqlisam存储方式
命令
# mysql安装目录/bin/mysqlhotcopy --user=用户 --password=密码 -q "数据库名" 备份文件存放目录

还原:
直接把文件覆盖到相应数据库data目录即可

7.5 mysqldump备份
适合mysqlisam和innodb存储方式,常用于数据备份,迁移。
mysqldump -h host -u user -p pass -B 数据库名 > 数据库备份名.sql
将数据库“数据库名”中的所有表备份到“数据库备份名.sql”文件,
“数据库备份名.sql”是一个文本文件,文件名任取。
该命令备份出来的sql中带有create dabatase信息,就是说备份出来的sql在还原时会把存在的库删除重建


mysqldump -uroot -pdcbicc106 -B mysql > mm.sql

##不含建库信息的mysqldump
mysqldump -h host -u user -p pass -B 数据库名 > 数据库备份名.sql

mysqldump -uroot -pdcbicc106 -B -n mysql > mm2.sql

# mysqldump -h host -u user -p pass --opt 数据库名 表1 表2 表3……表n > 数据库备份名.sql
将数据库“数据库名”中的表1 表2 表3……表n 备份到“数据库备份名.sql”文件,
“数据库备份名.sql”是一个文本文件,文件名任取。

# mysqldump -h host -u user -p pass --databases 数据库1 数据库1 > 数据库备份名.sql
将数据库“数据库1”和“数据库2”备份到“数据库备份名.sql”文件,
“数据库备份名.sql”是一个文本文件,文件名任取。

# mysqldump -hhostname -uusername -ppassword -no-data --databases databasename1 databasename2 databasename3 > multibackupfile.sql
仅仅备份数据库结构

# mysqldump -h host -u user -p pass --opt --all-databases > all-databases.sql
将所有数据库备份到all-databases.sql文件,all-databases.sql是一个文本文件,文件名任取。)

还原MySQL数据库

# mysql -hhostname -uusername -ppassword databasename < backupfile.sql
# mysql --default-character-set=utf8 -u xxx [database] < xxx.sql

还原压缩的MySQL数据库
# gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
# gunzip < backupfile.sql.gz | mysql --default-character-set=utf8 -u xxx [database]

将数据库转移到新服务器
# mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

备份有innodb存储方式的
dump参考选项:
--opt --default-character-set=utf8
--triggers -R --hex-blob --all-databases
--flush-logs
--single-transaction
--delete-master-logs
-x

7.6 导出表结构
mysqldump -h192.168.1.30 -uroot -123456 --default-character-set=utf8 -B backdb -d >/bak/biaojiegou.sql

针对mysql5.6使用了全局事务GTID的库,需要加--set-gtid-purged=OFF参数。否则报错
mysqldump -h192.168.1.30 -uroot -123456 --default-character-set=utf8 --set-gtid-purged=OFF -B backdb -d >/bak/biaojiegou.sql

7.7 只导出trigger,只备份trigger
mysqldump -h127.0.0.1 -uroot -p123456 --skip-opt --trigger -d -n -t -B backdb > /bak/backdb_trigger

7.8 导出函数funtcion和event
mysqldump -h192.168.1.30 -uroot -p123456 --default-character-set=utf8 -n -d -t -R -E dbname > /bak/funcation.sql
针对mysql5.6使用了全局事务GTID的库,需要加--set-gtid-purged=OFF参数。否则报错
mysqldump -h192.168.1.30 -uroot -p123456 --default-character-set=utf8 --set-gtid-purged=OFF -n -d -t -R -E dbname > /bak/funcation.sql

7.9 只备份数据库的数据,不带任何表结构和函数,触发器
mysqldump -uroot -pdcbicc106 -n -t -B mysql > mydata.sql

8.0 以分表方法导出数据,这种方式只适合备份和还原只能在mysql所在的服务器上执行哦,丙炔备份目录{BACKUPDIR} 必须有mysql运行用户(一般为mysql用户)的权限,切记切记
如果没有请chown mysql:mysql {BACKUPDIR}
分表导出适合:NOTE: This only works if mysqldump is run on the same machine as the mysqld server.

mysqldump -uroot -p${MYSQLPASSWORD} -T${BACKUPDIR} ${DATABASENAME}
mydump -h localhost -uroot -ppassword -T 备份目录 数据库名 表名

分表方式的数据还原:
mysqlimport -h local -uroot -p`123456` backdb `find /data/backup/database/backdb/2010-02-27/ -name "*.txt"`

八、mysql慢查询日志分享工具

mysqldumpslow :perl工具,mysql 官方自带
mysqlsla :perl工具 功能强大,数据报表齐全,定制化能力强.
mysql-explain-slow-log :perl 无
mysql-log-filter: python or php 不失功能的前提下,保持输出简洁
myprofi :php工具,非常精简

这里推荐mysql慢查询日志分析工具mysqlsla

使用举例
将慢日志mysqlslow.log中执行时间最长的10条sql显示并写到sql_10.log中。
mysqlsla -lt slow -sf "+select" -top 10 mysqlslow.log >sql_10.log

将慢日志mysqlslow.log中数据库为mydb的所有select和update的慢sql,显示并将查询次数最多的100条写到sql_su100.sql中。
mysqlsla -lt slow -sf "+select,update" -top 100 -sort c_sum -db mydb mysqlslow.log >sql_su100.log

mysqlsla -lt slow -sf "+select" -top 100 -sort c_sum mysqld_slow_query.log >sql_su100.log

九:binlog日志分析,通过mysqlbinlog工具解析写操作的sql语句,常用于mysql通过binlog日志恢复数据。
举例:
进入mysql的数据文件夹,如/var/lib/mysql/
cd /var/lib/mysql/
mysqlbinlog --start-datetime="2013-08-14 00:00:00" --stop-datetime="2013-08-14 23:59:59" mysql-bin.000098

启动 service mysqld start /etc/init.d/mysqld start 关闭 service mysqld stop /etc/init.d/mysqld start mysqladmin -u root -p shutdown 二:mysql数据库...

mysqld --inisialize-insecure                                  初始化以没有密码的形式进入

mysqld                                                        启动mysqld

tasklist |findstr mysqld                                       查看是否mysql启动 

mysql -uroot -p                                               链接

quit                                                          退出

tskill mysql                                                  干掉mysql

mysqld --install                                              在winds 中安装mysqld

mysqladmin -uroot password 123                                设置密码

mysqld --skip-grant-tables                                    跳过受限直接启动mysqld

update mysql.user set authentication_string=
password(456) where user='root'and host='localhost';(5.7版本) 修改密码成功

update mysql.user set password=password(5.6版本)

flush privileges;                                             刷新权限

select user();                                                查看当前用户

create user 'mqj'@'localhost' identfied by '123';             创建本机账号

create user 'egon'@'%' identfied by '123'                     创建远程账号

create user 'wupeiqi'@'192.168.20.%' identified by '123'      创建远程网端账号

mysql -h192.168.20.99 -ualex -p123                            远程链接


数据库文件夹的的操作

create database db1 charset utf8;                             增加db1文件夹

show databases ;                                              查看所有数据库

show create database db1;                                     查看db1文件夹

drop database db1;                                            删除db1文件夹

alter database db1 charset gbk;                               修改db1编码

操作文件(表)
切换到文件下:
use db2                                                       切换文件夹

c                                                            取消命令

create table t1(id int,name char(10));                        创建表



show  tables;                                                 查看当前文件下的所有表


show create table t1;                                         查看表

alter table t1 add age int;                                   增加字段

alter table t1 modify name char(12);                          改表中的名字字符

desc t1;                                                      查看表结构

drop table t1;                                                 删除表

操作文件的一行行内容(记录)

insert into db1.t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');   增加记录

select * from t1;                                                查看所有字段对应的值

select  * from t1 where id>2;                                   查看id大于2的字段

select name from t1;                                             查看单个字段

update t1 set name='SB' where id=3;                              改里面的记录

delete from t1 where id=3;                                       删除一条记录

对于清空表的记录有两种方式,但是推荐使用后者
delete from t1;
truncate t1; #当数据量大的时候下,删除速度快                     整体删除


自增id
create table t2(id int primary key,name char(10)); 
create table t3(id int not null unique,name char(10)); 
create table t2(id int primary key auto_increment,name char(10)); 


补充
create table t6 select * from t5;                                  拷贝表


create table t7 select * from t5 where 1=2;                        拷贝表结构

alter tablet7 modify id int primary key auto_increment              改表结构

delete t7 set name='' ;                                             删除记录 





select database()                                             查看当前在那个文件夹下

insert into t1(id) values(1)                                   插数据的方式

select * from t1;                                              查看插入数据的内容

select * from t1;


数据类型
1、数字(默认都是有符号,宽度指的是显示宽度,与存储无关)
(1)tinyint [unsigned][zerofill]  (1个字节存)
        有符号:
            -128~~127
        无符号:
            0~~255
  (2) int [unsigned][zerofill]      (4个字节存)
         有符号:
             -2147483648~~2147482647
         无符号: 
             0~~4294967295     
 (3)bigint[unsigned][zerofill]   (8个字节存)
          有符号:
             -9223372036854775808~~9223372036854775808
           无符号:
             0~~494967295
2、字符(宽度指的是字符个数 与存储有关):
     char   :定长(简单粗暴,不够则凑够固定长度存放起来,浪费空间,存取速度快)
     varchar: 变长(精准,计算除待存放的数据长度,节省空间,存取速度慢)
3、日期
#注册时间
     datatime 2017-09-06 10:39:46
#出生年月日 ,开学时间
     data:2017-09-06
#聊天记录,上课时间
     time:10:39:46
#出生年
     year:2017
4、枚举与集合
enum枚举:规定一个范围,可有多个值,但是为该字段船只是,只能去规定范围中的一个
set集合:规定一个范围,可有多个值,但是为该字段船只是,只能去规定范围中的一个或多个




1:
整型测试
create table t1(id tinyint); 
create table t2(id int); 
create table t3(id bigint) ;

#测试
create table t4(salary float(5,2));

insert into t4 values(3.735);
insert into t4 values(3.735684);



2、char 与 varcahr测试
create table t6(name char(4));
insert into t6 values('alex')
insert into t6 values('欧德博爱');
insert into t6 values('艾利克斯');





create table t7(x char(5),y varchar(5));
insert into t7 values('addd','dsds') #char_length:查看字符长度
 insert into t7 values('你好啊','好你妹')#char_length:查看字符长度

了解
 insert into t7 values('你好啊','好你妹')#length:查看字节长度
 select char_length(x),char_length(y) from t7;



注意两点:
insert into t7 values('abc','abc');#length:查看字节长度
select * from t7 where y='abc    '; #去掉末尾的空格然后去比较

3、日期
create table student(
id int ,
name char(5),
born_date date,
born_year year,
reg_time datetime,
class_time time
);
insert into student values(1,'alex',now(),now(),now(),now());
insert into student values(1,'alex','2017-09-06','2017','2017-09-06 10:09:36','09:06:36');


4、枚举与集合
create table student1(
id int  primary key auto_increment,
name char(5),
sex enum('male','female'),
hobbies set('music','read','coding')
);

insert into student1(name,sex,hobbies) values('egon','male','music,read,coding');





1 简单查询
select * from employee;
select name,salary from employee;

2 where条件
select name,salary from employee where salary > 10000;
select name,salary from employee where salary > 10000 and salary < 20000;
select name,salary from employee where salary between 10000 and 20000;
select name,salary from employee where salary not between 10000 and 20000;

select name,salary from employee where salary = 10000 or salary = 20000 or salary = 30000;
select name,salary from employee where salary in (10000,20000,30000);


select * from employee where salary = 10000 or age = 18 or sex='male';

select * from employee where post_comment is Null;
select * from employee where post_comment = Null;
select * from employee where post_comment is not Null;

select * from employee where name like '%n%';

select * from employee where name like 'e__n';

3 group by分组
mysql> select depart_id,group_concat(name)  from employee group by depart_id;


mysql> select depart_id,count(id)  from employee group by depart_id;


mysql> select depart_id,group_concat(id)  from employee group by depart_id;


mysql> select depart_id,count(id)  from employee group by depart_id;


mysql> select depart_id,max(salary) from employee group by depart_id;


mysql> select depart_id,min(salary) from employee group by depart_id;


mysql> select depart_id,sum(salary) from employee group by depart_id;


mysql> select depart_id,avg(salary) from employee group by depart_id;

2、不登录MySQL直接在外面恢复;
mysql -uroot day51 < D:\day51.sql

 

 

二、数据库文件件的操作

(2)、恢复到指定的库中:
create database bak; 新建一个文件夹
use dak; 切换到dak这个文件夹下
mysql -uroot bak < D:\day51.sql; 恢复数据

 

 

create database db1 charset utf8; 增加db1文件夹

show databases ; 查看所有数据库

show create database db1; 查看db1文件夹

drop database db1; 删除db1文件夹

alter database db1 charset gbk; 修改db1编码

 

 

只备份一个表
***mysqldump

2、操作文件

  • uroot day51 deparment > D:\day51-deparment.sql MySQL 进数据库 create database bakl; 新建一个库 quit 退出 在mysql外面恢复 mysql -uroot bakl < D:\day51-deparment.sql nysql 进数据库 use bakl 切换到dakl这个文件夹下***
切换到文件下:
use db2 切换文件夹

c 取消命令

create table t1(id int,name char(10)); 创建表


show tables; 查看当前文件下的所有表

show create table t1; 查看表

alter table t1 add age int; 增加字段

alter table t1 modify name char(12); 改表中的名字字符

desc t1; 查看表结构

drop table t1; 删除表

 

3、操作文件一行行内容

 

insert into db1.t1 values(1,'egon1'),(2,'egon2'),(3,'egon3'); 增加记录

select * from t1; 查看所有字段对应的值

select * from t1 where id>2; 查看id大于2的字段

select name from t1; 查看单个字段

update t1 set name='SB' where id=3; 改里面的记录

delete from t1 where id=3; 删除一条记录

对于清空表的记录有两种方式,但是推荐使用后者
delete from t1;
truncate t1; #当数据量大的时候下,删除速度快 整体删除


自增id
create table t2(id int primary key,name char(10)); 
create table t3(id int not null unique,name char(10)); 
create table t2(id int primary key auto_increment,name char(10));

直接在数据库外面查看
mysql -uroot -e "use bakl"
mysql -uroot -e "use bakl;show tables;"

4、一些不常用的操作

 

create table t6 select * from t5; 拷贝表

create table t7 select * from t5 where 1=2; 拷贝表结构

alter tablet7 modify id int primary key auto_increment 改表结构

delete t7 set name='' ; 删除记录

 

三、数据库的设计

 

连表的有性能消耗

备份多个库
mysqldump -uroot -e "show databases;" 查看全部的库

(1)、连表设计

 

方式一:

备份
mysqldump -uroot --databases db1 db2 db3 > E:\db1-db2-db3.sql

                    class UserType(models.Model):
                        """
                        用户类型表,个数经常变动
                        """
                        title = models.CharField(max_length=32)

                    class UserInfo(models.Model):
                        """
                        用户表:讲师和班主任
                        """
                        username = models.CharField(max_length=32)
                        password = models.CharField(max_length=64)
                        email = models.CharField(max_length=32)
                        ut = models.ForeignKey(to="UserType")

 

方式二:

MySQL         进数据库

                  - choices
                        # class UserInfo(models.Model):
                        #     """
                        #     用户表
                        #     """
                        #     username = models.CharField(max_length=32)
                        #     password = models.CharField(max_length=64)
                        #     email = models.CharField(max_length=32)
                        #
                        #     user_type_choices = (
                        #         (1, '班主任'),
                        #         (2, '讲师'),
                        #     )
                        #
                        #     user_type_id = models.IntegerField(choices=user_type_choices)

 

四、事务

删除原来的文件夹

事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,

 

即可回滚到原来的状态,从而保证数据库数据完整性。

quit 退出

五、存储

 

存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,

在外面查看
mysql -uroot < E:\db1-db2-db3.sql 恢复数据库
mysqldump -uroot -e "show databases;"

通过调用它的名字可以执行其内部的一堆sql

 

使用存储过程的优点

 

   #1 程序与数据实现解耦

备份所有库
mysqldump -uroot --all-databases > E:\all.sql

   #2 减少网络传输的数据量

二、表的导出和导入

六、数据库优化查询方式

澳门新濠3559 1

   1、优化查询的两种方式:**                           **

 

表的导出和导入

(show variables like '%secur%';查看选项)
导出
use day51; 切换到day51下面
show tables; 查看表
select from userinfo; 查看表下的文件夹*

                           (1):prefetch_related

 

select from userinfo into outfile 'E:\userinfo.txt' fields terminated by ','lines terminated by 'n'; 导出*

 

 

导入
create table user(
id int primary key auto_increment,
name char(32),
age int,
deparment_id int);      建表

load data infile 'E:\userinfo.txt'
into table user fields terminated by ','lines terminated by 'n'; 导入

                           (2):select_related

 

 三、 pymysql模块

#安装
pip3 install pymysql

 

(1) 链接、执行sql、关闭(游标)

1 基本使用
# import pymysql
# #mysql -h localhost -uroot -p123
# conn=pymysql.connect(host='localhost',user='root',password='',database='day47')
# cursor=conn.cursor() #拿到游标,即mysql >
#
# sql='select * from user where id>1;'
# rows=cursor.execute(sql) #拿到受影响的行数
# print('%s rows in set (0.00 sec)' %rows)
#
# cursor.close()
# conn.close()

1.小练习
import pymysql
user=input('用户名>>: ').strip()
pwd=input('密码>>: ').strip()

#链接,拿到游标
conn=pymysql.connect(host='localhost',user='root',password='',database='day47')
cursor=conn.cursor() #拿到游标,即mysql >

#执行sql
sql='select * from user where user="%s" and password="%s";' %(user,pwd)
print(sql)
rows=cursor.execute(sql) #拿到受影响的行数
# print('%s rows in set (0.00 sec)' %rows)

cursor.close()
conn.close()

if rows:
    print('登录成功')
else:
    print('登录失败')

#3:sql注入
import pymysql
user=input('用户名>>: ').strip()
pwd=input('密码>>: ').strip()
conn=pymysql.connect(host='localhost',user='root',password='',database='day47')
cursor=conn.cursor()

sql='select * from user where user="%s" and password="%s";' %(user,pwd)
print(sql)
rows=cursor.execute(sql)
cursor.close()
conn.close()
if rows:
    print('登录成功')
else:
    print('登录失败')

3:解决sql注入
import pymysql
user=input('用户名>>: ').strip()
pwd=input('密码>>: ').strip()
conn=pymysql.connect(host='localhost',user='root',password='',database='day47')
cursor=conn.cursor()

sql='select * from user where user=%s and password=%s;'
rows=cursor.execute(sql,[user,pwd])
print(rows)
cursor.close()
conn.close()
if rows:
    print('登录成功')
else:
    print('登录失败')

4 增删改
import pymysql
conn=pymysql.connect(host='localhost',user='root',password='',database='day47',charset='utf8')
cursor=conn.cursor()


sql='insert into user(user,password) values(%s,%s);'
rows=cursor.execute(sql,('alex','123'))
rows=cursor.executemany(sql,[('yuanhao','123'),('laowu','123'),('kgf','12323')])
print('%s row in set (0.00 sec)' %rows)
conn.commit()

cursor.close()
conn.close()

5 查:fetchone,fetchmany,fetchall
import pymysql
conn=pymysql.connect(host='localhost',user='root',password='',database='day47',charset='utf8')
cursor=conn.cursor()
sql='select * from user;'
rows=cursor.execute(sql)
查单条
res1=cursor.fetchone()
res2=cursor.fetchone()
res3=cursor.fetchone()
print(res1[0])
print(res2)
print(res3)

查多条
print(cursor.fetchmany(3))
print(cursor.fetchone())

查所有
print(cursor.fetchall())
print(cursor.fetchone())

光标的移动
print(cursor.fetchall())
cursor.scroll(1,mode='absolute')
print(cursor.fetchone())
cursor.scroll(3,mode='absolute')
print(cursor.fetchone())

print(cursor.fetchone())
print(cursor.fetchone())
cursor.scroll(-2,mode='relative')
print(cursor.fetchone())

print('%s row in set (0.00 sec)' %rows)
cursor.close()
conn.close()

四、获取插入的最后一条数据的自增ID

澳门新濠3559 2

澳门新濠3559 3

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
cursor=conn.cursor()

sql='insert into userinfo(name,password) values("xxx","123");'
rows=cursor.execute(sql)

conn.commit()
print(cursor.lastrowid) #在commit之前和之后都可以查看
cursor.close()
conn.close()

 

selsect_releated是主动连表,执行一次SQL

prefetch_releated不连表执行3次SQL

七、完整约束

    1、设置唯一约束

如下:

方法一:
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二:
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);

八、数据库的备份

1、备份与数据库的恢复

drop database day43; 删除原先的文件夹

show databases; 查看全部的文件夹

create database day43; 创建一个文件夹

use day43; 切换到day43这个文件夹下
source E:\day43.sql; 恢复数据

1、登录MySQL恢复:
drop database day51;
create database day51;
source D:\day51.sql;

 

2、不登录MySQL直接在外面恢复;
    mysql -uroot day51 < D:\day51.sql

 

(2)、恢复到指定的库中:
create database bak; 新建一个文件夹
use dak; 切换到dak这个文件夹下
mysql -uroot bak < D:\day51.sql; 恢复数据

 九、关键字的优先级

from

where

group by

having

select

distinct

order by

limit

 

1.找到表:from

2.拿着where指定的约束条件,去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组

4.按照select后的字段得到一张新的虚拟表,如果有聚合函数,则将组内数据进行聚合

5.将4的结果过滤:having,如果有聚合函数也是先执行聚合再having过滤

6.查出结果:select

7.去重

8.将结果按条件排序:order by

9.限制结果的显示条数

 

 

 

 

编辑:数据库 本文来源:mysql数据库修改用户密码,MYsql的数据备份

关键词: