mysql学习笔记（三）：常用命令和DDL，DCL和DML操作

目录

​一、连接MySQL

二、MySQL--修改密码

三、DDL数据库定义语言

四，DCL数据库控制语言

五、DML数据操纵语言

---

表和库区分大小写，其他的不区分大小写

​一、连接MySQL

格式：mysql -h主机地址 -u用户名 －p用户密码

1、连接到本机上的MySQ：

首先在打开cmd窗口，输入mysql -uroot -p ，然后空格进入MySQL控制台，MySQL的提示符是： 。

mysql -uroot -p 刚安装好是没有密码的

2、连接到远程主机上的MySQL：

mysql -h(ip地址) -uroot -p(密码); /*u与root可以不加空格*/

3、退出MySQL命令：exit 或 quit(回车)：

exit/quit;

二、MySQL--修改密码

格式：mysqladmin -u用户名 -p旧密码 password 新密码

1、root密码为空时，设置root密码：

mysqladmin -uroot newpassword; -- 因为开始时root没有密码，所以-p旧密码 可以省略

2、使用 sqladmin 命令修改密码：

mysqladmin -uroot -p123456 password 12345678;

3、使用set passsword命令修改密码：

set password for 用户名@localhost = password('新密码');

4、忘记密码情况下：

①关闭正在运行的MySQL服务。

②打开DOS窗口，转到mysqlbin目录。

③输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。

④再开一个DOS窗口(因为刚才那个DOS窗口已经不能动了)，输入mysql回车，如果成功，将出现MySQL提示符 >。

⑤连接权限数据库：use mysql; 。

⑥改密码：update user set password=password("新密码") where user="root";(别忘了最后加分号) 。

⑦刷新权限(必须步骤)：flush privileges;　。

⑧退出mysql quit；。

mysqld --skip-grant-tables

use mysql; --连接权限数据库

update user set password=password("新密码") where user="root"; -- 改密码

flush privileges; -- 刷新权限

quit； -- 退出mysql

三、DDL数据库定义语言

在创建表的时候用到的一些sql，定义表的结构和数据类型，表之间的连接和约束

1，创建库

create database db_name; -- 创建数据库
show databases; -- 显示所有的数据库
drop database db_name; -- 删除数据库
use db_name; -- 选择数据库

2，创建表

语法:

-CREATE [TEMPORARY] TABLE [IFNOT EXISTS][database_name.] <table_name>(<column_name> <data_type> [[not] null],...)
-TEMPORARY:指明创建临时表
-lF NOT EXISTS:如果要创建的表已经存在，强制不显示错误消息
database_name:数据库名- table_name:表名
column_name:列名- data_type:数据类型
       engine=myisam;    指定引擎
       default charset=utf8mb4;    指定字符集,

3，根据已有的表创建新表

新表继承旧表的结构，不包括内容

create table new_tbl like old_tbl;

新表继承旧表所选的内容，但外键，提示等没有继承

root@meikoko2 21:34 >create table mei6 as select name,city from sc_student;
Query OK, 4 rows affected (0.01 sec)

###create table new_tbl  as select ..... from old_tbl;

show tables; -- 显示数据表
desc tb_name； -- 显示表结构
drop table tb_name； -- 删除表
show create database meikoko;#查看表信息（字符集）
show variables like "%character%";#查看使用什么字符集

4，设置外键

foreign key 外键 （A表里面的主键被引用到B表里面做一个字段，这个字段就是B表的外键）省了磁盘空间，消耗内存和cpu空间

root@meikoko2 20:15 >create table mei3(
        dep int,
        dname varchar(20),
        primary key(dep));
Query OK, 0 rows affected (0.01 sec)

root@meikoko2 20:34 >insert into mei3(dep,dname) values(10,'市场部');

Query OK, 1 row affected (0.00 sec)

root@meikoko2 20:36 >insert into mei3(dep,dname) values(20,'销售.部');
Query OK, 1 row affected (0.01 sec)

root@meikoko2 20:36 >select * from mei3;
+-----+-----------+
| dep | dname     |
+-----+-----------+
|  10 | 市场部    |
|  20 | 销售部    |
+-----+-----------+
2 rows in set (0.00 sec)

root@meikoko2 20:36 >create table emp(
        id int,
        name varchar(20),
        dep int,
        primary key(id),
        foreign key(dep)  ##外键为dep
        references mei3(dep));  ##外键是mei3表的dep                
Query OK, 0 rows affected (0.02 sec)

alter命令

alter database meikoko2 default character set utf8mb4;修改表的字符集
alter add命令用来增加表的字段。
alter add命令格式：alter table 表名 add字段 类型 其他;
1) 加索引
alter table 表名 add index 索引名 (字段名1[，字段名2 …]);
alter table employee add index emp_name (name);
2) 加注关键字的索引
alter table 表名 add primary key (字段名);
alter table employee add primary key(id);
3) 加唯一限制条件的索引
alter table 表名 add unique 索引名 (字段名);
alter table employee add unique emp_name2(cardnumber);
4) 删除某个索引
alter table 表名 drop index 索引名;
alter table employee drop index emp_name;
5) 增加字段
ALTER TABLE table_name ADD field_name field_type;
6) 修改原字段名称及类型
ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
7) 删除字段
MySQL ALTER TABLE table_name DROP field_name;

字段属性（约束）

auto_increment  自动增加
zerofill  填零
unique  唯一性，允许为null
default 18 会默认填入18
foreign key  外键 （A表里面的主键被引用到B表里面做一个字段，这个字段就是B表的外键）省了磁盘空间，消耗内存和cpu空间
primary key 主键（建立索引，不允许重复）
如果要有两个主键就在最后primary key（...）
comment  注释
not null  不允许出现空值
signed        有符号数，默认的 int 类型，允许插入负数
unsigned     无符号数，设置后，无法插入负数。（int unsigned）

六、在shell里面操作mysql

1， -e

mysql -uroot -p123456 -e 'show databases;use meikoko;select * from t1'  ##结果可以用grep过滤

2， here document

#!/bin/bash
mysql -uroot -p '123456sc' <<EOFcreate database liyc;
use mei;
create table t1(id int);
insert into t1(id) values ( 1), (2), (8);
exit
EOF
echo"插入数据成功"

四，DCL数据库控制语言

是用来设置和更改用户权限的语言grant和revoke

权限分类

用户授权

一，建立用户

grant select,insert on 库.表 to '用户'@'主机'；

mysql8里

1.新建用户

create user 'scout'@'%' indentified by '123456';

2.给用户授权

grant select,insert on 库.表 to '用户'@'主机'；

3.默认的mysql8里的用户的密码使用的是最新的加密算法，sqlyog不支持，使用workbench可以的

解决办法：用旧的加密方式

create user 'admin'@'%' identified with mysql_native_password by 'your_password‘;

mysql5.7

1.新建用户

2.给用户授权

3.grant授权用户，如果用户不存在，会自动新建用户

grant all on meikoko.* to 'jiejie'@'%' identified by '123456';

二， WITH GRANT OPTION 表示该用户可以将自己拥有的权限授权给别人

grant all on *.* to 'mei3'@'%' with grant option;

三，查看用户有哪些权限

show grants for username

撤销权限

revoke 权限 on 库.表 from '用户'@'主机'；

刷新权限

flush privileges;

删除用户

drop user

设置密码

alter user

set password

五、DML数据操纵语言

主要用来对数据库的数据进行一些操作

插入数据(Insert)
第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
insert into tb_name values (value1,value2,value3,...);
第二种形式需要指定列名及被插入的值：
insert into tb_name (column1,column2,column3,...) values (value1,value2,value3,...);
插入其他表的数据
insert into new_table select 列.... from old_tabble where leagueno is NULL;##where判断语句
修改数据(update)
update PENALTIES set AMOUNT='50.00' where PLAYERNO=44;
删除
DELETE [IGNORE] FROM tbl_name[WHERE where_condition]
[ORDER BY ...]##根据什么来排序
【LIMIT row count】
#######
truncate table 表名;##删除整张表内容

delete删除表是一行一行删除，删除后会产生日志，可以根据日志恢复数据

truncate 删除数据特别快，整个表锁定然后删除，不会记录二进制日志，不能恢复，用来删除大表

七、查询

忽略错误提醒(ignore)
insert ignore into table values(...);
查询一列数的总和
select sum(list) from table;
查询多少行
select count(*) from table;
多表连接查询
root@meikoko2 20:52 >select id,name,dep,dname from emp join mei3 using(dep);
+----+------+------+-----------+
| id | name | dep  | dname     |
+----+------+------+-----------+
|  1 | 王   |   10 | 市场部    |
|  2 | 王   |   20 | 销售部    |
|  3 | 王   |   20 | 销售部    |
+----+------+------+-----------+
3 rows in set (0.00 sec)