mysql笔记

数据库查询语言(DQL, database QUERY LANGUAGE):对表的查询语句，例如select等。
数据库定义语言(DDL, database defined LANGUAGE):例如create database、drop database、create table、drop table等
数据库操作语言(DML, database manage LANGUAGE):例如update、insert、delete等.

DDL
1.创建库、创建表使用关键字create
2.删除库、删除表使用关键字drop
3.修改库、修改表使用关键字alter
4.查询库、查询表使用关键字show、select
DML
1.添加数据使用insert
2.删除数据使用delete
3.修改数据使用update
DQL
1.查询数据使用select
连接mysql：
mysql -uroot -p1992

库

创建库：
CREATE DATABASE dt55;
创建库的时候指定编码：
CREATE DATABASE dt55 CHARACTER SET utf8;
删除库：
DROP DATABASE 库名;
选中一个数据库：
USE dt55;
查看库的信息：
SHOW CREATE DATABASE dt55;
查看mysql服务器软件下所有的库
SHOW DATABASES;
查看当前用户连接的是哪个数据库
select DATABASE();
查看库下面的表
SHOW TABLES;
修改指定库的编码：
alter database 库名 character set 新编码名;
数据库备份:
通过命令备份

mysqldump -uroot -p19921127 account>/Users/wangkai/Desktop/databaseback/account_back.sql

还原

执行命令，进入mysql
创建数据库，因为备份的sql里没有创建数据库
create database account character set utf8;
use account;
source /Users/wangkai/Desktop/databaseback/account_back.sql

还可以通过图形化工具进行备份。

表

mysql中常见的数据类型：

varchar(n)、float、int(n)、biting(n)、date、datetime、text

默认值（DEFAULT），
非空(NOT NULL)，添加数据时必须填写。
主键(PRIMARY KEY),不能重复,一张表只要一个字段最为主键
唯一键(UNIQUE),被unique修饰的数据不能够重复
自动增长(AUTO_INCREMENT)尽量作用在int类型字段上
创建表:

DEFAULT是默认值
COMMENT是备注，永远写最后
CREATE TABLE Book(
id INT(20) AUTO_INCREMENT PRIMARY KEY COMMENT '图书编号',
gender VARCHAR(2) DEFAULT '男' COMMENT '性别',
stuName VARCHAR(40) NOT NULL COMMENT '学生姓名',
phone VARCHAR(20) UNIQUE COMMENT '手机号'
)

删除表:DROP TABLE 成绩表;
查看表结构：DESC students;
打印bank表sql创建信息：SHOW CREATE TABLE bank;
对已经存在的表进行重命名:
RENAME TABLE 旧表名 TO 新表名;
往已经存在的表添加字段信息:
ALTER TABLE bank ADD 字段名 VARCHAR(2);
删除某一张表中的字段:
ALTER TABLE bank DROP gender;
对表中字段进行重命名：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新字段数据类型
修改某一字段的数据类型长度：
ALTER TABLE bank CHANGE username username VARCHAR(50);
修改备注
ALTER TABLE Publisher MODIFY COLUMN P_LINKMAN VARCHAR(20) COMMENT '出版社联系人'
复制一张指定的表以及数据

CREATE TABLE aaa(
  SELECT * FROM `publisher`
);

往表中添加、删除、修改、查询数据(CRUD)

逻辑运算符与(and)，或(or)，非(not)

往指定的表中添加数据：

INSERT INTO `成绩表`(姓名,班级,成绩) VALUES('王凯','dt55班',90.5);
或者省略字段名，VALUES必须是全部的
INSERT INTO `成绩表` VALUES('王凯','dt55班',90.5);
或者（这种是mysql扩展语法）
INSERT INTO `成绩表` SET 姓名='wankgai',班级='3班',成绩='100';
同时插入多条数据：

INSERT INTO `成绩表`(姓名, 班级, 成绩)
VALUES('张飞', '7班', 100), ('赵云', '8班', 20), ('关于', '9班', 30);

查询插入
INSERT INTO aaa SELECT * FROM aaa;

删除数据

DELETE FROM `成绩表` WHERE 姓名='wangkai';
删除所有数据：
此处delete可以删除整张表，但是删除数据后，自增列不会从1开始
DELETE FROM students WHERE 1=1
如果要删除一整张表中的数据，使用truncate，删除数据后，如果字段是自增的，则重新从1开始
TRUNCATE TABLE students

查询表中的所有数据记录

查询所有字段
SELECT 姓名, 班级, 成绩 FROM `成绩表`;或者SELECT * FROM `成绩表`;
查询单个字段
SELECT 姓名 FROM `成绩表`;
条件查询
SELECT 姓名 FROM `成绩表` WHERE 成绩>=90;
取别名 （AS可以省略）
SELECT 姓名 AS 名字 FROM `成绩表` WHERE 成绩>=90;
多条件查询
SELECT * FROM `成绩表` WHERE 班级='dt55班' AND 成绩>=90;
表也可以取别名
SELECT * FROM `成绩表` s / SELECT s.姓名 FROM `成绩表` s
排序时字段可以是数值类型(int、float),也可以是varchar类型
如果varchar类型对应的字段存放的是中文，则不能够排序,如果是英文可以排序
降序查询(DESC)
SELECT * FROM users ORDER BY javaScore DESC;
升序查询(ASC)
SELECT * FROM users ORDER BY javaScore ASC;
同时查询多条记录IN()
SELECT * FROM users WHERE id=1 OR id=2 OR id=4;
SELECT * FROM users WHERE id IN(1,2,4);
SELECT * FROM users WHERE id NOT IN(3,5);
分组查询
SELECT goodCategory FROM goods GROUP BY goodCategory;
查询goods表中是否有种类为数码的类型
SELECT goodCategory FROM goods GROUP BY goodCategory HAVING goodCategory='数码';
分页查询，goods表中有7条数据记录，每页显示3条，总共可以分三页
第一页
SELECT * FROM goods LIMIT 0,3;
第二页
SELECT * FROM goods LIMIT 3,3;
第三页
SELECT * FROM goods LIMIT 6,3;
分页查询公式
SELECT * FROM goods LIMIT (pageNo-1)* pageSize,pageSize;

更新表中的数据

UPDATE students SET stuName='刘备',age=55 WHERE id=1;

函数

--聚合函数--
在mysql中函数使用select关键字调用:select 函数名(字段) 【FROM 表名】
找出最大值：max(字段名)
SELECT MAX(javaScore) AS 最低分 FROM users;
找出最小值：min(字段名)
SELECT MIN(javaScore) AS 最低分,username FROM users;
求平均数：avg(字段名)
SELECT AVG(javaScore) AS 平均分 FROM users;
求和：
SELECT SUM(javaScore) AS 总分数 FROM users;
统计记录：如果统计的字段值为null，则此字段对应的数据不在统计之内
SELECT COUNT(*) AS 总条数 FROM users;
--常用函数--
NOW():获取当前系统时间，时间格式包括年月日时分秒
SELECT NOW() AS 当前系统的时分秒;
CURTIME():只获取系统的时分秒
SELECT() CURTIME();
CURDATE():只获取系统的年月日
SELECT() CURDATE();
格式化时间

SELECT personName,DATE_FORMAT(birthday,'%Y年%m月%d日 %H:%i:%s') AS birthday FROM `person`;

--数学函数--
向上取舍：SELECT CEIL(1.5)
向上取舍：SELECT FLOOR(1.5)
随机数： SELECT RAND()

多表查询
屏幕快照 2019-02-22 下午2.39.27.png

屏幕快照 2019-02-22 下午2.39.15.png
SELECT u.username,temp.minScore 
FROM users u, (SELECT MIN(javaScore) AS minScore FROM users) temp
WHERE u.javaScore=temp.minScore;

查询所有部门中的所有员工
SELECT d.deptName,p.empName FROM dept d,emp p WHERE d.id=p.deptId;
找出'开发部'中的所有员工名、薪水、部门名
SELECT d.deptName,e.empName,e.salary FROM dept d,emp e WHERE d.id=e.deptId AND d.deptName='开发部';
找出'开发部'和'测试部'中的所有员工名、薪水、部门名
--第一种方式--

SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName IN ('开发部','测试部');

--第二种方式--

SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptID AND (d.deptName='开发部' OR d.deptName='测试部');

--第三种方式--
union、union all:可以将两个查询语句的结果进行合并，合并的前提是两个查询语句的数据结构是一样的
union可以去重
union all不可去重

SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptID AND d.deptName='开发部'
UNION ALL
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptID AND d.deptName='测试部'

多表查询语法1:select * from 表1，表2...表n where 条件
多表查询方式2：通过连接关键字
内连接
外连接
左外连接
右外连接
--内连接--
表1 inner join 表2 on 条件(多个表之间有关联的条件)
SELECT 字段名1，字段名2...字段n FROM 表1 别名1 INNER JOIN 表2 别名2 ON 条件 WHERE 条件
1.查询所有部门中所有员工

SELECT d.deptName,e.empName,e.salary
FROM dept d INNER JOIN emp e
ON d.id=e.deptID;

2.找出'开发部'中的所有员工名、薪水、部门名

SELECT d.deptName,e.empName,e.salary
FROM dept d INNER JOIN emp e 
ON d.id=e.deptID WHERE d.deptName='开发部';

模糊查询
查询book表中B_AUTHOR以’关‘开头的所有书籍
SELECT * FROM book WHERE B_AUTHOR LIKE '关%';
查询book表中B_AUTHOR以’羽‘结尾的所有书籍
SELECT * FROM book WHERE B_AUTHOR LIKE '%羽';
查询book表中B_AUTHOR包含’羽‘的所有书籍
SELECT * FROM book WHERE B_AUTHOR LIKE '%羽%';
--外链接--
==左外链接==
当条件不满足，以左边的表为主
SELECT p.p_NAME,p.p_LINKMAN,b.B_NAME,b.B_PRICE,b.B_AUTHOR FROM publisher p LEFT JOIN book b ON b.P_ID=p.P_ID
==右外链接==
当条件不满足，以右边的表为主
SELECT p.p_NAME,p.p_LINKMAN,b.B_NAME,b.B_PRICE,b.B_AUTHOR FROM publisher p RIGHT JOIN book b ON b.P_ID=p.P_ID
==3表联查==

屏幕快照 2019-02-25 下午2.56.57.png

屏幕快照 2019-02-25 下午2.56.43.png

屏幕快照 2019-02-25 下午2.57.06.png

关羽具体买了哪些商品(商品名，价格，客户名，客户手机号)

SELECT g.goodName,g.price,c.customerName,c.phone
FROM goods g INNER JOIN goods_customer gc INNER JOIN customer c
ON g.id=gc.goodId AND gc.customerId=c.id WHERE c.customerName='关羽';

视图

应用行业，金融行业，保险行业，财务行业
创建视图

CREATE VIEW view_all
AS SELECT e.id AS empId,e.empName,e.salaary,e.phone,d.deptName
FROM dept d INNER JOIN amp e ON d.id=e.deptId

删除视图
DROP VIEW view_all
查询某一视图中的数据
SELECT * FROM view_all WHERE empId=1;
删除视图重的某一条数据
DELETE FROM view_all WHERE empId=1;
往视图中添加数据
INSERT INTO view_emp SET empName='wangkai'
修改视图
UPDATE view_emp SET empName='lisi' WHERE id=7
删除，更改，添加会影响原来的表

事务

开启事务：start TRANSACTION
回滚事务（如果事务提交后，不能够回滚）：rollback;
提交事务：commit
事务的四大特性
1.原子性(automic)
多组操作不能分割，必须是一个整体
2.一致性(consisten)
事务操作前和事务操作后总量保持一致
3.隔离性(isolation):多个事务之间互不干扰
四种隔离级别read uncommitted,read committed,repeatable read,Serializable
查看隔离级别select @@transaction_isolation
修改mysql默认的隔离级别

set global transaction isolation level 隔离级别

不同的隔离级别会引发不同的问题
当mysql事务的隔离级别为read uncommitted时，会引发脏读
3.1脏读，
一个事务可以读取另一个事务未提交的数据
如何解决脏读：可以将事务的隔离级别改为read committed
当mysql软件的事务隔离级别为read committed的时候，会引发不可重复读。
如何解决不可重复读：将事务的隔离级别改为repeatable read
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
当 mysal软件的事务隔离级别为REPEATABLE READ时候，会引发虚读（幻读）
3.2虚读与幻读，
3.3不可重复读
4.持久性(durable)

存储过程

语法:
CREATE PROCEDURE 存储过程名{参数名1 参数类型1,参数名2 参数类型2}

DELIMITER //
CREATE PROCEDURE addSalary{money FLOAT, idd BIGINT}
BEGIN
 UPDATE 'emp' SET salary=salary+money WHERE id=idd
END//
DELIMITER ;

调用存储过程：call 存储过程名（）
CALL addSalary(1000,9)
删除存储过程
DROP PROCEDURE 'addSalary'
DROP PROCEDURE IF EXISTS 'addSalary'
带返回值的存储过程

DELIMITER //
CREATE PROCEDURE test1(IN i FLOAT, IN j FLOAT, OUT num FLOAT)
BEGIN
  SET num=i+j;
END//
DELIMITER  ;

CALL 'test1'(10,20,@result)
SELECT @result

带if的存储过程

DELIMITER //
CREATE PROCEDURE pro_addSalary(idd BIGINT,m FLOAT)
BEGIN
    IF(m>0) THEN
        UPDATE users SET money=money+m WHERE id=idd;
    ELSEIF m>10000 THEN
        SELECT '超出限额！！！' AS '友情提示';
    ELSE
        SELECT '您输入的金额不能够为负数！！！' AS '友情提示';
    END IF;
END//
DELIMITER ;

CALL pro_addSalary(1, 500);

case选择分支结构

DELIMITER
CREATE PROCEDURE pro_case(i INT)
BEGIN
    CASE i
        WHEN 1 THEN
            SELECT '星期一' AS '日期';
        WHEN 2 THEN
            SELECT '星期二' AS '日期';
        ELSE
          SELECT '不是周一，也不是周二' AS '日期';
    END CASE;
END//
DELIMITER ;

CALL `pro_case`(3);

while循环

DELIMITER //
CREATE PROCEDURE pro_while(IN i INT)
BEGIN
    DECLARE a INT DEFAULT 1;
    WHILE a<=i DO
        INSERT INTO `users` SET username='xxx',money=100;
        SET a=a+1;
    END WHILE
END//
DELIMITER ;

CALL pro_while(10);

-- 计算1到100之间的所有数字之和并返回
DELIMITER $$
CREATE PROCEDURE pro_while(IN i INT,OUT total INT)
BEGIN
    DECLARE a INT DEFAULT 1;
    SET total=0;
    WHILE a<=i DO
        SET total=total+a;
        SET a=a+1;
    END WHILE
END $$
DELIMITER ;

CALL pro_while(100,@aaa);
SELECT @aaa

loop循环
在user表中添加100条记录

DELIMITER //
CREATE PROCEDURE pro_loop()
BEGIN
    DECLARE i INT DEFAULT 0;
    loop_test:LOOP
        INSERT INTO `user` SET username='admin',money=200;
        SET i=i+1;
        IF(i=100) THEN
            LEAVE loop_test;
        END IF
    END LOOP    
END//
DELIMITER ;

找出1-100之间能够被3整除的所有数之和

DROP PROCEDURE IF EXISTS `pro_sum`;
DELIMITER //
CREATE PROCEDURE `pro_sum`()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;
    WHILE i<=10 DO
        IF i MOD 3=0 THEN
            SET total=total+i;
        END IF;
        SET i=i+1;
    END WHILE;
    SELECT total;
END//
DELIMITER ;

把某一个查询结果放到另一个变量里面用INTO

-- 将users表中的所有money之和返回
DELIMITER //
CREATE PROCEDURE pro_qiuSum(OUT total FLOAT)
BEGIN
    SELECT SUM(money) INTO total FROM `users`;
END//
DELIMITER ;
CALL pro_qiuSum(@a);
SELECT @a;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,332评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,508评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,812评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,607评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,728评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,919评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,071评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,802评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,256评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,576评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,712评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,389评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,032评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,798评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,026评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,473评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,606评论 2赞 350

推荐阅读更多精彩内容

  • MYSQL基础
    MYSQL 基础知识 1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 MySQL基本操...
    Kingtester阅读 7,790评论 5赞 116
  • mySQL笔记
    1、MySQL启动和关闭（安装及配置请参照百度经验，这里不再记录。MySQL默认端口号：3306；默认数据类型格式...
    强壮de西兰花阅读 643评论 0赞 1
  • mysql笔记(转)
    今天看到一位朋友写的mysql笔记总结，觉得写的很详细很用心，这里转载一下，供大家参考下，也希望大家能关注他原文地...
    信仰与初衷阅读 4,726评论 0赞 30
  • 觉悟
    寻找美妙 寻找希望 寻找相逢的瞬间 寻找顿悟的顺畅 一直在寻找 还来不及祈祷 藏地的松石挂在胸膛 游曳的灵魂 时刻...
    水石客阅读 317评论 3赞 3
  • 2018-12-12
    在商丘已经呆了将近一个月。这是一个新的购物中心，里面的员工也没有工作经验，整个项目管理是混乱而无序的，员工团队也不...
    言几又阅读 300评论 0赞 1