By

数据库sql语句 (结构化查询语言)

DDL(Data Definition Language)数据定义语言用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter等

一 创建 关键字 create

1. 创建数据库create database……

​ 创建数据库 create database mydatabase；

2.创建数据库带编码create database…..character set……

​ 带编码 create database mydatabase character set utf8;

3. 创建表create table….()……

​ create table mytable(

​ name char(10),

​ age int，

​ birthday date

);

4. 根据已有的表结构创建新表create table…..like…….

​ create table copymytable like mytable;

5. 创建用户 create user…. identified by…..

​ create user ‘用户名’@’主机’ identified by ‘密码’;

​ create user ‘user1’@‘localhost’ identified by ‘123456’;

二 修改 关键字 alter

1. 修改数据库编码格式alter database……default charactor set…….

​ alter database mydatabase defaule charactor set utf-8;

2.修改表的字符集（编码格式）alter table…….charactor set…….

​ alter table mytable charactor set utf-8；

3.修改表字段

​ 1. 添加字段 alter table…..add……

​ alter table mytable add myline varchar(10);

​ 2.修改字段类型alter table…..modify……

​ alter table mytable modify myline int

​ 3.修改字段名字alter table…..change ……

​ alter table mytable change myline newmyline varchar();

4.删除一个字段alter table…..drop……
alter table mytable drop myline;

三 删除 关键字drop

1. 删除数据库 drop database……

​ drop database mydatabase;

2. 删除表drop table……

​ drop table mytable；

四 完整性约束条件primary key/unique/not null/default/poreign key/zerofill/unsigned

  • primary key: 主键约束
  • unique: 唯一约束 注：唯一但是可以为空(空和空不相等)
  • not null: 非空约束
  • default: 默认值
  • poreign key: 外键约束
  • zerofill :零填充
  • unsigned:无符号位 <font color=red>注：无符号作用于数值类型</font>

1.在创建的表时候同时创建约束

​ **create table **mytable(

​ id int primary key/unique/not null/default 6/foreign key references **subject(id)/zerofill/unsigned**

);

​ **create table **mytable(

​ id int ,

​ name varchar(10),

primary key(id,name)/unique(id,name)/foreign key(id) references subject(id);

);

  1. 增加约束

    alter table mytable add primary key(id,name)/unique(id,name)/not null/default 6/zerofill;

    添加外键约束

    ​ alter table **mytable **add foreign key(id) references subject(id);

    ​ 带外键名的添加方法

    ​ alter table ** mytable **add constraint key_name foreign key(id) references subject(id);

    在添加外键约束的时候可以设置他的级联更新和级联删除

    on update cascade 级联更新，主键表的主键修改同时会修改外键表的外键

    ​ alter table ** mytable **add constraint key_name foreign key(id) references subject(id) on update cascade;

    on select cascade 级联删除，主键表的数据删除，同时会删除外键表对应的数据

    ​ alter table ** mytable **add constraint key_name foreign key(id) references subject(id) on delete cascade no update cascade ;

  2. 删除约束

    unique 约束删除用关键字 index

    alter table **mytable **drop primary key/index name_2

    删除外键

    alter table **mytable **drop foreign key key_name;

    <font color=red>注：主键约束相当于(唯一约束+非空约束)</font>

    <font color=red>一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示：</font>

    <font color=red>Multiple primary key defined!!!</font>

    <font color=red>删除主键约束前，如果有自增长需要先删除自增长,如果不删除自增长就无法删除主键约束</font>

int类型主键自增auto_increment

​ **create table **mytable(

​ id int primary key auto_increment

);

级联 cascade

on delete cascade 删除主表中的数据时，从表中的数据随之删除

on update cascade 更新主表中的数据时，从表中的数据随之更新

**on delete set null **删除主表中的数据时，从表中的数据置为空

默认 删除主表中的数据前需先删除从表中的数据，否则主表数据不会被删除

DML(Data Manipulation Language)数据操作语言用来对数据库中表的数据进行增删改。关键字：insert, delete, update等

一 插入记录 关键字 insert into

1.插入一条记录insert into…..value……

​ insert into mytable(id,name,age)value(1,’张三’,20);

​ inster into mytable value(1,’张三’,20);

二 更新记录 关键字 update…..set

1. 更新字段update…..set…….

​ 更新所有记录的id字段update…..set…….

​ update mytable set id=2；

​ 按条件更新id字段update…..set…….where……

update mytable set id=2 where name=‘张三’,sex=’女’;

三 删除记录 关键字 delete from

1. 删除记录 delete from….

​ 删除表中所有记录delete from……..

​ delete from mytable;

​ 按条件删除表中记录 delete from……where…….

​ delete from mytable where id=1;

DQL(Data Query Language) 数据查询语言对数据库进行数据查询，关键字select

一 查询 关键字 select……from

1.查询记录的 所有/部分 字段 select * ……from / select id,name……from 别名 as

属性名后面+空格+别名 也可以用as关键字

​ select * from mytable;

​ select id as 学号,name from mytable;

2.按条件查询记录的 所有/部分 字段select* ……from…….where……/select id,name ……from…….where……

​ select * from mytable where id=1;

​ select id,name from mytable where id=1;

3. where中的条件

1.逻辑运算 and/or/!

​ select * from mytable where id is null and name=’张三’ ;

​ select * from mytable where id is null or id is not null;

​ select * from mytable where name!=’张三’ ;

2. 判空 is null/is not null

​ select * from mytable where id is null;

​ select * from mytable where id is not null;

3. 可枚举值的范围查询 in();

​ select * from mytable where id in(1,2,4);

4. 连续范围查询 between…..and……

​ 操作符 between … and 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

​ select * from mytable where id between 1 and 4; –1 4都包含

5. 模糊查询 like…..

​ 通配符

​ % 代表任意零个或多个字符

​ _ 代表任意一个字符

​ select * from mytable where name like ‘张%’ ;

4. 对结果集进行排序 order by…..asc/desc

​ 升序 asc（默认）asc可省略 但是为了可读性 建议不要省略

​ select * from mytable where name like ‘张%’ order by id asc ;

​ 降序 desc

​ select * from mytable order by id desc;

5. 聚合函数 max() min() avg() sum() count()……

聚合函数放在select/having后面 他是对查询出的所有记录的指定字段进行操作

​ MAX(字段) ，统计指定字段的最大值，不统计记录为null ​ MIN(字段) ，统计指定字段的最小值，不统计记录为null ​ AVG(字段) ，统计指定字段的平均值，不统计记录为null ​ SUM(字段) ，统计指定字段的求和，不统计记录为null ​ COUNT(字段)，统计指定字段的记录条数，要求当前字段的值不能为空的记录才能统计到

​ select max(id) from mytable ;

6. 分组查询 group by……having…….

​ group by可以将查询结果按照指定字段进行分组 having 可以对分组后的记录进行条件筛选 如果没有对查询结果进行聚合操作 分组默认显示查询的第一行记录

​ <font color=blue>在where中不能使用别名 应为在where语句运行时select语句后的别名还没有执行所以还不存在，当需要大量使用别名的时候可以用group by….having分组筛选代替where语句 </font>

<font color=blue>*select name** 姓名 ,english  英语,chinese+english+math sum from **student **group by **id **having sum>220 ;</font>

​ select sex,avg(age)from mytable group by sex;

​ select sex,avg(age)from mytable group by sex having avg(age)>40;

7. 分页查询 limit

​ 取出查询结果的一部分

​ limit 0,3 起始位置从0开始,如果不写默认就是0 取三个数据 第一行记录也是从0开始的 包括开头

​ select ** name 姓名 **from mytable where sex=’男’ group by age desc limit 0,3;

8.去重查询 distinct

​ 去掉查询结果中的重复记录

​ select distinct name from student;

9. top字句 top/top….percent

​ top 子句用于规定要返回的记录的数目。

​ <font color=red> 注释：并非所有的数据库系统都支持 TOP 子句。</font>

​ select top 2 * from mytable;

​ top percent 选取 50% 的记录

​ select top 50 percent * from mytable;

二 多表查询….[inner] join……on…../…….left [outer] join…..on……/…….right[outer] join…..on……

1. 笛卡儿积

​ select * from student , subject;

​ 用 where 语句除去笛卡儿积(隐式内链接)

​ select * from student , subject where student . id = subject . id;

2. 内链接….[inner] join……on…..

​ 表与表之间连接 并用on关键字指定连接条件 inner 关键字可以省略

​ select * from **student **[inner] join subject on student . id = subject . id;

3. 外连接…….left [outer] join…..on……/…….right[outer] join…..on……

​ 左/右外连接 在符合内连接的基础上让左/右表的数据全部出来，右/左边有数据就显示，无数据就是为null

​ 找出所有学生的信息 还没来得及选课的同学 课程显示为null；

​ select * from **student **left [outer] join subject on student . id = subject . id;

​ select * from **student **right [outer] join subject on student . id = subject . id;

4. 子查询

​ 一个查询的结果是另一个查询的一部分，说白了就是查询里面包含另一个查询

​ select * from **student **where id = (select max(id) from subject );

Data Control Language(DCL) 数据控制语言 给用户授权，撤销授权

1. 用户授权 grant/revoke

1. 授权

​ grant 权限1,权限2,权限3,… on 数据库名.表名 to ‘用户名’@’主机’;

​ – 常用权限介绍 ​ create– 创建表或数据库权限 ​ alter– 修改表结构权限 ​ drop – 删除表或数据库权限 ​ insert– 添加表数据权限 ​ update– 更新表数据权限 ​ delete– 删除表数据权限 ​ select– 查询表数据权限 ​ all – 所有权限

​ 授予所有数据库所有资源

​ grant all on * . * to ‘user1’@‘localhost’;

2. 撤销授权

​ revoke 权限1,权限2,… on 数据库名.资源名 from ‘用户名’@’主机’;

​ 撤销user1对db1数据库所有资源的所有权限 ​ revoke all on db1.* from ‘user1’@’localhost’;

3. 查看权限

​ show grants for ‘用户名’@’主机’; – 查看指定用户的权限，适合root用户操作 ​ show grants; – 查看当前用户的权限

其他语句

查看 show

1.查看数据库show databases

​ show databases；

2.查看指定数据库show database……

​ **show database **mydatabase;

3.查询表的创建代码show create table……

​ show create table mytable;

选择数据库 use…..

​ use mydatabase；

修改表的名字 rename….to

​ rename table mytable to newmytable；

查询指定表的定义信息 desc….

​ desc mytable;

摧毁表结构再重新创建表结构 truncate table…..

​ truncate table mytable;

常用sql语句表

语句 语法
AND / OR SELECT column_name(s) FROM table_name WHERE condition AND|OR condition
ALTER TABLE (add column) ALTER TABLE table_name ADD column_name datatype
ALTER TABLE (drop column) ALTER TABLE table_name DROP COLUMN column_name
AS (alias for column) SELECT column_name AS column_alias FROM table_name
AS (alias for table) SELECT column_name FROM table_name AS table_alias
BETWEEN SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE CREATE DATABASE database_name
CREATE INDEX CREATE INDEX index_name ON table_name (column_name)
CREATE TABLE CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ……. )
CREATE UNIQUE INDEX CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE FROM DELETE FROM table_name (Note: Deletes the entire table!!)orDELETE FROM table_name WHERE condition
DROP DATABASE DROP DATABASE database_name
DROP INDEX DROP INDEX table_name.index_name
DROP TABLE DROP TABLE table_name
GROUP BY SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1
HAVING SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value
IN SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO INSERT INTO table_name VALUES (value1, value2,….)orINSERT INTO table_name (column_name1, column_name2,…) VALUES (value1, value2,….)
LIKE SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC]
SELECT SELECT column_name(s) FROM table_name
SELECT * SELECT * FROM table_name
SELECT DISTINCT SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO (used to create backup copies of tables) SELECT * INTO new_table_name FROM original_table_nameorSELECT column_name(s) INTO new_table_name FROM original_table_name
TRUNCATE TABLE (deletes only the data inside the table) TRUNCATE TABLE table_name
UPDATE UPDATE table_name SET column_name=new_value [, column_name=new_value] WHERE column_name=some_value
WHERE SELECT column_name(s) FROM table_name WHERE condition

来自 http://www.w3school.com.cn