mysql增删改查练习方法

有空的时候自己复习了一下简单的增删改查，下面有我写的实例，保存下来的记录保存方法

命令行练习方法，并保存自己敲击笔记的方法

登录mysql后即: mysql -uroot -ppassword

输入 tee mysql_demo.txt 回车，继续输入代码即可，

这时候会在你的cmd显示的位置创建一个mysql_demo.txt，结果和代码，都会转存到文件中

可参考图片:

mysql书写注意几点:

    版本:

1.  mysql版本的语句是否还允许这个语句  

    标点:

1. 最后有没有分号，大部分语句都要加分号；
2. 括号是不是英文的，分号是不是英文的

    语法：

1. 是否语法不对，如果语法错误了，那么就是硬伤，可以查一查具体到底是怎么写

如果英语方面不是特别的烂，一定把句子的每个单词翻译一遍，会很舒服

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| article            |
| book               |
| byte_easy          |
| demo               |
| flag_hezhi         |
| information_schema |
| jxgl               |
| mysql              |
| newdatabase        |
| performance_schema |
| sys                |
| test               |
+--------------------+
12 rows in set (0.00 sec)

mysql> create DATABASE demo
    -> ;
ERROR 1007 (HY000): Can't create database 'demo'; database exists
mysql> create DATABASE BOOK;#数据库不区分大小写，会显示已经存在
ERROR 1007 (HY000): Can't create database 'book'; database exists
mysql> create DATABASE newDemo;#会创建小写的数据库名
Query OK, 1 row affected (0.20 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| article            |
| book               |
| byte_easy          |
| demo               |
| flag_hezhi         |
| information_schema |
| jxgl               |
| mysql              |
| newdatabase        |
| newdemo            |
| performance_schema |
| sys                |
| test               |
+--------------------+
13 rows in set (0.00 sec)

mysql> use new demo
ERROR 1049 (42000): Unknown database 'new'
mysql> use newdemo
Database changed
mysql> create table t_demo;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table t_demo(
    -> id int,
    -> name varchar(20),
    -> pwd varchar(30),
    -> money decimal(7,3)
    -> );
Query OK, 0 rows affected (2.12 sec)

mysql> desc t_demo;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(20)  | YES  |     | NULL    |       |
| pwd   | varchar(30)  | YES  |     | NULL    |       |
| money | decimal(7,3) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into t_demo values(1,"hezhi","hezhi",100.00);#这个可以插入成功
Query OK, 1 row affected (0.21 sec)

mysql> insert into t_demo values(2,"hz","hz",10000000,333);#应该不可以
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t_demo values(2,"hz","hz",10000000.333);#应该不可以
ERROR 1264 (22003): Out of range value for column 'money' at row 1
mysql> insert into t_demo values(2,"hz","hz",1000000.33);#应该可以了
ERROR 1264 (22003): Out of range value for column 'money' at row 1
mysql> insert into t_demo values(2,"hz","hz",10000.33);#这时候发现，是这样的长度是7，小数点保留3位
ERROR 1264 (22003): Out of range value for column 'money' at row 1
mysql> insert into t_demo values(2,"hz","hz",1000.33);#这时候发现，是这样的长度是7，小数点保留3位
Query OK, 1 row affected (0.11 sec)

mysql> insert into t_demo values(2,"hz","hz",1000.333);#这时候又发现，整数位智能设置4位，小数点最多3位
Query OK, 1 row affected (0.06 sec)

mysql> insert into t_demo values(2,"hz","hz",1000.3334);#这时候又发现，整数位智能设置4位，小数点最多3位
Query OK, 1 row affected, 1 warning (0.18 sec)

mysql> select * from t_demo#会发现上一个就算保留成功m,也只有3位小数
    -> ;
+------+-------+-------+----------+
| id   | name  | pwd   | money    |
+------+-------+-------+----------+
|    1 | hezhi | hezhi |  100.000 |
|    2 | hz    | hz    | 1000.330 |
|    2 | hz    | hz    | 1000.333 |
|    2 | hz    | hz    | 1000.333 |
+------+-------+-------+----------+
4 rows in set (0.00 sec)

mysql> insert into t_demo values(3,"decimal","decimal",1000.3334123);#整数位智能设置4位，小数点最多3位
Query OK, 1 row affected, 1 warning (0.21 sec)

mysql> select * from t_demo#会发现上一个就算保留成功m,也只有3位小数
    -> ;
+------+---------+---------+----------+
| id   | name    | pwd     | money    |
+------+---------+---------+----------+
|    1 | hezhi   | hezhi   |  100.000 |
|    2 | hz      | hz      | 1000.330 |
|    2 | hz      | hz      | 1000.333 |
|    2 | hz      | hz      | 1000.333 |
|    3 | decimal | decimal | 1000.333 |
+------+---------+---------+----------+
5 rows in set (0.00 sec)

mysql> delete * from t_demo where name=`hz` limit 2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from t_demo where name=`hz` limit 2' at line 1
mysql> delete * from t_demo where name="hz" limit 2;#标垫写错了刚才
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from t_demo where name="hz" limit 2' at line 1
mysql> delete * from t_demo where `name`="hz" limit 2;#标垫写错了刚才
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from t_demo where `name`="hz" limit 2' at line 1
mysql> delete from t_demo where name="hz" limit 2;
Query OK, 2 rows affected (1.15 sec)

mysql> select * from t_demo #原来是这样，delete语句不需要*，用的不熟练
    -> ；
    -> ;
+------+---------+---------+----------+
| id   | name    | pwd     | money    |
+------+---------+---------+----------+
|    1 | hezhi   | hezhi   |  100.000 |
|    2 | hz      | hz      | 1000.333 |
|    3 | decimal | decimal | 1000.333 |
+------+---------+---------+----------+
3 rows in set (0.00 sec)

mysql> update t_demo set name="newName" where id=3;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_demo #update不是updata 记好了
    -> ；
    -> ;
+------+---------+---------+----------+
| id   | name    | pwd     | money    |
+------+---------+---------+----------+
|    1 | hezhi   | hezhi   |  100.000 |
|    2 | hz      | hz      | 1000.333 |
|    3 | newName | decimal | 1000.333 |
+------+---------+---------+----------+
3 rows in set (0.00 sec)

mysql> select * from t_demo where name="hezhi";
+------+-------+-------+---------+
| id   | name  | pwd   | money   |
+------+-------+-------+---------+
|    1 | hezhi | hezhi | 100.000 |
+------+-------+-------+---------+
1 row in set (0.00 sec)

mysql> union select 1,2,database();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union select 1,2,database()' at line 1
mysql> union select 1,2,database() from t_demo;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union select 1,2,database() from t_demo' at line 1
mysql> union select 1,2 from t_demo;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union select 1,2 from t_demo' at line 1
mysql> select 1,3;
+---+---+
| 1 | 3 |
+---+---+
| 1 | 3 |
+---+---+
1 row in set (0.00 sec)

mysql> select 1,3,database();
+---+---+------------+
| 1 | 3 | database() |
+---+---+------------+
| 1 | 3 | newdemo    |
+---+---+------------+
1 row in set (0.00 sec)

mysql> select 1,3,database();#常用于爆数据库
+---+---+------------+
| 1 | 3 | database() |
+---+---+------------+
| 1 | 3 | newdemo    |
+---+---+------------+
1 row in set (0.00 sec)

mysql> select * from t_demo union select 1,3,database();
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select * from t_demo union select 1,2,3,database();
+------+---------+---------+----------+
| id   | name    | pwd     | money    |
+------+---------+---------+----------+
|    1 | hezhi   | hezhi   | 100.000  |
|    2 | hz      | hz      | 1000.333 |
|    3 | newName | decimal | 1000.333 |
|    1 | 2       | 3       | newdemo  |
+------+---------+---------+----------+
4 rows in set (0.00 sec)

mysql> select * from t_demo union select 222,333,444,database();
+------+---------+---------+----------+
| id   | name    | pwd     | money    |
+------+---------+---------+----------+
|    1 | hezhi   | hezhi   | 100.000  |
|    2 | hz      | hz      | 1000.333 |
|    3 | newName | decimal | 1000.333 |
|  222 | 333     | 444     | newdemo  |
+------+---------+---------+----------+
4 rows in set (0.00 sec)

mysql> select * from t_demo union select 222,333,444,database();#联合查询，常用爆库的列数
+------+---------+---------+----------+
| id   | name    | pwd     | money    |
+------+---------+---------+----------+
|    1 | hezhi   | hezhi   | 100.000  |
|    2 | hz      | hz      | 1000.333 |
|    3 | newName | decimal | 1000.333 |
|  222 | 333     | 444     | newdemo  |
+------+---------+---------+----------+
4 rows in set (0.00 sec)

mysql> select name from t_demo union select 222,333,444,database();#联合查询，常用爆库的列数
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select name from t_demo union select database();#联合查询，常用爆库的列数
+---------+
| name    |
+---------+
| hezhi   |
| hz      |
| newName |
| newdemo |
+---------+
4 rows in set (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_newdemo |
+-------------------+
| t_demo            |
+-------------------+
1 row in set (0.01 sec)

mysql> create table drop_ing(
    -> id int,
    -> name varchar(123),
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 4
mysql> create table drop_ing(
    -> id int,
    -> name varchar(12),
    -> nono varchar(123));
Query OK, 0 rows affected (0.76 sec)

mysql> desc drop_ing#差点就m又多输入了个标点符号
    -> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(12)  | YES  |     | NULL    |       |
| nono  | varchar(123) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.36 sec)

mysql> drop table if exists drop_ing;
Query OK, 0 rows affected (0.83 sec)

mysql> show tables;
+-------------------+
| Tables_in_newdemo |
+-------------------+
| t_demo            |
+-------------------+
1 row in set (0.00 sec)

mysql> create table haha(
    -> id int);
Query OK, 0 rows affected (1.06 sec)

mysql> desc haha
    -> ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.13 sec)

mysql> drop table if exists A;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> #结束了，结束了
mysql> exit