MySQL常用语句

第1章 SQL结构化语言

1.什么是SQL?

SQL，英文全称Structured Query Language，中文意思是结构化查询语言，它是一种关系型数据库中的数据进行定义和操作的语言方法，是大多数关系数据库管理系统所支持的工业标准。

结构化查询语言SQL是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以，具有完全不同于底层结构的，不同数据库系统可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使得它具有极大的灵活性和强大的功能。不同的数据库系统的SQL语言会有一些差别。

2.SQL的分类

最常见的分类一般就是3类：

DDL(Data Definition Language)---数据定义语言（CREATE,ALTER,DROP），管理基础数据，例如：库，表   

DCL(Data Control Language)---数据控制语言（GRANT,REVOKE,COMMIT,ROLLBACK），用户授权，权限回收，数据提交回滚等。     

DML（Data Manipulation Language）---数据操作语言（SELECT,INSERT,DELETE,UPDATE），针对数据库里的表，记录  

第2章 MySQL数据库常见管理应用

1.创建数据库

命令语法： create database <数据库名>       #注意数据库不能数字开头

mysql> create database oldboy;            #创建数据库

Query OK, 1 row affected (0.01 sec)

mysql> show databases like 'oldboy%';        #查看oldboy开头的数据库

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

| Database (oldboy%) |

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

| oldboy             |

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

1 row in set (0.01 sec)

mysql> show databases like '%e%';      #查看包括‘e’的数据库

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

| Database (%e%)     |

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

| information_schema |

| test               |

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

2 rows in set (0.00 sec)

查看数据库创建语句

mysql> show create database oldboy;

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

| Database | Create Database                                                   |

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

| oldboy   | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET latin1 */ |

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

1 row in set (0.00 sec)

创建GBK字符集数据库

mysql> create database oldboy_gbk character set gbk collate gbk_chinese_ci;

Query OK, 1 row affected (0.00 sec)

 

mysql> show create database oldboy_gbk;

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

| Database   | Create Database                                                    |

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

| oldboy_gbk | CREATE DATABASE `oldboy_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |

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

1 row in set (0.00 sec)

提示：如记不住COLLATE校对规则，可以：

mysql> show character set;

查看对应字符集的校对规则

删除数据库

命令：drop database <数据库名>

例如：删除名为oldboy的数据库

mysql> drop database oldboy;

连接数据库

命令： use <数据库名>   #相当于linux里的cd切换目录的命令，use是切换数据库

例如：

use oldboy;       #进入oldboy库

select database();  #查看现在在哪个库中

删除mysql多余的系统账号

语法：drop user “user”@”主机域” #引号可以是单引号或双引号，但不能不加

mysql> drop user ‘root’@’oldboy’;

mysql> drop user ‘’@’localhost’;    #没有的部分就用双引号代替

 

创建MySQL用户及赋予用户权限

grant	all privileges	on dbname.*	to username@localhost	identified by‘passwd’
授权命令	对应权限	目标：库和表	用户名和客户端主机	用户密码

grant all privileges on dbname.* to username@localhost identified by ‘passwd’;

操作案例：

创建oldboy用户，对test库具备所有权限，允许从localhost主机登录管理数据库，密码为oldboy123

grant all privileges on test.*  to  ‘oldboy’@’localhost’ identified by ‘oldboy123’;

flush privileges;

show grants for ‘oldboy’@’localhost’     #查看权限

查看当前数据库用户情况：

mysql> select user,host from mysql.user;

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

| user | host      |

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

| root | localhost |

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

1 row in set (0.00 sec)

分两步授权方法：

1创建用户

mysql> create user oldboy@'localhost' identified by 'oldboy123';

Query OK, 0 rows affected (0.00 sec) 

2授权

mysql> grant all on test.* to oldboy@'localhost';

Query OK, 0 rows affected (0.00 sec)

授权局域网内主机远程连接数据库：

一条命令百分号匹配法：

grant all on *.* to test@’10.0.0.%’ identified by ‘test123’;

一条命令子网掩码配置法

grant all on *.* to test@’10.0.0.0/255.255.255.0’ identified by ‘test123’;

两条命令实现法：

1先创建用户并设置密码

create user test@’10.0.0.%’ identified by ‘oldboy123’;

2对用户授权指定权限和管理的库表

grant all on *.* to test@’10.0.0.0/255.255.255.0’;

flush privileges;

gtant包括的权限：

SELECT

INSERT

UPDATE

DELETE

CREATE

DROP

REFERENCES

INDEX

ALTER

CREATE TEMPORARY TABLES

LOCK TABLES

EXECUTE

CREATE VIEW

SHOW VIEW

CREATE ROUTINE

ALTER ROUTINE

EVENT

TRIGGER

第3章 表操作

首先创建库

mysql> create database oldboy character set utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

 

mysql> show create database oldboy;

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

| Database | Create Database                                                 |

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

| oldboy   | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ |

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

1 row in set (0.00 sec)

 

建立表

1建表的基本命令语法：

create table <表名>(

<字段名1><类型1>,

…

<字段名n><类型n>);

提示：其中create table是关键字，不能更改，但是大小写可以变化。

2建表语句

下面是人工写法设计的建表语句例子，表明student，

create table student(

id int(4) not null,

name char(20) not null,

age tinyint(2) NOT NULL default '0',

dept varchar(16) default NULL

);

MySQL生成的建表语句student表例子

CREATE TABLE `student` (

  `id` int(4) NOT NULL,

  `name` char(20) NOT NULL,

  `age` tinyint(2) NOT NULL DEFAULT '0',

  `dept` varchar(16) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql的引擎就相当于linux的文件系统，mysql要存数据，就要有引擎。

MySQL5.1及以前默认引擎为MyISAM，MySQL5.5.5以后默认引擎为InnoDB。

MySQL表的字段类型(以下知识，掌握黄色重点即可)

数字类型

列类型	需要的存储量
TINYINT	1字节 mysql> system echo $((2*8)) 256
SMALLINT	2字节
MEDIUMINT	3字节
INT	4字节（4294967296）
INTEGER	4字节
BIGINT	8字节
FLOAT(X)	4如果X<=24或8如果25<=X<=53
FLOAT	4字节
DOUBLE	8字节
DOUBLE PRECISION	8字节
REAL	8字节
DECIMAL(M,D)	M字节（D+2，如果M<D）
NUMERIC(M,D)	M字节（D+2，如果M<D）

日期和时间类型（DATE日期类型：支持范围是1000-01-01到9999-12-31。mysql以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列）

列类型	需要的存储量
DATE	3字节
DATETIME	8字节
TIMESTAMP	4字节
TIME	3字节
YEAR	1字节

串类型

列类型	需要的存储量
CHAR(M)	M字节，1<=M<=255
VARCHAR(M)	L+1字节，在此L<=M和1<=M<=255
BLOB,TEXT	L+2字节，在此L<2 ^ 16

1. INT[(M)]:正常大小整数类型

2. CHAR(M)型：定长字符串类型，当存储时，总是用空格填满右边到指定的长度。

3. VARCHAR型，变长字符串类型

有关mysql字段类型详细内容，请参考mysql手册。

1. INT[(M)]型： 正常大小整数类型

2. DOUBLE[(M,D)] [ZEROFILL]型：正常大小（双精密）浮点数字类型

3. DATE日期类型：支持的范围是1000-01-01到9999-12-31.MySQL以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列

4. CHAR(M)型：定长字符串，当存储时，总是用空格填满右边到指定的长度

5. BLOB TEXT类型，最大长度为65535(2^16-1)个字符。

6. VARCHAR型，变长字符串类型。

查看表结构

mysql> desc 表名;

查看建表语句

mysql> show create table 表名;

为表的字段创建索引

索引就像书的目录一样，如果在字段上建立了索引，那么索引列为查询条件时可以加快查询数据的速度，这是mysql优化的重要内容之一。

主键索引

查询数据库，按主键查询是最快的，每个表只能有一个主键列，但是可以有很多普通索引列。主键列要求列的所有内容必须唯一，而普通索引列则不要求内容必须唯一。

主键就类似于我们在学校学习时的学号一样，班级内是唯一的。整个表的一条记录的主键值在表内都是唯一的，用来唯一标识一条记录。那么，我们该如何建立主键索引和普通索引呢？

首先，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多列创建索引。

建立主键索引的方法：

1、 在建表时，可以增加建立主键索引的语句如下：

create table student(

id int(4) not null auto_increment,

 name char(20) not null,

 age tinyint(2) NOT NULL default '0',

 dept varchar(16) default NULL,

primary key(id),

key index_name (name)

);

primary key(id),            主键

key index_name (name)       name字段普通索引

只查看索引：

mysql> show index from studentG

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

        Table: student

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: id

    Collation: A

  Cardinality: 0

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

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

        Table: student

   Non_unique: 1

     Key_name: index_name

 Seq_in_index: 1

  Column_name: name

    Collation: A

  Cardinality: 0

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

2 rows in set (0.00 sec)

mysql> desc student;

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

| Field | Type        | Null | Key | Default | Extra          |

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

| id    | int(4)      | NO   | PRI | NULL    | auto_increment |

| name  | char(20)    | NO   | MUL | NULL    |                |

| age   | tinyint(2)  | NO   |     | 0       |                |

| dept  | varchar(16) | YES  |     | NULL    |                |

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

4 rows in set (0.00 sec)

PRI为主键标识     MUL为普通索引标识

 

2 建表后通过alter命令增加主键索引（不这样干）

alter table student change id id int primary key auto_increment;

创建普通索引

1在建表时，可以增加建立普通索引的语句：

create table student(

id int(4) not null auto_increment,

 name char(20) not null,

 age tinyint(2) NOT NULL default '0',

 dept varchar(16) default NULL,

primary key(id),

key index_name (name) 

);

2 建表后利用alter建立索引

mysql> alter table student add index index_dept(dept);

修改表，修改哪个表，添加，添加索引，索引名，字段名；

Query OK, 0 rows affected (0.13 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;

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

| Field | Type        | Null | Key | Default | Extra          |

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

| id    | int(4)      | NO   | PRI | NULL    | auto_increment |

| name  | char(20)    | NO   | MUL | NULL    |                |

| age   | tinyint(2)  | NO   |     | 0       |                |

| dept  | varchar(16) | YES  | MUL | NULL    |                |

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

4 rows in set (0.00 sec)

提示：生产环境中不能随便创建索引，因为如果记录很大的话，创建索引会很长时间，100W条记录，创建索引可能会1分多钟。创建索引往往会选在mysql不繁忙的时候。

删除索引：

mysql> alter table student drop index index_dept;   

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student drop index index_name;

Query OK, 0 rows affected (0.01 sec)

如果前几个字符就可以表示唯一，也可以创建一个字段的前几个字符为索引

mysql> create index index_name on student(name(8));  

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

name的前8个字符为索引

如果前几个字符就是唯一了，或者接近唯一了，就可以用前几个创建索引。

mysql> show index from studentG;

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

        Table: student

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: id

    Collation: A

  Cardinality: 0

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

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

        Table: student

   Non_unique: 1

     Key_name: index_name

 Seq_in_index: 1

  Column_name: name

    Collation: A

  Cardinality: 0

     Sub_part: 8             #前8个字符为索引

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

2 rows in set (0.00 sec)

select * from mysql.user where user=’root’ and host=’localhost’;

索引是在查询的条件上创建的索引,如上条语句就要创建user和host的索引

为表的多个字段创建联合索引

如果查询数据的条件

有时候查询的条件有四五个，把每一个条件都创建索引效率也不高，这时候就需要创建联合索引。

三个字段：

A   B   C

联合索引有前缀特性

index(A,B,C)

创建了一个ABC的联合索引，有前缀特性，单独查A、AB、ABC的时候都会用到索引，但是单独查B、BC、AC的时候就不会用到索引。

所以创建联合索引的时候要把：

经常用于条件查询的放到前面。

mysql> create index ind_name_dept on student(name,dept);

创建时需要注意name和dept哪个用于查询次数多，哪个就放在前边。

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;

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

| Field | Type        | Null | Key | Default | Extra          |

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

| id    | int(4)      | NO   | PRI | NULL    | auto_increment |

| name  | char(20)    | NO   | MUL | NULL    |                |

| age   | tinyint(2)  | NO   |     | 0       |                |

| dept  | varchar(16) | YES  |     | NULL    |                |

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

 rows in set (0.00 sec)

也可以创建前几个字符的联合索引：

mysql> create index ind_name_dept on student(name（8）,dept（10）);

创建联合索引的优点：

1、 更唯一

2、 多个列联合索引

如：我们公司所有人，  家在北京的   年龄小于30的   性别女

这样一下人就特别少或者没有了，唯一性比较高

创建唯一索引（非主键）

当希望这列内容都是唯一的时候，创建唯一索引

mysql> create unique index uni_ind_name on student(name);

Query OK, 0 rows affected (0.07 sec)

索引列的创建及生效条件

问题1、既然索引可以加快查询速度，那么就给所有的列创建索引吧？

解答：因为索引不但占用系统空间，而且更新数据库时还需要维护索引数据的，因此，索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，更新频繁，读取比较少的表要少建立索引。

问题2：需要在哪些列上创建索引呢？

select user,host  from  mysql.user  where password=….,索引一定要创建在where后的条件列上，而不是select后的选择数据的列上。另外，我们要尽量选择在唯一值多的大表上的列建立索引，例如，男女性别列唯一值，不适合建立索引。

创建索引命令集合小结

1、 创建索引相关命令集合

创建主键索引：

alter table sudent change id id int primary key auto_increment;

删除主键索引：

alter table student drop primary key;

创建普通索引：

alter table student add index index_dept(dept(8));

根据列的前n个字符创建索引

create index index_dept on student(dept(8));

根据多列创建联合索引

create index ind_name_dept on student(name,dept);

根据多个列的前n个字符创建联合索引

create index ind_name_dept on student(name(8)，dept(10))；

创建唯一索引

create unique index uni_ind_name on student(name);

删除普通索引与唯一索引：

alter table student drop index index_dept;

drop index index_dept on student;

创建索引的基本知识小结：

1、 索引类似书籍的目录，会加快查询数据的速度

2、 要在表的列（字段）上创建索引

3、 索引会加快查询速度，但是也会会影响更新的速度，因为更新要维护索引数据

4、 索引列并不是越多越好，要在频繁查询的where后的条件列上创建索引

5、 小表或重复值很多的列上不建索引，要在大表以及重复值少的条件列上创建索引

6、 多个列联合索引有前缀生效特性

7、 当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引

8、 索引从工作方式区分，有主键，唯一，普通索引。

9、 索引类型会有BTREE（默认）和hash（适合做缓存（内存数据库）等）

往表中插入数据

命令语法：

insert  into <表名> [( <字段名1>[,..<字段名n> ])] values (

值1 )[,(值n)]

新建一个简单的测试表test

mysql> create table `test` (

    `id` int(4) not null auto_increment,

    `name` char(20) not null,

    primary key (`id`)

    );

Query OK, 0 rows affected (0.02 sec)

往表中插入数据的不同例子：

按规矩指定所有列明，并且每列都插入值

mysql> insert into test (id,name) values(1,'oldboy');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

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

| id | name   |

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

|  1 | oldboy |

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

1 row in set (0.00 sec)

由于设置id子增，所以可以不指定id，只插入name列，id会自动增长

mysql> insert into test(name) values('oldgirl');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

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

| id | name    |

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

|  1 | oldboy  |

|  2 | oldgirl |

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

2 rows in set (0.00 sec)

不指定插入哪列，就要按规矩为每列都插入恰当的值

mysql> insert into test values(3,'inca');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

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

| id | name    |

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

|  1 | oldboy  |

|  2 | oldgirl |

|  3 | inca    |

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

3 rows in set (0.00 sec)

 

可以批量插入，提升效率

mysql> insert into test values(4,'yangrz'),(5,'yuereer');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;

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

| id | name    |

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

|  1 | oldboy  |

|  2 | oldgirl |

|  3 | inca    |

|  4 | yangrz  |

|  5 | yuereer |

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

5 rows in set (0.00 sec)

可以批量插入，直接插入多行：

mysql> insert into `test` values (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');

Query OK, 5 rows affected (0.00 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test;

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

| id | name    |

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

|  1 | oldboy  |

|  2 | oldgirl |

|  3 | inca    |

|  4 | zuma    |

|  5 | kaka    |

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

5 rows in set (0.00 sec)

查询数据

ysql> select id,name from test where id >1 and id <5 order by id desc INTO OUTFILE '/tmp/a.txt';

Query OK, 3 rows affected (0.00 sec)

mysql>

mysql>

mysql>

mysql> system cat /tmp/a.txt

4       zuma

3       inca

2       oldgirl

order  by  按id来排序

limit  2    查看前两个

limit  1，3  从第一个数据开始，输出三个，但不包括第一个数据

INTO OUTFILE  将查询结果保存到linux文件中。

and、or都可以用