PL SQL mysql 语法 mysql sql语句大全 |
您所在的位置:网站首页 › sql常用函数大全表图片 › PL SQL mysql 语法 mysql sql语句大全 |
一 、常用操作数据库的命令1.查看所有的数据库 : show databases;
2.创建一个数据库 : create database if not exists 数据库名;
3.删除一个数据库 : drop database if exists 数据库名;
4.选择一张表 (注意在建表之前必须要选择数据库) : use `表名`;
* --tab 键的上面,如果你的表名或字段名是一个特殊字段符,就需要带 `` *
5.在选中的数据库之中查看所有的表 : show tables;
6.查看创建数据库的语句 :show create database 数据库名;
7.查看student数据表的定义语句 :show create table 表名;
8.显示表的结构 :desc 表名;
9.删除表 : drop table 表名;
10.查看创建库的详细信息 :show create database 库名;
11.查看创建表的详细信息 : show create table 表名;二、建一张表 – 目标:创建一个schoo1数据库 – 创建学生表(列,字段)使用SQL 创建 – 学号int 登录密码varchar(20)姓名,性别varchar(2),出生日期(datatime),家庭住址,emai1 分析:id INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号’,id :字段名 (要加个票 Tab键下面的 ) INT(4):类型(长度) NOT NULL:不为空 AUTO_INCREMENT:自增 COMMENT ‘xxx’:注释 (这里 ’ ’ 是回车键隔壁的那个 ‘ ) DEFAULT ‘xxx’:默认值xxx -- 注意点,使用英文(),表的名称 和 字段 尽量使用 `` (Tab键下面的) 括起来 -- AUTO_ INCREMENT 自增 -- 字符串使用单引号括起来! -- 所有的语句后面加,(英文的),最后一个不用加 -- PRIMARY KEY 主键,一般一个表只有一个唯一的主键! CREATE TABLE IF NOT EXISTS `student2`( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT'密码', `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT'性别', `birthday` DATETIME DEFAULT NULL COMMENT'出生日期', `address` VARBINARY(100) DEFAULT NULL COMMENT'家庭住址', `email` VARBINARY(50) DEFAULT NULL COMMENT'邮箱', PRIMARY KEY(`id`) --设置主键 )ENGINE=INNODB DEFAULT CHARSET=utf8 --设置编码格式 CREATE TABLE [IF NOT EXISTS] `表名`( `字段名` 列类型[属性][索引][注释], `字段名` 列类型[属性][索引][注释], `字段名` 列类型[属性][索引][注释] (最后一句可以不要逗号) )[表类型][表的字符集设置][注释]``常用命令SHOW CREATE DATABASE school – 查看创建数据库的语句SHOW CREATE TABLE student – 查看student数据表的定义语句DESC student – 显示表的结构三、修改表的命令3.1 修改修改 1. 修改表名: alter table 旧表名 rename as 新表名 例子:alter table teacher rename as teacher1 2. 增加表的字段: alter table 表名 add 表字段名 字段类型 例子:alter table teacher1 add age int(11) 3. 修改表的字段的类型 --alter table 表名 modify 字段名 字段类型[ ] 例子:alter table teacher1 moify age varchar(11) -- 修改约束(例如:由int修改为varchar类型) 4. 修改指定的字段名 --alter table 表名 change 旧名字 新名字 字段类型[ ] 例子:alter table teacher1 change age age1 int(1) -- 字段重命名 5. 删除表的字段 : alter table 表名 deop 字段名 例子:alter table teacher1 drop age1 6. 添加字段并指定位置 alter table 表名 add 字段 字段类型 after 字段3.2 删除删除 1. 删除表(如果表存在再删除) 例子:drop table if exists teacher1四、DML 数据库操作语言(重要)4.1 添加添加 insert 语法: insert into 表名 (`字段名1`,`字段名2`,`字段名3`,……) values (`值1`,`值2`,`值3`, ……)还可以同时插入多条数据,VALUES后面的值需要使用,隔开即可 语法: insert into 表名 (`字段名1`,`字段名2`,`字段名3`,……) values (`值1`,`值2`,`值3`, ……),(`值1`,`值2`,`值3`, ……),……例子 -- 一般写插入语句,我们一定要数据和字段一一对应。 -- 插入多个字段 INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大一'); --单独只插入一个字段 INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aaaaa','男') --可以同时插入多条数据,VALUES后面的值需要使用,隔开即可 INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('李四','aaaaa','男'),('王五','23232','女')4.2 修改修改: update 修改谁(条件) set 原来的值=新值 语法: update 表名 set `字段名1` = '值1' ,`字段名2` = '值2' … where [条件]-- 修改学员名字 UPDATE `student` SET `name`='囷' WHERE id =1; -- 不指定条件的情况下,会改动所有表 UPDATE `student` SET `name`='233' -- 语法; -- UPDATE 表名 set column_name,[] = value where 条件条件:where 子句 运算符 id 等于 某个值,大于某个值,在某个区间内修改 操作符返回布尔值 注意: column_name 是数据库的列,带上条件,是筛选的条件,如果没有指定,则会修改所有的列value 是一个具体的值,也可以是一个变量多个设置的属性之间,使用英文逗号隔开UPDATE `student` SET `birthday`=CURRENT_TIME where `name`='李四' AND SEX = '男'4.3 删除delete 命令 语法 delete from 表名 [where 条件]-- 删除数据 (避免这样写,会删除所有的数据) DELETE FROM `student` -- 删除指定 DELETE FROM `student` where id= 1TRUNCATE 命令 作用:完全清空一个数据库,表的结构和索引不会变 delete 和 truncate 区别 相同点: 都能删除数据,都不会删除表结构不同: - TRUNCATE 重新设置自增列 计数器会归零 - TRUNCATE 不会影响事务测试代码: -- 测试delete 和 truncate 区别 CREATE TABLE `test`( `id` INT(4) NOT NULL AUTO_INCREMENT, `coll` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3') DELETE FROM `test` -- 不会影响自增 TRUNCATE TABLE `test` -- 自增会归零了解即可:delete删除的问题 重启数据库,现象 innoDB 自增列会从1开始(存在内存当中,断电即失)MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)五、DQL查询数据(最重点)5.1 DQL(Data Query Language) :数据查询语言 所有的查询操作都用它 Select(select 选择)简单的查询,复杂的查询它都能做数据库中最核心的语言,最重要的语句使用频率最高的语言5.2 指定查询字段1. 查询全部的学生:select *(通配符) from 表名 例子:select * from student 2.查询指定字段:select `字段1`,(英文逗号隔开)`字段2`,… from 表 例子:select `studentNo` , `studentName` from student 3.别名,给查询结果表头起一个名字 as 可以给字段起别名,也可以给表起别名 例子:selsct `studentNo` as 学号 , `studentName` as 学生名字 from student as s 4.函数 Concat(a,b)给查出来的表添加字体 例子:select concat('姓名:' ,StudentName) as 新名字 from student去重(将查询出来重复的数据去除掉) 语法:在select后面加上一个distinct select distinct `字段名` from 表名 数据库的列 (表达式) select 表达式 from 表 5.3where 条件子句作用:检索数据中符合条件的值 逻辑运算符 运算符 语法 结果 and && a and b , a&&b 逻辑与,两个都为真,结果为真 or || a or b , a||b 逻辑或,其中一个为真,则结果为真 Not != not a ,!a 逻辑非,真为假,假为真! 尽量使用英文 题目:查询考试成绩在95分到100分之间 方式一: SELECT `StduentNo`,`StudentResult` FROM result WHERE StudentResult >=95 AND StudentResult=80 -- 在这个基础上 增加一个科目 ,高等数学-2 SELECT DISTINCT s.`StudentNo`,`StudentName` FROM student s INNER JOIN result r ON r.StudentNo = s.StudentNo WHERE StudentResult>=80 AND `SubjectNo`=( SELECT Subject FROM `subject` WHERE SubjectName='高等数学-2' ) -- 查询课程为 高等数学-2 且分数不小于80分的同学的学号和姓名 SELECT s.`StudentNo`,`StudentName` FROM student s INNER JOIN result r ON s.StudentNo = r.StudentNo INNER JOIN `subject` sub ON r.`SubjectName`='高等数学-2' WHERE `SubjectaName`='高等数学-2' AND StudentResult >=80 -- 再改造 (由里即外) SELECT `StudentNo`,`StudentName` FROM student WHERE StudentNo IN( SELECT StudentNo result WHERE StudentResult >80 AND SubjectNo =( SELECT SubjectNo FROM `subject` WHERE `SubjectaName`='高等数学-2' ) )六、MySQL函数6.1 常用函数数学运算 SELECT ABS(-8) -- 绝对值 SELECT CEILING(9.4) -- 向上取整 SELECT FLOOR(9.4) -- 向下取整 SELECT RAND() -- 返回0-1随机数 SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1 -- 字符串函数 SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 返回字符串长度 SELECT CONCAT('我','爱','你') -- 拼接字符串 SELECT INSERT('我爱编程helloword',1,2,'超级热爱') -- 从某个位置开始替换某个长度 :1为替换的开启下标,2为替换的字字符的长度 结果为:超级热爱编程helloword SELECT UPPER('Abc') --小写字母 SELECT LOWER('Abc') --大写字母 SELECT INSTR('kuangshen','h') -- 返回第一次出现的字符串的索引 SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串 SELECT SUBSTR('狂神说坚持就能成功',4,6) --返回指定的子字符串(源字符串,截取的位置,截取的长度) SELECT REVERSE('清晨我上马') -- 反转 -- 查询姓 周 的同学 ,改成邹 SELECT REPLACE(studentname,'周','邹') FROM student WHERE studentname LIKE '周%' -- 时间跟日期函数(记住) SELECT CURRENT_DATE() -- 获取当前日期 SELECT CURDATE() -- 获取当前日期 SELECT NOW() -- 获取当前日期 SELECT LOCATIME() -- 本地时间 SELECT SYSDATE() -- 系统时间 SELECT YEAR(NOW()) SELECT MONTH(NOW()) SELECT DAY(NOW()) SELECT HOUR(NOW()) SELECT MINUTE(NOW()) SELECT SECOND(NOW()) -- 系统 SELECT SYSTEM_USER() SELECT USER() SELECT VERSION()6.2 聚合函数(常用)函数名称 描述 COUNT() 计数 SUM() 求和 AVG() 平均值 MAX() 最大值 MIN() 最小值 … … 分组 group by 分组后有条件判断用 naving 条件 6.3 数据库级别MD5加密(拓展)什么是MD5 主要增强算法复杂度不可逆性。 MD5不可逆,具体的MD5是一样的 MD5破解原理,背后有一个字典,MD5加密后的值,加密前的值 CREATE TABLE `testmd5`( `id` INT(4) NOT NULL, `name` VARCHAR(20) NOT NULL, `pwd` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`) )ENGINE=INNODB DEFAULT CHARSET=UTF8 -- 明文密码 INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456') -- 加密 UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1 UPDATE testmd5 SET pwd=MD5(pwd) WHERE id !=1 -- 加密全部 -- 插入时加密 INSERT INTO testmd5 VALUES(4,'小明',MD5('123456')) INSERT INTO testmd5 VALUES(5,'红',MD5('123456')) -- 如何校验,将用户传递过来的密码,进行MD5加密,然后对比加密后的值 SELECT * FROM testmd5 WHERE `name`='红' AND pwd=MD5('123456')明码 MD5加密后 插入数据的时候就加密: 七、事务7.1 什么是事务要么都成功,要么都失败 SQL执行, A给B转账 A 1000–> 200 B200SQL 执行, B收到A的钱 A800 — B400将一组SQL放在一个批次中执行 事务原则 : ACID原则 原子性,一致性,隔离性,持久性 (脏读,幻读…) 原子性(Atomicity) 要么都成功,要么都失败 一致性(Consistency) 事务前后的数据完整性要保持一致 持久性(Durability)–事务提交 事务一旦提交就不可逆转,被持久化到数据库中 隔离性 (Isolation) 事务产生多并发时,互不干扰 隔离产生的问题 脏读: 指一个事务读取了另外一个事务未提交的数据。 不可重复读: 在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对) 虚读(幻读) 是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。 (一般是行影响,多了一行) 执行事务 ========================事务=============================== -- mysql 自动开启事务提交 SET autocommit=0 -- 关闭 SET autocommit=1 -- 开启(默认的) ============================================================ -- 手动处理事务 SET autocommit =0 -- 关闭自动提交 -- 事务开启 START TRANSACTION -- 标记一个事务的开始,从这个之后的SQP都在同一个事务内 INSERT XX INSERT XX -- 提交 : 一旦数据提交,就会被持久化(成功) COMMIT -- 回滚: 回到原来的样子(失败) ROLLBACK -- 事务结束 SET autocommit = 1 -- 开启自动提交 ============================================================ -- 了解 SAVEPOINT 保存点名称 -- 设置一个事务的保存点 ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点 RELEASE SAVEPOINT 保存点 -- 删除保存点事务执行的流程图 模拟转账的例子 CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci USE shop CREATE TABLE account(id INT(3) NOT NULL AUTO_INCREMENT,name VARCHAR(30) NOT NULL,money DECIMAL(9,2) NOT NULL, PRIMARY KEY (id) )ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO account(name,money) VALUES(‘A’,2000),(‘B’,10000) – 模拟转账:事务 SET autocommit = 0; – 关闭自动提交 START TRANSACTION – 开启事务(一组事务) UPDATE account SET money = money-500 WHERE name = ‘A’ – A 转账给B UPDATE account SET money = money+500 WHERE name = ‘B’ – B 收到钱 COMMIT ; – 提交事务 ROLLBACK ; – 回滚 SET autocommit=1 – 恢复默认值 建表 CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci USE shop CREATE TABLE `account`( `id` INT(3) NOT NULL AUTO_INCREMENT, `name` VARCHAR(30) NOT NULL, `money` DECIMAL(9,2) NOT NULL, PRIMARY KEY (`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 插入数据 INSERT INTO account(`name`,`money`) VALUES('A',2000),('B',10000) =============================开启模拟转账业务==================================== -- 模拟转账:事务 SET autocommit = 0; -- 关闭自动提交 START TRANSACTION -- 开启事务(一组事务) UPDATE account SET money = money-500 WHERE `name` = 'A' -- A 转账给B UPDATE account SET money = money+500 WHERE `name` = 'B' -- B 收到钱 COMMIT ; -- 提交事务 ROLLBACK ; -- 回滚 SET autocommit=1 -- 恢复默认值 =================================================================八、索引MySQL 官方对索引的定义为:索引(Index)是帮助M有SQL高效获取的数据结构。 (如:不使用索引时0.5s —> 用了变为0.000001s) 提取句子主干,就可以得到索引的本质:索引是数据结构。 8.1索引的分类在一个表中,主键索引只能有一个,唯一索引可以有多个 主键索引 (PRIMARY KEY)唯一的标识,主键不可重复,只能有一个列作为主键唯一索引 (UNIQUE KEY)避免在一个字段中有重复的数据出现(如身份证每个人都是不一样的),唯一索引可以重复,多个字段都可以标识唯一索引常规索引(KEY/INDEX)默认的,index,key关键字来设置全文索引(FULLTEXT)在特点的数据库引擎下才有,MyISAM快速定位数据索引的使用 1.在创建表的时候给字段增加索引 2.创建完毕后,增加索引 显示所有的索引信息 show index from 表名 ============================================================================= 添加一个索引(方式一:) alter table 表 add 索引类型 index `索引名`(`字段名`) 解析:所有的改变表都是用alter 后面的那个索引名是自己起的,可以起得跟字段名一样 方式二:在建表的同时就设置索引 方式三:建一个常规索引============================================================================= explain 分析sql执行情况 explain select * from 表 --非全文索引 ```例子 索引的使用 1.在创建表的时候给字段增加索引 2.创建完毕后,增加索引 -- 显示所有的索引信息 SHOW INDEX FROM 表 -- 增加一个索引 ALTER TABLE school.student ADD FULLTEXT INDEX `srudentName`(`srudentName`) -- EXPLAIN 分析sql执行状况 EXPLAIN SELECT * FROM student -- 分析 非全文索引 sql的执行情况 EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST(`刘`);---- 分析 全文索引 sql的执行情况8.2 测试索引CREATE TABLE `app_user` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT '', `email` VARCHAR(50) NOT NULL, `phone` VARCHAR(20) DEFAULT '', `gender` TINYINT(4) UNSIGNED DEFAULT '0', `password` VARCHAR(100) NOT NULL DEFAULT '', `age` TINYINT(4) DEFAULT NULL, `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 -- 插入100万数据 DELIMITER $$ -- 写函数之前必写 CREATE FUNCTION mock_data() RETURNS INT BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE i java.util.Date类型 作用 date YYYY-MM-DD -->日期格式 time HH:mm:ss --> 时间格式 datetime (常用) YYYY-MM-DD HH:mm:ss 最常用的时间格式 timestamp 时间戳 1970.1.1到现在的毫秒数 year 年份表示 null 没有值,未知注意,不要使用null进行运算,结果为null十、数据库的字段类型(重点)unsigened: 无符号的整数声明该列不能声明负数zerofill: 0填充的10的长度 1 – 0000000001 不足位数用0 填充自增: 通常理解为自增,自动在上一条记录的基础上+1L通常用来设计唯一的主键 index,必须是整数类似可以自定义设置主键自增的起始值和步长非空 NULL not Null: 假设设置为 not null,如何不给他赋值,就会报错NULL 如果不填写,默认为NULL默认: 设置默认的值!
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |