33、mysql表增删改查、视图-白红宇

33、mysql表增删改查、视图

阅读量：5876 次

发布时间：2019-06-19

本文共 9718 字，大约阅读时间需要 32 分钟。

表创建：第一种方式

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_options]

(create_definition,...)：

字段的定义：字段名、类型和类型修饰符

键、约束或索引：

PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK

{INDEX|KEY}

USE mydb;

CREATE TABLE t1 (ID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,Name VARCHAR(50) NOT NULL,Age TINYINT NOT NULL, Gender ENUM('M','F') NOT NULL DEFAULT "M",Course VARCHAR(50) NOT NULL);

DESC t1;

[table_options]

ENGINE [=] engine_name

AUTO_INCREMENT [=] value

[DEFAULT] CHARACTER SET [=] charset_name

[DEFAULT] COLLATE [=] collation_name

COMMENT [=] 'string'

DELAY_KEY_WRITE [=] {0 | 1}

ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

查看支持的存储引擎

SHOW ENGINES;

查看指定表的存储引擎

SHOW TABLE STATUS LIKE 't1'\G;

创建表时指定存储引擎

CREATE TABLE t2 (ID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,Name VARCHAR(50) NOT NULL,Age TINYINT NOT NULL, Gender ENUM('M','F') NOT NULL DEFAULT "M",Course VARCHAR(50) NOT NULL) ENGINE = "MyISAM";

MyISAM表，每表有三个文件，都位于数据库目录中

tb_name.frm: 表结构定义

tb_name.MYD: 数据文件

tb_name.MYI: 索引文件

InnoDB表，有两种存储方式

1、默认：每表有一个独立文件和一个多表共享的文件

tb_name.frm: 表结构的定义，位于数据库目录中；

ibdata#: 共享的表空间文件，默认位于数据目录(datadir指向的目录)中；

2、独立的表空间：

tb_name.frm: 每表有一个表结构文件

tb_name.ibd: 一个独有的表空间文件

查看是否已启用每表一个表空间文件

SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';

启用每表一个表空间文件

SET GLOBAL innodb_file_per_table = ON;（重启mysqld后失效）

echo "innodb_file_per_table = ON" >> /etc/my.cnf （永久有效）

表创建：第二种方式（复制表数据）

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

[table_options]

select_statement

CREATE TABLE t3 SELECT * FROM t1;

DESC t3;

表创建：第三种方式（复制表结构）

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

{ LIKE old_tbl_name | (LIKE old_tbl_name) }

CREATE TABLE t4 LIKE t1;

DESC t4;

表删除：（CASCADE相关联的表也会被删除）

DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] ...

[RESTRICT | CASCADE]

表插入：

第一种：

INSERT INTO tb_name [(col1, col2,...)] {VALUES|VALUE} (val1, val2,...)[,(val21,val22,...),...]

INSERT INTO t1 (Name,Age,Gender,Course) VALUE ('Ling Huchong',24,'M','Hamogong');

INSERT INTO t1 (Name,Age,Gender,Course) VALUE ('Huang RONG',19,'F','Chilian Shenzhang');

INSERT INTO t1 VALUES (3,'Lu Wushang',18,'F','Jiuyang Shenggong');

第二种：

INSERT INTO tb_name SET col1=val1, col2=val2, ...

INSERT INTO t1 SET Name='Zhu Ziliu',Age=52,Course='Pixie Jianfa';

INSERT INTO t1 SET ID=5,Name='Chen Jialuo',Age=22,Gender='M',Course='Xianglong Shiba Zhang';

INSERT INTO t1 SET ID=6,Name='Ou Yangfeng',Age=70,Gender='M',Course='Shenxiang Bannuo Gong';

第三种：（从其他表查询插入）

INSERT INTO tb_name SELECT clause

REPLACE的工作机制：与INSERT相同，除了在新插入的数据与表中的主键或惟一索引定义的数据相同会替换老的行；

表修改：

ALTER TABLE tbl_name

[alter_specification [, alter_specification] ...]

修改字段定义：

插入新字段

ADD [COLUMN] col_name column_definition

[FIRST | AFTER col_name]

ALTER TABLE t1 ADD Class VARCHAR(50) NOT NULL;

ALTER TABLE t1 ADD test1 VARCHAR(50) NOT NULL FIRST;

ALTER TABLE t1 ADD test2 VARCHAR(50) NOT NULL AFTER test1;

删除字段

DROP [COLUMN] col_name

ALTER TABLE t1 DROP test1;

修改字段

修改字段名称

CHANGE [COLUMN] old_col_name new_col_name column_definition

[FIRST|AFTER col_name]

ALTER TABLE t1 CHANGE test2 test1 VARCHAR(50) NOT NULL;

修改字段类型及属性等

MODIFY [COLUMN] col_name column_definition

[FIRST | AFTER col_name]

ALTER TABLE t1 MODIFY test1 VARCHAR(30) NOT NULL AFTER Class;

修改约束、键或索引：

SHOW INDEX FROM t1;

ALTER TABLE t1 ADD INDEX(test1);

ALTER TABLE t1 DROP INDEX test1;

表改名：

RENAME [TO|AS] new_tbl_name

ALTER TABLE t1 RENAME to t6;

RENAME TABLE t6 TO t1;

练习题：

新建如下表（包括结构和内容）：

ID Name Age Gender Course

1 Ling Huchong 24 Male Hamogong

2 Huang Rong 19 Female Chilian Shenzhang

3 Lu Wushaung 18 Female Jiuyang Shenggong

4 Zhu Ziliu 52 Male Pixie Jianfa

5 Chen Jialuo 22 Male Xianglong Shiba Zhang

6 Ou Yangfeng 70 Male Shenxiang Bannuo Gong

CREATE TABLE t1 (ID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,Name VARCHAR(50) NOT NULL,Age TINYINT NOT NULL, Gender ENUM('M','F') NOT NULL DEFAULT "M",Course VARCHAR(50) NOT NULL);

INSERT INTO t1 (Name,Age,Gender,Course) VALUE ('Ling Huchong',24,'M','Hamogong');

INSERT INTO t1 (Name,Age,Gender,Course) VALUE ('Huang RONG',19,'F','Chilian Shenzhang');

INSERT INTO t1 VALUES (3,'Lu Wushang',18,'F','Jiuyang Shenggong');

INSERT INTO t1 SET Name='Zhu Ziliu',Age=52,Course='Pixie Jianfa';

INSERT INTO t1 SET ID=5,Name='Chen Jialuo',Age=22,Gender='M',Course='Xianglong Shiba Zhang';

INSERT INTO t1 SET ID=6,Name='Ou Yangfeng',Age=70,Gender='M',Course='Shenxiang Bannuo Gong';

1、新增字段Class 字段定义自行选择；放置于Name字段后；

ALTER TABLE t1 ADD Class VARCHAR(50) NOT NULL AFTER Name;

2、将ID字段名称修改为TID;

ALTER TABLE t1 CHANGE ID TID TINYINT NOT NULL AUTO_INCREMENT

3、将Age字段放置最后；

ALTER TABLE t1 MODIFY Age TINYINT NOT NULL AFTER Course;

表更新：

UPDATE：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1=val1 [, col_name2=val2] ...

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

UPDATE通常情况下，必须要使用WHERE子句，或者使用LIMIT限制要修改的行数；

UPDATE t1 SET Course='Zuibaxian' WHERE Name='Ling Huchong';

表内容删除：

DELETE:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

DELETE FROM t1 where TID=6;

表内容清空：

TRUNCATE t1；

MySQL的查询操作：

单表查询：简单查询

多表查询：连续查询

联合查询：

选择和投影：

投影：挑选要显示的字段

选择：挑选符合条件的行

投影：SELECT 字段1, 字段2, ... FROM tb_name;

SELECT * FROM t1;

SELECT Name,Age FROM t1;

选择：SELECT 字段1, ... FROM tb_name WHERE 子句;

布尔条件表达式

SELECT ID,Name,Age FROM t1 WHERE Age > 50;

布尔条件表达式操作符：

=等于

<=>等于

<>不等于

<小于

<=小于等于

>大于

>=大于等于

IS NULL值为NULL

IS NOT NULL值为非NULL

LIKE支持的通配符: %(任意长度的任意字符)， _（任意单个字符）

RLIKE, REGEXP支持使用正则表达式

IN判断指定字段的值是否在给定在列表中；

BETWEEN...AND...位于指定的范围之间

组合条件测试：

NOT, !非

AND, &&与

OR, ||或

聚合函数：

SUM()求和

AVG()求平均值

MAX()求最大值

MIN()求最小值

COUNT()计数

CREATE TABLE students (SID INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE KEY,Name CHAR(30) NOT NULL,Age TINYINT UNSIGNED NOT NULL,Gender ENUM('F','M') NOT NULL,Tutor char(30));

INSERT INTO students VALUES (1,'guojing',27,'M','songjiang'),(2,'huangyong',28,'F','huhong'),(3,'lulifang',21,'M','moma');

INSERT INTO students VALUES (4,'guoji',19,'M','songang'),(5,'huyong',37,'M','honghong'),(6,'lifang',21,'F','niuniu'),(7,'tomy',13,'M',NULL);

alter table students add ClassID tinyint unsigned;

update students set ClassID=2 where SID=1;

update students set ClassID=3 where SID=2;

update students set ClassID=3 where SID=3;

update students set ClassID=1 where SID=4;

update students set ClassID=2 where SID=5;

update students set ClassID=1 where SID=6;

update students set ClassID=4 where SID=7;

布尔条件查询语句

SELECT * FROM students WHERE Age <> 19;

SELECT Name,Age,Tutor FROM students WHERE Tutor IS NULL;

SELECT Name,Age,Tutor FROM students WHERE Name LIKE 'g%';

SELECT Name,Age,Tutor FROM students WHERE Name RLIKE '^g.*';

SELECT Name,Age,Tutor FROM students WHERE Age IN(19,20,21);

SELECT Name,Age,Tutor FROM students WHERE Age BETWEEN 19 AND 21;

组合条件查询语句

SELECT Name,Age,Gender FROM students WHERE Age > 25 AND Gender = 'M';

SELECT Name,Age,Gender FROM students WHERE Age > 25 AND Gender = 'M' ORDER BY Name ASC; （升序排序，默认为升序）

SELECT Name,Age,Gender FROM students WHERE Age > 25 AND Gender = 'M' ORDER BY Name DESC; （降序排序）

聚合函数查询语句

SELECT SUM(Age) FROM students;

SELECT AVG(Age) FROM students;

SELECT MAX(Age) FROM students;

SELECT MIN(Age) FROM students;

SELECT语句:

GROUP ... BY ...以什么为分组

HAVING ...与GROUP ... BY ... 结合使用

LIMIT ...限制显示的行数

DISTINCT指定的结果相同的只显示一次；

SQL_CACHE缓存于查询缓存中；

SQL_NO_CACHE不缓存查询结果；

统计表的行数

SELECT COUNT(Age) FROM students;

统计表中年龄大于25岁的人数

SELECT COUNT(Age) FROM students WHERE Age > 25;

统计以Gender为分组，各分组的人数

SELECT Gender,COUNT(Age) FROM students GROUP BY Gender;

统计以Gender为分组，各分组年龄大于25岁的人数

SELECT Gender,COUNT(Age) FROM students WHERE Age > 25 GROUP BY Gender;

统计各班的人数

SELECT ClassID,Count(Name) FROM students GROUP BY ClassID;

统计各班的人数、年龄之和

SELECT ClassID,Count(Name),SUM(Age) FROM students GROUP BY ClassID;

统计班级人数大于等于2人的班级

SELECT ClassID,COUNT(Name) FROM students GROUP BY ClassID HAVING COUNT(Name) >=2 ;

统计班级年龄之后小于等于50的班级

SELECT ClassID,SUM(Age) FROM students GROUP BY ClassID HAVING SUM(Age) <= 50;

限制显示2行

SELECT * FROM students LIMIT 2;

略过前面两行，显示后面的3行

SELECT * FROM students LIMIT 2,3;

显示年龄大于19岁的年龄，重复的只显示一次

SELECT DISTINCT Age FROM students WHERE Age > 19;

SELECT语句的执行流程：

FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... --> SELECT --> LIMIT

mysql -uroot -p < ./hellodb.sql

USE hellodb;

自然联结（等值联结)

SELECT students.Name,classes.Class FROM students,classes WHERE students.ClassID = classes.ClassID;

SELECT s.Name,c.Class FROM students AS s,classes AS c WHERE s.ClassID = c.ClassID;

上述两条SELECT语句效果一致

外联结：（mysql不支持全外联结）

左外联结：只保留出现在左外连接运算之前（左边）的关系中的元组；

left_tb LEFT JOIN right_tb ON 连接条件

SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID = c.ClassID;

右外联结：只保留出现在右外连接运算之后（右边）的关系中的元组；

left_tb RIGHT JOIN right_tb ON 连接条件

SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID = c.ClassID;

自联结：

显示学生和对应任课老师的名字

SELECT t.Name,s.Name FROM students AS t,students AS s WHERE t.StuID = s.TeacherID;

别名：

表别名

SELECT s.Name,c.Class FROM students AS s,classes AS c WHERE s.ClassID = c.ClassID;

字段别名

SELECT Name FROM students;

SELECT Name AS StuName FROM students;

子查询：

用于WHERE中的子查询

用于条件比较子查询只能一个值

用于IN 子查询可以返回多个值

EXISTS 子查询可以返回多个值

用于FROM子句的子查询

SELECT alias.col,... FROM (SELECT clause) AS alias WHERE condition

MySQL不擅长于子查询：应该避免使用子查询；

SELECT Name,Age,Gender FROM students WHERE Age > 25 AND Gender = 'M';

SELECT s.Name,s.Age,s.Gender FROM (SELECT * FROM students WHERE Gender = 'M') as s WHERE s.Age > 25;

上述两条SELECT语句效果一致

MySQL的联合查询：SELECT clauase UNION SELECT clause UNION ...

把两个或多个查询语句的结果合并成一个结果进行输出；

MySQL视图:

将存储下来的SELECT语句当做表来用

CREATE VIEW stu AS SELECT StuID,Name,Age,Gender FROM students;

SHOW TABLES;

SHOW TABLE STATUS\G;

SELECT * FROM stu;

DROP VIEW stu;

转载于:https://blog.51cto.com/kaiyuandiantang/2313501

你可能感兴趣的文章

JDK8中ArrayList的工作原理剖析

查看>>

安装gulp及相关插件

查看>>

如何在Linux用chmod来修改所有子目录中的文件属性？

查看>>

Applet

查看>>

高并发环境下，Redisson实现redis分布式锁

查看>>

乌克兰基辅一世遗修道院起火现场火光照亮夜空

查看>>

[iOS 10 day by day] Day 2：线程竞态检测工具 Thread Sanitizer

查看>>

Centos/Ubuntu下安装nodejs

查看>>

关于浏览器的cookie

查看>>

Hyper-V 2016 系列教程30 机房温度远程监控方案

查看>>

国内先进的智能移动广告聚合平台-KeyMob聚合

寒假。3.3.G - Common Child （最大公共子序）

查看>>

设计模式学习笔记--原型模式

查看>>

.Net 通过MySQLDriverCS操作MySQL

查看>>