MySQL - CURD/CRUD

DATABASE
    show databases;
    create database DBName;
    drop database DBName;
    ('Can not alter DBName')
    use DBName;

TABLE
    show tables;

    create table tableName (
    C1Name C1Type [C1Attribute] [Default Value],
    ...
    CnName CnType [CnAttribute] [Default Value]  ('the last column can not add comma')
    ) enqine enqineName charset charType;

    drop table tableName;
    rename table oldName to newName;
    desc tableName;    // check the table structure

    insert into tableName
    (column1, column2, ...) [option]
    values
    (column1, column2, ... );

    select (column) from table;

    update tableName
    set
    column1 = value1,
    ...
    columnN = valueN    // the last column can not add comma
    where
    conditions;

    delete from tableName where conditions;    ('NOTE: can not delete column. If you want to delete certain column, It is the same as updating the column with value '' ')

1、连接服务器了就可以查看有哪些数据库：
    show databases; --->注意，MySQL语句是以分号结束的！
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
information_schema：数据库的基本信息；
mysql：用户信息；如，root
performance_schema：性能优化的信息；
    上面的3个数据库是不能够随便修改的，如果更改了上面的3个数据库服务器就起不来了。

2、创建数据库：
    create database DBName;

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

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

1 2	3、删除数据库： drop database DBName;

1 2	4、修改数据库名：能否修改数据库的名字呢？不能！

5、选择数据库：
    use DBName; //选择相应的数据库，注意，这条语句是可以不加分号的。

mysql> use lzs；
Database changed

mysql> use lzs
Database changed

6、如何查看数据库的所以元素：
    show tables；

mysql> show tables;
Empty set (0.00 sec)

7、如何创建一张表：
    create table tablename (
	列1名称  列1类型 [列1属性] [默认值]，
	...

	列n名称  列n类型 [列n属性] [默认值](最后一列不能加逗号)
    ）enqine 引擎名 charset 字符集；

    建表的时候，最好就先在记事本上先写出来，再复制过去创建。

mysql> create table class
    -> (
    -> num int,
    -> name varchar(20), //varchar()，字符串
    -> height int //注意，最后一列不能够加逗号，因为逗号是用来分开列的。
    -> );
Query OK, 0 rows affected (0.19 sec)

//创建名为shop_all的表格
//id varchar(6) not null分别对应：列名、数据类型、数据长度、是否为空值
mysql> create table shop_all(
    -> id varchar(6) not null,
    -> status varchar(16) not null,
    -> owner varchar(20) not null,
    -> owner_id varchar(18) not null,
    -> introduction varchar(500) not null,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> show tables;
+---------------+
| Tables_in_lzs |
+---------------+
| class         |
| shop_all      |
+---------------+
2 rows in set (0.00 sec)

1 2	8、删除一张表： drop table tableName；

9、如何修改表名：
    rename table oldName to newName；

mysql> show tables;
+---------------+
| Tables_in_lzs |
+---------------+
| class         |
| shop_all      |
+---------------+
2 rows in set (0.00 sec)

mysql> rename table shop_all to shop;
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+---------------+
| Tables_in_lzs |
+---------------+
| class         |
| shop          |
+---------------+

10、查看表结构：
    desc tableName； //describe v.描述  description n.描述
mysql> desc class;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| num    | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| height | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)

mysql> desc shop;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | varchar(6)   | NO   | PRI | NULL    |       |
| status       | varchar(16)  | NO   |     | NULL    |       |
| owner        | varchar(20)  | NO   |     | NULL    |       |
| owner_id     | varchar(18)  | NO   |     | NULL    |       |
| introduction | varchar(500) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

    注意，当输入错误而且又按下了回车键Enter的时候，MySQL是没有返回改错的，

这时候我们可以结束输入"；"，不过这样会有报错。我们可以用"\c"来退出这条语句

。
mysql> create tabel class (     //table打错了
    -> num int,
    -> \c
mysql>


mysql> create table messages (
    -> id int,
    -> name varchar(20),
    -> sex varchar(10),
    -> height varchar(10),
    -> weight varchar(10)
    -> );
Query OK, 0 rows affected (0.46 sec)

mysql> show tables;
+---------------+
| Tables_in_lzs |
+---------------+
| class         |
| messages      |
| shop          |
+---------------+
3 rows in set (0.00 sec)

11、增加数据
    //往哪张表增，增哪几列，各列是什么值。
    insert into tableName //或者insert into lzs.tablename
    (列名1，列名2，...)  //你要增加哪一列的值就加哪一列的名字
    values
    (列1值，列2值，...), //values要与列名对应，varchar型要用单引号括起来
    (列1值，列2值，...); //当要增加多列，一列之间用逗号，最后列分号结束

    insert语句允许不写列名，如果没有声明列名，则默认插入所有列。因此，如果

没有列名，values应该与全部列按顺序一一对应。
    如果某列中不想插入值，可以用插入空格来处理，即' '。

mysql> insert into messages
    -> (id, name, sex, height, weight)
    -> values
    -> (1, 'jack', 'boy', '175cm', '60kg');
Query OK, 1 row affected (0.07 sec)

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 175cm  | 60kg   |
+------+------+------+--------+--------+
1 row in set (0.00 sec)

mysql> insert into messages
    -> (id, name, sex, height, weight)
    -> values
    -> (2, 'rose', 'girl', '165cm', '50kg');
Query OK, 1 row affected (0.09 sec)

12、查看数据
    select (列) from table；

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 175cm  | 60kg   |
|    2 | rose | girl | 165cm  | 50kg   |
+------+------+------+--------+--------+
2 rows in set (0.00 sec)

    只查看不同的值，也就是说，当表中有重复的数据时，只列出其中一个来：
    select distinct (column) from table;

TABLE: messages
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 175cm  | 60kg   |
|    2 | rose | girl | 165cm  | 50kg   |
|    2 | rose | girl | 165cm  | 50kg   |
|    3 | rose | girl | 165cm  | 50kg   |
+------+------+------+--------+--------+

mysql> select name from messages;
+------+
| name |
+------+
| jack |
| rose |
+------+

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 175cm  | 60kg   |
|    2 | rose | girl | 165cm  | 50kg   |
|    3 | rose | girl | 165cm  | 50kg   |
+------+------+------+--------+--------+

13、修改数据
    update tablename
    set
    列名1 = 新值1，
    列名2 = 新值2，
    ...
    列名n = 新值n  //注意，最后一个不要加逗号，逗号作用是用来分开数据的
    where
    条件(如，id = 2、name = 'xxx'等等);

mysql> update messages
    -> set
    -> height = '178cm',
    -> weight = '65kg'
    -> where     //条件
    -> name = 'jack';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 178cm  | 65kg   |
|    2 | rose | girl | 165cm  | 50kg   |
+------+------+------+--------+--------+
2 rows in set (0.00 sec)

mysql> insert into messages
    -> values
    -> (3, 'Jeff', 'boy', '175cm', '63kg'),
    -> (4, 'Tom', 'boy', '180cm', '70kg'),
    -> (5, 'Tim', 'boy', '185cm', '72kg');
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 178cm  | 65kg   |
|    2 | rose | girl | 165cm  | 50kg   |
|    3 | Jeff | boy  | 175cm  | 63kg   |
|    4 | Tom  | boy  | 180cm  | 70kg   |
|    5 | Tim  | boy  | 185cm  | 72kg   |
+------+------+------+--------+--------+
5 rows in set (0.00 sec)

mysql> select * from messages where id>2;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    3 | Jeff | boy  | 175cm  | 63kg   |
|    4 | Tom  | boy  | 180cm  | 70kg   |
|    5 | Tim  | boy  | 185cm  | 72kg   |
+------+------+------+--------+--------+
3 rows in set (0.03 sec)

mysql> select id, name, height from messages where weight = '50kg';
+------+------+--------+
| id   | name | height |
+------+------+--------+
|    2 | rose | 165cm  |
+------+------+--------+
1 row in set (0.00 sec)

14、删除数据
    delete from tablename where 条件； （from前面是没有列的）

    注意，delete只能根据条件删除行，没有删除列的。如果要删除列，那就不叫删

除了，而是将它改为null。用update就可以完成了。

mysql> delete from messages where id=3;
Query OK, 1 row affected (0.07 sec)

mysql> select * from messages;
+------+------+------+--------+--------+
| id   | name | sex  | height | weight |
+------+------+------+--------+--------+
|    1 | jack | boy  | 178cm  | 65kg   |
|    2 | rose | girl | 165cm  | 50kg   |
|    4 | Tom  | boy  | 180cm  | 70kg   |
|    5 | Tim  | boy  | 185cm  | 72kg   |
+------+------+------+--------+--------+
4 rows in set (0.00 sec)

lizs

MySQL - CURD/CRUD