MySQL的常规知识
show
查看数据库
show databases;
查看表
存在的所有表
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 databasedatabase_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等属性都是复制过来了。
desc
查看表结构
desc table_name;
drop
撤销表
drop table if exists table_name;
注:drop和exists的用法是不一样的,drop是永久删除表,而delete只是删除表中的数据。
delete
删除表数据
删除表的所有数据
delete from table_name;
删除指定表中的数据
delete from table_name where column_name > value;
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 新表 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可以用大于、小于等
连接查询
语法:
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的条件。
超连接查询
语法:
内部连接: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 表 drop 属性;
撤销和更改表的主键alter table 旧表名 rename 新表名;
在第一行添加属性alter table 表 drop primary key,add primary key(属性);
alter table table_name add I [….]first;
注:
这里的first表示在第一行添加属性I
[….]用于表示列的完整性
在属性I后面添加J属性
altertable table_name add I […] after J;
注:
[…]用于表示列的完整性
删除字段I的默认值这里的I[…] after J表示在J后面添加属性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 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-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 | +----+--------+---------------------+--------+
接下来我们使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; +--------+----------+ | name | COUNT(*) | +--------+----------+ | 小丽 | 1 | | 小明 | 3 | | 小王 | 2 | +--------+----------+ 3 rows in set (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 in set (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 in set (0.01 sec)
参考文章连接:
http://www.runoob.com/mysql/mysql-tutorial.html
http://blog.csdn.net/qq_30276065/article/details/52998178