Mysql整理

在MySQL中“；”代表着结束，exit退出；

一、基础概念

1、创建新表

通过命令提示符创建表

root@host# mysql -u root -p
Enter password:*******
mysql> use STU;---选择数据库
Database changed
mysql> CREATE TABLE stu_tbl(
   -> stu_id INT NOT NULL AUTO_INCREMENT,
   -> stu_title VARCHAR(100) NOT NULL,
   -> stu_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( stu_id )--设置主键
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;---设置引擎
Query OK, 0 rows affected (0.20 sec)
mysql>

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。

2、删除MySQL数据表

DROP TABLE table_name ;

3、插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

4、查询数据

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

LIMIT 属性来设定返回的记录数。可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

5、查询数据

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
使用 AND 或者 OR 指定一个或多个条件。

6、数据更新

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

7、删除数据

DELETE FROM table_name [WHERE Clause]

8、like进行模糊查询，like常于%一起使用

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

9、union使用

通过union的使用连接两个以上select语句的结果组合到一个结果集中

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

• expression 代表检索的列，table代表表名

• DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

• ALL: 可选，返回所有结果集，包含重复数据。

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；

10、排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

desc降序，asc升序，还可以添加where...like..设置条件

11、分组

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

12、连接

OIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

13、NULL值

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

NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。、

对于Oracle数据库，一般经常对空值处理的函数为NVL，而mysql中常用到的是ifnull，这两个函数相似，其实都是由一个函数衍生而来，那就是COALESCE()函数。

14、正则表达式

 MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

查找name字段中以'st'为开头的所有数据：

mysql> SELECT name FROM tbl WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：

mysql> SELECT name FROM tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

mysql> SELECT name FROM tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

mysql> SELECT name FROM tbl WHERE name REGEXP '^[aeiou]|ok$';

修改数据表名或者修改数据表字段时，就需要可以使用MySQL ALTER命令

15、创建索引：

CREATE INDEX indexName ON mytable(username(length)); 

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

ALTER table tableName ADD INDEX indexName(columnName)--修改表结构(添加索引)

16、mysql复制表：

第一、只复制表结构到新表

create table 新表 select * from 旧表 where 1=2 或者 create table 新表 like 旧表 

ps：可以拷贝一个表中其中的一些字段:

CREATE TABLE newadmin AS
(
    SELECT username, password FROM admin
)

第二、复制表结构及数据到新表

create table 新表 select * from 旧表 

17、对于重复数据处理

统计重复的数据：

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

过滤重复的数据：（distinct）

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;

也可以使用 GROUP BY 来读取数据表中不重复的数据：

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

想删除数据表中的重复数据：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

或者在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录

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

Sql练习：

1、select * from employees where hire_date = (select max(hire_date) from employees);

#最晚入职的不止一个人，需要嵌套子查询

 

2、select * from employees order by hire_date desc limit 2,1;

#limitx,y；从第X条开始，选取Y条。LIMIT 5;检索前 5 个记录行；
#//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
#SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last