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

                                               

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值