数据库基础命令

数据库定义

数据库就是一款帮助我们管理数据的程序

什么是SQL语句

​ 操作数据库的软件命令

为什么要有SQL语句

​ 数据库能够支持的语言非常多，为了避免识别问题，发明了SQL语句，规定只要是像使用数据库就统一使用SQL语句操作

数据库的分类

关系型数据库

​ 具有固定的表结构，并且表与表之间可以通过外键建立关系

​ MySQL、MariaDB、Oracle、PostgreSQL、sqlserver、sqlite、db2

非关系型数据

​ 没有固定的表结构，数据存储采用K：V键值对的形式

​ redis、mongoDB、memcache

通常将操作关系型数据库的语句称为SQL语句，操作非关系型数据库称为NoSQL语句

基本操作命令

登录服务端

mysql #	以游客模式登录
mysql -u用户名 -p密码 -hIP -P port 

mysql -u用户名 -p密码 

查看当前数据库所有名称

show databases;

取消之前的命令

\c

退出客户端

exit;
quit;

系统服务制作

解决路径切换问题

将bin目录路径添加到环境变量中
将mysql服务制做成系统服务(开机自启动)

先以管理员身份打开cmd窗口
输入制作命令
	mysqld --install 

启动服务
windows ：	net start mysql
mac: mysqld.server start 
停止服务
windows ：	net stop mysql
mac: mysqld.server stop

修改管理员密码

#设置密码
mysqladmin -uroot -p password 123456

#修改密码
mysqladmin -uroot -p123456 password 123


#在客户端中修改
set password=PASSWORD('123456')

忘记密码

#1、关闭mysql服务
#2、以跳过授权表的方式重新启动
	mysqld --skip-grant-tables
	
#3、修改指定用户的密码
update mysql.user set password=password(123) where uer="root" and host="localhost";

#4、退出重启服务

字符编码

#查看编码信息
 \s
 
 配置文件
 mysql默认的配置文件 my-default.ini 复制一份

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oUbyjggz-1637070296127)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109204032148.png)]

修改配置文件

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8


[root@db02 ~]# vim /etc/my.cnf
[root@db02 ~]# systemctl restart mysqld

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6FX02faU-1637070296134)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109204741693.png)]

基本SQL语句的库操作

增

create database 数据库名称；

mysql> show databases;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Nijg30uf-1637070296136)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109205230490.png)]

查

show databases;  #查询所有数据库
show create database 数据库名称；

改

alter database 数据库名称 charset='编码' #修改编码

删

drop database 数据库名称； 

基本SQL语句表操作

#切换数据库
mysql> use db;
Database changed

"""
查看当前所有数据库名称
select database();
"""

增

create table 表名(字段名称 字段类型) #创建表要给字段


mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| t1           |
+--------------+
1 row in set (0.00 sec)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ePCwYZKc-1637070296138)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109210555477.png)]

查

show tables; #查看当前所有表的名称
show create table 表名；# 查看指定表的详细信息
describle 表名； #简写desc

mysql> show create table t1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4Y48BTcu-1637070296139)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109211120859.png)]

改

alter table 表名 rename 新表名

mysql> alter table t1 rename T1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ots2JeOG-1637070296140)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109211329532.png)]

删

drop table 表名;

mysql> drop tables T1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mvJuPfSO-1637070296141)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109211459249.png)]

基本SQL语句记录操作

'''
创建库和表。
	create database db;
	use db;
	create table t1(id int ,name varchar(32));
'''

增

insert into 表名 values('按照字段顺序一一传值(逗号隔开)');
insert into tables_name values(...).(...),(...);

mysql> insert into t1 values(1,'cc'),(2,'xx');

查

select * from 表名； #查询所有字段的数据
select 字段名1，字段名2 from 表名; #可以通过库名，表名跨库
'''如果字段过多导致展示错乱 还可以使用\G结尾分行展示'''

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wpVrCrJB-1637070296143)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109213525995.png)]

改

update 表名 set 字段名=新值 where 字段名=存在的值 

mysql> update t1 set name='zz' where id=2;

删

delete from 表名 where 字段名='存在的值'

mysql> delete from t1 where id=1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OBQAgt0M-1637070296145)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109214431987.png)]

存储引擎

简单的理解为：存储引擎就是处理数据的底层逻辑 不同的引擎底层处理方式有有所不同

如何查看引擎信息

show engines;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DxvFtvu6-1637070296146)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109214711207.png)]

MyISAM

是mysql5.5之前的默认引擎，该引擎存取数据的速度都很快，但安全性低，不支持很多额外的功能

InnoDB

是mysql5.5之后的默认引擎，该引擎支持事物，行级锁、外键。

存储数据的速度没有MyISAM快但是功能和安全性更高

MEMORY

数据全部存储在内存中，速度很快，但断电立刻消失

BLACKHOLE

黑洞 任何放入其中的数据都会丢失（类似于垃圾处理）

存储引擎之间的差异

'创建表可以指定引擎'
create table t1(id int) engine=存储引擎;

create table t2(id int) engine=myisam;
create table t3(id int) engine=innodb;
create table t4(id int) engine=memory;
create table t5(id int) engine=blackhole;

# 不同存储引擎文件后缀名
	MyISAM
		三个文件
        	.frm	表结构
            .MYD	表数据
            .MYI	表索引
   InnoDB
		两个文件
    		.frm	表结构
        	.ibd     数据与索引
   memory
		.frm	表结构
   blackhole	
		.frm	表结构

插入数据
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
insert into t5 values(1);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RKQMOFGz-1637070296148)(/Users/raoxurou/Library/Application Support/typora-user-images/image-20211109220459727.png)]

创建表的完整语法

create table table_name(字段名1 字段类型(数字) 约束条件,
                        字段名2 字段类型(数字) 约束条件,
                        字段名3 字段类型(数字) 约束条件 
                       );

主要事项

1、字段名和字段类型是必须的 数字和约束条件是可选的
2、约束条件可以有多个
3、最后一个字段结尾不能有逗号

数据类型

整型

  tinyint		1bytes
  smallint		2bytes
  int 			4bytes
  bigint		8bytes
  
  '不同类型的int能够存储的数字范围不一样'
  

验证不同类型的是否会空出一个正负号

create table t7(id tinyint unsigned);
insert into t7 values(256),(-129);

浮点型

float(255,30)	#总共255位小，数占30位
double(255,30) 	#总共255位，小数占30位
decimal(65,30)	#总共65位，小数占30位

create table t8(id float(255,30));
create table t9(id double(255,30));
create table t10(id decimal(65.30));
insert into t8 values(1.111111111111);
insert into t9 values(1.111111111);
insert into t10 values(1.1111111);

'三者精确度不同'
decimal > double > float

字符类型

char(4)		#定长
	最多存储四个字符 超出则报错 没有超出则空格填充
varchar(4)
	最多存储四个字符 超出则报错 没有超出有几个存几个
	
create table t11(id int ,name char(4));
create table t12(id int,name varchar(4));
insert into t11 values(1,'cc');
insert into t12 values(1,'qq');

日期相关类型

date	年月日
datetime 年月日时分秒
time	时分秒
year	年

 create table user(id int(4) ,
                   name varchar(32),
                   reg_time datetime,
                   brithday time,
                   study_time time,
                   born_year year);
                   
  '字段可以加类似注释的说明  coment'
  
insert into user values(1,'jason','2000-11-11 11:11:11','2000-11-11','11:11:11','2000');  

枚举

对选一：提前定义好数据后，后续录入只能录入定义好的内容之一

create table t2(
	id int,
	name char(6),
	gender enum('male','femal','other')
	);
insert into t2 values(1,'jason','male');	#正常
insert into t2 values(2,'kevin','男')；	#报错

#################################################
mysql> create table t2(
    -> id int,
    -> name char(6),
    -> gender enum('male','femal','other')
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values(1,'jason','male');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(2,'kevin','nan');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

集合

多选多：包含多选一，

create table t3(id int,
               name char(32),
               hobbies set('baskball','football','doublecolorball')
               );
               
insert into t3 values(1,'jason','baskball');		#正常
insert into t3 values(2,'kevin','football,doublecolorball');	#正常
insert into t3 values(3,'cc','pingpangball')；	#报错

#######################################################################
mysql> insert into t3 values(2,'kevin','football,doublecolorball');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(3,'cc','pingpangball');
ERROR 1265 (01000): Data truncated for column 'hobbies' at row 1

SQL_MODE

模糊查询

show variables like '%mode%';

修改

set session
set global 

直接修改配置文件
sql_mode = 'strict_trans_tables'

数字在数字类型与字符类型中的区别

数字是否用来限制长度

create table t13(id int(3));
insert into t13 values(1111);

'数字在数字类型中不是用来限制存储长度，而是用来控制战术长度'


create table t14 (id int(8) unsignes zerofill);
insert into t14 values(1111);
insert into t14 values(11111);

'数字类型不指定数字，使用自带的即可'
create table t15 (id int);

char和varchar的差异

create table t16 (id int,name char(5));
insert into t16 values(1,'tom');

create table t17(id int,name varchar(5));
insert into t17 values(1,'tom');

'取消底层优化'
set global sql_mode='strict_trans_tables,pad_char_to_full_length'

统计某个字段数据的长度

char_length()

char和varchar那个好

char 
	优势：整存整取 速度快
	劣势：浪费存储空间
	
varchar 
	优势：节省存储空间
	劣势：存取数据的时候都需要考虑报头 速度较char慢

#创建库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
#查看所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db                 |
| db1                |
| django             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)
#使用库
mysql> use db1;
Database changed
#创建表字段
mysql> create table t1(id int ,name char(32));
Query OK, 0 rows affected (0.00 sec)
#查看表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.01 sec)
#在新创建的表中加入数据
mysql> insert into t1 values(1,'cc'),(2,'dd');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
#查看表记录内容
mysql> select id,name from t1;
+------+------+
| id   | name |
+------+------+
|    1 | cc   |
|    2 | dd   |
+------+------+
2 rows in set (0.00 sec)

#删除一条记录
mysql> delete from t1 where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select id,name from t1;
+------+------+
| id   | name |
+------+------+
|    2 | dd   |
+------+------+
1 row in set (0.00 sec)
#查看表字段
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(32) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

约束条件

​ 约束条件相当于是在字段上添加的额外约束(条件)

1.unsigned
	无符号
	 	id int unsigned
	
2.zerofull
	0填充
	 	id int zerofull
	 
3.not null
	非空
		create table t4(id int,name varchar(16) not null);
		insert into t4(name) values('jason');
		
		create table t5(id int,name varchar(16));
		insert into t5(id) values(1);
		
#######################################################################
mysql> create table t4(id int,name varchar(16) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t4(name) values('jason');
Query OK, 1 row affected (0.00 sec)

mysql> create table t5(id int,name varchar(16));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t5(id) values(1);
Query OK, 1 row affected (0.00 sec)
	
    
4.default
	默认值(用户给了就用用户的，用户没给就用默认的)
	
create table t6(id int,name varchar(16) default '匿名用户');
insert into t6(id) values(1);
insert into t6(id,name) values(2,'cc');
#######################################################################
mysql> create table t6(id int,name varchar(16) default '');
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t6(id) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6(id,name) values(2,'cc');
Query OK, 1 row affected (0.01 sec)


5.unique
	唯一(保证字段(一个、多个)在整个表中没有重复的数据)
	#单列唯一
	create table t7(id int,name char(4) unique);
	insert into t7 values(1,'zz'),(2,'cc'); 
	
	#联合唯一
	create table server(id int,
                        host varchar(4),
                        port varchar(16),
                        unique(host,port)
                       );
                       
6.primary key
	主键
	 1、从约束条件上而言主键相当于not null + unique(非空且唯一)
	 2.主键的功能目前简单的理解为能够加快数据的查询速度，相当于书的目录
	 3.InnDB存储引擎规定每张表都必须有且有一个主键
	 	表中没有任何的主键和其他约束条件
	 	InnDB默认会采用一个隐藏字段作为表的主键
	 	
	 	表中没有主键但是有非空且唯一的字段
	 	InnDB会自动将该字段升级为主键
	 	
'结论：每张表都必须要有一个id字段并且该字段就是主键'

#单列主键
	create table t8(id int primary key,
                name varchar(16)
                   );
     create table t9(id int,
                    name varchar(16),
                    age int not null unique,
                    phone bigint not null unique,
                    num int not null unique
                    );
                    
#多列主键(联合主键)
create table t10(id int ,
                 age int,
                name varchar(16),
                primary key(id,age)
                );
                
7.auto_increment
	自增(专门配合主键一起使用的，让主键能够自增)
	create table t11(id int primary key auto_increment,
                    name varchar(32)
                    );
   
   '''
   在插入数据的时候还可以指定表字段
   insert into t3(name) values('jason')
   '''
 

主键自增特性

1.主键自增是不会收到delete from删除操作的影响
2.truncate既可以看清空数据表页会重置主键值

外键

# 前戏
1.定义一张员工表
	id	name	age 	dep_name	dep_desc
    1	jason	18		外交部		搞外交
    2	kevin	28		教学部		教书育人
    3	tony	38		教学部		教书育人
    4	oscar	48		安保部		保家卫国
    5	jackson	58		财务部		发工资
"""
上述表不合理之处
	1.表内部数据混乱(可忽略)
	2.反复的录入重复数据(可忽略)
	3.修改数据太过繁琐  浪费磁盘空间(可忽略)
	4.极大地影响了操作数据的效率
"""
2.将上述表拆分成两张表
	id	name	age 
    1	jason	18
    2	kevin	28
    3	tony	38
    4	oscar	48
    5	jackson	58
	id	dep_name	dep_desc
    1	外交部		搞外交
    2	教学部		教书育人
    3	安保部		保家卫国
    4	财务部		发工资
"""拆分完之后解决了上述四个缺陷"""

#外键
	用来记录表与表之间的关系

如何查找表关系

多对一

多对多

一对一

多对一

查找关系换位思考，
书籍表与出版社表
	站在书籍表
	问:一本书能够对应多个出版社
        答:不可以
    2.再站在出版社表
    	问:一个出版社能否对应多本书
        答:可以
    结论:一个可以一个不可以 表关系为"多对一"
   		那么外键字段建在"多"的一方
# 针对具有表关系的SQL建议先写普通字段 最后再考虑外键字段
create table book(
	id int primary key auto_increment,
	title varchar(32),
	price int,
	pub_id int,
	foreign key(pub_id) references publish(id)
);

create table publish(
	id int primary key auto_increment,
	name varchar(32)
);

外键约束

1、在创建表的时候需要先创建被关联表(没有外键的表)

2、在写入数据的时候也需要先写入被关联表(没有外键的表)

3、被关联表里面的数据无法直接删除和修改关联字段的操作

级联更新，级联删除

create table emp(
	id int primary key auto_increment,
	name varchar(16),
	age int,
	dep_id int,
	foreign key (dep_id) references dep(id)
	on update cascade
	on delete cascade);
	
	
create table dep(
	id int primary key auto_increment,
	name varchar(16)
);

多对多

以书籍表与作者表为例
	1.先站在书籍表的基础之上
    	问:一本书能否对应多个作者
        答:可以
    2.在站在作者表的基础之上
    	问:一个作者能否对应多本书
         答:可以
    结论:两个都可以那么表关系就是"多对多"
        外键字段建在第三张关系表中
        
#在写普通字段之后，再考虑外键字段

create table book1(
	id int primary key auto_increment,
	name varchar(16)
);
create table author(
	id int primary key auto_increment,
    name varchar(16));
    
create table book2author(
	id int primary key auto_increment,
	author_id int,
	foreign key(author_id) references author(id)
	on update cascade
	on delete cascade,
	book_id int,
	foreign key(book_id) references book1(id)
	on update cascade
	on delete cascade);
	
	

一对一

关系

# 场景
	客户表与学生表
    QQ用户表
以用户表与用户详情表为例
	1.先站在用户表的基础之上
    	问:一个用户能否对应多个用户详情
        答:不可以
    2.在站在用户详情表基础之上
    	问:一个用户详情能否对应多个用户
         答:不可以
    结论:换位思考之后两边都不可以 那么表关系有两种
        	1.没有关系(用膝盖都能判断出来)
            2.一对一关系

'外键字段建在哪里?'
#理论上建在任何一方都可以但是推荐建在查询频率较高的表中
            
 create table user(
		id int primary key auto_increment,
 		name int,
 		detail_id int unique,
 		foreign key(detail_id) references user_detail(id)
 		on update cascade
 		on delete cascade
 );

操作表的SQL语句补充

修改表名称

alter table t1 rename t33;
#关键字修改

rename table t1 to 123;
rename table t1 to t44,t3 to 55;

添加表字段

alter table t10 add pwd int;

alter table t10 add hobby varchar(32) after age;
alter table t10 add nid int first;

修改字段和字段类型

alter table t10 modify age tinyint;
alter table t10 change ped password int;

复制表(了解)

# 查询语句执行的结果也是一张表，可以看成虚拟表

# 复制表结构＋记录 （key不会复制: 主键、外键和索引）
create table new_service select * from service;

# 只拷贝表结构(不包含键)
create table new1_service select * from service where 1=2;  

# 拷贝结构包含各种key
create table t4 like emp;