COMANDO
|
DESCRIPCION
|
SENTENCIA
|
PANTALLA
|
show databases;
|
Vercual4es son lasbases de datos que
enstanguadados
|
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| soporte |
| test |
+--------------------+
4 rows in set (0.00 sec)
|
|
Createtable
|
Crear una tabla
|
|
|
show tables
|
Ver
las tablas creadas
|
mysql> show tables from soporte;
+-------------------+
| Tables_in_soporte |
+-------------------+
| usuarios |
+-------------------+
1 row in set (0.00 sec)
|
SHOW
[FULL] TABLES [FROM db_name]
[LIKE 'pattern']
|
Show columns from
|
Ver
las columnas de una tabla
|
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| nombre | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
|
|
Droptable8span lang="ES-MX" style="font-family: 'Bell MT', serif;">
|
Elimina la tabla, incluyendo registros y
estructura.
|
DROP TABLE [
database_name . [ schema_name ] . | schema_name . ]<+div>
table_name [ ,...n ] [
; ]
|
DROP
[TEMPORARY] TABLE [IF EXISTS]
tbl_name
[, tbl_name] ...
[RESTRICT | CASCADE]
|
renametable [table] to [nuevo nombre de tabla]; –
|
Renombra una tabla de la base de datos.
|
RENAME
TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2]
...
|
RENAME
TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2]
...
|
Dele from
|
Borrar los datos de una tabla
|
delete
from user where user=[usuario] and host=”nombre_host”
|
DELETE
FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
|
Trúncate table
|
|
TRUNCATE TABLE
[
{database_name .[ schema_name ] . | schema_name . } ]
table_name
[ ; ]
|
USE AdventureWorks2012;
GO
SELECT COUNT(*) AS
BeforeTruncateCount
FROM
HumanResources.JobCandidate;
GO
TRUNCATE TABLE
HumanResources.JobCandidate;
GO
SELECT COUNT(*) AS
AfterTrqncateCount
FROM
HumanResources.JobCandidate;
GO
|
Where
|
No borrar los datos que cumplen una condición
|
mysql>
SELECT 'Monty!' REGEXP 'm%y%%';
-> 0
mysql>
SELECT 'Monty!' REGEXP '.*';
-> 1
mysql>
SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
-> 1
mysql>
SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
-> 1 0
mysql>
SELECT 'a' REGEXP '^[a-d]';
-> 1
|
mysql>
SELECT 1+'1';
-> 2
mysql>
SELECT CONCAT(2,' test');
-> '2 test'
|
Update
|
Modificar datos de una tabla
|
UPDATE
[LOW_PRIORITY] [IGNORE]
SET
col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
|
UPDATE
[LOW_PRIORITY] [IGNORE] tbl_name
SET
col_name1=expr1 [, col_name2=expr2 ...]
[WHERE
where_definition]
[ORDER
BY ...]
[LIMIT row_count]
|
set
|
Es una condición
|
Set (option)
opción_configuración
|
|
Add
|
Agregar un campo
|
|
|
Decimal
|
Tipo de dato que se usara en el campo
|
mysql>
SELECT ABS(2);
-> 2
mysql>
SELECT ABS(-32);
-> 32
|
|
modify
|
Modifica el tipo de datos de un campo
|
Alter table nombre
8span lang="ES-MX" style="font-family: 'Bell MT', serif;">Modifynombrecampo nueva
definición
|
alter table
NOMBRETABLA
modify NOMBRECAMPO
NUEVADEFINICION;
|
Change
|
Cambiar el nombre de un campo
|
|
|
Dropcolum
|
Renombrar algún campo
|
|
DROP TABLE [ifexists] (lista_tablas_separadas_po_comas)
|
Rename
|
Cambiar el nombre de una tabla
|
RENAME
TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2]
...
|
|
Createdatabase
|
Crear la base de datos
|
|
|
|
|
|
|
Inserinto
|
Introducir varios datos
|
INSERT INTO<nombre_tabla>
[(<campo1>[,<campo2>,...])]
values
(<valor1>,<valor2>,...);
|
|
Primarykey
|
Primer campo
|
create table libros(
codigoint not null,
titulo varchar(30),
autor varchar(30),
editorial varchar(20),
primarykey(codigo)
);
|
|
Select*from
|
Podemos ver todos los datos para comprobar que son correctos
|
USE AdventureWorks2012;
GO
SELECT *
FROM Production.Product
ORDER BY Name ASC;
-- Alternate way.
USE AdventureWorks2012;
GO
SELECT p.*
FROM Production.Product
AS p
ORDER BY Name ASC;
GO
|
|
Selectfromwhereleft
|
Precio mediode las sillas
|
SELECT id, name, LEFT(comments, 25) FROM comments ORDER BY cdate DESC LIMIT 5;
|
|
Alter table
|
Alterar la tabla
|
ALTER
[ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
|
|
Status
|
Obtiene información del estado del
servidor
|
SHOW TABLE
STATUS [FROM db_name] [LIKE 'pattern']
|
|
|
exit
|
|
select zona, fila, numero, nom
from espectaclesesp, entrades e
where esp.codi_recinte=e.codi_recinte
and nom like 'El país de les Cent Paraules'
and NOT EXISTS (select zona, fila, numero
from seients s, espectaclesesp
where esp.codi_recinte=s.codi_recinte
and nom like 'El país de les Cent Paraules'
)
order by zona
|
|
|
charset
|
Cambiar codificación de lenguaje.
|
[mysqld]:
character-set-server=lo que sea
collation-server=lo que sea
default-character-set=lo que sea
default-collation=lk que sea
|
|
|
delimiter
|
Establece un conjunto delimitador.
NOTE: Toma el resto de la línea como un nuevo delimitador.
|
delimiter //
drop procedure if exists
`database``stored procedure` //
create procedure
`database``stored procedure`
(parameter1 datatype,
parameter2 datatype)
begin
declare localvariabledatatype;
set localvariable = parameter2;
select fields from table where
field1 = parameter1;
end //
delimiter ;
|
|
|
print
|
Imprime el comando actual.
|
seleccione "Crear la tabla foo", como
"Acción";
will produce the following
output: se producirá la siguiente
salida:
+----------------------+ + ---------------------- +
| Action | | Acción |
+----------------------+ + ---------------------- +
| Creating Foo table | | Creación de la tabla foo |
+----------------------+ + ---------------------- +
1 row in set (0.00 sec) 1 row in set (0.00 sec)
|
|
|
Adverten
|
Muestra avisos después de cada
declaración.
|
MOSTRAR COUNT (*) ADVERTENCIAS;
SELECT
@@warning_count; SELECT @ @
warning_count;
|
|
|
nowarning
|
No muestra avisos después de cada
declaración.<+o:p>
|
mysql> CHANGE MASTER TO MASTER_HOST = 'n09.mysql.com';
Query OK, 0 rows affected (0.03 sec)Query OK, 0 filas afectadas (0,03 segundos)
Made sure it took Se ha asegurado de que se
mysql> show slave status\Gmysql> SHOW SLAVE STATUS \ G
*************************** 1. *************************** 1. row ***************************fila ***************************
Slave_IO_State: Waiting for master to send eventSlave_IO_State: A la espera de maestro para enviar un evento
Master_Host: n09.mysql.comMaster_Host: n09.mysql.com
|
|
|
notee
|
No escribe dentro del archivo de
salida.
|
mysqldump -h
123.456.789.112 -u webuserprod -p
--ignore-table=databasename.flag_contentdatabasename>backupfile.sql
|
|
|
VARCHAR
|
Cadena de longitud variable.
Opcionalmente se puede indicar el tamaño máximo de la cadena con un número
entre paréntesis.
|
mysql>CREATE
TABLE vc (v VARCHAR(4), c CHAR(4));
Query
OK, 0 rows affected (0.01 sec)Query OK, 0 filas afectadas (0,01 seg)
mysql>INSERT INTO vc VALUES ('ab
', 'ab ');mysql>INSERT
INTO vc VALUES ('ab ', 'ab ');
Query
OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec)
mysql>SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;mysql>SELECT CONCAT('(', v,
')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+ + --------------------- + --------------------- +
|
CONCAT('(', v, ')') | CONCAT('(', c, ')') | | CONCAT ('(' v ')') | CONCAT ('(', c ')') |
+---------------------+---------------------+ + --------------------- + --------------------- +
| (ab
) | (ab) | | (Ab) | (ab) |
+---------------------+---------------------+ + --------------------- + --------------------- +
1 row
in set (0.06 sec) 1 row in set
(0.02 segundos)
|
|
|
BINARY:
|
Similar a CHAR pero
en este caso las cadenas se almacenan como binarias
|
shell>
shell>useradd -r -g mysql mysql shell>useradd -r -g mysqlmysql
shell>cd /usr/local shell>cd
/usr/local
shell>tar zxvf /path/to/mysql-VERSION-OS .tar.gz shell>tar zxvf/path/to/mysql-VERSION-OS
.tar.gz
shell>ln -s full-path-to-mysql-VERSION-OS mysql shell>ln -s full-path-to-mysql-VERSION-OSmysql
shell>cd mysql shell>cd
mysql
shell>chown -R mysql .shell>chown -R mysql .
shell>chgrp -R mysql .shell>chgrp -R mysql .
shell>scripts/mysql_install_db --user=mysql shell>scripts/mysql_install_db
--user=mysql
shell>chown -R root .shell>chown
-R root .
shell>chown -R mysql data shell>chown -R mysql data
# Next command is optional # Comando siguiente es opcional
shell>cp
support-files/my-medium.cnf /etc/my.cnfshell>cp support-files/my-medium.cnf /etc/my.cnf
shell>bin/mysqld_safe --user=mysql & shell>bin/mysqld_safe
--user=mysql&
#
Next command is optional # Comandosiguienteesopcional
shell>cp support-files/mysql.server
/etc/init.d/mysql.server shell>cp
support-files/mysql.server /etc/init.d/mysql.server
|
|
|
VARBINARY
|
Similar
a VARCHAR pero en este caso las cadenas se almacenan como binarias.
|
mysql>CREATE
TABLE t (c BINARY(3));
Query
OK, 0 rows affected (0.01 sec)Query OK, 0 filas afectadas (0,01 seg)
mysql>INSERT INTO t SET c = 'a';mysql>INSERT INTO t SET c = 'a';
Query
OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0,01 segundos)
mysql>SELECT HEX(c), c = 'a', c = 'a\0\0' from t;mysql>SELECT HEX(c), c = 'a', c
= 'a\0\0' from t;
+--------+---------+-------------+ + -------- + --------- + ------------- +
|
HEX(c) | c = 'a' | c = 'a\0\0' | | HEX (c) | c = 'a' | c = 'a \ 0 \ 0' |
+--------+---------+-------------+ + -------- + --------- + ------------- +
|
610000 | 0 | 1 | | 610000 | 0 | 1
|
+--------+---------+-------------+ + -------- + --------- + ------------- +
1 row
in set (0.09 sec) 1 row in set
(0.09 segundos)
|
|
|
BLOB:
|
Un
BinaryLargeOBject (gran objeto binario), para almacenar datos binarios como
imágenes. El tamaño máximo es de 65.535 bytes.
|
QStringfileName =
"imagen.jpg";
QImage image(filaName);QImageimagen
(filaName);
LBL_IMAGE->setPixmap(QPixmap::fromImage(image));
// Put image into QLabel object (optional) LBL_IMAGE->setPixmap
(QPixmap :: FromImage (imagen)); / / Colocar la imagen en QLabelobjeto (opcional)
// load image to bytearray / / Carga de
la imagen de bytearray
QByteArray ba;QByteArrayba;
QFile f(fileName);QFile f (fileName);
if(f.open(QIODevice::ReadOnly)) if
(f.open (QIODevice :: ReadOnly))
{ {
ba = f.readAll();ba = f.readAll ();
f.close();f.close ();
} }
|
|
|
MEDIUMBLOB
|
Un BLOB de
tamaño medio (un máximo de 16.777.215 bytes; cerca de 16MB)
|
MySqlTransactionmytranmyConnection.BeginTransaction
= ();
MySqlCommand myCommand =
new MySqlCommand(query, myConnection, myTrans);MySqlCommandmyCommand = new MySqlCommand (consulta,
myConnection, mytran);
myCommand.Parameters.Add(new
MySqlParameter("?fitxer", vFitxer));myCommand.Parameters.Add (MySqlParameternueva
("Fitxer?", vFitxer));
myCommand.ExecuteNonQuery();
// It takes 1:42 minutesmyCommand.ExecuteNonQuery (); / / Se toma unos minutos 1:42
myTrans.Commit(); // The error throws heremyTrans.Commit (); / / error
|
|
|
TINYTEXT
|
Un
texto pequeño, con un máximo de 255 caracteres.
|
TINYINT () -128-127 FIRMADO o 0 a 255 UNSIGNED
SMALLINT( ) -32768 to 32767 SIGNED or 0 to 65535
UNSIGNED SMALLINT () -32768 a 32767 FIRMADO o 0-65535
UNSIGNED
MEDIUMINT( ) -8388608 to 8388607 SIGNED or 0 to
16777215 UNSIGNED MEDIUMINT () -8388608 a 8388607
FIRMADO o 0-16777215 UNSIGNED
INT( ) -2147483648 to 2147483647 SIGNED or 0 to
4294967295 UNSIGNED INT ()
-2147483648 a 2147483647 o FIRMADO 0 a 4294967295 UNSIGNED
BIGINT( )
-9223372036854775808 to 9223372036854775807 SIGNED or 0 to
18446744073709551615 UNSIGNED BIGINT () -9223372036854775808-9223372036854775807 FIRMADO o 0 a
18446744073709551615 UNSIGNED
FLOAT small number with a
floating decimal point FLOAT pequeño número con un punto decimal flotante
DOUBLE( , ) large number
with a floating decimal point DOBLE (,) con un gran número de punto decimal flotante
DECIMAL( , ) DOUBLE stored as a string , allowing for a fixed decimal
point
|
|
|
NOT NULL
|
No se puede introducir valores nulos.
|
mysql>SELECT
1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+ + ----------- + --------------- +
| 1
IS NULL | 1 IS NOT NULL | | 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+ + ----------- + --------------- +
| 0 |
1 | | 0 | 1 |
+-----------+---------------+ + ----------- + --------------- +
|
|
|
No hay comentarios:
Publicar un comentario