MySQL基本语句使用

DDL—数据定义语言(Create，Alter，Drop)

DCL—数据控制语言(Grant，Revoke)

DML—数据操纵语言(Delete，Update，Insert)

DQL—数据查询语言(Select，Show)

一、DDL—数据定义语言

1. Create

  1.1 语法

CREATE USER '用户名'@'主机ip' IDENTIFIED BY '密码';

CREATE DATABASE 数据库名 
CHARACTER SET 字符集名
COLLATE 校对规则名;

CREATE TABLE 表名 (列名 列属性);

  1.2 使用方法

mysql> CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';

mysql> create DATABASE RUNOOB CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

mysql> CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

  1.3 列属性

约束(一般建表时添加):
**primary key** ：主键约束
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
**not null**      ：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
**unique key** ：唯一键
列值不能重复
**unsigned** ：无符号
针对数字列，非负数。

其他属性:
**key** :索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加
**default**           :默认值
列中，没有录入值时，会自动使用default的值填充
**auto_increment**:自增长
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
**comment ** : 注释

2. Alter

  2.1 语法

Alter table<表名>
[Add <新列名><数据类型>[完整性约束]]
[drop <完整性约束名>]
[modify <列名><数据类型>]
[change <原名><改后名称><数据类型>];

  2.2 使用方法

mysql> ALTER TABLE testalter_tbl ADD i INT FIRST;
mysql> ALTER TABLE testalter_tbl ADD i INT AFTER c;

mysql> ALTER TABLE testalter_tbl  DROP i;

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

3.Drop

  3.1 语法

drop [database][table] <数据库/表名>;

  3.2 使用方法

mysql> drop database RUNOOB;

mysql> DROP TABLE runoob_tbl

 4.Truncate

  4.1 语法

truncate "表名";

    4.2 使用方法

truncate test;

二、DCL—数据控制语言

1. Grant

  1.1 语法

GRANT [all,权限] ON <对象类型><对象名> TO <'用户名'@'主机ip'> [WITH GRANTOPTION]

  1.2 使用方法

mysql> grant select on *.* to dba@localhost;

mysql> grant all on *.* to dba@localhost;

2. Revoke

  2.1 语法

REVOKE [all,权限] ON <对象类型><对象名> FROM <用户名'@'主机ip>

  2.2 使用方法

mysql> revoke all privileges on *.* from root@”%”;

mysql> delete from user where user=”root” and host=”%”;

三、DML—数据操纵语言

1.Delete

  1.1 语法

DELETE FROM <表名> [WHERE Clause]

  1.2 使用方法

mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;

2. Update

  2.1 语法

UPDATE <表名> SET field1=new-value1, field2=new-value2 [WHERE Clause]

  2.2 使用方法

mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

3. Insert

  3.1 语法

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );

  3.2 使用方法

mysql> INSERT INTO runoob_tbl 
    -> (id,sname,age,sex,intime)
    -> VALUES
    -> (2,'ls',19,'f',NOW());

 四、 DQL—数据查询语言

1. Select

  1.1 语法

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

  1.2 使用方法

   a. where的使用 : 用于筛选从from子句中返回的值，完成选择操作

   查询中国所有的城市名和人口数

SELECT  NAME,population FROM city 
WHERE countrycode='CHN';

   世界上小于100人的城市名和人口数

SELECT NAME,population FROM city 
WHERE population<100;

   查询中国人口数量大于800w的城市名和人口

SELECT NAME,population FROM city 
WHERE countrycode='CHN' AND population>8000000;

   查询中国或美国的城市名和人口数 

SELECT NAME,population FROM city 
WHERE countrycode='CHN' OR countrycode='USA';

   查询人口数量在500w到600w之间的城市名和人口数 

SELECT NAME,population FROM city 
WHERE population BETWEEN 5000000 AND 6000000;

   查询一下contrycode中带有CH开头城市信息

SELECT * FROM city  
WHERE countrycode  LIKE 'CH%';

   查询中国或美国的城市信息

SELECT NAME,population FROM city 
WHERE countrycode IN ('CHN' ,'USA');

   b. group by : 用于在查询结果集中对记录进行分组，以汇总数居或者为整个分组显示单行的汇总信息

   统计每个国家,城市的个数

SELECT  countrycode ,COUNT(id) FROM city
GROUP BY countrycode;

    统计中国 每个省的总人口数

SELECT district, SUM(population) FROM city 
WHERE countrycode='CHN'
GROUP BY district ;

   统计中国 每个省城市的个数

SELECT district, COUNT(NAME) FROM city 
WHERE countrycode='CHN'
GROUP BY district ;

   统计中国每个省城市的名字列表GROUP_CONCAT()

SELECT district, GROUP_CONCAT(NAME) FROM city 
WHERE countrycode='CHN'
GROUP BY district ;

   在同一列表中统计中国每个省城市的名字列表GROUP_CONCAT()

SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME)) FROM city 
WHERE countrycode='CHN'
GROUP BY district ;

   c. order by : 可以对结果集中的数据进行排序

   统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC ;

   d. limit : 可以对结果按排名进行筛选

   统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列,只显示前三名

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC 
LIMIT 0,3

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC 
LIMIT 3 OFFSET 0 

   e. having : 通常与GROUP BY子句一起使用，在完成对分组结果统计后，使用HAVING 子句的功能对分组的结果进一步的筛选

   统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

SELECT district ,SUM(population) FROM city 
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC 
LIMIT 3;