Tutorial sencillo de mysql - Queries bÃ¡sicas para aprender bases de datos

cabecera duhnnae.com

Tutorial bÃ¡sico cÃ³mo crear una base de datos en mysql, crear una tabla, aÃ±adir una columna, hacer consultas sobre esa tabla, y eliminar registros.
Tutoriales sencillos de mysql, programaciÃ³n y cÃ³digo en general, para que puedas aprender de una forma sencilla.

duhnn@duhnn-VirtualBox:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 5.5.43-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| emotions_land                |
| mysql                        |
| performance_schema           |
| printingemotions_development |
| pruebas                      |
| wshopping_development        |
+------------------------------+
7 rows in set (0.00 sec)

mysql> create database testtutorial;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| emotions_land                |
| mysql                        |
| performance_schema           |
| printingemotions_development |
| pruebas                      |
| testtutorial                 |
| wshopping_development        |
+------------------------------+
8 rows in set (0.00 sec)

mysql> select * from user;
ERROR 1046 (3D000): No database selected
mysql> use testtutorial;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create usuarios(id INTEGER PRIMARY KEY,nombre VARCHAR(100));
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 'usuarios(id INTEGER PRIMARY KEY,nombre VARCHAR(100))' at line 1
mysql> create table usuarios(id INTEGER PRIMARY KEY,nombre VARCHAR(100));
Query OK, 0 rows affected (0.10 sec)

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

mysql> describe usuarios;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | NULL    |       |
| nombre | varchar(100) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table usuarios add apellido VARCHAR(100);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe usuarios;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| nombre   | varchar(100) | YES  |     | NULL    |       |
| apellido | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into usuarios (id,nombre,apellido) VALUES (100,"Pepe","Ramos");
Query OK, 1 row affected (0.10 sec)

mysql> insert into usuarios (id,nombre,apellido) VALUES (101,"Pepa","Rama");
Query OK, 1 row affected (0.07 sec)

mysql> insert into usuarios (id,nombre,apellido) VALUES (102,"Juan","Ostras");
Query OK, 1 row affected (0.05 sec)

mysql> insert into usuarios (id,nombre,apellido) VALUES (102,"Fran","Jurez");
ERROR 1062 (23000): Duplicate entry '102' for key 'PRIMARY'
mysql> insert into usuarios (id,nombre,apellido) VALUES (105,"Fran","Jurez");
Query OK, 1 row affected (0.07 sec)

mysql> select * from usuarios;
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 100 | Pepe   | Ramos    |
| 101 | Pepa   | Rama     |
| 102 | Juan   | Ostras   |
| 105 | Fran   | Jurez    |
+-----+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from usuarios where nombre LIKE '%Pep%';
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 100 | Pepe   | Ramos    |
| 101 | Pepa   | Rama     |
+-----+--------+----------+
2 rows in set (0.00 sec)

mysql> select * from usuarios where nombre LIKE 'Pep';
Empty set (0.00 sec)

mysql> select * from usuarios where nombre LIKE 'Pepe';
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 100 | Pepe   | Ramos    |
+-----+--------+----------+
1 row in set (0.00 sec)

mysql> select * from usuarios where nombre LIKE 'Pep%';
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 100 | Pepe   | Ramos    |
| 101 | Pepa   | Rama     |
+-----+--------+----------+
2 rows in set (0.00 sec)

mysql> select nombre,apellido from usuarios where nombre LIKE 'Pep%';
+--------+----------+
| nombre | apellido |
+--------+----------+
| Pepe   | Ramos    |
| Pepa   | Rama     |
+--------+----------+
2 rows in set (0.00 sec)

mysql> select nombre,apellido from usuarios where nombre NOT LIKE 'Pep%';
+--------+----------+
| nombre | apellido |
+--------+----------+
| Juan   | Ostras   |
| Fran   | Jurez    |
+--------+----------+
2 rows in set (0.01 sec)

mysql> select * from usuarios;
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 100 | Pepe   | Ramos    |
| 101 | Pepa   | Rama     |
| 102 | Juan   | Ostras   |
| 105 | Fran   | Jurez    |
+-----+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from usuarios where id > 101;
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 102 | Juan   | Ostras   |
| 105 | Fran   | Jurez    |
+-----+--------+----------+
2 rows in set (0.00 sec)

mysql> select * from usuarios where id < 101;
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 100 | Pepe   | Ramos    |
+-----+--------+----------+
1 row in set (0.00 sec)

mysql> select * from usuarios where id <= 101;
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 100 | Pepe   | Ramos    |
| 101 | Pepa   | Rama     |
+-----+--------+----------+
2 rows in set (0.00 sec)

mysql> select * from usuarios where id IN (100,101);+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 100 | Pepe   | Ramos    |
| 101 | Pepa   | Rama     |
+-----+--------+----------+
2 rows in set (0.00 sec)

mysql> select * from usuarios where id IN (100,120);
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 100 | Pepe   | Ramos    |
+-----+--------+----------+
1 row in set (0.00 sec)

mysql> delete from usuarios where id = 100;
Query OK, 1 row affected (0.18 sec)

mysql> select * from usuarios;
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 101 | Pepa   | Rama     |
| 102 | Juan   | Ostras   |
| 105 | Fran   | Jurez    |
+-----+--------+----------+
3 rows in set (0.01 sec)

mysql> delete from usuarios limit 1;
Query OK, 1 row affected (0.17 sec)

mysql> select * from usuarios;
+-----+--------+----------+
| id  | nombre | apellido |
+-----+--------+----------+
| 102 | Juan   | Ostras   |
| 105 | Fran   | Jurez    |
+-----+--------+----------+
2 rows in set (0.01 sec)

mysql> delete from usuarios;
Query OK, 2 rows affected (0.06 sec)

mysql> select * from usuarios;
Empty set (0.00 sec)

mysql> drop table usuarios;
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| emotions_land                |
| mysql                        |
| performance_schema           |
| printingemotions_development |
| pruebas                      |
| testtutorial                 |
| wshopping_development        |
+------------------------------+
8 rows in set (0.00 sec)

mysql> drop database testtutorial;
Query OK, 0 rows affected (0.23 sec)

mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| emotions_land                |
| mysql                        |
| performance_schema           |
| printingemotions_development |
| pruebas                      |
| wshopping_development        |
+------------------------------+
7 rows in set (0.00 sec)

mysql> exit
Bye

ProgramaciÃ³n

Mysql

Terminal ubuntu