【MySQL】MySQL使用入门

MySQL使用入门

SQL分类

sql语句分为3类：

1.DDL（Data Definition Languages）语句：数据定义语言。这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字：create、drop、alter等。

【DDL语言是对数据库内部的对象进行创建，删除，修改的操作语言】

2.DML（Data Manipulation Language）语句：数据库操纵语句 ，用于添加，删除，更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括insert、delete、update和select等

【DML只是对表内的数据进行操作，不涉及到表的定义，结构的修改，更不会涉及到其他对象】

3.DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段的直接许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

DDL语句

1.启动MYSQL服务之后，输入以下命令连接到MYSQL服务器。

mysql -uroot -p#####    

2.创建数据库

CREATE DATABASE dbname

例：mysql>create database test1;

提示：命令的结束符，用 ； 或者 \g结束。

3.查看系统中存在哪些数据库

例：mysql>show databases;

information_schema：主要存储了系统中的溢写数据库对象信息。如，用户表信息，权限信息，字符集信息，分区信息等。

cluster：存储了系统的集群信息。

mysql：存储了系统的用户权限信息。

test：系统自动创建的测试数据库，任何用户都可以使用。

4.选择要进行操作的数据库

USE dbnane

例  mysql>use test1 			 [选择数据库test1]
    mysql>show tables;		   [查看test1数据库中创建的所有数据表]

5.删除数据库

drop database dbname;

mysql>drop darabase test1;

6.创建表

CREATE TABLE tablename(

column_name_1 column_type_1 constrains,

column_name_2 column_type_2 constraints,

……

column_name_n column_type_n constraints)

cloumn_name是列名，column_type是列的数据类型，constraints是这个列的约束条件

mysql>create table emp(ename varchar(10),hiredate date,saldecimal(10,2)deptno int(2));

7.表创建完毕后，如果需要查看一下表的定义，可以使用如下命令

DESC tablename

mydql>desc emp;

8.若desc 命令展示的表定义不够全面，查看更全面的表定义信息可用如下命令

mysql>show create table emp\G;

此命令可除了可表定义外，还可看见表定义的engine(存储引擎)和charset(字符集)等信息。"\G "选项的含义是使得记录能够按照字段竖着排列。

8.删除表

DROP TABLE tablename

mysql>drop table emp;

9.修改表

ALTER TABLE tablename MODIFY [COLUMN]column_definition [FIRST | AFTER col_name]

例如，修改表emp的ename字段定义，将varchar(10)改为varchar(20):

mysql>desc emp;
mysql>alter table emp modify ename varchar(20);
mysql>desc emp;

10.增加表字段

ALTER TABLE tablename ADD [COLUMN] column_definition[FIRST|AFTER col_name]

mysql>desc emp;
mysql>alter table emp add column age int(3);
mysql>desc emp;

11.删除表字段

ALTER TABLE tablename DROP[COLUMN]col_name

例如：将字段age删除

mysql>desc emp;
mysql>alter table emp drop column age;
mysql>desc emp;

12.字段改名

ALTER TABLE tablename CHANGE [COLUMN]old_col_name column_definition [FIRST|AFTER col_name]

mysql>desc emp;
mysql>alter table emp change age age1 int(4);
mysql>desc emp;

注意：change和modify都可以修改表的定义，不同的是change后面需要写两次列名。但是change的优点是可以修改列名称，modify则不能。

13.修改字段排列顺序

前面介绍的字段增加和修改语法（ADD/CHANGE/MODIFY）中，都有一个可选项first|after column_name,这个可选项可以用来修改字段再表中的位置，默认ADD增加的新字段是加载表的最后位置，而CHANGE/MODIFY默认都不会改变字段的位置。

例如：将新增的字段birth date 加载ename之后：

mysql>desc emp;
mysql>alter table emp add birth data after ename;
mysql>desc emp;

修改字段age，将它放在最前面：

mysql>alter table emp modify age int(3)first;
mysql>desc emp;

注意：CHANGE/FIRST|AFTER COLUMN 这些关键字都属于MySQL在标准SQL上的扩展，在其他数据库上不一定适用。

14.表改名

ALTER TABLE tablename RENAME[TO]new_tablename

例如，将表emp改名为emp1,命令如下

mysql>alter table emp rename emp1;
mysql>desc emp;

DML语句

1.插入记录

INSERT　INTO　tablename(field1,field2,…fieldn)VALUES(value1,value2,…valuesn);

例如，向表emp中插入一下记录：ename为zzx1，hiredate为2000-01-01，sal为2000，deptno为1，命令执行如下：

mysql>insert into emp(enable,hiredate,sal,deptno)values('zzx1','2000-01-01','2000',1);
mysql>insert into emp values('lisa','2003-02-01','3000',2);

对于含可空字段，非空但是含有默认值的字段，自增字段，可以不用inserth后的字段列表里面出现，values后面致谢对应字段名称的value,这些没写的字段可以自动设置为NULL，默认值，自增的下一个数字，这样在某些情况下可以大大缩短SQL语句的复杂性。

例如，支队表中的ename和sal字段显式插入值：

mysql>insert into emp(ename,sal)values('dony',1000);

查看实际插入值：

mysql>select * from emp;

在MYSQL中，insert语句还有一个很好的特性，可以一次性插入多条记录，语法如下：

INSERT INTO tablename (field1,field2,…fieldn)VALUES (record1_value1,record1_value2,…record1_valuesn),

(record2_value1,record2_value2,…record2_valuesn),

…

(recordn_value1,recordn_value2,…recordn_valuesn);

下面的例子中，对表dept一次插入两条记录：

mysql>insert into dept values(5,'dept5'),(6,'dept6');
mysql>select * from dept;

2.更新记录

对于表里的记录纸，可以通过update命令进行更改，语法如下：

UPDATE tablename SET field1=value1,field2=value2,…filedn=valuen [WHERE CONDITION]

例如，将表emp中的ename为"lisa"的薪水（sal）从3000更改为4000：

mysql>update emp set sal=4000 where ename='lisa';

在MYSQL中，update命令可以同时更新多个表中数据，语法如下：

UPDATE t1,t2..tn set t1.field1=expr1,tn.fieldn=exprn[WHERE CONDITION]

在下例中，同时更新表emp中的字段sal和表dept中的字段deptname:

mysql>select * from emp;
mysql>select * from dept;
mysql>update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;
mysql>select * from emp;
mysql>select * from dept;

两个表的数据同时进行了更新。

注意：多表更新的语法更多的用在了根据一个表的字段，来动态的更新另外一个表的字段

3.删除记录

如果记录不再需要，可以用delete命令进行删除，语法如下DELETE FROM tablename [WHERE CONDITION]

例如，在emp中将ename 为‘dony’的记录全部删除，命令如下：

mysql>delete from emp where ename='dony';

在MYSQL中可以一次删除多个表的记录。语法如下：

DELETE t1,t2…tn FROM t1,t2…tn[WHERE CONDITION]

如果from后面的表名用别名，则delete后面的也要用相应的别名，否则会提示语法错误。

在下例中，将表emp和dept中deptno为3的记录同时都删除：

mysql>select * from emp;
mysql>select * from dept;
mysql>select * from emp;
mysql>delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno =3
mysql>select * from emp;
mysql>select * from dept;

注意：不管是单表还是多表，不加where条件将会把表的所有记录删除，所以操作时一定要小心。

4.查询记录

SELECT * FROM tablename [WHERE CONDITION]

mysql>select * from emp;

下面两个查询是等价的。

mysql>select * from emp;

mysql>select ename,hiredate,sal,deptno from emp;

(1)查询不重复的记录

有时候需要将表中的记录去掉重复后显示出来，可以用distinct关键字来实现：

mysql>select ename,hiredate,sal,deptno from emp;
mysql>select distinct deptno from emp;

(2)条件查询

在很多情况下，用户并不需要查询所有记录，而只是需要根据限定条件来查询一部分数据，用where关键字可以来实现这样的操作。

mysql>select * from emp;
mysql>select * from emp where deptno=1;

结果集中将符合条件的记录列出来。上面的例子中，where后面的条件是一个字段的’=‘比较，除了’='以外，还可以使用>、<、>=、<=、!=等比较运算符；多个条件之间还可以使用or、and等逻辑运算符进行多条件联合查询。

以下是使用多字段条件查询的例子：

mysql>select * from emp where deptno=1and sal<3000;

(3)排序和限制

我们经常会有这样的需求，取出按照某个字段进行排序后的记录结果集，这就用到了数据库的排序操作，用关键字ORDER BY来实现。语法如下：

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC |ASC] , field2 [DESC|ASC] ,…fieldn[DESC|ASC]]

其中，DESC和ASC是排序顺序的关键字，DESC表示按照字段进行降序排列，ASC则表示升序排列，如果不写此关键字默认升序排列。ORDER BY 后面可以跟多个不同的排序字段，并且每个排序字段可以有不同的排序顺序。

例如，把emp表中的记录按照工资高低来进行显示：

mysql>select * from emp order by sal;

如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序，以此类推。如果只有一个排序字段，则这些字段相同的记录将会无序排列。

例如，把emp表中的记录都按照部门编号 depto字段排序：

mysql>select * from emp order by deptno;

对于deptno 相同的前两条记录，如果按照工资由高到底排序，可以使用以下命令：

mydql>select * from emp order by deptno ,saldesc;

对于排序后的记录，如果希望只显示一部分，而不是全部，这时，就可以使用LIMIT关键字来实现，LIMIT的语法如下：

SELECT ......[LIMIT offset_start,row_count]

其中offset_start表示记录的起始偏移量，row_count表示显示的行数。在默认情况下，其实偏移量为0，只需要写记录行数就可以，这时候，显示的实际就是前n条记录，看下面的例子：

例如，显示emp表中按照sal排序后的前3条记录：

mysql>select * from emp order by sal limit 3;

如果要显示emp表中按照sal排序后从第二条记录开始，显示3条记录：

mysql>select * from emp order by sal limit 1,3;

limit经常和order by一起配合使用来进行记录的分页显示。

注意：limit属于MYSQL扩展SQL92后的语法，在其他数据库上并不通用

（4）聚合

很多情况下，我们需要进行一些汇总操作，比如统计整个公司的人数或者统计每个部门的人数，这个时候就要用到SQL的聚合操作。

聚合操作的语法如下：SELECT [field1,field2,…fieldn]fun_name FROM tablename[WHERE where_contition] [GROUP field1,field2,…fieldn[WITH ROLLUP]] [HAVING where_contition]对其参数进行一下说明。

fun_name 表示要做的聚合操作，也就是聚合函数，常用的又sum(求和)、count(*)(记录数)、max(最大值)、min(最小值)。

GROUP BY 关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该卸载group 不要后面。

WITH ROLLUP 是可选语法，表明是否对分类聚合后的结果进行再汇总。

HAVING关键字表示对分类后的结果再进行条件的过滤。

注意：having和where的区别在于having是对聚合后的结果进行条件的过滤，而where是再聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用where 线过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用having进行再过滤。

例如，要emp表中统计公司的总人数：

mysql>select count(1) from emp;

在此基础上，要统计各个部门的人数：

mysql>select deptno,count(1) from emp group by deptno;

更细溢写，既要统计各部门人数，又要统计总人数：

mysql>select deptno,count(1) from emp gropo by deptno with rollup;

统计人数大于1 人的部门：

mysql>select deptno,count(1) from emp group by deptno having count(1)>1;

最后统计公司所有员工的薪水综额、最高和最低薪水：

mysql>select * from emp;
mysql>select sum(sal),max(sal),min(sal) from emp;

（5）表连接

当需要同时显示多个表中的字段时，就可以用表连接来实现这样的功能。

从大类上分，表连接分为内连接和外连接，它们之间的最主要区别是内连接仅选出两种表中互相匹配的记录，而外连接会选出其他不匹配的记录。我们最常用的是内连接。

例如，查询出所有雇员的名字和所在部门名称，因为雇员名称和所在部门分别存放在表emp和dept中，因此需要使用表连接来进行查询：

mysql>select * from emp;
mysql>select * from dept;
mysql>select ename,deptname from emp,dept where emp.deptno=dept.deptno;

外连接又分为左连接和右连接，具体定义如下：

左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录【left join】

右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录【right join】

例如：查询emp中所有用户名和所在部门名称：

mysql>select * from emp;
mysql>select * from dept;
mysql>select ename,deptname from emp left join dept on emp.deptno=dept.deptno;

比较这个查询和上例中的查询，都是查询用户名和部门名，两者的区别在于本例中列出了所有的用户名，即使有的用户名（dony）并不存在合法的部门名称（部门号为4，在deptno中没有这个部门）；而上例中仅仅列出了存在合法部门的用户名和部门名称。

右连接和左连接类似，两者之间可以互相转化，例如，上面的例子可以改写为如下的右连接：

mysql>select ename,deptname from dept right join emp on dept.deptno=emp.deptno;

(6)子查询

某些情况下，当我们查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。用于子查询的关键字主要包括 in、not in、！=、exists、not exists等。

例如，从emp表中查询出所有部门在表中的所有记录：

mysql>select * from emp;
mysql>select * from dept;
mysql>select * from emp where deptno in(select deptno from dept);

如果子查询记录数唯一，还可以用=代替in：

mysql>select * from emp where deptno =(select deptno from dept);
报错：ERROP 1242（21000）：Subquery returns more than 1 row
mysql>select * from emp where deptno = (select deptno from dept limit 1);

某些情况下，子查询可以转化为表连接，例如：

mysql>select * from emp where deptno in(select deptno from dept);

转换为表连接后：

mysql>select emp.* from emp,dept where emp.deptno =dept.deptno;

注意：表连接在很多情况下由于优化子查询

（7）记录联合

我们经常会碰到这样的应用，将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来，这个时候就需要用union和union all关键字来实现这样的功能，具体语法如下：

SELECT * FROM t1 UNiON|UNION ALL SELECT * FROM t2 … UNION|UNION ALL SELECT * FROM tn;

UNION 和 UNION ALL的主要区别是 UNION ALL是把结果集直接合并在一起，而UNION是将UNION ALL 后的结果进行一次DISTINCT，去除重复记录后的结果。

mysql>select * from emp;
mysql>select * from dept;
mysql>select deptno from emp
->union all
->select eptno from dept;

DCL语句

DCL语句主要是DBA用来管理系统中的对象权限时使用的。一般的开发人员很少使用。

创建一个数据库用户z1,具有对sakila数据库中所有表的SELECT/INSERT权限：

mysql> grant select,insert on sakila.* to 'z1'@'localhost'identified by '123';
mysql>exit

mysql -uz1 -p123
mysql>use sakila Database changed
mysql>insert into emp values('bzshen','2005-04-01',3000,'3');

由于权限变更，需要将z1的权限变更，收回INNSERT，只能对数据进行SELECT操作：

mysql -uroot
mysql>revoke insert on sakila.* from 'z1'@'localhost';
mysql>bye

用户z1重新登陆后执行前面语句：

mysql -uz1 -p123
mysql>insert into emp values('bzshen','2005-04-01',3000,'3');
mysql>use sakila Database changed
mysql>insert into emp values('bzshen','2005-04-01',3000,'3');
报错：ERROR 1142（42000）：INSERT command denied to user 'z1'@'localhost'for table 'emp'

帮助的使用

1.按帮助层次看帮助

mysql>?contents       #contents命令可查看显示所有可供查询的分类#
mysql>?data types     #查看MYSQL中支持的数据类型#
mysql>?int            #查看int类型的具体介绍#

2.快速查阅帮助

mysql>?show           #查看show命令都能查看什么东西#
mysql>?create table   #查看 CREATE TABLE的语法#

---

摘抄自《深入浅出MySQL-数据库开发优化与管理维护》