Mysql基础语句

一、 MySQL语句

    在熟悉安装及访问 MySQL 数据库以后， 接下来将学习使用 MySQL 数据库的基本操作，这也是在服务器运维工作中不可或缺的知识。 本节中的所有数据库语句均在“MySQL>”操作环境中执行

    MySQL 是一套数据库管理系统，在每台 MySQL 服务器中，均支持运行多个数据库，每个数据库存放着许多表， 如图所示。

（一） MySQL数据库的操作

1、 查看数据库

查看数据库有3种方式

（1） 第一种直接进入数据库查看

MySQL> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| MySQL              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

    1：information_schema这个数据库保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型，访问权限等。 [ˈskimə]

    2：performance_schema 这是MySQL5.5新增的一个性能优化的引擎：命名PERFORMANCE_SCHEMA,主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表，

    3：MySQL库是系统库，里面保存有账户信息，权限信息等。

    4：MySQL5.7后增加了sys 系统数据库，sys数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息,元数据是关于数据信息的数据，如数据库名或表名，列的数据类型，或访问权限等。

（2） 以行的方式显示

MySQL> show databases \G     #以行的方式显示

*************************** 1. row ***************************

Database: information_schema

*************************** 2. row ***************************

Database: MySQL

*************************** 3. row ***************************

Database: performance_schema

*************************** 4. row ***************************

Database: sys

4 rows in set (0.00 sec)

（3） 在shell中查看

MySQL -e后面直接跟sql语句，这种方式一般是在shell脚本中用到

[root@localhost ~]# MySQL -e 'show databases' -uroot -pAbcd1234

MySQL: [Warning] Using a password on the command line interface can be insecure.

+--------------------+

| Database           |

+--------------------+

| information_schema |

| MySQL              |

| performance_schema |

| sys                |

+--------------------+

2、创建数据库

（1） 语法

create database 数据库名;

（2）创建数据库注意事项

    1、在文件系统中，MySQL的数据存储区以目录方式表示MySQL数据库。因此，上面命令中的数据库名字必须与操作系统的约束的目录名字一致。例如不允许文件和目录名中有\,/,:,*,?,”,<,>,|这些特殊符号，在MySQL数据库名字中这些字母会被自动删除。

[root@localhost ~]# ls /data/MySQL/data/

auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  MySQL  performance_schema  sys

    2、数据库的名字不能超过64个字符，包含特殊字符的名字或者是全部由数字或保留字组成的名字必须用反引号``括起来。

    3、数据库不能重名

（3）创建一个数据库

MySQL> create database HA;

Query OK, 1 row affected (0.00 sec)

MySQL> create database `HA-test`;

Query OK, 1 row affected (0.00 sec)

（4） 查看数据库

MySQL> show databases;

 （5） 查看数据库目录

    我们可以去数据目录下查看新创建的数据库目录

[root@localhost ~]# ls  /data/MySQL/data/

 注：建议数据名不要包含特殊符号或是纯数字的。

3、选择要操作的数据库

    我们需要使用哪个数据库，就用use进行选择，后面的操作默认都是在被选择的数据库中进行操作。

MySQL> use HA-test;

Database changed

4、 查看自己在所处的位置

MySQL> select database();

 5、 在命令行选择默认的数据库

我们也可以在命令行直接选择我们需要进入的数据库

[root@localhost ~]# MySQL -uroot -pAbcd1234 HA-test

MySQL> select now(),user(),database();

6、删除数据库

（1）命令

MySQL> drop database `HA-test`;

Query OK, 0 rows affected (0.01 sec)

（2）使用IF EXISTS 子句以避免删除不存在的数据库时出现的MySQL错误信息      exists  [ɪɡˈzɪst]

MySQL> drop database if exists `HA-test`; #如果存在则删除

Query OK, 0 rows affected, 1 warning (0.00 sec)

也可以在创建数据库时使用

MySQL> create database if not exists HA;  #if not exists 如果不存在则创建

Query OK, 1 row affected (0.00 sec)

（二） 关于表的操作

1、创建表create：

语法：create table 表名 (字段名 类型, 字段名 类型, 字段名 类型);

MySQL> use HA;

Database changed

MySQL> create table student(id int(20),name char(40),age int);

Query OK, 0 rows affected (0.02 sec)

    其中id字段（列）定义为整数类型，也就是说id字段的值为整数，name字段定义为字符类型，也就是说name字段的值只能输入字符型数据。

2、 查看表相关信息

MySQL> use HA;

Database changed

MySQL> show tables;

3、 查看表结构

使用desc 命令来查看表的结构

MySQL> desc student;

4、查看创建表执行的命令

MySQL>  show create table student \G;

5、指定默认存储引擎和字符集

新建一个表，指定默认的存储引擎为InnoDB,编码为utf8

MySQL> create table student2(id int(20),name char(40),age int) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Query OK, 0 rows affected (0.02 sec)

MySQL> show create table student2 \G

6、 删除表

MySQL> drop table student2;

Query OK, 0 rows affected (0.01 sec)

7、 禁止预读表信息

没有禁止前转换数据库会有提示信息

MySQL> use MySQL;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with –A

解决这个问题可以在登陆MySQL的时候添加参数-A

[root@localhost ~]# MySQL -uroot -pAbcd1234 -A

MySQL> use MySQL;     

Database changed    #发现没有提示信息了

8、MySQL数据类型

数据类型的作用：用来约束将来录入数据的格式。

    MySQL的常用数据类型包括整数类型，浮点数类型，日期和时间类型，字符串类型，ENUM枚举类型等。

数值类型：

日期和时间类型：

字符串类型：

char和varchar区别

char类型：

CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间

当保存CHAR值时，一次性分配指定长度的存储空间，使用空格字符填充剩余空间。当检索到CHAR值时，尾部的空格被删除掉。

优点:

  char定长字符存储，效率较高，比如手机号11位很适合

缺点:

  定长长度的字符串比较浪费空间的

Varchar类型：

    VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间。 

    VARCHAR存储变长数据，保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。

    如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么"+1"呢？这一个字节用于保存实际使用了多大的长度。

优点:

  varchar相对char来讲更省空间

缺点:

  在存数据时，效率低点

总结：从空间上考虑，用varchar合适；从效率上考虑，用char合适。

枚举类型：

枚举类型字段的值，必须从预先定义好的字符串集合中选取。

ENUM(value1, value2,value3,...)

ENUM（枚举）：只能取一个，用于互斥。例如性别字段只能是男人或女人。

    下面我们创建一个学生信息表，包括学号、姓名、性别、年龄、电话号码和住址字段，每个字段要有注释描述。

MySQL> create database db01;

MySQL> use db01

MySQL> create table stu_info(id int comment '学号', name varchar(20) comment '姓名', age tinyint comment '年龄', ', sex enum('男','女') comment '性别', phone_number char(11) comment '电话号码', address varchar(255) comment '住址') engine=innodb default charset=utf8;

MySQL> desc stu_info;

9、修改表名称 alter

语法：alter table 表名 rename 新表名;

修改db01库中stu_info表名为student_info

MySQL> alter table stu_info rename student_info;

Query OK, 0 rows affected (0.02 sec)

MySQL> show tables;

10、 修改表中的字段类型

语法：alter table 表名 modify 要修改的字段名要修改的类型;

查看student_info表结构

MySQL> desc student_info;

修改字段name 的varchar(20)字段类型为varchar(30)

MySQL> MySQL> alter table student_info modify name varchar(30);

查看修改完的student_info表结构

MySQL> desc student_info;

11、 修改表中的字段类型和字段名称

语法：alter table 表名 change 原字段名 新字段名 新字段类型;

MySQL> alter table student_info change age birthday datetime comment '出生日期';

MySQL> desc student_info;

注：CHANGE 和MODIFY的区别：

CHANGE 对列进行重命名和更改列的类型，需给定旧的列名称和新的列名称、数据类型。 MODIFY 可以改变列的类型，此时不需要重命名（不需给定新的列名称）

12、 在表中添加字段：

语法：alter table 表名 add字段名 字段类型；

enum  #枚举类型，比如血型，只能在a、b、o、ab中选择。

MySQL>  alter table student_info add blood_type enum('o','a', 'b', 'ab');

MySQL> desc student_info;

13、 在表中指定位置添加字段

（1） 在第一列添加一个字段

MySQL> alter table student_info add uid int first;

MySQL> desc student_info;

（2）在phone_number后面添加一个class字段

MySQL> alter table student_info add class char(40) after phone_number;

14、删除表中字段

语法：alter table 表名 drop 字段名 ;

MySQL> alter table student_info drop uid;

MySQL> desc student_info;

发现表中uid字段不见了

（三） 关于表中记录的操作

1、 插入记录——INSERT

INSERT INTO 语句用于向表中插入新的行。

语法：insert  into 表名values (字段值1,字段值2, 字段值3);

    插入记录时values所指定的值要和表中字段的个数、顺序以及类型要一一对应。

在student_info表中插入数据

MySQL> insert into student_info values(1,'tom','2005-11-11','男','13311111111','c100','北京市昌平区沙河镇','ab');

Query OK, 1 row affected (0.01 sec)

insert执行成功后，可以使用select查询表中的数据，select语句在后面会详细讲解。这里只要会用就可以。

MySQL> select * from student_info;

2、 同时插入多条记录

MySQL> insert into student_info values(2,'kim','2007-9-11','男','13522222222','c100','北京市朝阳区团结湖','o'));  ,(3,'mary','2006-4-22','女','13522222222','c100','北京市昌平区政府街','b');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

查看插入的数据行

MySQL> select * from student_info;

3、 分开插入表记录

我们也可以指定所要插入数据的字段：

语法：

INSERT INTO table_name (字段1, 字段2,...) VALUES (字段值1, 字段值2,....)

例如：向students表中的id,name字段插入数据

MySQL> insert into student_info(id,name,sex,blood_type) values(4,'alice','女','ab');

Query OK, 1 row affected (0.00 sec)

查看插入的数据行

MySQL> select * from student_info;

4、 将一个表的查询结果插入另一个表中

创建t1表，将student_info表的查询结果插入到t1表

MySQL> create table t1(name varchar(30),sex enum('男','女'),class char(40));

MySQL> desc t1;

MySQL> insert into t1 (name,sex,class) select name,sex,class from student_info;

MySQL> select * from t1;

5、 查询表中记录——select

SELECT 语句用于从数据库表中读取数据。

语法：

select * from 表名； # *号表示表中所有的字段

（1） 查询student表中所有记录

事先创建students表，然后执行insert插入几行数据。

MySQL>  select * from students;

（2） 当表中记录比较多时可以使用\G查看

MySQL> select * from student\G

（3）只查询表中某个字段或某些字段的内容

MySQL> select name from students;

MySQL> select id,name from students;

（4）查看别的数据库的表或者不在本数据库上进行查看

语法：SELECT 字段 FROM 数据库名.表名;

效果等同于先使用use数据库，然后再看看表内容

MySQL> select * from HA.students;

6、删除表中记录——delete

DELETE语句用于删除表中的记录。

语法:

DELETE FROM table_name WHERE some_column=some_value;

请注意 SQL语句中的 WHERE 条件子句！

    WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！

（1）删除students表中id为3的行

MySQL>  delete from students where id=3;

Query OK, 1 row affected (0.00 sec)

MySQL> select * from students;  #发现表中id为3的记录不见了

+------+--------+------+

| id   | name   | age  |

+------+--------+------+

|    1 | zhangs |   21 |

|    2 | lis    |   24 |

|    4 | hangl  | NULL |

+------+--------+------+

3 rows in set (0.00 sec)

（2） 删除age为空的行

MySQL> delete from students where age is null;

Query OK, 1 row affected (0.00 sec)

MySQL> select * from students;

+------+--------+------+

| id   | name   | age  |

+------+--------+------+

|    1 | zhangs |   21 |

|    2 | lis    |   24 |

+------+--------+------+

2 rows in set (0.00 sec)

7、 更新记录——update

update 语句用于修改表中的数据。

语法：

UPDATE table_name 

SET column1=value1,column2=value2,...

WHERE some_column=some_value;

请注意 SQL UPDATE 语句中的 WHERE 子句！

    WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！

（1） 把表中id为2的记录age更新为25

MySQL> update students set age=25 where id=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MySQL> select * from students;

+------+--------+------+

| id   | name   | age  |

+------+--------+------+

|    1 | zhangs |   21 |

|    2 | lis    |   25 |

+------+--------+------+

2 rows in set (0.01 sec)

（2）把表中所有的id都更新为2

MySQL> update students set id=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 2  Changed: 1  Warnings: 0

MySQL> select * from students;

（3）同时更新多个字段的值，请使用逗号隔开

MySQL>  update students set id=1,name='zhangsan' where age=21;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0 

MySQL> select * from students;

3、 SQL条件查询语句

首先往表中插入一些数据

MySQL> insert into students values(2,'lisi',23),(3,'wange',26),(4,'libin',28),(5,'tom',30),(6,'sorry',24);

Query OK, 5 rows affected (0.00 sec)

Records: 5  Duplicates: 0  Warnings: 0

（1） 查询STUDENTS表中的NAME,AGE

MySQL> select name,age from students;

可以看到表中有重复的数据lisi

（2）去重复查询distinct

MySQL> select distinct name,age from students;  #可以看见重复的行不在了

4、 使用AND和OR进行多条件查询

（1）查询表中id>3和age>25的记录

MySQL> select id,name,age from students where id>3 and age>25;

（2） 查询表中id>3 或者 age>25的记录

MySQL> select id,name,age from students where id>3 or age>25;

5、MySQL区分大小写查询binary

MySQL默认查询是不区分大小写的

    BINARY是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解为在字符串比较的时候区分大小写。

插入大写记录

MySQL> insert into students values(7,'KILL',32),(8,'kill',32);

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

MySQL> select name from students where name='kill';

区分大小写查询

MySQL> select * from students where binary name='kill';

MySQL>  select * from students where binary name='KILL';

6、 MySQL查询排序

语法：select字段1，字段2 from 表名order by 字段名;

（1） 默认为升序asc

MySQL> select id from students order by id asc;

（2）降序desc

MySQL> select id from students order by id desc;

6、关于MySQL命令帮助

help会告诉我们很多使用方法和信息

MySQL> help show;

MySQL> help select;