MySQL使用入门

DDL、DML语句

DDL（data definition languages）数据定义语言，就是对数据库内部对象进行创建、删除、修改等；DML（data manipulation language）是数据操纵语言，是对表内数据进行操作

创建数据库——CREATE

• 显示当前创建的数据库

mysql> SHOW DATABASES    -> ;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)

• 创建数据库——CREATE

mysql> CREATE DATABASE test_db;Query OK, 1 row affected (0.00 sec)

删除数据库——DROP

mysql> DROP DATABASE test_db;Query OK, 0 rows affected (0.00 sec)

注：语句以分号结尾，命令不区分大小写

创建表

• 创建表的基本语法

CREATE TABLE tablename(col_name1 col_type1 constrations, col_name2 col_type2 constrations,col_name3 col_type3 constrations,

注：字段名 字段类型 约束条件

• 例子：

mysql> CREATE TABLE test_tb(    -> name varchar(10),     -> age int(6));Query OK, 0 rows affected (0.24 sec)

注：创建表之前要先选择数据库use db_name

• 用DESC查看表定义
  DESC table——name

mysql> DESC test_tb;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name  | varchar(10) | YES  |     | NULL    |       || age   | int(6)      | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)

• 更全面的显示表定义

mysql> show create table test_tb \G;*************************** 1. row ***************************       Table: test_tbCreate Table: CREATE TABLE `test_tb` (  `name` varchar(10) DEFAULT NULL,  `age` int(6) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

\G的含义是让记录能够按照字段竖向排列

删除表

• 删除表命令： DROP TABLE tablename;

mysql> DROP TABLE test_tb;Query OK, 0 rows affected (0.14 sec)

修改表

• 修改表类型
  语法如下：
  ALTER TABLE table_name MODIFY[COLUMN] column_definition[FIRST|AFTER col_name]

mysql> ALTER TABLE test_tb MODIFY age int(6);Query OK, 0 rows affected (0.08 sec)

• 增加表字段
  语法如下：
  ALTER TABLE table_name ADD[COLUMN] column_definition[FIRST|AFTER col_name]

mysql> ALTER TABLE test_tb ADD address varchar(50);Query OK, 0 rows affected (0.39 sec)

• 删除表字段
  语法如下：
  ALTER TABLE tablename DROP[COLUMN] col_name

mysql> ALTER TABLE test_tb DROP job;Query OK, 0 rows affected (0.41 sec)

• 字段改名
  语法如下：
  ALTER TABLE tablename CHANGE[COLUMN] old_col_name column_definition

mysql> alter table test_tb change tel telephone varchar(20);Query OK, 0 rows affected (0.08 sec)

• 修改字段排列顺序
  在字段的增加、修改语法中都有一个可选项first|alter colnum_name，这个选项是用来修改字段在表中的位置的

mysql> alter table test_tb add sex varchar(2) after name;Query OK, 0 rows affected (0.41 sec)

• 更改表名
  语法如下：
  ALTER TABLE tablename RENAME[TO] new_tablename

mysql> alter table test_tb rename table_test;Query OK, 0 rows affected (0.14 sec)

DML语句

DML语句主要是对数据库中的表操作，主要操作包括插入、更新、删除、查询

插入记录

语法如下：
INSERT INTO tablename(field1, field2, field3…fieldn)VALUES(value1, value2, value3, valuen);

mysql> insert into test_tb    -> (name, birth, age)    -> values    -> ('whc', '1995-03-02', 22);Query OK, 1 row affected (0.04 sec)

注：含可空字段、非空但是有默认值、自增字段， 这些可以不在insert之后的字段列表中出现
在MySQL中可以一次插入多条记录，语法如下：
INSERT INTO tablename(field1, field2, field3)
VALUES
(record1_value1, record1_value2, record1_value3),
(record2_value1, record2_value2, record2_value3);

mysql> insert into test_tb    -> (name, birth, age)    -> values    -> ('csy', '1996-06-25', 21),     -> ('xjt', '1995-10-02', 21);

注：每条记录之间用逗号隔开

更新记录

语法如下：
UPDATE tablename SET field1=value1, field2=value2, …..[WHERE CONDITION]

mysql> update test_tb set birth='1995-11-2' where name='xjt';Query OK, 1 row affected (0.06 sec)

现在有两张表，一张是物品单价和数目的关系表，一张是客户买东西的记录表

mysql> create table price(    -> num int(4),     -> price int(2));Query OK, 0 rows affected (0.25 sec)mysql> create table someonebuy(    -> name varchar(10),     -> num int(4),     -> price int(6) default 0);Query OK, 0 rows affected (0.26 sec)mysql> desc price;+-------+--------+------+-----+---------+-------+| Field | Type   | Null | Key | Default | Extra |+-------+--------+------+-----+---------+-------+| num   | int(4) | YES  |     | NULL    |       || price | int(2) | YES  |     | NULL    |       |+-------+--------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> desc someonebuy;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name  | varchar(10) | YES  |     | NULL    |       || num   | int(4)      | YES  |     | NULL    |       || price | int(6)      | YES  |     | 0       |       |+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)

现在插入3个用户，消费总额使用默认值

mysql> insert into price    -> values    -> (1, 10),     -> (2, 9),     -> (3, 7);Query OK, 3 rows affected (0.05 sec)mysql> insert into someonebuy    -> (name, num)    -> values    -> ('whc', 1),    -> ('csy', 2),    -> ('xjt', 3);Query OK, 3 rows affected (0.04 sec)

下面用更新语法来计算消费金额

mysql> update someonebuy a, price b set a.price=a.num*b.price where a.num=b.num; Query OK, 3 rows affected (0.04 sec)mysql> select * from someonebuy;+------+------+-------+| name | num  | price |+------+------+-------+| whc  |    1 |    10 || csy  |    2 |    18 || xjt  |    3 |    21 |+------+------+-------+3 rows in set (0.00 sec)

删除记录

语法如下：
DELETE FROM tablename[WHERE CONDITION];

mysql> delete from someonebuy where name='whc';Query OK, 1 row affected (0.03 sec)

一次删除多个表的记录

mysql> delete a, b from price a, someonebuy b where a.num=3 and b.name='csy';Query OK, 2 rows affected (0.05 sec)

查询记录

语法如下：
SELECT * FROM tablename[WHERE CONDITION];
也可以用逗号分割的字段代替

mysql> select name, price from someonebuy;+------+-------+| name | price |+------+-------+| xjt  |    21 |+------+-------+

其他查询要求：

• 查询不重复的记录
  在select后面加上distinct关键字可以去掉重复后显示出来
• 条件查询
  在末尾添加where语句，where后面的条件可以用”=”、”>”、”<”、”>=”、”<=”、”!=”；多条件联合查询可以用”and”、”or”等逻辑运算符
• 排序和限制
  使用关键字ORDER BY，语法如下：
  SELECT *FROM tablename[WHERE CONDITION] [ORDER BY field1[DESC\ASC], field2…………..]
  其中field表示排序字段，DESC表示降序， ASC表示升序，如果不写此关键字，默认升序排列
  注：如果第一个字段排序的值相同，就按照下一个字段排序，以此类推。如果只有一个字段，则这些字段相同的记录将会无须排列
• 聚合
  很多情况下用户需要进行一些汇总工作，比如统计公司的人数或者每个部门的人数，这时候就要用的SQL的聚合操作
  语法如下：
  SELECT [field1, field2, …fieldn] fun_name
  FROM tablename
  [WHERE CONTITION]
  [GROUP BY field1, field2, fieldn]
  [WITH ROLLUP]
  [HAVING CONTITION]
  说明：

参数 说明 fun_name 表示要做的聚合操作，也就是聚合函数，如sum、count(*)、max、min GROUP BY 表示要进行聚合的字段 WITH ROLLUP 表面是否要对聚合后的结果再进行汇总 HAVING 对分类后的结果再进行条件过滤

创建一个新的表

mysql> create table emp(    -> id int primary key auto_increment,     -> hiredate date,     -> sal int,     -> deptno int    -> );Query OK, 0 rows affected (0.22 sec)mysql> desc emp;+----------+---------+------+-----+---------+----------------+| Field    | Type    | Null | Key | Default | Extra          |+----------+---------+------+-----+---------+----------------+| id       | int(11) | NO   | PRI | NULL    | auto_increment || hiredate | date    | YES  |     | NULL    |                || sal      | int(11) | YES  |     | NULL    |                || deptno   | int(11) | YES  |     | NULL    |                |+----------+---------+------+-----+---------+----------------+4 rows in set (0.00 sec)

插入几个记录

mysql> insert into emp    -> (hiredate, sal, deptno)    -> values    -> ('2001-1-11', 2000, 1),     -> ('2003-3-23', 1600, 2),     -> ('2015-1-30', 5000, 3),    -> ('2017-8-22', 10000, 2),    -> ('2017-2-22', 14000, 2),    -> ('2016-7-22', 5000, 3);Query OK, 6 rows affected (0.04 sec)mysql> insert into emp    -> (sal, deptno)    -> values    -> (20000, 3);Query OK, 1 row affected (0.03 sec)mysql> select * from emp;+----+------------+-------+--------+| id | hiredate   | sal   | deptno |+----+------------+-------+--------+|  1 | 2001-01-11 |  2000 |      1 ||  2 | 2003-03-23 |  1600 |      2 ||  3 | 2015-01-30 |  5000 |      3 ||  4 | 2017-08-22 | 10000 |      2 ||  5 | 2017-02-22 | 14000 |      2 ||  6 | 2016-07-22 |  5000 |      3 ||  7 | NULL       | 20000 |      3 |+----+------------+-------+--------+7 rows in set (0.00 sec)

统计整个公司的人数

mysql> select count(*) from emp;+----------+| count(*) |+----------+|        7 |+----------+1 row in set (0.00 sec)查看各个部门的人数

mysql> select deptno, count(*) from emp group by deptno;+--------+----------+| deptno | count(*) |+--------+----------+|      1 |        1 ||      2 |        3 ||      3 |        3 |+--------+----------+3 rows in set (0.00 sec)

既要对各个部门统计，还要汇总

mysql> select deptno, count(*) from emp group by deptno with rollup;+--------+----------+| deptno | count(*) |+--------+----------+|      1 |        1 ||      2 |        3 ||      3 |        3 ||   NULL |        7 |+--------+----------+4 rows in set (0.00 sec)

对统计结果再过滤

mysql> select deptno, count(*) from emp group by deptno having count(*)>=3;+--------+----------+| deptno | count(*) |+--------+----------+|      2 |        3 ||      3 |        3 |+--------+----------+

统计薪水的高低

mysql> select sum(sal), max(sal), min(sal) from emp;+----------+----------+----------+| sum(sal) | max(sal) | min(sal) |+----------+----------+----------+|    57600 |    20000 |     1600 |+----------+----------+----------+

• 表连接
  当需要同时显示多个表的字段时，要用到表连接。表连接分为内连接和外链接。内连接仅显示两张表中相互匹配的记录，外连接会选出其他不匹配的记录
  创建一个部门代号和部门名称的表

mysql> create table dept(    -> deptno int,     -> deptname varchar(10));Query OK, 0 rows affected (0.23 sec)mysql> insert into dept    -> values    -> (1, 'tech'),    -> (2, 'sale'),     -> (3, 'hr');Query OK, 3 rows affected (0.05 sec)

之前我们只能查询员工和部门代号的关系，现在我们需要查询员工和部门名称的关系

mysql> select id, deptname from emp, dept where emp.deptno=dept.deptno;+----+----------+| id | deptname |+----+----------+|  1 | tech     ||  2 | sale     ||  3 | hr       ||  4 | sale     ||  5 | sale     ||  6 | hr       ||  7 | hr       |+----+----------+//这里用deptno作为匹配的桥梁

外连接又分为左连接和右连接
左连接：包含所有左边表中的记录甚至是右边表中没有和它匹配的记录
右连接：包含所有右边表中的记录甚至是左边表中没有和它匹配的记录
注：外连接和内连接的主要区别就是它可以显示那些没有匹配到的记录，比如出现某个员工是部门４的

• 子查询、
  某些情况下，进行查询的时候需要的条件是另一个select语句的结果，这时候用到子查询，子查询主要关键字有in、not in、exists、not exists等

mysql> insert into emp    -> (sal, deptno)    -> values    -> (12000, 4);Query OK, 1 row affected (0.04 sec)mysql> select * from emp where deptno in(select deptno from dept);+----+------------+-------+--------+| id | hiredate   | sal   | deptno |+----+------------+-------+--------+|  1 | 2001-01-11 |  2000 |      1 ||  2 | 2003-03-23 |  1600 |      2 ||  3 | 2015-01-30 |  5000 |      3 ||  4 | 2017-08-22 | 10000 |      2 ||  5 | 2017-02-22 | 14000 |      2 ||  6 | 2016-07-22 |  5000 |      3 ||  7 | NULL       | 20000 |      3 |+----+------------+-------+--------+7 rows in set (0.00 sec)

• 记录联合
  查询两个表时，将两个表的结果合并显示出来需要使用union和union all关键字

mysql> select sal from emp    -> union all    -> select deptname from dept;+-------+| sal   |+-------+| 2000  || 1600  || 5000  || 10000 || 14000 || 5000  || 20000 || 12000 || tech  || sale  || hr    |+-------+

注：ＵＮＩＯＮ和ＵＮＩＯＮ　ＡＬＬ的主要区别是ＵＮＩＯＮ会将重复数据过滤掉

DCL语句

主要是数据库管理员用来管理系统中对象的权限使用的，一般开发人员很少使用

帮助的使用

• 当不知道帮助能够提供什么时，可以使用“? contents”

mysql> ? contentsYou asked for help about help category: "Contents"For more information, type 'help <item>', where <item> is one of the followingcategories:   Account Management   Administration   Compound Statements   Data Definition   Data Manipulation   Data Types   Functions   Functions and Modifiers for Use with GROUP BY   Geographic Features   Help Metadata   Language Structure   Plugins   Procedures   Storage Engines   Table Maintenance   Transactions   User-Defined Functions   Utility

• 使用“? 类别名称”对感兴趣内容进一步查看
• 如果想快速查阅某项语法可以用“? 语法”，如：? create table