MySql语句

一:DDL语句整理:

创建表:

CREATE TABLE 表名(

      列名1 列类型1,

      列名2 列类型2);

删除表:

DROP TABLE 表名;

修改表名:

ALTER TABLE 表名 RENAME TO 新表名;

修改列名:

ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

修改列类型:

ALTER TABLE 表名 MODIFY 列名 列新的类型;

添加列:

ALTER TABLE 表名 ADD(列名1 列类型1,列名2 列类型);

删除列:

ALTER TABLE 表名 DROP 列名;

二:DML语句整理

插入数据

方法一:

INSERT INTO 表名(列名1 列名2 列名3)VALUES(对应列名1的值1,对应列名2的值2,对应列名3的值3);

方法二:

INSERT INTO 表名VALUES(对应第一列的值1,对应第二列的值2,..........,对应最后一列的值n); //表中一共有n列

修改数据

//下面写法会将所有数据的列名1这一列的数据,都修改为值1

UPDATE 表名 SET 列名=要修改的值; //如果类型为字符型,则需要用单引号:'要修改的值;

UODATE 表名 SET 列名1='值1',列名2='值2';

//上述修改自然是很尴尬的,所以应该有选择的去修改数据

//为修改语句增加条件

UPDATE 表名 SET 列名3='值3' WHERE ID = 2;  //将ID为2的那一行的数据中的'列名3'这一列的数据修改为'值3'

删除数据

//下面写法会将表名1中的数据都删除

DELETE FROM 表名1;

//为上述语句增加条件

DELETE FROM 表名1 WHERE ID=2; //将ID为2的那一行数据删除

三,DQL基础语句整理

公式

SELECT 查询那些列 FROM 从哪些表中查询 WHERE 查询条件 GROUP BY 按照哪个列分组 HAVING 分组后的条件ORDER BY 根据哪一列排序

列运算相关(纵向)

//查询两列之间数据的加减乘除结果集

SELECT 列名1+列名2,列名3-列名4,列名5*列名6,列名7/列名8 FORM 表名;

去重复查询

//关键字DISTINCT可以将查询结果中完全相同的数据去除

SELECT DISTINCT 列名 FROM 表名;

模糊查询

模糊查询关键字为:**like**

//字符短线:  _ 代表一个任意值的字符

//字符百分号:  % 代表任意数量的任意值得字符

//字符星号:  * 代表所有

SELECT * FROM 表名 WHERE name LIKE '张%'  //查询姓张的名字

OR //或

name LIKE '_ _ _'; //查询名字由三个字符组成的姓名

排序

//根据ID升序排序

ORDER BY ID ASC;

//根据ID降序排序

ORDER BY ID DESC;

//根据列x升序排序,如果X相同则根据Y降序排序

ORDER BY X  ASD,Y DESC;

关于null与0

1:任何与null做的运算(加减乘除等等)结果都为null;

2:concat('hello' , 列n);列n的值如果为null,则该函数的结果也是null;

3:如果将字符与数字做运算,那么字符会被当成0看待,无法转换成数字的都会被当成0;

4:ifnull(列n , 值m);如果列n的值为null,该函数可以将null替换成m使用(并不改变表中的数据);

四,DQL聚合函数整理

聚合函数一共有五个:求数据总个数,求数据值的总和,求数据中的最大值\最小值,求数据的平均值

求数据总个数 / /count(列名)

//查询表中的数据总数(如果某一行的数据都为null,则不会计算)

SELECT COUNT( * ) FROM 表;

求数据总和 //  sum(列名)

//取x列数据的总和

SELECT SUM(X) FROM 表;

求数据中的最大值\最小值 / / max(列名)与min(列名)

//取x列的最大值和y列的最小值

SELECT MAX(X) , MIN(Y) FROM 表;

求数据的平均值 //avg(列名)

//取x列的平均值

SELECT AVG(X) FROM 表;

五:SQL分组查询整理

一般见到每组,每班,每部门,每科室等词汇,基本就是要使用分组查询了;

//以列名x列分组,查询该列信息及分组后的每组的数量

SELECT 列名X,COUNT(列名X) FROM 表名 GROUP BY 列名X;

// 在分组之前的条件用WHERE,分组之后的条件用HAVING,看上方公式顺序

六:备份数据库与恢复数据库

备份与恢复的都是数据库中的每个表,如果该数据库不存在需要先 建立数据库

//在登录前,注意该语句后面不能写分号

//看箭头的方向,箭头的方向就代表数据的传输方向

//这句话就是在将数据库中的表信息传输到文件中存起来

//备份

mysqldump  -u用户名  -p密码   数据库名>目标文件; (绝对路径,文件后缀名为.sql)

//根据箭头的方向可以看出,这句话是将表信息从文件中传输到数据库中

//此语句后面也不能写分号

//恢复数据时,如果对应数据库不存在,则需要先建立数据库

//恢复

mysqldump -u用户名  -p密码   数据库名<目标文件(绝对路径)

//登录之后的恢复操作可以使用以下语句

//先进入要恢复数据的数据库

use 数据库名;

//再恢复数据

source 备份文件路径

二;约束

在日常生活中,偶尔也会说一句,受他约束,也就是被他管着的意思.

这里的约束,是作用在列上的, 也就是去束缚着一列,管着着一列,让这一列的数据都满足我们给它加上的约束,

那么被约束的列也就有了一定的特性.

比如我约束你不让你吃饭,那你就什么都可以干就是不能吃饭.

如果约束列a不能为null,那么列a也就可以为任何值但是不能为null.

1:主键约束

被主键约束的列的特点:

1)非空: 列中的值不能为null

2)唯一: 这一列中,每一行的值不能重复

3)被引用:可以通过引用主键的方式,引用改行的数据(主键就代表了这一行)

//添加主键的方法:

//在创建表时添加主键的两种方式

CREATE TABLE 表名 (列名1 列类型1 PRIMARY KEY ,列名2  列类型2);

CRRATE TABLE 表名(列名1 列类型 1,列名2 列类型2, PRIMARY KEY (列名1));

//创建表后,若要为某一列设置主键约束

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

//删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

//注意!主键在一张表中最多存在一个,但是可以将多个列一起设为主键

所以说主键并不一定就表示一列

//设置多个列为主键

ALTER TABLE 表名 ADD PRIMARY KEY (列1,列2,列3);

//但是如果已经设置过主键了,在设置主键的话就会报错提示主键重复定义

//如果在建立表的时候主键没有设置自增长,在建表完后,想要将主键设置为自增长,可用下面语句

ALTER TABLE 表名 MODIFY  COLUMN 列名 INT(11) NOT NULL AUTO_INCREMENT;

2:主键自增长约束

关键字:auto_increment

注意!关键字auto_increment不能单独使用,必须与主键一同使用

若不指定主键的值,则自增长会生效

若删除了id为3这一行数据,再增添数据时,id会从4开始,不会使用3

若id已经使用到4了,这时候添加可一个数据,指定id为7,再次添加数据id为null时,id会从8开始使用

3,非空约束

//not null即为非空约束

CREATE TABLE 表名(id  int  primary key auto_increment,name char(50) not null);

4,唯一约束

//union 即为唯一约束

CREATE TABLE 表名( id  int  primary key,name char(50) union);

注意!即使设置某一列为非空,唯一,也不能在其他表中将该列作为外键引用

三,外键约束

1,多对一或一对多关系

当表1与表2有关联时,如果表1是从表, 表2为主表时,可以通过外键约束,在表1中引用表2的主键

什么时候为主表,什么时候为从表

多对一或者一对多关系下,多的为从表,单一的为主表

比如:有一个班级表,有一个学生表,多个学生对应一个班级,这时候班级表就是主表,学生表就是从表

比如:有一个部门表,有个员工表,多个员工对应一个部门, 这时候部门表就是主表,员工表就是从表

当表1引用表2的主键时,表1该列下的数据必须是表2主键列中已存在的数据

注意!表1引用表2添加约束时的列名与表2的主键列名不是一定要一致的

注意这里引用的都是主键而不是一行记录

//指定外键的两种方式

//如果表1想指定表2的主键为外键,需要先存表2

CREATE TABLE 表2(表2ID  INT  PRIMARY KEY AUTO_INCREMENT, 表2name  VARCHAR(50));

//方式一:在创建表时指定外键

//fk 表2id是约束的名字  ,fk表示:froeign key 也就是外键

create table 表名(

  id int primary key auto_increment,

  name char(50),

  ddd  int ,

  constraint  fk 表2id  doreign  key (ddd)  references  表2(表2id)

  );

方式二:在创建表后,通过修改ddl语句添加外键约束

alter  table 表名 add  constraint  fk  表2id  foreign  key (ddd)  references 表2(表2id);

2,一对一关系

给表1的主键,加上表2的外键约束

这样表1的主键(一般为id)与表2的主键(一般为id)就会被约束,结果就是表1中有的id在表2中一定有,表2中没有的id,是不能添加进表1中的

例如:一夫一妻制

//建立丈夫表: husband

CREATE TABLE husband(

   hid INT PRIMARY  KEY  AUTO_INCREMENT,

   hname VARCHAR(50)

);

//建立妻子表:wife ,并且给妻子表的主键指定丈夫表的外键约束

CREATE  TABLE  WIFE(

    wid  INT  PRIMARY  KEY  AUTO_INCREMENT;

    wname VARCHAR(50),

   constraint  fk  wife  husband  foreign  key(wid)  references  husband(hid)

);

//3:多对多关系

一个老师可以教很多学生,一个学生也可以有很多老师,这就是多对多关系

多对多关系需要使用到中间表

有几张表,就要在中间表中建立几个外键

四:多表查询

//合并结果集

结果集,就是通过select ..from..查询出来的数据,查出来的数据实际也是一张表,也就是说结果集也就是一张表

//两个select语句中通过关键字:union连接即可

select a,b from 表1 //语句1

union  //all    如果加上all,则不会去重复

select c,d from 表2//语句2

//如果语句1查询出的结果集(就是查出的那个数据表)的结构(列的数量,列的类型)与语句2查询出的结果集一样

那么上述语句会执行成功

//结果会将表1的结果集与表2的结果集纵向拼接

//连接查询

有一个概念教笛卡尔积,在多表查询时,都会去除笛卡尔积

笛卡尔积概念请自行百度

去除的方法时在多表查询时加上一个条件:从表.外键列=主表.主键列

内连接

//mysql的方言

select * from 表名1 别名1 ,表名2 别名2 where 别名1.xx=别名2.yy;

//如果表名1为从表,则xx为外键约束的列名,yy为主键列名

//sql标准写法:

select * from 表名1 别名1 inner join  表名2 别名2 on 别名1.xx = 别名2.yy;

//自然连接:(这种格式的名称要自然)

//自然连接会自动找到两表中列名相同的列做比较(也就说,外键约束的列名一定要与主键的列名一样)

//一般很少用,可读性也低,知道就好

select * from 表名1 别名1  natural join 表名2 别名2;

内连接不会取出不符合条件的数据,比如表名1中某一行外键约束下的列的内容为null的话,则该行不会满足条件,内连接就不会取出它;

如果想取出,请使用外链接

外链接

外链接分为左外链接与右外链接

左外链接  就是不管左面表的数据是否满足条件(就是on后面的条件),都查出来

右外链接  与左外链接同理,只不过是右面的表

左外链接

select * from 表名1 别名1 left  outer  join  表名2 别名2 on 别名1.xx = 别名2.yy;

右外连接

select * from 表名1 别名1 righ outer  join  表名2 别名2 on 别名1.xx = 别名2.yy;

//如果左右都想要,有一个叫全外的东西,不过mysql不支持

//但是可以将上面两个语句,通过union连接,合并结果集的方式实现

子查询

在一条sql语句中有多个select的就是子查询

子查询的本质就是,先查询出一个表(结果集),再在这张表上进行后续的查询操作

标准中有两种写法:

写在from后面

写在where前面

如果查询出的是单行单列的结果集,那么可以直接将结果集作为判断条件

id =1 ,会确定一行数据, age确定了一列,所以查出的是单行单列的数据,可以用这种方式作为where后面的判断条件

select * from 表名1 where 列名1>(select age from 表名1 where id =1);

//如果查询出的是多行多列的结果集,那么可以直接将该结果集作为目标表来查询

select * from (select name ,age from 表名2);

//如果查询出的是多行单列的结果集,那么可以将该结果集看成集合

//关键字: ALL 表示大于该集合中所有的数据

//关键字 : in表示大于任意一个

select * from 表名1 where age >= all (select age from 表名1);

//如果查询出的是单行多列的结果集,那么可以将该结果集看成一个对象

//子查询可以查询名字为张三这一行的列1,列2的数据

//然后查询表名1中所有列1列2数据与张三的结果集相同的数据

select * from 表名1 where  (列1,列2) in (select 列1,列2 from 表名1 where name = '张三');