mysql数据库常用操作

目录

1.数据库的操作

1.1链接数据库

1.2退出数据库

1.3查看所有数据库

1.4显示数据库版本

1.5显示数据库时间

1.6创建数据库

1.7查看创建数据库的语句

1.8删除数据库

1.9查看当前使用的数据库

1.10使用数据库

2.数据表的操作

2.1显示当前数据库的表

2.2创建表

2.3查看表结构

示例：

2.4数据类型

数值类型（常用）

字符串

日期时间类型

2.5修改表-添加字段

2.6修改表-修改字段（不重命名字段）

2.7修改表-修改字段（重命名字段）

2.8修改表-删除字段

3.数据的增删改查

3.1增加记录

3.2修改记录

3.3查找记录

3.4逻辑删除

3.5删除记录

4.数据表查询

4.1一般查询

4.2正则表达式

4.3范围查询

4.4判断是否为空

4.5排序

4.6聚合函数

4.7分组

4.8分页

4.9内联查询

4.10左链接

4.11右链接

4.12自查询

4.13子查询

4.14设置外键

5.mysql的高级用法

5.1视图的创建

5.2事务

5.3索引

5.4账户管理

5.5远程连接数据库

5.6备份数据库

5.7还原数据库

6建立主从服务器

6.1.备份主服务器上的所有数据库

6.2.在从服务器上还原所有数据库

6.3.配置主服务器

6.4.配置从服务器ubuntu路径：

6.5.从服务器去连接读取主服务器数据，需要建立账户‘%’

6.6.开启同步

6.7.查看同步状态

6.8.常见问题

---

1.数据库的操作

1.1链接数据库

mysql -uroot -p

1.2退出数据库

exit/quit/ctrl+d

1.3查看所有数据库

show databases;

1.4显示数据库版本

select version();

1.5显示数据库时间

select now();

1.6创建数据库

create database python05 charset=utf8;

1.7查看创建数据库的语句

show create database python05;

1.8删除数据库

drop database python04;

1.9查看当前使用的数据库

select database();

1.10使用数据库

use python05;

 

2.数据表的操作

2.1显示当前数据库的表

show tables;

2.2创建表

create table xxxxx(id int,name varchar(30));

create table yyyyy(
    id int primary key not null auto_increment,
    name varchar(30)
);

2.3查看表结构

desc xxxxx;

 

示例：

  create table students(
      id int unsigned not null primary key auto_increment,
      name varchar(30),
      age tinyint unsigned,
      high decimal(5,2),
      gender enum("男","女","中性","保密") default "保密",
      cls_id int unsigned
  );

插入数据

insert into students values(0,"老王",18,188.88,"男",0);

查询数据

select * from students;

 

2.4数据类型

• 数值类型（常用）

类型	字节大小	有符号范围（signed）	无符号范围（unsigned）
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT/INTEGER	4	-2147483648~2147483647	0~42294967295
BIGINT	8	-9223372036854775808~<br />9223372036854775807	0~18446744073709551615

• 字符串

类型	字节大小	示例
CHAR	0~255	类型char(3)输入‘ab’，实际存储为'ab ',输入‘abcd’,实际存储为'abc'
VARCHAR	0~255	类型char(3)输入‘ab’，实际存储为'ab',输入‘abcd’,实际存储为'abc'
TEXT	0~65535	大文本（一般大于4000字采用该类型）

• 日期时间类型

类型	字节大小	示例
DATE	4	'2020-01-01'
TIME	3	'12:29:59'
DATETIME	8	'2020-01-01 12:29:59'
YEAR	1	'2017'
TIMESTAMP	4	'1970-01-01 00:00:01' UTC~'2038-01-01 00:00:01' UTC

2.5修改表-添加字段

alter table students add birthday datetime;

2.6修改表-修改字段（不重命名字段）

alter table students modify birthday date;

2.7修改表-修改字段（重命名字段）

alter table students change birthday birth date default "2000-01-01";

2.8修改表-删除字段

alter table students drop birth;

3.数据的增删改查

3.1增加记录

insert into students values(default,"小李飞刀",25,178.88,"女",0);

insert into students values(default,"貂蝉",28,178.88,"女",0),(default,"老李",26,178.88,3,1);

注：枚举中的下标从1开始

insert into students(name,age) values("小乔",29);

3.2修改记录

update students set age=22 where id=3;

3.3查找记录

select * from students where name='小李飞刀';

select id as 编号,name as 姓名 from students where name='小李飞刀';

3.4逻辑删除

alter table students add is_delete bit default 0;
  ​
update students set is_delete=1 where id=3;

3.5删除记录

delete from students where id=3;

4.数据表查询

4.1一般查询

  select * from students where name ="小王";
  select * from students where name like "%王";
  select * from students where name like "__";
  select name as 姓名 from students where name like "__";
  select id as 编号,name as 姓名 from students where name like "__";

4.2正则表达式

select * from students where name rlike "^小.*刀$";

4.3范围查询

  select * from students where age in(19,20,38);
  select * from students where age not in (19,20,38);
  select * from students where age between 18 and 38;
  select * from students where age not between 18 and 38;
  select * from students where not age between 18 and 38;

4.4判断是否为空

  select * from students where name is null;
  select * from students where name is not null;

4.5排序

  select * from students order by high;
  select * from students order by high desc;
  select * from students order by high desc,age desc;

4.6聚合函数

  select count(*) from students;
  select sum(age) from students;
  select max(age) from students;
  select min(age) from students;
  select avg(age) from students;
  select round(sum(age)/avg(age),2) from students;

4.7分组

  select sex,count(*) from students group by sex;
  select sex,sum(age) as sumage from students group by sex having sumage>50;
  select sex,group_concat(name,"_",age) from students group by sex;

4.8分页

--select * from students limit start,count;

  select * from students limit 2;
  select * from students limit 2,2;
  select * from students limit 4,2;

4.9内联查询

  select a.*,b.name as classname from students as a inner join class as b on a.cls_id=b.id;

4.10左链接

 select a.*,b.name as classname from students as a left join class as b on a.cls_id=b.id;

4.11右链接

  select a.*,b.name as classname from students as a right join class as b on a.cls_id=b.id;

4.12自查询

  select * from shengshi as a inner join shengshi as b on a.pro_id=b.id;

4.13子查询

  select * from shengshi where pid=(select * from shengshi where proname='浙江省');

4.14设置外键

  select * from students;
  select * from class;
  alter table students add foreign key  (cls_id) references class(id);

5.mysql的高级用法

5.1视图的创建

--create view 视图名 as select 语句;

  create view v_student_name as select id as 编号,name as 姓名 from students;

5.2事务

  start transaction;
  update checking set balance=balance-200.00 where customer_id =10233276;
  update saving set balance=balance+200 where customer_id =10233276;
  commit;

begin;也可以开启事务 rollback;撤销所有的修改

5.3索引

create index 索引名 on 表(索引字段(长度))；

  create index title_index on test_index(title(10))；

5.4账户管理

创建账户并授权

  grant select,update,insert,delete on jingdong.* to 'admin'@'%' identified by 'admin';

查看当前账户权限

  show grants;

查看admin账户权限

  show grants for 'admin'@'%';

修改密码

  update user set password = password('123456') where user='admin';

刷新账户权限

  FLUSH PRIVILEGES;

5.5远程连接数据库

  mysql -uadmin -p -h 39.100.54.235

5.6备份数据库

  mysqldump -uroot -p jingdong >jd.sql

5.7还原数据库

先创建新的数据库，在导入数据

  mysql -uroot -p jingdong2<jd.sql

6建立主从服务器

6.1.备份主服务器上的所有数据库

  mysqldump -uroot -p --all-databases --lock-all-tables >all_db.sql

6.2.在从服务器上还原所有数据库

  mysql -uroot -p <all_db.sql

6.3.配置主服务器

  #主库配置
  server_id=187
  log_bin = mysql-bin
  binlog_format = Row

  show variables like 'log_bin';--value为on才是开启日志
  show master status;

6.4.配置从服务器ubuntu路径：

 sudo gedit ./etc/mysql/mysql.conf.d/mysqld.cnf

  #从库配置
  server_id=128

6.5.从服务器去连接读取主服务器数据，需要建立账户‘%’

  grant all privileges on *.* to 'admin'@'%' identified by 'admin' with grant option;
  flush privileges;

  change master to master_host='39.100.54.235',master_user='admin',master_password='admin',master_log_file='mysql-bin.000002',master_log_pos=120;

6.6.开启同步

  start slave;
  --stop slave;

6.7.查看同步状态

show slave status \G;

  mysql> show slave status\G
  *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 39.100.54.235
                    Master_User: admin
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000002
            Read_Master_Log_Pos: 120
                 Relay_Log_File: MasService-relay-bin.000002
                  Relay_Log_Pos: 317
          Relay_Master_Log_File: mysql-bin.000002
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
  ...................................................
  注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

6.8.常见问题

  --show slave status中Slave_IO_State: Waiting to reconnect after a failed registration on master
  --在master上执行
  grant replication slave on *.* to 'user'@'%' identified by 'password';
  FLUSH PRIVILEGES;