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,编码为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 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，支持事务和索引，行锁定