MySQL 学习笔记

create database 数据库名称;

-- 判断student数据库存在就删除
drop database if exists student;
-- 重新创建student数据库
create database student;
-- 创建数据库后，如果需要使用数据库，使用：”use 数据库名；”如：use student;

drop database 数据库名称；

Eg： drop database user; -- 删除了名叫“user”的数据库；
drop database if exists user; -- 也可以用if exists做判断，避免发生错误；

create table 表名(
  字段名称1 数据类型 [ null / not null ] [ DEFAULT 默认值 ] [ AUTO_INCREMENT ] [ PRIMARY_KEY ] [ COMMENT ‘注释内容’ ] ,
......
  字段名称N 数据类型 [ null / not null ] [ DEFAULT 默认值 ] [ AUTO_INCREMENT ] [ PRIMARY_KEY ] [ COMMENT ‘注释内容’ ]
);

Eg:
drop table if exists teacher ; -- 首先判断是否存在teacher表，如果存在就删除
create table teacher(
  id int not null auto_increment primary key ,
  name varchar(20) not null
);
-- 创建了teacher表，里面有int、 name两个字段, 其中id是主键，非空，自增
-- 创建数据库后，如果需要使用数据库，使用：”use 数据库名；”如：use teacher;

drop table 表名;

Eg: drop table teacher;
或者删除多个表格： drop table teacher, student;

-- 完全复制的格式：
create table 表02 select * from 表01;
Eg: create table teacher02 select * from teacher;
-- 复制一张和teacher表完全一样的teacher02表；

-- 复制指定列的格式：
create table 表02 select 字段a，字段b，...... from 表01;
Eg: create table teacher02 select id, name from teacher;
-- 复制一张teacher02表，里面包含teacher表里的id，name；

-- 完全复制表结构（包括键key），但是不复制里面的内容，格式是：
create table 表02 like 表01;
Eg: create table teacher02 like teacher;
-- 复制一张teacher02表，其结构和teacher表相同，但没有teacher里的内容；

1. 增加字段：add
Eg: alter table teacher add age int null; -- 添加age列，int类型，可以为null；

2. 删除字段：drop
Eg: alter table teacher drop age; -- 删除age列；

3.修改字段：change
Eg: alter table teacher change name teacher_name varchar(30) not null;
-- 修改name列为teacher_name，varchar类型最大30个字符，不能为null；

4. 增加主键：add primary key()
Eg: alter table teacher add primary key(id); -- 设置id为主键（id是已有字段）；

5. 修改表名：rename to
Eg: alter table teacher rename to user; -- 把teacher表改名为user；

格式：alter table 表名 add [constraint] [约束名称] 约束类型（约束内容[可多个字段]）;

主键约束:
alter table user add constraint PK_user_id primary key(id);
-- 把user表的id添加主键约束，约束名称是PK_user_id。（PK是主键的简写）

设置联合主键的方式：
alter table user add primary key(id，name);
-- 设置了联合主键id和name； 补充： 在创建表的时候也可以直接设置主键约束。

唯一约束：alter table 表名 add [constraint] unique(id);

默认约束：alter table 表名 alter column列名 set DEFAULT 默认值;
-- 默认值可以为常量或者为空（null）；

检查约束：（与SQL Server中的检查约束有语法区别）可以使用**枚举**。
Eg：create table student ( ...... sex **enum**(‘男’, ‘女’) not
null -- 学员性别 );
-- 默认值可以为常量或者为空（null）；

外键约束： 格式：alter table 表名 add constraint 约束名称 约束类型;
(外键约束简称为“FK”)
Eg：alter table student add constraint FK_student_class_classid foreign key (classid) references class(classid);
-- 为student表所在的班级字段classid设置了外键约束。

创建时设置外键：
create table student (
......
classid int not null, foreign key(classid) references class(classid)
-- 设置名称为classid的外键，指向class表的classid；
);

设置级联操作：
restrict和no action表示在子表有关联记录的情况下父表不能更新；
cascade 表示父表在更新或者删除时，同时更新或删除子表对应的记录；
set null 表示父表在更新或者删除时，子表对应的字段设置为null；

Eg：
(1).alter table student add constraint FK_student_class_classid foreign key (classid) references class(classid) on delete set null;
-- 如：删除class表中classid=1对应的字段时，student表中的classid字段的值设置为null

(2).alter table student add constraint FK_student_class_classid foreign key (classid) references class(classid) on delete set cascade;
-- 如：删除class表中classid=1对应的字段时，同时删除student表中所有classid=1的该行数据。

语法：alter table 表名 drop [constraint] 约束名称;
-- constraint关键字可省略 -- 如果将表删除了，那么相关的约束也就删除了。

Eg01: alter table student drop primary key;
-- 存在一个主键约束，可用此操作；

Eg02: alter table student drop foreign key FK_class_student_id；
-- 删除外键

--（最常用）
-- 插入值的顺序必须与表中字段的顺序完全一致。
1. "insert...values"
语法：insert into 表名 values(值);

--插入一行数据
Eg01: insert into student values (1, ‘zhangsan’ );

--插入多行数据，用逗号隔开；
Eg02: insert into student values (2,‘jack’)，(3, ‘rose’);

补充：当插入的记录会引发主键或唯一约束冲突时，可以用 **on duplicate key update** 条件。
Eg: insert into student values (1, ‘mike’ ) **on duplicate key update id =10**;
-- 由于id是主键，id=1与上面zhangsan行冲突，所以自动把zhangsan的id设置为10，然后再把id=1的mike数据插入进去；

-- 按照每一列对应的值插入数据
2. ”insert...set”
语法：insert into 表名 set 列名1=值1, 列名2=值2,......;

--插入的数据是：id为5，姓名’abc’ 3）”insert...select”
-- 把从其他表查询到的数据插入到该表中，类似于like属性 Eg01: insert into user
Eg01: insert into student set id =5, name=’abc’;

--把从student中查询到的数据插入到user中；
select * from student;

格式：update 表名 set 列名1=值1 [,列名2=值2,......] [where...] [order by...] [limit 行数]
--把id=3的列的name改为wangwu；
eg： update student set name = 'wangwu' where id = 003;

格式：delete from 表名 where 列名=值；
--删除id＝３的列的数据。
eg：delete from student where id=3；

select * from 表1 cross join 表2
eg：
select class.cname, student.sname from student cross join class on class.cid =
student.classid;
--其中的on代表两个表的连接条件

方式一：
select * from 表1,表2 where 表1.列=表2.列
方式二：
select * from 表1
inner join 表2 on 表1.列=表2.列

select * from 表1
left join 表2 on 表1.列=表2.列

select * from 表1
right join 表2 on 表1.列=表2.列

select 表.字段1,表.字段2,表.字段3
from 表1 as 别名,表2 as 别名
where 别名.字段2=别名2.字段3；

select * from 表1 where 字段1 = （select 字段2 from 表2）;

eg：
select ename,sal from emp where sal>(select avg(sal) form emp);

eg：
select t.deptno,t.avgsal,s.grade
from (select deptno,avg(sal) as avgsal from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal;

Eg：
select ename,job from emp where job='manager'
union
select ename,job from emp where job='salesman';

limit 起始下标，长度
如果起始下标没有指定，默认从0开始，0表示表中第一条记录。
Eg：
--按照工资降序排列取前5个
select ename,sal from emp order by sal desc limit 5;

select t.* from t
order by t.x desc/asc
limit (pageNo-1)*pageSize,pageSize;

-- 基本语法
CREATE OR REPLACE VIEW view_name
AS select_statement
注意事项：表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图。

SELECT * FROM user_order_view WHERE user_name='Cicada';

SHOW CREATE VIEW user_order_view ;

ALTER VIEW view_name AS select_statement ;

DROP VIEW [IF EXISTS] view_name ;

UPDATE user_order_view SET user_name='smile' WHERE id='1';

-- 普通索引
CREATE index index1 on student (id);

-- 唯一索引
CREATE UNIQUE INDEX index2 on student(id);

-- 全文索引
CREATE FULLTEXT INDEX index3 on student(adress(20));

-- 创建单列索引
CREATE INDEX INDEX5 on student(name(10));

-- 联合索引
CREATE INDEX INDEX6 ON student (name,id);

-- 普通索引
ALTER TABLE student ADD INDEX index1(id);

-- 唯一索引
ALTER TABLE student ADD UNIQUE INDEX index2(id);

-- 全文索引
ALTER TABLE student add FULLTEXT INDEX index3 (NAME(20));

-- 单列索引
ALTER TABLE student ADD INDEX index4 (adress(20));

-- 联合索引
ALTER TABLE student ADD INDEX INDEX5 (name,id);

--触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
CREATE TRIGGER 触发器名称
--触发器有执行的时间设置：可以设置为事件发生前或后。
{ BEFORE | AFTER }
--同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。
{ INSERT | UPDATE | DELETE }
--触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
ON 表名称
--触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。
FOR EACH ROW
--触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。
<触发器SQL语句>

-- 创建表tab1
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1(
  tab1_id varchar(11)
);
-- 创建表tab2
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2(
  tab2_id varchar(11)
);

-- 创建触发器:t_afterinsert_on_tab1
-- 作用：增加tab1表记录后自动将记录增加到tab2表中
DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;
CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
   insert into tab2(tab2_id) values(new.tab1_id);
END;
-- 测试一下
INSERT INTO tab1(tab1_id) values('0001');
-- 看看结果
SELECT * FROM tab1;
SELECT * FROM tab2

-- 创建触发器:t_afterdelete_on_tab1
-- 作用：删除tab1表记录后自动将tab2表中对应的记录删去
DROP TRIGGER IF EXISTS t_afterdelete_on_tab1;
CREATE TRIGGER t_afterdelete_on_tab1
AFTER DELETE ON tab1
FOR EACH ROW
BEGIN
   delete from tab2 where tab2_id=old.tab1_id;
END;
-- 测试一下
DELETE FROM tab1 WHERE tab1_id='0001';
-- 看看结果
SELECT * FROM tab1;
SELECT * FROM tab2;

-- 创建触发器:t_afterupdate_on_tab1
-- 作用：修改tab1表记录后自动将tab2表中对应的记录更新
DROP TRIGGER IF EXISTS t_afterupdate_on_tab1;
CREATE TRIGGER t_afterupdate_on_tab1
AFTER UPDATE ON tab1
FOR EACH ROW
BEGIN
   update tab2 set tab2_id=new.tab1_id where tab2_id=old.tab1_id;
END;
-- 测试一下
update tab1 set tab1_id='0002' WHERE tab1_id='0001';
-- 看看结果
SELECT * FROM tab1;
SELECT * FROM tab2;

-- 建表 operate
DROP TABLE IF EXISTS `operate`;
CREATE TABLE `operate` (
  `Op_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `Op_type` varchar(20) NOT NULL COMMENT '操作方式',
  `Op_time` time NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`Op_id`),
  UNIQUE KEY `Op_id` (`Op_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 建表 product
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(10) NOT NULL COMMENT '产品编号',
  `Name` varchar(20) NOT NULL COMMENT '产品名称',
  `Function` varchar(50) DEFAULT NULL COMMENT '产品功能',
  `Company` varchar(20) NOT NULL COMMENT '生产厂家',
  `Address` varchar(20) DEFAULT NULL COMMENT '家庭住址',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
INSERT INTO product(id,`NAME`,`Function`,Company,Address) VALUES
    (1,'Strawberry','通便','中国','北京'),
    (2,'Watermelon','利尿','中国','杭州'),
    (3,'cherry','养颜驻容','中国','深圳');

-- 创建 BEFORE INSERT 触发器
CREATE TRIGGER product_bf_insert
BEFORE INSERT on product
FOR EACH ROW
INSERT INTO operate VALUES(NULL,'数据插入成功！',TIME(NOW()));

-- 插入数据
INSERT INTO product(id,`NAME`,`Function`,Company,Address) VALUES
    (4,'peach','补益气血，养阴生津','中国','北京');

-- 创建 AFTER UPDATE 触发器
CREATE TRIGGER product_af_update
AFTER UPDATE on product
FOR EACH ROW
INSERT INTO operate VALUES(NULL,'数据更新成功！',TIME(NOW()));

-- 更新数据
UPDATE product SET Address='北京海淀区' WHERE id=1;

-- 创建 AFTER DELETE 触发器
CREATE TRIGGER product_af_del
AFTER DELETE on product
FOR EACH ROW
INSERT INTO operate VALUES(NULL,'数据删除成功！',TIME(NOW()));

-- 删除数据
DELETE FROM product WHERE id=4;

-- 删除 product_bf_insert 和 product_af_update 触发器
DROP TRIGGER product_bf_insert;
DROP TRIGGER product_af_update;

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

-- 查询学生表中性别为男的学生总数
CREATE PROCEDURE u_pro(IN v_sex VARCHAR(5) CHARACTER SET utf8 ,OUT num INT)
  BEGIN
  SELECT COUNT(*) INTO num FROM student WHERE v_sex=sex;
end

CALL u_pro('男',@num);
SELECT @num;

-- 计算1+到n的值
CREATE PROCEDURE addresult(IN n INT)
BEGIN
  DECLARE i INT;
  DECLARE sum INT;
  SET sum =0;
  SET i =1;

  WHILE i<n DO
  SET sum=sum+i;
  SET i=i+1;
  END WHILE;

  SELECT sum ;
END

call addresult(50);

-- 计算1+到n的值
CREATE PROCEDURE addresult2(in n INT,out sum INT)
BEGIN
  DECLARE i INT;
  DECLARE sum INT;
  SET sum =0;
  SET i =1;

  WHILE i<n DO
  SET sum=sum+i;
  SET i=i+1;
  END WHILE;

  SELECT sum ;
END

call addresult2(50,@sum);

-- 判断变量
CREATE PROCEDURE user_main_pro3(INOUT v_id INT)
BEGIN
  # 定义变量
  DECLARE v_count INT(11);
  # 判断
  IF v_id > 3 THEN
     SET v_count = 100;
  ELSE
     SET v_count = 500;
  END IF;
  # 返回赋值
  SET v_id = v_count;
END

SET @v_id=1;
call user_main_pro3(@v_id);
SELECT @v_id;

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 返回当前时间
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());

SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);

SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);

SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);

-- 返回两个日期值之间的差值(月数)
SELECT PERIOD_DIFF(200302,199802);

-- 返回当前日期和生日之间的年龄 这样，如果 Brithday 是未来的年月日的话，计算结果为 0。
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee;

-- 下面的 SQL 语句计算员工的绝对年龄，即当 Birthday 是未来的日期时，将得到负值。
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORM AT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee;

SELECT ENCRYPT('root','salt');

SELECT ENCODE('xufeng','key');

-- 加解密放在一起
SELECT DECODE(ENCODE('xufeng','key'),'key');

SELECT AES_ENCRYPT('root','key');

SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');

SELECT MD5('123456');

SELECT SHA('123456');

CASE WHEN[test1] THEN [result1]...ELSE [default] END
-- 如果 testN 是真，则返回 resultN，否则返回 default

CASE [test] WHEN[val1] THEN [result]...ELSE [default] END
--  如果 test 和 valN 相等，则返回 resultN ，否则返回 default

SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');

SELECT IF(1 < 10,2,3),IF(56>100,'true','false');

CASE [expression to be evaluated]
WHEN [val 1] THEN [result 1]
WHEN [val 2] THEN [result 2]
WHEN [val 3] THEN [result 3]
......
WHEN [val n] THEN [result n]
ELSE [default result]
END

WHEN [conditional test 1] THEN [result 1]
WHEN [conditional test 2] THEN [result 2]
ELSE [default result]
END

SELECT CASE 'green' WHEN 'red' THEN 'stop' WHEN 'green' THEN 'go' END;

SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END;

SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN(2+2)<>4 THEN 'not OK' END ASST ATUS;

SELECT Name,IF((IsActive = 1),' 已激活 ',' 未激活 ') AS RESULT FROMUserLoginInfo;

SELECT fname,lname,(math+sci+lit) AS total,
  CASE WHEN (math+sci+lit) < 50 THEN 'D'
  WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C'
  WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B'
  ELSE 'A' END AS grade
FROM marks;

SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';

SELECT FORMAT(34234.34323432,3);

SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

SELECT DATE_FORMAT(19990330,'%Y-%m-%d');

SELECT DATE_FORMAT(NOW(),'%h:%i %p');

SELECT INET_ATON('10.122.89.47');

SELECT INET_NTOA(175790383);

SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;

SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);

SELECT DATABASE(),VERSION(),USER();

SELECT BENCHMARK(9999999,LOG(RAND()*PI()));
-- 该例中 ,MySQL 计算 LOG(RAND()*PI()) 表达式 9999999 次。