03、基本SQL语句-库、表、记录、存储引擎

基本SQL语句

一、库

1、系统数据库

information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象 
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库

1、TABLES表：提供了关于数据库中的表的信息
mysql> use information_schema
mysql> select * from tables limit 1\G
mysql> select * from tables where TABLE_NAME='city'\G
​
columns表：提供了关于数据库中表的列的信息
mysql> select * from columns\G
​
STATISTICS表：表索引的信息
​
COLLATIONS表：提供校验规则和字符集对应关系
​
Table_catalog    数据表登记目录
Table_schema    数据表所属的数据库名
Table_name    表名称
Table_type    表类型[system view|base table] 系统表 ，数据表
Engine    使用的数据库引擎[MyISAM|CSV|InnoDB]
Version    版本，默认值10
Row_format    行格式[Compact|Dynamic|Fixed]
Table_rows    表里所存多少行数据
Avg_row_length    平均行长度
Data_length    数据长度
Max_data_length    最大数据长度
Index_length    索引长度
Data_free    空间碎片  刚刚用过的暂时不再使用
Auto_increment    做自增主键的自动增量当前值  可以通过导出再导入数据进行释放
Create_time    表的创建时间
Update_time    表的更新时间
Check_time    表的检查时间
Table_collation    表的字符校验编码集
Checksum    校验和
Create_options    创建选项
Table_comment    表的注释、备注
​
CREATE TABLE student(gid int primary key COMMENT '列注释') COMMENT='表注释';

2、库操作

# 增
    create database 数据库名称;
​
# 查
    show databases;                   # 查看所有 
    show create database 数据库名称;     # 查看单个
    select database();                 # 查看当前所在库名称
    
# 改
    alter database 数据库名称 charset='编码';  # 修改编码
    
# 删
    drop database 数据库名称;    # 删除数据库

二、表

1、表操作

"""
1.查看当前所在库名称
    select database();
2.切换数据库
    use 数据库名称;
"""
​
# 增
    create table 表名(字段名称 字段类型)  # 创建表一定要给字段
    
# 查
    show tables;        # 产看当前库下面所有的表名称
    show create table 表名\G; # 查看指定表的详细信息(创建语句)
    describe 表名;    # 简写desc   查看表的结构
​
# 改
    1. alter table 旧表名 rename 新表名;
    
       rename table 旧表名1 to 新表名1,
                    旧表名2 to 新表名2,
                    旧表名3 to 新表名3;
      
    2. 增加字段
      alter table 表名
             add 字段名  数据类型 [约束条件…],
             add 字段名  数据类型 [约束条件…];
      alter table 表名
            add 字段名  数据类型 [约束条件…]  first;
      alter table 表名
            add 字段名  数据类型 [约束条件…]  after 字段名;
     
    3.增加索引
        # 主键索引
            alter table 表名 add primary key pri_字段名(字段)
        # 唯一索引
            alter table 表名 add unique key uni_字段名(字段)
        # 普通索引
            alter table 表名 add index idx_字段名(字段)
​
    4. 删除字段
          alter table 表名 
                drop 字段名;
​
    5. 修改字段
          alter table 表名 
                modify  字段名 数据类型 [约束条件…];
          alter table 表名 
                change 旧字段名 新字段名 旧数据类型 [约束条件…];
          alter table 表名 
                change 旧字段名 新字段名 新数据类型 [约束条件…];
# modify 是用来修改字段类型，change 既可以修改字段名，也可以修改字段类型。
​
# 删
    drop table 表名;

2、创建表的完整语法

create table 表名(
    字段名1 字段类型(数字) 约束条件,
    字段名2 字段类型(数字) 约束条件,
    字段名3 字段类型(数字) 约束条件,
    ......
);
​
# 注意事项
    1、字段名和字段类型是必须的，数字和约束条件是可选
    2、在同一张表中，字段名是不能相同
    3、约束条件可以有多个
    4、最后一个字段结尾不能有逗号(不容易发现)

3、案例1：创建库、表

mysql> create database db01;    # 创建数据库
mysql> use db01         # 切换数据库
mysql> select database();   # 查看当前所在库名称
    +------------+
    | database() |
    +------------+
    | db01       |
    +------------+
​
mysql> create table t1(
    -> id int,
    -> name varchar(32),
    -> sex enum('male','female'),
    -> age int(3)
    -> );                           # 创建表
​
mysql> show tables;                 # 查看表
+----------------+
| Tables_in_db01 |
+----------------+
| t1             |
+----------------+
​
mysql> desc t1;                     # 查看表的详细信息
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(32)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+

4、案例2：修改示例

1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;
​
2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name;         # 添加name字段之后
​
mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;        # 添加到最前面
​
3. 删除字段
mysql> alter table student10
    -> drop sex;
​
mysql> alter table service
    -> drop mac;
​
4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    # 修改为主键
​
5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined
​
mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        
​
7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;
​
8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;
​
9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 
​
b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;

5、复制表

# 复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;
​
# 只复制表结构
mysql> select * from service where 1=2;        # 条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> create table t4 like employees;

 

三、记录

1、记录操作

"""
操作记录之前要有库和表
	create database db01;
	use db01;
	create table t1(
    -> id int,
    -> name varchar(32),
    -> sex enum('male','female'),
    -> age int(3)
    -> );
"""

# 增
	1. 插入完整数据（顺序插入）
        # 语法一：
        insert into 表名(字段1,字段2,字段3,...) values(值1,值2,值3,...);
        # 语法二：
        insert into 表名 VALUES (值1,值2,值3,...);

    2. 指定字段插入数据
        # 语法：
        insert into 表名(字段1,字段2,字段3,...) values(值1,值2,值3,...);

    3. 插入多条记录
        # 语法：
        insert into 表名 values
            (值1,值2,值3,...),
            (值1,值2,值3,...),
            (值1,值2,值3,...);

    4. 插入查询结果
        # 语法：
        insert into 表名(字段1,字段2,字段3…字段n) 
                        select (字段1,字段2,字段3…字段n) from 表2
                        where ...;
	
# 查
	select * from 表名称;	# 查询所有字段数据
	select 字段名1,字段名2 from 表名;	# 也可以通过库名.表明跨库
    '''如果字段过多导致展示错乱，还可以使用\G结尾分行展示'''
    
# 改(更新)
	update 表名 set 
		字段名1='新值', 
		字段名2='新值',
		...
		where 字段名1=存在值,字段名2=存在值;	    # where筛选条件
        
     # 示例：
        update mysql.user set password=password(‘123’) 
            where user=’root’ and host=’localhost’; 
    
# 删
	delete from 表名 where 字段名=存在值;	# where筛选条件
    
    # 示例：
    delete from mysql.user 
        where password=’’;

2、案例：创建记录

mysql> insert into t1 values
    -> (1,'momo','male',18),
    -> (2,'lily','female',17)
    -> ;
mysql> select * from t1;
+------+------+--------+------+
| id   | name | sex    | age  |
+------+------+--------+------+
|    1 | momo | male   |   18 |
|    2 | lily | female |   17 |
+------+------+--------+------+

mysql> insert into t1(id) values
    -> (3),
    -> (4);
mysql> select * from t1;
+------+------+--------+------+
| id   | name | sex    | age  |
+------+------+--------+------+
|    1 | momo | male   |   18 |
|    2 | lily | female |   17 |
|    3 | NULL | NULL   | NULL |
|    4 | NULL | NULL   | NULL |
+------+------+--------+------+

mysql> select id,name,sex,age from t1;
+------+------+--------+------+
| id   | name | sex    | age  |
+------+------+--------+------+
|    1 | momo | male   |   18 |
|    2 | lily | female |   17 |
|    3 | NULL | NULL   | NULL |
|    4 | NULL | NULL   | NULL |
+------+------+--------+------+

 

四、存储引擎

1、什么是存储引擎

	数据库中表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

2、mysql支持的存储引擎

mysql> show engines\G;			# 查看所有支持的存储引擎
mysql> show variables like 'storage_engine%';		# 查看正在使用的存储引擎

"""
存储引擎就是处理数据底层逻辑 不同的引擎底层处理方式有所不同
"""

# 如何查看存储引擎信息
	show engines;
    
# 需要掌握四个：
	1、MyISAM
		是MySQL5.5版本之前默认的存储引擎
        	该引擎存取数据的速度都很快
            但是安全性较低 不支持很多额外的功能         
    2、InnoDB
    	是MySQL5.5版本之后默认的存储引擎
        	该引擎支持事务、行级锁、外键
            存取数据的速度没有MyISAM快但是功能和安全性更高         
    3、MEMORY
    	数据全部存储在内存中 速度很快但是断电立刻丢失      
    4、BLACKHOLE
    	黑洞 任何放入其中的数据都会消失(类似于垃圾处理站)
        
# 比较存储引擎之间的差异
	'''
	创建表可以指定存储引擎
		create table t1(id int) engine=存储引擎;
	'''
        # 1.建表时指定存储引擎
        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);