mysql

USE ex01;
CREATE TABLE e02(
   id INT,
   ‘name’ VARCHAR(44),
   ’password‘ VARCHAR(255),
   ‘birth’ DATE
  
   )
 #添加语句
INSERT INTO e02 VALUES(1928,"余科葳",010018,"2003-11-01");
INSERT INTO e02 VALUES(1922,"任某好",1842542,"2002-11-01");
INSERT INTO e02 VALUES(1956,"十三五",0141418,"2002-10-07");
SELECT*FROM e02; 
#改变语句
UPDATE e02 SET ’password‘=’password‘+100000;
UPDATE e02 SET ’password‘=10010018 WHERE id=1928;
#删除语句
DELETE FROM e02 WHERE id=1956;
#表中添加列
ALTER TABLE e02 ADD(
   money INT
)
 ALTER TABLE e02 ADD(
   age INT 
   )
   #删除列
   ALTER TABLE e02 DROP money;
#修改列
ALTER TABLE e02 MODIFY ’password‘ VARCHAR(200)
#表的名字更改
RENAME TABLE e02 TO exercise02;

#select语句 [distinct]去重
CREATE TABLE student(
id INT NOT NULL DEFAULT 1,
NAME VARCHAR(10) NOT NULL DEFAULT '',
chinese FLOAT NOT NULL DEFAULT 0.0,
math FLOAT NOT NULL DEFAULT 0.0,
english FLOAT NOT NULL DEFAULT 0.0
);
SELECT * FROM student
SELECT DISTINCT * FROM student
INSERT INTO student VALUES(1,'刘备',99,89,97),(2,'诸葛亮',98,99,99),(3,'关羽',80,87,91),(4,'张飞',78,68,87)
INSERT INTO student VALUES(5,'赵云',94,82,91),(6,'黄忠',78,95,87),(7,'马超',80,88,93),(8,'姜维',98,90,95)
INSERT INTO student VALUES(9,'刘shan',69,69,57)
#查询
SELECT NAME,chinese FROM student

#对列运算,算总分平均分
 SELECT NAME,(chinese+math+english)/3 FROM student
 #别名
  SELECT NAME AS '名字',(chinese+math+english)/3  AS '平均分' FROM student
  
  #where中经常使用的运算符
   SELECT*FROM student WHERE math>60 AND id>5 
   SELECT *FROM student WHERE (chinese+math+english)>270
 #刘%名字开头以刘开始就行，可以查询姓刘的成绩
 SELECT DISTINCT *FROM student WHERE math<chinese AND NAME LIKE '刘%'
 #查询区间
 SELECT NAME,chinese FROM student WHERE chinese>=90 AND chinese<=99
 SELECT NAME,english FROM student WHERE english BETWEEN 80 AND 99;
 #多个查询
 SELECT NAME,chinese FROM student WHERE chinese=90 OR chinese=80 OR chinese=98;
  SELECT NAME,chinese FROM student WHERE chinese IN(80,90,98)
  
    SELECT * FROM student WHERE chinese>=90 AND math>=90 AND english>=90;
#order by指定排序的列，可以是表中的列也可以是select语句中指定的列 asc默认升序，desc降序
#order by 子句应该位于select语句的结尾
SELECT DISTINCT NAME ,(math+chinese+english) AS total FROM student WHERE NAME LIKE '刘%'
 ORDER BY total DESC ;    
 
 #statistic统计
 #查询班级人数
 SELECT COUNT(*) FROM student;
 #查询数学成绩大于90的人数
 SELECT COUNT(NAME) FROM student WHERE math>=90;
 #合计函数sum
 SELECT SUM(math) FROM student
 
 

CREATE TABLE emp (
  empno MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',   #编号
  ename VARCHAR(20) NOT NULL DEFAULT '', #名字
  job VARCHAR(9) NOT NULL DEFAULT '',#工作
  mgr MEDIUMINT(8) UNSIGNED DEFAULT NULL,#上级编号
  hiredate DATE NOT NULL,#入职时间
  sal DECIMAL(7,2) NOT NULL,#工资
  comm DECIMAL(7,2) DEFAULT NULL,#补贴
  depton MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0'#部门编号
) ENGINE=INNODB DEFAULT CHARSET=utf8

SELECT *FROM emp

CREATE TABLE dept (
  deptnum MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',#部门号
  deptname VARCHAR(20) NOT NULL DEFAULT '',#部门名字
  location VARCHAR(13) NOT NULL DEFAULT ''#地址
) ENGINE=INNODB DEFAULT CHARSET=utf8

SELECT*FROM dept

CREATE TABLE salgrade(
  grade MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
  losal DECIMAL(17,2) NOT NULL,
  hisal DECIMAL(17,2) NOT NULL,
) ENGINE=INNODB DEFAULT CHARSET=utf8
SELECT*FROM salgrade
#group by 对查询结果分组统计
SELECT AVG(sal),MAX(sal),depton,job FROM emp GROUP BY depton,job
#查询工资小于两千
SELECT AVG(sal) AS '平均工资',MAX(sal) AS '最高工资',depton AS '部门编号'FROM emp GROUP BY depton,job HAVING AVG(sal)<2000
#字符串相关函数
#返回字符串字符集
SELECT  CHARSET(ename) FROM emp
#concat( , )连接字符串
SELECT CONCAT(ename,'工作是',job) AS '个人信息' FROM emp

#查询字符在字符串中出现的位置,在没表的时候使用dual亚元表
SELECT INSTR('yukewei','wei') FROM DUAL
 
CREATE TABLE animal(
 
num MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',   #编号
NAME VARCHAR(20) NOT NULL DEFAULT '', #名字
food VARCHAR(9) NOT NULL DEFAULT ''
  
 )
SELECT*FROM animal
INSERT INTO animal VALUES(001,'dog','meat')
INSERT INTO animal VALUES(002,'cat','manyfish')
INSERT INTO animal VALUES(001,'cow','eggs')
UPDATE animal SET num=003 WHERE NAME='cat';
#ucase(stirng)转大写 lcase转小写
SELECT UCASE(NAME),food FROM animal
SELECT num,LCASE(NAME) AS '名字',food FROM animal

#left（string,2）从左边开始取两个字节
SELECT LEFT(food,2) FROM animal
SELECT RIGHT(food,3) FROM animal
#length（）返回长度，按照字节
SELECT LENGTH(123456) FROM DUAL
SELECT LENGTH('余科葳') FROM DUAL
#replace(列, 替换前,替换后 )替换字符
SELECT NAME,REPLACE(food,'eggs','草') AS food FROM animal;
UPDATE animal SET food='草' WHERE num=2;
#strcmp逐字符比较两字符大小
SELECT STRCMP('aaa','ccc') FROM DUAL
#substring(string,a,b)截取从第a个字符开始取b个
SELECT SUBSTRING(NAME,1,2) FROM animal

#LTRIM(),RTRIM(),TRIM()去除左边，右边，两边的空格
SELECT RTRIM('ykw'         )AS NAME FROM DUAL
SELECT LTRIM(                'ykw')AS NAME FROM DUAL
SELECT TRIM(                'ykw'     )AS NAME FROM DUAL