MySQL操作

mysql架构

类似socket客户端和服务端

流程:

1. mysql服务端先启动,监听在一个特定的关口,默认3306
2. mysql客户端连接服务端
3. mysql客户端可以发送相关操作命令,操作服务端存储的数据

mysql操作

初始化:

mysqld --initialize-insecure

启动mysql服务:

mysqld 启动mysql服务

启动mysql客户端并连接mysql服务:

mysql -uroot -p

修改mysql密码:

C:\Windows\system32>mysqladmin -uroot -p "原密码"  password  "新密码"
	
出现的错误
C:\Windows\system32>mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
如果出现上述错误，代表密码输入错误

常用参数:

-u : user 用户名

-p : password 密码

-h : host 主机名或ip mysql -uroot -p -h 192.168.0.1

-P : port 端口号,默认3306 mysql -uroot -p -h 192.168.0.1 -P 3306

忘记密码:

1. 关闭mysql服务 管理员cmd中 net stop mysql57

2. 在cmd中执行:mysqld --skip-grant-tables 绕过密码验证的过程

3. 在cmd中执行:mysql mysql -uroot -p 走无密码通道

4. 执行下列sql指令:

   update mysql.user set authentication_string = password('') where user = 'root';

   flush privileges;一定要做

5. 重启mysql服务

SQL指令

操作数据库

增

​ create database 数据库名 charset utf8;

​ 命名规范:

​ 可以由字母,数字,下划线,@,#,$组成

​ 区分大小写

​ 唯一性

​ 不能使用关键字

​ 不能使用单独数字

​ 最长128位

删

​ drop database 数据库名;

改

​ 删除再添加

​ 如果数据库中由数据的话,直接drop会导致数据库中的数据丢失

​ 改和删之前一定要备份,或者不用

查

​ show databases;

​ show create database 数据库名; 查看建立的数据库

​ select database();

使用数据库(可不加;)

​ use 数据库名;

操作表

增

创建表
create table 表名(
    字段名1  列类型  [可选的参数],
    字段名1  列类型  [可选的参数]  --最后一行不加逗号
    ) charset=utf8;
增加字段
ALTER TABLE 表名
	ADD 字段名 列类型 [可选的参数],
	ADD 字段名 列类型 [可选的参数];	
alter table t1 add name varcher(32) not null default '';--在表尾部追加

ALTER TABLE 表名 DD 字段名 列类型 [可选的参数] FIRST;
alter table t1 add name varcher(32) not null default '' first;--在头部追加

ADD 字段名 列类型 [可选的参数] AFTER 字段名;
alter table t1 add name varcher(32) not null default '' after id;

列约束:

auto_increment 自增

primary key 主键索引 列的值不可重复

NOT NULL 标识该字段不能位空

注意:null占长度为null,''占空间为0

mysql> select length(NULL),length(''),length('1'),length('abc');
+--------------+------------+-------------+---------------+
| length(NULL) | length('') | length('1') | length('abc') |
+--------------+------------+-------------+---------------+
|         NULL |          0 |           1 |             3 |
+--------------+------------+-------------+---------------+
1 row in set (0.00 sec)

DEFAULT 为字段设置默认值

删(线上禁用)

删除表
drop table 表名:
删除字段
alter table 表名 drop 字段名;

改

1.修改表名

alter table 旧表名 rename 新表名;

2.修改(增加)字段

ALTER TABLE 表名
	ADD 字段名 列类型 [可选的参数],
	ADD 字段名 列类型 [可选的参数];	
alter table t1 add name varcher(32) not null default '';--在表尾部追加

ALTER TABLE 表名 DD 字段名 列类型 [可选的参数] FIRST;
alter table t1 add name varcher(32) not null default '' first;--在头部追加

ADD 字段名 列类型 [可选的参数] AFTER 字段名;
alter table t1 add name varcher(32) not null default '' after id;

3.删除字段

ALTER TABLE 表名  DROP 字段名;
mysql> alter table t88 drop name4;
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.修改字段

ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
mysql> alter table t88 modify name2 char(20);
Query OK, 1 row affected (0.88 sec)
Records: 1  Duplicates: 0  Warnings: 0
				
				
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
				
mysql> alter table t88 change name2 name22 varchar(32) not null default '';
Query OK, 1 row affected (0.82 sec)
Records: 1  Duplicates: 0  Warnings: 0	
				
mysql> alter table t88 change name22 name23;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

查

show tables;--显示目前数据库下所有表
show create table 表名;--显示表名表所有信息

复制表结构:

create table t2 like t1;--创建t2与t1表结构相似

操作表数据行

增

增加数据
insert into 表名 (列1,列2) values (值1,值2);
insert into t1 (id,name) values (1,'tom'),(2,'jerry'),(3,'James');
insert into t1 (name) select name from t2;

删

delete from 表名 where 条件;--删除满足条件的值
delete from 表名;--删除表中所有数据,后面再添加数据,自增从之前的数据继续加1
truncate 表名;--删除表中所有内容,后面再添加数据,从1开始

注意:delete删除是一行一行的删除,truncate是全选删除,truncate删除的效率高于delete

改

update 表名 set 列名1=新值1,列名2=新值2 where 条件;
update t1 set name = 'tooom' where id = 1;

查

定区间查询
select 列1,列2 from 表名 between 30 and 40;(*代表所有查询结果)
去重查询
select distinct name from t1;--返回不重复的值

%代表所有,_代表一个

单表操作

分组 group by

将所有记录按照某个相同的字段进行归类.

select 聚合函数,选取的字段 from employee group by 分组的字段;

group by 必须和 聚合函数连用

二次筛选 having

对group by 之后的数据进行二次筛选

升序降序 order by

order by 字段名 asc (默认升序) desc (降序)

分页 limit

limit offset ,size;

offset表示行数据索引,默认从第一行(索引为0)开始;

size 表示 取多少条数据

总结

使用顺序

select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件;

where > group by > having > order by > limit

多表操作

外键

方便多个表之间的查询及连接

外键自动生成为普通索引.

一对多:

constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

constraint fk_user_depart foreign key (depart_id) references department(id),

多对多:

生成中间表对应多个表,谈恋爱

多表联查:--⬇

连接JOIN

交叉连接cross join

笛卡尔乘积,全部交叉,在MySQL中,cross join从语法上说于inner join等同,数据量大,慎用

SELECT * from employees CROSS JOIN salaries

内连接

inner join,省略为join

• 等值连接,只选默写field相等的元组(行),使用ON限定关联的结果.

SELECT * from employees INNER JOIN ON employees.emp_no= salaries.emp_no;

• 自然连接,是一种特殊的等值连接,会自动去掉重复的列.用的少

--自然连接,去掉了重复列,且自行使用employees.emp_no= salaries.emp_no的条件
SELECT * from employees NATRUAL JOIN salaries;

外连接

outer join,可以省略为join

分为左外连接,即左连接;右外连接,即右连接;全外连接

--左连接
SELECT * from employees LEFT JOIN salaries ON employees.emp_no= salaries.emo_no;
--右连接
SELECT * from employees RIGHT JOIN salaries ON employees.emp_no= salaries.emo_no;
--这个右连接等价于上面的左连接
SELECT * from salaries RIGHT JOIN employees ON employees.emp_no= salaries.emo_no;

左外连接,右外连接

看表的数据的方向,谁是主表,谁的所有数据都显示,匹配不上的显示null

自连接

表自己和自己连接

本质上是把一张表当成两张表来用

select manager.* from emp manager,emp worker where manaer.empno=worker.mgr and worker.empno=1;
select manager.* from emp manager inner join emp worker on manaer.empno=worker.mgr where
worker.empno=1;

索引

使用索引的作用:

使用索引为了提高查询的效率.

索引的本质:

一个特殊的文件

索引的底层原理:

B+树

索引的种类

• 主键索引:加速查找 + 不能重复 + 不能为空 primary key pri
  • auto_increment 依赖primary key
  • 想要删除主键要先删除auto_increment
• 唯一索引:加速查找 + 不能重复 unique(name) uni
  • 联合唯一索引: unique(name,age) 将两列看成一个,两列不可同时重复
  • 支持索引最左前缀
• 普通索引: 加速查找 index 索引名 (name) mul 这是一个字典目录
  • 联合索引: index (name,age)
  • 支持索引最左前缀

注意:外键自动为普通索引键类型.

普通索引重复不重复无所谓,A在第几行或第几行出现过

唯一键索引.不能重复,A只在第几行出现过

主键索引.不能重复,A只在第几行出现过

主键索引:
增加:
方法一
create table user(
    id int auto_increment primary key,
    )
    auto_increment 依赖 primary key
方法二:
alter table user change id id int auto_increment primary key
删除:
先删除auto_increment,才能删除 primary key
alter table user drop primary key
唯一键索引:
增加:
方法一:
create table user(
	id int auto_increment primary key,
    phone int not null default 0,
    name varchar(32),
    unique ix_phone(索引名)  (phone(字段名))
)
方法二:
alter table user add unique index ix_phone (phone);
方法三:
create unique index ix_phone on user (phone);
删除:
alter table user drop index ix_phone;
普通索引:
增加:
方法一:
create table user(
    id int auto_uncrement primary key,
    name varchar(32) not null default '',
    index ix_name(name)
    );
方法二:
alter table user add undex ix_name (name);
方法三:
create index ix_name on user (name);
删除:
alter table user drop index ix_name;

索引的创建

主键一般不另设主键名,唯一键和普通索引建议加键名

1. 创建表时创建

create table xxx(
	id int auto_increment primary key
  --或者primary key  (id)
);

2. 使用alter方法

--add方法
alter table t1 add primary key 键名 (id);
--change方法
alter table t1 change id id int auto_increment primary key;

索引的删除

alter table t2 drop index [键名]

索引的有缺点

用空间换时间

1. 索引加快了查询速度
2. 会占用更大的磁盘空间

索引不会被命中的情况(拉低效率)

1. 不能在SQL语句进行四则运算

2. 不要使用函数

3. 类型不一致

   如果列是字符串类型，传入条件是必须用引号引起来，不然无法命中
   select * from tb1 where email = 999;
   #排序条件为索引，则select字段必须也是索引字段，否则无法命中
   

4. order by

   select name from s1 order by email desc;
   当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
   					
   select email from s1 order by email desc;
   特别的：如果对主键排序，则还是速度很快：
   select * from tb1 order by nid desc;
   

   order by 后面的字段如果没有设置索引,那么排序就很慢

5. 不要使用count(*)

最左前缀索引

explain方法

在SQL语句前加explain ,结尾使用\G结尾执行,显示参数

mysql> explain select * from teacher_info where name = 'echo'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: teacher_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

慢查询

查看相关变量

mysql> show variables like '%slow%';
+---------------------------+--------------------------+
| Variable_name             | Value                    |
+---------------------------+--------------------------+
| log_slow_admin_statements | OFF                      |
| log_slow_slave_statements | OFF                      |
| slow_launch_time          | 2                        |
| slow_query_log            | ON                       |
| slow_query_log_file       | DESKTOP-OKFL3T1-slow.log |
+---------------------------+--------------------------+
5 rows in set, 1 warning (0.25 sec)

mysql> show variables like  '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
5 rows in set, 1 warning (0.00 sec)

配置慢SQL的变量:

set global 变量名 = 值
				 
set global slow_query_log = on;
				
set global slow_query_log_file="D:/mysql-5.7.28/data/myslow.log";
				
set global long_query_time=1;

约束constrain

• unique约束 唯一键约束

  定义了唯一键索引,就定义了唯一键约束.

• primary key约束

  定义了主键,就定义了主键约束.

• 外键约束 foreign key

  外键,在表B中的列,关联表A中的主键,表B中的列就是外键.

  不易过多使用

  如果B表中某一列关联表A中的主键,那么B键的这一列就是A的外键

  外键可设外键约束

  设置外键约束之后,若想删除表A主键中数据,需先删除表B中的引用,若表A想要更改主键中信息,需要先删除表B的相关记录后,才可以修改表A的主键.

  外键约束,为了保证数据完整性,一致性,杜绝数据冗余,数据讹误.