Apache HTTP Server
Apache is configured by the files in the conf subdirectory.
ThreadsPerChild: It tells the server how many threads it should use. This is the maximum number of connections the server can handle at once, so be sure to set this number high enough for your site if you get a lot of hits. The recommended default is ThreadsPerChild 150.
You can review the Windows Application Event Log by using the Event Viewer, e.g. Start - Settings - Control Panel - Administrative Tools - Event Viewer.
-------
install Apache as a Windows NT service from the command prompt at the Apache bin subdirectory:
httpd.exe -k install
--------
Removing an Apache service
httpd.exe -k uninstall
---------
If Apache is running as a service, stop it by opening another console window and entering:
httpd.exe -k shutdown
------
To run Apache from the command line as a console application:
httpd.exe
Press control-C to stop
---------
Running as a service should be preferred over running in a console window because this lets Apache end any current operations and clean up gracefully.
-------
to restart. It forces it to reread the configuration file. Any operations in progress are allowed to complete without interruption. Press Control-Break in the console window you used for starting Apache, or if the server is running as a service, enter:
httpd.exe -k restart
--------
specifies an absolute path to a particular configuration file:
httpd.exe -f "c:\my server files\anotherconfig.conf"
specifies a relative path to a particular configuration file:
httpd.exe -f files\anotherconfig.conf
-----------
If you don't specify a configuration file with -f or -n, Apache will use the file name compiled into the server, such as conf\httpd.conf. This built-in path is relative to the installation directory.
----------
After starting Apache (either in a console window or as a service) it will be listening on port 80.
-----
To connect to the server and access the default page, launch a browser and enter this URL:
http://localhost/
You may have to use this URL:
http://127.0.0.1/
If you happen to be running Apache on an alternate port, you need to explicitly put that in the URL:
http://127.0.0.1:8080/
-------
Addresses on the Web are expressed with URLs - Uniform Resource Locators - which specify a protocol (e.g. http), a servername (e.g. www.apache.org), a URL-path (e.g. /docs/current/getting-started.html), and possibly a query string (e.g. ?arg=value) used to pass additional arguments to the server.
A client (e.g., a web browser) connects to a server (e.g., your Apache HTTP Server), with the specified protocol, and makes a request for a resource using the URL-path.
---------
In order to connect to a server, the client will first have to resolve the servername to an IP address - the location on the Internet where the server resides. Thus, in order for your web server to be reachable, it is necessary that the servername be in DNS.
More than one hostname may point to the same IP address, and more than one IP address can be attached to the same physical server. Thus, you can run more than one web site on the same physical server, using a feature called virtual hosts.
-------
The default configuration file is usually called httpd.conf.
The configuration is frequently broken into multiple smaller files, for ease of management. These files are loaded via the Include directive.
The server is configured by placing configuration directives in these configuration files. A directive is a keyword followed by one or more arguments that set its value.
--------
Static content is things like HTML files, image files, CSS files, and other files that reside in the filesystem. The DocumentRoot directive specifies where in your filesystem you should place these files.
----------
Typically, a document called index.html will be served when a directory is requested without a file name being specified.
---------
The location of the error log is defined by the ErrorLog directive, which may be set globally, or per virtual host.
----------
When httpd starts, it binds to some port and address on the local machine and waits for incoming requests. By default, it listens to all addresses on the machine.
--------
The Listen directive tells the server to accept incoming requests only on the specified port(s) or address-and-port combinations.
For example, to make the server accept connections on both port 80 and port 8000, on all interfaces, use:
Listen 80
Listen 8000
-------
https is the default for port 443 and http the default for all other ports.
-----
You only need to set the protocol if you are running on non-standard ports. For example, running an https site on port 8443:
Listen 192.170.2.1:8443 https
-------
httpd configuration files contain one directive per line. The backslash "\" may be used as the last character on a line to indicate that the directive continues onto the next line. There must be no other characters or white space between the backslash and the end of the line.
Arguments to directives are separated by whitespace. If an argument contains spaces, you must enclose that argument in quotes.
Directives in the configuration files are case-insensitive, but arguments to directives are often case sensitive. Lines that begin with the hash character "#" are considered comments, and are ignored. Comments may not be included on a line after a configuration directive. Blank lines and white space occurring before a directive are ignored, so you may indent directives for clarity.
--------
Only shell environment variables defined before the server is started can be used in expansions.
-------
You can check your configuration files for syntax errors without starting the server by using
apachectl configtest
or the -t command line option.
--------
To see which modules are currently compiled into the server, you can use the -l command line option. You can also see what modules are loaded dynamically using the -M command line option.
--------
Directives placed in the main configuration files apply to the entire server. If you wish to change the configuration for only a part of the server, you can scope your directives by placing them in <Directory>, <DirectoryMatch>, <Files>, <FilesMatch>, <Location>, and <LocationMatch> sections. These sections limit the application of the directives which they enclose to particular filesystem locations or URLs. They can also be nested, allowing for very fine grained configuration.
----------
httpd has the capability to serve many different websites simultaneously. This is called Virtual Hosting. Directives can also be scoped by placing them inside <VirtualHost> sections, so that they will only apply to requests for a particular website.
--------
httpd allows for decentralized management of configuration via special files placed inside the web tree. The special files are usually called .htaccess, but any name can be specified in the AccessFileName directive. Directives placed in .htaccess files apply to the directory where you place the file, and all sub-directories. The .htaccess files follow the same syntax as the main configuration files. Since .htaccess files are read on every request, changes made in these files take immediate effect.
----------
In deciding what file to serve for a given request, httpd's default behavior is to take the URL-Path for the request (the part of the URL following the hostname and port) and add it to the end of the DocumentRoot specified in your configuration files. Therefore, the files and directories underneath the DocumentRoot make up the basic document tree which will be visible from the web.
For example, if DocumentRoot were set to
/var/www/html
then a request for
http://www.example.com/fish/guppies.html
would result in the file
/var/www/html/fish/guppies.html
being served to the requesting client.
If a directory is requested (i.e. a path ending with /), the file served from that directory is defined by the DirectoryIndex directive. For example, if DocumentRoot were set as above, and you were to set:
DirectoryIndex index.html index.php
Then a request for http://www.example.com/fish/ will cause httpd to attempt to serve the file /var/www/html/fish/index.html. In the event that that file does not exist, it will next attempt to serve the file /var/www/html/fish/index.php.
--------
There are frequently circumstances where it is necessary to allow web access to parts of the filesystem that are not strictly underneath the DocumentRoot.
the Alias directive will map any part of the filesystem into the web space. For example, with
Alias /docs /var/web
the URL http://www.example.com/docs/dir/file.html will be served from /var/web/dir/file.html. The ScriptAlias directive works the same way, with the additional effect that all content located at the target path is treated as CGI scripts.
---------
Sometimes, it is desirable instead to inform the client that the requested content is located at a different URL, and instruct the client to make a new request with the new URL. This is called redirection and is implemented by the Redirect directive. For example, if the contents of the directory /foo/ under the DocumentRoot are moved to the new directory /bar/, you can instruct clients to request the content at the new location as follows:
Redirect permanent /foo/ http://www.example.com/bar/
This will redirect any URL-Path starting in /foo/ to the same URL path on the www.example.com server with /bar/ substituted for /foo/. You can redirect clients to any server, not only the origin server.
----------
Tuesday, November 25, 2014
Monday, November 24, 2014
MySQL
MySQL
RDBMS: relational database management system
SQL: Structured Query Language
LAMP: Linux, Apache, MySQL, Perl/PHP/Python
MySQL Workbench: free integrated environment developed by MySQL AB, that enables users to graphically administer MySQL databases and visually design database structures.
---------
ODBC: Open Database Connectivity
user account
privilege
----------
foreign key references
--------
trigger
---------
Ensuring high availability requires a certain amount of redundancy in the system. For database systems, the redundancy traditionally takes the form of having a primary server acting as a master, and using replication to keep secondaries available to take over in case the primary fails. This means that the "server" that the application connects to is in reality a collection of servers, not a single server. In a similar manner, if the application is using a sharded database, it is in reality working with a collection of servers, not a single server. In this case, a collection of servers is usually referred to as a farm.
---------
shell> mysql db_name
shell> mysql --user=user_name --password=your_password db_name
shell> mysql db_name < script.sql > output.tab
--column-names Write column names in results
--tee=file_name Append a copy of output to named file
--help, -?
--auto-vertical-output: Cause result sets to be displayed vertically if they are too wide for the current window, and using normal
tabular format otherwise. (This applies to statements terminated by ; or \G.)
--default-character-set=charset_name: Use charset_name as the default character set for the client and connection. A common issue that can occur when the operating system uses utf8 or another multibyte character set is that output from the mysql client is formatted incorrectly, due to the fact that the MySQL client uses the latin1 character set by default. You can usually fix such issues by using this option to force the client to use the system character set instead.
quit
exit: Exit mysql. Same as quit.
help: Display this help.
tee: Set outfile [to_outfile]. Append everything into given outfile.
use: Use another database. Takes database name as argument.
---------
The prompt command reconfigures the default mysql> prompt. The string for defining the prompt can
contain the following special sequences.
\d The default database
\h The server host
\p The current TCP/IP port or socket file
\U Your full user_name@host_name account name
\u Your user name
\v The server version
-------
SELECT 'Today is' , CURDATE();
--------
mysqladmin
create db_name
debug: Tell the server to write debug information to the error log.
drop db_name
extended-status: Display the server status variables and their values.
flush-privileges: Reload the grant tables
password new-password
ping: Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running.
shutdown: Stop the server.
variables: Display the server system variables and their values.
version: Display version information from the server.
------------
shell> mysql --help
shell> mysql -h host -u user -p
Enter password: ********
mysql> QUIT
SELECT VERSION(), CURRENT_DATE;
SELECT SIN(PI()/4), (4+1)*5;
SELECT VERSION(); SELECT NOW();
SELECT USER(),CURRENT_DATE;
SELECT USER();
SELECT * FROM my_table WHERE name = 'Smith' AND age < 30;
SHOW DATABASES;
USE test; use the database called "test"
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
CREATE DATABASE menagerie;
USE menagerie
shell> mysql -h host -u user -p menagerie
SELECT DATABASE() : to see which database is currently selected
SHOW TABLES;
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
DESCRIBE pet;
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; (to create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values
you can use NULL values. To represent these in your text file, use \N (backslash, capital-N).)
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
SELECT * FROM pet;
DELETE FROM pet;
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
SELECT * FROM pet WHERE name = 'Bowser';
SELECT * FROM pet WHERE birth >= '1998-1-1';
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
SELECT name, birth FROM pet;
SELECT owner FROM pet;
SELECT DISTINCT owner FROM pet;
SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';
SELECT name, birth FROM pet ORDER BY birth;
SELECT name, birth FROM pet ORDER BY birth DESC;
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
SELECT name, birth, MONTH(birth) FROM pet;
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
SELECT 1 IS NULL, 1 IS NOT NULL;
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';
SELECT * FROM pet WHERE name REGEXP '^b';
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
SELECT * FROM pet WHERE name REGEXP 'fy$';
SELECT * FROM pet WHERE name REGEXP 'w';
SELECT * FROM pet WHERE name REGEXP '^.....$';
SELECT * FROM pet WHERE name REGEXP '^.{5}$';
SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;
CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
SELECT pet.name, (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
SELECT DATABASE();
SELECT MAX(article) AS article FROM shop;
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
SELECT article, MAX(price) AS price FROM shop GROUP BY article;
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price;
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL;
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
SELECT * FROM shirt;
SELECT s.* FROM person p INNER JOIN shirt s ON s.owner = p.id WHERE p.name LIKE 'Lilliana%' AND s.color <> 'white';
CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) );
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich');
SELECT * FROM animals;
-----------
A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters. Examples:
'a string'
"another string"
Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:
'a string'
'a' ' ' 'string'
--------------
RDBMS: relational database management system
SQL: Structured Query Language
LAMP: Linux, Apache, MySQL, Perl/PHP/Python
MySQL Workbench: free integrated environment developed by MySQL AB, that enables users to graphically administer MySQL databases and visually design database structures.
---------
ODBC: Open Database Connectivity
user account
privilege
----------
foreign key references
--------
trigger
---------
Ensuring high availability requires a certain amount of redundancy in the system. For database systems, the redundancy traditionally takes the form of having a primary server acting as a master, and using replication to keep secondaries available to take over in case the primary fails. This means that the "server" that the application connects to is in reality a collection of servers, not a single server. In a similar manner, if the application is using a sharded database, it is in reality working with a collection of servers, not a single server. In this case, a collection of servers is usually referred to as a farm.
---------
shell> mysql db_name
shell> mysql --user=user_name --password=your_password db_name
shell> mysql db_name < script.sql > output.tab
--column-names Write column names in results
--tee=file_name Append a copy of output to named file
--help, -?
--auto-vertical-output: Cause result sets to be displayed vertically if they are too wide for the current window, and using normal
tabular format otherwise. (This applies to statements terminated by ; or \G.)
--default-character-set=charset_name: Use charset_name as the default character set for the client and connection. A common issue that can occur when the operating system uses utf8 or another multibyte character set is that output from the mysql client is formatted incorrectly, due to the fact that the MySQL client uses the latin1 character set by default. You can usually fix such issues by using this option to force the client to use the system character set instead.
quit
exit: Exit mysql. Same as quit.
help: Display this help.
tee: Set outfile [to_outfile]. Append everything into given outfile.
use: Use another database. Takes database name as argument.
---------
The prompt command reconfigures the default mysql> prompt. The string for defining the prompt can
contain the following special sequences.
\d The default database
\h The server host
\p The current TCP/IP port or socket file
\U Your full user_name@host_name account name
\u Your user name
\v The server version
-------
SELECT 'Today is' , CURDATE();
--------
mysqladmin
create db_name
debug: Tell the server to write debug information to the error log.
drop db_name
extended-status: Display the server status variables and their values.
flush-privileges: Reload the grant tables
password new-password
ping: Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running.
shutdown: Stop the server.
variables: Display the server system variables and their values.
version: Display version information from the server.
------------
shell> mysql --help
shell> mysql -h host -u user -p
Enter password: ********
mysql> QUIT
SELECT VERSION(), CURRENT_DATE;
SELECT SIN(PI()/4), (4+1)*5;
SELECT VERSION(); SELECT NOW();
SELECT USER(),CURRENT_DATE;
SELECT USER();
SELECT * FROM my_table WHERE name = 'Smith' AND age < 30;
SHOW DATABASES;
USE test; use the database called "test"
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
CREATE DATABASE menagerie;
USE menagerie
shell> mysql -h host -u user -p menagerie
SELECT DATABASE() : to see which database is currently selected
SHOW TABLES;
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
DESCRIBE pet;
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; (to create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values
you can use NULL values. To represent these in your text file, use \N (backslash, capital-N).)
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
SELECT * FROM pet;
DELETE FROM pet;
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
SELECT * FROM pet WHERE name = 'Bowser';
SELECT * FROM pet WHERE birth >= '1998-1-1';
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
SELECT name, birth FROM pet;
SELECT owner FROM pet;
SELECT DISTINCT owner FROM pet;
SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';
SELECT name, birth FROM pet ORDER BY birth;
SELECT name, birth FROM pet ORDER BY birth DESC;
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
SELECT name, birth, MONTH(birth) FROM pet;
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
SELECT 1 IS NULL, 1 IS NOT NULL;
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';
SELECT * FROM pet WHERE name REGEXP '^b';
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
SELECT * FROM pet WHERE name REGEXP 'fy$';
SELECT * FROM pet WHERE name REGEXP 'w';
SELECT * FROM pet WHERE name REGEXP '^.....$';
SELECT * FROM pet WHERE name REGEXP '^.{5}$';
SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;
CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
SELECT pet.name, (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
SELECT DATABASE();
SELECT MAX(article) AS article FROM shop;
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
SELECT article, MAX(price) AS price FROM shop GROUP BY article;
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price;
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL;
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
SELECT * FROM shirt;
SELECT s.* FROM person p INNER JOIN shirt s ON s.owner = p.id WHERE p.name LIKE 'Lilliana%' AND s.color <> 'white';
CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) );
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich');
SELECT * FROM animals;
-----------
A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters. Examples:
'a string'
"another string"
Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:
'a string'
'a' ' ' 'string'
--------------
Subscribe to:
Posts (Atom)