MySQL基础使用

数据库

其实我们常常说的数据库，应该叫数据库系统。

表和库

数据表：用来保存数据的表格

数据库：用来统一管理数据表的容器

启动mysql 关闭mysql

service mysqld start(启动)

service mysqld stop(关闭)

进入mysql

mysql -uroot -p

修改数据库管理员密码

/usr/bin/mysqladmin -u root password '新密码'

库的操作

查看库

show databases;

创建一个数据库

create database 您要创建的数据库名称;
//创建数据库并指定编码和校对集
create database `数据库名称` character set utf8 collate utf8_general_ci;
create database `数据库名称` character set utf8mb4 collate utf8mb4_general_ci;

修改已存在的数据库编码

alter database 数据库名 charset=新的编码

删除数据库

drop database 要删除的数据库名称;

切换当前数据库

use 数据库名称

数据表的操作

查看某个数据库里面的数据表的列表

show tables;

创建数据表

create table 表名(

    字段名1 类型(长度),
    字段名2 类型(长度),
    字段名3 类型(长度),
    ……
)

比如：

CREATE TABLE `x2` (
  `id` smallint(5),
  `name` varchar(4),
  `sex` tinyint(1),
  `mobile` char(11),
  `address` varchar(100),
  `add_time` datetime
)

创建数据表的时候指定数据表的编码

CREATE TABLE `x5` (
  `id` smallint(5) DEFAULT NULL,
  `name` varchar(4) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mobile` char(11) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `add_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

修改数据表

增加列（字段）

alter table 数据表名 add 列名以及类型、约束等信息
//一次增加多列
alter table 数据表名 add （列名1以及类型、约束等信息,列名2以及类型、约束等信息）

修改列（字段）

//列名必须是已经存在的
alter table 数据表名 modify 列名以及类型、约束等信息

//旧列名必须存在，新列名可以和旧列名一样，但不能和其它列名相同
alter table 数据表名 change 旧列名称 新列名以及类型、约束等信息

删除列

alter table 数据表名称 drop 列名

修改数据表的编码

alter table 数据表名 charset=新的编码

字段类型

字段约束

查看建表语句

show create table 数据表名;

删除数据表

drop table 表名;

数据操作

插入数据

insert into 表名 (字段名1,字段名2,……) values (字段1的值,字段2的值,……),(字段1的值,字段2的值,……)，(字段1的值,字段2的值,……);

//从一个表中选择数据插入到另一个表中
insert into user (name,sex,age,mobile,address,native,card_id,number,room,xxx) select name,sex,age,mobile,address,native,card_id,number,room,xxx from student;

查询数据

select * from 表名;

查询用户

select * from mysql.user \G;

查看user表结构　需要具体的项可结合表结构来查询

desc mysql.user;

查看MYSQL数据库中所有用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

查询特定字段数据

select number,name,age,address,sex from student where native='湖北';

多条件查询

select number,name,age,address,sex from student where native='湖北' and sex=1;
select number,name,age,address,sex from student where native='湖北' && sex=1;


select number,name,age,address,sex from student where native='湖北' or native='湖南';
select number,name,age,address,sex from student where native='湖北' || native='湖南';
select * from student where native in ('湖南','湖北');

where 子句

可以使用各种运算符：

比较运算符： >、 <、 >=、 <=、 !=、 =、 <>

逻辑运算符：and（&&）、or（||）

小括号可以改变各个条件的运算顺序。

字段名 in (条件值) 、字段名 not in (条件值)

字段名 between A值 and B值 (包含边界) / 字段名 not between A值 and B值（不包含边界）

字段名 like ‘%字符%‘、字段名 not like ‘%字符%‘ %可有可无，可在前，可在后。

字段名 is null 、 字段名 is not null 针对值为null的查询处理。

order 子句

可以对查询结果集根据某些字段进行正序（asc）或倒序（desc）排序。可以同时根据多个字段排序，优先以前面的排序，当前面的排序值一样的时候，以紧跟其后的排序规则进行排序，以此类推。

limit 子句

该子句后面可以跟两参数，第一个表示向后偏移量；第二个表示要查询的数量。

当偏移量为0的时候，可以省略。

where、order、limit子句如果两两同时出现，那么顺序应该是：where最前，order次之，limit最后。

group 子句

分组查询，先根据某个分组字段进行分组，然后在组内分别计算。

having 子句

当having不和group子句一起使用的时候，其外在表现和where子句一样。

当having和group同时出现的时候，having要放到group后面。表示，先分组计算处理，然后从分组后的结果中筛选符合条件的数据。

聚合函数

sum() 求和

avg() 求平均数

max() 求最大值

min() 求最小值

count() 求数量

大多数聚合函数的值，可以使用其他语句得到相同值。

其他常用函数

abs() 求绝对值

ceil() 进一法取整

floor() 退一法取整

round(n, m)  对n四舍五入，m表示小数位，默认是0位

mod(a, b) 求a/b的余数(取模)

pi() 获取π的值

rand(n) 获取一个0-1之间的随机数，如果n存在，多次获取，结果的值不变。

sqrt(n) 返回n的平方根

ascii() 获取一个字符在ascii表中的十进制数字值

concat(str1,str2,……,strn); 将多个字符串拼成一个字符串
concat_ws(a, str1,str2,……,strn)  将多个字符串拼成一个字符串，将str1-strn使用a字符拼分割拼接成一个长字符串。
//select concat_ws('-',name,age,mobile,native) from student;

insert(a,m,n,b)  将a字符（字段的值）从第m位开始，选取n个，被替换成b。
//select insert(card_id,7,8,'********') from student;

find_in_set(a, b) 在b列表中寻找a（b需要时使用逗号分割的，比如：'数学,英语,物理' ），返回的是a在b中的位置，为0时表示不存在。
//select find_in_set('数学', subject),id,name from student;

group_concat(字段名) 将一组内的某个字段的所有值使用逗号连接成一个字符串。

ucase(str) / upper(str) 将 str全部转成大写
lcase(str) / lower(str)将 str全部转成小写

length(str) 计算字符str的长度，其中每个汉字占用3位。

left(str,n) 从字符str的左侧开始，选取n位作为结果。
right(str,n) 从字符str的右侧开始，选取n位作为结果。

trim(str) 清除str中开头和结尾的空格部分
ltrim(str) 清除str中开头空格部分
rtrim(str) 清除str中结尾的空格部分

replace(str,old, new) 将str中的old字符（串）替换成new字符（串）

reverse(str) 将str颠倒顺序


curdate() 当前日期
curtime() 当前时间
unix_timestamp() 获取时间戳
now() 获取当前时间和日期


md5() 计算哈希值，使用的是md5算法
sha1() 计算hash值，使用的是sha1算法


if(a,b,c) 如果a为真，则返回b，否则返回c，经测试：a为字符（串）的时候，一直返回c。

ifnull(a,b) 如果a为null，则返回b，否则返回a。

删除数据

delete from 数据表名称;
delete from 数据表名称 where 条件;

修改数据

update 数据表名 set 字段名=新的值 where 条件;
update 数据表名 set 字段名1=新的值,字段名2=新的值 where 条件;

用户管理

创建用户

//创建完默认情况下不能本地登录，只能远程登录
create user 用户名 identified by 密码

//创建用户并只能可以通过某个地址的客户端登录
create user 用户名@主机地址 identified by 密码

//使用授权命令授权的时候创建新用户并授权
grant all privileges on xxxx.* to dddd@'%' identified by '654321';

授权

//使用授权命令授权的时候创建新用户并授权
grant 权限类型 on 数据库.数据表 to 用户@主机地址 identified by 密码;

//给已经存在的用户授权
grant 权限类型 on 数据库.数据表 to 已存在的用户名;

//给用aaa在xxxx数据库中的所有表都授有select权限
grant select on xxxx.* to aaa;
//对于有些权限的设置 需要加上host地址，这个地址一般和该用户在mysql.user表中的host字段的值相同
grant select on xxxx.* to aaa@'host地址';

//给用aaa在xxxx数据库中的所有表都授有update和delete权限
grant update,delete on xxxx.* to aaa;

常用的权限类型：

主机地址

撤销权限

基本格式：

revoke 权限类型 on 数据库.数据表 from 用户名;

例子：

//撤销某一类权限
revoke update on xxxx.* from aaa;

//一次性多个权限
revoke select,insert,delete on xxxx.* from aaa;

//一次性撤销所有权限
revoke all  on xxxx.* from aaa;
revoke all privileges on xxxx.* from aaa;

刷新权限

flush privileges;

注意：权限更新后，如果之前已经连接到服务端的用户，并不会马上生效新权限更改。只有退出后重新登录才生效。

密码修改

方法1：

mysqladmin -u 用户名 password 新密码 -p

例：

mysqladmin -u root password '654321' -p

方法2：

使用grant授权的时候给已存在用户进行密码修改

//给qqq用户在xxxx库中所有表的所有权限。
//如果qqq用户不存在，那么会创建一个新用户qqq，并且设置密码为111111。
//如果qqq用户已经存在且原密码不是111111，那么会将密码修改为111111。
grant all on xxxx.* to qqq@'localhost' identified by '111111';

方法3：

//给当前登录用户修改密码
set password=password('222222');

//权限比较高的用户为其他用户或者自己修改密码
set password for aaa@'%'=password('333333');

方法4：

直接修改mysql库里面的user表里面的数据

//将用户bbb的密码修改为111111.注意一定要使用password()函数处理
update user set Password=password('111111') where User='bbb';

//该方法修改完密码以后需要刷新一下
flush privileges;

附加1 mysql权限类型

导入和导出

导出

导出某个库的所有表结构和数据

基本结构：

mysqldump -u 用户名 -p 数据库名 > 导出的文件路径

例子：

mysqldump -u root -p xxxx > ./xxxx.sql

导出某个库的所有表结构

基本结构：

mysqldump -u 用户名 -p -d 数据库名 > 导出的文件路径

例子：

mysqldump -u root -p -d xxxx > ./xxxx.sql

导出某个库的某个表的结构和数据

基本结构：

mysqldump -u 用户名 -p 数据库名 数据表名 > 导出的文件路径

例子：

mysqldump -u root -p xxxx user > ./xxxx.sql

导出某个库的某个表的结构

基本结构：

mysqldump -u 用户名 -p -d 数据库名 数据表名 > 导出的文件路径

例子：

mysqldump -u root -p -d xxxx user > ./xxxx.sql

导出所有的数据库结构和数据

mysqldump -uroot -p --all-databases > aaa.sql

导出所有的数据库结构

mysqldump -uroot -p -d --all-databases > aaa.sql

更多请参考：https://www.cnblogs.com/emanlee/p/5410177.html

导入

方法1：

在mysql命令行：

source 备份文件路径

//例
source /root/123.sql

方法2：

 mysql -u 用户名 -p 数据库名 < 数据文件路径  
 
//例
mysql -u username -p test_db < test_db.sql  

杂项

起别名：as

MySQL基础使用

标签：sql   客户   for   www   平方根   now()   html   索引   float