MySQL学习笔记

学习资料:
菜鸟教程:http://www.runoob.com/mysql/mysql-tutorial.html


1.MySQL:
最流行的关系型数据库管理系统(RDBMS:Relational Database Management System), 特别在WEB应用方面.

2.管理

mysql --version
ps -ef | grep mysql

sudo service mysql status

#设置密码
mysql -u root -p

Enter password:

#启动/关闭mysql
cd /usr/bin
./mysqld_safe &

./mysqladmin -u root -p shutdown

#用户设置
>use mysql
>insert into user(Host,User,authentication_string,Select_priv,Insert_priv,Update_priv)
values ('localhost', 'guest', PASSWORD('guest123'), 'Y', 'Y', 'Y');
>flush privileges;
#用password()函数加密密码
#用flush privileges来重新用新用户载入

3.数据类型: http://www.runoob.com/mysql/mysql-data-types.html

4.mysql命令

1)数据库

create database <db_name>;
drop database <db_name>;
use <db_name>;

2)数据表

查看表结构
desc <table_name>;

创建表:
CREATE TABLE table_name(
   column1 datatype primary key not null,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

删除表:
drop table table_name;

向表插入数据:
INSERT INTO table_name (column1, column2....columnN)
VALUES (value1, value2....valueN);

查询表:
SELECT column1, column2, columnN FROM table_name
WHERE [condition]
[LIMIT N][OFFSET M];

修改表:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

删除表记录:
DELETE FROM table_name
WHERE [condition];
删除所有记录:

DELETE FROM table_name;

3)子句

WHERE 子句可用在SELECT, UPDATE, DELETE 语句中, 还可以使用比较或逻辑运算符指定条件.
SELECT column1, column2, columnN 
FROM table_name1, table_name2, table_nameN
WHERE [condition1] [AND/OR] [condition2]

LIKE 运算符是用来匹配通配符指定模式的文本值.
% 代表零个、一个或多个数字或字符.
SELECT column_list 
FROM table_name
WHERE column LIKE '%XXXX%'

ORDER BY 子句是用来基于一个或多个列按升序asc或降序desc顺序排列数据。
SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。
在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。
SELECT NAME, SUM(SALARY) 
FROM COMPANY 
GROUP BY NAME 
ORDER BY SALARY;

UNION 用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。选distinct(默认)则多个 SELECT 语句会删除重复的数据。
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

JION 用于在两个或多个表中查询数据。
http://www.runoob.com/mysql/mysql-join.html

4)NULL处理:

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

用=或!=查找null时永远返回false或找不到.

5)正则表达式:http://www.runoob.com/mysql/mysql-regexp.html

6)事务:
 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

begin/start transaction
commit/commit work
rollback/rollback work
savepoint <identifier> 存档点
release savepoint <identifier>
rollback to <identifier>
set transaction  用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

7)修改表结构(表名/表字段名):

//删除字段

ALTER TABLE testalter_tbl  DROP i;

//添加字段
ALTER TABLE testalter_tbl ADD i INT;
ALTER TABLE testalter_tbl ADD i INT FIRST;

ALTER TABLE testalter_tbl ADD i INT AFTER c;

//修改字段
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
ALTER TABLE testalter_tbl CHANGE i j BIGINT;

ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;

//修改字段默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

//修改数据表类型

ALTER TABLE testalter_tbl ENGINE = MYISAM;

//修改表名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;

8)索引:http://www.runoob.com/mysql/mysql-index.html


9)临时表:http://www.runoob.com/mysql/mysql-temporary-tables.html


10)复制表:

方法1:

//复制表结构及数据到新表

create table processing_files_3333 select * from processing_files;

方法2:

//只复制表结构到新表

create table processing_files_4444 like processing_files;

//拷贝表数据

insert into processing_files_4444 select * from processing_files;

方法3:

//获取数据表的完整结构。

SHOW CREATE TABLE runoob_tbl;

//修改SQL语句的数据表名，并执行SQL语句。
mysql> CREATE TABLE `clone_tbl` (
  -> `runoob_id` int(11) NOT NULL auto_increment,
  -> `runoob_title` varchar(100) NOT NULL default '',
  -> `runoob_author` varchar(40) NOT NULL default '',
  -> `submission_date` date default NULL,
  -> PRIMARY KEY  (`runoob_id`),
  -> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)

-> ) ENGINE=InnoDB;

//拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。
mysql> INSERT INTO clone_tbl (runoob_id,
    ->                        runoob_title,
    ->                        runoob_author,
    ->                        submission_date)
    -> SELECT runoob_id,runoob_title,
    ->        runoob_author,submission_date
    -> FROM runoob_tbl;

11)自增序列:

//用AUTO_INCREMENT实现自增
mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
);

重置序列:
如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

设置序列开始值:

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
)engine=innodb auto_increment=100 charset=utf8;
//或创建表以后:
mysql> ALTER TABLE t AUTO_INCREMENT = 100;

12)重复数据处理:

 你可以在MySQL数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。 

防止重复数据出现: 

设置指定字段为primary key/unique, 以保证唯一性.

CREATE TABLE person_tbl

(

   first_name CHAR(20) NOT NULL,

   last_name CHAR(20) NOT NULL,

   sex CHAR(10),

   UNIQUE/PRIMARY KEY (last_name, first_name)

);

为防止插入重复数据时出错, 

1)可用insert ignore into, 在插入重复数据时跳过且不报错.

2)可用replace into, 在插入重复数据时, 会替换原有数据.

统计重复数据:

select count(*) as repetitions, last_name, first_name
from person_tbl
group by last_name, first_name
having repetitions>1;

过滤重复数据

select distinct last_name, first_name
from person_tbl;

删除重复数据

create table tmp select last_name, first_name, sex from person_tbl group by (last_name, first_name, sex);
drop table person_tbl;
alter table tmp rename to person_tbl;

13)MySQL导出:

方法一:

>select * from <table_name> into outfile '/tmp/outfile.txt'

>fields terminated by ',' optionally enclosed by '"'

>lines terminated by '\n';

方法二:

$mysqldump -u root -p --no-create-info --tab=/tmp RUNOOB runoob_tbl

方法三:

$mysqldump -u root -p <database_name> <table_name> > dump.txt

$mysqldump -u root -p <database_name> > dump.txt

$mysqldump -u root -p --all-databases > dump.txt

14)MySQL导入:
方法一:

>load data local infile 'dump.txt' into table mytbl

>fields terminated by ':'

>lines terminated by '\r\n';

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下： 

>load data local infile 'dump.txt'

>into table mytbl (b,c,a);

方法二:

$mysqlimport -u root -p --local database_name dump.txt

//设置指定格式

$ mysqlimport -u root -p --local --fields-terminated-by=":" \

   --lines-terminated-by="\r\n"  database_name dump.txt

//设置列顺序

$ mysqlimport -u root -p --local --columns=b,c,a \

    database_name dump.txt