mysql transaction 语法_MySql基础语法

一、基础语法

1.创建数据库

create database test;

show databases;

2.删除数据库

drop database test;

3.选择数据库

use test;

4.创建表

create table if not exists `test1`(

`id` int unsigned auto_increment,

`title` varchar(100) not null,

`auther` varchar(40) not null,

`date` date,

primary key (`id`)

)engine=InnoDB default charset=utf8;

show tables;

5.删除表

drop table test1;

6.插入数据

insert into test1 (title,auther,date)values("mysql","Lisa",now());

7.查询数据

select * from test1;

8.where

select auther from test1 where title="php";

9.update

update test1 set auther="ELizabeth2"where id=2;

10.delete

delete from test1 where id=3;

11.like

select * from test1 where auther like '%a';

12.union

select date from test1 union select date from test2 order by date;

#去掉重复的值

select date from test1 union all select date from test2 order by date;

#包含重复的值

13.排序

select date from test1 union all select date from test2 order by date asc; #升序

select date from test1 union all select date from test2 order by date desc; #降序

14.group by 根据一个或者多个列对结果集进行分组

select auther,count(*)from test1 group by auther;

15.连接

#内连接：

select * from test1 a inner join test2 b on a.auther=b.auther;

#左连接:获取左表所有记录，即使右表没有对应匹配的记录

select * from test1 a left join test2 b on a.auther=b.auther;

#右连接

select * from test1 a right join test2 b on a.auther=b.auther;

16.null值的处理

select * from test1 where date is null;

select * from test1 where date is not null;

# 17.正则表达式

regexp select title from test1 where auther regexp "^E";

select title from test1 where auther regexp "a$";

select title,auther from test1 where auther regexp "i";

18.事物:BEGIN 开始一个事务;ROLLBACK 事务回滚;COMMIT 事务确认；事物的四大性质：1.原子性(或称不可分割性)、2.一致性、3.隔离性(又称独立性)、4.持久性

use test;

CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;

select * from runoob_transaction_test;

begin; #开始事物

insert into runoob_transaction_test value(5);

insert into runoob_transaction_test value(6);

commit; #事物确认

select * from runoob_transaction_test;

begin;

insert into runoob_transaction_test values(7);

rollback; #回滚

select * from runoob_transaction_test;

19.ALTER

use test;

SHOW COLUMNS FROM test1;

alter table test1 drop date; #删除字段

alter table test1 add i int; #添加字段

alter table test1 modify auther varchar(50); #修改字段类型

alter table test1 change auther auther2 varchar(60); #修改字段类型并重命名

alter table test1 alter i set default 10; #设置字段默认值

alter table test1 alter i drop default; #删除字段默认值

alter table test1 rename to test3; #重命名table_name

select * from test1;

20.索引

CREATE INDEX title_index ON test3(title(10)); #创建普通索引

alter table test3 add INDEX auther_index (auther2); #添加普通索引

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX username_index (username(10))

DROP INDEX username_index ON mytable; #删除普通索引

); #在创建表格时创建索引；

# 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX username_index ON mytable(username(10)); #创建唯一索引

ALTER table mytable ADD UNIQUE username_index2 (username(10)); #修改表结构

#使用alter来添加和删除索引

ALTER TABLE mytable ADD PRIMARY KEY (id);#唯一索引

ALTER TABLE mytable ADD UNIQUE id_index (id);

ALTER TABLE mytable ADD INDEX id_index2 (id);#普通索引

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);#可能会报错

#使用alter添加和删除主键

ALTER TABLE test3 MODIFY id INT NOT NULL;

ALTER TABLE test3 ADD PRIMARY KEY (id);

ALTER TABLE test3 DROP PRIMARY KEY;

#显示索引

SHOW INDEX FROM test3 \G;

# 21.临时表(临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间)

CREATE TEMPORARY TABLE SalesSummary (

product_name VARCHAR(50) NOT NULL,

total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,

avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,

total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);

INSERT INTO SalesSummary(product_name, total_sales, avg_unit_price, total_units_sold)VALUES('cucumber', 100.25, 90, 2);

DROP TABLE SalesSummary;

22.MYSQL复制表

SHOW CREATE TABLE test3 \G; #step1:获取数据表的完整结构

# step2:修改SQL语句的数据表名，并执行SQL语句。

# step3:插入数据

insert into test4 (id,title,auther2,i) select id,title,auther2,i from test3;

#另一种方法：#拷贝表中的某些列

CREATE TABLE test5 LIKE test3; #step1

INSERT INTO test5 SELECT * FROM test3; #step2

CREATE TABLE test6 AS

(

SELECT id,title,auther2 FROM test3

);

23.MySql 元数据

# 查询MySQL服务器上面的所有数据库

$dbhost = 'localhost:3306'; // mysql服务器主机地址

$dbuser = 'root'; // mysql用户名

$dbpass = '123456'; // mysql用户名密码

$conn = mysqli_connect($dbhost, $dbuser, $dbpass);

if(! $conn )

{

die('连接失败: ' . mysqli_error($conn));

}

// 设置编码，防止中文乱码

$db_list = mysqli_query($conn, 'SHOW DATABASES');

while ($db = mysqli_fetch_object($db_list))

{

echo $db->Database . "
";

}

mysqli_close($conn);

?>

# 24.MySql序列使用 (AUTO_INCREMENT)

mysql> CREATE TABLE insect

(

id INT UNSIGNED NOT NULL AUTO_INCREMENT,

PRIMARY KEY (id),

name VARCHAR(30) NOT NULL,

date DATE NOT NULL,

origin VARCHAR(30) NOT NULL

)engine=innodb auto_increment=100 charset=utf8;

ALTER TABLE t AUTO_INCREMENT = 100;

25.MySQL 处理重复数据

# PRIMARY KEY(主键) 或者 UNIQUE(唯一)

CREATE TABLE person_tbl

(

first_name CHAR(20) NOT NULL,

last_name CHAR(20) NOT NULL,

sex CHAR(10),

PRIMARY KEY (last_name, first_name)

);

CREATE TABLE person_tbl

(

first_name CHAR(20) NOT NULL,

last_name CHAR(20) NOT NULL,

sex CHAR(10),

UNIQUE (last_name, first_name)

);

SELECT COUNT(*) as repetitions, last_name, first_name FROM person_tbl

# 统计重复数据

GROUP BY last_name, first_name HAVING repetitions > 1;

# DISTINCT过滤重复数据

SELECT DISTINCT last_name, first_name FROM person_tbl;

# GROUP BY过滤重复数据

SELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);

#通过添加 INDEX(索引) 和 PRIMAY KEY(主键)的方法来删除重复数据

ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);

26. MySql 注入

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))

{

$result = mysqli_query($conn, "SELECT * FROM users

WHERE username=$matches[0]");

}

else

{

echo "username 输入异常";

}

27. MySQL 导出数据

select * from test3 into outfile '/root/test3.txt';

#导出 CSV 格式

SELECT * FROM test3 INTO OUTFILE '/tmp/test3.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

#生成一个文件，各值用逗号隔开

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

FROM test_table;

#导出 SQL 格式的数据

mysqldump -u root -p test test3 > test.txt

#导出整个数据库

mysqldump -u root -p test > ./test1.txt

28. MySQL 导入数据

#1、mysql 命令导入

mysql -u root -p test < test.txt #导入整个数据库

#2、source 命令导入

mysql> source /root/mysql_union/test.sql;

#3、使用 LOAD DATA 导入数据

mysql> create table test7(id int);

LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE test7;

#4、使用 mysqlimport 导入数据 (不常用)

mysqlimport -u root -p --local test test.txt;

二、perl

# 1.定义

Perl 是 Practical Extraction and Report Language 的缩写，可翻译为 "实用报表提取语言"。

Perl 是高级、通用、直译式、动态的程序语言。

Perl 最初的设计者为拉里·沃尔(Larry Wall)，于1987年12月18日发表。

Perl 借用了C、sed、awk、shell脚本以及很多其他编程语言的特性。

Perl 最重要的特性是Perl内部集成了正则表达式的功能，以及巨大的第三方代码库CPAN。

# 2.hello world

#!/usr/bin/perl

print "Hello, World!\n";