1、创建数据库
注意:创建之前判断该数据库是否存在
create database if not exists testdata;
2、删除数据库
drop database if exists testdata;
3、数据库的备份
mysqldump databasename > backup.sql (以sql文件形式存储)
4、数据库的恢复
mysqldump databasename < backup.sql (< 是恢复)
5、创建表
注意:创建表之前判断表是否存在
drop table if exists tablename;
create table tablename(
id INT,
name char(20),
address varchar(250),
primary key id
) engine=innodb,charset=utf8;
6、删除表
drop table if exists tablename;
7、表中添加一列
ALTER TABLE table_name
ADD column_name INT;
8、表中删除某一列
ALTER TABLE table_name
DROP COLUMN column_name;
9、表中添加主键
alter table table_name
add primary key (column_name);
add unique key (column_name);
primary key和unique key 区别在unique key 可以为null,并且一个表中可以有多个列设定为unique key。
10、表中创建索引
alter table table_name
add index index_alias(column_name);
// 另外一种方式
create index index_alias on table_name(column_name);
11、删除索引
alter table table_name
drop index index_alias;
12、创建视图和删除视图
视图是不存储数据,只是一组sql查询的组合。
create view alias_view as (as 是别名的关键词,不可以少)
select * from table_name;
//删除视图
drop view alias_view; (删除视图)
//更新视图
先drop 然后再create
13、基本SQL语句
插入:
insert into table_name
(filed1, field2)
values
(value1, value2);
更新字段值:
update table_name
set filed_name = values
where * (如果没有过滤条件,那么就是整表修改)
模糊查找: (like %)
like ‘%value%’;
查找:select * from table1 where field1 like ’%value1%’ (所有包含‘value1’这个模式的字符串)—like的语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count(*) as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1[separator]
12、几个高级查询运算符
UNIO和UNIO ALL
UNIO:
select Field1, Filed2,Field3 from tablename1
where 。。。
UNIO
select Field1, Filed2, Filed3 from tablename2
where 。。。
结果显示两个查询的结果取并集,不包括重复。并且要求查询的字段值需要一样的。
UNIO ALL:
去掉了重复的记录。
EXCEPT
EXCEPT ALL
INTERSECT
外链接: 左外连接(左连接)、右外连接(右连接)、全外连接
left outer join、right outer join、full outer join
13、判断数据库是否存在
if database exists database_name;
14、判断表是否存在
drop table if exists table_name;
15、判断存储过程是否存在
16、判断临时表是否存在
17、判断视图是否存在
18、判断函数是否存在
19、子查询
select *
from table_name
where fields_name in (select fiedls_name from table_name…)
//把某个查询的结果作为过滤条件,然后再进行查询。sql语句的执行都是从内到外的。
20、between and in or and 用法
between A and B : 在A 和B 之间
and: 与
or : 或
in(a,b) ; 取值为a或者b, 注意这里不是区间,是特定的两个值。
limit(m,n); 取从m行开始的n行记录。这里行0开始计算。
limit(m); 取前m行记录
21、日程安排提前五分钟提醒
22、一条sql 语句搞定数据库分页
23、随机取出10条数据
select * from table_name order by rand() limit 10;
rand(); // 随机数
24、随机选择记录
select * from table_name order by rand() ; // 所有记录随机排序
25、删除重复记录
26、初始化一张表(truncate table)
truncate table tablename;
// 作用是把原有的表删除,并且创建一个新表。
27、选择从10到15的记录
select * from table_name
limit(9, 5); //行0 开始,所以第10条记录实则是9开始。
28、数据类型转换
declare @numid int
declare @id varchar(50)
set @numid=2005
set @id=convert(varchar,@numid)
通过上述语句完成数据类型Int转换成varchar,其他转换类似,可参看convert函数
29、1=1,1=2的使用
在SQL语句组合时用的较多“where 1=1” 是表示选择全部 “where 1=2”全部不选,where 1<>1; ???
30、MySQL中实现行列倒置?
常用Mysql命令:
第一章:服务器的启动和停止
1、mysql服务的启动和停止
net start mysql ;
2、登录mysql
net stop mysql ;
3、增加新用户
第二章:操作数据库
4、显示数据库列表。
show databases;
5、显示库中的数据表
show tables;
6、显示数据表的结构
show create table table_name;
7、建库与删库
create database if not exists database_name;
drop database if exists database_name;
8、清空表中记录:
delete table
9、显示表中的记录:
select * from table_name;
10、显示表的结构
show create table tablename;
会显示创建表的sql语句。
第三章:导出和导入数据
1. 导出数据
导入数据
将文本数据导入数据库
选择你创建的数据库
use database_name;查看现在数据库中存在什么表
show tables;用文本方式将数据装入数据库表中
导入.sql文件命令
清空表
truncate table 、delete from table(加上过滤条件);删除表
drop table ;备份数据库
连接到远程主机上的MYSQL
退出MYSQL命令
exit删除某个密码为空的root用户或者匿名用户
为mysql创建一个新用户,并只赋予查看的权限
查看当前用户的命令 、当前时间、当前版本、当前数据库
当前用户:select user();
当前时间:select now();
当前版本:select version();
当前数据库:select database();创建临时表
从已经有的表中复制表的结构
复制表
create table table2 select * from table1;对表重新命名
rename table table_name2 to table_name1;
表名称改成table_name2;- 修改列的类型
alter table table_name
modify column column_name new_type;
- 修改列的类型
创建索引 、删除索引
create index index_table on table_name(column_name);联合字符或者多个列(将列id与”:”和列name和”=”连接)
select concat(id,’:’,name,’=’) from students;
limit(选出10到20条)<第一个记录集的编号是0>
select * from students order by id limit 9,10;分析索引效率
在一般的SQL语句前加上explain;- 分析结果的含义:
1)table:表名;
2)type:连接的类型,(ALL/Range/Ref)。其中ref是最理想的;
3)possible_keys:查询可以利用的索引名;
4)key:实际使用的索引;
5)key_len:索引中被使用部分的长度(字节);
6)ref:显示列名字或者”const”(不明白什么意思);
7)rows:显示MySQL认为在找到正确结果之前必须扫描的行数;
8)extra:MySQL的建议;
31、查看当前链接数
show processlist;
列出所有的数据表
show tables;
列出表中所有的索引
show index from tablename;
列出表中所有的字段
show columns from tablename;
32、创建存储过程、游标、触发器等
33、项目中常用的数据库脚本创建和执行.sql语句
高性能SQL语句的编写和调优:
Linux环境下的mysql使用。
1、外联结之左右联结:
所有的样例表:
vendors表:
vend_id
vend_name
vend_address 地址
vend_city 城市
vend_state 洲
vend_zip 邮编
vend_country
Products表:
pro_id
vend_id
prod_name
prod_price
prod_desc
customers表:
cust_id
cust_name
cust_address
cust_city
cust_state
cust_zip
cust_country
cust_contact
cust_email
orders表:
order_num
order_date
cust_id
orderitems表:
order_num
order_item
prod_id
quantity
item_price
productnotes表:
note_id
prod_id
note_date
note_text
联结:
1、自联结
使用子查询
select pro_id, prod_name
from Products
where vend_id =
( select vend_id
from Products
where pro_id = ‘DINTR’;)
select p1.pro_id, p1.prod_name
from Products as p1, Products as p2
where p1.vend_id = p2.vend_id and p2.pro_id = ‘DINTR’; (利用笛卡尔积,再使用过滤)
虽然结果一样,但是自联结的性能比子查询要高。
2、OUTER JOIN
内部联结和外部联结不同的在于,
外部联结包括了没有关联的行。
举例:
两张表:MyTable1 MyTable2
MyTable1数据:
id name
1 张三
2 李四
3 王二
4 码子
MyTable2数据:
id name
1 刘一
2 刘二
3 刘三
5 刘五
如果使用内部(inner join)联结:
语句:
select *
from MyTable1, MyTable2
where MyTable1.id = MyTable2.id;
结果: (去除了没有关联的列)
id name id name
1 张三 1 刘一
2 李四 2 刘二
3 王二 3 刘三
如果使用外部联结:
left outer join(左外联结):列出左边所有的数据
语句:
select *
from MyTable1 left outer join MyTable2
where MyTable1.id = MyTable2.id;
结果:
id name id name
1 张三 1 刘一
2 李四 2 刘二
3 王二 3 刘三
4 码子 NULL NULL
right outer join (右外联结):列出右边所有的数据
语句:
select *
from MyTable1 right outer join MyTable2
where MyTable1.id = MyTable2.id;
结果:
id name id name
1 张三 1 刘一
2 李四 2 刘二
3 王二 3 刘三
NULL NULL 5 刘五
如何随机获取表中记录?
使用rand() 函数。 和order by 可以完成随机抽取记录的功能。
原理就是order by rand() 可以随机的排序。
// 可按照随机顺序检索数据
select * from tablename order by rand();
//随机获取前n条记录
select * from tablename order by rand() limit n;