mysql基础语句

  mysql基础：

                            打开数据库：net start mysql

                            关闭数据库：net stop mysql

登录数据库

mysql -uroot -proot;

                                     1）管理数据库：

                                               增： create database 数据库名 defaultcharacter set 字符集;

                                               修： alter database 数据库名 defaultcharacter set 新的字符集;

                                               删： drop database 数据库名;

                                               查： show databases;

查看常用的字符串集的校验规则：

                                                                 showcharacter set;

                                     2）管理表：

                                               增： create table 表名(字段名1 字段类型,字段名2 字段类型......);

                                               修：

                                                        增加字段： alter table 表名 add column 字段名字段类型;

                                                        修改字段类型： alter table 表名 modify column字段名 新的字段类型;

                                                        修改字段名： alter table 表名 change column旧字段名 新的字段名 字段类型;

                                                        修改表名： alter table 表名 rename to 新表名;

                                               删： drop table 表名;

                                               查： show tables;         desc 表名;

                                     3)管理数据：

                                               增： insert into 表名(字段名1,字段名2....) values(值1，值2.....);

                                               修： update 表名 set 字段名1=值1,字段名2=值2...... where 条件;

                                               删： delete from 表名 where 条件;

                                                         truncate table 表名;

                                              查： (12中查询)

                                                        a）所有字段：select * from 表名;

                                                        b）指定字段： select 字段名1,字段名2 from 表名;

                                                        c）指定别名：select 字段名1 as 别名1,字段名2 as 别名2  from 表名;

                                                        d）添加常量列： select 常量值 as 别名 from 表名;

                                                        e）合并列： select (字段名1+字段名2+....) as 别名 from 表名;

                                                        f）去除重复： select distinct 字段名 from 表名;

                                                        g）条件查询：

                                                                  逻辑条件： where 条件1 and/or 条件2;

                                                                  比较条件： where 字段名 >/>=/</<=/=/<> 值

                                                                                             where字段名 between 值1 and 值2;

                                                                 判空条件：

                                                                           null;   where 字段名 is null/is not null;

                                                                     空字符串：  where 字段名=''/<>''

                                                                 模糊条件： where 字段名　like 值

                                                                                    %：代表任意个字符

                                                                                    _: 代表一个字符

                                                        h）聚合查询：

                                                                  max(): 最大值

                                                                  min（）：最小值

                                                                  avg（）:平均值

                                                                  count(): 统计数量

                                                        i）分页查询：

                                                                  limit 起始行，查询行数

                                                        j）排序：

                                                                           orderby 字段名 asc/desc

                                                                 asc:升序                 

                                                                 desc:降序

                                                        h）分组查询：

                                                                           groupby 字段名

                                                        k）分组后筛选：

                                                                           groupby 字段名 having 条件;

                

                            sql语句分类：

                                               1）数据定义语句（DDL）：

                                                                 create/alter/drop

                                               2）数据操作语句（DML）

                                                                 insert/update/delete/truncate

                                               3）数据查询语句（DQL）

                                                                 select/show