mysql数据中sql语句基础大全

在学习第一门数据库语言的时候，相比而言要比编程语言的逻辑性要差一点，更多的则是在背会记住的基础上熟练运用。首先来介绍mysql语言中需要背记的增删改查这些语句。

/*---------------------------新增-------------------------*/

查看现在有哪些数据库

show databases；

新建一个名为db_20的数据库：

create database db_20;

使用这个数据库

use db_20;

查看数据库中的表  show tables;

新建一个名为mytable的表,其中有两列，一列为id，数据类型为int型，默认长度为11。一列为name，数据类型为可变字符，长度为20；

create table  mytable(id int,name varchar(20));

查看mystable表的结构

desc mytable;

默认值都为null，没有主键也没有默认值；

现在开始插入一条数据，要注意的是注意数据类型要匹配；

insert into mytable(id,name)  values (1,‘tom‘);

如果需要插入多条数据，那么可以使用

INSERT INTO mytable(id,NAME) VALUES(2,‘jack‘),(3,‘rose‘);

如果要查看mytable表中现在有哪些数据，可以使用

select * from mytable；

显示结果为

删除这个表

drop table mytable;

现在重新创建一个表，其中要设置id为主键，并且自动增长。

create table mytable(

 id int auto_increment,

 `name` varchar(20),

primary key(id)

);//因为name这个词在sql中属于关键字，所以要加一对`来引上。

/*---------------------------修改表------------------------*/

如果要给mytable表新增一列，列名或者字段称之为creattime

ALTER TABLE  mytable ADD COLUMN creattime DATE;

现在删除这一列，可以使用

ALTER TABLE mytable DROP COLUMN creattime ;

给那么列添加唯一约束，意思就是除了主键是唯一之外，还有一列中的数据要求是唯一的

ALTER TABLE mytable ADD CONSTRAINT uk_mytable_name  UNIQUE(`name`);

如果在新建表的时候没有给id类设置称主键，可以用“

ALTER TABLE mytable ADD CONSTRAINT pk_mytable_id  PRIMARY KEY(id);

如果要给name类设置默认值，则可以试用

alter table mytable alter column name set default ‘unname‘;

现在新增一列address，默认值为China ,并且为not null可以使用

alter table mytable add column address varchar(20) default ‘China‘ not null ;

将name列也声明为not null。

ALTER TABLE mytable MODIFY COLUMN `name` VARCHAR(20) NOT NULL;

如果要把这个表名给改了，可以使用

rename table mytable to mtable;

RENAME TABLE mtable TO mytable;

下边开始将update语句，即更改表中某些数据的值

首先运行

select * from mytable;

接着加入数据

INSERT INTO mytable(`name`,address) VALUES(

‘tom‘,‘usa‘),(‘jack‘,‘uk‘),(‘rose‘,DEFAULT);

其中因为id为主键默认自动增长，所以就不用特别赋值，address有默认值，如果用default代替，则说明赋值为默认值；

如果要将tom的国籍改为pk，那么

UPDATE  mytable SET address=‘pk‘ WHERE NAME =‘tom‘;

如果要删除id 为1的人的信息，那么可以使用

delete from mytable where id=1;

如果要快速的删除表中数据
truncate table mytable；

设置表的时候要遵守的三大范式

三大范式

1. 确保每列的原子性

2. 在第一范式的基础上，确保每列都和主键相关

3. 在第二范式的基础上，确保每列都和主键直接相关，而不是间接相关

如果在mytable中插入一列classid列

alter table mytable add column classid int;

创建另外一个class表

CREATE TABLE class(id INT,NAME VARCHAR(20),PRIMARY KEY(id));

INSERT INTO class(id,NAME) VALUES (1,‘java‘),(2,‘web‘),(3,‘php‘);

因为在mytable表中classid列与class列有关，所以可以创建外键

alter table mytable add constraint fk_mytable_classid foreign key(classid) references class(id);

通常在开发不会直接设置外键，因为外键有很多约束，类型得和关联主键相同，另外取值范围必须在关联主键的取值范围内。如果设置了主键，那么在mytable中如果insert into mytable(name,address,classid) values(‘alex‘,‘pk‘,4);那么就插入不成功，因为4在class的id列中没有这个值。

如果要取消外键，可以使用来取消外键

alter table mytable drop foreign key fk_mytable_classid;

/*-----------------------查询---------------------------*/

在mysql中比较复杂的就是查询了

主要使用的语句是select

最简单的就是查询表中所有数据

select * from mytable;

查询表中的指定列

select id,name,address from mytable;

再插入一条数据，国籍也有usa

INSERT INTO mytable (NAME,address,classid) VALUES(‘lucy‘,‘usa‘,‘1‘);

那么当查询address列的时候就会出现两个usa

SELECT address FROM mytable;

需要去重复的话 需要执行

select distinct address from mytable;

如果需要分页的话执行

select * from mytale limit 0,5;

升序asc降序desc

select * from mytable order by name asc,id desc;

过滤查询查询国籍是美国的名字列

select name from mytable where address=‘usa‘;

查询国籍是美国并且名字叫lucy的

select name from mytable where address=‘usa‘ and name=‘lucy‘;

模糊查询

“_”通配符代表一个字符

“%”通配符代表0个或一个或任意多个字符

查询国籍中带有u字的，并且名字有o字的,此时用的关键字就是like了，而不能用=号了。

SELECT * FROM mytable WHERE address LIKE ‘%u%‘ AND `name` LIKE ‘%o%‘;

如果表明需要修改的时候执行

rename table mytable to mtable;

rename table mtable to mytable;

但是如果要将显示的列名改一下的话就可以使用as关键字来将显示出来的列名改一下，但是只是在显示当中，数据库中的列名还是原来的名字

select name as ‘名字‘,address as ‘国籍‘,classid as ‘班别‘ from mytable ;

/*----------------------文本处理函数-----------------------*/

left()返回左边指定长度的字符

SELECT  LEFT(address,2) AS ‘简称‘FROM mytable;

right()返回右边指定长度的字符

select right(name,3) as ‘名‘  from  mytable;

length()返回字符串的长度

select length(name) as ‘长度‘ from mytable;

lower()将字符串转换为小写

select lower(name)  from mytable;

upper()将字符串转换为大写

select upper(name) from mytable;

ltrim()去掉字符串左边的空格

select ltrim(name) from mytable;

rtrim()去掉串右边的空格

select rtrim(name) from mytable;

trim()去掉左右两边的空格

select trim(name) from mytable;

字符串连接

select contact(contact(contact(‘自我介绍‘，name),address）,classid) from mytable;

时间日期函数

curDate()

返回当前日期

curTime()

返回当前时间

now()

返回当前日期和时间

date()

返回日期时间的的日期部分

time()

返回日期时间的时间部分

day()

返回日期的天数部分

dayofweek()

返回一个日期对应星期数

hour()

返回时间的小时部分

minute()

返回时间的分钟部分

month()

返回日期的月份部分

second()

返回时间的秒部分

year()

返回日期的年份部分

datediff()

计算两个日期之差

addDate()

添加一个日期（天数）

聚合函数

min()

max()

count()

sum()

avg()

聚合函数常用于统计数据使用

聚合函数统计时忽略值为NULL的记录

SELECT NAME,address,MAX(classid) FROM mytable;

select count(*) from mytable;结果为4

插入一个time列

ALTER TABLE mytable ADD COLUMN `time` DATE;

ALTER TABLE mytable ALTER `time` SET DEFAULT ‘2005-09-02‘;

如果查询select(time) from mytable;显示的结果就为0；

分组查询，将在address中出现的数据进行分组统计，如果加条件的使用having关键词，下句结果是address值为uk总共出现了几次；

SELECT address,COUNT(*) FROM mytable GROUP BY address HAVING address =‘uk‘;

HAVING语句用于GROUP BY的过滤

WHERE用于分组前过滤

SELECT NAME,address,COUNT(*) FROM mytable WHERE `name` IS NOT NULL GROUP BY address  HAVING address =‘usa‘;

查询语句的顺序如下

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. HAVING

6. ORDER BY

7. LIMIT

/*-------------子查询指的是嵌套在查询中的查询-------------*/
我们现在新建第三张表

create table  school(id int ,name varchar(20),primary key(id));

另外在class表中增加一个schoolid列

alter table class add column schoolid varchar(20);

并且插入以下数据

如果现在要查询在stu大学上学的学生名字，那么就会用到子查询

SELECT NAME FROM mytable WHERE classid IN(

SELECT id FROM class WHERE schoolid IN(

SELECT id FROM school WHERE `name` =‘stu‘

)

);

顺序就是先找出名字为stu大学的学校id，然后根据学校id找class的id，然后通过class的id找classid，最后找到在这个classid中的学生名字。

现在新建一个student表，（id int primary key,name varchar(20)）学生的表，部分包含了刚才mytable中name中的值

如果现在要查询所有学生上的课数，如果没有选课的同学显示为0

根据classid列统计student表中学生选课的信息，结果如下

SELECT NAME AS ‘名字‘,(SELECT COUNT(classid) FROM mytable WHERE mytable.name = student.name)  AS ‘选课数量‘ FROM student;

等值查询和内联接查询

查询学员及对应班级信息，班级没有学员或学员班级信息错误的不显示。

SELECT my.id,my.name AS ‘名字‘,my.classid AS ‘班别‘,class.name AS ‘班名‘ 

FROM mytable AS my,class WHERE my.classid = class.id; 

和等值查询作用相同的是内联结查询

select my.id,my.name,class.name 

from mytable as my

inner join class

on my.classid = class.id;

左(外)联接查询和右(外)联接查询

select my.id,my.name,class.name 

from mytable as my

left join class

on my.classid = class.id;

left 左边的是主表 

select my.id,my.name,class.name 

from mytable as my

right join class

on my.classid = class.id; 

right 右边是主表

所谓外联结查询就是用一个表的内容尽量的补充主表的内容，如果主表中内容没有办法补充，就用null值来填充，总之尽量的将主表的内容填充满。

现在新建两张表

t1(name varchar(20),creattime TIMESTMP);

t2(name varchar(20),creattime timestmp);

联合查询

执行语句，就可以将这两张表的内容放在一起进行查询

SELECT NAME,CREATTIME FROM T1  

UNION

SELECT NAME,CREATTIME FROM T2 ;

union会自动去重，如果需要全部显示，那么就用union all来保持原来的数据

另外可以按照creattime降序排序，但是是联合之后才能够排序

SELECT NAME,creattime FROM T1  

UNION 

SELECT NAME,creattime FROM T2 ORDER BY creattime DESC;

MySQL其他知识

数据库引擎：

InnoDB：可靠的事务处理引擎，不支持全文搜索

MyISAM：是一个性能极高的引擎，支持全文搜索，但不支持事务处理

MEMORY：功能等同于MyISAM引擎，但由于数据存储在内存中，所以速度快

> create table xxx (

…

)engine=innodb;

/*============鸣谢：焦作凯盛it软件=======================*/

/*============初学者交流QQ：623687677====================*/

本文出自 “聂鲁达” 博客，转载请与作者联系！