某某茶叶有限公司欢迎您!
金沙棋牌在线 > 必赢棋牌官网 > mysql语句以及实例

mysql语句以及实例

时间:2020-03-22 21:24

简单的mysql教程 分组统计语句实例代码/*id name code_id code_name is_true score1 张三 1 帅气 是 52 张三 2 阔气 是 43 张三 1 帅气 是 44 张三 2 阔气 否 3 5 李四 1 帅气 是 56 李四 2 阔气 是 3

数据库操作

mysql年月分组语句

来看个实例mysql 如何 按name 分组 统计: 有多少个是和否,有多少个是和否,还有多少个

数据库备份与恢复

 

*/

备份mydb数据库,使用mydb.sql文件存储
[root@localhost bin]# mysqldump -u root -p mydb > mydb.sql

Java代码  

select name, sum(if(code_name='帅气' and is_true='是',1,0)), sum(if(code_name='帅气' and is_true='否',1,0)), sum(if(code_name='阔气' and is_true='是',1,0)), sum(if(code_name='阔气' and is_true='否',1,0)), count(*)from table1group by name

备份mydb数据库中的stu表,使用mydb.sql文件存储
[root@localhost bin]# mysqldump -u root -p mydb stu > mydb_stu.sql

SELECT count(DISTINCT(a.rect_id)) zcount, a.job_dept,   

恢复mydb数据信息(条件是mydb库存在)
[root@localhost bin]# mysql -u root -p mydb < mydb.sql

         DATE_FORMAT(submit_date, '%Y-%m') zsubmit_date  

查看当前MySQL下的所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

FROM  

创建一个mydb2的数据库

mysql> create database mydb2;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb2              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

        表名 a  

删除数据库mydb2

mysql> drop database mydb2;
Query OK, 0 rows affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

WHERE  

创建一个mydb数据库
mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

       a.statu = 3  

再次常见mydb数据库会报错
mysql> create database mydb;
ERROR 1007 (HY000): Can''t create database 'mydb'; database exists

       AND a.rstatu = 2  

尝试创建mydb数据库(若已存在则会报一个警告,不会报Error错误)
mysql> create database if not exists  mydb;
Query OK, 1 row affected, 1 warning (0.00 sec)

       AND a.job_dept IN ('19', '20', '21')  

查看mydb的建库语句
mysql> show create database mydb;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database mydbG
*************************** 1. row ***************************
       Database: mydb
Create Database: CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

GROUP BY  

查看当前所在数据库位置:NULL表示没有在任何数据库中
mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

       a.job_dept,  

选择进入mydb数据库
mysql> use mydb;
Database changed

       DATE_FORMAT(submit_date, '%Y-%m')  

查看当前所在数据库的位置
mysql> select database();
+------------+
| database() |
+------------+
| mydb       |
+------------+
1 row in set (0.00 sec)

 其中关键在于DATE_FORMAT(submit_date, '%Y-%m')对时间年月进行了分组排序

查看当前数据库中的所有表
mysql> show tables;
Empty set (0.00 sec)

 

创建一个uu表,内有三个字段id,name和age
mysql> create table uu(id int,name varchar(16),age int);
Query OK, 0 rows affected (0.09 sec)

Java代码  

查看uu表的建表语句
mysql> show create table uuG
*************************** 1. row ***************************
       Table: uu
Create Table: CREATE TABLE `uu` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(16) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

SELECT  

删除uu表
mysql> drop table uu;
Query OK, 0 rows affected (0.02 sec)

         zsubmit_date,  

添加一个数据,给定所有字段,所有的值
mysql> insert into uu(id,name,age) values(1,'zhangsan',20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into uu(id,name,age) values(2,'lisi',22);
Query OK, 1 row affected (0.02 sec)

         MAX(CASE WHEN job_dept = '19' THEN zcount ELSE 0 END ) 19zcount,  

不指定字段,添加值,值按默认顺序写
mysql> insert into uu values(3,'wangwu',25);
Query OK, 1 row affected (0.01 sec)

         MAX(CASE WHEN job_dept = '20' THEN zcount ELSE 0 END ) 20zcount,  

批量添加值
mysql> insert into uu values(4,'zhaoliu',21),
    -> (5,'xiaoli',22),
    -> (6,'xiaozhang',19);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

         MAX(CASE WHEN job_dept = '21' THEN zcount ELSE 0 END ) 21zcount  

将id值为4的信息age改为30(修改)
mysql> update uu set age=30 where id=4;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from uu;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    1 | zhangsan  |   20 |
|    2 | lisi      |   22 |
|    3 | wangwu    |   25 |
|    4 | zhaoliu   |   30 |
|    5 | xiaoli    |   22 |
|    6 | xiaozhang |   19 |
+------+-----------+------+
6 rows in set (0.00 sec)

FROM  

删除id为5的所有信息
mysql> delete from uu where id=5;
Query OK, 1 row affected (0.01 sec)

     (  

修改表结构实例

mysql> show create table ttG
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(16) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc tt;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

       SELECT   

1. 在tt表末尾添加一个phone字段,类型varchar(11),无其他约束
mysql> alter table tt add phone varchar(11);

           count(DISTINCT(a.rect_id)) zcount, a.job_dept,   

2. 在tt表中age字段后添加一个address字段,类型varchar(100) ,无其他约束
mysql> alter table tt add address varchar(100) after age;

           DATE_FORMAT(submit_date, '%Y-%m') zsubmit_date  

3. 在tt表首位插入一个mm字段,类型int
mysql> alter table tt add mm int first;
mysql> desc tt;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| mm      | int(11)      | YES  |     | NULL    |       |
| id      | int(11)      | YES  |     | NULL    |       |
| name    | varchar(16)  | YES  |     | NULL    |       |
| age     | int(11)      | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| phone   | varchar(11)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

       FROM  

4. 删除tt表的mm字段
mysql> alter table tt drop mm;

            表名 a  

5. 修改字段:tt表age字段类型改为tinyint类型,unsigned not null default 20
mysql> alter table tt modify age tinyint unsigned not null default 20; 

       WHERE  

6. 修改name字段名为username
mysql> alter table tt change name username varchar(16);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tt;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id       | int(11)             | YES  |     | NULL    |       |
| username | varchar(16)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | NO   |     | 20      |       |
| address  | varchar(100)        | YES  |     | NULL    |       |
| phone    | varchar(11)         | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

           a.statu = 3  

数据的增、删、改,查

=============

mysql> desc stu;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name    | varchar(8)          | NO   | UNI | NULL    |                |
| age     | tinyint(3) unsigned | YES  |     | NULL    |                |
| sex     | enum('w','m')       | NO   |     | m       |                |
| classid | char(8)             | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

           AND a.rstatu = 2  

1. 添加:

           AND a.job_dept IN ('19', '20', '21')  

格式:insert into 表名[(字段列表)] values(值列表)[,(值列表)...]

       GROUP BY