MYSQL数据库语句之单表操作(二)

电玩女神 2022-07-18 05:25 227阅读 0赞

## MYSQL数据库语句之单表操作(二) ##

### 针对数据库的操作 ###

**SQL语句不区分大小写**

1.连接数据库

mysql -h主机地址 -u 用户名 -p 密码;
    
    注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。
    
    如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是： mysql>

2.退出mysql

exit;

3.创建数据库

create database my;//my是数据库名称，注意SQL语句以分号结束
    
    create database if not exists my;//my是数据库名称,如果my数据库不存在就创建。
    
    二者的区别：前者如果将要创建的数据库存在，那么会显示创建错误，提示已经存在；后者不管是否存在都会显示成功。
    
    create database my character set gbk;//创建一个带gbk编码格式的my数据库
    
    create database my character set gbk collate gbk_chines e_ci;//创建数据库my,字符集gbk,校验规则gbk_chinese_ic;

4.查看数据库

show databases;//查看所有的数据库
    
    show create database my;//查看my数据库的细节(数据库编码格式等)  
    
    show collation like 'gbk%';//查看gbk校验规则
    
    select database();//查看当前使用的数据库

5.删除数据库

drop database my;//my是数据库名称

6.修改数据库的字符集

alter database my character set utf8;//将my数据库的字符集改为utf8.

7.切换使用数据库

user my;//将当前正在使用的数据库切换至my数据库

### 表的操作 ###

在创建表之前，需要指明数据库。  
一般会用到这2个命令。

select database();//查看当前的数据库
    
    use 数据库名称；//切换到该数据库

1.创建表

create table tb_name（ 列名1 类型1， 列名2 类型2， 列名3 类型3 ... );
    [注:]tb_name为表名，在最后一个类名不需要‘,’逗号（细节决定成败）。
    
    eg:
    create table tb_name( id int auto_increment, name varchar(30), age int, primary key(id) );
    
    [注：]在创建表后可以添加一些信息，如
    eg:
    create table tb_name1( id int auto_increment, name varchar(30), age int, primary key(id) )ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.删除表

drop table tb_name;
    
    drop table if exists tb_name;//如果存在tb_name就删除

3.重命名表名

alter table tb_name rename to new_tb_name; //tb_name是当前表，new_tb_name是新命名的表

4.查看某数据库的全部表名

select table_name from information_schema.tables where table_schema='数据库名';

5.查看表结构

desc tb_name;
    
    show columns form tb_name;

6.给表取别名

select tn.name,tn.age from tb_name as tn;//tn就是别名，tn.namej就是通过别名获取这个字段。常用于多表查询。

6.增加表中列/字段

alter table tb_name add 列名 数据类型 default '默认值';
    
    eg:
    alter table tb_name add sex boolean default '0';//boolean会转成tinyint(1),默认0
    [注：]boolean也可以写成bool，都是一样转成tinyint(1)类型

7.删除表中的字段

alter table tb_name drop age;//删除tb_name表中的age字段

8.修改字段及其类型

alter table 表名 change 原字段名 新字段名 数据类型;
    
    eg：
    alter table tb_name change name u_name varchar(30);//将tb_name表中的name字段改为u_name字段

9.alter table 对null值和默认值的影响

alter table tb_name modify age bigint not null default 100;//修改age数据类型为bigint，且不为null,默认值为100。

10.修改字段默认值

alter table tb_name alter age set default 100;//设置age的默认值为100。

--------------------

### 表的增删改查操作 ###

1.插入操作

insert语法 INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
    
    //防止插入重复的数据                       
    INSERT IGNORE INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
    
    insert into tb_name(filed1,filed2,...filedN) values(value1,value2,...valuen);//tb_name为表名，字符型数据需要使用单引号或双引号，如：‘value’或“value”
    
    eg:
    insert into tb_name (id,name,age) values (1,'zhangsan',29);
    insert into tb_name (name,age) values ("lisi",30);//id自增，所以可以不用指定
    insert into tb_name (name,age) values ("xiaoming",16);
    insert into tb_name (name,age) values ("xiaohua",18);
    insert into tb_name (name,age) values ("xiaoxiao",14);
    
    //表后边没有(filed...),在values后面需要将表所有的字段都写出来
    insert into tb_name values(11,'liubei',36);
    insert into tb_name values(12,'guanyu',35);
    insert into tb_name values(13,'zhangfei',34);

2.更新操作

更新操作语法：
    
    UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] eg: update tb_name set name='laowang',age=50 where id=2;
    update tb_name set name='daxia',age=25 where age>25;
    
    一般更新都会通过where语句指定范围。

3.删除操作

删除的语法：
    
    DELETE FROM table_name [WHERE Clause] eg: delete from tb_name;//删除tb_name表中所有的数据。
    delete from tb_name where id=1;
    delete from tb_name where name="zhangsan";
    delete from tb_name where age>100;

4.查询操作(在查询过程中，尽量过滤缩小查询范围)

select语法： SELECT column_name,column_name FROM table_name [WHERE Clause] 过滤重复的数据 select distinct name from tb_name;//过滤同名的数据。distinct一般过滤某字段中重复的数据。
    
    
    常用的查询语句：
    eg:
    select * from tb_name;//tb_name为表名，查询整张表的数据（*表示所有字段）
    select name,age from tb_name;//查询tb_name中的name，age字段。

![image][]

--------------------

### 查询相关的子句及其他 ###

1.where子句查询

where子句查询语法
    
    SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2..... where子句支持操作符：and,or,=,<>,!=,>=,<=,>,<,in(),is null,between...and...,。 and:表示且，or表示或。 eg: select * from tb_name where id=1;
    select * from tb_name where age>=14 && age <=20;
    select * from tb_name where name='xiaohua';
    select * from tb_name where age>=14 && age<=20 and name="xiaohua";
    select * from tb_name where age>=14 && age<=20 or name="laowang";
    
    select * from tb_name where age between 14 and 20;//查询年龄14到20之间的数据。

2.like子句

like语法
    
    SELECT field1, field2,...fieldN table_name1, table_name2... WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue' like: eg: select * from tb_name where name like 'xiaoxiao';//这种用法和name = 'xiaoxiao'一样;
    select * from tb_name where name like 'xiao%';//查询name中所有有xiao...这个的字段，%为通配符
    select * from tb_name where name like '%xiao';//查询name中所有有...xiao这个的字段
    select * from tb_name where name like '%xia%';//查询name中所有有...xia...这个的字段
    select * from tb_name where name like '%xia%' and age>=18;//可以和其他操作符联合使用
    
    select * from tb_name where name like 'xiao___';//'_'表示通配符，但只表示占一位,'%'也是通配符，表示占0个或者多个位。

1.  as:给表的字段取别名

select id as ID,name as NAME,age as AGE from tb_name;

![image][image 1]

4.in查询

select * from tb_name where age in(16,18,20);//查询 tb_name表中age字段中16，18，20的数据等同于下面语句。
    select * from tb_name where age = 16 or age=18 or age=20;

5.关于null的过滤

select * from tb_name where name is null;//查询tb_name中name字段为null的数据，不能用name=null，这样查询不到结果。

6.在列上进行计算后查询

select name ,age+10 from tb_name;//查询tb_name中数据，在age字段数据+10操作。

### order by排序 ###

order by语法：
    
    SELECT field1, field2,...fieldN table_name1, table_name2...
    ORDER BY field1, [field2...] [ASC [DESC]]
    
    order by:升序和降序，默认升序，
    asc:表示升序排列
    desc：表示降序排列
    
    eg:
    select * from tb_name order by age asc;//升序排列
    select * from tb_name order by age;//默认为升序
    select * from tb_name order by age desc;//降序排列
    select * from tb_name where name like '%xia%' order by age desc;//查询所有xia字段，年龄降序排列
    
    select * from tb_name order by age deac , name asc;//先按年龄降序排列，再按名字升序排列。

### group by分组查询 ###

***group by查询数据是基于下面这张表的数据进行的***

![image][image 2]

*  group by基本用法

group by语法：
    
    SELECT column_name, function(column_name)
    FROM table_name
    WHERE column_name operator value
    GROUP BY column_name;
    
    group by:根据一个或多个列对结果集进行分组。
    在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
    
    
    表前面的列名，如：name，表示按这列来分组，group by后面的列名是按这列来排序，默认升序。
    eg:
    select name from tb_name group by name;

![image][image 3]

*  group\_concat()函数

group_concat(列名)
    
    select name,group_concat(age) from tb_name GROUP BY name;//以name分组，获取name对应的age
    select age,group_concat(name) from tb_name GROUP BY name desc;//以age分组，获取age对应的name,以name列降序排列。
    
    应用场景:如：查询生日相同的人都有谁等

![image][image 4]

*  coalesce()函数

coalesce
    
    select coalesce(a,b,c);
    参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
    
    select coalesce(name,'总数'), sum(age) as age_sum  from tb_name group by name;//没有什么用似得

![image][image 5]

### mysql常用函数 ###

*  count()函数

count函数
    
    select count(*) from tb_name;//表中数据数量

*  sum()函数

sum(列名)：求和的结果作为age_sum列值
    
    select sum(age) as age_sum from tb_name;//年龄的总数

*  avg()函数

avg():平均数
    
    select avg(age) as age_avg from tb_name;

*  max()函数

select max(age) as age_avg from tb_name;

*  min()函数

select min(age) as age_avg from tb_name;

参考：

[菜鸟教程之MySql][MySql]

[image]: http://i2.buimg.com/567571/fbb0cba8b425c1e1.jpg
[image 1]: http://i2.buimg.com/567571/f584c15ec8fcf9ee.jpg
[image 2]: http://i4.buimg.com/567571/d941c41ac2dc86f9.png
[image 3]: http://i2.buimg.com/567571/02ff1ad8e6da7d59.jpg
[image 4]: http://i2.buimg.com/567571/c6ab8023fd5d0b94.jpg
[image 5]: http://i2.buimg.com/567571/c29b4dd5bff7bdec.jpg
[MySql]: http://www.runoob.com/mysql/mysql-tutorial.html