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服务器上面的所有数据库
<?php
$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 . "<br />";
}
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";