当前位置:首页 > Mysql > 正文内容

mysql数据库SQL语句及字符集定义

5年前 (2019-09-02)Mysql500

SQL简单语句

用于数据库的连接管理:mysql

命令行管理工具:mysqladmin

备份数据库和表的内容:mysqldump



MySQL接口自带的命令

\h 或 help 或?    查看帮助
\G             格式化查看数据(key:value)
\T 或 tee        记录日志
\c(5.7可以ctrl+c) 结束命令
\s 或 status      查看状态信息
\. 或 source      导入SQL数据
\u或 use         使用数据库
\q 或 exit 或 quit  退出

接收用户的SQL语句

将用户的SQL语句发送到服务器



help命令的使用

mysql> help
mysql> help contents
mysql> help select
mysql> help create
mysql> help create user
mysql> help status
mysql> help show



source命令的使用

#在MySQL中处理输入文件:

#如果这些文件包含SQL语句则称为:

#1.脚本文件

#2.批处理文件
mysql> SOURCE /data/mysql/world.sql
#或者使用非交互式
mysql</data/mysql/world.sql


mysqladmin命令的使用


1、“强制回应 (Ping)”服务器。

2、关闭服务器。

3、创建和删除数据库。

4、显示服务器和版本信息。

5、显示或重置服务器状态变量。

6、设置口令。

7、重新刷新授权表。

8、刷新日志文件和高速缓存。

9、启动和停止复制。

10、显示客户机信息。

#查看MySQL存活状态
[root@db01 ~]# mysqladmin -uroot -p123 ping

#查看MySQL状态信息
[root@db01 ~]# mysqladmin -uroot -p123 status

#关闭MySQL进程
[root@db01 ~]# mysqladmin -uroot -p123 shutdown

#查看MySQL参数
[root@db01 ~]# mysqladmin -uroot -p123 variables

#删除数据库
[root@db01 ~]# mysqladmin -uroot -p123 drop DATABASE

#创建数据库
[root@db01 ~]# mysqladmin -uroot -p123 create DATABASE

#重载授权表
[root@db01 ~]# mysqladmin -uroot -p123 reload

#刷新日志
[root@db01 ~]# mysqladmin -uroot -p123 flush-log

#刷新缓存主机
[root@db01 ~]# mysqladmin -uroot -p123 reload

#修改口令
[root@db01 ~]# mysqladmin -uroot -p123 password


SQL语句  


DDL语句数据定义语言

库操作(增删改查)create database | schema   


#创建qmf数据库
mysql> create database qmf;

#创建qmf数据库
mysql> create database qmf;

#查看数据库
mysql> show databases;

#查看qmf的创建语句(DQL)
mysql> show create database qmf;

#查看创建数据库语句帮助
mysql> help create database

#创建qmf数据库添加属性
mysql> create database testa charset utf8;

################################
删库:drop database

#删除qmf数据库
mysql> drop database qmf;

######################################
修改定义库:alter database

#修改qmf数据库属性
mysql> alter database qmf charset gbk;

#查看qmf的创建语句(DQL)
mysql> show create database qmf;




表操作(增删改查):create table (开发做) 

#查看创建表语句帮助
mysql> help create table

#创建表
mysql> create table student(
sid INT,
sname VARCHAR(20),
sage TINYINT,
sgender ENUM('m','f'),
cometime DATETIME);


数据类型

int整数 -231 ~ 231 -1
varchar字符类型 (变长)
char字符类型 (定长)
tinyint整数 -128 ~ 128
enum整数 -128 ~ 128
datetime时间类型 年月日时分秒


#创建表加其他属性
mysql> create table student(
sid INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(20) NOT NULL COMMENT ‘学生姓名’,
sage TINYINT UNSIGNED COMMENT ‘学生年龄’,
sgender ENUM('m','f')  NOT NULL DEFAULT ‘m’ COMMENT ‘学生性别’,
cometime DATETIME NOT NULL COMMENT ‘入学时间’)chatset utf8 engine innodb;

#查看建表语句
mysql> show create table student;

#查看表
mysql> show tables;

#查看表中列的定义信息
mysql> desc student;


数据属性

not null: 非空

primary key: 主键(唯一且非空的)

auto_increment: 自增(此列必须是:primary key或者unique key)

unique key: 单独的唯一的

default: 默认值

unsigned: 非负数

comment: 注释


#删除表
mysql> drop table student;


修改表定义:alter table (开发做)
#修改表名
mysql> alter table student rename stu;

#添加列和列定义
mysql> alter table stu add age int;

#添加多个列
mysql> alter table stu add test varchar(20),add qq int;

#指定位置进行添加列(表首)
mysql> alter table stu add classid varchar(20) first;

#指定位置进行添加列(指定列)
mysql> alter table stu add phone int after age;

#删除指定的列及定义
mysql> alter table stu drop qq;

#修改列及定义(列属性)
mysql> alter table stu modify sid varchar(20);

#修改列及定义(列名及属性)
mysql> alter table stu change phone telphone char(20);




DCL:数据控制语言


针对权限进行控制


grant


#授权root@10.0.0.51用户所有权限(非炒鸡管理员)
mysql> grant all on *.* to root@'10.0.0.51' identified by 'qmf123';

#怎么去授权一个炒鸡管理员呢?
mysql> grant all on *.* to root@'10.0.0.51' identified by 'qmf123' with grant option;

#其他参数(扩展)
max_queries_per_hour:一个用户每小时可发出的查询数量
max_updates_per_hour:一个用户每小时可发出的更新数量
max_connetions_per_hour:一个用户每小时可连接到服务器的次数
max_user_connetions:允许同时连接数量


revoke


#收回select权限
mysql> revoke select on *.* from root@'10.0.0.51';

#查看权限
mysql> show grants for root@'10.0.0.51';



操作表的数据行信息


insert


#基础用法,插入数据
mysql> insert into stu values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);

#规范用法,插入数据
mysql> insert into stu(classid,birth.sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);

#插入多条数据
mysql> insert into stu(classid,birth.sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456),
('linux02',2,NOW(),'zhangsi',21,'f',NOW(),111,1234567);
update

#不规范
mysql> update student set sgender='f';

#规范update修改
mysql> update student set sgender='f' where sid=1;

#如果非要全表修改
mysql> update student set sgender='f' where 1=1;



delete


#不规范
mysql> delete from student;

#规范删除(危险)
mysql> delete from student where sid=3;

#DDL删除表
mysql> truncate table student;


1、使用伪删除

使用update代替delete


1)额外添加一个状态列
mysql> alter table student add status enum(1,0) default 1;

2)使用update
mysql> update student set status='0' where sid=1;

3)应用查询存在的数据
mysql> select * from student where status=1;



DQL:数据查询语言


select:基础用法


#常用用法
mysql> select countrycode,district from city;

#查询单列
mysql> select countrycode from city;

#行级查询
mysql> select countrycode,district from city limit 2;
mysql> select id,countrycode,district from city limit 2,2;

#条件查询
mysql> select name,population from city where countrycode='CHN';

#多条件查询
mysql> select name,population from city where countrycode='CHN' and district='heilongjiang';

#模糊查询
mysql> select name,population,countrycode from city where countrycode like '%H%' limit 10;

#排序查询(顺序)
mysql> select id,name,population,countrycode from city order by countrycode limit 10;

#排序查询(倒叙)
mysql> select id,name,population,countrycode from city order by countrycode desc limit 10;

#范围查询(>,<,>=,<=,<>)
mysql> select * from city where population>=1410000;

#范围查询OR语句
mysql> select * from city where countrycode='CHN' or countrycode='USA';

#范围查询IN语句
mysql> select * from city where countrycode in ('CHN','USA');




字符集定义


字符集:是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称,包括各国家文字、标点符号、图形符号、数字等。


1、MySQL数据库的字符集

1.字符集(CHARACTER)

2.校对规则(COLLATION)


2、MySQL中常见的字符集

1.UTF8

2.LATIN1

3.GBK


3、常见校对规则

1.ci:大小写不敏感

2.cs或bin:大小写敏感


4、我们可以使用以下命令查看

mysql> show charset;

mysql> show collation;



字符集设置

1.操作系统级别

[root@db01 ~]# source /etc/sysconfig/i18n

[root@db01 ~]# echo $LANG

zh_CN.UTF-8


2.操作系统客户端级别(SSH)


3.MySQL实例级别


方法1:在编译安装时候就指定如下服务器端字符集。

cmake . 

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_EXTRA_CHARSETS=all \


方法2:在配置文件中设置字符集


[mysqld]

character-set-server=utf8


4.建库级别

mysql> create database qmf charset utf8 default collate = utf8_general_ci;


5.建表级别

mysql>  CREATE TABLE `test` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;


生产环境更改数据库(含数据)字符集的方法

mysql> alter database qmf CHARACTER SET utf8 collate utf8_general_ci;

mysql> alter table t1 CHARACTER SET utf8;



select的高级用法


1.多表连接查询(连表查询)

image.png


集合:

[zhang3,li4,wang5]


[50,70,80]


t1:

sid 1 2 3

sname zhang3 li4 wang5


t2:

sid 1 2 3

mark 50 70 80


范式: 减少数据冗余,防止产生一致性问题,把一个表作为一个原子,把一张表拆到不能再拆为止。(开发阶段设计规范)


例:根据两张表的内容查出张三的成绩

select t1.sname,t2.mark from t1,t2 where t1.sid=t2.sid and t1.sname=’zhang3’;


1.1传统连接(只能内连接,只能取交集)

#世界上小于100人的人口城市是哪个国家的?
select city.name,city.countrycode,country.name 
from city,country 
where city.countrycode=country.code 
and city.population<100;


1.2 NATURAL JOIN(自连接的表要有共同的列名字)

SELECT city.name,city.countrycode ,countrylanguage.language ,city.population
FROM  city NATURAL  JOIN  countrylanguage 
WHERE population > 1000000
ORDER BY population;


1.3企业中多表连接查询(内连接)

select city.name,city.countrycode,country.name 
from city join country on city.countrycode=country.code 
where city.population<100;

建议:使用join语句时,小表在前,大表在后。


1.4外连接

select city.name,city.countrycode,country.name 
from city left join country 
on city.countrycode=country.code 
and city.population<100;


1.5 UNION(合并查询)

#范围查询OR语句
mysql> select * from city where countrycode='CHN' or countrycode='USA';

#范围查询IN语句
mysql> select * from city where countrycode in ('CHN','USA');

替换为:
mysql> select * from city where countrycode='CHN' 
union  all
select * from city where countrycode='USA' limit 10

“mysql数据库SQL语句及字符集定义” 的相关文章

MySQL用户、权限简单管理

MySQL管理MySQL用户的作用:1、登陆MySQL数据库2、管理数据库对象MySQL用户管理:1、创建用户:create user2、删除用户:delete user drop user3、修改用户:update用户的定义:1、username@’主机域’2、主机域:可以理解为是MySQL登陆的...

MySQL的存储引擎(二)Innodb核心特性——事务

MySQL的存储引擎(二)Innodb核心特性——事务

Innodb核心特性——事务1.什么是事务主要针对DML语句(update,delete,insert)一组数据操作执行步骤,这些步骤被视为一个工作单元:1)用于对多个语句进行分组2)可以在多个客户机并发访问同一个表中的数据时使用所有步骤都成功或都失败1)如果所有步骤正常,则执行2)如果步骤出现错误...

MySQL的备份和恢复

备份的类型冷备份:这些备份在用户不能访问数据时进行,因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。温备份:这些备份在读取数据时进行,但在多数情况下,在进...

MySQL主从复制

主从复制简介1:高可用2:辅助备份3:分担负载复制是 MySQL 的一项功能,允许服务器将更改从一个实例复制到另一个实例。1:主服务器将所有数据和结构更改记录到二进制日志中。2:从属服务器从主服务器请求该二进制日志并在本地应用其内容。3:IO:请求主库,获取上一次执行过的新的事件,并存放到rela...

MySQL集群——MHA高可用及读写分离

MySQL集群——MHA高可用及读写分离

一.MHA简介MHA能够在较短的时间内实现自动故障检测和故障转移,通常在10-30秒以内;在复制框架中,MHA能够很好地解决复制过程中的数据一致性问题,由于不需要在现有的replication中添加额外的服务器,仅需要一个manager节点,而一个Manager能管理多套复制,所以能大大地节约服务器...