首页 > > 网络编程 > Mysql >

【MySQL】MySQL的常规操作

2018-06-17 23:10:10来源：未知阅读 ()

MySQL的常规知识

标准的SQL语句通常可分为如下的几种类型：

1,DCL（Database Control Language） :数据控制语言，主要由grant和revoke关键字组成。

2.DDL（Database Define Language）:数据定义语言，主要由Create、Drop、Alter和Truncate关键字组成。

3,DML（Database Manipulate Language）:数据控制语法，主要由insert、update和delete关键字组成。

4,DQL（Database Query Language）:数据查询语言，主要由select关键字组成

5,TCL（Tranlasion Control Language）:事物控制语言，主要由commit、rollback和savePoint关键字组成。

show

查看数据库

show databases;

查看表

查看编码格式

show variables like 'character%';

存在的所有表

show tables;

创建表的命令

show create table table_name;

表的结构

show index from table_name;

show columns from table_name;

注：“show columns from table_name;”和“desctable_name;”效果是一样的。

show table status like "table_name"\G

注：”\G”是为了让数据显示更清晰易懂，也可以不设置。

查看服务器

服务器状态

show status;

服务器配置变量

show variables;

use

使用数据库

use database_name;

create

创建数据库

create database if not exists database_name;

创建表

创建非临时表

create table student_info(StuID int not null auto_increment,StuName varchar(15) not null,Telephone bigInt not null,primary key(StuID));

注：

1，auto_increment属性的对象只能是整形，在进行值插入时若不指定具体的值，或指定其为null或0，则其自动赋值为当前列的最大值再加上1的值。

2，primary key()是设置主键,一个表可以不止一个主键。

创建临时表

create temporary table Student_info(StuID int not null auto_increment,StuName varchar(15) not null,Telephone bigInt not null,primary key(StuID));

注：创建临时表要用关键字”temporary”，临时表主要是用于保存一些临时数据，临时表的数据只是在当前连接可用，当断开链接后就会，MySQL就会自动删除临时表。

创建复制旧表的数据和结构的新表

create table new_table select * from old_table;

注：这种方法会将旧表中的所有内容都拷贝过来，当然我们也可以用delete、insert into来删除、增加。不过这种方法最不好的地方就是没有了旧表的primary key，extra(auto_incremet)等属性。需要自己用alter手动添加。

创建复制旧表的结构的新表

　　create table new_table select * from old_table where 1 = 2;

　　　　注：这种方法只是复制表结构，当然旧表中的primary key 和auto_increment等属性都是复制过来了。

创建表时指定引擎和编码方式
　　create table new_table(id int not null,name varchar(20) not null)engine=InnoDB,default charset=UTF-8;

　　　　注：因为默认的引擎就是InnoDB,默认的编码方式就是utf-8,所以如果不改变引擎和编码方式可以不添加engine=InnoDB,default charset=utf-8

创建表的时候，指定默认的值

　　create table new_table(id int not null,name varchar(20) not null,salary int not null default 1200)engine=InnoDB,default charset=UTF8;

　　　　注：salary int not null default 1200,表示插入所有行中salary的默认值都是1200。

desc

查看表结构

desc table_name;

drop

撤销数据库

drop database databasename;

撤销表

drop table if exists table_name;

注：drop和exists的用法是不一样的，drop是永久删除表，而delete只是删除表中的数据。

delete

删除表数据

删除表的所有数据

delete from table_name;

删除指定表中的数据

delete from table_name where column_name > value;

truncate

删除表数据

删除表的所有数据

truncate table_name;

　　注：truncate和delete的区别，对于大部分数据库而言，truncate都被当成DDL处理，truncate被称为“截断”某个表-它的作用是删除该表里的全部数据，但保留数据结构。相当于DML中的delete命令，truncate的速度要快得多，而且truncate不像delete可以删除指定的记录，truncate只能删除表中的全部记录。MySQL对truncate的处理比较特殊-如果使用非InnoDB的存储引擎，truncate要比delete快；如果使用InnoDB的存储引擎，在MySQL5.0.3之前，truncate和delete完全一样，在5.0.3之后，truncate比delete要快，但如果该表被外键约束所参照，truncate又变成delete操作。在5.0.13之后，快速truncate总是可用的，即比delete的速度要快。在使用

insert

插入数据

插入完整元组

insert into Student_info values (10,'宋文',123456789);

插入非完整元组

insert into Student_info(StuName,Telephone) values ('小吴,'123456789');

注：在决定插入元组数据中的属性个数时，主要依据表中属性的完整性来决定。

复制表数据

复制旧表的数据到新表

insert into 新表 select * from 旧表

注：

这里两个表的结构要求要一样

这里只是复制数据，旧表的primary key和auto_increment还是需要自己添加。下面的方法也可以得到两个完全一样的表

方法一：首先用“creat table 新表 like 旧表”将旧表的结构复制过来，然后使用“insert into 新表 select * from 旧表”将表中的数据复制过去。

方法二：show create table旧表命令得到创建表的命令，复制粘贴再更改名称最后运行，得到一个数据结构和属性一样的新表(除名称)，再用这里insert命令，将数据复制过去，就可以得到完全一样的表了。

复制旧表的部分数据到新表

insert into 新表 (字段1，字段2......)select 字段1,,字段2,.....from 旧表

注：

1，这里两个表的匹配字段数据类型要一致。

2，这里只是复制数据，旧表的primary key和auto_increment还是需要自己添加。

insert ignore into 和 insert into 的区别

INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。INSERT IGNORE INTO当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。而REPLACE INTO into如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。

select

查看当前日期

select current_date；

查看服务器版本

select version();

查看当前数据库名称

select database();

查看当前用户名

select user();

查询表中数据的语句

查询表中指定字段无重复的数据

select distinct column_name from table_name wherecondition;

注：

1，distinct用于显示的数据内容不存在重复

2，*表示所有数据

3，condition可以用大于、小于等

查询数据之动态指定条件语句

比如有两个表，分别是table1和table2,在table1中有一个id1属性，在table2中有一个id2属性。如果要查询符合id2中的id1的数据，不需要分两句书写，可以直接将其写到一起，比如：

select table1.id1 from table1 where id1=(select table2.id2 from table2 where id2=1);

必须注意：在条件语句中的得出table2的数据只能是一个明确的数值，不能是一个数组。

连接查询

语法：

select 属性 from 表1,表2 where (表1.属性 = 表2.属性)；

注：“表1.属性 = 表2.属性” 是很重要的，它表示两个表连接的条件。

简单连接查询

select * from student_info,test_table where( StuID >2) and(StuID < 5) and (student_info.StuID = test_table.test_StuID);

注：查询Student_info表中StuID和testable表中tesStuID的值相同的所有数据，并且满足Student_info中的StuID在2到5的条件。

自连接查询

语法：

　　使用别名

例如：

打印这个表中所有的id,name，但是这个name对应的id至少有一次出现在manager_id中（如果把id看成员工编号，manager_id看成员工上司的id号，那么请打印所有的管理层）。

select distinct z.id,z.name from zilianjie z,zilianjie j where j.manager_id=z.id;

超连接查询

语法：

内部连接:select 属性1,属性2....... from 表1 inner join 表2 on 表1.属性 = 表2.属性

左连接: select属性1,属性2....... from 表1 left join 表2 on 表1.属性 = 表2.属性

右连接 : select 属性1,属性2....... from 表1 left join 表2 on 表1.属性 = 表2.属性

内部连接

inner join 形式的连接称为内部连接，也可以写为join。内部连接表示只有满足条件的记录才会显示到查询结果中

如：

select * from student_info inner join test_table on student_info.StuID = test_table.test_StuID;

这个语句等价于select * from student_info,test_table where(student_info.StuID =test_table.test_StuID);

左连接

left join 形式的连接称为左连接。查询结果包含join左侧表中的所有记录以及右侧表满足条件的记录

右连接

right join 形式的连接称为右连接。查询结果包含join右侧表中的所有记录以及左侧表中满足条件的记录

并运算查询

语法

union表示并运算

实例

将两个表中满足条件的数据显示

select StuID,StuName,Telephone from student_info where StuName = "第三个名称"

union

select test_StuID,test_Name from test_table where test_StuID= 4;

注：这里两个表需要查询出来的属性数目要一致，否则就会出现“ERROR 1222 (21000): The used SELECT statements have a different number of columns”的错误。

update

更新部分数据

update table_name set StuName = "易建联" where StuID = 1;

更新整列的值

update table_name set Telephone= Telephone + 1;

alter

更改表

在表中增加一个属性

alter table 表 add column 属性数据类型 [列的完整性];

改变表中某个属性

alter table 表 change 属性属性数据类型 [列的完整性];

alter table 表 modify 属性数据类型 [列的完整性];

注：change可以改变属性名称，但是modify只能在原来的属性上进行修改。

撤销表中的某个属性

alter table 表 drop 属性；

更改表的名称

alter table 旧表名 rename 新表名；

撤销和更改表的主键

alter table 表 drop primary key,add primary key(属性);

在第一行添加属性

alter table table_name add I [….]first;

注：

这里的first表示在第一行添加属性I

[….]用于表示列的完整性

比如：

alter table userinfo add userAge varchar(2) not null first;

在属性I后面添加J属性

alter table table_name add I […] after J;

注：

[…]用于表示列的完整性

这里的I[…] after J表示在J后面添加属性I

删除字段I的默认值

alter table table_name alter I drop default;

注：使用 ALTER命令及 DROP子句来删除字段的默认值

更改表的引擎

alter table table_name engine=engineName;

注：将数据表的引擎更改为指定的引擎。

修改表序列的起始值

alter table table_name auto_increment=value;

注：

更改表的序列起始值为value。

这里的value依据设定auto_increment的列的数据类型而定，如int则可以为整数，如varchar(4)可以为字符串

视图

创建视图

创建或替换视图语法：

create or replace view 视图名[字段名1，字段名2，.....] as 查询语句

从单个表中派生视图

如：

create view v1 as select StuID,StuName from Student_info;

从多个表中派生视图

如：

create view v2 as select * from student_info,test_table where student_info.StuID = test_table.test_StuID;

视图虚字段

如：

create view v3 as select StuName,Telephone as oritel,telephone + 1 as retel from student_info;

注：这里重新创建了一个视图，有三个属性分别为StuName、oritel、retel，其中oritel属性就是Telephone,retel属性是Telephone+1的值。因为这里的oritel和retel属性在原表中是没有的，需要通过计算而来的，所有被成为虚字段。

删除视图

删除视图语法：

drop view 视图名

如：

drop view v3;

视图的说明

MySQL中的视图是可更新的，任何对原表的更改都会反应到视图中，或者任何对视图的更改都会反应到原表上。

正则表达式

查找指定的数据

查找name字段中以'st'为开头的所有数据：

select name from table_name where name regexp'^st';

查找name字段中以'ok'为结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头且以'ok'字符串结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou].+ok$';

查找name字段中以元音字符开头或者以'ok'字符串结尾的所有数据：

SELECT name FROM person_tbl WHERE nameREGEXP '^[aeiou]|ok$';

NULL运算符

说明：

IS NULL: 当列的值是NULL,此运算符返回true。

IS NOT NULL: 当列的值不为NULL, 运算符返回true。

<=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。

例子：

比如：

select * from table where column_name is null;

注：将会显示column_name为null的所有数据，不能使用select * from table where column_name = null。

select * from table where column_nameis not null;

注：将会显示column_name不为空的所有数据，不能使用select * from table where column_name != null。

select null <=> null;

注：将会显示1。

select null <=>"NotEmpty";

注：将会显示0。

SQL注入

sql注入是什么

就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

举例

MySQL特点，执行语句的时候如果出现这样的语句将会不报错，并且继续执行，

select * from table_name where user =value;show tables;

这样回车就是两个语句，这里是会执行两个语句的，如果在表单中查询用户的信息，那么在表单输入中的用户名一栏写入，

'jame;delete from user;'

那么最后就会删除user表。

事务

一般来说，事务是必须满足4个条件（ACID）：Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1、事务的原子性：一组事务，要么成功；要么撤回。

2、稳定性：有非法数据（外键约束之类），事务撤回。

3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项决定什么时候吧事务保存到日志里。

双索引

primary key(column1,column2);

设置了双索引的话，那么满足两个数据都重复的数据不会存入，有一个数据重复则可以存储。

order by

语法：

order by 属性1,属性2,...... [asc,desc];

注：asc为升序（默认为升序）

desc为降序

当有多个属性值进行排序的时候，按照先后顺序进行排序。

如:

select * from Student_info order byStuID,StuName desc;

先按照StuID进行降序排序，再按照StuName进行升序排序。

删除某个数据库下的所有表

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')

FROM information_schema.tables

WHERE table_schema = 'mydb';

将mydb换成需要删除的数据库就可以了。

binary

binary用于可以用于区分大小写

MySQL中本来是不区分大小写的，

比如：“select* from table_name where name = "Jame";”可以搜索出table_name 表中Jame、jame、jAme等数据，

但是“select * from table where binary name ="Jame";”就只能搜索出Jame数据。

addcslashes()

addcslashes() 函数在指定的字符前添加反斜杠。

参数描述

string 必需。规定要检查的字符串。

characters 可选。规定受 addcslashes() 影响的字符或字符范围。

select coalesce(a,b,c)

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

如：

创建一个employee_tbl表：

CREATE TABLE `employee_tbl`(`id`int(11) NOT NULL,`name`char(10) NOT NULL DEFAULT '',`date` datetime NOT NULL,`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

向表中插入数据：

INSERT INTO `employee_tbl` VALUES ('1','小明','2016-04-22 15:25:33','1'),('2','小王','2016-04-20 15:25:47','3'),('3','小丽','2016-04-19 15:26:02','2'),('4','小王','2016-04-07 15:26:14','4'),('5','小明','2016-04-11 15:26:40','4'),('6','小明','2016-04-04 15:26:54','2');

查询表中数据:

mysql> SELECT * FROM employee_tbl;+----+--------+---------------------+--------+| id | name   | date                | singin |+----+--------+---------------------+--------+|1|小明|2016-04-2215:25:33|1||2|小王|2016-04-2015:25:47|3||3|小丽|2016-04-1915:26:02|2||4|小王|2016-04-0715:26:14|4||5|小明|2016-04-1115:26:40|4||6|小明|2016-04-0415:26:54|2|+----+--------+---------------------+--------+

接下来我们使用 GROUP BY 语句将数据表按名字进行分组，并统计每个人有多少条记录：

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;+--------+----------+| name   | COUNT(*)|+--------+----------+|小丽|1||小明|3||小王|2|+--------+----------+3 rows inset(0.01 sec)

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

mysql> SELECT name, SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;+--------+--------------+| name   | singin_count |+--------+--------------+|小丽|2||小明|7||小王|7|| NULL   |16|+--------+--------------+4 rows inset(0.00 sec)

如果不使用With Rollup则结果不会出现NULL字段

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

以下实例中如果名字为空我们使用总数代替：

mysql> SELECT coalesce(name,'总数'), SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;+--------------------------+--------------+| coalesce(name,'总数')| singin_count |+--------------------------+--------------+|小丽|2||小明|7||小王|7||总数|16|+--------------------------+--------------+4 rows inset(0.01 sec)