MySQL基础命令（自练）

创建数据库
CREAT DATABASE TP;

DROP命令删除数据库
DROP DATABASE TP;

选择数据库
mysql -u root - p
mysql> use TP;

创建数据表
CREATE TABLE IF NOT EXISTS ‘tp_001’(
‘tp_id’ INT UNSIGNED AUTO_INCREMENT,
‘tp_name’ VARCHAR(200) NOT NULL,
‘tp_author’ VARCHAR(50) NOT NULL,
‘submission_date’ DATE,
PRIMARITY KEY(‘tp_id’)
)ENGING=InnoDB DEFAULT CHARSET=utf8;

删除数据表
DROP TABLE tp_001;

插入数据
INSERT INTO tp_001(tp_id,tp_name)
VALUES
(‘001’,‘kate’);

查询数据
SELECT * FROM tp_001 WHERE LIMIT 1,2;

where子句
SELECT * FROM tp_001 WHERE tp_id=1;

UPDATE更新
UPDATE tp_001 SET tp_name=‘手机’ WHERE tp_id=2;

DELETE语句
DELETE FROM tp_001 WHERE tp_id=3;

LIKE语句(模糊搜索)
SELECT * FROM tp_001 WHERE tp_name LIKE ‘*菲’;

UNION操作符
SELECT tp_name FROM tp_001 UNION SELECT tp_name FROM tp_002 ORDER BY tp_name;

排序(ORDER BY)
SELECT * FROM tp_001 ORDER BY submission_date des;

多表连接
内连接：SELECT s.tp_id,m.ap_num FROM tp_001 s INNER JOIN mp_001 m ON s.tp_id=m.ap_num;
左连接：SELECT s.tp_id,m.ap_num FROM tp_001 s LEFT JOIN mp_001 m ON s.tp_id=m.ap_num;
右连接：SELECT s.tp_id,m.ap_num FROM tp_001 s RIGHT JOIN mp_001 m ON s.tp_id=m.ap_nim;

NULL值处理
SELECT * FROM tp_001 WHERE tp_name=NULL;
SELECT * FROM tp_001 WHERE tp_id IS NOT NULL;

正则表达式
查找name字段中以’st’为开头的所有数据：
SELECT name FROM tp_001 WHERE name REGEXP ‘^st’;

查找name字段中以’ok’为结尾的所有数据：
SELECT name FROM tp_001 WHERE name REGEXP ‘$ok’;

查找name字段中包含’mar’字符串的所有数据：
SELECT name FROM tp_001 WHERE name REGEXP ‘mar’;

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：
SELECT name FROM tp_001 WHERE name REGEXP ‘¹|ok$’;

MYSQL事务
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1开启自动提交

ALTER命令
删除字段 ALTER tp_001 DROP i;
添加字段 ALTER tp_001 ADD i INT FIRST;
修改字段名称 ALTER TABLE tp_001 MODIFY;
修改字段默认值 ALTER TABLE tp_001 ALTER i SET DEFAULT 1000;
修改表名 ALTER TABLE tp_001 RENAME TO tp_1;

普通索引
创建索引
CREATE INDEX indexName ON tp_001(name(100));

添加索引
ALTER table tp_001 ADD INDEX indexName(columName);

创建表的时候直接指定
CREAE TABEL tp_001(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);

删除索引
DROP INDEX [indexName] ON tp_001;

唯一索引
创建索引
CREATE UNIQUE INDEX indexName ON tp_001(username(length));

修改表结构
ALTER table tp_001 ADD UNIQUE [indexName] (username(length));

创建表的时候直接指定
CREATE TABLE tp_001(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);

ALTER 命令添加和删除索引
ALTER TABLE tp_001 ADD PRIMARY KEY(column_list)
ALTER TABLE tp_001 ADD UNIQUE index_name(column_list)
ALTER TABLE tp_001 ADD INDEX index_name(column_list)
ALTER TABLE tp_001 ADD FULLTEXT index_name(column_list)

ALTER TABLE tp_001 ADD INDEX ©;
ALTER TABLE tp_001 DROP INDEX ©;

ALTER 命令添加和删除主键
ALTER TABLE tp_001 MODIFY i INT NOT NULL;
ALTER TABLE tp_001 ADD PRIMARY KEY (i);
ALTER TABLE tp_001 DROP PRIMARY KEY;

显示索引
SHOW INDEX FROM tp_001;

临时表
CREATE TEMPORARY TABLE SaleSummary (
produce_name VARCHAR(50) 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,totle_sales,avg_unit_price,total_units_sold)
VALUES
(‘cucumber’,100.25,90,2);
DROP TABLE SalesSummary;

复制表
CREATE TABLE ‘nowcoder_tbl’ (
‘nowcoder_id’ int(11) NOT NULL AUTO_INCREMENT,
‘nowcoder_title’ varchar(100) NOT NULL,
‘nowcoder_author’ varchar(40) NOT NULL,
‘submission_date’ date DEFAULT NULL,
PRIMARY KEY (‘nowcoder_id’)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

create table clone_tbl (
nowcoder_id int(11) not null auto_increment,
nowcoder_title varchar(100) not null default ‘’,
nowcoder_author varchar(40) not null defautl ‘’,
submission_date date default null,
primary key (nowcoder_id)
) engine=innodb;

insert into clone_tbl
(nowcoder_id,nowcoder_title,nowcoder_author,submission_date)
select
nowcoder_id,nowcoder_title,nowcoder_author,submission_date
from nowcoder_tbl;

序列使用

使用AUTO_INCREMENT
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
);
INSERT INTO insect (id,name,date,origin) VALUES
(NULL,‘housefly’,‘2001-09-10’,‘kitchen’),
(NULL,‘millipede’,‘2001-09-10’,‘driveway’),
(NULL,‘grasshopper’,‘2001-09-10’,‘front yard’);

充值序列
ALTER TABLE insect DROP id;
ALTER TABLE insect
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

设置序列的开始值
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=innobd auto_increment=100 charset=utf8;

创建表后设置序列的开始值
ALTER TABLE t AUTO_INCREMENT = 100;

处理重复数据
设置数据唯一性
(INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。)
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)
)

INSERT IGNORE INTO person_tbl (last_name,first)
VALUES(‘Jay’,‘Thomas’);

添加UNIQUE索引设置唯一性
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 repetitons > 1;

过滤重复数据
SELECT DISTINCT last_name,first_name
FROM person_tbl;
或
SELECT last_name,first_name
FROM person_tbl
GROUP BY (last_name,first_name);

删除重复数据
CREATE TABLE tmp SELECT last_time,first_name,sex FROM person_tbl GROUP BY (last_name,first_name,sex);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;
或在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录
ALTER IGNORE TABLE person_tbl
ADD PRIMARY KEY (last_name,first_name);

导出数据
将数据表 nowcoder_tbl 数据导出到 /tmp/nowcoder.txt 文件中:
SELECT * FROM nowcoder_tbl
INTO OUTFILE ‘/tmp/nowcoder.txt’;

通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：
SELECT * FROM passwd INTO OUTFILE ‘/tmp/nowcoder.txt’
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\r\n’;

各值用逗号隔开
SELECT a,b,a+b INTO OUTFILE ‘/tmp/result.text’
FIELD TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\n’
FROM test_table;

导出表作为原始数据
mysqldump -u root -p

导出SQL格式的数据
mysqldump -u root -p NOWCODER nowcoder_tbl > dump.txt

CREATE TABLE nowcoder_tbl (
nowcoder_id int(11) NOT NULL auto_increment,
nowcoder_title varchar(100) NOT NULL DEFAULT ‘’,
nowcoder_author varchar(40) NOT NULL DEFAULT ‘’,
submission_date date default NULL,
PRIMART KEY (nowcoder_id)
);

INSERT INTO nowcoder_tbl
VALUES (1,‘Learn PHP’,‘John Poul’,‘2007-05-24’);
INSERT INTO nowcoder_tbl
VALUES (2,‘Learn MySQL’,‘Abdul S’,‘2007-05-24’);
INSERT INTO nowcoder_tbl
VALUES (3,‘JAVA Tutorial’,‘Sanjay’,‘2007-05-06’);

导出整个数据库的数据，可以使用以下命令：
mysqldump -u root -p NOWCODER > database_dump.txt

备份所有数据库，可以使用以下命令：
mysqldump -u root -p

将数据拷贝至其他的 MySQL 服务器上:
mysqldump -u root -p database_name table_name > dump.txt

将备份的数据库导入到MySQL服务器中:
mysql -u root -p database_name < dump.txt

将导出的数据直接导入到远程的服务器上:
mysqldump -u root -p database_name
|mysql -h other-host.com database_name

导入数据
mysql命令导入：mysql -uroot -p123456 < nowcoder.sql

source命令导入：
create database abc;
use abc;
set names utf8;
source /home/abc/abc.sql

LOAD DATA导入：
LOAD DATA LOCAL INFILE ‘dump.txt’ INTO TABLE mytbl;
FIELDS TERMINATED BY ‘:’
LINES TERMINATED BY ‘\r\n’;

在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：
LOAD DATA LOCAL INFILE ‘dump.txt’
INTO TABLE mytbl (b,c,a);

---

1. aeiou ↩︎