MySQL基础语句以及简单优化

用户管理：

1、新建用户：create user 'username'@'host' identified by 'password';    insert into mysql.user(Host,User,Password) values("lochost","test",possword("1234"));

2、更改密码：set password for name = password(''1234');

3、权限管理：show grants for name /查看name的用户权限

grant select on bd_name.* to name; //给name用户db_name 数据库的所有权限

revoke select on db_name.* to name; //gant的反操作，去除权限。

数据库操作：

1、查看数据库：show databases;

2、创建数据库：create databases 库名;

3、使用数据库：USE 库名;

4、删除数据库：drop database 库名；

创建表：

1、create tble 库名(

            id  tinyint unsigned not null  auto_increment, //id值，无符号、非空、递增--唯一性，可做主键。

name varchar(60) not null,

score tinyint unsigned not null default 0, //设置默认列值

          )  default charset=utf8;

2、复制表

create table tb)name2 select * from tb_name;

复制部分

create table tb_name2 select id,name  from tb_name;

3、创建临时表

create temporart table tb_name(和创建普通变一样);

4、查询数据库中可用的表

show tables;

5、查看表结构：

describe tb_name;

或 show columns in tb_name;        //from也可以

6、重命名表

rename table name_old to name_new ;

alter table name_old rename name_new;

7、删除表

drop table if exists tb_name;

修改表：

修改表结构：

alter table tb_name add[change,rename,orop]  ...要更改的内容...

实例：

alter table tb_name add column address varchar(80) not null;

alter table tb_name orop address;

alter table tb_name change score score smallint(4) not null;

插入数据：

1、插入数据：interst into ta_name(id,name,score)values(null,'张三',140),(null,'李四',178);

2、插入检索出来的数据：insert into tb_name(name,score) select name,score from tb_name2;

删除数据：

delect from tb_name where id=3;

条件控制语句：

1、where语句：

select * from tb_name where id=3;

2、having语句：

select * from tb_name group by score having count(*)>2

3、相关条件控制符

=、>、< 、<>、 in(1,2,3....)     id=1 or id=2 or id=3、between a and b a到b之间、 not  and 

linke()用法中   is null 空值检测

4、limit

select * from table limit 95,-1;//检索及记录行数96-last

select * from table limit 5,10; //检索记录行数 6-15    初始记录行的偏移量是 0(而不是 1) 所以是从第六行开始 查询十条数据。

5、distinct 

select distinct tb_id from products; //去重 

6、模糊查询   like跟通配符   两个或多个%     占位符_一个下划线代表一个位置

分组查询：

1、分组查询可以按照指定的列进行分组：

select count(*) from tb_name group by score having count(*)>1;

2、条件使用having；

3、order by 排序：

order by desc|asc ==>按数据的降序和升序排列

union规格--可以执行两个语句（可以去除重复行）

全文检索--match和against：

   1、select  match(note_text)against('picaso') from tb_name;

          2、innobd引擎不支持全文索引，myisam可以；

视图：

1、创建视图 create view name as select * from tb_name where ~~order by ~~;

      在一个或者多个表上创建视图

create [or replace] view view_name as select.......

2、视图的特殊作用：

a、简化表之间的连接（把链接写着select中）；

b、重新格式化输出检索的数据（trim、concat等函数）；

c、过滤不想要的数据（select部分）

d、使用视图计算字段值，如汇总之类的。

创建索引：

1、create index(在一个或多个列上创建索引)

create index index_name on tb_name(cloumn[asc|desc]，......);

聚合函数：

count 统计指定列下不为NULL的记录行数；

sum  计算指定列的和；

max  计算指定列的最大值；

min  计算指定列的最小值；

avg  计算指定列的平均值；

数据库优化：

1、你必须选择记录条数最少的表作为基础表.

   (from 是从前往后检索的，所以要最少记录的表放在最前面)

2、采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

同时在链接的表中能过滤的就应该先进行过滤。（where是从后往前检索，所以能过滤最多数据的条件应放到最后。）

3、SELECT子句中避免使用 ‘ * ‘

4、尽量多使用COMMIT

5、计算记录条数时候，第一快：count(索引列)，第二快：cout(*)

6、用Where子句替换HAVING子句

7、通过内部函数提高SQL效率

8、使用表的别名(Alias)

9、用EXISTS替代IN

10、用NOT EXISTS替代NOT IN

11、用表连接替换EXISTS

12、用索引提高效率

       1.添加PRIMARY KEY（主键索引）

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

mysql>ALTER TABLE `table_name` ADD UNIQUE (

`column`

)

3.添加INDEX(普通索引)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`,`column2`, `column3` )

13、避免在索引列上使用计算（此条包括在select后面 where后面等任何地方，因为在索引列上计算会导致索引失效）

14、避免在索引列上使用NOT（在索引列使用not会导致索引失效）

15、用>=替代>

16、用UNION替换OR (适用于索引列)

17、用IN来替换OR

18、避免在索引列上使用IS NULL和IS NOT NULL

19、总是使用索引的第一个列

20、用UNION-ALL 替换UNION ( 如果有可能的话)

21、ORDER BY 子句只在两种严格的条件下使用索引.

22、避免改变索引列的类型

23、需要当心的WHERE子句

24、避免使用耗费资源的操作（带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY）

单表数据量超大的写操作比较多 可以建立多个相同的连续表   
业务逻辑清晰的  需要大量读操作可以分库