mysql-3 数据表的创建、增删改查

程序员文章站 2022-05-08 08:44:50

...

1.创建数据表

通用语法：CREATE TABLE table_name (column_name column_type);

CREATE TABLE IF NOT EXISTS `csj_tbl`(
   `csj_id` INT UNSIGNED AUTO_INCREMENT,
   `csj_title` VARCHAR(100) NOT NULL,
   `csj_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `csj_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。不设置utf-8，输入中文会出现展示问题。
表名和字段名外面的符号是反单引 ` ，不是单引号。

2.删除数据表

删除数据表有3种情况。

类型	内容	空间
drop	删除表结构和全部数据	立刻释放磁盘空间，包括Innodb和MylSAM
truncate	保留表结构，删除全部数据	立刻释放磁盘空间，包括Innodb和MylSAM
delete	保留表结构，删除指定的数据	MyISAM会立刻释放磁盘空间，InnoDB不会释放空间。执行delete后，使用optimize table table_name会释放全部磁盘空间

drop table table_name;

drop table csj_tbl;    #删除表
truncate table csj_tbl;    #删除全部数据
delete from student;    #删除全部数据
delete from student where name="tom";    #删除张三的数据

3.插入数据

语法：INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );

#指定行添加
insert into csj_tbl (csj_title,csj_author,submission_date)
    values("学习 java","菜鸟教程",NOW());
#不指定行添加
insert into csj_tbl 
    values(2,"学习 python","菜鸟教程",'2016-03-06');
#添加多行数据
insert into csj_tbl (csj_title,csj_author,submission_date) values 
    ("学习 php","廖雪峰",NOW()),
    ("学习 c++","菜鸟教程",NOW());
select * from csj_tbl;
select * from csj_tbl;

不指明列，会将数据依次插入到每列。此时要将每个数值全部写入，即使自增的数据，也不能省略。

4.mysql查询数据

语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0

select * from csj_tbl where csj_author="菜鸟教程" LIMIT 2;

5.where字句，设定查询条件

where 条件1 and|or 条件2

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

条件中可以使用操作符
mysql-3 数据表的创建、增删改查

select * from csj_tbl where  (csj_author="菜鸟教程" ) and (csj_id = 2 );

6.修改数据update

语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。

update csj_tbl set csj_author="菜鸟教程2" where csj_id=2;
#修改日期类型数据
UPDATE ACCOUNT_USER set CREATE_TIME=TO_DATE('2018-11-28 09:38:26','yyyy-mm-dd hh24:mi:ss') where id = 'C803' ;
commit;

7.delete

语法：DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

delete from csj_tbl WHERE csj_id=1;

8.like字句

WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 "runoob_author = 'RUNOOB.COM'"。
但是有时候我们需要获取 runoob_author 字段含有 "COM" 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。
语法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

你可以在 WHERE 子句中指定任何条件。
你可以在 WHERE 子句中使用LIKE子句。
你可以使用LIKE子句代替等号 =。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
你可以使用 AND 或者 OR 指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

9.union操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
语法：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
#example
#注意：使用union，会自动使用第一个表的列名为列名，排序也只能对列1进行排序。
SELECT 列名称1 FROM 表名称1 UNION SELECT 列名称2 FROM 表名称2 ORDER BY 列名称1；
SELECT 列名称1 FROM 表名称1 UNION ALL SELECT 列名称2 FROM 表名称2 ORDER BY 列名称1；

expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

select csj_author from csj_tbl UNION 
select name from student;

10.排序

语法：

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。
你可以添加 WHERE...LIKE 子句来设置条件。

select * from csj_tbl order by csj_id desc;
#如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：
select * from csj_tbl order by CONVERT(csj_author using gbk) desc;
#如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY：
select * from csj_tbl order by csj_author desc;

11.mysql分组

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

准备数据

#创建表
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
commit;
set foregin_key_checks=1;
set names utf8;
#使用group by语句将数据表按名字进行分组，并统计每个人有多少条记录
select name,count(*) from employee_tbl group by name;
#对name进行分组，只会打印每个name下的第一条信息
select * from employee_tbl group by name;
#按name分组，并统计不同name下的的行数
select count(*) from employee_tbl group by name;

12.with rollup 可以在group by的基础上再进行统计

select * from employee_tbl;
select name,count(*) from employee_tbl group by name with ROLLUP;
select name,SUM(singin),count(*),AVG(singin) from employee_tbl GROUP BY name WITH ROLLUP;

mysql-3 数据表的创建、增删改查

13.coalesce设置取代null的名称

语法：

select coalesce(a,b,c);

示例：

select COALESCE(name,"合计"),count(*) as 登陆次数 from employee_tbl group by name with ROLLUP;
select COALESCE(name,"合计") as name,SUM(singin),count(*),AVG(singin) from employee_tbl GROUP BY name WITH ROLLUP;

mysql-3 数据表的创建、增删改查

14.having一般跟在group by之后，执行记录组选择的一部分工作的

例如列出登陆次数大于2次。如果使用where 登陆次数 > 3，会因为表中没有“登陆次数”这列而报错，因为where只能对表中原有的列进行筛选。如果对group by生成的列筛选，需要用使having

select COALESCE(name,"合计"),count(*) as 登陆次数 from employee_tbl group by name having 登陆次数 > 2;

mysql-3 数据表的创建、增删改查

FAQ

1.修改数据后，是否需要commit

#执行sql命令
show VARIABLES like '%autocommit%';
#如果是OFF即不自动commit，需要手动commit操作（命令行可以直接“commit；“命令），否则是自动commit。

mysql-3 数据表的创建、增删改查

2. foreign_key_checks;在mysql中设置外键约束

mysql中如果表和表之间建立了外键约束，则无法删除表以及修改表结构。
解决办法是在mysql中取消外键约束：set foregin_key_checks=0;
然后将原来表的数据导出到sql语句，重新创建此表后，再把数据使用sql导入。
然后再设置外键约束。 set foregin_key_checks=1;

3.使用distinct和group by去重的区别

distinct：

distinct必须在所有列的前面
distinct会针对列依次去重
group by：
只能针对单列进行分组，不能在group by后面跟多个列
可以在去重的结果上进行运算

#distinct去重
select * from csj_tbl;
select DISTINCT csj_author from csj_tbl;
select DISTINCT csj_author,submission_date from csj_tbl;
select DISTINCT csj_author,submission_date,csj_id from csj_tbl;

#group by 分组
select * from employee_tbl;
select * from employee_tbl group by name;
select name,SUM(singin),count(*),AVG(singin) from employee_tbl GROUP BY name;

结果：
mysql-3 数据表的创建、增删改查

上一篇：你必须了解的64位编程知识（上篇）

下一篇：前端基础系列（四）JavaScript基础（上篇）

mysql-3 数据表的创建、增删改查

1.创建数据表

2.删除数据表

3.插入数据

4.mysql查询数据

5.where字句，设定查询条件

6.修改数据update

7.delete

8.like字句

9.union操作符

10.排序

11.mysql分组

12.with rollup 可以在group by的基础上再进行统计

13.coalesce设置取代null的名称

14.having一般跟在group by之后，执行记录组选择的一部分工作的

FAQ

1.修改数据后，是否需要commit

2. foreign_key_checks;在mysql中设置外键约束

3.使用distinct和group by去重的区别

从vue基础开始创建一个简单的增删改查的实例代码(推荐)

mysql数据库表的创建以及字段的增删改查操作及一些常用的查询命令介绍

利用idea创建一个基本的smm项目（带增删改查）

安卓中SQLite数据库的创建、增删改查

c#对XML文档的创建与增删改查的示例代码

从vue基础开始创建一个简单的增删改查的实例

Django中ORM表的创建和增删改查方法示例

单链表的创建及增删改查功能的实现

XML——对XML文档的创建与增删改查

新用户的创建、表的创建及增删改查(Oracle)