MySQL5.5从零开始学例题和综合案例
程序员文章站
2022-12-04 23:28:08
第一章例题CREATE TABLE students(student_id INT UNSIGNED,name VARCHAR(30),sex CHAR(1),birth DATE,PRIMARY KEY (student_id));INSERT INTO students (student_id, name, sex, birth) VALUES (4104......
第一章
例题
CREATE TABLE students
(
student_id INT UNSIGNED,
name VARCHAR(30),
sex CHAR(1),
birth DATE,
PRIMARY KEY (student_id)
);
INSERT INTO students (student_id, name, sex, birth) VALUES (41048101, 'Lucy Green', '1', '1990-02-14');
SELECT name FROM students WHERE student_id = 41048101;第三章
例题
查看当前所有存在的数据库
SHOW databases;
【例3.1】创建测试数据库test_db，
CREATE DATABASE test_db;
【例3.2】查看创建好的数据库test_db的定义，
SHOW CREATE DATABASE test_db\G;
【例3.3】删除测试数据库test_db，
DROP DATABASE test_db;
查看存储引擎
SHOW ENGINES \G;综合案例
步骤1：登陆数据库：
mysql -h localhost -u root -p
步骤2：创建数据库zoo：
CREATE DATABASE zoo;
SHOW DATABASES;
步骤3：选择当前数据库为zoo：
USE zoo;
查看数据库zoo的信息：
SHOW CREATE DATABASE zoo \G;
步骤4：删除数据库zoo：
DROP DATABASE zoo;第四章
例题
【例4.1】创建员工表tb_emp1
选择创建表的数据库：
USE test_db;
创建tb_emp1表：
CREATE TABLE tb_emp1
(
id int(11),
name varchar(25),
deptId int(11),
salary float
);
【例4.2】定义数据表tb_emp 2，其主键为id：
CREATE TABLE tb_emp2
(
id int(11) PRIMARY KEY,
name varchar(25),
deptId int(11),
salary float
);
【例4.3】定义数据表tb_emp 3，其主键为id：
CREATE TABLE tb_emp3
(
id int(11),
name varchar(25),
deptId int(11),
salary float,
PRIMARY KEY(id)
);
【例4.4】定义数据表tb_emp4，创建多字段联合主键SQL语句为：
CREATE TABLE tb_emp4
(
name varchar(25),
deptId int(11),
salary float,
PRIMARY KEY(name,deptId)
);
【例4.5】定义数据表tb_emp5，并在tb_emp5表上创建外键约束
创建一个部门表tb_dept1：
CREATE TABLE tb_dept1
(
id int(11) PRIMARY KEY,
name varchar(22) NOT NULL,
location varchar(50)
);
定义数据表tb_emp5，让它的键deptId作为外键关联到tb_dept1的主键id：
CREATE TABLE tb_emp5
(
id int(11) PRIMARY KEY,
name varchar(25),
deptId int(11),
salary float,
CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
【例4.6】定义数据表tb_emp6，指定员工的名称不能为空，SQL语句为：
CREATE TABLE tb_emp6
(
id int(11) PRIMARY KEY,
name varchar(25) NOT NULL,
deptId int(11),
salary float,
CONSTRAINT fk_emp_dept2 FOREIGN KEY (deptId) REFERENCES tb_dept(id)
);
【例4.7】定义数据表tb_dept2，指定部门的名称唯一，SQL语句为：
CREATE TABLE tb_dept2
(
id int(11) PRIMARY KEY,
name varchar(22) UNIQUE,
location varchar(50)
);
【例4.8】定义数据表tb_dept3，指定部门的名称唯一，SQL语句为：
CREATE TABLE tb_dept3
(
id int(11) PRIMARY KEY,
name varchar(22),
location varchar(50),
CONSTRAINT STH UNIQUE(name)
);
【例4.9】定义数据表tb_emp7，指定员工的部门编号默认为1111，SQL语句为：
CREATE TABLE tb_emp7
(
id int(11) PRIMARY KEY,
name varchar(25) NOT NULL,
deptId int(11) DEFAULT 1111,
salary float,
CONSTRAINT fk_emp_dept3 FOREIGN KEY (deptId) REFERENCES tb_dept(id)
);
【例4.10】定义数据表tb_emp8，指定员工的编号自动递增，SQL语句为：
CREATE TABLE tb_emp8
(
id int(11) PRIMARY KEY AUTO_INCREMENT,
name varchar(25) NOT NULL,
deptId int(11),
salary float,
CONSTRAINT fk_emp_dept5 FOREIGN KEY (deptId) REFERENCES tb_dept(id)
);
INSERT INTO tb_emp8 (name,salary) VALUES('Lucy',1000), ('Lura',1200),('Kevin',1500);
select * from tb_emp8;
【例4.11】分别使用DESCRIBE和DESC查看表tb_dept和表tb_emp1的表结构，操作过程如下：
DESCRIBE tb_dept;
DESC tb_emp1;
【例4.12】使用SHOW CREATE TABLE查看表tb_emp1的详细信息，SQL语句及相应的执行结果如下示：
SHOW CREATE TABLE tb_emp1;
SHOW CREATE TABLE tb_emp1\G;
【例4.13】将数据表tb_dept3改名为tb_deptment3
ALTER TABLE tb_dept3 RENAME tb_deptment3;
【例4.14】将数据表tb_dept1中name字段的数据类型由VARCHAR(22)的修改成VARCHAR(30)
ALTER TABLE tb_dept MODIFY name VARCHAR(30);
【例4.15】将数据表tb_dept1中的location字段名称改为loc，数据类型保持不变。
ALTER TABLE tb_dept1 CHANGE location loc varchar(50);
【例4.16】将数据表tb_dept1中的loc字段名称改为location，同时将数据类型保持变为varchar(60)。
ALTER TABLE tb_dept1 CHANGE loc location varchar(60);
【例4.17】在数据表tb_dept中添加一个没有完整性约束的int类型的字段managerId（部门经理编号），输入SQL语句如下：
ALTER TABLE tb_dept ADD managerId int(10);
【例4.18】在数据表tb_dept1中添加一个不能为空的varchar(12)类型的字段column1。输入如下SQL语句，并执行：
ALTER TABLE tb_dept1 ADD column1 varchar(12) not null;
【例4.19】在数据表tb_dept中添加一个int类型的字段column2。输入如下SQL语句，并执行：
ALTER TABLE tb_dept ADD column2 int(11) FIRST;
【例4.20】在数据表tb_dept1中name列后添加一个int类型的字段column3。输入如下SQL语句，并执行：
ALTER TABLE tb_dept1 ADD column3 int(11) AFTER name;
【例4.21】删除数据表tb_dept1表中的column2字段
ALTER TABLE tb_dept1 DROP column2;
【例4.22】将数据表tb_dept1中的column1字段修改为表的第一个字段
ALTER TABLE tb_dept1 MODIFY column1 varchar(12) FIRST;
【例4.23】将数据表tb_dept1中的column1字段插入到location字段后面
ALTER TABLE tb_dept1 MODIFY column1 varchar(12) AFTER location;
【例4.24】将数据表tb_deptment3的存储引擎修改为MyISAM
ALTER TABLE tb_deptment3 ENGINE=MyISAM;
SHOW CREATE TABLE tb_deptment3 \G;
【例4.25】删除数据表tb_emp9中的外键约束
CREATE TABLE tb_emp9
(
id int(11) PRIMARY KEY,
name varchar(25),
deptId int(11),
salary float,
CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);
ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;
【例4.26】删除数据表tb_dept2，输入如下SQL语句并执行：
DROP TABLE IF EXISTS tb_dept2;
【例4.25】删除被数据表tb_emp关联的数据表tb_dept2
CREATE TABLE tb_dept2
(
id int(11) PRIMARY KEY,
name varchar(22),
location varchar(50)
);
CREATE TABLE tb_emp
(
id int(11) PRIMARY KEY,
name varchar(25),
deptId int(11),
salary float,
CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept2(id)
);
直接删除父表tb_dept2
DROP TABLE tb_dept2;
解除关联子表tb_emp的外键约束
ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;
综合案例
步骤1：登陆数据库
mysql -h localhost -u root -p
步骤2：创建数据库Company
CREATE DATABASE company;
步骤3：创建表offices
CREATE TABLE offices
(
officeCode int(10) NOT NULL UNIQUE,
city varchar(50) NOT NULL,
address varchar(50) NOT NULL,
country varchar(50) NOT NULL,
postalCode varchar(15) NOT NULL,
PRIMARY KEY (officeCode)
);
步骤4：创建表employees
CREATE TABLE employees
(
employeeNumber int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
lastName varchar(50) NOT NULL,
firstName varchar(50) NOT NULL,
mobile varchar(25) NOT NULL,
officeCode int(10) NOT NULL,
jobTitle varchar(50) NOT NULL,
birth datetime,
note varchar(255),
sex varchar(5),
CONSTRAINT office_fk FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
);
步骤5：将表employees的mobile字段修改到officeCode字段后面
ALTER TABLE employees MODIFY mobile varchar(25) AFTER officeCode;
步骤6：将birth字段改名为employee_birth;
ALTER TABLE employees CHANGE birth employee_birth DATETIME;
步骤7：修改sex字段，数据类型为CHAR(1)，非空约束
ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;
步骤8：删除字段note
ALTER TABLE employees DROP note;
步骤9：增加字段名favoriate_activity，数据类型为VARCHAR(100)
ALTER TABLE employees ADD favoriate_activity VARCHAR(100);
步骤10：删除表offices
1）删除employees表的外键约束，输入如下语句：
ALTER TABLE employees DROP FOREIGN KEY office_fk;
2）删除表offices，输入如下语句：
DROP TABLE offices;
步骤11：修改表employees存储引擎为MyISAM
ALTER TABLE employees ENGINE=MyISAM;
步骤12：将表名修改为employees_info
ALTER TABLE employees RENAME employees_info;
第五章
例题
【例5.1】创建表tmp1，其中字段x，y，z，m，n数据类型依次为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
CREATE TABLE tmp1 ( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );
【例5.2】创建表tmp2，其中字段x，y，z，m，n数据类型依次为FLOAT(5,1)、DOUBLE(5,1)、DECIMAL(5,1),向表中插入数据5.12,5.15，5.123，
CREATE TABLE tmp2 ( x FLOAT(5,1), y DOUBLE(5,1), z DECIMAL(5,1) );
【例5.3】创建数据表tmp3，定义数据类型为YEAR的字段y,向表中插入值2010,‘2010’，‘2166’
创建表tmp3：
CREATE TABLE tmp3( y YEAR );
插入数据：
insert into tmp3 values(2010),('2010'),('2166');
【例5.4】向tmp3表中y字段插入2位字符串表示的YEAR值，分别为‘0’,‘00’，‘10’，‘66’
DELETE FROM tmp3;
INSERT INTO tmp3 values('0'),('00'),('77'),('10');
【例5.5】向tmp3表中y字段插入2位数字表示表示的YEAR值，分别为0,78,11
DELETE FROM tmp3;
INSERT INTO tmp3 values(0),(78),(11);
【例5.6】创建数据表tmp4，定义数据类型为TIME的字段t，向表中插入值‘10:05:05’，‘23:23’，‘2 10:10’，‘3 02’，‘10’
CREATE TABLE tmp4( t TIME );
insert into tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'),('10');
【例5.7】表tmp4中插入值‘101112’，111213,‘0’，107010
DELETE FROM tmp4;
INSERT INTO tmp4 values('101112'),(111213),( '0') ,(107010);
【例5.8】向tmp4表中插入系统当前时间
DELETE FROM tmp4;
INSERT INTO tmp4 values (CURRENT_TIME) ,(NOW());
【例5.9】创建数据表tmp5，定义数据类型为DATE的字段d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期
create table tmp5(d DATE);
insert into tmp5 values('1998-08-08'),('19980808'),('20101010');
【例5.10】向tmp5表中插入“YY-MM-DD“和“YYMMDD”字符串格式日期
DELETE FROM tmp5;
insert into tmp5 values('99-09-09'),( '990909'), ( '000101') ,( '121212');
【例5.11】向tmp5表中插入YY-MM-DD和YYMMDD数字格式日期
DELETE FROM tmp5;
insert into tmp5 values(19990909),( 990909), ( 000101) ,( 121212);
【例5.12】向tmp5表中插入系统当前日期
DELETE FROM tmp5;
insert into tmp5 values( CURRENT_DATE() ),( NOW() );
【例5.13】创建数据表tmp6，定义数据类型为DATETIME的字段dt,向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”字符串格式日期和时 间值
CREATE TABLE tmp6( dt DATETIME );
INSERT INTO tmp6 values('1998-08-08 08:08:08'),('19980808080808'),('20101010101010');
【例5.14】向tmp6表中插入“YY-MM-DD HH:MM:SS “和“YYMMDDHHMMSS”字符串格式日期和时间值
DELETE FROM tmp6;
INSERT INTO tmp6 values('99-09-09 09:09:09'),('990909090909'),('101010101010');
【例5.15】向tmp6表中插入YY-MM-DD HH:MM:SS 和YYMMDDHHMMSS数值格式日期和时间值
DELETE FROM tmp6;
INSERT INTO tmp6 values('19990909090909'), ('101010101010');
【例5.16】向tmp6表中插入系统当前日期和时间值
DELETE FROM tmp6;
insert into tmp6 values( NOW() );
【例5.17】创建数据表tmp7，定义数据类型为TIMESTAMP的字段ts，向表中插入值‘19950101010101’，‘950505050505’，‘1996-02-02 02:02:02’，‘97@03@03 03@03@03’，121212121212，NOW()
CREATE TABLE tmp7( ts TIMESTAMP);
INSERT INTO tmp7
values ('19950101010101'),
('950505050505'),
('1996-02-02 02:02:02'),
('97@03@03 03@03@03'),
(121212121212),
( NOW() );
【例5.18】向tmp7表中插入当前日期，查看插入值，更改时区为东10区，再次查看插入值，SQL语句如下：
DELETE FROM tmp7;
mysql> insert into tmp7 values( NOW() );
【例5.20】创建tmp8表，定义字段ch和vch数据类型依次为CHAR（4）、VARCHAR（4）向表中插入数据‘ab ’，SQL语句如下：
CREATE TABLE tmp8( ch CHAR(4), vch VARCHAR(4) );
INSERT INTO tmp8 VALUES('ab ', 'ab ');
【例5.21】创建表tmp9，定义ENUM类型的列enm('first'，'second'，'third')，查看列成员的索引值，SQL语句如下：
create table tmp9( enm ENUM('first','second','third') );
insert into tmp9 values('first'),('second') ,('third') ,('') ,(NULL);
【例5.22】创建表tmp10，定义INT类型的soc字段，ENUM类型的字段level，列表值为（'excellent','good', 'bad'），向表tmp10中插入数据‘good’，1，2，3，‘best’，SQL语句如下：
Create table tmp10 (soc INT, level enum('excellent', 'good','bad') );
insert into tmp10 values(70,'good'), (90,1),(75,2),(50,3),(100,'best');
【例5.23】创建表tmp11，定义SET类型的字段s，取值列表为（'a', 'b', 'c', 'd'），插入数据('a')，('a,b,a')，('c,a,d')，('a,x,b,y')。
create table tmp11 ( s SET('a', 'b', 'c', 'd'));
INSERT INTOtmp11 values('a'),( 'a,b,a'),('c,a,d'),('a,x,b,y');
【例5.24】创建表tmp12，定义BIT(4)类型的字段b，向表中插入数据2、9、15、16。
create table tmp12( b BIT(4) );
insert into tmp12 VALUES(2), (9), (15),(16);
【例5.25】创建表tmp13，定义BINARY(3)类型的字段b和VARBINARY(3)类型的字段vb，并向插入数据‘5’，比较两个字段的存储空间：
首先创建表tmp13，输入SQL语句如下：
CREATE TABLE tmp13( b binary(3), vb varbinary(30) );
INSERT INTO tmp13 VALUES(5,5);
SELECT length(b), length(vb) FROM tmp13;
SELECT b,vb,b = '5', b='5\0\0',vb='5',vb = '5\0\0' FROM tmp13;
【例5.26】创建表tmp14，定义数据类型为INT的字段num，插入值64，对num值进行算术运算：
CREATE table tmp14 ( num INT);
INSERT INTO tmp14 value(64);
SELECT num, num+10, num-10, num+5-3, num+36.5 FROM tmp14;
【例5.27】对tmp14表中的num进行乘法，除法运算
SELECT num, num *2, num /2, num/3, num%3 FROM tmp14;
【例5.28】用0除num
SELECT num, num / 0, num %0 FROM tmp14;;
【例5.29】使用“=”进行相等判断，
SELECT 1=0, '2'=2, 2=2,'0.02'=0, 'b'='b', (1+3) = (2+1),NULL=NULL;
【例5.30】使用‘<=>’进行相等的判断，SQL语句如下：
SELECT 1<=>0, '2'<=>2, 2<=>2,'0.02'<=>0, 'b'<=>'b', (1+3) <=> (2+1),NULL<=>NULL;
【例5.31】使用‘<>’和‘!=’进行不相等的判断，SQL语句如下：
SELECT 'good'<>'god', 1<>2, 4!=4, 5.5!=5, (1+3)!=(2+1),NULL<>NULL;
【例5.32】使用‘<=’进行比较判断，SQL语句如下：
SELECT 'good'<='god', 1<=2, 4<=4, 5.5<=5, (1+3) <= (2+1),NULL<=NULL;
【例5.33】使用‘<=’进行比较判断，SQL语句如下：
SELECT 'good'<'god', 1<2, 4<4, 5.5<5, (1+3) < (2+1),NULL<NULL;
【例5.34】使用‘>=’进行比较判断，SQL语句如下：
SELECT 'good'>='god', 1>=2, 4>=4, 5.5>=5, (1+3) >= (2+1),NULL>=NULL;
【例5.35】使用‘>’进行比较判断，SQL语句如下：
SELECT 'good'>'god', 1>2, 4>4, 5.5>5, (1+3) > (2+1),NULL>NULL;
【例5.36】使用IS NULL，ISNULL和IS NOT NULL判断NULL值和非NULL值，SQL语句如下：
SELECT NULL IS NULL, ISNULL(NULL),ISNULL(10), 10 IS NOT NULL;
【例5.37】使用BETWEEN AND 进行值区间判断，输入SQL语句如下：
SELECT 4 BETWEEN 4 AND 6, 8 BETWEEN 6 AND 8,12 BETWEEN 9 AND 10;
【例5.38】使用LEAST运算符进行大小判断，SQL语句如下：
SELECT least(2,0), least(20.0,3.0,100.5), least('a','c','b'),least(10,NULL);
【例5.39】使用GREATEST运算符进行大小判断，SQL语句如下：
SELECT greatest(2,0), greatest(20.0,3.0,100.5), greatest('a','c','b'),greatest(10,NULL);
【例5.40】使用IN，NOT IN运算符进行判断，SQL语句如下：
SELECT 2 IN (1,3,5,'thks'), 'thks' IN (1,3,5,'thks');
【例5.41】存在NULL值时的IN查询，SQL语句如下：
SELECT NULL IN (1,3,5,'thks'),10 IN (1,3,NULL,'thks');
【例5.42】使用运算符LIKE进行字符串匹配运算，SQL语句如下：
SELECT 'stud' LIKE 'stud', 'stud' LIKE 'stu_','stud' LIKE '%d','stud' LIKE 't_ _ _', 's' LIKE NULL;
【例5.43】使用运算符REGEXP进行字符串匹配运算，SQL语句如下：
SELECT 'ssky' REGEXP '^s', 'ssky' REGEXP 'y$', 'ssky' REGEXP '.sky', 'ssky' REGEXP '[ab]';
【例5.44】分别使用非运算符“NOT”和“！”进行逻辑判断，SQL语句如下：
SELECT NOT 10, NOT (1-1), NOT -5, NOT NULL, NOT 1 + 1;
【例5.45】分别使用与运算符“AND”和“&&”进行逻辑判断，SQL语句如下：
SELECT 1 AND -1,1 AND 0,1 AND NULL, 0 AND NULL;
【例5.46】分别使用或运算符“OR”和“||”进行逻辑判断，SQL语句如下：
SELECT 1 OR -1 OR 0, 1 OR 2,1 OR NULL, 0 OR NULL, NULL OR NULL;
【例5.47】使用异或运算符“XOR”进行逻辑判断，SQL语句如下：
SELECT 1 XOR 1, 0 XOR 0, 1 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1;
【例5．48】使用位或运算符进行运算
SELECT 10 | 15, 9 | 4 | 2;
【例5.49】使用位与运算符进行运算
SELECT 10 & 15, 9 &4& 2;
【例5.50】使用位异或运算符进行运算
SELECT 10 ^ 15, 1 ^0, 1 ^ 1;
【例5.51】使用位左移运算符进行运算
SELECT 1<<2, 4<<2;
【例5.52】使用位右移运算符进行运算
SELECT 1>>1, 16>>2;
【例5.53】使用位取反运算符进行运算
SELECT 5 & ~1;
综合案例
CREATE TABLE tmp15 (note VARCHAR(100), price INT);
INSERT INTO tmp15 VALUES('Thisisgood', 50);
1.对tmp15表中的整数值字段price进行算术运算
SELECT price, price + 10, price -10, price * 2, price /2, price%3 FROM tmp15 ;
2.对tmp15中的整型数值字段price进行比较运算
SELECT price, price> 10, price<10, price != 10, price =10, price <=>10,price <>10 FROM tmp15 ;
3.判断price值是否落在30~80区间；返回与70，30相比最大的值，判断price是否为IN列表（10, 20, 50, 35）中的某个值
SELECT price, price BETWEEN 30 AND 80, GREATEST(price, 70,30), price IN (10, 20, 50,35) FROM tmp15 ;
4..对tmp15中的字符串数值字段note进行比较运算，判断表tmp15中note字段是否为空；使用LIKE判断是否以字母'd'开头；使用REGEXP判断是否以字母'y'尾；判断是否包含字母'g'或者'm'
SELECT note, note IS NULL, note LIKE 't%', note REGEXP '$y' ,note REGEXP '[gm]' FROM tmp15 ;
5.将price字段值与NULL，0进行逻辑运算
SELECT price, price && 1, price && NULL, price||0, price AND 0, 0 AND NULL, price OR NULL FROM tmp15 ;
SELECT price,!price,NOT NULL,price XOR 3, 0 XOR NULL, price XOR 0 FROM tmp15 ;
6.将price字段值与2、4惊醒按位与、按位或操作，并对price进行按位操作
SELECT price, price&2 , price|4, ~price FROM tmp15 ;
7.将price字段值分别左移和右移两位
SELECT price, price<<2, price>>2 FROM tmp15 ;
第六章
例题
【例6.1】求2，-3.3和-33的绝对值，SQL语句如下：
SELECT ABS(2), ABS(-3.3), ABS(-33);
【例6.2】返回圆周率值，SQL语句如下：
SELECT pi();
【例6.3】求9，40和-49的二次平方根，SQL语句如下：
SELECT SQRT(9), SQRT(40), SQRT(-49);
【例6.4】对MOD(31,8)，MOD(234, 10)，MOD(45.5,6)进行求余运算，SQL语句如下：
SELECT MOD(31,8),MOD(234, 10),MOD(45.5,6);
【例6.5】使用CEILING函数返回最小整数，SQL语句如下：
SELECT CEIL(-3.35),CEILING(3.35);
【例6.6】使用FLOOR函数返回最大整数，SQL语句如下：
SELECT FLOOR(-3.35), FLOOR(3.35);
【例6.7】使用RAND()函数产生随机数，SQL语句如下：
SELECT RAND(),RAND(),RAND();
【例6.8】使用RAND(x)函数产生随机数，SQL语句如下：
SELECT RAND(10),RAND(10),RAND(11);
【例6.9】使用ROUND(x)函数对操作数进行四舍五入操作，SQL语句如下：
SELECT ROUND(-1.14),ROUND(-1.67), ROUND(1.14),ROUND(1.66);
【例6.10】使用ROUND(x,y)函数对操作数进行四舍五入操作，结果保留小数点后面指定y位，SQL语句如下：
SELECT ROUND(1.38, 1), ROUND(1.38, 0), ROUND(232.38, -1), round(232.38,-2);
【例6.11】使用TRUNCATE(x,y)函数对操作数进行四舍五入操作，结果保留小数点后面指定y位，SQL语句如下：
SELECT TRUNCATE(1.31,1), TRUNCATE(1.99,1), TRUNCATE(1.99,0), TRUNCATE(19.99,-1);
【例6.12】使用SIGN函数返回参数的符号，SQL语句如下：
SELECT SIGN(-21),SIGN(0), SIGN(21);
【例6.13】使用POW和POWER函数进行乘方运算，SQL语句如下：
SELECT POW(2,2), POWER(2,2),POW(2,-2), POWER(2,-2);
【例6.14】使用EXP函数计算e的乘方，SQL语句如下：
SELECT EXP(3),EXP(-3),EXP(0);
【例6.15】使用LOG(x)函数计算自然对数，SQL语句如下：
SELECT LOG(3), LOG(-3);
【例6.16】使用LOG10计算以10为基数的对数，SQL语句如下：
SELECT LOG10(2), LOG10(100), LOG10(-100);
【例6.17】使用RADIANS将角度转换为弧度，SQL语句如下：
SELECT RADIANS(90),RADIANS(180);
【例6.18】使用DEGREES将弧度转换为角度，SQL语句如下：
SELECT DEGREES(PI()), DEGREES(PI() / 2);
【例6.19】使用SIN函数计算正弦值，SQL语句如下：
SELECT SIN(1), ROUND(SIN(PI()));
【例6.20】使用ASIN函数计算反正弦值，SQL语句如下：
SELECT ASIN(0.8414709848078965), ASIN(3);
【例6.21】使用COS函数计算余弦值，SQL语句如下：
SELECT COS(0),COS(PI()),COS(1);
【例6.22】使用ACOS计算反余弦值，SQL语句如下：
SELECT ACOS(1),ACOS(0), ROUND(ACOS(0.5403023058681398));
【例6.23】使用TAN函数计算正切值，SQL语句如下：
SELECT TAN(0.3), ROUND(TAN(PI()/4));
【例6.24】使用ATAN函数计算反正切值，SQL语句如下：
SELECT ATAN(0.30933624960962325), ATAN(1);
【例6.25】使用COT()函数计算正切值，SQL语句如下，
SELECT COT(0.3), 1/TAN(0.3),COT(PI() / 4);
【例6.26】使用CHAR_LENGTH函数计算字符串字符个数，SQL语句如下：
SELECT CHAR_LENGTH('date'), CHAR_LENGTH('egg');
【例6.27】使用LENGTH函数计算字符串长度，SQL语句如下：
SELECT LENGTH('date'), LENGTH('egg');
【例6.28】使用CONCAT函数连接字符串，SQL语句如下：
SELECT CONCAT('My SQL', '5.5'),CONCAT('My',NULL, 'SQL');
【例6.29】使用CONCAT_WS函数连接带分隔符的字符串，SQL语句如下：
SELECT CONCAT_WS('-', '1st','2nd', '3rd'), CONCAT_WS('*', '1st', NULL, '3rd');
【例6.30】使用INSERT函数进行字符串替代操作，SQL语句如下：
SELECT INSERT('Quest', 2, 4, 'What') AS col1,
INSERT('Quest', -1, 4, 'What') AS col2,
INSERT('Quest', 3, 100, 'Wh') AS col3;
【例6.31】使用LOWER函数或者LCASE函数将字符串中所有字母字符转换为小写，SQL语句如下：
SELECT LOWER('BEAUTIFUL'), LCASE('Well');
【例6.32】使用UPPER函数或者UCASE函数将字符串中所有字母字符转换为大写，SQL语句如下：
SELECT UPPER('black'), UCASE('BLacK');
【例6.33】使用LEFT函数返回字符串中左边的字符，SQL语句如下：
SELECT LEFT('football', 5);
【例6.34】使用RIGHT函数返回字符串中右边的字符，SQL语句如下：
SELECT RIGHT('football', 4);
【例6.35】使用LPAD函数对字符串进行填充操作，SQL语句如下：
SELECT LPAD('hello',4,'??'), LPAD('hello',10,'??');
【例6.36】使用LPAD函数对字符串进行填充操作，SQL语句如下：
SELECT RPAD('hello',4,'?'), RPAD('hello',10,'?');
【例6.37】使用LTRIM函数删除字符串左边的空格，SQL语句如下：
SELECT '( book )',CONCAT('(',LTRIM(' book '),')');
【例6.38】SELECT CONCAT( '(', RTRIM (' book '), ')');
SELECT '( book )',CONCAT('(', RTRIM (' book '),')');
【例6.39】SELECT CONCAT( '(', TRIM(' book ') , ')');
SELECT '( book )',CONCAT('(', TRIM(' book '),')');
【例6.40】使用TRIM(s1 FROM s)函数删除字符串中两端指定的字符，SQL语句如下：
SELECT TRIM('xy' FROM 'xyxboxyokxxyxy') ;
【例6.41】使用REPEAT函数重复生成相同的字符串，SQL语句如下：
SELECT REPEAT('MySQL', 3);
【例6.42】使用SPACE函数生成由空格组成的字符串，SQL语句如下：
SELECT CONCAT('(', SPACE(6), ')' );
【例6.43】使用REPLACE函数进行字符串替代操作，SQL语句如下：
SELECT REPLACE('xxx.mysql.com', 'x', 'w');
【例6.44】使用STRCMP函数比较字符串大小，SQL语句如下：
SELECT STRCMP('txt', 'txt2'),STRCMP('txt2', 'txt'), STRCMP('txt', 'txt');
【例6.45】使用SUBSTRING函数获取指定位置处的子字符串，SQL语句如下：
SELECT SUBSTRING('breakfast',5) AS col1,
SUBSTRING('breakfast',5,3) AS col2,
SUBSTRING('lunch', -3) AS col3,
SUBSTRING('lunch', -5, 3) AS col4;
【例6.46】使用MID()函数获取指定位置处的子字符串，SQL语句如下：
SELECT MID('breakfast',5) as col1,
MID('breakfast',5,6) as col2,
MID('lunch', -3) as col3,
MID('lunch', -5, 3) as col4;
【例6.47】使用LOCATE，POSITION，INSTR函数查找字符串中指定子字符串的开始位置，SQL语句如下：
SELECT LOCATE('ball','football'),POSITION('ball'IN 'football'),INSTR ('football', 'ball');
【例6.48】使用REVERSE函数反转字符串，SQL语句如下：
SELECT REVERSE('abc');
【例6.49】使用ELT函数返回指定位置字符串，SQL语句如下：
SELECT ELT(3,'1st','2nd','3rd'), ELT(3,'net','os');
【例6.50】使用FIELD函数返回指定字符串第一次出现的位置，SQL语句如下：
SELECT FIELD('Hi', 'hihi', 'Hey', 'Hi', 'bas') as col1,
FIELD('Hi', 'Hey', 'Lo', 'Hilo', 'foo') as col2;
【例6.51】使用FIND_IN_SET()函数返回子字符串在字符串列表中的位置，SQL语句如下：
SELECT FIND_IN_SET('Hi','hihi,Hey,Hi,bas');
【例6.52】使用MAKE_SET根据二进制位选取指定字符串，SQL语句如下：
SELECT MAKE_SET(1,'a','b','c') as col1,
MAKE_SET(1 | 4,'hello','nice','world') as col2,
MAKE_SET(1 | 4,'hello','nice',NULL,'world') as col3,
MAKE_SET(0,'a','b','c') as col4;
【例6.53】使用日期函数获取系统当期日期，SQL语句如下：
SELECT CURDATE(),CURRENT_DATE(), CURDATE() + 0;
【例6.54】使用时间函数获取系统当期日期，SQL语句如下：
SELECT CURTIME(),CURRENT_TIME(),CURTIME() + 0;
【例6.55】使用日期时间函数获取当前系统日期和时间，SQL语句如下：
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
【例6.56】使用UNIX_TIMESTAMP函数返回UNIX格式的时间戳，SQL语句如下：
SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), NOW();
【例6.57】使用FROM_UNIXTIME函数将UNIX时间戳转换为普通格式时间，SQL语句如下：
SELECT FROM_UNIXTIME('1311476091');
【例6.58】使用UTC_DATE()函数返回当前UTC日期值，SQL语句如下：
SELECT UTC_DATE(), UTC_DATE() + 0;
【例6.59】使用UTC_TIME()函数返回当前UTC时间值，SQL语句如下：
SELECT UTC_TIME(), UTC_TIME() + 0;
【例6.60】使用MONTH()函数返回指定日期中的月份，SQL语句如下：
SELECT MONTH('2011-02-13');
【例6.61】使用MONTHNAME()函数返回指定日期中的月份的名称，SQL语句如下：
SELECT MONTHNAME('2011-02-13');
【例6.62】使用DAYNAME()函数返回指定日期的工作日名称，SQL语句如下：
SELECT DAYNAME('2011-02-13');
【例6.63】使用DAYOFWEEK()函数返回日期对应的周索引，SQL语句如下：
SELECT DAYOFWEEK('2011-02-13');
【例6.64】使用WEEKDAY()函数返回日期对应的周索引，SQL语句如下：
SELECT WEEKDAY('2011-02-13 22:23:00'), WEEKDAY('2011-07-01');
【例6.65】使用WEEK()函数查询指定日期是一年中的第几周，SQL语句如下：
SELECT WEEK('2011-02-20'),WEEK('2011-02-20',0), WEEK('2011-02-20',1);
【例6.66】使用WEEKOFYEAR()查询指定日期是一年中的第几周，SQL语句如下：
SELECT WEEK('2011-02-20',3), WEEKOFYEAR('2011-02-20');
【例6.67】使用DAYOFYEAR()函数返回指定日期在一年中的位置，SQL语句如下：
SELECT DAYOFYEAR('2011-02-20');
【例6.68】使用DAYOFYEAR()函数返回指定日期在一个月中的位置，SQL语句如下：
SELECT DAYOFMONTH('2011-02-20');
【例6.69】使用YEAR()函数返回指定日期对应的年份，SQL语句如下：
SELECT YEAR('11-02-03'),YEAR('96-02-03');
【例6.70】使用QUARTER()函数返回指定日期对应的季度，SQL语句如下
SELECT QUARTER('11-04-01');
【例6.71】使用MINUTE()函数返回指定时间的分钟值，SQL语句如下：
SELECT MINUTE('11-02-03 10:10:03');
【例6.72】使用MINUTE()函数返回指定时间的秒值，SQL语句如下
SELECT SECOND('10:05:03');
【例6.73】使用EXTRACT函数提前日期或者时间值，SQL语句如下：
SELECT EXTRACT(YEAR FROM '2011-07-02') AS col1,
EXTRACT(YEAR_MONTH FROM '2011-07-12 01:02:03') AS col2,
EXTRACT(DAY_MINUTE FROM '2011-07-12 01:02:03') AS col3;
【例6.74】使用TIME_TO_SEC函数将时间值转换为秒值，SQL语句如下：
SELECT TIME_TO_SEC('23:23:00');
【例6.75】使用SEC_TO_TIME()函数将秒值转换为时间格式，SQL语句如下：
SELECT SEC_TO_TIME(2345),SEC_TO_TIME(2345)+0,
TIME_TO_SEC('23:23:00'), SEC_TO_TIME(84180);
【例6.76】使用DATE_ADD()和ADDDATE()函数执行日期加操作，SQL语句如下：
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col1,
ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col2,
DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) AS col3;
【例6.77】使用DATE_SUB和SUBDATE函数执行日期减操作，SQL语句如下：
SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY) AS col1,
SUBDATE('2011-01-02', INTERVAL 31 DAY) AS col2,
DATE_SUB('2011-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS col3;
【例6.78】使用ADDTIME进行时间加操作，SQL语句如下：
SELECT ADDTIME('2000-12-31 23:59:59','1:1:1'), ADDTIME(’02:02:02’,’02:00:00’);
【例6.79】使用SUBTIME()函数执行时间减操作，SQL语句如下：
SELECT SUBTIME('2000-12-31 23:59:59','1:1:1'), SUBTIME('02:02:02','02:00:00');
【例6.80】使用DATEDIFF()函数计算两个日期之间的间隔天数，SQL语句如下：
SELECT DATEDIFF('2010-12-31 23:59:59','2010-12-30') AS col1,
DATEDIFF('2010-11-30 23:59:59','2010-12-31') AS col2;
【6.81】使用DATE_FORMAT()函数格式化输出日期和时间值，SQL语句如下：
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS col1,
DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j') AS col2;
【例6.82】使用TIME_FORMAT()函数格式化输入时间值，SQL语句如下：
SELECT TIME_FORMAT('16:00:00', '%H %k %h %I %l');
【例6.83】使用GET_FORMAT()函数显示不同格式化类型下的显示格式字符串，SQL语句如下：
SELECT GET_FORMAT(DATE,'EUR'), GET_FORMAT(DATE,'USA');
【例6.84】在DATE_FORMAT()函数中使用GET_FORMAT函数返回的显示格式字符串来显示指定的日期值，SQL语句如下：
SELECT DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA') );
【例6.85】使用IF()函数进行条件判断，SQL语句如下：
SELECT IF(1>2,2,3),
IF(1<2,'yes ','no'),
IF(STRCMP('test','test1'),'no','yes');
【例6.86】使用IFNULL()函数进行条件判断，SQL语句如下：
SELECT IFNULL(1,2), IFNULL(NULL,10), IFNULL(1/0, 'wrong');
【例6.87】使用CASE value WHEN语句执行分支操作，SQL语句如下：
SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
【例6.88】使用CASE WHEN语句执行分支操作，SQL语句如下：
SELECT CASE WHEN 1<0 THEN 'true' ELSE 'false' END;
【例6.89】查看当前MySQL版本号，SQL语句如下：
SELECT VERSION();
【例6.90】查看当前用户的链接数，SQL语句如下：
SELECT CONNECTION_ID();
【例6.91】使用SHOW PROCESSLIST命令输出当前用户连接信息，SQL语句如下：
SHOW PROCESSLIST;
【例6.92】查看当前使用的数据库，SQL语句如下：
SELECT DATABASE(),SCHEMA();
【例6.93】获取当前登陆用户名称，SQL语句如下：
SELECT USER(), CURRENT_USER(), SYSTEM_USER();
【例6.94】使用CHARSET()函数返回字符串使用的字符集，SQL语句如下：
SELECT CHARSET('abc'), CHARSET(CONVERT('abc' USING latin1)), CHARSET(VERSION());
【例6.95】使用COLLATION()函数返回字符串排列方式，SQL语句如下：
SELECT COLLATION('abc'),COLLATION(CONVERT('abc' USING utf8));
【例6.96】使用SELECT LAST_INSERT_ID查看最后一个自动生成的列值，执行过程如下：
⑴．一次插入一条记录
首先创建表worker，其Id字段带有AUTO_INCREMENT约束，SQL语句如下：
CREATE TABLE worker (Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, Name VARCHAR(30));
分别单独向表worker中插入2条记录：
INSERT INTO worker VALUES(NULL, 'jimy');
INSERT INTO worker VALUES(NULL, 'Tom');
SELECT LAST_INSERT_ID();
⑵．一次同时插入多条记录
接下来，向表中插入多条记录，SQL语句如下：
INSERT INTO worker VALUES(NULL, 'Kevin'),(NULL,'MIchal'),(NULL,'Nick');
SELECT LAST_INSERT_ID();
【例6.97】使用PASSWORD函数加密密码，SQL语句如下：
SELECT PASSWORD('newpwd');
【例6.98】使用MD5函数加密字符串，SQL语句如下：
SELECT MD5 ('mypwd');
【例6.99】使用ENCODE加密字符串，SQL语句如下：
SELECT ENCODE('secret','cry'), LENGTH(ENCODE('secret','cry'));
【例6.100】使用DECODE函数解密被ENCODE加密的字符串，SQL语句如下：
SELECT DECODE(ENCODE('secret','cry'),'cry');
【例6.101】使用FORMAT函数格式化数字，保留小数点位数为指定值，SQL语句如下：
SELECT FORMAT(12332.123456, 4), FORMAT(12332.1,4), FORMAT(12332.2,0);
【例6.102】使用CONV函数在不同进制数值之间转换，SQL语句如下：
SELECT CONV('a',16,2), CONV(15,10,2), CONV(15,10,8), CONV(15,10,16);
【例6.103】使用INET_ATON函数将字符串网络点地址转换为数值网络地址，SQL语句如下：
SELECT INET_ATON('209.207.224.40');
【例6.104】使用INET_NTOA函数将数值网络地址转换为字符串网络点地址，SQL语句如下：
SELECT INET_NTOA(3520061480);
【例6.105】使用加锁、解锁函数，SQL语句如下：
SELECT GET_LOCK('lock1',10) AS GetLock,
IS_USED_LOCK('lock1') AS ISUsedLock,
IS_FREE_LOCK('lock1') AS ISFreeLock,
RELEASE_LOCK('lock1') AS ReleaseLock;
【例6.106】使用BENCHMARK重复执行指定函数，SQL语句如下：
SELECT BENCHMARK( 500000, PASSWORD ('newpwd') );
【例6.107】使用CONVERT()函数改变字符串的默认字符集，SQL语句如下：
SELECT CHARSET('string'), CHARSET(CONVERT('string' USING latin1));
【例6.108】使用CAST和CONVERT函数进行数据类型的转换，SQL语句如下：
select CAST(100 AS CHAR(2)), CONVERT('2010-10-01 12:12:12',TIME);
1.使用数学函数RAND()生成3个10以内的随机整数。
SELECT ROUND(RAND() * 10), ROUND(RAND() * 10), ROUND(RAND() * 10);
2.使用sin()，cos()，tan()，cot()函数计算三角函数值，并将计算结果值转换成整数值。
SELECT PI(), sin(PI()/2),cos(PID()), ROUND(tan(PI()/4)), FLOOR(cot(PI()/4));
3.创建表，并使用字符串和日期函数，对字段值进行操作。
1）创建表member
CREATE TABLE member
(
m_id INT AUTO_INCREMENT PRIMARY KEY,
m_FN VARCHAR(100),
m_LN VARCHAR(100),
m_birth DATETIME,
m_info VARCHAR(255) NULL
);
2）插入一条记录
INSERT INTO member VALUES (NULL, 'Halen ', 'Park', '1970-06-29', 'GoodMan ');
3）返回m_FN的长度，返回记录中人的全名，将m_info字段值转换成小写字母，将m_info的值反向输出。
SELECT LENGTH(m_FN), CONCAT(m_FN, m_LN),
LOWER(m_info), REVERSE(m_info) FROM member;
4）计算第一条记录中人的年龄，并计算m_birth字段中的值在那一年中的位置，按照“Saturday October 4th 1997”格式输出时间值。
SELECT YEAR(CURDATE())-YEAR(m_birth) AS age, DAYOFYEAR(m_birth) AS days, DATE_FORMAT(m_birth, '%W %D %M %Y') AS birthDate FROM member;
5）插入一条新的记录，m_FN值为“Samuel”，m_LN值为“Green”，m_birth值为系统当前时间，m_info为空。使用LAST_INSERT_ID()查看最后 插入的ID值。
INSERT INTO member VALUES (NULL, 'Samuel', 'Green', NOW(),NULL);
4.使用CASE进行条件判断，如果m_birth小于2000年，显示“old”，如果m_birth大于2000年，则显示“young”
SELECT m_birth,
CASE WHEN YEAR(m_birth) < 2000 THEN 'old'
WHEN YEAR(m_birth) > 2000 THEN 'young'
ELSE 'not born' END AS status FROM member;
第七章
例题
/*--------------------------本章需要用到的数据表--------------------------*/
CREATE TABLE customers
(
c_id int NOT NULL AUTO_INCREMENT,
c_name char(50) NOT NULL,
c_address char(50) NULL,
c_city char(50) NULL,
c_zip char(10) NULL,
c_contact char(50) NULL,
c_email char(255) NULL,
PRIMARY KEY (c_id)
);
CREATE TABLE fruits
(
f_id char(10) NOT NULL,
s_id INT NOT NULL,
f_name char(255) NOT NULL,
f_price decimal(8,2) NOT NULL,
PRIMARY KEY(f_id)
) ;
CREATE TABLE suppliers
(
s_id int NOT NULL AUTO_INCREMENT,
s_name char(50) NOT NULL,
s_city char(50) NULL,
s_zip char(10) NULL,
s_call CHAR(50) NOT NULL,
PRIMARY KEY (s_id)
) ;
CREATE TABLE orderitems
(
o_num int NOT NULL,
o_item int NOT NULL,
f_id char(10) NOT NULL,
quantity int NOT NULL,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (o_num,o_item)
) ;
CREATE TABLE orders
(
o_num int NOT NULL AUTO_INCREMENT,
o_date datetime NOT NULL,
c_id int NOT NULL,
PRIMARY KEY (o_num)
) ;
/*--------------------------插入数据--------------------------*/
INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email)
VALUES(10001, 'RedHook', '200 Street ', 'Tianjin', '300000', 'LiMing', 'LMing@163.com'),
(10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo','Jerry@hotmail.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong', NULL),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', 'sam@hotmail.com');
INSERT INTO fruits (f_id, s_id, f_name, f_price)
VALUES('a1', 101,'apple',5.2),
('b1',101,'blackberry', 10.2),
('bs1',102,'orange', 11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana', 10.3),
('t2',102,'grape', 5.3),
('o2',103,'coconut', 9.2),
('c0',101,'cherry', 3.2),
('a2',103, 'apricot',2.2),
('l2',104,'lemon', 6.4),
('b2',104,'berry', 7.6),
('m1',106,'mango', 15.6),
('m2',105,'xbabay', 2.6),
('t4',107,'xbababa', 3.6),
('m3',105,'xxtt', 11.6),
('b5',107,'xxxx', 3.6);
INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');
INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)
VALUES(30001, 1, 'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 5, 14.99);
INSERT INTO orders(o_num, o_date, c_id)
VALUES(30001, '2008-09-01', 10001),
(30002, '2008-09-12', 10003),
(30003, '2008-09-30', 10004),
(30004, '2008-10-03', 10005),
(30005, '2008-10-08', 10001);
【例7.1】从fruits表中检索所有字段的数据
SELECT * FROM fruits;
SELECT f_id, s_id ,f_name, f_price FROM fruits;
【例7.2】查询当前表中f_name列所有水果名称，输入如下语句：
SELECT f_name FROM fruits;
【例7.3】例如，从fruits表中获取f_name和f_price两列，输入如下语句：
SELECT f_name, f_price FROM fruits;
【例7.4】查询价格为10.2元的水果的名称，输入如下语句：
SELECT f_name, f_price FROM fruits WHERE f_price = 10.2;
【例7.5】查找名称为“apple”的水果的价格,输入如下语句：
SELECT f_name, f_price FROM fruits WHERE f_name = 'apple';
【例7.6】查询价格小于10的水果的名称，输入如下语句：
SELECT f_name, f_price FROM fruits WHERE f_price < 10;
【例7.7】s_id为101和102的记录，输入如下语句：
SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN (101,102) ORDER BY f_name;
【例7.8】查询所有s_id不等于101也不等于102的记录，输入如下语句：
SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id NOT IN (101,102)
ORDER BY f_name;
【例7.9】查询价格在2.00元到10.5元之间水果名称和价格
SELECT f_name, f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;
【例7.10】查询价格在2.00元到10.5元之外的水果名称和价格
SELECT f_name, f_price FROM fruits WHERE f_price NOT BETWEEN 2.00 AND 10.20;
【例7.11】查找所有以‘b’字母开头的水果，输入如下语句：
SELECT f_id, f_name FROM fruits WHERE f_name LIKE 'b%';
【例7.12】在fruits表中，查询f_name中包含字母‘g’的记录
SELECT f_id, f_name FROM fruits WHERE f_name LIKE '%g%';
【例7.13】查询以‘b’开头，并以‘y’结尾的水果的名称
SELECT f_name FROM fruits WHERE f_name LIKE 'b%y';
【例7.14】在fruits表中，查询以字母‘y’结尾，且‘y’前面只有4个字母的记录
SELECT f_id, f_name FROM fruits WHERE f_name LIKE '____y';
【例7.15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值：
SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL;
【例7.16】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值
SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL;
【例7.17】在fruits表中查询s_id = ‘101’，并且f_price大于5的记录价格和名称
SELECT f_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price >=5;
【例7.18】在fruits表中查询s_id = ‘101’或者’102’，并且f_price大于5，并且f_name=’apple’的记录价格和名称
SELECT f_id, f_price, f_name FROM fruits
WHERE s_id IN('101', '102') AND f_price >= 5 AND f_name = 'apple';
【例7.19】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：
SELECT s_id,f_name, f_price FROM fruits WHERE s_id = 101 OR s_id = 102;
【例7.20】查询s_id=101或者s_id=102的水果供应商的f_price和f_name
SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN(101,102);
【例7.21】查询fruits表中s_id字段的值，并返回s_id字段值不得重复
SELECT DISTINCT s_id FROM fruits;
【例7.22】查询fruits表的f_name字段值，并对其进行排序
select f_name from fruits ORDER BY f_name;
【例7.23】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序
SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price;
【例7.24】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序
SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;
【例7.25】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：
SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name;
【例7.26】根据s_id对fruits表中的数据进行分组
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;
【例7.27】根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来
SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;
【例7.28】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息
SELECT s_id, GROUP_CONCAT(f_name) AS Names
FROM fruits
GROUP BY s_id HAVING COUNT(f_name) > 1;
【例7.29】根据s_id对fruits表中的数据进行分组，并显示记录数量
SELECT s_id, COUNT(*) AS Total
FROM fruits
GROUP BY s_id WITH ROLLUP;
【例7.30】根据s_id和f_name字段对fruits表中的数据进行分组， SQL语句如下，
SELECT * from fruits group by s_id,f_name;
【例7.31】查询订单价格大于100的订单号和总订单价格
SELECT o_num, SUM(quantity * item_price) AS orderTotal
FROM orderitems
GROUP BY o_num
HAVING SUM(quantity*item_price) >= 100;
【例7.32】显示fruits表查询结果的前4行，输入如下语句：
SELECT * From fruits LIMIT 4;
【例7.33】在fruits 表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录
SELECT * From fruits LIMIT 4, 3;
【例7.34】查询customers表中总的行数
SELECT COUNT(*) AS cust_num from customers;
【例7.35】查询customers表中有电子邮箱的顾客的总数，输入如下语句：
SELECT COUNT(c_email) AS email_num
FROM customers;
【例7.36】在orderitems表中，使用COUNT()函数统计不同订单号中订购的水果种类
SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num;
【例7.37】在orderitems表中查询30005号订单一共购买的水果总量，输入如下语句：
SELECT SUM(quantity) AS items_total
FROM orderitems
WHERE o_num = 30005;
【例7.38】在orderitems表中，使用SUM()函数统计不同订单号中订购的水果总量
SELECT o_num, SUM(quantity) AS items_total
FROM orderitems
GROUP BY o_num;
【例7.39】在fruits表中，查询s_id=103的供应商的水果价格的平均值，SQL语句如下：
SELECT AVG(f_price) AS avg_price
FROM fruits
WHERE s_id = 103;
【例7.40】在fruits表中，查询每一个供应商的水果价格的平均值，SQL语句如下：
SELECT s_id,AVG(f_price) AS avg_price
FROM fruits
GROUP BY s_id;
【例7.41】在fruits表中查找市场上价格最高的水果，SQL语句如下：
mysql>SELECT MAX(f_price) AS max_price FROM fruits;
【例7.42】在fruits表中查找不同供应商提供的价格最高的水果
SELECT s_id, MAX(f_price) AS max_price
FROM fruits
GROUP BY s_id;
【例7.43】在fruits表中查找f_name的最大值，SQL语句如下
SELECT MAX(f_name) from fruits;
【例7.44】在fruits表中查找市场上价格最低的水果，SQL语句如下：
mysql>SELECT MIN(f_price) AS min_price FROM fruits;
【例7.45】在fruits表中查找不同供应商提供的价格最低的水果
SELECT s_id, MIN(f_price) AS min_price
FROM fruits
GROUP BY s_id;
【例7.46】在fruits表和suppliers表之间使用内连接查询，查询之前，查看两个表的结构，
SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits ,suppliers
WHERE fruits.s_id = suppliers.s_id;
【例7.47】在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询
SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id;
【例7.48】查询供应f_id=’a1’的水果供应商提供的其他水果种类
SELECT f1.f_id, f1.f_name
FROM fruits AS f1, fruits AS f2
WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';
【例7.49】在customers表和orders表中，查询所有客户，包括没有订单的客户，SQL语法如下，
SELECT customers.c_id, orders.o_num
FROM customers LEFT OUTER JOIN orders
ON customers.c_id = orders.c_id;
【例7.50】在customers表和orders表中，查询所有订单，包括没有客户的订单
SELECT customers.c_id, orders.o_num
from customers RIGHT OUTER JOIN orders
ON customers.c_id = orders.c_id;
【例7.51】在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001、的客户的订单信息
SELECT customers.c_id, orders.o_num
FROM customers INNER JOIN orders
ON customers.c_id = orders.c_id AND customers.c_id = 10001;
【例7.52】在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询，并对查询结果排序
SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id
ORDER BY fruits.s_id;
定义两个表tb1和tb2
CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);
向两个表中插入数据，
INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);
【例7.53】返回tbl2表的所有 num2 列，然后将 tbl1 中的 num1 的值与之进行比较，只要大于 num2的任何值为符合查询条件的结果。
SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);
【例7.54】返回tbl1表的中比tbl2表num2 列所有值都大的值
SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);
【例7.55】查询表suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的记录
SELECT * from fruits
WHERE EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);
【例7.56】查询表suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的f_price大于10.20的记录
SELECT * from fruits
WHERE f_price>10.20 AND EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);
【例7.57】查询表suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录
SELECT * from fruits
WHERE NOT EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);
【例7.58】在orderitems表中查询订购f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id
SELECT c_id FROM orders WHERE o_num IN
(SELECT o_num FROM orderitems WHERE f_id = 'c0');
【例7.59】与前一个例子语句类似，但是在SELECT语句中使用NOT IN操作符
SELECT c_id FROM orders WHERE o_num NOT IN
(SELECT o_num FROM orderitems WHERE f_id = 'c0');
【例7.60】在suppliers表中查询s_city等于Tianjin的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类
SELECT s_id, f_name FROM fruits
WHERE s_id =
(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');
【例7.61】在suppliers表中查询s_city等于Tianjin的供应商s_id，然后在fruits表中查询所有非该供应商提供的水果的种类，SQL语句如下，
SELECT s_id, f_name FROM fruits
WHERE s_id <>
(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');
【例7.62】查询所有价格小于9的水果的列表，查询s_id等于101和103所有的水果种类，使用UNION连接查询结果
SELECT s_id, f_name, f_price
FROM fruits
WHERE f_price < 9.0
UNION ALL
SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN(101,103);
【例7.63】查询所有价格小于9的水果的列表，查询s_id等于101和103所有的水果种类，使用UNION ALL连接查询结果，SQL语句如下
SELECT s_id, f_name, f_price
FROM fruits
WHERE f_price < 9.0
UNION ALL
SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN(101,103);
【例7.64】为orders表取别名o，查询订30001订单的下单日期
SELECT * from orders AS o
WHERE o.o_num = 30001;
【例7.65】为customers和orders表分别取别名，并进行连接查询
SELECT c.c_id, o.o_num
FROM customers AS c LEFT OUTER JOIN orders AS o
ON c.c_id = o.c_id;
【例7.66】查询fruits表，为f_name取别名fruit_name，f_price取别名fruit_price，为fruits表取别名f1，查询表中f_price < 8的水果的名称
SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_price
FROM fruits AS f1
WHERE f1.f_price < 8;
【例7.67】查询suppliers表中字段s_name和s_city，使用CONCAT函数连接这个两个字段值，并取列别名为suppliers_title。
如果没有对连接后的值取别名，其显示列名称将会不够直观，输入如下SQL，
SELECT CONCAT(RTRIM(s_name) , ' (', RTRIM(s_city), ')')
FROM suppliers
ORDER BY s_name;
【例7.68】在fruits表中，查询f_name字段以字母‘b’开头的记录
SELECT * FROM fruits WHERE f_name REGEXP '^b';
【例7.69】在fruits表中，查询f_name字段以“be”开头的记录
SELECT * FROM fruits WHERE f_name REGEXP '^be';
【例7.70】在fruits表中，查询f_name字段以字母‘t’结尾的记录
SELECT * FROM fruits WHERE f_name REGEXP 'y$';
【例7.71】在fruits表中，查询f_name字段以字符串“rry”结尾的记录
SELECT * FROM fruits WHERE f_name REGEXP 'rry$';
【例7.72】在fruits表中，查询f_name字段值包含字母‘a’与‘g’且两个字母之间只有一个字母的记录
SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
【例7.73】在fruits表中，查询f_name字段值以字母‘b’开头，且‘b’后面出现字母‘a’的记录
SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
【例7.74】在fruits表中，查询f_name字段值以字母‘b’开头，且‘b’后面出现字母‘a’至少一次的记录
SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
【例7.75】在fruits表中，查询f_name字段值包含字符串“on”的记录
SELECT * FROM fruits WHERE f_name REGEXP 'on';
【例7.76】在fruits表中，查询f_name字段值包含字符串“on”或者“ap”的记录
SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
【例7.77】在fruits表中，使用LIKE运算符查询f_name字段值为“on”的记录
SELECT * FROM fruits WHERE f_name LIKE 'on';
【例7.78】在fruits表中，查找f_name字段中包含字母o或者t的记录
SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
【例7.79】在fruits表，查询s_id字段中数值中包含4、5或者6的记录
SELECT * FROM fruits WHERE s_id REGEXP '[456]';
【例7.80】在fruits表中，查询f_id字段包含字母a到e和数字1到2以外的字符的记录
SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
【例7.81】在fruits表中，查询f_name字段值出现字符串‘x’至少2次的记录
SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
【例7.82】在fruits表中，查询f_name字段值出现字符串“ba”最少1次，最多3次的记录
SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';
综合案例
步骤1：创建数据表employee和dept，
创建dept表
CREATE TABLE dept
(
d_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
d_name VARCHAR(50),
d_location VARCHAR(100)
);
创建employee表
CREATE TABLE employee
(
e_no INT NOT NULL PRIMARY KEY,
e_name VARCHAR(100) NOT NULL,
e_gender CHAR(2) NOT NULL,
dept_no INT NOT NULL,
e_job VARCHAR(100) NOT NULL,
e_salary SMALLINT NOT NULL,
hireDate DATE,
CONSTRAINT dno_fk FOREIGN KEY(dept_no)
REFERENCES dept(d_no)
);
步骤2：将指定记录分别插入两个表中，执行过程如下：
向dept表中插入数据
INSERT INTO dept
VALUES (10, 'ACCOUNTING', 'ShangHai'),
(20, 'RESEARCH ', 'BeiJing '),
(30, 'SALES ', 'ShenZhen '),
(40, 'OPERATIONS ', 'FuJian ');
向employee表中插入数据
INSERT INTO employee
VALUES (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'),
(1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),
(1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'),
(1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'),
(1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'),
(1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'),
(1007, 'CLARK', 'm',10, 'MANAGER', 2450,'2002-09-12'),
(1008, 'SCOTT', 'm',20, 'ANALYST', 3000,'2003-05-12'),
(1009, 'KING', 'f',10, 'PRESIDENT', 5000,'1995-01-01'),
(1010, 'TURNER', 'f',30, 'SALESMAN', 1500,'1997-10-12'),
(1011, 'ADAMS', 'm',20, 'CLERK', 1100,'1999-10-05'),
(1012, 'JAMES', 'm',30, 'CLERK', 950,'2008-06-15');
步骤3：在employee表中，查询所有记录的e_no、e_name和e_salary字段值，语句如下：
SELECT e_no, e_name, e_salary;
步骤4：在employee表中，查询dept_no等于10和20的所有记录。
SELECT * FROM employee WHERE dept_no IN (10, 20);
步骤5：在employee表中，查询工资范围在800到2500之间的员工信息。
SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;
步骤6：在employee表中，查询部门编号为20的部门中的员工信息。
SELECT * from employee WHERE dept_no = 20;
步骤7：在employee表中，查询每个部门最高工资的员工信息。
SELECT dept_no, MAX(e_salary) FROM employee GROUP BY dept_no;
步骤8：查询员工BLAKE所在部门和部门所在地。
SELECT d_no, d_location FROM dept WHERE d_no=
(SELECT dept_no FROM employee WHERE e_name='BLAKE');
步骤9：使用连接查询，查询所有员工的部门和部门信息。
SELECT e_no, e_name, dept_no, d_name,d_location
FROM employee, dept WHERE dept.d_no=employee.dept_no;
步骤10：在employee表中，计算每个部门各有多少名员工。
SELECT dept_no, COUNT(*) FROM employee GROUP BY dept_no;
步骤11：在employee表中，计算不同类型职工的总工资数。
SELECT e_job, SUM(e_salary) FROM employee GROUP BY e_job;
步骤12：在employee表中，计算不同部门的平均工工资。
SELECT dept_no, AVG(e_salary) FROM employee GROUP BY dept_no;
步骤13：在employee表中，查询工资低于1500的员工信息。
SELECT * FROM employee WHERE e_salary < 1500;
步骤14：在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列。
SELECT e_name,dept_no, e_salary
FROM employee ORDER BY dept_no DESC, e_salary DESC;
步骤15：在employee表中，查询员工姓名以字母A或S开头的员工的信息。
SELECT * FROM employee WHERE e_name REGEXP '^[as]';
步骤16：在employee表中，查询到目前为止，工龄大于等于10年的员工信息。
SELECT * from employee where YEAR(CURDATE()) -YEAR(hireDate) >= 10;
第八章
综合案例
CREATE TABLE person
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(40) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
info CHAR(50) NULL,
PRIMARY KEY (id)
);
【例8.1】在person表中，插入一条新记录，id值为3，name值为Green，age值为21，SQL语句如下：
INSERT INTO person (id ,name, age , info) VALUES (1,'Green', 21, 'Lawyer');
【例8.2】在person表中，插入一条新记录，id值为4，name值为Suse，age值为22，info值为dancer，SQL语句如下：
INSERT INTO person (age ,name, id , info)
VALUES (22, 'Suse', 2, 'dancer');
【例8.3】在person表中，插入一条新记录，name值为Mary，age值为24，SQL语句如下：
INSERT INTO person
VALUES (3,'Mary', 24, 'Musician');
【例8.4】在person表中，插入一条新记录，name值为Willam，age值为20，info值为sports man，SQL语句如下：
INSERT INTO person (name, age,info)
VALUES('Willam', 20, 'sports man');
【例8.5】在person表中，插入一条新记录，name值为laura，age值为25，SQL语句如下：
INSERT INTO person (name, age ) VALUES ('Laura', 25);
【例8.6】在person表中，在name、age和info字段指定插入值，同时插入3条新记录，SQL语句如下：
INSERT INTO person(name, age, info)
VALUES ('Evans',27, 'secretary'),
('Dale',22, 'cook'),
('Edison',28, 'singer');
【例8.7】在person表中，不指定插入列表，同时插入2条新记录，SQL语句如下：
INSERT INTO person
VALUES (9,'Harry',21, 'magician'),
(NULL,'Harriet',19, 'pianist');
【例8.8】从person_old表中查询所有的记录，并将其插入到person表中，过程如下：
首先，创建一个名为person_old的数据表，
CREATE TABLE person_old
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(40) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
info CHAR(50) NULL,
PRIMARY KEY (id)
);
向person_old表中添加两条记录：
INSERT INTO person_old
VALUES (10,'Harry',20, 'student'), (11,'Beckham',31, 'police');
将查询结果插入到表中
INSERT INTO person(id, name, age, info)
SELECT id, name, age, info FROM person_old;
【例8.9】在person表中，更新id值为10的记录，将age字段值改为15，将name字段值改为LiMing，SQL语句如下：
UPDATE person SET age = 15, name=’LiMing’ WHERE id = 10;
【例8.10】在person表中，更新age值为19到22的记录，将info字段值都改为student，SQL语句如下：
UPDATE person SET info=’student’ WHERE id BETWEEN 19 AND 22;
【例8.11】在person表中，删除id等于10的记录，SQL语句如下：
DELETE FROM person WHERE id = 10;
【例8.12】在person表中，使用DELETE语句同时删除多条记录，在前面UPDATE语句中将age字段值在19到22之间的记录的info字段值修改为student，在这里删除这些记录，SQL语句如下：
DELETE FROM person WHERE age BETWEEN 19 AND 22;
【例8.13】删除person表中所有记录，SQL语句如下：
DELETE from person;
综合案例
步骤1：创建数据表books
CREATE TABLE books
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
authors VARCHAR(200) NOT NULL,
price INT(11) NOT NULL,
pubdate YEAR NOT NULL,
note VARCHAR(255) NULL,
num INT NOT NULL DEFAULT 0
);
步骤2：向books表中插入记录
1）指定所有字段名称插入记录，SQL语句如下：
INSERT INTO books (id, name, authors, price, pubdate,note,num)
VALUES(1, 'Tale of AAA', 'Dickes', 23, '1995', 'novel',11);
2）不指定字段名称插入记录，SQL语句如下：
INSERT INTO books
VALUES (2,'EmmaT','Jane lura',35,'1993', 'joke',22);
3）同时插入多条记录。
使用INSERT语句将剩下的多条记录插入表中，SQL语句如下：
INSERT INTO books
VALUES(3, 'Story of Jane', 'Jane Tim', 40, '2001', 'novel', 0),
(4, 'Lovey Day', 'George Byron', 20, '2005', 'novel', 30),
(5, 'Old Land', 'Honore Blade', 30, '2010', 'law',0),
(6,'The Battle','Upton Sara',33,'1999', 'medicine',40),
(7,'Rose Hood','Richard Kale',28,'2008', 'cartoon',28);
步骤3：将小说类型(novel)的书的价格都增加5。
UPDATE books SET price = price + 5 WHERE note = 'novel';
步骤4：将名称为EmmaT的书的价格改为40，并将说明改为drama
UPDATE books SET price=40,note='drama' WHERE name='EmmaT';
步骤5：删除库存为0的记录。
DELETE FROM books WHERE num=0;
第九章
例题
【例9.1】在book表中的year_publication字段上建立普通索引
CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication)
);
使用EXPLAIN语句查看索引是否正在使用：
explain select * from book where year_publication=1990 \G;
【例9.2】创建一个表t1，在表中的id字段上使用UNIQUE关键字创建唯一索引
CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
【例9.3】创建一个表t2，在表中的name字段上使用创建单列索引
CREATE TABLE t2
(
id INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(20))
);
【例9.4】创建表t3，在表中的id和info字段上建立组合索引
CREATE TABLE t3
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdx(id, name, info(100))
);
【例9.5】创建表t4，在表中的info字段上建立全文索引
CREATE TABLE t4
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;
【例9.6】创建表t5，在空间类型为GEOMETRY的字段上创建空间索引
CREATE TABLE t5 ( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) )ENGINE=MyISAM;
【例9.7】在book表中的bookname字段上建立名为BkNameIdx的普通索引
ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30) );
【例9.8】在book表的bookId字段上建立名称为UniqidIdx 的唯一索引
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );
【例9.9】在book表的comment字段上建立单列索引
ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );
【例9.10】在book表的authors和info字段上建立组合索引
ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),info(50) );
【例9.11】创建表t6，在t6表上使用ALTER TABLE创建全文索引
首先创建表t6，注意修改ENGINE参数为MyISAM，MySQL默认引擎InnoDB不支持全文索引
CREATE TABLE t6
(
id INT NOT NULL,
info CHAR(255)
) ENGINE=MyISAM;
使用ALTER TABLE语句在info字段上创建全文索引：
ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );
【例9.12】创建表t7，在t7的空间数据类型字段g上创建名称为spatIdx的空间索引
CREATE TABLE t7 ( g GEOMETRY NOT NULL )ENGINE=MyISAM;
使用ALTER TABLE在表t7的g字段建立空间索引,
ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);
【例9.13】在book表中的bookname字段上建立名为BkNameIdx的普通索引
CREATE INDEX BkNameIdx ON book(bookname);
【例9.14】在book表的bookId字段上建立名称为UniqidIdx 的唯一索引
CREATE UNIQUE INDEX UniqidIdx ON book ( bookId );
【例9.15】在book表的comment字段上建立单列索引
CREATE INDEX BkcmtIdx ON book(comment(50) );
【例9.16】在book表的authors和info字段上建立组合索引
CREATE INDEX BkAuAndInfoIdx ON book ( authors(20),info(50) );
【例9.17】删除表t6，重新建立表t6，在t6表中使用CREATE INDEX语句，在CHAR类型的info字段上创建全文索引
drop table t6;
CREATE TABLE t6
(
id INT NOT NULL,
info CHAR(255)
) ENGINE=MyISAM;
CREATE FULLTEXT INDEX ON t6(info);
【例9.18】删除表t7，重新创建表t7，在t7表中使用CREATE INDEX语句，在空间数据类型字段g上创建名称为spatIdx的空间索引
首先删除表t7，并重新建立该表
drop table t7;
CREATE TABLE t7 ( g GEOMETRY NOT NULL )ENGINE=MyISAM;
使用CREATE INDEX语句在表t7的g字段建立空间索引，
CREATE SPATIAL INDEX spatIdx ON t7 (g);
【例9.19】删除book表中的名称为UniqidIdx的唯一索引
ALTER TABLE book DROP INDEX UniqidIdx;
【例9.20】删除book表中名称为BkAuAndInfoIdx的组合索引
DROP INDEX BkAuAndInfoIdx ON book;
综合案例
步骤1：登陆MySQL数据库
mysql -h localhost -u root -p
步骤2：创建数据库company
CREATE database index_test;
USE index_test;
步骤3：创建表test_table1
CREATE TABLE test_table1
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name CHAR(100) NOT NULL,
address CHAR(100) NOT NULL,
description CHAR(100) NOT NULL,
UNIQUE INDEX UniqIdx(id),
INDEX MultiColIdx(name(20), address(30)),
INDEX ComIdx( description(30) )
);
步骤4：创建表test_tabl2，存储引擎为MyISAM
CREATE TABLE test_table2
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
firstname CHAR(100) NOT NULL,
middlename CHAR(100) NOT NULL,
lastname CHAR(100) NOT NULL,
birth DATE NOT NULL,
title CHAR(100) NULL
) ENGINE=MyISAM;
步骤5：使用ALTER TABLE语句在表test_table2的birth字段上建立名称为ComDateIdx的普通索引
ALTER TABLE test_table2 ADD INDEX ComDateIdx(birth);
步骤6：使用ALTER TABLE语句在表test_table2表的id字段上添加名称为UniqIdx2的唯一索引，并以降序排列
ALTER TABLE test_table2 ADD UNIQUE INDEX UniqIdx2 (id DESC) ;
步骤7：使用CREATE INDEX在firstname、middlename和lastname3个字段上建立名称为MultiColIdx2组合索引
CREATE INDEX MultiColIdx2 ON test_table2(firstname, middlename, lastname);
步骤8：使用CREATE INDEX在title字段上建立名称为FTIdx的全文索引
CREATE INDEX FTIdx ON test_table2(title);
步骤9：使用ALTER TABLE语句删除表test_table1中名称为UniqIdx的唯一索引
ALTER TABLE test_table1 DROP INDEX UniqIdx;
步骤10：使用DROP INDEX语句删除表test_table2中名称为MultiColIdx2的组合索引
DROP INDEX MultiColIdx2 ON test_table2;第十章
例题
【例10.1】创建查看fruits表的存储过程，代码如下：
DELIMITER //
CREATE PROCEDURE Proc()
BEGIN
SELECT * FROM fruits;
END //
DELIMITER ;
【例10.2】创建名称为CountProc的存储过程，代码如下：
DELIMITER //
CREATE PROCEDURE CountProc(OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM fruits;
END //
DELIMITER ;
【例10.3】创建存储函数，名称为NameByZip，该函数返回SELECT语句的查询结果，数值类型为字符串型，代码如下：
DELIMITER //
CREATE FUNCTION NameByZip()
RETURNS CHAR(50)
RETURN (SELECT s_name FROM suppliers WHERE s_call= '48075');
END //
DELIMITER;
【例10.4】下面定义一个名称为myparam的变量，类型为INT类型，默认值为100，代码如下：
DECLARE myparam INT DEFAULT 100;
【例10.5】声明3个变量，分别为var1、var2和var3，数据类型为INT，使用SET为变量赋值，代码如下：
DECLARE var1, var2, var3 INT;
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;
【例10.6】声明变量fruitname和fruitprice，通过SELECT ... INTO语句查询指定记录并为变量赋值，代码如下：
DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);
SELECT f_name,f_price INTO fruitname, fruitprice
FROM fruits WHERE f_id ='a1';
【例10.7】定义"ERROR 1148 (42000)"错误，名称为command_not_allowed。可以用两种不同的方法来定义，代码如下：
//方法一：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
//方法二：使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR 1148
【例10.8】定义处理程序的几种方式，代码如下：
//方法一：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
//方法二：捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info=' NO_SUCH_TABLE ';
//方法三：先定义条件，然后调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info=' NO_SUCH_TABLE ';
//方法四：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
//方法五：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info=' NO_SUCH_TABLE ';
//方法六：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
【例10.9】定义条件和处理程序，具体执行的过程如下：
CREATE TABLE test.t (s1 int,primary key (s1));
DELIMITER //
CREATE PROCEDURE handlerdemo ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO test.t VALUES (1);
SET @x = 2;
INSERT INTO test.t VALUES (1);
SET @x = 3;
END;
//
DELIMITER ;
CALL handlerdemo(); /*调用存储过程*/
SELECT @x; /*查看调用过程结果*/
【例10.10】声明名称为cursor_fruit的光标，代码如下：
DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits ;
【例10.11】使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price这两个变量中，代码如下：
FETCH cursor_fruit INTO fruit_name, fruit_price ;
【例10.12】打开名称为cursor_fruit的光标，代码如下：
OPEN cursor_fruit ;
【例10.13】关闭名称为cursor_fruit的光标，代码如下：
CLOSE cursor_fruit;
【例10.14】IF语句的示例，代码如下：
IF val IS NULL
THEN SELECT 'val is NULL';
ELSE SELECT 'val is not NULL';
END IF;
【例10.15】使用CASE流程控制语句判断val值等于1、等于2，或者两者都不等，语句如下：
CASE val
WHEN 1 THEN SELECT 'val is 1';
WHEN 2 THEN SELECT 'val is 2';
ELSE SELECT 'val is not 1 or 2';
END CASE;
【例10.16】使用CASE流程控制语句判断val是否为空、小于0、大于0或者等于0，或者两者都不等，语句如下：
CASE
WHEN val IS NULL THEN SELECT 'val is NULL';
WHEN val < 0 THEN SELECT 'val is less than 0';
WHEN val > 0 THEN SELECT 'val is greater than 0';
ELSE SELECT 'val is 0';
END CASE;
【例10.17】使用LOOP语句的进行循环操作，id值小于等于10之前，将重复执行循环过程，代码如下：
DECLARE id INT DEFAULT 0;
add_loop: LOOP
SET id = id + 1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP add_ loop;
【例10.18】ITERATE语句示例，代码如下：
CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT DEFAULT 0;
my_loop: LOOP
SET p1= p1 + 1;
IF p1 < 10 THEN ITERATE my_loop;
ELSEIF p1 > 20 THEN LEAVE my_loop;
END IF;
SELECT 'p1 is between 10 and 20';
END LOOP my_loop;
END
【例10.19】REPEAT语句示例，id值小于等于10之前，将重复执行循环过程，代码如下：
DECLARE id INT DEFAULT 0;
REPEAT
SET id = id + 1;
UNTIL id >= 10
END REPEAT;
【例10.20】WHILE语句示例，id值小于等于10之前，将重复执行循环过程，代码如下：
DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;
【例10.21】定义名为CountProc的存储过程，然后调用这个存储过程，代码执行如下：
DELIMITER //
CREATE PROCEDURE CountProc (IN sid INT, OUT num INT)
BEGIN
SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
END //
DELIMITER ;
CALL CountProc (101, @num);
select @num;
【例10.22】定义存储函数CountProc，然后调用这个函数，代码如下：
mysql> DELIMITER //
mysql> CREATE FUNCTION CountProc (sid INT)
-> RETURNS INT
-> BEGIN
-> RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
-> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
调用存储函数：
mysql> SELECT Countproc(101);
【例10.23】SHOW STATUS语句示例，代码如下：
SHOW PROCEDURE STATUS LIKE 'C%'\G;
【例10.24】SHOW CREATE语句示例，代码如下：
SHOW CREATE FUNCTION test.CountProc \G;
【例10.25】从Routines表中查询名称为CountProc的存储函数的信息，代码如下：
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE = 'FUNCTION' \G
【例10.26】修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行，代码如下：
ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER ;
【例10.27】修改存储函数CountProc的定义。将读写权限改为READS SQL DATA，并加上注释信息“FIND NAME”，代码如下：
ALTER FUNCTION CountProc
READS SQL DATA
COMMENT 'FIND NAME' ;
【例10.28】删除存储过程和存储函数，代码如下：
DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;综合案例
步骤1：创建一个sch表，并且向sch表中插入表格中的数据
CREATE TABLE sch(id INT, name VARCHAR(50),glass VARCHAR(50));
INSERT INTO sch VALUE(1,’xiaoming’,’1班’), (1,’xiaojun’,’2班’);
步骤2：创建一个存储函数用来统计表sch中记录数
/*创建存储函数*/
DELIMITER //
CREATE FUNCTION count_sch()
RETURNS INT
RETURN (SELECT COUNT(*) FROM sch);
//
/*调用存储函数*/
SELECT count_sch() //
DELIMITER ;
步骤3：创建一个存储过程，通过调用存储函数的方法来获取表sch中记录数和sch表中id的和。
/*创建存储过程*/
DELIMITER //
CREATE PROCEDURE add_id(out count INT)
BEGIN
DECLARE itmp INT;
DECLARE cur_id CURSOR FOR SELECT id FROM sch;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
SELECT count_sch() INTO count;
SET @sum=0;
OPEN cur_id;
REPEAT
FETCH cur_id INTO itmp;
IF itmp<10
THEN SET @sum= @sum+itmp;
END IF;
UNTIL 0 END REPEAT;
CLOSE cur_id;
END //
/*查看存储过程调用后的结果*/
SELECT @a, @sum //
DELIMITER ;第十一章
例题
/*样例表*/
CREATE TABLE student
(
s_id INT,
name VARCHAR(40)
);
CREATE TABLE stu_info
(
s_id INT,
glass VARCHAR(40),
addr VARCHAR(90)
);
【例11.1】在t表格上创建一个名为view_t的视图，代码如下：
CREATE TABLE t (qty INT, price INT); /*创建基本表t*/
INSERT INTO t VALUES(3, 50); /*插入记录*/
CREATE VIEW view_t AS SELECT quantity, price, quantity *price FROM t; /*创建视图view_t*/
SELECT * FROM view_t;
【例11.2】在t表格上创建一个名为view_t2的视图，代码如下：
CREATE VIEW view_t2(qty, price, total ) AS SELECT quantity, price, quantity *price FROM t;
SELECT * FROM view_t2;
【例11.3】在表student和表stu_info上创建视图stu_glass，代码如下：
INSERT INTO student VALUES(1,'wanglin1'),(2,'gaoli'),(3,'zhanghai'); /*插入记录*/
INSERT INTO stu_info VALUES(1, 'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','shandong');
CREATE VIEW stu_glass (id,name, glass) AS SELECT student.s_id,student.name ,stu_info.glass FROM student ,stu_info WHERE student.s_id=stu_info.s_id;
SELECT * FROM stu_glass;
【例11.4】通过DESCRIBE语句查看视图view_t的定义，代码如下：
DESCRIBE view_t;
【例11.5】下面将通过一个例子来学习使用SHOW TABLE STATUS命令查看视图信息，代码如下：
SHOW TABLE STATUS LIKE 'view_t' \G;
SHOW TABLE STATUS LIKE 't' \G;
【例11.6】SHOW CREATE VIEW查看视图的详细定义，代码如下：
SHOW CREATE VIEW view_t \G;
【例11.7】在views表中查看视图的详细定义，代码如下：
SELECT * FROM information_schema.views \G;
【例11.8】修改视图view_t，代码如下：
DESC view_t;
CREATE OR REPLACE VIEW view_t AS SELECT * FROM t;
DESC view_t;
【例11.9】使用ALTER语句修改视图view_t，代码如下：
DESC view_t;
ALTER VIEW view_t AS SELECT quantity FROM t;
DESC view_t;
【例11.10】使用UPDATE语句更新视图view_t，代码如下：
SELECT * FROM view_t; /*查看更新之前的视图*/
SELECT * FROM t; /*查看更新之前的表*/
UPDATE view_t SET quantity=5; /*更新视图*/
SELECT * FROM t; /*查看更新之后的表*/
SELECT * FROM view_t; /*查看更新之后的视图*/
SELECT * FROM view_t2;
【例11.11】使用INSERT语句在基本表t中插入一条记录，代码如下：
INSERT INTO t VALUES (3,5);
SELECT * FROM t;
SELECT * FROM view_t2;
【例11.12】删除stu_glass视图，代码如下：
DROP VIEW IF EXISTS stu_glass;
SHOW CREATE VIEW stu_glass;
综合案例
步骤1：创建学生表stu，插入3条记录。
CREATE TABLE stu
(
s_id INT PRIMARY KEY,
s_name VARCHAR(20),
addr VARCHAR(50),
tel VARCHAR(50)
);
INSERT INTO stu
VALUES(1,'XiaoWang','Henan','0371-12345678'),
(2,'XiaoLi','Hebei','13889072345'),
(3,'XiaoTian','Henan','0371-12345670');
步骤2：创建报名表sign，插入3条记录。
CREATE TABLE sign
(
s_id INT PRIMARY KEY,
s_name VARCHAR(20),
s_sch VARCHAR(50),
s_sign_sch VARCHAR(50)
);
INSERT INTO sign
VALUES(1,'XiaoWang','Middle School1','Peking University'),
(2,'XiaoLi','Middle School2','Tsinghua University'),
(3,'XiaoTian','Middle School3','Tsinghua University');
步骤3：创建成绩表stu_mark，插入3条记录。
CREATE TABLE stu_mark (s_id INT PRIMARY KEY ,s_name VARCHAR(20) ,mark int );
INSERT INTO stu_mark VALUES(1,'XiaoWang',80),(2,'XiaoLi',71),(3,'XiaoTian',70);
步骤4：创建考上Peking University的学生的视图
CREATE VIEW beida (id,name,mark,sch)
AS SELECT stu_mark.s_id,stu_mark.s_name,stu_mark.mark, sign.s_sign_sch
FROM stu_mark ,sign
WHERE stu_mark.s_id=sign.s_id AND stu_mark.mark>=41 AND sign.s_sign_sch='Peking University';
步骤5：创建考上Tsinghua University的学生的视图
CREATE VIEW qinghua (id,name,mark,sch)
AS SELECT stu_mark.s_id, stu_mark.s_name, stu_mark.mark, sign.s_sign_sch
FROM stu_mark ,sign
WHERE stu_mark.s_id=sign.s_id AND stu_mark.mark>=40 AND sign.s_sign_sch='Tsinghua University';
步骤6：XiaoTian的成绩在录入的时候录入错误多录了50分，对其录入成绩进行更正。
UPDATE stu_mark SET mark = mark-50 WHERE stu_mark.s_name ='XiaoTian';
步骤7：查看更新过后视图和表的情况。
SELECT * FROM stu_mark;
SELECT * FROM qinghua;
SELECT * FROM beida;
步骤8：查看视图的创建信息。
SELECT * FROM information_schema.views\G
步骤9：删除创建的视图。
DROP VIEW beida;
DROP VIEW qinghua;第十二章
例题
【例12.1】创建一个单执行语句的触发器
/*创建account表*/
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
/*创建触发器*/
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
/*调用触发器*/
SET @sum =0;
INSERT INTO account VALUES(1,1.00), (2,2.00);
/*查看出发器调用后的结果*/
SELECT @sum;
【例12.2】创建一个包含多个执行语句的触发器
/*创建4个测试数据表*/
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
/*创建触发器*/
DELIMITER //
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
//
DELIMITER ;
/*分别向test3和test4表中插入测试数据*/
INSERT INTO test3 (a3)
VALUES (NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
/*向test1表中插入数据，激活触发器的调用事件调用触发器*/
INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);
/*查看触发器执行后的结果*/
SELECT * FROM test1;
SELECT * FROM test2;
SELECT * FROM test3;
SELECT * FROM test4;
【例12.3】通过SHOW TRIGGERS命令查看一个触发器
/*创建myname表*/
CREATE TABLE myname(
id int(11) DEFAULT NULL,
name char(20) DEFAULT NULL
);
/*创建触发器*/
CREATE TRIGGER trig_update AFTER UPDATE ON account
FOR EACH ROW INSERT INTO myname VALUES (1,'after update');
/*查看触发器信息*/
SHOW TRIGGERS \G;
【例12.4】通过SELECT命令查看触发器
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME= 'trig_update'\G
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS \G;
【例12.5】创建一个在account表更新之后，更新myname数据表的触发器
/*创建触发器*/
CREATE TRIGGER trig_insert AFTER INSERT ON account
FOR EACH ROW INSERT INTO myname VALUES (2,'after insert');
/*激活触发器*/
INSERT INTO account VALUES (1,1.00), (2,2.00);
/*查看触发器执行后的结果*/
SELECT * FROM myname;
【例12.6】删除一个触发器
DROP TRIGGER ins_sum;综合案例
步骤1：创建persons表
CREATE TABLE persons (name VARCHAR(40), num int);
步骤2：创建一个销售额表sales
CREATE TABLE sales (name VARCHAR(40), sum int);
步骤3：创建一个触发器
CREATE TRIGGER num_sum AFTER INSERT ON persons
FOR EACH ROW INSERT INTO sales VALUES (NEW.name,7*NEW.num);
步骤4：向persons表中插入记录
INSERT INTO persons VALUES ('xiaoxiao',20),('xiaohua',69);
SELECT * FROM persons;
SELECT *FROM sales;第十三章
例题
【例13.1】使用root用户登录到本地mysql服务器的test库中
mysql -uroot -p -hlocalhost test
【例13.2】使用root用户登录到本地mysql服务器的test库中，执行一条查询语句
mysql -uroot -p -hlocalhost test -e "DESC person;"
【例13.3】使用CREATE USER创建一个用户，用户名是jeffrey，密码是mypass，主机名是localhost
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
【例13.4】使用GRANT语句创建一个新的用户testUser，密码为testpwd。用户 testUser对所有的数据有查询和更新权限，并授于对所有数据表的SELECT和UPDATE权限
GRANT SELECT,UPDATE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testpwd'; /*创建账户并授予权限*/
SELECT Host,User,Select_priv,Update_priv, FROM mysql.user where user='testUser'; /*查看账户权限信息*/
【例13.5】使用INSERT创建一个新账户，其用户名称为customer1，主机名称为localhost，密码为customer1：
INSERT INTO user (Host,User,Password) VALUES('localhost','customer1',PASSWORD('customer1'));
【例13.6】使用DROP USER删除用户'jeffrey'@'localhost'
DROP USER 'jeffrey'@'localhost';
【例13.7】使用DELETE删除用户'customer1'@'localhost'
DELETE FROM mysql.user WHERE host='localhost' and user='customer1';
【例13.8】使用mysqladmin将root用户的密码修改为“rootpwd”
mysqladmin -u root -p password "rootpwd"
【例13.9】使用UPDATE语句将root用户的密码修改为“rootpwd2”：
UPDATE mysql.user set Password=password("rootpwd2")
WHERE User="root" and Host="localhost";
【例13.10】使用SET语句将root用户的密码修改为“rootpwd3”：
SET PASSWORD=password("rootpwd3");
【例13.11】使用SET语句将testUser用户的密码修改为“newpwd”：
SET PASSWORD FOR 'testUser'@'localhost'=password("newpwd");
【例13.12】使用UPDATE语句将testUser用户的密码修改为“newpwd2”：
UPDATE mysql.user set Password=PASSWORD("newpwd2")
WHERE User="testUser" and Host="localhost";
【例13.13】使用GRANT语句将testUser用户的密码修改为“newpwd3”：
GRANT USAGE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'newpwd3';
【例13.14】testUser用户使用SET语句将自身的密码修改为“newpwd4”：
SET PASSWORD = PASSWORD("newpwd4");
【例13.15】使用GRANT语句创建一个新的用户grantUser，密码为‘grantpwd’。用户grantUser对所有的数据有查询、插入权限。并授于GRANT权限
GRANT SELECT,INSERT ON *.* TO 'grantUser'@'localhost'
IDENTIFIED BY 'grantpwd'
WITH GRANT OPTION;
【例13.16】使用REVOKE语句取消用户testUser的更新权限
REVOKE UPDATE ON *.* FROM 'testUser'@'localhost';
【例13.17】使用SHOW GRANTS语句查询用户testUser的权限信息
SHOW GRANTS FOR 'testUser'@'localhost';
综合案例
步骤1.打开MySQL客户端工具，输入登录命令，登录MySQL：
/*使用root用户登录mysql*/
mysql -u root -p
步骤2.将选择mysql数据库为当前数据库。
use mysql;
步骤3.创建新账户，用户名称为newAdmin，允许其从本地主机访问MySQL。
GRANT SELECT, UPDATE(id, name, age)
ON test.person
TO 'newAdmin'@'localhost' IDENTIFIED BY 'pw1'
WITH MAX_CONNECTIONS_PER_HOUR 30;
步骤4.分别从user表中查看新账户的账户信息，从tables_priv和columns_priv表中查看权限信息。
/* 查看user表中账户信息*/
SELECT host, user, select_priv, update_priv FROM user WHERE user='newAdmin';
/*查看tables_priv表中权限信息*/
SELECT host, db, user, table_name, table_priv, column_priv
FROM tables_priv WHERE user='newAdmin';
/*查看columns_priv表中权限信息*/
SELECT host, db, user, table_name, column_name, column_priv
FROM columns_priv WHERE user='newAdmin';
步骤5.使用SHOW GRANTS语句查看newAdmin的权限信息
SHOW GRANTS FOR 'newAdmin'@'localhost';
步骤6.使用newAdmin用户登录MySQL
/*退出命令*/
exit
/*使用newAdmin 用户登录mysql*/
mysql -u newAdmin -p
步骤7.使用newAdmin用户查看test数据库中person表中的数据
SELECT * FROM test.person LIMIT 5;
步骤8.使用newAdmin用户向person表中插入一条新记录，查看语句执行结果。
INSERT INTO test.person(name, age,info) VALUES('gaga', 30);
步骤9.退出当前登录，使用root用户重新登录，收回newAdmin账户的权限。
/*退出命令*/
exit
/*使用root用户登录mysql*/
mysql -u root -p
REVOKE SELECT, UPDATE ON test.person FROM 'newAdmin'@'localhost';
步骤10.删除newAdmin的账户信息。
删除指定账户，可以使用DROP USER语句，输入如下：
DROP USER 'newAdmin'@'localhost';
第十四章
例题
/***************************样例表***************************/
CREATE DATABASE booksDB;
user booksDB;
CREATE TABLE books
(
bk_id INT NOT NULL PRIMARY KEY,
bk_title VARCHAR(50) NOT NULL,
copyright YEAR NOT NULL
);
INSERT INTO books
VALUES (11078, 'Learning MySQL', 2010),
(11033, 'Study Html', 2011),
(11035, 'How to use php', 2003),
(11072, 'Teach youself javascript', 2005),
(11028, 'Learing C++', 2005),
(11069, 'MySQL professional', 2009),
(11026, 'Guide to MySQL 5.5', 2008),
(11041, 'Inside VC++', 2011);
CREATE TABLE authors
(
auth_id INT NOT NULL PRIMARY KEY,
auth_name VARCHAR(20),
auth_gender CHAR(1)
);
INSERT INTO authors
VALUES (1001, 'WriterX' ,'f'),
(1002, 'WriterA' ,'f'),
(1003, 'WriterB' ,'m'),
(1004, 'WriterC' ,'f'),
(1011, 'WriterD' ,'f'),
(1012, 'WriterE' ,'m'),
(1013, 'WriterF' ,'m'),
(1014, 'WriterG' ,'f'),
(1015, 'WriterH' ,'f');
CREATE TABLE authorbook
(
auth_id INT NOT NULL,
bk_id INT NOT NULL,
PRIMARY KEY (auth_id, bk_id),
FOREIGN KEY (auth_id) REFERENCES authors (auth_id),
FOREIGN KEY (bk_id) REFERENCES books (bk_id)
);
INSERT INTO authorbook
VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);
/***************************样例表***************************/
【例14.1】使用mysqldump命令备份数据库中的所有表
mysqldump -u root -p booksdb > C:/backup/booksdb_20110101.sql
【例14.2】备份booksDB数据库中的books表
mysqldump -u root -p booksDB books > C:/backup/books_20110101.sql
【例14.3】使用mysqldump备份booksDB和test数据库
mysqldump -u root -p --databases booksDB test> C:\backup\books_testDB_20110101.sql
【例14.4】使用mysqldump备份服务器中的所有数据库
mysqldump -u root -p --all-databases > C:/backup/alldbinMySQL.sql
【例14.5】使用mysqlhotcopy备份test数据库到/usr/backup目录下
mysqlhotcopy -u root -p test /usr/backup
【例14.6】使用mysql命令将C:\backup\booksdb_20110101.sql文件中的备份导入到数据库中
mysql -u root-p booksDB < C:/backup/booksdb_20110101.sql
【例14.7】使用root用户登录到服务器，然后使用souce导入本地的备份文件booksdb_20110101.sql
use booksdb;
source C:/backup/booksdb_20110101.sql
【例14.8】从mysqlhotcopy拷贝的备份恢复数据库
cp �CR /usr/backup/test usr/local/mysql/data
【例14.9】将www.abc.com主机上的MySQL数据库全部迁移到www.bcd.com主机上。在www.abc.com主机上执行的命令如下：
mysqldump -h www.bac.com -uroot -ppassword dbname |
mysql -hwww.bcd.com -uroot -ppassword
【例14.10】使用SELECT...INTO OUTFILE将test数据库中的person表中的记录导出到文本文件
SELECT * FROM test.person INTO OUTFILE "C:/person0.txt";
【例14.11】使用SELECT...INTO OUTFILE将test数据库中的person表中的记录导出到文本文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号‘,’间隔，所有字段值用双引号括起来，定义转义字符定义为单引号‘\’’
SELECT * FROM test.person INTO OUTFILE "C:/person1.txt"
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"'
ESCAPED BY '\''
LINES
TERMINATED BY '\r\n';
【例14.12】使用SELECT...INTO OUTFILE将test数据库中的person表中的记录导出到文本文件，使用LINES选项，要求每行记录以字符串“> ”开始，以“<end>”字符串结尾
SELECT * FROM test.person INTO OUTFILE "C:/person2.txt"
LINES
STARTING BY '> '
TERMINATED BY '<end>';
SELECT * FROM test.person INTO OUTFILE "C:/person2.txt"
LINES
STARTING BY '> '
TERMINATED BY '<end>\r\n';
【例14.13】使用mysqldump将test数据库中的person表中的记录导出到文本文件
mysqldump -T C:/ test person -u root -p
【例14.14】使用mysqldump命令将test数据库中的person表中的记录导出到文本文件，使用FIELDS选项，要求字段之间使用逗号‘,’间隔，所有字符类型字段值用双引号括起来，定义转义字符定义为问号‘?’，每行记录以回车换行符“\r\n”结尾
mysqldump -T C:\backup test person -u root -p --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n
【例14.15】使用mysql语句导出test数据库中person表中的记录到文本文件
mysql -u root -p --execute="SELECT * FROM person;" test > C:\person3.txt
【例14.16】使用mysql命令导出test数据库中person表中的记录到文本文件，使用--vertical参数显示结果
mysql -u root -p --vertical --execute="SELECT * FROM person;" test > C:\person4.txt
【例14.17】使用mysql命令导出test数据库中person表中的记录到html文件
mysql -u root -p --html --execute="SELECT * FROM person;" test > C:\person5.html
【例14.18】使用mysql命令导出test数据库中person表中的记录到xml文件
mysql -u root -p --xml --execute="SELECT * FROM person;" test > C:\person6.xml
【例14.19】使用LOAD DATA命令将C:\person0.txt文件中的数据导入到test数据库中的person表
LOAD DATA INFILE 'C:/person0.txt' INTO TABLE test.person;
【例14.20】使用LOAD DATA命令将C:\person1.txt文件中的数据导入到test数据库中的person表，使用FIELDS选项和LINES选项，要求字段之间使用逗号‘,’间隔，所有字段值用双引号括起来，定义转义字符定义为单引号‘\’’
LOAD DATA INFILE 'C:/person1.txt' INTO TABLE test.person
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"'
ESCAPED BY '\''
LINES
TERMINATED BY '\r\n';
【例14.21】使用mysqlimport命令将C:\backup目录下的person.txt文件内容导入到test数据库中，字段之间使用逗号‘,’间隔，字符类型字段值用双引号括起来，定义转义字符定义为问号‘?’，每行记录以回车换行符“\r\n”结尾
mysqlimport -u root -p test C:/backup/person.txt --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n
综合案例
步骤1：使用mysqldump命令将suppliers表备份到文件C:\bktestdir\suppliers_bk.sql。
mysqldump -u root -p test suppliers > C:\bktestdir\suppliers_bk.sql
步骤2：使用mysql命令还原suppliers表到test数据库中
DELETE FROM suppliers;
source C:/bktestdir/suppliers_bk.sql;
步骤3：使用SELECT⋯ INTO OUTFILE语句导出suppliers表中的记录，导出文件位于目录C:\bktestdir下，名称为suppliers_out.txt
SELECT * FROM test.suppliers INTO OUTFILE "C:/bktestdir/suppliers_out.txt"
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"'
LINES
STARTING BY '<'
TERMINATED BY '>\r\n';
步骤4：使用LOAD DATA INFILE语句导入suppliers_out.txt数据到suppliers表。
LOAD DATA INFILE 'C:/bktestdir/suppliers_out.txt' INTO TABLE test.suppliers
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"'
LINES
STARTING BY '<'
TERMINATED BY '>\r\n';
步骤5：使用musqldump命令将suppliers表中的记录导出到文件C:\bktestdir\suppliers_html.html。
mysql -u root -p --html --execute="SELECT * FROM suppliers;" test > C:/bktestdir/suppliers_html.html
第十五章
综合案例
步骤1：设置启动二进制日志，并指定二进制日志文件名为binlog.log
打开my.ini（或者my.cnf）在[mysqld]组下添加如下内容：
[mysqld]
log-bin=binlog.log
net stop mysql
net start mysql
步骤2：将二进制日志文件存储路径改为d:/log
打开my.ini（或者my.cnf）在[mysqld]组下修改如下内容：
[mysqld]
log-bin=d:/log/binlog.log
net stop mysql
net start mysql
步骤3：查看flush logs对二进制日志的影响。
mysqladmin flush-logs
FLUSH LOGS;
步骤4：查看二进制日志。
mysqlbinlog d:\log\binlog.000001
步骤5：使用二进制日志还原数据
USE test
CREATE TABLE member(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)); /*创建数据库*/
INSERT INTO member VALUES(NULL, 'Playboy1');
INSERT INTO member VALUES(NULL, 'Playboy2');
SET SQL_LOG_BIN=0; /*暂停二进制日志功能*/
drop table member; /*删除数据表*/
SELECT * FROM member;
SET SQL_LOG_BIN=1; /*打开二进制日志功能*/
mysqlbinlog d:/log/binlog.0000001 | mysql -u root -p /*还原数据表*/
SELECT * FROM member;
步骤6：删除二进制日志
RESET MASTER;
步骤7：暂停和重新启动二进制日志
SET sql_log_bin = 0; /*暂停日志*/
SET sql_log_bin =1; /*打开日志*/
步骤8：设置启动错误日志。
[mysqld]
log-error
步骤9：设置错误日志的文件为d:/log/error_log.err
[mysqld]
log-error=d:/log/error_log.err
步骤10：查看错误日志。
略
步骤11：设置启动通用查询日志，并且设置通用查询日志文件为：d:/log/ gneral_query.log。
[mysqld]
log =d:/log/general_query.log
步骤13：设置启动慢查询日志，设置慢查询日志的文件为d:/log/ slow_query.log，并设置记录查询时间超过3秒的语句。
[mysqld]
log-slow-queries=d:/log/slow_query.log
long_query_time=3
步骤14：查看慢查询日志。
略
第十六章
例题
【例16.1】使用EXPLAIN语句来分析一个查询语句
EXPLAIN SELECT * FROM fruits;
【例16.2】下面是查询语句中不使用索引和使用索引的对比。
EXPLAIN SELECT * FROM fruits WHERE f_name='apple'; /*分析未使用索引时的查询情况*/
CREATE INDEX index_name ON fruits(f_name); /*创建索引*/
EXPLAIN SELECT * FROM fruits WHERE f_name='apple'; /*分析使用索引时的查询情况*/
【例16.3】查询语句中使用LIKE关键字，并且匹配的字符串中含有‘%’符，EXPLAIN语句执行如下：
EXPLAIN SELECT * FROM fruits WHERE f_name like '%x';
EXPLAIN SELECT * FROM fruits WHERE f_name like 'x%';
【例16.4】本例在表fruits中f_id、f_price字段创建多列索引，验证多列索引的使用情况。
CREATE INDEX index_id_price ON fruits(f_id, f_price);
EXPLAIN SELECT * FROM fruits WHERE f_id='l2';
EXPLAIN SELECT * FROM fruits WHERE f_price=5.2;
【例16.5】查询语句使用OR关键字的情况：
EXPLAIN SELECT * FROM fruits WHERE f_name='apple' or s_id=101 \G;
EXPLAIN SELECT * FROM fruits WHERE f_name='apple' or f_id='l2' \G;
【例16.6】下面的会员表（members）主要用来存储会员登录认证信息，
CREATE TABLE members (
Id int(11) NOT NULL AUTO_INCREMENT,
username varchar(255) DEFAULT NULL ,
password varchar(255) DEFAULT NULL ,
last_login_time datetime DEFAULT NULL ,
last_login_ip varchar(255) DEFAULT NULL ,
PRIMARY KEY (Id)
) ;
CREATE TABLE members_detail (
member_id int(11) NOT NULL DEFAULT 0,
address varchar(255) DEFAULT NULL ,
telephone varchar(16) DEFAULT NULL ,
description text
) ;
SELECT * FROM members LEFT JOIN members_detail ON members.id=members_detail.member_id
【例16.7】会员信息表和会员组信息表如下：
CREATE TABLE vip(
Id int(11) NOT NULL AUTO_INCREMENT,
username varchar(255) DEFAULT NULL,
password varchar(255) DEFAULT NULL,
groupId INT(11) DEFAULT 0,
PRIMARY KEY (Id)
) ;
CREATE TABLE vip_group (
Id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
remark varchar(255) DEFAULT NULL,
PRIMARY KEY (Id)
) ;
CREATE TABLE temp_vip (
Id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(255) DEFAULT NULL,
group_name varchar(255) DEFAULT NULL,
group_remark varchar(255) DEFAULT NULL,
PRIMARY KEY (Id)
);
INSERT INTO temp_vip(user_name, group_name, group_remark)
SELECT v.username,g.name,g.remark
FROM vip as v ,vip_group as g
WHERE v.groupId=g.Id;
【例16.8】使用ANALYZE TABLE来分析message表，执行的语句及结果如下：
ANALYZE TABLE fruits;
综合案例
步骤1：分析查询语句，理解索引对查询速度的影响。
1.使用分析查询语句“SELECT * FROM fruits WHERE f_name='banana';”，执行的语句及执行结果如下：
EXPLAIN SELECT * FROM fruits WHERE f_name='banana';
2.使用分析查询语句“SELECT * FROM fruits WHERE f_name like '%na'”，执行的语句及执行结果如下：
EXPLAIN SELECT * FROM fruits WHERE f_name like '%na';
3．使用EXPLAIN分析查询语句下面执行“SELECT * FROM fruits WHERE f_name like 'ba%';”语句如下：
EXPLAIN SELECT * FROM message WHERE title like ' ba%';
步骤2：练习分析表、检查表、优化表。
1．使用ANALYZE TABLE语句分析fruits表，执行的语句及结果如下：
ANALYZE TABLE fruits;
2．使用CHECK TABLE语句检查表fruits，执行的语句及结果如下：
CHECK TABLE fruits;
本文地址：https://blog.csdn.net/a617976080/article/details/85916771