开发者社区> 李振良> 正文

快速学习MySQL SQL语句

简介:
+关注继续查看

须知:

SQL语言:结构化查询语言,是关系型数据库查询语言的标准,不同的数据库虽然有自己私有扩展,但关键词都支持:(select、update、delete、insert、where)

SQL语句分类:像Oracle、MSSQL都是通用的

DDL:数据定义语言(create、alter、drop、rename)

DML:数据库维护语言(select、insert、update、delete)

DCL:数据库控制语言,权限(Grant、revoke)

TCL:事务控制语言(commt、sarepqint):新型的语句

一、数据库增删改查

根据这个表结构以下来操作:

mysql> select * from user;

+----+----------+------+

| id | name     | pass |

+----+----------+------+

基本操作

1.创建数据库

create database name;

2.删除数据库

drop database name;

3.创建一个用户表

create table user(id int,name varchar(30),pass varchar(30));

4.插入数据

insert into user(id,name,pass) values("1","zhangsan","123");

5.删除一个表

drop table user;

6.查看表字段

desc table user;

7.查看表数据

select * from user;

8.修改表名

rename table user to tab1;

9.更新数据

update user set pass="newpass" where pass=123;  #把密码更新为newpass

update user set id=10,name='lisi' where id=1;   #把id记录的name更新为id10和name为lisi

10.重命名字段名

alter table user change pass newname varchar(30); 

11.表中添加一个字段

alter table user add age int;

12.修改字段

alter table user modify age int no null default 20; #当年龄输入为空时,默认为20岁

13.删除表中的一个字段

alter table user drop age;

14.删除一条记录

delete from user where id=1;

15.查找一条记录

select id,name,pass from user where id=1;

16.删除ID记录

delete from user where id>=3 id<=5;             #删除大于3小于5的id

delete from user where id in(1,3,5);            #删除1,3,5的id

delete from user where id=1 or id=3 or id=5;       #删除1,3,5的id

delete from user where id between 1 and 5;       #删除1至5的id

高级查询

1.返回结果删除重复项

select distinct id from user;

2.查询字段中为NULL

select * from where pass is null;  

select * from where pass is not null; #查询不为NULL的

3.like模糊查询,包含zhang的列出来

select * from user where name like '%zhang%';

select * from user where name like '%zhang%' or name like '%li%';#查找包含zhang或li的列出来

也可以使用正则表达式查询,生产环境一般不用的,因为查询慢,效果一样

select * from user where name regexp 'li';
4.使用order by对查询结果排序(升序/降序),默认是升序

select id,name,pass from user order by id asc/desc;

5.使用limit取出排名前三个

select * from user  order by id desc limit 3;

6.concat函数使用-字符串连接符

mysql> select id,name,pass,concat(id,'_',name) idname from user;

+----+----------+------+------------+

| id | name     | pass | idname     |

+----+----------+------+------------+

|  1 | zhangsan | 123  | 1_zhangsan |

|  2 | lisi     | 123  | 2_lisi     |

|  3 | zhaowu   | 123  | 3_zhaowu   |

+----+----------+------+------------+

7.rand函数随机排序,如随机抽取前三名

select * from user order by rand() limit 3;

8.count统计,如统计多少个id记录

select count(*) count from user; 

#count为自定义显示查询结果后字段名,*为mysql优化后的查询方法,要比直接写入id效率高

select count(id) from user where name='zhangsan'; #统计zhangsan多少条记录

9.sum求和,如统计同消费了多少钱

select sum(id) from user where name='lisi';  #所有id数加在一起

10.avg平均数,如求班级平均分

select avg(id) from user;  

11.max最大值,如得到一个最高分

select max(id) from user;

12.min最小值

select min(id) from user;

13.group by分组聚合

select name,count(id) from user group by name order by desc;  

#使用count来聚合,基于name分组,再order by排序下(一般排名都使用分组聚合)

select name,count(id) count from user group by name having count>=3;

#查询前三名,having是对分组的结果进行筛选,这不能用where,count代表查询结果后显示的字段名

14.普通多表查询(前提:两个表必须有关系)

先创建两个有关系的表:

create table user(id int unsigned auto_increment primary key,name varchar(30),age int);

create table post(id int unsigned auto_increment primary key,uid int,title varchar(200),content text);

结果一:查询用户发的所有记录

select user.name,post.title,post.content from user,post where user.id=post.uid;

结果二:统计每个用户有多少个记录

select user.name,post.title,post.content count(id) from user,post where user.id=post.uid group by user.name;

15.联表查询(查询成绩)

mysql> select * from tb1;

+------+----------+

| id   | name     |

+------+----------+

|    1 | zhangsan |

|    2 | lisi     |

+------+----------+

mysql> select * from tb2;

+------+-------+

| id   | score |

+------+-------+

|    1 |    80 |

|    2 |    81 |

+------+-------+

mysql> select tb1.name,tb2.score from tb1,tb2 where tb1.id = tb2.id;

+----------+-------+

| name     | score |

+----------+-------+

| zhangsan |    80 |

| lisi     |    81 |

+----------+-------+

二、表字段类型

1.数值

int(size):整型,只能存整数数字,不能为空,默认允许输入null,也可以设置不允许写(not null)

float:浮点型,可以写入整数或浮点数

1.1字段属性

unsigned:无符号,全是整数

zerofill:与长度无关,不够3位时前面补0,默认看不见

null与not null:允许输入null和不允许输入

default:不允许null情况下,当输入空时,则使用默认值

auto_increment:一般自增ID

1.2示例

例如,设置id为自增:

create table user(id int unsigned auto_increment primary key,name varchar(30),pass varchar(30));

primary key:有auto_increment必须设置为主键索引,提供查询速度,再插入就不用写id列了,如果你写了,就以你写的为准,继续自增。

例如,创建一个表,当输入性别为空时,默认则为男:

create table user2(id int unsigned auto_increment primary key,name varchar(30),sex varchar(5) not null default "nan");

查看字段信息:

mysql> desc user2;

+-------+------------------+------+-----+---------+----------------+

| Field | Type             | Null | Key | Default | Extra          |

+-------+------------------+------+-----+---------+----------------+

| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

| name  | varchar(30)      | YES  |     | NULL    |                |

| sex   | varchar(5)       | NO   |     | nan     |                |

+-------+------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

2.字符串

char(size):占用size的字节,但查询速度快,最大支持255个字符

varchar(size):存多少占多少,剩点空间,最大支持65535个字符

text:支持65535字节

longtext:支持42亿字节

3.日期类型(有的会使用int类型来存时间戳)

以下是官方说法占用的字节,但实际会有不符。

date:年月日,占用3个字节

time:时分秒,占用3个字节

datetime:年月日时分秒,占用8个字节

year:年,占用1个字节

三、用户管理

1.设置Mysql密码

方法一:set password=password('newpass');

方法二:update user set password=password('newpass') where user='root';

方法三:grant all on *.* to 'root'@'localhost' identified by 'newpass';

再刷新:flush privileges;

2.创建用户并授权

grant all privileges on *.* to 'user'@'localhost' identified by 'pass';

*.*:对应的是数据库/表的权限

localhost:只允许本地访问,也可以设置%为所有访问

3.撤销用户权限

revoke all privileges on *.* from 'root'@'localhost' identified by 'pass';

四、数据库字符集

1.查看服务器基本信息\s

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.34, for Linux (x86_64) using  EditLine wrapper
Connection id:          141700
Current database:       lab
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.34-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 47 days 4 hours 54 min 1 sec
Threads: 4  Questions: 189434783  Slow queries: 1  Opens: 283512  Flush tables: 23  Open tables: 46  Queries per second avg: 46.447
--------------

可以看到四种默认字符集是latin1,修改四种默认字符集,client和connection两个要是一样的。

set character_set_server=utf8;

set character_set_database=utf8;

set character_set_client=utf8;

set character_set_connection=utf8;

也可以修改my.cnf默认字符集

[mysqld]

character_set_server=utf8

[mysql]

default-character-set=utf8

重启mysql!

2.查看创建数据库时使用的字符集

show create database mydb;

五、创建表字段索引

1.主键索引

 1.1创建一个主键索引,一般创建表时已经设置

 alter table user add primary key(id);

 1.2删除一个主键索引

 alter table user drop primary key;

2.普通索引

 2.1创建一个普通索引

 alter table user add index index_name(name);

 2.2删除一个普通索引

 alter table user drop index index_name;

 2.3查看主键索引和普通索引

 show index from user;

 show keys from user;

注:修改字段名不影响索引。

3.查看表中创建的索引

mysql> desc user2;

+-------+------------------+------+-----+---------+----------------+

| Field | Type             | Null | Key | Default | Extra          |

+-------+------------------+------+-----+---------+----------------+

| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

| name  | varchar(30)      | YES  | MUL | NULL    |                |

| pass  | varchar(30)      | YES  |     | NULL    |                |

+-------+------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

上面key中的PRI代表是主键索引,MUL代表是普通索引。

4.查看索引信息

mysql> show index from user2;

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| user2 |          0 | PRIMARY    |            1 | id          | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |

| user2 |          1 | index_name |            1 | name        | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

可以看到user表,做了PRIMARY主键索引和index_name名字的普通索引。

5.测试是否使用索引

mysql> desc select id,name from user2 where name='zhangsan';

+----+-------------+-------+------+---------------+------------+---------+-------+------+--------------------------+

| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra                    |

+----+-------------+-------+------+---------------+------------+---------+-------+------+--------------------------+

|  1 | SIMPLE      | user2 | ref  | index_name    | index_name | 33      | const |    1 | Using where; Using index |

+----+-------------+-------+------+---------------+------------+---------+-------+------+--------------------------+

1 row in set (0.00 sec)

用desc检测语句,可以看到查询一条张三的记录,使用了普通索引index_name这个名字,检索了一行(rows)就找到了这条记录。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用docker快速体验oceanbase
在Windows上使用dockers快速体验oceanbase
5 0
sqlite数据库
• 创建Entry的内部类, 该类实现BaseColumns • tableName定义常量字符串 • 为table的每一项什么常量字符串
4 0
图神经网络17-DGL实战:构建图神经网络(GNN)模块
图神经网络17-DGL实战:构建图神经网络(GNN)模块
4 0
Java 8 新特性:Java 类库的新特性之 Stream类(二)
Java 8 新特性:Java 类库的新特性之 Stream类(二)
4 0
数据结构与算法之排序(冒泡、选择、插入、希尔、归并、快速)(一)
数据结构与算法之排序(冒泡、选择、插入、希尔、归并、快速)
5 0
第四章 Gradle任务
上一章我们已经介绍了Gradle脚本的基础,在其中我们也强调了Gradle中最要的Projects和Tasks这两个概念,尤其是Tasks,我们的所有Gradle的构建工作都是由Tasks组合完成的,那么这一章我们就详细的介绍下Tasks--任务。
6 0
ContentProvider
构建content URI public class TaskContract { /* COMPLETED (1) Add content provider constants to the Contract Clients need to know how to access the task data, and it's your job to provide these content URI's for the path to that data: 1) Content authority, 2) Base content
3 0
+关注
李振良
6年互联网运维经验,擅长Linux,Python,Docker,MySQL,运维自动化等技术领域。
149
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载