1029 数据库表以及行的基本操作

目录

• 操作表
  • 1. 增
    • 列约束
    • 列类型
      • 1. 数字
        • 整型
        • 浮点型
      • 2. 字符串
        • char(长度):定长
        • varchar(长度):变长
        • 区别
      • 3. 时间日期类型
        • year
        • date
        • time
        • datatime
        • timestamp
      • 4. 枚举
  • 2.改
    • 1.修改表名
    • 2.添加字段
    • 3.删除字段
    • 4.修改字段
  • 3. 删
  • 4. 查
    • 查看所有表
    • 查询表中列的信息
  • 5. 复制表结构
    • 查看表的创建语句
    • like 复制
• 操作数据行
  • 1.增
  • 2.查
    • 查询数据行
    • 按条件查询
    • between...and...:取值范围是闭区间
    • distinct 去除重复元素
    • 通过四则运算查询
    • in() 查询输出指定列
    • like : 模糊查询
  • 3. 改
    • 指定列修改值
  • 4. 删
    • 根据条件删除列
    • 删除表中所有数据
    • truncate 全选删除
    • 区别

操作表

表类似于文件

1. 增

创建新表

create table 表名(
    字段名  列类型 [可选的参数],  ### 记住加逗号
    字段名  列类型 [可选的参数],  ### 记住加逗号
    字段名  列类型 [可选的参数]  ### 最后一行不加逗号
    .....
) charset = utf-8 ;    

例子:
    create table t1(
    	id int,		  # int 数字类型			
        name char(5)   # char代表字符串
    ) charset = utf8 ;
'''
mysql> create table t1(ID int,name char(5))charset = utf8;
Query OK, 0 rows affected (0.02 sec)'''

列约束

****************************************

auto_increment:		 自增1
primary key			主键索引,加快查询速度,列的值不能重复
not null			标识该字段不能为空
default				为该字段设置默认值

例子:
    create table t2(
    id int auto_increment primary key,
    name char(10) 
    )charset=utf8;
    '''Query OK, 0 rows affected (0.02 sec)'''

    mysql> insert into t2(name) values ('ersha');
        # 添加值也可以只添加一项,id项是自增的
    '''Query OK, 1 row affected (0.01 sec)'''
    
    mysql> select * from t2 ;
    /*  +----+-------+
        | id | name  |
        +----+-------+
        |  1 | ersha |
        |  2 | dasha |
        +----+-------+
        2 rows in set (0.00 sec) */

推荐使用:
	create table t3(
    	id int unsigned auto_increment primary key,
        //  设置无符号int      自增加1       主键索引
        name char(10) not null default 'xxx',
        //   字符串(10)   非空     设置默认值为xxx
        age int not null default 0
        //   int    非空     默认值0
    )charset = utf8 ;
//  Query OK, 0 rows affected (0.02 sec)

// 只添加 age 值,其余的默认值
	insert into t3 (age) values(10);
	// Query OK, 1 row affected (0.00 sec)

// 查看t3表内容
	 select * from t3 ;
    /*  +----+------+-----+
        | id | name | age |
        +----+------+-----+
        |  1 | xxx  |  10 |
        +----+------+-----+
        1 row in set (0.00 sec) */ 

列类型

****************************************

create table 表名(
	字段名 列类型 unsigned [可选参数],
	字段名 列类型 [可选参数],
	字段名 列类型 [可选参数]
	.....
	)charset = utf8 ;

1. 数字

整型

分类:
    tinyint	 //(微小int)
    smallint //(小的int)
    int		// 常用的 ********************推荐使用
    mediumint //(中等int)
    bigint	//(大int)
    
区别:
    1. 整数类型
    2. 取值范围
    3. unsigned   加上代表不能取负数,只适用于整型.

应用场景:
	根据公司业务场景,来选取合适的类型

浮点型

*********

'''类型
float：4字节，3.4E–38～3.4E+38 *
double：8字节，1.7E–308～1.7E+308
decimal：M，D大值基础上+2
'''
    
'''宽度：
限制存储宽度
(M, D) => M为位数，D为小数位
float(255, 30)：精度最低，最常用
double(255, 30)：精度高，占位多
decimal(65, 30)：字符串存，全精度
'''

float 不一定精准

decimal 非常精确的数字

decimal(m,d) m是数字总个数(负号不算),d是小数点后个数

// 新建t5表
create table t5(
	id int auto_increment primary key,
    salary decimal(16,10),
    num float
)charset = utf8 ;

1.	// 添加数据 (decimal 正好10位)
    insert into t5 (salary,num)
        values(500023.0123456789,5000.0123456789);
    // 查询数据
    select * from t5;
    /*
    +----+-------------------+---------+
    | id | salary            | num     |
    +----+-------------------+---------+
    |  1 | 500023.0123456789 | 5000.01 |
    +----+-------------------+---------+
    1 row in set (0.00 sec)
    */

2.	// 添加数据 (decimal 少于10位)
    insert into t5 (salary,num)
        values(500023.01234567,5000.0123456789);
	// 查询数据
    select * from t5;
	/*
    +----+-------------------+---------+
    | id | salary            | num     |
    +----+-------------------+---------+
    |  1 | 500023.0123456789 | 5000.01 |
    |  2 | 500023.0123456700 | 5000.01 |
    +----+-------------------+---------+
    2 rows in set (0.00 sec)
    */ 会自动补全10位
        
3.	// 添加数据 (decimal 多于10位)
    insert into t5 (salary,num)
		values(500023.012345678989,5000.0123456789);
	// 查询数据
    select * from t5;
	/* 
    +----+-------------------+---------+
    | id | salary            | num     |
    +----+-------------------+---------+
    |  1 | 500023.0123456789 | 5000.01 |
    |  2 | 500023.0123456700 | 5000.01 |
    |  3 | 500023.0123456790 | 5000.01 |
    +----+-------------------+---------+
    */  会四舍五入进1

2. 字符串

char(长度):定长

create table t6 (
	id int unsigned auto_increment primary key,
    name char(10) not null default 'xxx'
)charset = utf8 ;

// 插入数据(少于10位)
    insert into t6(name) values('hellow');

    // 查看数据行
    select * from t6;
    /* 
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | hellow |
    +----+--------+
    1 row in set (0.00 sec)
    */
// 插入数据(多于10)
	insert into t6(name) values('dadadadbeia');
	/*		
ERROR 1406 (22001): Data too long for column 'name' at row 1		超出长度报错

varchar(长度):变长

create table t6(
	id int auto_increment primary key,
    name varchar(10) not null default 'xxx'
) charset = utf8;

// 插入数据(小于10)
	insert into t6(name) values('hellow');
	
	// 查看数据行
	select * from t6;
	/*
	+----+--------+
    | id | name   |
    +----+--------+
    |  1 | hellow |
    +----+--------+
    1 row in set (0.00 sec) */
// 插入数据(大于10)
	insert into t6(name) values('dadadadbeia');
/*
ERROR 1406 (22001): Data too long for column 'name' at row 1		超出长度报错

区别

char定长

无论插入的字符是多少个,永远固定占用规定的长度
应用场景:
	身份证
	手机号
	md5加密之后的值,比如密码等 char(32)

varchar变长

根据插入的长度来计算所占的字节数,但是有一个字节是用来存储字符串长度的
注意:如果不能确定插入的数据的大小,一般建议使用varchar(255)

3. 时间日期类型

year

YYYY（1901/2155）

date

YYYY-MM-DD（1000-01-01/9999-12-31）

time

HH:MM:SS（'-838:59:59'/'838:59:59'）

datatime

************************

YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59  ）

timestamp

时间戳	YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

例子

create table t8(
	d data,
	t time,
    dt datetime
);

// 添加数据行
insert into t8 values(now(),now(),now());

// 查询数据
select * from t8;
/*
    +------------+----------+---------------------+
    | d          | t        | dt                  |
    +------------+----------+---------------------+
    | 2019-10-29 | 17:17:05 | 2019-10-29 17:17:05 |
    +------------+----------+---------------------+
    1 row in set (0.00 sec)

4. 枚举

列出所有的选项(enum) 只接受正确输入的数值

create table t9(
	id int auto_increment primary key,
	gender enum('male','female')
)charset utf8;

// 插入数据行
    insert into t9 (gender) values ('male');
        //Query OK, 1 row affected (0.01 sec)
	insert into t9 (gender) values ('female');
		//Query OK, 1 row affected (0.01 sec)
	insert into t9 (gender) values ('snsdd');
		// ERROR 1265 (01000): Data truncated for column 'gender' at row 1

2.改

1.修改表名

alter table 旧表名 rename 新表名;

//修改表名
alter table t8 rename t88;

2.添加字段

1.alter table 表名
    add 字段名 列类型[可选参数],
	add 字段名 列类型[可选参数];

    //增加字段  	(永远在最后一行添加)
    alter table t88 add name varchar(32) not null default'';
    /*	Query OK, 0 rows affected (0.08 sec)
    Records: 0  Duplicates: 0  Warnings: 0	*/


2.alter table 表名 
    add 字段名 列类型 [可选参数] first;

// 添加字段		(永远在第一行)
alter table t88 add name3 varchar(32) not null default'' first;
/*	Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0	*/


3.alter table 表名
    add 字段名 列类型[可选参数] after 字段名;

// 添加字段		(可添加在指定数据后)
alter table t88 add name4 varchar(32) not null default '' after d ;
/*	Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0	*/

3.删除字段

alter table 表名 drop 字段名;

// 删除字段
alter table t88 drop name4 ;

/*	Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0	*/ 

4.修改字段

1.	// 指定字段修改类型
alter table t88 modify 字段名 数据类型[完整性约束条件...];
    // 修改字段
    alter table t88 modify name3 char(20);

    /*	Query OK, 1 row affected (0.05 sec)
    Records: 1  Duplicates: 0  Warnings: 0	*/


2.	// 修改字段至新值
alter table 表名 change 旧字段名 新字段名 新数据类型[完整性约束条件...];
	// 修改
	alter table t88 change name3 name33 varchar(32) not null default '' ;
	/*	Query OK, 1 row affected (0.05 sec)
	Records: 1  Duplicates: 0  Warnings: 0	*/

	//修改2  
	alter table t88 change name33 name3 ;
	报错 不能

3. 删

线上禁用

drop table 表名;

drop table t9;
//Query OK, 0 rows affected (0.01 sec)

4. 查

查看所有表

show tables;

查询表中列的信息

desc 表名;

5. 复制表结构

查看表的创建语句

show create table 表名;

show create table t88;

/*
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t88   | CREATE TABLE `t88` (
  `name33` varchar(32) NOT NULL DEFAULT '',
  `d` date DEFAULT NULL,
  `t` time DEFAULT NULL,
  `dt` datetime DEFAULT NULL,
  `name` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

like 复制

create table t89 like t88;

// Query OK, 0 rows affected (0.02 sec)

操作数据行

数据行像是在文件中的数据

1.增

添加数据行

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

例子:
	insert into t1 (id,name) values (1,'dasha');
    insert into t1(id,name) values (2,'ersha'),(3,'sansha'),(4,'sisha');
   
#  Query OK, 1 row affected (0.00 sec)

2.查

查询数据行

select 列1,列2 from 表名;    	// 查询指定的列
select * from 表名;			// * 查询所有的列

按条件查询

1. select * from 表名 where 条件
2. select * from 表名 where 条件 and 条件
   
select * from t3 where id > 4 ;
//	'>' '<' '=' '>=' '<=' '!=' '<>'	 都可做判断//

/*
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  5 | sisha |   0 |
|  6 | wusha |   0 |
+----+-------+-----+
2 rows in set (0.01 sec)	*/

between...and...:取值范围是闭区间

select * from t3 where id between 2 and 4;

/*
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  2 | dasha  |   0 |
|  3 | ersha  |   0 |
|  4 | sansha |   0 |
+----+--------+-----+
3 rows in set (0.00 sec)

distinct 去除重复元素

select distinct name from t3;
/*
+--------+
| name   |
+--------+
| xxx    |
| dasha  |
| ersha  |
| sansha |
| sisha  |
| wusha  |
+--------+
6 rows in set (0.00 sec)

通过四则运算查询

尽量不要使用,占用查询内存

select name ,age*10 from t3;
select name, age*10 as age from t3;

in() 查询输出指定列

select * from t3 where id in (2,4,1);

/*
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | xxx    |  10 |
|  2 | dasha  |   0 |
|  4 | sansha |   0 |
+----+--------+-----+
3 rows in set (0.00 sec)

like : 模糊查询

不让用,与主键查询比占用内存

1.以'x'开头
    select * from t3 where name like 'x%';
/*
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | xxx  |  10 |
+----+------+-----+
1 row in set (0.00 sec)	*/

2.以'a'结尾
    select * from t3 where name like '%a';
/*
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  2 | dasha  |   0 |
|  3 | ersha  |   0 |
|  4 | sansha |   0 |
|  5 | sisha  |   0 |
|  6 | wusha  |   0 |
|  7 | dasha  |   0 |
+----+--------+-----+
6 rows in set (0.00 sec)	*/

3.包含'n'的
    select * from t3 where name like '%n%';
    
/*
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  4 | sansha |   0 |
+----+--------+-----+
1 row in set (0.00 sec)
    

3. 改

指定列修改值

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

// 修改列名
	update t3 set name='yyy' where id=3;
	// "<" ">" "=" "<=" ">=" "and" "or"
/*	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0	*/
//查询列内容
	select * from t3;
/*
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | xxx    |  10 |
|  2 | dasha  |   0 |
|  3 | yyy    |   0 |

4. 删

根据条件删除列

delete from 表名 where 条件;
	delete from t5 where id=1 ;
	delete from t5 where id>1 and id <3
//	'<' '>' "=" "<=" ">="   都可判断

删除表中所有数据

delete from 表名;
// 删除表中所有的数据 Query OK, 1 row affected (0.00 sec)

truncate 全选删除

没有where条件的

truncate t5;

区别

1. delete 之后,插入数据从上一次主键自增加1开始,truncate则是从1开始.
2. delete删除是一行一行删除. truncate:是全选删除,删除速度是高于delete的