第一章 数据库的建立 1、mysql>PROMPT 标识符名 //修改标识符 常用：mysql>PROMPT u@h d u:当前用户 h:服务器名称 d:当前打开的数据库 MySQL语法规范: ·关键字与函数全部大写 ·数据库名、表名全部小写 ·SQL语句必须以‘;’结尾 2、mysql>USE x; //使用x数据库 3、mysql>CREATE DATABASE t1 CHARACTER SET = utf8;//创建数据库t1,编码为utf8 4、mysql>SHOW DATABASES; //显示所有数据库 5、mysql>SHOW CREATE DATABASE t1;//显示数据库t1的属性 6、mysql>ALTER DATABASE t1 CHARACTER SET = utf8;//将数据库t1的编码方式改为utf8 7、mysql>DROP DATABASE t1;//将数据库t1删除 8、mysql>SHOW WARNINGS;//显示警告 第二章 关系表的建立 1、mysql>SELECT DATABASE();//查询当前所打开的数据库 2、mysql>SHOW TABLES;//显示当前所打开的数据库中的表格 3、mysql>SHOW TABLES FROM t1;//显示t1数据库中的表 4、mysql>SHOW COLUMNS FROM tb1;//显示关系表t1的属性,t1必须在当前所打开的数据库中 5、mysql>CREATE TABLE tb1( ->username VARCHAR(20), ->userage TINYINT UNSIGNED, ->salary FLOAT UNSIGNED);//创建一个关系表格 6、mysql>INSERT tb1 VALUES('Tom',25,7500,50);//在tb1中插入一个实例 7、mysql>INSERT tb1(username,salary) VALUES('John',6900.00);//实例部分赋值 8、mysql>SELECT * FROM tb1; //完整显示tb1; 9、mysql>SELECT * ->FROM tb1 ->WHERE username = 'Tom';//显示表tb1中Tom的全部信息 10、id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY //AUTO_INCREMENT 插入实例时id增1，且必须为PRIMARY KEY 11、sex enum('男','女','保密') DEFAULT '保密' //若sex为赋值，则默认为'保密' 12、一个较为完整操作的实例: mysql>CREATE TABLE tb1( ->id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ->name VARCHAR(20) UNIQUE NOT NULL, ->class SMALLINT UNSIGNED DEFAULT '9011', ->sex enum('f','m','s') DEFAULT 's'); 13、mysql>SHOW CREATE TABLE tb1; //显示关系表的属性 14、mysql>DROP TABLE tb1;//将表tb1删除 第三章 约束与修改 1、参照约束方式 ①.CASCADE //父表中删除某行时子表相参照的值的行也删除 ②.SET NULL//父表中删除某行时子表相参照的列的值设为NULL 注意：参照属性与被参照属性的结构必须相同 实例: mysql>CREATE TABLE provinces( ->pname VARCHAR(30) UNIQUE NOT NULL, ->pid SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY); mysql>CREATE TABLE client( ->cname VARCHAR(30) UNIQUE NOT NULL, ->cid SMALLINT UNSIGNED, ->FOREIGN KEY (cid) REFERENCES provinces(pid) ON DELETE CASCADE); 2、mysql>ALTER TABLE tb1 ADD age SMALLINT UNSIGNED;//在数据表中加一个新属性 3、mysql>ALTER TABLE tb1 ADD class SMALLINT AFTER age;//在age插入一个属性 将AFTER age改为FIRST则在表的最前面插入一属性 4、mysql>ALTER TABLE tb1 ADD( ->major VARCHAR(32) NOT NOLL, ->score FLOAT);//插入多列，位置只能位于最后 5、mysql>ALTER TABLE tb1 DROP score,DROP major;//删除多列 6、mysql>ALTER TABLE tb1 ADD PRIMARY KEY(id);//添加主键约束 mysql>ALTER TABLE tb1 DROP PRIMARY KEY;//删除主键约束 7、mysql>ALTER TABLE tb1 ADD UNIQUE(name);//添加唯一约束 8、mysql>ALTER TABLE tb1 ADD FOREIGN KEY(id) REFERENCES provinces(pid); //添加参照约束 9、mysql>ALTER TABLE tb1 ALTER num SET DEFAULT 15;//添加默认约束 10、mysql>ALTER TABLE tb1 MODIFY id INT; //将id原来SMALLINT的结构改为INT, 修改结构时必须将其他定义了的结构也列上 //例如：id若定义了NOT NULL,则上语句改为: mysql>ALTER TABLE tb1 MODIFY id INT NOT NULL; 11、mysql>ALTER TABLE tb1 CHANGE id i_d INT;//将id的名称改变，其定义了的 约束必须一个不少的加上去 12、mysql>ALTER TABLE tb1 RENAME tb2;//将表更名 13、mysql>CREATE TABLE user( ->ID INT, ->name CHAR(10), ->CONSTRAINT C1 PRIMARY KEY(ID,name));//将属性组设为主键 第四章 操作记录 创建一个user数据表 mysql>CREATE TABLE user( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(20) NOT NULL, -> password VARCHAR(32) NOT NULL, -> age TINYINT DEFAULT 10, -> sex BOOLEAN); //alter table user auto_increment = 10; 自增号码将从10开始 1、插入记录 ①mysql>INSERT user VALUES(,,,) //若user不跟属性，则必须所有属性均赋值 // DEFAULT属性可用DEFAULT 代替，AUTO_INCREMENT 属性可用NULL,DEFAULT代替 ②mysql>INSERT user SET 属性名=属性值,,,,//只能一次插入一条记录 2、插入多条记录 mysql>INSERT user VALUES(,,,),(,,,)//插入多条记录 3、更新记录 mysql>UPDATE user SET age=20;//将表中所有记录的age属性均设为10 4、同时更新多条记录 mysql>UPDATE user SET age = age - id, sex = 0;//将所有记录的年龄减去其id, 且将sex设为0 5、mysql>UPDATE user SET sex = 1 WHERE id % 2 = 0;//将id为偶数的记录sex设为0 6、删除记录 mysql>DELETE FROM user WHERE id = 5;//将id号为 5 的记录删除 7、查找记录 mysql>SELECT id AS ID FROM user;//将结果集中属性id改名为ID 8、mysql>SELECT name, id FROM user;//结果集中第一列显示name, 第二列显示id; 9、mysql>SELECT * FROM user ORDER BY age DESC,id ASC; //显示表中的全部结果以age降序，若age相同则以id升序排列; 10、mysql>SELECT * FROM user LIMIT 2,2; //从上到下,从0开始数，返回以第2个开始的2条记录 11、mysql>CREATE TABLE tb( ->id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, ->name VARCHAR(20)); mysql>INSERT tb(name) SELECT name FROM user WHERE id > 2; //将user中id>2的记录插入tb中 12、mysql>CREATE TABLE date_test( ->day_ DATE, ->time_ DATETIME); mysql>INSERT date_test VALUES('2013-12-3','2013-12-3 11:30:30'); //DATE 及 DATETIME 类型的插入方式 13、mysql>UPDATE tb1,tb2 SET tb1.score = tb2.score WHERE tb1.ID=tb2.ID; //用tb2的数据对tb1进行更新 第五章 子查询与连接 1、mysql>CREATE TABLE class( ->ID SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, ->monitor VARCHAR(32) NOT NULL UNIQUE ->); mysql>CREATE TABLE student( ->classNum SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, ->name VARCHAR(32) NOT NULL, ->age SMALLITNT UNSIGNED DEFAULT 20 ->); mysql>SELECT monitor,name,age ->FROM class INNER JOIN student //INNER JOIN内部连接、LEFT JOIN左连接、RIGHT JOIN右连接 ->ON ID = classNum; 第六章 字符串函数 1、字符串连接函数 ①CONCAT mysql>SELECT CONCAT('imooc','-','MySQL');将'imooc'、'-'、'MySQL'连接 mysql>SELECT CONCAT(first_name,last_name) AS FULLNAME FROM Name; ②CONCAT_WS('分隔符'，'字符串','',,,); mysql>SELECT CONCAT_WS('|','A','B','C'); 2、格式转换函数,将实数转化为字符串 mysql>SELECT FORMAT(实数,保留小数点位数); 例如 mysql>SELECT FORMAT(212003.89,1) 结果为 212,003.9 //千分位表示法 3、字符串大小写转换函数 mysql>SELECT LOWER('MySQL'); //结果为 mysql mysql>SELECT UPPER('Mysql'); //结果为 mysql 4、取头尾函数 mysql>SELECT LEFT('MySQL',2);//结果为 My mysql>SELECT RIGHT('MySQL',2);//结果为 QL 5、求字符串长度 mysql>SELECT LENGTH('MySQL'); //结果为5 6、去前导、后续函数 mysql>SELECT TRIM(LEADING ' ' FROM ' MySQL '); //去前导' ',结果为'MySQL '; mysql>SELECT TRIM(TRAILING ' ' FROM ' MySQL '); //去后续' ',结果为' MySQL'; mysql>SELECT TRIM(BOTH ' ' FROM ' MySQL ');//两者均去,结果为'MySQL' 7、字符串替代函数 mysql>SELECT REPLACE('??MySQL???','?','*');//将字符串中的？改为 * 8、取子串函数 mysql>SELECT SUBSTRING('MySQL',1,2);//从第1位往后取2个,结果为My 9、字符串比较函数 '%'代表任意个字符, '_'代表任意一个字符 mysql>SELECT last_name FROM Name WHERE last_name LIKE 'L%'; //查找以'L'开头的last_name mysql>SELECT last_name FROM Name WHERE last_name LIKE '%1%%' ESCAPE '1'; //查找含有'%'的last_name 第七章 数值函数 1、CEIL 向上取正 mysql>SELECT CEIL(3.01);//结果为 4; 2、FLOOR 向下取整 mysql>SELECT FLOOR(3.99);//结果为 3; 3、DIV 整数取整 mysql>SELECT 3 DIV 4; //结果为 0；3/4 为 0.75; 4、MOD 取余数 等价于 % mysql>SELECT 5 MOD 2;//小数亦可 5、POWER 指幂函数 mysql>SELECT POWER(2,3);//结果为8 6、ROUND 四舍五入函数 mysql>SELECT ROUND(3.56,1);//保留小数点后1位,结果为3.6 7、TRUNCATE 非四舍五入 mysql>SELECT TRUNCATE(125.89,1)//保留小数点后1位，结果为125.8,不做四舍五入 第八章 日期时间函数 1、SELECT NOW(); 显示当前日期和时间 2、SELECT CURDATE(); 显示当前日期 3、SELECT CURTIME(); 显示当前时间 4、SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY); //得到2014-3-12加上365天的结果 DAY 可以换为 YEAR,WEEK,MONTH //若将365改为负数则为减去 5、SELECT DATEDIFF('2012-3-12','2011-3-12'); //求2012-3-12 与 2011-3-12 相差的天数 6、SELECT DATE_FORMAT('2012-3-12','%m/%d/%Y');//结果为03/12/2012; //%m,%d,%Y位置可以互换 第八章 密码函数 1、SELECT MD5('student'); //给字符串'student'加密 2、修改登录密码 mysql>SET PASSWARD = PASSWARD('%新密码%'); 第九章 自定义函数 1、创建实例 求两个数的平均值 mysql>CREATE FUNCTION f1(num1 INT, num2 INT)//参数 ->RETURNS FLOAT //返回值类型 ->RETURN (num1+num2)/2; 2、调用函数 mysql>SELECT f1(12,21); 3、mysql>DELIMITER % //将%替代';'作为结束语句 4、函数体包含多语句 mysql>CREATE FUNCTION f1(username VARCHAR(32)) ->RETURNS INT ->BEGIN 符合结构必须包含在BEGIN...END 中 ->INSERT user VALUES(username); ->RETURN LAST_INSERT_ID(); 返回最后插入记录的ID ->END// 5、mysql>DROP FUNCTION f1;//将函数f1删除 6、mysql>SHOW FUNCTION STATUSG 显示数据库中的函数信息 7、每个函数最多有一个返回值 第九章 存储过程 1、具体示例 mysql>DELIMITER % 第一步 mysql>CREATE PROCEDURE removeuser(IN id INT UNSIGNED) ->BEGIN ->DELETE FROM user WHERE user.ID = id; ->END% mysql>CALL removeuser(3); //将id为3的记录删除 CALL调用过程体 2、mysql>DROP PROCEDURE removeuser; //将过程体删除 3、存储体可带出多个返回值 mysql>DELIMITER % mysql>CREATE PROCEDURE removeuser(IN age INT,OUT des INT,OUT res INT)//OUT 定义的变量将返回值带出 ->BEGIN ->DELETE FROM users WHERE users.age = age; ->SELECT ROW_COUNT() INTO des; //ROW_COUNT 返回由于上句操作，变动的记录个数 ->SELECT COUNT(*) FROM users INTO res; ->EDN// mysql>DELIMITER ; mysql>CALL removeuser(21,@des,@res); //@不可少 mysql>SELECT @des,@res; //@不可少 4、实例 用存储过程访问表中前 几个 记录 DELIMITER % CREATE PROCEDURE tt(p INT UNSIGNED) BEGIN DECLARE n INT DEFAULT 0; DECLARE i INT DEFAULT 0; SELECT COUNT(*) INTO n FROM 销售主表; SET i = n DIV p; SELECT * FROM 销售主表 ORDER BY 订单金额 DESC LIMIT 0,i; END% CALL tt(2)%//查询前50%的记录 5、while语句范例 DELIMITER % CREATE PROCEDURE tt() BEGIN DECLARE x INT DEFAULT 0; WHILE x <= 5 DO SELECT x; SET x = x + 1; END WHILE; END% 6、if语句范例 DELIMITER % CREATE PROCEDURE tt(score FLOAT) BEGIN IF score > 90 THEN SELECT 'A' AS class; ELSEIF score <= 90 AND score >= 60 THEN SELECT 'B' AS class;//注意else与if之间无' ' ELSE SELECT 'C' AS class; END IF; END% 第十章 存储引擎 1、MyISAM 存储限制达256TB，支持索引、表级锁定、数据压缩 2、InnoDB 存储限制为64TB，支持事务和索引，行锁定