MySQL

数据库

 

CREATE TABLE IF NOT EXISTS `student`(
            `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
            `name` VARCHAR(20) NOT NULL DEFAULT'匿名' COMMENT '姓名',
            `sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT '性别',
            PRIMARY KEY (`id`)
​
)ENGINE = INNODB DEFAULT CHARSET=utf8

--格式：
CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列类型 属性 默认值 注释
    .....
    PRIMARY KEY (`id`) --设置主键
)ENGINE = INNODB DEFAULT CHARSET=utf8 --设置引擎以及字符集
   

 

MySql数据管理

外键

CREATE TABLE `grade`(
        `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
        `gradename` VARCHAR(2) NOT NULL COMMENT '年级',
        PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
​
ALTER TABLE `student` ADD `gradeid` INT(10) NOT NULL COMMENT '年级';
​
ALTER TABLE `student` ADD  CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

 

DML语言

添加

INSERT INTO 表名(字段名1，字段名2....) VALUES()

INSERT INTO `student`(`id`,`name`,`sex`,`gradeid`) VALUES (1,'张三','男','151');
INSERT INTO `student`(`name`,`sex`,`gradeid`) VALUES ('李四','女','251');

修改

UPDATE 表名 SET 字段名+修改  条件  

UPDATE `student` SET `name`='瓜皮' WHERE `id` =1;

删除

DELETE FROM  表名 条件

DELETE FROM `student` WHERE `id`=1; --删除id为1的一行
​
TRUNCATE TABLE `student`; --清空student表

DQL

查询指定字段

-- 查询所有的学生  SELECT 字段 FROM 表名
SELECT * FROM `student`;
-- 查询指定字段
SELECT `studentname` FROM `student`;
-- 别名
SELECT `studentname` AS 沙雕 FROM `student`;
-- 函数 ConCAT
SELECT CONCAT('姓名:',`studentname`) AS 新名字 FROM `student`;

模糊查询

-- like
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%张%';
-- in
SELECT `studentno`,`address` FROM `student`
WHERE `address` IN ('北京朝阳','广东深圳');

注意：

in查询的限定条件不能有数据表中没有的数据。

 

笛卡尔积现象

给表起别名：效率高，可读性高

避免笛卡尔积现象，需要加条件过滤，加了条件过滤后，底层匹配次数减少了？答案是没有，还是那么多次，但是显示出的数据为有效数据，即所需要的数据。

连接方式

SQL99：
内连接  
语法：
    …
      A
    (inner) Join
      B
    On
       连接条件
    Where
    …
括号内的inner可以省略
​
​
外连接
语法：
    …
      A
    left Join
      B
    On
       连接条件
    Where
    …
或者
    …
      A
    right Join
      B
    On
       连接条件
    Where
    …

 

子查询

-- 找出每个部门平均薪水的薪资等级
-- 在FROM语句后嵌套SELECT语句，即子查询语句
​
-- 第一步，找出每个部门的平均薪水
SELECT 
    `deptno`,AVG(sal) AS avgsal
FROM
    `emp` GROUP BY `deptno`;
    
-- 第二步，让上面的表与薪资等级表连接
SELECT
    t.*,s.GRADE
FROM
    (SELECT `deptno`,AVG(sal) AS avgsal FROM `emp` GROUP BY `deptno`) t
JOIN
    salgrade s
ON
    t.avgsal BETWEEN s.LOSAL AND s.HISAL;
    
​
​
+--------+-------------+-------+
| deptno | avgsal      | GRADE |
+--------+-------------+-------+
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+

-- 在SELECT后嵌套子查询（没必要）
--案例：找出每个员工所在部门的名称，要求显示员工名和部门名
-- 分析：
SELECT
    e.ename, 
    (SELECT d.dname FROM dept d WHERE e.deptno = d.deptno) AS dname
FROM
    emp e;
    
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
    
    

limit

每页显示多少条数据：

第pageNO页，(pageNO-1)*pageSize, pageSize

 

约束

1、非空约束

not null

2、唯一性约束

unique

-- 唯一性约束修饰的字段，不能重复，但可以为null
CREATE TABLE IF NOT EXISTS t_user(
    id INT,
    name VARCHAR(255) UNIQUE
);
INSERT INTO t_user VALUES(1,'zhangsan');
/*
mysql> INSERT INTO t_user VALUES(12,'zhangsan');
1062 - Duplicate entry 'zhangsan' for key 'name'
*/
INSERT INTO t_user(id) VALUES(2);
INSERT INTO t_user(id) VALUES(3);
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | NULL     |
|  3 | NULL     |
+----+----------+

两列或多列添加unique

分为联合添加约束，和单独约束
drop table if exists t_user;
create table t_user(
    id INT,
    userno varchar(255),
    username VARCHAR(255),
    unique(username,userno)  --表级约束
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
select * from t_user;
+------+----------+--------+
| id   | username | userno |
+------+----------+--------+
|    1 | 111      | zs     |
|    2 | 111      | ls     |
|    3 | 222      | zs     |
+------+----------+--------+
​
drop table if exists t_user;
create table  t_user(
    id INT,
    userno varchar(255) unique,
    username VARCHAR(255) unique
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
/*
mysql> insert into t_user values(2,'111','ls');
ERROR 1062 (23000): Duplicate e/ntry '111' for key 'userno'
*/

3、主键约束

一张表的主键约束只有一个。auto_increment 自增，主键不可为空，不可重复，一般采用自然主键。

drop table if exists t_user;
create table  t_user(
    id INT primary key aotu_increment,
    userno varchar(255) 
    username VARCHAR(255) 
);

 

4、外键约束

foreign key(字段名) references 引用表名(引用字段名)

CREATE TABLE `grade`(
        `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
        `gradename` VARCHAR(2) NOT NULL COMMENT '年级',
        PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
​
ALTER TABLE `student` ADD `gradeid` INT(10) NOT NULL COMMENT '年级';
​
ALTER TABLE `student` ADD  CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);