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

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字段。

---

查询相关的子句及其他

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;

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查询数据是基于下面这张表的数据进行的

• 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;

• 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列降序排列。

应用场景:如：查询生日相同的人都有谁等

• 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;//没有什么用似得

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