MySQL5.5从零开始学例题和综合案例

第一章

例题

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