~阿弥陀佛~

人生能有多少天是按照自己的想法去活着

数据库操作详解 (7-9)

精讲7 SQL与关系数据库基本操作

 

1.结构化查询语言(Structured Query Language,SQL)是专门用来与数据库通信

的语言,它可以帮助用户操作关系数据库。

2. SQL特点:SQL 不是某个特定数据库供应商专有的语言 ;SQL 简单易学 ;SQL 强大、灵活,可以进行非常复杂和高级的数据库操作。

3. SQL组成:数据查询、数据定义、数据操纵、数据控制

数据定义语言(Data Definition Language,DDL)

CREATE:创建数据库或数据库对象

ALTER:对数据库或数据库对象进行修改

DROP:删除数据库或数据库对象

数据操纵语言(Data Manipulation Language,DML)

SELECT:从表或视图中检索数据

INSERT:将数据插入到表或视图中

UPDATE:修改表或视图中的数据

DELETE:从表或视图中删除数据

数据控制语言(Data Control Language,DCL)

GRANT:用于授予权限

REVOKE:用于收回权限

4. 嵌入式和动态SQL规则:嵌入式和动态SQL规则规定了SQL语句在高级语言程序设计中使用的规范方法,一遍适用较复杂的应用。

5. SQL调用与会话规则:SQL调用SQL历程和调用规则,以便提高SQL的灵活性、有效性、共享性使SQL具有更多的高级语言的特征。

6. 关系数据库管理系统(RDBMS)体积小、速度快、开放源代码、遵循GPL

7. MYSQL的两种架构方式

LAMP:使用 Linux 作为操作系统,Apache 作为 web 服务器,MYSQL 作为数据库管理系统,PHP,Perl 或 Python 语言作为服务端脚本解释器;

WAMP:使用 Windows 作为操作系统,Apache 作为 web 服务器,MYSQL 作为数据库管理系统,PHP,Perl 或 Python 语言作为服务端脚本解释器;

8. MYSQL扩展语言要素:常量、变量、运算符、表达式、内置函数

常量. 也称字面值或标量值

字符串常量:用单引号或双引号括起来的字符序列,分为ASCII字符串常量和Unicode字符串常量

数值常量:整数常量和浮点数常量。

十六进制常量:每对十六进制数字被转换为一个字符,其最前面有一个字母“X”(或“x”)

时间日期常量:用单引号讲表示日期时间的字符串括起来而构成的 例如:’2018-06-05’

位字段值:格式:b’value’à二进制值

布尔值TURE—1  FALSE-0

NULL值

 

变量. 临时存储数据

用户变量:用户变量前常添加一个符号@,用于将其与列名区分开

系统变量:大多数系统变量应用于其他SQL语句中时,必须在系统变量前添加两个@

 

运算符

算术运算符+(加)、-(减)、*(乘)、/(除)、%(求模)即整数求余

位运算符&(位与)、|(位或)、^(位异或)、~(位取反)、>>(位右移)、<<(位左移)

比较运算符=(等于)、>(大于)、<(小于)、>=(大于等于)、<=(小于等于)、<>(不等于)、!=(不等于)、<=>(相等或都等于空)

逻辑运算符OT或!(逻辑非)、AND或&&(逻辑与)、OR或||(逻辑或)、XOR(逻辑异或)

 

表达式(表达式是常量、变量、列名、复杂计算、运算符和函数的组合)

字符型表达式

数值型表达式

日期型表达式

 

内置函数

数学函数 ABS()(绝对值函数)……

聚合函数 COUNT()(返回行数)……

字符串函数 ASCII()(字符串表达式最左端字符的ASCII代码值)…..

日期和时间函数 NOW()(日期时间)……

加密函数 ENCODE()(加密)……

控制流程函数 IF()(检验条件)……

格式化函数 FORMAT()(指定格式)……

类型转换函数 CAST()(转换数据类型)……

系统信息函数 USER()取得 当前登陆的用户……

 

9. 创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

    [DEFAULT]CHARACTER SET[=]charset_name

    |[DEFAULT]COLLATE[=]collation_name

create database或create schema语句:cahracter set设置字符集、[]为可选项。|分隔花括号中的选择项。 if not exist判断数据库是否存在。default指定默认值。collate指定字符集校对规则。

 

 

10.  create database db_name

     use dbname

11.       CREATE DATABASE mysql_test

    -> default character set gb2312

    -> default collate gb2312_chinese_ci;

12.  DROP{DATABASE|SCHEMA}[IF EXISTS]db_name

     drop database if exists hahaha; (防止报错信息)

13. SHOW {DATABASES | SCHEMAS}

    Like关键字用于匹配指定的数据库名称;

    Where从句用于指定数据库名称查询范围的条件。

 

 

 

 

精讲8:SQL基本操作

1. 创建表

数据表是关系数据库中最重要、最基本的数据对象,也是数据存储的基本单位

数据表被定义为字段的集合,按行和列的格式存储,每一行代表一条记录,每一列代表记录中一个字段的取值。需要确定表中每个字段的数据类型。

以下表要求:在一个已有数据库mysql_test中新建一个包含客户姓名、性别、地址、联系方式等内容的客户基本信息表,要求将客户的id号指定为该表的

主键。

 

CREATE TABLE customers

(

cust_id INT NOT NULL AUTO_INCREMENT,

cust_name CHAR(50)NOT NULL,

cust_sex CHAR(1)NOT NULL DEFAULT 0,

cust_address CHAR(50)NULL,

cust_contact CHAR(50)NULL,

PRIMARY KEY(cust_id)

);

【释义:① create table为持久表,若添加temporary则为临时表;

② int(整型)、double(浮点型)、bool(布尔型)、date(日期型)、timestamp(时间戳)、time(时间型)、char(定长字符串型)、varchar(可变长字符);

③ auto_increment:为当前列设置自增长 default设置默认值  null缺值, primary key指定主键】

2. 更新表

使用ALTER TABLE语句,增加或删减列、创建或取消索引、更改原有列的数据类型、重新命名列或表,更改表的评注和表的引擎类型,为表重新创建触发器、存储过程、索引和外键等。

ADD[COLUMN]子句

ALTER TABLE mysql_test.customers ADD COLUMN cust_city1 char(10)NOT NULL DEFAULT 'Wuhan' AFTER cust_sex;

【释义:add column增加新列】

 

CHANGE[COLUMN]子句:修改表中列的名称或数据类型

ALTER TABLE customers CHANGE COLUMN cust_sex sex char(1)NULL DEFAULT 'M';

释义:change column 修改列的名称或数据类型;cust_sex sex重命名为sex; 默认为字符常量M

 

ALTER[COLUMN]子句:修改或删除表中指定列的默认值

ALTER TABLE mysql_test.customers ALTER COLUMN cust_city SET DEFAULT 'beijing';

【释义:alter column修改或删除指定列的默认值】

 

MODIFY[COLUMN]子句:只修改指定列的数据类型,不会干涉它的列名

ALTER TABLE mysql_test.customers MODIFY COLUMN cust_name char(20) FIRST;

【释义:modify column 修改指定列的数据类型, first设置为表的第一列】

 

DROP[COLUMN]子句:删除表中多余的列

ALTER TABLE mysql_test.customers DROP COLUMN cust_contact;


RENAME[TO]子句:为表重命名

ALTER TABLE mysql_test.customers RENAME TO mysql_test.backup_customers;

RENAME TABLE mysql_test.backup_customers TO mysql_test.customers;

 

表操作语句结构集合:

  SHOW [FULL] TABLE [{FROM | IN} db_name]  [LIKE’pattern’ | WHERE expr]   (查看数据库中的表)

  SHOW [FULL] COLUMNS {FROM | IN} tbl_name[{FROM | IN} db_name] [LIKE’pattern’ | WHERE expr]  (查看表结构)

 {DESCRIBE | DESC} tbl_name [col_name | wild]   (查看表结构)

 

 

3. 索引

索引定义:索引是提高数据文件访问效率的有效方法

索引通常被创建为单列索引和组合索引

索引存在的弊端:

1)索引是以文件的形式存储的,如果有大量的索引,索引文件可能比数据文件更快达到最大的文件尺寸;

2)索引在提高查询速度的同时,会降低更新表的速度。

 

4. 索引的分类

普通索引INDEX或KEY, 唯一性索引(UNIQUE),主键索引(PRIMARY KEY)

CREATE [UNIQUE] INDEX index_name ON tbl_name(index_col_name,…)

【释义:UNIQUE唯一索引,index_name指定索引名, tb1_name指定要建立索引的表名,index_col_name索引的描述 =col_name [(length)][ASC | DESC]   desc倒叙,asc升序 】

CREATE INDEX index_customers ON mysql_test.customers(cust_name(3) ASC);

【释义:在数据库mysql_test的表customers上,根据客户姓名列的前三个字符创建一个升序索引index_customers  】

 

5. 索引的语法

使用create table语句创建

1)语法项[CONSTRAINT[symbol]]PRIMARY KEY(index_col_name,…),

      用于表示在创建新表的同时创建该表的主键;

2)语法项{INDEX|KEY}[index_name](index_col_name,…),

      用于表示在创建新表的同时创建该表的索引;

3)语法项[CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name]

      (index_col_name,…),

      用于表示在创建新表的同时创建该表的唯一性索引;

4)语法项[CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name,…),

      用于表示在创建新表的同时创建该表的外键;

使用alter table语句创建

1)语法项ADD {INDEX|KEY} [index_name](index_col_name,…),

      用于表示在修改表的同时为该表添加索引;

2)语法项ADD [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,…),

      用于表示在创建新表的同时为该表添加主键;

3)语法项ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name]

      (index_col_name,…),

      用于表示在修改表的同时为该表添加唯一性索引;

4)语法项ADD [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name,…),

      用于表示在创建新表的同时为该表添加外键;

 

 

6. 在已有数据库mysql_test上新建一个包含产品卖家id号、姓名、地址、

联系方式、售卖产品类型、当月销量等内容的产品卖家信息表seller,

要求在创建表的同时,为该表添加由卖家id号和售卖产品类型组成的

联合主键,并在当月销量上创建索引。

CREATE TABLE seller

(

seller_id int NOT NULL,

seller_name char(50)NOT NULL,

seller_address char(50)NULL,

seller_contact char(50)NULL,

product_type int(5)NULL,

sales int NULL,

PRIMARY KEY(seller_id,product_type),

INDEX index_seller(sales)

);

 

ALTER TABLE mysql_test.seller ADD INDEX index_seller_name(seller_name);  (使用ALTER TABLE语句在数据库mysql_test中表seller的姓名上添加一列非唯一的索引,取名为index_seller_name  )

 

7. 查看索引

    SHOW {INDEX | INDEXES | KEYS}  {FROM | IN} tbl_name   [{FROM | IN} db_name]

[WHERE expr]

    show index from customers;

8. 删除索引

方法一:drop index删除

DROP INDEX index_customers ON mysql_test.customers;(删除customers表中的索引index_customers)

1)选用DROP PRIMARY KEY子句用于删除表中的主键,由于一个表中只有

一个主键,其也是一个索引;

2)选用DROP INDEX子句用于删除各种类型的索引;

3)选用DROP FOREIGN KEY子句用于删除外键。(foreign)

 

方法二:alter table删除

ALTER TABLE mysql_test.customers DROP PRIMARY KEY,DROP INDEX index_customers;

ALTER TABLE mysql_test.seller ADD INDEX index_seller_name(seller_name);  (使用ALTER TABLE语句在数据库mysql_test中表seller的姓名列上添加一个非唯一索引,取名为index_seller_name。)

 

 

 

精讲9:增删改查

 

1. 插入

insert….values:插入单行或多行元组

格式:INSERT [INTO] tbl_name [(col_name,…)]  {VALUES | VALUE}({expr | DEFAULT},…),(…),…  (tb1表名, col_name列名, 通过关键字values或value引导的子句,expr表示一个常量、变量或一个表达式,也可以是空值null)

 

测试①:使用INSERT…VALUES语句向数据库mysql_test的表customers中插入这样一行完整数据:(901,张三 ,F,北京市,朝阳区)

INSERT INTO mysql_test.customers VALUES (901,'张三','F','北京市','朝阳区');

 

测试②:

使用INSERT…VALUES语句向数据库mysql_test的表customers中插入这样一行数据,要求该数据目前只用明确给出cust_name列和cust_address列的信息,即分别为‘李四’‘武汉’,而cust_id由系统自动生成,cust_sex列选用表中默认值,另外cust_contact列的值暂不确定,可不用指定

INSERT INTO mysql_test.customers VALUES (0,'李四',DEFAULT,'武汉市',NULL);

 

 

insert….set插入部分列值数据

格式:INSERT [INTO] tbl_name  SET col_name={expr | DEFAULT},…

 

测试①:

使用INSERT…SET语句向数据库mysql_test的表customers中插入数据:名为李四 ,地址为武汉,性别默认

INSERT INTO mysql_test.customers SET cust_name='李四',cust_address='武汉',cust_sex=DEFAULT;

 

isnert….select 插入查询语句

格式: INSERT [INTO] tbl_name [(col_name,…)] SELECT…

 

 

2. 删除

delete 删除一行或多行

格式    DELETE FROM tbl_name [WHERE where_condition] [ORDER BY …] [LIMIT row_count]

 

测试①:

使用DELETE语句删除数据库mysql_test的表customers中客户名为“李四”的客户信息。

DELETE FROM mysql_test.customers WHERE cust_name='李四';

 

 

3. 修改

update修改表数据

格式:UPDATE tbl_name  SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]…  [WHERE where_condition]  [ORDER BY …] [LIMIT row_count]

 

测试①:

使用UPDATE语句将数据库mysql_test的表customers中姓名为“张三”的客户的地址更新为“武汉”。

UPDATE mysql_test.customers  SET cust_address='武汉市' WHERE cust_name='张三';

 

 

4. 查询

格式:select [ALL|DISTINCT|DISTINCTROW]  select_expr[select_expr…]

from table  where where_condition  (from 表  where 条件)

group by col_name   (对检索到的记录进行分组)

[ASC | DESC]…[WITH ROLLUP]

[HAVING where_condition]   (指定组的选择条件)

[order by col_name|expr|position]  (对查询的结果进行排序)

[ASC | DESC]…

[limit offset|row_count|row_count OFFSET offset]  (限制行数) 

 

测试①:

选择指定的列查询数据库mysql_sest的表customers中各个客户的姓名、性别和地址信息

SELECT cust_name,cust_sex,cust_address FROM mysql_test.customers;

 

测试②:

选择全部的信息查询数据库mysql_sest的表customers中各个客户的所有信息

select * from mysql_test.customers;

 

 

5. 定义并使用列的别名

格式: column_name [AS] column_alias

 

测试①:使用AS定义cust_address别名为“地址”

SELECT cust_name,cust_address AS 地址,cust_contact FROM mysql_test.customers;

 

6. 替换查询结果中的数据

CASE WHEN条件1  THEN表达式1 WHEN 条件2  THEN表达式2

ELSE 表达式

END[AS]column_alias

 

 

测试①:

查询数据库mysql_test的表customers中客户的cust_name列和cust_sex列,要求判断结果集中cust_sex列的值,如果该列的值为M,则显示输出“男”,否则为“女”,同时在结果集的显示中将cust_sex列用别名“性别”标注。

SELECT cust_name,

CASE

WHEN cust_sex='M' THEN '男'

ELSE '女'

END AS 性别

FROM mysql_test.customers;

 

7. 计算列值

测试

查询数据库mysql_test的表customers中每个客户的cust_name列、cust_sex列,以及对cust_id列加上数字100后的值

SELECT cust_name,cust_sex,cust_id+100  FROM mysql_test.customers; 

 

 

8. from子句与多表连接查询

交叉连接,又叫做笛卡尔积

SELECT * FROM tbl1 CROSS JOIN tbl2;

SELECT * FROM tbl1,tbl2;

测试①:

假设数据库中有两张表,分别是tbl1和tbl2,现要求输出这两张表执行交叉连接后的所有数据集,写出相应的SQL语句

SELECT * FROM tbl1 CROSS JOIN tbl2;

 

 

内连接

内连接又分为

等值连接:使用运算符=

非等值连接:使用除=之外的其他比较运算符

自连接:将一个表与它自身连接

select some_columns from table INNER JOIN table2 ON some conditions;

some conditions=[<table1>.]<列名或列别名><比较运算符>[<table2>.]<列名或列别名>】

测试①:

根据学生基本信息登记表tb_student和学生成绩表tb_score,使用内连接查询每个学生及其选课成绩的详细信息。

SELECT * FROM tb_student INNER JOIN tb_score ON tb_student.studentNo=tb_score.studentNo;

 

外连接

*左外连接:在FROM子句中使用关键字LEFT OUTER JOIN或LEFT JOIN

*右外连接:在FROM子句中使用关键字RIGHT OUTER JOIN或RIGHT JOIN

 

测试

根据学生基本信息登记表tb_student和学生成绩表tb_score,使用左外连接查询每个学生及其选课成绩的详细信息。

SELECT * FROM tb_studentLEFT JOINtb_score ON tb_student.studentNo=tb_score.studentNo;

 

简述左外连接和右外连接的区别。

左外连接:也称左连接。以左表为基表,在FROM子句中使用关键字“LEFT OUTER JOIN”或关键字“LEFT JOIN”来连接两张表。
右外连接:也称右连接。以右表为基表,在FROM子句中使用关键字“RIGHT OUTER JOIN”或关键字“RIGHT JOIN”来连接两张表。

Next Post

Previous Post

© 2022 ~阿弥陀佛~

ICP备案号: 京ICP备19038630号

版权所有@ 王力翔

耗时 0.414 秒 | 查询 39 次 | 内存 22.99 MB