MySql笔记【持续更新】

MySql数据库基本指令：

数据库备份与恢复：
在实际的项目开发中我们的数据是十分的宝贵，因此需要不断的进行数据库数据的备份和恢复操作。

保存数据库具体指令如下：
以下语句（root 代表我的数据库的用户名和密码，transaction代表我创建的数据库的名称）
mysqldump -u root -p transaction>d:\transaction.sql( mysqldump -u 用户名 -p 数据库名称>保存路径\数据库名称.sql)
备份：
C:\Documents and Settings\Administrator>mysqldump -u root -p transaction>d:\transaction.sql
以上的语句用于将指定的数据库备份到指定的文件。
阅读备份文件发现以下知识点：
1．该备份中不包含创建数据库的语句
2．该备份中的插入语句使用的是简写形式化直接使用多个()插入多条记录。
恢复：
source 指定要执行的sql脚本文件
mysql -> source  d:/day02.sql


Dos窗口状态下登陆：
cmd登陆：mysql -u root -p
password: root

在MySQL中可以使用以下语句来保存我们在命令行中编写的SQL指令：tee c:\mysql.txt
MySQL指令：
数据库操作：(database)
1查看数据库：show databases;
2.查看自己创建的数据库：show create database day01;
3.创建数据库：create database day01;
4.数据库编码方式与校对规则指令：
  character set utf8 （编码方式）
  collate utf8_general_ci;（校对规则）
5.修改数据库
mysql> alter database day01
    -> character set gbk
    -> collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)

以上的语句将数据的编码方式和校对规则改为gbk。
6.删除数据库:drop database day01;
7.多学一招：在MySQL中如何查找编码支持的编码集和对应的校对规则？
方式一：查看MySQL的文档。( my-ini.txt )
方式二：show character set;
常用的编码集和校对规则：
| gbk  | GBK Simplified Chinese | gbk_chinese_ci  |  2 |
| utf8 | UTF-8 Unicode          | utf8_general_ci |  3 |

表操作：（tables）
类对应的是数据库中的表。
对象对应的数据表的中的记录。
使用数据库：use day01;

创建表：
1.mysql> create table users
    -> ( id int,
    -> name varchar(20),
    -> age int,
    -> address varchar(10) );
Query OK, 0 rows affected (0.05 sec)
注意：表格要以()开始和结尾，每一个列之间使用逗号分隔最后一个不需要。

查看表：
mysql> show tables;
+-----------------+
| Tables_in_day01 |
+-----------------+
| users           |
+-----------------+
1 row in set (0.00 sec)
以上的代码查看的是所有的表，如果需要查看指定的表使用以下的命令
mysql> show create table users;
+-------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                   
+-------+----------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(10) DEFAULT NULL,
  `birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
该语句显示的是表格的所有的定义的列字段。可以使用以下的命令查看表的信息头
mysql> desc account;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10) | YES  |     | NULL    |                |
| salary | varchar(10) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

修改表：
添加列
1．添加列
mysql> alter table users
    -> add column
    -> sex char(1);
2．修改列名
mysql> alter table users
    -> change column
    -> sex gender char(1);
以上的指令的指令也可以修改列的类型。
3．修改列的类型
mysql> alter table users
    -> modify column gender varchar(10);
以上的指令主要用于修改列的类型。
4．修改表名
mysql> alter table users rename to staff;
或
mysql> rename table users to staff;
5．删除列
mysql> alter table staff
    -> drop column id;
删除表
mysql> drop table staff;

表数据操作
在表中的数据我们一般使用最多的就是对表数据的CRUD。
准备：
mysql> create table staff(
    -> id int,
    -> name varchar(20),
    -> gender char(1),
    -> birthday date,
    -> entry_date date,
    -> job varchar(20),
    -> salary double,
    -> resume longtext
    -> )character set utf8
    -> collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
添加数据
mysql> insert into staff
    -> values
    -> (1,'jack','m','1983-12-12','2010-01-01','coder',2000,'this is a good job');
以上的语句可以在表中添加一条记录，但是每次需要给所有列进行添加比较繁琐。
如果在插入数据的时候遇到字符文本和日期类型那么值需要使用单引号引起来。
因此可以在添加记录的时候指定需要添加的列。
mysql> insert into staff
    -> (id,name,gender)
    -> values
    -> (2,'lucy','w');
Query OK, 1 row affected (0.00 sec)

mysql> select * from staff;
+------+------+--------+------------+------------+-------+--------+--------------------+
| id   | name | gender | birthday   | entry_date | job   | salary | resume             |
+------+------+--------+------------+------------+-------+--------+--------------------+
|    1 | jack | m      | 1983-12-12 | 2010-01-01 | coder |   2000 | this is a good job |
|    2 | lucy | w      | NULL       | NULL       | NULL  |   NULL | NULL               |
+------+------+--------+------------+------------+-------+--------+--------------------+
以上的语句添加了指定的列，其他的列默认值null。以后在项目中使用的时候都指定列名这样便于阅读者阅读。
中文的插入：
首先设置MySQL使用命令行默认的GBK编码进行数值的接收和显示。
set character_set_client=gbk;    -->设置接收时候使用GBK进行读取
set character_set_results=gbk;	 -->设置显示时候使用GBK进行显示
该设置只是对当前窗口有效。

修改数据：
mysql> update staff set salary=3000;
以上语句将所有的记录的工资全部修改为3000。
mysql> update staff set salary=10000 where id=3;
以上语句只能修改一列，如果需要同时修改多列，那么需要使用以下语句：
mysql> update staff set birthday='1981-01-23',entry_date='2012-10-01' where name='lucy';

查询数据(重点):
查询指定表的所有的列。
mysql> select * from staff;
+------+------+--------+------------+------------+-------+--------+--------------------+
| id   | name | gender | birthday   | entry_date | job   | salary | resume     |
+------+------+--------+------------+------------+-------+--------+--------------------+
|    1 | jack | m    | 1983-12-12 | 2010-01-01 | coder |   2000 | this is a good job |
+------+------+--------+------------+------------+-------+--------+--------------------+
以下语句指定查看的列
mysql> select id,name,gender from staff;
+------+--------+--------+
| id   | name   | gender |
+------+--------+--------+
|    1 | jack   | m    |
|    2 | lucy   | w    |
+------+--------+--------+
以下的语句给查询的列起一个别名方便查看
mysql> select id as 编号,name as 姓名,gender as 性别 from staff;
+------+--------+------+
| 编号 | 姓名 | 性别|
+------+--------+------+
|    1 | jack   | m   |
|    2 | lucy   | w   |
|    3 | 许巍   |男   |
+------+--------+------+
删除数据
mysql> delete from staff where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from staff;
+------+------+--------+------------+------------+-------+--------+--------------------+
| id   | name | gender | birthday   | entry_date | job   | salary | resume     |
+------+------+--------+------------+------------+-------+--------+--------------------+
|    1 | jack | m    | 1983-12-12 | 2010-01-01 | coder | 3000 | this is a good job |
|    2 | lucy | w    | 1981-01-23 | 2012-10-01 | NULL |3000 | NULL      |
+------+------+--------+------------+------------+-------+--------+--------------------+
以上的删除语句需要注意，如果没有指定where条件那么默认删除表中的所有记录。
举例：如果有一个表该表中有10W条记录，如果需要将表中的数据进行全部删除，那么是使用delete该是truncate？
如果使用delete那么会一条一条逐行删除。
如果使用truncate那么就直接将表结构摧毁然后重新建立表结构。
truncate  表名;

查询语句(重点)：

普通查询
查询所有的表数据
mysql> select * from student;
mysql> select name,english from student;

列计算查找
mysql> select (chinese+english+math) from student;
mysql> select name,(chinese+english+math) from student;
mysql> select name,(chinese+english+math) as 总分 from student;
mysql> select name,(chinese+english+math)+10 as 总分 from student;

过滤查询
mysql> select distinct english from student;	 过滤相同的数据
mysql> select * from student where name='李一';
mysql> select name,english from student where english>90;
mysql> select name,(chinese+english+math) from student where(chinese+english+math)>200;
查询英语成绩在78~89之间的学生的信息？
mysql> select name,english from student where english>=78 and english<=89;
mysql> select name,english from student where english between 78 and 89;
查询英语成绩是78或85的学生的信息？
mysql> select name,english from student where english=78 or english=85;
mysql> select name,english from student where english in(85,78);

模糊查找
mysql> select name,english from student where name like '李%'; 取出所有的姓李的学生
mysql> select name,english from student where name like '李_';  取出所有的姓李的且名字为两个字符的学生
mysql> select name,english from student where name like '李_ _'; 取出所有的姓李的且名字为三个字符的学生

排序查询
mysql> select name,english from student order by english;  默认升序排列
mysql> select name,english from student order by english desc;  指定以降序的方式显示
select name,(chinese+english+math) as 总分 from student
order by (chinese+english+math) desc limit 0,3;       
参数1指定起始的索引数据默认从0开始。
参数2指定显示的数据的个数，但是也隐含了结束的索引，具体在分页的时候讲解。

函数查询
mysql> select count(*) from student;   函数中传递的是列名,默认统计所有行
mysql> select count(name) from student;  如果列值为null默认不统计
mysql> select count(name) from student where math>90;
mysql> select sum(math) from student;
mysql> select sum(math)/count(math) from student;
mysql> select avg(math) from student;
mysql> select max(english) from student;

group by和having
一个原则：使用group by的命令中，只可以查询分组的组信息与sum()一类函数，如：
select address,sum(price) from orders group by address;
准备：
create table orders(
	id int,
	name varchar(20),
	address varchar(20),
	price float);
insert into orders(id,name,address,price) values(1,'电视','广州',900);
insert into orders(id,name,address,price) values(2,'洗衣机','北京',100);
insert into orders(id,name,address,price) values(3,'洗衣粉','广州',90);
insert into orders(id,name,address,price) values(4,'毛巾','北京',9);
insert into orders(id,name,address,price) values(5,'冰箱','广州',90);
将订单中的商品以产地进行分组？
mysql> select * from orders group by address;
mysql> select address,sum(price) from orders group by address;
mysql> select address,sum(price) from orders group by address having sum(price)>1000;

MySQL常用函数：
mysql> SELECT VERSION(),  	 5.5.27
mysql> select current_date();  	 2012-11-18
mysql> select sysdate();  	 2012-11-18 13:25:42
mysql> select now();    	 2012-11-18 13:28:28 

可见MySQL提供很多的函数。
注意：当你定义一个列的类型为char(1)的时候，那么你可以存入一个英文字符，
也可以存入一个中文字符。比如性别，你可以存一个m也可以存一个’男’。

多学一招：
3．char(length)和varchar(length)?
答：定义为char(10)的时候，无论你存储多长的不超过10个字节的数据，其保存的长度都是10个字节；
定义为varchar(10)的时候，当你存入一个3个字节的数据时，那其保存的长度就是3个字节。