mysql代码综合

`testtable`mysql -u root -pSHOW DATABASES;CREATE DATABASE mydb1 mydb2;CREATE DATABASE mydb2 CHARACTER SET gbk;CREATE DATABASE IF NOT EXISTS mydb4;#不存在才创建SHOW CREATE DATABASES mydb1;SHOW GLOBAL VARIABLES LIKE \'port\';ALTER DATABASE mydb2 CHARACTER SET gbk;DROP DATABASE mydb1;DROP DATABASE mydb2;DROP DATABASE mydb3;DROP DATABASE mydb4;#带空格的库名删除——用反引号（重音符号）``括起来DROP DATABASES `mydb aaa`;SELECT DATABASE();#查看使用的数据库USE mydb2;#`Name`使用数据库#查询SELECT NAME FROM city;#查询name列SELECT * FROM city;#查询所有列 *效率低，可读性差SELECT NAME ,Population*12 FROM city;#数据可以进行运算SELECT NAME ,Population*12 AS ‘人口12倍‘ FROM city;#用as为运算后的列起名字SELECT DISTINCT Population FROM city;#DISTINCT去除重复的数据，保留唯一性SELECT DISTINCT Population FROM city ORDER BY Population ASC;#desc 降序排序 asc升序排序SELECT DISTINCT Population,B FROM city ORDER BY Population ASC,B ASC;#Population数据相同时按B的升序来排列SELECT DISTINCT Population FROM city WHERE Population=300;#where 条件 = > <  != <>最后2个都是不等于SELECT DISTINCT Population FROM city WHERE Population!=300;SELECT DISTINCT Population FROM city WHERE Population<>300;SELECT  NAME,Population FROM city WHERE NAME=\'Fakaofo\' AND Population=300;#and or notSELECT Population FROM city WHERE Population BETWEEN 300 AND 20000;SELECT Population FROM city WHERE Population>=300 AND Population<=20000;#和上面等价SELECT Population FROM city WHERE Population IS NOT NULL;#查询nullSELECT Population FROM city WHERE Population IN(300,5200,2345);#枚举查询 效率低 可用多个and代替#模糊查询SELECT Population FROM city WHERE Population LIKE _%;# _表示单个任意字符 %表示任意长度的任意字符#分支查询SELECT Population,NAME,CASEWHEN Population<10000 THEN \'A\'WHEN Population>=10000 AND Population<=40000 THEN \'B\'ELSE \'C\'END AS \'level\'FROM city ORDER BY LEVEL ASC;#时间查询SELECT SYSDATE() AS TIME;SELECT CURDATE() AS DATE;SELECT CURTIME() AS \'时分秒\';SELECT WEEK(SYSDATE()) AS 一年中的第几周;SELECT YEAR(SYSDATE()) AS 日期中的年份;SELECT HOUR(SYSDATE()) AS  小时值;SELECT MINUTE(SYSDATE()) AS  分钟值;SELECT DATEDIFF(\'2020-4-5\',\'2020-4-1\') AS 相隔天数;#指定日期间的相隔天数SELECT ADDDATE(\'2020-4-1\',4) AS 隔n天后的日期;#n天后的日期#字符串查询SELECT CONCAT(\'My\',\'s\',\'q\',\'l\') AS 拼接结果;#字符串拼接SELECT CONCAT(NAME,population) AS 拼接结果 FROM city;#insert(str,pos,len,newstr) 将set中pos位置后的len长度字符替换成newstr 数据库中下标从1开始SELECT INSERT(\'这是一个数据库\',3,2,\'mysql\');#2代表’一个‘，替换成了mysqlSELECT LOWER(\'POWER\');SELECT UPPER(\'power\');#SUBSTRING(str,num,len) 将str指定num位置开始截取len个内容SELECT SUBSTRING(\'i love mysql\',3,6);#聚合函数SELECT SUM(Population) AS 所有人口 FROM city;SELECT AVG(Population) AS 平均值 FROM city;SELECT MAX(Population) AS MAX FROM city;SELECT MIN(Population) AS MIN FROM city;SELECT COUNT(Population) AS 行数 FROM city;#聚合函数会自动忽略null值#分组查询SELECT IsOfficial,SUM(Percentage) FROM countrylanguage GROUP BY IsOfficial;#分组求和 按IsOfficial的值分组SELECT CountryCode,IsOfficial,SUM(Percentage) FROM countrylanguage GROUP BY CountryCode,IsOfficial;#多列分组#分组查询中 select显示的列只能是分组依据列，聚合函数列 不可是其他的列#分组过滤查询SELECT CountryCode,SUM(Percentage) FROM countrylanguage GROUP BY CountryCode HAVING CountryCode=\'ZWE\';#多列分组#限定查询 limit 起始行 查询行数 起始行从0开始SELECT * FROM city LIMIT 0,5;SELECT * FROM city LIMIT 5,5;#一般用来分页 起始行变，行数不变#语句编写顺序#select 列名 from 表名 where 条件 group by 分组 having 过滤条件 order by 排序(desc/asc) limit 起始行,总行数#执行顺序#from 	where 	group by  having    select    order by    limit#查询的进阶#子查询 一行一列SELECT NAME,Population FROM city WHERE Population>(SELECT Population FROM city WHERE NAME=\'Herat\') ORDER BY Population ASC;#子查询得到一行一列的结果 才可以做外部查询的条件判断#子查询做枚举查询的条件SELECT CountryCode,NAME,Population FROM city WHERE Population IN(SELECT Population FROM city WHERE CountryCode=\'NLD\') ORDER BY Population ASC;#子查询 一行多列#all 高于所有,要高于数值最高的    any 高于部分,只要高于数值最低的就算SELECT CountryCode,NAME,Population FROM cityWHERE Population >ALL(SELECT Population FROM city WHERE CountryCode=\'NLD\')ORDER BY Population ASC;SELECT CountryCode,NAME,Population FROM cityWHERE Population >ANY(SELECT Population FROM city WHERE CountryCode=\'NLD\')ORDER BY Population ASC;#子查询 多行多列SELECT NAME,Population FROM(SELECT NAME,Population FROM city ORDER BY Population ASC) AS tempLIMIT 0,5;#聚合函数嵌套子查询 要用as取名提到临时表格SELECT MAX(ttt) FROM (SELECT Population AS ttt FROM city WHERE CountryCode=\'NLD\') AS aaa;#合并查询 要求列数相同 列数据类型可以不同#union 去除重复的数据 union all 不去除(SELECT NAME,Population FROM city LIMIT 0,5) UNION (SELECT NAME,Population FROM country LIMIT 0,5);#表连接查询#1内连接查询SELECT * FROM mytable1 INNER JOIN mytable2 ON mytable1.`工作`=mytable2.`工作`;#符合sql标准SELECT * FROM mytable1,mytable2 WHERE mytable1.`工作`=mytable2.`工作`;#mysql独有写法#左外连接查询SELECT * FROM mytable1 LEFT JOIN mytable2 ON mytable1.`工作`=mytable2.`工作`;#有链接查询SELECT * FROM mytable1 RIGHT JOIN mytable2 ON mytable1.`工作`=mytable2.`工作`;#DML操作（增删改）#新增INSERT INTO mytable1(工作,姓名,年龄) VALUE (\'机械\',\'吴天\',23);#修改UPDATE mytable1 SET 姓名=\'郭二\' WHERE 姓名=\'吴天\';#删除DELETE FROM mytable1 WHERE 姓名=\'郭二\';#删除时不加where限定条件的话，删除的是整张表的数据#delete 只会删除表的数据，对表的结构不造成影响#清空整表数据TRUNCATE TABLE mytable1;#与delete删除整表数据不同，truncate是把表销毁，再按原表格式创建一个新表#delete删除时，会n行受到影响，truncate是0行受到影响。#创建一个表CREATE  TABLE testtable (id   INT,姓名 VARCHAR(20),年龄 INT)CHARSET=utf8;SELECT * FROM testtable;INSERT INTO testtable(id,姓名,年龄) VALUE (1,\'张三\',25);#给现有表添加一列ALTER TABLE testtable ADD 工作 VARCHAR(255);#修改现有表的数据类型或者约束ALTER TABLE testtable MODIFY 工作 VARCHAR(100);#删除现有表的一列ALTER TABLE testtable DROP 工作;#修改现有表的列名ALTER TABLE testtable CHANGE 工作 方向 VARCHAR(255);#删除表DROP TABLE testtable;#约束#实体完整性约束#表中每一行数据代表一个实体 实体完整性约束是指每一行数据不重复，实体唯一。#主键约束#primary key 唯一，标识表中的一行数据，此列的值不可重复，且不能为nullCREATE  TABLE testtable (id   INT PRIMARY KEY,姓名 VARCHAR(20),年龄 INT)CHARSET=utf8;#插入2遍会报错：Duplicate entry \'1\' for key \'PRIMARY\'  主键1重复INSERT INTO testtable(id,姓名,年龄) VALUE (1,\'张三\',25);#唯一约束#UNIQUE 标识表中的一行数据，此列的值不可重复，可以为nullDROP TABLE testtable;CREATE  TABLE testtable (id   INT UNIQUE,姓名 VARCHAR(20),年龄 INT)CHARSET=utf8;#id为null时可以无限重复添加INSERT INTO testtable(id,姓名,年龄) VALUE (NULL,\'张三\',25);#自动增长列#AUTO_INCREMENT 给主键数值添加自动增长。从1开始，每次加1。不能单独使用，和主键配合。DROP TABLE testtable;CREATE  TABLE testtable (id   INT PRIMARY KEY AUTO_INCREMENT,姓名 VARCHAR(20),年龄 INT)CHARSET=utf8;INSERT INTO testtable(姓名,年龄) VALUE (\'张三\',25);#域完整性约束#限制单元格数据正确性#1.非空约束not nullDROP TABLE testtable;#姓名唯一，不为空CREATE  TABLE testtable (id   INT PRIMARY KEY AUTO_INCREMENT,姓名 VARCHAR(20) UNIQUE NOT NULL,年龄 INT)CHARSET=utf8;#错误: Column \'姓名\' cannot be nullINSERT INTO testtable(姓名,年龄) VALUE (NULL,25);#2.默认值约束defaultDROP TABLE testtable;#年龄默认值为0CREATE  TABLE testtable (id   INT PRIMARY KEY AUTO_INCREMENT,姓名 VARCHAR(20) UNIQUE NOT NULL,年龄 INT DEFAULT 0)CHARSET=utf8;#年龄使用默认值INSERT INTO testtable(姓名,年龄) VALUE (\'张三\',DEFAULT);DROP TABLE testtable;#引用完整性约束#涉及到两张表 一个主表 一个从表#constraint 引用名 foreign key(列名) references 被引用的表名(列名)#创建一个专业表 主表CREATE  TABLE Speciality (id   INT PRIMARY KEY AUTO_INCREMENT,SpecialName VARCHAR(20) UNIQUE NOT NULL)CHARSET=utf8;INSERT INTO Speciality(SpecialName) VALUES (\'java\');INSERT INTO Speciality(SpecialName) VALUES (\'h5\');#创建一个课程表 从表CREATE TABLE `subject`(subjectid  INT PRIMARY KEY AUTO_INCREMENT,subjectname VARCHAR(20) UNIQUE NOT NULL,subjecthours INT DEFAULT 10,specialid INT  NOT NULL,#插入的数据中的specialid必须在Speciality专业表里存在 否则报错CONSTRAINT fk_subject_specialid FOREIGN KEY (specialid) REFERENCES Speciality(id))CHARSET=utf8;INSERT INTO `subject`(subjectname,subjecthours,specialid) VALUES(\'javase\',30,2);INSERT INTO `subject`(subjectname,subjecthours,specialid) VALUES(\'css\',20,1);#当2个表存在引用关系时，要删除表，需要先删除从表，在删除主表DROP TABLE `subject`;DROP TABLE `Speciality`;#约束创建整合CREATE TABLE Grade(Gradeid INT PRIMARY KEY AUTO_INCREMENT,Gradename VARCHAR(20) UNIQUE NOT NULL)CHARSET=utf8;CREATE TABLE student(student_id VARCHAR(50) PRIMARY KEY,student_name VARCHAR(50) NOT NULL,sex CHAR(2) DEFAULT \'男\',borndate DATE NOT NULL,phone VARCHAR(11),gradeid INT NOT NULL,CONSTRAINT fk_student_grade FOREIGN KEY(gradeid) REFERENCES Grade(Gradeid))CHARSET=utf8;INSERT INTO Grade(Gradename) VALUE (\'软工\');INSERT INTO Grade(Gradename) VALUE (\'计科\');INSERT INTO Grade(Gradename) VALUE (\'物联网\');INSERT INTO student(student_id,student_name,sex,borndate,phone,Gradeid)VALUE (\'20200905001\',\'李四\',DEFAULT,\'2020-09-05\',\'130--------\',1);DROP TABLE Grade;DROP TABLE student;#事务/*事务是一个最小执行单元，有多个sql语句组成，一个事务中，所有的sql语句执行完成功后，整个事务成功，有一个sql语句失败，整个事务执行失败事务的边界:开始：连接到sql,执行一条dml语句 或者 上一个事务结束，又输入一条DML语句结束：1.提交 a.显示提交：commit b.隐式提交：一条创建、删除语句，正常退出2.回滚 a.显示回滚:rollback b.隐式回滚：非正常退出(断电、宕机)*//*数据库为每一个客户端都维护一个空间独立的缓存区,叫做回滚段，所有增删改语句的执行结果都缓存在其中，只有事务中所有sql语句都正常结束commit，才将回滚段中缓存的数据同步到数据库，否则，整个事务回滚rollback。事物的特性:1.Atomicity: 原子性，最小不可拆分2.Consistency:一致性，从一个一致状态到另一个一致状态，一个失败都返回原状态3.Isolation:隔离性，多个事物之间互相隔离互不影响。事务执行前后结果可见，中间不可见4.Durability:持久性，对系统影响是永久性的*/CREATE TABLE testtable(id INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20) NOT NULL,age INT DEFAULT 20,money INT DEFAULT 0)CHARSET=utf8;INSERT INTO testtable(`name`,money) VALUE (\'张三\',2000);INSERT INTO testtable(`name`,money) VALUE (\'李四\',3000);DELIMITER //  #创建存储过程DECLARE t_error INTEGER DEFAULT 0;  #定义一个int类型变量t_error初值为0#声明mysql遇到异常时继续(CONTINUE)执行 并将t_error设为1DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;START TRANSACTION;UPDATE testtable SET money=money-1000 WHERE NAME=\'张三\';UPDATE testtable SET money=moneys+1000 WHERE NAME=\'李四\';#这一句出错#判断出错回滚，不出错提交IF t_error = 1 THENROLLBACK;ELSECOMMIT;END IF;//DROP TABLE testtable;#权限管理#创建用户CREATE USER `zzb` IDENTIFIED BY \'123\';#授权  将mysql1中的所有表授权给zzb用户GRANT ALL ON mysql1.* TO `zzb`;#撤销权限#撤销权限后账户要重新连接客户端才生效REVOKE ALL ON mysql1.* FROM `zzb`;#删除用户DROP USER `zzb`;#视图CREATE VIEW shitu1 AS SELECT 姓名,年龄,工作 FROM mytable1;SELECT * FROM shitu1;CREATE OR REPLACE VIEW shitu1 AS SELECT 工作,姓名,年龄 FROM mytable1 WHERE 工作 IS NOT NULL;ALTER VIEW shitu1 AS SELECT 工作,姓名,年龄 FROM mytable1 WHERE 工作 IS NOT NULL;SELECT * FROM shitu1;DROP VIEW shitu1;