MySQL——数据库语言+语法

MySQL——数据库语言+语法

CREATE TABLE `NewTable` (
`id`  int NOT NULL AUTO_INCREMENT ,
`name`  varchar(10) CHARACTER SET utf8 NOT NULL ,
`age`  decimal(6,4) NOT NULL ,
`img`  blob NULL ,
PRIMARY KEY (`id`)
)
DEFAULT CHARACTER SET=utf8
;


DESC  NewTable;
-- 修改表元素
ALTER TABLE NewTable
MODIFY COLUMN `name`  varchar(30),
MODIFY COLUMN `name`  DECIMAL(6,3);
;
--  修改列名
ALTER  TABLE `user1`
CHANGE dept deptId  VARCHAR(10) 




-- 添加行
ALTER TABLE NewTable
ADD COLUMN `sexId` CHAR(1) not NULL;


-- 删除行
ALTER TABLE NewTable
DROP COLUMN`sexId`;


-- 删除表结构
drop table`USER1`;

select CURDATE() from DUAL;


DESC newtable


select*from `newtable`


-- 插入数据
INSERT INTO`newtable`VALUES(DEFAULT,"张3张",8.5,NULL);
INSERT INTO`classid`VALUES("2010","李10","1","A102");
SELECT *FROM classid


INSERT INTO`grade`VALUES("209","B","98");
SELECT *FROM grade
-- 插入一个表的数据
INSERT  INTO user1
select*from `newtable`


-- 修改数据
-- 有条件
UPDATE `newtable`
set `name`="李we",
       age=5.3
WHERE  name="张三"
AND age=4.5  ;-- != = <>


select*from `newtable`
WHERE  (name="张三"and age=3.5)
OR age=3.5  ;
-- 删除表数据内容
DELETE FROM `USER1`
WHERE  ID=1


select*from `newtable`
WHERE  ID="1"
--  性能好效率高 清空表
TRUNCATE TABLE`newtable`


-- 复制表 但是没有主键递增
CREATE TABLE `user1`
select*from `newtable`


-- 排序
select*from `newtable`
WHERE 1=1
ORDER BY age DESC , id DESC
-- 查询排序  desc降序 asc升序
SELECT *,ASCII(NAME) FROM `newtable` WHERE 1=1
ORDER BY age DESC , id DESC
-- 去除重复数据
SELECT DISTINCT NAME ,age,img FROM`newtable`
-- 范围查询  in notin 
select*from `newtable`
WHERE name not in("张3","张");
-- 范围查询 BETWEEN
SELECT*from`newtable`
WHERE age BETWEEN 3.5 and 4.6
-- 模糊查询 %代表任意多个字符 _一个字符
SELECT*FROM`newtable`
WHERE `name` LIKE'%张%'


-- 子查询


SELECT /*INDEX*/*from
(SELECT *,
-- id,name,age,img,
( SELECT deptDesc FROM dept 
WHERE deptId=`user1`.deptId)as dept
from`user1`


WHERE deptId =any( SELECT deptId FROM dept    -- in 等同于 = any
where deptDesc like '人事%' or deptDesc like '财务%'))as zyff


-- or deptId=( SELECT deptId FROM dept
-- where deptDesc like '财务')




SELECT*FROM `user1`
-- 限制索引前两个
SELECT*FROM`user1` ORDER BY age DESC LIMIT 0,2


SELECT*, 
CASE WHEN age<5
THEN
   "儿童"
ELSE
    "少年"
END AS part
from `user1`


SELECT*, 
CASE WHEN deptId="A"
THEN
   "财务部"
WHEN deptId="C"
THEN
   "IT部"
WHEN deptId="D"
THEN
   "人事部"
ELSE
    "管理部"
END AS partt
from `user1`


 SELECT*, IF(age<5,'儿童 ','少年')FROM user1;
-- 汉字长度
SELECT*,LENGTH(deptDesc) FROM dept
-- 连接字符串
SELECT  CONCAT(deptId,":",deptDesc)d FROM dept
-- 3,0插入字符串 3,3替换3位
SELECT INSERT('Quadratic', 3, 0, 'What');
-- 截取字符串
SELECT left('12345',5);
SELECT RIGHT('2135435',5);
-- 1,5从第几位到 后几位  -3倒数后几位 MID与SUBSTRING同义词
SELECT SUBSTRING('012345678',1,5);
SELECT MID('012345678',1,5);
-- 查找字符串 2.从第6位开始查找
SELECT LOCATE('bar','footbarbar')
SELECT LOCATE('bar','footbarbar',6)
-- 1.转换成小写 2.转换成大写
SELECT LOWER('adsadQWEQ1232')
SELECT UPPER('asdasdASDASD1231')
-- 去除开头空格 LTRIM去除左空格RTRIM右空格
SELECT TRIM('  asdf  dfasd  ')
SELECT RTRIM('  asdf  dfasd  ')
SELECT LTRIM('  asdf  dfasd  ')
-- 替换字符串
SELECT REPLACE('  df sdf  sdaf dsf ',' ','');
-- 重复3次
SELECT REPEAT('w',4)
SELECT REPEAT(deptDesc,4) FROM dept
-- 字符串反转
SELECT REVERSE(deptDesc)FROM dept
-- 数值函数
SELECT  POW(2,3)
SELECT RAND()
SELECT FLOOR(7+(RAND()*6))
-- 取随机数据
SELECT *FROM user1
WHERE 1
ORDER BY RAND() LIMIT 3;
-- 四舍五入
SELECT ROUND(-1.5)
SELECT FLOOR(1.5)
SELECT ROUND(5.553454,3)
-- 当前日期
SELECT CURDATE()
SELECT CURRENT_DATE()
SELECT CURRENT_TIME()
SELECT CURRENT_TIMESTAMP()
SELECT YEAR(CURDATE())
SELECT MONTH(CURDATE())
SELECT YEARWEEK(CURDATE())
SELECT YEARWEEK('2016&01+23')
-- 设置日期
SELECT ADDDATE(CURDATE(),-50)
-- 有问题
SELECT CURDATE()-30
-- 分钟加减
SELECT ADDTIME(CURRENT_TIME(),'-01:00:00');
SELECT DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL '-1 1' DAY_HOUR);
-- 相差天数
SELECT DATEDIFF(CURRENT_DATE(),'20170125')


-- 格式化日期
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-- 判断周几
SELECT 
case WHEN
DATE_FORMAT(CURDATE(),'%w')=0
THEN '周日'
WHEN
DATE_FORMAT(CURDATE(),'%w')=1
THEN '周一'
WHEN
DATE_FORMAT(CURDATE(),'%w')=2
THEN '周二'
WHEN
DATE_FORMAT(CURDATE(),'%w')=3
THEN '周三'
WHEN
DATE_FORMAT(CURDATE(),'%w')=4
THEN '周四'
WHEN
DATE_FORMAT(CURDATE(),'%w')=5
THEN '周五'
WHEN
DATE_FORMAT(CURDATE(),'%w')=6
THEN '周六'
end  weekNo
-- 当前月份的第几天
SELECT DAYOFMONTH(CURDATE());
-- 当前月第几周
SELECT DAYOFWEEK(CURDATE());
-- 当前年第几天
SELECT DAYOFYEAR(CURDATE())


SELECT EXTRACT(DAY_MINUTE FROM CURRENT_TIMESTAMP());
-- 当前月份的最后一天
SELECT LAST_DAY(CURDATE())


SELECT SYSDATE()
-- 1.name 2.date
INSERT INTO ttt
VALUES('zz',SYSDATE())


SELECT *FROM ttt


SELECT COUNT(*) FROM`user1`
SELECT sum(age)FROM `user1`;
SELECT AVG(age)FROM `user1`;
SELECT MIN(age)FROM `user1`;
SELECT MAX(age)FROM `user1`;



CREATE TABLE stu(
id VARCHAR(10) NOT NULL,
name VARCHAR(10) ,
sex VARCHAR(10) ,
class VARCHAR(10) ,
PRIMARY KEY (id)
)
DEFAULT CHARACTER SET=utf8


CREATE TABLE subject(
id VARCHAR(10) NOT NULL,
subname VARCHAR(10) ,
PRIMARY KEY (id)
)
DEFAULT CHARACTER SET=utf8


CREATE TABLE class(
id VARCHAR(10) NOT NULL,
name VARCHAR(10) ,
PRIMARY KEY (id)
)
DEFAULT CHARACTER SET=utf8


CREATE TABLE grade(
id VARCHAR(10) NOT NULL,
subjectid VARCHAR(10) NOT NULL,
grade VARCHAR(10) ,
PRIMARY KEY (id)
)
DEFAULT CHARACTER SET=utf8






SELECT stu.name,
CONCAT(grade.subjectid,subject.subname)subname,
grade.grade
FROM grade,stu,subject
WHERE grade.id=stu.id and subject.id=grade.subjectid; 






SELECT 
class.name,
SUM(grade.grade),
AVG(grade.grade)
FROM grade,stu,class 
WHERE grade.id=stu.id and class.id=stu.class
GROUP BY class.name
HAVING 1=1;


SELECT class.name,subject.subname, 
SUM(grade.grade),
AVG(grade.grade)
from class,stu,grade,subject
where stu.class=class.id and grade.id=stu.id and subject.id=grade.subjectid
GROUP BY class.name,SUBJECT.subname 
HAVING SUM(grade.grade)>870;




SELECT stu.name,
stu.class,
grade.subjectid,
grade.grade
FROM stu,grade
WHERE stu.id=grade.id
-- 左链接  左表为主，右表有数据就匹配显示
SELECT stu.name,
stu.class,
grade.subjectid,
grade.grade,
FROM stu
LEFT JOIN grade
on stu.id=grade.id
LEFT JOIN subject
ON stu.id=subject.id


WHERE grade.subjectid='A'


-- 右连接
SELECT 


grade.id,
grade.grade,
grade.subjectid,
stu.class,
stu.name
FROM  grade
RIGHT JOIN  stu
ON  grade.id=stu.id


CREATE TABLE dept(
deptId VARCHAR(10) NOT NULL,
deptDesc VARCHAR(10) ,
fromDate VARCHAR(10) ,
toDate VARCHAR(10) ,
PRIMARY KEY (deptId)
)
DEFAULT CHARACTER SET=utf8


CREATE TABLE user(
userId VARCHAR(10) NOT NULL,
userName VARCHAR(10) ,
sexId VARCHAR(10) ,
deptId VARCHAR(10) ,
PRIMARY KEY (userId)
)
DEFAULT CHARACTER SET=utf8


CREATE TABLE part(
partNo VARCHAR(10) NOT NULL,
partDesc VARCHAR(10) ,
type VARCHAR(10) ,
text VARCHAR(10) ,
PRIMARY KEY (partNo)
)
DEFAULT CHARACTER SET=utf8


CREATE TABLE poInfo(
PoNo VARCHAR(10) NOT NULL,
partNo VARCHAR(10) NOT NULL,
userId VARCHAR(10) ,
poDate VARCHAR(10) ,
price INT(10) ,
count INT(10) ,
stuta VARCHAR(10) ,
PRIMARY KEY (PoNo,partNo)


)
DEFAULT CHARACTER SET=utf8






-- 统计金额
SELECT *FROM view2
-- 添加视图
CREATE view  department.view2  AS
SELECT poInfo.poNo 清单编号,dept.deptDesc,poInfo.price*poInfo.count 每单总价
from poinfo,dept,user
where user.userId=poInfo. userId and 
user.deptId=dept.deptId 
and poDate>='2006-01-01' 
and poDate < CURDATE() 
 and dept.deptDesc="IT部";


HAVING  dept.deptDesc="IT部"
-- 查询
SELECT *FROM view2
-- 添加视图
CREATE view  department.view2  AS
-- 修改
alter  VIEW  VIEW2  AS
-- 删除
DROP VIEW  VIEW2 


SELECT dept.deptDesc, SUM(poInfo.price*poInfo.count) 总价
from poinfo,dept,user
where user.userId=poInfo.userId and 
user.deptId=dept.deptId 
and poDate>='2006-01-01' 
and poDate < CURDATE() 
and dept.deptDesc="IT部"
;
-- UNION 能够去除重复数据
(SELECT dept.deptDesc,poInfo.poNo 清单编号,poInfo.price*poInfo.count 单子总价
from poinfo,dept,user
where user.userId=poInfo.userId and 
user.deptId=dept.deptId 
and poDate>='2006-01-01' 
and poDate < CURDATE() 
and dept.deptDesc="IT部" )
UNION
(SELECT "","",SUM(poInfo.price*poInfo.count) 总价
from poinfo,dept,user
where user.userId=poInfo.userId and 
user.deptId=dept.deptId 
and poDate>='2006-01-01' 
and poDate < CURDATE() 
and dept.deptDesc="IT部"
)




SELECT dept.deptDesc,poInfo.poNo 清单编号,poInfo.price*poInfo.count 单子总价
from poinfo,dept,user
where user.userId=poInfo.userId and 
user.deptId=dept.deptId 
and poDate>='2006-01-01' 
and poDate < CURDATE() 
and dept.deptDesc="IT部" 
UNION ALL
SELECT "总金额","",SUM(poInfo.price*poInfo.count)
from poinfo,dept,user
where user.userId=poInfo.userId and 
user.deptId=dept.deptId 
and poDate>='2006-01-01' 
and poDate < CURDATE() 
and dept.deptDesc="IT部";






-- 各部门2015总价
SELECT LEFT(poinfo.poDate,4) nian ,dept.deptDesc,SUM(poInfo.price*poInfo.count) 总价
from poinfo,dept,user
where user.userId=poInfo. userId and 
user.deptId=dept.deptId 
and poDate>='2006-01-01' 
and poDate < CURDATE() 
GROUP BY 
 LEFT(poinfo.poDate,4),
dept.deptDesc ;






-- 各部门2016-1019年平均平均
SELECT dept.deptDesc,SUM(poInfo.price*poInfo.count)/(YEAR(CURDATE())-substring('2006-05-09',1,4)) 总价
from poinfo,dept,user
where user.userId=poInfo. userId and 
user.deptId=dept.deptId 
and poDate>='2006-05-09' 
and poDate < CURDATE() 
GROUP BY dept.deptDesc ;


-- avg平均
SELECT dept.deptDesc,AVG(poInfo.price*poInfo.count)
from poinfo,dept,user
where user.userId=poInfo. userId and 
user.deptId=dept.deptId 
AND poinfo.poDate BETWEEN '2006-01-01'and'2015-12-31'
GROUP BY dept.deptDesc ;





delimiter //
 
CREATE FUNCTION helloe(s VARCHAR(20)) RETURNS VARCHAR(50)
 RETURN CONCAT('Hello, ',s,'!');
   //
delimiter ;


SELECT helloe('world')FROM DUAL;


 delimiter //
 
 CREATE FUNCTION hello2 (s CHAR(20)) RETURNS CHAR(50)
 RETURN CONCAT('Hello, ',s,'!')
  //


 delimiter
 SELECT helloe('哈哈');




-- 自定义函数分支结构
delimiter //
DROP FUNCTION IF EXISTS cutstr;
CREATE FUNCTION cutstr(s VARCHAR(20),n INT) 
RETURNS VARCHAR(50)
BEGIN
   IF(ISNULL(s))THEN RETURN '';
   ELSEIF(CHAR_LENGTH(s)<=n) THEN RETURN s;
   ELSE  RETURN CONCAT(LEFT(s,n),'....');
    END IF;
END;
   //
delimiter ;


SELECT cutStr('213214325',3)


SELECT CHAR_LENGTH('中文哈哈123423')
-- 判断周几
delimiter //
DROP FUNCTION IF EXISTS getweek;
CREATE FUNCTION getweek(str VARCHAR(50)) 
RETURNS VARCHAR(4)
BEGIN
  IF(DATE_FORMAT(str,'%w')=0)
THEN  RETURN '星期七';
ELSEIF(DATE_FORMAT(str,'%w')=1)
THEN  RETURN '星期一';
ELSEIF(DATE_FORMAT(str,'%w')=2)
THEN  RETURN '星期二';
ELSEIF(DATE_FORMAT(str,'%w')=3)
THEN  RETURN '星期三';
ELSEIF(DATE_FORMAT(str,'%w')=4)
THEN  RETURN '星期四';
ELSEIF(DATE_FORMAT(str,'%w')=5)
THEN  RETURN '星期五';
ELSEIF(DATE_FORMAT(str,'%w')=6)
THEN  RETURN '星期六';
ELSE
 RETURN '输入日期格式不能识别';
END IF;
END;
 //
delimiter ;
SELECT getweek(CURDATE())
SELECT getweek('2016-10-26')




SELECT DATE_FORMAT(CURDATE(),'%W')


-- 自定义函数循环结构
delimiter //
DROP FUNCTION IF EXISTS doiterate;
CREATE FUNCTION doiterate(p1 INT) RETURNS INT
BEGIN
  zyf: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE zyf; END IF;
    LEAVE zyf;
  END LOOP zyf;
RETURN p1;
END
 //
delimiter ;


--  自定义函数循环结构
delimiter //
DROP FUNCTION IF EXISTS doiterate;
CREATE FUNCTION doiterate(p1 INT) RETURNS INT
BEGIN
  aha: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE aha; END IF;
    LEAVE aha;
  END LOOP aha;
  RETURN p1;
END
//
delimiter;
SELECT doiterate(11);


-- 循环返回星星
delimiter //
DROP FUNCTION IF EXISTS morestar;
CREATE FUNCTION morestar(num INT) RETURNS VARCHAR(100)
BEGIN
DECLARE star VARCHAR(100)  DEFAULT'';
DECLARE i INT DEFAULT 0;
  zyf: LOOP
     SET i=i+1;
    SET star = CONCAT(star,'*');
    IF i < num THEN ITERATE zyf; END IF;
    LEAVE zyf;
  END LOOP zyf;
  RETURN star;
END
//
delimiter;


SELECT morestar(11);


CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);
 
DELIMITER |
 
CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END
|
 
DELIMITER ;


INSERT INTO test3 (a3) VALUES 
  (NULL), (NULL), (NULL), (NULL), (NULL), 
  (NULL), (NULL), (NULL), (NULL), (NULL);
 
INSERT INTO test4 (a4) VALUES 
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);


INSERT INTO test1 VALUES 
    (1), (3), (1), (7), (1), (8), (4), (4);




SELECT*FROM test1
SELECT*FROM test2
SELECT*FROM test3
SELECT*FROM test4


 delimiter //
 
 CREATE FUNCTION hello3 (s CHAR(20)) RETURNS CHAR(50)
 RETURN CONCAT('Hello, ',s,'!')
  //


 delimiter
 SELECT hello1('world');





-- 存储过程
delimiter // 
DROP PROCEDURE IF EXISTS simpleproc ; 
 CREATE PROCEDURE simpleproc (In num INT,OUT param1 INT)
   BEGIN
    SELECT COUNT(*)*num INTO param1 FROM poinfo;
     SELECT param1;
   END
   //
delimiter ;
 
CALL simpleproc(10,@qq);
SELECT @qq;
-- 插入操作
delimiter // 
DROP PROCEDURE IF EXISTS INSERT_user;
CREATE PROCEDURE INSERT_user(in id INT,
in NAME VARCHAR(10),
in sexid VARCHAR(10),
in deptid VARCHAR(10),OUT result VARCHAR(10))
BEGIN 
    SET @ID=id;
    SET @NAME=name;
    SET @SEXID=sexid;
    SET @DEPTID=deptid;
    SET @insertSql=CONCAT('INSERT INTO USER VALUES(?,?,?,?)');
      PREPARE stmtinsert FROM @insertSql;
    EXECUTE stmtinsert USING @ID,@NAME,@SEXID,@DEPTID;
    DEALLOCATE PREPARE stmtinsert;
    SET result='ok';
    SELECT result;
END;
//
delimiter;


CALL  INSERT_USER(321000012,'张宇峰11','1','B',@result);




-- 光标用法
delimiter // 
DROP PROCEDURE IF EXISTS curdemo;
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a,b,c,d VARCHAR(10);
  DECLARE cur1 CURSOR FOR SELECT userid,username,sexid,deptid FROM `user` ;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  REPEAT
    FETCH cur1 INTO a,b,c,d;
    IF NOT done THEN
       
          INSERT INTO userTemp VALUES (a,b,c,d);
 
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur1;
END
//
delimiter;


CALL curdemo()




SELECT*FROM `user` WHERE userid='3210001'






CREATE TABLE userTemp
AS
SELECT*FROM user


SELECT*FROM userTemp


DELETE FROM userTemp








-- 光标用法 插入指定数条数据
delimiter // 
DROP PROCEDURE IF EXISTS randow;
CREATE PROCEDURE randow(IN f INT,OUT result VARCHAR(10))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE b,a,c,d VARCHAR(10);
  DECLARE cur1 CURSOR FOR SELECT userid,username,sexid,deptid FROM `user` order by RAND() LIMIT 0,f;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  REPEAT
    FETCH cur1 INTO a, b,c,d;
    IF NOT done THEN
       
          INSERT INTO userTemp VALUES (a,b,c,d);
    
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur1;
   SET result='ok';
    SELECT result;
END
//
delimiter;
CALL randow(6,@result);




SELECT*FROM `user` order by RAND() LIMIT 0,5;


-- 光标用法动态
delimiter // 
DROP PROCEDURE IF EXISTS randowdt;
CREATE PROCEDURE randowdt(IN QT INT,OUT result VARCHAR(10))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE b,a,c,d VARCHAR(10);
  delete FROM usertemp;
 SET @cnt=QT;
set @sql=CONCAT('insert into userTemp select userid,username,sexid,deptid from `user` ORDER BY RAND() LIMIT ',@CNT);


PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
SET result='ok';
    SELECT result;
END;
//
delimiter;
CALL  randowdt(5,@result)
SELECT*FROM usertemp