MySQL 常用SQL语句

■  在DOS命令行启动MYSQL服务：

net start mysql

■在DOS命令行停止MYSQL服务：

net stop mysql

■查看被监听的端口:

netstat –na | findstr 3306

findstr用于查找后面的端口是否存在。

■创建数据库用户：只有根用户（root）才有创建新用户的权限

CREATE USER user_name1 IDENTIFIED BY ‘password’,

 user_name2 IDENTIFIED  BY ‘password’;

一次可以创建多个数据库用户

■删除数据库用户:

DROP USER user_name;

■选择用户:

select user();

■用户的权限控制：

GRANT库，表级的权限控制：将某个库中的某个表的控制权限赋予某个用户

GRANT ALL    ON db_name.table_name TO  uer_name [indentified by ‘password’];

■查看所有的字符编码:

SHOW CHARACTER SET;  

=======================================================================

■登录MySQL数据库: 在DOS命令行登录MYSQL控制台

mysql -u user_name -p(Enter,回车键入密码，若直接输入则为可显)

Enter password：*********

Mysql –h hostname –u user_name –p

Enter password:*********

例：mysql –h 192.168.5.105 –uroot –p

Enter password:*******

■查看运行环境信息:

进入MYSQL命令行工具后 , 使用status;或\s 查看运行环境信息

■创建数据库：

create database db_name；

[default]CHARACTER SET charset_name        //设置数据库的编码发方式

[default]COLLATE collation_name ;          //设置按collation_name字段排序

//不能写成utf-8，utf8的默认校对为utf8_general_ci（通过show character set查看）

CREATE DATABASE db_name CHARACTER    SET utf8 COLLATE  utf8_general_ci； 

CHARACTER SET:指定数据库采用的字符集

COLLATE:指定数据库字符集的比较方式

■使用数据库：

use db_name;           

■显示数据库：

SHOW DATABASES；

■显示数据库创建语句：

SHOW CREATE DATABASE db_name;

■删除数据库：

DROP DATABASE  db_name;

删除时可先判断是否存在，写成：DROP DATABASE IF EXISTS db_name;

■查看创建数据库的指令并查看数据库使用的编码：

show create databasedb_name;

■查看数据库编码：

showvariables like ‘char%’;

■查看数据库当前引擎：

SHOW CREATE TABLE table_name;

■修改数据库当前引擎：

ALTER TABLE table_name ENGINE=MYISAM| INNODB;( ‘|’表示‘或者’，选其一)

你能用的数据库引擎取决于mysql在安装的时候是如何被编译的。要添加一个新的引擎，就必须重新编译MYSQL。在缺省情况下，MYSQL支持三个引擎：ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEY（BDB），也常常可以使用。

■设置数据库编码：

setcharacter_set_client=gbk;//可以存中文

setcharacter_set_results=gbk;//可以看中文

■备份数据库：

MYSQLDUMP –u用户名(根用户) –p密码 db_name >  存放路径级/文件名(文件格式：.sql)

（不是在mysql控制台执行，而要退出控制台在DOS下执行）

例子：

MYSQLDUMP –u root –p******* mydb > D:/mydb.sql;

■恢复数据库：

前提：要创建一个空数据库

SOURCE 存放路径/文件名.sql (在Mysql控制台执行)

■如何将大量数据存入数据库中的表中:

首先，将数据按表的结构（字段的顺序要对应）存入文本文档中；

然后，某字段若没有值则填入NULL，注意，每个字段值之间用Tab键隔开（/r/n）。

最后，使用命令：LOAD DATA LOCAL INFILE ‘E:/Test/pet.txt’ INTO TABLE pet  LINES

               TERMINATED BY ‘\r\n’;

Terminate 结束，终止；

■创建表：

1.CREATE TABLE pet (

id int PRIMARY KEY AUTO_INCREMENT ,

name VARCHAR(20) NOT NULL UNIQUE,

owner VARCHAR(20) NOT NULL,

species VARCHAR(20),

sex CHAR(1),

birth DATE ,

);

2.CREATE TABLE table_name(

   field1  datatype,

   field2  datatype,

   ……

)CHARACTER SET 字符集 COLLATE 校对规则;

 field:指定列名    datatype:指定列类型

■显示表:

show tables; 

■显示某个表创建时的全部信息：

SHOW CREATE TABLE table_name;

■显示表的结构信息：

DESCRIBE table_name;    缩写形式 : desc table_name;

■查找数据:

SELECT * FROM table_name;

■显示表的各字段:

DESCRIBE table_name;

■清空表中的数据:

1.TRUNCATE table_name;

此方法会使表中的取号器（ID）从1开始

2.DELETE FROM table_name;
不带where参数的delete语句可以删除mysql表中所有内容，使用truncate table也可以清空mysql表中所有内容。效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。
delete的效果有点像将mysql表中所有记录一条一条删除到删完，而truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。 

注意：在MySQL中事务的特殊说明：

1.  mysql控制台是默认自动提交事务（dml）.

2.  若在控制台使用事务，应该做以下设置：

*SETAUTOCOMMIT=FALSE;    //解除自动提交功能

*SAVEPOINT point;         //设置保存点point

//一系列操作…

*ROLLBACK TOpoint;      //回滚到保存点

使用TRUNCATE table_name删除的表不能够回滚，但删除速度较快，

使用DELETE FROM table_name删除的表可以回滚，删除速度相对较慢。

■创建表:

CREATE    TABLE table_name;

■删除表:

DROP TABLE    table_name;     

■插入数据:

1.INSERT INTO table_name [(字段1，字段2，字段3，…)] VALUES (值1，值2，…);

如果向表中的每个字段都插入一个值，那么前面[]括号内字段名可写也可不写。

2.INSERT INTO pet  VALUES('Puffball','Diane','hamster','f','1999-03-30',NULL);

3.从 源表 中筛选符合条件的记录，批量插入到 (指定的)目标表 中:

insert into 目标表(字段1, 字段2,...字段n) select 字段1, 字段2,...字段n from 源表 where 条件

4.向表中插入条数据：

insert into articles (id, content,userid)

values (2,’hahaha’,11),(null,’xixixi’,10),(13,’aiaiai’,1)，…;

■删除数据：

DELETE    FROM table_name WHERE id=’10’;

delete语句不能删除某一列的值，可使用update更新列值。

使用delete语句仅删除记录，不删除表本身，删除表使用drop table语句。

■更新数据:

UPDATE pet SET birth = '1989-08-31'WHERE name = 'Bowser' ORDER    BY birth DESC;

ASC(升序，默认方式)；DESC（降序）

WHERE和 ORDER 语句也可用于查询select 与 删除delete

■查询数据:

1.SELECT * FROM pet WHERE name = 'Bowser';

SELECT * FROM pet WHERE birth > '1998-1-1';

SELECT * FROM pet WHERE species = 'dog' AND sex ='f';

SELECT * FROM pet WHERE species = 'snake' OR species= 'bird';

SELECT * FROM pet WHERE (species = 'cat' AND sex ='m')

          OR(species = 'dog' AND sex = 'f');

SELECT name, birth FROM pet;

SELECT chinese+english+math  FROM Students;    //列之间可以进行运算

2.增加关键字DISTINCT检索出每个唯一的输出记录，把相同的记录值取其中一个即可。

SELECT DISTINCT owner FROM pet; //distinct[dɪ'stɪŋkt] 截然不同的

3.使用一个WHERE子句结合行选择与列选择：

SELECT name, species, birth FROM pet

        WHEREspecies = 'dog' OR species = 'cat';

4.为了排序结果，使用ORDER BY子句:

SELECT name, birth FROM pet WHERE species = 'dog' ORDERBY birth;

默认排序是升序，最小的值在第一。要想以降序排序，在你正在排序的列名上增加DESC（降

序）关键字：

SELECT name, birth FROM pet ORDER BY birth DESC;

5.可以对多个列进行排序，并且可以按不同的方向对不同的列进行排序：

SELECT name, species, birth FROM pet

ORDER BY species, birth DESC;

注意DESC关键字仅适用于在它前面的列名（birth）；不影响species列的排列顺序。

6.MySQL提供了几个函数，可以用来计算日期，例如，计算年龄或提取日期部分：

SELECT name, birth, CURDATE(),((YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)) AS age FROM pet;

1.RENAME TABLE table_name TO new_name;

2.ALTER TABLE table_name RENAME TO new_table_name;