JavaWeb学习笔记-MySQL数据库基础知识

这是我在学习MySQL数据库时整理的基础知识，现与大家分享。

第一章 数据库的建立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,编码为utf84、mysql>SHOW DATABASES;        //显示所有数据库5、mysql>SHOW CREATE DATABASE t1;//显示数据库t1的属性6、mysql>ALTER DATABASE t1 CHARACTER SET = utf8;//将数据库t1的编码方式改为utf87、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 KEY11、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属性均设为104、同时更新多条记录   mysql>UPDATE user SET age = age - id, sex = 0;//将所有记录的年龄减去其id,    且将sex设为05、mysql>UPDATE user SET sex = 1 WHERE id % 2 = 0;//将id为偶数的记录sex设为06、删除记录   mysql>DELETE FROM user WHERE id = 5;//将id号为 5 的记录删除7、查找记录   mysql>SELECT id AS ID FROM user;//将结果集中属性id改名为ID8、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'); //结果为 mysql4、取头尾函数     mysql>SELECT LEFT('MySQL',2);//结果为 My     mysql>SELECT RIGHT('MySQL',2);//结果为 QL5、求字符串长度     mysql>SELECT LENGTH('MySQL'); //结果为56、去前导、后续函数     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个,结果为My9、字符串比较函数 '%'代表任意个字符, '_'代表任意一个字符     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);//结果为86、ROUND 四舍五入函数   mysql>SELECT ROUND(3.56,1);//保留小数点后1位,结果为3.67、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 STATUS\G 显示数据库中的函数信息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，支持事务和索引，行锁定