SQL语句

DDL:数据库定义语言(库、表的管理)

CREATE,DROP,ALTER

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |    ###虚拟库,用户信息
| mysql              |    ###授权库,用户权限信息
| performance_schema |    ###数据库性能参数
| sys                |    ###数据库性能参数
+--------------------+


创建数据库
mysql> CREATE database Q;

名字要求:1、区分大小写
         2、不能与命令相同
         3、不能纯数字、特殊符号

进入数据库
mysql> USE node1;                    ###进入node1
Database changed
mysql> SELECT database();            ###查看所在库
+------------+
| database() |
+------------+
| node1      |
+------------+
1 row in set (0.00 sec)


删除数据库
drop database Q;

建表
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (id int,name varchar(30));
Query OK, 0 rows affected (0.02 sec)


查表
mysql> show tables;             ###显示表名
+-----------------+
| Tables_in_node1 |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.01 sec)

mysql> desc t2;                 ###显示表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> select * from t2;         ###显示表内容
+------+------+
| id   | name |
+------+------+
|    1 | Q    |
|    2 | K    |
+------+------+
2 rows in set (0.00 sec)

mysql> show create table student2\G           ###显示表属性
*************************** 1. row ***************************
       Table: student2
Create Table: CREATE TABLE `student2` (
  `name` varchar(5) DEFAULT NULL,
  `sex` enum('m','w') DEFAULT NULL,
  `job` set('worker','dancer','IT','teacher') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


删表
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

TYPE类型: 

一、数值类型
int——整数 

     unsigned-无符号型
mysql> create table test2 (int_test int unsigned,tinyint_test tinyint unsigned)
;
Query OK, 0 rows affected (0.01 sec)

mysql> desc test2
    -> ;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| int_test     | int unsigned     | YES  |     | NULL    |       |
| tinyint_test | tinyint unsigned | YES  |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

    zerofill-零填充
mysql> create table test3 (id int(6) zerofill,age int(10) zerofill);
Query OK, 0 rows affected, 4 warnings (0.02 sec)
mysql> desc test3;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(6) unsigned zerofill  | YES  |     | NULL    |       |
| age   | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from test3;
+--------+------------+
| id     | age        |
+--------+------------+
| 000023 | 0000000078 |
+--------+------------+
1 row in set (0.00 sec)


float——小数,四舍五入
decimal——准确小数,直接放弃
mysql> create table test4 (sum float(5,3));                ###float(5位数,3位是小数)
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> desc test4;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| sum   | float(5,3) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)
二、字符类型
char——255位
varchar——65535位
三、枚举、集合类型
enum——单选
set——多选

mysql> create table student2 (name varchar(5),
    -> sex enum('m','w'),
    -> job set('worker','dancer','IT','teacher'));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into student2 values('K','w','worker,dancer');
Query OK, 1 row affected (0.00 sec)
四、时间类型
date——日期
time——时间
year——年
datetime——时间日期
timestamp——自动填充时间

mysql> create table t6 (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t6 values(now(),now(),now());            ###函数now(),系统当前时间
Query OK, 1 row affected, 1 warning (0.00 sec)


mysql> select * from t6;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2023-11-28 | 15:48:32 | 2023-11-28 15:48:32 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

表约束

default——默认值
not null——不能为空
unsigned——无符号
zerofill——零填充
primary key——值不能重复、不能为空
auto_increment——自动按序填写
foreign key——同步父表主键与其他子表外键
unique——值不能重复、可以为空

mysql> create table student3 (
    -> id int not null,
    -> name varchar(10) not null,
    -> sex enum('m','w') default'm',
    -> age int(3) unsigned not null;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> desc student3;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int           | NO   |     | NULL    |       |
| name  | varchar(10)   | NO   |     | NULL    |       |
| sex   | enum('m','w') | YES  |     | m       |       |
| age   | int unsigned  | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> create table t1 (
    -> id int primary key auto_increment,        <<————
    -> name varchar(10) not null,
    -> sex enum('m','w') default'w');
Query OK, 0 rows affected (0.02 sec)
效果一样:
mysql> create table t1 (
    -> id int auto_increment,
    -> name varchar(10) not null,
    -> sex enum('m','w') default'w'),
    -> primary key (id));                        <<————

mysql> desc t1;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int           | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)   | NO   |     | NULL    |                |
| sex   | enum('m','w') | YES  |     | w       |                |
+-------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)



复合主键:
mysql> create table t2(
    -> id int auto_increment,
    -> name varchar(10) not null,
    -> sex enum('m','w') default'm',
    -> primary key(id,name));             <<————
mysql> desc t2;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int           | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)   | NO   | PRI | NULL    |                |
| sex   | enum('m','w') | YES  |     | m       |                |
+-------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)



外键:
mysql> create table t1 (
    -> name varchar(10) primary key,
    -> mail varchar(20) not null,
    -> tel int(11) unsigned);
mysql> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(10)  | NO   | PRI | NULL    |       |
| mail  | varchar(20)  | NO   |     | NULL    |       |
| tel   | int unsigned | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table t2 (
    -> id int(3) auto_increment,
    -> name varchar(10) not null,
    -> payroll float(8,2) not null,
    -> primary key(id),
    -> foreign key(name) references t1(name) on update cascade on delete cascade); 
         <<————
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> desc t2;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | NO   | MUL | NULL    |                |
| payroll | float(8,2)  | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

DML:数据库操纵语言(数据的管理)

INSERT,DELETE,UPDATE

插入数据
mysql> insert into t2 values (1,"Q");        ###多个数据
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 (name) values ('C');        ###指定数据
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(3,'A'),(4,'B'),(5,'C');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0



更新数据
mysql> update t1 set sex='w' where sex='m';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t2 set id=9 where name='C';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0




删除数据
mysql> delete from t1 where name='K';           ###多个数据
Query OK, 1 row affected (0.00 sec)

mysql> update t2 set id=null where name='Q';    ###指定数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

DQL:数据库查询语言

SELECT

一、简单查询
select * from t1;                    <<---查看所有列

mysql> select id,name from t2;       <<---查看指定列
+----+------+
| id | name |
+----+------+
|  3 | H    |
|  2 | K    |
|  1 | Q    |
+----+------+
3 rows in set (0.00 sec)


mysql> select name,payroll*9 from t2;        <<---结果加减乘除
+------+-----------+
| name | payroll*9 |
+------+-----------+
| Q    |   7200.00 |
| K    |   6030.00 |
| H    |   8100.00 |
+------+-----------+
3 rows in set (0.00 sec)





二、条件查询
=,and,or,between and,in,like

mysql> select name,mail from t1 where mail='K@123.com';        <<---单条件查询
+------+-----------+
| name | mail      |
+------+-----------+
| K    | K@123.com |
+------+-----------+
1 row in set (0.00 sec)


mysql> select name,payroll from t2 where id='3' and payroll>700;    <<---and多条件查询
+------+---------+
| name | payroll |
+------+---------+
| H    |  900.00 |
+------+---------+
1 row in set (0.00 sec)

mysql> select name,payroll from t2 where id='3' or payroll>700;    <<---or多条件查询
+------+---------+
| name | payroll |
+------+---------+
| Q    |  800.00 |
| H    |  900.00 |
+------+---------+
2 rows in set (0.00 sec)


mysql> select name,payroll from t2 where payroll between 600 and 800;   <<---两个数之间
+------+---------+
| name | payroll |
+------+---------+
| Q    |  800.00 |
| K    |  670.00 |
+------+---------+
2 rows in set (0.00 sec)

mysql> select name,payroll from t2 where payroll not between 600 and 800;   <<---两个数之外
+------+---------+
| name | payroll |
+------+---------+
| H    |  900.00 |
+------+---------+
1 row in set (0.00 sec)


mysql> select name,payroll from t2 where payroll in(800,900);       <<---in多条件查询
+------+---------+
| name | payroll |
+------+---------+
| Q    |  800.00 |
| H    |  900.00 |
+------+---------+
2 rows in set (0.00 sec)

mysql> select name,payroll from t2 where payroll not in(800,900);
+------+---------+
| name | payroll |
+------+---------+
| K    |  670.00 |
+------+---------+
1 row in set (0.00 sec)


mysql> select name,tel from t1 where tel is null;        <<---查询空值
+------+------+
| name | tel  |
+------+------+
| M    | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> select name,tel from t1 where tel is not null;
+------+------+
| name | tel  |
+------+------+
| H    |  789 |
| K    |  456 |
| Q    |  123 |
+------+------+
3 rows in set (0.00 sec)


mysql> select name,mail from t1 where mail like 'M@%';       <<---模糊查询
+------+-----------+
| name | mail      |
+------+-----------+
| M    | M@123.com |
+------+-----------+
1 row in set (0.00 sec)

mysql> select name,mail from t1 where mail like '__123.com';    <<---1个下划线代表一个字符
+------+-----------+
| name | mail      |
+------+-----------+
| H    | H@123.com |
| K    | K@123.com |
| M    | M@123.com |
| Q    | Q@123.com |
+------+-----------+
4 rows in set (0.00 sec)






三、查询排序
asc,desc,limit

mysql> select * from t2 order by payroll asc;        <<---升序,默认
+----+------+---------+
| id | name | payroll |
+----+------+---------+
|  2 | K    |  670.00 |
|  1 | Q    |  800.00 |
|  3 | H    |  900.00 |
+----+------+---------+
3 rows in set (0.00 sec)

mysql> select * from t2 order by payroll desc;        <<---降序
+----+------+---------+
| id | name | payroll |
+----+------+---------+
|  3 | H    |  900.00 |
|  1 | Q    |  800.00 |
|  2 | K    |  670.00 |
+----+------+---------+
3 rows in set (0.00 sec)

mysql> select * from t2 order by payroll limit 2;        <<---前2行
+----+------+---------+
| id | name | payroll |
+----+------+---------+
|  2 | K    |  670.00 |
|  1 | Q    |  800.00 |
+----+------+---------+
2 rows in set (0.00 sec)





四、多表查询
mysql> select t1.name,t1.tel,t2.payroll from t1,t2 where t1.name = t2.name;   <<---内连接,显示两表相同列的其他信息
+------+------+---------+
| name | tel  | payroll |
+------+------+---------+
| Q    |  123 |  800.00 |
| K    |  456 |  670.00 |
| H    |  789 |  900.00 |
+------+------+---------+
3 rows in set (0.00 sec)




mysql> select t2.id,t1.name,t1.mail,t2.payroll        <<---左连接,先显示左表,再匹配
    -> from t1 left join t2                            ###left
    -> on t1.name = t2.name;                           ###匹配
+------+------+-----------+---------+
| id   | name | mail      | payroll |
+------+------+-----------+---------+
|    3 | H    | H@123.com |  900.00 |
|    2 | K    | K@123.com |  670.00 |
| NULL | M    | M@123.com |    NULL |
|    1 | Q    | Q@123.com |  800.00 |
+------+------+-----------+---------+
4 rows in set (0.00 sec)

mysql> select t2.id,t1.name,t1.mail,t2.payroll        <<---右连接,先显示右表,再匹配
    -> from t1 right join t2                           ###right
    -> on t1.name = t2.name;                           ###匹配
+----+------+-----------+---------+
| id | name | mail      | payroll |
+----+------+-----------+---------+
|  1 | Q    | Q@123.com |  800.00 |
|  2 | K    | K@123.com |  670.00 |
|  3 | H    | H@123.com |  900.00 |
+----+------+-----------+---------+
3 rows in set (0.00 sec)




mysql> select t2.id,t1.name from t1,t2;        <<---交叉连接,查询结果相乘
+----+------+
| id | name |
+----+------+
|  1 | H    |
|  2 | H    |
|  3 | H    |
|  1 | K    |
|  2 | K    |
|  3 | K    |
|  1 | M    |
|  2 | M    |
|  3 | M    |
|  1 | Q    |
|  2 | Q    |
|  3 | Q    |
+----+------+
12 rows in set (0.00 sec)





五、复合使用
mysql> select t1.name,t1.mail,t2.id,t2.payroll        <<---左连接+and匹配
    -> from t1 left join t2
    -> on t1.name =t2.name                            ###第一次条件匹配
    -> and payroll >700;                              ###第二次条件匹配
+------+-----------+------+---------+
| name | mail      | id   | payroll |
+------+-----------+------+---------+
| H    | H@123.com |    3 |  900.00 |
| K    | K@123.com | NULL |    NULL |
| M    | M@123.com | NULL |    NULL |
| Q    | Q@123.com |    1 |  800.00 |
+------+-----------+------+---------+
4 rows in set (0.00 sec)


mysql> select t1.name,t1.mail,t2.id,t2.payroll        <<---左连接+排序
    -> from t1 left join t2
    -> on t1.name = t2.name                           ###条件匹配
    -> order by id asc;                               ###结果排序
+------+-----------+------+---------+
| name | mail      | id   | payroll |
+------+-----------+------+---------+
| M    | M@123.com | NULL |    NULL |
| Q    | Q@123.com |    1 |  800.00 |
| K    | K@123.com |    2 |  670.00 |
| H    | H@123.com |    3 |  900.00 |
+------+-----------+------+---------+
4 rows in set (0.00 sec)



mysql> select * from t1 where name                         ###然后根据匹配结果显示
    -> in (select name from t2 where payroll >700);        ###首先条件匹配
+------+-----------+------+
| name | mail      | tel  |
+------+-----------+------+
| Q    | Q@123.com |  123 |
| H    | H@123.com |  789 |
+------+-----------+------+
2 rows in set (0.00 sec)


mysql> select * from t1 where
    -> exists (select * from t2 where payroll >1000);        ###条件不成立,输出空
Empty set (0.00 sec)

mysql> select * from t1 where
    -> exists (select * from t2 where payroll >800);         ###条件成立,执行父句
+------+-----------+------+
| name | mail      | tel  |
+------+-----------+------+
| H    | H@123.com |  789 |
| K    | K@123.com |  456 |
| M    | M@123.com | NULL |
| Q    | Q@123.com |  123 |
+------+-----------+------+
4 rows in set (0.00 sec)

DCL:数据库控制语言(权限管理)

GRANT,REVOKE

创建用户
mysql> create user tom@'localhost' identified by 'Tom@123.com';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.user\G;        ###查看用户


删除用户
mysql> drop user tom@'localhost';
Query OK, 0 rows affected (0.01 sec)


修改密码
mysql> alter user 'root'@'localhost' identified by 'JKzone@123456';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;                        ###加载配置
Query OK, 0 rows affected (0.00 sec)



登录参数
[root@node1 ~]# mysql -uroot -p -P 3306            ###指定端口
Enter password: 

[root@node1 ~]# mysql -uroot -p school             ###指定数据库
Enter password: 

[root@node1 ~]# mysql -utom -p -h 192.168.1.2;     ###远程登录
Enter password: 


[root@node1 ~]# mysql -uroot -p'JKzone@123' -e "show databases;"        <<---不登录执行命令
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| node1              |
| performance_schema |
| school             |
| sys                |
+--------------------+




授权管理
mysql> grant all on node1.* to tom@'localhost';        ###授权本地登录,管理node1库
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on node1.* to tom@'192.168.1.%';        ###个别网段登录
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,update on school.test3 to tom@'localhost';        ###指定查询、更新权限
Query OK, 0 rows affected (0.00 sec)

mysql> grant select(id,age) on school.test3 to tom@'localhost';       ###指定列权限
Query OK, 0 rows affected (0.00 sec)



查看授权
mysql> show grants\G                                <<---当前用户
*************************** 1. row ***************************
Grants for tom@112.74.52.%: GRANT USAGE ON *.* TO `tom`@`112.74.52.%`
1 row in set (0.00 sec)

mysql> show grants for mary@'localhost';            <<---指定用户
+-------------------------------------------------------------+
| Grants for mary@localhost                                   |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mary`@`localhost`                    |
| GRANT ALL PRIVILEGES ON `school`.`t2` TO `mary`@`localhost` |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)



回收授权
mysql> revoke all on school.t2 from mary@'localhost';
Query OK, 0 rows affected (0.00 sec)

  • 9
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

金牌收租佬

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值