Mysql数据库数据增删改查

设：
create database db_books default character set utf8mb4 collate utf8mb4_general_ci; ##建db_books数据库设置
use db_books ##进入db_books
create table t_hero(               ##建t_hero表
   id int primary key auto_increment,   ##字段为id 类型为int，添加主键(主键自增)
   username varchar(255) unique not null,   ##字段为username 类型为varchar(非固定字节),添加唯一约束，非空约束
   age int check(age >= 18),   ##字段为age 类型为int,添加检查约束(大于等于18才可以添加进去)注：8.0版本以上为强制约束。
   gender char(5) check( gender in ("男", "女")),   ##字段为gender 类型为char(固定字节)添加检查约束(只能添加"男"或者"女")
   tel char(20) default '110' )       ##字段为tel 类型为char(非固定),添加默认约束(默认为110)

增加（insert）：

insert into t_hero(字段1 ...) values(v1 ....);
insert into t_hero(id, username, gender, age, tel) values(null, "哇哈哈", "男", 20, default);

更新（update）：

   将表中原有数据修改为我们需要的数据
   update t_name set 字段1 = 新值, 字段2 = 新值 ... where 条件
   update t_hero set age = 18, gender = "女" where id = 7;   ##修改表中id为7的数据，age=18,gender="女"

删除（delete）：

   delete from table name where 条件
   delete from t_hero where id =11;   ##删除表中id为11的数据
   truncate 语句（慎用）
   该语句也是用来删除表，是无法恢复的

查(select)：##查看数据

   select * from 表名;
   select id, username, age, gender, tel from t_hero;   ##查看全部字段的 t_hero
   select username from t_hero;           ##查看username列的 t_hero
       desc t_hero（表名称）   ##查看表结构
       show create table t_hero;

表的修改

表结构的修改：alter table
       |-- 增加新的字段
           ALTER TABLE 表名 ADD 列名列类型约束条件;
           alter table t_hero add address varchar(255);       ##字段为address 类型为varchar(非固定字节)添加到表t_hero中
       |-- 修改字段的类型
           ALTER TABLE 表名 MODIFY 列名列类型;
           alter table t_hero modify id bigint;           ##修改表中字节为id的类型，修改为bigint
       |-- 修改字段名称
           ALTER TABLE 表名 CHANGE 旧列名新列名列类型;
           alter table t_hero change id hero_id int;           ##修改表中的字段id，修改为hero_id，类型为int(也可以重新定义)
       |-- 修改表名称
           ALTER TABLE 表名 RENAME 新表名;
           alter table t_hero rename hero;               ##修改表t_hero名字,改为hero
           -- 第二种写法
           rename table hero to t_hero;

表的复制：

|-- create table 新表名 like 源表
create table xiyou like t_hero; ##复制t_hero表，名字为xiyou

|-- create table 新表名 select * from 源表
create table sanguo select * from xiyou; ##第二种写法

排序：

SQL，默认如果没有指定排序规则，则按照主键升序排列

   select 字段1, 字段2...
   from 表名称
   [where 条件]
   [order by 字段A [asc|desc] [,字段B...] ]

分组：

以字段结果相同为一组，进行分组

注：如果遇到函数查询时可： select count(*),sex  from student group by sex; #会按照性别分组并计算总人数
   select 字段1, 字段2...
   from 表名称
   [where 条件]
   [group by 字段]
   [order by 字段A [asc|desc] [,字段B...] ]

分组后的筛选：

注：该语句必须是在group by之后

   select 字段1, 字段2...
   from 表名称
   [where 条件]
   [group by 字段 [having 筛选条件]]
   [order by 字段A [asc|desc] [,字段B...] ]

分页：

select 字段1, 字段2...       列：limit 0,2 | 2,2 | 4,2   #会显示每页俩行内容
   from 表名称
   [where 条件]
   [group by 字段 [having 筛选条件]]
   [order by 字段A [asc|desc] [,字段B...] ]
   [limit 数字, 数字]

内连接（inner join）：三种方式

   select *
   from emp, dept
   where emp.deptId = dept.id and age > 30; ##俩张表需要一个关联式，三个表需要俩个关联式

   select e.*, d.name as dName
   from emp e
   inner join dept d on(e.deptId=d.id)

   select e.name uname , e.age as age, d.name deptName
   from emp e
   join dept d on(e.deptId=d.id)
   where e.age > 40;

外连接（outer join）

   |-- 左外连接：
       select *
       from emp
       left join dept on(emp.deptId=dept.id);

   |-- 右外连接
       select *
       from emp
       right join dept on(emp.deptId=dept.id);

主要用于报表和数据统计时

自连接（self join）

自然连接（nature join）
       这种标准会以两张表列名相同作为标准

       board 板块

       create table board (
           id int primary key auto_increment,
           board_name varchar(100) not null unique,
           descs varchar(255),
           parent_id int
       )

       insert into board values(null, "java技术", "java语言相关区域", null);
       insert into board values(null, ".NET技术", ".NET语言相关区域", null);
       insert into board values(null, "PYTHON技术", "PYTHON语言相关区域", null);
       insert into board values(null, "前端技术", "前端语言相关区域", null);
       insert into board values(null, "PHP技术", "PHP语言相关区域", null);

       insert into board values(null, "IDEA工具", "java编程工具", 1);
       insert into board values(null, "框架", "java web框架", 1);
       insert into board values(null, "spring", "spring全家桶", 1);
       insert into board values(null, "asp", "asp相关区域", 2);
       insert into board values(null, "C#", "C#语言相关区域", 2);

insert into board values(null, "test", "C#语言相关区域", 8);

       -- ”java技术“板块下所有子版块
       select b2.* from board as b1, board as b2
       where b1.id=b2.parent_id and b1.board_name="java技术"

子查询：

将查询的结果作为一张表参与二次查询，就叫做子查询

       1、将第一次查询的结果作为条件参与二次查询
           select * from board where parent_id=(select id from board where board_name="java技术")
       2、也可以将第一次查询的结果作为表参与二次查询
           select * from board, (select * from board where parent_id=2) as b where board.id=b.parent_id;

授权认证：

   dcl：
       grant
       revoke

   创建用户：
       第一种方式：
           CREATE USER <'用户名'@'地址'> IDENTIFIED BY ‘密码’;
           # 创建一个名称为ljh，密码也是ljh的用户
           create user ljh@'%' identified by 'ljh';

       第二种方式：
           insert插入到mysql.user表中
           INSERT INTO mysql.user(user,host, authentication_string,ssl_cipher,
               x509_issuer,x509_subject)
           VALUES('user2','localhost',password('ABCabc123!'),'','','');
       第三种方式：
           grant创建用户
           grant 权限 on db.table to username@'%' identified by 密码;

           grant select on db_spring.* to cjc@"%" identified by 'cjc';

grant select on *.* to cjc@"%" identified by 'cjc';

           grant [select, update, show, create, drop,insert] privilegs
           on db_name.*
           to 'zbt'@'%' identified by 'zbt';

grant all on db_yckd.* to cjc@"%" identified by 'cjc';

取消授权：
revoke
revoke 权限 on *.* from 'cjc'@'%';

删除用户：
drop user cjc;
delete from mysql.user where user='cjc';

函数：
sql中提供了哪些好用的函数

   version()       # 用来查询当前数据库的版本
   user()           # 查询当前登录用户
   database()       # 查询当前所在的数据库
   uuid()           # 返回uuid的值，分布式情况下数据库主键不重复的解决方案

   聚合函数：
       count(列名称)       # 统计行
       max(列名称)       # 最大值
       min(列名称)       # 最小值
       sum(列名称)       # 求和统计
       avg(列名称)       # 求平均数

   如果使用了聚合函数，建议和别名配合使用！！
   数值型函数：
       abs(num)       # 求绝对值
       sqrt(num)       # 开平方根
       pow(x, y)/power       # 幂次方
       mod(x, y)       # 求余
       ceil(num)/ceiling()   # 向上取整
       floor(num)       # 向下取整
       round(num)       # 四舍五入
       RAND()           # 随机数
       sign(num)       # 返回自然数的符号（正：1，负：-1，0为0）

   字符串函数
       length()           # 获取字符串存储长度，注意中文编码问题
       char_length()           # 字符长度
       concat(s1,s2...)       # 拼接字符串
       INSERT(str,pos,len,newstr)   # 替换字符串
       lower()               # 转换为小写
       upper()               # 转大写
       left(s, len)           # 从左侧截取len长度的字符串
       right(s, len)           # 从右侧截取len长度的字符串
       trim()               # 清除字符串两侧空格
       replace(s,s1, s2)       # 替换字符串
       substring(s, pos, len)       # 截取字符串
       reverse(str)           # 翻转字符串
       STRCMP(expr1,expr2)       # 比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1则相反
       INSTR(str,s)           # 返回第一次出现子串的位置
       locate(s, str [,pos])       # 返回第一次出现子串的位置，pos表示匹配位置

日期和时间函数

   insert into user VALUES(null, "王皮皮", "2000-06-13 09:26:33");
   insert into user VALUES(null, "王皮皮", "2000/06/13 09:26:33");

insert into user VALUE(uuid(), "欧阳飞天", now());

   SELECT CURDATE();
   SELECT CURRENT_DATE();
   SELECT CURRENT_DATE;

   SELECT CURTIME();
   SELECT CURRENT_TIME();
   SELECT CURRENT_TIME;

SELECT NOW();
SELECT SYSDATE();

   # 获取给定时间的日期
   SELECT DATE(now());
   SELECT DATE('2002-03-26 01:01:13');

SELECT TIME(SYSDATE());
SELECT TIME('2002-03-26 01:01:13');

SELECT MONTH(now());
SELECT MONTHNAME(now());

   # 每月的第几天
   SELECT DAY(now());
   -- 星期数
   SELECT DAYNAME(now());
   SELECT DAYOFWEEK(now()) # 0是星期天，以此类推
   SELECT week(now());

   SELECT year(now());
   # 查询一年中的那一天
   SELECT DAYOFYEAR(now());

SELECT DATEDIFF('2020-10-10', "20200808");

SELECT SEC_TO_TIME(60804)
SELECT TIME_TO_SEC(now())

posted on 2020-10-17 14:30 也有理想阅读(153) 评论(0) 编辑收藏举报

刷新页面返回顶部

也有理想

导航

公告