数据库sql语句及使用示例

利用 Transact-SQL 语言创建满足以下要求的数据库：

1） 数据库存在于连接 MySQL 中；
2） 数据库名称为 mydb；
3） 字符集选择 utf8 -- UTF-8 Unicode；
4） 排序规则选择 utf8_general_ci；

具体步骤如下：
点击“新建查询”，在查询编辑器输入以下代码，点击“运行”，完成后， 左侧列表
中选中连接“数 MySQL”，点击“刷新”按钮，列表即可见到“testdb”。

CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE
utf8_general_ci;

collate在sql中是用来定义排序规则的。排序规则其实就是当比较两个字符串时，根据某种规则来确定哪个比较大，是否相等。各个数据库支持不同的排序规则。

utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。

utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感。

utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感。

利用 Transact-SQL 语言查看数据库及表的信息

1.查看连接中的所有数据库
步骤：点击“新建查询”，输入

SHOW DATABASES 

2.查看数据库 mysql 中所有的表
步骤：点击“新建查询”，输入

USE mysql;
SHOW TABLES;

3.查看数据库“mysql”中表“help_keyword”的结构。
步骤：点击“新建查询”，输入

USE mysql;
DESC help_keyword;
或者DESCRIBE help_keyword;
或者SHOW COLUMNS FROM help_keyword;

4.查看数据库表中的内容

USE mysql;
SELECT * FROM help_keyword;

利用 Transact-SQL 语言修改数据库 mydb 的字符集

步骤：点击“新建查询”，输入以下代码，点击运行

ALTER DATABASE mydb DEFAULT CHARACTER SET = latin1;

利用 Transact-SQL 语言删除数据库 testdb

DROP DATABASE testdb

在数据库 xsgl 中，利用 Transact-SQL 语言创建表格：

1）表格名为 kc（课程情况表）；
2）表格中各个属性用sql描述

USE xsgl;
create table kc
(
kch char(4) not null, (不能取空值)
kcm char(20) null,
xss int null,
xf int null,
primary key(kch) （设置为主键）
)engine=innodb default charset=utf8 auto_increment=1;（使用innodb引擎，数据库默认编码为utf-8，自增键的起始序号为1）

利用 Transact-SQL 语言修改 kc 表

1.增加“成绩”一列 cj， int 型，允许为空值，默认为 0。

ALTER TABLE kc ADD COLUMN cj INT DEFAULT 0;

2.修改cj列的类型为char。

ALTER TABLE kc CHANGE COLUMN cj cj CHAR(4);

3.修改cj列的列名为mark。

ALTER TABLE kc CHANGE cj mark CHAR(4) DEFAULT 0;

4.删除mark列。

ALTER TABLE kc DROP COLUMN mark;

5.删除表 kc。

DROP TABLE kc

6.利用 Transact-SQL 将表 xs 重命名为 Students。

RENAME TABLE xsgl.xs TO xsgl.Students;

7.使用alert table增加和删除列和改变数据类型

ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name ;
DROP COLUMN column_name;
ALTER TABLE table_name
ALTER COLUMN column_name datatype

简单查询语句

1.查询 world 数据库的 country 表中的国名（Name）、洲名（Continent）和地区（Region）。

USE world
SELECT NAME,Continent,Region FROM country;

2.从 world 数据库的 city 表中搜索返回前 20 条的数据。

USE world;
SELECT * FROM city LIMIT 0,20;

其中，“LIMIT M,N”中的 M 表示从第 M 条（不包括 M）开始

3.使用 WHERE 子句从 world 数据库的 country 表中检索出所有领土面积超过一百万
平方公里的国家名称（Name）、洲名（Continent） 以及领土面积（SurfaceArea）

USE world;
SELECT NAME,Continent,SurfaceArea
FROM country
WHERE SurfaceArea > 1000000;

4.查询在 world 数据库的 country 表中 Name 以字母 C 开头的国家的洲名 Continent,地
区 Region

USE world;
SELECT Name,Continent,Region
FROM country
WHERE NAME like 'C%';

5.查询 world 数据库 Country 表中所有国家的 Name 和 Condinent，并按生日 SurfaceArea
从小到大进行排列。

USE world;
SELECT Name,Continent,SurfaceArea
FROM country
ORDER BY SurfaceArea;

如果是降序排列的话

ORDER BY Population DESC;

6.country表中查询独立年份为空的国家的名称、国家年份。

USE world;
SELECT NAME,IndepYear
FROM country
WHERE IndepYear is null;

使用 Transact-SQL 语言输入表数据、修改表数据和删除表数据

1.在表 newlanguage 中插入中国的客家话，其名称为 CountryCode 为 CHN， language
为 Kejia， isOfficial 为 F， percentage 为 0.3。

USE world;
INSERT INTO newlanguage
VALUES ('CHN','Kejia','F',0.3);

2.试将表 countrylanguage 中的所有记录插入到表 newlanguage 中去，

USE world;
INSERT INTO newlanguage
SELECT *
FROM countrylanguage;

3.将表 newlanguage 中 Language 为“Kejia” 的语言的 Percentage 改为 0.4

USE world;
UPDATE newlanguage
SET Percentage = 0.4
WHERE Language = 'Kejia';

4.将表 newlanguage 中语言的 Percentage 均减去 0.1

USE world;
UPDATE newlanguage
SET Percentage = Percentage - 0.1;

5.删除表 newlanguage 中澳大利亚（CountrCode 为“AUS”） 的英语记录。

USE world;
DELETE FROM newlanguage
WHERE CountryCode='AUS'
AND Language='English';

6.清空表 newlanguage 中的所有数据。

USE world;
TRUNCATE TABLE newlanguage;

视图管理

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。视图是存储在数据库中的查询的SQL 语句，它主要出于两种原因：安全原因， 视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。这个视图就像一个“窗口”，从中只能看到你想看的数据列。这意味着你可以在这个视图上使用SELECT *，而你看到的将是你在视图定义里给出的那些数据列

1.建立亚洲国家的视图 asia_country，并要求进行修改和插入操作时仍需保证该视图
只有亚洲国家，视图的属性名为 Code， Name， Continent， Population， GNP,
GovernmentForm.

USE world;
CREATE VIEW asia_country
AS
SELECT Code,Name,Continent,Population,GNP,GovernmentForm
FROM country
WHERE Continent='Asia';

2.建立国家简称（country.Code）、城市名(city.Name)、语言名(countrylanguage.Language)
的视图 cc_language。本视图由三个基本表的连接操作导出

USE world;
CREATE VIEW cc_language
AS
SELECT country.Code,city.Name,countrylanguage.Language
FROM country,city,countrylanguage
WHERE country.Code = city.CountryCode
AND country.Code = countrylanguage.CountryCode;

数据类型

1.日期和时间数据类型

数据类型	含义
date	3字节，日期，格式：2014-09-18
time	3字节，时间，格式，09:20:20
datetime	8字节，日期格式，格式：2014-09-18 08:42:40
timestamp	4字节，自动存储记录修改时间
year	1字节，年份

2.整型

mysql数据类型	含义（有符号）
tinyint	1字节，范围（-128~127)
smallint	2字节，范围（-32768~32767）
int	4字节（-2147483648~-2147483648)

3.浮点型

mysql数据类型	含义
float(m,d)	4字节，单精度浮点，m总个数，d小数位
double(m,d)	8字节，双精度浮点，m总个数，d小数位
decimal(m,d)	decimal是存储为字符串的浮点数

4.字符串类型

mysql数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	可变长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符

char（n）和 varchar（n）中括号中 n 代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入 m 个中文，但是实际会占用 m*3 个字节

同时 char 和 varchar 最大的区别就在于 char 不管实际 value 都会占用 n 个字符的空间，而
varchar 只会占用实际字符应该占用的空间+1，并且实际空间+1<=n

超过 char 和 varchar 的 n 设置后，字符串会被截断

char 在存储的时候会截断尾部的空格， varchar 和 text 不会。

varchar 会使用 1-3 个字节来存储长度， text 不会。

5.其他类型

• enum(“member1″ , “member2″ , … “member65535″ )： enum 数据类型就是定义了
  一种枚举，最多包含 65535 个不同的成员。当定义了一个 enum 的列时，该列的值限制为列
  定义中声明的值。如果列声明包含 NULL 属性，则 NULL 将被认为是一个有效值，并且是
  默认值。如果声明了 NOT NULL，则列表的第一个成员是默认值
• set(“member” , “member2″ , … “member64″ )： set 数据类型为指定一组预定义值中
  的零个或多个值提供了一种方法，这组值最多包括 64 个成员。值的选择限制为列定义中声
  明的值

数据类型属性

1.auto_increment

auto_increment 能为新插入的行赋一个唯一的整数标识符。为列赋此属性将为每个新插
入的行赋值为上一次插入的 ID+1。
MySQL 要求将 auto_increment 属性用于作为主键的列。此外，每个表只允许有一个
auto_increment 列。例如：

id smallint not null auto_increment primary key 

2.binary

binary 属性只用于 char 和 varchar 值。当为列指定了该属性时，将以区分大小写的方式
排序。与之相反，忽略 binary 属性时，将使用不区分大小写的方式排序。例如：

hostname char(25) binary not null

3.default

default 属性确保在没有任何值可用的情况下，赋予某个常量值，这个值必须是常量，因
为 MySQL 不允许插入函数或表达式值。此外，此属性无法用于 BLOB 或 TEXT 列。如果已
经为此列指定了 NULL 属性，没有指定默认值时默认值将为 NULL，否则默认值将依赖于
字段的数据类型。例如：

subscribed enum('0', '1') not null default '0'

4.index(索引)

如果所有其他因素都相同，要加速数据库查询，使用索引通常是最重要的一个步骤。索
引一个列会为该列创建一个有序的键数组，每个键指向其相应的表行。以后针对输入条件可
以搜索这个有序的键数组，与搜索整个未索引的表相比，这将在性能方面得到极大的提升

create table employees
(
id varchar(9) not null,
firstname varchar(15) not null,
lastname varchar(25) not null,
email varchar(45) not null,
phone varchar(10) not null,
index lastname(lastname),
primary key(id)
);

我们也可以利用 MySQL 的 create index 命令在创建表之后增加索引：

create index lastname on employees (lastname(7)) 

5.not null

如果将一个列定义为 not null，将不允许向该列插入 null 值。建议在重要情况下始终使
用 not null 属性，因为它提供了一个基本验证，确保已经向查询传递了所有必要的值

6.null

为列指定 null 属性时，该列可以保持为空，而不论行中其它列是否已经被填充。记住，
null 精确的说法是“无”，而不是空字符串或 0。

7.primary key

primary key 属性用于确保指定行的唯一性。指定为主键的列中，值不能重复，也不能
为空。为指定为主键的列赋予 auto_increment 属性是很常见的，因为此列不必与行数据有任
何关系，而只是作为一个唯一标识符。主键又分为以下两种：
①单字段主键: 如果输入到数据库中的每行都已经有不可修改的唯一标识符，一般会使
用单字段主键。注意，此主键一旦设置就不能再修改。
②多字段主键: 如果记录中任何一个字段都不可能保证唯一性，就可以使用多字段主
键。这时，多个字段联合起来确保唯一性。如果出现这种情况，指定一个 auto_increment 整
数作为主键是更好的办法

8.unique

被赋予 unique 属性的列将确保所有值都有不同的值，只是 null 值可以重复。一般会指
定一个列为 unique，以确保该列的所有值都不同。例如：

email varchar(45) unique 

9.zerofill

zerofill 属性可用于任何数值类型，用 0 填充所有剩余字段空间。例如，无符号 int 的默
认宽度是 10；因此，当“零填充”的 int 值为 4 时，将表示它为 0000000004。例如：

orderid int unsigned zerofill not null

高级查询语句

1.查询所有国家名称及相应的城市、语言。 (涉及三个表)

USE world;
SELECT country.Name,city.Name,countrylanguage.Language
FROM country,city,countrylanguage
WHERE country.Code= city.CountryCode
AND city.CountryCode = countrylanguage.CountryCode;

2.查询国家政体为共和国且国家人口在一千万以上的城市的名称和城市人口、所属国
家

USE world;
SELECT city.Name,city.Population,country.Name
FROM country,city
WHERE city.CountryCode = country.Code
AND country.Population > 10000000;

3.统计 country 表中共和国政体的国家数。

USE world;
SELECT COUNT(Code)//计算数量，计算code数量
FROM country
WHERE GovernmentForm = 'Republic';

4.统计 country 表中共和国政体国家的平均人口。

USE world;
SELECT AVG(Population)//计算平均
FROM country
WHERE GovernmentForm = 'Republic';

5.分组统计 country 表中各政体的国家个数。

USE world;
SELECT GovernmentForm,count(*)//计算
FROM country
GROUP BY GovernmentForm;//分组

6.查询有超过两条城市记录的国家的名称。

USE world;
SELECT country.Name
FROM city,country
WHERE city.CountryCode = country.Code
GROUP BY city.CountryCode
HAVING COUNT(city.Name) > 2; //表示拥有的数量

7、查询非共和政体的国家的名称和政体

USE world;
SELECT Name,GovernmentForm
FROM country
WHERE GovernmentForm <> 'Republic'; //怎样取非

8.查询平均每国人口数高于非洲的大洲名称及该平均数，以平均数的降序排列

USE world;
SELECT Continent,AVG(Population)
FROM country
GROUP BY Continent
HAVING AVG(Population) >
(SELECT AVG(Population) FROM country WHERE Continent = 'Africa')
ORDER BY AVG(Population) DESC;//嵌套循环

9.查询 city 表中多于 3 个城市记录且缩写以“A”开头的国家的名称和城市平均人口，
以平均人口的升序排列

USE world;
SELECT city.CountryCode,country.Name,AVG(city.Population)
FROM city,country
WHERE city.CountryCode LIKE 'A%'
AND city.CountryCode = country.Code
GROUP BY city.CountryCode
HAVING COUNT(city.Name) > 3
ORDER BY AVG(city.Population);

10.查找零件表中最小

USE test;
select pno
from p
having min(weight);

11.查找同时为J1和J2提供零件的供应商代码

USE test;
select sno
from spj
where jno='J1' and sno in (select sno from spj where jno='j2')

使用sql语言创建复杂数据库

1.学生基本表和课程基本表

要求：

1、 在 xs 表中定义 xh 为主键。
2、在 kc 表中定义 xh 和 kch 联合构成主键。
3、定义 kc 表中的 kcm 列满足唯一性约束。
4、定义 kc 表中的 fs 列默认值为 0。
5、定义 xs 表中的 xb 列的 CHECK 约束“男”或“女”。
6、在 xs 表与 kc 表之间定义外键 xh。

xs表：

use xsgl;
create table xs
(
xh int not null,
xm char(8) null,
xb char(2) null check(xb in('男','女')),//check约束，范围规定，枚举值规定，特定匹配
nl tinyint null,
zy char(16) null,
jtzz char(50) null,
primary key(xh)
)engine=innodb default charset=utf8 auto_increment=1;

kc表

create table kc
(
xh int not null,
kch int not null,
kcm char(20) null unique,
xss int null,
xf int null,
fs int null default 0,
primary key(xh,kch),
foreign key(xh) references xs(xh) on delete cascade on update
cascade//外键,并且xs的xh发生改变时，跟随改变和删除
)engine=innodb default charset=utf8 auto_increment=1;

2.创建一个学生—课程数据库，包括学生关系Student、课程关系Course和选修
关系SC

1)建立学生-课程数据库xskc，创建Student表，并将Student表中的Sno属性定义为主键

2)创建Course表和SC表，并将SC表中的Sno,Cno联合构成主键

CREATE DATABASE xskc;
USE xskc;
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
)

USE xskc;
CREATE TABLE Course
(Cno CHAR(9) PRIMARY KEY,
Cname CHAR(20),
Cpno CHAR(4),
Ccredit SMALLINT
) ;
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(9) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno)
)

3）定义sc表中参照完整性

USE xskc;
ALTER TABLE SC ADD FOREIGN KEY (Sno) REFERENCES Student(Sno);
ALTER TABLE SC ADD FOREIGN KEY (Cno) REFERENCES Course(Cno);

我们常常希望保证在一个关系中给定属性集上的取值也在另一个关系的特定属性集的取值中出现。这种情况称为参照完整性(referential integrity)

外键级联的三种方式

1）：on delete no action 数据库默认的方式，禁止删除，就是不级联删除

2）：on delete cascade 级联删除

3）：on delete set null 不级联删除，将外键级联的字段赋个空值。

数据库自定义函数

需要mysql实现sql逻辑处理，参数是IN参数，含有returns语句用来指定函数返回类型

创建：CREATE FUNCTION函数名称(参数列表)

RETURNS 返回值类型

函数体

修改： ALTER FUNCTION 函数名称 [characteristic ...]

删除：DROP FUNCTION [IF EXISTS] 函数名称

调用：SELECT 函数名称(参数列表)

数据库存储过程

一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，比一个个执行sql语句效率高，用户通过指定存储过程的名字并给出参数来执行它。参数可以为IN, OUT, 或INOUT

创建：CREATE PROCEDURE 过程名 (参数列表) [characteristic ...]

函数体

修改： ALTER PROCEDURE 过程名 [characteristic ...]

删除：DROP PROCEDURE [IF EXISTS] 过程名

调用：CALL 过程名(参数列表)

存储过程与函数区别

1.存储过程，功能强大，可以执行包括修改表等一系列数据库操作，也可以创建为数据库启动时自动运行的存储过程。

自定义函数，用户定义函数不能用于执行一组修改全局数据库状态的操作。
2.对于sql server: 存储过程，可以使用非确定函数。自定义函数，不允许在用户定义函数主体中内置非确定函数。

3.存储过程，可返回记录集。

自定义函数，可以返回表变量。
4.存储过程，其返回值不能被直接引用。

自定义函数，其返回值可以被直接引用。
5.存储过程，用 CALL 语句执行。

自定义函数，在查询语句中调用。

触发器

与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

创建：

CREATE TRIGGER <触发器名称> --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
{ BEFORE | AFTER } --触发器有执行的时间设置：可以设置为事件发生前或后。
{ INSERT | UPDATE | DELETE } --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。
ON <表名称> --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
FOR EACH ROW --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。
<触发器SQL语句> --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。
--创建触发器（CREATE TRIGGER），需要SUPER权限。

eg:

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END

可以用来进行另一种级联式修改

删除：DROP TRIGGER 方案名称.触发器名称

事件

可以将数据库按自定义的时间周期触发某种操作，可以理解为时间触发器，类似于linux系统下面的任务调度器crontab，或者类似与window下面的计划任务。值得一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

创建

CREATE

    [DEFINER = { user | CURRENT_USER }]   --定义事件执行的时候检查权限的用户。

    EVENT

    [IF NOT EXISTS]

    event_name

    ON SCHEDULE schedule              --定义执行的时间和时间间隔。

    [ON COMPLETION [NOT] PRESERVE]     --定义事件是一次执行还是永久执行，默认为一次执行，即NOT PRESERVE。

    [ENABLE | DISABLE | DISABLE ON SLAVE]   --定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLE ON SLAVE

    [COMMENT 'comment']                 -- 注释

    DO event_body;

 

schedule:

    AT timestamp [+ INTERVAL interval] ...

     | EVERY interval

    [STARTS timestamp [+ INTERVAL interval]...]

    [ENDS timestamp [+ INTERVAL interval] ...]

interval:

  quantity {YEAR | QUARTER | MONTH | DAY | HOUR| MINUTE |

              WEEK | SECOND | YEAR_MONTH |DAY_HOUR |

DAY_MINUTE |DAY_SECOND| HOUR_MINUTE |

HOUR_SECOND| MINUTE_SECOND}

删除：

DROP EVENT [IF EXISTS] event_name

修改：

ALTER

    [DEFINER = { user | CURRENT_USER }]

    EVENT event_name

    [ON SCHEDULE schedule]

    [ON COMPLETION [NOT] PRESERVE]

    [RENAME TO new_event_name]

    [ENABLE | DISABLE | DISABLE ON SLAVE]

    [COMMENT 'comment']

    [DO event_body]

查看是否开启了事件

SHOW VARIABLES LIKE 'event_scheduler';

SELECT @@event_scheduler;

SHOW PROCESSLIST;

如果看到event_scheduler为on或者PROCESSLIST中显示有event_scheduler的信息说明就已经开启了事件。如果显示为off或者在PROCESSLIST中查看不到event_scheduler的信息，那么就说明事件没有开启，我们需要开启它。