sql常用语句

1.连接数据库，输入命令：mysql –h IP –P 端口号 -u 用户名 -p密码

注： -h 后面跟mysql服务器的IP地址， -p 可以跟密码，如果跟密码不需要空格；也可以不跟密码；（第一个 P大写，第二个p小写）
查看数据库：show databases;
新建数据库：create database 数据库名
选择数据库 : use 数据库名
显示数据库结构：show create database 数据库名
删除数据库：drop database 数据库名
查看数据库中的表： show tables
查看指定表的详细信息：show create table 表名
查看指定表的结构：desc 表名
查看表中的列的基本信息：describe 表名
修改表名： rename table旧表名 to 新表名 / alter table 旧表名 rename 新表名
删除表：drop table 表名
查看存储引擎：show engines
从.sql文件引入SQL语句：SOURCE <.sql文件路径>
查看存储的位置：show variabes like ‘datadir’
查看当前MySQL服务实例使用的字符集：show variables like 'character%'

2.创建新表
CREATE TABLE <表名> (
<列名1> <列类型1>,
<列名2> <列类型2>,
<列名3> <列类型3>,
PRIMARY KEY (<列名1>),
FOREIGN KEY (<列名2>) REFERENCES <表名2>(<列名2>)
);
主键（PRIMARY KEY）用来标识一条记录（一行），所以每条记录的主键值必须是唯一的。主键可以定义在多列上，这称为联合主键（composite primary key）。
如果我们把表视作具有某种结构的数组（例如，C语言中的struct），那么外键（FOREIGN KEY）可以视作指针。
例子：
CREATE TABLE instructor (
ID CHAR(5),
name VARCHAR(20) NOT NULL,
dept_name VARCHAR(20),
salary NUMERIC(8,2),
primary key (ID),
primary key (dept_name) references department(dept_name));
在上面的例子中，我们创建了一个教员（instructor）表，该表的主键是ID，外键是教员所在的部门名称（dept_name），关联部门（department）表。此外，教员表还包括姓名（name）、薪水（salary）。

其中，姓名有约束NOT NULL，表示姓名这一项不能为空。

2. 修改字段相关信息
删除字段：alter table 表名 drop 字段名
例：Alter table stu drop name
添加新字段：alter table 表名 add 新字段名新数据类型新约束条件 first after 旧字段名
例：Alter table stu add name varchar(20)

Alter table stu add name varchar(20) after id NOT NULL DEFAULT '' COMMENT '姓名'

Alter table stu add name varchar(20) first NOT NULL DEFAULT '' COMMENT '姓名'

Alter table stu add name varchar(20) before age NOT NULL DEFAULT '' COMMENT '姓名'

修改字段名：alter table 表名 change 旧字段名新字段名新数据类型
例：Alter table stu change name newname varchar(20)
修改数据类型：alter table 表名 modify 字段名新数据类型
例：Alter table stu modify newname varchar(30)

3.复制表
对已有的表进行复制，如下：
create table 表名 as select 字段名 from 表名;
完全复制表结构、数据：create table newClass as select * from class
复制部分字段：create table newClass2 as select id,name from class
复制表结构：create table newClass3 as select * from class where 1=0

create table newClass3 as select * from class where 1=1
insert into stu_info select * from stu_info;

4. 在表中插入新纪录
insert into <表名> (<列名1>, <列名2>, <列名3>, …) VALUES (<值1>, <值2>, <值3>, …);
也可以省略列名（依序在所有列上插入新值）：insert into <表名>VALUES (<值1>, <值2>, <值3>, …);
一次插入多条：Insert into stu values(3,’new1’), (4,’new2’), (5,’new3’)

5.使用replace插入新记录
一般情况下在向表中插入数据时，我们经常会遇到这样的情况：

首先判断数据是否存在；
如果不存在，则插入；
如果存在，则更新

replace into 跟 insert 功能类似，不同点在于： replace into 首先尝试插入数据到表中，

如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
否则，直接插入新数据。要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据

replace into 目标表名(字段列表1) select (字段列表2) from 源表 where 条件
replace into 表名（字段列表） values （值列表）
replace into 表名 set 字段1=值1, 字段2=值2

6. 在表中更新记录
修改全部字段值：update 表名 set 字段名1=值1,字段名2=值2,….. ,字段名n=值n
按条件修改字段值：update 表名 set 字段名1=值1,字段名2=值2,….. ,字段名n=值n where <条件>
给张三年龄加1岁：update stu set age=age+1 where name='张三'

7. 清空表
delete from <表名>
delete from <表名> where <条件>
truncate table 表名
区别：
truncate 不能加where条件
truncate 会重置auto_increment
delete可以进行回滚操作，truncate 不能回滚

查询
8. SELECT
表中选择数据：SELECT <列名1>, <列名2>, … FROM <表名>;
选择所有内容：SELECT * FROM <表名>;
表名、字段名取别名,as可以省略：select id as 学号,name as 姓名,age as 年龄 from stu as s
合并id和name字段：select concat('编号:',a.id,' 姓名:',a.name) from stu a

9. distinct
distinct过滤掉了重复的值：select distinct <列名1>, <列名2>, … FROM <表名>;
过滤两列的记录，先比较第一个列在比较第二个列
SELECT count(distinct a.name) form emp a;

10. WHERE
WHERE的条件通常是：
比较文本（text）
比较数字（numbers）
AND、OR、NOT等逻辑运算
让我们来看一些例子：
SELECT * FROM course WHERE dept_name='Comp. Sci.';
SELECT * FROM course WHERE credits>=3;
SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits!=3;
select * from emp where sal<>3000; //<>表示不等于
select * from emp where sal!=‘null
select * from emp where sal<=>null; //等于空，不能用=
select * from emp where comm is null;
select * from emp where comm is not null;

11. GROUP BY
GROUP BY语句可以分组结果，常用于COUNT、MAX、MIN、SUM、AVG等聚合函数（aggregate functions）。
SELECT <列名1>, <列名2>, … FROM <表名> GROUP BY <列名>;
列出每个部门的课程数量：
SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name;

12. HAVING
SELECT <列名1>, <列名2>, … FROM <表名> GROUP BY <列名x> HAVING <条件>;
那么，HAVING和WHERE有什么不同呢？让我们先来看一个例子，列出开了不止一门课程的部门开设的课程数：
SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name
HAVING COUNT(course_id)>1;
这里HAVING不能换成WHERE，因为WHERE直接针对行操作，且在GROUP BY之前运行
（即先通过WHERE筛选行，之后再将筛选出的行通过GROUP BY分组）。假设SQL中不存在HAVING语句，那么我们只能先新建一张表，将COUNT(course_id)作为新表的列，然后在新表上再通过WHERE进行筛选（当然，实际上SQL提供了派生表、CTE等机制，并不用真的手工建新表）。

13. ORDER BY
ORDER BY可以对结果进行排序，在没有明确指定ASC（升序）或DESC（降序）的情况下，默认按升序排列。
SELECT <列名1>, <列名2>, … FROM <表名> ORDER BY <列名1>, <列名2>, …, ASC/DESC;
例子：
SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC;
select * from emp order by sal asc,hiredate desc;

14. BETWEEN
BETWEEN语句用于指定区间。
SELECT <列名1>, <列名2>, … FROM <表名> WHERE <列名x> BETWEEN <值1> AND <值2>;
其中“值”可能是数字，文本，乃至日期等。

15. IN
IN语句表示值属于某个集合。
SELECT <列名1>, <列名2>, … FROM <表名> WHERE <列名n> IN (<值1>, <值2>, …);
SELECT <列名1>, <列名2>, … FROM <表名> WHERE <列名n> NO IN (<值1>, <值2>, …);

16.limit
结果集索引从0开始，0表示字段名，1.2.3...为值；limit 2,5—表示索引为2从第三条数据开始取5条数据
返回前10条数据：select * from emp a limit 10
取工资最高的2条数据：select * from emp a order by a.sal desc limit 2

17. LIKE
LIKE用于匹配文本中的特定模式。
SELECT <列名1>, <列名2>, … FROM <表名> WHERE <列名x> LIKE <模式>;
模式中可以使用以下两个通配符：
% （零个、一个或多个字符或汉字）
_ （单个字符或汉字）
select * from stu a where name like '宋'
select * from stu a where name like '%tom%';
select * from stu a where id like '12__';

18. JOIN
JOIN用来组合两张以上表中的值。下图展示了JOIN的三种类型：
SELECT <列名1>, <列名2>, … FROM <表名1> JOIN <表名2> ON <表名1.列名x> = <表名2.列名x>
inner join ... on
left outer join ... on
right outer join ... on

19.索引

#查看表索引
show index from table_name
#添加索引,普通索引、唯一索引、全文索引
create table test(
id int PRIMARY KEY COMMENT'测试id',
tag varchar(20) COMMENT'标签',
information text COMMENT'全文',
key tag(tag),
FULLTEXT KEY (information),
unique key(id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='测试表';

create index index_name on table_name(recurring_flag)
create unique index index_name on table_name(record_id)
create fulltext index index_name on table_name(company_name)

alter table table_name add index index_name(recurring_flag)
alter table table_name add unique index_name(record_id)
alter table table_name add fulltext index_name(company_name)
#只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引
#MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引

#删除索引
alter table table_name DROP key index_name
drop index index_name on table_name

#删除主键
alter table bill_order DROP primary key

20. 视图

视图（view）是虚拟的SQL表。它包含行和列，和一般的SQL表格很类似。视图总是显示数据库中的最新数据。
创建视图：
CREATE VIEW <视图名> AS SELECT <列名1>, <列名2>, … FROM <表名> WHERE <条件>;
删除视图：
DROP VIEW <视图名>;

21. 聚合函数
COUNT(列名) 返回行数
SUM(列名) 返回指定列的值之和
AVG(列名) 返回指定列的平均值
MIN(列名) 返回指定列的最小值
MAX(列名) 返回指定列的最大值

22. 嵌套子查询
在SQL请求中，可以嵌套SELECT-FROM-WHERE表达式，称为嵌套子查询（nested subqueries）。
查找2009年秋、2010年春都开的课程：
SELECT DISTINCT course_id FROM section WHERE semester = ‘Fall’ AND year= 2009 AND
course_id IN ( SELECT course_id FROM section WHERE semester = ‘Spring’ AND year= 2010 );

相关推荐