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个或者多个位。

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;