1.8、基本的sql语句：

1.8.1、数据库操作

1、创建数据库：

create database数据库名称;

2、查看有哪些数据库：

show databases;

3、选择使用数据库;

use db1;

4、删除数据库：

drop database数据库名;

1.8.2、对表操作

1、创建数据库表：

use school;--选择指定的数据库，接下来的操作全部在这个库中

如下：create table表名（表属性列）；

create table student(id int(4) primary key,name char(10) not null,sex char(2),age int(3));

其中：primary key --主键

not null --不为空

default --默认值

auto_increment----自增

show tables;---查看有哪些表

desc user;---查看表结构

2、删除数据库表

drop table表名；

如：drop table user;

3、修改表

3.1修改表名：

alter table旧表名rename新表名；

alter table student rename user;

3.2修改表结构：

增加列add

alter table表名add增加的列名（数据类型、长度）；

alter table user add address char(10) not null;

3.3删除列drop

alter table表名drop删除的列名；

alter table user drop address;

3.4修改列change（注：属性不可以为空，否则会出现错误提示）

alter table表名change修改列的列名 修改后的列名 （数据类型、长度）；

alter table user change age age int(4) not null;

alter table user change sex sex2 varchar(3) not null;

3.5删除数据库表：

drop table表名；

drop table class；----删除class表格；

1.8.3、对数据库表中的数据操作

插入数据：

1、插入一条数据

insert into表名（属性列）values（值）；

以下三种方式：

a、insert into student(id,name,age) values(1,'lihui',20);

b、insert into student values(2,'康梦霞',21);

----【值的顺序和表结构没关系，只和插入数据属性列的顺序有关系；】

c、insert into student(id,name) values(1,'lihui');

2、插入多条数据

insert into student(id,name,age) values(6,'曹运普',22),(7,'洪祖厚',23);

3、更新数据：

update ..set ..

update表名set需要修改的值where语句；

update class set sex='boy' where id=2;

4、删除数据：全

delete from表名 （where语句）；---根据条件选择删除

delete from表名；----删除部内容，把数据库清空了

truncate表名；---清空数据库表=delete from表名；

如果在清空数据库表时，truncate效率更高，delete可以跟where语句，但是truncate不可以；

5、查询语句：

select * from表名；---  *代表所有列

select goods_name from ecshop；

运算符：

算数运算符+ - * /

比较运算符>、=、<、!=、>=、<=

逻辑运算符: and  or not

select * from表名where语句；

5-1、market_price和shop_price之和大于10000的商品信息，名称和价格；

select goods_name,market_price,shop_price,market_price+shop_price from ecs_goods where market_price+shop_price>10000;

select goods_name,market_price,shop_price,market_price-shop_price from ecs_goods wheremarket_price-shop_price>1000;

5-2商品的数量大于等于97的商品有哪些？

select goods_name,goods_number from ecs_goods wheregoods_number>=97;

5-3预售额大于100000商品信息

select goods_name,shop_price*goods_number from ecs_goods whereshop_price*goods_number>100000;

5-4商品数量大于97并且价格大于5000的商品信息，展示名称和价格，数量；

select goods_name,goods_number,shop_price from ecs_goods wheregoods_number>97 and shop_price>5000;

5-5商品是热销的产品或者数量小于99的商品信息 名称，热销is_hot=1，数量

select goods_number from ecs_goods wherenot goods_number=99;

select goods_number from ecs_goods wheregoods_number!=99;

Mysql表内容查询：

1、between....and....(在小数和大数之间：注意包含边界值含=)

用法：select要显示的信息1,要显示的信息2 from表名where要求的信息between...and...;

2、limit（查询行数从当前行下一行开始显示）

Limit当前所在行,所显示行数

用法：select * from表名limit当前所在行,所显示行数;

3、like（模糊查询）(注：要查询字段在第一个或者最后一个位置%可以只写一个，一般是写两个一个在前一个在后)

%匹配所有字符-匹配单个字符

用法：select * from student where Department='计算机系' andName like '张%';

或者select * from student where Department='计算机系' andName like '张__';（_有两个代表张后面有连个字符）

Mysql函数：（以下1-7都可以和where语句结合查询更为具体的条件信息）

1、Count（计数即统计）

select count(*) from score;

2、Sum（求和）

select sum (Grade) from score;

3、max（求最大值）

select max(Grade) from score;

4、min（求最小值）

select min(Grade) from score;

5、avg（求均值）

select avg(Grade) from score;

select avg(Grade) from scorewhere C_name='计算机';

6、distinct（去重）

selectdistinctshop_price from ecs_goods;

7、mod（求余可以算作运算符）

select goods_name from ecs_goods wheregoods_number mod 5=2;

8、排序：以下两个都可以和limit连用

order by（升序排序）

Order by....desc（降序排序）

selectgoods_name,shop_price from ecs_goodsorder by shop_price desc limit 0,3;

select stu_id,grade from score where c_name='中文' and grade<(select avg(grade) from score) order bygrade desc;（忘记写好几次，执行不出来）

select stu_id,grade from score  where grade<(select avg(grade) from score where c_name='计算机') order by grade desc;（where的嵌套注意（）的位置）

9、分组：注意和排序order by的区别

group by一般是先对什么进行分组，在进行统计操作

select max(grade),c_name from score group by c_name;

10、条件语句having（注意和where的区别）

select sum(grade) from score group by c_name havingsum(grade)>200;

Having是在分组之后加的条件，where是在分组之前加的条件

where......group by......having.....order(使用顺序规律)

select c_name,count(*) from score group by c_name having count(*)>=2;（求某一科目学生人数不小于2的）（注意count（*）的用法）

9、多表查询

需要建立一个连接多表连接

主键与外键

用户表user_id当成主键 外键esc_order_info  user_id

查询出那些用户存在订单信息，显示用户名跟订单号

select order_id,user_name from ecs_order_info,ecs_users where ecs_order_info.user_id=ecs_users.user_id;

查询出订单的金额，用户名，产品名称

select c1.goods_name,c1.goods_price,u1.user_name from ecs_order_goods c1,ecs_order_info c2,ecs_users u1 where c1.order_id=c2.order_id and c2.user_id=u1.user_id;

//1，from表中逗号隔开2，where条件后列出表与表的关联，多个表中and连接3，ecs_order_goods c1取表的别名

//4，展示的值全部在from前面，当多个表中存在相同的列名时，必须添加表的前缀

取表的别名

10、union合并

select * from student UNION select * from student_copy;

//把student与student_copy表的结果展示一起

//2张表中存在重复时候，去重

//2张表中存在的列数量要一致

select id,name from student UNION select name,age from student_copy UNION select stu_id,score from sc;

select * from student,sc where student.id=sc.stu_id;//多表查询只展示了2张表相同部分

11、左右连接

left join左连接

right join右连接

//左表为基准，右表的数据依附左表,左表数据全部展示，当右边存在可以相同连接时展示对应

//当左表中存在，右表中不存在数据时，右表列用null代替

//当右边存在左表不存在数据时，删掉

SELECT * from student left JOIN sc on student.id=sc.stu_id;

right join右连接//与左连接反之

SELECT * from student right JOIN sc on student.id=sc.stu_id;

12、join内连接//

只展示相同数据部分

SELECT * from student JOIN sc on student.id=sc.stu_id;

oracl full join全连接//mysql数据没有全连接

仿照全连接

SELECT * from student left JOIN sc on student.id=sc.stu_id UNION SELECT * from student RIGHT JOIN sc on student.id=sc.stu_id;

13、创建视图

create view视图名as select语句

查看创建视图的语句:show create view viewname;

删除视图：drop view视图名

create view productcustomers as select cust_name，cust_contact,prod_id from customers，orders，orderitems where customers.cust_id=orders.cust_id and orderitems.order_num=order.order_num ;

//创建了一个名为productcustomers的视图，它联结3个表，以返回订购了任意产品的所有客户列表，如果执行select * from productcustomers ,将列出订购了任意产品的客户。

为了检索出订购了产品tnt2的客户，则

select cust_name,cust_contact from productcustomers  where prod_id='tnt2';

14、备份恢复常用方法

MySQL有三种保证数据安全的方法：

l常规日志和更新日志通过保存执行的查询供你必要时恢复

l数据库备份通过导出数据或者表文件的拷贝来保护数据

l数据库复制

MySQL内部复制功能是建立在两个或两个以上服务器之间，通过设定它们之间的主-从关系来实现的。其中一个作为主服务器，其它的作为从服务器。

用SQL进行备份恢复语句

备份（在MySQL的bin目录下命令行中执行）:

mysqldump -u root -p database_name > d:db.bak

恢复（同样在mysql的bin目录下执行）:

mysql -u root -p database_name < d:db.bak

注意:在WIN下,路径用path/filename.sql是不行的,那就用pathfilename.sql

15、时间日期函数

1、获得当前日期时间函数

a、获得当前日期+时间（date+time）函数：now（）

mysql>select now();

| now() |

| 2017-05-08 22:20:46 |

b、获得当前日期+时间（date+time）函数：sysdate（）

mysql>select sysdate();

| sysdate()           |

| 2017-05-17 11:24:34 |

sysdate()日期时间函数跟now()类似，不同之处在于：now()在执行开始时值就得到了，sysdate()在函数执行时动态得到值。看下面的例子就明白了：

mysql> select now(),sleep(3),now();

| now()               | sleep(3) | now()               |

| 2017-05-17 11:20:19 |        0 | 2017-05-17 11:20:19 |

mysql> select sysdate(),sleep(3),sysdate();

| sysdate()           | sleep(3) | sysdate()           |

| 2017-05-17 11:25:46 |        0 | 2017-05-17 11:25:49 |

sysdate()日期时间函数，一般情况下很少用到。

c、获得当前时间戳函数：current_timestamp,current_timestamp();

mysql>select current_timestamp,current_timestamp();

| current_timestamp   | current_timestamp() |

| 2017-05-17 11:30:16 | 2017-05-17 11:30:16 |

16、日期转换函数、时间转换函数

a、Date/Time to Str（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)

mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |

| 20080808222301                                     |

以上例子日期、时间转换函数能够把一个日期/时间转换成各种各样的字符串格式。它是str_to_date(str,format)函数的 一个逆转换。

b、Str to Date（字符串转换为日期）函数：str_to_date(str, format)

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09

select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09

select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09

select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30

select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

c、（日期、天数）转换函数：to_days(date), from_days(days)

mysql> select to_days('2008-08-08');

| to_days('2008-08-08') |

|                733627 |

mysql> select from_days('733627');

| from_days('733627') |

| 2008-08-08          |

1row in set

d、（时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605

select sec_to_time(3605); -- '01:00:05'

e、拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31); -- '2001-01-31'

select makedate(2001,32); -- '2001-02-01'

mysql> select maketime(12,15,30);

| maketime(12,15,30) |

| 12:15:30           |

mysql> select maketime(30,70,120);

| maketime(30,70,120) |

| NULL                |输入数据不合法输出为空值

17、日期时间计算函数

a、为日期增加一个时间间隔：date_add()

set @dt = now();

select date_add(@dt, interval 1 day);

mysql> set @dt = now();

Query OK, 0 rows affected

mysql> select date_add(@dt, interval 1 day);

+-------------------------------+

| date_add(@dt, interval 1 day) |

+-------------------------------+

| 2017-05-18 14:09:13           |

+-------------------------------+

select date_add(@dt, interval 1 hour); -- add 1 hour

select date_add(@dt, interval 1 minute);

select date_add(@dt, interval 1 second);

select date_add(@dt, interval 1 microsecond);

select date_add(@dt, interval 1 week);

select date_add(@dt, interval 1 month);

select date_add(@dt, interval 1 quarter);

select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day); -- sub 1 day

b、adddate，addtime函数可以用date_add来替代，如下

mysql> set @dt = '2008-08-09 12:12:33';

mysql> select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+

| date_add(@dt, interval '01:15:30' hour_second) |

+------------------------------------------------+

| 2008-08-09 13:28:03 |

+------------------------------------------------+

mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+

| date_add(@dt, interval '1 01:15:30' day_second) |

+-------------------------------------------------+

| 2008-08-10 13:28:03 |

+-------------------------------------------------+

c、为日期减去一个时间间隔：date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+

| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |

+----------------------------------------------------------------+

| 1997-12-30 22:58:59 |

+----------------------------------------------------------------+

MySQL date_sub()日期时间函数 和date_add()用法一致，不再赘述

d、日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2)：两个日期相减date1 - date2，返回天数。

mysql> select datediff('2008-08-08', '2008-08-01');

+--------------------------------------+

| datediff('2008-08-08', '2008-08-01') |

+--------------------------------------+

|                                    7 |

+--------------------------------------+

select datediff('2008-08-01', '2008-08-08'); -- -7

MySQL timediff(time1,time2)：两个日期相减time1 - time2，返回time差值。

mysql> select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00');

+--------------------------------------------------------+

| timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00') |

+--------------------------------------------------------+

| 08:08:08                                               |

+--------------------------------------------------------+

select timediff('08:08:08', '00:00:00'); -- 08:08:08

注意：timediff(time1,time2)函数的两个参数类型必须相同。

e、 时间戳（timestamp）转换、增、减函数：

timestamp(date) -- date to timestamp

timestamp(dt,time) -- dt + time

timestampadd(unit,interval,datetime_expr) --

timestampdiff(unit,datetime_expr1,datetime_expr2) --

示例：

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00

select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01

select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00

select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00

MySQL timestampadd()函数类似于date_add()。

select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1

select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485

select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7

MySQL timestampdiff()函数就比datediff()功能强多了，datediff()只能计算两个日期（date）之间相差的天数。