二、库、表、记录的操作

目录

• 一、 操作库
  • （一）增 create
  • （二）删 drop
  • （三）改 alter charset
  • （四）查 show
  • （五）使用数据库 use
• 二、操作表
  • （一）完整性约束
  • （二） 数据类型
  • （三） 增
    • （1） 增加表 create
    • （2）增加字段alter add
  • （四）改
    • （1）修改表名 alter rename
    • （2）修改字段
  • （五）删
    • （1）删除表drop
    • （2）删除字段 alter drop
  • （六）查 show
  • （七）复制表结构 create like
• 三、操作表记录
  • （一）增 insert into
  • （二）查 select from
    • （1）where条件
  • （三）删 delete、truncate
  • （四）改 update set
• 补充

一、 操作库

（一）增 create

create database 数据库名称 charset utf8;
# 命名规范
#   1. 可以有字母、数字、下划线、@、#、$
#   2. 区分大小写
#   3. 唯一性
#   4. 不能使用关键字
#   5. 不能单独使用数字
#   6. 最长128位

（二）删 drop

drop database 数据库名称;

（三）改 alter charset

# 删除在增加
# 如果数据库中有数据的话，直接drop会导致数据库的数据丢失
# 线上环境，不能直接删除数据，在删除之前，需要进行备份
alter database 数据库名称 charset utf8 # 修改库的编码

（四）查 show

show databases;  #查看当前所有库
show create database 数据库名;  # 展示单个库
select database(); # 查看当前所在库

（五）使用数据库 use

use 数据库名; # 切换到库目录

二、操作表

（一）完整性约束

1. auto_increment : 约束字段为自动增长1
2. primary key : 约束其为唯一的标识，列的值不能重复，相当于主键索引，加快查询速度
3. unsigned : 默认数字类型范围有符号（有负数），约束其无符号（没有负数）
4. not null : 标识该字段不能为空
5. default ：指定默认值， 当插入数据时，若无设置，则自动添加默认值
6. zerofill ： 使用0填充
7. foreign key：外键，使用在多表创建中
8. unique：唯一标识，用于多表创建的一对一中

# 例子1：
create table t2 (
id int auto_increment primary key,
name char(10)
)charset=utf8;

# 例子2：
create table t3 (
id int unsigned auto_increment primary key,
name char(10) not null default 'xxxx',
age int not null default 0
)charset-utf8;

（二） 数据类型

1. 数字类型

   • 整数类型

     tinyint、smallint、mediumint、int、bigint为整数类型，一般常用int。

   • 浮点型

     1. float[m,d]：m代表数字总个数，最大值255，d是小数后个数，最大值30，随着小数的增多，精度变得不准确，
     2. double[m,d]：m代表数字总个数，最大值255，d是小数后个数，最大值30，精度比float高，但是也不准确
     3. decimal[m,d]：m代表数字总个数，最大值65，d是小数后个数，最大值30，随着小数的增多，精度始终精确

     # 例子1 ：
     create table t5(
     id int auto_increment primary key,
     salary decimal(16,10),
     num float
     )charset=utf8;
     
     # 例子2(刚好10位) ：
     insert into t5 (salary, num) values(500023.2312345678, 5000.2374837284783274832);
     
     # 例子3 （少于10位）
     insert into t5(salary,num) values (500023.231234567, 5000.2374837284783274832);
     
     # 例子4 （多于10位）
     insert into t5 (salary,num) values (500023.23123456789, 5000.2374837284783274832);

2. 字符串

   1. char(长度)：定长，范围为（0-255）

      无论插入字符是多少个，永远固定占规定的长度（会用空格填充），检索时会删掉尾部的空格

      应用场景：身份证、手机号、MD5加密的值

   2. varchar(长度)：变长，范围为（0-65535）

      根据插入的字符串的长度来计算所占的字节数，会增加一个字节的前缀来保存字符串的大小

      注意：如果不能确定插入数据的大小，一般建议使用varchar（255）

      create table t6(
      id int unsigned auto_increment primary key,
      name char(10) not null default 'xxx',
      )charset=utf8;
      
      insert into t6 (name) values ('hello')  # 只能输入10个字节以内
      
      insert into t6(name) values ('sadfasdfsafafdsadfdasf')  # 报错，超出范围

3. 时间日期类型

   1. year
   2. date
   3. time
   4. datetime
   5. timestamp

   一般使用datetime

   create table t8(
   d date,
   t time,
   dt datetime
   );
   
   select * from t8;

4. 枚举和集合

   1. enum：单选，只能在给定的一个范围内选一个值，比如性别
   2. set：多选，在给定的范围内可以选择一个或一个以上的值（爱好）

   create table t9(
   id int auto_increment primary key,
   gender enum('male','female')
   )charset=utf8;
   
   insert into t9 (gender) values('male');
   
   inser into t9 (gender) values ('female')l
   
   insert into t9(gender) values ('sdfasf0')  # 报错

（三） 增

（1） 增加表 create

# []内不是必填内容
create table 表名(
字段名 列类型 [约束条件]，  # 记住加逗号
····
字段名 列类型 [约束条件]   #最后一行不加逗号
）charset=utf8;  # 加分号


# 例子
#创建编码为utf8，字段为id、name的表t1
create table t1(
id int,
name char(5)
)charset=utf8;

# 增加数据
insert into t1 (id,name) values (1,'wick');

# 查询数据
select * from t1;  # *代表查询所有的列

（2）增加字段alter add

1. 默认添加最后一列

   alter table 表名
   add 字段名 列类型 [约束条件],
   add 字段名 列类型 [约束条件];
   
   # 默认添加在最后一列之后
   alter table t88
   add name varchar(32) not null default '';

2. 添加在首列（first）

   alter table 表名
   add 字段名 列类型 [约束条件] first;
   
   # 添加在首行
   alter table t88
   add name3 varchar(32) not null default '' first;

3. 添加在指定的位置（after）

   alter table 表名
   add 字段名 列类型 [约束条件] after 字段名;
   
   # 添加在首行
   alter table t88
   add name3 varchar(32) not null default '' after d;

（四）改

（1）修改表名 alter rename

alter table 旧表名 rename 新表名;

# 指令
alter table t8 rename t88;

（2）修改字段

1. modify

# 只可以修改字段的类型和约束条件
alter table 表名 modify 字段名 类型 [约束条件]

# 例子
alter table t88 modify name2 char(20);

2. change

# 可以修改字段、类型、约束条件
alter table 表名 change 旧字段名 新字段名 新数据类型 [约束条件]

# 例子
alter table t88 change name2 name22 varchar(32) not null default '';

alter table t88 change name22 name23;

（五）删

线上禁用

（1）删除表drop

drop table 表名

# 例子：
drop table t9;

（2）删除字段 alter drop

alter table 表名 drop 字段名;

# 例子
alter table t88 drop name4;

（六）查 show

show tables;：查看当前库所有表

show create table 表名;：查看表的创建语句

（七）复制表结构 create like

1. 查看表的创建语句，复制，运行

   show create table t88;

2. like实现

   create table 新表名 like 旧表名;

   create table t89 like t88;

三、操作表记录

（一）增 insert into

insert into 表名(列1,列2) values(值1,值2);

# 例子
insert into t1(id,name) values(1,'nick');

insert into t1(id,name) values(1,'tank'),(3,'zekai');

（二）查 select from

（1）where条件

1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 ：在80和100之间，闭区间，包含80和100
3. in（23,45,23）：值是23或45或23
4. like'wick%'：以wick开头，%代表任意多字符，_标识一个字符
5. 逻辑运算符：and or not

select 列1 , 列2 from 表名;
select * from 表名;  # 代表所有的列

# 例子
select * from t66 where id>30 and id<40;

select * from t66 where id between 31 and 33;

select * from t66 where id in (23,34,11);

select * from t66 where name like 'x%';  # x开头的
select * from t66 where name like '%x';  # x结尾的
select * from t66 where name like '%x%';  # 包含x的，不要用

# distinct避免重复
select distinct name from t66;

# 四则运算，不要用
select name, age*10 from t3;

（三）删 delete、truncate

delete from 表名;  #删除表中所有数据
delete from 表名 where 条件;
truncate 表名;  # 没有where文件的，

# 例子
delete from t5 where id=1;
delete from t5 where id>=1;
delete from t5 where id>=1 and id<10;

truncate和delete区别：

1. delete删除之后，再插入数据是从上一次的主键加1开始，truncate是从1开始
2. delete相当于一行一行的删除，truncate相当于全选删除

（四）改 update set

update 表名 set 列名1= 新值1，列名2=新值2 where 条件;

# 例子
uodate t66 set name = 'xxxx' where id = 30;

update t66 set name='xxxx' where id>20 or name='zekai';

补充

1. sql指令必须加;
2. tee D:/mylog.log：会自动把接下来所有的sql中语句和结果导入到文件中