下载地址mysql

常用数据类型

  1. double(5,2):表示最多5位，其中必须有2位小数
  2. char(10):固定长度的字符串类型；char(10) ‘abc ‘
  3. varchar(10):可变长度字符串类型；varchar(10) ‘abc’
  4. text:字符串类型
  5. blob:二进制类型
  6. date:日期类型，格式位yyyy-MM-dd
  7. time:时间类型，格式位hh:mm:ss
  8. datetime:日期时间类型，yyyy-MM-dd hh:mm:ss

在mysql中，字符串类型和日期类型都要用单引号括起来。’MXADAQ’,’2020-1-11 ‘

数据表常用操作（DDL）

  1. 创建数据库：

    1. create database db_users character set utf8 collate utf8_general_ci;

    collate utf8_general_ci表示数据库字符校对规则
    修改默认字符集 ,这样数据库建的表和字段都会使用设置的字符集’utf8’，collate utf8_general_ci为设置校对规则，可以省略不写

    1. -- 查看数据库编码
    2. show create database db_name;
    3. -- 查看数据库表编码
    4. show create table tb_name;
    5. -- 查看字段的编码
    6. show full columns from tb_name;
    7. -- 修改数据库字符集
    8. alter database db_users default character set utf8 collate utf8_general_ci;
    9. -- 修改表的字符集 
    10. alter table tb_students character set gbk;
    11. -- 让字段可以填入中文,修改字段的字符集 
    12. alter table tb_students change name name varchar(20) character set utf8 collate utf8_general_ci;

    2.删除数据库：(谨慎使用)

    1. drop database db_users;

  2. 使用数据库：

    1. use db_users;

  3. 显示全部数据库

    1. show databases;

  4. 查看数据库表

    1. use db_users;
    2. show tables;

  5. 创建数据库表

    1. use db_users;
    2. create table tb_users(
    3. id bigint auto_increment not null primary key,
    4. name varchar(30) not null,
    5. age int(16) not null,
    6. address varchar(30) not null,
    7. createtime datetime);

  6. 查看数据库的属性

    1. describe tb_users;
    2. -- 简写为
    3. desc employees;

    image.png

  7. 修改数据库表结构

    1. #新增一列 alter table tb_users add scores int not null;
    2. #添加主键 alter table tb_users add primary key(id);
    3. #添加唯一索引 alter table tb_users add unique(name);
    4. #删除主键 alter table tb_users drop primary key cascade;
    5. #删除not null约束 alter table tb_users modify name null;
    6. #修改字段类型 alter table tb_users modify name varchar(20);
    7. #修改列名 alter table tb_users change name address varchar(30) not null;
    8. #删除一列 alter table tb_users drop address;
    9. #更改表名字 rename table tb_users to tb_students;
    10. #查看创建表的过程 show create table tb_users;
    11. >>>>
    12. | Table    | Create Table                                                                                                                                                                                                                                                                     |
    13. +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    14. | tb_users | CREATE TABLE `tb_users` (
    15. `id` bigint(20) NOT NULL AUTO_INCREMENT,
    16. `username` varchar(30) CHARACTER SET latin1 NOT NULL,
    17. `password` varchar(30) CHARACTER SET latin1 NOT NULL,
    18. `createtime` datetime NOT NULL,
    19. PRIMARY KEY (`id`)
    20. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    21. +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    22. 1 row in set (0.00 sec)

  8. 删除数据库表

    1. drop table if exists teachers; #'if exists'避免删除不存在的表

    数据常用操作(DML)

  1. 插入数据
  • 列名与列值类型，个数，顺序要一一对应
  • 值不要超过定义的长度

  • 插入日期和字符要用引号括起来

    1. #添加一条
    2. insert into teachers (id,address,name,score) values (3,'上海','张三',70);
    3. #添加多条
    4. insert into teachers (id,address,name,score) values (4,'广州','王五',79),(5,'广州','洪五',19);
    5. >>Query OK, 2 rows affected (0.01 sec)
    6. >>Records: 2  Duplicates: 0  Warnings: 0

    如果再插入前数据库中就已经有这个记录，那就需要先删除原纪录，再插入新记录

  • 情景示例：这张表存的每个客户最近一次交易订单信息，要求保证单个用户数据不重复录入，且执行效率最高，与数据库交互最少，支撑数据库的高可用

此时可以使用replace into 这样就不用先查询再决定是否先删除再插入，“replace into”语句是基于唯一索引或主键来判断唯一（是否存在）的。

  1. replace into last_transaction (transId,username,amount,trans_time,remark) 
  2.    values (null, 'chenhaha', 30, '2020-06-11 20:00:20', '会员充值');
  3. replace into last_transaction (transId,username,amount,trans_time,remark) 
  4.    values (null, 'chenhaha', 100, '2020-06-11 21:00:00', '购买盲僧至高之拳皮肤');

如果希望插入一条新记录，但是记录已经存在就更新改记录，可以使用”insert into…on duplicate key update…”

  • 情景示例：这张表存了用户历史充值金额，如果第一次充值就新增一条数据，如果该用户充值过就累加历史充值金额，需要保证单个用户数据不重复录入。
    1. insert into total_transction(t_tansId,username,total_amount,last_transTime,last_remark)
    2. values (null,'chenhaha',30,'2020-06-11 20:00:01','充值会员') on duplicate key update total_amount=total_amount+30,last_transTime='2020-06-11 20:00:20',last_remark='充会员';
    如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用insert ignore into…
    1. insert ignore into users_info (id, username, sex, age ,balance, create_time) 
    2. values (null, 'chenhaha', '男', 12, 0, '2020-06-11 20:00:20');

  1. 更改数据

    1. select * from teachers\G;

    * 1. row * id: 1 address: 安徽省合肥市 name: 王五 score: 70 * 2. row * id: 2 address: 北京 name: 李四 score: 90 * 3. row * id: 3 address: 上海 name: 张三 score: 70 * 4. row * id: 4 address: 广州 name: 王五 score: 79 * 5. row * id: 5 address: 广州 name: 洪五 score: 19 5 rows in set (0.00 sec)

    1. #未加条件，所以的分数变为90
    2. update teachers set score=90;
    3. >>>>
    4. +----+--------------+------+-------+
    5. | id | address      | name | score |
    6. +----+--------------+------+-------+
    7. |  1 | 安徽省合肥市 | 王五 |    90 |
    8. |  2 | 北京         | 李四 |    90 |
    9. |  3 | 上海         | 张三 |    90 |
    10. |  4 | 广州         | 王五 |    90 |
    11. |  5 | 广州         | 洪五 |    90 |
    12. +----+--------------+------+-------+
    13. 5 rows in set (0.00 sec)
    14. #改张三的为60
    15. update teachers set score=60 where name='张三';
    16. >>>>
    17. +----+--------------+------+-------+
    18. | id | address      | name | score |
    19. +----+--------------+------+-------+
    20. |  1 | 安徽省合肥市 | 王五 |    90 |
    21. |  2 | 北京         | 李四 |    90 |
    22. |  3 | 上海         | 张三 |    60 |
    23. |  4 | 广州         | 王五 |    90 |
    24. |  5 | 广州         | 洪五 |    90 |
    25. +----+--------------+------+-------+
    26. 5 rows in set (0.00 sec)
    27. #改变名字是张三且id为3的人的分数为70(多个条件用‘and’连接)
    28. update teachers set score=70 where id=3 and name='张三';
    29. >>>>
    30. +----+--------------+------+-------+
    31. | id | address      | name | score |
    32. +----+--------------+------+-------+
    33. |  1 | 安徽省合肥市 | 王五 |    90 |
    34. |  2 | 北京         | 李四 |    90 |
    35. |  3 | 上海         | 张三 |    70 |
    36. |  4 | 广州         | 张三 |    90 |
    37. |  5 | 广州         | 张三 |    90 |
    38. +----+--------------+------+-------+
    39. 5 rows in set (0.00 sec) 
    40. #名字为李四的年龄加1
    41. alter table teachers add age int not null;
    42. update teachers set age=20;
    43. update teachers set age=age+1,score=90 where name='李四';
    44. >>>>
    45. +----+--------------+------+-------+-----+
    46. | id | address      | name | score | age |
    47. +----+--------------+------+-------+-----+
    48. |  1 | 安徽省合肥市 | 王五 |    90 |  20 |
    49. |  2 | 北京         | 李四 |    90 |  22 |
    50. |  3 | 上海         | 张三 |    70 |  21 |
    51. |  4 | 广州         | 张三 |    90 |  21 |
    52. |  5 | 广州         | 张三 |    90 |  21 |
    53. +----+--------------+------+-------+-----+

  2. 删除数据库表记录

    1. delete from teachers where name='王五';
    2. select * from teachers;
    3. +----+---------+------+-------+-----+
    4. | id | address | name | score | age |
    5. +----+---------+------+-------+-----+
    6. |  2 | 北京    | 李四 |    90 |  22 |
    7. |  3 | 上海    | 张三 |    70 |  21 |
    8. |  4 | 广州    | 李四 |    80 |  31 |
    9. |  5 | 广州    | 张三 |    90 |  21 |
    10. +----+---------+------+-------+-----+
    11. delect from teachers;#直接删除表中数据，但是表结构还在，数据可以找回
    12. truncate table teachers;#等同于先drop table teachers;然后create table if not exists teachers();表的结构还在，但是数据无法找回了

  3. 修改数据库密码

选择到mysql数据库里面的user表，用户相关信息都在这里面

  1. update mysql.user set authentication_string=password('12345678') where user='root' and Host='localhost';
  2. flush privileges;#刷新注册表
  3. exit;#退出重新登录
  4. mysql -uroot -q12345678
  1. mysqladmin -u root -p password 123456
  2. Enter password: **********
  3. mysqladmin: [Warning] Using a password on the command line interface can be insecure.
  4. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

数据库查询操作（DQL）

  1. 查询所有的列

    1. select * from teachers;

  2. 查询指定的列

    1. select address,name,age from teachers;

  3. 按条件查询

where语句常用的运算符

字段名 默认值 描述
= 等于 id=1
> id>5
< id<3
>= id>=4
<= id<=9
!= id!=10
is null id is null
is not null id is not null
between … and … id between 1 and 10
in id in (4,5,6)
not in name not in (a,b)
like %匹配一个或者多个，_至匹配一个字符 name like (‘abc_’)
not like %匹配一个或者多个，_至匹配一个字符 name not like (‘abc%’)
regexp 常规表达式 name正则表达式 
  1. select * from teachers where id=1 or name='李四';
  2. select * from teachers where id in (1,4,5);
  3. select * from teachers where name is not null;
  4. select * from teachers where gender != '男';
  5. select * from teachers where age between 18 and 20;
  6. select * from teachers where name like '__'; #name是2个字符的全部数据
  7. select * from teachers where name like '王%'; #name以王开头的数据
  8. select * from teachers where name like '%三%';#查出全部name包含这个‘三’的数据
  1. 在结果中去除重复行

distinct可以在结构中去除掉重复行

  1. select  distinct name from teachers;

image.png

  1. 查询结果进行运算，必须都是数据型

ifnull(score,0) 表示：如果score为null则设置为0

  1. select *, age+ifnull(score,0) from teachers;

image.png

  • 列名很难看，使用as修改：

    1. select *, age+ifnull(score,0) as total from teachers;

    image.png

  • as可以为任何字段取别名：

    1. select name as tername from teachers;

    image.png

  1. 字段排序操作

  • asc表示升序排序

    1. select * from teachers order by age; #默认是升序排序

    image.png

  • desc表示降序排序

    1. select * from teachers order by age desc,id desc;

    image.png

  1. 聚合函数

    1. count 统计不为null的数据

      1. select count(*) from teachers;
      2. select count(*) as total from teachers where ifnull(score,0)+age>100;

      image.png
      统计多个

      1. select count(*) as total,count(age) from teachers where ifnull(score,0)+age>100;

      image.png

    2. max

      1. select max(age) from teachers;

    3. min

      1. select min(age) from teachers;

    4. sum

      1. select sum(age),sum(score) from teachers;

      image.png

    5. avg

      1. select avg(age) from teachers;

      image.png

  2. 分组查询

    1. select * from teachers group by gender;

    image.png
    只会显示每组中的第一个数据，要都显示通常需要把需要显示的字段加到group by里面，例如：

    1. select name,gender from teachers group by gender,name;

    image.png

    1. select gender,group_concat(name),group_concat(age),group_concat(score) from teachers group by gender;

    按照男女分组，显示包含的名字
    image.png
    分组最主要的作用是分组进行聚合计算

    1. select gender,group_concat(age),sum(age) from teachers group by gender;

    image.png
    查找男、女年龄大于20的人数：

    1. select gender,group_concat(age),count(age) from teachers where age>20 group by gender;

    image.png
    HAVING,只显示满足条件的结果

  • 分组后进行筛选
  • having后面可以使用聚合函数

只显示成绩大于100的城市老师信息：

  1. select address,group_concat(age),group_concat(score),sum(score) from teachers 
  2. group by address having sum(score)>100;

image.png

  1. 限制查询显示的列数（分页操作）

角标从0开始

  1. select * from teachers limit 0,3;

image.png