MYSQL学习笔记

最新推荐文章于 2022-02-11 18:27:08 发布

北有池鱼

最新推荐文章于 2022-02-11 18:27:08 发布

阅读量384

点赞数 1

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/u010016487/article/details/84830077

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MYSQL学习笔记

1. MYSQL安装

选择将mysql安装为Windows服务
询问是否修改默认root用户的密码，“Enable root access from remotemachines（是否允许root用户在其它的机器上登陆，如果要安全，就不要勾上，如果要方便，就勾上它）”

2. Mysql权限管理

权限管理分为两个阶段：

连接检查

服务器首先会检查你是否允许连接。因为创建用户的时候会加上主机限制，可以限制成本地、某个IP、某个IP段、以及任何地方等，只允许你从配置的指定地方登陆

权限检查

如果你能连接，Mysql会检查你发出的每个请求，看你是否有足够的权限实施它。比如你要更新某个表、或者查询某个表，Mysql会查看你对哪个表或者某个列是否有权限。再比如，你要运行某个存储过程，Mysql会检查你对存储过程是否有执行权限等

1.用户登录：

C:\Users\gl131>mysql -h localhost -uroot -padmin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.15 MySQL Community Server (GPL)

2.创建用户并授权

mysql> grant all privileges on test.* to guanlei@localhost identified by "admin";
Query OK, 0 rows affected (0.00 sec)

该语句表示：将test数据库的所有表的所有权限赋予给localhost模式下的guanlei用户

在数据库中查询结果如下：

3.修改用户密码

mysql> grant all privileges on test.* to guanlei@localhost identified by "admin";
Query OK, 0 rows affected (0.00 sec)

该语句是将用户guanlei 的密码修改为‘123456’，且password(‘123456’)有加密的作用，其在数据库中存储的是密文

4.删除用户

mysql>  delete from mysql.user where user='usertest' and host='localhost';
Query OK, 1 row affected (0.04 sec)

该语句将localhost下的usertest用户删除；

5.删除数据库及表

mysql> create database testDataBase;
Query OK, 1 row affected (0.00 sec)

mysql> use testDataBase;
Database changed

mysql> create table testTable(id int);
Query OK, 0 rows affected (0.13 sec)

mysql> drop table testTable;
Query OK, 0 rows affected (0.07 sec)

mysql> drop database testDataBase;
Query OK, 0 rows affected (0.04 sec)

在本例中，先创建了一个测试数据库和该数据库中的一个测试表，然后对数据库及数据表进行了删除操作；

6.删除账户及权限

mysql> drop user guanlei@localhost;
Query OK, 0 rows affected (0.00 sec)

该语句删除了本地的guanlei用户；

7.同理，其他的用户授权也与之类似，如：为用户增加创建视图的权限：

mysql> grant create view on test.* to guanlei@'localhost';
Query OK, 0 rows affected (0.00 sec)

8.查看自己和其他人的权限


mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for guanlei@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for guanlei@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'guanlei'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' |
| GRANT SELECT, CREATE VIEW ON `test`.* TO 'guanlei'@'localhost'                                                 |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

9.撤销授予他人的权限

mysql> revoke select on test.* from usertest@localhost;
Query OK, 0 rows affected (0.00 sec)

10.刷新数据库

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

11.保证密码安全

方法：先查出你的密码的哈希值，然后在新建用户的时候输入哈希值
那么在日志里面就只能看到哈希值

mysql> select password('mypass');
+-------------------------------------------+
| password('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> create user 'tom'@'localhost' identified by password '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
Query OK, 0 rows affected (0.00 sec)

12.忘记密码怎么办？

用SET PASSWORD命令
用mysqladmin
用UPDATE直接编辑user表

mysql> set password for guanlei@localhost =password('123');
Query OK, 0 rows affected (0.00 sec)

mysql> update mysql.user set password=password('123') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

13.修改root用户密码

关闭正在运行的MySQL服务；
打开DOS窗口，转到mysql\bin目录；
输入mysqld --skip-grant-tables 回车，–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证
再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录；
输入mysql回车，如果成功，将出现MySQL提示符 >
连接权限数据库： use mysql;
改密码：update user set password=password(“123”) where user=“root”;（别忘了最后加分号） ;
刷新权限（必须步骤）：flush privileges;
退出 quit;
注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

C:\Program Files (x86)\MySQL\MySQL Server 5.5>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Database changed
mysql> update user set password=password("admin") where user="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

3. mysql语法

3.1 mysql基本语法

SQL分为两个部分DDL和DML：

DDL是指数据库定义语言，是创建数据库或数据表时的一些操作，如CREATE、ALTER、DROP等，DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上；

DML是我们最经常用到的SELECT、UPDATE、INSERT、DELETE。主要用来对数据库的数据进行一些操作。

DDL语言:

CREATE DATABASE - 创建新数据库

ALTER DATABASE - 修改数据库

CREATE TABLE - 创建新表

ALTER TABLE - 变更（改变）数据库表

DROP TABLE - 删除表

CREATE INDEX - 创建索引（搜索键）

DROP INDEX - 删除索引

测试代码如下：

mysql> create database mysqltest;
Query OK, 1 row affected (0.04 sec)

mysql> create table users(id int(11) primary key,name varchar(50));
Query OK, 0 rows affected (0.15 sec)

mysql> alter table users add age int;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index index_age on users(age);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop index index_age on users;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table users;
Query OK, 0 rows affected (0.06 sec)

mysql> drop database mysqltest;
Query OK, 0 rows affected (0.00 sec)

DML语言

SELECT - 从数据库表中获取数据

UPDATE - 更新数据库表中的数据

DELETE - 从数据库表中删除数据

INSERT INTO - 向数据库表中插入数据

测试代码如下

首先创建库 mytest
创建表
查询、删除等操作演示

create table emp (
    empno numeric(4) not null,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate datetime,
    sal numeric(7, 2),
    comm numeric(7, 2),
    deptno numeric(2)
);
 
insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
 
create table dept (
    deptno numeric(2),
    dname varchar(14),
    loc varchar(13)
);
 
insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');
 
create table bonus (
    empno numeric(4),
    job varchar(9),
    sal numeric,
    comm numeric
);
 
create table salgrade (
    grade numeric,
    losal numeric,
    hisal numeric
);
 
insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);

查询（SELECT 列名称 FROM 表名称
）

mysql> select ename from emp;
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.00 sec)

查询不重复字段（SELECT DISTINCT 列名称 FROM 表名称
）

mysql> select distinct(mgr) from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+
7 rows in set (0.04 sec)

查询符合指定条件的字段（SELECT 列名称 FROM 表名称 WHERE 列运算符值
）

mysql> select ename from emp where emp.job='clerk';
+--------+
| ename  |
+--------+
| SMITH  |
| ADAMS  |
| JAMES  |
| MILLER |
+--------+
4 rows in set (0.00 sec)

AND 和 OR 运算符

mysql> select ename from emp where job = 'clerk' and sal > 1000;
+--------+
| ename  |
+--------+
| ADAMS  |
| MILLER |
+--------+
2 rows in set (0.06 sec)

mysql> select ename from emp where job = 'clerk' or  job ='salesman';
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| MARTIN |
| TURNER |
| ADAMS  |
| JAMES  |
| MILLER |
+--------+
8 rows in set (0.00 sec)

ORDER BY 语句

mysql> select ename,sal from emp where job = 'salesman' ORDER BY sal;
+--------+---------+
| ename  | sal     |
+--------+---------+
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
+--------+---------+
4 rows in set (0.00 sec)

INSERT INTO 表名称 VALUES (值1, 值2,…)

mysql> insert into bonus values(1001,'salesman' ,1600,10);
Query OK, 1 row affected (0.07 sec)

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

mysql> update bonus set comm = 1000 where job = 'salesman';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

DELETE FROM 表名称 WHERE 列名称 = 值

mysql> delete from bonus where job = 'salesman';
Query OK, 1 row affected (0.06 sec)

3.2 MYSQL 高级语法

TOP 子句&Limit子句（分页）(mysql没有直接的TOP函数)

mysql> select ename from emp limit 5;
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
+--------+
5 rows in set (0.00 sec)

Like操作符,SQL通配符（%， _, [charlist], [^charlist], [!charlist]）

mysql> select ename from emp where ename like '%am%';
+-------+
| ename |
+-------+
| ADAMS |
| JAMES |
+-------+
2 rows in set (0.00 sec)

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,…)

mysql> select ename,job from emp where job in ('clerk','salesman');
+--------+----------+
| ename  | job      |
+--------+----------+
| SMITH  | CLERK    |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| ADAMS  | CLERK    |
| JAMES  | CLERK    |
| MILLER | CLERK    |
+--------+----------+
8 rows in set (0.00 sec)

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

mysql> select ename,sal from emp where sal between 2000 and 3000;
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
+-------+---------+
5 rows in set (0.00 sec)

表的 SQL Alias 语法

mysql> select a.ename as name from emp a where a.sal > 4000;
+------+
| name |
+------+
| KING |
+------+
1 row in set (0.04 sec)

SQL INNER JOIN ,Join关键字

mysql> select * from emp e inner join dept d on e.deptno = d.deptno ;
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
14 rows in set (0.00 sec)

mysql> select * from emp e join dept d on e.deptno = d.deptno ;
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
14 rows in set (0.00 sec)

LeftJoin;

mysql> select * from emp e left join dept d on e.deptno = d.deptno ;
+-------+---------+-----------+------+---------------------+----------+---------+--------+--------+------------+----------+
| empno | ename   | job       | mgr  | hiredate            | sal      | comm    | deptno | deptno | dname      | loc      |
+-------+---------+-----------+------+---------------------+----------+---------+--------+--------+------------+----------+
|  7369 | SMITH   | CLERK     | 7902 | 1980-12-17 00:00:00 |   800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN   | SALESMAN  | 7698 | 1981-02-20 00:00:00 |  1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD    | SALESMAN  | 7698 | 1981-02-22 00:00:00 |  1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
|  7566 | JONES   | MANAGER   | 7839 | 1981-04-02 00:00:00 |  2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7654 | MARTIN  | SALESMAN  | 7698 | 1981-09-28 00:00:00 |  1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE   | MANAGER   | 7839 | 1981-05-01 00:00:00 |  2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7782 | CLARK   | MANAGER   | 7839 | 1981-06-09 00:00:00 |  2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7788 | SCOTT   | ANALYST   | 7566 | 1982-12-09 00:00:00 |  3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7839 | KING    | PRESIDENT | NULL | 1981-11-17 00:00:00 |  5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7844 | TURNER  | SALESMAN  | 7698 | 1981-09-08 00:00:00 |  1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS   | CLERK     | 7788 | 1983-01-12 00:00:00 |  1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES   | CLERK     | 7698 | 1981-12-03 00:00:00 |   950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7902 | FORD    | ANALYST   | 7566 | 1981-12-03 00:00:00 |  3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7934 | MILLER  | CLERK     | 7782 | 1982-01-23 00:00:00 |  1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  1111 | testemp | testemp   | 7782 | 1982-01-23 00:00:00 | 12222.00 |    0.00 |      0 |   NULL | NULL       | NULL     |
+-------+---------+-----------+------+---------------------+----------+---------+--------+--------+------------+----------+
15 rows in set (0.00 sec)

RigthJoin;

mysql> select * from emp e right join dept d on e.deptno = d.deptno ;
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  NULL | NULL   | NULL      | NULL | NULL                |    NULL |    NULL |   NULL |     40 | OPERATIONS | BOSTON   |
|  NULL | NULL   | NULL      | NULL | NULL                |    NULL |    NULL |   NULL |     50 | test       | test     |
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
16 rows in set (0.00 sec)

full join(mysql不支持，可以用left join + union + right join 实现)

mysql> select * from emp e right join dept d on e.deptno = d.deptno union ( select * from emp e left join dept d on e.deptno );

+-------+---------+-----------+------+---------------------+----------+---------+--------+--------+------------+----------+
| empno | ename   | job       | mgr  | hiredate            | sal      | comm    | deptno | deptno | dname      | loc      |
+-------+---------+-----------+------+---------------------+----------+---------+--------+--------+------------+----------+
|  7782 | CLARK   | MANAGER   | 7839 | 1981-06-09 00:00:00 |  2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
...
|  7934 | MILLER  | CLERK     | 7782 | 1982-01-23 00:00:00 |  1300.00 |    NULL |     10 |     50 | test       | test     |
|  1111 | testemp | testemp   | 7782 | 1982-01-23 00:00:00 | 12222.00 |    0.00 |      0 |   NULL | NULL       | NULL     |
+-------+---------+-----------+------+---------------------+----------+---------+--------+--------+------------+----------+
73 rows in set (0.03 sec)

SQL UNION ， Union All操作符

见 full join 操作

SELECT INTO （MySQL不支持,可以用以下语法替代）

mysql> insert into emp_bak select * from emp;
Query OK, 15 rows affected (0.09 sec)
Records: 15  Duplicates: 0  Warnings: 0

CREATE DATABASE 语句、CREATE TABLE 语句

详见本节开头的数据库和数据表创建部分-

Unique，PrimaryKey，ForeignKey，Default，Check；

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。当有外键表存在时不能删除关联表。

create table dept_bak (
    deptno numeric(2) PRIMARY KEY,
    dname varchar(14) UNIQUE,
    loc varchar(13)
);
create table emp_bak (
    empno numeric(4) not null PRIMARY KEY,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate datetime,
    sal numeric(7, 2) DEFAULT 0,
    comm numeric(7, 2),
    deptno numeric(2),
	FOREIGN KEY(deptno) REFERENCES dept_bak(deptno)
);

CreateIndex、DropIndex

详见DDL部分

CreateView

mysql> create view emp_view as select empno,ename,job,deptno from emp where job = 'salesman';
Query OK, 0 rows affected (0.07 sec)

mysql> select * from emp_view;
+-------+--------+----------+--------+
| empno | ename  | job      | deptno |
+-------+--------+----------+--------+
|  7499 | ALLEN  | SALESMAN |     30 |
|  7521 | WARD   | SALESMAN |     30 |
|  7654 | MARTIN | SALESMAN |     30 |
|  7844 | TURNER | SALESMAN |     30 |
+-------+--------+----------+--------+
4 rows in set (0.00 sec)

mysql> drop view emp_view;
Query OK, 0 rows affected (0.00 sec)

NULL判断；（NULL用Is Null , is not null）

mysql> select * from emp where comm is null;
+-------+--------+-----------+------+---------------------+---------+------+--------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+---------------------+---------+------+--------+
10 rows in set (0.00 sec)

mysql> select * from emp where comm is not null;
+-------+---------+----------+------+---------------------+----------+---------+--------+
| empno | ename   | job      | mgr  | hiredate            | sal      | comm    | deptno |
+-------+---------+----------+------+---------------------+----------+---------+--------+
|  7499 | ALLEN   | SALESMAN | 7698 | 1981-02-20 00:00:00 |  1600.00 |  300.00 |     30 |
|  7521 | WARD    | SALESMAN | 7698 | 1981-02-22 00:00:00 |  1250.00 |  500.00 |     30 |
|  7654 | MARTIN  | SALESMAN | 7698 | 1981-09-28 00:00:00 |  1250.00 | 1400.00 |     30 |
|  7844 | TURNER  | SALESMAN | 7698 | 1981-09-08 00:00:00 |  1500.00 |    0.00 |     30 |
|  1111 | testemp | testemp  | 7782 | 1982-01-23 00:00:00 | 12222.00 |    0.00 |      0 |
+-------+---------+----------+------+---------------------+----------+---------+--------+
5 rows in set (0.00 sec)

IFNULL()

mysql> select ifnull (comm,'空') from emp;
+--------------------+
| ifnull (comm,'空') |
+--------------------+
| 空                 |
| 300.00             |
| 500.00             |
| 空                 |
| 1400.00            |
| 空                 |
| 空                 |
| 空                 |
| 空                 |
| 0.00               |
| 空                 |
| 空                 |
| 空                 |
| 空                 |
| 0.00               |
+--------------------+
15 rows in set (0.00 sec)

嵌套查询

mysql> select count(1) from (select ename from emp) e;
+----------+
| count(1) |
+----------+
|       15 |
+----------+
1 row in set (0.03 sec)

-正则表达式

mysql> select * from emp where ename regexp '^s';
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.03 sec)

mysql> select * from emp where ename regexp '^[sj]|s$';
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL |     20 |
|  7876 | ADAMS | CLERK   | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL |     20 |
|  7900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+-------+-------+---------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)

4. MYSQL事务管理

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句
事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

开启事务前的表状态：

mysql> select * from bonus;
+-------+------+------+------+
| empno | job  | sal  | comm |
+-------+------+------+------+
|  1001 | 1    |    1 |    1 |
+-------+------+------+------+
1 row in set (0.00 sec)

使用事务插入两条记录，且暂时不commit;查询该表，发现新数据已经可以查看

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `mytest`.`bonus` (`empno`, `job`, `sal`, `comm`) VALUES ('1002', '10', '1', '1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `mytest`.`bonus` (`empno`, `job`, `sal`, `comm`) VALUES ('1003', '20', '1', '1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from bonus;
+-------+------+------+------+
| empno | job  | sal  | comm |
+-------+------+------+------+
|  1001 | 1    |    1 |    1 |
|  1002 | 10   |    1 |    1 |
|  1003 | 20   |    1 |    1 |
+-------+------+------+------+
3 rows in set (0.00 sec)

打开新的cmd，进入sql查询该表记录（此时，事务外的其他进程查询该表时，仍显示的是旧数据）

mysql> select * from bonus;
+-------+------+------+------+
| empno | job  | sal  | comm |
+-------+------+------+------+
|  1001 | 1    |    1 |    1 |
+-------+------+------+------+
1 row in set (0.00 sec)

在旧的cmd中，输入commit;然后切到新cmd中查询该表，发现新的数据已经可以查询了。

mysql> select * from bonus;
+-------+------+------+------+
| empno | job  | sal  | comm |
+-------+------+------+------+
|  1001 | 1    |    1 |    1 |
|  1002 | 10   |    1 |    1 |
|  1003 | 20   |    1 |    1 |
+-------+------+------+------+
3 rows in set (0.00 sec)

MYSQL 事务处理主要有两种方法：

用 BEGIN, ROLLBACK, COMMIT来实现BEGIN开始一个事务ROLLBACK事务回滚
COMMIT事务确认
直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0禁止自动提交

SET AUTOCOMMIT=1开启自动提交

4.MYSQL 临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。

实验

打开两个cmd命令行（A/B），分别连接Mysql数据库，在A中创建临时表，并查询如下：

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
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO SalesSummary
    ->     (product_name, total_sales, avg_unit_price, total_units_sold)
    ->     VALUES
    ->     ('cucumber', 100.25, 90, 2);
Query OK, 1 row affected (0.06 sec)

mysql> select * from salesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

在B中也查询A中创建的临时表，提示如下：

mysql> select * from salesSummary;
ERROR 1146 (42S02): Table 'mytest.salessummary' doesn't exist

mysql复制表

如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。如果仅仅使用CREATE TABLE ... SELECT命令，是无法实现的。
使用SHOW CREATE TABLE命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令将完全的复制数据表结构。
如果你想复制表的内容，你就可以使用INSERT INTO ... SELECT语句来实现。

实验

mysql> create table emp_bak like emp;
Query OK, 0 rows affected (0.14 sec)

mysql> select * from emp_bak;
Empty set (0.00 sec)

mysql> insert into emp_bak select * from emp;
Query OK, 15 rows affected (0.07 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> select * from emp_bak;
+-------+---------+-----------+------+---------------------+----------+---------+--------+
| empno | ename   | job       | mgr  | hiredate            | sal      | comm    | deptno |
+-------+---------+-----------+------+---------------------+----------+---------+--------+
|  7369 | SMITH   | CLERK     | 7902 | 1980-12-17 00:00:00 |   800.00 |    NULL |     20 |
|  7499 | ALLEN   | SALESMAN  | 7698 | 1981-02-20 00:00:00 |  1600.00 |  300.00 |     30 |
|  7521 | WARD    | SALESMAN  | 7698 | 1981-02-22 00:00:00 |  1250.00 |  500.00 |     30 |
|  7566 | JONES   | MANAGER   | 7839 | 1981-04-02 00:00:00 |  2975.00 |    NULL |     20 |
|  7654 | MARTIN  | SALESMAN  | 7698 | 1981-09-28 00:00:00 |  1250.00 | 1400.00 |     30 |
|  7698 | BLAKE   | MANAGER   | 7839 | 1981-05-01 00:00:00 |  2850.00 |    NULL |     30 |
|  7782 | CLARK   | MANAGER   | 7839 | 1981-06-09 00:00:00 |  2450.00 |    NULL |     10 |
|  7788 | SCOTT   | ANALYST   | 7566 | 1982-12-09 00:00:00 |  3000.00 |    NULL |     20 |
|  7839 | KING    | PRESIDENT | NULL | 1981-11-17 00:00:00 |  5000.00 |    NULL |     10 |
|  7844 | TURNER  | SALESMAN  | 7698 | 1981-09-08 00:00:00 |  1500.00 |    0.00 |     30 |
|  7876 | ADAMS   | CLERK     | 7788 | 1983-01-12 00:00:00 |  1100.00 |    NULL |     20 |
|  7900 | JAMES   | CLERK     | 7698 | 1981-12-03 00:00:00 |   950.00 |    NULL |     30 |
|  7902 | FORD    | ANALYST   | 7566 | 1981-12-03 00:00:00 |  3000.00 |    NULL |     20 |
|  7934 | MILLER  | CLERK     | 7782 | 1982-01-23 00:00:00 |  1300.00 |    NULL |     10 |
|  1111 | testemp | testemp   | 7782 | 1982-01-23 00:00:00 | 12222.00 |    0.00 |      0 |
+-------+---------+-----------+------+---------------------+----------+---------+--------+
15 rows in set (0.00 sec)

mysql元数据

查询结果信息：SELECT, UPDATE 或 DELETE语句影响的记录数。
数据库和数据表的信息：包含了数据库及数据表的结构信息。
MySQL服务器信息：包含了数据库服务器的当前状态，版本号等。
在MySQL的命令提示符中，我们可以很容易的获取以上服务器信息。

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.15    |
+-----------+
1 row in set (0.00 sec)

mysql> select Database();
+------------+
| Database() |
+------------+
| mytest     |
+------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql序列使用

MySQL序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。
AUTO_INCREMENT
LAST_INSERT_ID( )
重置序列
设置序列的开始值

mysql> CREATE TABLE insect
    ->     (
    ->     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     PRIMARY KEY (id),
    ->     name VARCHAR(30) NOT NULL, # type of insect
    ->     date DATE NOT NULL, # date collected
    ->     origin VARCHAR(30) NOT NULL # where collected
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> 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');
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

mysql> select LAST_INSERT_ID( ) from insect;
+-------------------+
| LAST_INSERT_ID( ) |
+-------------------+
|                 0 |
|                 0 |
|                 0 |
+-------------------+
3 rows in set (0.00 sec)

重置序列

如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：


mysql> ALTER TABLE insect DROP id;

Query OK, 3 rows affected (0.42 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

Query OK, 3 rows affected (0.33 sec)
Records: 3  Duplicates: 0  Warnings: 0

修改序列值:
ALTER TABLE t AUTO_INCREMENT = 100;

mysql导出语句

使用 SELECT ... INTO OUTFILE 语句导出数据
SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
输出不能是一个已存在的文件。防止文件数据被篡改。
你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

实验
将emp表的数据导出到D:/emp.txt中

mysql> SELECT * INTO OUTFILE 'D:/emp.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> FROM emp;
Query OK, 15 rows affected (0.00 sec)

SQL中的常见函数

Avg;

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2749.800000 |
+-------------+
1 row in set (0.00 sec)

Count;

mysql> select count(sal) from emp;
+------------+
| count(sal) |
+------------+
|         15 |
+------------+
1 row in set (0.00 sec)

Max，

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 12222.00 |
+----------+
1 row in set (0.00 sec)

Min，

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

Sum，

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 41247.00 |
+----------+
1 row in set (0.00 sec)

Mid，（字段， Index，Count）


mysql> select ename from emp where mid(hiredate,1,4) = '1982';
+---------+
| ename   |
+---------+
| SCOTT   |
| MILLER  |
| testemp |
+---------+
3 rows in set (0.00 sec)

Length;

mysql> select length(ename) from emp where ename ='jones';
+---------------+
| length(ename) |
+---------------+
|             5 |
+---------------+
1 row in set (0.00 sec)

Round（字段，四舍五入位数）;四舍五入；

mysql> select round(sal,3) from emp  where ename ='jones';
+--------------+
| round(sal,3) |
+--------------+
|     2975.000 |
+--------------+
1 row in set (0.00 sec)

Now;

mysql> select now() from emp where ename ='jones';
+---------------------+
| now()               |
+---------------------+
| 2018-08-12 18:38:14 |
+---------------------+
1 row in set (0.00 sec)

DATE_FORMAT;

mysql> SELECT DATE_FORMAT(Now(),'%b %d %Y %h:%i %p') as PerDate FROM  emp where ename ='jones';
+----------------------+
| PerDate              |
+----------------------+
| Aug 12 2018 06:37 PM |
+----------------------+
1 row in set (0.00 sec)

Ucase,Lcase

mysql> select ucase(ename) from emp where ename = 'jones';
+--------------+
| ucase(ename) |
+--------------+
| JONES        |
+--------------+
1 row in set (0.00 sec)

mysql> select lcase(ename) from emp where ename = 'jones';
+--------------+
| lcase(ename) |
+--------------+
| jones        |
+--------------+
1 row in set (0.00 sec)

Group By, Having;(查询部门中薪资支出之和大于10000的所有部门)

mysql> select deptno,sum(sal) from emp group by deptno having sum(sal) > 10000;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|      0 | 12222.00 |
|     20 | 10875.00 |
+--------+----------+
2 rows in set (0.00 sec)

mysql优化

查询频繁的列需要建立索引；
少用类似select * from t where xx is null（放弃索引全局扫描）
尽量避免在 where 子句中使用 != 或 <> 操作符（放弃索引全局扫描）
尽量避免在 where 子句中使用 or 来连接条件（任何一个条件列没有索引，将放弃索引全局扫描）
尽量避免使用in 和 not in ，否则会导致全表扫描
连续的数值，能用 between 就不要用 in
尽量用Exits代替In，（Exits会使用索引，In不会使用索引）
like ‘%abc%’不使用索引
尽量避免在 where 子句中对字段进行表达式操作
select id from t where num/2 = 100-> select id from t where num = 100*2
避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
select id from t where datediff(day,createdate,’2005-11-30′) = 0
select id from t where name like ‘abc%’ select id from t where createdate >= ‘2005-11-30’ and createdate < '2005-12-1‘
对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
尽量用Count（1）代替Count(*)
索引列最好不要超过6个；
尽量使用数字类型代替字符类型（数字类型只比较一次，字符类型逐个字符对比）
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
任何地方都不要使用 select * from t,用具体的字段列表代替“*”，不要返回用不到的任何字段
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
尽量避免大事务操作，提高系统并发能力。
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

mysql 备份与还原

将本文测试所用数据库备份到E:\mysql.sql下

C:\Users\gl131>mysqldump -uroot -padmin -hlocalhost mytest > E:\mysql.sql

还原数据库,还原前需要先创建一个空的数据库（本例中为mydatabase）

C:\Users\gl131>mysql -uroot -padmin mydatabase < E:\mysql.sql

mysql日志管理

1.在mysql 5.5中，查看log-bin是否开启


mysql> show variables like 'log_%';
+---------------------------------+------------------------------------------------------+
| Variable_name                   | Value                                                |
+---------------------------------+------------------------------------------------------+
| log_bin                         | OFF                                                  |
| log_bin_trust_function_creators | OFF                                                  |
| log_error                       | C:\ProgramData\MySQL\MySQL Server 5.5\Data\SGLPC.err |
| log_output                      | FILE                                                 |
| log_queries_not_using_indexes   | OFF                                                  |
| log_slave_updates               | OFF                                                  |
| log_slow_queries                | OFF                                                  |
| log_warnings                    | 1                                                    |
+---------------------------------+------------------------------------------------------+
8 rows in set (0.00 sec)

创建日志输出文件夹"C:/mysql-logs/
在my.ini中的[sqlld]中添加以下信息

log-error = "C:/mysql-logs/error.log"
log = "C:/mysql-logs/mysql.log"
log-bin="C:/mysql-logs/mysql_log_bin"

重启mysql服务

C:\WINDOWS\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

北有池鱼

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
MYSQL学习笔记

MYSQL学习笔记1. MYSQL安装2. Mysql权限管理3. mysql语法3.1 mysql基本语法3.2 MYSQL 高级语法4. MYSQL事务管理4.MYSQL 临时表mysql复制表mysql元数据mysql序列使用mysql导出语句SQL中的常见函数mysql优化mysql 备份与还原mysql日志管理1. MYSQL安装选择将mysql安装为Windows服务询问是否修...
复制链接

扫一扫

专栏目录